import { property, epcEnum } from "./property";
import { goalEnum, portfolio } from "./portfolio";
import {
  bigserial,
  text,
  timestamp,
  pgTable,
  real,
  boolean,
  bigint,
  pgEnum,
  integer,
  index,
} from "drizzle-orm/pg-core";
import { Material, material } from "./materials";
import { InferModel, sql } from "drizzle-orm";
import { z } from "zod";

// For recommendations, measure types was initially defined as a string but we should convert this to an enum in the future

export const measureTypeEnum = pgEnum("measure_type", [
  // Heating systems
  "air_source_heat_pump",
  "boiler_upgrade",
  "high_heat_retention_storage_heaters",
  "secondary_heating",

  // Heating controls
  "roomstat_programmer_trvs",
  "time_temperature_zone_control",
  "cylinder_thermostat",

  // Insulation
  "cavity_wall_insulation",
  "extension_cavity_wall_insulation",
  "external_wall_insulation",
  "internal_wall_insulation",
  "loft_insulation",
  "flat_roof_insulation",
  "room_roof_insulation",
  "solid_floor_insulation",
  "suspended_floor_insulation",

  // Windows & doors
  "double_glazing",
  "secondary_glazing",
  "draught_proofing",

  // Ventilation
  "mechanical_ventilation",

  // Lighting
  "low_energy_lighting",

  // Renewables
  "solar_pv",

  // Other fabric / hot water
  "hot_water_tank_insulation",
  "sealing_open_fireplace",
]);

export const recommendation = pgTable(
  "recommendation",
  {
    id: bigserial("id", { mode: "bigint" }).primaryKey(),
    propertyId: bigint("property_id", { mode: "bigint" })
      .notNull()
      .references(() => property.id),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    type: text("type").notNull(),
    measureType: text("measure_type"),
    description: text("description").notNull(),
    estimatedCost: real("estimated_cost"),
    constingencyCost: real("contingency_cost"),
    // default will indicate whether a mtaterial is currently being used in a recommendation and we will use this boolean to switch
    // between materials in the UI and switch off all materials entirely
    default: boolean("default").notNull(),
    startingUValue: real("starting_u_value"),
    newUValue: real("new_u_value"),
    sapPoints: real("sap_points"),
    heatDemand: real("heat_demand"),
    kwhSavings: real("kwh_savings"),
    co2EquivalentSavings: real("co2_equivalent_savings"),
    energySavings: real("energy_savings"),
    energyCostSavings: real("energy_cost_savings"),
    propertyValuationIncrease: real("property_valuation_increase"),
    rentalYieldIncrease: real("rental_yield_increase"),
    totalWorkHours: real("total_work_hours"),
    labourDays: real("labour_days"),
    alreadyInstalled: boolean("already_installed").default(false),
  },
  (table) => [
    index("recommendation_property_id_idx").on(table.propertyId),
    index("idx_recommendation_active_defaults")
      .on(table.id)
      .where(
        sql`${table.default} = true AND ${table.alreadyInstalled} = false`,
      ),

    index("idx_recommendation_active_id_property")
      .on(table.id, table.propertyId)
      .where(
        sql`${table.default} = true AND ${table.alreadyInstalled} = false`,
      ),
  ],
);

export const unitQuantity: [string, ...string[]] = ["m2", "part", "kwp"];
export const unitQuantityEnum = pgEnum("unit_quantity", unitQuantity);

export const recommendationMaterials = pgTable(
  "recommendation_materials",
  {
    id: bigserial("id", { mode: "bigint" }).primaryKey(),
    recommendationId: bigint("recommendation_id", {
      mode: "bigint",
    })
      .notNull()
      .references(() => recommendation.id, { onDelete: "cascade" }),
    materialId: bigint("material_id", { mode: "bigint" })
      .notNull()
      .references(() => material.id),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    depth: real("depth"),
    quantity: real("quantity"),
    quantityUnit: unitQuantityEnum("quantity_unit"),
    estimatedCost: real("estimated_cost").notNull(),
  },
  (table) => [
    index("recommendation_materials_recommendation_id_idx").on(
      table.recommendationId,
    ),
  ],
);

