-- Purchase orders: replace plant_id/warehouse_id with billing_id/shipping_id
-- (both reference locations, plant OR warehouse) and add cgst/sgst/igst columns
-- (tax_total is retained). Idempotent: safe to re-run.

BEGIN;

-- 1. New GST split columns (tax_total kept as the combined total)
ALTER TABLE purchase_orders ADD COLUMN IF NOT EXISTS cgst NUMERIC(15, 4) NOT NULL DEFAULT 0;
ALTER TABLE purchase_orders ADD COLUMN IF NOT EXISTS sgst NUMERIC(15, 4) NOT NULL DEFAULT 0;
ALTER TABLE purchase_orders ADD COLUMN IF NOT EXISTS igst NUMERIC(15, 4) NOT NULL DEFAULT 0;

-- 2. New billing/shipping location columns
ALTER TABLE purchase_orders ADD COLUMN IF NOT EXISTS billing_id BIGINT;
ALTER TABLE purchase_orders ADD COLUMN IF NOT EXISTS shipping_id BIGINT;

-- 3. Backfill from the old columns when they still exist
DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name = 'purchase_orders' AND column_name = 'plant_id'
  ) THEN
    UPDATE purchase_orders SET billing_id = plant_id WHERE billing_id IS NULL;
    UPDATE purchase_orders
       SET shipping_id = COALESCE(warehouse_id, plant_id)
     WHERE shipping_id IS NULL;
  END IF;
END $$;

-- 4. Enforce NOT NULL now that data is backfilled
ALTER TABLE purchase_orders ALTER COLUMN billing_id SET NOT NULL;
ALTER TABLE purchase_orders ALTER COLUMN shipping_id SET NOT NULL;

-- 5. The view selects po.* so it must be dropped before dropping old columns
DROP VIEW IF EXISTS v_purchase_orders CASCADE;

-- 6. Drop old location columns (their FK constraints/indexes drop with them)
ALTER TABLE purchase_orders DROP COLUMN IF EXISTS plant_id;
ALTER TABLE purchase_orders DROP COLUMN IF EXISTS warehouse_id;

-- 7. Foreign keys + indexes for the new columns
ALTER TABLE purchase_orders DROP CONSTRAINT IF EXISTS fk_po_billing_location;
ALTER TABLE purchase_orders
  ADD CONSTRAINT fk_po_billing_location FOREIGN KEY (billing_id) REFERENCES locations(id);
ALTER TABLE purchase_orders DROP CONSTRAINT IF EXISTS fk_po_shipping_location;
ALTER TABLE purchase_orders
  ADD CONSTRAINT fk_po_shipping_location FOREIGN KEY (shipping_id) REFERENCES locations(id);
CREATE INDEX IF NOT EXISTS idx_po_billing_id ON purchase_orders(billing_id);
CREATE INDEX IF NOT EXISTS idx_po_shipping_id ON purchase_orders(shipping_id);

-- 8. Recreate the reporting view against billing/shipping locations
CREATE VIEW v_purchase_orders AS
SELECT
  po.*,
  bl.code AS billing_code,
  bl.name AS billing_name,
  bl.type AS billing_type,
  sl.code AS shipping_code,
  sl.name AS shipping_name,
  sl.type AS shipping_type,
  v.vendor_code,
  v.vendor_name,
  v.vendor_type
FROM purchase_orders po
JOIN locations bl ON bl.id = po.billing_id
JOIN locations sl ON sl.id = po.shipping_id
JOIN vendors v ON v.id = po.vendor_id
WHERE po.deleted_at IS NULL;

COMMIT;

-- Verify:
-- SELECT column_name FROM information_schema.columns
--  WHERE table_name = 'purchase_orders'
--    AND column_name IN ('billing_id','shipping_id','cgst','sgst','igst','plant_id','warehouse_id');