import {
  bigserial,
  text,
  timestamp,
  pgTable,
  real,
  doublePrecision,
  pgEnum,
  integer,
  boolean,
  smallint,
  bigint,
  uniqueIndex,
  index,
  jsonb,
} from "drizzle-orm/pg-core";
import { portfolio, PortfolioStatus } from "./portfolio";
import { InferModel } from "drizzle-orm";
import { materialTypeEnum } from "./materials";
import { sql } from "drizzle-orm";
import { uploadedFiles } from "./uploaded_files";

// This is a placeholder for the property schema
export interface PropertyMeta {
  id: number;
  uprn: number;
  address: string;
  postcode: string;
  hasPreConditionReport: boolean;
  hasRecommendations: boolean;
  createdAt: string;
  propertyType: string;
  builtForm: string;
  localAuthority: string;
  constituency: string;
  numberOfRooms: number;
  yearBuilt: number;
  tenure: string;
  currentEpcRating: string;
  currentSapPoints: number;
  originalSapPoints: number | null;
  // Provenance of the displayed "current" figures (see ADR-0002 / provenance.ts).
  // "estimated" = no certificate (predicted); "remodelled" = lodged but
  // rebaselined; "none" = lodged == effective, or legacy.
  provenanceSignal: "estimated" | "remodelled" | "none";
  // Lodged (gov-register) headline, only when a real certificate exists; feeds
  // the "Lodged EPC" badge alongside the effective "current".
  lodgedEpcRating: string | null;
  lodgedSapPoints: number | null;
  updatedAt: string;
  currentValuation: number | null;
  detailsEpc: {
    currentEnergyDemand: number | null;
    co2Emissions: number | null;
    estimated: boolean;
  };
}

export type Rating = "Very good" | "Good" | "Poor" | "Very poor" | "N/A";

export interface Feature {
  feature: string;
  description: string;
  rating: Rating;
}

export interface GeneralFeature {
  feature: string;
  description: string | number;
}

export interface NonInstrusiveFeature {
  title: string;
  note: string;
}

export interface ConditionReportData {
  id: number;
  lastUpdated: string;
  fullAddress: string;
  postcode: string;
  currentEpcRating: string;
  inConservationArea: "Yes" | "No" | "Unknown";
  propertyType: string;
  builtForm: string;
  totalFloorArea: number;
  tenure: string;
  retrofitFeatures: Feature[];
  generalFeatures: GeneralFeature[];
  heatingDemand: GeneralFeature[];
  yearBuilt: string;
}

export const PropertyCreationStatus: [string, ...string[]] = [
  "LOADING",
  "READY",
  "ERROR",
];

export const Epc: [string, ...string[]] = ["A", "B", "C", "D", "E", "F", "G"];

export const propertyCreationStatusEnum = pgEnum(
  "creation_status",
  PropertyCreationStatus,
);
export const epcEnum = pgEnum("epc", Epc);
export const propertyStatusEnum = pgEnum("status", PortfolioStatus);

export const property = pgTable(
  "property",
  {
    id: bigserial("id", { mode: "bigint" }).primaryKey(),

    portfolioId: bigint("portfolio_id", { mode: "bigint" })
      .notNull()
      .references(() => portfolio.id),

    creationStatus: propertyCreationStatusEnum("creation_status").notNull(),
    uprn: bigint("uprn", { mode: "bigint" }),
    certificateNumber: text("certificate_number"),

    landlordPropertyId: text("landlord_property_id"),
    buildingReferenceNumber: bigint("building_reference_number", {
      mode: "bigint",
    }),

    status: propertyStatusEnum("status"),
    address: text("address"),
    postcode: text("postcode"),
    userInputtedAddress: text("user_inputted_address"),
    userInputtedPostcode: text("user_inputted_postcode"),
    lexiscore: real("lexiscore"),

    hasPreConditionReport: boolean("has_pre_condition_report"),
    hasRecommendations: boolean("has_recommendations"),

    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),

    propertyType: text("property_type"),
    builtForm: text("built_form"),
    localAuthority: text("local_authority"),
    constituency: text("constituency"),

    numberOfRooms: integer("number_of_rooms"),
    yearBuilt: text("year_built"),
    tenure: text("tenure"),

    currentEpcRating: epcEnum("current_epc_rating"),
    currentSapPoints: real("current_sap_points"),
    currentValuation: real("current_valuation"),

    // When we have already installed measures, we will adjust the SAP points to reflect this. We keep a record of
    // 1) The number of points we've adjusted by
    // 2) a flag to indicate whether the SAP points have been adjusted, for easily filtering
    installedMeasuresSapPointAdjustment: real(
      "installed_measures_sap_point_adjustment",
    ),
    isSapPointsAdjustedForInstalledMeasures: boolean(
      "is_sap_points_adjusted_for_installed_measures",
    ).default(false),
    // To be deprecated
    originalSapPoints: real("original_sap_points"),
    // lodged data
    lodgedSapPoints: real("lodged_sap_points"),
    lodgedEpcRating: epcEnum("lodged_epc_rating"),

    // Soft-delete marker. Hard deletes on `property` are expensive (cascading
    // child rows, indexes), so callers flag a row here and reads filter it out;
    // a separate cron job performs the physical delete later. NOT NULL so reads
    // never deal with a tri-state — every live row is explicitly `false`.
    markedForDeletion: boolean("marked_for_deletion").notNull().default(false),
  },
  (table) => [
    uniqueIndex("uq_property_portfolio_uprn")
      .on(table.portfolioId, table.uprn)
      .where(sql`${table.uprn} IS NOT NULL`),
    // Plain `portfolio_id = ?` filters (reporting, portfolio list) — the
    // partial unique above only covers rows WHERE uprn IS NOT NULL, so the
    // planner can't use it for these.
    index("ix_property_portfolio").on(table.portfolioId),
  ],
);

