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.cities
(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    name character varying(75) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.cv_cover_letters
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    created_at date,
    is_active boolean,
    is_deleted boolean,
    content character varying(255) NOT NULL,
    jobseeker_id integer NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.cv_experiences
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    created_at date,
    is_active boolean,
    is_deleted boolean,
    leave_date date,
    "position" character varying(255) NOT NULL,
    start_date date NOT NULL,
    workplace_name character varying(255) NOT NULL,
    jobseeker_id integer NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.cv_foreign_languages
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    created_at date,
    is_active boolean,
    is_deleted boolean,
    language character varying(255) NOT NULL,
    level smallint NOT NULL,
    jobseeker_id integer NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.cv_images
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    created_at date,
    is_active boolean,
    is_deleted boolean,
    uploaded_at date,
    url character varying(255),
    jobseeker_id integer,
    PRIMARY KEY (id)
);

CREATE TABLE public.cv_links
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    created_at date,
    is_active boolean,
    is_deleted boolean,
    name character varying(255),
    url character varying(255) NOT NULL,
    jobseeker_id integer NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.cv_schools
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    created_at date,
    is_active boolean,
    is_deleted boolean,
    department_name character varying(255),
    school_name character varying(255),
    jobseeker_id integer NOT NULL,
    start_year date,
    graduation_year date,
    PRIMARY KEY (id)
);

CREATE TABLE public.cv_skills
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    created_at date,
    is_active boolean,
    is_deleted boolean,
    name character varying(255) NOT NULL,
    job_seeker_id integer NOT NULL,
    PRIMARY KEY (id)
);

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

CREATE TABLE public.employers
(
    user_id integer NOT NULL,
    website character varying(250) NOT NULL,
    phone_number character varying(30) NOT NULL,
    is_verified boolean NOT NULL,
    company_name character varying(250) NOT NULL,
    PRIMARY KEY (user_id)
);

CREATE TABLE public.employers_verification_employees
(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    employer_id integer NOT NULL,
    employee_id integer NOT NULL,
    created_at date NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.favorites
(
    id integer NOT NULL,
    jobseeker_id integer,
    jobadvert_id integer,
    PRIMARY KEY (id)
);

CREATE TABLE public.job_advert_confirmation
(
    id integer NOT NULL,
    confirmation_date date,
    is_confirmed boolean,
    employee_id integer,
    job_advert_id integer 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 ),
    job_position_id integer NOT NULL,
    description character varying NOT NULL,
    city_id integer NOT NULL,
    open_position_count integer NOT NULL,
    deadline date NOT NULL,
    is_open boolean,
    created_at timestamp without time zone,
    is_active boolean,
    is_deleted boolean,
    employer_id integer NOT NULL,
    published_at date NOT NULL,
    salary_min integer,
    salary_max integer,
    work_hour_id integer,
    work_type_id integer,
    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 ),
    job_title character varying(150) NOT NULL,
    created_at timestamp without time zone,
    is_active boolean,
    is_deleted boolean,
    PRIMARY KEY (id)
);

CREATE TABLE public.jobseekers
(
    user_id integer NOT NULL,
    first_name character varying(50) NOT NULL,
    last_name character varying(50) NOT NULL,
    national_id character varying(11) NOT NULL,
    is_verified boolean NOT NULL,
    date_of_birth date NOT NULL,
    PRIMARY KEY (user_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(100) NOT NULL,
    password character varying(30) NOT NULL,
    created_at timestamp without time zone,
    is_active boolean,
    is_deleted boolean,
    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 ),
    user_id integer NOT NULL,
    code character varying(50) NOT NULL,
    is_confirmed boolean NOT NULL,
    created_at date NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.verification_employer
(
    id integer NOT NULL,
    company_name character varying(255),
    created_at date,
    email character varying(255),
    is_active boolean,
    is_deleted boolean,
    is_verified boolean,
    password character varying(255),
    phone_number character varying(255),
    web_address character varying(255),
    PRIMARY KEY (id)
);

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

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

ALTER TABLE public.cv_cover_letters
    ADD FOREIGN KEY (jobseeker_id)
    REFERENCES public.jobseekers (user_id)
    NOT VALID;


ALTER TABLE public.cv_experiences
    ADD FOREIGN KEY (jobseeker_id)
    REFERENCES public.jobseekers (user_id)
    NOT VALID;


ALTER TABLE public.cv_foreign_languages
    ADD FOREIGN KEY (jobseeker_id)
    REFERENCES public.jobseekers (user_id)
    NOT VALID;


ALTER TABLE public.cv_images
    ADD FOREIGN KEY (jobseeker_id)
    REFERENCES public.jobseekers (user_id)
    NOT VALID;


ALTER TABLE public.cv_links
    ADD FOREIGN KEY (jobseeker_id)
    REFERENCES public.jobseekers (user_id)
    NOT VALID;


ALTER TABLE public.cv_schools
    ADD FOREIGN KEY (jobseeker_id)
    REFERENCES public.jobseekers (user_id)
    NOT VALID;


ALTER TABLE public.cv_skills
    ADD FOREIGN KEY (job_seeker_id)
    REFERENCES public.jobseekers (user_id)
    NOT VALID;


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


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


ALTER TABLE public.employers_verification_employees
    ADD FOREIGN KEY (employee_id)
    REFERENCES public.employees (user_id)
    NOT VALID;


ALTER TABLE public.employers_verification_employees
    ADD FOREIGN KEY (employer_id)
    REFERENCES public.employers (user_id)
    NOT VALID;


ALTER TABLE public.favorites
    ADD FOREIGN KEY (jobseeker_id)
    REFERENCES public.jobseekers (user_id)
    NOT VALID;


ALTER TABLE public.favorites
    ADD FOREIGN KEY (jobadvert_id)
    REFERENCES public.job_adverts (id)
    NOT VALID;


ALTER TABLE public.job_advert_confirmation
    ADD FOREIGN KEY (employee_id)
    REFERENCES public.employees (user_id)
    NOT VALID;


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


ALTER TABLE public.job_adverts
    ADD FOREIGN KEY (work_type_id)
    REFERENCES public.work_types (id)
    NOT VALID;


ALTER TABLE public.job_adverts
    ADD FOREIGN KEY (work_hour_id)
    REFERENCES public.work_hours (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 (employer_id)
    REFERENCES public.employers (user_id)
    NOT VALID;


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


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


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

END;