-- =============================================================
-- 02_data_quality_checks.sql
-- AWS Consumer Complaint Intelligence Dashboard
-- North Carolina Credit Card Fees & Interest Analysis
--
-- PURPOSE: Validate data quality after loading into Athena.
--          Run these checks before any KPI queries.
-- =============================================================

-- -------------------------------------------------------
-- CHECK 1: Total row count
-- Expected: 542 rows (matching the source CSV)
-- -------------------------------------------------------
SELECT COUNT(*) AS total_rows
FROM consumer_complaints.nc_credit_card_complaints;

-- -------------------------------------------------------
-- CHECK 2: Null / empty rates for key columns
-- -------------------------------------------------------
SELECT
    COUNT(*)                                                          AS total_rows,
    SUM(CASE WHEN date_received IS NULL OR date_received = '' THEN 1 ELSE 0 END)
                                                                      AS null_date_received,
    SUM(CASE WHEN company IS NULL OR company = '' THEN 1 ELSE 0 END) AS null_company,
    SUM(CASE WHEN sub_issue IS NULL OR sub_issue = '' THEN 1 ELSE 0 END)
                                                                      AS null_sub_issue,
    SUM(CASE WHEN company_response IS NULL OR company_response = '' THEN 1 ELSE 0 END)
                                                                      AS null_company_response,
    SUM(CASE WHEN timely_response IS NULL OR timely_response = '' THEN 1 ELSE 0 END)
                                                                      AS null_timely_response,
    SUM(CASE WHEN consumer_narrative IS NULL OR consumer_narrative = '' THEN 1 ELSE 0 END)
                                                                      AS no_narrative,
    ROUND(
        SUM(CASE WHEN consumer_narrative IS NULL OR consumer_narrative = '' THEN 1 ELSE 0 END)
        * 100.0 / COUNT(*), 1
    )                                                                 AS no_narrative_pct
FROM consumer_complaints.nc_credit_card_complaints;

-- -------------------------------------------------------
-- CHECK 3: Distinct values for categorical columns
-- -------------------------------------------------------
SELECT 'sub_issue'        AS column_name, COUNT(DISTINCT sub_issue)        AS distinct_values FROM consumer_complaints.nc_credit_card_complaints
UNION ALL
SELECT 'company_response' AS column_name, COUNT(DISTINCT company_response) AS distinct_values FROM consumer_complaints.nc_credit_card_complaints
UNION ALL
SELECT 'timely_response'  AS column_name, COUNT(DISTINCT timely_response)  AS distinct_values FROM consumer_complaints.nc_credit_card_complaints
UNION ALL
SELECT 'submitted_via'    AS column_name, COUNT(DISTINCT submitted_via)    AS distinct_values FROM consumer_complaints.nc_credit_card_complaints
UNION ALL
SELECT 'state'            AS column_name, COUNT(DISTINCT state)            AS distinct_values FROM consumer_complaints.nc_credit_card_complaints;

-- -------------------------------------------------------
-- CHECK 4: Confirm all records are from North Carolina
-- -------------------------------------------------------
SELECT state, COUNT(*) AS count
FROM consumer_complaints.nc_credit_card_complaints
GROUP BY state
ORDER BY count DESC;

-- -------------------------------------------------------
-- CHECK 5: Date range validation
-- -------------------------------------------------------
SELECT
    MIN(date_parse(date_received, '%m/%d/%y')) AS earliest_complaint,
    MAX(date_parse(date_received, '%m/%d/%y')) AS latest_complaint
FROM consumer_complaints.nc_credit_card_complaints
WHERE date_received IS NOT NULL AND date_received != '';

-- -------------------------------------------------------
-- CHECK 6: ZIP code masking analysis
-- -------------------------------------------------------
SELECT
    COUNT(*)                                                                    AS total_rows,
    SUM(CASE WHEN zip_code = 'XXXXX' THEN 1 ELSE 0 END)                        AS fully_masked_zips,
    SUM(CASE WHEN zip_code LIKE '%X%' AND zip_code != 'XXXXX' THEN 1 ELSE 0 END) AS partially_masked_zips,
    SUM(CASE WHEN zip_code NOT LIKE '%X%' AND zip_code IS NOT NULL THEN 1 ELSE 0 END) AS clean_zips
FROM consumer_complaints.nc_credit_card_complaints;
