Compiled_code_for_Deliverable_1.sql

-- Drop schemas
DROP SCHEMA IF EXISTS staging CASCADE;
DROP SCHEMA IF EXISTS dw CASCADE;

-- 			ELT


-- Create schemas
CREATE SCHEMA IF NOT EXISTS staging;
CREATE SCHEMA IF NOT EXISTS dw;


-- Staging table: transactions (keep raw text columns)
DROP TABLE IF EXISTS staging.stg_transactions;
CREATE TABLE staging.stg_transactions (
  id              BIGINT,
  raw_date        TEXT,
  client_id       TEXT,
  card_id         TEXT,
  amount_raw      TEXT,
  use_chip_raw    TEXT,
  merchant_id     TEXT,
  merchant_city   TEXT,
  merchant_state  TEXT,
  merchant_zip    TEXT,
  mcc             TEXT,
  errors          TEXT
);

select * from staging.stg_transactions;
-- Staging table: users
DROP TABLE IF EXISTS staging.stg_users;
CREATE TABLE staging.stg_users (
  id                TEXT,
  current_age       TEXT,
  retirement_age    TEXT,
  birth_year        TEXT,
  birth_month       TEXT,
  gender            TEXT,
  address           TEXT,
  latitude          TEXT,
  longitude         TEXT,
  per_capita_income TEXT,
  yearly_income     TEXT,
  total_debt        TEXT,
  credit_score      TEXT,
  num_credit_cards  TEXT
);


--  Staging table: cards
DROP TABLE IF EXISTS staging.stg_cards;
CREATE TABLE staging.stg_cards (
  id                 TEXT,
  client_id          TEXT,
  card_brand         TEXT,
  card_type          TEXT,
  card_number_raw    TEXT,
  expires_raw        TEXT,
  cvv                TEXT,
  has_chip_raw       TEXT,
  num_cards_issued   TEXT,
  credit_limit_raw   TEXT,
  acct_open_date_raw TEXT,
  year_pin_last_changed TEXT,
  card_on_dark_web   TEXT
);
select * from staging.stg_cards;

--  Staging table: mcc codes 
DROP TABLE IF EXISTS staging.stg_mcc;
CREATE TABLE staging.stg_mcc (
  mcc_code TEXT PRIMARY KEY,
  description TEXT
);
select * from staging.stg_mcc;

--  Staging table: fraud labels 
DROP TABLE IF EXISTS staging.stg_fraud_labels;
CREATE TABLE staging.stg_fraud_labels (
  transaction_id BIGINT PRIMARY KEY,
  fraud_label TEXT
);
select COUNT(*) from staging.stg_fraud_labels;

-- dw dimensions


--  dim_date
DROP TABLE IF EXISTS dw.dim_date;
CREATE TABLE dw.dim_date (
  date_key BIGSERIAL PRIMARY KEY,
  full_date DATE UNIQUE,
  day INT,
  month INT,
  quarter INT,
  year INT,
  is_weekend BOOLEAN
);
select * from dw.dim_date



--  dim_customer
DROP TABLE IF EXISTS dw.dim_customer;
CREATE TABLE dw.dim_customer (
  customer_key BIGSERIAL PRIMARY KEY,
  customer_id TEXT UNIQUE,
  current_age INT,
  birth_year INT,
  birth_month INT,
  gender TEXT,
  address TEXT,
  latitude NUMERIC(9,6),
  longitude NUMERIC(9,6),
  per_capita_income NUMERIC(14,2),
  yearly_income NUMERIC(14,2),
  total_debt NUMERIC(18,2),
  credit_score INT,
  num_credit_cards INT
);

select * from  dw.dim_customer
--  dim_card
DROP TABLE IF EXISTS dw.dim_card;
CREATE TABLE dw.dim_card (
  card_key BIGSERIAL PRIMARY KEY,
  card_id TEXT UNIQUE,
  client_id TEXT,
  card_brand TEXT,
  card_type TEXT,
  card_number_masked TEXT,
  has_chip BOOLEAN,
  credit_limit NUMERIC(14,2),
  acct_open_date DATE,
  card_on_dark_web BOOLEAN
);

select * from dw.dim_card;

