`src/db.ts`

136 lines
import fs from "node:fs";
import path from "node:path";
import Database from "better-sqlite3";
import type { PatternSnapshot } from "./schemas.js";

export type PortfolioRow = {
  id: number;
  slug: string;
  title: string | null;
  created_at: string;
  updated_at: string;
  contact_name: string;
  business_name: string | null;
  email: string;
  phone: string | null;
  website: string | null;
  password_hash: string | null;
  patterns_json: string;
  ai_intro: string | null;
};

export type Portfolio = {
  id: number;
  slug: string;
  title?: string;
  createdAt: string;
  updatedAt: string;
  contact: {
    name: string;
    business?: string;
    email: string;
    phone?: string;
    website?: string;
  };
  passwordHash?: string;
  patterns: PatternSnapshot[];
  aiIntro?: string;
};

export function openDb(sqlitePath: string) {
  const dir = path.dirname(sqlitePath);
  fs.mkdirSync(dir, { recursive: true });

  const db = new Database(sqlitePath);
  db.pragma("journal_mode = WAL");

  db.exec(`
    CREATE TABLE IF NOT EXISTS portfolios (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      slug TEXT NOT NULL UNIQUE,
      title TEXT,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL,
      contact_name TEXT NOT NULL,
      business_name TEXT,
      email TEXT NOT NULL,
      phone TEXT,
      website TEXT,
      password_hash TEXT,
      patterns_json TEXT NOT NULL,
      ai_intro TEXT
    );

    CREATE INDEX IF NOT EXISTS idx_portfolios_slug ON portfolios(slug);
  `);

  return db;
}

export function insertPortfolio(db: Database.Database, args: {
  slug: string;
  title?: string;
  createdAtIso: string;
  updatedAtIso: string;
  contact: { name: string; business?: string; email: string; phone?: string; website?: string };
  passwordHash: string | null;
  patterns: PatternSnapshot[];
  aiIntro: string | null;
}): number {
  const stmt = db.prepare(`
    INSERT INTO portfolios (
      slug, title, created_at, updated_at,
      contact_name, business_name, email, phone, website,
      password_hash,
      patterns_json,
      ai_intro
    ) VALUES (
      @slug, @title, @created_at, @updated_at,
      @contact_name, @business_name, @email, @phone, @website,
      @password_hash,
      @patterns_json,
      @ai_intro
    )
  `);

  const info = stmt.run({
    slug: args.slug,
    title: args.title?.trim() || null,
    created_at: args.createdAtIso,
    updated_at: args.updatedAtIso,
    contact_name: args.contact.name,
    business_name: (args.contact.business ?? "").trim() || null,
    email: args.contact.email,
    phone: (args.contact.phone ?? "").trim() || null,
    website: (args.contact.website ?? "").trim() || null,
    password_hash: args.passwordHash,
    patterns_json: JSON.stringify(args.patterns),
    ai_intro: args.aiIntro
  });

  return Number(info.lastInsertRowid);
}

export function getPortfolioBySlug(db: Database.Database, slug: string): Portfolio | null {
  const row = db.prepare(`SELECT * FROM portfolios WHERE slug = ?`).get(slug) as PortfolioRow | undefined;
  if (!row) return null;

  return {
    id: row.id,
    slug: row.slug,
    title: row.title ?? undefined,
    createdAt: row.created_at,
    updatedAt: row.updated_at,
    contact: {
      name: row.contact_name,
      business: row.business_name ?? undefined,
      email: row.email,
      phone: row.phone ?? undefined,
      website: row.website ?? undefined
    },
    passwordHash: row.password_hash ?? undefined,
    patterns: JSON.parse(row.patterns_json) as PatternSnapshot[],
    aiIntro: row.ai_intro ?? undefined
  };
}