data/dIV/schema-coverage-analysis.sql

-- schema-coverage-analysis.sql
-- Schema Coverage Analysis - Generates detailed comparison report
-- Citizen Intelligence Agency - Open Source Intelligence Platform
-- Generated for PostgreSQL 16
--
-- Purpose: Generates detailed coverage analysis comparing full_schema.sql
--          expected objects with actual database objects
--
-- Issue: Hack23/cia#7872 - Extend Schema Validation Script
--
-- *** MAINTENANCE NOTE ***
-- The table arrays in this script must be kept in sync with full_schema.sql
-- and schema-validation-v2.sql. When the schema changes, update all three files.
-- Last synchronized: 2025-11-24
--
-- Usage:
--   psql -U postgres -d cia_dev -f schema-coverage-analysis.sql > coverage_analysis.txt 2>&1
--
-- Features:
--   - Identifies objects in full_schema.sql but missing in database
--   - Identifies objects in database but not in full_schema.sql
--   - Generates coverage percentage report
--   - Exports results to CSV format

\set ECHO queries
\timing on

\echo ''
\echo '=========================================================================='
\echo '=== SCHEMA COVERAGE ANALYSIS                                           ==='
\echo '=========================================================================='
\echo ''
\echo 'Issue: Hack23/cia#7872 - Schema Coverage Analysis'
\echo 'Comparing full_schema.sql (expected) vs actual database'
\echo ''

-- ===========================================================================
-- PART 1: TABLE COVERAGE ANALYSIS
-- ===========================================================================

\echo ''
\echo '--- TABLE COVERAGE ANALYSIS ---'
\echo ''

WITH expected_tables AS (
    SELECT unnest(ARRAY[
        'against_proposal_container', 'against_proposal_data', 'agency',
        'application_action_event', 'application_configuration', 'application_session',
        'application_view', 'assignment_data', 'assignment_element',
        'committee_document_data', 'committee_proposal_component_0', 'committee_proposal_container',
        'committee_proposal_data', 'countries_element', 'country_element',
        'data_element', 'data_source_content', 'databasechangelog',
        'databasechangeloglock', 'detail_data', 'detail_element',
        'document_activity_container', 'document_activity_data', 'document_attachment',
        'document_attachment_container', 'document_container_element', 'document_content_data',
        'document_data', 'document_detail_container', 'document_detail_data',
        'document_element', 'document_person_reference_co_0', 'document_person_reference_da_0',
        'document_proposal_container', 'document_proposal_data', 'document_reference_container',
        'document_reference_data', 'document_status_container', 'domain_portal',
        'encrypted_value', 'indicator_element', 'indicators_element',
        'jv_commit', 'jv_commit_property', 'jv_global_id',
        'jv_snapshot', 'language_content_data', 'language_data',
        'operational_information_cont_0', 'performance_indicator_content', 'person_assignment_data',
        'person_assignment_element', 'person_container_data', 'person_container_element',
        'person_data', 'person_detail_data', 'person_detail_element',
        'person_element', 'portal', 'qrtz_blob_triggers',
        'qrtz_calendars', 'qrtz_cron_triggers', 'qrtz_fired_triggers',
        'qrtz_job_details', 'qrtz_locks', 'qrtz_paused_trigger_grps',
        'qrtz_scheduler_state', 'qrtz_simple_triggers', 'qrtz_simprop_triggers',
        'qrtz_triggers', 'quality_assurance_content', 'rule_violation',
        'sweden_county_data', 'sweden_county_data_container', 'sweden_county_electoral_area',
        'sweden_county_electoral_regi_0', 'sweden_county_electoral_regi_1', 'sweden_election_region',
        'sweden_election_type', 'sweden_election_type_contain_0', 'sweden_municipality_data',
        'sweden_municipality_election_0', 'sweden_parliament_electoral__0', 'sweden_parliament_electoral__1',
        'sweden_political_party', 'target_profile_content', 'topic',
        'topics', 'user_account', 'user_account_address',
        'vote_data', 'vote_meta_data', 'world_bank_data'
    ]) AS table_name
),
actual_tables AS (
    SELECT tablename AS table_name
    FROM pg_tables
    WHERE schemaname = 'public'
),
comparison AS (
    SELECT 
        COALESCE(e.table_name, a.table_name) AS table_name,
        e.table_name IS NOT NULL AS in_schema,
        a.table_name IS NOT NULL AS in_database,
        CASE 
            WHEN e.table_name IS NULL THEN 'EXTRA (in DB, not in full_schema.sql)'
            WHEN a.table_name IS NULL THEN 'MISSING (in full_schema.sql, not in DB)'
            ELSE 'OK (in both)'
        END AS status
    FROM expected_tables e
    FULL OUTER JOIN actual_tables a ON e.table_name = a.table_name
)
SELECT 
    table_name,
    status
