-- Migration 005: Enhanced Date Activities
-- Created: 2025-11-12
-- Phase 4: Dating Improvements - Component 35

-- =====================================================
-- DATE ACTIVITIES SYSTEM
-- =====================================================

-- Create date_activities table for mini-game questions and activity definitions
CREATE TABLE IF NOT EXISTS date_activities (
    id INT PRIMARY KEY AUTO_INCREMENT,
    activity_name VARCHAR(100) NOT NULL UNIQUE,
    base_cost INT DEFAULT 0,
    premium_version BOOLEAN DEFAULT FALSE,
    diamond_cost INT DEFAULT 0,
    affinity_gain_min INT NOT NULL,
    affinity_gain_max INT NOT NULL,
    energy_cost INT NOT NULL,
    minigame_questions JSON,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_activity_name (activity_name),
    INDEX idx_premium (premium_version)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create date_history table to track date attempts and outcomes
CREATE TABLE IF NOT EXISTS date_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    player_id INT NOT NULL,
    npc_id INT NOT NULL,
    activity_name VARCHAR(100) NOT NULL,
    performance_score DECIMAL(3,2) NOT NULL,  -- 0.00 to 1.00
    affinity_gained INT NOT NULL,
    energy_spent INT NOT NULL,
    diamonds_spent INT DEFAULT 0,
    date_timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_player_date (player_id, date_timestamp),
    INDEX idx_npc (npc_id),
    INDEX idx_activity (activity_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert default date activity definitions
INSERT INTO date_activities (activity_name, base_cost, premium_version, diamond_cost, affinity_gain_min, affinity_gain_max, energy_cost, minigame_questions)
VALUES
    ('dinner', 0, FALSE, 0, 3, 8, 10, JSON_ARRAY(
        JSON_OBJECT('text', 'Your date mentions loving concerts', 'correct', 'ask_about_music', 'wrong', 'change_subject'),
        JSON_OBJECT('text', 'Your date seems interested in your hobby', 'correct', 'share_more', 'wrong', 'talk_about_work'),
        JSON_OBJECT('text', 'The conversation hits a lull', 'correct', 'ask_open_ended_question', 'wrong', 'check_phone')
    )),
    ('movies', 0, FALSE, 0, 2, 6, 8, JSON_ARRAY(
        JSON_OBJECT('text', 'Your date laughs at a scene', 'correct', 'laugh_together', 'wrong', 'stay_silent'),
        JSON_OBJECT('text', 'Your hands touch on the armrest', 'correct', 'gentle_hand_hold', 'wrong', 'pull_away_quickly')
    )),
    ('coffee', 0, FALSE, 0, 2, 5, 5, JSON_ARRAY(
        JSON_OBJECT('text', 'Your date shares a personal story', 'correct', 'listen_attentively', 'wrong', 'interrupt_with_own_story'),
        JSON_OBJECT('text', 'You notice they like their coffee a certain way', 'correct', 'remember_preference', 'wrong', 'ignore_it')
    )),
    ('walk_in_park', 0, FALSE, 0, 2, 6, 7, JSON_ARRAY(
        JSON_OBJECT('text', 'Your date points out a beautiful view', 'correct', 'appreciate_moment_together', 'wrong', 'keep_walking'),
        JSON_OBJECT('text', 'A dog runs up to you both', 'correct', 'share_playful_moment', 'wrong', 'ignore_dog')
    )),
    ('luxury_restaurant', 0, TRUE, 50, 15, 25, 10, JSON_ARRAY(
        JSON_OBJECT('text', 'The sommelier asks about wine preferences', 'correct', 'ask_date_preference', 'wrong', 'order_without_asking'),
        JSON_OBJECT('text', 'Your date compliments the ambiance', 'correct', 'agree_and_compliment_them', 'wrong', 'say_youve_been_better'),
        JSON_OBJECT('text', 'Conversation flows naturally', 'correct', 'maintain_eye_contact', 'wrong', 'look_around_room'),
        JSON_OBJECT('text', 'Your date shares a childhood memory', 'correct', 'ask_thoughtful_followup', 'wrong', 'change_topic')
    )),
    ('weekend_getaway', 0, TRUE, 100, 25, 40, 20, JSON_ARRAY(
        JSON_OBJECT('text', 'Planning the itinerary together', 'correct', 'collaborate_on_plans', 'wrong', 'take_full_control'),
        JSON_OBJECT('text', 'Your date seems tired from travel', 'correct', 'suggest_relaxing_first', 'wrong', 'push_to_go_out'),
        JSON_OBJECT('text', 'Something unexpected happens', 'correct', 'stay_positive_and_adaptable', 'wrong', 'complain_about_it'),
        JSON_OBJECT('text', 'Intimate conversation by sunset', 'correct', 'be_vulnerable_and_open', 'wrong', 'keep_it_superficial'),
        JSON_OBJECT('text', 'Your date thanks you for the trip', 'correct', 'express_gratitude_back', 'wrong', 'downplay_effort')
    )),
    ('cooking_together', 0, FALSE, 0, 4, 9, 12, JSON_ARRAY(
        JSON_OBJECT('text', 'Your date suggests trying a different recipe', 'correct', 'be_flexible_and_try_it', 'wrong', 'insist_on_original_plan'),
        JSON_OBJECT('text', 'You accidentally make a mess', 'correct', 'laugh_it_off_together', 'wrong', 'get_frustrated'),
        JSON_OBJECT('text', 'Your date compliments your cooking skill', 'correct', 'thank_and_return_compliment', 'wrong', 'brag_about_it')
    )),
    ('concert', 0, TRUE, 30, 10, 18, 15, JSON_ARRAY(
        JSON_OBJECT('text', 'The music is too loud to talk', 'correct', 'enjoy_music_together', 'wrong', 'try_shouting_over_music'),
        JSON_OBJECT('text', 'Your date sings along to a song', 'correct', 'join_in_or_enjoy_watching', 'wrong', 'tell_them_to_stop'),
        JSON_OBJECT('text', 'During a slow song', 'correct', 'hold_them_close', 'wrong', 'stand_awkwardly_apart')
    ))
ON DUPLICATE KEY UPDATE
    base_cost = VALUES(base_cost),
    premium_version = VALUES(premium_version),
    diamond_cost = VALUES(diamond_cost),
    affinity_gain_min = VALUES(affinity_gain_min),
    affinity_gain_max = VALUES(affinity_gain_max),
    energy_cost = VALUES(energy_cost),
    minigame_questions = VALUES(minigame_questions);
