-- Moventra ERP - MySQL 8 / MariaDB 10.6+
-- Registre de consommation par la méthode du plein à plein.
CREATE TABLE fuel_full_tank_readings (
  id VARCHAR(80) PRIMARY KEY,
  asset_id VARCHAR(80) NOT NULL,
  fill_date DATE NOT NULL,
  entry_type ENUM('FULL') NOT NULL DEFAULT 'FULL',
  odometer_km INT UNSIGNED NOT NULL,
  fuel_milliliters INT UNSIGNED NOT NULL,
  total_cost_cents BIGINT UNSIGNED NOT NULL DEFAULT 0,
  unit_price_millimes INT UNSIGNED NOT NULL DEFAULT 0,
  previous_reading_id VARCHAR(80) NULL,
  distance_km INT UNSIGNED NOT NULL DEFAULT 0,
  consumption_milliliters_per_100km INT UNSIGNED NOT NULL DEFAULT 0,
  anomaly_code VARCHAR(40) NOT NULL DEFAULT '',
  anomaly_message VARCHAR(255) NOT NULL DEFAULT '',
  note VARCHAR(500) NOT NULL DEFAULT '',
  created_by VARCHAR(120) NOT NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT fk_full_tank_asset FOREIGN KEY (asset_id)
    REFERENCES fleet_assets(id) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_full_tank_previous FOREIGN KEY (previous_reading_id)
    REFERENCES fuel_full_tank_readings(id) ON UPDATE CASCADE ON DELETE SET NULL,
  CONSTRAINT chk_full_tank_odometer CHECK (odometer_km > 0),
  CONSTRAINT chk_full_tank_liters CHECK (fuel_milliliters > 0),
  UNIQUE KEY uq_full_tank_asset_date (asset_id, fill_date),
  UNIQUE KEY uq_full_tank_asset_odometer (asset_id, odometer_km),
  KEY idx_full_tank_asset_chronology (asset_id, fill_date, odometer_km)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Valeur affichée : consumption_milliliters_per_100km / 1000 = L/100 km.
-- Formule : (fuel_milliliters / 1000) * 100 / distance_km.