import { db } from "@/app/db/db";
import { sql } from "drizzle-orm";
import { NextRequest, NextResponse } from "next/server";
import { sapToEpc } from "@/app/utils";
import type { PortfolioGoalType } from "@/app/db/schema/portfolio";

/* =======================
   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;
};

type EpcRow = {
  effective_sap: number | null;
};

/* =======================
   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";
  const useOriginalBaseline =
    request.nextUrl.searchParams.get("useOriginalBaseline") === "true";

  /* ----------------------------------------------------------
     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;

  /* ----------------------------------------------------------
     QUERY 1 — Scenario metrics (PLANS ONLY)
  ---------------------------------------------------------- */
  const scenarioMetricsResult = await db.execute(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
          WHEN cost_of_works > 0
            AND post_sap_points IS NOT NULL
          THEN post_sap_points - p.current_sap_points
          ELSE 0
        END
      )::float AS total_sap_uplift
    FROM latest_plans lp
    JOIN property p ON p.id = lp.property_id
    -- Conditional filter: only restrict by original_sap_points 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 (
      ${useOriginalBaseline} = false      -- toggle off → include everything
      OR ${minSap}::float IS NULL         -- no EPC target → nothing to filter on
      OR p.original_sap_points < ${minSap}::float  -- actual filter
    );
  `);

  const scenarioAgg = scenarioMetricsResult.rows[0] as ScenarioAggregates;

  /* ----------------------------------------------------------
     QUERY 1b — Upgrade costs (PLANS ONLY)
  ---------------------------------------------------------- */
  const upgradedResult = await db.execute(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 funding_package fp ON fp.plan_id = lp.id
    WHERE lp.cost_of_works > 0
      AND (
        ${useOriginalBaseline} = false
        OR ${minSap}::float IS NULL
        OR p.original_sap_points < ${minSap}::float
      );
  `);

  const upgraded = upgradedResult.rows[0] as UpgradedAggregates;

  /* ----------------------------------------------------------
     QUERY 2 — Portfolio AFTER scenario (ALL properties)
  ---------------------------------------------------------- */
  const portfolioMetricsResult = await db.execute(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
    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 e.co2_emissions
        END AS effective_carbon,

        /* ---------- Bills ---------- */
        CASE
          WHEN lp.id IS NOT NULL THEN lp.post_energy_bill
          ELSE (
            e.heating_cost_current +
            e.hot_water_cost_current +
            e.lighting_cost_current +
            e.appliances_cost_current +
            e.gas_standing_charge +
            e.electricity_standing_charge -
            COALESCE(e.installed_measures_total_energy_bill_adjustment, 0)
          )
        END AS effective_bills

      FROM property p
      LEFT JOIN property_details_epc e
        ON e.property_id = p.id

      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 (
            ${useOriginalBaseline} = false
            OR ${minSap}::float IS NULL
            OR p.original_sap_points < ${minSap}::float
          )
        ORDER BY created_at DESC
        LIMIT 1
      ) lp ON true

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

  const portfolioAgg = portfolioMetricsResult.rows[0] as PortfolioAggregates;

  /* ----------------------------------------------------------
     QUERY 3 — EPC band distribution (ALL properties)
  ---------------------------------------------------------- */
  const epcRows = await db.execute(sql`
    SELECT
      CASE
        WHEN lp.id IS NOT NULL THEN lp.post_sap_points
        ELSE p.current_sap_points
      END AS effective_sap
    FROM property p
    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 (
          ${useOriginalBaseline} = false
          OR ${minSap}::float IS NULL
          OR p.original_sap_points < ${minSap}::float
        )
      ORDER BY created_at DESC
      LIMIT 1
    ) lp ON true
    WHERE p.portfolio_id = ${pid};
  `);

  const scenario_epc_counts: Record<string, number> = {
    A: 0,
    B: 0,
    C: 0,
    D: 0,
    E: 0,
    F: 0,
    G: 0,
    Unknown: 0,
  };

  for (const row of epcRows.rows as EpcRow[]) {
    const band = sapToEpc(row.effective_sap);
    scenario_epc_counts[band] += 1;
  }

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

  const constructionCost = upgraded.total_cost ?? 0;
  const nUpgraded = upgraded.n_units_upgraded ?? 0;
  const pc_cost = constructionCost * 0.3;

  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: constructionCost,
    contingency: upgraded.contingency ?? 0,
    total_funding: upgraded.total_funding ?? 0,
    net_cost: constructionCost - (upgraded.total_funding ?? 0),
    total_sap_uplift: scenarioAgg.total_sap_uplift ?? 0,
    gross_per_unit:
      nUpgraded > 0 ? (constructionCost + pc_cost) / nUpgraded : 0,

    /* -------- shared -------- */
    scenario_epc_counts,
    pc_cost,
  });
}
