-- Ads hub module — watch to earn + post campaigns (same user account)
USE aihub;

ALTER TABLE subscription_plans
  MODIFY plan_type ENUM('general','sure_odds','casino','forex','ads') NOT NULL DEFAULT 'general';

CREATE TABLE IF NOT EXISTS ad_profiles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL UNIQUE,
  account_type ENUM('individual','company') NOT NULL DEFAULT 'individual',
  display_name VARCHAR(160) NOT NULL,
  company_name VARCHAR(160) NULL,
  company_reg_no VARCHAR(80) NULL,
  category VARCHAR(60) NULL,
  status ENUM('pending','approved','rejected','suspended') NOT NULL DEFAULT 'pending',
  rejection_reason VARCHAR(255) NULL,
  reviewed_by INT NULL,
  reviewed_at DATETIME NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_ad_profile_status (status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS ad_campaigns (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  title VARCHAR(160) NOT NULL,
  description TEXT NULL,
  ad_type ENUM('image','video') NOT NULL DEFAULT 'image',
  media_url VARCHAR(500) NOT NULL,
  media_thumb_url VARCHAR(500) NULL,
  target_url VARCHAR(500) NULL,
  cta_label VARCHAR(80) DEFAULT 'Learn more',
  budget_total DECIMAL(15,2) NOT NULL,
  budget_spent DECIMAL(15,2) NOT NULL DEFAULT 0,
  cost_per_view DECIMAL(15,2) NOT NULL,
  viewer_reward DECIMAL(15,2) NOT NULL,
  platform_fee DECIMAL(15,2) NOT NULL DEFAULT 0,
  min_watch_seconds INT NOT NULL DEFAULT 15,
  completions_count INT NOT NULL DEFAULT 0,
  starts_at DATETIME NULL,
  ends_at DATETIME NULL,
  status ENUM('draft','pending','active','paused','ended','rejected') NOT NULL DEFAULT 'draft',
  rejection_reason VARCHAR(255) NULL,
  reviewed_by INT NULL,
  reviewed_at DATETIME NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_ad_campaign_status (status, starts_at, ends_at),
  INDEX idx_ad_campaign_owner (user_id, status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS ad_views (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  campaign_id INT NOT NULL,
  user_id INT NOT NULL,
  status ENUM('started','completed','rejected') NOT NULL DEFAULT 'started',
  watch_seconds INT NOT NULL DEFAULT 0,
  reward_paid DECIMAL(15,2) NOT NULL DEFAULT 0,
  started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  completed_at DATETIME NULL,
  FOREIGN KEY (campaign_id) REFERENCES ad_campaigns(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_ad_views_user_day (user_id, completed_at),
  INDEX idx_ad_views_campaign (campaign_id, status)
) ENGINE=InnoDB;

INSERT IGNORE INTO modules (slug, name, icon, description, status, sort_order) VALUES
('ads', 'Ads', '📺', 'Watch ads to earn or post your business ads', 'enabled', 12);

UPDATE modules SET status = 'enabled', sort_order = 12, description = 'Watch ads to earn or post your business ads' WHERE slug = 'ads';

INSERT IGNORE INTO subscription_plans (plan_code, name, plan_type, price, duration_days, status) VALUES
('ADS-WEEK', 'Ads Weekly', 'ads', 5000, 7, 'active'),
('ADS-MONTH', 'Ads Monthly', 'ads', 15000, 30, 'active');

INSERT IGNORE INTO unlock_rules (module_id, rule_type, plan_id, sort_order, status)
SELECT m.id, 'subscription', sp.id, sp.id, 'active'
FROM modules m
JOIN subscription_plans sp ON sp.plan_type = 'ads' AND sp.status = 'active'
WHERE m.slug = 'ads'
AND NOT EXISTS (
  SELECT 1 FROM unlock_rules ur WHERE ur.module_id = m.id AND ur.plan_id = sp.id
);

INSERT IGNORE INTO settings (setting_key, setting_value) VALUES
('ads_platform_fee_pct', '60'),
('ads_platform_owner_user_id', ''),
('ads_default_cpv', '250'),
('ads_image_min_seconds', '15'),
('ads_video_min_seconds', '20'),
('ads_video_min_percent', '80'),
('ads_daily_earn_cap', '15000'),
('ads_max_completions_per_day', '40'),
('ads_min_campaign_budget', '5000');