CREATE TABLE DEPT2 AS SELECT * FROM dept ;

ALTER TABLE dept2 ADD( status VARCHAR2(10),request VARCHAR2(10));

CREATE TABLE dept_backup AS SELECT  * FROM dept2 WHERE 1=2;

--already created data making status change
update dept2 set status='APPROVED',REQUEST='CREATE';


create or replace 
PROCEDURE Maker_checker_test(p_deptno  IN NUMBER, 
                             p_dname   IN VARCHAR2, 
                             p_loc     IN VARCHAR2, 
                             p_user    IN VARCHAR2,                                              
                             p_request IN VARCHAR2) 
IS 
  l_request VARCHAR2(10); 
BEGIN 
    IF p_user = 'MAKER' THEN 
      IF p_request = 'INSERT' THEN 
        INSERT INTO dept2 (deptno, dname, loc, status, request) --insert to master table
        VALUES      ( p_deptno, p_dname, p_loc, 'PENDING', p_request ); 
      ELSIF p_request = 'UPDATE' THEN 
        INSERT INTO dept_backup 
        SELECT deptno, p_dname, p_loc, status, p_request --insert to temp table with new values(visible for checker)
        FROM   dept2 
        WHERE  deptno = p_deptno; 

        UPDATE dept2 
        SET    status = 'PENDING', request = p_request  --update master table status for changes made
        WHERE  deptno = p_deptno; 
      ELSIF p_request = 'DELETE' THEN 
       
        UPDATE dept2 
        SET    status = 'PENDING', request = p_request --update master table status for changes made(visible for checker)
        WHERE  deptno = p_deptno; 
      END IF; 
    ELSE 
      SELECT request 
      INTO   l_request 
      FROM   dept2 
      WHERE  deptno = p_deptno 
             AND status = 'PENDING'; 

      IF p_request = 'APPROVED' THEN 
        IF l_request = 'INSERT' THEN 
          UPDATE dept2 
          SET    status = 'APPROVED'      --final approval for insertion
          WHERE  deptno = p_deptno; 
        ELSIF l_request = 'UPDATE' THEN 
          UPDATE dept2 t1 --loading temp table data to master table
          SET    ( dname, loc, status ) = (SELECT dname, loc, 'APPROVED' 
                                                    FROM   dept_backup t2 
                                                    WHERE  t1.deptno = t2.deptno AND t1.status = 'PENDING') where  t1.deptno = p_deptno ; 
        ELSIF l_request = 'DELETE' THEN 
         INSERT INTO dept_backup 
         SELECT deptno, p_dname, p_loc, p_request,request  --storing back up of master table
         FROM   dept2 
         WHERE  deptno = p_deptno; 

          DELETE FROM dept2   --delete from master table
          WHERE  deptno = p_deptno;        
        END IF; 
      ELSE 
        IF l_request = 'INSERT' THEN 
          INSERT INTO dept_backup    --inserting into temp table for backup
                      (deptno, dname, loc, status ,request)
          SELECT p_deptno, p_dname, p_loc, 'REJECTED' , request
          FROM   dept2 
          WHERE  deptno = p_deptno; 

          DELETE FROM dept2 --delete from master table
          WHERE  deptno = p_deptno; 
        ELSIF l_request = 'UPDATE' THEN --rejected the request
          UPDATE dept2  
          SET    status = 'REJECTED' 
          WHERE  deptno = p_deptno; 
        ELSIF l_request = 'DELETE' THEN --rejected the request
          UPDATE dept2 
          SET    status = 'REJECTED' 
          WHERE  deptno = p_deptno; 
        END IF; 
      END IF; 

      NULL; 
    END IF; 
END;

--testing
BEGIN
Maker_checker_test(50,'DEFENCE','FERGUSON','MAKER','INSERT');
Maker_checker_test(30,'FINANCE','CHICAGO','MAKER','UPDATE');
Maker_checker_test(40,'OPERATIONS','BOSTON','MAKER','DELETE');
Maker_checker_test(20,'RESEARCH','DALLAS','MAKER','DELETE');
Maker_checker_test(10,'ACCOUNTING','INDIANA','MAKER','UPDATE');
Maker_checker_test(60,'MEDICINE','MADISON','MAKER','INSERT');
end;


BEGIN
Maker_checker_test(50,'DEFENCE','FERGUSON','CHECKER','APPROVED');
Maker_checker_test(30,'FINANCE','CHICAGO','CHECKER','APPROVED');
Maker_checker_test(40,'OPERATIONS','BOSTON','CHECKER','REJECTED');
Maker_checker_test(20,'RESEARCH','DALLAS','CHECKER','APPROVED');
Maker_checker_test(10,'ACCOUNTING','NEW YORK','CHECKER','REJECTED');
Maker_checker_test(60,'MEDICINE','MADISON','CHECKER','REJECTED');
END;