-- Verification queries for the API2 dashboard shadow tables created by
-- rulebase-web/rulebase-api2/drizzle/0060_mature_famine.sql.
--
-- Run after the migration applies. Every query should either return
-- `matches = true`, `diff_count = 0`, or no rows for orphan checks.

WITH table_counts AS (
  SELECT 'dashboard_groups' AS table_name,
         (SELECT count(*) FROM public.dashboard_groups) AS public_count,
         (SELECT count(*) FROM api2.dashboard_groups) AS api2_count
  UNION ALL
  SELECT 'dashboards',
         (SELECT count(*) FROM public.dashboards),
         (SELECT count(*) FROM api2.dashboards)
  UNION ALL
  SELECT 'dashboard_sections',
         (SELECT count(*) FROM public.dashboard_sections),
         (SELECT count(*) FROM api2.dashboard_sections)
  UNION ALL
  SELECT 'dashboard_widget_type_groups',
         (SELECT count(*) FROM public.dashboard_widget_type_groups),
         (SELECT count(*) FROM api2.dashboard_widget_type_groups)
  UNION ALL
  SELECT 'dashboard_widget_types',
         (SELECT count(*) FROM public.dashboard_widget_types),
         (SELECT count(*) FROM api2.dashboard_widget_types)
  UNION ALL
  SELECT 'dashboard_widgets',
         (SELECT count(*) FROM public.dashboard_widgets),
         (SELECT count(*) FROM api2.dashboard_widgets)
  UNION ALL
  SELECT 'dashboard_filter_rules',
         (SELECT count(*) FROM public.dashboard_filter_rules),
         (SELECT count(*) FROM api2.dashboard_filter_rules)
  UNION ALL
  SELECT 'dashboard_preferences',
         (SELECT count(*) FROM public.dashboard_preferences),
         (SELECT count(*) FROM api2.dashboard_preferences)
  UNION ALL
  SELECT 'dashboard_accesses',
         (SELECT count(*) FROM public.dashboard_accesses),
         (SELECT count(*) FROM api2.dashboard_accesses)
  UNION ALL
  SELECT 'dashboard_alerts',
         (SELECT count(*) FROM public.dashboard_alerts),
         (SELECT count(*) FROM api2.dashboard_alerts)
)
SELECT table_name, public_count, api2_count, public_count = api2_count AS matches
FROM table_counts
ORDER BY table_name;

