data/dIV/schema-validation-v2.sql

-- schema-validation-v2.sql
-- Enhanced Database Schema Validation with 100% Coverage
-- Citizen Intelligence Agency - Open Source Intelligence Platform
-- Generated for PostgreSQL 16
--
-- Purpose: Validates ALL 177 database objects (93 tables + 56 views + 28 materialized views)
--          defined in full_schema.sql with comprehensive coverage reporting
--
-- Issue: Hack23/cia#7872 - Extend Schema Validation Script
-- Related: Hack23/cia#7865 - Schema validation script foundation
--
-- *** MAINTENANCE NOTE ***
-- The object arrays in this script (tables, views, materialized views) must be kept
-- in sync with full_schema.sql. When the schema changes:
-- 1. Update the arrays in this script (lines ~56, ~220, ~335)
-- 2. Update the arrays in schema-coverage-analysis.sql
-- 3. Update the object counts in README-SCHEMA-MAINTENANCE.md
-- 4. Run this script to verify 100% coverage
-- Last synchronized: 2025-11-24
--
-- Usage:
--   psql -U postgres -d cia_dev -f schema-validation-v2.sql > schema_validation_report.txt 2>&1
--
-- Features:
--   - Validates ALL 93 base tables from full_schema.sql
--   - Validates ALL 56 regular views from full_schema.sql
--   - Validates ALL 28 materialized views from full_schema.sql
--   - Generates coverage report showing 100% validation
--   - Compares full_schema.sql objects vs actual database
--   - Identifies missing or extra objects
--   - Provides detailed statistics and health metrics

\set ECHO queries
\timing on

-- ===========================================================================
-- PART 1: VALIDATION METADATA
-- ===========================================================================

\echo ''
\echo '=========================================================================='
\echo '=== SCHEMA VALIDATION V2.0 - 100% COVERAGE                             ==='
\echo '=========================================================================='
\echo ''
\echo 'Issue: Hack23/cia#7872 - Extend Schema Validation Script'
\echo 'Purpose: Validate ALL 177 objects from full_schema.sql'
\echo 'Target Coverage: 93 tables + 56 views + 28 materialized views = 177 objects'
\echo ''
\echo 'Validation started at:' 
SELECT NOW();
\echo ''

-- ===========================================================================
-- PART 2: COMPREHENSIVE TABLE VALIDATION (ALL 93 TABLES)
-- ===========================================================================

\echo ''
\echo '=========================================================================='
\echo '=== COMPREHENSIVE TABLE VALIDATION (93 TABLES)                         ==='
\echo '=========================================================================='
\echo ''

DO $$
DECLARE
    -- *** MAINTENANCE NOTE ***
    -- This array must be kept in sync with full_schema.sql
    -- Update this list when tables are added/removed from the schema
    -- Last verified: 2025-11-24 against full_schema.sql
    -- Total expected: 93 tables
    v_all_tables TEXT[] := 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'
    ];
    v_table TEXT;
    v_count INTEGER;
    v_col_count INTEGER;
    v_total_validated INTEGER := 0;
    v_total_errors INTEGER := 0;
    v_total_expected INTEGER;
BEGIN
    v_total_expected := array_length(v_all_tables, 1);
    
    RAISE NOTICE 'Validating ALL % tables from full_schema.sql', v_total_expected;
    RAISE NOTICE '================================================================';
    RAISE NOTICE '';
    
    FOREACH v_table IN ARRAY v_all_tables
    LOOP
        BEGIN
            -- Get row count
            EXECUTE format('SELECT COUNT(*) FROM %I', v_table) INTO v_count;
            
            -- Get column count
            SELECT COUNT(*) INTO v_col_count
            FROM information_schema.columns
            WHERE table_schema = 'public' AND table_name = v_table;
            
            RAISE NOTICE '✓ Table: % | Rows: % | Columns: %', 
                         rpad(v_table, 40), lpad(v_count::text, 10), lpad(v_col_count::text, 3);
            v_total_validated := v_total_validated + 1;
        EXCEPTION WHEN OTHERS THEN
            RAISE WARNING '✗ Table: % | ERROR: %', v_table, SQLERRM;
            v_total_errors := v_total_errors + 1;
        END;
    END LOOP;
    
    RAISE NOTICE '';
    RAISE NOTICE '================================================================';
    RAISE NOTICE 'TABLE VALIDATION SUMMARY:';
    RAISE NOTICE '  Expected:  % tables', v_total_expected;
    RAISE NOTICE '  Validated: % tables (%.2f%%)', v_total_validated, 
                 (v_total_validated::float / v_total_expected * 100);
    RAISE NOTICE '  Errors:    % tables', v_total_errors;
    RAISE NOTICE '================================================================';
    RAISE NOTICE '';
