-- 002_user_panel.sql
USE sanfeshop_db;

-- Extensiones de sa_shops para dominios
ALTER TABLE sa_shops
  ADD COLUMN custom_domain VARCHAR(190) NULL UNIQUE AFTER slug,
  ADD COLUMN domain_status ENUM('none','pending','active','error') NOT NULL DEFAULT 'none' AFTER custom_domain,
  ADD COLUMN cf_dns_record_id VARCHAR(64) NULL AFTER domain_status;

-- Productos básicos (MVP)
CREATE TABLE IF NOT EXISTS sa_products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  shop_id INT NOT NULL,
  name VARCHAR(200) NOT NULL,
  sku VARCHAR(100) NOT NULL,
  price DECIMAL(12,2) NOT NULL DEFAULT 0,
  stock INT NOT NULL DEFAULT 0,
  status ENUM('active','draft') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_shop_sku (shop_id, sku),
  FOREIGN KEY (shop_id) REFERENCES sa_shops(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Clientes (placeholder MVP)
CREATE TABLE IF NOT EXISTS sa_customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  shop_id INT NOT NULL,
  name VARCHAR(180) NOT NULL,
  email VARCHAR(180) NULL,
  phone VARCHAR(40) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (shop_id) REFERENCES sa_shops(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Órdenes (placeholder MVP)
CREATE TABLE IF NOT EXISTS sa_orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  shop_id INT NOT NULL,
  order_number VARCHAR(40) NOT NULL,
  customer_name VARCHAR(180) NOT NULL,
  customer_email VARCHAR(180) NULL,
  total DECIMAL(12,2) NOT NULL DEFAULT 0,
  status ENUM('pending','paid','cancelled') NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_shop_ordernum (shop_id, order_number),
  FOREIGN KEY (shop_id) REFERENCES sa_shops(id) ON DELETE CASCADE
) ENGINE=InnoDB;
