-- 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_codes
(
    id integer NOT NULL,
    activation_code character varying(6) NOT NULL,
    creation_date date NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.candidate_links
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 20000 CACHE 1 ),
    user_id integer NOT NULL,
    link_type_id integer NOT NULL,
    link_path text NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.candidates
(
    id integer NOT NULL,
    first_name text NOT NULL,
    last_name text NOT NULL,
    birth_year text NOT NULL,
    is_activated boolean NOT NULL,
    identification_number character varying(255),
    image_url text,
    description text,
    PRIMARY KEY (id)
);

CREATE TABLE public.candidates_activations
(
    activation_code_id integer NOT NULL,
    candidate_id integer NOT NULL,
    PRIMARY KEY (activation_code_id)
);

CREATE TABLE public.cities
(
    id integer NOT NULL,
    city_name text NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.employer_activation_by_system_users
(
    id integer NOT NULL,
    system_user_id integer NOT NULL,
    employer_id integer NOT NULL,
    activate_date date NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.employers
(
    id integer NOT NULL,
    company_name character varying(200) NOT NULL,
    web_adress character varying(100) NOT NULL,
    phone_number character varying(11) NOT NULL,
    is_activated boolean NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.employers_activations
(
    activation_code_id integer NOT NULL,
    employer_id integer NOT NULL,
    PRIMARY KEY (activation_code_id)
);

CREATE TABLE public.job_experiences
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 20000 CACHE 1 ),
    user_id integer NOT NULL,
    workplace_name text NOT NULL,
    "position" text NOT NULL,
    start_date date NOT NULL,
    finish_date date,
    PRIMARY KEY (id)
);

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

CREATE TABLE public.job_postings
(
    id integer NOT NULL,
    deadline_date date,
    is_active boolean,
    job_description character varying(255),
    listing_date date,
    max_salary double precision,
    min_salary double precision,
    number_of_open_position integer,
    city_id integer,
    employer_id integer,
    position_id integer,
    PRIMARY KEY (id)
);

CREATE TABLE public.languages
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 20000 CACHE 1 ),
    user_id integer NOT NULL,
    language_name text NOT NULL,
    language_level integer NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.link_types
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 20000 CACHE 1 ),
    link_type_name text NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.schools
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 20000 CACHE 1 ),
    user_id integer NOT NULL,
    department text NOT NULL,
    start_date date NOT NULL,
    graduate_date date,
    school_name text,
    PRIMARY KEY (id)
);

CREATE TABLE public.skills
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 20000 CACHE 1 ),
    user_id integer NOT NULL,
    skill_name text NOT NULL,
    PRIMARY KEY (id)
);

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

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

ALTER TABLE public.candidate_links
    ADD FOREIGN KEY (link_type_id)
    REFERENCES public.link_types (id)
    NOT VALID;


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


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


ALTER TABLE public.candidates_activations
    ADD FOREIGN KEY (activation_code_id)
    REFERENCES public.activation_codes (id)
    NOT VALID;


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


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


ALTER TABLE public.employer_activation_by_system_users
    ADD FOREIGN KEY (system_user_id)
    REFERENCES public.system_users (user_id)
    NOT VALID;


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


ALTER TABLE public.employers_activations
    ADD FOREIGN KEY (activation_code_id)
    REFERENCES public.activation_codes (id)
    NOT VALID;


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


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


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


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


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


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


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


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


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

END;