// lib/store.ts — server-side data access over the local SQLite db.
// All DB reads/writes for the app go through here. Import only from /api routes
// and server scripts, never client components.

import { randomUUID } from "node:crypto";
import { desc, eq } from "drizzle-orm";
import { db } from "@/db";
import { sources, snapshots, todos, editions, settings } from "@/db/schema";
import { Settings, SETTINGS_DEFAULTS } from "@/lib/schemas/setting";

const now = () => new Date().toISOString();

/* ───────────────── sources ───────────────── */

export function listSources() {
  return db.select().from(sources).orderBy(sources.position).all();
}

export function getSource(id: string) {
  return db.select().from(sources).where(eq(sources.id, id)).get();
}

export function updateSource(
  id: string,
  patch: Partial<{
    label: string;
    enabled: boolean;
    config: Record<string, unknown>;
    refreshSeconds: number;
    position: number;
    size: string;
    cols: number;
    rows: number;
  }>,
) {
  if (Object.keys(patch).length > 0) {
    db.update(sources).set(patch).where(eq(sources.id, id)).run();
  }
  return getSource(id);
}

/* ───────────────── snapshots ───────────────── */

export function latestSnapshot(sourceId: string) {
  return db
    .select()
    .from(snapshots)
    .where(eq(snapshots.sourceId, sourceId))
    .orderBy(desc(snapshots.fetchedAt))
    .limit(1)
    .get();
}

export function insertSnapshot(input: {
  sourceId: string;
  payload: unknown;
  ok: boolean;
  error: string | null;
}) {
  const id = randomUUID();
  db.insert(snapshots)
    .values({
      id,
      sourceId: input.sourceId,
      payload: input.payload ?? null,
      fetchedAt: now(),
      ok: input.ok,
      error: input.error,
    })
    .run();
  // keep only the most recent 5 snapshots per source
  const rows = db
    .select({ id: snapshots.id })
    .from(snapshots)
    .where(eq(snapshots.sourceId, input.sourceId))
    .orderBy(desc(snapshots.fetchedAt))
    .all();
  for (const r of rows.slice(5)) {
    db.delete(snapshots).where(eq(snapshots.id, r.id)).run();
  }
  return db.select().from(snapshots).where(eq(snapshots.id, id)).get()!;
}

/* ───────────────── todos ───────────────── */

export function listTodos() {
  return db.select().from(todos).orderBy(desc(todos.createdAt)).all();
}

export function createTodo(input: { title: string; due?: string | null }) {
  const id = randomUUID();
  db.insert(todos)
    .values({
      id,
      title: input.title,
      done: false,
      due: input.due ?? null,
      createdAt: now(),
    })
    .run();
  return db.select().from(todos).where(eq(todos.id, id)).get()!;
}

export function updateTodo(
  id: string,
  patch: Partial<{ title: string; done: boolean; due: string | null }>,
) {
  if (Object.keys(patch).length > 0) {
    db.update(todos).set(patch).where(eq(todos.id, id)).run();
  }
  return db.select().from(todos).where(eq(todos.id, id)).get();
}

export function deleteTodo(id: string) {
  db.delete(todos).where(eq(todos.id, id)).run();
}

/* ───────────────── editions ───────────────── */

export function listEditions() {
  return db.select().from(editions).orderBy(desc(editions.generatedAt)).all();
}

export function getEdition(id: string) {
  return db.select().from(editions).where(eq(editions.id, id)).get();
}

export function insertEdition(input: { date: string; pdfPath: string }) {
  const id = randomUUID();
  db.insert(editions)
    .values({ id, date: input.date, pdfPath: input.pdfPath, generatedAt: now() })
    .run();
  return db.select().from(editions).where(eq(editions.id, id)).get()!;
}

// Remove an edition row. Returns its pdfPath so the caller can unlink the file.
export function deleteEdition(id: string): string | null {
  const row = db.select().from(editions).where(eq(editions.id, id)).get();
  if (!row) return null;
  db.delete(editions).where(eq(editions.id, id)).run();
  return row.pdfPath;
}

/* ───────────────── settings ───────────────── */

export function getSettings(): Settings {
  const row = db.select().from(settings).where(eq(settings.key, "settings")).get();
  const parsed = Settings.safeParse(row?.value ?? {});
  return parsed.success ? parsed.data : SETTINGS_DEFAULTS;
}

export function saveSettings(patch: Partial<Settings>): Settings {
  const next = Settings.parse({ ...getSettings(), ...patch });
  db.insert(settings)
    .values({ key: "settings", value: next })
    .onConflictDoUpdate({ target: settings.key, set: { value: next } })
    .run();
  return next;
}

/* ───────────────── export / import ───────────────── */

export function exportAll() {
  return {
    sources: listSources(),
    todos: listTodos(),
    settings: getSettings(),
    editions: listEditions(),
  };
}

// Replace sources + todos and merge settings. Idempotent (full replace by table).
// Editions are regenerable and reference disk files, so they are not imported.
export function importAll(data: {
  sources?: unknown[];
  todos?: unknown[];
  settings?: Record<string, unknown>;
}) {
  if (Array.isArray(data.sources)) {
    db.delete(sources).run();
    for (const raw of data.sources) {
      const s = raw as Record<string, unknown>;
      if (!s.id || !s.kind || !s.label) continue;
      db.insert(sources)
        .values({
          id: String(s.id),
          kind: String(s.kind),
          label: String(s.label),
          enabled: s.enabled !== false,
          config: (s.config as Record<string, unknown>) ?? {},
          refreshSeconds: Number(s.refreshSeconds ?? 900),
          position: Number(s.position ?? 0),
          cols: Number(s.cols ?? 1),
          rows: Number(s.rows ?? 2),
        })
        .run();
    }
  }

  if (Array.isArray(data.todos)) {
    db.delete(todos).run();
    for (const raw of data.todos) {
      const t = raw as Record<string, unknown>;
      if (!t.id || !t.title) continue;
      db.insert(todos)
        .values({
          id: String(t.id),
          title: String(t.title),
          done: Boolean(t.done),
          due: t.due ? String(t.due) : null,
          createdAt: String(t.createdAt ?? now()),
        })
        .run();
    }
  }

  if (data.settings && typeof data.settings === "object") {
    const parsed = Settings.safeParse(data.settings);
    if (parsed.success) saveSettings(parsed.data);
  }
}