-- Database Update Script for Pixels Dating
-- Run this to add new features to existing database
-- DO NOT run this on a fresh installation

-- Add settings column if it doesn't exist (for older installations)
ALTER TABLE users ADD COLUMN IF NOT EXISTS settings JSON AFTER location_data;

-- Add heartbeat column for real-time online status tracking
ALTER TABLE users ADD COLUMN IF NOT EXISTS heartbeat TIMESTAMP NULL AFTER settings;

-- Add indexes for better performance
CREATE INDEX IF NOT EXISTS idx_users_heartbeat ON users(heartbeat);
CREATE INDEX IF NOT EXISTS idx_users_last_seen ON users(last_seen);
CREATE INDEX IF NOT EXISTS idx_matches_compatibility ON matches(compatibility_score);
CREATE INDEX IF NOT EXISTS idx_messages_recipient ON messages(recipient_id);
CREATE INDEX IF NOT EXISTS idx_messages_sender ON messages(sender_id);

-- Add new columns to messages table for better tracking
ALTER TABLE messages ADD COLUMN IF NOT EXISTS read_at TIMESTAMP NULL AFTER created_at;
ALTER TABLE messages ADD COLUMN IF NOT EXISTS message_type ENUM('text', 'image', 'system') DEFAULT 'text' AFTER message;

-- Add new columns to matches table for better match management
ALTER TABLE matches ADD COLUMN IF NOT EXISTS created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER status;
ALTER TABLE matches ADD COLUMN IF NOT EXISTS last_interaction TIMESTAMP NULL AFTER created_at;

-- Add user preferences table if it doesn't exist
CREATE TABLE IF NOT EXISTS user_preferences (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    preference_key VARCHAR(100) NOT NULL,
    preference_value TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_user_preference (user_id, preference_key)
);

-- Add user activity log table for better tracking
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,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at 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_created (created_at)
);

-- Add notification preferences table
CREATE TABLE IF NOT EXISTS notification_preferences (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    notification_type VARCHAR(50) NOT NULL,
    enabled BOOLEAN DEFAULT TRUE,
    email_enabled BOOLEAN DEFAULT FALSE,
    inworld_enabled BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_user_notification (user_id, notification_type)
);

-- Add user sessions table for better session management
CREATE TABLE IF NOT EXISTS user_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    session_token VARCHAR(255) NOT NULL,
    platform ENUM('web', 'sl') DEFAULT 'web',
    ip_address VARCHAR(45),
    user_agent TEXT,
    last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_session_token (session_token),
    INDEX idx_sessions_user (user_id),
    INDEX idx_sessions_expires (expires_at)
);

-- Update existing data with default values where needed
UPDATE users SET heartbeat = NULL WHERE heartbeat IS NULL;
UPDATE users SET settings = '{}' WHERE settings IS NULL;

-- Add some default notification preferences for existing users
INSERT IGNORE INTO notification_preferences (user_id, notification_type, enabled, email_enabled, inworld_enabled)
SELECT 
    id as user_id,
    'new_match' as notification_type,
    TRUE as enabled,
    FALSE as email_enabled,
    TRUE as inworld_enabled
FROM users;

INSERT IGNORE INTO notification_preferences (user_id, notification_type, enabled, email_enabled, inworld_enabled)
SELECT 
    id as user_id,
    'new_message' as notification_type,
    TRUE as enabled,
    FALSE as email_enabled,
    TRUE as inworld_enabled
FROM users;

INSERT IGNORE INTO notification_preferences (user_id, notification_type, enabled, email_enabled, inworld_enabled)
SELECT 
    id as user_id,
    'profile_view' as notification_type,
    TRUE as enabled,
    FALSE as email_enabled,
    TRUE as inworld_enabled
FROM users;

-- Clean up old sessions (older than 30 days)
DELETE FROM user_sessions WHERE expires_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

-- Add swipe action tables for Tinder-style 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,
    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)
);

-- Update table comments for documentation
ALTER TABLE users COMMENT = 'User accounts with enhanced online status tracking';
ALTER TABLE matches COMMENT = 'User matches with interaction tracking';
ALTER TABLE messages COMMENT = 'Messages with read status and type tracking';
ALTER TABLE user_preferences COMMENT = 'User-specific preference settings';
ALTER TABLE user_activity_log COMMENT = 'Log of user activities for analytics';
ALTER TABLE notification_preferences COMMENT = 'User notification preferences';
ALTER TABLE user_sessions COMMENT = 'Active user sessions across platforms';
ALTER TABLE user_likes COMMENT = 'User like actions for swipe functionality';
ALTER TABLE user_passes COMMENT = 'User pass actions for swipe functionality';
ALTER TABLE user_blocks COMMENT = 'User block actions for swipe functionality';
