-- Test Script: POS Sessions Management Enhancement Migration
-- Date: 2025-11-15
-- Purpose: Verify that the migration was applied correctly
-- Run this after applying 20251115_pos_sessions_management_enhancement.sql

-- ========================================
-- TEST 1: Verify New Columns Exist
-- ========================================

DO $$
DECLARE
    missing_columns TEXT[] := ARRAY[]::TEXT[];
    col TEXT;
BEGIN
    RAISE NOTICE '========================================';
    RAISE NOTICE 'TEST 1: Checking if all new columns exist';
    RAISE NOTICE '========================================';
    
    -- List of expected columns
    FOREACH col IN ARRAY ARRAY[
        'verified',
        'verified_by',
        'verified_at',
        'supervisor_counted_cash',
        'adjusted_variance',
        'variance_explanation',
        'verification_notes',
        'approval_status',
        'total_sales',
        'cash_sales'
    ] LOOP
        IF NOT EXISTS (
            SELECT 1 
            FROM information_schema.columns 
            WHERE table_name = 'pos_sessions' 
            AND column_name = col
        ) THEN
            missing_columns := array_append(missing_columns, col);
        END IF;
    END LOOP;
    
    IF array_length(missing_columns, 1) > 0 THEN
        RAISE WARNING 'FAILED: Missing columns: %', array_to_string(missing_columns, ', ');
    ELSE
        RAISE NOTICE '✓ PASSED: All columns exist';
    END IF;
END $$;

-- ========================================
-- TEST 2: Verify Constraints Exist
-- ========================================

DO $$
BEGIN
    RAISE NOTICE '';
    RAISE NOTICE '========================================';
    RAISE NOTICE 'TEST 2: Checking constraints';
    RAISE NOTICE '========================================';
    
    -- Check foreign key constraint
    IF EXISTS (
        SELECT 1 FROM pg_constraint 
        WHERE conname = 'fk_pos_sessions_verified_by'
    ) THEN
        RAISE NOTICE '✓ PASSED: Foreign key constraint fk_pos_sessions_verified_by exists';
    ELSE
        RAISE WARNING 'FAILED: Foreign key constraint fk_pos_sessions_verified_by missing';
    END IF;
    
    -- Check check constraint
    IF EXISTS (
        SELECT 1 FROM pg_constraint 
        WHERE conname = 'chk_pos_sessions_approval_status'
    ) THEN
        RAISE NOTICE '✓ PASSED: Check constraint chk_pos_sessions_approval_status exists';
    ELSE
        RAISE WARNING 'FAILED: Check constraint chk_pos_sessions_approval_status missing';
    END IF;
END $$;

-- ========================================
-- TEST 3: Verify Indexes Exist
-- ========================================

DO $$
DECLARE
    missing_indexes TEXT[] := ARRAY[]::TEXT[];
    idx TEXT;
BEGIN
    RAISE NOTICE '';
    RAISE NOTICE '========================================';
    RAISE NOTICE 'TEST 3: Checking indexes';
    RAISE NOTICE '========================================';
    
    FOREACH idx IN ARRAY ARRAY[
        'idx_pos_sessions_verified',
        'idx_pos_sessions_verified_by',
        'idx_pos_sessions_approval_status',
        'idx_pos_sessions_cashier_date',
        'idx_pos_sessions_status_verified'
    ] LOOP
        IF NOT EXISTS (
            SELECT 1 FROM pg_indexes 
            WHERE indexname = idx
        ) THEN
            missing_indexes := array_append(missing_indexes, idx);
        END IF;
    END LOOP;
    
    IF array_length(missing_indexes, 1) > 0 THEN
        RAISE WARNING 'FAILED: Missing indexes: %', array_to_string(missing_indexes, ', ');
    ELSE
        RAISE NOTICE '✓ PASSED: All indexes exist';
    END IF;
END $$;

-- ========================================
-- TEST 4: Verify Enhanced View Exists
-- ========================================

