-- =========================================================
-- NuLL Terminal System - Database Schema
-- Import this into phpMyAdmin (XAMPP) or via:
--   mysql -u root -p < schema.sql
-- =========================================================

CREATE DATABASE IF NOT EXISTS null_terminal CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE null_terminal;

-- ---------------------------------------------------------
-- USERS & ROLES
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,      -- admin, mentor, member, student
    description VARCHAR(255)
) ENGINE=InnoDB;

INSERT IGNORE INTO roles (name, description) VALUES
('admin', 'Full system control'),
('mentor', 'Can grade, view all submissions, limited admin'),
('member', 'IT professional / regular team member'),
('student', 'Cyber/IT student, participates in challenges');

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    full_name VARCHAR(120) NOT NULL,
    email VARCHAR(150) UNIQUE,
    password_hash VARCHAR(255) NOT NULL,   -- Argon2id
    role_id INT NOT NULL DEFAULT 4,
    coins INT DEFAULT 0,
    rank_points INT DEFAULT 0,
    preferred_currency CHAR(3) NOT NULL DEFAULT 'GHS',
    is_active TINYINT(1) DEFAULT 1,
    is_banned TINYINT(1) DEFAULT 0,
    totp_secret VARCHAR(64) DEFAULT NULL,  -- optional 2FA
    last_login DATETIME DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES roles(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS currencies (
    code CHAR(3) PRIMARY KEY,
    name VARCHAR(80) NOT NULL,
    symbol VARCHAR(8) NOT NULL,
    paystack_supported TINYINT(1) NOT NULL DEFAULT 0,
    is_enabled TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB;

INSERT IGNORE INTO currencies (code, name, symbol, paystack_supported) VALUES
('GHS', 'Ghanaian Cedi', 'GH₵', 1),
('NGN', 'Nigerian Naira', '₦', 1),
('USD', 'US Dollar', '$', 1),
('ZAR', 'South African Rand', 'R', 1),
('GBP', 'Pound Sterling', '£', 0),
('EUR', 'Euro', '€', 0);

CREATE TABLE IF NOT EXISTS exchange_rates (
    base_code CHAR(3) NOT NULL DEFAULT 'GHS',
    quote_code CHAR(3) NOT NULL,
    rate DECIMAL(18,8) NOT NULL,
    source VARCHAR(80) NOT NULL DEFAULT 'manual',
    fetched_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (base_code, quote_code),
    FOREIGN KEY (base_code) REFERENCES currencies(code),
    FOREIGN KEY (quote_code) REFERENCES currencies(code)
) ENGINE=InnoDB;

INSERT IGNORE INTO exchange_rates (base_code, quote_code, rate, source) VALUES
('GHS', 'GHS', 1, 'system'), ('GHS', 'NGN', 110, 'bootstrap'),
('GHS', 'USD', 0.065, 'bootstrap'), ('GHS', 'ZAR', 1.18, 'bootstrap'),
('GHS', 'GBP', 0.051, 'bootstrap'), ('GHS', 'EUR', 0.060, 'bootstrap');

CREATE TABLE IF NOT EXISTS account_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    full_name VARCHAR(120) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    requested_currency CHAR(3) NOT NULL DEFAULT 'GHS',
    status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    reviewed_by INT DEFAULT NULL,
    reviewed_at DATETIME DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- DAILY ACCESS CODE
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS daily_codes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code_date DATE UNIQUE NOT NULL,
    code_hash VARCHAR(255) NOT NULL,   -- SHA-256/HMAC hash stored, never plaintext
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Per-user override: which users are allowed to fetch/see the code at all
CREATE TABLE IF NOT EXISTS user_code_access (
    user_id INT PRIMARY KEY,
    allowed TINYINT(1) DEFAULT 1,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- PROJECTS & TASKS (virtual filesystem)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS projects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(80) UNIQUE NOT NULL,      -- used as folder name /projects/<slug>
    name VARCHAR(150) NOT NULL,
    description TEXT,
    instructions_format ENUM('txt','md') DEFAULT 'md',
    instructions_body LONGTEXT,            -- README content shown via `cat`
    created_by INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS project_members (
    project_id INT NOT NULL,
    user_id INT NOT NULL,
    can_edit TINYINT(1) DEFAULT 1,
    PRIMARY KEY (project_id, user_id),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `groups` (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    slug VARCHAR(80) UNIQUE NOT NULL,
    description TEXT,
    created_by INT NOT NULL,
    is_public TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS group_members (
    group_id INT NOT NULL,
    user_id INT NOT NULL,
    role ENUM('owner','moderator','member') NOT NULL DEFAULT 'member',
    status ENUM('active','muted','banned') NOT NULL DEFAULT 'active',
    joined_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (group_id, user_id),
    FOREIGN KEY (group_id) REFERENCES `groups`(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS group_join_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    group_id INT NOT NULL,
    user_id INT NOT NULL,
    note VARCHAR(500),
    status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    reviewed_by INT DEFAULT NULL,
    reviewed_at DATETIME DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY pending_group_request (group_id, user_id, status),
    FOREIGN KEY (group_id) REFERENCES `groups`(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS group_posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    group_id INT NOT NULL,
    user_id INT NOT NULL,
    body TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (group_id) REFERENCES `groups`(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS group_comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    post_id INT NOT NULL,
    user_id INT NOT NULL,
    body TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES group_posts(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS group_events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    group_id INT NOT NULL,
    created_by INT NOT NULL,
    title VARCHAR(160) NOT NULL,
    details TEXT,
    starts_at DATETIME NOT NULL,
    location VARCHAR(255),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (group_id) REFERENCES `groups`(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS tasks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    details TEXT,
    assigned_to INT DEFAULT NULL,
    status ENUM('pending','inprogress','complete','deleted') DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (assigned_to) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS task_files (
    id INT AUTO_INCREMENT PRIMARY KEY,
    task_id INT NOT NULL,
    filename VARCHAR(255) NOT NULL,
    stored_path VARCHAR(500) NOT NULL,
    uploaded_by INT,
    uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS project_files (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT NOT NULL,
    uploaded_by INT NOT NULL,
    filename VARCHAR(255) NOT NULL,
    stored_path VARCHAR(500) NOT NULL,
    mime_type VARCHAR(120),
    file_size BIGINT NOT NULL DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS project_groups (
    project_id INT NOT NULL,
    group_id INT NOT NULL,
    PRIMARY KEY (project_id, group_id),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (group_id) REFERENCES `groups`(id) ON DELETE CASCADE
) ENGINE=InnoDB;

SET @add_project_public := (
    SELECT IF(COUNT(*) = 0, 'ALTER TABLE projects ADD COLUMN is_public TINYINT(1) NOT NULL DEFAULT 0 AFTER instructions_body', 'SELECT 1')
    FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'projects' AND column_name = 'is_public'
);
PREPARE stmt_project_public FROM @add_project_public;
EXECUTE stmt_project_public;
DEALLOCATE PREPARE stmt_project_public;

-- ---------------------------------------------------------
-- PERSONAL WORKSPACE, SHARING & NOTIFICATIONS
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS filesystem_nodes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    parent_id INT DEFAULT NULL,
    owner_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    node_type ENUM('directory','file') NOT NULL,
    content LONGTEXT DEFAULT NULL,
    stored_path VARCHAR(500) DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_sibling (parent_id, owner_id, name),
    FOREIGN KEY (parent_id) REFERENCES filesystem_nodes(id) ON DELETE CASCADE,
    FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS file_shares (
    id INT AUTO_INCREMENT PRIMARY KEY,
    node_id INT NOT NULL,
    sender_id INT NOT NULL,
    recipient_id INT NOT NULL,
    permission ENUM('read','write') DEFAULT 'read',
    note VARCHAR(255) DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (node_id) REFERENCES filesystem_nodes(id) ON DELETE CASCADE,
    FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (recipient_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sender_id INT NOT NULL,
    recipient_id INT DEFAULT NULL,
    subject VARCHAR(160) NOT NULL,
    body TEXT NOT NULL,
    is_broadcast TINYINT(1) DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (recipient_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    message_id INT DEFAULT NULL,
    kind VARCHAR(40) NOT NULL DEFAULT 'system',
    body VARCHAR(500) NOT NULL,
    is_read TINYINT(1) DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (message_id) REFERENCES messages(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS chat_typing (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    conversation_type ENUM('direct','broadcast','group') NOT NULL,
    target_id INT NOT NULL,
    is_typing TINYINT(1) NOT NULL DEFAULT 1,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_typing_state (user_id, conversation_type, target_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- FEATURE FLAGS (admin on/off switches)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS feature_flags (
    id INT AUTO_INCREMENT PRIMARY KEY,
    feature_key VARCHAR(80) UNIQUE NOT NULL,   -- e.g. 'connector.virustotal', 'challenges', 'uploads'
    is_enabled TINYINT(1) DEFAULT 1,
    updated_by INT,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- API CONNECTORS (Phase 4)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS api_connectors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    conn_key VARCHAR(80) UNIQUE NOT NULL,    -- matches manifest file name, e.g. 'virustotal'
    display_name VARCHAR(150) NOT NULL,
    category VARCHAR(80),                    -- threat_intel, ip_intel, dns_ssl, breach, osint, malware, email_security
    requires_key TINYINT(1) DEFAULT 1,
    api_key_encrypted TEXT DEFAULT NULL,      -- AES-256 encrypted, set by admin
    is_enabled TINYINT(1) DEFAULT 0,          -- off until admin adds a key
    rate_limit_per_min INT DEFAULT 10,
    docs_url VARCHAR(255),
    signup_url VARCHAR(255)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS api_call_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    conn_key VARCHAR(80),
    query_input VARCHAR(255),
    success TINYINT(1),
    called_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- CHALLENGES (Phase 5)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS challenges (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    category VARCHAR(80),               -- web, crypto, forensics, osint, network
    points INT DEFAULT 100,
    entry_fee_coins INT NOT NULL DEFAULT 0,
    starts_at DATETIME,
    ends_at DATETIME,
    prize TEXT,
    created_by INT,
    host_id INT DEFAULT NULL,
    is_active TINYINT(1) DEFAULT 1,
    FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

SET @schema_add_entry_fee := (
    SELECT IF(COUNT(*) = 0,
        'ALTER TABLE challenges ADD COLUMN entry_fee_coins INT NOT NULL DEFAULT 0 AFTER points',
        'SELECT 1')
    FROM information_schema.columns
    WHERE table_schema = DATABASE() AND table_name = 'challenges' AND column_name = 'entry_fee_coins'
);
PREPARE schema_stmt_entry_fee FROM @schema_add_entry_fee;
EXECUTE schema_stmt_entry_fee;
DEALLOCATE PREPARE schema_stmt_entry_fee;

SET @schema_add_host_id := (
    SELECT IF(COUNT(*) = 0,
        'ALTER TABLE challenges ADD COLUMN host_id INT DEFAULT NULL AFTER created_by',
        'SELECT 1')
    FROM information_schema.columns
    WHERE table_schema = DATABASE() AND table_name = 'challenges' AND column_name = 'host_id'
);
PREPARE schema_stmt_host_id FROM @schema_add_host_id;
EXECUTE schema_stmt_host_id;
DEALLOCATE PREPARE schema_stmt_host_id;

CREATE TABLE IF NOT EXISTS challenge_submissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    challenge_id INT NOT NULL,
    user_id INT NOT NULL,
    file_path VARCHAR(500),
    submitted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    graded_by INT DEFAULT NULL,
    score INT DEFAULT NULL,
    feedback TEXT,
    graded_at DATETIME DEFAULT NULL,
    FOREIGN KEY (challenge_id) REFERENCES challenges(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS wallet_transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    type ENUM('topup','coin_purchase','challenge_entry','challenge_reward','admin_grant','admin_debit','refund') NOT NULL,
    amount_minor BIGINT NOT NULL DEFAULT 0,
    coins_delta INT NOT NULL DEFAULT 0,
    balance_after INT NOT NULL DEFAULT 0,
    reference VARCHAR(120) UNIQUE DEFAULT NULL,
    description VARCHAR(255) NOT NULL,
    metadata JSON DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS paystack_transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    reference VARCHAR(120) UNIQUE NOT NULL,
    access_code VARCHAR(120) DEFAULT NULL,
    amount_minor BIGINT NOT NULL,
    currency VARCHAR(8) NOT NULL DEFAULT 'NGN',
    email VARCHAR(150) NOT NULL,
    mobile_number VARCHAR(40) DEFAULT NULL,
    status ENUM('initialized','success','failed','abandoned') NOT NULL DEFAULT 'initialized',
    gateway_response VARCHAR(255) DEFAULT NULL,
    raw_response JSON DEFAULT NULL,
    verified_at DATETIME DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS challenge_participants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    challenge_id INT NOT NULL,
    user_id INT NOT NULL,
    entry_fee_coins INT NOT NULL DEFAULT 0,
    status ENUM('joined','withdrawn','disqualified') NOT NULL DEFAULT 'joined',
    joined_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_participant (challenge_id, user_id),
    FOREIGN KEY (challenge_id) REFERENCES challenges(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS challenge_results (
    id INT AUTO_INCREMENT PRIMARY KEY,
    challenge_id INT NOT NULL,
    user_id INT NOT NULL,
    position TINYINT NOT NULL,
    point_multiplier DECIMAL(3,2) NOT NULL,
    points_awarded DECIMAL(10,2) NOT NULL DEFAULT 0,
    coins_awarded INT NOT NULL DEFAULT 0,
    announced_by INT NOT NULL,
    announced_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_result_position (challenge_id, position),
    UNIQUE KEY unique_result_user (challenge_id, user_id),
    FOREIGN KEY (challenge_id) REFERENCES challenges(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (announced_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS leaderboard_snapshot (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    coins INT DEFAULT 0,
    rank_points INT DEFAULT 0,
    rank_title VARCHAR(50),
    position INT,
    snapshot_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- AUDIT LOG (everything, immutable)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    command_raw VARCHAR(500),
    result_summary VARCHAR(255),
    ip_address VARCHAR(45),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- SESSIONS (custom, so we can force-kill terminals)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS sessions (
    id VARCHAR(64) PRIMARY KEY,
    user_id INT NOT NULL,
    ip_address VARCHAR(45),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Default admin user: username admin / password Admin@12345 (CHANGE IMMEDIATELY)
-- Hash generated with PHP password_hash(..., PASSWORD_ARGON2ID)
-- Run includes/create_admin.php once instead of trusting a hardcoded hash.