-- Remove contract_id from orders (orders are tied only to directives)
ALTER TABLE orders DROP COLUMN IF EXISTS contract_id;
DROP INDEX IF EXISTS idx_orders_contract_id;

-- Add directive_name as a denormalized field for searchability
ALTER TABLE orders ADD COLUMN IF NOT EXISTS directive_name VARCHAR(500);

-- Make directive_id required for new orders (but keep existing NULLs)
-- We use a trigger approach to populate directive_name automatically
CREATE OR REPLACE FUNCTION update_order_directive_name()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.directive_id IS NOT NULL THEN
    SELECT title INTO NEW.directive_name FROM directives WHERE id = NEW.directive_id;
  ELSE
    NEW.directive_name := NULL;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_order_directive_name
  BEFORE INSERT OR UPDATE OF directive_id ON orders
  FOR EACH ROW
  EXECUTE FUNCTION update_order_directive_name();

-- Backfill directive_name for existing orders
UPDATE orders o SET directive_name = d.title
FROM directives d WHERE o.directive_id = d.id;

-- Index for searching by directive_name
CREATE INDEX IF NOT EXISTS idx_orders_directive_name ON orders(directive_name);