-- Migration: Add soft-delete columns to deals table
-- Date: 2026-04-16
-- Description: Replaces hard DELETE with soft-delete so accidental deletions are
--              reversible without touching Zoho's recycle bin. Triggered by the
--              MTQ108595 incident (accidental UI click cascaded DELETE to Zoho).

ALTER TABLE deals ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMP NULL DEFAULT NULL;
ALTER TABLE deals ADD COLUMN IF NOT EXISTS deleted_by INT DEFAULT NULL;
ALTER TABLE deals ADD INDEX IF NOT EXISTS idx_deal_deleted_at (deleted_at);

-- FK to users; ON DELETE SET NULL so removing a user does not erase deletion history
ALTER TABLE deals ADD CONSTRAINT deals_deleted_by_fk
  FOREIGN KEY (deleted_by) REFERENCES users(id) ON DELETE SET NULL;