--  dim_merchant
DROP TABLE IF EXISTS dw.dim_merchant;
CREATE TABLE dw.dim_merchant (
  merchant_key BIGSERIAL PRIMARY KEY,
  merchant_id TEXT UNIQUE,
  merchant_city TEXT,
  merchant_state TEXT,
  merchant_zip TEXT,
  location TEXT
);

select * from dw.dim_merchant
--  dim_mcc
DROP TABLE IF EXISTS dw.dim_mcc;
CREATE TABLE dw.dim_mcc (
  mcc_key BIGSERIAL PRIMARY KEY,
  mcc_code TEXT UNIQUE,
  description TEXT
);
select * from dw.dim_mcc
-- dim_error
DROP TABLE IF EXISTS dw.dim_error CASCADE;
CREATE TABLE dw.dim_error (
    error_key BIGSERIAL PRIMARY KEY,
    error_desc TEXT UNIQUE
);
select * from  dw.dim_error
-- Fact Table

--  fact_transactions 
DROP TABLE IF EXISTS dw.fact_transactions;
CREATE TABLE dw.fact_transactions (
  fact_id BIGSERIAL PRIMARY KEY,
  transaction_id BIGINT UNIQUE,
  date_key BIGINT REFERENCES dw.dim_date(date_key),
  customer_key BIGINT REFERENCES dw.dim_customer(customer_key),
  card_key BIGINT REFERENCES dw.dim_card(card_key),
  merchant_key BIGINT REFERENCES dw.dim_merchant(merchant_key),
  mcc_key BIGINT REFERENCES dw.dim_mcc(mcc_key),
  amount NUMERIC(18,2),
  use_chip BOOLEAN,
  fraud_label BOOLEAN,
  anomaly_flag BOOLEAN DEFAULT FALSE,
  raw_amount TEXT,
  raw_date TEXT,
  error_key TEXT
);

select * from dw.fact_transactions

-- insert into dim date
INSERT INTO dw.dim_date (full_date, day, month, quarter, year, is_weekend)
SELECT DISTINCT
    TO_DATE(SPLIT_PART(raw_date, ' ', 1), 'YYYY-MM-DD') AS full_date,
    EXTRACT(DAY FROM TO_DATE(SPLIT_PART(raw_date, ' ', 1), 'YYYY-MM-DD'))::INT AS day,
    EXTRACT(MONTH FROM TO_DATE(SPLIT_PART(raw_date, ' ', 1), 'YYYY-MM-DD'))::INT AS month,
    EXTRACT(QUARTER FROM TO_DATE(SPLIT_PART(raw_date, ' ', 1), 'YYYY-MM-DD'))::INT AS quarter,
    EXTRACT(YEAR FROM TO_DATE(SPLIT_PART(raw_date, ' ', 1), 'YYYY-MM-DD'))::INT AS year,
    CASE
        WHEN EXTRACT(DOW FROM TO_DATE(SPLIT_PART(raw_date, ' ', 1), 'YYYY-MM-DD')) IN (0,6)
        THEN TRUE ELSE FALSE
    END AS is_weekend
FROM staging.stg_transactions
WHERE raw_date IS NOT NULL;


-- insert into dim customer
INSERT INTO dw.dim_customer (
    customer_id,
    current_age,
    birth_year,
    birth_month,
    gender,
    address,
    latitude,
    longitude,
    per_capita_income,
    yearly_income,
    total_debt,
    credit_score,
    num_credit_cards
)
SELECT DISTINCT
    u.id::TEXT AS customer_id,
    NULLIF(u.current_age, '')::INT AS current_age,
    NULLIF(u.birth_year, '')::INT AS birth_year,
    NULLIF(u.birth_month, '')::INT AS birth_month,
    INITCAP(TRIM(u.gender)) AS gender,
    TRIM(u.address) AS address,
    NULLIF(u.latitude, '')::NUMERIC(9,6) AS latitude,
    NULLIF(u.longitude, '')::NUMERIC(9,6) AS longitude,
    REPLACE(REPLACE(u.per_capita_income, '$', ''), ',', '')::NUMERIC(14,2) AS per_capita_income,
    REPLACE(REPLACE(u.yearly_income, '$', ''), ',', '')::NUMERIC(14,2) AS yearly_income,
    REPLACE(REPLACE(u.total_debt, '$', ''), ',', '')::NUMERIC(18,2) AS total_debt,
    NULLIF(u.credit_score, '')::INT AS credit_score,
    NULLIF(u.num_credit_cards, '')::INT AS num_credit_cards
