massive_real_world_data.sql

-- ============================================================
-- CDR TEST DATA GENERATOR - OVERAGES & ROAMING EDITION
-- Purpose: Generate realistic test data with overages and roaming
-- Output: CSV files ready for CDR Exporter Import
-- ============================================================

-- First, let's ensure we have roaming service packages
DO $$
BEGIN
    -- Add roaming packages if they don't exist
    INSERT INTO service_package (name, type, amount, priority, price, is_roaming, description)
    VALUES 
        ('International Voice', 'voice', 50, 1, 150, TRUE, '50 roaming minutes'),
        ('International Data', 'data', 1024, 1, 300, TRUE, '1GB roaming data'),
        ('International SMS', 'sms', 50, 1, 50, TRUE, '50 roaming SMS')
    ON CONFLICT DO NOTHING;
END $$;

-- ============================================================
-- GENERATE CDR CSV DATA - WITH OVERAGES & ROAMING
-- ============================================================

DO $$
DECLARE
    v_counter INTEGER := 0;
    v_batch INTEGER := 0;
    v_line TEXT;
    v_file_id INTEGER;
    v_output TEXT;
BEGIN
    RAISE NOTICE '=== Generating CDR Test Data with Overages & Roaming ===';

    -- Create file record
    INSERT INTO file (parsed_flag, file_path) VALUES (FALSE, 'OVERAGE_ROAMING_TEST_CDRS.csv')
    RETURNING id INTO v_file_id;
    
    RAISE NOTICE 'Created file record ID: %', v_file_id;

    -- ========================================
    -- BATCH 1: Normal Usage (within quota) - 10 CDRs
    -- ========================================
    RAISE NOTICE 'Creating batch 1: Normal usage within quota...';
    
    -- Customer 1: Basic plan (2000 voice mins) - uses 1800, should be within quota
    FOR i IN 1..10 LOOP
        v_line := v_file_id || ',201000000001,201090000000,2026-04-01 10:' || LPAD(i::TEXT, 2, '0') || ':00,180,1,EGYVO,,0';
        v_output := COALESCE(v_output, '') || v_line || E'\n';
    END LOOP;

    -- ========================================
    -- BATCH 2: OVERAGE - Voice (exceeds 2000 min quota)
    -- ========================================
    RAISE NOTICE 'Creating batch 2: Voice OVERAGES (exceeds quota)...';
    
    -- Customer with Basic plan - make 15 calls of 200 seconds each = 3000 seconds = 50 minutes OVERAGE
    FOR i IN 11..25 LOOP
        -- This will exceed the 2000 minute quota
        v_line := v_file_id || ',201000000001,201090000000,2026-04-01 14:' || LPAD(i::TEXT, 2, '0') || ':00,200,1,EGYVO,,0';
        v_output := v_output || E'\n' || v_line;
    END LOOP;

    -- ========================================
    -- BATCH 3: OVERAGE - Data (exceeds 10000 MB quota)
    -- ========================================
    RAISE NOTICE 'Creating batch 3: Data OVERAGES (exceeds quota)...';
    
    -- Customer with Premium (10000 MB data) - use 15000 MB = 5000 MB OVERAGE
    FOR i IN 1..5 LOOP
        -- Data in bytes (service_id=2), 3GB = 3221225472 bytes
        v_line := v_file_id || ',201000000002,201098000000,2026-04-01 11:30:00,3221225472,2,EGYVO,,0';
        v_output := v_output || E'\n' || v_line;
    END LOOP;

    -- Another 2GB data usage
    FOR i IN 6..7 LOOP
        v_line := v_file_id || ',201000000002,201098000000,2026-04-01 16:' || LPAD(i::TEXT, 2, '0') || ':00,2147483648,2,EGYVO,,0';
        v_output := v_output || E'\n' || v_line;
    END LOOP;

    -- ========================================
    -- BATCH 4: ROAMING CDRs (vplmn set - international)
    -- ========================================
    RAISE NOTICE 'Creating batch 4: ROAMING CDRs...';
    
    -- Roaming in Egypt (outside home network) - customer on Premium with roaming
    FOR i IN 1..10 LOOP
        -- vplmn set = roaming detected
        v_line := v_file_id || ',201000000002,201099000000,2026-04-01 12:' || LPAD(i::TEXT, 2, '0') || ':00,300,1,EGYVO,VODAFONE,0';
        v_output := v_output || E'\n' || v_line;
    END LOOP;

    -- Roaming data (larger usage)
    FOR i IN 1..3 LOOP
        v_line := v_file_id || ',201000000002,201099000000,2026-04-01 17:' || LPAD(i::TEXT, 2, '0') || ':00,1572864000,2,EGYVO,VODAFONE,0';
        v_output := v_output || E'\n' || v_line;
    END LOOP;

    -- ========================================
    -- BATCH 5: Heavy Overage - Multiple customers
    -- ========================================
    RAISE NOTICE 'Creating batch 5: Heavy overages from multiple customers...';
    
    -- Customer on Basic ( Voice Pack 2000 mins)
    FOR i IN 1..20 LOOP
        v_line := v_file_id || ',201000000003,201091000000,2026-04-02 09:' || LPAD(i::TEXT, 2, '0') || ':00,150,1,EGYVO,,0';
        v_output := v_output || E'\n' || v_line;
    END LOOP;

    -- Customer on Premium (Data Pack 10000 MB)
    FOR i IN 1..8 LOOP
        v_line := v_file_id || ',201000000004,201092000000,2026-04-02 10:' || LPAD(i::TEXT, 2, '0') || ':00,2097152000,2,EGYVO,,0';
        v_output := v_output || E'\n' || v_line;
    END LOOP;

    -- ========================================
    -- BATCH 6: SMS Overages
    -- ========================================
    RAISE NOTICE 'Creating batch 6: SMS overages...';
    
    -- SMS Pack has 500 limit - use 600 (100 overage)
    FOR i IN 1..100 LOOP
        v_line := v_file_id || ',201000000001,201097000000,2026-04-01 20:00:00,1,3,EGYVO,,0';
        v_output := v_output || E'\n' || v_line;
    END LOOP;

    -- ========================================
    -- BATCH 7: Mixed Services with Roaming Charges
    -- ========================================
    RAISE NOTICE 'Creating batch 7: Roaming + Overages combined...';
    
    -- Roaming customer who exceeds roaming pack
    FOR i IN 1..15 LOOP
        v_line := v_file_id || ',201000000005,201094000000,2026-04-03 08:' || LPAD(i::TEXT, 2, '0') || ':00,400,1,EGYVO,ETISALAT,0';
        v_output := v_output || E'\n' || v_line;
    END LOOP;

    -- Roaming data overage
    FOR i IN 1..5 LOOP
        v_line := v_file_id || ',201000000005,201094000000,2026-04-03 15:' || LPAD(i::TEXT, 2, '0') || ':00,3145728000,2,EGYVO,ETISALAT,0';
        v_output := v_output || E'\n' || v_line;
    END LOOP;

    -- ========================================
    -- Output to file for import
    -- ========================================
    RAISE NOTICE 'Writing CSV file...';
    
    -- Note: In PostgreSQL we can't write files directly
    -- This generates the data for you to copy/export
    -- The output below shows what would be in the CSV
    
    RAISE NOTICE '============================================';
    RAISE NOTICE 'CDR CSV OUTPUT (copy this to a .csv file)';
    RAISE NOTICE '============================================';
    RAISE NOTICE '%', v_output;
    RAISE NOTICE '============================================';
    
    -- Actually insert the CDRs into the database
    RAISE NOTICE 'Inserting CDRs directly into database...';
    
