-- =============================================================
-- 01_create_athena_external_table.sql
-- AWS Consumer Complaint Intelligence Dashboard
-- North Carolina Credit Card Fees & Interest Analysis
--
-- PURPOSE: Create an Athena external table pointing to the raw
--          complaints CSV stored in Amazon S3.
--
-- BEFORE RUNNING:
--   Replace 'your-bucket-name' with your actual S3 bucket name.
--   Ensure the Glue Data Catalog database 'consumer_complaints'
--   has been created (or update the database name below).
--
-- ATHENA DIALECT: Presto / Trino (AWS Athena v3)
-- =============================================================

-- Step 1: Create the database if it does not exist
CREATE DATABASE IF NOT EXISTS consumer_complaints
COMMENT 'CFPB consumer complaint data for NC credit card analysis';

-- Step 2: Create the external table
-- REPLACE 'your-bucket-name' with your actual S3 bucket name below
CREATE EXTERNAL TABLE IF NOT EXISTS consumer_complaints.nc_credit_card_complaints (
    date_received        STRING,
    product              STRING,
    sub_product          STRING,
    issue                STRING,
    sub_issue            STRING,
    consumer_narrative   STRING,
    company_public_resp  STRING,
    company              STRING,
    state                STRING,
    zip_code             STRING,
    tags                 STRING,
    consumer_consent     STRING,
    submitted_via        STRING,
    date_sent_to_company STRING,
    company_response     STRING,
    timely_response      STRING,
    consumer_disputed    STRING,
    complaint_id         STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
    'separatorChar' = ',',
    'quoteChar'     = '"',
    'escapeChar'    = '\\'
)
STORED AS TEXTFILE
LOCATION 's3://your-bucket-name/consumer-complaints/raw/'
-- ^^^ REPLACE 'your-bucket-name' with your actual S3 bucket name
TBLPROPERTIES (
    'has_encrypted_data' = 'false',
    'skip.header.line.count' = '1',
    'classification' = 'csv'
);

-- Step 3: Verify the table was created
SELECT COUNT(*) AS total_rows
FROM consumer_complaints.nc_credit_card_complaints;
