-- Asset transfers: replace from/to plant + warehouse columns with
-- from_location_id / to_location_id (either plant or warehouse).
-- Idempotent: safe to re-run.

BEGIN;

ALTER TABLE asset_transfers ADD COLUMN IF NOT EXISTS from_location_id BIGINT;
ALTER TABLE asset_transfers ADD COLUMN IF NOT EXISTS to_location_id BIGINT;

-- Backfill from old columns (prefer warehouse, else plant) when present
DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name = 'asset_transfers' AND column_name = 'from_plant_id'
  ) THEN
    UPDATE asset_transfers
       SET from_location_id = COALESCE(from_warehouse_id, from_plant_id)
     WHERE from_location_id IS NULL;
    UPDATE asset_transfers
       SET to_location_id = COALESCE(to_warehouse_id, to_plant_id)
     WHERE to_location_id IS NULL;
  END IF;
END $$;

-- Drop old columns (FK constraints drop with them)
ALTER TABLE asset_transfers DROP COLUMN IF EXISTS from_plant_id;
ALTER TABLE asset_transfers DROP COLUMN IF EXISTS to_plant_id;
ALTER TABLE asset_transfers DROP COLUMN IF EXISTS from_warehouse_id;
ALTER TABLE asset_transfers DROP COLUMN IF EXISTS to_warehouse_id;

-- Foreign keys for the new columns
ALTER TABLE asset_transfers DROP CONSTRAINT IF EXISTS fk_at_from_location;
ALTER TABLE asset_transfers
  ADD CONSTRAINT fk_at_from_location FOREIGN KEY (from_location_id) REFERENCES locations(id);
ALTER TABLE asset_transfers DROP CONSTRAINT IF EXISTS fk_at_to_location;
ALTER TABLE asset_transfers
  ADD CONSTRAINT fk_at_to_location FOREIGN KEY (to_location_id) REFERENCES locations(id);

COMMIT;

-- Verify:
-- SELECT column_name FROM information_schema.columns
--  WHERE table_name = 'asset_transfers'
--    AND column_name IN ('from_location_id','to_location_id','from_plant_id','to_plant_id');