-- A appliquer via phpMyAdmin apres 2026-07-05_challenges.sql.
-- Page Epargne : objectifs d'epargne + versements manuels. Le montant
-- courant d'un objectif = somme de ses versements.

CREATE TABLE IF NOT EXISTS savings_goals (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id       INT UNSIGNED NOT NULL,
    label         VARCHAR(150) NOT NULL,
    target_cents  INT UNSIGNED NOT NULL,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    KEY idx_savings_goals_user (user_id),
    CONSTRAINT fk_savings_goals_user
        FOREIGN KEY (user_id) REFERENCES users (id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS savings_deposits (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    goal_id       INT UNSIGNED NOT NULL,
    amount_cents  INT UNSIGNED NOT NULL,
    deposit_date  DATE NOT NULL,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    KEY idx_savings_deposits_goal_date (goal_id, deposit_date),
    CONSTRAINT fk_savings_deposits_goal
        FOREIGN KEY (goal_id) REFERENCES savings_goals (id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;