-- 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.activation_panel_for_system_user

(

id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),

detail character varying(200),

activation_date timestamp without time zone,

is_approved boolean,

system_user integer,

job_id integer,

PRIMARY KEY (id)

);

CREATE TABLE public.attended_schools

(

id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),

school_id integer NOT NULL,

"startingDateOfUniversity" date NOT NULL,

"graduationDateOfUniversity" date,

job_seeker_id integer,

graduation_date_of_university timestamp without time zone,

starting_date_of_university timestamp without time zone,

PRIMARY KEY (id)

);

CREATE TABLE public.cities

(

id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),

city_name character varying(200) NOT NULL,

PRIMARY KEY (id)

);

CREATE TABLE public.department

(

id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),

department_name character varying NOT NULL,

PRIMARY KEY (id)

);

CREATE TABLE public.departments

(

id integer NOT NULL,

department_name character varying(255),

PRIMARY KEY (id)

);

CREATE TABLE public.employers

(

id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 32767 CACHE 1 ),

userid integer,

companyname character varying(50),

website character varying(100),

phonenumber character varying(15),

PRIMARY KEY (id)

);

CREATE TABLE public.images

(

id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),

job_seeker_id integer NOT NULL,

image_title character varying(100) NOT NULL,

image character varying(500) NOT NULL,

PRIMARY KEY (id)

);

CREATE TABLE public.job_positions

(

id integer NOT NULL,

position_name character varying(50),

PRIMARY KEY (id)

);

CREATE TABLE public.job_seekers

(

id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 32767 CACHE 1 ),

userid integer,

firstname character varying(50),

lastname character varying(50),

birthdate integer,

nationalityid bigint,

github_account character varying(255),

info_about_job_seeker character varying(255),

linkedin_account character varying(255),

job_position_id integer,

PRIMARY KEY (id)

);

CREATE TABLE public.jobs

(

id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),

general_position_id integer NOT NULL,

description character varying(3400) NOT NULL,

city_id integer NOT NULL,

min_salary integer NOT NULL,

max_salary integer NOT NULL,

number_of_empty_positions integer NOT NULL,

is_active boolean NOT NULL,

deadline date NOT NULL,

created_date date NOT NULL,

employer_id integer NOT NULL,

work_place_id integer NOT NULL,

work_time_id integer NOT NULL,

PRIMARY KEY (id)

);

CREATE TABLE public.known_languages

(

id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),

language_level_id integer NOT NULL,

language_name character varying NOT NULL,

job_seeker_id integer,

PRIMARY KEY (id)

);

CREATE TABLE public.language_levels

(

id integer NOT NULL,

level_description character varying(200) 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,

department_id integer,

PRIMARY KEY (id)

);

CREATE TABLE public.system_users

(

id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 32767 CACHE 1 ),

userid integer,

role character varying(255),

PRIMARY KEY (id)

);

CREATE TABLE public.users

(

id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 32767 CACHE 1 ),

isactivated boolean NOT NULL,

status boolean NOT NULL,

email character varying(100),

createddate date,

password character varying(255),

PRIMARY KEY (id)

);

CREATE TABLE public.work_experiences

(

id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),

position_id integer NOT NULL,

workplace_name character varying NOT NULL,

starting_date date NOT NULL,

quit_date date,

job_seeker_id integer NOT NULL,

PRIMARY KEY (id)

);

CREATE TABLE public.work_place

(

id integer NOT NULL,

work_place_name character varying(255),

PRIMARY KEY (id)

);

CREATE TABLE public.work_time

(

id integer NOT NULL,

work_time_name character varying(255),

PRIMARY KEY (id)

);

ALTER TABLE public.activation_panel_for_system_user

ADD FOREIGN KEY (job_id)

REFERENCES public.jobs (id)

NOT VALID;

ALTER TABLE public.activation_panel_for_system_user

ADD FOREIGN KEY (system_user)

REFERENCES public.system_users (id)

NOT VALID;

ALTER TABLE public.attended_schools

ADD FOREIGN KEY (job_seeker_id)

REFERENCES public.job_seekers (id)

NOT VALID;

ALTER TABLE public.attended_schools

ADD FOREIGN KEY (school_id)

REFERENCES public.schools (id)

NOT VALID;

ALTER TABLE public.departments

ADD FOREIGN KEY (id)

REFERENCES public.schools (id)

NOT VALID;

ALTER TABLE public.employers

ADD FOREIGN KEY (userid)

REFERENCES public.users (id)

NOT VALID;

ALTER TABLE public.images

ADD FOREIGN KEY (job_seeker_id)

REFERENCES public.job_seekers (id)

NOT VALID;

ALTER TABLE public.job_seekers

ADD FOREIGN KEY (job_position_id)

REFERENCES public.job_positions (id)

NOT VALID;

ALTER TABLE public.job_seekers

ADD FOREIGN KEY (userid)

REFERENCES public.users (id)

NOT VALID;

ALTER TABLE public.jobs

ADD FOREIGN KEY (city_id)

REFERENCES public.cities (id)

NOT VALID;

ALTER TABLE public.jobs

ADD FOREIGN KEY (employer_id)

REFERENCES public.employers (id)

NOT VALID;

ALTER TABLE public.jobs

ADD FOREIGN KEY (work_time_id)

REFERENCES public.work_time (id)

NOT VALID;

ALTER TABLE public.jobs

ADD FOREIGN KEY (work_place_id)

REFERENCES public.work_place (id)

NOT VALID;

ALTER TABLE public.jobs

ADD FOREIGN KEY (general_position_id)

REFERENCES public.job_positions (id)

NOT VALID;

ALTER TABLE public.known_languages

ADD FOREIGN KEY (job_seeker_id)

REFERENCES public.job_seekers (id)

NOT VALID;

ALTER TABLE public.known_languages

ADD FOREIGN KEY (language_level_id)

REFERENCES public.language_levels (id)

NOT VALID;

ALTER TABLE public.schools

ADD FOREIGN KEY (department_id)

REFERENCES public.department (id)

NOT VALID;

ALTER TABLE public.system_users

ADD FOREIGN KEY (userid)

REFERENCES public.users (id)

NOT VALID;

ALTER TABLE public.work_experiences

ADD FOREIGN KEY (job_seeker_id)

REFERENCES public.job_seekers (id)

NOT VALID;

ALTER TABLE public.work_experiences

ADD FOREIGN KEY (position_id)

REFERENCES public.job_positions (id)

NOT VALID;

END;