// We create a plan type, for common plan types that we produce for clients
export const PlanType: [string, ...string[]] = [
  "solar_eco4",
  "solar_hhrsh_eco4",
  "empty_cavity_eco",
  "partial_cavity_eco",
  "extraction_eco",
];
export type PlanTypeEnum =
  | "solar_eco4"
  | "solar_hhrsh_eco4"
  | "empty_cavity_eco"
  | "partial_cavity_eco"
  | "extraction_eco";
export const planTypeEnum = pgEnum("plan_type", PlanType);

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

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

    propertyId: bigint("property_id", { mode: "bigint" })
      .notNull()
      .references(() => property.id),

    scenarioId: bigint("scenario_id", { mode: "bigint" }).references(
      () => scenario.id,
    ),

    createdAt: timestamp("created_at").notNull().defaultNow(),
    isDefault: boolean("is_default").notNull(),

    // ─────────────────────────────────────────────────────────
    // Valuation metrics (existing)
    // ─────────────────────────────────────────────────────────
    valuationIncreaseLowerBound: real("valuation_increase_lower_bound"),
    valuationIncreaseUpperBound: real("valuation_increase_upper_bound"),
    valuationIncreaseAverage: real("valuation_increase_average"),

    // ─────────────────────────────────────────────────────────
    // NEW — SAP / EPC
    // ─────────────────────────────────────────────────────────
    postSapPoints: real("post_sap_points"),
    postEpcRating: epcEnum("post_epc_rating"),

    // ─────────────────────────────────────────────────────────
    // NEW — Carbon emissions (tonnes CO₂e/yr)
    // ─────────────────────────────────────────────────────────
    postCo2Emissions: real("post_co2_emissions"),
    co2Savings: real("co2_savings"), // baseline - post

    // ─────────────────────────────────────────────────────────
    // NEW — Energy bills
    // ─────────────────────────────────────────────────────────
    postEnergyBill: real("post_energy_bill"),
    energyBillSavings: real("energy_bill_savings"),

    // ─────────────────────────────────────────────────────────
    // NEW — Energy consumption (kWh/year)
    // ─────────────────────────────────────────────────────────
    postEnergyConsumption: real("post_energy_consumption"),
    energyConsumptionSavings: real("energy_consumption_savings"),

    // ─────────────────────────────────────────────────────────
    // NEW — Valuation
    // ─────────────────────────────────────────────────────────
    valuationPostRetrofit: real("valuation_post_retrofit"),
    valuationIncrease: real("valuation_increase"),

    // Plan costing data
    costOfWorks: real("cost_of_works"),
    contingencyCost: real("contingency_cost"),

    // ─────────────────────────────────────────────────────────
    // Plan type stays as-is
    // ─────────────────────────────────────────────────────────
    planType: planTypeEnum("plan_type"),
  },
  (table) => [
    index("idx_plan_portfolio_scenario").on(
      table.portfolioId,
      table.scenarioId,
    ),
    index("idx_plan_latest_per_property").on(
      table.portfolioId,
      table.scenarioId,
      table.propertyId,
      table.createdAt.desc(),
    ),
  ],
);

export const planRecommendations = pgTable(
  "plan_recommendations",
  {
    id: bigserial("id", { mode: "bigint" }).primaryKey(),
    planId: bigint("plan_id", { mode: "bigint" })
      .notNull()
      .references(() => plan.id),
    recommendationId: bigint("recommendation_id", {
      mode: "bigint",
    })
      .notNull()
      .references(() => recommendation.id),
  },
  (table) => [
    index("idx_plan_recommendations_plan_id").on(table.planId),
    index("idx_plan_recommendations_plan_rec").on(
      table.planId,
      table.recommendationId,
    ),
  ],
);

export const HousingType: [string, ...string[]] = ["Private", "Social"];

export const housingTypeEnum = pgEnum("housing_type", HousingType);