export const FeatureRating: [string, ...string[]] = [
  "Very good",
  "Good",
  "Poor",
  "Very poor",
  "N/A",
];

export const FeatureRatingNumeric: [number, ...number[]] = [5, 4, 3, 2, 1];

export const propertyDetailsEpc = pgTable(
  "property_details_epc",
  {
    id: bigserial("id", { mode: "bigint" }).primaryKey(),
    propertyId: bigint("property_id", { mode: "bigint" })
      .notNull()
      .references(() => property.id),
    portfolioId: bigint("portfolio_id", { mode: "bigint" })
      .notNull()
      .references(() => portfolio.id),
    fullAddress: text("full_address"),
    // Date the EPC was lodged
    lodgementDate: timestamp("lodgement_date"),
    isExpired: boolean("is_expired"),
    totalFloorArea: real("total_floor_area"),
    walls: text("walls"),
    wallsRating: smallint("walls_rating"),
    roof: text("roof"),
    roofRating: smallint("roof_rating"),
    floor: text("floor"),
    floorRating: smallint("floor_rating"),
    windows: text("windows"),
    windowsRating: smallint("windows_rating"),
    heating: text("heating"),
    heatingRating: smallint("heating_rating"),
    heatingControls: text("heating_controls"),
    heatingControlsRating: smallint("heating_controls_rating"),
    hotWater: text("hot_water"),
    hotWaterRating: smallint("hot_water_rating"),
    lighting: text("lighting"),
    lightingRating: smallint("lighting_rating"),
    mainfuel: text("mainfuel"),
    ventilation: text("ventilation"),
    solarPv: real("solar_pv"),
    solarHotWater: boolean("solar_hot_water"),
    windTurbine: smallint("wind_turbine"),
    floorHeight: real("floor_height"),
    numberHeatedRooms: integer("number_heated_rooms"),
    heatLossCorridor: boolean("heat_loss_corridor"),
    unheatedCorridorLength: real("unheated_corridor_length"),
    numberOpenFireplaces: integer("number_of_open_fireplaces"),
    numberExtensions: integer("number_of_extensions"),
    numberStoreys: integer("number_of_storeys"),
    mainsGas: boolean("mains_gas"),
    energyTariff: text("energy_tariff"),
    // This is heat demand
    primaryEnergyConsumption: real("primary_energy_consumption"),
    co2Emissions: real("co2_emissions"),
    // Bad naming but currentEnergyDemand is the current kwh consumption - needs to be renamed
    currentEnergyDemand: real("current_energy_demand"),
    currentEnergyDemandHeatingHotwater: real(
      "current_energy_demand_heating_hotwater",
    ),
    estimated: boolean("estimated").default(false),
    // We indicate if the property has an overwritten SAP 05 EPC. I.e. there is a valid EPC, however it's a SAP 05
    // EPC which isn't particularly useful. This value is defaulted to False
    sap05Overwritten: boolean("sap_05_overwritten").default(false),
    // When we've overwritten a SAP 05 EPC, we store the SAP 05 score and rating here for reference
    sap05Score: real("sap_05_score"),
    sap05EpcRating: epcEnum("sap_05_epc_rating"),
    // Include current estimates for energy bills, across the different types of energy
    // These predictions are based on the EPC predicted consumptions + current energy prices
    heatingEnergyCostCurrent: real("heating_cost_current"),
    hotWaterEnergyCostCurrent: real("hot_water_cost_current"),
    lightingEnergyCostCurrent: real("lighting_cost_current"),
    appliancesEnergyCostCurrent: real("appliances_cost_current"),
    gasStandingCharge: real("gas_standing_charge"),
    electricityStandingCharge: real("electricity_standing_charge"),

    // When we have already installed measures, we will adjust the carbon, bills, kwh, heat demandto reflect this. We keep a record of
    // 1) The adjustments
    // 2) original values
    // 3) a flag to indicate whether the values have been adjusted, for easily filtering

    // original values - we don't need bills because we don't actually adjust any of the originals we just subtract adjustments from current values
    // TODO - deprecate
    originalCo2Emissions: real("original_co2_emissions"),
    originalPrimaryEnergyConsumption: real(
      "original_primary_energy_consumption",
    ),
    originalCurrentEnergyDemand: real("original_current_energy_demand"),
    originalCurrentEnergyDemandHeatingHotwater: real(
      "original_current_energy_demand_heating_hotwater",
    ),

    // adjustment quantities - TODO: deprecate
    installedMeasuresCo2Adjustment: real("installed_measures_co2_adjustment"),
    installedMeasuresEnergyDemandAdjustment: real(
      "installed_measures_energy_demand_adjustment",
    ),
    installedMeasuresTotalEnergyBillAdjustment: real(
      "installed_measures_total_energy_bill_adjustment",
    ),
    installedMeasuresHeatDemandAdjustment: real(
      "installed_measures_heat_demand_adjustment",
    ),
    isEpcAdjustedForInstalledMeasures: boolean(
      "is_epc_adjusted_for_installed_measures",
    ).default(false),

    // Lodged values
    lodgedCo2Emissions: real("lodged_co2_emissions"),
    lodgedHeatDemand: real("lodged_heat_demand"),
    hasBeenRemodelled: boolean("has_been_remodelled").default(false),
    // additional fields
    environment_impact_current: real("environment_impact_current"),
  },
  (table) => [
    uniqueIndex("uq_property_details_epc_property_portfolio").on(
      table.propertyId,
      table.portfolioId,
    ),
  ],
);