FROM staging.stg_users u
WHERE u.id IS NOT NULL;


-- insert into dim card 
INSERT INTO dw.dim_card (
    card_id,
    client_id,
    card_brand,
    card_type,
    card_number_masked,
    has_chip,
    credit_limit,
    acct_open_date,
    card_on_dark_web
)
SELECT DISTINCT
    c.id::TEXT AS card_id,
    c.client_id::TEXT AS client_id,
    INITCAP(TRIM(c.card_brand)) AS card_brand,
    INITCAP(TRIM(c.card_type)) AS card_type,

    -- 🔒 Mask card number safely (remove non-digits first)
    CONCAT('XXXX-XXXX-XXXX-', RIGHT(LPAD(REGEXP_REPLACE(c.card_number_raw::TEXT, '\D', '', 'g'), 16, '0'), 4)) AS card_number_masked,

    CASE 
        WHEN UPPER(TRIM(c.has_chip_raw)) = 'YES' THEN TRUE
        ELSE FALSE
    END AS has_chip,
    
    REPLACE(REPLACE(c.credit_limit_raw, '$', ''), ',', '')::NUMERIC(14,2) AS credit_limit,
    
    CASE
        WHEN TRIM(c.acct_open_date_raw) ~ '^[A-Za-z]{3}-[0-9]{2}$' THEN TO_DATE(TRIM(c.acct_open_date_raw), 'Mon-YY')
        WHEN TRIM(c.acct_open_date_raw) ~ '^[A-Za-z]{3}-[0-9]{4}$' THEN TO_DATE(TRIM(c.acct_open_date_raw), 'Mon-YYYY')
        WHEN TRIM(c.acct_open_date_raw) ~ '^[0-9]{4}-[0-9]{2}-[0-9]{2}$' THEN TO_DATE(TRIM(c.acct_open_date_raw), 'YYYY-MM-DD')
        ELSE NULL
    END AS acct_open_date,
    
    CASE 
        WHEN UPPER(TRIM(c.card_on_dark_web)) = 'YES' THEN TRUE
        ELSE FALSE
    END AS card_on_dark_web

FROM staging.stg_cards c
WHERE c.id IS NOT NULL;



-- insert into dim merchant

TRUNCATE dw.fact_transactions, dw.dim_merchant RESTART IDENTITY CASCADE;

INSERT INTO dw.dim_merchant (
    merchant_id,
    merchant_city,
    merchant_state,
    merchant_zip,
    location
)
SELECT DISTINCT ON (clean_merchant_id)
    clean_merchant_id AS merchant_id,
    INITCAP(TRIM(t.merchant_city)) AS merchant_city,
    UPPER(TRIM(t.merchant_state)) AS merchant_state,
    TRIM(t.merchant_zip)::TEXT AS merchant_zip,
    CONCAT(
        INITCAP(TRIM(t.merchant_city)), ', ',
        UPPER(TRIM(t.merchant_state)), ' ',
        TRIM(t.merchant_zip)
    ) AS location
FROM (
    SELECT
        REGEXP_REPLACE(TRIM(merchant_id), '[^0-9A-Za-z]', '', 'g') AS clean_merchant_id,
        merchant_city,
        merchant_state,
        merchant_zip
    FROM staging.stg_transactions
    WHERE merchant_id IS NOT NULL AND TRIM(merchant_id) <> ''
) t
ORDER BY clean_merchant_id;


-- insert into dim mcc 

TRUNCATE dw.dim_mcc RESTART IDENTITY CASCADE;

INSERT INTO dw.dim_mcc (mcc_code, description)
SELECT DISTINCT
    TRIM(mcc_code)::TEXT AS mcc_code,
    INITCAP(TRIM(description)) AS description
FROM staging.stg_mcc
WHERE mcc_code IS NOT NULL
  AND TRIM(mcc_code) <> '';

-- insert into dim error
INSERT INTO dw.dim_error (error_desc)
SELECT DISTINCT
    CASE WHEN TRIM(errors) = '' OR errors IS NULL THEN 'NO_ERROR'
         ELSE INITCAP(TRIM(errors)) END
