import { db } from "@/db";
import { awards, clients, exhibitions, projects } from "@/db/schema";
import { asc, sql } from "drizzle-orm";
import { seedAwards, seedClients, seedExhibitions, seedProjects } from "@/lib/content";

export type ProjectRecord = typeof projects.$inferSelect;
export type AwardRecord = typeof awards.$inferSelect;
export type ExhibitionRecord = typeof exhibitions.$inferSelect;
export type ClientRecord = typeof clients.$inferSelect;

/** Public alias used across client components. */
export type Project = ProjectRecord;
export type Award = AwardRecord;
export type Exhibition = ExhibitionRecord;
export type Client = ClientRecord;

let seedPromise: Promise<void> | null = null;

/**
 * Ensures the tables exist and contain the studio content.
 * Safe to call on every request – it only inserts what is missing.
 */
export async function ensureSeeded(): Promise<void> {
  if (!seedPromise) {
    seedPromise = (async () => {
      await db.execute(sql`
        CREATE TABLE IF NOT EXISTS projects (
          id serial PRIMARY KEY,
          slug varchar(120) NOT NULL UNIQUE,
          title varchar(160) NOT NULL,
          category varchar(80) NOT NULL,
          year varchar(16) NOT NULL,
          location varchar(160),
          cover varchar(300) NOT NULL,
          summary text NOT NULL,
          description text NOT NULL,
          specs jsonb NOT NULL DEFAULT '[]'::jsonb,
          awards jsonb NOT NULL DEFAULT '[]'::jsonb,
          credits jsonb NOT NULL DEFAULT '[]'::jsonb,
          images jsonb NOT NULL DEFAULT '[]'::jsonb,
          video_url text,
          buy_url text,
          buy_label varchar(80),
          external_links jsonb NOT NULL DEFAULT '[]'::jsonb,
          keywords jsonb NOT NULL DEFAULT '[]'::jsonb,
          featured boolean NOT NULL DEFAULT false,
          sort_order integer NOT NULL DEFAULT 0,
          created_at timestamptz NOT NULL DEFAULT now()
        )`);
      await db.execute(sql`
        CREATE TABLE IF NOT EXISTS awards (
          id serial PRIMARY KEY,
          label varchar(220) NOT NULL,
          url text,
          sort_order integer NOT NULL DEFAULT 0
        )`);
      await db.execute(sql`
        CREATE TABLE IF NOT EXISTS exhibitions (
          id serial PRIMARY KEY,
          label varchar(220) NOT NULL,
          sort_order integer NOT NULL DEFAULT 0
        )`);
      await db.execute(sql`
        CREATE TABLE IF NOT EXISTS clients (
          id serial PRIMARY KEY,
          label varchar(160) NOT NULL,
          url text,
          sort_order integer NOT NULL DEFAULT 0
        )`);

      for (const p of seedProjects) {
        await db
          .insert(projects)
          .values({
            slug: p.slug,
            title: p.title,
            category: p.category,
            year: p.year,
            location: p.location,
            cover: p.cover,
            summary: p.summary,
            description: p.description,
            specs: p.specs,
            awards: p.awards,
            credits: p.credits,
            images: p.images,
            videoUrl: p.videoUrl ?? null,
            buyUrl: p.buyUrl ?? null,
            buyLabel: p.buyLabel ?? null,
            externalLinks: p.externalLinks ?? [],
            keywords: p.keywords,
            featured: p.featured ?? false,
            sortOrder: p.sortOrder,
          })
          .onConflictDoUpdate({
            target: projects.slug,
            set: {
              title: p.title,
              category: p.category,
              year: p.year,
              location: p.location,
              cover: p.cover,
              summary: p.summary,
              description: p.description,
              specs: p.specs,
              awards: p.awards,
              credits: p.credits,
              images: p.images,
              videoUrl: p.videoUrl ?? null,
              buyUrl: p.buyUrl ?? null,
              buyLabel: p.buyLabel ?? null,
              externalLinks: p.externalLinks ?? [],
              keywords: p.keywords,
              featured: p.featured ?? false,
              sortOrder: p.sortOrder,
            },
          });
      }

      const existingAwards = await db.select({ id: awards.id }).from(awards).limit(1);
      if (existingAwards.length === 0) {
        await db.insert(awards).values(
          seedAwards.map((a, i) => ({ label: a.label, url: a.url ?? null, sortOrder: i })),
        );
      }

      const existingExhibitions = await db
        .select({ id: exhibitions.id })
        .from(exhibitions)
        .limit(1);
      if (existingExhibitions.length === 0) {
        await db.insert(exhibitions).values(
          seedExhibitions.map((label, i) => ({ label, sortOrder: i })),
        );
      }

      const existingClients = await db.select({ id: clients.id }).from(clients).limit(1);
      if (existingClients.length === 0) {
        await db.insert(clients).values(
          seedClients.map((c, i) => ({ label: c.label, url: c.url ?? null, sortOrder: i })),
        );
      }
    })().catch((error) => {
      seedPromise = null;
      throw error;
    });
  }
  return seedPromise;
}

export async function getProjects(): Promise<ProjectRecord[]> {
  await ensureSeeded();
  return db.select().from(projects).orderBy(asc(projects.sortOrder));
}

export async function getProject(slug: string): Promise<ProjectRecord | null> {
  await ensureSeeded();
  const rows = await db.select().from(projects).where(sql`${projects.slug} = ${slug}`).limit(1);
  return rows[0] ?? null;
}

export async function getAwards(): Promise<AwardRecord[]> {
  await ensureSeeded();
  return db.select().from(awards).orderBy(asc(awards.sortOrder));
}

export async function getExhibitions(): Promise<ExhibitionRecord[]> {
  await ensureSeeded();
  return db.select().from(exhibitions).orderBy(asc(exhibitions.sortOrder));
}

export async function getClients(): Promise<ClientRecord[]> {
  await ensureSeeded();
  return db.select().from(clients).orderBy(asc(clients.sortOrder));
}