-- ============================================
-- Product Customer Type Verification Queries
-- ============================================

-- 1. Check product distribution by customer_type
SELECT 
    customer_type,
    COUNT(*) as product_count,
    GROUP_CONCAT(code ORDER BY code SEPARATOR ', ') as product_codes
FROM product_packages
WHERE is_active = 1
GROUP BY customer_type
ORDER BY customer_type;

-- 2. List all active products with their customer types
SELECT 
    id,
    code,
    name,
    customer_type,
    package_type,
    monthly_price,
    is_active
FROM product_packages
WHERE is_active = 1
ORDER BY customer_type, code;

-- 3. Verify Business-only products
SELECT 
    code,
    name,
    customer_type,
    package_type,
    monthly_price
FROM product_packages
WHERE is_active = 1
  AND customer_type = 'business'
ORDER BY code;

-- Expected results:
-- SHARED_BROADBAND, DEDICATED_BURST, DIA

-- 4. Verify Wholesale-only products
SELECT 
    code,
    name,
    customer_type,
    package_type,
    monthly_price
FROM product_packages
WHERE is_active = 1
  AND customer_type = 'wholesale'
ORDER BY code;

-- Expected results:
-- DARK_FIBRE, IPLC

-- 5. Verify products available to BOTH segments
SELECT 
    code,
    name,
    customer_type,
    package_type,
    monthly_price
FROM product_packages
WHERE is_active = 1
  AND customer_type = 'both'
ORDER BY code;

-- Expected results:
-- MAGELLAN, IP_TRANSIT, INTERCITY_VPN

-- 6. Simulate Business customer view (company)
-- Products with customer_type = 'business' OR 'both'
SELECT 
    code,
    name,
    customer_type,
    monthly_price
FROM product_packages
WHERE is_active = 1
  AND (customer_type = 'business' OR customer_type = 'both')
ORDER BY customer_type, code;

-- Expected: SHARED_BROADBAND, DEDICATED_BURST, DIA, MAGELLAN, IP_TRANSIT, INTERCITY_VPN

-- 7. Simulate Wholesale customer view (individual)
-- Products with customer_type = 'wholesale' OR 'both'
SELECT 
    code,
    name,
    customer_type,
    monthly_price
FROM product_packages
WHERE is_active = 1
  AND (customer_type = 'wholesale' OR customer_type = 'both')
ORDER BY customer_type, code;

-- Expected: DARK_FIBRE, IPLC, MAGELLAN, IP_TRANSIT, INTERCITY_VPN

-- 8. Check for products with incorrect customer_type values
SELECT 
    code,
    name,
    customer_type
FROM product_packages
WHERE customer_type NOT IN ('business', 'wholesale', 'both');

-- Should return 0 rows

-- 9. Find products missing customer_type assignment
SELECT 
    code,
    name,
    customer_type
FROM product_packages
WHERE customer_type IS NULL;

-- Should return 0 rows

-- 10. Count products by customer_type and package_type
SELECT 
    customer_type,
    package_type,
    COUNT(*) as count
FROM product_packages
WHERE is_active = 1
GROUP BY customer_type, package_type
ORDER BY customer_type, package_type;

-- 11. Verify specific product codes have correct customer_type
SELECT 
    code,
    name,
    customer_type,
    CASE 
        WHEN code IN ('SHARED_BROADBAND', 'DEDICATED_BURST', 'DIA') 
            AND customer_type = 'business' THEN '✓ Correct'
        WHEN code IN ('DARK_FIBRE', 'IPLC') 
            AND customer_type = 'wholesale' THEN '✓ Correct'
        WHEN code IN ('MAGELLAN', 'IP_TRANSIT', 'INTERCITY_VPN') 
            AND customer_type = 'both' THEN '✓ Correct'
        ELSE '✗ Incorrect'
    END as validation_status
FROM product_packages
WHERE code IN (
    'SHARED_BROADBAND', 'DEDICATED_BURST', 'DIA',
    'DARK_FIBRE', 'IPLC',
    'MAGELLAN', 'IP_TRANSIT', 'INTERCITY_VPN'
);

-- All rows should show '✓ Correct'

-- 12. Check for duplicate product codes
SELECT 
    code,
    COUNT(*) as count
FROM product_packages
GROUP BY code
HAVING COUNT(*) > 1;

-- Should return 0 rows

-- 13. List inactive products (for audit)
SELECT 
    code,
    name,
    customer_type,
    is_active,
    updated_at
FROM product_packages
WHERE is_active = 0
ORDER BY updated_at DESC;

-- 14. Full product audit report
SELECT 
    id,
    code,
    name,
    customer_type,
    package_type,
    service_category,
    monthly_price,
    requires_feasibility_check,
    is_active,
    created_at,
    updated_at
FROM product_packages
ORDER BY customer_type, code;

-- 15. Check products by feasibility requirements
SELECT 
    customer_type,
    requires_feasibility_check,
    COUNT(*) as count,
    GROUP_CONCAT(code ORDER BY code SEPARATOR ', ') as products
FROM product_packages
WHERE is_active = 1
GROUP BY customer_type, requires_feasibility_check
ORDER BY customer_type, requires_feasibility_check;

-- ============================================
-- Update Queries (if needed)
-- ============================================

-- Update specific product to business
-- UPDATE product_packages SET customer_type = 'business' WHERE code = 'PRODUCT_CODE';

-- Update specific product to wholesale
-- UPDATE product_packages SET customer_type = 'wholesale' WHERE code = 'PRODUCT_CODE';

-- Update specific product to both
-- UPDATE product_packages SET customer_type = 'both' WHERE code = 'PRODUCT_CODE';

-- Bulk update business products
-- UPDATE product_packages 
-- SET customer_type = 'business' 
-- WHERE code IN ('SHARED_BROADBAND', 'DEDICATED_BURST', 'DIA');

-- Bulk update wholesale products
-- UPDATE product_packages 
-- SET customer_type = 'wholesale' 
-- WHERE code IN ('DARK_FIBRE', 'IPLC');

-- Bulk update both products
-- UPDATE product_packages 
-- SET customer_type = 'both' 
-- WHERE code IN ('MAGELLAN', 'IP_TRANSIT', 'INTERCITY_VPN');