export const scenario = pgTable("scenario", {
  id: bigserial("id", { mode: "bigint" }).primaryKey(),
  name: text("name"),
  budget: real("budget"),
  portfolioId: bigint("portfolio_id", { mode: "bigint" })
    .notNull()
    .references(() => portfolio.id),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  housingType: housingTypeEnum("housing_type").notNull(),
  goal: goalEnum("goal").notNull(),
  goalValue: text("goal_value"),
  ashpCop: real("ashp_cop").default(2.8), // Coefficient of Performance for Air Source Heat Pumps defaults to 2.8
  triggerFilePath: text("trigger_file_path"),
  alreadyInstalledFilePath: text("already_installed_file_path"),
  patchesFilePath: text("patches_file_path"),
  nonInvasideRecommendationsFilePath: text(
    "non_invasive_recommendations_file_path",
  ),
  exclusions: text("exclusions"),
  multiPlan: boolean("multi_plan"),
  isDefault: boolean("is_default").notNull(),
  // Aggregations that were previously being stored against the portfolio, that are now being stored against the scenario
  cost: real("cost"),
  contingency: real("contingency"),
  funding: real("funding"),
  totalWorkHours: real("total_work_hours"),
  energySavings: real("energy_savings"), // Unit is always kWh so we don't need to store unit
  co2EquivalentSavings: real("co2_equivalent_savings"), // Unit is always tonnes so we don't need to store unit
  energyCostSavings: real("energy_cost_savings"), // Unit is always £ so we don't need to store unit for the moment
  propertyValuationIncrease: real("property_valuation_increase"), // Unit is always £ so we don't need to store unit for the moment
  labourDays: real("labour_days"),
  // Aggregates added for the summary tab
  epcBreakdownPreRetrofit: text("epc_breakdown_pre_retrofit"),
  epcBreakdownPostRetrofit: text("epc_breakdown_post_retrofit"),
  numberOfProperties: integer("number_of_properties"),
  nUnitsToRetrofit: integer("n_units_to_retrofit"),
  co2PerUnitPreRetrofit: text("co2_per_unit_pre_retrofit"),
  co2PerUnitPostRetrofit: text("co2_per_unit_post_retrofit"),
  energyBillPerUnitPreRetrofit: text("energy_bill_per_unit_pre_retrofit"),
  energyBillPerUnitPostRetrofit: text("energy_bill_per_unit_post_retrofit"),
  energyConsumptionPerUnitPreRetrofit: text(
    "energy_consumption_per_unit_pre_retrofit",
  ),
  energyConsumptionPerUnitPostRetrofit: text(
    "energy_consumption_per_unit_post_retrofit",
  ),
  valuationImprovementPerUnit: text("valuation_improvement_per_unit"),
  costPerUnit: text("cost_per_unit"),
  costPerCo2Saved: text("cost_per_co2_saved"),
  costPerSapPoint: text("cost_per_sap_point"),
  valuationReturnOnInvestment: text("valuation_return_on_investment"),
});

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

    uprn: bigint("uprn", { mode: "bigint" }).notNull(),

    measureType: measureTypeEnum("measure_type").notNull(),
    installedAt: timestamp("installed_at").defaultNow(),

    // Impacts
    sapPoints: real("sap_points"),
    carbonSavings: real("carbon_savings"), // tonnes CO₂e / yr
    kwhSavings: real("kwh_savings"), // kWh / yr
    billSavings: real("bill_savings"), // £ / yr
    heatDemandSavings: real("heat_demand_savings"),

    //
    source: text("source"), // e.g. "EPC", "Survey", "Installer"

    // Soft delete / supersession
    isActive: boolean("is_active").notNull().default(true),
  },
  (table) => [
    index("idx_installed_measure_uprn").on(table.uprn),

    index("idx_installed_measure_uprn_active")
      .on(table.uprn)
      .where(sql`${table.isActive} = true`),

    index("idx_installed_measure_measure_type").on(table.measureType),

    index("idx_installed_measure_uprn_measure")
      .on(table.uprn, table.measureType)
      .where(sql`${table.isActive} = true`),
  ],
);

