hrms.sql

create sequence hibernate_sequence;

create sequence candidates_id_seq;

create sequence employers_id_seq;

create sequence employees_id_seq;

create table users
(
    id       integer generated by default as identity,
    email    varchar(320) not null,
    password varchar(25)  not null,
    constraint pk_users
        primary key (id),
    constraint uc_users_email
        unique (email)
);

create table employees
(
    user_id    integer generated always as identity,
    first_name varchar(35) not null,
    last_name  varchar(35) not null,
    constraint pk_employees
        primary key (user_id),
    constraint fk_employees_users
        foreign key (user_id) references users
);

alter sequence employees_id_seq owned by employees.user_id;

create table candidates
(
    user_id         integer generated always as identity,
    first_name      varchar(35) not null,
    last_name       varchar(35) not null,
    identity_number varchar(11) not null,
    birth_year      integer     not null,
    constraint pk_candidates
        primary key (user_id),
    constraint uc_candidates_identity_number
        unique (identity_number),
    constraint fk_candidates_users
        foreign key (user_id) references users
);

alter sequence candidates_id_seq owned by candidates.user_id;

create table employers
(
    user_id      integer generated always as identity,
    company_name varchar(250) not null,
    web_address  varchar(50)  not null,
    phone_number varchar(12)  not null,
    constraint pk_employers
        primary key (user_id),
    constraint fk_employers_users
        foreign key (user_id) references users
);

alter sequence employers_id_seq owned by employers.user_id;

create table verification_codes
(
    id            integer generated by default as identity,
    code          varchar(38) not null,
    is_verified   boolean     not null,
    verified_date date,
    constraint pk_verification_codes
        primary key (id),
    constraint uc_verification_codes_code
        unique (code)
);

create table verification_codes_candidates
(
    id           integer not null,
    candidate_id integer not null,
    constraint pk_verification_codes_candidates
        primary key (id),
    constraint fk_verification_codes_candidates_verification_codes
        foreign key (id) references verification_codes,
    constraint fk_verification_codes_candidates_candidates
        foreign key (candidate_id) references candidates
);

create table verification_codes_employers
(
    id          integer not null,
    employer_id integer not null,
    constraint pk_verification_codes_employers
        primary key (id),
    constraint fk_verification_codes_employers_verification_codes
        foreign key (id) references verification_codes,
    constraint fk_verification_codes_employers_employers
        foreign key (employer_id) references employers
);

create table employee_confirms
(
    id           integer generated by default as identity,
    employee_id  integer not null,
    is_confirmed boolean not null,
    confirm_date date,
    constraint pk_employee_confirms
        primary key (id),
    constraint fk_employee_confirms_employees
        foreign key (employee_id) references employees
);

create table employee_confirm_employers
(
    id          integer not null,
    employer_id integer not null,
    constraint pk_employee_confirms_employers
        primary key (id),
    constraint fk_employee_confirms_employers_employee_confirms
        foreign key (id) references employee_confirms,
    constraint fk_employee_confirms_employers_employers
        foreign key (employer_id) references employers
);

create table job_titles
(
    id    integer generated by default as identity,
    title varchar(235) not null,
    constraint pk_job_titles
        primary key (id),
    constraint uc_job_titles_title
        unique (title)
);