-- Test queries for combined week report functions
-- Run these queries to verify the functions work correctly
--
-- Note: Replace the example parameters with actual values from your database

-- ============================================
-- Test 1: get_combined_week_report_by_store
-- ============================================

-- First, find some valid test data
SELECT 
    'Finding test data for get_combined_week_report_by_store' AS test_name,
    store_code,
    customer_id,
    week_start_date::date
FROM task
WHERE week_start_date IS NOT NULL
    AND store_code IS NOT NULL
    AND customer_id IS NOT NULL
ORDER BY week_start_date DESC
LIMIT 5;

-- Test the function with sample parameters
-- Replace the values below with actual values from the query above
/*
SELECT * FROM get_combined_week_report_by_store(
    'STORE001',  -- p_store_code: Replace with actual store_code
    123,         -- p_customer_id: Replace with actual customer_id
    '2025-01-20' -- p_week_start_date: Replace with actual week_start_date
)
LIMIT 10;
*/

-- Verify function returns correct structure
SELECT 
    'Testing function structure' AS test_name,
    COUNT(*) AS row_count,
    COUNT(DISTINCT store_code) AS unique_stores,
    COUNT(DISTINCT customer_id) AS unique_customers,
    COUNT(DISTINCT week_start_date) AS unique_weeks
FROM get_combined_week_report_by_store(
    (SELECT store_code FROM task WHERE store_code IS NOT NULL LIMIT 1),
    (SELECT customer_id FROM task WHERE customer_id IS NOT NULL LIMIT 1),
    (SELECT week_start_date::date FROM task WHERE week_start_date IS NOT NULL ORDER BY week_start_date DESC LIMIT 1)
);

-- ============================================
-- Test 2: get_combined_week_report_by_region_channel
-- ============================================

-- First, find some valid test data (region, channel, customer_id, week_start_date)
SELECT 
    'Finding test data for get_combined_week_report_by_region_channel' AS test_name,
    s.region,
    s.channel,
    t.customer_id,
    t.week_start_date::date
FROM task t
INNER JOIN store s ON t.store_code = s.store_code
WHERE t.week_start_date IS NOT NULL
    AND t.store_code IS NOT NULL
    AND t.customer_id IS NOT NULL
    AND s.region IS NOT NULL
    AND s.channel IS NOT NULL
ORDER BY t.week_start_date DESC
LIMIT 5;

-- Test the function with sample parameters
-- Replace the values below with actual values from the query above
/*
SELECT * FROM get_combined_week_report_by_region_channel(
    'Region1',   -- p_region: Replace with actual region
    'Channel1',  -- p_channel: Replace with actual channel
    123,         -- p_customer_id: Replace with actual customer_id
    '2025-01-20' -- p_week_start_date: Replace with actual week_start_date
)
LIMIT 10;
*/

-- Verify function returns correct structure
SELECT 
    'Testing function structure' AS test_name,
    COUNT(*) AS row_count,
    COUNT(DISTINCT store_code) AS unique_stores,
    COUNT(DISTINCT customer_id) AS unique_customers,
    COUNT(DISTINCT week_start_date) AS unique_weeks,
    COUNT(DISTINCT channel) AS unique_channels
FROM get_combined_week_report_by_region_channel(
    (SELECT region FROM store WHERE region IS NOT NULL LIMIT 1),
    (SELECT channel FROM store WHERE channel IS NOT NULL LIMIT 1),
    (SELECT customer_id FROM task WHERE customer_id IS NOT NULL LIMIT 1),
    (SELECT week_start_date::date FROM task WHERE week_start_date IS NOT NULL ORDER BY week_start_date DESC LIMIT 1)
);

-- ============================================
-- Test 3: Compare results with base query
-- ============================================

-- Verify that function results match expected structure
-- This query checks that all columns are present and have correct types
SELECT 
    'Column structure check' AS test_name,
    column_name,
    data_type
FROM information_schema.columns
WHERE table_name = 'combined_week_report'
ORDER BY ordinal_position;

-- Compare row counts (if base table exists)
-- Note: This assumes combined_week_report table exists
/*
SELECT 
    'Row count comparison' AS test_name,
    (SELECT COUNT(*) FROM combined_week_report) AS base_table_count,
    (SELECT COUNT(*) FROM get_combined_week_report_by_store(
        (SELECT store_code FROM task LIMIT 1),
        (SELECT customer_id FROM task LIMIT 1),
        (SELECT week_start_date::date FROM task ORDER BY week_start_date DESC LIMIT 1)
    )) AS function_count;
*/

-- ============================================
-- Test 4: Edge cases
-- ============================================

-- Test with NULL parameters (should handle gracefully or return empty)
-- Note: These may return empty results or errors depending on function design
/*
-- Test NULL store_code
SELECT COUNT(*) FROM get_combined_week_report_by_store(NULL, 123, '2025-01-20');

-- Test NULL customer_id  
SELECT COUNT(*) FROM get_combined_week_report_by_store('STORE001', NULL, '2025-01-20');

-- Test NULL week_start_date
SELECT COUNT(*) FROM get_combined_week_report_by_store('STORE001', 123, NULL);

-- Test with non-existent values (should return empty)
SELECT COUNT(*) FROM get_combined_week_report_by_store('NONEXISTENT', 999999, '2099-01-01');
*/

