-- Delete tables if they exist
DROP TABLE IF EXISTS transaction_breakdown CASCADE;
DROP TABLE IF EXISTS transaction CASCADE;
DROP TABLE IF EXISTS transaction_account CASCADE;
DROP TABLE IF EXISTS tag CASCADE;
DROP TABLE IF EXISTS "user" CASCADE;
DROP TABLE IF EXISTS tag_assigned_to_transaction CASCADE;

-- Create USER table
CREATE TABLE "user" (
    user_id SERIAL PRIMARY KEY,
    user_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    password VARCHAR(100) NOT NULL
);

-- Create TRANSACTION_ACCOUNT table
CREATE TABLE transaction_account (
    transaction_account_id SERIAL PRIMARY KEY,
    account_name VARCHAR(50),
    balance DECIMAL(10, 2),
    user_id INT REFERENCES "user"(user_id)
);

-- Create TRANSACTION table
CREATE TABLE transaction (
    transaction_id SERIAL PRIMARY KEY,
    transaction_name VARCHAR(100),
    amount DECIMAL(10, 2) NOT NULL,
    net_amount DECIMAL(10, 2),
    date TIMESTAMPTZ NOT NULL
);

-- Create TAG table
CREATE TABLE tag (
    tag_id SERIAL PRIMARY KEY,
    tag_name VARCHAR(50) NOT NULL
);

-- Create TAG_ASSIGNED_TO_TRANSACTION table
CREATE TABLE tag_assigned_to_transaction (
    tag_assigned_to_transaction_id SERIAL PRIMARY KEY,
    transaction_id INT NOT NULL REFERENCES transaction(transaction_id) ON DELETE CASCADE,
    tag_id INT NOT NULL REFERENCES tag(tag_id) ON DELETE CASCADE
);

-- Create TRANSACTION_BREAKDOWN table
CREATE TABLE transaction_breakdown (
    transaction_breakdown_id SERIAL PRIMARY KEY,
    transaction_id INT REFERENCES transaction(transaction_id),
    transaction_account_id INT REFERENCES transaction_account(transaction_account_id),
    spent_amount DECIMAL(10, 2),
    earned_amount DECIMAL(10, 2)
);