import { db } from "@/app/db/db";
import { sql } from "drizzle-orm";
import { NextRequest, NextResponse } from "next/server";
import type { PortfolioGoalType } from "@/app/db/schema/portfolio";
import {
  newApproachJoins,
  carbonSql,
  billsSql,
  effectiveSapSql,
  effectiveEpcBandSql,
  isNewApproachSql,
  lodgedSapSql,
} from "@/lib/services/epcSources";
import { computeLedger, DEFAULT_GOAL_BAND } from "@/lib/reporting/model";
import { EPC_BANDS } from "@/lib/scenarios/model";
import { tagFilterCondition } from "@/lib/reporting/tagFilterSql";
import { parseReportingViewState } from "@/lib/reporting/viewState";

/* =======================
   Types
======================= */

type ScenarioAggregates = {
  n_units: number;
  avg_sap: number | null;
  avg_carbon: number | null;
  avg_bills: number | null;
  total_carbon: number | null;
  total_bills: number | null;
  total_sap_uplift: number | null;
};

type UpgradedAggregates = {
  n_units_upgraded: number;
  total_cost: number | null;
  contingency: number | null;
  total_funding: number | null;
};

type PortfolioAggregates = {
  avg_sap: number | null;
  avg_carbon: number | null;
  avg_bills: number | null;
  total_carbon: number | null;
  total_bills: number | null;
  // EPC-band buckets, computed in SQL (see BAND_BUCKETS_SQL) rather than shipped
  // one row per property to Node.
  band_a: number;
  band_b: number;
  band_c: number;
  band_d: number;
  band_e: number;
  band_f: number;
  band_g: number;
  band_unknown: number;
};

/**
 * EPC-band buckets by SAP, matching sapToEpc's thresholds exactly (A≥92, B≥81,
 * C≥69, D≥55, E≥39, F≥21, else G; NULL → Unknown). Bucketing in SQL keeps the
 * scenario band distribution to a single aggregate row instead of returning
 * every property's SAP for the client to loop over.
 */
const BAND_BUCKETS_SQL = sql`
  COUNT(*) FILTER (WHERE band_sap >= 92)::int                     AS band_a,
  COUNT(*) FILTER (WHERE band_sap >= 81 AND band_sap < 92)::int   AS band_b,
  COUNT(*) FILTER (WHERE band_sap >= 69 AND band_sap < 81)::int   AS band_c,
  COUNT(*) FILTER (WHERE band_sap >= 55 AND band_sap < 69)::int   AS band_d,
  COUNT(*) FILTER (WHERE band_sap >= 39 AND band_sap < 55)::int   AS band_e,
  COUNT(*) FILTER (WHERE band_sap >= 21 AND band_sap < 39)::int   AS band_f,
  COUNT(*) FILTER (WHERE band_sap IS NOT NULL AND band_sap < 21)::int AS band_g,
  COUNT(*) FILTER (WHERE band_sap IS NULL)::int                   AS band_unknown
`;

/* =======================
   Constants
======================= */

const EPC_MIN_SAP: Record<string, number> = {
  A: 92,
  B: 81,
  C: 69,
  D: 55,
  E: 39,
  F: 21,
  G: 0,
};

