USE aihub;

ALTER TABLE forex_academy_modules
  ADD COLUMN access_tier ENUM('free','weekly','monthly') NOT NULL DEFAULT 'weekly' AFTER description;

UPDATE forex_academy_modules SET access_tier = 'free' WHERE module_num = 1;
UPDATE forex_academy_modules SET access_tier = 'weekly' WHERE module_num BETWEEN 2 AND 6;
UPDATE forex_academy_modules SET access_tier = 'monthly' WHERE module_num BETWEEN 7 AND 9;

INSERT IGNORE INTO subscription_plans (plan_code, name, plan_type, price, duration_days, perks_json, status) VALUES
('forex_academy_weekly', 'Forex Academy Weekly', 'forex', 15000, 7, '{"academy_tier":"weekly","unlocks":"beginner+intermediate"}', 'active'),
('forex_academy_monthly', 'Forex Academy Monthly', 'forex', 45000, 30, '{"academy_tier":"monthly","unlocks":"all_levels"}', 'active');

INSERT IGNORE INTO forex_academy_quizzes (module_id, title, pass_score_pct)
SELECT m.id, CONCAT('Module ', m.module_num, ' Assessment'), 70
FROM forex_academy_modules m
WHERE m.module_num IN (2, 4, 6, 7, 8, 9)
AND NOT EXISTS (SELECT 1 FROM forex_academy_quizzes q WHERE q.module_id = m.id);