foamking/lib/db.ts

import Database from "better-sqlite3"
import path from "path"

// Database file stored in project root
const DB_PATH = path.join(process.cwd(), "data", "quotes.db")

// Ensure data directory exists
import fs from "fs"
const dataDir = path.dirname(DB_PATH)
if (!fs.existsSync(dataDir)) {
  fs.mkdirSync(dataDir, { recursive: true })
}

const db = new Database(DB_PATH)

// Initialize database schema
db.exec(`
  CREATE TABLE IF NOT EXISTS quotes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    postal_code TEXT NOT NULL,
    address TEXT,
    area REAL NOT NULL,
    height REAL NOT NULL,
    include_floor_heating INTEGER NOT NULL DEFAULT 1,
    flooring_type TEXT NOT NULL DEFAULT 'STANDARD',
    customer_name TEXT NOT NULL,
    customer_email TEXT NOT NULL,
    customer_phone TEXT NOT NULL,
    remarks TEXT,
    total_excl_vat REAL,
    total_incl_vat REAL,
    status TEXT NOT NULL DEFAULT 'new',
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
  )
`)

// Migration: Add status column if it doesn't exist
try {
  db.exec("ALTER TABLE quotes ADD COLUMN status TEXT NOT NULL DEFAULT 'new'")
} catch {
  // Column already exists
}

// Migration: Add email_opened_at column for tracking
try {
  db.exec("ALTER TABLE quotes ADD COLUMN email_opened_at TEXT")
} catch {
  // Column already exists
}

// Users table for authentication
db.exec(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    email TEXT UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    name TEXT NOT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
  )
`)

// Sessions table for login sessions
db.exec(`
  CREATE TABLE IF NOT EXISTS sessions (
    id TEXT PRIMARY KEY,
    user_id INTEGER NOT NULL,
    expires_at TEXT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
  )
