import {
  bigserial,
  text,
  timestamp,
  pgTable,
  real,
  pgEnum,
  integer,
  bigint,
  boolean,
  date,
} from "drizzle-orm/pg-core";
import { InferModel } from "drizzle-orm";

// Define the table
export const energyAssessment = pgTable("energy_assessments", {
  id: bigserial("id", { mode: "bigint" }).primaryKey(),
  uprn: bigint("uprn", { mode: "bigint" }).notNull(),
  uprnSource: text("uprn_source").notNull(),
  propertyType: text("property_type").notNull(),
  buildingReferenceNumber: text("building_reference_number"),
  currentEnergyEfficiency: text("current_energy_efficiency").notNull(),
  currentEnergyRating: text("current_energy_rating").notNull(),
  address1: text("address1").notNull(),
  address2: text("address2").notNull(),
  address3: text("address3"),
  posttown: text("posttown").notNull(),
  postcode: text("postcode").notNull(),
  address: text("address").notNull(),
  county: text("county"),
  constituency: text("constituency"),
  constituencyLabel: text("constituency_label"),
  lowEnergyFixedLightCount: text("low_energy_fixed_light_count").notNull(),
  constructionAgeBand: text("construction_age_band").notNull(),
  mainheatEnergyEff: text("mainheat_energy_eff").notNull(),
  windowsEnvEff: text("windows_env_eff").notNull(),
  lightingEnergyEff: text("lighting_energy_eff").notNull(),
  environmentImpactPotential: text("environment_impact_potential").notNull(),
  mainheatcontDescription: text("mainheatcont_description").notNull(),
  sheatingEnergyEff: text("sheating_energy_eff").notNull(),
  localAuthority: text("local_authority").notNull(),
  localAuthorityLabel: text("local_authority_label").notNull(),
  fixedLightingOutletsCount: text("fixed_lighting_outlets_count").notNull(),
  energyTariff: text("energy_tariff").notNull(),
  mechanicalVentilation: text("mechanical_ventilation").notNull(),
  solarWaterHeatingFlag: text("solar_water_heating_flag").notNull(),
  co2EmissionsPotential: text("co2_emissions_potential").notNull(),
  numberHeatedRooms: text("number_heated_rooms").notNull(),
  floorDescription: text("floor_description").notNull(),
  energyConsumptionPotential: text("energy_consumption_potential").notNull(),
  builtForm: text("built_form").notNull(),
  numberOpenFireplaces: text("number_open_fireplaces").notNull(),
  windowsDescription: text("windows_description").notNull(),
  glazedArea: text("glazed_area").notNull(),
  inspectionDate: timestamp("inspection_date", {
    precision: 6,
    withTimezone: true,
  }).notNull(),
  mainsGasFlag: text("mains_gas_flag").notNull(),
  co2EmissCurrPerFloorArea: text("co2_emiss_curr_per_floor_area").notNull(),
  heatLossCorridor: text("heat_loss_corridor").notNull(),
  unheatedCorridorLength: text("unheated_corridor_length"),
  flatStoreyCount: text("flat_storey_count"),
  roofEnergyEff: text("roof_energy_eff").notNull(),
  totalFloorArea: text("total_floor_area").notNull(),
  environmentImpactCurrent: text("environment_impact_current").notNull(),
  roofDescription: text("roof_description").notNull(),
  floorEnergyEff: text("floor_energy_eff").notNull(),
  numberHabitableRooms: text("number_habitable_rooms").notNull(),
  hotWaterEnvEff: text("hot_water_env_eff").notNull(),
  mainheatcEnergyEff: text("mainheatc_energy_eff").notNull(),
  mainFuel: text("main_fuel").notNull(),
  lightingEnvEff: text("lighting_env_eff").notNull(),
  windowsEnergyEff: text("windows_energy_eff").notNull(),
  floorEnvEff: text("floor_env_eff").notNull(),
  sheatingEnvEff: text("sheating_env_eff").notNull(),
  lightingDescription: text("lighting_description").notNull(),
  roofEnvEff: text("roof_env_eff").notNull(),
  wallsEnergyEff: text("walls_energy_eff").notNull(),
  photoSupply: text("photo_supply").notNull(),
  lightingCostPotential: text("lighting_cost_potential").notNull(),
  mainheatEnvEff: text("mainheat_env_eff").notNull(),
  multiGlazeProportion: text("multi_glaze_proportion").notNull(),
  mainHeatingControls: text("main_heating_controls").notNull(),
  flatTopStorey: text("flat_top_storey"),
  secondheatDescription: text("secondheat_description").notNull(),
  wallsEnvEff: text("walls_env_eff").notNull(),
  transactionType: text("transaction_type").notNull(),
  extensionCount: text("extension_count").notNull(),
  mainheatcEnvEff: text("mainheatc_env_eff").notNull(),
  lmkKey: text("lmk_key"),
  windTurbineCount: text("wind_turbine_count").notNull(),
  tenure: text("tenure").notNull(),
  floorLevel: text("floor_level").notNull(),
  potentialEnergyEfficiency: text("potential_energy_efficiency").notNull(),
  potentialEnergyRating: text("potential_energy_rating").notNull(),
  hotWaterEnergyEff: text("hot_water_energy_eff").notNull(),
  lowEnergyLighting: text("low_energy_lighting").notNull(),
  wallsDescription: text("walls_description").notNull(),
  hotwaterDescription: text("hotwater_description").notNull(),
  co2EmissionsCurrent: text("co2_emissions_current").notNull(),
  heatingCostCurrent: text("heating_cost_current").notNull(),
  heatingCostPotential: text("heating_cost_potential").notNull(),
  hotWaterCostCurrent: text("hot_water_cost_current").notNull(),
  hotWaterCostPotential: text("hot_water_cost_potential").notNull(),
  lightingCostCurrent: text("lighting_cost_current").notNull(),
  energyConsumptionCurrent: text("energy_consumption_current").notNull(),
  lodgementDate: date("lodgement_date").notNull(), // Updated to date
  lodgementDatetime: timestamp("lodgement_datetime", {
    precision: 6,
    withTimezone: false,
  }).notNull(),

  mainheatDescription: text("mainheat_description").notNull(),
  floorHeight: real("floor_height").notNull(),
  glazedType: text("glazed_type").notNull(),
  fileLocation: text("file_location").notNull(),
  surveyorName: text("surveyor_name").notNull(),
  surveyorCompany: text("surveyor_company").notNull(),
  spaceHeatingKwh: text("space_heating_kwh").notNull(),
  waterHeatingKwh: text("water_heating_kwh").notNull(),
  numberOfDoors: integer("number_of_doors").notNull(),
  numberOfInsulatedDoors: integer("number_of_insulated_doors").notNull(),
  numberOfFloors: integer("number_of_floors").notNull(),
  insulationWallArea: real("insulation_wall_area").notNull(),
  heatLossPerimeter: real("heat_loss_perimeter").notNull(),
  partyWallLength: real("party_wall_length").notNull(),
  perimeter: real("perimeter").notNull(),
  roomsWithBathAndOrShower: integer("rooms_with_bath_and_or_shower"),
  roomsWithMixerShowerNoBath: integer("rooms_with_mixer_shower_no_bath"),
  roomWithBathAndMixerShower: integer("room_with_bath_and_mixer_shower"),
  percentDraftproofed: integer("percent_draftproofed"),
  hasHotWaterCylinder: boolean("has_hot_water_cylinder"),
  cylinderInsulationType: text("cylinder_insulation_type"),
  cylinderInsulationThickness: integer("cylinder_insulation_thickness"),
  cylinderThermostat: boolean("cylinder_thermostat"),
  main_dwelling_ground_floor_area: real("main_dwelling_ground_floor_area"),
  number_of_windows: integer("number_of_windows"),
  windows_area: real("windows_area"),
});

