SELECT 
    'customers' as table_name, COUNT(*) as row_count FROM customers
UNION ALL
SELECT 'vendors', COUNT(*) FROM vendors
UNION ALL
SELECT 'items', COUNT(*) FROM items
UNION ALL
SELECT 'sales_orders', COUNT(*) FROM sales_orders
UNION ALL
SELECT 'purchase_orders', COUNT(*) FROM purchase_orders
UNION ALL
SELECT 'gl_journal_entries', COUNT(*) FROM gl_journal_entries
UNION ALL
SELECT 'employees', COUNT(*) FROM employees
ORDER BY row_count DESC;

SELECT 
    COUNT(*) as total_customers,
    SUM(CASE WHEN email IS NULL OR email = '' THEN 1 ELSE 0 END) as missing_email,
    SUM(CASE WHEN phone IS NULL OR phone = '' THEN 1 ELSE 0 END) as missing_phone,
    SUM(CASE WHEN address_line1 IS NULL THEN 1 ELSE 0 END) as missing_address,
    SUM(CASE WHEN country IS NULL OR country = '' THEN 1 ELSE 0 END) as missing_country,
    SUM(CASE WHEN status NOT IN ('ACTIVE', 'INACTIVE', 'PROSPECT') THEN 1 ELSE 0 END) as invalid_status
FROM customers;

SELECT 
    MIN(order_date) as earliest_order,
    MAX(order_date) as latest_order,
    COUNT(*) as total_orders,
    COUNT(DISTINCT customer_id) as unique_customers,
    COUNT(DISTINCT YEAR(order_date)) as years_of_data
FROM sales_orders;

SELECT 
    email,
    COUNT(*) as duplicate_count,
    GROUP_CONCAT(customer_id) as customer_ids,
    GROUP_CONCAT(customer_name) as names
FROM customers
WHERE email IS NOT NULL AND email != ''
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC;

SELECT DISTINCT country, COUNT(*) as records
FROM customers
WHERE country NOT IN ('US', 'UK', 'CA', 'DE', 'FR', 'AU', 'JP')
GROUP BY country
ORDER BY records DESC;

SELECT customer_id, phone,
    REGEXP_REPLACE(phone, '[^0-9+]', '') as cleaned_phone
FROM customers
WHERE phone IS NOT NULL
  AND phone REGEXP '[^0-9+() -]';

-- Order lines without parent orders
SELECT ol.line_id, ol.order_id
FROM order_lines ol
LEFT JOIN sales_orders so ON ol.order_id = so.order_id
WHERE so.order_id IS NULL;

-- Invoices without customers
SELECT i.invoice_id, i.customer_id
FROM ar_invoices i
LEFT JOIN customers c ON i.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

SELECT 
    old.account_number as source_account,
    old.account_name as source_name,
    map.new_account_number as target_account,
    map.new_account_name as target_name,
    COALESCE(map.new_account_number, 'UNMAPPED') as mapping_status
FROM gl_accounts old
LEFT JOIN account_mapping map ON old.account_number = map.old_account_number
ORDER BY mapping_status DESC, old.account_number;

SELECT 
    c.customer_id as legacy_id,
    UPPER(TRIM(c.customer_name)) as customer_name,
    LOWER(TRIM(c.email)) as email,
    REGEXP_REPLACE(c.phone, '[^0-9]', '') as phone_clean,
    COALESCE(c.country, 'US') as country,
    CASE c.status
        WHEN 'A' THEN 'ACTIVE'
        WHEN 'I' THEN 'INACTIVE'
        WHEN 'P' THEN 'PROSPECT'
        ELSE 'UNKNOWN'
    END as status_mapped,
    c.created_date,
    CURRENT_TIMESTAMP as migration_date
FROM customers c
WHERE c.status != 'DELETED';

SELECT 
    'customers' as entity,
    (SELECT COUNT(*) FROM source_db.customers WHERE status != 'DELETED') as source_count,
    (SELECT COUNT(*) FROM target_db.customers) as target_count,
    (SELECT COUNT(*) FROM source_db.customers WHERE status != 'DELETED') - 
    (SELECT COUNT(*) FROM target_db.customers) as difference;

-- Source system totals
SELECT 
    'SOURCE' as system,
    SUM(CASE WHEN entry_type = 'DEBIT' THEN amount ELSE 0 END) as total_debits,
    SUM(CASE WHEN entry_type = 'CREDIT' THEN amount ELSE 0 END) as total_credits
FROM source_db.gl_journal_lines
UNION ALL
-- Target system totals
SELECT 
    'TARGET',
    SUM(CASE WHEN entry_type = 'DEBIT' THEN amount ELSE 0 END),
    SUM(CASE WHEN entry_type = 'CREDIT' THEN amount ELSE 0 END)
FROM target_db.gl_journal_lines;

-- Records that exist in source but not in target
SELECT s.customer_id, s.customer_name, s.email
FROM source_db.customers s
LEFT JOIN target_db.customers t ON s.customer_id = t.legacy_id
WHERE t.legacy_id IS NULL
  AND s.status != 'DELETED';