-- File     : egw_sessions.sql
-- Created  : Apr 16, 2026
-- Author   : Thomas Kirby <tkirby at dynengsys.com> (Thomas)
-- Company  : Dynamic Engineering Systems
-- Rights   : Under license to assignee. Not for resale or redistribution.
--
-- Purpose  : Tracks EGW API wallet transactions initiated by Ember.
--            One row per EGW session. Status updated via inbound EGW webhook.

CREATE TABLE egw_sessions (
    id                   INT UNSIGNED  AUTO_INCREMENT PRIMARY KEY,
    rtp_reference_number VARCHAR(40)   NOT NULL DEFAULT '',
    session_id           VARCHAR(36)   NOT NULL,
    game_distributor_id  VARCHAR(20)   NOT NULL,
    game_id              INT UNSIGNED  NOT NULL,
    game_name            VARCHAR(20)   NOT NULL,
    game_username        VARCHAR(20)   NOT NULL,
    player_game_user_id  INT UNSIGNED  NULL,       -- EGW-assigned customer ID; populated via webhook after /create/
    status               VARCHAR(20)   NOT NULL DEFAULT 'pending',  -- pending / success / failed
    created_at           DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at           DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_session_id   (session_id),
    INDEX idx_rtp_ref      (rtp_reference_number),
    INDEX idx_game_username (game_username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