FROM comparison
WHERE status != 'OK (in both)'
ORDER BY status, table_name;

-- Coverage summary for tables
WITH expected_tables AS (SELECT 93 AS count),
     actual_tables AS (SELECT COUNT(*) AS count FROM pg_tables WHERE schemaname = 'public'),
     matched_tables AS (
         SELECT COUNT(*) AS count FROM pg_tables 
         WHERE schemaname = 'public' AND tablename IN (
             'against_proposal_container', 'against_proposal_data', 'agency',
             'application_action_event', 'application_configuration', 'application_session',
             'application_view', 'assignment_data', 'assignment_element',
             'committee_document_data', 'committee_proposal_component_0', 'committee_proposal_container',
             'committee_proposal_data', 'countries_element', 'country_element',
             'data_element', 'data_source_content', 'databasechangelog',
             'databasechangeloglock', 'detail_data', 'detail_element',
             'document_activity_container', 'document_activity_data', 'document_attachment',
             'document_attachment_container', 'document_container_element', 'document_content_data',
             'document_data', 'document_detail_container', 'document_detail_data',
             'document_element', 'document_person_reference_co_0', 'document_person_reference_da_0',
             'document_proposal_container', 'document_proposal_data', 'document_reference_container',
             'document_reference_data', 'document_status_container', 'domain_portal',
             'encrypted_value', 'indicator_element', 'indicators_element',
             'jv_commit', 'jv_commit_property', 'jv_global_id',
             'jv_snapshot', 'language_content_data', 'language_data',
             'operational_information_cont_0', 'performance_indicator_content', 'person_assignment_data',
             'person_assignment_element', 'person_container_data', 'person_container_element',
             'person_data', 'person_detail_data', 'person_detail_element',
             'person_element', 'portal', 'qrtz_blob_triggers',
             'qrtz_calendars', 'qrtz_cron_triggers', 'qrtz_fired_triggers',
             'qrtz_job_details', 'qrtz_locks', 'qrtz_paused_trigger_grps',
             'qrtz_scheduler_state', 'qrtz_simple_triggers', 'qrtz_simprop_triggers',
             'qrtz_triggers', 'quality_assurance_content', 'rule_violation',
             'sweden_county_data', 'sweden_county_data_container', 'sweden_county_electoral_area',
             'sweden_county_electoral_regi_0', 'sweden_county_electoral_regi_1', 'sweden_election_region',
             'sweden_election_type', 'sweden_election_type_contain_0', 'sweden_municipality_data',
             'sweden_municipality_election_0', 'sweden_parliament_electoral__0', 'sweden_parliament_electoral__1',
             'sweden_political_party', 'target_profile_content', 'topic',
             'topics', 'user_account', 'user_account_address',
             'vote_data', 'vote_meta_data', 'world_bank_data'
         )
     )
SELECT 
    'TABLES' AS object_type,
    e.count AS expected_in_schema,
    a.count AS found_in_database,
    m.count AS matched,
    ROUND((m.count::numeric / e.count) * 100, 2) AS coverage_pct
FROM expected_tables e, actual_tables a, matched_tables m;

-- ===========================================================================
-- PART 2: REGULAR VIEW COVERAGE ANALYSIS
-- ===========================================================================

\echo ''
\echo '--- REGULAR VIEW COVERAGE ANALYSIS ---'
\echo ''

