/**
 * Local SQLite Database Wrapper
 * 
 * Uses better-sqlite3 for local development and testing.
 * In production, use Supabase (supabase.ts) instead.
 */

import Database from 'better-sqlite3';
import { join, dirname } from 'path';
import { mkdirSync, existsSync } from 'fs';

// Ensure data directory exists
const dataDir = join(process.cwd(), 'data');
if (!existsSync(dataDir)) {
  mkdirSync(dataDir, { recursive: true });
}

const dbPath = join(dataDir, 'ecommerce.db');

// Create database connection
export const db = new Database(dbPath);

// Enable WAL mode for better performance
db.pragma('journal_mode = WAL');

// ============================================
// Initialize Tables
// ============================================

db.exec(`
  -- Users table
  CREATE TABLE IF NOT EXISTS users (
    id TEXT PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    name TEXT,
    role TEXT DEFAULT 'customer' CHECK(role IN ('customer', 'vendor', 'admin')),
    avatar_url TEXT,
    phone TEXT,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
  );

  -- Profiles table (additional user info)
  CREATE TABLE IF NOT EXISTS profiles (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL UNIQUE,
    first_name TEXT,
    last_name TEXT,
    phone TEXT,
    address TEXT,
    city TEXT,
    province TEXT,
    postal_code TEXT,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now')),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
  );

  -- Categories table
  CREATE TABLE IF NOT EXISTS categories (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    slug TEXT UNIQUE NOT NULL,
    description TEXT,
    image_url TEXT,
    parent_id TEXT,
    sort_order INTEGER DEFAULT 0,
    is_active INTEGER DEFAULT 1,
    created_at TEXT DEFAULT (datetime('now')),
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
  );

  -- Products table
  CREATE TABLE IF NOT EXISTS products (
    id TEXT PRIMARY KEY,
    vendor_id TEXT,
    category_id TEXT,
    name TEXT NOT NULL,
    slug TEXT UNIQUE NOT NULL,
    description TEXT,
    price REAL NOT NULL,
    compare_at_price REAL,
    sku TEXT UNIQUE,
    barcode TEXT,
    inventory INTEGER DEFAULT 0,
    images TEXT DEFAULT '[]',
    status TEXT DEFAULT 'active' CHECK(status IN ('draft', 'active', 'archived')),
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now')),
    FOREIGN KEY (vendor_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
  );

  -- Orders table
  CREATE TABLE IF NOT EXISTS orders (
    id TEXT PRIMARY KEY,
    order_number TEXT UNIQUE NOT NULL,
    user_id TEXT,
    guest_email TEXT,
    status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'confirmed', 'processing', 'shipped', 'delivered', 'cancelled')),
    payment_status TEXT DEFAULT 'pending' CHECK(payment_status IN ('pending', 'paid', 'failed', 'refunded')),
    subtotal REAL NOT NULL,
    tax REAL DEFAULT 0,
    shipping REAL DEFAULT 0,
    discount REAL DEFAULT 0,
    total REAL NOT NULL,
    shipping_name TEXT,
    shipping_phone TEXT,
    shipping_address TEXT,
    shipping_city TEXT,
    shipping_province TEXT,
    shipping_postal_code TEXT,
    notes TEXT,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now')),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
  );

  -- Order items table
  CREATE TABLE IF NOT EXISTS order_items (
    id TEXT PRIMARY KEY,
    order_id TEXT NOT NULL,
    product_id TEXT,
    product_name TEXT NOT NULL,
    product_image TEXT,
    price REAL NOT NULL,
    quantity INTEGER NOT NULL,
    total REAL NOT NULL,
    created_at TEXT DEFAULT (datetime('now')),
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL
  );

  -- Cart items table
  CREATE TABLE IF NOT EXISTS cart_items (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    product_id TEXT NOT NULL,
    quantity INTEGER DEFAULT 1,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now')),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    UNIQUE(user_id, product_id)
  );

  -- Payments table
  CREATE TABLE IF NOT EXISTS payments (
    id TEXT PRIMARY KEY,
    order_id TEXT NOT NULL,
    method TEXT NOT NULL CHECK(method IN ('promptpay', 'credit_card', 'bank_transfer', 'cash_on_delivery')),
    amount REAL NOT NULL,
    status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'processing', 'completed', 'failed', 'refunded')),
    reference TEXT,
    metadata TEXT DEFAULT '{}',
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now')),
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
  );

  -- Sessions table (for auth)
  CREATE TABLE IF NOT EXISTS sessions (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    token TEXT UNIQUE NOT NULL,
    expires_at TEXT NOT NULL,
    created_at TEXT DEFAULT (datetime('now')),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
  );

  -- Refresh tokens table
  CREATE TABLE IF NOT EXISTS refresh_tokens (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    token TEXT UNIQUE NOT NULL,
    expires_at TEXT NOT NULL,
    created_at TEXT DEFAULT (datetime('now')),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
  );

  -- Create indexes for better query performance
  CREATE INDEX IF NOT EXISTS idx_products_slug ON products(slug);
  CREATE INDEX IF NOT EXISTS idx_products_category ON products(category_id);
  CREATE INDEX IF NOT EXISTS idx_products_vendor ON products(vendor_id);
  CREATE INDEX IF NOT EXISTS idx_products_status ON products(status);
  CREATE INDEX IF NOT EXISTS idx_orders_user ON orders(user_id);
  CREATE INDEX IF NOT EXISTS idx_orders_number ON orders(order_number);
  CREATE INDEX IF NOT EXISTS idx_orders_status ON orders(status);
  CREATE INDEX IF NOT EXISTS idx_order_items_order ON order_items(order_id);
  CREATE INDEX IF NOT EXISTS idx_cart_items_user ON cart_items(user_id);
  CREATE INDEX IF NOT EXISTS idx_sessions_token ON sessions(token);
  CREATE INDEX IF NOT EXISTS idx_refresh_tokens_token ON refresh_tokens(token);
`);

