import { db } from "@/app/db/db";
import { sql } from "drizzle-orm";
import { DOMNA_COST_MAP } from "@/app/domna/financials";
import { PlanTypeEnum } from "@/app/db/schema/recommendations";

export interface PlanWithTotals extends Record<string, unknown> {
  planId: string;
  planType: string | null;
  planName: string | null;
  createdAt: string;
  propertyId: number;
  landlordPropertyId: string | null;
  address: string | null;
  postcode: string | null;
  currentSapPoints: number | null;
  currentEpcRating: string | null;
  fundingScheme: string | null;
  totalFunding: number | null;
  totalUplift: number | null;
  totalCarbonSavings: number | null;
  totalBillSavings: number | null;
  totalRecommendationCost?: number | null;
  surveyCost?: number;
  clientContribution?: number;
  totalRecommendationSapPoints: number | null;
  averageSapPost?: number;
}

export async function getPlansWithTotals(
  portfolioId: string
): Promise<PlanWithTotals[]> {
  const result = await db.execute<PlanWithTotals>(sql`
    SELECT
      pl.id AS "planId",
      pl.plan_type AS "planType",
      pl.name AS "planName",
      pl.created_at AS "createdAt",
      pl.property_id AS "propertyId",
      p.landlord_property_id AS "landlordPropertyId",
      p.address AS "address",
      p.postcode AS "postcode",
      p.current_sap_points AS "currentSapPoints",
      p.current_epc_rating AS "currentEpcRating",
      fp.scheme AS "fundingScheme",
      COALESCE(fp.project_funding, 0) AS "totalFunding",
      COALESCE(fp.total_uplift, 0) AS "totalUplift",
      COALESCE(SUM(r.co2_equivalent_savings), 0) AS "totalCarbonSavings",
      COALESCE(SUM(r.energy_cost_savings), 0) AS "totalBillSavings",
      COALESCE(SUM(r.estimated_cost), 0) AS "totalRecommendationCost",
      COALESCE(SUM(r.sap_points), 0) AS "totalRecommendationSapPoints"
    FROM plan pl
    INNER JOIN property p
      ON p.id = pl.property_id
    LEFT JOIN funding_package fp
      ON fp.plan_id = pl.id
    LEFT JOIN plan_recommendations prx
      ON prx.plan_id = pl.id
    LEFT JOIN recommendation r
      ON r.id = prx.recommendation_id
      AND r.default = true
    WHERE pl.portfolio_id = ${portfolioId}
      AND pl.plan_type IN (
        'solar_eco4',
        'solar_hhrsh_eco4',
        'empty_cavity_eco',
        'partial_cavity_eco',
        'extraction_eco'
      )
    GROUP BY
      pl.id,
      pl.plan_type,
      pl.name,
      pl.created_at,
      pl.property_id,
      p.landlord_property_id,
      p.address,
      p.postcode,
      p.current_sap_points,
      p.current_epc_rating,
      fp.scheme,
      fp.project_funding,
      fp.total_uplift
    ORDER BY pl.created_at DESC;
  `);

  const data = result.rows.map((plan) => {
    const planType = plan.planType as PlanTypeEnum | null;

    const surveyCost = planType
      ? (DOMNA_COST_MAP[planType] ?? DOMNA_COST_MAP.default)
      : DOMNA_COST_MAP.default;

    const totalCost = plan.totalRecommendationCost ?? 0;
    const funding = (plan.totalFunding ?? 0) + (plan.totalUplift ?? 0);
    const uplift = plan.totalUplift ?? 0;

    const rawContribution = totalCost + surveyCost - funding - uplift;
    const clientContribution = rawContribution > 0 ? rawContribution : 0;
    const postSapPoints =
      (plan.currentSapPoints ?? 0) + (plan.totalRecommendationSapPoints ?? 0);

    return {
      ...plan,
      totalFunding: funding, // overwrite
      surveyCost,
      clientContribution,
      averageSapPost: postSapPoints,
    };
  });

  return data;
}
