import "server-only";
import { db } from "@/app/db/db";
import { sql } from "drizzle-orm";
import type { Measure } from "@/lib/reporting/measures";
import { tagFilterCondition } from "@/lib/reporting/tagFilterSql";
import { emptyTagFilter, type TagFilter } from "@/lib/reporting/viewState";

/**
 * Grouped measure spend for a scenario's latest plans — the data behind the
 * reporting "where the money goes" allocation (PRD #370). Shared by the
 * server action (reporting/actions.ts) and the measures API routes so the
 * SQL lives in one place.
 *
 * The recommendations are joined on plan_id alone (it uniquely identifies the
 * plan and its property), so idx_recommendation_plan_id is used; grouped by
 * measure_type only, since the UI buckets by category and never reads the
 * `type` variant.
 */
export async function queryScenarioMeasures(
  portfolioId: number,
  scenarioId: number | "default",
  tags: TagFilter = emptyTagFilter(),
): Promise<Measure[]> {
  const pid = BigInt(portfolioId);

  // "default" (the recommended-plans pseudo-scenario) selects the latest
  // is_default plan per property rather than a specific scenario_id.
  const planScope =
    scenarioId === "default"
      ? sql`is_default = true`
      : sql`scenario_id = ${BigInt(scenarioId)}`;

  const result = await db.execute<{
    measure_type: string | null;
    homes_count: number;
    total_cost: number | null;
    average_cost: number | null;
  }>(sql`
    SELECT
      r.measure_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 (
      SELECT DISTINCT ON (property_id)
        id, property_id
      FROM plan
      WHERE portfolio_id = ${pid}
        AND ${planScope}
        AND ${tagFilterCondition(tags, sql`plan.property_id`)}
      ORDER BY property_id, created_at DESC
    ) lp
    JOIN recommendation r
      ON r.plan_id = lp.id
      AND r.default = true
      AND r.already_installed = false
    GROUP BY r.measure_type
    ORDER BY total_cost DESC;
  `);

  return result.rows.map((row) => ({
    measureType: row.measure_type ?? "unknown",
    homesCount: row.homes_count,
    totalCost: Number(row.total_cost ?? 0),
    averageCost: Number(row.average_cost ?? 0),
  }));
}
