-- Drop the RawCardData table if it already exists

DROP TABLE IF EXISTS RawCardData;

-- Create the RawCardData table with VARCHAR data types
CREATE TABLE RawCardData (
    client_id VARCHAR(50),
    card_brand VARCHAR(50),
    card_type VARCHAR(50),
    card_number VARCHAR(50),
    expires VARCHAR(50),
    cvv VARCHAR(50),
    has_chip VARCHAR(50),
    num_cards_issued VARCHAR(50),
    credit_limit VARCHAR(50),
    acct_open_date VARCHAR(50),
    year_pin_last_changed VARCHAR(50),
    card_on_dark_web VARCHAR(50)
);

-- Step 1: Drop the ProcessedCardData table if it already exists
DROP TABLE IF EXISTS ProcessedCardData;

-- Step 2: Create the ProcessedCardData table with appropriate data types
CREATE TABLE ProcessedCardData (
    client_id INT,
    card_brand VARCHAR(50),
    card_type VARCHAR(50),
    card_number BIGINT,
    expires DATE,
    cvv INT,
    has_chip BIT,
    num_cards_issued INT,
    credit_limit DECIMAL(10, 2),
    acct_open_date DATE,
    year_pin_last_changed INT,
    card_on_dark_web BIT
);

-- Step 3: Populate the ProcessedCardData table from RawCardData
-- Replace spaces and parentheses in the card_type column before inserting
INSERT INTO ProcessedCardData
SELECT
    TRY_CAST(client_id AS INT) AS client_id,
    card_brand,
    -- Replace spaces with underscores, replace '(' with '_', and ')' with '' in card_type
    REPLACE(REPLACE(REPLACE(card_type, ' ', '_'), '(', '_'), ')', '') AS card_type,
    TRY_CAST(card_number AS BIGINT) AS card_number,
    TRY_CAST(CONCAT(SUBSTRING(expires, 4, 4), '-', SUBSTRING(expires, 1, 2), '-01') AS DATE) AS expires,
    TRY_CAST(cvv AS INT) AS cvv,
    CASE WHEN UPPER(has_chip) = 'YES' THEN 1 ELSE 0 END AS has_chip, -- 1 (TRUE), 0 (FALSE)
    TRY_CAST(num_cards_issued AS INT) AS num_cards_issued,
    TRY_CAST(REPLACE(credit_limit, '$', '') AS DECIMAL(10, 2)) AS credit_limit,
    TRY_CAST(CONCAT(SUBSTRING(acct_open_date, 4, 4), '-', SUBSTRING(acct_open_date, 1, 2), '-01') AS DATE) AS acct_open_date,
    TRY_CAST(year_pin_last_changed AS INT) AS year_pin_last_changed,
    CASE WHEN UPPER(card_on_dark_web) = 'YES' THEN 1 ELSE 0 END AS card_on_dark_web -- 1 (TRUE), 0 (FALSE)
FROM RawCardData;

-- Step 4: Drop the RawCardData table after processing
DROP TABLE RawCardData;