export const propertyDetailsSpatial = pgTable(
  "property_details_spatial",
  {
    id: bigserial("id", { mode: "bigint" }).primaryKey(),
    uprn: bigint("uprn", { mode: "bigint" }),
    xCoordinate: real("x_coordinate"),
    yCoordinate: real("y_coordinate"),
    latitude: real("latitude"),
    longitude: real("longitude"),
    conservationStatus: boolean("conservation_status"),
    isListedBuilding: boolean("is_listed_building"),
    isHeritageBuilding: boolean("is_heritage_building"),
  },
  (table) => [uniqueIndex("uq_property_details_spatial_uprn").on(table.uprn)],
);

export const propertyDetailsMeter = pgTable("property_details_meter", {
  id: bigserial("id", { mode: "bigint" }).primaryKey(),
  uprn: bigint("uprn", { mode: "bigint" }),
  energySupplier: text("energy_supplier"),
  gasSupplier: text("gas_supplier"),
  meterReadingTotal: real("meter_reading_total"),
  meterReadingElectricity: real("meter_reading_electricity"),
  meterReadingGas: real("meter_reading_gas"),
});

export const propertyTargets = pgTable("property_targets", {
  id: bigserial("id", { mode: "bigint" }).primaryKey(),
  propertyId: bigint("property_id", { mode: "bigint" })
    .notNull()
    .references(() => property.id),
  portfolioId: bigint("portfolio_id", { mode: "bigint" })
    .notNull()
    .references(() => portfolio.id),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  epc: epcEnum("epc"),
  heatDemand: text("heat_demand"),
});

// This is the model for the results of non-invasive surveys, associated with a property.
// We store the data against a uprn. Each row will contain a metadata about the survey itself
export const nonInstrusiveSurvey = pgTable("non_intrusive_survey", {
  id: bigserial("id", { mode: "bigint" }).primaryKey(),
  uprn: bigint("uprn", { mode: "bigint" }).notNull(),
  surveyDate: timestamp("survey_date").notNull(),
  surveyor: text("surveyor").notNull(),
});

// This model contains the actual notes that were taken down during the non-invasive survey
export const nonIntrusiveSurveyNotes = pgTable("non_intrusive_survey_notes", {
  id: bigserial("id", { mode: "bigint" }).primaryKey(),
  surveyId: bigint("survey_id", { mode: "bigint" })
    .notNull()
    .references(() => nonInstrusiveSurvey.id),
  title: text("title").notNull(),
  note: text("note").notNull(),
});

export type Property = InferModel<typeof property, "select">;
export type PropertyDetailsEpc = InferModel<
  typeof propertyDetailsEpc,
  "select"
>;
export type PropertyDetailsSpatial = InferModel<
  typeof propertyDetailsSpatial,
  "select"
>;

// This type is used for the getProperties function in src/app/portfolio/[slug]/utils.ts
export interface PropertyToRecommendation {
  estimatedCost?: number | null;
  sapPoints?: number | null;
}

export interface PropertyWithRelations extends Record<string, unknown> {
  id: number | string | bigint;
  portfolioId: number | string | bigint;
  address: string | null;
  postcode: string | null;
  status: string | null;
  creationStatus: string | null;
  currentEpcRating: string | null;
  currentSapPoints: number | null;
  targetEpc: string | null;
  planId: number | null;
  fundingScheme: string | null;
  totalRecommendationSapPoints: number | null;
  totalRecommendationCost: number | null;
  // New fields
  landlordPropertyId: string | null;
  originalSapPoints: number | null;
  // Effective "current" (ADR-0002) is in currentEpcRating/currentSapPoints; these
  // carry the separate lodged certificate + the default plan's post-retrofit
  // rating + the provenance signal that drives the "Predicted" pill.
  lodgedEpcRating: string | null;
  postEpcRating: string | null;
  postSapPoints: number | null;
  provenanceSignal: "estimated" | "remodelled" | "none";
  epcLodgementDate: string | null;
  epcIsExpired: boolean | null;
  // Optional columns (hidden by default)
  propertyType: string | null;
  builtForm: string | null;
  yearBuilt: string | null;
  totalFloorArea: number | null;
  co2Emissions: number | null;
  mainfuel: string | null;
  wallType: string | null;
  roofType: string | null;
  heatingSystem: string | null;
  lexiscore: number | null;
  // Portfolio Tags on this property (ADR-0013), name-ordered; [] when untagged.
  tags: PropertyTag[];
}

/** A Tag as it appears on a property row (id serialised to a string). */
export interface PropertyTag {
  id: string;
  name: string;
  colour: string;
}

export type NonIntrusiveSurveyNotes = InferModel<
  typeof nonIntrusiveSurveyNotes,
  "select"
>;
export type NonInstrusiveSurvey = InferModel<
  typeof nonInstrusiveSurvey,
  "select"
>;
export interface NonIntrusiveSurveyData {
  uprn: bigint;
  surveyDate: Date;
  surveyor: string;
  notes: { title: string; note: string }[];
}

// ─── Enums ────────────────────────────────────────────────────────────────────

export const energyElementTypeEnum = pgEnum("energy_element_type", [
  "roof", "wall", "floor", "main_heating", "window",
  "lighting", "hot_water", "secondary_heating", "main_heating_controls",
]);

