'use strict';
const path = require('path');
const { DatabaseSync } = require('node:sqlite');

const DATA_DIR = path.join(__dirname, 'data');
const DB_PATH = path.join(DATA_DIR, 'touscino.db');

const db = new DatabaseSync(DB_PATH);

db.exec('PRAGMA journal_mode = WAL;');
db.exec('PRAGMA foreign_keys = ON;');

const orderCols = db.prepare("PRAGMA table_info(orders)").all().map((c) => c.name);
if (!orderCols.includes('review_requested')) {
  db.exec('ALTER TABLE orders ADD COLUMN review_requested INTEGER NOT NULL DEFAULT 0;');
}

const productCols = db.prepare("PRAGMA table_info(products)").all().map((c) => c.name);
if (!productCols.includes('sizes')) {
  db.exec("ALTER TABLE products ADD COLUMN sizes TEXT DEFAULT '';");
}
if (!productCols.includes('size_type')) {
  db.exec("ALTER TABLE products ADD COLUMN size_type TEXT DEFAULT 'none';");
  db.exec("UPDATE products SET size_type = 'shoes' WHERE size_type = 'none' AND TRIM(COALESCE(sizes, '')) <> '';");
}

const orderItemCols = db.prepare("PRAGMA table_info(order_items)").all().map((c) => c.name);
if (!orderItemCols.includes('size')) {
  db.exec("ALTER TABLE order_items ADD COLUMN size TEXT DEFAULT '';");
}

db.exec(`
CREATE TABLE IF NOT EXISTS settings (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL
);

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

CREATE TABLE IF NOT EXISTS categories (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  slug TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  description TEXT DEFAULT '',
  image TEXT DEFAULT '',
  sort_order INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS products (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  slug TEXT NOT NULL UNIQUE,
  description TEXT DEFAULT '',
  category_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,
  price REAL,
  compare_at_price REAL,
  stock INTEGER NOT NULL DEFAULT 0,
  sku TEXT DEFAULT '',
  image TEXT DEFAULT '',
  status TEXT NOT NULL DEFAULT 'draft',
  featured INTEGER NOT NULL DEFAULT 0,
  sizes TEXT DEFAULT '',
  size_type TEXT DEFAULT 'none',
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS product_images (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  image TEXT NOT NULL,
  alt TEXT DEFAULT '',
  sort_order INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS coupons (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  code TEXT NOT NULL UNIQUE,
  type TEXT NOT NULL DEFAULT 'percent',
  value REAL NOT NULL,
  min_subtotal REAL DEFAULT 0,
  expires_at TEXT,
  usage_limit INTEGER DEFAULT 0,
  used_count INTEGER NOT NULL DEFAULT 0,
  active INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS orders (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  order_number TEXT NOT NULL UNIQUE,
  user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
  email TEXT NOT NULL,
  name TEXT NOT NULL,
  phone TEXT NOT NULL,
  address_line1 TEXT NOT NULL,
  address_line2 TEXT DEFAULT '',
  city TEXT NOT NULL,
  province TEXT DEFAULT '',
  postal_code TEXT DEFAULT '',
  country TEXT DEFAULT 'South Africa',
  subtotal REAL NOT NULL,
  discount_amount REAL NOT NULL DEFAULT 0,
  coupon_code TEXT DEFAULT '',
  shipping_fee REAL NOT NULL DEFAULT 0,
  total REAL NOT NULL,
  currency TEXT DEFAULT 'ZAR',
  status TEXT NOT NULL DEFAULT 'pending',
  payment_method TEXT NOT NULL DEFAULT 'payfast',
  payment_status TEXT NOT NULL DEFAULT 'unpaid',
  review_requested INTEGER NOT NULL DEFAULT 0,
  notes TEXT DEFAULT '',
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS order_items (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id INTEGER,
  name TEXT NOT NULL,
  price REAL NOT NULL,
  qty INTEGER NOT NULL,
  image TEXT DEFAULT ''
);

CREATE TABLE IF NOT EXISTS messages (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  email TEXT NOT NULL,
  phone TEXT DEFAULT '',
  subject TEXT DEFAULT '',
  body TEXT NOT NULL,
  is_read INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS shipping_rates (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  region TEXT NOT NULL,
  fee REAL NOT NULL,
  free_over REAL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS reviews (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  product_id INTEGER REFERENCES products(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  city TEXT DEFAULT '',
  title TEXT DEFAULT '',
  body TEXT NOT NULL,
  rating INTEGER NOT NULL DEFAULT 5,
  approved INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS testimonials (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  customer_name TEXT NOT NULL,
  location TEXT DEFAULT '',
  product_name TEXT DEFAULT '',
  text TEXT NOT NULL,
  rating INTEGER NOT NULL DEFAULT 5,
  photo TEXT DEFAULT '',
  approved INTEGER NOT NULL DEFAULT 1,
  sort_order INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
`);

