import { db } from "@/app/db/db";
import { sql } from "drizzle-orm";
import { NextRequest, NextResponse } from "next/server";
import {
  newApproachJoins,
  carbonSql,
  billsSql,
  effectiveSapSql,
  effectiveEpcBandSql,
  estimatedSql,
  isExpiredSql,
  lodgedEpcBandSql,
  provenanceSignalSql,
} from "@/lib/services/epcSources";
import { EPC_BANDS } from "@/lib/scenarios/model";
import { tagFilterCondition } from "@/lib/reporting/tagFilterSql";
import { parseReportingViewState } from "@/lib/reporting/viewState";

/**
 * Drill-down: the homes behind a reporting figure (PRD #370, Screen C).
 * One filter per request; every count on the reporting page deep-links here.
 * Band-movement filters implement the CONTEXT.md Likely downgrade / upgrade
 * definitions (lodged band vs effective band; real certificates only —
 * lodgedEpcBandSql renders NULL for predicted homes).
 */

const FILTERS = [
  "band",
  "estimated",
  "expired",
  "likely-downgrade",
  "likely-upgrade",
  "measure",
] as const;

type Filter = (typeof FILTERS)[number];

const DEFAULT_PAGE_SIZE = 25;
const MAX_PAGE_SIZE = 100;

function parsePositiveInt(value: string | null, fallback: number): number {
  const parsed = Number(value);
  return Number.isInteger(parsed) && parsed > 0 ? parsed : fallback;
}

export async function GET(
  request: NextRequest,
  props: { params: Promise<{ portfolioId: string }> },
) {
  const { portfolioId } = await props.params;
  const pid = BigInt(portfolioId);
  const params = request.nextUrl.searchParams;

  const filter = params.get("filter") as Filter | null;
  if (!filter || !FILTERS.includes(filter)) {
    return NextResponse.json({ error: "Invalid filter" }, { status: 400 });
  }

  const band = params.get("band");
  if (filter === "band" && (!band || !(EPC_BANDS as readonly string[]).includes(band))) {
    return NextResponse.json({ error: "Invalid band" }, { status: 400 });
  }

  const scenarioId = params.get("scenarioId");
  const measureType = params.get("measureType");
  if (filter === "measure" && (!scenarioId || !measureType)) {
    return NextResponse.json(
      { error: "measure filter requires scenarioId and measureType" },
      { status: 400 },
    );
  }

  const page = parsePositiveInt(params.get("page"), 1);
  const pageSize = Math.min(
    parsePositiveInt(params.get("pageSize"), DEFAULT_PAGE_SIZE),
    MAX_PAGE_SIZE,
  );

  const filterSql = {
    band: sql`COALESCE((${effectiveEpcBandSql})::text, 'Unknown') = ${band}`,
    estimated: sql`${estimatedSql(sql`e`)} = true`,
    expired: sql`${isExpiredSql(sql`e`)} = true AND ${estimatedSql(sql`e`)} = false`,
    "likely-downgrade": sql`(${lodgedEpcBandSql}) IS NOT NULL
      AND (${effectiveEpcBandSql}) IS NOT NULL
      AND (${lodgedEpcBandSql}) < (${effectiveEpcBandSql})`,
    "likely-upgrade": sql`(${lodgedEpcBandSql}) IS NOT NULL
      AND (${effectiveEpcBandSql}) IS NOT NULL
      AND (${lodgedEpcBandSql}) > (${effectiveEpcBandSql})`,
    measure: sql`EXISTS (
      SELECT 1
      FROM (
        SELECT DISTINCT ON (property_id) id
        FROM plan
        WHERE portfolio_id = ${pid}
          AND scenario_id = ${scenarioId ? BigInt(scenarioId) : null}
          AND property_id = p.id
        ORDER BY property_id, created_at DESC
      ) lp
      JOIN recommendation r
        ON r.plan_id = lp.id
        AND r.default = true
        AND r.already_installed = false
        AND r.measure_type = ${measureType}
    )`,
  }[filter];

  const result = await db.execute(sql`
    SELECT
      p.id,
      COALESCE(p.address, p.user_inputted_address) AS address,
      COALESCE(p.postcode, p.user_inputted_postcode) AS postcode,
      (${effectiveEpcBandSql})::text AS epc_band,
      (${effectiveSapSql})::float AS sap,
      (${billsSql(sql`e`)})::float AS bills,
      (${carbonSql(sql`e`)})::float AS carbon,
      ${provenanceSignalSql} AS provenance,
      COUNT(*) OVER()::int AS total
    FROM property p
    LEFT JOIN property_details_epc e ON e.property_id = p.id
    ${newApproachJoins}
    WHERE p.portfolio_id = ${pid}
      AND ${tagFilterCondition(parseReportingViewState(params).tags)}
      AND ${filterSql}
    ORDER BY address NULLS LAST, p.id
    LIMIT ${pageSize} OFFSET ${(page - 1) * pageSize};
  `);

  type Row = {
    id: number;
    address: string | null;
    postcode: string | null;
    epc_band: string | null;
    sap: number | null;
    bills: number | null;
    carbon: number | null;
    provenance: string;
    total: number;
  };

  const rows = result.rows as Row[];

  return NextResponse.json({
    properties: rows.map((r) => ({
      id: r.id,
      address: r.address,
      postcode: r.postcode,
      epcBand: r.epc_band,
      sap: r.sap,
      bills: r.bills,
      carbon: r.carbon,
      provenance: r.provenance,
    })),
    total: rows[0]?.total ?? 0,
    page,
    pageSize,
  });
}