END $$;

-- ============================================================
-- DIRECT CDR INSERTION WITH OVERAGES & ROAMING
-- ============================================================

DO $$
DECLARE
    v_file_id INTEGER;
    v_cdr_id INTEGER;
    v_contract_id INTEGER;
BEGIN
    RAISE NOTICE '=== Direct CDR Insertion ===';
    
    -- Get or create file
    INSERT INTO file (parsed_flag, file_path) 
    VALUES (FALSE, 'OVERAGE_ROAMING_DIRECT.csv')
    ON CONFLICT (file_path) DO UPDATE SET file_path = EXCLUDED.file_path
    RETURNING id INTO v_file_id;
    
    RAISE NOTICE 'File ID: %', v_file_id;

    -- ========================================
    -- Test Case 1: Normal usage (within quota)
    -- ========================================
    FOR i IN 1..10 LOOP
        INSERT INTO cdr (file_id, dial_a, dial_b, start_time, duration, service_id, hplmn, vplmn, external_charges, rated_flag)
        VALUES (v_file_id, '201000000001', '201090000000', '2026-04-01 10:00:00', 180, 1, 'EGYVO', NULL, 0, FALSE)
        RETURNING id INTO v_cdr_id;
    END LOOP;
    RAISE NOTICE 'Inserted 10 normal voice CDRs';

    -- ========================================
    -- Test Case 2: Voice OVERAGE (exceeds 2000 min)
    -- ========================================
    FOR i IN 11..25 LOOP
        INSERT INTO cdr (file_id, dial_a, dial_b, start_time, duration, service_id, hplmn, vplmn, external_charges, rated_flag)
        VALUES (v_file_id, '201000000001', '201090000000', '2026-04-01 14:00:00', 200, 1, 'EGYVO', NULL, 0, FALSE)
        RETURNING id INTO v_cdr_id;
    END LOOP;
    RAISE NOTICE 'Inserted 15 voice OVERAGE CDRs (3000 sec / 50 min over quota)';

    -- ========================================
    -- Test Case 3: Data OVERAGE (exceeds 10000 MB)
    -- ========================================
    FOR i IN 1..5 LOOP
        INSERT INTO cdr (file_id, dial_a, dial_b, start_time, duration, service_id, hplmn, vplmn, external_charges, rated_flag)
        VALUES (v_file_id, '201000000002', '201098000000', '2026-04-01 11:30:00', 3221225472, 2, 'EGYVO', NULL, 0, FALSE);
    END LOOP;
    FOR i IN 6..7 LOOP
        INSERT INTO cdr (file_id, dial_a, dial_b, start_time, duration, service_id, hplmn, vplmn, external_charges, rated_flag)
        VALUES (v_file_id, '201000000002', '201098000000', '2026-04-01 16:00:00', 2147483648, 2, 'EGYVO', NULL, 0, FALSE);
    END LOOP;
    RAISE NOTICE 'Inserted 7 data OVERAGE CDRs (~16GB total, ~6GB over quota)';

    -- ========================================
    -- Test Case 4: ROAMING CDRs (vplmn set)
    -- ========================================
    FOR i IN 1..10 LOOP
        INSERT INTO cdr (file_id, dial_a, dial_b, start_time, duration, service_id, hplmn, vplmn, external_charges, rated_flag)
        VALUES (v_file_id, '201000000002', '201099000000', '2026-04-01 12:00:00', 300, 1, 'EGYVO', 'VODAFONE', 0, FALSE);
    END LOOP;
    FOR i IN 1..3 LOOP
        INSERT INTO cdr (file_id, dial_a, dial_b, start_time, duration, service_id, hplmn, vplmn, external_charges, rated_flag)
        VALUES (v_file_id, '201000000002', '201099000000', '2026-04-01 17:00:00', 1572864000, 2, 'EGYVO', 'VODAFONE', 0, FALSE);
    END LOOP;
    RAISE NOTICE 'Inserted 13 roaming CDRs (voice + data)';

    -- ========================================
    -- Test Case 5: More customers with overages
    -- ========================================
    FOR i IN 1..20 LOOP
        INSERT INTO cdr (file_id, dial_a, dial_b, start_time, duration, service_id, hplmn, vplmn, external_charges, rated_flag)
        VALUES (v_file_id, '201000000003', '201091000000', '2026-04-02 09:00:00', 150, 1, 'EGYVO', NULL, 0, FALSE);
    END LOOP;
    FOR i IN 1..8 LOOP
        INSERT INTO cdr (file_id, dial_a, dial_b, start_time, duration, service_id, hplmn, vplmn, external_charges, rated_flag)
        VALUES (v_file_id, '201000000004', '201092000000', '2026-04-02 10:00:00', 2097152000, 2, 'EGYVO', NULL, 0, FALSE);
    END LOOP;
    RAISE NOTICE 'Inserted 20 voice + 8 data CDRs from multiple customers';

    -- ========================================
    -- Test Case 6: SMS overage
    -- ========================================
    FOR i IN 1..100 LOOP
        INSERT INTO cdr (file_id, dial_a, dial_b, start_time, duration, service_id, hplmn, vplmn, external_charges, rated_flag)
        VALUES (v_file_id, '201000000001', '201097000000', '2026-04-01 20:00:00', 1, 3, 'EGYVO', NULL, 0, FALSE);
    END LOOP;
    RAISE NOTICE 'Inserted 100 SMS CDRs (exceeds 500 limit)';

    -- ========================================
    -- Test Case 7: Roaming + Overages combined
    -- ========================================
    FOR i IN 1..15 LOOP
        INSERT INTO cdr (file_id, dial_a, dial_b, start_time, duration, service_id, hplmn, vplmn, external_charges, rated_flag)
        VALUES (v_file_id, '201000000005', '201094000000', '2026-04-03 08:00:00', 400, 1, 'EGYVO', 'ETISALAT', 0, FALSE);
    END LOOP;
    FOR i IN 1..5 LOOP
        INSERT INTO cdr (file_id, dial_a, dial_b, start_time, duration, service_id, hplmn, vplmn, external_charges, rated_flag)
        VALUES (v_file_id, '201000000005', '201094000000', '2026-04-03 15:00:00', 3145728000, 2, 'EGYVO', 'ETISALAT', 0, FALSE);
    END LOOP;
    RAISE NOTICE 'Inserted 20 roaming + overage CDRs';

    -- Mark file as parsed
    UPDATE file SET parsed_flag = TRUE WHERE id = v_file_id;
    
    RAISE NOTICE '============================================';
    RAISE NOTICE 'Total CDRs inserted: ~203';
    RAISE NOTICE 'Test scenarios:';
    RAISE NOTICE '  - Normal usage (within quota)';
    RAISE NOTICE '  - Voice overages (exceeds 2000 min)';
    RAISE NOTICE '  - Data overages (exceeds 10000 MB)';
    RAISE NOTICE '  - Roaming CDRs (vplmn set)';
    RAISE NOTICE '  - SMS overages (exceeds 500)';
    RAISE NOTICE '  - Combined roaming + overages';
    RAISE NOTICE '============================================';
    
