-- Social modules, ads poster settings, advertiser plans

ALTER TABLE subscription_plans
  MODIFY plan_type ENUM('general','sure_odds','football','casino','forex','ads','ads_poster','surveys','social_media','social_posts') NOT NULL DEFAULT 'general';

INSERT IGNORE INTO subscription_plans (plan_code, name, plan_type, price, duration_days, perks_json, status) VALUES
('ADS-POST-WEEK', 'Post Ads Weekly', 'ads_poster', 5000, 7, '{"minFollowers":100}', 'active'),
('ADS-POST-MONTH', 'Post Ads Monthly', 'ads_poster', 15000, 30, '{"minFollowers":100}', 'active'),
('SOCIAL-WEEK', 'Social Media Weekly', 'social_media', 10000, 7, '{}', 'active'),
('SOCIAL-MONTH', 'Social Media Monthly', 'social_media', 35000, 30, '{}', 'active'),
('SPOST-WEEK', 'Social Posts Weekly', 'social_posts', 12000, 7, '{}', 'active'),
('SPOST-MONTH', 'Social Posts Monthly', 'social_posts', 40000, 30, '{}', 'active');

INSERT IGNORE INTO modules (slug, name, icon, description, status, sort_order) VALUES
('social_media', 'Social Media', '📱', 'Grow followers, likes & subs on TikTok, Instagram, YouTube & more', 'coming_soon', 13),
('social_posts', 'Social Posts', '📣', 'Schedule one post across all your social platforms', 'coming_soon', 14);

INSERT IGNORE INTO settings (setting_key, setting_value) VALUES
('ads_min_followers', '100'),
('ads_poster_min_budget', '5000'),
('social_platform_fee_pct', '60'),
('social_posts_platform_fee_pct', '60');

CREATE TABLE IF NOT EXISTS sm_accounts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  platform ENUM('tiktok','instagram','twitter','youtube','facebook','linkedin','other') NOT NULL,
  handle VARCHAR(120) NOT NULL,
  profile_url VARCHAR(500) NULL,
  follower_count INT DEFAULT 0,
  status ENUM('pending','active','rejected') DEFAULT 'pending',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_sm_user (user_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS sm_orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  account_id INT NULL,
  platform VARCHAR(40) NOT NULL,
  service_type ENUM('followers','likes','views','comments','subscribers') NOT NULL,
  quantity INT NOT NULL,
  target_url VARCHAR(500) NOT NULL,
  price DECIMAL(12,2) NOT NULL,
  status ENUM('pending','processing','completed','cancelled') DEFAULT 'pending',
  admin_note TEXT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  completed_at DATETIME NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (account_id) REFERENCES sm_accounts(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS sp_posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  body TEXT NOT NULL,
  media_urls JSON NULL,
  scheduled_at DATETIME NULL,
  status ENUM('draft','scheduled','published','failed') DEFAULT 'draft',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  published_at DATETIME NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS sp_post_targets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  post_id INT NOT NULL,
  platform VARCHAR(40) NOT NULL,
  status ENUM('pending','published','failed') DEFAULT 'pending',
  external_id VARCHAR(120) NULL,
  error_message VARCHAR(255) NULL,
  FOREIGN KEY (post_id) REFERENCES sp_posts(id) ON DELETE CASCADE
) ENGINE=InnoDB;