// ============================================
// Type Definitions
// ============================================

export interface DbUser {
  id: string;
  email: string;
  password_hash: string;
  name: string | null;
  role: 'customer' | 'vendor' | 'admin';
  avatar_url: string | null;
  phone: string | null;
  created_at: string;
  updated_at: string;
}

export interface DbProduct {
  id: string;
  vendor_id: string | null;
  category_id: string | null;
  name: string;
  slug: string;
  description: string | null;
  price: number;
  compare_at_price: number | null;
  sku: string | null;
  barcode: string | null;
  inventory: number;
  images: string;
  status: 'draft' | 'active' | 'archived';
  created_at: string;
  updated_at: string;
}

export interface DbOrder {
  id: string;
  order_number: string;
  user_id: string | null;
  guest_email: string | null;
  status: 'pending' | 'confirmed' | 'processing' | 'shipped' | 'delivered' | 'cancelled';
  payment_status: 'pending' | 'paid' | 'failed' | 'refunded';
  subtotal: number;
  tax: number;
  shipping: number;
  discount: number;
  total: number;
  shipping_name: string | null;
  shipping_phone: string | null;
  shipping_address: string | null;
  shipping_city: string | null;
  shipping_province: string | null;
  shipping_postal_code: string | null;
  notes: string | null;
  created_at: string;
  updated_at: string;
}

export interface DbCategory {
  id: string;
  name: string;
  slug: string;
  description: string | null;
  image_url: string | null;
  parent_id: string | null;
  sort_order: number;
  is_active: number;
  created_at: string;
}

// ============================================
// Helper Functions
// ============================================

/**
 * Get user by email
 */
export function getUserByEmail(email: string): DbUser | undefined {
  return db.prepare('SELECT * FROM users WHERE email = ?').get(email) as DbUser | undefined;
}

/**
 * Get user by ID
 */
export function getUserById(id: string): DbUser | undefined {
  return db.prepare('SELECT * FROM users WHERE id = ?').get(id) as DbUser | undefined;
}

/**
 * Create user
 */
export function createUser(user: Omit<DbUser, 'created_at' | 'updated_at'>): DbUser {
  const stmt = db.prepare(`
    INSERT INTO users (id, email, password_hash, name, role, avatar_url, phone)
    VALUES (@id, @email, @password_hash, @name, @role, @avatar_url, @phone)
  `);
  stmt.run(user);
  return getUserById(user.id)!;
}

/**
 * Get product by slug
 */
export function getProductBySlug(slug: string): DbProduct | undefined {
  return db.prepare('SELECT * FROM products WHERE slug = ? AND status = ?')
    .get(slug, 'active') as DbProduct | undefined;
}

/**
 * Get products by category
 */