END $$;

-- ============================================================
-- RATE ALL THE CDRs
-- ============================================================
DO $$
DECLARE
    v_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_count FROM cdr WHERE rated_flag = FALSE;
    RAISE NOTICE 'Rating % unrated CDRs...', v_count;
    
    FOR v_count IN SELECT id FROM cdr WHERE rated_flag = FALSE LOOP
        PERFORM rate_cdr(v_count.id);
    END LOOP;
    
    RAISE NOTICE 'All CDRs rated successfully!';
    
    -- Show results
    RAISE NOTICE '';
    RAISE NOTICE '=== RATING RESULTS ===';
    RAISE NOTICE 'Voice Pack consumed: %', (SELECT SUM(consumed) FROM contract_consumption WHERE service_package_id = 1);
    RAISE NOTICE 'Data Pack consumed: %', (SELECT SUM(consumed) FROM contract_consumption WHERE service_package_id = 2);
    RAISE NOTICE 'SMS Pack consumed: %', (SELECT SUM(consumed) FROM contract_consumption WHERE service_package_id = 3);
    
END $$;

-- ============================================================
-- GENERATE BILLS TO SEE OVERAGES & ROAMING CHARGES
-- ============================================================
DO $$
DECLARE
    v_contract_id INTEGER;
BEGIN
    RAISE NOTICE '=== Generating Bills ===';
    
    FOR v_contract_id IN SELECT id FROM contract WHERE status = 'active' LOOP
        BEGIN
            PERFORM generate_bill(v_contract_id, '2026-04-01');
        EXCEPTION WHEN OTHERS THEN
            RAISE WARNING 'Failed to generate bill for contract %: %', v_contract_id, SQLERRM;
        END;
    END LOOP;
    
    RAISE NOTICE 'Bills generated!';
    
    -- Show bill details with overages
    RAISE NOTICE '';
    RAISE NOTICE '=== BILL DETAILS (showing overages & roaming) ===';
    FOR v_contract_id IN SELECT id FROM bill WHERE billing_period_start = '2026-04-01' LOOP
        RAISE NOTICE 'Bill for contract %:', v_contract_id;
        RAISE NOTICE '  Voice: %', (SELECT voice_usage FROM bill WHERE contract_id = v_contract_id);
        RAISE NOTICE '  Data: %', (SELECT data_usage FROM bill WHERE contract_id = v_contract_id);
        RAISE NOTICE '  SMS: %', (SELECT sms_usage FROM bill WHERE contract_id = v_contract_id);
        RAISE NOTICE '  Overage: %', (SELECT overage_charge FROM bill WHERE contract_id = v_contract_id);
        RAISE NOTICE '  Roaming: %', (SELECT roaming_charge FROM bill WHERE contract_id = v_contract_id);
    END LOOP;
    
END $$;

-- ============================================================
-- SUMMARY
-- ============================================================
DO $$
BEGIN
    RAISE NOTICE '';
    RAISE NOTICE '============================================';
    RAISE NOTICE 'TEST DATA GENERATION COMPLETE!';
    RAISE NOTICE '============================================';
    RAISE NOTICE '';
    RAISE NOTICE 'This data includes:';
    RAISE NOTICE '1. Normal usage within quota';
    RAISE NOTICE '2. Voice overages (exceeds Voice Pack limit)';
    RAISE NOTICE '3. Data overages (exceeds Data Pack limit)';
    RAISE NOTICE '4. Roaming CDRs (vplmn field set)';
    RAISE NOTICE '5. SMS overages (exceeds SMS Pack limit)';
    RAISE NOTICE '6. Combined roaming + overage scenarios';
    RAISE NOTICE '';
    RAISE NOTICE 'Check bills for:';
    RAISE NOTICE '- overage_charge (domestic overage)';
    RAISE NOTICE '- roaming_charge (international usage)';
    RAISE NOTICE '';
    RAISE NOTICE 'Data ready for CSV export via CDR Exporter';
    RAISE NOTICE '============================================';
END $$;