// ─── epc_property ─────────────────────────────────────────────────────────────

export const epcProperty = pgTable(
  "epc_property",
  {
    id: bigserial("id", { mode: "bigint" }).primaryKey(),
    propertyId: bigint("property_id", { mode: "bigint" })
      // .notNull()
      .references(() => property.id),
    portfolioId: bigint("portfolio_id", { mode: "bigint" })
      // .notNull()
      .references(() => portfolio.id),
    uploadedFileId: bigint("uploaded_file_id", { mode: "bigint" })
      .unique()
      .references(() => uploadedFiles.id),

    // Provenance of this EPC picture: "lodged" (a real public/landlord EPC) or
    // "predicted" (EPC Prediction gap-fill, ADR-0031 in the Model repo). A
    // property may hold one of each, so reads filter on it. Defaults to "lodged"
    // — every existing row is a real lodged EPC.
    source: text("source").notNull().default("lodged"),

    // Identity / admin
    uprn:                       bigint("uprn", { mode: "bigint" }),
    uprnSource:                 text("uprn_source"),
    reportReference:            text("report_reference"),
    reportType:                 text("report_type"),
    assessmentType:             text("assessment_type"),
    sapVersion:                 real("sap_version"),
    schemaType:                 text("schema_type"),
    schemaVersionsOriginal:     text("schema_versions_original"),
    status:                     text("status"),
    calculationSoftwareVersion: text("calculation_software_version"),

    // Address
    addressLine1: text("address_line_1").notNull(),
    addressLine2: text("address_line_2"),
    postTown:     text("post_town").notNull(),
    postcode:     text("postcode").notNull(),
    regionCode:   text("region_code"),
    countryCode:  text("country_code"),
    languageCode: text("language_code"),

    // Property description
    dwellingType:     text("dwelling_type").notNull(),
    propertyType:     text("property_type"),
    builtForm:        text("built_form"),
    tenure:           text("tenure").notNull(),
    transactionType:  text("transaction_type").notNull(),

    // Dates
    inspectionDate:   timestamp("inspection_date").notNull(),
    completionDate:   timestamp("completion_date"),
    registrationDate: timestamp("registration_date"),

    // Measurements
    totalFloorAreaM2: real("total_floor_area_m2").notNull(),
    measurementType:  integer("measurement_type"),

    // Flags
    solarWaterHeating:              boolean("solar_water_heating").notNull(),
    hasHotWaterCylinder:            boolean("has_hot_water_cylinder").notNull(),
    hasFixedAirConditioning:        boolean("has_fixed_air_conditioning").notNull(),
    hasConservatory:                boolean("has_conservatory"),
    hasHeatedSeparateConservatory:  boolean("has_heated_separate_conservatory"),
    conservatoryType:               integer("conservatory_type"),
    conservatoryFloorAreaM2:        doublePrecision("conservatory_floor_area_m2"),
    conservatoryGlazedPerimeterM:   doublePrecision("conservatory_glazed_perimeter_m"),
    conservatoryDoubleGlazed:       boolean("conservatory_double_glazed"),
    conservatoryThermallySeparated: boolean("conservatory_thermally_separated"),
    conservatoryRoomHeightStoreys:  doublePrecision("conservatory_room_height_storeys"),

    // Counts
    doorCount:                            integer("door_count").notNull(),
    wetRoomsCount:                        integer("wet_rooms_count").notNull(),
    extensionsCount:                      integer("extensions_count").notNull(),
    heatedRoomsCount:                     integer("heated_rooms_count").notNull(),
    openChimneysCount:                    integer("open_chimneys_count").notNull(),
    habitableRoomsCount:                  integer("habitable_rooms_count").notNull(),
    insulatedDoorCount:                   integer("insulated_door_count").notNull(),
    cflFixedLightingBulbsCount:           integer("cfl_fixed_lighting_bulbs_count").notNull(),
    ledFixedLightingBulbsCount:           integer("led_fixed_lighting_bulbs_count").notNull(),
    incandescentFixedLightingBulbsCount:  integer("incandescent_fixed_lighting_bulbs_count").notNull(),
    blockedChimneysCount:                 integer("blocked_chimneys_count"),
    draughtproofedDoorCount:              integer("draughtproofed_door_count"),
    energyRatingAverage:                  integer("energy_rating_average"),
    lowEnergyFixedLightingBulbsCount:     integer("low_energy_fixed_lighting_bulbs_count"),
    fixedLightingOutletsCount:            integer("fixed_lighting_outlets_count"),
    lowEnergyFixedLightingOutletsCount:   integer("low_energy_fixed_lighting_outlets_count"),
    numberOfStoreys:                      integer("number_of_storeys"),
    anyUnheatedRooms:                     boolean("any_unheated_rooms"),

    // Misc
    hydro:                          boolean("hydro"),
    photovoltaicArray:              boolean("photovoltaic_array"),
    wasteWaterHeatRecovery:         text("waste_water_heat_recovery"),
    pressureTest:                   integer("pressure_test"),
    pressureTestCertificateNumber:  integer("pressure_test_certificate_number"),
    percentDraughtproofed:          integer("percent_draughtproofed"),
    insulatedDoorUValue:            real("insulated_door_u_value"),
    multipleGlazedProportion:       integer("multiple_glazed_proportion"),
    windowsTransmissionUValue:          real("windows_transmission_u_value"),
    windowsTransmissionDataSource:      integer("windows_transmission_data_source"),
    windowsTransmissionSolarTransmittance: real("windows_transmission_solar_transmittance"),

    // Energy source
    energyGasConnectionAvailable:         boolean("energy_gas_connection_available").notNull(),
    energyMeterType:                      text("energy_meter_type").notNull(),
    energyPvBatteryCount:                 integer("energy_pv_battery_count").notNull(),
    energyWindTurbinesCount:              integer("energy_wind_turbines_count").notNull(),
    energyGasSmartMeterPresent:           boolean("energy_gas_smart_meter_present").notNull(),
    energyIsDwellingExportCapable:        boolean("energy_is_dwelling_export_capable").notNull(),
    energyWindTurbinesTerrainType:        text("energy_wind_turbines_terrain_type").notNull(),
    energyElectricitySmartMeterPresent:   boolean("energy_electricity_smart_meter_present").notNull(),
    energyPvConnection:                   jsonb("energy_pv_connection"),
    energyPvPercentRoofArea:              integer("energy_pv_percent_roof_area"),
    energyPvBatteryCapacity:              real("energy_pv_battery_capacity"),
    energyWindTurbineHubHeight:           real("energy_wind_turbine_hub_height"),
    energyWindTurbineRotorDiameter:       real("energy_wind_turbine_rotor_diameter"),
    energyPvDiverterPresent:              boolean("energy_pv_diverter_present").notNull().default(false),

    // Heating config
    heatingCylinderSize:                  jsonb("heating_cylinder_size"),
    heatingWaterHeatingCode:              integer("heating_water_heating_code"),
    heatingWaterHeatingFuel:              integer("heating_water_heating_fuel"),
    heatingImmersionHeatingType:          jsonb("heating_immersion_heating_type"),
    heatingCylinderInsulationType:        jsonb("heating_cylinder_insulation_type"),
    heatingCylinderThermostat:            text("heating_cylinder_thermostat"),
    heatingSecondaryFuelType:             integer("heating_secondary_fuel_type"),
    heatingSecondaryHeatingType:          jsonb("heating_secondary_heating_type"),
    heatingCylinderInsulationThicknessMm: integer("heating_cylinder_insulation_thickness_mm"),
    heatingCylinderVolumeMeasuredL:       integer("heating_cylinder_volume_measured_l"), // litres, nullable
    heatingWwhrsIndexNumber1:             integer("heating_wwhrs_index_number_1"),
    heatingWwhrsIndexNumber2:             integer("heating_wwhrs_index_number_2"),
    heatingShowerOutletType:              jsonb("heating_shower_outlet_type"),
    heatingShowerWwhrs:                   integer("heating_shower_wwhrs"),

    // Ventilation
    ventilationType:                    text("ventilation_type"),
    ventilationDraughtLobby:            boolean("ventilation_draught_lobby"),
    ventilationPressureTest:            text("ventilation_pressure_test"),
    ventilationOpenFluesCount:          integer("ventilation_open_flues_count"),
    ventilationClosedFluesCount:        integer("ventilation_closed_flues_count"),
    ventilationBoilerFluesCount:        integer("ventilation_boiler_flues_count"),
    ventilationOtherFluesCount:         integer("ventilation_other_flues_count"),
    ventilationExtractFansCount:        integer("ventilation_extract_fans_count"),
    ventilationPassiveVentsCount:       integer("ventilation_passive_vents_count"),
    ventilationFluelessGasFiresCount:   integer("ventilation_flueless_gas_fires_count"),
    ventilationInPcdfDatabase:          boolean("ventilation_in_pcdf_database"),
    mechanicalVentilation:              integer("mechanical_ventilation"),
    mechanicalVentDuctType:             integer("mechanical_vent_duct_type"),
    mechanicalVentDuctPlacement:        integer("mechanical_vent_duct_placement"),
    mechanicalVentDuctInsulation:       integer("mechanical_vent_duct_insulation"),
    mechanicalVentilationIndexNumber:   integer("mechanical_ventilation_index_number"),
    mechanicalVentMeasuredInstallation: text("mechanical_vent_measured_installation"),
    mechanicalVentDuctInsulationLevel:  integer("mechanical_vent_duct_insulation_level"),

    // Addendum flags
    addendumStoneWalls:  boolean("addendum_stone_walls"),
    addendumSystemBuild: boolean("addendum_system_build"),
    addendumNumbers:     jsonb("addendum_numbers"),

    // Heating counts
    heatingNumberBaths:         integer("heating_number_baths"),
    heatingNumberBathsWwhrs:    integer("heating_number_baths_wwhrs"),
    heatingElectricShowerCount: integer("heating_electric_shower_count"),
    heatingMixerShowerCount:    integer("heating_mixer_shower_count"),

    // Ventilation detail
    ventilationPresent:                    boolean("ventilation_present").notNull().default(false),
    ventilationShelteredSides:             integer("ventilation_sheltered_sides"),
    ventilationHasSuspendedTimberFloor:    boolean("ventilation_has_suspended_timber_floor"),
    ventilationSuspendedTimberFloorSealed: boolean("ventilation_suspended_timber_floor_sealed"),
    ventilationHasDraughtLobby:            boolean("ventilation_has_draught_lobby"),
    ventilationAirPermeabilityAp4M3HM2:    real("ventilation_air_permeability_ap4_m3_h_m2"),
    ventilationAirPermeabilityAp50M3HM2:   real("ventilation_air_permeability_ap50_m3_h_m2"), // m³/h·m² @50Pa, nullable
    ventilationMechanicalVentilationKind:  text("ventilation_mechanical_ventilation_kind"),
  },
  (table) => [
    // A property may now hold one "lodged" and one "predicted" EPC row per
    // portfolio (ADR-0031), so `source` is part of the uniqueness key.
    uniqueIndex("uq_epc_property_property_portfolio").on(
      table.propertyId,
      table.portfolioId,
      table.source,
    ),
    // Every lodged/predicted read filters on (property_id, source).
    index("ix_epc_property_property_source").on(
      table.propertyId,
      table.source,
    ),
  ],
);

