-- Schema de la base "Kompte" (gestion de budget).
-- Import initial via phpMyAdmin (cPanel). Les evolutions ulterieures se font
-- via des fichiers dates dans database/migrations/, a appliquer dans l'ordre.
--
-- Conventions du projet :
--   - utf8mb4 / utf8mb4_unicode_ci partout
--   - montants stockes en centimes (INT), jamais en flottant
--   - CREATE TABLE IF NOT EXISTS pour rester rejouable sans danger
--   - adresses IP stockees en binaire via INET6_ATON (IPv4 + IPv6)

SET NAMES utf8mb4;

-- ---------------------------------------------------------------------------
-- Banques, creees par les admins (nom + logo). Referencees par les
-- utilisateurs ; supprimer une banque ne supprime pas les utilisateurs
-- (bank_id repasse a NULL). Les logos vivent dans storage/banks/.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS banks (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name            VARCHAR(100) NOT NULL,
    logo_filename   VARCHAR(100) NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uq_banks_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Categories de depenses, creees par les admins. is_active permet d'archiver
-- une categorie sans casser l'historique des operations qui la referencent.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS categories (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    color       VARCHAR(7) NOT NULL DEFAULT '#6b7194' COMMENT 'couleur hex de la pastille (#rrggbb)',
    icon        VARCHAR(60) NULL COMMENT 'classes CSS de l''icone (Font Awesome ou Tabler), ex. "fa-solid fa-house"',
    is_active   TINYINT(1) NOT NULL DEFAULT 1,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uq_categories_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Enseignes (commerces/services), creees par les admins. Les logos vivent
-- dans storage/merchants/. Les futures operations y feront reference.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS merchants (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name            VARCHAR(100) NOT NULL,
    logo_filename   VARCHAR(100) NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uq_merchants_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Utilisateurs. Comptes crees uniquement par les admins (ou via l'assistant
-- /setup pour le tout premier admin) : aucune inscription publique.
-- Double authentification au choix : aucune, application TOTP, ou code par
-- e-mail (twofa_method) — configurable depuis le futur profil.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id                      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    first_name              VARCHAR(100) NOT NULL,
    last_name               VARCHAR(100) NOT NULL,
    email                   VARCHAR(190) NOT NULL,
    password_hash           VARCHAR(255) NOT NULL,
    role                    ENUM('user', 'admin') NOT NULL DEFAULT 'user',
    avatar_filename         VARCHAR(100) NULL,
    bank_id                 INT UNSIGNED NULL,
    twofa_method            ENUM('none', 'totp', 'email') NOT NULL DEFAULT 'none',
    totp_secret_encrypted   VARBINARY(255) NULL COMMENT 'secret TOTP chiffre (AES-256-GCM, cle derivee de app.key), jamais en clair',
    is_active               TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'un compte desactive ne peut plus se connecter',
    failed_login_attempts   TINYINT UNSIGNED NOT NULL DEFAULT 0,
    locked_until            DATETIME NULL,
    last_login_at           DATETIME NULL,
    created_at              DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at              DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uq_users_email (email),
    KEY idx_users_bank_id (bank_id),
    CONSTRAINT fk_users_bank
        FOREIGN KEY (bank_id) REFERENCES banks (id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Budgets mensuels par categorie : l'utilisateur renseigne (ou non) un
-- montant pour chaque categorie creee par l'admin. Un seul montant par
-- couple utilisateur/categorie, applique chaque mois.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS user_category_budgets (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id         INT UNSIGNED NOT NULL,
    category_id     INT UNSIGNED NOT NULL,
    amount_cents    INT UNSIGNED NOT NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uq_user_category_budgets (user_id, category_id),
    KEY idx_user_category_budgets_category (category_id),
    CONSTRAINT fk_user_category_budgets_user
        FOREIGN KEY (user_id) REFERENCES users (id)
        ON DELETE CASCADE,
    CONSTRAINT fk_user_category_budgets_category
        FOREIGN KEY (category_id) REFERENCES categories (id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Operations (depenses/revenus) saisies par les utilisateurs. Structure
-- minimale posee pour les simulateurs de la page Outils ; completee par
-- migration quand la page de saisie "Mois en cours" sera developpee.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS operations (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id         INT UNSIGNED NOT NULL,
    type            ENUM('expense', 'income') NOT NULL DEFAULT 'expense',
    kind            ENUM('unique', 'fixed', 'subscription', 'salary', 'refund', 'family', 'other') NOT NULL DEFAULT 'unique' COMMENT 'depense : unique/fixed/subscription — entree : salary/refund/family/other',
    label           VARCHAR(190) NOT NULL,
    amount_cents    INT UNSIGNED NOT NULL,
    category_id     INT UNSIGNED NULL,
    merchant_id     INT UNSIGNED NULL,
    operation_date  DATE NOT NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    KEY idx_operations_user_date (user_id, operation_date),
    KEY idx_operations_user_merchant (user_id, merchant_id),
    KEY idx_operations_category (category_id),
    CONSTRAINT fk_operations_user
        FOREIGN KEY (user_id) REFERENCES users (id)
        ON DELETE CASCADE,
    CONSTRAINT fk_operations_category
        FOREIGN KEY (category_id) REFERENCES categories (id)
        ON DELETE SET NULL,
    CONSTRAINT fk_operations_merchant
        FOREIGN KEY (merchant_id) REFERENCES merchants (id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Defis : ne pas depenser dans une categorie pendant N jours. Les jours
-- reussis/rates sont calcules depuis 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;

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

-- ---------------------------------------------------------------------------
-- Jetons "se souvenir de moi", pattern selecteur/validateur : le cookie
-- contient les deux valeurs, seule l'empreinte du validateur est stockee.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS user_remember_tokens (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id         INT UNSIGNED NOT NULL,
    selector        CHAR(24) NOT NULL COMMENT 'partie publique du cookie, sert a la recherche',
    validator_hash  CHAR(64) NOT NULL COMMENT 'sha256 du validateur secret, jamais le validateur en clair',
    user_agent      VARCHAR(255) NULL,
    expires_at      DATETIME NOT NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY uq_user_remember_tokens_selector (selector),
    KEY idx_user_remember_tokens_user_id (user_id),
    CONSTRAINT fk_user_remember_tokens_user
        FOREIGN KEY (user_id) REFERENCES users (id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Journal de connexion : alimente l'historique du profil utilisateur et le
-- journal du panneau admin. user_id nullable (echec sur e-mail inconnu) et
-- conserve en SET NULL si le compte est supprime.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS login_logs (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     INT UNSIGNED NULL,
    email       VARCHAR(190) NOT NULL COMMENT 'adresse saisie au moment de la tentative',
    event       ENUM('success', 'failure', 'lockout', 'magic_link') NOT NULL,
    source_ip   VARBINARY(16) NULL,
    user_agent  VARCHAR(255) NULL,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    KEY idx_login_logs_user_created (user_id, created_at),
    KEY idx_login_logs_created (created_at),
    CONSTRAINT fk_login_logs_user
        FOREIGN KEY (user_id) REFERENCES users (id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Liens de connexion magiques : token envoye par e-mail, seule l'empreinte
-- est stockee, usage unique, expiration courte (auth.magic_link_ttl_minutes).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS magic_link_tokens (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     INT UNSIGNED NOT NULL,
    token_hash  CHAR(64) NOT NULL,
    expires_at  DATETIME NOT NULL,
    used_at     DATETIME NULL,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY uq_magic_link_tokens_hash (token_hash),
    KEY idx_magic_link_tokens_user (user_id),
    CONSTRAINT fk_magic_link_tokens_user
        FOREIGN KEY (user_id) REFERENCES users (id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Trace de TOUTES les demandes de lien magique (meme sur e-mail inconnu),
-- uniquement pour le rate limiting par IP : ne revele rien sur les comptes.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS magic_link_requests (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    source_ip   VARBINARY(16) NULL,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    KEY idx_magic_link_requests_ip_created (source_ip, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Codes de double authentification envoyes par e-mail (twofa_method = email) :
-- code a 6 chiffres stocke hache, usage unique, expiration courte. Les echecs
-- de saisie comptent dans le verrouillage du compte (users.locked_until).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS email_otp_codes (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     INT UNSIGNED NOT NULL,
    code_hash   CHAR(64) NOT NULL,
    expires_at  DATETIME NOT NULL,
    used_at     DATETIME NULL,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

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