WITH expected_views AS (
    SELECT unnest(ARRAY[
        'view_application_action_event_page_annual_summary',
        'view_application_action_event_page_daily_summary',
        'view_application_action_event_page_element_annual_summary',
        'view_application_action_event_page_element_daily_summary',
        'view_application_action_event_page_element_hourly_summary',
        'view_application_action_event_page_element_weekly_summary',
        'view_application_action_event_page_hourly_summary',
        'view_application_action_event_page_modes_annual_summary',
        'view_application_action_event_page_modes_daily_summary',
        'view_application_action_event_page_modes_hourly_summary',
        'view_application_action_event_page_modes_weekly_summary',
        'view_application_action_event_page_weekly_summary',
        'view_audit_author_summary',
        'view_audit_data_summary',
        'view_committee_productivity',
        'view_committee_productivity_matrix',
        'view_decision_temporal_trends',
        'view_document_data_committee_report_url',
        'view_ministry_decision_impact',
        'view_ministry_effectiveness_trends',
        'view_ministry_productivity_matrix',
        'view_ministry_risk_evolution',
        'view_party_effectiveness_trends',
        'view_party_performance_metrics',
        'view_politician_behavioral_trends',
        'view_politician_risk_summary',
        'view_riksdagen_coalition_alignment_matrix',
        'view_riksdagen_committee',
        'view_riksdagen_committee_parliament_member_proposal',
        'view_riksdagen_committee_role_member',
        'view_riksdagen_committee_roles',
        'view_riksdagen_crisis_resilience_indicators',
        'view_riksdagen_goverment',
        'view_riksdagen_goverment_proposals',
        'view_riksdagen_goverment_role_member',
        'view_riksdagen_goverment_roles',
        'view_riksdagen_intelligence_dashboard',
        'view_riksdagen_member_proposals',
        'view_riksdagen_party',
        'view_riksdagen_party_ballot_support_annual_summary',
        'view_riksdagen_party_coalation_against_annual_summary',
        'view_riksdagen_party_decision_flow',
        'view_riksdagen_party_document_summary',
        'view_riksdagen_party_member',
        'view_riksdagen_party_momentum_analysis',
        'view_riksdagen_party_role_member',
        'view_riksdagen_party_signatures_document_summary',
        'view_riksdagen_party_summary',
        'view_riksdagen_person_signed_document_summary',
        'view_riksdagen_politician',
        'view_riksdagen_politician_ballot_summary',
        'view_riksdagen_politician_decision_pattern',
        'view_riksdagen_politician_experience_summary',
        'view_riksdagen_politician_influence_metrics',
        'view_riksdagen_voting_anomaly_detection',
        'view_risk_score_evolution'
    ]) AS view_name
),
actual_views AS (
    SELECT viewname AS view_name
    FROM pg_views
    WHERE schemaname = 'public'
),
comparison AS (
    SELECT 
        COALESCE(e.view_name, a.view_name) AS view_name,
        e.view_name IS NOT NULL AS in_schema,
        a.view_name IS NOT NULL AS in_database,
        CASE 
            WHEN e.view_name IS NULL THEN 'EXTRA (in DB, not in full_schema.sql)'
            WHEN a.view_name IS NULL THEN 'MISSING (in full_schema.sql, not in DB)'
            ELSE 'OK (in both)'
        END AS status
    FROM expected_views e
    FULL OUTER JOIN actual_views a ON e.view_name = a.view_name
)
SELECT 
    view_name,
    status
FROM comparison
WHERE status != 'OK (in both)'
ORDER BY status, view_name;

-- Coverage summary for views
WITH expected_views AS (SELECT 56 AS count),
     actual_views AS (SELECT COUNT(*) AS count FROM pg_views WHERE schemaname = 'public'),
     matched_views AS (
         SELECT COUNT(*) AS count FROM pg_views 
         WHERE schemaname = 'public' AND viewname IN (
             'view_application_action_event_page_annual_summary',
             'view_application_action_event_page_daily_summary',
             'view_application_action_event_page_element_annual_summary',
             'view_application_action_event_page_element_daily_summary',
             'view_application_action_event_page_element_hourly_summary',
             'view_application_action_event_page_element_weekly_summary',
             'view_application_action_event_page_hourly_summary',
             'view_application_action_event_page_modes_annual_summary',
             'view_application_action_event_page_modes_daily_summary',
             'view_application_action_event_page_modes_hourly_summary',
             'view_application_action_event_page_modes_weekly_summary',
             'view_application_action_event_page_weekly_summary',
             'view_audit_author_summary',
             'view_audit_data_summary',
             'view_committee_productivity',
             'view_committee_productivity_matrix',
             'view_decision_temporal_trends',
             'view_document_data_committee_report_url',
             'view_ministry_decision_impact',
             'view_ministry_effectiveness_trends',
             'view_ministry_productivity_matrix',
             'view_ministry_risk_evolution',
             'view_party_effectiveness_trends',
             'view_party_performance_metrics',
             'view_politician_behavioral_trends',
             'view_politician_risk_summary',
             'view_riksdagen_coalition_alignment_matrix',
             'view_riksdagen_committee',
             'view_riksdagen_committee_parliament_member_proposal',
             'view_riksdagen_committee_role_member',
             'view_riksdagen_committee_roles',
             'view_riksdagen_crisis_resilience_indicators',
             'view_riksdagen_goverment',
             'view_riksdagen_goverment_proposals',
             'view_riksdagen_goverment_role_member',
             'view_riksdagen_goverment_roles',
             'view_riksdagen_intelligence_dashboard',
             'view_riksdagen_member_proposals',
             'view_riksdagen_party',
             'view_riksdagen_party_ballot_support_annual_summary',
             'view_riksdagen_party_coalation_against_annual_summary',
             'view_riksdagen_party_decision_flow',
             'view_riksdagen_party_document_summary',
             'view_riksdagen_party_member',
             'view_riksdagen_party_momentum_analysis',
             'view_riksdagen_party_role_member',
             'view_riksdagen_party_signatures_document_summary',
             'view_riksdagen_party_summary',
             'view_riksdagen_person_signed_document_summary',
             'view_riksdagen_politician',
             'view_riksdagen_politician_ballot_summary',
             'view_riksdagen_politician_decision_pattern',
             'view_riksdagen_politician_experience_summary',
             'view_riksdagen_politician_influence_metrics',
             'view_riksdagen_voting_anomaly_detection',
             'view_risk_score_evolution'
         )
     )
