-- A appliquer via phpMyAdmin apres 2026-07-05_operations.sql.
-- Page Defis : un defi = ne pas depenser dans une categorie pendant N jours.
-- Les jours reussis/rates sont calcules depuis la table operations (une
-- depense de la categorie sur un jour du defi = jour rate).

CREATE TABLE IF NOT EXISTS challenges (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id      INT UNSIGNED NOT NULL,
    category_id  INT UNSIGNED NOT NULL,
    label        VARCHAR(150) NOT NULL,
    start_date   DATE NOT NULL,
    end_date     DATE NOT NULL,
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    KEY idx_challenges_user (user_id, start_date),
    KEY idx_challenges_category (category_id),
    CONSTRAINT fk_challenges_user
        FOREIGN KEY (user_id) REFERENCES users (id)
        ON DELETE CASCADE,
    CONSTRAINT fk_challenges_category
        FOREIGN KEY (category_id) REFERENCES categories (id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;