export type Plan = InferModel<typeof plan, "select">;
export type Recommendation = InferModel<typeof recommendation, "select">;
export type PlanRecommendations = InferModel<
  typeof planRecommendations,
  "select"
>;
export type RecommendationMaterial = InferModel<
  typeof recommendationMaterials,
  "select"
>;
export type ScenarioSelect = InferModel<typeof scenario, "select">;

// We allow recommendation types to be a string however we'll set up a typing for it here to control
// the types we expect
// Note: roof_insulation, wall_insulation and floor_insulation aren't used in the app anymore and
// have been superseded by the more specific types. The backend will no longer return these types and
// we will depracate these in the future
export type RecommendationType =
  | "internal_wall_insulation"
  | "external_wall_insulation"
  | "cavity_wall_insulation"
  | "suspended_floor_insulation"
  | "solid_floor_insulation"
  | "exposed_floor_insulation"
  | "loft_insulation"
  | "flat_roof_insulation"
  | "room_roof_insulation"
  | "mechanical_ventilation"
  | "sealing_open_fireplace"
  | "low_energy_lighting"
  | "windows_glazing"
  | "roof_insulation"
  | "wall_insulation"
  | "floor_insulation"
  | "solar_pv"
  | "heating"
  | "hot_water_tank_insulation"
  | "heating_control"
  | "secondary_heating"
  | "cylinder_thermostat"
  | "trickle_vents"
  | "mixed_glazing"
  | "draught_proofing"
  | "extension_cavity_wall_insulation";

export type UnnestedRecommendation = {
  quantity: number | null;
  quantityUnit: string | null;
  estimatedCost: number;
  materialType: string | null;
  propertyId: string;
  measureType: string | null;
  measureTypeCount: number | undefined;
};

export interface PortfolioPlanRecommendation {
  quantity: number | null; // Allow null for cases where no materials are associated
  quantityUnit: string | null;
  estimatedCost: number;
  materialType: string | null;
  numberOfProperties: number;
  measureTypeCount: number | undefined;
}

export interface RecommendationMaterialToMaterial extends Material {
  material: Material;
}

export interface RecommendationWithMaterials {
  id: string;
  type: RecommendationType;
  description: string;
  estimatedCost: number;
  startingUValue: number;
  newUValue: number;
  sapPoints: number;
  heatDemand: number;
  co2EquivalentSavings: number;
  energySavings: number;
  energyCostSavings: number;
  propertyValuationIncrease: number;
  rentalYieldIncrease: number;
  totalWorkHours: number;
  recommendationMaterials: RecommendationMaterialToMaterial[];
}

export const measuresDisplayLabels = {
  internal_wall_insulation: "Internal Wall Insulation",
  external_wall_insulation: "External Wall Insulation",
  cavity_wall_insulation: "Cavity Wall Insulation",
  loft_insulation: "Loft Insulation",
  flat_roof_insulation: "Flat Roof Insulation",
  room_roof_insulation: "Room-in-Roof Insulation",
  suspended_floor_insulation: "Suspended Floor Insulation",
  solid_floor_insulation: "Solid Floor Insulation",
  boiler_upgrade: "Boiler Upgrade",
  high_heat_retention_storage_heaters: "High Heat Retention Storage Heater",
  air_source_heat_pump: "Air Source Heat Pump",
  secondary_heating: "Secondary Heating",
  solar_pv: "Solar PV",
  double_glazing: "Double Glazing",
  secondary_glazing: "Secondary Glazing",
  ventilation: "Ventilation",
  low_energy_lighting: "Low Energy Lighting",
  fireplace: "Fireplace",
  hot_water_tank_insulation: "Hot Water Tank Insulation",
  cylinder_thermostat: "Cylinder Thermostat",
} as const;

export type MeasureKey = keyof typeof measuresDisplayLabels;

export const measuresList: MeasureKey[] = Object.keys(
  measuresDisplayLabels,
) as MeasureKey[];

export const MeasureKeyEnum = z.enum([
  ...Object.keys(measuresDisplayLabels),
] as [
  MeasureKey, // Force at least one measure key
  ...MeasureKey[],
]);