// ─── epc_property_energy_performance ─────────────────────────────────────────

export const epcPropertyEnergyPerformance = pgTable(
  "epc_property_energy_performance",
  {
    id:             bigserial("id", { mode: "bigint" }).primaryKey(),
    epcPropertyId:  bigint("epc_property_id", { mode: "bigint" })
      .notNull()
      .unique()
      .references(() => epcProperty.id),

    // Current
    energyRatingCurrent:              integer("energy_rating_current"),
    energyConsumptionCurrent:         integer("energy_consumption_current"),
    environmentalImpactCurrent:       integer("environmental_impact_current"),
    heatingCostCurrent:               real("heating_cost_current"),
    lightingCostCurrent:              real("lighting_cost_current"),
    hotWaterCostCurrent:              real("hot_water_cost_current"),
    co2EmissionsCurrent:              real("co2_emissions_current"),
    co2EmissionsCurrentPerFloorArea:  integer("co2_emissions_current_per_floor_area"),
    currentEnergyEfficiencyBand:      text("current_energy_efficiency_band"),

    // Potential
    energyRatingPotential:            real("energy_rating_potential"),
    energyConsumptionPotential:       integer("energy_consumption_potential"),
    environmentalImpactPotential:     integer("environmental_impact_potential"),
    heatingCostPotential:             real("heating_cost_potential"),
    lightingCostPotential:            real("lighting_cost_potential"),
    hotWaterCostPotential:            real("hot_water_cost_potential"),
    co2EmissionsPotential:            real("co2_emissions_potential"),
    potentialEnergyEfficiencyBand:    text("potential_energy_efficiency_band"),
  },
);

