PLSQL_deVega_Estela.sql

/*******REALIZADO POR : ESTELA DE VEGA MARTIN 1ºDAM***********/
/***********************PL/SQL CUBE_X*************************/

/*---------------------TABLA CONTENIDOS------------------------
|  CONDICION  |                  EJERCICIOS                   |
---------------------------------------------------------------
|  FUNCIONES  |  EJERCICIO --> 4, 6, 8                        |
|  PROCEDIM.  |  EJERCICIO --> 3, 4, 5, 7, 8, 9               |
|     IF      |  EJERCICIO --> 1, 2, 4, 5, 7, 8, 9            |
|    CASE     |  EJERCICIO --> 3                              |
|   BUCLES    |  EJERCICIO --> 3, 6                           |
|  CURSOR IMP |  EJERCICIO --> 4, 5, 7, 9                     |
|  CURSOR EXP |  EJERCICIO --> 4, 6, 7, 8, 9                  |
| TRIG DERIV  |  EJERCICIO --> 1, 2, 3                        |
| TRIG AUTOM  |  EJERCICIO --> 3, 6                           |
---------------------------------------------------------------*/

SET SERVEROUTPUT ON;
/*1) DISPARADOR QUE SE ENCARGA DE MANTENER ACTUALIZADO EL ATRIBUTO DERIVADO 
"level_user" DE LA TABLA CUBE_USERS ANTE CUALQUIER TIEMPO QUE HAGA EL USUARIO.
CADA TIEMPO QUE HAGA UN USUARIO S LE SUMARA 25 DE EXPERIENCIA*/
CREATE OR REPLACE TRIGGER trigLevelUser
AFTER INSERT OR DELETE OR UPDATE ON SCRAMBLE
FOR EACH ROW
BEGIN 
    IF INSERTING THEN
        UPDATE CUBE_USERS
        SET LEVEL_USER = LEVEL_USER + 25
        WHERE ID_USER = :new.ID_USER;
        
    ELSIF DELETING THEN
        UPDATE CUBE_USERS
        SET LEVEL_USER = LEVEL_USER - 25
        WHERE ID_USER = :old.ID_USER;
        
    ELSIF UPDATING THEN
        UPDATE CUBE_USERS
        SET LEVEL_USER = LEVEL_USER + 25
        WHERE ID_USER = :new.ID_USER;
        
        UPDATE CUBE_USERS
        SET LEVEL_USER = LEVEL_USER - 25
        WHERE ID_USER = :old.ID_USER;
    END IF;
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);

END trigLevelUser;
/


/*2) DISPARADOR QUE SE ENCARGA DE MANTENER ACTUALIZADO EL ATRIBUTO DERIVADO 
"discount" DE LA TABLA MEMBERS ANTE CUALQUIER LAPSO DE SEIS MESES QUE CUMPLA
SIENDO MIEMBRO. CADA SEIS MESES LE DAN UN DESCUENTO DE DIEZ EUROS PARA FUTURAS 
COMPETICIONES EXCLUSIVAS*/
-- SE CREA UNA VISTA PARA INSERTAR AQUI LOS DATOS DE MEMBERS Y LUEGO SE INTERTA 
-- EN LA TABLA MEMBERS EL DESCUENTO YA ACTUALIZADO. SE USA INSTEAD OF YA QUE NO  
-- SE PUEDE EDITAR LA TABLA EN EL TRIGGER QUE SE USA ESA TABLA 
CREATE VIEW MEMBERS_VIEW AS
    SELECT ID_USER, ID_MEMBER, DISCOUNT, REGISTRATION_DATE
    FROM MEMBERS;

CREATE OR REPLACE TRIGGER trigDiscountMember
INSTEAD OF INSERT OR DELETE OR UPDATE ON MEMBERS_VIEW
FOR EACH ROW
DECLARE
    V_DISCOUNT MEMBERS.DISCOUNT%TYPE;
BEGIN 
    IF INSERTING THEN
        V_DISCOUNT := (10 * TRUNC(MONTHS_BETWEEN(SYSDATE, :new.REGISTRATION_DATE)/6));
        INSERT INTO MEMBERS VALUES (:new.ID_USER, :new.ID_MEMBER, V_DISCOUNT, :new.REGISTRATION_DATE);
        
    ELSIF DELETING THEN
        DELETE FROM MEMBERS WHERE ID_MEMBER = :old.ID_MEMBER;
        
    ELSIF UPDATING THEN
        UPDATE MEMBERS
        SET DISCOUNT = (10 * TRUNC(MONTHS_BETWEEN(SYSDATE, :new.REGISTRATION_DATE)/6))
        WHERE ID_MEMBER = :new.ID_MEMBER;
        
        UPDATE MEMBERS
        SET DISCOUNT = (10 * TRUNC(MONTHS_BETWEEN(SYSDATE, :old.REGISTRATION_DATE)/6))
        - (10 * TRUNC(MONTHS_BETWEEN(SYSDATE, :old.REGISTRATION_DATE)/6))
        WHERE ID_MEMBER = :old.ID_MEMBER;
    END IF;
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/
-- EN VEZ DE INSERTAR LOS DATOS EN MEMBERS (linea 144 del diseño fisico) CAMBIAR
-- LA TABLA MEMBERS POR LA VISTA Y ASI SI FUNCIONARA EL TRIGGER


/*3) DISPARADOR QUE SE ENCARGA DE MANTENER ACTUALIZADO EL ATRIBUTO DERIVADO 
"avg_minutes" Y "avg_seconds" Y LOS DEMAS DATOS DE LA TABLA AVERAGE ANTE 
CUALQUIER TIEMPO QUE HAYA METIDO EL USUARIO. 
EL DISPARADOR LLAMARA A UN PROCEDIMIENTO QUE ACTUALICE LA TABLA*/
CREATE OR REPLACE PROCEDURE actualizarAverage 
IS 
    MAXNUMBER NUMBER(7);
    CONTADORWHILE NUMBER(7);
