import "server-only";
import { and, asc, desc, eq, ilike, or, sql } from "drizzle-orm";
import { db } from "@/lib/db/client";
import {
  consentiqProcedures,
  type ConsentiqProcedure,
} from "@/lib/db/schema";
import type { Procedure, ConsentPoint } from "@/data/consentiq/procedures";

/**
 * Map a DB row into the `Procedure` shape that the public explorer and
 * builder components were written against. Keeps the UI side type-safe
 * without forcing the schema to match the client data verbatim.
 */
export function rowToPublicProcedure(row: ConsentiqProcedure): Procedure {
  return {
    slug: row.slug,
    name: row.name,
    region: row.region,
    approxDurationMin: row.approxDurationMin,
    anaesthesia: row.anaesthesia,
    summary: row.summary,
    involves: row.involves,
    options: row.options.map((o) => ({
      id: o.id,
      label: o.label,
      kind: o.kind,
      help: o.help,
      defaultValue: o.defaultValue,
      choices: o.choices,
    })),
    risks: row.risks.map<ConsentPoint>((r) => ({
      title: r.title,
      detail: r.detail,
      severity:
        r.severity === "common" || r.severity === "rare" || r.severity === "serious"
          ? r.severity
          : undefined,
    })),
    benefits: row.benefits,
    alternatives: row.alternatives,
    recovery: row.recovery,
    warnings: row.warnings,
  };
}

export async function listPublishedProcedures() {
  return db
    .select()
    .from(consentiqProcedures)
    .where(eq(consentiqProcedures.status, "published"))
    .orderBy(asc(consentiqProcedures.region), asc(consentiqProcedures.name));
}

export async function listPublishedPublicProcedures(): Promise<Procedure[]> {
  const rows = await listPublishedProcedures();
  return rows.map(rowToPublicProcedure);
}

export async function getPublicProcedureBySlug(
  slug: string,
): Promise<Procedure | null> {
  const row = await getPublishedProcedureBySlug(slug);
  return row ? rowToPublicProcedure(row) : null;
}

export async function getPublishedProcedureBySlug(slug: string) {
  const [row] = await db
    .select()
    .from(consentiqProcedures)
    .where(
      and(
        eq(consentiqProcedures.slug, slug),
        eq(consentiqProcedures.status, "published"),
      ),
    )
    .limit(1);
  return row ?? null;
}

export async function getProcedureForAdmin(id: string) {
  const [row] = await db
    .select()
    .from(consentiqProcedures)
    .where(eq(consentiqProcedures.id, id))
    .limit(1);
  return row ?? null;
}

export async function listProceduresForAdmin({
  q,
  region,
  status,
}: {
  q?: string;
  region?: string;
  status?: "draft" | "published" | "archived" | "all";
} = {}) {
  const conditions = [];
  if (status && status !== "all") {
    conditions.push(eq(consentiqProcedures.status, status));
  }
  if (region && region.length > 0) {
    conditions.push(eq(consentiqProcedures.region, region));
  }
  if (q && q.trim().length > 0) {
    const pattern = `%${q.trim()}%`;
    conditions.push(
      or(
        ilike(consentiqProcedures.name, pattern),
        ilike(consentiqProcedures.summary, pattern),
      )!,
    );
  }
  const whereClause = conditions.length > 0 ? and(...conditions) : undefined;
  return db
    .select()
    .from(consentiqProcedures)
    .where(whereClause)
    .orderBy(desc(consentiqProcedures.updatedAt));
}

export async function getProcedureKpis() {
  const [total, published, regions] = await Promise.all([
    db.select({ count: sql<number>`count(*)::int` }).from(consentiqProcedures),
    db
      .select({ count: sql<number>`count(*)::int` })
      .from(consentiqProcedures)
      .where(eq(consentiqProcedures.status, "published")),
    db
      .select({ count: sql<number>`count(distinct ${consentiqProcedures.region})::int` })
      .from(consentiqProcedures),
  ]);
  return {
    total: Number(total[0]?.count ?? 0),
    published: Number(published[0]?.count ?? 0),
    regions: Number(regions[0]?.count ?? 0),
  };
}

export async function listDistinctRegions(): Promise<string[]> {
  const rows = await db
    .selectDistinct({ region: consentiqProcedures.region })
    .from(consentiqProcedures)
    .orderBy(asc(consentiqProcedures.region));
  return rows.map((r) => r.region).filter(Boolean);
}
