-- Phase 2A paymentgate.ritter.my test-only schema.
-- Use cPanel/MySQL credentials supplied manually from config/config.template.php.

CREATE TABLE IF NOT EXISTS pg_purchase_entitlements (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    entitlement_id VARCHAR(96) NOT NULL UNIQUE,
    eve_user_id VARCHAR(128) NULL,
    licence_scope VARCHAR(128) NOT NULL,
    status VARCHAR(32) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS pg_payment_authorisation_records (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    par_id VARCHAR(96) NOT NULL UNIQUE,
    entitlement_id VARCHAR(96) NOT NULL,
    assigned_node_id VARCHAR(128) NOT NULL,
    simulated_payment_status VARCHAR(32) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS pg_bundle_seat_ledgers (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ledger_id VARCHAR(96) NOT NULL UNIQUE,
    assigned_node_id VARCHAR(128) NOT NULL,
    seats_total INT UNSIGNED NOT NULL,
    seats_consumed INT UNSIGNED NOT NULL DEFAULT 0,
    licence_term_days INT UNSIGNED NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS pg_audit_records (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    request_id VARCHAR(96) NOT NULL,
    event_name VARCHAR(128) NOT NULL,
    event_payload JSON NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);