import { db } from "@/app/db/db";
import { sql } from "drizzle-orm";
import {
  NEW_APPROACH_CUTOFF,
  newApproachJoins,
  carbonSql,
  billsSql,
  energyConsumptionSql,
  estimatedSql,
  isExpiredSql,
  effectiveSapSql,
  effectiveEpcBandSql,
  lodgedEpcBandSql,
  propertyTypeSql,
  constructionYearSql,
} from "@/lib/services/epcSources";

import type {
  AverageMetrics,
  AgeBandCount,
  EpcBandCount,
  EstimatedCounts,
  PropertyTypeCount,
  BaselineMetrics,
  TotalMetrics,
} from "./types";
import {
  toReportingScenario,
  type ReportingScenario,
} from "./reportingScenarios";
import { tagFilterCondition } from "@/lib/reporting/tagFilterSql";
import { emptyTagFilter, type TagFilter } from "@/lib/reporting/viewState";

export type { ReportingScenario };

/** Bands the single-pass baseline query buckets into, in display order. */
const BASELINE_EPC_BANDS = [
  "A",
  "B",
  "C",
  "D",
  "E",
  "F",
  "G",
  "Unknown",
] as const;

/**
 * One-pass baseline aggregate. Total, averages, totals, EPC-band distribution,
 * estimated/actual split and expired count all share the *identical*
 * `property ⋈ property_details_epc ⋈ newApproachJoins` scan, so they collapse
 * into a single conditional-aggregation query rather than six separate full
 * scans of the same portfolio — which was the bulk of the reporting page's
 * load time on large portfolios. Age bands (correlated subqueries) and likely
 * downgrades (a different, legacy-only join) stay separate and run in parallel.
 */
export async function getBaselineAggregates(
  portfolioId: number,
  tags: TagFilter = emptyTagFilter(),
): Promise<{
  total: number;
  averages: AverageMetrics;
  totals: TotalMetrics;
  epcBands: EpcBandCount[];
  estimatedCounts: EstimatedCounts;
  expiredEpcs: number;
}> {
  const est = estimatedSql(sql`e`);
  const bandExpr = sql`COALESCE((${effectiveEpcBandSql})::text, 'Unknown')`;

  // Per-band actual/estimated counts as FILTER columns — replaces the GROUP BY
  // in the old getCountByEpcBand. Aliases derive from the constant band list.
  const bandCols = sql.join(
    BASELINE_EPC_BANDS.flatMap((b) => {
      const k = b.toLowerCase();
      return [
        sql`COUNT(*) FILTER (WHERE ${bandExpr} = ${b} AND ${est} = false)::int AS ${sql.raw(`epc_${k}_actual`)}`,
        sql`COUNT(*) FILTER (WHERE ${bandExpr} = ${b} AND ${est} = true)::int AS ${sql.raw(`epc_${k}_estimated`)}`,
      ];
    }),
    sql`, `,
  );

  const result = await db.execute<Record<string, number | null>>(sql`
    SELECT
      COUNT(*)::int AS total,
      AVG(${effectiveSapSql})::float AS avg_sap,
      AVG(${carbonSql(sql`e`)})::float AS avg_carbon,
      AVG(${billsSql(sql`e`)})::float AS avg_bills,
      AVG(${energyConsumptionSql(sql`e`)})::float AS avg_energy_consumption,
      SUM(${carbonSql(sql`e`)})::float AS total_carbon,
      SUM(${billsSql(sql`e`)})::float AS total_bills,
      SUM(CASE WHEN ${est} = true THEN 1 ELSE 0 END)::int AS estimated,
      SUM(CASE WHEN ${est} = false THEN 1 ELSE 0 END)::int AS actual,
      SUM(
        CASE
          WHEN ${isExpiredSql(sql`e`)} = true AND ${est} = false THEN 1
          ELSE 0
        END
      )::int AS expired,
      ${bandCols}
    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 = ${portfolioId}
    ${newApproachJoins}
    WHERE p.portfolio_id = ${portfolioId} AND ${tagFilterCondition(tags)};
  `);

  const row = result.rows[0];

  // Omit zero-count bands to mirror the old GROUP BY (which only emitted bands
  // that were present); the ladder + toBandCounts both tolerate the absence.
  const epcBands: EpcBandCount[] = BASELINE_EPC_BANDS.map((b) => {
    const k = b.toLowerCase();
    return {
      epc: b,
      actual: Number(row[`epc_${k}_actual`] ?? 0),
      estimated: Number(row[`epc_${k}_estimated`] ?? 0),
    };
  }).filter((band) => band.actual + band.estimated > 0);

  return {
    total: Number(row.total ?? 0),
    averages: {
      avg_sap: row.avg_sap as number | null,
      avg_carbon: row.avg_carbon as number | null,
      avg_bills: row.avg_bills as number | null,
      avg_energy_consumption: row.avg_energy_consumption as number | null,
    },
    totals: {
      total_carbon: row.total_carbon as number | null,
      total_bills: row.total_bills as number | null,
    },
    epcBands,
    estimatedCounts: {
      estimated: Number(row.estimated ?? 0),
      actual: Number(row.actual ?? 0),
    },
    expiredEpcs: Number(row.expired ?? 0),
  };
}