END $$;

-- ===========================================================================
-- PART 3: COMPREHENSIVE REGULAR VIEW VALIDATION (ALL 56 VIEWS)
-- ===========================================================================

\echo ''
\echo '=========================================================================='
\echo '=== COMPREHENSIVE REGULAR VIEW VALIDATION (56 VIEWS)                   ==='
\echo '=========================================================================='
\echo ''

DO $$
DECLARE
    -- *** MAINTENANCE NOTE ***
    -- This array must be kept in sync with full_schema.sql
    -- Update this list when regular views are added/removed from the schema
    -- Last verified: 2025-11-24 against full_schema.sql
    -- Total expected: 56 regular views
    v_all_views TEXT[] := 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'
    ];
    v_view TEXT;
    v_count INTEGER;
    v_col_count INTEGER;
    v_total_validated INTEGER := 0;
    v_total_errors INTEGER := 0;
    v_total_expected INTEGER;
BEGIN
    v_total_expected := array_length(v_all_views, 1);
    
    RAISE NOTICE 'Validating ALL % regular views from full_schema.sql', v_total_expected;
    RAISE NOTICE '================================================================';
    RAISE NOTICE '';
    
    FOREACH v_view IN ARRAY v_all_views
    LOOP
        BEGIN
            -- Get row count
            EXECUTE format('SELECT COUNT(*) FROM %I', v_view) INTO v_count;
            
            -- Get column count
            SELECT COUNT(*) INTO v_col_count
            FROM information_schema.columns
            WHERE table_schema = 'public' AND table_name = v_view;
            
            RAISE NOTICE '✓ View: % | Rows: % | Columns: %', 
                         rpad(v_view, 60), lpad(v_count::text, 10), lpad(v_col_count::text, 3);
            v_total_validated := v_total_validated + 1;
        EXCEPTION WHEN OTHERS THEN
            RAISE WARNING '✗ View: % | ERROR: %', v_view, SQLERRM;
            v_total_errors := v_total_errors + 1;
        END;
    END LOOP;
    
    RAISE NOTICE '';
    RAISE NOTICE '================================================================';
    RAISE NOTICE 'REGULAR VIEW VALIDATION SUMMARY:';
    RAISE NOTICE '  Expected:  % views', v_total_expected;
    RAISE NOTICE '  Validated: % views (%.2f%%)', v_total_validated, 
                 (v_total_validated::float / v_total_expected * 100);
    RAISE NOTICE '  Errors:    % views', v_total_errors;
    RAISE NOTICE '================================================================';
    RAISE NOTICE '';
END $$;

-- ===========================================================================
-- PART 4: COMPREHENSIVE MATERIALIZED VIEW VALIDATION (ALL 28 MVIEWS)
-- ===========================================================================

\echo ''
\echo '=========================================================================='
\echo '=== COMPREHENSIVE MATERIALIZED VIEW VALIDATION (28 MVIEWS)             ==='
\echo '=========================================================================='
\echo ''

DO $$
DECLARE
    -- *** MAINTENANCE NOTE ***
    -- This array must be kept in sync with full_schema.sql
    -- Update this list when materialized views are added/removed from the schema
    -- Last verified: 2025-11-24 against full_schema.sql
    -- Total expected: 28 materialized views
    v_all_mviews TEXT[] := 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'
    ];
    v_mview TEXT;
    v_count INTEGER;
    v_col_count INTEGER;
    v_total_validated INTEGER := 0;
    v_total_errors INTEGER := 0;
    v_total_expected INTEGER;
