-- Classifieds SaaS schema (MySQL 8+)

CREATE TABLE IF NOT EXISTS plans (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(50) UNIQUE,
  name VARCHAR(100),
  price_monthly_cents INT DEFAULT 0,
  limits_json TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS tenants (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  slug VARCHAR(50) UNIQUE NOT NULL,
  plan_id INT NOT NULL,
  status ENUM('active','suspended') DEFAULT 'active',
  stripe_customer_id VARCHAR(120) NULL,
  stripe_subscription_id VARCHAR(120) NULL,
  subscription_status VARCHAR(50) DEFAULT 'none',
  current_period_end DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (plan_id) REFERENCES plans(id)
);

CREATE TABLE IF NOT EXISTS categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  name VARCHAR(120) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_tenant (tenant_id),
  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS ads (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  user_id INT NOT NULL,
  category_id INT NOT NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT NOT NULL,
  price DECIMAL(10,2) DEFAULT 0,
  location_city VARCHAR(80) NOT NULL,
  location_state VARCHAR(20) NOT NULL,
  location_country VARCHAR(50) DEFAULT 'US',
  status ENUM('pending','approved','rejected') DEFAULT 'pending',
  is_featured TINYINT(1) DEFAULT 0,
  expires_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_tenant_status (tenant_id, status),
  INDEX idx_location (tenant_id, location_state, location_city),
  INDEX idx_category (tenant_id, category_id),
  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT
);

CREATE TABLE IF NOT EXISTS ad_images (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ad_id INT NOT NULL,
  path VARCHAR(255) NOT NULL,
  sort_order INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (ad_id) REFERENCES ads(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS ad_moderation_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  ad_id INT NOT NULL,
  admin_user_id INT NOT NULL,
  action ENUM('approved','rejected') NOT NULL,
  reason VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_tenant_created (tenant_id, created_at),
  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  FOREIGN KEY (ad_id) REFERENCES ads(id) ON DELETE CASCADE,
  FOREIGN KEY (admin_user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Seed plans
INSERT INTO plans (code, name, price_monthly_cents, limits_json) VALUES
('basic', 'Basic', 0, JSON_OBJECT('ads_per_month', 50, 'images_per_ad', 6, 'featured_ads', 0))
ON DUPLICATE KEY UPDATE name=VALUES(name);

INSERT INTO plans (code, name, price_monthly_cents, limits_json) VALUES
('pro', 'Pro', 4900, JSON_OBJECT('ads_per_month', 500, 'images_per_ad', 12, 'featured_ads', 25))
ON DUPLICATE KEY UPDATE name=VALUES(name);