SELECT 
    'VIEWS' AS object_type,
    e.count AS expected_in_schema,
    a.count AS found_in_database,
    m.count AS matched,
    ROUND((m.count::numeric / e.count) * 100, 2) AS coverage_pct
FROM expected_views e, actual_views a, matched_views m;

-- ===========================================================================
-- PART 3: MATERIALIZED VIEW COVERAGE ANALYSIS
-- ===========================================================================

\echo ''
\echo '--- MATERIALIZED VIEW COVERAGE ANALYSIS ---'
\echo ''

WITH expected_mviews AS (
    SELECT unnest(ARRAY[
        'view_riksdagen_committee_ballot_decision_party_summary',
        'view_riksdagen_committee_ballot_decision_politician_summary',
        'view_riksdagen_committee_ballot_decision_summary',
        'view_riksdagen_committee_decision_type_org_summary',
        'view_riksdagen_committee_decision_type_summary',
        'view_riksdagen_committee_decisions',
        'view_riksdagen_document_type_daily_summary',
        'view_riksdagen_org_document_daily_summary',
        'view_riksdagen_party_document_daily_summary',
        'view_riksdagen_politician_document',
        'view_riksdagen_politician_document_daily_summary',
        'view_riksdagen_politician_document_summary',
        'view_riksdagen_vote_data_ballot_party_summary',
        'view_riksdagen_vote_data_ballot_party_summary_annual',
        'view_riksdagen_vote_data_ballot_party_summary_daily',
        'view_riksdagen_vote_data_ballot_party_summary_monthly',
        'view_riksdagen_vote_data_ballot_party_summary_weekly',
        'view_riksdagen_vote_data_ballot_politician_summary',
        'view_riksdagen_vote_data_ballot_politician_summary_annual',
        'view_riksdagen_vote_data_ballot_politician_summary_daily',
        'view_riksdagen_vote_data_ballot_politician_summary_monthly',
        'view_riksdagen_vote_data_ballot_politician_summary_weekly',
        'view_riksdagen_vote_data_ballot_summary',
        'view_riksdagen_vote_data_ballot_summary_annual',
        'view_riksdagen_vote_data_ballot_summary_daily',
        'view_riksdagen_vote_data_ballot_summary_monthly',
        'view_riksdagen_vote_data_ballot_summary_weekly',
        'view_worldbank_indicator_data_country_summary'
    ]) AS mview_name
),
actual_mviews AS (
    SELECT matviewname AS mview_name
    FROM pg_matviews
    WHERE schemaname = 'public'
),
comparison AS (
    SELECT 
        COALESCE(e.mview_name, a.mview_name) AS mview_name,
        e.mview_name IS NOT NULL AS in_schema,
        a.mview_name IS NOT NULL AS in_database,
        CASE 
            WHEN e.mview_name IS NULL THEN 'EXTRA (in DB, not in full_schema.sql)'
            WHEN a.mview_name IS NULL THEN 'MISSING (in full_schema.sql, not in DB)'
            ELSE 'OK (in both)'
        END AS status
    FROM expected_mviews e
    FULL OUTER JOIN actual_mviews a ON e.mview_name = a.mview_name
)
SELECT 
    mview_name,
    status
FROM comparison
WHERE status != 'OK (in both)'
ORDER BY status, mview_name;

