-- ============================================================================
--  Cricket Strike VR — Database Schema (MySQL)
--  Namecheap cPanel > phpMyAdmin ma aa file Import karo (ya SQL tab ma paste).
--  Pehla cPanel > MySQL Databases ma ek database + user banavo, pachi aa run karo.
-- ============================================================================

-- ---- 1. Venues (game zone / cafe) ----
CREATE TABLE IF NOT EXISTS venues (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(150) NOT NULL DEFAULT '',
    owner_pass  VARCHAR(100) NOT NULL DEFAULT '1234',
    gst         DECIMAL(5,2) NOT NULL DEFAULT 18.00,
    franchise   DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    salary      INT NOT NULL DEFAULT 0,
    rent        INT NOT NULL DEFAULT 0,
    owner_qr    TEXT,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ek default venue (id = 1) — single kiosk mate
INSERT INTO venues (id, name) VALUES (1, 'My Game Zone')
ON DUPLICATE KEY UPDATE id = id;

-- ---- 2. Sessions / tickets ----
CREATE TABLE IF NOT EXISTS sessions (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    venue_id    INT NOT NULL DEFAULT 1,
    serial_no   INT NOT NULL,
    token       VARCHAR(40) NOT NULL UNIQUE,
    name        VARCHAR(120) NOT NULL DEFAULT '',
    mobile      VARCHAR(15)  NOT NULL DEFAULT '',
    mode        VARCHAR(30)  NOT NULL,
    overs       INT NOT NULL DEFAULT 0,
    amount      INT NOT NULL DEFAULT 0,
    pay         VARCHAR(10)  NOT NULL DEFAULT 'cash',
    status      VARCHAR(10)  NOT NULL DEFAULT 'pending',  -- pending / played
    played_at   DATETIME NULL,
    created_at  DATETIME NOT NULL,
    INDEX idx_venue (venue_id),
    INDEX idx_token (token),
    INDEX idx_mobile (mobile),
    INDEX idx_created (created_at)
) ENGINE=InnoDB;

-- ---- 3. Live score (har venue no ek j current row) ----
CREATE TABLE IF NOT EXISTS live_score (
    venue_id     INT PRIMARY KEY DEFAULT 1,
    player       VARCHAR(120) DEFAULT '',
    mode         VARCHAR(30)  DEFAULT '',
    score        INT DEFAULT 0,
    wicket       INT DEFAULT 0,
    overs        VARCHAR(10) DEFAULT '0.0',
    fours        INT DEFAULT 0,
    sixes        INT DEFAULT 0,
    strike_rate  DECIMAL(6,2) DEFAULT 0,
    run_rate     DECIMAL(6,2) DEFAULT 0,
    target       INT DEFAULT 0,
    high_score   INT DEFAULT 0,
    high_by      VARCHAR(120) DEFAULT '',
    updated_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO live_score (venue_id) VALUES (1)
ON DUPLICATE KEY UPDATE venue_id = venue_id;

-- ---- 4. Match queue (operator START -> Quest aa polls kare) ----
CREATE TABLE IF NOT EXISTS match_queue (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    venue_id    INT NOT NULL DEFAULT 1,
    token       VARCHAR(40) NOT NULL,
    player      VARCHAR(120) DEFAULT '',
    mode        VARCHAR(30) DEFAULT '',
    overs       INT DEFAULT 0,
    consumed    TINYINT NOT NULL DEFAULT 0,   -- 0 = Quest e haju nathi lidho
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_q (venue_id, consumed)
) ENGINE=InnoDB;

-- ---- 5. Top scores / leaderboard ----
CREATE TABLE IF NOT EXISTS top_scores (
    id        INT AUTO_INCREMENT PRIMARY KEY,
    venue_id  INT NOT NULL DEFAULT 1,
    name      VARCHAR(120) NOT NULL,
    mobile    VARCHAR(15) DEFAULT '',
    score     INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_ls (venue_id, score)
) ENGINE=InnoDB;