-- ============================================================
-- CasinoGab - Schéma base de données MySQL 8+
-- ============================================================

CREATE DATABASE IF NOT EXISTS casinogab_db
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE casinogab_db;

-- ── Utilisateurs ─────────────────────────────────────────
CREATE TABLE users (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(100)    NOT NULL,
    phone       VARCHAR(20)     NOT NULL UNIQUE,
    email       VARCHAR(150)    NULL,
    password    VARCHAR(255)    NOT NULL,
    balance     DECIMAL(15,2)   NOT NULL DEFAULT 0.00,
    kyc_status  ENUM('pending','submitted','approved','rejected') NOT NULL DEFAULT 'pending',
    role        ENUM('player','admin') NOT NULL DEFAULT 'player',
    country     CHAR(2)         NOT NULL DEFAULT 'GA',
    is_blocked  TINYINT(1)      NOT NULL DEFAULT 0,
    avatar      VARCHAR(255)    NULL,
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME        NULL ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_phone     (phone),
    INDEX idx_kyc       (kyc_status),
    INDEX idx_blocked   (is_blocked),
    INDEX idx_created   (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Soumissions KYC ──────────────────────────────────────
CREATE TABLE kyc_submissions (
    id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id          INT UNSIGNED NOT NULL,
    id_number        VARCHAR(50)  NOT NULL,
    id_document      VARCHAR(255) NULL,
    address          TEXT         NOT NULL,
    birthdate        DATE         NOT NULL,
    nationality      VARCHAR(50)  NOT NULL,
    status           ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    rejection_reason VARCHAR(500) NULL,
    reviewed_at      DATETIME     NULL,
    created_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at       DATETIME     NULL ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_status    (status),
    INDEX idx_user      (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Transactions ──────────────────────────────────────────
CREATE TABLE transactions (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id             INT UNSIGNED NOT NULL,
    type                ENUM('deposit','withdraw','game_bet','game_win','bonus','refund') NOT NULL,
    amount              DECIMAL(15,2) NOT NULL,
    status              ENUM('pending','completed','failed','cancelled') NOT NULL DEFAULT 'pending',
    reference           VARCHAR(100) NOT NULL UNIQUE,         -- Référence marchande CG... (max 15 chars PVIT)
    pvit_reference_id   VARCHAR(100) NULL,                    -- reference_id retourné par PVIT
    pvit_status         VARCHAR(30)  NULL,                    -- SUCCESS | FAILED | PENDING
    pvit_operator       VARCHAR(20)  NULL,                    -- airtel | moov
    callback_data       JSON         NULL,                    -- Payload brut du callback PVIT
    metadata            JSON         NULL,                    -- Données supplémentaires (téléphone, etc.)
    created_at          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME     NULL ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user      (user_id),
    INDEX idx_type      (type),
    INDEX idx_status    (status),
    INDEX idx_ref       (reference),
    INDEX idx_pvit_ref  (pvit_reference_id),
    INDEX idx_created   (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Sessions de jeu ───────────────────────────────────────
CREATE TABLE game_sessions (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     INT UNSIGNED NOT NULL,
    game_type   ENUM('roulette','slots','coinflip','dice','blackjack','mines') NOT NULL,
    bet_amount  DECIMAL(15,2) NOT NULL,
    win_amount  DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    result      ENUM('win','loss','push') NOT NULL,
    game_data   JSON         NULL,
    server_seed VARCHAR(64)  NOT NULL,
    ip_address  VARCHAR(45)  NOT NULL,
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user      (user_id),
    INDEX idx_game      (game_type),
    INDEX idx_result    (result),
    INDEX idx_created   (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Notifications ─────────────────────────────────────────
CREATE TABLE notifications (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     INT UNSIGNED NOT NULL,
    type        VARCHAR(50)  NOT NULL,
    title       VARCHAR(200) NOT NULL,
    message     TEXT         NOT NULL,
    is_read     TINYINT(1)   NOT NULL DEFAULT 0,
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user  (user_id),
    INDEX idx_read  (is_read)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Administrateur par défaut ─────────────────────────────
-- Mot de passe : Admin@2024! (à changer immédiatement)
INSERT INTO users (name, phone, password, role, kyc_status, balance)
VALUES (
    'Administrateur',
    '+24100000000',
    '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', -- password
    'admin',
    'approved',
    0.00
);
