-- 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),
    day_id INT REFERENCES DAY(day_id),
    tag_id INT REFERENCES TAG(tag_id)
);

-- 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)
);

-- Create YEAR table
CREATE TABLE YEAR (
    year_id SERIAL PRIMARY KEY,
    year_name INT NOT NULL
);

-- Create MONTH table
CREATE TABLE MONTH (
    month_id SERIAL PRIMARY KEY,
    year_id INT REFERENCES YEAR(year_id),
    month_name VARCHAR(20) NOT NULL
);

-- Create DAY table
CREATE TABLE DAY (
    day_id SERIAL PRIMARY KEY,
    month_id INT REFERENCES MONTH(month_id),
    day_number INT,
    day_name VARCHAR(20)
);

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

-- 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 DAY CASCADE;
DROP TABLE IF EXISTS MONTH CASCADE;
DROP TABLE IF EXISTS YEAR CASCADE;
DROP TABLE IF EXISTS TAG CASCADE;
DROP TABLE IF EXISTS USER CASCADE;