import { db } from "@/app/db/db";
import { property } from "@/app/db/schema/property";
import { and, asc, eq, isNull } from "drizzle-orm";

export interface UnmatchedProperty {
  id: string;
  address: string | null;
  postcode: string | null;
}

// Properties in a portfolio that have no UPRN — i.e. were never matched to an
// Ordnance Survey address record. We surface these separately from the main
// property table so the customer can correct the address/postcode and re-run
// the address→UPRN search (the "advanced ARA search", wired up in a follow-up).
// `markedForDeletion` rows are excluded so soft-deleted properties don't show.
export async function getUnmatchedProperties(
  portfolioId: string,
): Promise<UnmatchedProperty[]> {
  if (!/^\d+$/.test(portfolioId)) return [];
  const pid = BigInt(portfolioId);

  const rows = await db
    .select({
      id: property.id,
      address: property.address,
      postcode: property.postcode,
      userInputtedAddress: property.userInputtedAddress,
      userInputtedPostcode: property.userInputtedPostcode,
    })
    .from(property)
    .where(
      and(
        eq(property.portfolioId, pid),
        isNull(property.uprn),
        eq(property.markedForDeletion, false),
      ),
    )
    .orderBy(asc(property.address));

  // Prefer the canonical address; fall back to whatever the user typed on
  // import so the row is still identifiable while it awaits a match.
  return rows.map((r) => ({
    id: String(r.id),
    address: r.address ?? r.userInputtedAddress ?? null,
    postcode: r.postcode ?? r.userInputtedPostcode ?? null,
  }));
}
