-- ============================================================
-- PredictLeague Database Schema
-- Virtual points prediction game — NO real money / payment gateway
-- anywhere in this schema by design.
-- ============================================================

-- Import this file directly into your existing cPanel database
-- (mytoolsh_matkdata) via phpMyAdmin — don't run CREATE DATABASE,
-- cPanel already created it for you.

-- ------------------------------------------------------------
-- Admin users (people who manage the panel)
-- ------------------------------------------------------------
CREATE TABLE admin_users (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    username      VARCHAR(50)  NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name     VARCHAR(100),
    role          ENUM('super_admin','admin') DEFAULT 'admin',
    status        ENUM('active','inactive') DEFAULT 'active',
    last_login    DATETIME NULL,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Default admin — username: admin / password: admin123
-- CHANGE THIS PASSWORD after first login.
INSERT INTO admin_users (username, password_hash, full_name, role)
VALUES ('admin', '$2y$10$EaQIh7hXsw.C70zire3vhOj/euY6roqEWG.oN34xoGIdIK3D9c7/i', 'Super Admin', 'super_admin');

-- ------------------------------------------------------------
-- App users (players)
-- ------------------------------------------------------------
CREATE TABLE users (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    name           VARCHAR(100) NOT NULL,
    email          VARCHAR(150) UNIQUE,
    phone          VARCHAR(15)  UNIQUE,
    password_hash  VARCHAR(255) NOT NULL,
    points_balance INT DEFAULT 100,
    referral_code  VARCHAR(20) UNIQUE,
    referred_by    INT NULL,
    status         ENUM('active','banned') DEFAULT 'active',
    created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (referred_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Prediction rounds ("contests"). No matka market names —
-- generic titles set by admin.
-- ------------------------------------------------------------
CREATE TABLE contests (
    id                INT AUTO_INCREMENT PRIMARY KEY,
    title             VARCHAR(150) NOT NULL,
    contest_type      ENUM('number_guess','match_prediction') DEFAULT 'number_guess',
    min_value         INT DEFAULT 0,
    max_value         INT DEFAULT 9,
    points_multiplier DECIMAL(6,2) DEFAULT 9.5 COMMENT 'virtual multiplier only, never cash',
    open_time         DATETIME NOT NULL,
    close_time        DATETIME NOT NULL,
    result_time       DATETIME NULL,
    result_value      VARCHAR(20) NULL,
    status            ENUM('upcoming','open','closed','resolved') DEFAULT 'upcoming',
    created_by        INT,
    created_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES admin_users(id)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- User predictions/entries for a contest
-- ------------------------------------------------------------
CREATE TABLE predictions (
    id               INT AUTO_INCREMENT PRIMARY KEY,
    user_id          INT NOT NULL,
    contest_id       INT NOT NULL,
    predicted_value  VARCHAR(20) NOT NULL,
    points_used      INT NOT NULL,
    points_won       INT DEFAULT 0,
    is_winner        TINYINT(1) DEFAULT 0,
    submitted_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (contest_id) REFERENCES contests(id)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Points ledger — full audit trail. This table is the reason
-- points can never quietly become cash: every change is logged
-- with a type and there is no withdraw/payout type at all.
-- ------------------------------------------------------------
CREATE TABLE points_ledger (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    user_id       INT NOT NULL,
    type          ENUM('signup_bonus','daily_bonus','referral_bonus','contest_stake','contest_win','admin_adjust') NOT NULL,
    points        INT NOT NULL COMMENT 'can be negative for stakes/deductions',
    balance_after INT NOT NULL,
    reference_id  INT NULL COMMENT 'contest_id or prediction_id depending on type',
    note          VARCHAR(255),
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Site settings (bonus amounts etc.)
-- ------------------------------------------------------------
CREATE TABLE settings (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    setting_key   VARCHAR(50) UNIQUE NOT NULL,
    setting_value VARCHAR(255)
) ENGINE=InnoDB;

INSERT INTO settings (setting_key, setting_value) VALUES
('site_name', 'PredictLeague'),
('signup_bonus_points', '100'),
('daily_login_bonus', '10'),
('referral_bonus_points', '50');