DO $$
BEGIN
    RAISE NOTICE '';
    RAISE NOTICE '========================================';
    RAISE NOTICE 'TEST 4: Checking enhanced view';
    RAISE NOTICE '========================================';
    
    IF EXISTS (
        SELECT 1 FROM information_schema.views 
        WHERE table_name = 'pos_sessions_enhanced'
    ) THEN
        RAISE NOTICE '✓ PASSED: View pos_sessions_enhanced exists';
        
        -- Test if view is queryable
        PERFORM COUNT(*) FROM pos_sessions_enhanced;
        RAISE NOTICE '✓ PASSED: View pos_sessions_enhanced is queryable';
    ELSE
        RAISE WARNING 'FAILED: View pos_sessions_enhanced missing';
    END IF;
END $$;

-- ========================================
-- TEST 5: Verify Function Exists
-- ========================================

DO $$
BEGIN
    RAISE NOTICE '';
    RAISE NOTICE '========================================';
    RAISE NOTICE 'TEST 5: Checking payment breakdown function';
    RAISE NOTICE '========================================';
    
    IF EXISTS (
        SELECT 1 FROM pg_proc 
        WHERE proname = 'get_session_payment_breakdown'
    ) THEN
        RAISE NOTICE '✓ PASSED: Function get_session_payment_breakdown exists';
    ELSE
        RAISE WARNING 'FAILED: Function get_session_payment_breakdown missing';
    END IF;
END $$;

-- ========================================
-- TEST 6: Verify Audit Log Table Exists
-- ========================================

DO $$
BEGIN
    RAISE NOTICE '';
    RAISE NOTICE '========================================';
    RAISE NOTICE 'TEST 6: Checking audit log table';
    RAISE NOTICE '========================================';
    
    IF EXISTS (
        SELECT 1 FROM information_schema.tables 
        WHERE table_name = 'pos_session_verification_log'
    ) THEN
        RAISE NOTICE '✓ PASSED: Table pos_session_verification_log exists';
    ELSE
        RAISE WARNING 'FAILED: Table pos_session_verification_log missing';
    END IF;
END $$;

-- ========================================
-- TEST 7: Verify Trigger Exists
-- ========================================

DO $$
BEGIN
    RAISE NOTICE '';
    RAISE NOTICE '========================================';
    RAISE NOTICE 'TEST 7: Checking verification trigger';
    RAISE NOTICE '========================================';
    
    IF EXISTS (
        SELECT 1 FROM pg_trigger 
        WHERE tgname = 'trg_log_session_verification'
    ) THEN
        RAISE NOTICE '✓ PASSED: Trigger trg_log_session_verification exists';
    ELSE
        RAISE WARNING 'FAILED: Trigger trg_log_session_verification missing';
    END IF;
END $$;

-- ========================================
-- TEST 8: Verify Data Migration
-- ========================================

DO $$
DECLARE
    total_sessions INTEGER;
    sessions_with_totals INTEGER;
    sessions_with_approval_status INTEGER;
BEGIN
    RAISE NOTICE '';
    RAISE NOTICE '========================================';
    RAISE NOTICE 'TEST 8: Checking data migration';
    RAISE NOTICE '========================================';
    
    SELECT COUNT(*) INTO total_sessions FROM pos_sessions WHERE NOT isdeleted;
    
    SELECT COUNT(*) INTO sessions_with_totals 
    FROM pos_sessions 
    WHERE NOT isdeleted 
    AND total_sales IS NOT NULL 
    AND cash_sales IS NOT NULL;
    
    SELECT COUNT(*) INTO sessions_with_approval_status 
    FROM pos_sessions 
    WHERE NOT isdeleted 
    AND status IN ('closed', 'abandoned')
    AND approval_status IS NOT NULL;
    
    RAISE NOTICE 'Total sessions: %', total_sessions;
    RAISE NOTICE 'Sessions with calculated totals: %', sessions_with_totals;
    RAISE NOTICE 'Closed/Abandoned sessions with approval status: %', sessions_with_approval_status;
    
    IF sessions_with_totals = total_sessions THEN
        RAISE NOTICE '✓ PASSED: All sessions have calculated totals';
    ELSE
        RAISE WARNING 'WARNING: % sessions missing calculated totals', (total_sessions - sessions_with_totals);
    END IF;