BEGIN
    v_total_expected := array_length(v_all_mviews, 1);
    
    RAISE NOTICE 'Validating ALL % materialized views from full_schema.sql', v_total_expected;
    RAISE NOTICE '================================================================';
    RAISE NOTICE '';
    
    FOREACH v_mview IN ARRAY v_all_mviews
    LOOP
        BEGIN
            -- Get row count
            EXECUTE format('SELECT COUNT(*) FROM %I', v_mview) INTO v_count;
            
            -- Get column count
            SELECT COUNT(*) INTO v_col_count
            FROM information_schema.columns
            WHERE table_schema = 'public' AND table_name = v_mview;
            
            RAISE NOTICE '✓ MView: % | Rows: % | Columns: %', 
                         rpad(v_mview, 60), lpad(v_count::text, 10), lpad(v_col_count::text, 3);
            v_total_validated := v_total_validated + 1;
        EXCEPTION WHEN OTHERS THEN
            RAISE WARNING '✗ MView: % | ERROR: %', v_mview, SQLERRM;
            v_total_errors := v_total_errors + 1;
        END;
    END LOOP;
    
    RAISE NOTICE '';
    RAISE NOTICE '================================================================';
    RAISE NOTICE 'MATERIALIZED VIEW VALIDATION SUMMARY:';
    RAISE NOTICE '  Expected:  % mviews', v_total_expected;
    RAISE NOTICE '  Validated: % mviews (%.2f%%)', v_total_validated, 
                 (v_total_validated::float / v_total_expected * 100);
    RAISE NOTICE '  Errors:    % mviews', v_total_errors;
    RAISE NOTICE '================================================================';
    RAISE NOTICE '';
END $$;

-- ===========================================================================
-- PART 5: COVERAGE REPORT - FULL_SCHEMA.SQL VS ACTUAL DATABASE
-- ===========================================================================

\echo ''
\echo '=========================================================================='
\echo '=== COVERAGE REPORT: FULL_SCHEMA.SQL VS ACTUAL DATABASE                ==='
\echo '=========================================================================='
\echo ''

-- Report: Tables in full_schema.sql vs database
\echo '--- TABLES: Checking full_schema.sql (93) vs actual database ---'
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'
)
SELECT 
    COALESCE(e.table_name, a.table_name) AS table_name,
    CASE 
        WHEN e.table_name IS NULL THEN 'EXTRA (in DB, not in schema)'
        WHEN a.table_name IS NULL THEN 'MISSING (in schema, not in DB)'
        ELSE 'OK'
    END AS status
FROM expected_tables e
FULL OUTER JOIN actual_tables a ON e.table_name = a.table_name
WHERE e.table_name IS NULL OR a.table_name IS NULL
ORDER BY status, table_name;

-- Count coverage
WITH expected_tables AS (
    SELECT 93 AS expected_count
),
actual_match AS (
    SELECT COUNT(*) AS matched_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,
    expected_count AS expected,
    matched_count AS found_in_db,
    ROUND(matched_count::numeric / expected_count * 100, 2) AS coverage_pct
FROM expected_tables, actual_match;

\echo ''
\echo '--- REGULAR VIEWS: Checking full_schema.sql (56) vs actual database ---'
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'
)
SELECT 
    COALESCE(e.view_name, a.view_name) AS view_name,
    CASE 
        WHEN e.view_name IS NULL THEN 'EXTRA (in DB, not in schema)'
        WHEN a.view_name IS NULL THEN 'MISSING (in schema, not in DB)'
        ELSE 'OK'
    END AS status
FROM expected_views e
FULL OUTER JOIN actual_views a ON e.view_name = a.view_name
WHERE e.view_name IS NULL OR a.view_name IS NULL
ORDER BY status, view_name;

-- Count coverage
WITH expected_views AS (
    SELECT 56 AS expected_count
),
actual_match AS (
    SELECT COUNT(*) AS matched_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,
    expected_count AS expected,
    matched_count AS found_in_db,
    ROUND(matched_count::numeric / expected_count * 100, 2) AS coverage_pct
FROM expected_views, actual_match;

\echo ''
\echo '--- MATERIALIZED VIEWS: Checking full_schema.sql (28) vs actual database ---'
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'
)
SELECT 
    COALESCE(e.mview_name, a.mview_name) AS mview_name,
    CASE 
        WHEN e.mview_name IS NULL THEN 'EXTRA (in DB, not in schema)'
        WHEN a.mview_name IS NULL THEN 'MISSING (in schema, not in DB)'
        ELSE 'OK'
    END AS status
FROM expected_mviews e
FULL OUTER JOIN actual_mviews a ON e.mview_name = a.mview_name
WHERE e.mview_name IS NULL OR a.mview_name IS NULL
ORDER BY status, mview_name;

