import { eq } from "drizzle-orm";
import * as XLSX from "xlsx";

import { db } from "@/app/db/db";
import { bulkAddressUploads } from "@/app/db/schema/bulk_address_uploads";
import { bulkUploadUprnCorrections } from "@/app/db/schema/bulk_upload_uprn_corrections";
import { createRetrofitDataS3Client } from "@/app/utils/s3";
import { SOURCE_ROW_ID_COLUMN } from "./s3Keys";

// Columns address2uprn writes onto each combiner row (ADR-0057). `status` is the
// per-row outcome; a withheld/ambiguous/no-match row has an empty `uprn`.
const UPRN_COL = "address2uprn_uprn";
const STATUS_COL = "address2uprn_status";
const LEXISCORE_COL = "address2uprn_lexiscore";

// A combiner row is confidently matched only when address2uprn kept a UPRN AND
// did not flag it. Everything else — unmatched, ambiguous_duplicate,
// invalid_postcode, error, or a legacy row with no status but no UPRN — needs
// the user to resolve it on the Addresses tab before finalise.
function isConfidentlyMatched(row: Record<string, string>): boolean {
  const uprn = (row[UPRN_COL] ?? "").trim();
  const status = (row[STATUS_COL] ?? "").trim();
  if (!uprn) return false;
  return status === "" || status === "matched";
}

export interface FlaggedAddressRow {
  sourceRowId: string;
  address: string;
  postcode: string;
  status: string;
  lexiscore: number | null;
  // The candidate address2uprn found but did not trust (null when none).
  suggestedUprn: string | null;
  // The user's confirmation (from bulk_upload_uprn_corrections), if any.
  resolvedUprn: string | null;
  resolvedAddress: string | null;
  markedNoUprn: boolean;
  // Resolved = user picked a UPRN OR asserted there is none.
  resolved: boolean;
}

function parseS3Uri(uri: string): { bucket: string; key: string } {
  const url = new URL(uri); // s3://bucket/key...
  const key = url.pathname.startsWith("/") ? url.pathname.slice(1) : url.pathname;
  if (!url.hostname || !key) throw new Error(`Malformed S3 URI: ${uri}`);
  return { bucket: url.hostname, key };
}

async function readCombinerRows(s3Uri: string): Promise<Record<string, string>[]> {
  const { bucket, key } = parseS3Uri(s3Uri);
  const s3 = createRetrofitDataS3Client();
  const result = await s3.getObject({ Bucket: bucket, Key: key }).promise();
  const body = result.Body?.toString("utf-8");
  if (!body) return [];
  const wb = XLSX.read(body, { type: "string" });
  const sheet = wb.Sheets[wb.SheetNames[0]];
  // Every cell as a string so a numeric UPRN never arrives as a float.
  return XLSX.utils.sheet_to_json<Record<string, string>>(sheet, { defval: "", raw: false });
}

function buildAddress(row: Record<string, string>): string {
  return ["Address 1", "Address 2", "Address 3"]
    .map((c) => (row[c] ?? "").trim())
    .filter(Boolean)
    .join(", ");
}

/**
 * The combiner rows that need user confirmation, joined with any saved
 * correction. Reads the combiner CSV from `combinedOutputS3Uri` (ADR-0057).
 * Returns [] until the combiner has run.
 */
export async function getFlaggedAddresses(
  uploadId: string,
): Promise<FlaggedAddressRow[]> {
  const [row] = await db
    .select({ combinedOutputS3Uri: bulkAddressUploads.combinedOutputS3Uri })
    .from(bulkAddressUploads)
    .where(eq(bulkAddressUploads.id, uploadId));
  if (!row?.combinedOutputS3Uri) return [];

  const [rows, corrections] = await Promise.all([
    readCombinerRows(row.combinedOutputS3Uri),
    db
      .select()
      .from(bulkUploadUprnCorrections)
      .where(eq(bulkUploadUprnCorrections.uploadId, uploadId)),
  ]);

  const correctionByRow = new Map(corrections.map((c) => [c.sourceRowId, c]));

  return rows
    .filter((r) => !isConfidentlyMatched(r))
    .map((r) => {
      const sourceRowId = (r[SOURCE_ROW_ID_COLUMN] ?? "").trim();
      const uprn = (r[UPRN_COL] ?? "").trim();
      const rawScore = (r[LEXISCORE_COL] ?? "").trim();
      const lexiscore = rawScore === "" ? null : Number(rawScore);
      const c = correctionByRow.get(sourceRowId);
      const resolvedUprn = c?.uprn ?? null;
      const markedNoUprn = c?.markedNoUprn ?? false;
      return {
        sourceRowId,
        address: buildAddress(r),
        postcode: (r["postcode"] ?? "").trim(),
        status: (r[STATUS_COL] ?? (uprn ? "matched" : "unmatched")).trim(),
        lexiscore: lexiscore !== null && Number.isFinite(lexiscore) ? lexiscore : null,
        suggestedUprn: uprn || null,
        resolvedUprn,
        resolvedAddress: c?.address ?? null,
        markedNoUprn,
        resolved: resolvedUprn !== null || markedNoUprn,
      };
    });
}