/* =======================
   Route
======================= */

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

  if (!scenarioId || scenarioId === "null") {
    return NextResponse.json({ error: "Invalid scenarioId" }, { status: 400 });
  }

  const pid = BigInt(portfolioId);
  const sid = BigInt(scenarioId);
  const hideNonCompliant =
    request.nextUrl.searchParams.get("hideNonCompliant") === "true";
  // "Measure against lodged EPCs" — judge who needed upgrading by the
  // lodged (register) position rather than the effective baseline. Reads
  // lodgedSapSql (real-certificate-gated; legacy falls back to
  // original_sap_points); unknown lodged keeps the home rather than
  // silently dropping it.
  const useLodgedBaseline =
    request.nextUrl.searchParams.get("useLodgedBaseline") === "true";

  // Tag filter (view-wide): scope every property scan below to the tagged
  // subset. Parsed via the shared view-state parser so the route reads the URL
  // exactly as the client serialised it.
  const tags = parseReportingViewState(request.nextUrl.searchParams).tags;
  const tagScope = tagFilterCondition(tags);

  /* ----------------------------------------------------------
     Compliance window — ADR-0010. complianceDate activates the
     filter; complianceBand defaults to C.
  ---------------------------------------------------------- */
  const complianceDateParam =
    request.nextUrl.searchParams.get("complianceDate");
  const complianceBandParam =
    request.nextUrl.searchParams.get("complianceBand");

  if (
    complianceBandParam !== null &&
    !(EPC_BANDS as readonly string[]).includes(complianceBandParam)
  ) {
    return NextResponse.json(
      { error: "Invalid complianceBand" },
      { status: 400 },
    );
  }
  if (
    complianceDateParam !== null &&
    (!/^\d{4}-\d{2}-\d{2}$/.test(complianceDateParam) ||
      Number.isNaN(Date.parse(complianceDateParam)))
  ) {
    return NextResponse.json(
      { error: "Invalid complianceDate" },
      { status: 400 },
    );
  }

  const complianceActive = complianceDateParam !== null;
  const complianceBand = complianceBandParam ?? DEFAULT_GOAL_BAND;
  // Placeholder date when inactive keeps the ::date cast valid; the
  // leading boolean short-circuits it out of the predicate.
  const complianceDate = complianceDateParam ?? "2030-01-01";

  /**
   * True when the row survives the compliance-window filter. A home is
   * skippable iff its lodged certificate shows the window band or better
   * AND remains valid (lodged within 10 years of the cut-off). EPC bands
   * compare lexically (A best). COALESCE(false): unknown or predicted
   * evidence is never skippable — mirrored lodged_* estimates don't count
   * because the new-approach arm requires a real lodged row (epl).
   */
  const passesComplianceWindowSql = sql`(
    ${complianceActive} = false
    OR NOT COALESCE(
      CASE WHEN ${isNewApproachSql} THEN (
        epl.id IS NOT NULL
        AND bp.lodged_epc_band <= ${complianceBand}
        AND COALESCE(epl.registration_date, epl.inspection_date) > (${complianceDate}::date - INTERVAL '10 years')
      ) ELSE (
        COALESCE(e.estimated, false) = false
        AND p.current_epc_rating <= ${complianceBand}
        AND e.lodgement_date > (${complianceDate}::date - INTERVAL '10 years')
      ) END,
      false)
  )`;

  /* ----------------------------------------------------------
     Query 0 — scenario definition
  ---------------------------------------------------------- */
  const scenarioResult = await db.execute(sql`
    SELECT goal, goal_value
    FROM scenario
    WHERE id = ${sid}
      AND portfolio_id = ${pid}
    LIMIT 1
  `);

  const scenario = scenarioResult.rows[0] as
    | { goal: PortfolioGoalType; goal_value: string }
    | undefined;

  if (!scenario) {
    return NextResponse.json({ error: "Scenario not found" }, { status: 404 });
  }

  const minSap =
    scenario.goal === "Increasing EPC"
      ? EPC_MIN_SAP[scenario.goal_value]
      : null;

  // The scenario's target band, for Increasing-EPC scenarios only (other goals
  // have no band target). Used by the interim already-at-target guard below.
  const targetBand =
    scenario.goal === "Increasing EPC" ? scenario.goal_value : null;

  /**
   * INTERIM guard for a backend bug (Hestia-Homes/Model#1652): the modelling engine
   * emits costed plans for homes that are ALREADY at the scenario's target band
   * — e.g. ~£300–600 of low-energy lighting / system tune-ups on effective-C
   * homes in a "Reach EPC C" scenario. Those aren't upgrades toward the target,
   * so counting them inflates "Homes upgraded" (and its costs) and stops the KPI
   * reconciling with the below-target band distribution. Until the engine stops
   * recommending works on already-compliant homes, exclude any home whose
   * EFFECTIVE band already meets the target from the upgrade aggregate. Bands
   * compare lexically (A best), so "still needs work" = effective band > target.
   * Unknown/NULL effective band is kept (can't prove compliant). Only bites for
   * Increasing-EPC scenarios; NULL targetBand ⇒ no-op.
   */
  const stillNeedsUpgradeSql = sql`(
    ${targetBand}::text IS NULL
    OR (${effectiveEpcBandSql}) IS NULL
    OR (${effectiveEpcBandSql})::text > ${targetBand}::text
  )`;

  /* ----------------------------------------------------------
     The three data queries below are independent — they only need `minSap`
     (from the scenario definition above), not each other's results — so they
     run in one parallel wave instead of four serial round-trips. Query 2 folds
     the old per-property EPC-distribution scan into its own aggregate.
  ---------------------------------------------------------- */

  /* ----------------------------------------------------------
     QUERY 1 — Scenario metrics (PLANS ONLY)
  ---------------------------------------------------------- */
  const scenarioMetricsSql = sql`
    WITH latest_plans AS (
      SELECT DISTINCT ON (property_id)
        *
      FROM plan
      WHERE portfolio_id = ${pid}
        AND scenario_id = ${sid}
        AND (
          ${hideNonCompliant} = false
          OR (
            ${minSap}::float IS NOT NULL
            AND post_sap_points >= ${minSap}::float
          )
        )
      ORDER BY property_id, created_at DESC
    )
    SELECT
      COUNT(*)::int AS n_units,
      AVG(post_sap_points)::float AS avg_sap,
      AVG(post_co2_emissions)::float AS avg_carbon,
      AVG(post_energy_bill)::float AS avg_bills,
      SUM(post_co2_emissions)::float AS total_carbon,
      SUM(post_energy_bill)::float AS total_bills,
      SUM(
        CASE
          -- TEMP (demo): baseline is the effective SAP, not p.current_sap_points
          -- (NULL for new-approach → uplift summed to 0 → £/SAP showed 0). Count
          -- genuine gains only (post > baseline); sub-baseline plans excluded.
          WHEN cost_of_works > 0
            AND post_sap_points > (${effectiveSapSql})
          THEN post_sap_points - (${effectiveSapSql})
          ELSE 0
        END
      )::float AS total_sap_uplift
    FROM latest_plans lp
    JOIN property p ON p.id = lp.property_id
    LEFT JOIN property_baseline_performance bp ON bp.property_id = p.id
    LEFT JOIN epc_property epl ON epl.property_id = p.id AND epl.source = 'lodged'
    -- Scope the legacy (being-phased-out) table to this portfolio so its
    -- (property_id, portfolio_id) index is used — without the portfolio filter
    -- Postgres seq-scans the whole 600k-row table on every overlay. Migrated
    -- portfolios have ~0 rows here, so the join becomes a near-empty lookup.
    LEFT JOIN property_details_epc e ON e.property_id = p.id AND e.portfolio_id = ${pid}
    -- Conditional filter: only restrict by the lodged baseline when the toggle is on
    -- AND the scenario has an EPC target. Written as an OR chain so Postgres evaluates
    -- it as a single WHERE clause — avoiding the need to dynamically build the query
    -- string in application code (which would require string concatenation and risks
    -- SQL injection). The OR short-circuits left-to-right: if the first or second
    -- condition is true, the third is never evaluated, so all rows pass through.
    WHERE ${tagScope}
    AND (
      ${useLodgedBaseline} = false      -- toggle off → include everything
      OR ${minSap}::float IS NULL         -- no EPC target → nothing to filter on
      OR COALESCE((${lodgedSapSql}) < ${minSap}::float, true)  -- actual filter; unknown lodged → keep
    )
    AND ${passesComplianceWindowSql};
  `;

  /* ----------------------------------------------------------
     QUERY 1b — Upgrade costs (PLANS ONLY)
  ---------------------------------------------------------- */
  const upgradedCostsSql = sql`
    WITH latest_plans AS (
      SELECT DISTINCT ON (property_id)
        *
      FROM plan
      WHERE portfolio_id = ${pid}
        AND scenario_id = ${sid}
        AND (
          ${hideNonCompliant} = false
          OR (
            ${minSap}::float IS NOT NULL
            AND post_sap_points >= ${minSap}::float
          )
        )
      ORDER BY property_id, created_at DESC
    )
    SELECT
      COUNT(*)::int AS n_units_upgraded,
      SUM(cost_of_works)::float AS total_cost,
      SUM(contingency_cost)::float AS contingency,
      SUM(
        COALESCE(fp.project_funding, 0) +
        COALESCE(fp.total_uplift, 0)
      )::float AS total_funding
    FROM latest_plans lp
    JOIN property p ON p.id = lp.property_id
    LEFT JOIN property_baseline_performance bp ON bp.property_id = p.id
    LEFT JOIN epc_property epl ON epl.property_id = p.id AND epl.source = 'lodged'
    -- Portfolio-scoped legacy join (see Query 1) — index lookup, not seq scan.
    LEFT JOIN property_details_epc e ON e.property_id = p.id AND e.portfolio_id = ${pid}
    LEFT JOIN funding_package fp ON fp.plan_id = lp.id
    WHERE ${tagScope}
      AND lp.cost_of_works > 0
      -- TEMP (demo): exclude plans whose post SAP is below the effective baseline
      -- (target-level post_sap on already-compliant homes) — not real upgrades.
      -- COALESCE keeps rows we can't compare. See ADR-0002.
      AND COALESCE(lp.post_sap_points >= (${effectiveSapSql}), true)
      -- INTERIM: drop homes already at the scenario target band (backend bug —
      -- see stillNeedsUpgradeSql above, Hestia-Homes/Model#1652).
      AND ${stillNeedsUpgradeSql}
      AND (
        ${useLodgedBaseline} = false
        OR ${minSap}::float IS NULL
        OR COALESCE((${lodgedSapSql}) < ${minSap}::float, true)
      )
      AND ${passesComplianceWindowSql};
  `;

  /* ----------------------------------------------------------
     QUERY 2 — Portfolio AFTER scenario (ALL properties)
     Folds the former EPC-distribution scan (Query 3) in: the same
     per-property LATERAL pass yields the average/total aggregates AND the
     band buckets (band_sap → BAND_BUCKETS_SQL), so it's one scan, not two,
     and no per-property rows cross the wire.
  ---------------------------------------------------------- */
  const portfolioMetricsSql = sql`
    SELECT
      AVG(effective_sap)::float AS avg_sap,
      AVG(effective_carbon)::float AS avg_carbon,
      AVG(effective_bills)::float AS avg_bills,
      SUM(effective_carbon)::float AS total_carbon,
      SUM(effective_bills)::float AS total_bills,
      ${BAND_BUCKETS_SQL}
    FROM (
      SELECT
        /* ---------- SAP ---------- */
        CASE
          WHEN lp.id IS NOT NULL THEN lp.post_sap_points
          ELSE p.current_sap_points
        END AS effective_sap,

        /* ---------- Carbon ---------- */
        CASE
          WHEN lp.id IS NOT NULL THEN lp.post_co2_emissions
          ELSE ${carbonSql(sql`e`)}
        END AS effective_carbon,

        /* ---------- Bills ---------- */
        CASE
          WHEN lp.id IS NOT NULL THEN lp.post_energy_bill
          ELSE ${billsSql(sql`e`)}
        END AS effective_bills,

        /* ---------- Band SAP (EPC distribution) ---------- */
        CASE
          -- A retrofit scenario can't make a property worse. The engine writes a
          -- target-level post_sap (e.g. ~C) even for properties already above the
          -- target, so post_sap can sit BELOW the baseline — which made the chart
          -- show e.g. B properties "improving" down to C. Clamp to the baseline.
          WHEN lp.id IS NOT NULL THEN GREATEST(${effectiveSapSql}, lp.post_sap_points)
          -- No qualifying plan → unchanged property. Use the effective
          -- (re-baselined) baseline to match the "before" distribution; NOT
          -- p.current_sap_points (NULL for new-approach → "Unknown"). See ADR-0002.
          ELSE ${effectiveSapSql}
        END AS band_sap

      FROM property p
      -- Portfolio-scoped legacy join (see Query 1) — index lookup, not seq scan.
      LEFT JOIN property_details_epc e
        ON e.property_id = p.id AND e.portfolio_id = ${pid}
      ${newApproachJoins}

      LEFT JOIN LATERAL (
        SELECT *
        FROM plan
        WHERE plan.property_id = p.id
          AND plan.portfolio_id = ${pid}
          AND plan.scenario_id = ${sid}
          AND (
            ${hideNonCompliant} = false
            OR (
              ${minSap}::float IS NOT NULL
              AND plan.post_sap_points >= ${minSap}::float
            )
          )
          AND (
            ${useLodgedBaseline} = false
            OR ${minSap}::float IS NULL
            OR COALESCE((${lodgedSapSql}) < ${minSap}::float, true)
          )
          AND ${passesComplianceWindowSql}
        ORDER BY created_at DESC
        LIMIT 1
      ) lp ON true

      WHERE p.portfolio_id = ${pid} AND ${tagScope}
    ) q;
  `;

  // One parallel wave: scenario metrics, upgrade costs, portfolio-after.
  const [scenarioMetricsResult, upgradedResult, portfolioMetricsResult] =
    await Promise.all([
      db.execute(scenarioMetricsSql),
      db.execute(upgradedCostsSql),
      db.execute(portfolioMetricsSql),
    ]);

  const scenarioAgg = scenarioMetricsResult.rows[0] as ScenarioAggregates;
  const upgraded = upgradedResult.rows[0] as UpgradedAggregates;
  const portfolioAgg = portfolioMetricsResult.rows[0] as PortfolioAggregates;

  const scenario_epc_counts: Record<string, number> = {
    A: portfolioAgg.band_a,
    B: portfolioAgg.band_b,
    C: portfolioAgg.band_c,
    D: portfolioAgg.band_d,
    E: portfolioAgg.band_e,
    F: portfolioAgg.band_f,
    G: portfolioAgg.band_g,
    Unknown: portfolioAgg.band_unknown,
  };

  /* ----------------------------------------------------------
     RESPONSE
  ---------------------------------------------------------- */

  const nUpgraded = upgraded.n_units_upgraded ?? 0;
  const ledger = computeLedger({
    constructionCost: upgraded.total_cost ?? 0,
    contingency: upgraded.contingency ?? 0,
    funding: upgraded.total_funding ?? 0,
    homesUpgraded: nUpgraded,
  });

  return NextResponse.json({
    /* -------- portfolio-after-scenario -------- */
    avg_sap:
      portfolioAgg.avg_sap !== null
        ? Number(portfolioAgg.avg_sap).toFixed(1)
        : null,
    avg_carbon: portfolioAgg.avg_carbon,
    avg_bills: portfolioAgg.avg_bills,
    total_carbon: portfolioAgg.total_carbon,
    total_bills: portfolioAgg.total_bills,

    /* -------- scenario-only (canonical ledger per CONTEXT.md) -------- */
    n_units: scenarioAgg.n_units,
    n_units_upgraded: nUpgraded,
    construction_cost: ledger.constructionCost,
    contingency: ledger.contingency,
    total_funding: ledger.funding,
    gross_cost: ledger.grossCost,
    net_cost: ledger.netCost,
    total_sap_uplift: scenarioAgg.total_sap_uplift ?? 0,
    gross_per_unit: ledger.grossPerHome,

    /* -------- shared -------- */
    scenario_epc_counts,
    pc_cost: ledger.projectDelivery,
  });
}
