create table categories(
    id NUMBER primary key,
    display_name VARCHAR2(255),
    parent_id NUMBER
);

create table employers(
    id NUMBER primary key,
    company_name VARCHAR2(255),
    address VARCHAR2(255),
    description VARCHAR2(4000),
    created_at DATE,
    updated_at DATE
);

create table employees(
    id NUMBER primary key,
    first_name VARCHAR2(255),
    last_name VARCHAR2(255),
    gender VARCHAR2(255),
    salary NUMBER,
    email VARCHAR2(255),
    phone VARCHAR2(255),
    schedule_type NUMBER,
    job_position VARCHAR2(255),
    category_id NUMBER,
    education_history VARCHAR2(4000),
    work_history VARCHAR2(4000),
    created_at DATE,
    updated_at DATE,
    constraint fk_employee_category foreign key (category_id) references  categories(id)
);

create table vacancies(
    id NUMBER primary key,
    employer_id NUMBER,
    deadline DATE,
    description VARCHAR2(4000),
    title VARCHAR2(255),
    salary NUMBER,
    job_position VARCHAR2(255),
    category_id NUMBER,
    schedule_type NUMBER,
    city VARCHAR2(255),
    phone VARCHAR2(255),
    email VARCHAR2(255),
    created_at DATE,
    updated_at DATE,
    constraint fk_vacancy_category foreign key (category_id) references  categories(id)
);

// (!!) result(0-waiting,1-accepted,2-rejected)
create table applications(
    id NUMBER primary key,
    employee_id NUMBER,
    vacancy_id NUMBER,
    result NUMBER,
    created_at DATE,
    updated_at DATE,
    constraint fk_application_employee foreign key (employee_id) references  employees(id),
    constraint fk_application_vacancy foreign key (vacancy_id) references  vacancies(id)
)

INSERT INTO categories VALUES (1, 'Maliyye', null);
INSERT INTO categories VALUES (2, 'Sigorta', 1);
INSERT INTO categories VALUES (3, 'Audit', 1);
INSERT INTO categories VALUES (4, 'Muhasibat', 1);
INSERT INTO categories VALUES (5, 'Informasiya Texnologiyalari', null);
INSERT INTO categories VALUES (6, 'IT mutexessis', 5);
INSERT INTO categories VALUES (7, 'Proqramlasdirma', 5);
INSERT INTO categories VALUES (8, 'Sistem Idareetmesi', 5);

INSERT INTO employers VALUES (1, 'Azercell', 'Filan street 14', 'Telekommunikasiya sirketi',SYSDATE,SYSDATE);
INSERT INTO employers VALUES (2, 'ATL Tech', 'Basqa street 14', 'IT Solutions',SYSDATE,SYSDATE);
INSERT INTO employers VALUES (3, 'Idrak Technology', 'Tamam basqa street 14', 'Yene IT Solutions',SYSDATE,SYSDATE);

INSERT INTO employees VALUES (1, 'Chingiz', 'Mammadli', 'male',2000,'memmedlicngz@gmail.com','+994505050505',0,'Software Engineer',7,'Qafqaz 2014-2019','APA 2017-2018',SYSDATE,SYSDATE);
INSERT INTO employees VALUES (2, 'Ferize', 'Ehmedova', 'female',3500,'ferizeahmedova@gmail.com','+994555555555',1,'Muhasib',4,'BDU 2012-2016','P&G 2017-2019',SYSDATE,SYSDATE);
INSERT INTO employees VALUES (3, 'Nagi', 'Quliyev', 'male',800,'nagiquliyev@gmail.com','+994707070707',1,'Sys admin',8,'ADNA 2012-2016','SomeCompany 2015-2017',SYSDATE,SYSDATE);


INSERT INTO vacancies VALUES (1, 1, '12/20/2018', 'Java dev axtarilir, 9dan 18e qeder, 3 il is tecrubesi falan filan','Yaxsi dev axtarilir',500,'Java Developer',7,0,'Baku','+994125025002','hr@azercell.com',SYSDATE,SYSDATE);
INSERT INTO vacancies VALUES (2, 3, '11/19/2018', 'Muhasib axtarilir, 9dan 18e qeder, 5 il is tecrubesi falan filan','Muhasib vakansiyasi',2000,'Muhasib',4,1,'Baku','+994125445454','recruitment@idrak.az',SYSDATE,SYSDATE);

INSERT INTO applications VALUES (1, 1, 1, 0, SYSDATE, SYSDATE);
INSERT INTO applications VALUES (2, 2, 2, 0, SYSDATE, SYSDATE);


// --- d) Create at least one view(join at least 2 table by foreign keys) ---

CREATE OR REPLACE VIEW application_details AS
SELECT employees.first_name||' '||employees.last_name AS fullname, employers.company_name, vacancies.job_position, vacancies.title
FROM applications
JOIN employees ON applications.employee_id = employees.id
JOIN vacancies ON applications.vacancy_id = vacancies.id
JOIN employers ON vacancies.employer_id = employers.id;

SELECT * FROM application_details;

///////
CREATE SEQUENCE   "APPLICATION_SEQUENCE"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE  NOPARTITION;

CREATE SEQUENCE   "CATEGORY_SEQUENCE"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE  NOPARTITION;

CREATE SEQUENCE   "EMPLOYEE_SEQUENCE"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE  NOPARTITION;

CREATE SEQUENCE   "EMPLOYER_SEQUENCE"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE  NOPARTITION

CREATE SEQUENCE   "VACANCY_SEQUENCE"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE  NOPARTITION

///
create or replace procedure "CATEGORY_CRUD_PROCEDURE"
(p_operation IN VARCHAR2,
p_id IN NUMBER,
p_display_name IN VARCHAR2,
p_parent_id IN NUMBER)
is
begin
   CASE p_operation
      when 'create' then
          insert into categories
              (id, display_name, parent_id)
          values
              (category_sequence.nextval, p_display_name, p_parent_id);
      --when 'read' then
          --select * from categories;
      when 'update' then
          update categories set display_name = p_display_name, parent_id = p_parent_id
          where id = p_id;
      when 'delete' then dbms_output.put_line('You passed');
          DELETE FROM categories WHERE ID = p_id;
      else dbms_output.put_line('No such operation');
   END CASE;
end;

///
begin
 CATEGORY_CRUD_PROCEDURE('create', null, 'yeni', null);
end