import { db } from "@/app/db/db";
import { sql, eq } from "drizzle-orm";
import { scenario } from "@/app/db/schema/recommendations";

import type {
  AverageMetrics,
  AgeBandCount,
  EpcBandCount,
  EstimatedCounts,
  PropertyTypeCount,
  BaselineMetrics,
  TotalMetrics,
} from "./types";

export async function getPortfolioCounts(portfolioId: number): Promise<number> {
  const result = await db.execute<{ total: number }>(sql`
    SELECT COUNT(*)::int AS total
    FROM property
    WHERE portfolio_id = ${portfolioId};
  `);

  return result.rows[0].total;
}

export async function getAverages(
  portfolioId: number,
): Promise<AverageMetrics> {
  const result = await db.execute<AverageMetrics>(sql`
    SELECT
      AVG(p.current_sap_points)::float AS avg_sap,
      AVG(e.co2_emissions)::float AS avg_carbon,
      AVG(
        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)
      )::float AS avg_bills,
      AVG(e.primary_energy_consumption)::float AS avg_energy_consumption
    FROM property p
    LEFT JOIN property_details_epc e ON e.property_id = p.id
    WHERE p.portfolio_id = ${portfolioId};
  `);

  return result.rows[0];
}

export async function getTotals(portfolioId: number): Promise<TotalMetrics> {
  const result = await db.execute<TotalMetrics>(sql`
    SELECT
      SUM(e.co2_emissions)::float AS total_carbon,
      SUM(
        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)
      )::float AS total_bills
    FROM property p
    LEFT JOIN property_details_epc e ON e.property_id = p.id
    WHERE p.portfolio_id = ${portfolioId};
  `);

  return result.rows[0];
}

export async function getCountByAgeBand(
  portfolioId: number,
): Promise<AgeBandCount[]> {
  const result = await db.execute<AgeBandCount>(sql`
    SELECT
      CASE
        WHEN year_built ~ '^[0-9]+$' THEN
          CASE
            WHEN CAST(year_built AS int) < 1900 THEN '<1900'
            WHEN CAST(year_built AS int) BETWEEN 1900 AND 1929 THEN '1900–1929'
            WHEN CAST(year_built AS int) BETWEEN 1930 AND 1949 THEN '1930–1949'
            WHEN CAST(year_built AS int) BETWEEN 1950 AND 1975 THEN '1950–1975'
            WHEN CAST(year_built AS int) BETWEEN 1976 AND 1999 THEN '1976–1999'
            ELSE '2000+'
          END
        ELSE 'Unknown'
      END AS age_band,
      COUNT(*)::int AS count
    FROM property
    WHERE portfolio_id = ${portfolioId}
    GROUP BY age_band
    ORDER BY age_band;
  `);

  return result.rows;
}

export async function getCountByEpcBand(
  portfolioId: number,
): Promise<EpcBandCount[]> {
  const result = await db.execute<EpcBandCount>(sql`
    SELECT *
    FROM (
      SELECT
        COALESCE(p.current_epc_rating::text, 'Unknown') AS epc,
        COUNT(*) FILTER (
          WHERE e.estimated = false OR e.estimated IS NULL
        )::int AS actual,
        COUNT(*) FILTER (
          WHERE e.estimated = true
        )::int AS estimated
      FROM property p
      LEFT JOIN property_details_epc e
        ON e.property_id = p.id
      WHERE p.portfolio_id = ${portfolioId}
      GROUP BY epc
    ) q
    ORDER BY
      CASE
        WHEN q.epc = 'A' THEN 1
        WHEN q.epc = 'B' THEN 2
        WHEN q.epc = 'C' THEN 3
        WHEN q.epc = 'D' THEN 4
        WHEN q.epc = 'E' THEN 5
        WHEN q.epc = 'F' THEN 6
        WHEN q.epc = 'G' THEN 7
        ELSE 8
      END;
  `);

  return result.rows;
}

export async function getEstimatedCounts(
  portfolioId: number,
): Promise<EstimatedCounts> {
  const result = await db.execute<EstimatedCounts>(sql`
    SELECT
      SUM(CASE WHEN e.estimated = true THEN 1 ELSE 0 END)::int AS estimated,
      SUM(CASE WHEN e.estimated = false THEN 1 ELSE 0 END)::int AS actual
    FROM property_details_epc e
    WHERE e.portfolio_id = ${portfolioId};
  `);

  return result.rows[0];
}

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

  return result.rows;
}

export async function getExpiredEpcCount(portfolioId: number): Promise<number> {
  const result = await db.execute<{ expired: number }>(sql`
    SELECT
      SUM(
        CASE 
          WHEN is_expired = true AND estimated = false 
          THEN 1 
          ELSE 0 
        END
      )::int AS expired
    FROM property_details_epc
    WHERE portfolio_id = ${portfolioId};
  `);

  return result.rows[0].expired;
}

export async function getLikelyDowngrades(
  portfolioId: number,
): 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 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,
): Promise<BaselineMetrics> {
  const [
    total,
    averages,
    totals,
    ageBands,
    epcBands,
    estimatedCounts,
    expiredEpcs,
    likelyDowngrades,
  ] = await Promise.all([
    getPortfolioCounts(portfolioId),
    getAverages(portfolioId),
    getTotals(portfolioId),
    getCountByAgeBand(portfolioId),
    getCountByEpcBand(portfolioId),
    getEstimatedCounts(portfolioId),
    getExpiredEpcCount(portfolioId),
    getLikelyDowngrades(portfolioId),
  ]);

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

export async function getScenarios(portfolioId: number) {
  const rows = await db
    .select()
    .from(scenario)
    .where(eq(scenario.portfolioId, BigInt(portfolioId)));

  // Normalise response (only return what we need right now)
  return rows.map((s) => ({
    id: Number(s.id),
    name: s.name ?? `Scenario ${s.id}`,
    // we will compute the performance metrics in another function
  }));
}
