create or replace procedure "PROCEDURE_MAESTRO"
(iduser IN NUMBER)
is
    pokeid NUMBER;
    pokenombre
begin
SELECT id_pokemon AS "ID", nombre_pokemon AS "POKEMON"
   FROM pokemon
MINUS
SELECT id_pokemon, nombre_pokemon
   FROM pokemon, salvaje, pokedex
   WHERE usuario_id = 3 AND id_salvaje = salvaje_id AND pokemon_id = id_pokemon AND estado = 1;
end;​

create or replace trigger "USUARIO_T1"
AFTER
update on "USUARIO"
for each row
DECLARE
    achiev NUMBER;
begin
SELECT count(*) INTO achiev FROM adquisicion WHERE usuario_id = :new.id_usuario AND logro_id = 1;
IF :new.pokemones = 6 THEN
    IF achiev = 0 THEN
       INSERT INTO adquisicion VALUES(1, :new.id_usuario, 3);
    END IF;
END IF;
end;​

create or replace procedure "ACTUALIZAR_POKEDEX"
(iduser IN NUMBER,
idsalvaje IN NUMBER,
capturado IN NUMBER)
is
    antes NUMBER;
    despues NUMBER;
begin
    SELECT count(*) INTO antes
    FROM (SELECT id_pokemon FROM pokemon
        MINUS
        SELECT id_pokemon FROM pokemon, salvaje, pokedex
        WHERE usuario_id = iduser AND id_salvaje = salvaje_id AND pokemon_id = id_pokemon AND estado = 1);
    UPDATE pokedex SET estado = capturado WHERE usuario_id = iduser AND salvaje_id = idsalvaje;
    SELECT count(*) INTO despues
    FROM (SELECT id_pokemon FROM pokemon
        MINUS
        SELECT id_pokemon FROM pokemon, salvaje, pokedex
        WHERE usuario_id = iduser AND id_salvaje = salvaje_id AND pokemon_id = id_pokemon AND estado = 1);
IF antes <> despues THEN
    UPDATE usuario SET pokemones = pokemones + 1 WHERE id_usuario = iduser;
END IF;
end;​

create or replace procedure "ACTUALIZAR_SLOTS"
(iduser IN NUMBER,
iditem IN NUMBER,
nuevaquantity IN NUMBER)
is
    old NUMBER;
    idmochila NUMBER;
    total NUMBER;
begin
IF nuevaquantity < 0 THEN RAISE_APPLICATION_ERROR(-20003, 'Nueva cantidad negativa.');
ELSE
SELECT id_mochila, peso INTO idmochila, total FROM mochila WHERE usuario_id = iduser;
SELECT cantidad INTO old FROM slots WHERE item_id = iditem AND mochila_id = idmochila;
IF total - old + nuevaquantity < 101 THEN UPDATE mochila SET peso = total - old + nuevaquantity WHERE id_mochila = idmochila;
                                          UPDATE slots SET cantidad = nuevaquantity WHERE mochila_id = idmochila AND item_id = iditem;
ELSE RAISE_APPLICATION_ERROR(-20002, 'Cantidad invalida.');
    END IF;
END IF;
end;​

create or replace procedure "ELIMINAR_SLOT"
(iduser  NUMBER,
iditem  NUMBER)
is
    quantity NUMBER;
    idmochila NUMBER;
    total NUMBER;
begin
SELECT id_mochila, peso INTO idmochila, total FROM mochila WHERE usuario_id = iduser;
SELECT cantidad INTO quantity FROM slots WHERE mochila_id = idmochila AND item_id = iditem;
UPDATE mochila SET peso = total - quantity WHERE id_mochila = idmochila;
DELETE FROM slots WHERE mochila_id = idmochila AND item_id = iditem;
end;​

create or replace procedure "INSERTAR_A_MOCHILA"
(iduser IN NUMBER,
iditem IN NUMBER,
quantity IN NUMBER)
is
    idmochila NUMBER;
    total NUMBER;
begin
SELECT id_mochila, peso into idmochila, total FROM mochila WHERE usuario_id = iduser;
IF quantity > 101 THEN
    RAISE_APPLICATION_ERROR (-20001, 'Item utiliza mas del espacio maximo');
ELSIF ((total+quantity) < 101) THEN
    INSERT INTO SLOTS VALUES (idmochila, iditem, quantity);
    UPDATE mochila SET peso = total + quantity WHERE id_mochila = idmochila;
