CREATE TABLE IF NOT EXISTS admins (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  name VARCHAR(120) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS stores (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  domain VARCHAR(190) NOT NULL,
  sender_name VARCHAR(120) NULL,
  sender_email VARCHAR(190) NULL,
  merchant_base_url VARCHAR(255) NULL,
  merchant_api_key TEXT NULL,
  merchant_api_secret TEXT NULL,
  contacts_endpoint VARCHAR(255) DEFAULT '/contacts',
  products_endpoint VARCHAR(255) DEFAULT '/products',
  orders_endpoint VARCHAR(255) DEFAULT '/orders',
  brevo_list_id VARCHAR(50) NULL,
  active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS contacts (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  store_id INT NOT NULL,
  external_id VARCHAR(190) NULL,
  email VARCHAR(190) NOT NULL,
  first_name VARCHAR(120) NULL,
  last_name VARCHAR(120) NULL,
  phone VARCHAR(80) NULL,
  marketing_consent TINYINT(1) DEFAULT 0,
  unsubscribed TINYINT(1) DEFAULT 0,
  unsubscribed_at DATETIME NULL,
  unsubscribe_token VARCHAR(64) NOT NULL,
  raw_json LONGTEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_store_email (store_id,email),
  KEY idx_contacts_email (email),
  CONSTRAINT fk_contacts_store FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS products (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  store_id INT NOT NULL,
  external_id VARCHAR(190) NULL,
  sku VARCHAR(190) NOT NULL,
  name VARCHAR(500) NULL,
  url TEXT NULL,
  image_url TEXT NULL,
  price DECIMAL(12,2) DEFAULT 0,
  stock DECIMAL(12,3) DEFAULT 0,
  active TINYINT(1) DEFAULT 1,
  raw_json LONGTEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_store_sku (store_id,sku),
  CONSTRAINT fk_products_store FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS orders (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  store_id INT NOT NULL,
  external_id VARCHAR(190) NOT NULL,
  email VARCHAR(190) NULL,
  total DECIMAL(12,2) DEFAULT 0,
  currency VARCHAR(10) DEFAULT 'RON',
  status VARCHAR(100) NULL,
  ordered_at DATETIME NULL,
  raw_json LONGTEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_store_order (store_id,external_id),
  KEY idx_orders_email (email),
  CONSTRAINT fk_orders_store FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS segments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  store_id INT NULL,
  name VARCHAR(190) NOT NULL,
  type VARCHAR(80) NOT NULL,
  rules_json LONGTEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_segments_store FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS campaigns (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  store_id INT NOT NULL,
  name VARCHAR(190) NOT NULL,
  subject VARCHAR(255) NOT NULL,
  html_content LONGTEXT NOT NULL,
  status ENUM('draft','scheduled','sent','error') DEFAULT 'draft',
  provider_campaign_id VARCHAR(100) NULL,
  scheduled_at DATETIME NULL,
  sent_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_campaigns_store FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sync_logs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  store_id INT NOT NULL,
  status VARCHAR(30) NOT NULL,
  result_json LONGTEXT NULL,
  started_at DATETIME NULL,
  finished_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY idx_sync_store (store_id),
  CONSTRAINT fk_sync_store FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;