-- RoyalPandit Chat Application Database Schema

-- Users Table
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    unique_id VARCHAR(20) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    mobile VARCHAR(20) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    profile_photo VARCHAR(255) DEFAULT NULL,
    status ENUM('pending', 'approved', 'blocked') DEFAULT 'pending',
    is_admin TINYINT(1) DEFAULT 0,
    is_ghost_mode TINYINT(1) DEFAULT 0,
    failed_login_attempts INT DEFAULT 0,
    last_failed_login DATETIME DEFAULT NULL,
    last_seen DATETIME DEFAULT NULL,
    last_activity DATETIME DEFAULT CURRENT_TIMESTAMP,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_unique_id (unique_id),
    INDEX idx_status (status),
    INDEX idx_email (email),
    INDEX idx_last_activity (last_activity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- OTP Codes Table
CREATE TABLE IF NOT EXISTS otp_codes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    otp_code VARCHAR(10) NOT NULL,
    type ENUM('login', 'signup', 'reset') DEFAULT 'login',
    expires_at DATETIME NOT NULL,
    used TINYINT(1) DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_otp (user_id, otp_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sessions Table
CREATE TABLE IF NOT EXISTS sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    session_token VARCHAR(255) UNIQUE NOT NULL,
    device_info TEXT,
    ip_address VARCHAR(45),
    is_valid TINYINT(1) DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NOT NULL,
    last_activity DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_session (user_id, session_token),
    INDEX idx_token (session_token),
    INDEX idx_last_activity (last_activity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Messages Table
CREATE TABLE IF NOT EXISTS messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sender_id INT NOT NULL,
    receiver_id INT NOT NULL,
    message TEXT NOT NULL,
    message_type ENUM('text', 'image', 'video', 'gif', 'voice', 'file') DEFAULT 'text',
    file_url VARCHAR(500) DEFAULT NULL,
    file_name VARCHAR(255) DEFAULT NULL,
    file_size INT DEFAULT 0,
    is_deleted_for_all TINYINT(1) DEFAULT 0,
    deleted_by_sender TINYINT(1) DEFAULT 0,
    deleted_by_receiver TINYINT(1) DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (receiver_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_sender_receiver (sender_id, receiver_id),
    INDEX idx_created_at (created_at),
    INDEX idx_receiver (receiver_id),
    INDEX idx_updated_at (updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Message Status Table
CREATE TABLE IF NOT EXISTS message_status (
    id INT AUTO_INCREMENT PRIMARY KEY,
    message_id INT NOT NULL,
    user_id INT NOT NULL,
    status ENUM('sent', 'delivered', 'read') DEFAULT 'sent',
    read_at DATETIME DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (message_id) REFERENCES messages(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_message_status (message_id, user_id),
    INDEX idx_status (status),
    INDEX idx_read_at (read_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Message Reactions Table
CREATE TABLE IF NOT EXISTS message_reactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    message_id INT NOT NULL,
    user_id INT NOT NULL,
    reaction VARCHAR(10) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (message_id) REFERENCES messages(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_message_reaction (message_id, user_id),
    INDEX idx_reaction (reaction),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Typing Status Table
CREATE TABLE IF NOT EXISTS typing_status (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    chat_with_id INT NOT NULL,
    is_typing TINYINT(1) DEFAULT 0,
    started_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    stopped_at DATETIME DEFAULT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (chat_with_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_typing_status (user_id, chat_with_id),
    INDEX idx_typing (is_typing),
    INDEX idx_started_at (started_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Online Status Table
CREATE TABLE IF NOT EXISTS online_status (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    is_online TINYINT(1) DEFAULT 0,
    is_typing TINYINT(1) DEFAULT 0,
    last_seen DATETIME DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_online_status (user_id),
    INDEX idx_online (is_online),
    INDEX idx_last_seen (last_seen)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Chat Cleared Table
CREATE TABLE IF NOT EXISTS chat_cleared (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    chat_with_id INT NOT NULL,
    cleared_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (chat_with_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_chat_cleared (user_id, chat_with_id),
    INDEX idx_cleared_at (cleared_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Announcements Table
CREATE TABLE IF NOT EXISTS announcements (
    id INT AUTO_INCREMENT PRIMARY KEY,
    admin_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_active (is_active),
    INDEX idx_created_at (created_at),
    INDEX idx_updated_at (updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Announcement Reads Table
CREATE TABLE IF NOT EXISTS announcement_reads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    announcement_id INT NOT NULL,
    user_id INT NOT NULL,
    is_read TINYINT(1) DEFAULT 0,
    read_at DATETIME DEFAULT NULL,
    FOREIGN KEY (announcement_id) REFERENCES announcements(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_read (announcement_id, user_id),
    INDEX idx_read (is_read),
    INDEX idx_read_at (read_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Rate Limiting Table
CREATE TABLE IF NOT EXISTS rate_limits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    identifier VARCHAR(255) UNIQUE NOT NULL,
    attempts INT DEFAULT 1,
    last_attempt INT NOT NULL,
    INDEX idx_identifier (identifier),
    INDEX idx_last_attempt (last_attempt)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Activity Logs Table
CREATE TABLE IF NOT EXISTS activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT DEFAULT NULL,
    action VARCHAR(100) NOT NULL,
    details TEXT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_user (user_id),
    INDEX idx_action (action),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Chat Themes Table
CREATE TABLE IF NOT EXISTS chat_themes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    chat_with_id INT NOT NULL,
    theme_name VARCHAR(100) DEFAULT 'default',
    background_color VARCHAR(7) DEFAULT '#ECE5DD',
    message_color VARCHAR(7) DEFAULT '#DCF8C6',
    text_color VARCHAR(7) DEFAULT '#111B21',
    emoji_set VARCHAR(50) DEFAULT 'default',
    background_image VARCHAR(255) DEFAULT NULL,
    is_locked TINYINT(1) DEFAULT 0,
    locked_by INT DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (chat_with_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_chat_theme (user_id, chat_with_id),
    INDEX idx_theme_name (theme_name),
    INDEX idx_updated_at (updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Settings Table
CREATE TABLE IF NOT EXISTS settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert Default Admin User (password: admin123 - change in production!)
INSERT INTO users (unique_id, name, email, mobile, password_hash, status, is_admin) 
VALUES (
    'admin001',
    'Administrator',
    'admin@royalpandit.shop',
    '0000000000',
    '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', -- admin123 hashed
    'approved',
    1
) ON DUPLICATE KEY UPDATE id=id;

-- Insert Default Users with Same Password (password: admin123)
INSERT INTO users (unique_id, name, email, mobile, password_hash, status, is_admin) 
VALUES 
    ('user001', 'User One', 'user1@royalpandit.shop', '0000000000', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'approved', 0),
    ('user002', 'User Two', 'user2@royalpandit.shop', '0000000000', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'approved', 0),
    ('user003', 'User Three', 'user3@royalpandit.shop', '0000000', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'approved', 0),
    ('user004', 'User Four', 'user4@royalpandit.shop', '0000000', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'approved', 0),
    ('user005', 'User Five', 'user5@royalpandit.shop', '0000000', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'approved', 0)
ON DUPLICATE KEY UPDATE id=id;

-- Insert Default Settings
INSERT INTO settings (setting_key, setting_value) VALUES
('maintenance_mode', '0'),
('max_file_size', '52428800'),
('allowed_image_types', 'jpg,jpeg,png,webp'),
('allowed_video_types', 'mp4,mov'),
('max_message_length', '5000'),
('self_destruct_max_hours', '24'),
('auto_logout_enabled', '1'),
('auto_logout_minutes', '5')
ON DUPLICATE KEY UPDATE setting_key=setting_key;

-- Insert Default Chat Theme (WhatsApp Default)
INSERT INTO chat_themes (user_id, chat_with_id, theme_name, background_color, message_color, text_color, emoji_set, background_image, is_locked, created_at, updated_at)
SELECT 
    u1.id as user_id,
    u2.id as chat_with_id,
    'whatsapp_default' as theme_name,
    '#ECE5DD' as background_color,
    '#DCF8C6' as message_color,
    '#111B21' as text_color,
    'default' as emoji_set,
    NULL as background_image,
    0 as is_locked,
    NOW() as created_at,
    NOW() as updated_at
FROM users u1
CROSS JOIN users u2 ON u1.id != u2.id
WHERE u1.is_admin = 0 AND u2.is_admin = 0
LIMIT 100
ON DUPLICATE KEY UPDATE user_id=user_id, chat_with_id=chat_with_id, theme_name=VALUES(theme_name), background_color=VALUES(background_color), message_color=VALUES(message_color), text_color=VALUES(text_color), emoji_set=VALUES(emoji_set), background_image=VALUES(background_image), is_locked=VALUES(is_locked), updated_at=VALUES(updated_at);

-- Create Indexes for Performance
CREATE INDEX IF NOT EXISTS idx_messages_sender_receiver_time ON messages (sender_id, receiver_id, created_at);
CREATE INDEX IF NOT EXISTS idx_messages_receiver_time ON messages (receiver_id, created_at);
CREATE INDEX IF NOT EXISTS idx_messages_updated_at ON messages (updated_at);
CREATE INDEX IF NOT EXISTS idx_messages_message_type ON messages (message_type);
CREATE INDEX IF NOT EXISTS idx_messages_file_url ON messages (file_url);

CREATE INDEX IF NOT EXISTS idx_users_status_created ON users (status, created_at);
CREATE INDEX IF NOT EXISTS idx_users_last_activity ON users (last_activity);
CREATE INDEX IF NOT EXISTS idx_users_is_admin ON users (is_admin);

CREATE INDEX IF NOT EXISTS idx_sessions_user_valid ON sessions (user_id, is_valid);
CREATE INDEX IF NOT EXISTS idx_sessions_expires_at ON sessions (expires_at);
CREATE INDEX IF NOT EXISTS idx_sessions_last_activity ON sessions (last_activity);

CREATE INDEX IF NOT EXISTS idx_typing_status_started ON typing_status (is_typing, started_at);
CREATE INDEX IF NOT EXISTS idx_typing_status_stopped ON typing_status (is_typing, stopped_at);

CREATE INDEX IF NOT EXISTS idx_announcements_active_created ON announcements (is_active, created_at);
CREATE INDEX IF NOT EXISTS idx_announcements_updated_at ON announcements (updated_at);

CREATE INDEX IF NOT EXISTS idx_activity_logs_user_created ON activity_logs (user_id, created_at);
CREATE INDEX IF NOT EXISTS idx_activity_logs_action_created ON activity_logs (action, created_at);
CREATE INDEX IF NOT EXISTS idx_activity_logs_ip_created ON activity_logs (ip_address, created_at);

-- Create Index for Chat Themes Performance
CREATE INDEX IF NOT EXISTS idx_chat_themes_user_updated ON chat_themes (user_id, updated_at);
CREATE INDEX IF NOT EXISTS idx_chat_themes_chat_updated ON chat_themes (chat_with_id, updated_at);