FROM staging.stg_transactions;


--  insert data into fact_transactions
TRUNCATE dw.fact_transactions RESTART IDENTITY CASCADE;

INSERT INTO dw.fact_transactions (
    transaction_id,
    date_key,
    customer_key,
    card_key,
    merchant_key,
    mcc_key,
    amount,
    use_chip,
    fraud_label,
    anomaly_flag,
    raw_amount,
    raw_date,
    error_key
)
SELECT
    t.id AS transaction_id,
    d.date_key,
    cu.customer_key,
    ca.card_key,
    m.merchant_key,
    mc.mcc_key,
    REPLACE(REPLACE(t.amount_raw, '$', ''), ',', '')::NUMERIC(18,2) AS amount,
    CASE 
        WHEN UPPER(TRIM(t.use_chip_raw)) IN ('YES', 'Y', 'TRUE', 'T', '1') THEN TRUE
        ELSE FALSE
    END AS use_chip,
    CASE 
        WHEN LOWER(TRIM(f.fraud_label)) IN ('yes','y','true','fraud','1') THEN TRUE
        ELSE FALSE
    END AS fraud_label,
    FALSE AS anomaly_flag,
    t.amount_raw,
    t.raw_date,
    e.error_key

FROM staging.stg_transactions t
LEFT JOIN dw.dim_date d 
  ON TO_DATE(SPLIT_PART(t.raw_date, ' ', 1), 'YYYY-MM-DD') = d.full_date
LEFT JOIN dw.dim_customer cu 
  ON t.client_id::TEXT = cu.customer_id
LEFT JOIN dw.dim_card ca 
  ON t.card_id::TEXT = ca.card_id
LEFT JOIN dw.dim_merchant m 
  ON TRIM(t.merchant_id)::TEXT = m.merchant_id
LEFT JOIN dw.dim_mcc mc 
  ON TRIM(t.mcc) = mc.mcc_code
LEFT JOIN staging.stg_fraud_labels f
  ON t.id = f.transaction_id
LEFT JOIN dw.dim_error e  
  ON (
        CASE 
            WHEN TRIM(t.errors) = '' OR t.errors IS NULL THEN 'NO_ERROR'
            ELSE INITCAP(TRIM(t.errors))
        END
     ) = e.error_desc
WHERE t.id IS NOT NULL;


select * from dw.fact_transactions
--				ETL


--  create schemas for ETL
CREATE SCHEMA IF NOT EXISTS etl;
CREATE SCHEMA IF NOT EXISTS dw_etl;

-- create cleaned tables
DROP TABLE IF EXISTS etl.transactions_clean;
CREATE TABLE etl.transactions_clean (
  transaction_id BIGINT PRIMARY KEY,
  transaction_date DATE,
  client_id TEXT,
  card_id TEXT,
  amount NUMERIC(18,2),
  use_chip BOOLEAN,
  merchant_id TEXT,
  merchant_city TEXT,
  merchant_state TEXT,
  merchant_zip TEXT,
  mcc_code TEXT,
  errors TEXT
);

select * from  etl.transactions_clean
DROP TABLE IF EXISTS etl.users_clean;
CREATE TABLE etl.users_clean (
  customer_id TEXT PRIMARY KEY,
  current_age INT,
  retirement_age INT,
  birth_year INT,
  birth_month INT,
  gender TEXT,
  address TEXT,
  latitude NUMERIC(9,6),
  longitude NUMERIC(9,6),
  per_capita_income NUMERIC(14,2),
  yearly_income NUMERIC(14,2),
  total_debt NUMERIC(18,2),
  credit_score INT,
  num_credit_cards INT
);

select * from etl.users_clean
DROP TABLE IF EXISTS etl.cards_clean;
CREATE TABLE etl.cards_clean (
  card_id TEXT PRIMARY KEY,
  client_id TEXT,
  card_brand TEXT,
  card_type TEXT,
  card_number_masked TEXT,
  has_chip BOOLEAN,
  credit_limit NUMERIC(14,2),
  acct_open_date DATE,
  card_on_dark_web BOOLEAN
);

