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

import { bulkAddressUploads } from "./bulk_address_uploads";

// User confirmations of a combiner row's UPRN, captured on the pre-finalise
// "Addresses" review tab (ADR-0057). address2uprn withholds ambiguous / no-UPRN
// matches; the user resolves each flagged row via OS Places here, keyed by the
// combiner CSV's `source_row_id` (there is no `property.id` yet — the finaliser
// creates identities). At dispatchFinaliser these corrections are overlaid onto
// the combiner CSV before the finaliser reads it, so the confirmed UPRN drives
// both the identity insert and property_overrides in one pass — no post-finalise
// backfill (which is why the portfolio "Unmatched" tab is retired).
//
// A row is resolved when it has a numeric `uprn` OR `markedNoUprn = true` (the
// user asserts no UPRN exists — an accepted terminal state; the finaliser
// leaves it as a no-UPRN property).
export const bulkUploadUprnCorrections = pgTable(
  "bulk_upload_uprn_corrections",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    uploadId: uuid("upload_id")
      .notNull()
      .references(() => bulkAddressUploads.id, { onDelete: "cascade" }),
    // The combiner CSV join key (synthetic UUID minted at start-address-matching).
    sourceRowId: text("source_row_id").notNull(),
    // The confirmed UPRN; null when markedNoUprn.
    uprn: text("uprn"),
    address: text("address"),
    postcode: text("postcode"),
    // The user asserts this address has no UPRN — resolved without one.
    markedNoUprn: boolean("marked_no_uprn").notNull().default(false),
    userId: text("user_id"),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp("updated_at", { withTimezone: true })
      .notNull()
      .defaultNow()
      .$onUpdate(() => new Date()),
  },
  (t) => ({
    // One correction per combiner row — the confirm action upserts on this.
    uploadRowUnique: unique("bulk_upload_uprn_corrections_upload_row_unique").on(
      t.uploadId,
      t.sourceRowId,
    ),
  }),
);
