-- MyAli — Phase 0 schema
-- Tables: stores, import_logs, supplier_products
-- Run via:  npm run migrate   (creates DB if missing, then applies this file)
-- Or manually:  mysql -u root -p myali < schema.sql

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS stores (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  api_token VARCHAR(255) NOT NULL UNIQUE,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS import_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  store_id INT NOT NULL,
  source_url VARCHAR(1024),
  status ENUM('received','processed','failed') NOT NULL DEFAULT 'received',
  error_message TEXT,
  payload LONGTEXT,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_log_store FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE CASCADE,
  INDEX idx_store_created (store_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS supplier_products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  store_id INT NOT NULL,
  aliexpress_id VARCHAR(64),
  source_url VARCHAR(1024),
  title VARCHAR(512),
  description LONGTEXT,
  main_image VARCHAR(1024),
  images JSON,
  price_min DECIMAL(12,2),
  price_max DECIMAL(12,2),
  currency VARCHAR(8) DEFAULT 'USD',
  original_price_min DECIMAL(12,2),
  original_price_max DECIMAL(12,2),
  attributes JSON,
  variants JSON,
  seller JSON,
  shipping JSON,
  raw_data JSON,
  status ENUM('staged','published','archived') NOT NULL DEFAULT 'staged',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_sp_store FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE CASCADE,
  INDEX idx_store_status (store_id, status),
  INDEX idx_aliexpress_id (aliexpress_id),
  UNIQUE KEY uq_store_ae (store_id, aliexpress_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Default MyAli store (change the token in production)
INSERT INTO stores (name, api_token)
SELECT 'MyAli Store', 'myali-dev-token-change-me'
WHERE NOT EXISTS (
  SELECT 1 FROM stores WHERE api_token = 'myali-dev-token-change-me'
);