select * from etl.cards_clean
DROP TABLE IF EXISTS etl.mcc_clean;
CREATE TABLE etl.mcc_clean (
  mcc_code TEXT PRIMARY KEY,
  description TEXT
);
select * from etl.mcc_clean
DROP TABLE IF EXISTS etl.fraud_clean;
CREATE TABLE etl.fraud_clean (
  transaction_id BIGINT PRIMARY KEY,
  fraud_label TEXT
);
select * from etl.fraud_clean
--  create dw_etl star schema (same structure as dw)

DROP TABLE IF EXISTS dw_etl.dim_date CASCADE;
CREATE TABLE dw_etl.dim_date (
  date_key BIGSERIAL PRIMARY KEY,
  full_date DATE UNIQUE,
  day INT,
  month INT,
  quarter INT,
  year INT,
  is_weekend BOOLEAN
);
select * from dw_etl.dim_date 
DROP TABLE IF EXISTS dw_etl.dim_customer CASCADE;
CREATE TABLE dw_etl.dim_customer (
  customer_key BIGSERIAL PRIMARY KEY,
  customer_id TEXT UNIQUE,
  current_age INT,
  birth_year INT,
  birth_month INT,
  gender TEXT,
  address TEXT,
  latitude NUMERIC(9,6),
  longitude NUMERIC(9,6),
  per_capita_income NUMERIC(14,2),
  yearly_income NUMERIC(14,2),
  total_debt NUMERIC(18,2),
  credit_score INT,
  num_credit_cards INT
);
select * from dw_etl.dim_customer
DROP TABLE IF EXISTS dw_etl.dim_card CASCADE;
CREATE TABLE dw_etl.dim_card (
  card_key BIGSERIAL PRIMARY KEY,
  card_id TEXT UNIQUE,
  client_id TEXT,
  card_brand TEXT,
  card_type TEXT,
  card_number_masked TEXT,
  has_chip BOOLEAN,
  credit_limit NUMERIC(14,2),
  acct_open_date DATE,
  card_on_dark_web BOOLEAN
);
select * from dw_etl.dim_card
DROP TABLE IF EXISTS dw_etl.dim_merchant CASCADE;
CREATE TABLE dw_etl.dim_merchant (
  merchant_key BIGSERIAL PRIMARY KEY,
  merchant_id TEXT UNIQUE,
  merchant_city TEXT,
  merchant_state TEXT,
  merchant_zip TEXT,
  location TEXT
);
select * from dw_etl.dim_merchant
DROP TABLE IF EXISTS dw_etl.dim_mcc CASCADE;
CREATE TABLE dw_etl.dim_mcc (
  mcc_key BIGSERIAL PRIMARY KEY,
  mcc_code TEXT UNIQUE,
  description TEXT
);
select * from dw_etl.dim_mcc
DROP TABLE IF EXISTS etl.errors_clean;
CREATE TABLE etl.errors_clean (
    error_desc TEXT PRIMARY KEY
);
select * from etl.errors_clean

DROP TABLE IF EXISTS dw_etl.dim_error CASCADE;
CREATE TABLE dw_etl.dim_error (
    error_key BIGSERIAL PRIMARY KEY,
    error_desc TEXT UNIQUE
);
select * from dw_etl.dim_error

-- Fact Table

DROP TABLE IF EXISTS dw_etl.fact_transactions CASCADE;
CREATE TABLE dw_etl.fact_transactions (
  fact_id BIGSERIAL PRIMARY KEY,
  transaction_id BIGINT UNIQUE,
  date_key BIGINT REFERENCES dw_etl.dim_date(date_key),
  customer_key BIGINT REFERENCES dw_etl.dim_customer(customer_key),
  card_key BIGINT REFERENCES dw_etl.dim_card(card_key),
  merchant_key BIGINT REFERENCES dw_etl.dim_merchant(merchant_key),
  mcc_key BIGINT REFERENCES dw_etl.dim_mcc(mcc_key),
  amount NUMERIC(18,2),
  use_chip BOOLEAN,
  fraud_label BOOLEAN,
  anomaly_flag BOOLEAN DEFAULT FALSE,
  raw_amount TEXT,
  raw_date TEXT
);

select * from dw_etl.fact_transactions;
-- Transformed Data into tables

-- users 
TRUNCATE etl.users_clean;

