-- Advent challenges table
CREATE TABLE IF NOT EXISTS challenges (
    id BIGSERIAL PRIMARY KEY,
    user_did TEXT NOT NULL,
    day INT NOT NULL,
    time_started TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    time_challenge_one_completed TIMESTAMPTZ NULL,
    time_challenge_two_completed TIMESTAMPTZ NULL,
    verification_code_one TEXT NULL,
    verification_code_two TEXT NULL,
    CONSTRAINT challenges_user_day_unique UNIQUE(user_did, day),
    CONSTRAINT challenges_day_range CHECK (day >= 1 AND day <= 25)
);

-- Indexes to speed up common lookups
CREATE INDEX IF NOT EXISTS idx_challenges_user_did ON challenges(user_did);
CREATE INDEX IF NOT EXISTS idx_challenges_day ON challenges(day);