-- Game site: Pre-stored bets and callback logs
-- Run this on the game site database (same as shonu_kaichila, shonu_subjects)

-- Bets table: bets must be stored BEFORE callback arrives
CREATE TABLE IF NOT EXISTS softapi_bets (
    bet_id VARCHAR(64) PRIMARY KEY,
    member_account VARCHAR(255) NOT NULL,
    round_id VARCHAR(255) NOT NULL,
    bet_amount DECIMAL(15,2) NOT NULL,
    bet_type ENUM('YES', 'NO') NOT NULL DEFAULT 'YES',
    status ENUM('PENDING', 'SETTLED') NOT NULL DEFAULT 'PENDING',
    win_amount DECIMAL(15,2) NOT NULL DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    settled_at DATETIME NULL,
    INDEX idx_round_user_amount_status (round_id, member_account, bet_amount, status),
    INDEX idx_pending (round_id, member_account, bet_amount, status),
    INDEX idx_settled_awaiting_win (round_id, member_account, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Callback logs: raw callback data for debugging
CREATE TABLE IF NOT EXISTS callback_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    payload_hash VARCHAR(64) NULL COMMENT 'SHA256 of raw payload for idempotency',
    raw_payload JSON NOT NULL,
    bet_id_settled VARCHAR(64) NULL,
    response_data JSON NULL,
    processed TINYINT NOT NULL DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_payload_hash (payload_hash),
    INDEX idx_processed (processed),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;