import { db } from "@/app/db/db";
import { sql } from "drizzle-orm";
import { NextRequest, NextResponse } from "next/server";

type MeasureAggregateRow = {
  measure_type: string | null;
  type: string | null;
  includes_battery: boolean | null;
  homes_count: number;
  total_cost: number | null;
  average_cost: number | null;
};

export async function GET(
  request: NextRequest,
  props: { params: Promise<{ portfolioId: string }> },
) {
  const { portfolioId } = await props.params;

  const pid = BigInt(portfolioId);

  const result = await db.execute(sql`
    SELECT
      r.measure_type,
      r.type,
      COUNT(DISTINCT r.property_id)::int AS homes_count,
      SUM(r.estimated_cost)::float       AS total_cost,
      AVG(r.estimated_cost)::float       AS average_cost
    FROM recommendation r
    WHERE r.default = true
      AND r.already_installed = false
      AND EXISTS (
        SELECT 1
        FROM (
          SELECT DISTINCT ON (p.property_id)
            p.id
          FROM plan p
          WHERE p.portfolio_id = ${pid}
            AND p.is_default = true
          ORDER BY p.property_id, p.created_at DESC
        ) lp
        JOIN plan_recommendations pr
          ON pr.plan_id = lp.id
        WHERE pr.recommendation_id = r.id
      )
    GROUP BY
      r.measure_type,
      r.type
    ORDER BY total_cost DESC;
  `);

  const measures = (result.rows as MeasureAggregateRow[]).map((row) => ({
    measureType: row.measure_type ?? "unknown",
    type: row.type ?? "unknown",
    homesCount: row.homes_count,
    totalCost: Number(row.total_cost ?? 0),
    averageCost: Number(row.average_cost ?? 0),
    // includesBattery: row.includes_battery ?? false,
  }));

  return NextResponse.json({
    portfolioId: Number(portfolioId),
    measures,
  });
}
