CREATE DATABASE finances;

CREATE TABLE business_type(
    id SERIAL PRIMARY KEY,
    name VARCHAR(128) NOT NULL,
    created_date DATE NOT NULL DEFAULT now()
);

CREATE TABLE business(
    id SERIAL PRIMARY KEY,
    name VARCHAR(256) NOT NULL,
    business_type_id INT,
    created_date DATE NOT NULL DEFAULT now(),
    FOREIGN KEY (business_type_id) REFERENCES business_type(id)
);

CREATE TABLE transaction(
    id SERIAL PRIMARY KEY,
    amount INT NOT NULL,
    business_id INT NOT NULL,
    is_expense BOOLEAN NOT NULL,
    created_date DATE NOT NULL DEFAULT now(),
    FOREIGN KEY (business_id) REFERENCES business(id)
);

CREATE TABLE account_type(
    id SERIAL PRIMARY KEY,
    name VARCHAR(256) UNIQUE NOT NULL,
    is_income BOOLEAN NOT NULL,
    is_expense BOOLEAN NOT NULL,
    created_date DATE NOT NULL DEFAULT now()
);

CREATE TABLE account(
    id SERIAL PRIMARY KEY,
    name VARCHAR(256) UNIQUE NOT NULL,
    account_type_id INT NOT NULL,
    created_date DATE NOT NULL DEFAULT now(),
    updated_date DATE,
    FOREIGN KEY (account_type_id) REFERENCES account_type(id)
);

CREATE TABLE account_total(
    account_id INT NOT NULL,
    total FLOAT NOT NULL,
    created_date DATE NOT NULL DEFAULT now(),
    FOREIGN KEY (account_id) REFERENCES account(id)
);