-- Check and Create Missing Tables Script
-- Run this if you're getting 500 errors on admin dashboard

USE captain_smart_chat;

-- Create activity_logs table if it doesn't exist
CREATE TABLE IF NOT EXISTS activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    activity_type VARCHAR(50) NOT NULL,
    description TEXT,
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_activity_type (activity_type),
    INDEX idx_created_at (created_at)
);

-- Create online_users table if it doesn't exist
CREATE TABLE IF NOT EXISTS online_users (
    user_id INT PRIMARY KEY,
    last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Create notifications table if it doesn't exist
CREATE TABLE IF NOT EXISTS notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    notification_type ENUM('message', 'announcement', 'mention', 'system') DEFAULT 'message',
    title VARCHAR(200) NOT NULL,
    content TEXT,
    reference_id INT,
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_is_read (is_read)
);

-- Create user_sessions table if it doesn't exist
CREATE TABLE IF NOT EXISTS user_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    session_token VARCHAR(255) UNIQUE NOT NULL,
    ip_address VARCHAR(45),
    user_agent VARCHAR(255),
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_session_token (session_token),
    INDEX idx_expires_at (expires_at)
);

-- Verify all tables exist
SELECT 
    'users' as table_name, COUNT(*) as exists_check FROM information_schema.tables 
    WHERE table_schema = 'captain_smart_chat' AND table_name = 'users'
UNION ALL
SELECT 
    'messages', COUNT(*) FROM information_schema.tables 
    WHERE table_schema = 'captain_smart_chat' AND table_name = 'messages'
UNION ALL
SELECT 
    'announcements', COUNT(*) FROM information_schema.tables 
    WHERE table_schema = 'captain_smart_chat' AND table_name = 'announcements'
UNION ALL
SELECT 
    'activity_logs', COUNT(*) FROM information_schema.tables 
    WHERE table_schema = 'captain_smart_chat' AND table_name = 'activity_logs'
UNION ALL
SELECT 
    'online_users', COUNT(*) FROM information_schema.tables 
    WHERE table_schema = 'captain_smart_chat' AND table_name = 'online_users';
