-- Pixels Dating Database Schema
-- Second Life Dating Platform

-- Users table
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    sl_key VARCHAR(36) UNIQUE NOT NULL,
    username VARCHAR(50) NOT NULL,
    display_name VARCHAR(100),
    email VARCHAR(100),
    password_hash VARCHAR(255),
    profile_data JSON,
    interests JSON,
    location_data JSON,
    settings JSON,
    heartbeat TIMESTAMP NULL,
    last_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Matches table
CREATE TABLE matches (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user1_id INT,
    user2_id INT,
    compatibility_score DECIMAL(5,2),
    status ENUM('pending', 'accepted', 'rejected', 'blocked'),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user1_id) REFERENCES users(id),
    FOREIGN KEY (user2_id) REFERENCES users(id)
);

-- Messages table
CREATE TABLE messages (
    id INT PRIMARY KEY AUTO_INCREMENT,
    sender_id INT,
    recipient_id INT,
    message TEXT,
    platform ENUM('sl', 'web'),
    read_status BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (sender_id) REFERENCES users(id),
    FOREIGN KEY (recipient_id) REFERENCES users(id)
);

-- Compatibility scores table
CREATE TABLE compatibility_scores (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user1_id INT,
    user2_id INT,
    interest_match DECIMAL(5,2),
    location_proximity DECIMAL(5,2),
    activity_match DECIMAL(5,2),
    overall_score DECIMAL(5,2),
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user1_id) REFERENCES users(id),
    FOREIGN KEY (user2_id) REFERENCES users(id),
    UNIQUE KEY unique_pair (user1_id, user2_id)
);

-- Events table for dating events
CREATE TABLE events (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    event_type ENUM('speed_dating', 'group_date', 'virtual_venue', 'game_night'),
    region_name VARCHAR(100),
    coordinates JSON,
    start_time TIMESTAMP,
    end_time TIMESTAMP,
    max_participants INT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id)
);

-- Event participants table
CREATE TABLE event_participants (
    id INT PRIMARY KEY AUTO_INCREMENT,
    event_id INT,
    user_id INT,
    status ENUM('registered', 'attended', 'no_show'),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (event_id) REFERENCES events(id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    UNIQUE KEY unique_participation (event_id, user_id)
);

-- User preferences table
CREATE TABLE user_preferences (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    age_range_min INT,
    age_range_max INT,
    species_preference JSON,
    style_preference JSON,
    activity_preference JSON,
    location_preference JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Indexes for better performance
CREATE INDEX idx_users_sl_key ON users(sl_key);
CREATE INDEX idx_users_last_seen ON users(last_seen);
CREATE INDEX idx_matches_user1 ON matches(user1_id);
CREATE INDEX idx_matches_user2 ON matches(user2_id);
CREATE INDEX idx_messages_recipient ON messages(recipient_id);
CREATE INDEX idx_messages_sender ON messages(sender_id);
CREATE INDEX idx_compatibility_user1 ON compatibility_scores(user1_id);
CREATE INDEX idx_compatibility_user2 ON compatibility_scores(user2_id);
CREATE INDEX idx_events_start_time ON events(start_time);
CREATE INDEX idx_event_participants_event ON event_participants(event_id);