INSERT INTO etl.users_clean
SELECT DISTINCT
  u.id::TEXT AS customer_id,
  NULLIF(u.current_age,'')::INT,
  NULLIF(u.retirement_age,'')::INT,
  NULLIF(u.birth_year,'')::INT,
  NULLIF(u.birth_month,'')::INT,
  INITCAP(TRIM(u.gender)) AS gender,
  TRIM(u.address),
  NULLIF(u.latitude,'')::NUMERIC(9,6),
  NULLIF(u.longitude,'')::NUMERIC(9,6),
  NULLIF(REGEXP_REPLACE(u.per_capita_income, '[^\d\.]', '', 'g'), '')::NUMERIC(14,2),
  NULLIF(REGEXP_REPLACE(u.yearly_income, '[^\d\.]', '', 'g'), '')::NUMERIC(14,2),
  NULLIF(REGEXP_REPLACE(u.total_debt, '[^\d\.]', '', 'g'), '')::NUMERIC(18,2),
  NULLIF(u.credit_score,'')::INT,
  NULLIF(u.num_credit_cards,'')::INT
FROM staging.stg_users u
WHERE u.id IS NOT NULL;

-- cards
TRUNCATE etl.cards_clean;

INSERT INTO etl.cards_clean
SELECT DISTINCT
  c.id::TEXT AS card_id,
  c.client_id::TEXT,
  INITCAP(TRIM(c.card_brand)) AS card_brand,
  INITCAP(TRIM(c.card_type)) AS card_type,
  CONCAT('XXXX-XXXX-XXXX-', RIGHT(LPAD(REGEXP_REPLACE(c.card_number_raw::TEXT, '\D', '', 'g'), 16, '0'),4)) AS card_number_masked,
  CASE WHEN UPPER(TRIM(c.has_chip_raw)) = 'YES' THEN TRUE ELSE FALSE END AS has_chip,
  NULLIF(REGEXP_REPLACE(c.credit_limit_raw, '[^\d\.]', '', 'g'), '')::NUMERIC(14,2) AS credit_limit,
  CASE
    WHEN TRIM(c.acct_open_date_raw) ~ '^[A-Za-z]{3}-[0-9]{2}$' THEN TO_DATE(TRIM(c.acct_open_date_raw), 'Mon-YY')
    WHEN TRIM(c.acct_open_date_raw) ~ '^[A-Za-z]{3}-[0-9]{4}$' THEN TO_DATE(TRIM(c.acct_open_date_raw), 'Mon-YYYY')
    WHEN TRIM(c.acct_open_date_raw) ~ '^[0-9]{4}-[0-9]{2}-[0-9]{2}$' THEN TO_DATE(TRIM(c.acct_open_date_raw), 'YYYY-MM-DD')
    ELSE NULL
  END AS acct_open_date,
  CASE WHEN UPPER(TRIM(c.card_on_dark_web)) = 'YES' THEN TRUE ELSE FALSE END AS card_on_dark_web
FROM staging.stg_cards c
WHERE c.id IS NOT NULL;

-- mcc 

TRUNCATE etl.mcc_clean;

INSERT INTO etl.mcc_clean (mcc_code, description)
SELECT DISTINCT TRIM(mcc_code)::TEXT, INITCAP(TRIM(description))
FROM staging.stg_mcc
WHERE mcc_code IS NOT NULL AND TRIM(mcc_code) <> '';


-- fraud
TRUNCATE etl.fraud_clean;

INSERT INTO etl.fraud_clean (transaction_id, fraud_label)
SELECT transaction_id, TRIM(fraud_label) FROM staging.stg_fraud_labels;

select * from  etl.fraud_clean
-- transactions
TRUNCATE etl.transactions_clean;

