-- Migration: Relationship Events System (Compatible)
-- Created: 2025-11-12
-- Phase 4: Dating Improvements - Component 34
-- Compatible with lifesim_savegames VARCHAR player IDs

-- =====================================================
-- RELATIONSHIP EVENTS SYSTEM
-- =====================================================

-- Create relationship_events table for event definitions
CREATE TABLE IF NOT EXISTS relationship_events (
    id INT PRIMARY KEY AUTO_INCREMENT,
    event_type ENUM('argument', 'anniversary', 'jealousy', 'proposal', 'breakup_threat', 'gift_request', 'family_meeting') NOT NULL,
    trigger_condition VARCHAR(200) NOT NULL,
    description_template TEXT NOT NULL,
    resolution_options JSON NOT NULL,
    priority INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_event_type (event_type),
    INDEX idx_priority (priority)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create player_relationship_events table for event history
-- Uses VARCHAR for player_id and npc_id to match lifesim_savegames.id
CREATE TABLE IF NOT EXISTS player_relationship_events (
    id INT PRIMARY KEY AUTO_INCREMENT,
    player_id VARCHAR(128) NOT NULL,
    npc_id VARCHAR(128) NOT NULL,
    event_id INT NOT NULL,
    occurred_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    resolution_chosen VARCHAR(100),
    affinity_change INT DEFAULT 0,
    diamond_cost INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_player_npc (player_id, npc_id),
    INDEX idx_occurred_date (occurred_date),
    INDEX idx_event_type (event_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert default relationship event definitions
INSERT IGNORE INTO relationship_events (event_type, trigger_condition, description_template, resolution_options, priority) VALUES
-- Argument Event
('argument', 'affinity_drop_15',
 'You and {partner_name} had a disagreement. They seem upset.',
 '[
    {"choice": "apologize", "label": "Apologize sincerely", "affinity_change": 10, "diamond_cost": 0},
    {"choice": "buy_gift", "label": "Buy a gift to make up", "affinity_change": 25, "diamond_cost": 20},
    {"choice": "ignore", "label": "Give them space", "affinity_change": -10, "diamond_cost": 0}
 ]',
 10),

-- Anniversary Event
('anniversary', 'days_together_365',
 'It''s your {years}year anniversary with {partner_name}!',
 '[
    {"choice": "dinner_date", "label": "Take them to dinner", "affinity_change": 5, "diamond_cost": 0},
    {"choice": "luxury_date", "label": "Plan a luxury date", "affinity_change": 15, "diamond_cost": 50},
    {"choice": "forgot", "label": "You forgot...", "affinity_change": -20, "diamond_cost": 0}
 ]',
 20),

-- Jealousy Event
('jealousy', 'talked_to_other_person',
 '{partner_name} seems jealous of your interactions with others.',
 '[
    {"choice": "reassure", "label": "Reassure them", "affinity_change": 5, "diamond_cost": 0},
    {"choice": "dismiss", "label": "Dismiss their concerns", "affinity_change": -15, "diamond_cost": 0},
    {"choice": "grand_gesture", "label": "Make a grand gesture", "affinity_change": 20, "diamond_cost": 30}
 ]',
 15),

-- Proposal Event
('proposal', 'affinity_above_90',
 '{partner_name} wants to take your relationship to the next level.',
 '[
    {"choice": "accept", "label": "Accept the proposal", "affinity_change": 30, "diamond_cost": 0},
    {"choice": "wait", "label": "Say you need more time", "affinity_change": -5, "diamond_cost": 0},
    {"choice": "decline", "label": "Decline politely", "affinity_change": -40, "diamond_cost": 0}
 ]',
 30),

-- Breakup Threat Event
('breakup_threat', 'affinity_below_20',
 '{partner_name} is considering ending the relationship.',
 '[
    {"choice": "fight_for_it", "label": "Fight for the relationship", "affinity_change": 15, "diamond_cost": 0},
    {"choice": "romantic_gesture", "label": "Make a romantic gesture", "affinity_change": 30, "diamond_cost": 50},
    {"choice": "let_go", "label": "Let them go", "affinity_change": -50, "diamond_cost": 0}
 ]',
 25),

-- Gift Request Event
('gift_request', 'birthday_or_holiday',
 '{partner_name} hints about wanting something special.',
 '[
    {"choice": "thoughtful_gift", "label": "Get a thoughtful gift", "affinity_change": 15, "diamond_cost": 10},
    {"choice": "expensive_gift", "label": "Get an expensive gift", "affinity_change": 25, "diamond_cost": 40},
    {"choice": "forget", "label": "Forget about it", "affinity_change": -25, "diamond_cost": 0}
 ]',
 15),

-- Family Meeting Event
('family_meeting', 'relationship_months_6',
 '{partner_name} wants you to meet their family.',
 '[
    {"choice": "enthusiastic", "label": "Be enthusiastic", "affinity_change": 20, "diamond_cost": 0},
    {"choice": "nervous", "label": "Show you''re nervous but willing", "affinity_change": 10, "diamond_cost": 0},
    {"choice": "make_excuse", "label": "Make an excuse to delay", "affinity_change": -15, "diamond_cost": 0}
 ]',
 20);

SELECT 'Migration 004: Relationship Events (Compatible) - COMPLETED' AS status;
