-- Script Update Management System Tables
-- Migration: 007_create_script_updates_tables

-- Scripts version tracking
CREATE TABLE IF NOT EXISTS script_versions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    display_name VARCHAR(150),
    description TEXT,
    current_version VARCHAR(50),
    dev_server_version VARCHAR(50),
    main_server_version VARCHAR(50),
    install_path VARCHAR(255) COMMENT 'Path on FiveM server: resources/[qb]/qb-core',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_script_name (name),
    INDEX idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Available updates (uploaded new versions)
CREATE TABLE IF NOT EXISTS script_updates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    script_name VARCHAR(100) NOT NULL,
    version VARCHAR(50) NOT NULL,
    changelog TEXT,
    changelog_url VARCHAR(255) COMMENT 'CFX.re changelog URL',
    upload_path VARCHAR(255) NOT NULL COMMENT 'Local path to extracted files',
    file_count INT DEFAULT 0,
    zip_size_mb DECIMAL(10,2),
    uploaded_by INT NOT NULL,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('pending', 'dev_deployed', 'main_deployed', 'rejected', 'archived') DEFAULT 'pending',
    FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_script_status (script_name, status),
    INDEX idx_uploaded_at (uploaded_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Custom file tracking (files that have been modified)
CREATE TABLE IF NOT EXISTS script_custom_files (
    id INT AUTO_INCREMENT PRIMARY KEY,
    script_name VARCHAR(100) NOT NULL,
    file_path VARCHAR(500) NOT NULL COMMENT 'Relative path within script: config.lua, server/main.lua',
    original_hash VARCHAR(64) COMMENT 'SHA256 hash of original file',
    custom_hash VARCHAR(64) COMMENT 'SHA256 hash of modified file',
    modified_by INT,
    modified_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    note TEXT COMMENT 'Why was this file modified?',
    is_tracked BOOLEAN DEFAULT TRUE COMMENT 'If false, always overwrite on update',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_script_file (script_name, file_path),
    FOREIGN KEY (modified_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_script (script_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Update execution history
CREATE TABLE IF NOT EXISTS update_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    update_id INT NOT NULL,
    script_name VARCHAR(100) NOT NULL,
    version VARCHAR(50) NOT NULL,
    target_server ENUM('dev', 'main') NOT NULL,
    files_updated INT DEFAULT 0,
    files_skipped INT DEFAULT 0,
    files_total INT DEFAULT 0,
    execution_log TEXT COMMENT 'JSON array of file operations',
    executed_by INT NOT NULL,
    executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    discord_sent BOOLEAN DEFAULT FALSE,
    discord_message_id VARCHAR(100),
    player_announcement_id INT NULL,
    status ENUM('success', 'partial', 'failed') DEFAULT 'success',
    error_message TEXT,
    FOREIGN KEY (update_id) REFERENCES script_updates(id) ON DELETE CASCADE,
    FOREIGN KEY (executed_by) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (player_announcement_id) REFERENCES player_announcements(id) ON DELETE SET NULL,
    INDEX idx_script_server (script_name, target_server),
    INDEX idx_executed_at (executed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Player announcements (stad updates)
CREATE TABLE IF NOT EXISTS player_announcements (
    id INT AUTO_INCREMENT PRIMARY KEY,
    update_history_id INT NOT NULL,
    script_name VARCHAR(100) NOT NULL,
    title VARCHAR(200) NOT NULL,
    message TEXT NOT NULL,
    image_url VARCHAR(500),
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    discord_sent BOOLEAN DEFAULT FALSE,
    discord_message_id VARCHAR(100),
    discord_webhook_url VARCHAR(500),
    FOREIGN KEY (update_history_id) REFERENCES update_history(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default FiveM server config
INSERT INTO script_versions (name, display_name, description, install_path) VALUES
('qb-core', 'QB-Core Framework', 'Core framework voor QBCore', 'resources/[qb]/qb-core'),
('qb-inventory', 'QB Inventory', 'Inventory systeem', 'resources/[qb]/qb-inventory'),
('qb-vehicleshop', 'QB Vehicle Shop', 'Voertuig winkel', 'resources/[qb]/qb-vehicleshop'),
('qb-garages', 'QB Garages', 'Garage systeem', 'resources/[qb]/qb-garages'),
('qb-policejob', 'QB Police Job', 'Politie baan', 'resources/[qb]/qb-policejob')
ON DUPLICATE KEY UPDATE updated_at = CURRENT_TIMESTAMP;