-- Count coverage
WITH expected_mviews AS (
    SELECT 28 AS expected_count
),
actual_match AS (
    SELECT COUNT(*) AS matched_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,
    expected_count AS expected,
    matched_count AS found_in_db,
    ROUND(matched_count::numeric / expected_count * 100, 2) AS coverage_pct
FROM expected_mviews, actual_match;

-- ===========================================================================
-- PART 6: FINAL COVERAGE SUMMARY
-- ===========================================================================

\echo ''
\echo '=========================================================================='
\echo '=== FINAL VALIDATION COVERAGE SUMMARY                                  ==='
\echo '=========================================================================='
\echo ''

DO $$
DECLARE
    v_table_count INTEGER;
    v_view_count INTEGER;
    v_mview_count INTEGER;
    v_total_validated INTEGER;
    v_coverage_pct NUMERIC;
BEGIN
    -- Get actual counts
    SELECT COUNT(*) INTO v_table_count FROM pg_tables WHERE schemaname = 'public';
    SELECT COUNT(*) INTO v_view_count FROM pg_views WHERE schemaname = 'public';
    SELECT COUNT(*) INTO v_mview_count FROM pg_matviews WHERE schemaname = 'public';
    
    v_total_validated := v_table_count + v_view_count + v_mview_count;
    v_coverage_pct := ROUND((v_total_validated::numeric / 177) * 100, 2);
    
    RAISE NOTICE '╔════════════════════════════════════════════════════════════════╗';
    RAISE NOTICE '║               SCHEMA VALIDATION V2.0 REPORT                    ║';
    RAISE NOTICE '╠════════════════════════════════════════════════════════════════╣';
    RAISE NOTICE '║ Object Type          │ Expected │ Validated │ Coverage       ║';
    RAISE NOTICE '╠══════════════════════╪══════════╪═══════════╪════════════════╣';
    RAISE NOTICE '║ Tables               │ %       │ %        │ %          ║', 
                 lpad('93', 8), lpad(v_table_count::text, 9), 
                 lpad(ROUND((v_table_count::numeric/93)*100, 1)::text || '%', 14);
    RAISE NOTICE '║ Regular Views        │ %       │ %        │ %          ║', 
                 lpad('56', 8), lpad(v_view_count::text, 9), 
                 lpad(ROUND((v_view_count::numeric/56)*100, 1)::text || '%', 14);
    RAISE NOTICE '║ Materialized Views   │ %       │ %        │ %          ║', 
                 lpad('28', 8), lpad(v_mview_count::text, 9), 
                 lpad(ROUND((v_mview_count::numeric/28)*100, 1)::text || '%', 14);
    RAISE NOTICE '╠══════════════════════╪══════════╪═══════════╪════════════════╣';
    RAISE NOTICE '║ TOTAL                │ %      │ %       │ %         ║', 
                 lpad('177', 8), lpad(v_total_validated::text, 9), 
                 lpad(v_coverage_pct::text || '%', 14);
    RAISE NOTICE '╚════════════════════════════════════════════════════════════════╝';
    RAISE NOTICE '';
    
    IF v_coverage_pct >= 100 THEN
        RAISE NOTICE '✓✓✓ SUCCESS: 100%% validation coverage achieved!';
        RAISE NOTICE '    All objects from full_schema.sql are validated.';
    ELSIF v_coverage_pct >= 95 THEN
        RAISE NOTICE '✓✓ EXCELLENT: %.2f%% coverage - nearly complete', v_coverage_pct;
    ELSIF v_coverage_pct >= 90 THEN
        RAISE NOTICE '✓ GOOD: %.2f%% coverage - minor gaps remain', v_coverage_pct;
    ELSE
        RAISE NOTICE '⚠ WARNING: %.2f%% coverage - significant gaps exist', v_coverage_pct;
    END IF;
    
    RAISE NOTICE '';
END $$;

\echo ''
\echo '=========================================================================='
\echo '=== VALIDATION COMPLETED                                               ==='
\echo '=========================================================================='
\echo ''
\echo 'Validation finished at:'
SELECT NOW();
\echo ''
\echo 'Issue: Hack23/cia#7872 - Extend Schema Validation Script'
\echo 'Status: Complete - 100% coverage validation implemented'
\echo ''

\timing off