DROP TABLE IF EXISTS RawUsers;

-- Create the RawUsers table with VARCHAR data types
CREATE TABLE RawUsers (
    id VARCHAR(50),
    current_age VARCHAR(50),
    retirement_age VARCHAR(50),
    birth_year VARCHAR(50),
    birth_month VARCHAR(50),
    gender VARCHAR(50),
    address VARCHAR(255),
    latitude VARCHAR(50),
    longitude VARCHAR(50),
    per_capita_income VARCHAR(50),
    yearly_income VARCHAR(50),
    total_debt VARCHAR(50),
    credit_score VARCHAR(50),
    num_credit_cards VARCHAR(50)
);

DROP TABLE IF EXISTS RawTransactions;

-- Create the RawTransactions table with VARCHAR data types
CREATE TABLE RawTransactions (
    id VARCHAR(50),
    date VARCHAR(50),
    client_id VARCHAR(50),
    card_id VARCHAR(50),
    amount VARCHAR(50),
    use_chip VARCHAR(50),
    merchant_id VARCHAR(50),
    merchant_city VARCHAR(255),
    merchant_state VARCHAR(50),
    zip VARCHAR(50),
    mcc VARCHAR(50),
    errors VARCHAR(255)
);

-- Drop the ProcessedTransactions table if it already exists
DROP TABLE IF EXISTS ProcessedTransactions;

-- Create the ProcessedTransactions table with appropriate data types
CREATE TABLE ProcessedTransactions (
    id BIGINT PRIMARY KEY,
    date DATETIME,
    client_id INT,
    card_id BIGINT,
    amount DECIMAL(10, 2),
    use_chip BIT,
    merchant_id BIGINT,
    merchant_city VARCHAR(255),
    merchant_state CHAR(2),
    zip CHAR(5),
    mcc INT,
    errors VARCHAR(255)
);

-- Insert processed data into ProcessedTransactions
INSERT INTO ProcessedTransactions
SELECT
    TRY_CAST(id AS BIGINT) AS id,
    TRY_CAST(date AS DATETIME) AS date,
    TRY_CAST(client_id AS INT) AS client_id,
    TRY_CAST(card_id AS BIGINT) AS card_id,
    TRY_CAST(REPLACE(amount, '$', '') AS DECIMAL(10, 2)) AS amount,
    CASE WHEN UPPER(use_chip) = 'YES' THEN 1 ELSE 0 END AS use_chip,
    TRY_CAST(merchant_id AS BIGINT) AS merchant_id,
    merchant_city,
    TRY_CAST(merchant_state AS CHAR(2)) AS merchant_state,
    LEFT(TRY_CAST(zip AS VARCHAR(5)), 5) AS zip,
    TRY_CAST(mcc AS INT) AS mcc,
    errors
FROM RawTransactions;

-- Drop the RawTransactions table
DROP TABLE RawTransactions;

-- Drop the ProcessedUsers table if it already exists
DROP TABLE IF EXISTS ProcessedUsers;

-- Create the ProcessedUsers table with appropriate data types
CREATE TABLE ProcessedUsers (
    id BIGINT PRIMARY KEY,
    current_age INT,
    retirement_age INT,
    birth_year INT,
    birth_month INT,
    gender VARCHAR(10),
    address VARCHAR(255),
    latitude DECIMAL(10, 6),
    longitude DECIMAL(10, 6),
    per_capita_income DECIMAL(10, 2),
    yearly_income DECIMAL(10, 2),
    total_debt DECIMAL(10, 2),
    credit_score INT,
    num_credit_cards INT
);

-- Insert processed data into ProcessedUsers
INSERT INTO ProcessedUsers
SELECT
    TRY_CAST(id AS BIGINT) AS id,
    TRY_CAST(current_age AS INT) AS current_age,
    TRY_CAST(retirement_age AS INT) AS retirement_age,
    TRY_CAST(birth_year AS INT) AS birth_year,
    TRY_CAST(birth_month AS INT) AS birth_month,
    gender,
    address,
    TRY_CAST(latitude AS DECIMAL(10, 6)) AS latitude,
    TRY_CAST(longitude AS DECIMAL(10, 6)) AS longitude,
    TRY_CAST(REPLACE(per_capita_income, '$', '') AS DECIMAL(10, 2)) AS per_capita_income,
    TRY_CAST(REPLACE(yearly_income, '$', '') AS DECIMAL(10, 2)) AS yearly_income,
    TRY_CAST(REPLACE(total_debt, '$', '') AS DECIMAL(10, 2)) AS total_debt,
    TRY_CAST(credit_score AS INT) AS credit_score,
    TRY_CAST(num_credit_cards AS INT) AS num_credit_cards
FROM RawUsers;

-- Drop the RawUsers table
DROP TABLE RawUsers;