END $$;

-- ========================================
-- TEST 9: Test Enhanced View Query
-- ========================================

DO $$
DECLARE
    test_record RECORD;
BEGIN
    RAISE NOTICE '';
    RAISE NOTICE '========================================';
    RAISE NOTICE 'TEST 9: Testing enhanced view queries';
    RAISE NOTICE '========================================';
    
    -- Get a sample record from the enhanced view
    SELECT * INTO test_record 
    FROM pos_sessions_enhanced 
    LIMIT 1;
    
    IF FOUND THEN
        RAISE NOTICE '✓ PASSED: Can query pos_sessions_enhanced';
        RAISE NOTICE '  Sample record:';
        RAISE NOTICE '    Session ID: %', test_record.session_id;
        RAISE NOTICE '    Cashier: %', test_record.cashier_name;
        RAISE NOTICE '    Verification Status: %', test_record.verification_status;
        RAISE NOTICE '    Variance Level: %', test_record.variance_level;
        RAISE NOTICE '    Transaction Count: %', test_record.transaction_count;
    ELSE
        RAISE NOTICE 'INFO: No sessions found (this is OK if database is empty)';
    END IF;
END $$;

-- ========================================
-- TEST 10: Test Payment Breakdown Function
-- ========================================

DO $$
DECLARE
    test_session_id UUID;
    breakdown_count INTEGER;
BEGIN
    RAISE NOTICE '';
    RAISE NOTICE '========================================';
    RAISE NOTICE 'TEST 10: Testing payment breakdown function';
    RAISE NOTICE '========================================';
    
    -- Get a session with transactions
    SELECT session_id INTO test_session_id
    FROM pos_sessions
    WHERE NOT isdeleted
    AND status = 'closed'
    LIMIT 1;
    
    IF test_session_id IS NOT NULL THEN
        SELECT COUNT(*) INTO breakdown_count
        FROM get_session_payment_breakdown(test_session_id);
        
        RAISE NOTICE '✓ PASSED: Function get_session_payment_breakdown is executable';
        RAISE NOTICE '  Test session: %', test_session_id;
        RAISE NOTICE '  Payment methods found: %', breakdown_count;
        
        -- Show the breakdown
        IF breakdown_count > 0 THEN
            RAISE NOTICE '  Payment breakdown:';
            FOR breakdown_count IN 
                SELECT payment_method, transaction_count, total_amount
                FROM get_session_payment_breakdown(test_session_id)
            LOOP
                -- Results will be shown in query output
            END LOOP;
        END IF;
    ELSE
        RAISE NOTICE 'INFO: No closed sessions found to test (this is OK if database is empty)';
    END IF;
END $$;

-- Display payment breakdown results (if any)
SELECT 
    session_id,
    payment_method,
    transaction_count,
    to_char(total_amount, 'FM$999,999,990.00') as total_amount
FROM (
    SELECT session_id
    FROM pos_sessions
    WHERE NOT isdeleted AND status = 'closed'
    LIMIT 1
) s
CROSS JOIN LATERAL get_session_payment_breakdown(s.session_id)
ORDER BY total_amount DESC;

-- ========================================
-- SUMMARY
-- ========================================

DO $$
BEGIN
    RAISE NOTICE '';
    RAISE NOTICE '========================================';
    RAISE NOTICE 'MIGRATION VERIFICATION COMPLETE';
    RAISE NOTICE '========================================';
    RAISE NOTICE 'Review the output above for any FAILED or WARNING messages';
    RAISE NOTICE 'All tests should show ✓ PASSED for a successful migration';
END $$;

-- Show sample enhanced view data
SELECT 
    session_id,
    cashier_name,
    status,
    verification_status,
    variance_level,
    display_variance,
    transaction_count,
    to_char(total_sales, 'FM$999,999,990.00') as total_sales,
    to_char(cash_sales, 'FM$999,999,990.00') as cash_sales
FROM pos_sessions_enhanced
ORDER BY session_start DESC
LIMIT 5;