WITH row_diffs AS (
  SELECT 'dashboard_groups public_minus_api2' AS check_name, count(*) AS diff_count
  FROM (
    SELECT id, organization_id, name, position, created_at, updated_at FROM public.dashboard_groups
    EXCEPT
    SELECT id, organization_id, name, position, created_at, updated_at FROM api2.dashboard_groups
  ) diff
  UNION ALL
  SELECT 'dashboard_groups api2_minus_public', count(*)
  FROM (
    SELECT id, organization_id, name, position, created_at, updated_at FROM api2.dashboard_groups
    EXCEPT
    SELECT id, organization_id, name, position, created_at, updated_at FROM public.dashboard_groups
  ) diff
  UNION ALL
  SELECT 'dashboards public_minus_api2', count(*)
  FROM (
    SELECT id, organization_id, created_by_id, dashboard_group_id, dashboard_group_position, name, visibility, card_emoji, card_header_color, card_header_pattern, created_at, updated_at FROM public.dashboards
    EXCEPT
    SELECT id, organization_id, created_by_id, dashboard_group_id, dashboard_group_position, name, visibility, card_emoji, card_header_color, card_header_pattern, created_at, updated_at FROM api2.dashboards
  ) diff
  UNION ALL
  SELECT 'dashboards api2_minus_public', count(*)
  FROM (
    SELECT id, organization_id, created_by_id, dashboard_group_id, dashboard_group_position, name, visibility, card_emoji, card_header_color, card_header_pattern, created_at, updated_at FROM api2.dashboards
    EXCEPT
    SELECT id, organization_id, created_by_id, dashboard_group_id, dashboard_group_position, name, visibility, card_emoji, card_header_color, card_header_pattern, created_at, updated_at FROM public.dashboards
  ) diff
  UNION ALL
  SELECT 'dashboard_sections public_minus_api2', count(*)
  FROM (
    SELECT id, dashboard_id, name, position, created_at, updated_at FROM public.dashboard_sections
    EXCEPT
    SELECT id, dashboard_id, name, position, created_at, updated_at FROM api2.dashboard_sections
  ) diff
  UNION ALL
  SELECT 'dashboard_sections api2_minus_public', count(*)
  FROM (
    SELECT id, dashboard_id, name, position, created_at, updated_at FROM api2.dashboard_sections
    EXCEPT
    SELECT id, dashboard_id, name, position, created_at, updated_at FROM public.dashboard_sections
  ) diff
  UNION ALL
  SELECT 'dashboard_widget_type_groups public_minus_api2', count(*)
  FROM (
    SELECT id, reference, name, position, created_at, updated_at FROM public.dashboard_widget_type_groups
    EXCEPT
    SELECT id, reference, name, position, created_at, updated_at FROM api2.dashboard_widget_type_groups
  ) diff
  UNION ALL
  SELECT 'dashboard_widget_type_groups api2_minus_public', count(*)
  FROM (
    SELECT id, reference, name, position, created_at, updated_at FROM api2.dashboard_widget_type_groups
    EXCEPT
    SELECT id, reference, name, position, created_at, updated_at FROM public.dashboard_widget_type_groups
  ) diff
  UNION ALL
  SELECT 'dashboard_widget_types public_minus_api2', count(*)
  FROM (
    SELECT id, dashboard_widget_type_group_id, reference, title, description, position, fields, created_at, updated_at FROM public.dashboard_widget_types
    EXCEPT
    SELECT id, dashboard_widget_type_group_id, reference, title, description, position, fields, created_at, updated_at FROM api2.dashboard_widget_types
  ) diff
  UNION ALL
  SELECT 'dashboard_widget_types api2_minus_public', count(*)
  FROM (
    SELECT id, dashboard_widget_type_group_id, reference, title, description, position, fields, created_at, updated_at FROM api2.dashboard_widget_types
    EXCEPT
    SELECT id, dashboard_widget_type_group_id, reference, title, description, position, fields, created_at, updated_at FROM public.dashboard_widget_types
  ) diff
  UNION ALL
  SELECT 'dashboard_widgets public_minus_api2', count(*)
  FROM (
    SELECT id, dashboard_id, dashboard_widget_type_id, dashboard_section_id, position, source, metric_aggregation, metric_field, name, description, display_type, display_unit, comparison_period, date_grouping, dimensions, layout, config, created_at, updated_at FROM public.dashboard_widgets
    EXCEPT
    SELECT id, dashboard_id, dashboard_widget_type_id, dashboard_section_id, position, source, metric_aggregation, metric_field, name, description, display_type, display_unit, comparison_period, date_grouping, dimensions, layout, config, created_at, updated_at FROM api2.dashboard_widgets
  ) diff
  UNION ALL
  SELECT 'dashboard_widgets api2_minus_public', count(*)
  FROM (
    SELECT id, dashboard_id, dashboard_widget_type_id, dashboard_section_id, position, source, metric_aggregation, metric_field, name, description, display_type, display_unit, comparison_period, date_grouping, dimensions, layout, config, created_at, updated_at FROM api2.dashboard_widgets
    EXCEPT
    SELECT id, dashboard_id, dashboard_widget_type_id, dashboard_section_id, position, source, metric_aggregation, metric_field, name, description, display_type, display_unit, comparison_period, date_grouping, dimensions, layout, config, created_at, updated_at FROM public.dashboard_widgets
  ) diff
  UNION ALL
  SELECT 'dashboard_filter_rules public_minus_api2', count(*)
  FROM (
    SELECT id, organization_id, dashboard_id, type, value, config, created_at, updated_at FROM public.dashboard_filter_rules
    EXCEPT
    SELECT id, organization_id, dashboard_id, type, value, config, created_at, updated_at FROM api2.dashboard_filter_rules
  ) diff
  UNION ALL
  SELECT 'dashboard_filter_rules api2_minus_public', count(*)
  FROM (
    SELECT id, organization_id, dashboard_id, type, value, config, created_at, updated_at FROM api2.dashboard_filter_rules
    EXCEPT
    SELECT id, organization_id, dashboard_id, type, value, config, created_at, updated_at FROM public.dashboard_filter_rules
  ) diff
  UNION ALL
  SELECT 'dashboard_preferences public_minus_api2', count(*)
  FROM (
    SELECT id, organization_id, dashboard_id, membership_id, pinned, homepage, created_at, updated_at FROM public.dashboard_preferences
    EXCEPT
    SELECT id, organization_id, dashboard_id, membership_id, pinned, homepage, created_at, updated_at FROM api2.dashboard_preferences
  ) diff
  UNION ALL
  SELECT 'dashboard_preferences api2_minus_public', count(*)
  FROM (
    SELECT id, organization_id, dashboard_id, membership_id, pinned, homepage, created_at, updated_at FROM api2.dashboard_preferences
    EXCEPT
    SELECT id, organization_id, dashboard_id, membership_id, pinned, homepage, created_at, updated_at FROM public.dashboard_preferences
  ) diff
  UNION ALL
  SELECT 'dashboard_accesses public_minus_api2', count(*)
  FROM (
    SELECT id, dashboard_id, organization_id, grantee_type, grantee_id, created_at, updated_at FROM public.dashboard_accesses
    EXCEPT
    SELECT id, dashboard_id, organization_id, grantee_type, grantee_id, created_at, updated_at FROM api2.dashboard_accesses
  ) diff
  UNION ALL
  SELECT 'dashboard_accesses api2_minus_public', count(*)
  FROM (
    SELECT id, dashboard_id, organization_id, grantee_type, grantee_id, created_at, updated_at FROM api2.dashboard_accesses
    EXCEPT
    SELECT id, dashboard_id, organization_id, grantee_type, grantee_id, created_at, updated_at FROM public.dashboard_accesses
  ) diff
  UNION ALL
  SELECT 'dashboard_alerts public_minus_api2', count(*)
  FROM (
    SELECT id, dashboard_id, dashboard_widget_id, organization_id, threshold_type, threshold_value, baseline_period, window_period, created_at, updated_at FROM public.dashboard_alerts
    EXCEPT
    SELECT id, dashboard_id, dashboard_widget_id, organization_id, threshold_type, threshold_value, baseline_period, window_period, created_at, updated_at FROM api2.dashboard_alerts
  ) diff
  UNION ALL
  SELECT 'dashboard_alerts api2_minus_public', count(*)
  FROM (
    SELECT id, dashboard_id, dashboard_widget_id, organization_id, threshold_type, threshold_value, baseline_period, window_period, created_at, updated_at FROM api2.dashboard_alerts
    EXCEPT
    SELECT id, dashboard_id, dashboard_widget_id, organization_id, threshold_type, threshold_value, baseline_period, window_period, created_at, updated_at FROM public.dashboard_alerts
  ) diff
)
SELECT check_name, diff_count, diff_count = 0 AS matches
FROM row_diffs
ORDER BY check_name;

