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.