PRAGMA foreign_keys = ON;

CREATE TABLE accounts(
    id INTEGER NOT NULL PRIMARY KEY,
    name TEXT NOT NULL,
    opening_balance INTEGER NOT NULL,
    opening_date TEXT NOT NULL
);

CREATE TABLE budget_updates(
    id INTEGER NOT NULL PRIMARY KEY,
    category_id INTEGER NOT NULL,
    date TEXT NOT NULL,
    new_budget INTEGER NOT NULL,
    new_period INTEGER NOT NULL,
    FOREIGN KEY (category_id)
        REFERENCES categories (id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
);

CREATE TABLE categories(
    id INTEGER NOT NULL PRIMARY KEY,
    name TEXT NOT NULL,
    initial_budget INTEGER NOT NULL,
    initial_period INTEGER NOT NULL
);

CREATE TABLE category_transactions(
    id INTEGER NOT NULL PRIMARY KEY,
    description TEXT NOT NULL,
    quantity INTEGER NOT NULL,
    category_id INTEGER NOT NULL,
    FOREIGN KEY (category_id)
        REFERENCES categories (id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
);

CREATE TABLE category_transfers(
    id INTEGER NOT NULL PRIMARY KEY,
    description TEXT NOT NULL,
    quantity INTEGER NOT NULL,
    from_category_id INTEGER NOT NULL,
    to_category_id INTEGER NOT NULL,
    FOREIGN KEY (from_category_id)
        REFERENCES categories (id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    FOREIGN KEY (to_category_id)
        REFERENCES categories (id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
);

CREATE TABLE transactions(
    id INTEGER NOT NULL PRIMARY KEY,
    description TEXT NOT NULL,
    payee TEXT NOT NULL,
    quantity INTEGER NOT NULL,
    account_id INTEGER NOT NULL,
    FOREIGN KEY (account_id)
        REFERENCES accounts (id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
);