CREATE OR REPLACE
PACKAGE BODY PACKVIDEO AS
  CURSOR cursorPelicula (idPelicula title.title_id%TYPE) IS
      SELECT * FROM title WHERE title_id = idPelicula;
  CURSOR cursorSocios IS SELECT *
                  FROM member;

  --devuelve TRUE si el socio no adeuda peliculas, caso contrario FALSE
  FUNCTION comprobarEstadoSocio(idSocio member.member_id%TYPE) RETURN BOOLEAN AS
  BEGIN
    FOR i IN 1..tablaAlquileres.COUNT LOOP
      IF tablaAlquileres(i).member_id = idSocio AND tablaAlquileres(i).act_ret_date = NULL THEN
        RETURN FALSE;
      END IF;
    END LOOP;
    RETURN TRUE;
  END comprobarEstadoSocio;

  FUNCTION buscarPeliculaPorTitulo(tituloPelicula title.title%TYPE)RETURN NUMBER AS
  idPelicula title.title_id%TYPE;
  BEGIN
    SELECT title_id INTO idPelicula FROM title WHERE title = tituloPelicula;
    RETURN idPelicula;
    EXCEPTION
      WHEN NO_DATA_FOUND  OR TOO_MANY_ROWS THEN
        RETURN -1;
  END buscarPeliculaPorTitulo;

  FUNCTION comprobarDisponibilidad(idPelicula title_copy.title_id%TYPE) RETURN NUMBER AS
  TYPE tipoTablaTitleCopy IS TABLE OF title_copy.copy_id%TYPE INDEX BY BINARY_INTEGER;
  copiasDeLaPelicula tipoTablaTitleCopy;
  BEGIN
    SELECT copy_id BULK COLLECT INTO copiasDeLaPelicula FROM title_copy WHERE title_id = idPelicula AND status = 'AVAILABLE';
    IF copiasDeLaPelicula.COUNT = 0 THEN
      RETURN -1;
    ELSE
      RETURN copiasDeLaPelicula(copiasDeLaPelicula.FIRST);
    END IF;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        RETURN -1;
  END comprobarDisponibilidad;

  FUNCTION validarFechaReserva(fechaReserva reservation.res_date%TYPE) RETURN BOOLEAN AS
  BEGIN
    IF fechaReserva > SYSDATE+4 OR fechaReserva < SYSDATE THEN
      RETURN FALSE;
    ELSE
      RETURN TRUE;
    END IF;
  END validarFechaReserva;
  FUNCTION comprobarReserva(idPelicula title.title_id%TYPE) RETURN BOOLEAN AS
  fecha reservation.res_date%TYPE;
  BEGIN
    SELECT res_date INTO fecha FROM RESERVATION WHERE title_id = idPelicula;
  RETURN TRUE;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RETURN FALSE;
    WHEN TOO_MANY_ROWS THEN
      RETURN TRUE;
  END comprobarReserva;

  FUNCTION comprobarReserva(idPelicula title.title_id%TYPE, idSocio member.member_id%TYPE) RETURN BOOLEAN AS
  fecha reservation.res_date%TYPE;
  BEGIN
    SELECT res_date INTO fecha FROM RESERVATION WHERE member_id = idSocio AND title_id = idPelicula;
  RETURN TRUE;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RETURN FALSE;
    WHEN TOO_MANY_ROWS THEN
      RETURN TRUE;
  END comprobarReserva;

  FUNCTION buscarSocioPorNombre(nombre member.first_name%TYPE, apellido member.last_name%TYPE) RETURN member.member_id%TYPE AS
  i BINARY_INTEGER;
  BEGIN
    i := tablaSocios.FIRST;
    WHILE i <= tablaSocios.LAST AND (nombre <> tablaSocios(i).first_name OR apellido <> tablaSocios(i).last_name) LOOP
        i := tablaSocios.next(i);
    END LOOP;
    IF i IS NULL THEN
      RETURN -1;
    ELSE
      RETURN tablaSocios(i).member_id;
    END IF;
  END buscarSocioPorNombre;

  FUNCTION nuevoSocio(nombre member.first_name%TYPE, apellido member.last_name%TYPE, domicilio member.address%TYPE, localidad member.city%TYPE) RETURN member.member_id%TYPE AS
    idNuevoSocio member.member_id%TYPE;
  BEGIN
    SELECT member_id_seq.NEXTVAL INTO idNuevoSocio FROM DUAL;
    INSERT INTO MEMBER (member_id, last_name, first_name, address, city, join_date) VALUES (idNuevoSocio, apellido, nombre, domicilio, localidad, SYSDATE);
    tablaSocios(idNuevoSocio).member_id := idNuevoSocio;
    tablaSocios(idNuevoSocio).first_name := nombre;
    tablaSocios(idNuevoSocio).last_name := apellido;
    tablaSocios(idNuevoSocio).address := domicilio;
    tablaSocios(idNuevoSocio).city := localidad;
    --TODO: ver tema exceptions
    RETURN idNuevoSocio;
  END nuevoSocio;

  FUNCTION nuevoSocio(nombre member.first_name%TYPE, apellido member.last_name%TYPE, domicilio member.address%TYPE, localidad member.city%TYPE, telefono member.phone%TYPE) RETURN member.member_id%TYPE AS
    idNuevoSocio member.member_id%TYPE;
  BEGIN
    SELECT member_id_seq.NEXTVAL INTO idNuevoSocio FROM DUAL;
    INSERT INTO MEMBER VALUES (idNuevoSocio, apellido, nombre, domicilio, localidad, telefono, SYSDATE);
    IF sql%rowcount = 0 THEN
      DBMS_OUTPUT.put_line('error');
    END IF;
    tablaSocios(idNuevoSocio).member_id := idNuevoSocio;
    tablaSocios(idNuevoSocio).first_name := nombre;
    tablaSocios(idNuevoSocio).last_name := apellido;
    tablaSocios(idNuevoSocio).address := domicilio;
    tablaSocios(idNuevoSocio).city := localidad;
    tablaSocios(idNuevoSocio).phone := telefono;
    RETURN idNuevoSocio;
    --TODO: ver tema exceptions
  END nuevoSocio;

  PROCEDURE reservarPelicula(idSocio member.member_id%TYPE, idPelicula title.title_id%TYPE, fechaReserva reservation.res_date%TYPE) AS
  BEGIN
    IF validarFechaReserva(fechaReserva) = TRUE THEN
      INSERT INTO reservation VALUES (fechaReserva, idSocio, idPelicula);
    ELSE
      DBMS_OUTPUT.put_line('La fecha de reserva tiene que ser entre los 4 días posteriores'); --TODO: ver tema errores
    END IF;
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        DBMS_OUTPUT.put_line('El socio ya tenía reservada la pelicula');
  END reservarPelicula;

  PROCEDURE reservarPelicula(nombre member.first_name%TYPE, apellido member.last_name%TYPE, idPelicula title.title_id%TYPE, fechaReserva reservation.res_date%TYPE) AS
  idSocio member.member_id%TYPE;
  exceptionSocioNoExistente EXCEPTION;
  BEGIN
    idSocio := buscarSocioPorNombre(nombre, apellido);
    IF idSocio = -1 THEN
      RAISE exceptionSocioNoExistente;
    END IF;
    reservarPelicula(idSocio, idPelicula, fechaReserva);
    EXCEPTION
      WHEN exceptionSocioNoExistente THEN
        DBMS_OUTPUT.put_line('Socio no encontrado');
  END reservarPelicula;

  PROCEDURE alquilarPelicula(idSocio member.member_id%TYPE, idPelicula title.title_id%TYPE) AS
  idCopia title_copy.copy_id%TYPE;
  fechaActual DATE;
  nuevoAlquiler NUMBER := tablaAlquileres.LAST+1;
  exceptionCopiaNoExistente EXCEPTION;
  exceptionSocioDeudor EXCEPTION;
  BEGIN
    IF comprobarEstadoSocio(idSocio) = FALSE THEN
      RAISE exceptionSocioDeudor;
    ELSE
      --Si tiene una reserva de la pelicula la elimino
      IF comprobarReserva(idPelicula, idSocio) = TRUE THEN
        DELETE FROM reservation WHERE member_id = idSocio AND title_id = idPelicula;
      END IF;
      idCopia := comprobarDisponibilidad(idPelicula);
      IF idCopia = -1 THEN
        RAISE exceptionCopiaNoExistente;
      ELSE
        SELECT SYSDATE INTO fechaActual FROM DUAL;
        tablaAlquileres(nuevoAlquiler).book_date := fechaActual;
        tablaAlquileres(nuevoAlquiler).copy_id := idCopia;
        tablaAlquileres(nuevoAlquiler).member_id := idSocio;
        tablaAlquileres(nuevoAlquiler).title_id := idPelicula;
        --tablaAlquileres(nuevoAlquiler).act_ret_date := NULL;
        tablaAlquileres(nuevoAlquiler).exp_ret_date := fechaActual+2;
        INSERT INTO rental (book_date, copy_id, member_id, title_id, exp_ret_date) VALUES (fechaActual, idCopia, idSocio, idPelicula, fechaActual+2);
        UPDATE title_copy SET status = 'RENTED' WHERE copy_id = idCopia AND title_id = idPelicula;
        --TODO:Ver el tema de los errores
        DBMS_OUTPUT.put_line('El numero de copia de la pelicula ' || idPelicula || ' que se alquilo es: ' || idCopia);
      END IF;
    END IF;
    EXCEPTION
      WHEN exceptionCopiaNoExistente THEN
          DBMS_OUTPUT.put_line('Pelicula no disponible');
      WHEN exceptionSocioDeudor THEN
          DBMS_OUTPUT.put_line('El socio es deudor.');
  END alquilarPelicula;

  PROCEDURE alquilarPelicula(idSocio member.member_id%TYPE, tituloPelicula title.title%TYPE) AS
  idPelicula NUMBER;
  BEGIN
    idPelicula := buscarPeliculaPorTitulo(tituloPelicula);
    IF idPelicula = -1 THEN
      DBMS_OUTPUT.put_line('No existe la pelicula o hay mas de una con ese nombre'); --TODO: ver tema errores
    ELSE
      alquilarPelicula(idSocio, idPelicula);
    END IF;
  END alquilarPelicula;

  PROCEDURE retornarPelicula(idCopia title_copy.copy_id%TYPE, idPelicula title_copy.title_id%TYPE) AS
  estadoCopia title_copy.status%TYPE;
  exceptionUpdateAlquiler EXCEPTION;
  exceptionUpdateCopia EXCEPTION;
  BEGIN
    IF comprobarReserva(idPelicula) = TRUE THEN
      estadoCopia := 'RESERVED';
    ELSE
      estadoCopia := 'AVAILABLE';
    END IF;
    UPDATE RENTAL SET act_ret_date = SYSDATE WHERE copy_id = idCopia AND title_id = idPelicula;
    IF sql%rowcount = 0 THEN
      RAISE exceptionUpdateAlquiler;
    END IF;
    UPDATE title_copy SET status = estadoCopia WHERE copy_id = idCopia AND title_id = idPelicula;
    IF sql%rowcount = 0 THEN
      RAISE exceptionUpdateCopia;
    END IF;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.put_line('Numero de copia inexistente'); --TODO: ver tema errores
      WHEN exceptionUpdateAlquiler THEN
        DBMS_OUTPUT.put_line('Error al actualizar alquiler'); --TODO: ver tema errores
      WHEN exceptionUpdateCopia THEN
        DBMS_OUTPUT.put_line('Error al actualizar estado de la copia'); --TODO: ver tema errores
  END retornarPelicula;

  PROCEDURE listarSocio(idSocio member.member_id%TYPE) AS
  tituloPelicula title.title%TYPE;
  cantidadPeliculasNoDevueltas NUMBER := 0;
  BEGIN
      DBMS_OUTPUT.put_line('Nombre: ' || tablaSocios(idSocio).first_name || '. Apellido: ' || tablaSocios(idSocio).last_name);
      DBMS_OUTPUT.put_line('Dirección: ' || tablaSocios(idSocio).address || '. Ciudad: ' || tablaSocios(idSocio).city || '.');
      IF comprobarEstadoSocio(tablaSocios(idSocio).member_id) = TRUE THEN
        DBMS_OUTPUT.put_line('Normal');
      ELSE
        DBMS_OUTPUT.put_line('Deudor');
      END IF;
      FOR j IN 1..tablaAlquileres.COUNT LOOP
        IF tablaAlquileres(j).member_id = tablaSocios(idSocio).member_id THEN
          DBMS_OUTPUT.put_line('Fecha de alquiler: ' || tablaAlquileres(j).book_date || '. Fecha de devolución: ' || tablaAlquileres(j).act_ret_date);
          SELECT title INTO tituloPelicula FROM title WHERE title_id = tablaAlquileres(j).title_id;
          DBMS_OUTPUT.put_line('Titulo: ' || tituloPelicula);
          IF tablaAlquileres(j).act_ret_date = NULL THEN
            cantidadPeliculasNoDevueltas := cantidadPeliculasNoDevueltas+1;
          END IF;
        END IF;
      END LOOP;
      DBMS_OUTPUT.put_line('Este socio debe ' || cantidadPeliculasNoDevueltas || ' peliculas.');
  END listarSocio;

  PROCEDURE listarSocio(nombre member.first_name%TYPE, apellido member.last_name%TYPE) AS
  idSocio member.member_id%TYPE;
  BEGIN
    idSocio := buscarSocioPorNombre(nombre, apellido);
    listarSocio(idSocio);
  END listarSocio;

  PROCEDURE listarPeliculasAlquiladas AS
    cantidadCopias NUMBER;
  BEGIN
      FOR i IN 1..tablaAlquileres.COUNT LOOP
          FOR unaPeli IN cursorPelicula(tablaAlquileres(i).title_id) LOOP
            DBMS_OUTPUT.put_line('id: ' || unaPeli.title_id || '. Nombre: ' || unaPeli.title || '. Fecha de Estreno: ' || unaPeli.release_date);
            DBMS_OUTPUT.put_line(unaPeli.description);
            DBMS_OUTPUT.put_line('Rating: ' || unaPeli.rating || '. Categoria: ' || unaPeli.category);
            SELECT COUNT(*) INTO cantidadCopias FROM title_copy WHERE title_id = unaPeli.title_id AND status = 'AVAILABLE';
            DBMS_OUTPUT.put_line('Cantidad de copias disponibles: ' || cantidadCopias);
          END LOOP;
      END LOOP;
  END;
