-- Presence-only comparison generated from local databases on 2026-08-05.
-- Source: zavvion_events. Compared target snapshot: live_db_zavvion.
-- Result: both contain the same 84 base tables and 838 table columns.
--
-- The exact missing-table and missing-column sets are empty, so no schema-creation
-- or schema-alteration statement is warranted. This script performs verification only
-- and never changes schema or data. Select the intended database before running.
-- A hosted database that does not match this local snapshot reports a named
-- abort_reason instead of guessing which production objects should be created.
-- This verifies table/column name presence only; it does not normalize indexes,
-- foreign keys, engines, collations, defaults, or existing column definitions.

SET SESSION group_concat_max_len = 1000000;

SET @zv_expected_base_table_count = 84;
SET @zv_expected_column_count = 838;
SET @zv_expected_table_presence_md5 = 'd221607634e5aa7f48ede5c99588a3da';
SET @zv_expected_column_presence_md5 = '2b7b3a3bc22036398fd212fc233e196c';

SET @zv_target_database = DATABASE();

SELECT COUNT(*),
       MD5(GROUP_CONCAT(t.table_name ORDER BY t.table_name SEPARATOR '|'))
INTO @zv_actual_base_table_count, @zv_actual_table_presence_md5
FROM information_schema.tables AS t
WHERE t.table_schema = DATABASE()
  AND t.table_type = 'BASE TABLE';

SELECT COUNT(*),
       MD5(GROUP_CONCAT(CONCAT(c.table_name, '.', c.column_name)
                        ORDER BY c.table_name, c.column_name SEPARATOR '|'))
INTO @zv_actual_column_count, @zv_actual_column_presence_md5
FROM information_schema.columns AS c
INNER JOIN information_schema.tables AS t
    ON t.table_schema = c.table_schema
   AND t.table_name = c.table_name
   AND t.table_type = 'BASE TABLE'
WHERE c.table_schema = DATABASE();

SET @zv_abort_reason = CASE
    WHEN @zv_target_database IS NULL THEN 'NO_DATABASE_SELECTED'
    WHEN @zv_actual_base_table_count <> @zv_expected_base_table_count
      OR @zv_actual_column_count <> @zv_expected_column_count
        THEN 'TABLE_OR_COLUMN_COUNT_MISMATCH'
    WHEN NOT (@zv_actual_table_presence_md5 <=> @zv_expected_table_presence_md5)
        THEN 'TABLE_PRESENCE_MISMATCH'
    WHEN NOT (@zv_actual_column_presence_md5 <=> @zv_expected_column_presence_md5)
        THEN 'COLUMN_PRESENCE_MISMATCH'
    ELSE NULL
END;

SELECT
    @zv_target_database AS target_database,
    @zv_expected_base_table_count AS expected_base_tables,
    @zv_actual_base_table_count AS actual_base_tables,
    @zv_expected_column_count AS expected_columns,
    @zv_actual_column_count AS actual_columns,
    @zv_expected_table_presence_md5 AS expected_table_presence_md5,
    @zv_actual_table_presence_md5 AS actual_table_presence_md5,
    @zv_expected_column_presence_md5 AS expected_column_presence_md5,
    @zv_actual_column_presence_md5 AS actual_column_presence_md5,
    IF(@zv_abort_reason IS NULL, 0, NULL) AS missing_tables_or_columns,
    IF(@zv_abort_reason IS NULL, 'none', 'unknown_without_fresh_metadata') AS missing_object_names,
    IF(@zv_abort_reason IS NULL, 0, NULL) AS extra_tables_or_columns,
    IF(@zv_abort_reason IS NULL, 'none', 'unknown_without_fresh_metadata') AS extra_object_names,
    COALESCE(@zv_abort_reason, 'none') AS abort_reason,
    IF(@zv_abort_reason IS NULL, 'NO_SCHEMA_CHANGES_REQUIRED', 'STOP_AND_EXPORT_FRESH_METADATA') AS result;