BEGIN 
    -- SELECCIONAR EL MÍNIMO E IR CONTANDO TODOS LOS AVERAGES
    SELECT COUNT(ID_AVERAGE) INTO MAXNUMBER FROM SCRAMBLE;
    SELECT MIN(ID_AVERAGE) INTO CONTADORWHILE FROM SCRAMBLE;

    WHILE CONTADORWHILE < (MAXNUMBER + 1) LOOP
        -- PERIOD        
        UPDATE AVERAGE 
        SET PERIOD_AVG = (SELECT COUNT(ID_SCRAMBLE) FROM SCRAMBLE 
                          WHERE ID_AVERAGE = CONTADORWHILE)
        WHERE ID_AVERAGE = CONTADORWHILE;
        
        -- PEOR MINUTO
        UPDATE AVERAGE 
        SET WORST_MINUTES = 
                (SELECT TRUNC(MAX(MINUTES1 * 60 + SECONDS1)/60) AS MINUTOS 
                FROM SCRAMBLE WHERE ID_AVERAGE = CONTADORWHILE)
        WHERE ID_AVERAGE = CONTADORWHILE;
        
        -- PEOR SEGUNDO
        UPDATE AVERAGE SET WORST_SECONDS = 
                        (SELECT MAX(MINUTES1 * 60 + SECONDS1) - MAX(MINUTES1 * 60) 
                            AS SECONDS 
                        FROM SCRAMBLE WHERE ID_AVERAGE = CONTADORWHILE)
        WHERE ID_AVERAGE = CONTADORWHILE;
        
        -- MEJOR MINUTO
        UPDATE AVERAGE SET PB_MINUTES = 
                        (SELECT TRUNC(MIN(MINUTES1 * 60 + SECONDS1)/60) AS MINUTOS 
                        FROM SCRAMBLE WHERE ID_AVERAGE = CONTADORWHILE)
        WHERE ID_AVERAGE = CONTADORWHILE;
        
        -- MEJOR SEGUNDO
        UPDATE AVERAGE SET PB_SECONDS = 
                        (SELECT MIN(MINUTES1 * 60 + SECONDS1) - MIN(MINUTES1 * 60) 
                            AS SEGUNDOS 
                        FROM SCRAMBLE WHERE ID_AVERAGE = CONTADORWHILE)
        WHERE ID_AVERAGE = CONTADORWHILE; 
        
        -- AVG MINUTOS
        UPDATE AVERAGE SET AVG_MINUTES =
            (SELECT TRUNC((SUM(TIEMPO) - MIN(TIEMPO) - MAX(TIEMPO))/ (A.PERIOD_AVG - 2) / 60)
                AS MEDIAMINUTOS
            FROM (SELECT ID_AVERAGE,   
                    CASE
                        WHEN COMMENTS1 IS NULL AND COMMENTS2 IS NULL THEN
                            MINUTES1 * 60 + SECONDS1
                        WHEN COMMENTS1 LIKE '+2' THEN
                            MINUTES1 * 60 + SECONDS1 + 2
                        WHEN COMMENTS1 LIKE 'DNF' THEN
                            (MINUTES1 * 60 + SECONDS1) * 0
                    END AS TIEMPO
                FROM SCRAMBLE S
                WHERE S.ID_AVERAGE = CONTADORWHILE
            ) TIEMPO
            INNER JOIN AVERAGE A ON TIEMPO.ID_AVERAGE = A.ID_AVERAGE
            WHERE A.ID_AVERAGE = CONTADORWHILE
            GROUP BY A.PERIOD_AVG)
        WHERE ID_AVERAGE = CONTADORWHILE;
        
        -- AVG SEGUNDOS
        UPDATE AVERAGE SET AVG_SECONDS = 
            (SELECT MOD(TRUNC((SUM(TIEMPO) - MIN(TIEMPO) - MAX(TIEMPO))
                / (A.PERIOD_AVG-2),3),60)  AS MEDIASEGUNDOS
            FROM (SELECT ID_AVERAGE,   
                    CASE
                        WHEN COMMENTS1 IS NULL AND COMMENTS2 IS NULL THEN
                            MINUTES1 * 60 + SECONDS1
                        WHEN COMMENTS1 LIKE '+2' THEN
                            MINUTES1 * 60 + SECONDS1 + 2
                        WHEN COMMENTS1 LIKE 'DNF' THEN
                            (MINUTES1 * 60 + SECONDS1) * 0
                    END AS TIEMPO
                FROM SCRAMBLE S
                WHERE S.ID_AVERAGE = CONTADORWHILE
            ) TIEMPO
            INNER JOIN AVERAGE A ON TIEMPO.ID_AVERAGE = A.ID_AVERAGE
            WHERE A.ID_AVERAGE = CONTADORWHILE
            GROUP BY A.PERIOD_AVG)
        WHERE ID_AVERAGE = CONTADORWHILE;
        
        CONTADORWHILE := CONTADORWHILE + 1;
    END LOOP;
END;
/


CREATE OR REPLACE TRIGGER triAvgMinutesAndSeconds
AFTER INSERT OR UPDATE OR DELETE ON SCRAMBLE
BEGIN
    -- LLAMAR AL PROCEDIMIENTO PARA ACTUALIZAR LA TABLA AVERAGE
    actualizarAverage;
    DBMS_OUTPUT.PUT_LINE('ACTUALIZACION REALIZADA CORRECTAMENTE');
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/


