-- Migration 006: Missing Retention Tables
-- Created: 2025-11-12
-- Adds tables that backend code expects but were missing from 001_no_fk migration

-- =====================================================
-- DAILY LOGIN REWARDS (Backend expects these names)
-- =====================================================

CREATE TABLE IF NOT EXISTS daily_login_rewards (
    day_number INT PRIMARY KEY,
    reward_type ENUM('diamonds', 'energy', 'item', 'prestige') NOT NULL,
    reward_amount INT NOT NULL,
    reward_item_id INT NULL,
    display_name VARCHAR(100),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS player_login_streak (
    player_id VARCHAR(128) PRIMARY KEY,
    current_streak INT DEFAULT 0,
    last_login_date DATE,
    total_logins INT DEFAULT 0,
    next_reward_day INT DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed daily login rewards (7-day cycle)
INSERT IGNORE INTO daily_login_rewards (day_number, reward_type, reward_amount, display_name) VALUES
(1, 'diamonds', 5, '5 Diamonds'),
(2, 'diamonds', 10, '10 Diamonds'),
(3, 'energy', 50, '50 Energy'),
(4, 'diamonds', 15, '15 Diamonds'),
(5, 'diamonds', 20, '20 Diamonds + Item'),
(6, 'diamonds', 25, '25 Diamonds'),
(7, 'diamonds', 50, '50 Diamonds Bonus');

-- =====================================================
-- DAILY QUESTS (Backend expects 'daily_quest_templates')
-- =====================================================

CREATE TABLE IF NOT EXISTS daily_quest_templates (
    id INT PRIMARY KEY AUTO_INCREMENT,
    quest_type VARCHAR(100) NOT NULL,
    description VARCHAR(200) NOT NULL,
    progress_required INT NOT NULL,
    diamond_reward INT NOT NULL,
    difficulty ENUM('easy', 'medium', 'hard') NOT NULL,
    energy_cost INT DEFAULT 0,
    icon_name VARCHAR(100),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_difficulty (difficulty)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS player_daily_quests (
    id INT PRIMARY KEY AUTO_INCREMENT,
    player_id VARCHAR(128) NOT NULL,
    quest_template_id INT NOT NULL,
    progress INT DEFAULT 0,
    completed BOOLEAN DEFAULT FALSE,
    assigned_date DATE NOT NULL,
    completed_date DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_player_date (player_id, assigned_date),
    INDEX idx_player_completed (player_id, completed)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed quest templates
INSERT IGNORE INTO daily_quest_templates (quest_type, description, progress_required, diamond_reward, difficulty, energy_cost, icon_name) VALUES
('complete_activities', 'Complete 5 activities', 5, 10, 'easy', 0, 'checkmark.circle'),
('earn_money', 'Earn $500', 500, 15, 'medium', 0, 'dollarsign.circle'),
('build_relationships', 'Have 3 conversations', 3, 10, 'easy', 0, 'message.circle'),
('study_hard', 'Study for 2 hours', 2, 20, 'medium', 0, 'book.circle'),
('work_hours', 'Work for 4 hours', 4, 25, 'hard', 0, 'briefcase.circle'),
('health_focus', 'Maintain health above 80%', 80, 15, 'medium', 0, 'heart.circle'),
('relationship_growth', 'Increase affinity by 10 points', 10, 20, 'medium', 0, 'heart.fill'),
('new_skill', 'Improve any skill by 5 points', 5, 15, 'easy', 0, 'star.circle'),
('save_money', 'Save $200 without spending', 200, 20, 'hard', 0, 'banknote.circle'),
('attend_events', 'Attend 2 random events', 2, 10, 'easy', 0, 'calendar.circle'),
('perfect_day', 'Keep all stats above 50% for 24 hours', 24, 50, 'hard', 0, 'sparkles');

SELECT 'Migration 006: Missing Retention Tables - COMPLETED' AS status;