function getSetting(key, fallback = null) {
  const row = db.prepare('SELECT value FROM settings WHERE key = ?').get(key);
  return row ? row.value : fallback;
}

function setSetting(key, value) {
  db.prepare(
    'INSERT INTO settings (key, value) VALUES (?, ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value'
  ).run(key, String(value));
}

function getAllSettings() {
  const rows = db.prepare('SELECT key, value FROM settings').all();
  const out = {};
  for (const r of rows) out[r.key] = r.value;
  return out;
}

function categoryBySlug(slug) {
  return db.prepare('SELECT * FROM categories WHERE slug = ?').get(slug);
}

function categoriesList() {
  return db
    .prepare(
      `SELECT c.*, (SELECT COUNT(*) FROM products p WHERE p.category_id = c.id AND p.status='live') AS product_count
       FROM categories c ORDER BY c.sort_order`
    )
    .all();
}

function productBySlug(slug) {
  return db.prepare('SELECT * FROM products WHERE slug = ?').get(slug);
}

function productById(id) {
  return db.prepare('SELECT * FROM products WHERE id = ?').get(id);
}

function liveProducts(limit, offset, categoryId, featuredOnly) {
  let sql = `SELECT p.*, c.name AS category_name, c.slug AS category_slug
             FROM products p LEFT JOIN categories c ON c.id = p.category_id
             WHERE p.status = 'live'`;
  const params = [];
  if (categoryId) {
    sql += ' AND p.category_id = ?';
    params.push(categoryId);
  }
  if (featuredOnly) {
    sql += ' AND p.featured = 1';
  }
  sql += ' ORDER BY p.featured DESC, p.created_at DESC';
  if (limit) {
    sql += ' LIMIT ?';
    params.push(limit);
    if (offset) {
      sql += ' OFFSET ?';
      params.push(offset);
    }
  }
  return db.prepare(sql).all(...params);
}

function countLiveProducts(categoryId) {
  if (categoryId) {
    return db.prepare("SELECT COUNT(*) AS c FROM products WHERE status='live' AND category_id = ?").get(categoryId).c;
  }
  return db.prepare("SELECT COUNT(*) AS c FROM products WHERE status='live'").get().c;
}

function relatedProducts(product, limit = 4) {
  if (product.category_id) {
    return db
      .prepare(
        `SELECT * FROM products WHERE status='live' AND category_id = ? AND id != ? ORDER BY created_at DESC LIMIT ?`
      )
      .all(product.category_id, product.id, limit);
  }
  return db
    .prepare(`SELECT * FROM products WHERE status='live' AND id != ? ORDER BY created_at DESC LIMIT ?`)
    .all(product.id, limit);
}

function productGallery(productId) {
  return db.prepare('SELECT * FROM product_images WHERE product_id = ? ORDER BY sort_order').all(productId);
}

function productGalleryMap(productIds) {
  if (!productIds.length) return {};
  const placeholders = productIds.map(() => '?').join(',');
  const rows = db.prepare(`SELECT * FROM product_images WHERE product_id IN (${placeholders}) ORDER BY sort_order`).all(...productIds);
  const map = {};
  for (const r of rows) {
    if (!map[r.product_id]) map[r.product_id] = [];
    map[r.product_id].push(r);
  }
  return map;
}

function slugify(text) {
  return String(text)
    .toLowerCase()
    .trim()
    .replace(/[^a-z0-9\s-]/g, '')
    .replace(/\s+/g, '-')
    .replace(/-+/g, '-')
    .replace(/^-|-$/g, '');
}

function uniqueSlug(text, table = 'products', id = null) {
  let base = slugify(text) || 'item';
  let slug = base;
  let n = 2;
  for (;;) {
    const row = db.prepare(`SELECT id FROM ${table} WHERE slug = ?`).get(slug);
    if (!row || (id && row.id === id)) return slug;
    slug = `${base}-${n++}`;
  }
}

const SIZE_TYPES = {
  none: 'No sizes',
  clothing: 'Clothing (S – XXL)',
  shoes: 'Shoes (UK)',
};

function sizeOptions(type) {
  if (type === 'clothing') return ['S', 'M', 'L', 'XL', 'XXL'];
  if (type === 'shoes') {
    return ['4', '4.5', '5', '5.5', '6', '6.5', '7', '7.5', '8', '8.5', '9', '9.5', '10', '10.5', '11', '11.5', '12'];
  }
  return [];
}

module.exports = {
  db,
  getSetting,
  setSetting,
  getAllSettings,
  categoryBySlug,
  categoriesList,
  productBySlug,
  productById,
  liveProducts,
  countLiveProducts,
  relatedProducts,
  productGallery,
  productGalleryMap,
  slugify,
  uniqueSlug,
  SIZE_TYPES,
  sizeOptions,
};
