CREATE TABLE users (
    id          SERIAL PRIMARY KEY,
    name        TEXT NOT NULL UNIQUE,
    pwd         TEXT NOT NULL,
    description TEXT
);

CREATE TABLE categories (
    id        SERIAL PRIMARY KEY,
    name      TEXT    NOT NULL,
    parent_id INTEGER REFERENCES categories,
    user_id   INTEGER NOT NULL REFERENCES users
);

CREATE INDEX parent_id
    ON categories (parent_id);

CREATE INDEX user_id
    ON categories (user_id);


CREATE TABLE const_expenses (
    id          SERIAL PRIMARY KEY,
    description TEXT,
    expense     NUMERIC(10, 2) NOT NULL,
    duration    INTERVAL       NOT NULL,
    start       DATE           NOT NULL,
    "end"       DATE           NOT NULL,
    prev_id     INTEGER REFERENCES const_expenses,
    category_id INTEGER        NOT NULL REFERENCES categories,
    user_id     INTEGER        NOT NULL REFERENCES users
);

CREATE TABLE single_expenses (
    id          BIGSERIAL PRIMARY KEY,
    description TEXT,
    expense     NUMERIC(10, 2) NOT NULL,
    date        DATE           NOT NULL,
    -- we need the cast to timestamp, because it is only considered immutable then
    corr_month  DATE GENERATED ALWAYS AS (DATE_TRUNC('month', date::timestamp)) STORED,
    category_id INTEGER        NOT NULL REFERENCES categories,
    user_id     INTEGER        NOT NULL REFERENCES users
);

CREATE INDEX idx_single_month ON single_expenses (user_id, corr_month);