

-- ------------------------------------------------------------
-- 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');
