/**
 * Scenario persistence per ADR-0003. Query discipline:
 *  - gallery: ONE grouped plans-count query per portfolio (index prefix scan)
 *  - detail:  ONE count scoped to (portfolio_id, scenario_id) (index-only)
 *  - delete:  single atomic statement guarded on no-plans-exist
 * Never per-scenario probes inside a list.
 */
import { db } from "@/app/db/db";
import { desc, eq, and, sql } from "drizzle-orm";
import { scenario } from "@/app/db/schema/recommendations";
import {
  deriveScenarioStatus,
  parseExclusions,
  scenarioToInsertRow,
  type ScenarioConfigInput,
  type ScenarioStatus,
} from "./model";

export interface ScenarioListItem {
  id: bigint;
  name: string | null;
  housingType: string;
  goal: string;
  goalValue: string | null;
  budget: number | null;
  exclusions: string[];
  fabricFirst: boolean;
  createdAt: Date;
  plansCount: number;
  status: ScenarioStatus;
}

export async function listScenariosWithStatus(
  portfolioId: bigint,
): Promise<ScenarioListItem[]> {
  const [rows, counts] = await Promise.all([
    db
      .select()
      .from(scenario)
      .where(eq(scenario.portfolioId, portfolioId))
      .orderBy(desc(scenario.createdAt), desc(scenario.id)),
    db.execute<{ scenario_id: string; n: number }>(sql`
      SELECT scenario_id, COUNT(DISTINCT property_id)::int AS n
      FROM plan
      WHERE portfolio_id = ${portfolioId} AND scenario_id IS NOT NULL
      GROUP BY scenario_id
    `),
  ]);
  const byScenario = new Map(counts.rows.map((r) => [String(r.scenario_id), r.n]));
  return rows.map((s) => {
    const plansCount = byScenario.get(String(s.id)) ?? 0;
    return {
      id: s.id,
      name: s.name,
      housingType: s.housingType,
      goal: s.goal,
      goalValue: s.goalValue,
      budget: s.budget,
      exclusions: parseExclusions(s.exclusions),
      fabricFirst: s.fabricFirst,
      createdAt: s.createdAt,
      plansCount,
      status: deriveScenarioStatus(plansCount),
    };
  });
}

export async function getScenarioWithPlansCount(
  portfolioId: bigint,
  scenarioId: bigint,
): Promise<ScenarioListItem | null> {
  const [row] = await db
    .select()
    .from(scenario)
    .where(and(eq(scenario.id, scenarioId), eq(scenario.portfolioId, portfolioId)))
    .limit(1);
  if (!row) return null;
  const plansCount = await countScenarioProperties(portfolioId, scenarioId);
  return {
    id: row.id,
    name: row.name,
    housingType: row.housingType,
    goal: row.goal,
    goalValue: row.goalValue,
    budget: row.budget,
    exclusions: parseExclusions(row.exclusions),
    fabricFirst: row.fabricFirst,
    createdAt: row.createdAt,
    plansCount,
    status: deriveScenarioStatus(plansCount),
  };
}

/** Index-only scan on the plan composite index — gates delete and powers the blocked-delete message. */
export async function countScenarioProperties(
  portfolioId: bigint,
  scenarioId: bigint,
): Promise<number> {
  const res = await db.execute<{ n: number }>(sql`
    SELECT COUNT(DISTINCT property_id)::int AS n
    FROM plan
    WHERE portfolio_id = ${portfolioId} AND scenario_id = ${scenarioId}
  `);
  return res.rows[0].n;
}

export async function createScenario(
  portfolioId: bigint,
  config: ScenarioConfigInput,
): Promise<bigint> {
  const [created] = await db
    .insert(scenario)
    .values(scenarioToInsertRow(config, portfolioId))
    .returning({ id: scenario.id });
  return created.id;
}

export async function renameScenario(
  portfolioId: bigint,
  scenarioId: bigint,
  name: string,
): Promise<boolean> {
  const updated = await db
    .update(scenario)
    .set({ name })
    .where(and(eq(scenario.id, scenarioId), eq(scenario.portfolioId, portfolioId)))
    .returning({ id: scenario.id });
  return updated.length > 0;
}

export type DeleteScenarioResult =
  | { outcome: "deleted" }
  | { outcome: "not_found" }
  | { outcome: "has_plans"; plansCount: number };

/**
 * Atomic guarded delete: the no-plans check lives inside the DELETE statement
 * so a modelling run landing plans mid-request cannot orphan them (ADR-0003).
 */
export async function deleteScenarioIfUnmodelled(
  portfolioId: bigint,
  scenarioId: bigint,
): Promise<DeleteScenarioResult> {
  const res = await db.execute(sql`
    DELETE FROM scenario s
    WHERE s.id = ${scenarioId}
      AND s.portfolio_id = ${portfolioId}
      AND NOT EXISTS (
        SELECT 1 FROM plan p
        WHERE p.portfolio_id = ${portfolioId} AND p.scenario_id = s.id
      )
    RETURNING s.id
  `);
  if (res.rows.length > 0) return { outcome: "deleted" };

  const plansCount = await countScenarioProperties(portfolioId, scenarioId);
  if (plansCount > 0) return { outcome: "has_plans", plansCount };
  return { outcome: "not_found" };
}
