-- Migration: Dating Compatibility Algorithm
-- Created: 2025-11-12
-- Phase 4: Dating Improvements - Component 32

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

-- Create match_attempts table for tracking dating matches
CREATE TABLE IF NOT EXISTS match_attempts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    player_id INT NOT NULL,
    target_id INT 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;

-- Add interests column to persons table
SET @dbname = DATABASE();
SET @tablename = 'persons';
SET @columnname = 'interests';
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, ' JSON DEFAULT (JSON_ARRAY())')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- Add personality_traits column to persons table
SET @columnname = 'personality_traits';
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, ' JSON DEFAULT (JSON_ARRAY())')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;
