-- Integrity: rows that belong to a hospital are removed with it (and orphans can't stall background jobs)
DELETE FROM notifications WHERE tenant_id NOT IN (SELECT id FROM tenants);
DELETE FROM payments WHERE tenant_id NOT IN (SELECT id FROM tenants);
DELETE FROM otp_codes WHERE tenant_id NOT IN (SELECT id FROM tenants);
DELETE FROM abdm_requests WHERE tenant_id NOT IN (SELECT id FROM tenants);
DELETE FROM audit_log WHERE tenant_id IS NOT NULL AND tenant_id NOT IN (SELECT id FROM tenants);
ALTER TABLE notifications ADD CONSTRAINT fk_notify_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE;
ALTER TABLE payments ADD CONSTRAINT fk_pay_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE;
ALTER TABLE otp_codes ADD CONSTRAINT fk_otp_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE;
ALTER TABLE abdm_requests ADD CONSTRAINT fk_abdmreq_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE;