-- Phase 7: transactionally prove the effective_date gate.
-- Uses a never-committed INSERT inside an explicit transaction, then ROLLBACK.
-- Target: a dummy email guaranteed not to collide with real production data.

START TRANSACTION;

-- Pick a dummy email that doesn't collide with any real contact.
SET @fake_email = 'future-dated-004b-smoke@example.invalid';

-- Insert a far-future-dated suppression + probe the gate conditions directly.
INSERT INTO contact_manual_suppressions
(email, reason, source, effective_date, replacement_email, evidence_file, confidence, active)
VALUES (@fake_email, 'LEFT_COMPANY', 'TEST', '2099-12-31', NULL, NULL, 'HIGH', 1);

-- The gate predicate from the patched eligibility (SELECT-level truth check only).
SELECT 'INSIDE_TXN_future_dated_blocks_today?' AS probe,
       CASE WHEN EXISTS (
         SELECT 1 FROM contact_manual_suppressions ms
         WHERE LOWER(TRIM(ms.email)) = @fake_email
           AND ms.active = 1
           AND (ms.effective_date IS NULL OR ms.effective_date <= CURRENT_DATE)
       ) THEN 'YES_WOULD_BLOCK' ELSE 'NO_WOULD_PASS' END AS result;

-- A past-dated suppression should block today.
UPDATE contact_manual_suppressions SET effective_date='2000-01-01'
 WHERE LOWER(TRIM(email)) = @fake_email;

SELECT 'INSIDE_TXN_past_dated_blocks_today?' AS probe,
       CASE WHEN EXISTS (
         SELECT 1 FROM contact_manual_suppressions ms
         WHERE LOWER(TRIM(ms.email)) = @fake_email
           AND ms.active = 1
           AND (ms.effective_date IS NULL OR ms.effective_date <= CURRENT_DATE)
       ) THEN 'YES_WOULD_BLOCK' ELSE 'NO_WOULD_PASS' END AS result;

-- NULL effective_date should block today.
UPDATE contact_manual_suppressions SET effective_date=NULL
 WHERE LOWER(TRIM(email)) = @fake_email;

SELECT 'INSIDE_TXN_null_effective_date_blocks_today?' AS probe,
       CASE WHEN EXISTS (
         SELECT 1 FROM contact_manual_suppressions ms
         WHERE LOWER(TRIM(ms.email)) = @fake_email
           AND ms.active = 1
           AND (ms.effective_date IS NULL OR ms.effective_date <= CURRENT_DATE)
       ) THEN 'YES_WOULD_BLOCK' ELSE 'NO_WOULD_PASS' END AS result;

-- Boundary: effective_date = today should block.
UPDATE contact_manual_suppressions SET effective_date=CURRENT_DATE
 WHERE LOWER(TRIM(email)) = @fake_email;

SELECT 'INSIDE_TXN_today_dated_blocks_today?' AS probe,
       CASE WHEN EXISTS (
         SELECT 1 FROM contact_manual_suppressions ms
         WHERE LOWER(TRIM(ms.email)) = @fake_email
           AND ms.active = 1
           AND (ms.effective_date IS NULL OR ms.effective_date <= CURRENT_DATE)
       ) THEN 'YES_WOULD_BLOCK' ELSE 'NO_WOULD_PASS' END AS result;

-- Active=0 should NOT block even with past date.
UPDATE contact_manual_suppressions SET active=0, effective_date='2000-01-01'
 WHERE LOWER(TRIM(email)) = @fake_email;

SELECT 'INSIDE_TXN_inactive_does_not_block?' AS probe,
       CASE WHEN EXISTS (
         SELECT 1 FROM contact_manual_suppressions ms
         WHERE LOWER(TRIM(ms.email)) = @fake_email
           AND ms.active = 1
           AND (ms.effective_date IS NULL OR ms.effective_date <= CURRENT_DATE)
       ) THEN 'YES_WOULD_BLOCK' ELSE 'NO_WOULD_PASS' END AS result;

ROLLBACK;

-- Prove nothing was persisted
SELECT 'AFTER_ROLLBACK_count' AS probe, COUNT(*) AS result
FROM contact_manual_suppressions
WHERE LOWER(TRIM(email)) = 'future-dated-004b-smoke@example.invalid';

SELECT 'AFTER_ROLLBACK_total_active' AS probe,
       COUNT(*) AS result
FROM contact_manual_suppressions
WHERE active=1;