SELECT 'dashboards.dashboard_group_id' AS orphan_check, d.id
FROM api2.dashboards d
LEFT JOIN api2.dashboard_groups g ON g.id = d.dashboard_group_id
WHERE d.dashboard_group_id IS NOT NULL AND g.id IS NULL
UNION ALL
SELECT 'dashboard_sections.dashboard_id', s.id
FROM api2.dashboard_sections s
LEFT JOIN api2.dashboards d ON d.id = s.dashboard_id
WHERE d.id IS NULL
UNION ALL
SELECT 'dashboard_widgets.dashboard_id', w.id
FROM api2.dashboard_widgets w
LEFT JOIN api2.dashboards d ON d.id = w.dashboard_id
WHERE d.id IS NULL
UNION ALL
SELECT 'dashboard_widgets.dashboard_widget_type_id', w.id
FROM api2.dashboard_widgets w
LEFT JOIN api2.dashboard_widget_types wt ON wt.id = w.dashboard_widget_type_id
WHERE w.dashboard_widget_type_id IS NOT NULL AND wt.id IS NULL
UNION ALL
SELECT 'dashboard_widgets.dashboard_section_id', w.id
FROM api2.dashboard_widgets w
LEFT JOIN api2.dashboard_sections s ON s.id = w.dashboard_section_id
WHERE w.dashboard_section_id IS NOT NULL AND s.id IS NULL
UNION ALL
SELECT 'dashboard_filter_rules.dashboard_id', r.id
FROM api2.dashboard_filter_rules r
LEFT JOIN api2.dashboards d ON d.id = r.dashboard_id
WHERE d.id IS NULL
UNION ALL
SELECT 'dashboard_preferences.dashboard_id', p.id
FROM api2.dashboard_preferences p
LEFT JOIN api2.dashboards d ON d.id = p.dashboard_id
WHERE d.id IS NULL
UNION ALL
SELECT 'dashboard_accesses.dashboard_id', a.id
FROM api2.dashboard_accesses a
LEFT JOIN api2.dashboards d ON d.id = a.dashboard_id
WHERE d.id IS NULL
UNION ALL
SELECT 'dashboard_alerts.dashboard_id', a.id
FROM api2.dashboard_alerts a
LEFT JOIN api2.dashboards d ON d.id = a.dashboard_id
WHERE d.id IS NULL
UNION ALL
SELECT 'dashboard_alerts.dashboard_widget_id', a.id
FROM api2.dashboard_alerts a
LEFT JOIN api2.dashboard_widgets w ON w.id = a.dashboard_widget_id
WHERE w.id IS NULL;

