CREATE TABLE IF NOT EXISTS blocked_account_recovery_requests (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    reference_no VARCHAR(64) NOT NULL,
    ticket_id VARCHAR(64) DEFAULT NULL,
    telegram_id VARCHAR(64) NOT NULL,
    user_id BIGINT UNSIGNED DEFAULT NULL,
    customer_name VARCHAR(255) DEFAULT NULL,
    registered_email VARCHAR(255) DEFAULT NULL,
    mobile VARCHAR(50) NOT NULL,
    document_type VARCHAR(80) NOT NULL,
    document_type_label VARCHAR(120) DEFAULT NULL,
    date_of_birth DATE NOT NULL,
    father_name VARCHAR(255) NOT NULL,
    selfie_path VARCHAR(255) DEFAULT NULL,
    document_front_path VARCHAR(255) DEFAULT NULL,
    document_back_path VARCHAR(255) DEFAULT NULL,
    selfie_telegram_file_id VARCHAR(255) DEFAULT NULL,
    document_front_telegram_file_id VARCHAR(255) DEFAULT NULL,
    document_back_telegram_file_id VARCHAR(255) DEFAULT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'pending_review',
    officer_notes TEXT DEFAULT NULL,
    reviewed_by VARCHAR(255) DEFAULT NULL,
    reviewed_at DATETIME DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_reference_no (reference_no),
    KEY idx_ticket_id (ticket_id),
    KEY idx_telegram_id (telegram_id),
    KEY idx_user_id (user_id),
    KEY idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
