-- Run this once on your DigitalOcean MySQL database
CREATE DATABASE IF NOT EXISTS nafath CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE nafath;

CREATE TABLE IF NOT EXISTS nafath_requests (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    trans_id CHAR(36) NOT NULL,
    person_id VARCHAR(20) NOT NULL,
    user_type VARCHAR(50) NULL,
    random_number VARCHAR(10) NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'PENDING',
    verified TINYINT(1) NOT NULL DEFAULT 0,
    payload_json LONGTEXT NULL,
    verified_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_nafath_requests_trans_id (trans_id),
    KEY idx_nafath_requests_person_id (person_id),
    KEY idx_nafath_requests_status (status),
    KEY idx_nafath_requests_verified (verified)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS nafath_callback_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    trans_id CHAR(36) NULL,
    raw_body LONGTEXT NOT NULL,
    decoded_json LONGTEXT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_nafath_callback_logs_trans_id (trans_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;