WITH sequences AS (
  SELECT 'dashboard_groups' AS table_name,
         (SELECT last_value FROM api2.dashboard_groups_id_seq) AS sequence_value,
         COALESCE((SELECT max(id) FROM api2.dashboard_groups), 0) AS max_id
  UNION ALL
  SELECT 'dashboards',
         (SELECT last_value FROM api2.dashboards_id_seq),
         COALESCE((SELECT max(id) FROM api2.dashboards), 0)
  UNION ALL
  SELECT 'dashboard_sections',
         (SELECT last_value FROM api2.dashboard_sections_id_seq),
         COALESCE((SELECT max(id) FROM api2.dashboard_sections), 0)
  UNION ALL
  SELECT 'dashboard_widget_type_groups',
         (SELECT last_value FROM api2.dashboard_widget_type_groups_id_seq),
         COALESCE((SELECT max(id) FROM api2.dashboard_widget_type_groups), 0)
  UNION ALL
  SELECT 'dashboard_widget_types',
         (SELECT last_value FROM api2.dashboard_widget_types_id_seq),
         COALESCE((SELECT max(id) FROM api2.dashboard_widget_types), 0)
  UNION ALL
  SELECT 'dashboard_widgets',
         (SELECT last_value FROM api2.dashboard_widgets_id_seq),
         COALESCE((SELECT max(id) FROM api2.dashboard_widgets), 0)
  UNION ALL
  SELECT 'dashboard_filter_rules',
         (SELECT last_value FROM api2.dashboard_filter_rules_id_seq),
         COALESCE((SELECT max(id) FROM api2.dashboard_filter_rules), 0)
  UNION ALL
  SELECT 'dashboard_preferences',
         (SELECT last_value FROM api2.dashboard_preferences_id_seq),
         COALESCE((SELECT max(id) FROM api2.dashboard_preferences), 0)
  UNION ALL
  SELECT 'dashboard_accesses',
         (SELECT last_value FROM api2.dashboard_accesses_id_seq),
         COALESCE((SELECT max(id) FROM api2.dashboard_accesses), 0)
  UNION ALL
  SELECT 'dashboard_alerts',
         (SELECT last_value FROM api2.dashboard_alerts_id_seq),
         COALESCE((SELECT max(id) FROM api2.dashboard_alerts), 0)
)
SELECT table_name,
       sequence_value,
       max_id,
       sequence_value >= max_id AS sequence_is_ready
FROM sequences
ORDER BY table_name;