export async function getCountByAgeBand(
  portfolioId: number,
  tags: TagFilter = emptyTagFilter(),
): Promise<AgeBandCount[]> {
  const result = await db.execute<AgeBandCount>(sql`
    SELECT
      CASE
        WHEN q.built_year < 1900 THEN '<1900'
        WHEN q.built_year BETWEEN 1900 AND 1929 THEN '1900–1929'
        WHEN q.built_year BETWEEN 1930 AND 1949 THEN '1930–1949'
        WHEN q.built_year BETWEEN 1950 AND 1975 THEN '1950–1975'
        WHEN q.built_year BETWEEN 1976 AND 1999 THEN '1976–1999'
        WHEN q.built_year >= 2000 THEN '2000+'
        ELSE 'Unknown'
      END AS age_band,
      COUNT(*)::int AS count
    FROM (
      SELECT ${constructionYearSql} AS built_year
      FROM property p
      ${newApproachJoins}
      WHERE p.portfolio_id = ${portfolioId} AND ${tagFilterCondition(tags)}
    ) q
    GROUP BY age_band
    ORDER BY age_band;
  `);

  return result.rows;
}

export async function getCountByPropertyType(
  portfolioId: number,
  tags: TagFilter = emptyTagFilter(),
): Promise<PropertyTypeCount[]> {
  const result = await db.execute<PropertyTypeCount>(sql`
    SELECT ${propertyTypeSql} AS type, COUNT(*)::int AS count
    FROM property p
    ${newApproachJoins}
    WHERE p.portfolio_id = ${portfolioId} AND ${tagFilterCondition(tags)}
    GROUP BY type
    ORDER BY count DESC;
  `);

  return result.rows;
}

export async function getLikelyDowngrades(
  portfolioId: number,
  tags: TagFilter = emptyTagFilter(),
): Promise<number> {
  const result = await db.execute<{ downgrades: number }>(sql`
    SELECT
      COUNT(*)::int AS downgrades
    FROM property p
    JOIN property_details_epc e
      ON e.property_id = p.id
    WHERE p.portfolio_id = ${portfolioId} AND ${tagFilterCondition(tags)}
      -- GAP: the SAP-05 "likely downgrade" signal has no equivalent in the new
      -- pipeline (handled there via Rebaseline). Restrict to legacy properties.
      AND p.updated_at < ${NEW_APPROACH_CUTOFF}::date
      AND e.sap_05_overwritten = true
      AND p.current_sap_points IS NOT NULL
      AND e.sap_05_score IS NOT NULL
      AND p.current_sap_points < e.sap_05_score;
  `);

  return result.rows[0].downgrades;
}

export async function loadBaselineMetrics(
  portfolioId: number,
  tags: TagFilter = emptyTagFilter(),
): Promise<BaselineMetrics> {
  // Three parallel scans, not eight: the single-pass aggregate carries total,
  // averages, totals, EPC bands, estimated split and expired count; age bands
  // and likely downgrades keep their own (differently-shaped) queries.
  const [aggregates, ageBands, likelyDowngrades] = await Promise.all([
    getBaselineAggregates(portfolioId, tags),
    getCountByAgeBand(portfolioId, tags),
    getLikelyDowngrades(portfolioId, tags),
  ]);

  return {
    total: aggregates.total,
    averages: aggregates.averages,
    totals: aggregates.totals,
    ageBands,
    epcBands: aggregates.epcBands,
    estimatedCounts: aggregates.estimatedCounts,
    expiredEpcs: aggregates.expiredEpcs,
    likelyDowngrades,
  };
}

/**
 * Scenarios for the reporting combobox: config plus derived modelled state
 * (ADR-0003). Deliberately lighter than listScenariosWithStatus — it derives
 * "modelled" from whether a scenario appears in `plan` at all
 * (DISTINCT scenario_id, an index-only scan on idx_plan_portfolio_scenario),
 * NOT COUNT(DISTINCT property_id) per scenario, which scans and de-dupes every
 * plan row and timed out the page on very large portfolios. Reporting only
 * needs the boolean, not the count.
 */