/** Count of flagged rows the user has not yet resolved — gates Finalise. */
export async function countUnresolvedFlaggedAddresses(uploadId: string): Promise<number> {
  const flagged = await getFlaggedAddresses(uploadId);
  return flagged.filter((f) => !f.resolved).length;
}

const ADDRESS_COL = "address2uprn_address";
const OVERLAY_STATUS_CONFIRMED = "user_confirmed";
const OVERLAY_STATUS_NO_UPRN = "user_no_uprn";

/**
 * Overlay the user's saved corrections onto the combiner CSV and write the
 * result to a sibling `…​.confirmed.csv`, returning its S3 URI (ADR-0057). The
 * finaliser reads this so a confirmed UPRN drives both the identity insert and
 * property_overrides in one pass. No corrections ⇒ the original URI is returned
 * unchanged (no rewrite). A `markedNoUprn` row keeps an empty UPRN (accepted
 * terminal state — the finaliser leaves it as a no-UPRN property).
 */
export async function buildConfirmedCombinerUri(
  uploadId: string,
  combinedOutputS3Uri: string,
): Promise<string> {
  const corrections = await db
    .select()
    .from(bulkUploadUprnCorrections)
    .where(eq(bulkUploadUprnCorrections.uploadId, uploadId));
  if (corrections.length === 0) return combinedOutputS3Uri;
  const byRow = new Map(corrections.map((c) => [c.sourceRowId, c]));

  const { bucket, key } = parseS3Uri(combinedOutputS3Uri);
  const s3 = createRetrofitDataS3Client();
  const result = await s3.getObject({ Bucket: bucket, Key: key }).promise();
  const body = result.Body?.toString("utf-8");
  if (!body) return combinedOutputS3Uri;

  const wb = XLSX.read(body, { type: "string" });
  const sheet = wb.Sheets[wb.SheetNames[0]];
  // Array-of-arrays so every column + its order is preserved exactly; we patch
  // only the address2uprn cells of corrected rows.
  const aoa = XLSX.utils.sheet_to_json<string[]>(sheet, {
    header: 1,
    defval: "",
    raw: false,
    blankrows: false,
  });
  const headers = (aoa[0] ?? []).map((h) => String(h));
  const iRow = headers.indexOf(SOURCE_ROW_ID_COLUMN);
  const iUprn = headers.indexOf(UPRN_COL);
  const iStatus = headers.indexOf(STATUS_COL);
  const iAddr = headers.indexOf(ADDRESS_COL);
  if (iRow < 0 || iUprn < 0) return combinedOutputS3Uri; // not an address2uprn CSV

  for (let r = 1; r < aoa.length; r++) {
    const line = aoa[r];
    const srid = String(line[iRow] ?? "").trim();
    const c = byRow.get(srid);
    if (!c) continue;
    if (c.markedNoUprn) {
      line[iUprn] = "";
      if (iStatus >= 0) line[iStatus] = OVERLAY_STATUS_NO_UPRN;
    } else {
      line[iUprn] = c.uprn ?? "";
      if (iStatus >= 0) line[iStatus] = OVERLAY_STATUS_CONFIRMED;
      if (iAddr >= 0 && c.address) line[iAddr] = c.address;
    }
  }

  const csv = XLSX.utils.sheet_to_csv(XLSX.utils.aoa_to_sheet(aoa));
  const confirmedKey = `${key.replace(/\.csv$/i, "")}.confirmed.csv`;
  await s3
    .putObject({ Bucket: bucket, Key: confirmedKey, Body: csv, ContentType: "text/csv" })
    .promise();
  return `s3://${bucket}/${confirmedKey}`;
}

export type UprnCorrectionInput =
  | {
      sourceRowId: string;
      uprn: string;
      address: string;
      postcode: string;
      markedNoUprn?: false;
    }
  | { sourceRowId: string; markedNoUprn: true };

/** Upsert one combiner row's confirmation (assign a UPRN, or mark no-UPRN). */
export async function upsertUprnCorrection(
  uploadId: string,
  input: UprnCorrectionInput,
  userId?: string,
): Promise<void> {
  const values = input.markedNoUprn
    ? {
        uploadId,
        sourceRowId: input.sourceRowId,
        uprn: null,
        address: null,
        postcode: null,
        markedNoUprn: true as const,
        userId: userId ?? null,
      }
    : {
        uploadId,
        sourceRowId: input.sourceRowId,
        uprn: input.uprn,
        address: input.address,
        postcode: input.postcode,
        markedNoUprn: false as const,
        userId: userId ?? null,
      };

  await db
    .insert(bulkUploadUprnCorrections)
    .values(values)
    .onConflictDoUpdate({
      target: [
        bulkUploadUprnCorrections.uploadId,
        bulkUploadUprnCorrections.sourceRowId,
      ],
      set: {
        uprn: values.uprn,
        address: values.address,
        postcode: values.postcode,
        markedNoUprn: values.markedNoUprn,
        userId: values.userId,
        updatedAt: new Date(),
      },
    });
}