BEGIN
  FOR registroSocio IN cursorSocios LOOP
    tablaSocios(registroSocio.member_id).member_id := registroSocio.member_id;
    tablaSocios(registroSocio.member_id).last_name := registroSocio.last_name;
    tablaSocios(registroSocio.member_id).first_name := registroSocio.first_name;
    tablaSocios(registroSocio.member_id).address := registroSocio.address;
    tablaSocios(registroSocio.member_id).city := registroSocio.city;
    tablaSocios(registroSocio.member_id).phone := registroSocio.phone;
    tablaSocios(registroSocio.member_id).join_date := registroSocio.join_date;
  END LOOP;
  SELECT * BULK COLLECT INTO tablaAlquileres FROM RENTAL;
END PACKVIDEO;

--Especificacion

CREATE OR REPLACE
PACKAGE PACKVIDEO
IS
    TYPE tipoTablaSocios IS TABLE OF member%ROWTYPE INDEX BY BINARY_INTEGER;
    tablaSocios tipoTablaSocios;
    TYPE tipoTablaAlquileres IS TABLE OF rental%ROWTYPE INDEX BY BINARY_INTEGER;
    tablaAlquileres tipoTablaAlquileres;
    FUNCTION nuevoSocio(nombre member.first_name%TYPE, apellido member.last_name%TYPE, domicilio member.address%TYPE, localidad member.city%TYPE) RETURN member.member_id%TYPE;
    FUNCTION nuevoSocio(nombre member.first_name%TYPE, apellido member.last_name%TYPE, domicilio member.address%TYPE, localidad member.city%TYPE, telefono member.phone%TYPE) RETURN member.member_id%TYPE;
    PROCEDURE reservarPelicula(idSocio member.member_id%TYPE, idPelicula title.title_id%TYPE, fechaReserva reservation.res_date%TYPE);
    PROCEDURE reservarPelicula(nombre member.first_name%TYPE, apellido member.last_name%TYPE, idPelicula title.title_id%TYPE, fechaReserva reservation.res_date%TYPE);
    PROCEDURE alquilarPelicula(idSocio member.member_id%TYPE, idPelicula title.title_id%TYPE);
    PROCEDURE alquilarPelicula(idSocio member.member_id%TYPE, tituloPelicula title.title%TYPE);
    PROCEDURE retornarPelicula(idCopia title_copy.copy_id%TYPE, idPelicula title_copy.title_id%TYPE);
    PROCEDURE listarSocio(nombre member.first_name%TYPE, apellido member.last_name%TYPE);
    PROCEDURE listarSocio(idSocio member.member_id%TYPE);
    PROCEDURE listarPeliculasAlquiladas;