// ─── epc_flat_details ─────────────────────────────────────────────────────────

export const epcFlatDetails = pgTable(
  "epc_flat_details",
  {
    id:             bigserial("id", { mode: "bigint" }).primaryKey(),
    epcPropertyId:  bigint("epc_property_id", { mode: "bigint" })
      .notNull()
      .unique()
      .references(() => epcProperty.id),

    level:                    integer("level").notNull(),
    topStorey:                text("top_storey").notNull(),
    flatLocation:             integer("flat_location").notNull(),
    heatLossCorridor:         integer("heat_loss_corridor").notNull(),
    storeyCount:              integer("storey_count"),
    unheatedCorridorLengthM:  integer("unheated_corridor_length_m"),
  },
);

// ─── epc_main_heating_detail ──────────────────────────────────────────────────

export const epcMainHeatingDetail = pgTable(
  "epc_main_heating_detail",
  {
    id:             bigserial("id", { mode: "bigint" }).primaryKey(),
    epcPropertyId:  bigint("epc_property_id", { mode: "bigint" })
      .notNull()
      .references(() => epcProperty.id),

    hasFghrs:                   boolean("has_fghrs").notNull(),
    mainFuelType:               jsonb("main_fuel_type").notNull(),
    heatEmitterType:            jsonb("heat_emitter_type").notNull(),
    emitterTemperature:         jsonb("emitter_temperature").notNull(),
    mainHeatingControl:         jsonb("main_heating_control").notNull(),
    fanFluePresent:             boolean("fan_flue_present"),
    boilerFlueType:             integer("boiler_flue_type"),
    boilerIgnitionType:         integer("boiler_ignition_type"),
    centralHeatingPumpAge:      integer("central_heating_pump_age"),
    centralHeatingPumpAgeStr:   text("central_heating_pump_age_str"),
    mainHeatingIndexNumber:     integer("main_heating_index_number"),
    sapMainHeatingCode:         integer("sap_main_heating_code"),
    mainHeatingNumber:          integer("main_heating_number"),
    mainHeatingCategory:        integer("main_heating_category"),
    mainHeatingFraction:        integer("main_heating_fraction"),
    mainHeatingDataSource:      integer("main_heating_data_source"),
    condensing:                 boolean("condensing"),
    weatherCompensator:         boolean("weather_compensator"),
    communityHeatingBoilerFuelType: integer("community_heating_boiler_fuel_type"), // SAP fuel code, nullable
    communityHeatingChpFraction:    real("community_heating_chp_fraction"),        // 0..1 fraction, nullable
  },
  (table) => [
    // The portfolio list resolves Main Fuel per row via a correlated subquery on
    // epc_property_id (mainfuelSql, ADR-0012); without this index that seq-scans
    // the table for every property in the count/export path.
    index("ix_epc_main_heating_detail_epc_property").on(table.epcPropertyId),
  ],
);

// ─── epc_building_part ────────────────────────────────────────────────────────

