CREATE TABLE IF NOT EXISTS inventory_items (
  id TEXT PRIMARY KEY,
  reference TEXT NOT NULL UNIQUE,
  description TEXT NOT NULL,
  category TEXT NOT NULL DEFAULT '',
  brand TEXT NOT NULL DEFAULT '',
  unit TEXT NOT NULL DEFAULT 'U',
  warehouse TEXT NOT NULL DEFAULT 'Magasin central',
  location TEXT NOT NULL DEFAULT '',
  minimum_quantity REAL NOT NULL DEFAULT 0,
  maximum_quantity REAL NOT NULL DEFAULT 0,
  reorder_quantity REAL NOT NULL DEFAULT 0,
  compatible_assets TEXT NOT NULL DEFAULT '',
  balance_quantity REAL NOT NULL DEFAULT 0,
  average_unit_cost_cents INTEGER NOT NULL DEFAULT 0,
  active INTEGER NOT NULL DEFAULT 1,
  created_by TEXT NOT NULL,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS stock_movements (
  id TEXT PRIMARY KEY,
  item_id TEXT NOT NULL,
  movement_type TEXT NOT NULL,
  quantity REAL NOT NULL,
  unit_cost_cents INTEGER NOT NULL DEFAULT 0,
  document_type TEXT NOT NULL DEFAULT '',
  document_reference TEXT NOT NULL DEFAULT '',
  reason TEXT NOT NULL,
  destination TEXT NOT NULL DEFAULT '',
  occurred_at TEXT NOT NULL,
  created_by TEXT NOT NULL,
  created_at TEXT NOT NULL,
  FOREIGN KEY (item_id) REFERENCES inventory_items(id)
);

CREATE TABLE IF NOT EXISTS purchase_receipts (
  id TEXT PRIMARY KEY,
  purchase_request_id TEXT NOT NULL,
  order_reference TEXT NOT NULL,
  delivery_note TEXT NOT NULL DEFAULT '',
  warehouse TEXT NOT NULL,
  notes TEXT NOT NULL DEFAULT '',
  received_by TEXT NOT NULL,
  received_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS purchase_receipt_lines (
  id TEXT PRIMARY KEY,
  receipt_id TEXT NOT NULL,
  request_line_id TEXT NOT NULL,
  item_id TEXT NOT NULL,
  quantity REAL NOT NULL,
  unit_cost_cents INTEGER NOT NULL DEFAULT 0,
  FOREIGN KEY (receipt_id) REFERENCES purchase_receipts(id),
  FOREIGN KEY (item_id) REFERENCES inventory_items(id)
);

CREATE INDEX IF NOT EXISTS idx_inventory_items_status ON inventory_items(active,warehouse,reference);
CREATE INDEX IF NOT EXISTS idx_stock_movements_item_date ON stock_movements(item_id,occurred_at DESC);
CREATE INDEX IF NOT EXISTS idx_stock_movements_document ON stock_movements(document_type,document_reference);
CREATE INDEX IF NOT EXISTS idx_purchase_receipts_order ON purchase_receipts(order_reference,received_at DESC);
CREATE UNIQUE INDEX IF NOT EXISTS idx_purchase_receipts_unique_delivery ON purchase_receipts(order_reference,delivery_note) WHERE delivery_note<>'';
CREATE INDEX IF NOT EXISTS idx_purchase_receipt_lines_request_line ON purchase_receipt_lines(request_line_id);

INSERT OR IGNORE INTO inventory_items (id,reference,description,category,brand,unit,warehouse,location,minimum_quantity,maximum_quantity,reorder_quantity,compatible_assets,balance_quantity,average_unit_cost_cents,active,created_by,created_at,updated_at) VALUES
('STK-FLT-HU12140','FLT-HU12140','Filtre à huile Euro 6','Filtration','','U','Casablanca','A-01-01',12,40,24,'Camions Euro 6',8,33000,1,'migration','2026-08-12T00:00:00.000Z','2026-08-12T00:00:00.000Z'),
('STK-BRK-29246','BRK-29246','Jeu plaquettes essieu AV','Freinage','','Jeu','Casablanca','B-02-01',6,20,10,'Camions / tracteurs',3,465000,1,'migration','2026-08-12T00:00:00.000Z','2026-08-12T00:00:00.000Z'),
('STK-ADB-20L','ADB-20L','AdBlue – bidon 20 L','Consommables','','Bidon','Mohammedia','C-01-03',20,80,40,'Véhicules diesel SCR',34,19000,1,'migration','2026-08-12T00:00:00.000Z','2026-08-12T00:00:00.000Z'),
('STK-BEL-8PK','BEL-8PK','Courroie accessoires 8PK','Transmission','','U','Tanger','D-03-02',4,12,6,'Camions / tracteurs',2,58000,1,'migration','2026-08-12T00:00:00.000Z','2026-08-12T00:00:00.000Z');

INSERT OR IGNORE INTO stock_movements (id,item_id,movement_type,quantity,unit_cost_cents,document_type,document_reference,reason,destination,occurred_at,created_by,created_at) VALUES
('MVT-INIT-FLT','STK-FLT-HU12140','INITIAL',8,33000,'INVENTORY','OUVERTURE','Stock initial','','2026-08-12T00:00:00.000Z','migration','2026-08-12T00:00:00.000Z'),
('MVT-INIT-BRK','STK-BRK-29246','INITIAL',3,465000,'INVENTORY','OUVERTURE','Stock initial','','2026-08-12T00:00:00.000Z','migration','2026-08-12T00:00:00.000Z'),
('MVT-INIT-ADB','STK-ADB-20L','INITIAL',34,19000,'INVENTORY','OUVERTURE','Stock initial','','2026-08-12T00:00:00.000Z','migration','2026-08-12T00:00:00.000Z'),
('MVT-INIT-BEL','STK-BEL-8PK','INITIAL',2,58000,'INVENTORY','OUVERTURE','Stock initial','','2026-08-12T00:00:00.000Z','migration','2026-08-12T00:00:00.000Z');
PRAGMA optimize;