export async function getReportingScenarios(
  portfolioId: number,
): Promise<ReportingScenario[]> {
  const pid = BigInt(portfolioId);
  const [configRes, modelledRes] = await Promise.all([
    db.execute<{
      id: string;
      name: string | null;
      goal: string;
      goal_value: string | null;
      budget: number | null;
      housing_type: string;
      created_at: string | Date;
    }>(sql`
      SELECT id, name, goal, goal_value, budget, housing_type, created_at
      FROM scenario
      WHERE portfolio_id = ${pid}
      ORDER BY created_at DESC, id DESC
    `),
    db.execute<{ scenario_id: string }>(sql`
      SELECT DISTINCT scenario_id
      FROM plan
      WHERE portfolio_id = ${pid} AND scenario_id IS NOT NULL
    `),
  ]);

  const modelled = new Set(modelledRes.rows.map((r) => String(r.scenario_id)));

  return configRes.rows.map((s) => toReportingScenario(s, modelled));
}

/** The portfolio's own goal — drives the current-stock goal callout. */
export async function getPortfolioGoal(portfolioId: number): Promise<string> {
  const result = await db.execute<{ goal: string }>(sql`
    SELECT goal FROM portfolio WHERE id = ${portfolioId} LIMIT 1;
  `);
  return result.rows[0]?.goal ?? "None";
}

export interface ScenarioConfig {
  name: string | null;
  goal: string;
  goalValue: string | null;
  budget: number | null;
  housingType: string;
  exclusions: string[];
  fabricFirst: boolean;
}

/** `scenario.exclusions` is a text column holding a `{a, b}`-style literal. */
function parseExclusions(raw: string | null): string[] {
  if (!raw) return [];
  const inner = raw.replace(/^\{|\}$/g, "").trim();
  if (!inner) return [];
  return inner
    .split(",")
    .map((s) => s.trim().replace(/^"|"$/g, ""))
    .filter(Boolean);
}

/** The immutable configuration of a single scenario — for the report brief. */
export async function getScenarioConfig(
  scenarioId: number,
): Promise<ScenarioConfig | null> {
  const result = await db.execute<{
    name: string | null;
    goal: string;
    goal_value: string | null;
    budget: number | null;
    housing_type: string;
    exclusions: string | null;
    fabric_first: boolean;
  }>(sql`
    SELECT name, goal, goal_value, budget, housing_type, exclusions, fabric_first
    FROM scenario
    WHERE id = ${BigInt(scenarioId)}
    LIMIT 1;
  `);
  const row = result.rows[0];
  if (!row) return null;
  return {
    name: row.name,
    goal: row.goal,
    goalValue: row.goal_value,
    budget: row.budget,
    housingType: row.housing_type,
    exclusions: parseExclusions(row.exclusions),
    fabricFirst: row.fabric_first,
  };
}

export type DataQualityMetrics = {
  total: number;
  inDate: number;
  expired: number;
  estimated: number;
  likelyDowngrades: number;
  likelyUpgrades: number;
};

/**
 * Evidence composition + band-movement counts for the data-quality page
 * (Screen E). Every home is counted once by strongest evidence
 * (in-date lodged › expired › estimated). Likely downgrade/upgrade follow
 * the CONTEXT.md band-movement definitions (lodged band vs effective band;
 * real certificates only — lodgedEpcBandSql is NULL for predicted homes).
 * Band letters compare lexically, so a lodged band that is a *better*
 * letter than effective (lodged < effective) is a likely downgrade.
 */
export async function getDataQualityMetrics(
  portfolioId: number,
): Promise<DataQualityMetrics> {
  const result = await db.execute<DataQualityMetrics>(sql`
    SELECT
      COUNT(*)::int AS total,
      SUM(CASE
        WHEN ${estimatedSql(sql`e`)} = false AND ${isExpiredSql(sql`e`)} = false
        THEN 1 ELSE 0 END)::int AS "inDate",
      SUM(CASE
        WHEN ${estimatedSql(sql`e`)} = false AND ${isExpiredSql(sql`e`)} = true
        THEN 1 ELSE 0 END)::int AS expired,
      SUM(CASE WHEN ${estimatedSql(sql`e`)} = true THEN 1 ELSE 0 END)::int AS estimated,
      SUM(CASE
        WHEN (${lodgedEpcBandSql}) IS NOT NULL
          AND (${effectiveEpcBandSql}) IS NOT NULL
          AND (${lodgedEpcBandSql}) < (${effectiveEpcBandSql})
        THEN 1 ELSE 0 END)::int AS "likelyDowngrades",
      SUM(CASE
        WHEN (${lodgedEpcBandSql}) IS NOT NULL
          AND (${effectiveEpcBandSql}) IS NOT NULL
          AND (${lodgedEpcBandSql}) > (${effectiveEpcBandSql})
        THEN 1 ELSE 0 END)::int AS "likelyUpgrades"
    FROM property p
    -- Portfolio-scoped legacy join (see getBaselineAggregates) — index, not seq scan.
    LEFT JOIN property_details_epc e ON e.property_id = p.id AND e.portfolio_id = ${portfolioId}
    ${newApproachJoins}
    WHERE p.portfolio_id = ${portfolioId};
  `);

  return result.rows[0];
}
