Write SQL queries to validate data integrity between two tables.

-- Find order_items with no matching order (orphans)
SELECT oi.*
FROM order_items oi
LEFT JOIN orders o ON oi.order_id = o.id
WHERE o.id IS NULL;
-- Expected: 0 rows (no orphan items)

-- Find orders with no matching customer
SELECT o.id, o.customer_id, o.total_amount
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
WHERE c.id IS NULL;
-- Expected: 0 rows

-- Find employees assigned to non-existent departments
SELECT e.id, e.name, e.dept_id
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
WHERE d.id IS NULL AND e.dept_id IS NOT NULL;
-- Expected: 0 rows

-- After data migration: source vs target count
SELECT
    (SELECT COUNT(*) FROM source_customers) AS source_count,
    (SELECT COUNT(*) FROM target_customers) AS target_count,
    (SELECT COUNT(*) FROM source_customers) -
    (SELECT COUNT(*) FROM target_customers) AS difference;
-- Expected: difference = 0

-- Per-status count comparison
SELECT
    status,
    COUNT(*) AS source_count
FROM source_orders
GROUP BY status

EXCEPT   -- or MINUS in Oracle

SELECT
    status,
    COUNT(*) AS target_count
FROM target_orders
GROUP BY status;
-- Expected: 0 rows (both have same status distribution)

-- Rows in source but NOT in target after migration
SELECT id, email FROM source_users
WHERE id NOT IN (SELECT id FROM target_users);

-- Or using EXCEPT (more efficient)
SELECT id, email FROM source_users
EXCEPT
SELECT id, email FROM target_users;
-- Expected: 0 rows

-- Records in target but not in source (phantom records)
SELECT id FROM target_users
EXCEPT
SELECT id FROM source_users;
-- Expected: 0 rows

-- Check users table matches between source and target
SELECT s.id, s.email AS source_email, t.email AS target_email,
       s.name AS source_name, t.name AS target_name
FROM source_users s
JOIN target_users t ON s.id = t.id
WHERE s.email != t.email
   OR s.name  != t.name
   OR COALESCE(s.phone, '') != COALESCE(t.phone, '');
-- Expected: 0 rows (no mismatches)

-- Verify order total = sum of line items
SELECT o.id, o.total_amount,
       SUM(oi.quantity * oi.unit_price) AS calculated_total,
       o.total_amount - SUM(oi.quantity * oi.unit_price) AS discrepancy
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, o.total_amount
HAVING ABS(o.total_amount - SUM(oi.quantity * oi.unit_price)) > 0.01;
-- Expected: 0 rows (no calculation errors)

-- Verify payment amounts match order amounts
SELECT o.id, o.total_amount, p.amount AS paid_amount
FROM orders o
JOIN payments p ON p.order_id = o.id
WHERE o.total_amount != p.amount
  AND o.status = 'paid';
-- Expected: 0 rows

-- Ensure no duplicate primary keys (shouldn't happen but good to check)
SELECT id, COUNT(*) FROM users GROUP BY id HAVING COUNT(*) > 1;

-- Ensure email is unique
SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
-- Expected: 0 rows

-- Ensure unique order numbers
SELECT order_number, COUNT(*)
FROM orders
GROUP BY order_number
HAVING COUNT(*) > 1;

-- Find orders where created_at > updated_at (impossible)
SELECT id, created_at, updated_at
FROM orders
WHERE updated_at < created_at;
-- Expected: 0 rows

-- Find cancelled orders that still have active payments
SELECT o.id AS order_id, p.id AS payment_id
FROM orders o
JOIN payments p ON p.order_id = o.id
WHERE o.status = 'cancelled'
  AND p.status = 'active';
-- Expected: 0 rows (business rule violation)