-- =============================================================
-- 06_semantic_views.sql
-- AWS Consumer Complaint Intelligence Dashboard
-- North Carolina Credit Card Fees & Interest Analysis
--
-- PURPOSE: Create semantic (dashboard-ready) views in Athena
--          that pre-aggregate data for QuickSight or BI tools.
--
-- These views act as a governed data layer — analysts query
-- the views rather than the raw table directly.
-- =============================================================

-- -------------------------------------------------------
-- VIEW 1: Complaint summary by company
-- Dashboard use: Company leaderboard, response rate table
-- -------------------------------------------------------
CREATE OR REPLACE VIEW consumer_complaints.vw_company_summary AS
SELECT
    company,
    COUNT(*)                                                                AS total_complaints,
    SUM(CASE WHEN LOWER(timely_response) = 'yes' THEN 1 ELSE 0 END)        AS timely_count,
    ROUND(
        SUM(CASE WHEN LOWER(timely_response) = 'yes' THEN 1 ELSE 0 END)
        * 100.0 / COUNT(*), 1
    )                                                                       AS timely_rate_pct,
    SUM(CASE WHEN company_response = 'Closed with monetary relief' THEN 1 ELSE 0 END)
                                                                            AS monetary_relief_count,
    ROUND(
        SUM(CASE WHEN company_response = 'Closed with monetary relief' THEN 1 ELSE 0 END)
        * 100.0 / COUNT(*), 1
    )                                                                       AS monetary_relief_pct,
    SUM(CASE WHEN company_response = 'Closed with explanation' THEN 1 ELSE 0 END)
                                                                            AS closed_explanation_count,
    SUM(CASE WHEN company_response = 'In progress' THEN 1 ELSE 0 END)      AS in_progress_count,
    SUM(CASE WHEN consumer_narrative IS NOT NULL AND consumer_narrative != '' THEN 1 ELSE 0 END)
                                                                            AS complaints_with_narrative
FROM consumer_complaints.nc_credit_card_complaints
GROUP BY company;

-- -------------------------------------------------------
-- VIEW 2: Monthly trend summary
-- Dashboard use: Time-series line chart, trend analysis
-- -------------------------------------------------------
CREATE OR REPLACE VIEW consumer_complaints.vw_monthly_trend AS
SELECT
    DATE_FORMAT(date_parse(date_received, '%m/%d/%y'), '%Y-%m')             AS year_month,
    YEAR(date_parse(date_received, '%m/%d/%y'))                             AS complaint_year,
    MONTH(date_parse(date_received, '%m/%d/%y'))                            AS complaint_month,
    COUNT(*)                                                                AS total_complaints,
    SUM(CASE WHEN sub_issue = 'Problem with fees' THEN 1 ELSE 0 END)       AS fee_complaints,
    SUM(CASE WHEN sub_issue = 'Charged too much interest' THEN 1 ELSE 0 END)
                                                                            AS interest_complaints,
    SUM(CASE WHEN sub_issue = 'Unexpected increase in interest rate' THEN 1 ELSE 0 END)
                                                                            AS rate_increase_complaints
FROM consumer_complaints.nc_credit_card_complaints
WHERE date_received IS NOT NULL AND date_received != ''
GROUP BY
    DATE_FORMAT(date_parse(date_received, '%m/%d/%y'), '%Y-%m'),
    YEAR(date_parse(date_received, '%m/%d/%y')),
    MONTH(date_parse(date_received, '%m/%d/%y'));

-- -------------------------------------------------------
-- VIEW 3: Response outcome summary
-- Dashboard use: Donut/pie chart, KPI cards
-- -------------------------------------------------------
CREATE OR REPLACE VIEW consumer_complaints.vw_response_summary AS
SELECT
    company_response                                                         AS response_outcome,
    COUNT(*)                                                                 AS complaint_count,
    ROUND(COUNT(*) * 100.0 / (
        SELECT COUNT(*) FROM consumer_complaints.nc_credit_card_complaints
    ), 1)                                                                    AS pct_of_total,
    SUM(CASE WHEN LOWER(timely_response) = 'yes' THEN 1 ELSE 0 END)         AS timely_count,
    ROUND(
        SUM(CASE WHEN LOWER(timely_response) = 'yes' THEN 1 ELSE 0 END)
        * 100.0 / COUNT(*), 1
    )                                                                        AS timely_rate_pct
FROM consumer_complaints.nc_credit_card_complaints
GROUP BY company_response;

-- -------------------------------------------------------
-- VIEW 4: Sub-issue detail view
-- Dashboard use: Sub-issue breakdown table, filter panel
-- -------------------------------------------------------
CREATE OR REPLACE VIEW consumer_complaints.vw_subissue_detail AS
SELECT
    sub_issue,
    company,
    DATE_FORMAT(date_parse(date_received, '%m/%d/%y'), '%Y-%m')             AS year_month,
    company_response,
    timely_response,
    CASE
        WHEN consumer_narrative IS NOT NULL AND consumer_narrative != '' THEN 'Yes'
        ELSE 'No'
    END                                                                      AS has_narrative,
    COUNT(*)                                                                 AS complaint_count
FROM consumer_complaints.nc_credit_card_complaints
WHERE date_received IS NOT NULL AND date_received != ''
GROUP BY
    sub_issue, company,
    DATE_FORMAT(date_parse(date_received, '%m/%d/%y'), '%Y-%m'),
    company_response, timely_response,
    CASE
        WHEN consumer_narrative IS NOT NULL AND consumer_narrative != '' THEN 'Yes'
        ELSE 'No'
    END;