-- Coverage summary for materialized views
WITH expected_mviews AS (SELECT 28 AS count),
     actual_mviews AS (SELECT COUNT(*) AS count FROM pg_matviews WHERE schemaname = 'public'),
     matched_mviews AS (
         SELECT COUNT(*) AS count FROM pg_matviews 
         WHERE schemaname = 'public' AND matviewname IN (
             'view_riksdagen_committee_ballot_decision_party_summary',
             'view_riksdagen_committee_ballot_decision_politician_summary',
             'view_riksdagen_committee_ballot_decision_summary',
             'view_riksdagen_committee_decision_type_org_summary',
             'view_riksdagen_committee_decision_type_summary',
             'view_riksdagen_committee_decisions',
             'view_riksdagen_document_type_daily_summary',
             'view_riksdagen_org_document_daily_summary',
             'view_riksdagen_party_document_daily_summary',
             'view_riksdagen_politician_document',
             'view_riksdagen_politician_document_daily_summary',
             'view_riksdagen_politician_document_summary',
             'view_riksdagen_vote_data_ballot_party_summary',
             'view_riksdagen_vote_data_ballot_party_summary_annual',
             'view_riksdagen_vote_data_ballot_party_summary_daily',
             'view_riksdagen_vote_data_ballot_party_summary_monthly',
             'view_riksdagen_vote_data_ballot_party_summary_weekly',
             'view_riksdagen_vote_data_ballot_politician_summary',
             'view_riksdagen_vote_data_ballot_politician_summary_annual',
             'view_riksdagen_vote_data_ballot_politician_summary_daily',
             'view_riksdagen_vote_data_ballot_politician_summary_monthly',
             'view_riksdagen_vote_data_ballot_politician_summary_weekly',
             'view_riksdagen_vote_data_ballot_summary',
             'view_riksdagen_vote_data_ballot_summary_annual',
             'view_riksdagen_vote_data_ballot_summary_daily',
             'view_riksdagen_vote_data_ballot_summary_monthly',
             'view_riksdagen_vote_data_ballot_summary_weekly',
             'view_worldbank_indicator_data_country_summary'
         )
     )
SELECT 
    'MATERIALIZED VIEWS' AS object_type,
    e.count AS expected_in_schema,
    a.count AS found_in_database,
    m.count AS matched,
    ROUND((m.count::numeric / e.count) * 100, 2) AS coverage_pct
FROM expected_mviews e, actual_mviews a, matched_mviews m;

-- ===========================================================================
-- PART 4: OVERALL COVERAGE SUMMARY
-- ===========================================================================

\echo ''
\echo '=========================================================================='
\echo '=== OVERALL COVERAGE SUMMARY                                           ==='
\echo '=========================================================================='
\echo ''

WITH coverage_summary AS (
    SELECT 
        'Tables' AS object_type,
        93 AS expected,
        (SELECT COUNT(*) FROM pg_tables WHERE schemaname = 'public') AS found,
        93 AS validated
    UNION ALL
    SELECT 
        'Regular Views',
        56,
        (SELECT COUNT(*) FROM pg_views WHERE schemaname = 'public'),
        56
    UNION ALL
    SELECT 
        'Materialized Views',
        28,
        (SELECT COUNT(*) FROM pg_matviews WHERE schemaname = 'public'),
        28
)
SELECT 
    object_type,
    expected,
    found,
    validated,
    ROUND((validated::numeric / expected) * 100, 1) AS validation_coverage_pct,
    ROUND((found::numeric / expected) * 100, 1) AS database_coverage_pct,
    CASE 
        WHEN found = expected AND validated = expected THEN '✓ Perfect Match'
        WHEN found >= expected AND validated = expected THEN '✓ All Expected Objects Found'
        WHEN found < expected THEN '✗ Missing Objects'
        ELSE '⚠ Check Required'
    END AS status
FROM coverage_summary;

-- Grand total
WITH totals AS (
    SELECT 
        177 AS expected_total,
        (SELECT COUNT(*) FROM pg_tables WHERE schemaname = 'public') +
        (SELECT COUNT(*) FROM pg_views WHERE schemaname = 'public') +
        (SELECT COUNT(*) FROM pg_matviews WHERE schemaname = 'public') AS found_total,
        177 AS validated_total
)
SELECT 
    'TOTAL' AS object_type,
    expected_total,
    found_total,
    validated_total,
    ROUND((validated_total::numeric / expected_total) * 100, 2) AS coverage_pct,
    CASE 
        WHEN found_total >= expected_total THEN '✓ All Objects Validated'
        ELSE '✗ Missing Objects'
    END AS status
FROM totals;

\echo ''
\echo '=========================================================================='
\echo '=== COVERAGE ANALYSIS COMPLETE                                         ==='
\echo '=========================================================================='
\echo ''
\echo 'Coverage analysis finished at:'
SELECT NOW();
\echo ''

\timing off