export const epcBuildingPart = pgTable(
  "epc_building_part",
  {
    id:             bigserial("id", { mode: "bigint" }).primaryKey(),
    epcPropertyId:  bigint("epc_property_id", { mode: "bigint" })
      .notNull()
      .references(() => epcProperty.id),

    identifier:           text("identifier").notNull(),
    constructionAgeBand:  text("construction_age_band").notNull(),

    // Wall
    wallConstruction:       jsonb("wall_construction").notNull(),
    wallInsulationType:     jsonb("wall_insulation_type").notNull(),
    wallThicknessMeasured:  boolean("wall_thickness_measured").notNull(),
    partyWallConstruction:  jsonb("party_wall_construction"),
    buildingPartNumber:     integer("building_part_number"),
    wallDryLined:           boolean("wall_dry_lined"),
    wallThicknessMm:        integer("wall_thickness_mm"),
    wallInsulationThickness: jsonb("wall_insulation_thickness"),
    // Union[int,str] SAP code — JSONB to preserve int-vs-str on round-trip,
    // matching wall_insulation_thickness / roof_insulation_thickness.
    wallInsulationThermalConductivity: jsonb("wall_insulation_thermal_conductivity"), // nullable
    // age band source

    // Floor
    floorHeatLoss:            integer("floor_heat_loss"),
    floorInsulationThickness: text("floor_insulation_thickness"),
    flatRoofInsulationThickness: jsonb("flat_roof_insulation_thickness"),
    floorType:                text("floor_type"),
    floorConstructionType:    text("floor_construction_type"),
    floorInsulationTypeStr:   text("floor_insulation_type_str"),
    floorUValueKnown:         boolean("floor_u_value_known"),

    // Roof
    roofConstruction:         integer("roof_construction"),
    roofInsulationLocation:   jsonb("roof_insulation_location"),
    roofInsulationThickness:  jsonb("roof_insulation_thickness"),

    roofConstructionType: text("roof_construction_type"),
    curtainWallAge:       text("curtain_wall_age"),

    // Room in roof (inlined)
    roomInRoofFloorArea:            real("room_in_roof_floor_area"),
    roomInRoofConstructionAgeBand:  text("room_in_roof_construction_age_band"),

    // Alternative wall 1 (inlined)
    altWall1Area:               real("alt_wall_1_area"),
    altWall1DryLined:           text("alt_wall_1_dry_lined"),
    altWall1Construction:       integer("alt_wall_1_construction"),
    altWall1InsulationType:     integer("alt_wall_1_insulation_type"),
    altWall1ThicknessMeasured:  text("alt_wall_1_thickness_measured"),
    altWall1InsulationThickness: text("alt_wall_1_insulation_thickness"),
    altWall1IsSheltered:        boolean("alt_wall_1_is_sheltered"), // nullable (null when no alt wall)

    // Alternative wall 2 (inlined)
    altWall2Area:               real("alt_wall_2_area"),
    altWall2DryLined:           text("alt_wall_2_dry_lined"),
    altWall2Construction:       integer("alt_wall_2_construction"),
    altWall2InsulationType:     integer("alt_wall_2_insulation_type"),
    altWall2ThicknessMeasured:  text("alt_wall_2_thickness_measured"),
    altWall2InsulationThickness: text("alt_wall_2_insulation_thickness"),
    altWall2IsSheltered:        boolean("alt_wall_2_is_sheltered"), // nullable (null when no alt wall)
  },
  (table) => [
    // Per-EPC part lookups: the reporting age-band subquery and the
    // condition-report / descriptor resolvers all filter on epc_property_id.
    index("ix_epc_building_part_epc_property").on(table.epcPropertyId),
  ],
);

// ─── epc_floor_dimension ──────────────────────────────────────────────────────

export const epcFloorDimension = pgTable(
  "epc_floor_dimension",
  {
    id:               bigserial("id", { mode: "bigint" }).primaryKey(),
    epcBuildingPartId: bigint("epc_building_part_id", { mode: "bigint" })
      .notNull()
      .references(() => epcBuildingPart.id),

    floor:                integer("floor"),
    roomHeightM:          real("room_height_m").notNull(),
    totalFloorAreaM2:     real("total_floor_area_m2").notNull(),
    partyWallLengthM:     real("party_wall_length_m").notNull(),
    heatLossPerimeterM:   real("heat_loss_perimeter_m").notNull(),
    floorInsulation:      integer("floor_insulation"),
    floorConstruction:    integer("floor_construction"),
    isExposedFloor:              boolean("is_exposed_floor").notNull().default(false),
    isAbovePartiallyHeatedSpace: boolean("is_above_partially_heated_space").notNull().default(false),
  },
  (table) => [
    // Condition-report floor-height/storey lookups fetch all dimensions for
    // one building part.
    index("ix_epc_floor_dimension_building_part").on(table.epcBuildingPartId),
  ],
);

// ─── epc_window ───────────────────────────────────────────────────────────────

export const epcWindow = pgTable(
  "epc_window",
  {
    id:             bigserial("id", { mode: "bigint" }).primaryKey(),
    epcPropertyId:  bigint("epc_property_id", { mode: "bigint" })
      .notNull()
      .references(() => epcProperty.id),

    glazingGap:                 jsonb("glazing_gap").notNull(),
    orientation:                jsonb("orientation").notNull(),
    windowType:                 jsonb("window_type").notNull(),
    glazingType:                jsonb("glazing_type").notNull(),
    windowWidth:                real("window_width").notNull(), // add unit?
    windowHeight:               real("window_height").notNull(),  // add unit?
    draughtProofed:             jsonb("draught_proofed").notNull(),
    windowLocation:             jsonb("window_location").notNull(),
    windowWallType:             jsonb("window_wall_type").notNull(),
    permanentShuttersPresent:   jsonb("permanent_shutters_present").notNull(),
    frameMaterial:              text("frame_material"),
    frameFactor:                real("frame_factor"),
    permanentShuttersInsulated: text("permanent_shutters_insulated"),

    // Transmission details (inlined)
    transmissionUValue:               real("transmission_u_value"),
    transmissionDataSource:           jsonb("transmission_data_source"),
    transmissionSolarTransmittance:   real("transmission_solar_transmittance"),
  },
);