// Against each energy assessment, there are documents that are collected on site
// Define the table

export const DocumentType: [string, ...string[]] = [
  "EPR",
  "Condition Report",
  "Evidence Report",
  "Summary Information",
  "Floor Plan",
  "Scenario Draft EPC",
  "Scenario Site Notes",
];

export const DocumentTypeEnum = pgEnum("document_type", DocumentType);

export const energyAssessmentScenarios = pgTable(
  "energy_assessment_scenarios",
  {
    id: bigserial("id", { mode: "bigint" }).primaryKey(),
    scenarioName: text("scenario_name").notNull(),
    // Foreign key to associate scenario with energy assessments
    energyAssessmentId: bigint("energy_assessment_id", {
      mode: "bigint",
    })
      .notNull()
      .references(() => energyAssessment.id),
  }
);

export const energyAssessmentDocuments = pgTable(
  "energy_assessment_documents",
  {
    id: bigserial("id", { mode: "bigint" }).primaryKey(),
    uprn: bigint("uprn", { mode: "bigint" }).notNull(),
    energyAssessmentId: bigint("energy_assessment_id", {
      mode: "bigint",
    })
      .notNull()
      .references(() => energyAssessment.id),
    documentType: DocumentTypeEnum("document_type").notNull(),
    documentLocation: text("document_location").notNull(),
    // The uploaded at date is automatically created when a new record is added
    uploadedAt: timestamp("uploaded_at", {
      precision: 6,
      withTimezone: true,
    })
      .notNull()
      .defaultNow(),
    scenarioId: bigint("scenario_id", {
      mode: "bigint",
    }).references(() => energyAssessmentScenarios.id),
  }
);

// Types for the new table
export type EnergyAssessment = InferModel<typeof energyAssessment, "select">;
export type EnergyAssessmentScenario = InferModel<
  typeof energyAssessmentScenarios,
  "select"
>;
export type EnergyAssessmentDocument = InferModel<
  typeof energyAssessmentDocuments,
  "select"
>;

// We define a type for the energyassessment docments that embeds a scenario in
// the document
export type EnergyAssessmentDocumentWithScenario = EnergyAssessmentDocument & {
  scenario: EnergyAssessmentScenario | null;
};
