table_creations_MAIN.sql

-- PROJECT1_BOOKS
INSERT INTO PROJECT1_BOOKS
SELECT DISTINCT ISBN10, TITLE FROM PROJECT1_BOOKS_LOAD;

--COMMIT;
---------------------------------------------------------------
--PROJECT1_AUTHORS

SELECT T.AUTHOR,
REGEXP_SUBSTR(T.AUTHOR,'[^,]+',1,1) PART1,
REGEXP_SUBSTR(T.AUTHOR,'[^,]+',1,2) PART2,
REGEXP_SUBSTR(T.AUTHOR,'[^,]+',1,3) PART3,
REGEXP_SUBSTR(T.AUTHOR,'[^,]+',1,4) PART4,
REGEXP_SUBSTR(T.AUTHOR,'[^,]+',1,5) PART5
FROM PROJECT1_BOOKS_LOAD T;

CREATE TABLE TEMP1(NAM VARCHAR2(100));

INSERT INTO TEMP1
SELECT REGEXP_SUBSTR(T.AUTHOR,'[^,]+',1,1) PART1
FROM PROJECT1_BOOKS_LOAD T
WHERE REGEXP_SUBSTR(T.AUTHOR,'[^,]+',1,1) IS NOT NULL;

INSERT INTO PROJECT1_AUTHORS
SELECT AUTHORS_SEQ.NEXTVAL, NAM FROM(
SELECT UNIQUE NAM FROM TEMP1
ORDER BY 1);

--COMMIT;
--------------------------------------------------------------------
--PROJECT1_BOOK_AUTHORS

CREATE TABLE TEMP2(ISBN10 VARCHAR2(20),
                   NAME VARCHAR2(100));

INSERT INTO TEMP2
SELECT ISBN10, 
REGEXP_SUBSTR(T.AUTHOR,'[^,]+',1,1) PART1
FROM PROJECT1_BOOKS_LOAD T
WHERE REGEXP_SUBSTR(T.AUTHOR,'[^,]+',1,1) IS NOT NULL;

INSERT INTO PROJECT1_BOOK_AUTHORS
SELECT UNIQUE A.AUTHOR_ID, T.ISBN10
FROM PROJECT1_AUTHORS A, TEMP2 T
WHERE A.NAME = T.NAME
ORDER BY 1;

--COMMIT;

----------------------------------------------------------------------
-- PROJECT1_LIBRARY_BRANCH

INSERT INTO PROJECT1_LIBRARY_BRANCH
SELECT * FROM PROJECT1_LIBRARY_BRANCH_LOAD;

--COMMIT;

----------------------------------------------------------------------
--PROJECT1_BOOK_COPIES 

DECLARE

CURSOR C1 IS 
SELECT ISBN10, BRANCH_ID, COPIES_NO
FROM PROJECT1_BOOK_COPIES_LOAD
WHERE COPIES_NO > 0
ORDER BY 1;

VARC1 C1%ROWTYPE;
V_COPIES NUMBER;

BEGIN
    OPEN C1;
    LOOP
        FETCH C1 INTO VARC1;
        EXIT WHEN C1%NOTFOUND;
        
        V_COPIES := VARC1.COPIES_NO;
        
        FOR I IN 1..V_COPIES LOOP
            INSERT INTO PROJECT1_BOOK_COPIES
            VALUES(BOOK_ID_SEQ.NEXTVAL, VARC1.ISBN10, VARC1.BRANCH_ID);
        END LOOP;    
    END LOOP;
    CLOSE C1;
END;
/

--COMMIT;

---------------------------------------------------------------------------
--PROJECT1_BORROWER
INSERT INTO PROJECT1_BORROWER
SELECT DISTINCT CARD_NO, SSN, FNAME, LNAME, ADDRESS, PHONE
FROM PROJECT1_BORROWER_LOAD;

--COMMIT;

---------------------------------------------------------------------------

--PROJECT1_BOOK_LOANS

DECLARE

CURSOR C1 IS
SELECT M.RA,
       M.BOOK_ID, 
       M.CARD_NO, 
       M.DT_OUT, 
       M.DT_OUT+7 DT_DUE,  
       CASE WHEN MOD(M.RA,4)=0 THEN NULL
        WHEN MOD(M.RA,3)=0 THEN M.DT_OUT+5
        WHEN MOD(M.RA,5)=0 THEN M.DT_OUT+2
        ELSE M.DT_OUT+7 END DT_IN
FROM (SELECT A.RA RA, 
             A.BOOK_ID, 
             B.CARD_NO, 
             C.DT_OUT
      FROM (SELECT ROWNUM RA, 
                   BOOK_ID 
            FROM(SELECT BOOK_ID 
                 FROM PROJECT1_BOOK_COPIES
                 ORDER BY DBMS_RANDOM.VALUE)
            WHERE ROWNUM <= 100) A,
           (SELECT ROWNUM RB, 
                   CARD_NO 
            FROM(SELECT CARD_NO 
                 FROM PROJECT1_BORROWER
                 ORDER BY DBMS_RANDOM.VALUE)
            WHERE ROWNUM <= 200) B,
           (SELECT TRUNC(TO_DATE('31/10/2020','DD/MM/RRRR') + DBMS_RANDOM.VALUE * 364) DT_OUT 
            FROM DUAL) C
WHERE A.RA = B.RB
ORDER BY DBMS_RANDOM.VALUE) M;

VARC1 C1%ROWTYPE;

BEGIN
    OPEN C1;
    LOOP
        FETCH C1 INTO VARC1;
        EXIT WHEN C1%NOTFOUND;
        
        INSERT INTO PROJECT1_BOOK_LOANS
        VALUES('L' || LPAD(BOOK_LOANS_SEQ.NEXTVAL,3,0), VARC1.BOOK_ID, VARC1.CARD_NO, VARC1.DT_OUT, VARC1.DT_DUE, VARC1.DT_IN);
        
    END LOOP;
    CLOSE C1;
        
END;
/

--COMMIT;

---------------------------------------------------------------------------------------
--PROJECT1_FINES

INSERT INTO PROJECT1_FINES
SELECT T.LOAN_ID, 
       ROUND((NVL(TO_DATE(T.DATE_IN,'DD/MM/RRRR'),SYSDATE) - TO_DATE(T.DATE_DUE,'DD/MM/RRRR'))*0.25,2) FINE_AMT, 
       CASE WHEN MOD(SUBSTR(T.LOAN_ID,2),2) = 0 THEN 'Y'
            WHEN MOD(SUBSTR(T.LOAN_ID,2),3) = 0 THEN 'Y'
            ELSE 'N' END PAID
FROM PROJECT1_BOOK_LOANS T
WHERE T.DATE_IN IS NULL OR TO_DATE(T.DATE_IN,'DD/MM/RRRR') - TO_DATE(T.DATE_DUE,'DD/MM/RRRR') > 0;

--COMMIT;