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 account_transfers(
    id INTEGER NOT NULL PRIMARY KEY,
    date TEXT NOT NULL,
    description TEXT NOT NULL,
    quantity INTEGER NOT NULL,
    from_account_id INTEGER NOT NULL,
    to_account_id INTEGER NOT NULL,
    FOREIGN KEY (from_account_id)
        REFERENCES accounts (id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    FOREIGN KEY (to_account_id)
        REFERENCES accounts (id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
);


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

CREATE TABLE categories(
    id INTEGER NOT NULL PRIMARY KEY,
    name TEXT NOT NULL,
    balance INTEGER NOT NULL,
    balance_date TEXT NOT NULL,
    budget_period INTEGER NOT NULL,
    budget_period_unit TEXT NOT NULL,
    budget_quantity INTEGER NOT NULL
);

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,
    date TEXT NOT NULL,
    account_id INTEGER NOT NULL,
    FOREIGN KEY (account_id)
        REFERENCES accounts (id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
);

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