CREATE TABLE IF NOT EXISTS payments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    deposit_id CHAR(36) NOT NULL UNIQUE,
    external_reference VARCHAR(128) DEFAULT NULL,
    phone VARCHAR(20) NOT NULL,
    amount DECIMAL(18,3) NOT NULL,
    currency CHAR(3) NOT NULL,
    status VARCHAR(40) NOT NULL,
    raw_request JSON NOT NULL,
    raw_response JSON NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    INDEX idx_payments_status (status),
    INDEX idx_payments_created (created_at)
);

CREATE TABLE IF NOT EXISTS payouts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    payout_id CHAR(36) NOT NULL UNIQUE,
    phone VARCHAR(20) NOT NULL,
    amount DECIMAL(18,3) NOT NULL,
    provider VARCHAR(40) NOT NULL,
    currency CHAR(3) NOT NULL,
    status VARCHAR(40) NOT NULL,
    raw_request JSON NOT NULL,
    raw_response JSON NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    INDEX idx_payouts_status (status),
    INDEX idx_payouts_created (created_at)
);

CREATE TABLE IF NOT EXISTS refunds (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    refund_id CHAR(36) NOT NULL UNIQUE,
    deposit_id CHAR(36) NOT NULL,
    amount DECIMAL(18,3) NOT NULL,
    currency CHAR(3) NOT NULL,
    status VARCHAR(40) NOT NULL,
    raw_request JSON NOT NULL,
    raw_response JSON NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    INDEX idx_refunds_deposit (deposit_id),
    INDEX idx_refunds_status (status),
    INDEX idx_refunds_created (created_at)
);

CREATE TABLE IF NOT EXISTS logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    reference_id VARCHAR(64) NOT NULL,
    type ENUM('DEPOSIT', 'PAYOUT', 'REFUND', 'WEBHOOK', 'ERROR') NOT NULL,
    payload JSON NOT NULL,
    created_at DATETIME NOT NULL,
    INDEX idx_logs_reference (reference_id),
    INDEX idx_logs_type (type),
    INDEX idx_logs_created (created_at)
);