hrms.sql

-- This script was generated by a beta version of the ERD tool in pgAdmin 4.
-- Please log an issue at https://redmine.postgresql.org/projects/pgadmin4/issues/new if you find any bugs, including reproduction steps.
BEGIN;


CREATE TABLE public.candidate_users
(
    id integer NOT NULL,
    first_name character varying(50) NOT NULL,
    last_name character varying(50) NOT NULL,
    identity_number character varying(11) NOT NULL,
    date_of_birth date NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.candidate_users_verify
(
    id integer NOT NULL,
    candidate_id integer NOT NULL,
    status boolean,
    PRIMARY KEY (id)
);

CREATE TABLE public.cities
(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    city_name character varying(25) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.edu_graduates
(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    description character varying NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.employer_user_staff_verify
(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    employer_id integer NOT NULL,
    confirm_staff integer,
    confirm_date date,
    is_confirmed boolean,
    created_date date NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.employer_user_staff_verify_account_update
(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    employer_id integer,
    confirm_staff integer,
    confirm_date date,
    is_confirmed boolean NOT NULL,
    created_date date NOT NULL,
    temp_account_information jsonb NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.employer_users
(
    id integer NOT NULL,
    company_name character varying(100) NOT NULL,
    web_address character varying(50) NOT NULL,
    phone_number character varying(17) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.employer_users_verify
(
    id integer NOT NULL,
    employer_id integer NOT NULL,
    status boolean,
    PRIMARY KEY (id)
);

CREATE TABLE public.favorite_job_adverts
(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    candidate_user_id integer NOT NULL,
    job_advert_id integer NOT NULL,
    created_date date NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.job_advert_staff_verify
(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    job_advert_id integer NOT NULL,
    confirm_staff_id integer,
    confirm_date date,
    is_confirmed boolean,
    created_date date NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.job_adverts
(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    employer_id integer NOT NULL,
    position_id integer NOT NULL,
    city_id integer NOT NULL,
    advert_description character varying(250) NOT NULL,
    min_salary integer,
    max_salary integer,
    open_position smallint NOT NULL,
    deadline date,
    created_date date NOT NULL,
    is_active boolean NOT NULL,
    working_place_id integer NOT NULL,
    working_time_id integer NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.job_positions
(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    "position" character varying(50) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.resume_edu
(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    resume_id integer NOT NULL,
    school_name character varying NOT NULL,
    graduate_id integer NOT NULL,
    school_department character varying NOT NULL,
    started_date date NOT NULL,
    ended_date date,
    created_date date NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.resume_job_exp
(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    resume_id integer NOT NULL,
    company_name character varying NOT NULL,
    job_position_id integer NOT NULL,
    started_date date NOT NULL,
    ended_date date,
    created_date date NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.resume_langs
(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    resume_id integer NOT NULL,
    language character varying NOT NULL,
    lang_level character(1) NOT NULL,
    created_date date NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.resume_techs
(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    resume_id integer NOT NULL,
    description character varying NOT NULL,
    created_date date NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.resumes
(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    candidate_id integer NOT NULL,
    github_link character varying,
    linkedin_link character varying,
    photo character varying,
    description character varying,
    created_date date NOT NULL,
    is_active boolean,
    updated_date date,
    PRIMARY KEY (id)
);

CREATE TABLE public.staff_users
(
    id integer NOT NULL,
    first_name character varying(50) NOT NULL,
    last_name character varying(50) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.users
(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    email character varying(50) NOT NULL,
    password character varying(32) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.verify_code
(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    verify_code character varying(10) NOT NULL,
    is_confirmed boolean,
    created_date date NOT NULL,
    confirmed_date date,
    PRIMARY KEY (id)
);

CREATE TABLE public.working_place_types
(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    working_place character varying(50) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.working_time_types
(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    working_time character varying(50) NOT NULL,
    PRIMARY KEY (id)
);

ALTER TABLE public.candidate_users
    ADD FOREIGN KEY (id)
    REFERENCES public.users (id)
    NOT VALID;


ALTER TABLE public.candidate_users_verify
    ADD FOREIGN KEY (candidate_id)
    REFERENCES public.candidate_users (id)
    NOT VALID;


ALTER TABLE public.candidate_users_verify
    ADD FOREIGN KEY (id)
    REFERENCES public.verify_code (id)
    NOT VALID;


ALTER TABLE public.employer_user_staff_verify
    ADD FOREIGN KEY (employer_id)
    REFERENCES public.employer_users (id)
    NOT VALID;


ALTER TABLE public.employer_user_staff_verify
    ADD FOREIGN KEY (confirm_staff)
    REFERENCES public.staff_users (id)
    NOT VALID;


ALTER TABLE public.employer_user_staff_verify_account_update
    ADD FOREIGN KEY (confirm_staff)
    REFERENCES public.staff_users (id)
    NOT VALID;


ALTER TABLE public.employer_user_staff_verify_account_update
    ADD FOREIGN KEY (employer_id)
    REFERENCES public.employer_users (id)
    NOT VALID;


ALTER TABLE public.employer_users
    ADD FOREIGN KEY (id)
    REFERENCES public.users (id)
    NOT VALID;


ALTER TABLE public.employer_users_verify
    ADD FOREIGN KEY (employer_id)
    REFERENCES public.employer_users (id)
    NOT VALID;


ALTER TABLE public.employer_users_verify
    ADD FOREIGN KEY (id)
    REFERENCES public.verify_code (id)
    NOT VALID;


ALTER TABLE public.favorite_job_adverts
    ADD FOREIGN KEY (candidate_user_id)
    REFERENCES public.candidate_users (id)
    NOT VALID;


ALTER TABLE public.favorite_job_adverts
    ADD FOREIGN KEY (job_advert_id)
    REFERENCES public.job_adverts (id)
    NOT VALID;


ALTER TABLE public.job_advert_staff_verify
    ADD FOREIGN KEY (confirm_staff_id)
    REFERENCES public.staff_users (id)
    NOT VALID;


ALTER TABLE public.job_advert_staff_verify
    ADD FOREIGN KEY (job_advert_id)
    REFERENCES public.job_adverts (id)
    NOT VALID;


ALTER TABLE public.job_adverts
    ADD FOREIGN KEY (employer_id)
    REFERENCES public.employer_users (id)
    NOT VALID;


ALTER TABLE public.job_adverts
    ADD FOREIGN KEY (city_id)
    REFERENCES public.cities (id)
    NOT VALID;


ALTER TABLE public.job_adverts
    ADD FOREIGN KEY (position_id)
    REFERENCES public.job_positions (id)
    NOT VALID;


ALTER TABLE public.job_adverts
    ADD FOREIGN KEY (working_place_id)
    REFERENCES public.working_place_types (id)
    NOT VALID;


ALTER TABLE public.job_adverts
    ADD FOREIGN KEY (working_time_id)
    REFERENCES public.working_time_types (id)
    NOT VALID;


ALTER TABLE public.resume_edu
    ADD FOREIGN KEY (graduate_id)
    REFERENCES public.edu_graduates (id)
    NOT VALID;


ALTER TABLE public.resume_edu
    ADD FOREIGN KEY (resume_id)
    REFERENCES public.resumes (id)
    NOT VALID;


ALTER TABLE public.resume_job_exp
    ADD FOREIGN KEY (job_position_id)
    REFERENCES public.job_positions (id)
    NOT VALID;


ALTER TABLE public.resume_job_exp
    ADD FOREIGN KEY (resume_id)
    REFERENCES public.resumes (id)
    NOT VALID;


ALTER TABLE public.resume_langs
    ADD FOREIGN KEY (resume_id)
    REFERENCES public.resumes (id)
    NOT VALID;


ALTER TABLE public.resume_techs
    ADD FOREIGN KEY (resume_id)
    REFERENCES public.resumes (id)
    NOT VALID;


ALTER TABLE public.resumes
    ADD FOREIGN KEY (candidate_id)
    REFERENCES public.candidate_users (id)
    NOT VALID;


ALTER TABLE public.staff_users
    ADD FOREIGN KEY (id)
    REFERENCES public.users (id)
    NOT VALID;

END;