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,
} from "@/lib/services/epcSources";
import { computeLedger } from "@/lib/reporting/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 rather than shipped one row per property.
  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
 * 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 }> },
) {
  const { portfolioId } = await props.params;

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

  const pid = BigInt(portfolioId);
  const hideNonCompliant =
    request.nextUrl.searchParams.get("hideNonCompliant") === "true";

  // Tag filter (view-wide): scope every property scan to the tagged subset.
  const tags = parseReportingViewState(request.nextUrl.searchParams).tags;
  const tagScope = tagFilterCondition(tags);

  /* ----------------------------------------------------------
     The three data queries below are independent (they share no results), so
     they run in one parallel wave. 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 is_default = true
      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 "downgrade" plans
          -- are excluded rather than dragging the uplift negative. See ADR-0002.
          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
    WHERE ${tagScope};
  `;

  /* ----------------------------------------------------------
     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 is_default = true
      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 funding_package fp ON fp.plan_id = lp.id
    WHERE ${tagScope}
      AND lp.cost_of_works > 0
      -- TEMP (demo): a plan whose post SAP is below the effective baseline isn't
      -- a real upgrade (target-level post_sap on already-compliant homes), so
      -- exclude it from the count + cost. COALESCE keeps rows we can't compare
      -- (NULL post or NULL baseline). See ADR-0002.
      AND COALESCE(lp.post_sap_points >= (${effectiveSapSql}), true);
  `;

  /* ----------------------------------------------------------
     QUERY 2 — Portfolio AFTER scenario (ALL properties)
     Folds the former EPC-distribution scan (Query 3) in: one per-property
     LATERAL pass yields both the aggregates and the band buckets.
  ---------------------------------------------------------- */
  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 so
          -- the post-scenario band is never worse than before.
          WHEN lp.id IS NOT NULL THEN GREATEST(${effectiveSapSql}, lp.post_sap_points)
          -- No plan → unchanged property. Use the effective (re-baselined) baseline
          -- so this matches the "before" distribution — NOT p.current_sap_points,
          -- which is NULL for new-approach properties. See ADR-0002.
          ELSE ${effectiveSapSql}
        END AS band_sap

      FROM property p
      -- Scope the legacy (being-phased-out) table to this portfolio so its
      -- (property_id, portfolio_id) index is used instead of a full seq scan of
      -- the whole 600k-row table; migrated portfolios have ~0 rows here.
      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.is_default = true
        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 constructionCost = upgraded.total_cost ?? 0;
  const nUpgraded = upgraded.n_units_upgraded ?? 0;

  // Same ledger maths as the [scenarioId] route: gross = construction +
  // project delivery + contingency; net = gross − funding; per-home over gross.
  const ledger = computeLedger({
    constructionCost,
    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 -------- */
    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,
  });
}
