-- Fixed Matching System Schema Update
-- This works with your existing user_preferences table structure

-- Compatibility scores table
CREATE TABLE IF NOT EXISTS compatibility_scores (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user1_id INT NOT NULL,
    user2_id INT NOT NULL,
    overall_score DECIMAL(5,2) NOT NULL DEFAULT 0,
    detailed_scores JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user1_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (user2_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_compatibility (user1_id, user2_id),
    INDEX idx_compatibility_score (overall_score),
    INDEX idx_user1_compatibility (user1_id, overall_score),
    INDEX idx_user2_compatibility (user2_id, overall_score)
);

-- Enhanced matches table
CREATE TABLE IF NOT EXISTS matches (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user1_id INT NOT NULL,
    user2_id INT NOT NULL,
    status ENUM('active', 'inactive', 'blocked') DEFAULT 'active',
    compatibility_score DECIMAL(5,2) DEFAULT 0,
    last_interaction TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user1_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (user2_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_match (user1_id, user2_id),
    INDEX idx_matches_user1 (user1_id, status),
    INDEX idx_matches_user2 (user2_id, status),
    INDEX idx_matches_status (status),
    INDEX idx_matches_compatibility (compatibility_score)
);

-- User interactions for swipe functionality
CREATE TABLE IF NOT EXISTS user_likes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    liked_user_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (liked_user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_like (user_id, liked_user_id),
    INDEX idx_likes_user (user_id),
    INDEX idx_likes_liked (liked_user_id)
);

CREATE TABLE IF NOT EXISTS user_passes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    passed_user_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (passed_user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_pass (user_id, passed_user_id),
    INDEX idx_passes_user (user_id),
    INDEX idx_passes_passed (passed_user_id)
);

CREATE TABLE IF NOT EXISTS user_blocks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    blocked_user_id INT NOT NULL,
    reason VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (blocked_user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_block (user_id, blocked_user_id),
    INDEX idx_blocks_user (user_id),
    INDEX idx_blocks_blocked (blocked_user_id)
);

-- User activity log for better matching
CREATE TABLE IF NOT EXISTS user_activity_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    activity_type VARCHAR(50) NOT NULL,
    activity_data JSON,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_activity_user (user_id),
    INDEX idx_activity_type (activity_type),
    INDEX idx_activity_timestamp (timestamp)
);

-- Insert default preferences for existing users using the correct table structure
INSERT IGNORE INTO user_preferences (user_id, age_range_min, age_range_max, species_preference, style_preference, activity_preference, location_preference)
SELECT 
    id as user_id,
    18 as age_range_min,
    100 as age_range_max,
    '[]' as species_preference,
    '[]' as style_preference,
    '[]' as activity_preference,
    '{}' as location_preference
FROM users
WHERE id NOT IN (SELECT user_id FROM user_preferences WHERE user_id IS NOT NULL);

-- Update existing user_preferences records that might have NULL values
UPDATE user_preferences 
SET 
    age_range_min = COALESCE(age_range_min, 18),
    age_range_max = COALESCE(age_range_max, 100),
    species_preference = COALESCE(species_preference, '[]'),
    style_preference = COALESCE(style_preference, '[]'),
    activity_preference = COALESCE(activity_preference, '[]'),
    location_preference = COALESCE(location_preference, '{}')
WHERE user_id IS NOT NULL;
