import type { Database } from 'bun:sqlite';

export const SCHEMA_VERSION = 9;

export function applySchema(db: Database) {
  db.run('PRAGMA journal_mode = WAL');
  db.run('PRAGMA foreign_keys = ON');

  const v = (db.query('PRAGMA user_version').get() as { user_version: number }).user_version;
  if (v < 1) {
    db.run('DROP TABLE IF EXISTS orders');
    db.run('DROP TABLE IF EXISTS sessions');
    db.run('DROP TABLE IF EXISTS products');
    const hasUsers = db.query(`SELECT name FROM sqlite_master WHERE type='table' AND name='users'`).get();
    if (hasUsers) {
      const cols = db.query('PRAGMA table_info(users)').all() as { name: string }[];
      if (!cols.some(c => c.name === 'is_admin')) {
        db.run('ALTER TABLE users ADD COLUMN is_admin INTEGER NOT NULL DEFAULT 0');
      }
    }
  }

  db.run(`CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    name_en TEXT NOT NULL DEFAULT '',
    slug TEXT NOT NULL UNIQUE,
    description TEXT NOT NULL DEFAULT '',
    description_en TEXT NOT NULL DEFAULT '',
    price_cents INTEGER NOT NULL,
    unit_size INTEGER NOT NULL DEFAULT 100,
    unit TEXT NOT NULL DEFAULT 'g',
    images TEXT NOT NULL DEFAULT '[]',
    category TEXT NOT NULL DEFAULT 'bilki',
    stock_count INTEGER NOT NULL DEFAULT 0,
    harvest_year INTEGER,
    usage TEXT NOT NULL DEFAULT '',
    usage_en TEXT NOT NULL DEFAULT '',
    ord INTEGER NOT NULL DEFAULT 0,
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
  )`);

  if (v < 3) {
    const cols = db.query('PRAGMA table_info(products)').all() as { name: string }[];
    if (!cols.some(c => c.name === 'unit_size')) db.run('ALTER TABLE products ADD COLUMN unit_size INTEGER NOT NULL DEFAULT 100');
    if (!cols.some(c => c.name === 'stock_count')) {
      db.run('ALTER TABLE products ADD COLUMN stock_count INTEGER NOT NULL DEFAULT 0');
    }
    if (!cols.some(c => c.name === 'harvest_year')) db.run('ALTER TABLE products ADD COLUMN harvest_year INTEGER');
    if (!cols.some(c => c.name === 'images')) {
      if (cols.some(c => c.name === 'image')) {
        db.run(`UPDATE products SET images = '[\"' || image || '\"]' WHERE image IS NOT NULL AND image != ''`);
      }
      db.run('ALTER TABLE products ADD COLUMN images TEXT NOT NULL DEFAULT \'[]\'');
    }
    db.run("UPDATE products SET unit = 'g', unit_size = 100 WHERE unit = 'kg'");
  }

  if (v < 4) {
    const cols = db.query('PRAGMA table_info(products)').all() as { name: string }[];
    if (!cols.some(c => c.name === 'ord')) {
      db.run('ALTER TABLE products ADD COLUMN ord INTEGER NOT NULL DEFAULT 0');
      const rows = db.query('SELECT id FROM products ORDER BY id').all() as { id: number }[];
      rows.forEach((r, i) => db.query('UPDATE products SET ord = ? WHERE id = ?').run(i, r.id));
    }
  }

  db.run(`CREATE TABLE IF NOT EXISTS users (
    id TEXT PRIMARY KEY,
    email TEXT UNIQUE,
    password_hash TEXT,
    nostr_pubkey TEXT UNIQUE,
    name TEXT,
    phone TEXT,
    is_admin INTEGER NOT NULL DEFAULT 0,
    delivery_prefs TEXT,
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
  )`);

  if (v < 2) {
    const cols = db.query('PRAGMA table_info(users)').all() as { name: string }[];
    if (!cols.some(c => c.name === 'delivery_prefs')) db.run('ALTER TABLE users ADD COLUMN delivery_prefs TEXT');
    if (!cols.some(c => c.name === 'phone')) db.run('ALTER TABLE users ADD COLUMN phone TEXT');
  }

  db.run(`CREATE TABLE IF NOT EXISTS orders (
    id TEXT PRIMARY KEY,
    view_token TEXT NOT NULL,
    user_id TEXT,
    items TEXT NOT NULL,
    subtotal_cents INTEGER NOT NULL,
    shipping_cents INTEGER NOT NULL DEFAULT 0,
    total_cents INTEGER NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending',
    payment_method TEXT NOT NULL,
    payment_status TEXT NOT NULL DEFAULT 'pending',
    payment_id TEXT,
    courier TEXT,
    shipping_method TEXT,
    shipping_address TEXT,
    customer_name TEXT NOT NULL,
    customer_email TEXT NOT NULL,
    customer_phone TEXT,
    notes TEXT,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (user_id) REFERENCES users(id)
  )`);

  db.run(`CREATE TABLE IF NOT EXISTS sessions (
    token TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    expires_at INTEGER NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
  )`);

  db.run(`CREATE TABLE IF NOT EXISTS offices (
    id TEXT PRIMARY KEY,
    courier TEXT NOT NULL,
    name TEXT NOT NULL,
    city TEXT NOT NULL,
    address TEXT NOT NULL,
    updated_at TEXT NOT NULL DEFAULT (datetime('now'))
  )`);

  db.run(`CREATE TABLE IF NOT EXISTS nostr_challenges (
    challenge TEXT PRIMARY KEY,
    created_at INTEGER NOT NULL
  )`);

  db.run(`CREATE TABLE IF NOT EXISTS content (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL DEFAULT ''
  )`);

  if (v < 5) {
    db.run('INSERT OR IGNORE INTO content (key, value) VALUES (?, ?)', ['terms_bg', '']);
    db.run('INSERT OR IGNORE INTO content (key, value) VALUES (?, ?)', ['terms_en', '']);
  }

  if (v < 6) {
    db.run(`CREATE TABLE IF NOT EXISTS contact_messages (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      email TEXT NOT NULL,
      subject TEXT NOT NULL DEFAULT 'general',
      message TEXT NOT NULL,
      created_at TEXT NOT NULL DEFAULT (datetime('now'))
    )`);
  }

  if (v < 9) {
    const cols = db.query('PRAGMA table_info(products)').all() as { name: string }[];
    if (!cols.some(c => c.name === 'usage')) db.run('ALTER TABLE products ADD COLUMN usage TEXT NOT NULL DEFAULT \'\'');
    if (!cols.some(c => c.name === 'usage_en')) db.run('ALTER TABLE products ADD COLUMN usage_en TEXT NOT NULL DEFAULT \'\'');
  }

  db.run(`PRAGMA user_version = ${SCHEMA_VERSION}`);
}