create table users
(
    id          int auto_increment
        primary key,
    name        varchar(50)  not null,
    pwd         varchar(255) not null,
    description varchar(100) null,
    constraint name
        unique (name)
);

create table categories
(
    id        int auto_increment
        primary key,
    name      varchar(50) not null,
    parent_id int         null,
    user_id   int         not null,
    constraint categories_ibfk_1
        foreign key (parent_id) references categories (id),
    constraint categories_ibfk_2
        foreign key (user_id) references users (id)
);

create index parent_id
    on categories (parent_id);

create index user_id
    on categories (user_id);

create table const_expenses
(
    id          int auto_increment
        primary key,
    description varchar(50)      null,
    expense     decimal(10, 2)   not null,
    months      tinyint unsigned not null,
    start       date             not null,
    end         date             not null,
    prev_id     int              null,
    category_id int              not null,
    user_id     int              not null,
    constraint const_expenses_ibfk_1
        foreign key (prev_id) references const_expenses (id),
    constraint const_expenses_ibfk_2
        foreign key (category_id) references categories (id),
    constraint const_expenses_ibfk_3
        foreign key (user_id) references users (id)
);

create index category_id
    on const_expenses (category_id);

create index idx_start_end
    on const_expenses (user_id, start, end);

create index ix_constexpense_end
    on const_expenses (end);

create index ix_constexpense_start
    on const_expenses (start);

create index prev_id
    on const_expenses (prev_id);

create table single_expenses
(
    id          int auto_increment
        primary key,
    description varchar(50)         null,
    expense     decimal(10, 2)      not null,
    year        year                not null,
    month       tinyint(6) unsigned not null,
    day         tinyint(6) unsigned not null,
    category_id int                 not null,
    user_id     int                 not null,
    constraint single_expenses_ibfk_1
        foreign key (category_id) references categories (id),
    constraint single_expenses_ibfk_2
        foreign key (user_id) references users (id)
);

create index category_id
    on single_expenses (category_id);

create index idx_single_date
    on single_expenses (user_id, year, month);