-- Migration 009: Restore tickets that were incorrectly soft-deleted
-- 
-- Issue: Date-filtered syncs were soft-deleting all records outside the sync window
-- This restores tickets that were deleted during the recent 7-day sync
--
-- Run: docker exec pulse-postgres psql -U pulse_user -d pulse_autotask -f /app/migrations/009_restore_deleted_tickets.sql

BEGIN;

-- Restore all tickets that were soft-deleted
-- We can safely restore all deleted tickets since Autotask is the source of truth
UPDATE tickets
SET is_deleted = false, deleted_at = NULL
WHERE is_deleted = true;

-- Log the restoration
DO $$
DECLARE
  restored_count INTEGER;
BEGIN
  GET DIAGNOSTICS restored_count = ROW_COUNT;
  RAISE NOTICE 'Restored % tickets', restored_count;
END $$;

COMMIT;

-- Verify restoration
SELECT 
  COUNT(*) FILTER (WHERE is_deleted = false) as active_tickets,
  COUNT(*) FILTER (WHERE is_deleted = true) as deleted_tickets,
  COUNT(*) as total_tickets
FROM tickets;