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; scenarioId: string }> }
) {
  const { portfolioId, scenarioId } = await props.params;

  const pid = BigInt(portfolioId);
  const sid = BigInt(scenarioId);

  // TEMP: Remove batteries as underspecified
  // const result = await db.execute(sql`
  //   WITH latest_plans AS (
  //     SELECT DISTINCT ON (property_id)
  //       *
  //     FROM plan
  //     WHERE portfolio_id = ${pid}
  //       AND scenario_id = ${sid}
  //     ORDER BY property_id, created_at DESC
  //   ),

  //   recommendation_flags AS (
  //     SELECT
  //       r.id                        AS recommendation_id,
  //       r.measure_type              AS measure_type,
  //       r.property_id               AS property_id,
  //       r.estimated_cost            AS estimated_cost,
  //       BOOL_OR(m.includes_battery) AS includes_battery

  //     FROM latest_plans lp
  //     JOIN plan_recommendations pr
  //       ON pr.plan_id = lp.id
  //     JOIN recommendation r
  //       ON r.id = pr.recommendation_id

  //     LEFT JOIN recommendation_materials rm
  //       ON rm.recommendation_id = r.id
  //     LEFT JOIN material m
  //       ON m.id = rm.material_id
  //       AND m.is_active = true

  //     WHERE r.default = true
  //       AND r.already_installed = false

  //     GROUP BY
  //       r.id,
  //       r.measure_type,
  //       r.property_id,
  //       r.estimated_cost
  //   )

  //   SELECT
  //     measure_type,
  //     COALESCE(includes_battery, false) AS includes_battery,

  //     COUNT(DISTINCT property_id)::int  AS homes_count,
  //     SUM(estimated_cost)::float        AS total_cost,
  //     AVG(estimated_cost)::float        AS average_cost

  //   FROM recommendation_flags
  //   GROUP BY
  //     measure_type,
  //     includes_battery
  //   ORDER BY total_cost DESC;
  // `);

  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.scenario_id  = ${sid}
          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),
    scenarioId: Number(scenarioId),
    measures,
  });
}