export function getProductsByCategory(categorySlug: string, limit = 20, offset = 0): DbProduct[] {
  return db.prepare(`
    SELECT p.* FROM products p
    JOIN categories c ON p.category_id = c.id
    WHERE c.slug = ? AND p.status = 'active'
    ORDER BY p.created_at DESC
    LIMIT ? OFFSET ?
  `).all(categorySlug, limit, offset) as DbProduct[];
}

/**
 * Get featured products
 */
export function getFeaturedProducts(limit = 10): DbProduct[] {
  return db.prepare(`
    SELECT * FROM products 
    WHERE status = 'active'
    ORDER BY created_at DESC
    LIMIT ?
  `).all(limit) as DbProduct[];
}

/**
 * Get order by number
 */
export function getOrderByNumber(orderNumber: string): DbOrder | undefined {
  return db.prepare('SELECT * FROM orders WHERE order_number = ?').get(orderNumber) as DbOrder | undefined;
}

/**
 * Create order
 */
export function createOrder(order: Omit<DbOrder, 'created_at' | 'updated_at'>): DbOrder {
  const stmt = db.prepare(`
    INSERT INTO orders (
      id, order_number, user_id, guest_email, status, payment_status,
      subtotal, tax, shipping, discount, total,
      shipping_name, shipping_phone, shipping_address, shipping_city,
      shipping_province, shipping_postal_code, notes
    ) VALUES (
      @id, @order_number, @user_id, @guest_email, @status, @payment_status,
      @subtotal, @tax, @shipping, @discount, @total,
      @shipping_name, @shipping_phone, @shipping_address, @shipping_city,
      @shipping_province, @shipping_postal_code, @notes
    )
  `);
  stmt.run(order);
  return getOrderByNumber(order.order_number)!;
}

/**
 * Update order
 */
export function updateOrder(id: string, updates: Partial<DbOrder>): void {
  const fields = Object.keys(updates)
    .filter(k => k !== 'id')
    .map(k => `${k} = @${k}`)
    .join(', ');
  
  if (fields) {
    const stmt = db.prepare(`UPDATE orders SET ${fields}, updated_at = datetime('now') WHERE id = @id`);
    stmt.run({ ...updates, id });
  }
}

/**
 * Get cart items for user
 */
export function getCartItems(userId: string) {
  return db.prepare(`
    SELECT ci.*, p.name, p.slug, p.price, p.images, p.inventory
    FROM cart_items ci
    JOIN products p ON ci.product_id = p.id
    WHERE ci.user_id = ?
  `).all(userId);
}

/**
 * Add to cart
 */
export function addToCart(userId: string, productId: string, quantity = 1): void {
  const existing = db.prepare(
    'SELECT * FROM cart_items WHERE user_id = ? AND product_id = ?'
  ).get(userId, productId);

  if (existing) {
    db.prepare(
      'UPDATE cart_items SET quantity = quantity + ? WHERE user_id = ? AND product_id = ?'
    ).run(quantity, userId, productId);
  } else {
    const id = crypto.randomUUID();
    db.prepare(
      'INSERT INTO cart_items (id, user_id, product_id, quantity) VALUES (?, ?, ?, ?)'
    ).run(id, userId, productId, quantity);
  }
}

/**
 * Update cart item quantity
 */
export function updateCartQuantity(userId: string, productId: string, quantity: number): void {
  if (quantity <= 0) {
    db.prepare('DELETE FROM cart_items WHERE user_id = ? AND product_id = ?').run(userId, productId);
  } else {
    db.prepare(
      'UPDATE cart_items SET quantity = ?, updated_at = datetime(\'now\') WHERE user_id = ? AND product_id = ?'
    ).run(quantity, userId, productId);
  }
}

/**
 * Clear cart
 */
export function clearCart(userId: string): void {
  db.prepare('DELETE FROM cart_items WHERE user_id = ?').run(userId);
}

/**
 * Update product inventory
 */
export function updateInventory(productId: string, quantity: number): void {
  db.prepare(
    'UPDATE products SET inventory = inventory - ?, updated_at = datetime(\'now\') WHERE id = ?'
  ).run(quantity, productId);
}

/**
 * Get categories
 */
export function getCategories(): DbCategory[] {
  return db.prepare('SELECT * FROM categories WHERE is_active = 1 ORDER BY sort_order, name').all() as DbCategory[];
}
