CREATE OR REPLACE VIEW v_customer_data_quality AS
SELECT 
    c.customer_id,
    c.customer_name,
    c.last_successful_sync_at,
    m.expected_row_count,
    (SELECT COUNT(*) FROM patient_records pr WHERE pr.customer_id = c.customer_id) AS actual_row_count,
    (SELECT COUNT(*) FROM patient_records pr WHERE pr.customer_id = c.customer_id) - m.expected_row_count AS drift
FROM customers c
JOIN customer_metadata m ON c.customer_id = m.customer_id;

-- Note for QA (Ravi): 
-- The 'drift' column is the single source of truth for "is data missing?".
-- If drift > 0, we are missing records.
-- If drift < 0, we have duplicates or deletions that weren't expected.
-- Do not add 'last_sync_at' to the WHERE clause of the count query; we need the current state, not a historical snapshot.