/**
  Cleanup Template Widgets
  ---------------------------
  This script "gathers" all document_widgets associated with documents that are templates
  (they have a company, but no flow associated to them) and the 20k most recent non-template
  widgets, and then deletes all widgets which are NOT in that collection.
 */

-- Store IDs of widgets associated with 'template documents' to a temporary table
CREATE TEMPORARY TABLE template_widgets
SELECT id
FROM (
         SELECT dw.id,
                COALESCE(q.company_id, i.company_id, c.company_id) AS widgetable_company_id,
                COALESCE(q.flow_id, i.flow_id, c.flow_id)          AS widgetable_flow_id

         FROM document_widgets dw
                  LEFT JOIN quotations q ON (
                 dw.widgetable_type = 'App\\Quotation' AND
                 dw.widgetable_id = q.id
             )
                  LEFT JOIN invoices i ON (
                 dw.widgetable_type = 'App\\Invoice' AND
                 dw.widgetable_id = q.id
             )
                  LEFT JOIN confirmations c ON (
                 dw.widgetable_type = 'App\\Confirmation' AND
                 dw.widgetable_id = c.id
             )
     ) widgets_with_relations
WHERE COALESCE(widgetable_company_id, 0) != 0
  AND COALESCE(widgetable_flow_id, 0) = 0;

-- Store IDs of the most recent 20k widgets which do not belong to a template
CREATE TEMPORARY TABLE most_recent_non_template_widgets
SELECT id
FROM document_widgets
WHERE id NOT IN (SELECT id FROM template_widgets)
ORDER BY id DESC
LIMIT 20000;

-- Prepare a temporary table with the IDs of the to-be-deleted widgets
CREATE TEMPORARY TABLE to_be_deleted_template_widgets
SELECT id
FROM document_widgets
WHERE id NOT IN (SELECT id FROM template_widgets)
  AND id NOT IN (SELECT id FROM most_recent_non_template_widgets);

-- Delete all widgets not present in either of the above temporary tables
DELETE
FROM document_widgets
WHERE id IN (SELECT id FROM to_be_deleted_template_widgets);

-- Drop the temporary tables we created earlier
DROP TEMPORARY TABLE template_widgets;
DROP TEMPORARY TABLE most_recent_non_template_widgets;
DROP TEMPORARY TABLE to_be_deleted_template_widgets;
