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.abilities
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    ability_name character varying(50) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.abilities_candidates
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    ability_id integer NOT NULL,
    candidate_id integer NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.candidate_images
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    candidate_id integer NOT NULL,
    image_url character varying(500) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.candidate_school_with_departmans
(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    candidate_id integer NOT NULL,
    school_departments_id integer NOT NULL,
    starting_date date NOT NULL,
    ending_date date,
    PRIMARY KEY (id)
);

CREATE TABLE public.candidates
(
    id integer NOT NULL,
    first_name character varying(35) NOT NULL,
    last_name character varying(35) NOT NULL,
    identification_number character varying(11) NOT NULL,
    birth_date date NOT NULL,
    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.curriculum_vitaes
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    candidate_id integer NOT NULL,
    cover_letter character varying(500) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.departments
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    department_name character varying(150) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.employee_confirms
(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    employee_id integer NOT NULL,
    is_confirmed boolean NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.employee_confirms_employers
(
    id integer NOT NULL,
    employer_id integer NOT NULL,
    PRIMARY KEY (id)
);

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

CREATE TABLE public.employer_updates
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    changes json NOT NULL,
    employer_id integer NOT NULL,
    is_approved boolean NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.employers
(
    id integer NOT NULL,
    company_name character varying(255) NOT NULL,
    web_address character varying(255) NOT NULL,
    phone_number character varying,
    PRIMARY KEY (id)
);

CREATE TABLE public.favorites
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    candidate_id integer NOT NULL,
    job_advertisement_id integer NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.job_advertisements
(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    city_id integer NOT NULL,
    job_position_id integer NOT NULL,
    release_date timestamp without time zone NOT NULL,
    application_deadline timestamp without time zone NOT NULL,
    employer_id integer NOT NULL,
    min_salary real NOT NULL,
    max_salary real NOT NULL,
    number_of_open_position integer NOT NULL,
    description text NOT NULL,
    is_activate boolean NOT NULL,
    is_deleted boolean NOT NULL,
    working_time_id integer NOT NULL,
    way_of_working_id integer NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.job_experiences_candidates
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    workplace_id integer NOT NULL,
    job_position_id integer NOT NULL,
    candidate_id integer NOT NULL,
    starting_date date NOT NULL,
    ending_date date,
    PRIMARY KEY (id)
);

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

CREATE TABLE public.language_stages
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    stage_name character varying(2) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.languages
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    language_name character varying(100) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.languages_candidates
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    candidate_id integer NOT NULL,
    language_id integer NOT NULL,
    stage_id integer NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.school_departments
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    school_id integer NOT NULL,
    department_id integer NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.schools
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    school_name character varying(200) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.social_media_accounts
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    candidate_id integer NOT NULL,
    github_link character varying(500),
    linkedin_link character varying(500),
    PRIMARY KEY (id)
);

CREATE TABLE public.users
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    email_address character varying(320) NOT NULL,
    password character varying(25) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.verification_codes
(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    code character varying(38) NOT NULL,
    is_verified boolean NOT NULL,
    user_id integer,
    PRIMARY KEY (id)
);

CREATE TABLE public.way_of_workings
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    way_of_working_name character varying(50) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.working_times
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    working_time_name character varying(50) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.workplaces
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    workplace_name character varying(150) NOT NULL,
    PRIMARY KEY (id)
);

ALTER TABLE public.abilities_candidates
    ADD FOREIGN KEY (ability_id)
    REFERENCES public.abilities (id)
    NOT VALID;


ALTER TABLE public.abilities_candidates
    ADD FOREIGN KEY (candidate_id)
    REFERENCES public.candidates (id)
    NOT VALID;


ALTER TABLE public.candidate_images
    ADD FOREIGN KEY (candidate_id)
    REFERENCES public.candidates (id)
    NOT VALID;


ALTER TABLE public.candidate_school_with_departmans
    ADD FOREIGN KEY (school_departments_id)
    REFERENCES public.school_departments (id)
    NOT VALID;


ALTER TABLE public.candidate_school_with_departmans
    ADD FOREIGN KEY (candidate_id)
    REFERENCES public.candidates (id)
    NOT VALID;


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


ALTER TABLE public.curriculum_vitaes
    ADD FOREIGN KEY (candidate_id)
    REFERENCES public.candidates (id)
    NOT VALID;


ALTER TABLE public.employee_confirms
    ADD FOREIGN KEY (employee_id)
    REFERENCES public.employees (id)
    NOT VALID;


ALTER TABLE public.employee_confirms_employers
    ADD FOREIGN KEY (id)
    REFERENCES public.employee_confirms (id)
    NOT VALID;


ALTER TABLE public.employee_confirms_employers
    ADD FOREIGN KEY (employer_id)
    REFERENCES public.employers (id)
    NOT VALID;


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


ALTER TABLE public.employer_updates
    ADD FOREIGN KEY (employer_id)
    REFERENCES public.employers (id)
    NOT VALID;


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


ALTER TABLE public.favorites
    ADD FOREIGN KEY (candidate_id)
    REFERENCES public.candidates (id)
    NOT VALID;


ALTER TABLE public.favorites
    ADD FOREIGN KEY (job_advertisement_id)
    REFERENCES public.job_advertisements (id)
    NOT VALID;


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


ALTER TABLE public.job_advertisements
    ADD FOREIGN KEY (employer_id)
    REFERENCES public.employers (id)
    NOT VALID;


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


ALTER TABLE public.job_advertisements
    ADD FOREIGN KEY (way_of_working_id)
    REFERENCES public.way_of_workings (id)
    NOT VALID;


ALTER TABLE public.job_advertisements
    ADD FOREIGN KEY (working_time_id)
    REFERENCES public.working_times (id)
    NOT VALID;


ALTER TABLE public.job_experiences_candidates
    ADD FOREIGN KEY (candidate_id)
    REFERENCES public.candidates (id)
    NOT VALID;


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


ALTER TABLE public.job_experiences_candidates
    ADD FOREIGN KEY (workplace_id)
    REFERENCES public.workplaces (id)
    NOT VALID;


ALTER TABLE public.languages_candidates
    ADD FOREIGN KEY (candidate_id)
    REFERENCES public.candidates (id)
    NOT VALID;


ALTER TABLE public.languages_candidates
    ADD FOREIGN KEY (language_id)
    REFERENCES public.languages (id)
    NOT VALID;


ALTER TABLE public.languages_candidates
    ADD FOREIGN KEY (stage_id)
    REFERENCES public.language_stages (id)
    NOT VALID;


ALTER TABLE public.school_departments
    ADD FOREIGN KEY (department_id)
    REFERENCES public.departments (id)
    NOT VALID;


ALTER TABLE public.school_departments
    ADD FOREIGN KEY (school_id)
    REFERENCES public.schools (id)
    NOT VALID;


ALTER TABLE public.social_media_accounts
    ADD FOREIGN KEY (candidate_id)
    REFERENCES public.candidates (id)
    NOT VALID;


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

END;