-- Wave 7: Inventory Movements (Transaction Log - Immutable)
CREATE TABLE IF NOT EXISTS inventory_movements (
  id TEXT PRIMARY KEY,
  material_id TEXT NOT NULL,
  warehouse_id TEXT NOT NULL,
  shelf_id TEXT,
  quantity_in NUMERIC(12, 2) NOT NULL DEFAULT 0,
  quantity_out NUMERIC(12, 2) NOT NULL DEFAULT 0,
  reference_type TEXT NOT NULL,
  reference_id TEXT,
  shipment_id TEXT,
  invoice_id TEXT,
  movement_date TIMESTAMPTZ NOT NULL,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_inventory_movements_material ON inventory_movements (material_id);
CREATE INDEX IF NOT EXISTS idx_inventory_movements_warehouse ON inventory_movements (warehouse_id);
CREATE INDEX IF NOT EXISTS idx_inventory_movements_date ON inventory_movements (movement_date);
CREATE INDEX IF NOT EXISTS idx_inventory_movements_ref_type ON inventory_movements (reference_type);