`)

// Start IDs at 1000
const countResult = db.prepare("SELECT COUNT(*) as count FROM quotes").get() as { count: number }
if (countResult.count === 0) {
  db.exec(
    "INSERT INTO quotes (id, postal_code, area, height, customer_name, customer_email, customer_phone) VALUES (999, '0000', 0, 0, 'init', 'init', 'init')"
  )
  db.exec("DELETE FROM quotes WHERE id = 999")
  // Reset autoincrement to start at 1000
  db.exec("UPDATE sqlite_sequence SET seq = 999 WHERE name = 'quotes'")
}

export interface QuoteInput {
  postalCode: string
  address?: string
  area: number
  height: number
  includeFloorHeating: boolean
  flooringType: string
  customerName: string
  customerEmail: string
  customerPhone: string
  remarks?: string
  totalExclVat?: number
  totalInclVat?: number
}

export type QuoteStatus = "new" | "contacted" | "accepted" | "rejected"

export interface StoredQuote extends QuoteInput {
  id: number
  status: QuoteStatus
  createdAt: string
  emailOpenedAt: string | null
}

export function saveQuote(quote: QuoteInput): { id: number; slug: string } {
  const stmt = db.prepare(`
    INSERT INTO quotes (
      postal_code, address, area, height, include_floor_heating, flooring_type,
      customer_name, customer_email, customer_phone, remarks, total_excl_vat, total_incl_vat
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `)

  const result = stmt.run(
    quote.postalCode,
    quote.address || null,
    quote.area,
    quote.height,
    quote.includeFloorHeating ? 1 : 0,
    quote.flooringType,
    quote.customerName,
    quote.customerEmail,
    quote.customerPhone,
    quote.remarks || null,
    quote.totalExclVat || null,
    quote.totalInclVat || null
  )

  const id = result.lastInsertRowid as number
  const slug = `${quote.postalCode}-${id}`

  return { id, slug }
}

export function getQuoteBySlug(slug: string): StoredQuote | null {
  const match = slug.match(/^(\d{4})-(\d+)$/)
  if (!match) return null

  const [, postalCode, idStr] = match
  const id = parseInt(idStr, 10)

  const stmt = db.prepare("SELECT * FROM quotes WHERE id = ? AND postal_code = ?")
  const row = stmt.get(id, postalCode) as any

  if (!row) return null

  return rowToQuote(row)
}

export function getQuoteById(id: number): StoredQuote | null {
  const stmt = db.prepare("SELECT * FROM quotes WHERE id = ?")
  const row = stmt.get(id) as any
  if (!row) return null
  return rowToQuote(row)
}

export function getAllQuotes(): StoredQuote[] {
  const stmt = db.prepare("SELECT * FROM quotes ORDER BY id DESC")
  const rows = stmt.all() as any[]
  return rows.map(rowToQuote)
}

export function updateQuoteStatus(id: number, status: QuoteStatus): boolean {
  const stmt = db.prepare("UPDATE quotes SET status = ? WHERE id = ?")
  const result = stmt.run(status, id)
  return result.changes > 0
}

export function markEmailOpened(id: number): boolean {
  // Only update if not already set (first open)
  const stmt = db.prepare(
    "UPDATE quotes SET email_opened_at = ? WHERE id = ? AND email_opened_at IS NULL"
  )
  const result = stmt.run(new Date().toISOString(), id)
  return result.changes > 0
}

function rowToQuote(row: any): StoredQuote {
  return {
    id: row.id,
    postalCode: row.postal_code,
    address: row.address,
    area: row.area,
    height: row.height,
    includeFloorHeating: row.include_floor_heating === 1,
    flooringType: row.flooring_type,
    customerName: row.customer_name,
    customerEmail: row.customer_email,
    customerPhone: row.customer_phone,
    remarks: row.remarks,
    totalExclVat: row.total_excl_vat,
    totalInclVat: row.total_incl_vat,
    status: row.status || "new",
    createdAt: row.created_at,
    emailOpenedAt: row.email_opened_at || null,
  }
}

// User management
export interface User {
  id: number
  email: string
  name: string
  createdAt: string
}

export interface UserWithPassword extends User {
  passwordHash: string
}

export function createUser(email: string, passwordHash: string, name: string): User {
  const stmt = db.prepare("INSERT INTO users (email, password_hash, name) VALUES (?, ?, ?)")
  const result = stmt.run(email, passwordHash, name)
  return {
    id: result.lastInsertRowid as number,
    email,
    name,
    createdAt: new Date().toISOString(),
  }
}

export function getUserByEmail(email: string): UserWithPassword | null {
  const stmt = db.prepare("SELECT * FROM users WHERE email = ?")
  const row = stmt.get(email) as any
  if (!row) return null
  return {
    id: row.id,
    email: row.email,
    name: row.name,
    passwordHash: row.password_hash,
    createdAt: row.created_at,
  }
}

export function getUserById(id: number): User | null {
  const stmt = db.prepare("SELECT id, email, name, created_at FROM users WHERE id = ?")
  const row = stmt.get(id) as any
  if (!row) return null
  return {
    id: row.id,
    email: row.email,
    name: row.name,
    createdAt: row.created_at,
  }
}

// Session management
export function createSession(sessionId: string, userId: number, expiresAt: Date): void {
  const stmt = db.prepare("INSERT INTO sessions (id, user_id, expires_at) VALUES (?, ?, ?)")
  stmt.run(sessionId, userId, expiresAt.toISOString())
}

export function getSession(sessionId: string): { userId: number; expiresAt: Date } | null {
  const stmt = db.prepare("SELECT user_id, expires_at FROM sessions WHERE id = ?")
  const row = stmt.get(sessionId) as any
  if (!row) return null
  return {
    userId: row.user_id,
    expiresAt: new Date(row.expires_at),
  }
}

export function deleteSession(sessionId: string): void {
  const stmt = db.prepare("DELETE FROM sessions WHERE id = ?")
  stmt.run(sessionId)
}

export function cleanExpiredSessions(): void {
  const stmt = db.prepare("DELETE FROM sessions WHERE expires_at < ?")
  stmt.run(new Date().toISOString())
}