/* 4) CREAR UN PROCEDIMIENTO LLAMADO "insertTimesChamp" QUE PERMITA INSERTAR TIEMPOS
DE UN USUARIO EN UN CAMPEONATO Y, POSTERIORMENTE, MOSTRAR EL GANADOR DE ESE 
CAMPEONATO. PARA OBTENER EL GANADOR SE LLAMARA A UNA FUNCION QUE RETORNE EL NOMBRE
DEL QUE HAYA HECHO LA MENOR MEDIA. EL GANADOR SE MOSTRARA PASADOS LOS CINCO 
TIEMPOS DE CADA USUARIO.
EL PROCEDIMIENTO RECIBIRA COMO PARAMETROS LOS SIGUIENTES DATOS :
    ID DEL CAMPEONATO, ID DEL USUARIO QUE INSERTA EL TIEMPO, EL SCRAMBLE, NOMBRE DE 
    LA CATEGORÍA (E.G., 3x3x3, 2x2x2), MINUTOS DEL TIEMPO REGISTRADO, SEGUNDOS DEL 
    TIEMPO REGISTRADO, ID DEL PROMEDIO (AVERAGE) AL QUE PERTENECE EL TIEMPO, 
    COMENTARIOS ADICIONALES (OPCIONAL).
    
ANTES DE INSERTAR LOS TIEMPOS, SE DEBEN VERIFICAR LAS SIGUIENTES CONDICIONES:
    - VERIFICAR QUE EL CAMPEONATO ESPECIFICADO EXISTE.
    - VERIFICAR QUE EL USUARIO ESTÁ REGISTRADO EN EL CAMPEONATO.
    - VERIFICAR QUE EL NOMBRE DE LA CATEGORÍA ESPECIFICADA PERTENECE AL CAMPEONATO.
    - SI EL ID DEL AVERAGE NO EXISTE, CREAR UNO NUEVO.
    - VERIFICAR QUE EL ID DEL AVERAGE ES ÚNICO PARA CADA USUARIO EN UNA CATEGORÍA 
      DEL CAMPEONATO. ES DECIR, UN USUARIO SOLO PUEDE TENER UN ID DE AVERAGE 
      POR CADA CATEGORÍA EN EL CAMPEONATO.
    - VERIFICAR QUE EL USUARIO NO HA REGISTRADO MÁS DE CINCO TIEMPOS EN LA 
      CATEGORÍA. SI YA HA REGISTRADO CINCO TIEMPOS, NO SE LE PERMITIRÁ REGISTRAR MÁS.
      
SI ALGUNA DE ESTAS CONDICIONES NO SE CUMPLE, SE ENVIARÁ UN MENSAJE DE ERROR 
CORRESPONDIENTE.*/
/* CREAR UNA FUNCION QUE DEVUELVA EL NOMBRE DEL USUARIO QUE TENGA LA MENOR
MEDIA DE TIEMPOS DE UN CAMPEONATO DE UNA CATEGORIA */
CREATE OR REPLACE FUNCTION nomUserMinTime (P_ID_CHAMP CHAMPIONSHIP.ID_CHAMP%TYPE,
    P_ID_CATEGORY CUBE_TYPE.ID_TYPE%TYPE)
RETURN VARCHAR2
IS 
    V_NAME CUBE_USERS.NAME_USER%TYPE;
BEGIN
    SELECT NAME_USER INTO V_NAME FROM CUBE_USERS
            WHERE ID_USER = (
                SELECT DISTINCT ID_USER
                FROM SCRAMBLE
                WHERE ID_AVERAGE = (
                    SELECT DISTINCT ID_AVERAGE
                    FROM AVERAGE
                    WHERE AVG_MINUTES * 60 + AVG_SECONDS = (
                        SELECT MIN(AVG_MINUTES * 60 + AVG_SECONDS)
                        FROM AVERAGE
                        WHERE ID_AVERAGE IN ( 
                            SELECT DISTINCT ID_AVERAGE
                            FROM SCRAMBLE
                            WHERE ID_TYPE = P_ID_CATEGORY
                            AND ID_CHAMP = P_ID_CHAMP
                        )
                    )
                )
            );
    RETURN V_NAME;
END nomUserMinTime;
/

CREATE OR REPLACE PROCEDURE insertTimesChamp (
    P_ID_CHAMP CHAMPIONSHIP.ID_CHAMP%TYPE,
    P_ID_USER USER_CHAMP_COMPETE.ID_USER%TYPE, 
    P_SCRAMBLE SCRAMBLE.DESCRIPTION_SCRAMBLE%TYPE,
    P_NAME_CATEGORY CUBE_TYPE.NAME_TYPE%TYPE,
    P_MINUTES SCRAMBLE.MINUTES1%TYPE,
    P_SECONDS SCRAMBLE.SECONDS1%TYPE,
    P_ID_AVERAGE SCRAMBLE.ID_AVERAGE%TYPE,
    P_COMMENTS SCRAMBLE.COMMENTS1%TYPE DEFAULT null
) 
IS
    V_ID_CATEGORY CUBE_TYPE.ID_TYPE%TYPE;
    V_WINNER CUBE_USERS.NAME_USER%TYPE;
    V_MIN_TIME NUMBER;
    V_MAX_ID_SCRAMBLE SCRAMBLE.ID_SCRAMBLE%TYPE;
    V_EXISTS AVERAGE.ID_AVERAGE%TYPE;
    V_SCRAMBLE_COUNT SCRAMBLE.ID_SCRAMBLE%TYPE;
    V_AVG_EXISTS SCRAMBLE.ID_AVERAGE%TYPE;
    CURSOR C_CHECK_CHAMP IS
        SELECT ID_CHAMP FROM CHAMPIONSHIP WHERE ID_CHAMP = P_ID_CHAMP;
    V_CH_CHAMP C_CHECK_CHAMP%ROWTYPE;
    
    CURSOR C_CHECK_USER_CHAMP IS
        SELECT ID_USER FROM USER_CHAMP_COMPETE WHERE ID_CHAMP = P_ID_CHAMP 
                                                AND ID_USER = P_ID_USER;
    V_CH_USERCHAMP C_CHECK_USER_CHAMP%ROWTYPE;
    
    CURSOR C_CHECK_CATEGORY IS
        SELECT ID_TYPE FROM CUBE_CHAMP_PERTENECE WHERE ID_CHAMP = P_ID_CHAMP AND ID_TYPE = 
            (SELECT ID_TYPE FROM CUBE_TYPE WHERE NAME_TYPE = P_NAME_CATEGORY);
    V_CH_CATEGORY C_CHECK_CATEGORY%ROWTYPE;
    
    CURSOR C_CHECK_IDAVERAGE IS
        SELECT ID_AVERAGE FROM SCRAMBLE WHERE ID_CHAMP = P_ID_CHAMP 
                            AND ID_USER = P_ID_USER AND ID_TYPE = V_ID_CATEGORY;
    V_CH_IDAVERAGE C_CHECK_IDAVERAGE%ROWTYPE;
