-- Wave 6: Internal Transfers
CREATE TABLE IF NOT EXISTS internal_transfers (
  id TEXT PRIMARY KEY,
  transfer_number TEXT NOT NULL UNIQUE,
  material_id TEXT NOT NULL,
  quantity NUMERIC(12, 2) NOT NULL,
  from_warehouse_id TEXT NOT NULL,
  from_shelf_id TEXT,
  to_warehouse_id TEXT NOT NULL,
  to_shelf_id TEXT,
  transfer_date TIMESTAMPTZ NOT NULL,
  created_by TEXT NOT NULL,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_internal_transfers_material ON internal_transfers (material_id);
CREATE INDEX IF NOT EXISTS idx_internal_transfers_from_wh ON internal_transfers (from_warehouse_id);
CREATE INDEX IF NOT EXISTS idx_internal_transfers_to_wh ON internal_transfers (to_warehouse_id);
CREATE INDEX IF NOT EXISTS idx_internal_transfers_number ON internal_transfers (transfer_number);