-- Migration: Monetization and Retention Systems
-- Created: 2025-11-12
-- Phases 1 & 2 of App Store Launch Plan

-- =====================================================
-- 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 INT 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)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Add unlimited energy tracking to players table (if not exists)
-- Note: ALTER TABLE IF NOT EXISTS is not supported, so we check first
SET @dbname = DATABASE();
SET @tablename = 'players';
SET @columnname = 'unlimited_energy_until';
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (table_name = @tablename)
      AND (table_schema = @dbname)
      AND (column_name = @columnname)
  ) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' DATETIME NULL')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- Add diamonds column if not exists
SET @columnname = 'diamonds';
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (table_name = @tablename)
      AND (table_schema = @dbname)
      AND (column_name = @columnname)
  ) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' INT DEFAULT 0 NOT NULL')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- Component 10: Time Skip System
CREATE TABLE IF NOT EXISTS time_skip_purchases (
    id INT PRIMARY KEY AUTO_INCREMENT,
    player_id INT 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)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Component 11: Diamond Economy
CREATE TABLE IF NOT EXISTS diamond_transactions (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    player_id INT 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)
) 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 INT NOT NULL,
    transaction_data JSON,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_player_timestamp (player_id, created_at)
) 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,
    display_name VARCHAR(200) NOT NULL,
    description TEXT,
    category ENUM('life_milestone', 'career', 'relationship', 'collection', 'secret') NOT NULL,
    diamond_reward INT NOT NULL,
    icon_name VARCHAR(100),
    progress_max INT DEFAULT 1,
    hidden BOOLEAN DEFAULT FALSE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_category (category),
    INDEX idx_key (key_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS player_achievements (
    id INT PRIMARY KEY AUTO_INCREMENT,
    player_id INT NOT NULL,
    achievement_id INT NOT NULL,
    progress INT DEFAULT 0,
    unlocked BOOLEAN DEFAULT FALSE,
    unlock_date DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_player_achievement (player_id, achievement_id),
    INDEX idx_player_unlocked (player_id, unlocked)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Component 19: Daily Login Reward System
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 INT 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;

-- Component 20: Daily Quest System
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 INT 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;

-- Component 21: Collection Tracking & Statistics
CREATE TABLE IF NOT EXISTS player_statistics (
    player_id INT PRIMARY KEY,
    lifetime_earnings BIGINT DEFAULT 0,
    lifetime_spending BIGINT DEFAULT 0,
    total_relationships INT DEFAULT 0,
    total_activities INT DEFAULT 0,
    total_conversations INT DEFAULT 0,
    total_years_lived INT DEFAULT 0,
    total_deaths INT DEFAULT 0,
    highest_job_level INT DEFAULT 0,
    max_affinity_reached INT DEFAULT 0,
    people_dated INT DEFAULT 0,
    times_fired INT DEFAULT 0,
    years_married INT DEFAULT 0,
    job_count INT DEFAULT 0,
    children_count INT DEFAULT 0,
    friends_count INT DEFAULT 0,
    ever_married BOOLEAN DEFAULT FALSE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS player_photo_album (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    player_id INT NOT NULL,
    event_type VARCHAR(100) NOT NULL,
    event_description TEXT,
    game_date DATETIME NOT NULL,
    character_age INT,
    snapshot_data JSON,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_player_date (player_id, game_date),
    INDEX idx_event_type (event_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Add inventory tracking (for item ownership counts)
CREATE TABLE IF NOT EXISTS player_inventory (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    player_id INT NOT NULL,
    item_id INT NOT NULL,
    quantity INT DEFAULT 1,
    acquired_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_player_item (player_id, item_id),
    INDEX idx_player (player_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Add initial data

-- Daily rewards (7-day cycle)
INSERT 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')
ON DUPLICATE KEY UPDATE reward_amount=reward_amount;

-- Quest templates
INSERT INTO daily_quest_templates (quest_type, description, progress_required, diamond_reward, difficulty, energy_cost, icon_name) VALUES
-- Easy quests
('talk_to_characters', 'Talk to 3 different characters', 3, 10, 'easy', 9, 'message'),
('buy_item', 'Buy an item from the store', 1, 5, 'easy', 0, 'cart'),
('attend_class', 'Attend 2 classes', 2, 8, 'easy', 6, 'book'),
('socialize', 'Socialize with friends', 2, 10, 'easy', 6, 'person.2'),

-- Medium quests
('work_hours', 'Work for 6 hours', 6, 15, 'medium', 18, 'briefcase'),
('go_on_date', 'Go on a date', 1, 20, 'medium', 15, 'heart'),
('complete_activities', 'Complete 5 activities', 5, 18, 'medium', 25, 'checkmark.circle'),
('study', 'Study for 4 hours', 4, 15, 'medium', 12, 'book.fill'),

-- Hard quests
('spend_energy', 'Spend 50 energy on activities', 50, 25, 'hard', 50, 'bolt'),
('earn_money', 'Earn $500', 500, 30, 'hard', 40, 'dollarsign'),
('increase_affinity', 'Increase affinity by 20 points total', 20, 35, 'hard', 45, 'heart.fill')
ON DUPLICATE KEY UPDATE description=description;

-- =====================================================
-- PHASE 3: ONBOARDING & TUTORIAL SYSTEM
-- =====================================================

-- Component 27: Tutorial State Tracking
CREATE TABLE IF NOT EXISTS player_tutorial_progress (
    player_id INT PRIMARY KEY,
    onboarding_complete BOOLEAN DEFAULT FALSE,
    tutorial_step INT DEFAULT 0,
    tooltips_seen JSON DEFAULT '{}',
    first_session_date DATETIME,
    onboarding_completed_date DATETIME,
    skip_tutorial BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (player_id) REFERENCES players(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Add tutorial milestone tracking
CREATE TABLE IF NOT EXISTS tutorial_milestones (
    id INT PRIMARY KEY AUTO_INCREMENT,
    player_id INT NOT NULL,
    milestone_type ENUM('step_complete', 'tooltip_seen', 'action_completed'),
    milestone_data JSON,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (player_id) REFERENCES players(id),
    INDEX idx_player_timestamp (player_id, timestamp)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