END;

--Trigger

CREATE OR REPLACE TRIGGER ACTUALIZARRESERVAS
BEFORE INSERT OR UPDATE ON RESERVATION
FOR EACH ROW
BEGIN
  DELETE FROM RESERVATION WHERE RES_DATE < SYSDATE;
END;

--setDePruebas

DECLARE
idSocio NUMBER;
BEGIN
  idSocio := packvideo.nuevoSocio('andres', 'cabrera', 'casa rosada', 'asdasd');
END;

BEGIN
  packvideo.reservarPelicula('andres', 'cabrera', 92, '12/06/2011');
END;

BEGIN
  packvideo.alquilarpelicula(133, 'Soda Gang');
  DBMS_OUTPUT.put_line (packvideo.tablaAlquileres(packvideo.tablaAlquileres.LAST).member_id);
END;

BEGIN
  packvideo.alquilarpelicula(133, 'zona ganjah');
  --dbms_output.put_line (packvideo.tablaAlquileres(packvideo.tablaAlquileres.last).member_id);
END;

BEGIN
  --Alquilar una pelicula
  --packvideo.alquilarpelicula(133, 97);
  --Devolver una pelicula
  --ackvideo.retornarpelicula(1, 97);
  --Reservar una pelicula
  packvideo.reservarpelicula(132, 97, '15/06/2011');
  --Mostrar el listado de peliculas alquiladas
  --packvideo.listarpeliculasalquiladas;
  --packvideo.listarSocio('andres', 'cabrera');
END;