dataBaseHRMS.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.candidates
(
    id integer NOT NULL,
    first_name character varying(25) NOT NULL,
    last_name character varying(25) NOT NULL,
    identification_number character varying(11) NOT NULL,
    birth_date date NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.candidates_cv
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    candidate_id integer NOT NULL,
    linkedin_address character varying,
    github_address character varying,
    cover_letter text NOT NULL,
    is_active boolean NOT NULL,
    avatar_url character varying NOT NULL,
    PRIMARY KEY (id)
);

COMMENT ON TABLE public.candidates_cv
    IS 'Adayların CV Tablosu';

COMMENT ON COLUMN public.candidates_cv.candidate_id
    IS 'ÖN SÖZ';

COMMENT ON COLUMN public.candidates_cv.is_active
    IS 'Öne Çıkan CV';

CREATE TABLE public.candidates_cv_school
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    school_name character varying NOT NULL,
    entry_date date NOT NULL,
    graduation_date date,
    department character varying NOT NULL,
    candidates_cv_id integer NOT NULL,
    is_continue boolean,
    PRIMARY KEY (id)
);

COMMENT ON TABLE public.candidates_cv_school
    IS 'Okullar';

COMMENT ON COLUMN public.candidates_cv_school.department
    IS 'Meslek';

CREATE TABLE public.candidates_job_experiences
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    workplace_name character varying NOT NULL,
    entry_date date NOT NULL,
    exit_date date,
    job_titles_id integer NOT NULL,
    candidates_cv_id integer NOT NULL,
    is_continue boolean NOT NULL,
    job_detail text,
    PRIMARY KEY (id)
);

COMMENT ON TABLE public.candidates_job_experiences
    IS ' İŞ DENEYİMLERİ';

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

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

COMMENT ON TABLE public.candidates_talent
    IS 'İş arayan yetenekleri';

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(50) NOT NULL,
    PRIMARY KEY (id)
);

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

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

COMMENT ON TABLE public.favs
    IS 'favoriler';

CREATE TABLE public.job_advertisements
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    employer_id integer NOT NULL,
    job_title_id integer NOT NULL,
    description text NOT NULL,
    city_id integer NOT NULL,
    quota integer NOT NULL,
    appeal_expiration_date timestamp without time zone NOT NULL,
    is_active boolean NOT NULL,
    created_date timestamp without time zone,
    min_salary double precision NOT NULL,
    max_salary double precision NOT NULL,
    work_hours_id integer NOT NULL,
    work_types_id integer NOT NULL,
    is_confirmed_by_admin boolean,
    PRIMARY KEY (id)
);

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

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

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

COMMENT ON TABLE public.talents
    IS 'Yetenekler Tablosu';

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(50) NOT NULL,
    password character varying(25) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public.verification_codes
(
    id integer NOT NULL GENERATED ALWAYS 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 NOT NULL,
    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.candidates
    ADD FOREIGN KEY (id)
    REFERENCES public.users (id)
    NOT VALID;


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


ALTER TABLE public.candidates_cv_school
    ADD FOREIGN KEY (candidates_cv_id)
    REFERENCES public.candidates_cv (id)
    NOT VALID;


ALTER TABLE public.candidates_job_experiences
    ADD FOREIGN KEY (candidates_cv_id)
    REFERENCES public.candidates_cv (id)
    NOT VALID;


ALTER TABLE public.candidates_job_experiences
    ADD FOREIGN KEY (job_titles_id)
    REFERENCES public.job_titles (id)
    NOT VALID;


ALTER TABLE public.candidates_lang
    ADD FOREIGN KEY (candidates_cv_id)
    REFERENCES public.candidates_cv (id)
    NOT VALID;


ALTER TABLE public.candidates_lang
    ADD FOREIGN KEY (languages_id)
    REFERENCES public.languages (id)
    NOT VALID;


ALTER TABLE public.candidates_talent
    ADD FOREIGN KEY (candidates_cv_id)
    REFERENCES public.candidates_cv (id)
    NOT VALID;


ALTER TABLE public.candidates_talent
    ADD FOREIGN KEY (talents_id)
    REFERENCES public.talents (id)
    NOT VALID;


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


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


ALTER TABLE public.favs
    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.users (id)
    NOT VALID;


ALTER TABLE public.job_advertisements
    ADD FOREIGN KEY (job_title_id)
    REFERENCES public.job_titles (id)
    NOT VALID;


ALTER TABLE public.job_advertisements
    ADD FOREIGN KEY (work_hours_id)
    REFERENCES public.work_hours (id)
    NOT VALID;


ALTER TABLE public.job_advertisements
    ADD FOREIGN KEY (work_types_id)
    REFERENCES public.work_types (id)
    NOT VALID;


ALTER TABLE public.system_users
    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;