ELSE
    RAISE_APPLICATION_ERROR (-20000, 'Tamaño de mochila excedido');
END IF;
end;​

create or replace procedure "INSERTAR_A_POKEDEX"
(iduser IN VARCHAR2,
salvajeid IN VARCHAR2,
capturado IN VARCHAR2)
is
    antes NUMBER;
    despues NUMBER;
begin
    SELECT count(*) INTO antes
    FROM (SELECT id_pokemon FROM pokemon
        MINUS
        SELECT id_pokemon FROM pokemon, salvaje, pokedex
        WHERE usuario_id = iduser AND id_salvaje = salvaje_id AND pokemon_id = id_pokemon AND estado = 1);
    INSERT INTO pokedex VALUES (iduser, salvajeid, capturado);
    SELECT count(*) INTO despues
    FROM (SELECT id_pokemon FROM pokemon
        MINUS
        SELECT id_pokemon FROM pokemon, salvaje, pokedex
        WHERE usuario_id = iduser AND id_salvaje = salvaje_id AND pokemon_id = id_pokemon AND estado = 1);
IF antes <> despues THEN
    UPDATE usuario SET pokemones = pokemones + 1 WHERE id_usuario = iduser;
END IF;
end;​

create or replace procedure "PROCEDURE_MAESTRO"
(iduser IN NUMBER)
is
    tabla SYS_REFCURSOR;
begin
open tabla FOR
SELECT id_pokemon, nombre_pokemon
   FROM pokemon
MINUS
SELECT id_pokemon, nombre_pokemon
   FROM pokemon, salvaje, pokedex
   WHERE usuario_id = 3 AND id_salvaje = salvaje_id AND pokemon_id = id_pokemon AND estado = 1;
end;​

create or replace procedure "REGISTRAR_USUARIO"
(nombre_us IN VARCHAR2,
apellidos_us IN VARCHAR2,
edad_us IN NUMBER,
mail_us IN VARCHAR2,
login_us IN VARCHAR2,
pass_us IN VARCHAR2)
is
    iduser NUMBER;
begin
INSERT INTO usuario (nombre, apellidos, edad, mail, fecha_registro, login, password, pokemones)
VALUES (nombre_us, apellidos_us, edad_us, mail_us, current_date, login_us, pass_us, 0);

SELECT id_usuario INTO iduser FROM usuario WHERE nombre = nombre_us;
INSERT INTO MOCHILA (usuario_id, estado, peso) VALUES (iduser, 0, 0);
end;​

CREATE OR REPLACE FORCE VIEW  "VIEW_LOGROS" ("NOMBRE", "NOMBRE_LOGRO") AS
  SELECT nombre AS "Nombre", nombre_logro AS "Logro"
FROM usuario, logros, adquisicion
WHERE (id_usuario = usuario_id) AND (id_logro = logro_id)
ORDER BY nombre;​

CREATE OR REPLACE FORCE VIEW  "VIEW_MAESTROS" ("LOGIN", "NOMBRE", "APELLIDOS", "Total") AS
  SELECT login, nombre, apellidos, SUM(puntos) AS "Total"
FROM usuario, adquisicion, logros, (SELECT DISTINCT usuario_id
                           FROM adquisicion, logros
                           WHERE logro_id = '1') maestros
WHERE maestros.usuario_id = adquisicion.usuario_id AND adquisicion.logro_id = logros.id_logro AND usuario.id_usuario = adquisicion.usuario_id
GROUP BY usuario.login, usuario.nombre, usuario.apellidos;​

CREATE OR REPLACE FORCE VIEW  "VIEW_MOCHILA" ("USUARIOS", "ITEM", "CANTIDAD") AS
  SELECT usuario.login AS Usuarios, items.nombre_item AS Item, slots.cantidad AS Cantidad
FROM usuario, mochila, slots, items
WHERE id_usuario = usuario_id AND id_mochila = mochila_id AND item_id = id_item AND slots.cantidad > 0
ORDER BY login, nombre_item;​

CREATE OR REPLACE FORCE VIEW  "VIEW_POKEDEX" ("NOMBRE", "NOMBRE_POKEMON", "ESTADO") AS
  SELECT nombre AS "Usuario", nombre_pokemon AS "Pokemon", estado AS "Estado"
FROM usuario, salvaje, pokemon, pokedex
WHERE (id_usuario = usuario_id) AND (salvaje_id = id_salvaje) AND (pokemon_id = id_pokemon)
ORDER BY nombre, nombre_pokemon, estado;​