-- Migration: Monetization and Retention Systems (Compatible with lifesim_savegames)
-- Created: 2025-11-12
-- Compatible with existing JSON blob storage in lifesim_savegames
--
-- NOTE: This migration creates new tables for Phase 1-7 features without
-- requiring a normalized 'players' table. Player data continues to be stored
-- in lifesim_savegames.json and lifesim_savegames.pickle_data
--
-- Foreign keys reference lifesim_savegames.id (VARCHAR) as player_id

-- =====================================================
-- PHASE 1: MONETIZATION FOUNDATION
-- =====================================================

-- Component 9: Energy Refill System
CREATE TABLE IF NOT EXISTS energy_refill_purchases (
    id INT PRIMARY KEY AUTO_INCREMENT,
    player_id VARCHAR(128) NOT NULL,
    refill_type ENUM('small', 'medium', 'full', 'unlimited_24h') NOT NULL,
    energy_amount INT NOT NULL,
    diamond_cost INT NOT NULL,
    energy_before INT NOT NULL,
    energy_after INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_player_timestamp (player_id, created_at),
    FOREIGN KEY (player_id) REFERENCES lifesim_savegames(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Component 10: Time Skip System
CREATE TABLE IF NOT EXISTS time_skip_purchases (
    id INT PRIMARY KEY AUTO_INCREMENT,
    player_id VARCHAR(128) NOT NULL,
    skip_type ENUM('1hour', '1day', '1week', 'next_event') NOT NULL,
    diamond_cost INT NOT NULL,
    time_before DATETIME NOT NULL,
    time_after DATETIME NOT NULL,
    events_simulated INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_player_timestamp (player_id, created_at),
    FOREIGN KEY (player_id) REFERENCES lifesim_savegames(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Component 11: Diamond Economy
CREATE TABLE IF NOT EXISTS diamond_transactions (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    player_id VARCHAR(128) NOT NULL,
    transaction_type ENUM('earn', 'spend') NOT NULL,
    amount INT NOT NULL,
    reason VARCHAR(200),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_player_timestamp (player_id, created_at),
    INDEX idx_type (transaction_type),
    FOREIGN KEY (player_id) REFERENCES lifesim_savegames(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Component 12: Purchase Validation & Anti-Cheat
CREATE TABLE IF NOT EXISTS processed_transactions (
    idempotency_key VARCHAR(64) PRIMARY KEY,
    player_id VARCHAR(128) NOT NULL,
    transaction_data JSON,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_player_timestamp (player_id, created_at),
    FOREIGN KEY (player_id) REFERENCES lifesim_savegames(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- PHASE 2: RETENTION MECHANICS
-- =====================================================

-- Component 18: Achievement System
CREATE TABLE IF NOT EXISTS achievements (
    id INT PRIMARY KEY AUTO_INCREMENT,
    key_name VARCHAR(100) UNIQUE NOT NULL,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    category ENUM('career', 'relationships', 'education', 'wealth', 'activities') NOT NULL,
    reward_diamonds INT DEFAULT 0,
    requirement_description TEXT,
    requirement_type VARCHAR(50),
    requirement_value INT,
    icon VARCHAR(50),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS player_achievements (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    player_id VARCHAR(128) NOT NULL,
    achievement_id INT NOT NULL,
    progress INT DEFAULT 0,
    progress_required INT,
    unlocked BOOLEAN DEFAULT FALSE,
    unlocked_at DATETIME NULL,
    acknowledged BOOLEAN DEFAULT FALSE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_player_achievement (player_id, achievement_id),
    INDEX idx_player_unlocked (player_id, unlocked),
    FOREIGN KEY (player_id) REFERENCES lifesim_savegames(id) ON DELETE CASCADE,
    FOREIGN KEY (achievement_id) REFERENCES achievements(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Component 19: Daily Rewards System
CREATE TABLE IF NOT EXISTS daily_rewards_state (
    player_id VARCHAR(128) PRIMARY KEY,
    current_streak INT DEFAULT 0,
    last_login_date DATE,
    last_claimed_day INT DEFAULT 0,
    rewards_data JSON,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (player_id) REFERENCES lifesim_savegames(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS daily_reward_claims (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    player_id VARCHAR(128) NOT NULL,
    day INT NOT NULL,
    diamonds_earned INT DEFAULT 0,
    energy_earned INT DEFAULT 0,
    money_earned INT DEFAULT 0,
    claimed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_player_day (player_id, day),
    FOREIGN KEY (player_id) REFERENCES lifesim_savegames(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Component 20: Daily Quests System
CREATE TABLE IF NOT EXISTS daily_quests (
    id INT PRIMARY KEY AUTO_INCREMENT,
    quest_type VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    requirement_value INT NOT NULL,
    reward_diamonds INT DEFAULT 0,
    reward_energy INT DEFAULT 0,
    reward_money INT DEFAULT 0,
    difficulty ENUM('easy', 'medium', 'hard') DEFAULT 'easy',
    category VARCHAR(50),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS player_daily_quests (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    player_id VARCHAR(128) NOT NULL,
    quest_id INT NOT NULL,
    progress INT DEFAULT 0,
    progress_required INT NOT NULL,
    completed BOOLEAN DEFAULT FALSE,
    claimed BOOLEAN DEFAULT FALSE,
    quest_date DATE NOT NULL,
    completed_at DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_player_quest_date (player_id, quest_id, quest_date),
    INDEX idx_player_date (player_id, quest_date),
    INDEX idx_completed (completed),
    FOREIGN KEY (player_id) REFERENCES lifesim_savegames(id) ON DELETE CASCADE,
    FOREIGN KEY (quest_id) REFERENCES daily_quests(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Component 21: Player Statistics Tracking
CREATE TABLE IF NOT EXISTS player_statistics (
    player_id VARCHAR(128) PRIMARY KEY,
    total_play_time_minutes INT DEFAULT 0,
    total_activities_completed INT DEFAULT 0,
    total_relationships_formed INT DEFAULT 0,
    total_jobs_held INT DEFAULT 0,
    total_money_earned BIGINT DEFAULT 0,
    total_money_spent BIGINT DEFAULT 0,
    total_diamonds_earned INT DEFAULT 0,
    total_diamonds_spent INT DEFAULT 0,
    achievements_unlocked INT DEFAULT 0,
    daily_rewards_claimed INT DEFAULT 0,
    daily_quests_completed INT DEFAULT 0,
    energy_refills_purchased INT DEFAULT 0,
    time_skips_purchased INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (player_id) REFERENCES lifesim_savegames(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- PHASE 7: GDPR COMPLIANCE
-- =====================================================

-- Component 43: Data Export & Deletion
CREATE TABLE IF NOT EXISTS data_export_requests (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    player_id VARCHAR(128) NOT NULL,
    export_data LONGTEXT,
    status ENUM('pending', 'completed', 'failed') DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    completed_at DATETIME NULL,
    INDEX idx_player_status (player_id, status),
    FOREIGN KEY (player_id) REFERENCES lifesim_savegames(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS account_deletion_requests (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    player_id VARCHAR(128) NOT NULL,
    scheduled_deletion_date DATE NOT NULL,
    status ENUM('pending', 'cancelled', 'completed') DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    cancelled_at DATETIME NULL,
    completed_at DATETIME NULL,
    INDEX idx_player_status (player_id, status),
    INDEX idx_scheduled_date (scheduled_deletion_date, status),
    FOREIGN KEY (player_id) REFERENCES lifesim_savegames(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- SEED DATA: Default Achievements
-- =====================================================

INSERT IGNORE INTO achievements (key_name, name, description, category, reward_diamonds, requirement_description, requirement_type, requirement_value) VALUES
-- Education Achievements
('first_day_school', 'First Day', 'Started elementary school', 'education', 5, 'Start elementary school', 'event', 1),
('high_school_grad', 'High School Graduate', 'Graduated from high school', 'education', 25, 'Graduate high school', 'event', 1),
('college_grad', 'College Graduate', 'Earned a college degree', 'education', 50, 'Graduate college', 'event', 1),
('straight_a_student', 'Straight A Student', 'Maintained perfect grades for a full year', 'education', 100, 'Get all A grades for 1 year', 'stat', 1),

-- Career Achievements
('first_job', 'First Job', 'Got your first job', 'career', 10, 'Get first job', 'event', 1),
('promotion', 'Climbing the Ladder', 'Received a promotion', 'career', 20, 'Get promoted', 'event', 1),
('millionaire', 'Millionaire', 'Accumulated $1,000,000', 'wealth', 200, 'Reach $1,000,000', 'money', 1000000),
('employee_of_month', 'Employee of the Month', 'Earned employee recognition', 'career', 15, 'Win employee of month', 'event', 1),

-- Relationship Achievements
('first_friend', 'Social Butterfly', 'Made your first friend', 'relationships', 10, 'Make first friend', 'event', 1),
('first_kiss', 'First Kiss', 'Shared your first kiss', 'relationships', 15, 'Experience first kiss', 'event', 1),
('engaged', 'Engaged', 'Got engaged to be married', 'relationships', 50, 'Get engaged', 'event', 1),
('married', 'Happily Married', 'Got married', 'relationships', 75, 'Get married', 'event', 1),
('parent', 'Parent', 'Had your first child', 'relationships', 100, 'Have first child', 'event', 1),

-- Activities Achievements
('extracurricular', 'Well Rounded', 'Joined an extracurricular activity', 'activities', 10, 'Join activity', 'event', 1),
('athlete', 'Athletic Achievement', 'Excelled at a sport', 'activities', 25, 'Excel at sports', 'stat', 5),
('musician', 'Musical Talent', 'Learned to play an instrument', 'activities', 25, 'Learn instrument', 'event', 1)
;

-- =====================================================
-- SEED DATA: Default Daily Quests
-- =====================================================

INSERT IGNORE INTO daily_quests (quest_type, name, description, requirement_value, reward_diamonds, reward_energy, reward_money, difficulty, category) VALUES
('complete_activities', 'Busy Day', 'Complete 5 activities', 5, 10, 20, 100, 'easy', 'general'),
('earn_money', 'Money Maker', 'Earn $500', 500, 15, 0, 0, 'medium', 'wealth'),
('build_relationships', 'Social Hour', 'Have 3 conversations', 3, 10, 15, 0, 'easy', 'social'),
('study_hard', 'Scholar', 'Study for 2 hours', 2, 20, 0, 0, 'medium', 'education'),
('work_hours', 'Hard Worker', 'Work for 4 hours', 4, 25, 0, 200, 'hard', 'career'),
('health_focus', 'Health Conscious', 'Maintain health above 80%', 80, 15, 25, 0, 'medium', 'health'),
('relationship_growth', 'Relationship Builder', 'Increase affinity with someone by 10 points', 10, 20, 0, 0, 'medium', 'relationships'),
('new_skill', 'Skill Up', 'Improve any skill by 5 points', 5, 15, 20, 0, 'easy', 'skills'),
('save_money', 'Saver', 'Save $200 without spending', 200, 20, 0, 0, 'hard', 'wealth'),
('attend_events', 'Event Goer', 'Attend 2 random events', 2, 10, 15, 0, 'easy', 'general'),
('perfect_day', 'Perfect Day', 'Keep all stats above 50% for 24 hours', 24, 50, 30, 500, 'hard', 'general')
;

-- Migration complete
SELECT 'Migration 001: Monetization and Retention (Compatible) - COMPLETED' AS status;
