CREATE TABLE IF NOT EXISTS department_requests (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    request_code VARCHAR(32) NOT NULL,
    department VARCHAR(80) NOT NULL,
    request_type VARCHAR(80) NOT NULL,
    title VARCHAR(140) NOT NULL,
    amount DECIMAL(12,2) DEFAULT NULL,
    priority VARCHAR(20) NOT NULL DEFAULT 'Standard',
    needed_by DATE DEFAULT NULL,
    justification TEXT NOT NULL,
    requester_name VARCHAR(120) NOT NULL,
    requester_email VARCHAR(150) NOT NULL,
    status VARCHAR(40) NOT NULL DEFAULT 'Pending Level 1',
    current_stage TINYINT UNSIGNED NOT NULL DEFAULT 1,
    final_stage TINYINT UNSIGNED NOT NULL DEFAULT 3,
    last_comment TEXT DEFAULT NULL,
    audit_trail LONGTEXT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_request_code (request_code),
    KEY idx_requester_email (requester_email),
    KEY idx_department (department),
    KEY idx_status_stage (status, current_stage),
    KEY idx_updated_at (updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;