CREATE TABLE IF NOT EXISTS inventory_stock_balances (
  id TEXT PRIMARY KEY,
  item_id TEXT NOT NULL,
  warehouse TEXT NOT NULL,
  location TEXT NOT NULL DEFAULT '',
  quantity REAL NOT NULL DEFAULT 0,
  updated_at TEXT NOT NULL,
  FOREIGN KEY (item_id) REFERENCES inventory_items(id),
  UNIQUE (item_id, warehouse, location)
);

INSERT OR IGNORE INTO inventory_stock_balances (id,item_id,warehouse,location,quantity,updated_at)
SELECT 'BAL-' || id,id,warehouse,location,balance_quantity,updated_at FROM inventory_items;

ALTER TABLE stock_movements ADD COLUMN source_warehouse TEXT NOT NULL DEFAULT '';
ALTER TABLE stock_movements ADD COLUMN source_location TEXT NOT NULL DEFAULT '';
ALTER TABLE stock_movements ADD COLUMN destination_type TEXT NOT NULL DEFAULT '';
ALTER TABLE stock_movements ADD COLUMN destination_id TEXT NOT NULL DEFAULT '';
ALTER TABLE stock_movements ADD COLUMN destination_label TEXT NOT NULL DEFAULT '';
ALTER TABLE stock_movements ADD COLUMN return_slip_reference TEXT NOT NULL DEFAULT '';
ALTER TABLE stock_movements ADD COLUMN counterpart_movement_id TEXT NOT NULL DEFAULT '';

CREATE INDEX IF NOT EXISTS idx_inventory_balances_item ON inventory_stock_balances(item_id,warehouse,location);
CREATE INDEX IF NOT EXISTS idx_stock_movements_destination ON stock_movements(destination_type,destination_id,occurred_at DESC);
CREATE INDEX IF NOT EXISTS idx_stock_movements_return_slip ON stock_movements(return_slip_reference) WHERE return_slip_reference<>'';
PRAGMA optimize;