-- Remove synthetic check fixtures that were incorrectly projected as appointments.

BEGIN;

CREATE TEMP TABLE synthetic_check_task_cleanup ON COMMIT DROP AS
SELECT
    task.project_id,
    task.task_id,
    task.revision,
    task.document->>'title' AS title
FROM workbench.operational_tasks AS task
WHERE task.task_type = 'external_work_order'
  AND task.document#>>'{source,kind}' = 'external_system'
  AND COALESCE(task.document#>>'{source,event_kind}', 'general') = 'general'
  AND EXISTS (
      SELECT 1
      FROM peripheral_sample.work_orders AS source_order
      WHERE source_order.project_id = task.project_id
        AND task.document#>>'{source,external_id}' =
            'peripheral_sample:' || source_order.work_order_id::text
        AND source_order.document->>'created_by' =
            'f1a20d4c-b7b7-5b67-b134-92e5ac541162'
  )
  AND NOT EXISTS (
      SELECT 1
      FROM workbench.operational_task_actions AS action
      WHERE action.project_id = task.project_id
        AND action.document->>'kind' = 'create_work_order'
        AND action.document#>>'{receipt,adapter}' = 'peripheral_sample'
        AND action.document#>>'{receipt,receipt_id}' =
            split_part(task.document#>>'{source,external_id}', ':', 2)
  );

DELETE FROM workbench.operational_task_requests AS request
USING synthetic_check_task_cleanup AS cleanup
WHERE request.project_id = cleanup.project_id
  AND (
      request.scope LIKE 'task:' || cleanup.task_id::text || ':%'
      OR request.response->>'task_id' = cleanup.task_id::text
      OR request.response#>>'{task,task_id}' = cleanup.task_id::text
      OR request.response#>>'{notification,task_id}' = cleanup.task_id::text
  );

DELETE FROM workbench.operational_tasks AS task
USING synthetic_check_task_cleanup AS cleanup
WHERE (task.project_id, task.task_id) = (cleanup.project_id, cleanup.task_id);

INSERT INTO workbench.domain_event_log (
    project_id,
    event_id,
    topic,
    aggregate_type,
    aggregate_id,
    aggregate_revision,
    metadata
)
SELECT
    project_id,
    (
        substr(md5(project_id::text || ':' || task_id::text || ':synthetic-check-cleanup'), 1, 8)
        || '-' || substr(md5(project_id::text || ':' || task_id::text || ':synthetic-check-cleanup'), 9, 4)
        || '-5' || substr(md5(project_id::text || ':' || task_id::text || ':synthetic-check-cleanup'), 14, 3)
        || '-a' || substr(md5(project_id::text || ':' || task_id::text || ':synthetic-check-cleanup'), 18, 3)
        || '-' || substr(md5(project_id::text || ':' || task_id::text || ':synthetic-check-cleanup'), 21, 12)
    )::uuid,
    'operational.task.changed',
    'operational_task',
    task_id::text,
    revision + 1,
    jsonb_build_object('change', 'deleted', 'reason', 'synthetic_check_projection_cleanup')
FROM synthetic_check_task_cleanup
ON CONFLICT (project_id, event_id) DO NOTHING;

COMMIT;