--PRIMERO EJECUTAR SOLO LA CABECERA

create or replace PACKAGE GESTION_VIDEOS
AS
PROCEDURE SUBIR_VIDEO (CANAL IN VARCHAR2, TITLE IN VARCHAR2, DESCRIPTION IN VARCHAR2, VIDEO IN BLOB);
PROCEDURE REPRODUCIR_VIDEO (VIDEO_ID IN NUMBER , DURACION IN NUMBER);
END;

--/////////////////////
--Luego ejecutar el cuerpo
--/////////////////////

create or replace PACKAGE BODY GESTION_VIDEOS
AS

  PROCEDURE SUBIR_VIDEO (CANAL IN VARCHAR2, TITLE IN VARCHAR2 , DESCRIPTION IN VARCHAR2 , VIDEO IN BLOB ) AS

  video_owner_id  mitubo.usuarios.id%TYPE;
  cid_owner mitubo.canal.cid%TYPE;
  vid_number mitubo.videos.vid%TYPE;

  BEGIN
    vid_number := videos_id_seq.NEXTVAL;

    SELECT usu.id INTO video_owner_id FROM mitubo.usuarios usu WHERE UPPER(usu.alias) = UPPER(USER);
    IF canal IS NOT NULL THEN
      SELECT cid INTO cid_owner FROM mitubo.canal WHERE UPPER(canal) = UPPER(nombre);

      FOR sub IN (
        SELECT usuarios_id FROM mitubo.suscripciones WHERE UPPER(canal_cid) = UPPER(cid_owner)
      ) LOOP
        --dbms_output.put_line(sub.usuarios_id);
        INSERT INTO mitubo.not_suscriptor VALUES (notif_id_seq.NEXTVAL,'N', null, sub.usuarios_id, vid_number, 1, null);
      END LOOP;
    END IF;

    INSERT INTO mitubo.videos VALUES (vid_number, cid_owner, null, title, description, null, null, null, null, null,
      SYSDATE, null, null, video_owner_id, video);

  END SUBIR_VIDEO;

  PROCEDURE REPRODUCIR_VIDEO (VIDEO_ID IN NUMBER , DURACION IN NUMBER ) AS

  player_id  mitubo.usuarios.id%TYPE;
  player_saldo mitubo.usuarios.saldo%TYPE;
  video_length mitubo.videos.duracion_seg%TYPE;
  video_precio mitubo.videos.precio%TYPE;
  cuenta_videos NUMBER;
  cuenta_notif NUMBER;
  ex_visionado_no_autorizado EXCEPTION;

  BEGIN
    SELECT usu.id, usu.saldo INTO player_id, player_saldo FROM mitubo.usuarios usu WHERE UPPER(usu.alias) = UPPER(USER);
    SELECT duracion_seg, precio INTO video_length, video_precio FROM mitubo.videos WHERE video_id = vid;
    SELECT COUNT(*) INTO cuenta_videos FROM mitubo.historial_videos WHERE UPPER(usuarios_id) = UPPER(player_id) AND video_id = videos_vid;
    SELECT COUNT(*) INTO cuenta_notif FROM mitubo.not_suscriptor WHERE UPPER(usuarios_id) = UPPER(player_id) AND video_id = videos_vid;

    --comprobar si el video es de pago
    IF video_precio IS NOT NULL AND video_precio > player_saldo THEN
           RAISE ex_visionado_no_autorizado;
    END IF;

    IF cuenta_videos > 0 THEN
    --actualizar num visualizaciones del video
      UPDATE mitubo.videos
      SET visualizaciones = visualizaciones+1
      WHERE vid = video_id;
    --insertar el video en el historial
      IF duracion = video_length THEN
        INSERT INTO mitubo.historial_videos VALUES (SYSDATE, SYSDATE+((1/86400)*duracion), 100, player_id, video_id);
      ELSE
        INSERT INTO mitubo.historial_videos VALUES (SYSDATE, null, TRUNC((duracion/video_length)*100), player_id, video_id);
      END IF;
    END IF;
    --si existe notificacion de visualizacion de video, borrarla
    IF cuenta_notif > 0 THEN
      DELETE FROM mitubo.not_suscriptor WHERE (player_id = usuarios_id AND video_id = videos_vid);
    END IF;

    EXCEPTION
      WHEN ex_visionado_no_autorizado THEN
        DBMS_OUTPUT.PUT_LINE
          ('Este usuario no tiene dinero suficiente para ver este video.');

  END REPRODUCIR_VIDEO;

END GESTION_VIDEOS;