// ─── epc_photovoltaic_array ───────────────────────────────────────────────────

export const epcPhotovoltaicArray = pgTable(
  "epc_photovoltaic_array",
  {
    id:             bigserial("id", { mode: "bigint" }).primaryKey(),
    epcPropertyId:  bigint("epc_property_id", { mode: "bigint" })
      .notNull()
      .references(() => epcProperty.id),

    arrayIndex:   integer("array_index").notNull(), // preserves list order for round-trip equality
    peakPower:    real("peak_power").notNull(),      // kWp, float (e.g. 3.24)
    pitch:        integer("pitch").notNull(),        // SAP pitch CODE (categorical int, not degrees)
    overshading:  integer("overshading").notNull(),  // SAP overshading CODE (categorical int)
    orientation:  integer("orientation"),            // SAP orientation CODE, nullable
  },
);

// ─── epc_energy_element ───────────────────────────────────────────────────────

export const epcEnergyElement = pgTable(
  "epc_energy_element",
  {
    id:             bigserial("id", { mode: "bigint" }).primaryKey(),
    epcPropertyId:  bigint("epc_property_id", { mode: "bigint" })
      .notNull()
      .references(() => epcProperty.id),

    elementType:                  energyElementTypeEnum("element_type").notNull(),
    description:                  text("description").notNull(),
    energyEfficiencyRating:       integer("energy_efficiency_rating").notNull(),
    environmentalEfficiencyRating: integer("environmental_efficiency_rating").notNull(),
  },
  (table) => [
    // Condition-report fabric lookups fetch all elements for one EPC.
    index("ix_epc_energy_element_epc_property").on(table.epcPropertyId),
  ],
);

// ─── epc_renewable_heat_incentive ─────────────────────────────────────────────

export const epcRenewableHeatIncentive = pgTable(
  "epc_renewable_heat_incentive",
  {
    id: bigserial("id", { mode: "bigint" }).primaryKey(),
    epcPropertyId: bigint("epc_property_id", { mode: "bigint" })
      .notNull()
      .unique()
      .references(() => epcProperty.id),
    spaceHeatingKwh: real("space_heating_kwh").notNull(),
    waterHeatingKwh: real("water_heating_kwh").notNull(),
    impactOfLoftInsulationKwh: real("impact_of_loft_insulation_kwh"),
    impactOfCavityInsulationKwh: real("impact_of_cavity_insulation_kwh"),
    impactOfSolidWallInsulationKwh: real("impact_of_solid_wall_insulation_kwh"),
  },
);

// ─── property_baseline_performance ────────────────────────────────────────────

export const rebaselineReasonEnum = pgEnum("rebaseline_reason", [
  "none",
  "pre_sap10",
  "physical_state_changed",
  "both",
]);

export const propertyBaselinePerformance = pgTable(
  "property_baseline_performance",
  {
    id: bigserial("id", { mode: "bigint" }).primaryKey(),
    propertyId: bigint("property_id", { mode: "bigint" })
      .notNull()
      .unique()
      .references(() => property.id),

    // Lodged performance (from gov EPC register)
    lodgedSapScore: integer("lodged_sap_score"),
    lodgedEpcBand: epcEnum("lodged_epc_band"),
    lodgedCo2EmissionsTPerYr: real("lodged_co2_emissions_t_per_yr"),
    lodgedPrimaryEnergyIntensityKwhPerM2Yr: integer(
      "lodged_primary_energy_intensity_kwh_per_m2_yr",
    ),

    // Effective performance (what modelling scored against)
    effectiveSapScore: integer("effective_sap_score").notNull(),
    effectiveEpcBand: epcEnum("effective_epc_band").notNull(),
    effectiveCo2EmissionsTPerYr: real(
      "effective_co2_emissions_t_per_yr",
    ).notNull(),
    effectivePrimaryEnergyIntensityKwhPerM2Yr: integer(
      "effective_primary_energy_intensity_kwh_per_m2_yr",
    ).notNull(),

    rebaselineReason: rebaselineReasonEnum("rebaseline_reason").notNull(),

    // Interim energy demand (from EPC RHI data; superseded by bill block below once populated)
    spaceHeatingKwh: real("space_heating_kwh").notNull(),
    waterHeatingKwh: real("water_heating_kwh").notNull(),

    // Bill block — nullable until BillDerivation wiring lands
    fuelRatesPeriod: text("fuel_rates_period"),
    heatingKwh: real("heating_kwh"),
    heatingCostGbp: real("heating_cost_gbp"),
    hotWaterKwh: real("hot_water_kwh"),
    hotWaterCostGbp: real("hot_water_cost_gbp"),
    lightingKwh: real("lighting_kwh"),
    lightingCostGbp: real("lighting_cost_gbp"),
    appliancesKwh: real("appliances_kwh"),
    appliancesCostGbp: real("appliances_cost_gbp"),
    cookingKwh: real("cooking_kwh"),
    cookingCostGbp: real("cooking_cost_gbp"),
    pumpsFansKwh: real("pumps_fans_kwh"),
    pumpsFansCostGbp: real("pumps_fans_cost_gbp"),
    coolingKwh: real("cooling_kwh"),
    coolingCostGbp: real("cooling_cost_gbp"),
    standingChargesGbp: real("standing_charges_gbp"),
    segCreditGbp: real("seg_credit_gbp"),
    totalAnnualBillGbp: real("total_annual_bill_gbp"),
  },
);