import { describe, expect, it } from "vitest";
import { PgDialect } from "drizzle-orm/pg-core";
import { sql } from "drizzle-orm";
import {
  newApproachJoins,
  estimatedSql,
  withoutEpcSql,
  isExpiredSql,
  provenanceSignalSql,
} from "./epcSources";

/**
 * `epc_property.source` has three values: `lodged`, and the two flavours of the
 * predicted slot — `predicted` (gap-filled blind) and `expired` (gap-filled, but
 * conditioned on the dwelling's own expired pre-2012 certificate; Model ADR-0054).
 *
 * These tests exist because of a specific, silent failure: every EPC expression
 * used to match `source = 'predicted'` alone. An `expired` row then matched
 * neither the lodged nor the predicted alias, so the home read as though it had a
 * valid current certificate and vanished from BOTH the "Homes Without an EPC" and
 * "Expired EPCs" cards. `epc_property.source` is plain `text` with no enum or CHECK
 * constraint, so nothing at the database level would have caught it.
 *
 * They assert on rendered SQL rather than query results because the repo has no
 * database harness — structural, but they pin the exact invariant that broke.
 */

const dialect = new PgDialect();
const render = (fragment: ReturnType<typeof sql>) =>
  dialect.sqlToQuery(fragment).sql;

// A zero-interpolation fragment renders empty on its own, so embed it.
const renderJoins = () => render(sql`SELECT 1 ${newApproachJoins}`);

describe("newApproachJoins", () => {
  it("joins all three sources, so no epc_property row is invisible", () => {
    const joins = renderJoins();
    expect(joins).toContain("epl.source = 'lodged'");
    expect(joins).toContain("epp.source = 'predicted'");
    expect(joins).toContain("epx.source = 'expired'");
  });
});

describe("estimatedSql — 'was this picture modelled?'", () => {
  it("counts BOTH flavours of the predicted slot as estimated", () => {
    // An expired-conditioned prediction is still a prediction: it must carry the
    // Estimated provenance signal, not read as a real certificate.
    expect(render(estimatedSql(sql`e`))).toContain(
      "(epl.id IS NULL AND (epp.id IS NOT NULL OR epx.id IS NOT NULL))",
    );
  });
});

describe("withoutEpcSql — 'does any certificate exist?'", () => {
  it("requires neither a lodged nor a historical record", () => {
    expect(render(withoutEpcSql(sql`e`))).toContain(
      "(epl.id IS NULL AND epx.id IS NULL)",
    );
  });

  it("does not consult the blind-predicted row: a prediction is not a certificate", () => {
    // A home with only a `predicted` row has no EPC anywhere — that is precisely
    // what makes it "without an EPC", so `epp` must not appear as evidence of one.
    expect(render(withoutEpcSql(sql`e`))).not.toContain("epp");
  });

  it("is a different question from estimatedSql", () => {
    // The bug was treating these as synonyms. An `expired` home is estimated AND
    // has an EPC, so the two predicates must disagree about it.
    expect(render(withoutEpcSql(sql`e`))).not.toEqual(render(estimatedSql(sql`e`)));
  });
});

describe("isExpiredSql — 'we found a certificate and it is out of date'", () => {
  it("treats an expired-source row as expired by construction", () => {
    // ADR-0054: an `expired` row is only ever written when the conditioning
    // certificate predates 2012, so it is >10 years old by definition.
    expect(render(isExpiredSql(sql`e`))).toContain("epx.id IS NOT NULL");
  });

  it("falls back to inspection_date when the lodged cert has no registration_date", () => {
    // Reading registration_date alone silently counted date-less lodged certs as
    // current; lodgementDateSql already coalesces the same way.
    expect(render(isExpiredSql(sql`e`))).toContain(
      "COALESCE(epl.registration_date, epl.inspection_date)",
    );
  });

  it("excludes gap-filled legacy rows — nothing to expire without a certificate", () => {
    expect(render(isExpiredSql(sql`e`))).toContain(
      "NOT COALESCE(e.estimated, false)",
    );
  });
});

describe("provenanceSignalSql", () => {
  it("shows the Estimated badge for both flavours of the predicted slot", () => {
    // Without epx here, an expired-conditioned home would render with no badge at
    // all, as though its picture came off a real current certificate.
    expect(render(provenanceSignalSql)).toContain(
      "(epp.id IS NOT NULL OR epx.id IS NOT NULL) AND epl.id IS NULL",
    );
  });
});