BEGIN

   -- VERIFICAR SI EL ID DE CAMPEONATO EXISTE
    OPEN C_CHECK_CHAMP;
    FETCH C_CHECK_CHAMP INTO V_CH_CHAMP;
    IF C_CHECK_CHAMP%NOTFOUND THEN
        CLOSE C_CHECK_CHAMP;
        RAISE_APPLICATION_ERROR(-20001, 'Error: El ID de campeonato no existe.');
    END IF;
    CLOSE C_CHECK_CHAMP;
    
    -- VERIFICAR SI EL ID DE USUARIO PERTENECE AL CAMPEONATO
    OPEN C_CHECK_USER_CHAMP;
    FETCH C_CHECK_USER_CHAMP INTO V_CH_USERCHAMP;
    IF C_CHECK_USER_CHAMP%NOTFOUND THEN
        CLOSE C_CHECK_USER_CHAMP;
        RAISE_APPLICATION_ERROR(-20002, 'Error: El usuario no pertenece al campeonato especificado.');
    END IF;
    CLOSE C_CHECK_USER_CHAMP;
    
    -- VERIFICAR SI LA CATEGORÍA PERTENECE AL CAMPEONATO
    OPEN C_CHECK_CATEGORY;
    FETCH C_CHECK_CATEGORY INTO V_CH_CATEGORY;
    IF C_CHECK_CATEGORY%NOTFOUND THEN
        CLOSE C_CHECK_CATEGORY;
        RAISE_APPLICATION_ERROR(-20003, 'Error: La categoría no pertenece al 
            campeonato especificado.');
    END IF;
    CLOSE C_CHECK_CATEGORY;
    
    -- OBTENER ID_TYPE PARA LA CATEGORÍA
    SELECT ID_TYPE INTO V_ID_CATEGORY FROM CUBE_TYPE WHERE NAME_TYPE LIKE P_NAME_CATEGORY;
    
   -- VERIFICAR SI EL USUARIO YA TIENE UN ID_AVERAGE EN ESTA CATEGORÍA
   OPEN C_CHECK_IDAVERAGE;
    FETCH C_CHECK_IDAVERAGE INTO V_CH_IDAVERAGE;
    IF V_CH_IDAVERAGE.ID_AVERAGE != P_ID_AVERAGE THEN
        CLOSE C_CHECK_IDAVERAGE;
        RAISE_APPLICATION_ERROR(-20004, 'Error: El usuario ya tiene un ID_AVERAGE 
            distinto en esta categoría para este campeonato.');
    END IF;
    CLOSE C_CHECK_IDAVERAGE;
    
    -- VERIFICAR SI EL USUARIO YA TIENE CINCO TIEMPOS EN ESTA CATEGORÍA
    SELECT COUNT(ID_SCRAMBLE) INTO V_SCRAMBLE_COUNT 
    FROM SCRAMBLE 
    WHERE ID_CHAMP = P_ID_CHAMP AND ID_USER = P_ID_USER AND ID_TYPE = V_ID_CATEGORY;
    
    IF V_SCRAMBLE_COUNT >= 5 THEN
        RAISE_APPLICATION_ERROR(-20005, 'Error: El usuario ya tiene cinco tiempos 
            en esta categoría para este campeonato.');
    ELSE IF V_SCRAMBLE_COUNT = 4 THEN 
        -- ACTUALIZAR EL GANADOR DEL CAMPEONATO
        UPDATE CUBE_CHAMP_PERTENECE SET WINNER =
            nomUserMinTime(P_ID_CHAMP, V_ID_CATEGORY) 
        WHERE ID_CHAMP = P_ID_CHAMP AND ID_TYPE = V_ID_CATEGORY;
        
        -- SELECCIONAR EL GANADOR
        SELECT WINNER INTO V_WINNER  FROM CUBE_CHAMP_PERTENECE 
            WHERE ID_CHAMP = P_ID_CHAMP AND ID_TYPE = V_ID_CATEGORY;
        -- MOSTRAR EL GANADOR DEL CAMPEONATO
        DBMS_OUTPUT.PUT_LINE('El ganador del campeonato ' || P_ID_CHAMP || ' es el usuario ' || V_WINNER);
        END IF;
    END IF;
    
    -- SI NO EXISTE EL ID DEL AVERAGE, SE CREARA UNO
    SELECT COUNT(ID_AVERAGE) INTO V_EXISTS FROM AVERAGE WHERE ID_AVERAGE = P_ID_AVERAGE;
    IF V_EXISTS = 0 THEN
        INSERT INTO AVERAGE (ID_AVERAGE, PERIOD_AVG) VALUES (P_ID_AVERAGE, 0);
    END IF;

    -- INSERTAR EL TIEMPO EN LA TABLA SCRAMBLE
    SELECT MAX(ID_SCRAMBLE) INTO V_MAX_ID_SCRAMBLE FROM SCRAMBLE; 
    INSERT INTO SCRAMBLE (ID_SCRAMBLE, ID_USER, ID_TYPE, DESCRIPTION_SCRAMBLE, 
        MINUTES1, SECONDS1, COMMENTS1, ID_AVERAGE, ID_CHAMP, REGISTRATION_DATE)
    VALUES (V_MAX_ID_SCRAMBLE + 1, P_ID_USER, V_ID_CATEGORY, P_SCRAMBLE, P_MINUTES,
        P_SECONDS, P_COMMENTS, P_ID_AVERAGE, P_ID_CHAMP, SYSDATE);
    
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('Error: No se encontraron datos.');
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END insertTimesChamp;
/

INSERT INTO CHAMPIONSHIP VALUES (123, 1, 'TEST CHAMPIONSHIP', 0, 2, '3x3x3 CUBE CATEGORIES.'); 
INSERT INTO CUBE_CHAMP_PERTENECE VALUES (1, 123, null);
INSERT INTO USER_CHAMP_COMPETE VALUES (1, 123);
INSERT INTO USER_CHAMP_COMPETE VALUES (2, 123);

EXECUTE insertTimesChamp(123, 2, 'U´ F´ U R´ U2 F´ U2 F R2', '2x2x2', 1, 53, 40);
EXECUTE insertTimesChamp(123, 2, 'F´ R U2 R2 U´ R F´ U R2', '2x2x2', 1, 23, 40);
EXECUTE insertTimesChamp(123, 2, 'R´ F2 R U´ R F U´ F´ U', '2x2x2', 1, 25, 40);
EXECUTE insertTimesChamp(123, 2, 'R´ F2 R´ U´ F´ U2 F2 R U´', '2x2x2', 0, 54, 40);
EXECUTE insertTimesChamp(123, 2, 'F´ R2 F´ U´ F´ U´ F´ R U', '2x2x2', 1, 32, 40);

EXECUTE insertTimesChamp(123, 1, 'U2 R U2 F´ R´ U2 F´ U2 R', '2x2x2', 0, 59, 41);
EXECUTE insertTimesChamp(123, 1, 'F U´ F R2 F U´ F R2 F´', '2x2x2', 1, 12, 41);
EXECUTE insertTimesChamp(123, 1, 'F R´ U´ R2 F´ U2 F´ U2 R', '2x2x2', 0, 35, 41);
EXECUTE insertTimesChamp(123, 1, 'R F´ R2 U2 R U2 R´ F U2', '2x2x2', 0, 24, 41);
EXECUTE insertTimesChamp(123, 1, 'F R2 U´ R U F U´ F2 R2', '2x2x2', 0, 25, 41);


/* 5) CREAR UN PROCEDIMIENTO LLAMADO "proInsertUpdate" QUE TOME EL PARAMETRO 
EL NOMBRE DEL USUARIO, MAIL, PASSWORD PARA INSERTAR UN NUEVO USUARIO EN CASO 
DE QUE NO EXISTA O ACTUALIZAR LOS DATOS DEL USUARIO*/
CREATE OR REPLACE PROCEDURE proInsertUpdate (P_NOMBRE CUBE_USERS.NAME_USER%TYPE,
    P_MAIL CUBE_USERS.MAIL%TYPE, P_PASSWORD CUBE_USERS.PASSWORD_USER%TYPE)
IS
    V_COUNT CUBE_USERS.NAME_USER%TYPE;
    V_MAXID CUBE_USERS.ID_USER%TYPE;
BEGIN
    SELECT COUNT(ID_USER) INTO V_COUNT FROM CUBE_USERS 
    WHERE NAME_USER LIKE P_NOMBRE;
    IF V_COUNT > 0 THEN     
        UPDATE CUBE_USERS SET MAIL = P_MAIL, PASSWORD_USER = P_PASSWORD
            WHERE NAME_USER LIKE P_NOMBRE;
        DBMS_OUTPUT.PUT_LINE('SE HA ACTUALIZADO CORRECTAMENTE');
    ELSE 
        SELECT MAX(ID_USER) INTO V_MAXID FROM CUBE_USERS;
        INSERT INTO CUBE_USERS VALUES ((V_MAXID + 1), P_NOMBRE, P_PASSWORD, 0, 'USER', 
            P_MAIL, SYSDATE);
        DBMS_OUTPUT.PUT_LINE('SE HA INSERTADO CORRECTAMENTE');
    END IF;
    
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('Error: No se encontraron datos.');
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
    
END proInsertUpdate;
/
EXECUTE proInsertUpdate ('blue985', 'blue985@gmail.com', 'hola');


/* 6) CREAR UN TRIGGER QUE SE ACTIVARA DESPUES DE CADA INSERCION, MODIFICACION O 
ELIMINACION EN LA TABLA CUBE_USERS. 
SE LLAMARA A UNA FUNCION QUE RETORNARA UN TIPO DE DATO "CLOB", YA QUE LA CADENA 
ALMACENARA VARIOS CARACTERES, QUE LISTARA LOS DATOS DEL USUARIO, ENTRE ELLOS
EL NOMBRE, EL NIVEL, EL ROL, LA FECHA DE CUANDO SE REGISTRO, MAIL Y LA CONTRASEÑA,
QUE MOSTRAR LOS DATOS DE LOS USUARIOS QUE EXISTEN.*/
CREATE OR REPLACE FUNCTION listUsers 
RETURN CLOB
IS
    CURSOR CUR IS SELECT NAME_USER, MAIL, PASSWORD_USER, LEVEL_USER, ROLE_USER,
        REGISTRATION_DATE FROM CUBE_USERS;
    V_REG CUR%ROWTYPE;
    V_TEXTO CLOB := EMPTY_CLOB();    
BEGIN
    v_texto := '';
    OPEN CUR;
    FETCH CUR INTO V_REG;    
    WHILE CUR%FOUND LOOP
        V_TEXTO := V_TEXTO || 
        '| ' || V_REG.NAME_USER || '  | ' || V_REG.LEVEL_USER || 
                '| ' || V_REG.ROLE_USER || ' | ' || V_REG.REGISTRATION_DATE || ' | ' 
                || V_REG.MAIL || ' | ' || V_REG.PASSWORD_USER || ' |' ;
        V_TEXTO := V_TEXTO || CHR(10) || 
        '--------------------------------------------------------------------------------' || CHR(10);
        FETCH CUR INTO V_REG; 
    END LOOP;    
    CLOSE CUR;
    RETURN V_TEXTO;
END listUsers;
/



CREATE OR REPLACE TRIGGER triVisualizarUsuariosStmt
AFTER INSERT OR UPDATE OR DELETE ON CUBE_USERS
BEGIN
    DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');
    DBMS_OUTPUT.PUT_LINE('| NAME  | LEVEL | ROLE | REGISTRATION |          MAIL         |    PASSWORD    |');
    DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');
    -- FUNCION PARA MOSTRAR LA LISTA DE USUARIOS
    DBMS_OUTPUT.PUT_LINE(listUsers);
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

INSERT INTO CUBE_USERS VALUES (1101,'Hfsda910','H',0,'USER',  
    'H9dsa10@gmail.com',TO_DATE('27/03/2024', 'DD/MM/YYYY'));
UPDATE CUBE_USERS SET LEVEL_USER = 1 WHERE ID_USER = 1101;    
DELETE FROM CUBE_USERS WHERE ID_USER = 1101;



/* 7) CREAR UN PROCEDIMIENTO LLAMADO "getUserAvgScore" QUE DETERMINE SI UN USUARIO
HA REGISTRADO EL MEJOR TIEMPO EN UNA CATEGORIA ESPECIFICA. SE LE PASARA COMO 
PARAMETROS EL NOMBRE DEL USUARIO PARA EL CUAL SE ESTA VERIFICANDO EL TIEMPO
Y LA CATEGORIA.
SE GESTIONARA QUE EL NOMBRE Y LA CATEGORIA SI EXISTAN, ADEMAS DE POSIBLES ERRORES*/
CREATE OR REPLACE PROCEDURE getUserAvgScore (
    P_NAME_USER CUBE_USERS.NAME_USER%TYPE,
    P_CATEGORY CUBE_TYPE.NAME_TYPE%TYPE
) 
IS
    V_ID_USER CUBE_USERS.ID_USER%TYPE;
    V_BEST_TIME NUMBER;
    V_USER_BEST_TIME NUMBER;
    
    CURSOR C_CHECK_CATEGORY IS
        SELECT NAME_TYPE FROM CUBE_TYPE
        WHERE NAME_TYPE = P_CATEGORY;
    V_CH_CATEGORY C_CHECK_CATEGORY%ROWTYPE;
    
    CURSOR C_CHECK_USER IS
        SELECT NAME_USER FROM CUBE_USERS
        WHERE NAME_USER = P_NAME_USER;
    V_CH_USER C_CHECK_USER%ROWTYPE;
BEGIN

   -- VERIFICAR SI EL NOMBRE DE LA CATEGORIA EXISTE
    OPEN C_CHECK_CATEGORY;
    FETCH C_CHECK_CATEGORY INTO V_CH_CATEGORY;
    IF C_CHECK_CATEGORY%NOTFOUND THEN
        CLOSE C_CHECK_CATEGORY;
        RAISE_APPLICATION_ERROR(-20011, 'Error: El nombre de la categoria no existe.');
    END IF;
    CLOSE C_CHECK_CATEGORY;
    
    -- VERIFICAR SI EL NOMBRE DEL USUARIO EXISTE
    OPEN C_CHECK_USER;
    FETCH C_CHECK_USER INTO V_CH_USER;
    IF C_CHECK_USER%NOTFOUND THEN
        CLOSE C_CHECK_USER;
        RAISE_APPLICATION_ERROR(-20012, 'Error: El nombre del usuario no esta registrado.');
    END IF;
    CLOSE C_CHECK_USER;

    SELECT ID_USER INTO V_ID_USER FROM CUBE_USERS 
        WHERE NAME_USER = P_NAME_USER;
        
    -- OBTENER EL MEJOR TIEMPO DE LA CATEGORIA ESPECIFICADA
    SELECT MIN(MINUTES1 * 60 + SECONDS1)
    INTO V_BEST_TIME
    FROM SCRAMBLE
    WHERE ID_TYPE = (SELECT ID_TYPE FROM CUBE_TYPE 
                    WHERE NAME_TYPE = P_CATEGORY);

    -- OBTENER EL MEJOR TIEMPO DEL USUARIO EN LA CATEGORIA ESPECIFICADA
    SELECT MIN(MINUTES1 * 60 + SECONDS1)
    INTO V_USER_BEST_TIME
    FROM SCRAMBLE
    WHERE ID_TYPE = (SELECT ID_TYPE FROM CUBE_TYPE 
                    WHERE NAME_TYPE = P_CATEGORY)
    AND ID_USER = V_ID_USER;

    -- COMPARAR LOS TIEMPOS
    IF V_USER_BEST_TIME = V_BEST_TIME THEN
        DBMS_OUTPUT.PUT_LINE('EL USUARIO ' || P_NAME_USER || ' TIENE EL MEJOR TIEMPO EN LA CATEGORIA ' 
            || P_CATEGORY);
    ELSE
        DBMS_OUTPUT.PUT_LINE('EL USUARIO ' || P_NAME_USER || ' NO TIENE EL MEJOR TIEMPO EN LA CATEGORIA ' 
            || P_CATEGORY);
    END IF;

    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('Error: No se encontraron datos.');
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END getUserAvgScore;
/
-- MEJOR TIEMPO
EXECUTE getUserAvgScore('CubeChamp', '2x2x2'); 
EXECUTE getUserAvgScore('ElJoaki', '2x2x2'); 


/* 8) CREAR UN PROCEDIMIENTO LLAMADO "showUserInChamps" QUE TOME COMO 
PARÁMETROS EL NOMBRE DEL USUARIO. EL PROCEDIMIENTO LLAMARÁ A LAS FUNCIONES 
"getUserWins" Y "getUserParticipations" PARA CONTAR EL NÚMERO DE VECES QUE UN 
USUARIO HA GANADO UN CAMPEONATO Y EL NÚMERO DE VECES QUE HA PARTICIPADO EN UN 
CAMPEONATO, RESPECTIVAMENTE. LUEGO, LLAMARÁ A LA FUNCIÓN "getUserWinPorcentage" 
PARA CALCULAR EL PORCENTAJE DE VICTORIAS DEL USUARIO. EL PROCEDIMIENTO MOSTRARÁ 
EL NÚMERO DE VECES QUE EL USUARIO HA GANADO, EL NÚMERO DE VECES QUE HA PARTICIPADO 
Y EL PORCENTAJE DE VICTORIAS.

EL PROCEDIMIENTO TAMBIÉN VERIFICARÁ SI EL USUARIO INTRODUCIDO ESTÁ REGISTRADO EN 
LA BASE DE DATOS Y SI PERTENECE A ALGÚN CAMPEONATO. SI NO SE CUMPLE ALGUNA DE 
ESTAS CONDICIONES, SE MOSTRARÁ UN MENSAJE DE ERROR CORRESPONDIENTE.*/
CREATE OR REPLACE FUNCTION getUserWins (P_NAME CUBE_USERS.NAME_USER%TYPE)
RETURN NUMBER
IS
    V_COUNT NUMBER;
BEGIN
    SELECT COUNT(ID_CHAMP) INTO V_COUNT FROM CUBE_CHAMP_PERTENECE 
    WHERE WINNER = P_NAME;
    RETURN V_COUNT;
END getUserWins;
/

CREATE OR REPLACE FUNCTION getUserParticipations (P_NAME CUBE_USERS.NAME_USER%TYPE)
RETURN NUMBER
IS
    V_COUNT NUMBER;
BEGIN
    SELECT COUNT(ID_CHAMP) INTO V_COUNT FROM USER_CHAMP_COMPETE 
    WHERE ID_USER = (SELECT ID_USER FROM CUBE_USERS 
                    WHERE NAME_USER = P_NAME);
    RETURN V_COUNT;
END getUserParticipations;
/

CREATE OR REPLACE FUNCTION getUserWinPorcentage(P_NAME CUBE_USERS.NAME_USER%TYPE)
RETURN NUMBER
IS
    V_WINS NUMBER;
    V_PARTICIPATIONS NUMBER;
    V_PORCENTAGE NUMBER;
BEGIN
    V_WINS := getUserWins(P_NAME);
    V_PARTICIPATIONS := getUserParticipations(P_NAME);
    
    V_PORCENTAGE := (V_WINS / V_PARTICIPATIONS) * 100;
    
    RETURN V_PORCENTAGE;
END getUserWinPorcentage;
/


CREATE OR REPLACE PROCEDURE showUserInChamps (P_NAME_USER CUBE_USERS.NAME_USER%TYPE)
IS
    CURSOR C_CHECK_USER IS
        SELECT NAME_USER FROM CUBE_USERS 
        WHERE NAME_USER = P_NAME_USER;
    V_CH_USER C_CHECK_USER%ROWTYPE;
    
    CURSOR C_CHECK_USER_CHAMP IS
        SELECT ID_USER FROM USER_CHAMP_COMPETE 
        WHERE ID_USER = (SELECT ID_USER FROM CUBE_USERS 
                        WHERE NAME_USER = P_NAME_USER);
    V_CH_USERCHAMP C_CHECK_USER_CHAMP%ROWTYPE;
   
BEGIN
    -- VERIFICAR SI EL USUARIO INTRODUCIDO ESTA REGISTRADO EN LA BASE DE DATOS
    OPEN C_CHECK_USER;
    FETCH C_CHECK_USER INTO V_CH_USER;
    IF C_CHECK_USER%NOTFOUND THEN
        CLOSE C_CHECK_USER;
        RAISE_APPLICATION_ERROR(-20001, 'Error: El usuario no esta registrado.');
    END IF;
    CLOSE C_CHECK_USER;

    -- VERIFICAR SI EL USUARIO PERTENECE A ALGUN CAMPEONATO
    OPEN C_CHECK_USER_CHAMP;
    FETCH C_CHECK_USER_CHAMP INTO V_CH_USERCHAMP;
    IF C_CHECK_USER_CHAMP%NOTFOUND THEN
        CLOSE C_CHECK_USER_CHAMP;
        RAISE_APPLICATION_ERROR(-20002, 'Error: El usuario no pertenece a ningun campeonato.');
    END IF;
    CLOSE C_CHECK_USER_CHAMP;
    
    -- MOSTRAR
    DBMS_OUTPUT.PUT_LINE(P_NAME_USER || ' ha participado en ' || getUserParticipations(P_NAME_USER)
    || ' campeonato/s. Y ha ganado ' || getUserWins(P_NAME_USER) || ' campeonato/s');
    
    DBMS_OUTPUT.PUT_LINE('Porcentaje de victorias: ' || TO_CHAR(getUserWinPercentage(P_NAME_USER), '999.99') || '%');

    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('Error: No se encontraron datos.');
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);

END showUserInChamps;
/

EXECUTE showUserInChamps('blue985'); 
EXECUTE showUserInChamps('AlexMx'); 
-- NO PERTENECE A NINGUN CAMPEONATO
EXECUTE showUserInChamps('Cubero89');
-- NO EXISTE
EXECUTE showUserInChamps('Cubero'); 



/* 9) CREAR UN PROCEDIMIENTO LLAMADO "showAvgPbWorstInChampCategory" QUE MUESTRE 
LA MEDIA, EL MEJOR TIEMPO Y EL PEOR TIEMPO DE UN USUARIO EN UNA CATEGORÍA 
ESPECÍFICA DE UN CAMPEONATO. EL PROCEDIMIENTO DEBE RECIBIR COMO PARÁMETROS EL 
NOMBRE DEL USUARIO, LA CATEGORÍA Y EL ID DEL CAMPEONATO. 
ADEMAS, DEBE VERIFICAR LA EXISTENCIA DEL USUARIO, LA CATEGORÍA Y EL CAMPEONATO, 
SI NO, LANZAR UN MENSAJE DE ERROR. */
CREATE OR REPLACE procedure showAvgPbWorstInChampCategory 
    (P_NAME_USER CUBE_USERS.NAME_USER%TYPE, 
    P_CATEGORY CUBE_TYPE.NAME_TYPE%TYPE, 
    P_ID_CHAMP CHAMPIONSHIP.ID_CHAMP%TYPE)
IS
    V_PB_MINUTES AVERAGE.PB_MINUTES%TYPE;
    V_PB_SECONDS AVERAGE.PB_SECONDS%TYPE;
    V_WORST_MINUTES AVERAGE.WORST_MINUTES%TYPE;
    V_WORST_SECONDS AVERAGE.WORST_SECONDS%TYPE;
    V_AVG_MINUTES AVERAGE.AVG_MINUTES%TYPE;
    V_AVG_SECONDS AVERAGE.AVG_SECONDS%TYPE;
    V_ID_CATEGORY CUBE_CHAMP_PERTENECE.ID_TYPE%TYPE;
    V_ID_USER CUBE_USERS.ID_USER%TYPE;
    
    CURSOR C_CHECK_CHAMP IS
        SELECT ID_CHAMP FROM CHAMPIONSHIP 
        WHERE ID_CHAMP = P_ID_CHAMP;
    V_CH_CHAMP C_CHECK_CHAMP%ROWTYPE;
    
    CURSOR C_CHECK_USER_CHAMP IS
        SELECT ID_USER FROM USER_CHAMP_COMPETE 
        WHERE ID_CHAMP = P_ID_CHAMP AND ID_USER = V_ID_USER;
    V_CH_USERCHAMP C_CHECK_USER_CHAMP%ROWTYPE;
    
    CURSOR C_CHECK_CATEGORY IS
        SELECT ID_TYPE FROM CUBE_CHAMP_PERTENECE 
        WHERE ID_CHAMP = P_ID_CHAMP AND ID_TYPE = 
            (SELECT ID_TYPE FROM CUBE_TYPE 
            WHERE NAME_TYPE = P_CATEGORY);
    V_CH_CATEGORY C_CHECK_CATEGORY%ROWTYPE;
    
    CURSOR C_CHECK_USER IS
        SELECT NAME_USER FROM CUBE_USERS
        WHERE NAME_USER = P_NAME_USER;
    V_CH_USER C_CHECK_USER%ROWTYPE;
BEGIN
    -- VERIFICAR SI EL ID DE CAMPEONATO EXISTE
    OPEN C_CHECK_CHAMP;
    FETCH C_CHECK_CHAMP INTO V_CH_CHAMP;
    IF C_CHECK_CHAMP%NOTFOUND THEN
        CLOSE C_CHECK_CHAMP;
        RAISE_APPLICATION_ERROR(-20001, 'Error: El ID de campeonato no existe.');
    END IF;
    CLOSE C_CHECK_CHAMP;
    
    -- VERIFICAR SI LA CATEGORÍA PERTENECE AL CAMPEONATO
    OPEN C_CHECK_CATEGORY;
    FETCH C_CHECK_CATEGORY INTO V_CH_CATEGORY;
    IF C_CHECK_CATEGORY%NOTFOUND THEN
        CLOSE C_CHECK_CATEGORY;
        RAISE_APPLICATION_ERROR(-20002, 'Error: La categoría no pertenece al campeonato especificado.');
    END IF;
    CLOSE C_CHECK_CATEGORY;
    
    -- VERIFICAR SI EL NOMBRE DEL USUARIO EXISTE
    OPEN C_CHECK_USER;
    FETCH C_CHECK_USER INTO V_CH_USER;
    IF C_CHECK_USER%NOTFOUND THEN
        CLOSE C_CHECK_USER;
        RAISE_APPLICATION_ERROR(-2003, 'Error: El nombre del usuario no esta registrado.');
    END IF;
    CLOSE C_CHECK_USER;

    -- SELECCIONAR EL ID DEL USUARIO
    SELECT ID_USER INTO V_ID_USER 
    FROM CUBE_USERS WHERE NAME_USER = P_NAME_USER;
    
    -- VERIFICAR SI EL ID DE USUARIO PERTENECE AL CAMPEONATO
    OPEN C_CHECK_USER_CHAMP;
    FETCH C_CHECK_USER_CHAMP INTO V_CH_USERCHAMP;
    IF C_CHECK_USER_CHAMP%NOTFOUND THEN
        CLOSE C_CHECK_USER_CHAMP;
        RAISE_APPLICATION_ERROR(-20004, 'Error: El usuario no pertenece al campeonato especificado.');
    END IF;
    CLOSE C_CHECK_USER_CHAMP;
    
    -- SELECCIONAR EL ID DE LA CATEGORIA
    SELECT ID_TYPE INTO V_ID_CATEGORY 
    FROM CUBE_TYPE WHERE NAME_TYPE = P_CATEGORY;
    
    -- SELECCIONAR EL MEJOR TIEMPO
    SELECT PB_MINUTES, PB_SECONDS 
    INTO V_PB_MINUTES, V_PB_SECONDS
    FROM AVERAGE 
    GROUP BY PB_SECONDS,PB_MINUTES
    HAVING (PB_MINUTES * 60 + PB_SECONDS) = (SELECT MIN(MINUTES1 * 60 + SECONDS1) 
                        FROM SCRAMBLE 
                        WHERE ID_CHAMP = P_ID_CHAMP AND ID_TYPE = V_ID_CATEGORY
                        AND ID_USER = V_ID_USER);
    
    -- SELECCIONAR EL PEOR TIEMPO
    SELECT WORST_MINUTES, WORST_SECONDS 
    INTO V_WORST_MINUTES, V_WORST_SECONDS
    FROM AVERAGE 
    GROUP BY WORST_SECONDS,WORST_MINUTES
    HAVING (WORST_MINUTES * 60 + WORST_SECONDS) = (SELECT MAX(MINUTES1 * 60 + SECONDS1) 
                        FROM SCRAMBLE 
                        WHERE ID_CHAMP = P_ID_CHAMP AND ID_TYPE = V_ID_CATEGORY 
                        AND ID_USER = V_ID_USER);
     
    -- SELECCIONAR LA MEDIA                   
    SELECT AVG_MINUTES, AVG_SECONDS 
    INTO V_AVG_MINUTES, V_AVG_SECONDS
    FROM AVERAGE 
    WHERE ID_AVERAGE = (SELECT DISTINCT ID_AVERAGE 
                        FROM SCRAMBLE 
                        WHERE ID_CHAMP = P_ID_CHAMP AND ID_TYPE = V_ID_CATEGORY 
                        AND ID_USER = V_ID_USER);
     
    DBMS_OUTPUT.PUT_LINE('******* TIEMPOS DE ' || P_NAME_USER || ' *******');
    DBMS_OUTPUT.PUT_LINE('EL MEJOR TIEMPO : ' || V_PB_MINUTES || ':' || V_PB_SECONDS);
    DBMS_OUTPUT.PUT_LINE('EL PEOR TIEMPO : ' || V_WORST_MINUTES || ':' || V_WORST_SECONDS);
    DBMS_OUTPUT.PUT_LINE('LA MEDIA DE TIEMPOS : ' || V_AVG_MINUTES || ':' || V_AVG_SECONDS);
    
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('Error: No se encontraron datos.');
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);

END showAvgPbWorstInChampCategory;
/

EXECUTE showAvgPbWorstInChampCategory('blue985', '2x2x2', 123);