-- Migration: Belgian TCO Calculator
-- Drops generic tco_calculations, recreates with Belgian-specific schema
-- Creates tco_parameters for configurable Belgian fiscal params

DROP TABLE IF EXISTS tco_calculations;

CREATE TABLE tco_calculations (
    id                       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    vehicle_id               BIGINT UNSIGNED NOT NULL,
    label                    VARCHAR(255)    DEFAULT NULL,

    -- Véhicule
    fuel_type                VARCHAR(20)     NOT NULL DEFAULT 'ELECTRIC',
    engine_type              VARCHAR(20)     NOT NULL DEFAULT 'ELECTRIC',
    co2_g_per_km             SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    consumption_l_100km      DECIMAL(6,2)    DEFAULT NULL,
    consumption_kwh_100km    DECIMAL(6,2)    DEFAULT NULL,
    catalog_price_vat_incl   DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    registration_date        DATE            NOT NULL,

    -- Contrat
    leasing_start_date       DATE            NOT NULL,
    period_months            TINYINT UNSIGNED NOT NULL DEFAULT 60,
    annual_km                INT UNSIGNED    NOT NULL DEFAULT 10000,

    -- Prix leasing
    lease_price_vat_incl     DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
    interest_vat_excl        DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
    insurance_vat_incl       DECIMAL(10,2)   NOT NULL DEFAULT 0.00,

    -- Paramètres fiscaux (stockés en décimal : 0.35 = 35%)
    vat_deduct_pct           DECIMAL(5,4)    NOT NULL DEFAULT 0.3500,
    corp_tax_rate            DECIMAL(5,4)    NOT NULL DEFAULT 0.2500,

    -- Prix carburants au moment du calcul
    fuel_price_electricity   DECIMAL(7,4)    NOT NULL DEFAULT 0.3200,
    fuel_price_petrol        DECIMAL(7,4)    NOT NULL DEFAULT 1.7200,
    fuel_price_diesel        DECIMAL(7,4)    NOT NULL DEFAULT 1.6000,
    driving_modifier         DECIMAL(5,4)    NOT NULL DEFAULT 0.0000,

    -- Résultats mensuels
    tco1_monthly             DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
    tco2_monthly             DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
    tco3_monthly             DECIMAL(10,2)   NOT NULL DEFAULT 0.00,

    -- Composantes détail (sauvegardées pour affichage historique)
    lease_net_monthly        DECIMAL(10,2)   DEFAULT NULL,
    fuel_cost_monthly        DECIMAL(10,2)   DEFAULT NULL,
    co2_contrib_monthly      DECIMAL(10,2)   DEFAULT NULL,
    bik_monthly              DECIMAL(10,2)   DEFAULT NULL,
    deduct_final_pct         DECIMAL(5,4)    DEFAULT NULL,
    disallowed_monthly       DECIMAL(10,2)   DEFAULT NULL,
    tax_savings_monthly      DECIMAL(10,2)   DEFAULT NULL,

    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;

-- Paramètres fiscaux belges configurables
CREATE TABLE IF NOT EXISTS tco_parameters (
    param_key   VARCHAR(60)  NOT NULL,
    param_value VARCHAR(255) NOT NULL,
    label       VARCHAR(255) DEFAULT NULL,
    updated_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (param_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO tco_parameters (param_key, param_value, label) VALUES
    ('fuel_electricity',   '0.3200',  'Prix électricité (€/kWh)'),
    ('fuel_petrol',        '1.7200',  'Prix essence (€/L)'),
    ('fuel_diesel',        '1.6000',  'Prix diesel (€/L)'),
    ('co2_index',          '1.62911', 'Coefficient indexation CO₂ 2026'),
    ('co2_min_contrib',    '25.99',   'Contribution CO₂ minimale avant indexation (€/mois)'),
    ('bik_min_annual',     '1690.00', 'ATN minimum annuel (€)'),
    ('vat_rate',           '0.21',    'Taux TVA belge'),
    ('default_vat_deduct', '0.35',    'Déductibilité TVA par défaut'),
    ('default_corp_tax',   '0.25',    'Taux IS par défaut')
ON DUPLICATE KEY UPDATE param_value = VALUES(param_value);
