-- Migration: table tco_calculations
-- Stocke les calculs TCO complets (TCO1 + TCO2 + TCO3) par véhicule

CREATE TABLE IF NOT EXISTS tco_calculations (
    id              BIGINT UNSIGNED   NOT NULL AUTO_INCREMENT,
    vehicle_id      BIGINT UNSIGNED   NOT NULL,
    label           VARCHAR(255)      DEFAULT NULL,
    period_months   TINYINT UNSIGNED  NOT NULL DEFAULT 12,
    km_an           INT UNSIGNED      DEFAULT NULL,

    -- TCO 1 : Véhicule
    t1_acquisition  DECIMAL(12,2)     NOT NULL DEFAULT 0.00,
    t1_entretien    DECIMAL(12,2)     NOT NULL DEFAULT 0.00,
    t1_carburant    DECIMAL(12,2)     NOT NULL DEFAULT 0.00,
    t1_assurance    DECIMAL(12,2)     NOT NULL DEFAULT 0.00,
    t1_reparations  DECIMAL(12,2)     NOT NULL DEFAULT 0.00,
    t1_pneumatiques DECIMAL(12,2)     NOT NULL DEFAULT 0.00,
    t1_total        DECIMAL(12,2)     NOT NULL DEFAULT 0.00,

    -- TCO 2 : Conducteur
    t2_sinistres        DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    t2_amendes          DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    t2_usure            DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    t2_surconsommation  DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    t2_total            DECIMAL(12,2) NOT NULL DEFAULT 0.00,

    -- TCO 3 : Flotte
    t3_gestion_admin    DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    t3_taxe             DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    t3_tva              DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    t3_carte_peage      DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    t3_total            DECIMAL(12,2) NOT NULL DEFAULT 0.00,

    tco_total       DECIMAL(12,2)     NOT NULL DEFAULT 0.00,
    notes           TEXT              DEFAULT NULL,
    calculated_at   TIMESTAMP         NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    INDEX idx_vehicle (vehicle_id),
    INDEX idx_date    (calculated_at),
    CONSTRAINT fk_tco_calc_vehicle
        FOREIGN KEY (vehicle_id) REFERENCES vehicles(vehicle_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
