ALTER TABLE maintenance_orders ADD COLUMN battery_operation TEXT;
ALTER TABLE maintenance_orders ADD COLUMN battery_position TEXT;
ALTER TABLE maintenance_orders ADD COLUMN battery_reference TEXT;
ALTER TABLE maintenance_orders ADD COLUMN battery_serial TEXT;
ALTER TABLE maintenance_orders ADD COLUMN battery_brand TEXT;
ALTER TABLE maintenance_orders ADD COLUMN battery_voltage INTEGER;
ALTER TABLE maintenance_orders ADD COLUMN battery_capacity_ah INTEGER;
ALTER TABLE maintenance_orders ADD COLUMN battery_installation_km INTEGER;
ALTER TABLE maintenance_orders ADD COLUMN battery_installation_date TEXT;
ALTER TABLE maintenance_orders ADD COLUMN battery_cost_cents INTEGER NOT NULL DEFAULT 0;

CREATE TABLE fleet_batteries (
  id TEXT PRIMARY KEY,
  asset_id TEXT NOT NULL,
  position TEXT NOT NULL,
  reference TEXT NOT NULL,
  serial TEXT NOT NULL,
  brand TEXT NOT NULL DEFAULT '',
  voltage INTEGER NOT NULL DEFAULT 12,
  capacity_ah INTEGER NOT NULL DEFAULT 0,
  installed_km INTEGER NOT NULL DEFAULT 0,
  installed_date TEXT NOT NULL DEFAULT '',
  cost_cents INTEGER NOT NULL DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'Montée',
  source_order_id TEXT NOT NULL DEFAULT '',
  updated_at TEXT NOT NULL
);

CREATE UNIQUE INDEX idx_fleet_batteries_asset_position ON fleet_batteries(asset_id, position);
CREATE UNIQUE INDEX idx_fleet_batteries_serial ON fleet_batteries(serial) WHERE serial <> '';
CREATE INDEX idx_fleet_batteries_asset_status ON fleet_batteries(asset_id, status);
PRAGMA optimize;