-- Migration : création de la table vehicle_tco_entries
-- TCO = Total Cost of Ownership (Coût Total de Possession)

CREATE TABLE IF NOT EXISTS vehicle_tco_entries (
    id          BIGINT UNSIGNED   NOT NULL AUTO_INCREMENT,
    vehicle_id  BIGINT UNSIGNED   NOT NULL,
    tco_type    ENUM('vehicule','conducteur','flotte') NOT NULL
                COMMENT 'TCO Véhicule / TCO Conducteur / TCO Flotte',
    cost_type   VARCHAR(60)       NOT NULL
                COMMENT 'Sous-catégorie : acquisition_loyer, carburant_energie, sinistres_accidents…',
    amount      DECIMAL(10,2)     NOT NULL,
    entry_date  DATE              NOT NULL,
    description TEXT              DEFAULT NULL,
    created_at  TIMESTAMP         NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    INDEX idx_vehicle   (vehicle_id),
    INDEX idx_date      (entry_date),
    INDEX idx_tco_type  (tco_type),

    CONSTRAINT fk_tco_vehicle
        FOREIGN KEY (vehicle_id) REFERENCES vehicles (vehicle_id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
