-- Remove brand_id from items & purchase_orders and drop the brands master table.
-- WARNING: destructive. Export brand data first if it must be retained.
-- Idempotent: safe to re-run.

BEGIN;

-- 0) Drop dependent view (selects purchase_orders.*); recreated by patch-purchase-orders-drop-po-type.sql
DROP VIEW IF EXISTS v_purchase_orders CASCADE;

-- 1) Drop FK constraints (name may vary by DB; drop defensively)
ALTER TABLE items DROP CONSTRAINT IF EXISTS items_brand_id_fkey;
ALTER TABLE purchase_orders DROP CONSTRAINT IF EXISTS purchase_orders_brand_id_fkey;

-- 2) Drop columns
ALTER TABLE items DROP COLUMN IF EXISTS brand_id;
ALTER TABLE purchase_orders DROP COLUMN IF EXISTS brand_id;

-- 3) Drop the brands master (CASCADE removes its check/unique constraints and user FKs)
DROP TABLE IF EXISTS brands CASCADE;

COMMIT;

-- Verify:
-- SELECT column_name FROM information_schema.columns WHERE table_name = 'items' AND column_name = 'brand_id';
-- SELECT to_regclass('public.brands');  -- expect NULL