ddl.sql

conn iutpc/iutpcadmin
--one user added 
--username iutpc 
--password iutpcadmin@123
--admin access 1

insert into user_info values('admin','iutpcadmin','iutpcadmin',1);


CREATE TABLE USER_INFO(
	USERID VARCHAR2(20 CHAR) PRIMARY KEY,
	NAME VARCHAR2(40 CHAR) NOT NULL,
	PASSWORD VARCHAR2(50 CHAR),
	ADMINACCESS NUMBER NOT NULL -- 0 IF HAS ADMIN ACCESS || 1 / 2 IS THE DIVISION OF CODERS
);

CREATE TABLE PENDING_REQ(
	USERID VARCHAR2(20 CHAR) PRIMARY KEY,
	NAME VARCHAR2(40 CHAR) NOT NULL,
	PASSWORD VARCHAR2(50 CHAR) NOT NULL,
	VJUDGE_HANDLE VARCHAR2(40 CHAR) NOT NULL,
	CF_HANDLE VARCHAR2(40 CHAR) NOT NULL,
	TOPH_HANDLE VARCHAR2(40 CHAR) NOT NULL
);


CREATE TABLE DIVISION_INFO( -- 0 MEANS FOR ALL --1 MEANS FOR DIV 1 --2 MEANS FOR DIV 2
	DIVISION_ID NUMBER PRIMARY KEY,
	LOWEST_RANK NUMBER NOT NULL,
	HIGHEST_RANK NUMBER NOT NULL
);

insert into DIVISION_INFO VALUES(1,1600,8000);
insert into DIVISION_INFO VALUES(2,0,1599);
insert into DIVISION_INFO VALUES(0,0,8000);


CREATE TABLE PLATFORM_INFO(
	PLATFORM VARCHAR2(40 CHAR) PRIMARY KEY,
	PLATFORM_HOME_URL VARCHAR2(40 CHAR) NOT NULL
);

INSERT INTO PLATFORM_INFO VALUES('vjudge','https://vjudge.com');
INSERT INTO PLATFORM_INFO VALUES('codeforces','https://codeforces.com');
INSERT INTO PLATFORM_INFO VALUES('toph','https://toph.co');



CREATE TABLE HANDLE_INFO(
	USERID VARCHAR2(20 CHAR) PRIMARY KEY,
	CF_HANDLE VARCHAR2(40 CHAR) NOT NULL,
	VJUDGE_HANDLE VARCHAR2(40 CHAR) NOT NULL,
	TOPH_HANDLE VARCHAR2(40 CHAR) NOT NULL,
	DIVISION_ID NUMBER NOT NULL, --SHOULD BE GENERATED BY USING TRIGGER AND CURSOR
	FOREIGN KEY(DIVISION_ID) REFERENCES DIVISION_INFO(DIVISION_ID),
	FOREIGN KEY(USERID) REFERENCES USER_INFO(USERID)
);


CREATE TABLE CURRENT_RATING(
	USERID VARCHAR2(20 CHAR) PRIMARY KEY,
	CF_RATING NUMBER,
	VJUDGE_RATING NUMBER,
	OVERALL_RATING NUMBER,
	FOREIGN KEY(USERID) REFERENCES USER_INFO(USERID)	
);

CREATE TABLE CONTEST_INFO(
	CONTEST_ID VARCHAR2(20 CHAR) PRIMARY KEY, -- FORMAT YYYYMMDIV###  || DIV -> 000 OR 001 || ### IS THE NUMBER 
	CONTEST_NAME VARCHAR2(40 CHAR) NOT NULL,
	CONTEST_URL VARCHAR2(40 CHAR) NOT NULL,
	PLATFORM_ID VARCHAR2(40 CHAR) NOT NULL, 
	DIVISION NUMBER NOT NULL,
	MANDATORY NUMBER NOT NULL, -- 0 MEANS NOT 1 MEANS YES
	FOREIGN KEY(PLATFORM_ID) REFERENCES PLATFORM_INFO(PLATFORM),
	FOREIGN KEY(DIVISION) REFERENCES DIVISION_INFO(DIVISION_ID)
);

CREATE TABLE RANKLIST(
	CONTEST_ID VARCHAR2(20 CHAR),
	USERID VARCHAR2(20 CHAR),
	POINT_GAINED NUMBER,
	TIME_PENALTY NUMBER,
	CHANGE_RATING NUMBER,
	FOREIGN KEY(CONTEST_ID) REFERENCES CONTEST_INFO(CONTEST_ID)
);



--problem set is not implemented for the current time
--may be weighter contest will be added later
CREATE TABLE PROBLEM_SET(
	CONTEST_ID VARCHAR2(20 CHAR),
	PROBLEM_ID NUMBER,
	WEIGHT NUMBER,
	FOREIGN KEY(CONTEST_ID) REFERENCES CONTEST_INFO(CONTEST_ID) 
);


INSERT INTO USER_INFO VALUES('IUTPC','IUT PROGRAMMING COMMUNITY ADMIN','IUTPCADMIN@123',1);

--test 

CREATE OR REPLACE FUNCTION CREATE_CONTEST_ID (PLATFORM IN VARCHAR2)
RETURN VARCHAR2
IS
CNT NUMBER;
CID VARCHAR2(40 CHAR);
BEGIN
SELECT COUNT(CONTEST_ID) INTO CNT FROM CONTEST_INFO WHERE PLATFORM_ID = PLATFORM;
DBMS_OUTPUT.PUT_LINE(CNT);
CID := CONCAT(PLATFORM,TO_CHAR(CNT+1));
RETURN CID;
END;
/

SET SERVEROUTPUT ON

DECLARE
STR VARCHAR2(40 CHAR);
BEGIN
STR := CREATE_CONTEST_ID('vjudge');
DBMS_OUTPUT.PUT_LINE(STR);
END;
/


CREATE or REPLACE TRIGGER lutha
BEFORE INSERT ON CONTEST_INFO 
for each row
BEGIN
	:new.CONTEST_ID := CREATE_CONTEST_ID(:new.PLATFORM_ID);
END;
/

CREATE OR REPLACE FUNCTION GET_DIVISION_ID(RATING IN NUMBER)
RETURN NUMBER
IS
D1H NUMBER;
D1L NUMBER;
D2H NUMBER;
D2L NUMBER;
ID NUMBER;
BEGIN
SELECT LOWEST_RANK INTO D1L FROM DIVISION_INFO WHERE DIVISION_ID = 1;
SELECT HIGHEST_RANK INTO D1H FROM DIVISION_INFO WHERE DIVISION_ID = 1;
SELECT LOWEST_RANK INTO D2L FROM DIVISION_INFO WHERE DIVISION_ID = 2;
SELECT HIGHEST_RANK INTO D2H FROM DIVISION_INFO WHERE DIVISION_ID = 2;
IF RATING >= D1L THEN
	ID := 1;
ELSE
	ID := 2;
END IF;
RETURN ID;
END;
/



SET SERVEROUTPUT ON

DECLARE
STR NUMBER;
BEGIN
STR := GET_DIVISION_ID(2000);
DBMS_OUTPUT.PUT_LINE(STR);
END;
/






CREATE OR REPLACE TRIGGER ADD_DIVISION
AFTER INSERT OR UPDATE ON CURRENT_RATING
FOR EACH ROW 
BEGIN
	UPDATE HANDLE_INFO 
	SET DIVISION_ID = GET_DIVISION_ID(:NEW.OVERALL_RATING)
	WHERE USERID = :NEW.USERID;
END;
/