migrations/version_0.sql

-- Create table TRANSACTION
CREATE TABLE IF NOT EXISTS TRANSACTIONS (
    id SERIAL PRIMARY KEY,
    transaction_number VARCHAR NOT NULL,
    transaction_amount DECIMAL(10, 2) NOT NULL,
    transaction_datetime TIMESTAMP NOT NULL,
    transaction_category VARCHAR,
    customer_firstname VARCHAR,
    customer_lastname VARCHAR,
    customer_gender VARCHAR,
    customer_credit_card_num VARCHAR NOT NULL,
    customer_address_street VARCHAR,
    customer_address_city VARCHAR,
    customer_address_state VARCHAR,
    customer_address_zip VARCHAR,
    customer_address_latitude DECIMAL(9, 6),
    customer_address_longitude DECIMAL(9, 6),
    customer_address_city_population INT,
    customer_job VARCHAR,
    customer_dob DATE,
    merchant_name VARCHAR NOT NULL,
    merchant_address_latitude DECIMAL(9, 6),
    merchant_address_longitude DECIMAL(9, 6),
    is_fraud BOOLEAN,
    -- Add unique constraint on transaction_number
    CONSTRAINT unique_transaction UNIQUE (transaction_number)
);

-- Add index on transaction_number
CREATE INDEX idx_transaction_number ON TRANSACTIONS (transaction_number);
-- Add index on transaction_datetime
CREATE INDEX idx_transaction_datetime ON TRANSACTIONS (transaction_datetime);
-- Add index on transaction_category
CREATE INDEX idx_transaction_category ON TRANSACTIONS (transaction_category);
-- Add index on transaction_amount
CREATE INDEX idx_transaction_amount ON TRANSACTIONS (transaction_amount);
-- Add index on is_fraud
CREATE INDEX idx_is_fraud ON TRANSACTIONS (is_fraud);