import { pgTable, uuid, text, timestamp, boolean } from "drizzle-orm/pg-core";
import { InferModel } from "drizzle-orm";

export const hubspotDealData = pgTable("hubspot_deal_data", {
  id: uuid("id").defaultRandom().primaryKey(),

  dealId: text("deal_id").notNull(),
  dealname: text("dealname"),
  dealstage: text("dealstage"),
  companyId: text("company_id"),
  projectId: text("project_id"),
  projectCode: text("project_code"),

  landlordPropertyId: text("landlord_property_id"),
  listingId: text("listing_id"),
  uprn: text("uprn"),
  outcome: text("outcome"),
  outcomeNotes: text("outcome_notes"),

  majorConditionIssueDescription: text("major_condition_issue_description"),
  majorConditionIssuePhotos: text("major_condition_issue_photos"),
  majorConditionIssuePhotosS3: text("major_condition_issue_evidence_s3_url"),

  coordinationStatus: text("coordination_status"),
  designStatus: text("design_status"),
  bookingStatus: text("booking_status"),

  pashubLink: text("pashub_link"),
  sharepointLink: text("sharepoint_link"),
  dampmouldGrowth: text("dampmould_growth"),
  preSap: text("pre_sap"),
  coordinator: text("coordinator"),
  mtpCompletionDate: timestamp("mtp_completion_date", { precision: 6, withTimezone: true }),
  mtpReModelCompletionDate: timestamp("mtp_re_model_completion_date", { precision: 6, withTimezone: true }),
  ioeV3CompletionDate: timestamp("ioe_v3_completion_date", { precision: 6, withTimezone: true }),
  proposedMeasures: text("proposed_measures"),
  approvedPackage: text("approved_package"),
  designer: text("designer"),
  dealType: text("design_type"),
  designCompletionDate: timestamp("design_completion_date", { precision: 6, withTimezone: true }),
  actualMeasuresInstalled: text("actual_measures_installed"),
  installer: text("installer"),
  installerHandover: text("installer_handover"),
  lodgementStatus: text("lodgement_status"),
  measuresLodgementDate: timestamp("measures_lodgement_date", { precision: 6, withTimezone: true }),
  lodgementDate: timestamp("lodgement_date", { precision: 6, withTimezone: true }),
  expectedCommencementDate: timestamp("expected_commencement_date", { precision: 6, withTimezone: true }),
  coordination_comments: text("coordination_comments"),
  surveyor: text("surveyor"),
  dampMouldAndRepairComments: text("damp_mould_and_repairs_comments"),
  batch: text("batch"),
  batchDescription: text("batch_description"),
  blockReference: text("block_reference"),
  nonfundedMeasures: text("nonfunded_measures"),
  epcPrn: text("epc_prn"),
  potentialPostSapScoreDropdown: text("potential_post_sap_score_dropdown"),
  eiScore: text("ei_score"),
  eiScorePotential: text("ei_score__potential_"),
  epcSapScore: text("epc_sap_score"),
  epcSapScorePotential: text("epc_sap_score__potential_"), // This is being replaced by potentialPostSapScoreDropdown ^
  confirmedSurveyDate: timestamp("confirmed_survey_date", { precision: 6, withTimezone: true }),
  confirmedSurveyTime: text("confirmed_survey_time"),
  surveyedDate: timestamp("surveyed_date", { precision: 6, withTimezone: true }),

  surveyType: text("survey_type"),
  measuresForPibiOrdered: text("measures_for_pibi_ordered"),
  pibiOrderDate: timestamp("pibi_order_date", { precision: 6, withTimezone: true }),
  pibiCompletedDate: timestamp("pibi_completed_date", { precision: 6, withTimezone: true }),
  propertyHaltedDate: timestamp("property_halted_date", { precision: 6, withTimezone: true }),
  propertyHaltedReason: text("property_halted_reason"),
  technicalApprovedMeasuresForInstall: text("technical_approved_measures_for_install"),
  sentToInstallerForPricing: timestamp("sent_to_installer_for_pricing", { precision: 6, withTimezone: true }),
  domnasurveyRequired: boolean("domna_survey_required"),
  domnaSurveyType: text("domna_survey_type"),
  domnaSurveyDate: timestamp("domna_survey_date", { precision: 6, withTimezone: true }),

  dateBookingMade: timestamp("date_booking_made", { precision: 6, withTimezone: true }),
  lastContactDate: timestamp("last_contact_date", { precision: 6, withTimezone: true }),
  lastOutboundCall: timestamp("last_outbound_call", { precision: 6, withTimezone: true }),
  lastOutboundEmail: timestamp("last_outbound_email", { precision: 6, withTimezone: true }),
  lastSubmissionDate: timestamp("last_submission_date", { precision: 6, withTimezone: true }),

  numberOfAttempts: text("number_of_attempts"),
  clientBookingReference: text("client_booking_reference"),
  thirdPartySurveyorIdentifier: text("third_party_surveyor_identifier"),

  designConstraints: text("design_constraints"),
  planningComments: text("planning_comments"),
  planningStatus: text("planning_status"),
  planningSuggestedApproach: text("planning_suggested_approach"),
  planningAuthority: text("planning_authority"),
  designatedArea: text("designated_area"),
  article4PdRights: text("article_4_pd_rights"),
  listedBuilding: text("listed_building"),

  createdAt: timestamp("created_at", { precision: 6, withTimezone: true })
    .defaultNow()
    .notNull(),

  updatedAt: timestamp("updated_at", { precision: 6, withTimezone: true })
    .defaultNow()
    .$onUpdate(() => new Date())
    .notNull(),
});