INSERT INTO etl.transactions_clean (
  transaction_id, transaction_date, client_id, card_id, amount,
  use_chip, merchant_id, merchant_city, merchant_state, merchant_zip, mcc_code, errors
)
SELECT
  t.id::BIGINT AS transaction_id,
  -- parse date: if raw contains timestamp or already ISO
  CASE
    WHEN t.raw_date ~ '^\d{4}-\d{2}-\d{2}' THEN TO_DATE(SPLIT_PART(t.raw_date,' ',1), 'YYYY-MM-DD')
    WHEN t.raw_date ~ '^\d{1,2}/\d{1,2}/\d{4}' THEN TO_DATE(SPLIT_PART(t.raw_date,' ',1), 'MM/DD/YYYY')
    ELSE NULL
  END AS transaction_date,
  t.client_id::TEXT,
  t.card_id::TEXT,
  NULLIF(REGEXP_REPLACE(t.amount_raw, '[^\d\.\-()]', '', 'g'), '')::numeric(18,2) * 
    CASE WHEN t.amount_raw ~ '^\(' THEN -1 ELSE 1 END AS amount,
  CASE WHEN UPPER(TRIM(t.use_chip_raw)) LIKE '%CHIP%' THEN TRUE
       WHEN UPPER(TRIM(t.use_chip_raw)) LIKE '%SWIPE%' THEN FALSE
       ELSE NULL END AS use_chip,
  REGEXP_REPLACE(TRIM(t.merchant_id), '[^0-9A-Za-z]', '', 'g') AS merchant_id,
  INITCAP(TRIM(t.merchant_city)) AS merchant_city,
  UPPER(TRIM(t.merchant_state)) AS merchant_state,
  TRIM(t.merchant_zip) AS merchant_zip,
  TRIM(t.mcc) AS mcc_code,
  t.errors
FROM staging.stg_transactions t
WHERE t.id IS NOT NULL;


-- Load data into dw_etl dimensions 

-- dim date
TRUNCATE dw_etl.dim_date RESTART IDENTITY CASCADE;

INSERT INTO dw_etl.dim_date (full_date, day, month, quarter, year, is_weekend)
SELECT DISTINCT
  transaction_date::date,
  EXTRACT(DAY FROM transaction_date)::INT,
  EXTRACT(MONTH FROM transaction_date)::INT,
  EXTRACT(QUARTER FROM transaction_date)::INT,
  EXTRACT(YEAR FROM transaction_date)::INT,
  CASE WHEN EXTRACT(ISODOW FROM transaction_date) IN (6,7) THEN TRUE ELSE FALSE END
FROM etl.transactions_clean
WHERE transaction_date IS NOT NULL;




-- dim customer
TRUNCATE dw_etl.dim_customer RESTART IDENTITY CASCADE;

INSERT INTO dw_etl.dim_customer (
  customer_id, current_age, birth_year, birth_month,
  gender, address, latitude, longitude, per_capita_income, yearly_income,
  total_debt, credit_score, num_credit_cards
)
SELECT customer_id, current_age,birth_year, birth_month,
  gender, address, latitude, longitude, per_capita_income, yearly_income,
  total_debt, credit_score, num_credit_cards
FROM etl.users_clean;



-- dim card

TRUNCATE dw_etl.dim_card RESTART IDENTITY CASCADE;

INSERT INTO dw_etl.dim_card (
  card_id, client_id, card_brand, card_type, card_number_masked,
  has_chip, credit_limit, acct_open_date, card_on_dark_web
)
SELECT card_id, client_id, card_brand, card_type, card_number_masked,
  has_chip, credit_limit, acct_open_date, card_on_dark_web
FROM etl.cards_clean;



-- dim merchant

TRUNCATE dw_etl.fact_transactions, dw_etl.dim_merchant RESTART IDENTITY CASCADE;

INSERT INTO dw_etl.dim_merchant (merchant_id, merchant_city, merchant_state, merchant_zip, location)
SELECT DISTINCT ON (merchant_id)
  merchant_id,
  INITCAP(TRIM(merchant_city)) AS merchant_city,
  UPPER(TRIM(merchant_state)) AS merchant_state,
  TRIM(merchant_zip) AS merchant_zip,
  CONCAT(
    INITCAP(TRIM(merchant_city)), ', ',
    UPPER(TRIM(merchant_state)), ' ',
    TRIM(COALESCE(merchant_zip, ''))
  ) AS location
FROM etl.transactions_clean
WHERE merchant_id IS NOT NULL
  AND TRIM(merchant_id) <> ''
ORDER BY merchant_id, merchant_city;



-- dim mcc

TRUNCATE dw_etl.dim_mcc RESTART IDENTITY CASCADE;

INSERT INTO dw_etl.dim_mcc (mcc_code, description)
SELECT mcc_code, description FROM etl.mcc_clean;

