-- A appliquer via phpMyAdmin apres 2026-07-05_admin_enseignes_icones.sql.
-- Table des operations (depenses/revenus), necessaire aux simulateurs de la
-- page Outils. Structure minimale : elle sera 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',
    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;