-- Migration: Dating Compatibility Algorithm (Compatible)
-- Created: 2025-11-12
-- Phase 4: Dating Improvements - Component 32
-- Compatible with lifesim_savegames player IDs

-- =====================================================
-- COMPATIBILITY ALGORITHM SYSTEM
-- =====================================================

-- Create match_attempts table for tracking dating matches
-- Uses VARCHAR for player_id to match lifesim_savegames.id format
CREATE TABLE IF NOT EXISTS match_attempts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    player_id VARCHAR(128) NOT NULL,
    target_id VARCHAR(128) NOT NULL,
    compatibility_score INT DEFAULT 50,
    success BOOLEAN DEFAULT FALSE,
    attempted_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_player_date (player_id, attempted_date),
    INDEX idx_target (target_id),
    INDEX idx_compatibility (compatibility_score)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- NOTE: Interests and personality_traits are stored in JSON blob
-- within lifesim_savegames as:
--   person.interests (array)
--   person.personality_traits (array)
--
-- The backend dating/matching.py module handles this automatically

SELECT 'Migration 003: Dating Compatibility (Compatible) - COMPLETED' AS status,
       'match_attempts table created, interests/traits in JSON blob' AS note;