-- clean errors
INSERT INTO etl.errors_clean (error_desc)
SELECT DISTINCT
    CASE 
        WHEN TRIM(errors) = '' OR errors IS NULL THEN 'NO_ERROR'
        ELSE INITCAP(TRIM(errors))
    END AS error_desc
FROM etl.transactions_clean;

-- dim error
TRUNCATE dw_etl.dim_error RESTART IDENTITY CASCADE;

INSERT INTO dw_etl.dim_error (error_desc)
SELECT error_desc
FROM etl.errors_clean;

-- Fact Table

ALTER TABLE dw_etl.fact_transactions
ADD COLUMN error_key BIGINT REFERENCES dw_etl.dim_error(error_key);

TRUNCATE dw_etl.fact_transactions RESTART IDENTITY CASCADE;

INSERT INTO dw_etl.fact_transactions (
  transaction_id,
  date_key,
  customer_key,
  card_key,
  merchant_key,
  mcc_key,
  amount,
  use_chip,
  fraud_label,
  anomaly_flag,
  raw_amount,
  raw_date,
  error_key                     -- NEW COLUMN
)
SELECT
  tx.transaction_id,
  d.date_key,
  cu.customer_key,
  ca.card_key,
  m.merchant_key,
  mm.mcc_key,
  tx.amount,
  tx.use_chip,
  CASE WHEN LOWER(fc.fraud_label) IN ('yes','y','true','fraud','1') THEN TRUE ELSE FALSE END AS fraud_label,
  FALSE AS anomaly_flag,
  NULL::text AS raw_amount,
  tx.transaction_date::text,
  e.error_key                   -- NEW COLUMN
FROM etl.transactions_clean tx
LEFT JOIN dw_etl.dim_date d 
       ON d.full_date = tx.transaction_date::date
LEFT JOIN dw_etl.dim_customer cu 
       ON cu.customer_id = tx.client_id
LEFT JOIN dw_etl.dim_card ca 
       ON ca.card_id = tx.card_id
LEFT JOIN dw_etl.dim_merchant m 
       ON m.merchant_id = tx.merchant_id
LEFT JOIN dw_etl.dim_mcc mm 
       ON mm.mcc_code = tx.mcc_code
LEFT JOIN etl.fraud_clean fc 
       ON fc.transaction_id = tx.transaction_id
LEFT JOIN dw_etl.dim_error e
       ON (
            CASE 
                WHEN tx.errors IS NULL OR TRIM(tx.errors) = '' THEN 'NO_ERROR'
                ELSE INITCAP(TRIM(tx.errors))
            END
          ) = e.error_desc;

--		Comparisons 

SELECT
  (SELECT COUNT(*) FROM dw.fact_transactions) AS elt_fact_count,
  (SELECT COUNT(*) FROM dw_etl.fact_transactions) AS etl_fact_count;


SELECT
  (SELECT ROUND(SUM(amount),2) FROM dw.fact_transactions) AS elt_total_amount,
  (SELECT ROUND(SUM(amount),2) FROM dw_etl.fact_transactions) AS etl_total_amount;

SELECT
  (SELECT COUNT(*) FROM dw.dim_customer) AS elt_customers,
  (SELECT COUNT(*) FROM dw_etl.dim_customer) AS etl_customers,
  (SELECT COUNT(*) FROM dw.dim_card) AS elt_cards,
  (SELECT COUNT(*) FROM dw_etl.dim_card) AS etl_cards,
  (SELECT COUNT(*) FROM dw.dim_merchant) AS elt_merchants,
  (SELECT COUNT(*) FROM dw_etl.dim_merchant) AS etl_merchants,
  (SELECT COUNT(*) FROM dw.dim_mcc) AS elt_mcc,
  (SELECT COUNT(*) FROM dw_etl.dim_mcc) AS etl_mcc,
  (SELECT COUNT(*) FROM dw.dim_date) AS elt_dates,
  (SELECT COUNT(*) FROM dw_etl.dim_date) AS etl_dates;


SELECT
  (SELECT COUNT(*) FROM dw.fact_transactions WHERE fraud_label IS TRUE) AS elt_fraud_count,
  (SELECT COUNT(*) FROM dw_etl.fact_transactions WHERE fraud_label IS TRUE) AS etl_fraud_count;