import {
  bigint,
  pgEnum,
  pgTable,
  smallint,
  text,
  timestamp,
  unique,
  uuid,
} from "drizzle-orm/pg-core";
import { portfolio } from "./portfolio";
import { property } from "./property";

// The per-Property fact layer deferred by ADR-0004: one row per
// (property, building_part, override_component) carrying the resolved enum value
// as a denormalised snapshot. Design + rationale (Q6–Q9, snapshot-not-FK,
// recalculate-on-rerun) live in docs/design/bulk-upload-finaliser.md.
//
// `override_component` values mirror the classifier category keys used in BOTH the
// frontend (src/lib/bulkUpload/columnFields.ts) and the Model backend
// (ClassifiableColumn.name), so the finaliser maps category → component with no
// translation. This is the only DB-level typing left on a row — `override_value`
// is a free-text snapshot of the resolved enum from `landlord_*_overrides`.
export const OverrideComponentValues: [string, ...string[]] = [
  "wall_type",
  "roof_type",
  "property_type",
  "built_form_type",
  "main_fuel",
  "glazing",
  "construction_age_band",
  "water_heating",
  "main_heating_system",
];

export const overrideComponentEnum = pgEnum(
  "override_component",
  OverrideComponentValues,
);

export const propertyOverrides = pgTable(
  "property_overrides",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    propertyId: bigint("property_id", { mode: "bigint" })
      .notNull()
      .references(() => property.id, { onDelete: "cascade" }),
    portfolioId: bigint("portfolio_id", { mode: "bigint" })
      .notNull()
      .references(() => portfolio.id, { onDelete: "cascade" }),
    // 0 = main building, 1 = extension 1, 2 = extension 2, … (ADR-0004 ordering).
    buildingPart: smallint("building_part").notNull(),
    overrideComponent: overrideComponentEnum("override_component").notNull(),
    // Denormalised snapshot copy of the resolved enum from landlord_*_overrides.
    overrideValue: text("override_value").notNull(),
    // Raw spreadsheet cell text this snapshot resolved from (provenance + re-sync key).
    originalSpreadsheetDescription: text(
      "original_spreadsheet_description",
    ).notNull(),
    createdAt: timestamp("created_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
    updatedAt: timestamp("updated_at", { withTimezone: true })
      .notNull()
      .defaultNow()
      .$onUpdate(() => new Date()),
  },
  (table) => ({
    propertyComponentPartUnique: unique(
      "property_overrides_property_component_part_unique",
    ).on(table.propertyId, table.overrideComponent, table.buildingPart),
  }),
);
