-- settings
create table if not exists server_settings
(
    id    integer primary key generated by default as identity,
    name  varchar not null,
    value varchar not null
);
insert into server_settings (name, value)
values ('version', '1.0.0');

-- projects
create table if not exists projects
(
    id          integer primary key generated by default as identity,
    name        varchar    not null,
    description varchar,
    key         varchar(4) not null
);
insert into projects (name, key)
values ('demo', 'DEMO'),('madsky', 'MAD');

-- statuses
create table if not exists statuses
(
    id          integer primary key generated by default as identity,
    name        varchar           not null,
    description varchar,
    position    integer default 0 not null
);

insert into statuses (name)
values ('todo'),
       ('is working'),
       ('done');

-- issues
create table if not exists issues
(
    id          integer primary key generated by default as identity,
    name        varchar                   not null,
    description varchar,
    position    integer     default 0     not null,
    created     timestamptz DEFAULT now() not null,
    status_id   integer                   not null,
    project_id  integer                   not null,
    foreign key (status_id) references statuses,
    foreign key (project_id) references projects
);

-- users
create table if not exists users
(
    id            integer primary key generated by default as identity,
    email         varchar not null unique,
    password_hash varchar,
    name          varchar,
    avatar        varchar,
    role          varchar default 'user'
);
create index if not exists user_email_index on users (email);
-- insert into users (email, password_hash, name, isAdmin)
-- values ('admin@admin.ru', '', 'admin', true);

-- relation
create table if not exists user_to_issue
(
    user_id  integer,
    issue_id integer,
    foreign key (user_id) references users,
    foreign key (issue_id) references issues on delete cascade,
    constraint user_to_issue_primary_key primary key (user_id, issue_id)
);
create index if not exists user_to_issue_user_id_index on user_to_issue (user_id);
create index if not exists user_to_issue_issue_id_index on user_to_issue (issue_id);

create table if not exists user_to_project
(
    user_id    integer,
    project_id integer,
    foreign key (user_id) references users,
    foreign key (project_id) references projects on delete cascade,
    constraint user_to_project_primary_key primary key (user_id, project_id)
);
create index if not exists user_to_project_user_id_index on user_to_project (user_id);
create index if not exists user_to_project_project_id_index on user_to_project (project_id);


-- categories
create table if not exists categories
(
    id          integer primary key generated by default as identity,
    name        varchar not null,
    description varchar,
    color       varchar(7) default '#000000',
    image       varchar,
    user_id     integer not null,
    foreign key (user_id) references users
);
-- insert into categories (name, color, user_id)
-- values ('Default', '#ffffff', 1);