-- Clear messages and leases, then update messages.lease_id FK to ON DELETE SET NULL.
-- Run with: psql "<connection string>" -f scripts/clear-messages-leases-then-fix-fk.sql
-- WARNING: Permanently deletes all rows in messages and leases (and lease_documents).
-- Unlinks lease_id in tenant_invites, payment_transactions, and reminders.

BEGIN;

-- 1. Clear messages (references leases)
DELETE FROM messages;

-- 2. Unlink or remove other tables that reference leases so we can delete leases
DELETE FROM lease_documents;
UPDATE tenant_invites SET lease_id = NULL;
UPDATE payment_transactions SET lease_id = NULL;
UPDATE reminders SET lease_id = NULL;

-- 3. Clear leases
DELETE FROM leases;

-- 4. Update constraint so future lease deletes don't block on messages
ALTER TABLE messages DROP CONSTRAINT IF EXISTS messages_lease_id_leases_id_fk;
ALTER TABLE messages ADD CONSTRAINT messages_lease_id_leases_id_fk
  FOREIGN KEY (lease_id) REFERENCES public.leases(id) ON DELETE SET NULL ON UPDATE NO ACTION;

COMMIT;