use monitoreo;

drop procedure if exists Lighthouse_TipoDispositivo;
drop procedure if exists Lighthouse_BuscarCrearRequestedURL;
drop procedure if exists Lighthouse_BuscarCrearNombreFinalUrl;
drop procedure if exists Lighthouse_BuscarCrearCategory;
drop procedure if exists Lighthouse_BuscarCrearNameCategory;
drop procedure if exists Lighthouse_BuscarCrearTitle;
drop procedure if exists Lighthouse_BuscarCrearType;
drop procedure if exists Lighthouse_InsertRegistro;

delimiter $$

create procedure Lighthouse_TipoDispositivo(_nombreTipoDispositivo varchar(255))
begin
    declare _idTipoDispositivo int;
    set _idTipoDispositivo = (select td.id_tipo_dispositivo from monitoreo.tipo_dispositivo as td WHERE td.nombre_tipo_dispositivo=_nombreTipoDispositivo);
    
    if _idTipoDispositivo is null then
        INSERT INTO monitoreo.tipo_dispositivo values (null, _nombreTipoDispositivo);
    end if;
    select td.id_tipo_dispositivo from monitoreo.tipo_dispositivo as td WHERE td.nombre_tipo_dispositivo=_nombreTipoDispositivo;
end $$

create procedure Lighthouse_BuscarCrearRequestedURL(_nombreRequestedURL varchar(255))
begin
    declare _idRequestURL int;
    set _idRequestURL = (select r.id_requestedUrl from monitoreo.requestedurl as r WHERE r.nombre_requestedUrl=_nombreRequestedURL);
    
    if _idRequestURL is null then
        INSERT INTO monitoreo.requestedurl values (null, _nombreRequestedURL);
    end if;
    select r.id_requestedUrl from monitoreo.requestedurl as r WHERE r.nombre_requestedUrl=_nombreRequestedURL;
end $$

create procedure Lighthouse_BuscarCrearNombreFinalUrl(_nombreFinalURL varchar(255))
begin
    declare _idFinalURL int;
    set _idFinalURL = (select f.id_finalUrl from monitoreo.finalurl as f WHERE f.nombre_finalUrl=_nombreFinalURL);
    
    if _idFinalURL is null then
        INSERT INTO monitoreo.finalurl values (null, _nombreFinalURL);
    end if;
    select f.id_finalUrl from monitoreo.finalurl as f WHERE f.nombre_finalUrl=_nombreFinalURL;
end $$

create procedure Lighthouse_BuscarCrearCategory(_categoria varchar(255))
begin
    declare _idCategoria int;
    set _idCategoria = (select c.id_category from monitoreo.category as c WHERE c.category=_categoria);
    
    if _idCategoria is null then
        INSERT INTO monitoreo.category values (null, _categoria, null);
    end if;
    select c.id_category from monitoreo.category as c WHERE c.category=_categoria;
end $$

create procedure Lighthouse_BuscarCrearNameCategory(_idCategoria int, _nameCategoria varchar(255))
begin
    declare _idNAmeCategoria int;
    set _idNAmeCategoria = (select n.id_name_category from monitoreo.name_category as n WHERE n.name_category=_nameCategoria AND n.id_category=_idCategoria);
    
    if _idNAmeCategoria is null then
        INSERT INTO monitoreo.name_category values (null, _idCategoria, _nameCategoria, null);
    end if;
    select n.id_name_category from monitoreo.name_category as n WHERE n.name_category=_nameCategoria AND n.id_category=_idCategoria;
end $$

create procedure Lighthouse_BuscarCrearTitle(_idNameCategoria int, _title varchar(255))
begin
    declare _idTitle int;
    set _idTitle = (select t.id_title from monitoreo.title as t WHERE t.title=_title AND t.id_name_category=_idNameCategoria);
    
    if _idTitle is null then
        INSERT INTO monitoreo.title values (null, _idNameCategoria, _title, null);
    end if;
    select t.id_title from monitoreo.title as t WHERE t.title=_title AND t.id_name_category=_idNameCategoria;
end $$

create procedure Lighthouse_BuscarCrearType(_type varchar(100))
begin
    declare _idType int;
    set _idType = (select t.id_type from monitoreo.type as t WHERE t.type=_type);
    
    if _idType is null then
        INSERT INTO monitoreo.type values (null, _type, null);
    end if;
    select t.id_type from monitoreo.type as t WHERE t.type=_type;
end $$

create procedure Lighthouse_InsertRegistro(_idResqURL int, _idFinalURL int, _idTitle int, _idType int, _score varchar(255), _idTipoDispositivo int, _fechaRegistro datetime)
begin
    insert into monitoreo.registro values(null, _idResqURL, _idFinalURL, _idTitle, _idType, _score, _idTipoDispositivo, _fechaRegistro);
end $$