-- Football Prediction Contests
-- Supports free entry (entry_fee=0) and paid entry
-- Prize pool grows as paid users join (90% of fees, 10% platform cut)

CREATE TABLE IF NOT EXISTS fb_contests (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title        VARCHAR(180) NOT NULL,
    description  TEXT,
    entry_fee    DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    prize_pool   DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    max_entries  INT UNSIGNED NULL,
    status       ENUM('open','closed','resolved') NOT NULL DEFAULT 'open',
    start_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    end_at       DATETIME NOT NULL,
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_end_at (end_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS fb_contest_entries (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    contest_id  INT UNSIGNED NOT NULL,
    user_id     INT UNSIGNED NOT NULL,
    picks_json  JSON,
    score       SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    rank        INT UNSIGNED NULL,
    payout      DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    entered_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_user_contest (user_id, contest_id),
    INDEX idx_contest (contest_id),
    INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed a free daily contest and a paid weekly contest (admin can edit via panel)
INSERT IGNORE INTO fb_contests (id, title, description, entry_fee, prize_pool, max_entries, status, start_at, end_at)
VALUES
(1, '🆓 Daily Free Picks', 'Pick today\'s results — free entry, bragging rights!', 0.00, 0.00, NULL, 'open', CURDATE(), DATE_ADD(CURDATE(), INTERVAL 1 DAY)),
(2, '⚽ Weekly Prediction Pool', 'Paid weekly contest — prize pool grows with entries!', 2000.00, 0.00, 200, 'open', CURDATE(), DATE_ADD(CURDATE(), INTERVAL 7 DAY));