/* EW query */


DELIMITER $$
DROP FUNCTION IF EXISTS `fn_isdate`$$
CREATE  DEFINER = CURRENT_USER  FUNCTION `fn_isdate`(in_str VARCHAR(255)) RETURNS tinyint 
DETERMINISTIC
BEGIN
IF LENGTH(DATE(in_str)) IS NOT NULL AND LENGTH(TIME(in_str)) IS NOT NULL THEN
    RETURN 0;
ELSE
    RETURN 1;
END IF;
END$$
DELIMITER ;

DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_ew_last_event_id` $$
CREATE PROCEDURE  `sp_ew_last_event_id`(
)
BEGIN
SELECT MAX(id) FROM `ew_events_summary`;

END $$
DELIMITER ;


DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_ew_show_hosts` $$
CREATE PROCEDURE  `sp_ew_show_hosts`(
)
BEGIN
Select distinct hostname From `ew_instance` Where hostname is not  NULL;

END $$
DELIMITER ;

DELIMITER //

DROP PROCEDURE IF EXISTS sp_ew_arcsummary//

Create  DEFINER = CURRENT_USER  PROCEDURE sp_ew_arcsummary(IN event_id bigint(20), IN loc_version bigint(20), IN ew_instance varchar(80))
BEGIN
	SELECT  ew_arc_summary.* 
	FROM ew_arc_summary 
		join ew_sqkseq on (ew_arc_summary.ewsqkid = ew_sqkseq.id) 
	WHERE ew_sqkseq.ewid = (SELECT id 
					FROM ew_instance 
					WHERE ewname =ew_instance) 
		AND  ew_sqkseq.qkseq = event_id
		AND ew_arc_summary.version = loc_version;
END//

DELIMITER ;

DELIMITER //

DROP PROCEDURE IF EXISTS sp_ew_eventphases_list//

CREATE  DEFINER = CURRENT_USER  PROCEDURE sp_ew_eventphases_list(IN event_id bigint(20), IN loc_version bigint(20), IN ew_instance varchar(80))
BEGIN
SELECT ew_instance.ewname, ew_scnl.sta, ew_scnl.cha, ew_scnl.net, ew_scnl.loc,
ROUND(ew_arc_phase.dist, 2) as dist, 
IF(ew_arc_phase.Ponset='P', ew_arc_phase.Pres, '')  AS Pres, 
IF(ew_arc_phase.Ponset='P', ew_arc_phase.Pwt, '')  AS Pwt, 
IF(ew_arc_phase.Sonset='S', ew_arc_phase.Sres, '') AS Sres, 
IF(ew_arc_phase.Sonset='S', ew_arc_phase.Swt, '') AS Swt, 
IF(ew_arc_phase.Ponset='P', CONCAT(ew_arc_phase.Ponset, ew_arc_phase.Plabel), '') AS PLab, 
IF(ew_arc_phase.Ponset='P', CONCAT(CAST(ew_arc_phase.Pat_dt AS CHAR),SUBSTR(ROUND(ew_arc_phase.Pat_usec/1000000.0, 2), 2) ), '') AS 'P_phase_time',
IF(ew_arc_phase.Ponset='P', ew_arc_phase.Pqual, '') AS Pqual,
IF(ew_arc_phase.Sonset='S', CONCAT(ew_arc_phase.Sonset, ew_arc_phase.Slabel), '') AS SLab,
IF(ew_arc_phase.Sonset='S', CONCAT(CAST(ew_arc_phase.Sat_dt AS CHAR),  SUBSTR(ROUND(ew_arc_phase.Sat_usec/1000000.0, 2), 2) ), '') AS 'S_phase_time',
IF(ew_arc_phase.Sonset='S', ew_arc_phase.Squal, '') AS Squal,
ew_arc_phase.codalen, ew_arc_phase.codawt, ew_arc_phase.Pfm, ew_arc_phase.Sfm, ew_arc_phase.datasrc, ew_arc_phase.Md, ew_arc_phase.azm, ew_arc_phase.takeoff, ew_arc_phase.id, ew_sqkseq.qkseq, ew_arc_phase.version, ew_module.modname, ew_arc_phase.modified 
FROM ew_arc_phase 
JOIN ew_module ON (ew_arc_phase.ewmodid=ew_module.id) 
JOIN ew_instance ON (ew_module.ewid=ew_instance.id) 
JOIN ew_sqkseq ON (ew_arc_phase.ewsqkid=ew_sqkseq.id) 
JOIN ew_scnl ON (ew_scnl.id=ew_arc_phase.scnlid) 
WHERE ew_sqkseq.qkseq = event_id AND ew_arc_phase.version = loc_version AND ew_instance.ewname= ew_instance;
END//

DELIMITER ;

DELIMITER //

DROP PROCEDURE IF EXISTS sp_ew_magnitudepicks//

CREATE  DEFINER = CURRENT_USER  PROCEDURE sp_ew_magnitudepicks(IN event_id bigint(20), IN l_version bigint(20), IN ew_instance varchar(80))
BEGIN
SELECT ew_instance.ewname, ew_scnl.sta, ew_scnl.cha, ew_scnl.net, ew_scnl.loc,ew_sqkseq.qkseq, ew_magnitude_phase.version, ew_module.modname, ew_magnitude_phase.modified,ew_magnitude_phase.*
	FROM ew_magnitude_phase
	JOIN ew_module ON (ew_magnitude_phase.ewmodid=ew_module.id )
	JOIN ew_instance ON (ew_module.ewid=ew_instance.id)
	JOIN ew_sqkseq ON (ew_magnitude_phase.ewsqkid=ew_sqkseq.id)
	JOIN ew_scnl ON (ew_scnl.id=ew_magnitude_phase.scnlid)
	WHERE ew_sqkseq.qkseq = event_id AND ew_magnitude_phase.version = l_version AND ew_instance.ewname= ew_instance;
END//

DELIMITER ;


DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_ew_event_list` $$
CREATE PROCEDURE  `sp_ew_event_list`(
in_start_time  VARCHAR(19),
in_end_time    VARCHAR(19)
)
uscita: BEGIN
    IF fn_isdate(in_start_time) THEN
        SELECT'ERROR: start_time not datatime format yyy-mm-dd HH:mm:ss';
        LEAVE uscita;
    END IF;
    IF fn_isdate(in_end_time) THEN
        SELECT'ERROR: end_time not datatime format  yyy-mm-dd HH:mm:ss';
        LEAVE uscita;
    END IF;
    SELECT `ew_arc_summary`.`id` AS `db_id`,
    `ew_sqkseq`.`qkseq` AS `ew_id`,
     CONCAT(`ew_arc_summary`.`ot_dt`, '.', LEFT(`ew_arc_summary`.`ot_usec`,4)) AS `ot_time`,
    `ew_magnitude_summary`.`mag` AS `mag`,
    `ew_magnitude_summary`.`szmagtype` AS `mag_type`,
    `ew_magnitude_summary`.`error` AS `mag_error`,
    `ew_arc_summary`.`quality` AS `quality`,
    ROUND(`ew_arc_summary`.`lat`,3) AS `lat`,
    ROUND(`ew_arc_summary`.`lon`,3) AS `lon`,
    ROUND(`ew_arc_summary`.`z`,3) AS `depth`,
    `ew_arc_summary`.`erh` AS `error_h`,
    `ew_arc_summary`.`erz` AS `error_z`,
    `ew_arc_summary`.`nphtot` AS `nphtot`,
    `ew_arc_summary`.`gap` AS `gap`,
    `ew_arc_summary`.`dmin` AS `dmin`,
    `ew_arc_summary`.`rms` AS `rms`,
    `ew_instance`.`ewname` AS `ewname`,
    `ew_instance`.`hostname` AS `hostname`,
    `modmag`.`modname` AS `modmag`,
--    `ew_module`.`modname` AS `modarc`,
--    `ew_sqkseq`.`qkseq` AS `qkseq`,
    `ew_arc_summary`.`version` AS `version`,
    `ew_arc_summary`.`modified` AS `modified`,
    GROUP_CONCAT(DISTINCT CONCAT(`ew_scnl`.`sta`, '_', `ew_arc_phase`.`dist`, '_', `ew_arc_phase`.`Pwt`) SEPARATOR ';') AS `station_list`
    FROM `ew_arc_summary`
    JOIN `ew_module` ON `ew_arc_summary`.`ewmodid` = `ew_module`.`id`
    JOIN `ew_instance` ON `ew_module`.`ewid` = `ew_instance`.`id`
    JOIN `ew_sqkseq` ON `ew_arc_summary`.`ewsqkid` = `ew_sqkseq`.`id`
    LEFT JOIN `ew_magnitude_summary` ON `ew_arc_summary`.`ewsqkid` = `ew_magnitude_summary`.`ewsqkid`
        AND `ew_arc_summary`.`version` = `ew_magnitude_summary`.`version`
    LEFT JOIN `ew_module` `modmag` ON IF(ISNULL(`ew_magnitude_summary`.`mag`),0,`ew_magnitude_summary`.`ewmodid` = `modmag`.`id`)
    LEFT JOIN `ew_arc_phase` ON `ew_arc_phase`.`ewsqkid` = `ew_arc_summary`.`ewsqkid`
    JOIN `ew_scnl` ON `ew_arc_phase`.`scnlid` = `ew_scnl`.`id`
    WHERE `ew_arc_summary`.`ot_dt` >= in_start_time 
        AND `ew_arc_summary`.`ot_dt` <= in_end_time
    GROUP BY `ew_instance`.`ewname`,`ew_module`.`modname`,`ew_sqkseq`.`qkseq`,`ew_arc_summary`.`version`
    ORDER BY `ew_arc_summary`.`ot_dt`,`ew_arc_summary`.`ot_usec`;
END $$
DELIMITER ;

DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_ew_event_instance` $$
CREATE PROCEDURE  `sp_ew_event_instance`(
in_instance         VARCHAR(19),
in_start_time       VARCHAR(19),
in_end_time         VARCHAR(19)
)
-- call sp_ew_event_instance('endeavour','2010-01-01',now());
uscita: BEGIN
    DECLARE l_id_instance        BIGINT DEFAULT 0;
    
    SELECT id INTO l_id_instance
    FROM `ew_instance` 
    WHERE ewname = in_instance;
    IF l_id_instance = 0 THEN
        SELECT'ERROR: wrong istance name';
        LEAVE uscita;
    END IF;
    
    IF fn_isdate(in_start_time) THEN
        SELECT'ERROR: start_time not datatime format yyy-mm-dd HH:mm:ss';
        LEAVE uscita;
    END IF;
    IF fn_isdate(in_end_time) THEN
        SELECT'ERROR: end_time not datatime format  yyy-mm-dd HH:mm:ss';
        LEAVE uscita;
    END IF;
    SELECT CAST(CONCAT(`e`.`ewsqkid`, '_', `e`.`version`) AS CHAR(50)) AS `sqkid_version`,
    `e`.`ewsqkid` AS `sqkid`,
    `e`.`qkseq` AS `qkseq`,
    CAST(CONCAT(`e`.`ot_dt`, '.', LEFT(`e`.`ot_usec`,4)) AS CHAR(50)) AS `ot_time`,
    `e`.`mag` AS `mag`,
    `e`.`mag_type` AS `mag_type`,
    `e`.`mag_error` AS `mag_error`,
    `e`.`arc_quality` AS `quality`,
    ROUND(`e`.`lat`,3) AS `lat`,
    ROUND(`e`.`lon`,3) AS `lon`,
    ROUND(`e`.`z`,3) AS `depth`,
    `e`.`erh` AS `error_h`,
    `e`.`erz` AS `error_z`,
    `e`.`nphtot` AS `nphtot`,
    `e`.`gap` AS `gap`,
    `e`.`dmin` AS `dmin`,
    `e`.`rms` AS `rms`,
    `e`.`ewname` AS `ewname`,
    `e`.`mag_ewmodid` AS `modmag`,
    `e`.`region` AS `region_name`,
    `e`.`version` AS `version`,
    `e`.`modified` AS `modified`,
    `s`.`net`,
    `s`.`sta`,
    `p`.`dist`,
    `p`.`Pwt`
    FROM `ew_events_summary` e
    JOIN `ew_arc_phase` p ON `p`.`ewsqkid` = `e`.`ewsqkid` AND `p`.`version` = `e`.`version`
    JOIN `ew_scnl` s ON `p`.`scnlid` = `s`.`id`
    WHERE `e`.`ot_dt` >= in_start_time 
        AND `e`.`ot_dt` <= in_end_time
        AND `e`.`ewname` = in_instance
    ORDER BY `e`.`ewsqkid` DESC, `e`.`version` DESC; -- , `p`.`dist` ASC;
END $$
DELIMITER ;

DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_ew_event` $$
CREATE PROCEDURE  `sp_ew_event`(
in_start_time       VARCHAR(50),
in_end_time         VARCHAR(50)
)
-- call sp_ew_event('hew1_mole','2010-04-08',now());
uscita: BEGIN
    
    IF fn_isdate(in_start_time) THEN
        SELECT'ERROR: start_time not datatime format yyy-mm-dd HH:mm:ss';
        LEAVE uscita;
    END IF;
    IF fn_isdate(in_end_time) THEN
        SELECT'ERROR: end_time not datatime format  yyy-mm-dd HH:mm:ss';
        LEAVE uscita;
    END IF;

    SELECT CAST(CONCAT(`e`.`ewsqkid`, '_', `e`.`version`) AS CHAR(50)) AS `sqkid_version`,
    `e`.`ewsqkid` AS `sqkid`,
    `e`.`qkseq` AS `qkseq`,
    `e`.`ewname` AS `instance`,
    CAST(CONCAT(`e`.`ot_dt`, '.', LEFT(`e`.`ot_usec`,4)) AS CHAR(50)) AS `ot_time`,
    `e`.`mag` AS `mag`,
    `e`.`mag_type` AS `mag_type`,
    `e`.`mag_error` AS `mag_error`,
    `e`.`arc_quality` AS `quality`,
    ROUND(`e`.`lat`,3) AS `lat`,
    ROUND(`e`.`lon`,3) AS `lon`,
    ROUND(`e`.`z`,3) AS `depth`,
    `e`.`erh` AS `error_h`,
    `e`.`erz` AS `error_z`,
    `e`.`nphtot` AS `nphtot`,
    `e`.`gap` AS `gap`,
    `e`.`dmin` AS `dmin`,
    `e`.`rms` AS `rms`,
    `e`.`ewname` AS `ewname`,
    `e`.`mag_ewmodid` AS `modmag`,
    `e`.`region` AS `region_name`,
    `e`.`version` AS `version`,
    `e`.`modified` AS `modified`,
    `s`.`net`,
    `s`.`sta`,
    `p`.`dist`,
    `p`.`Pwt`
    FROM `ew_events_summary` e
    JOIN `ew_arc_phase` p ON `p`.`ewsqkid` = `e`.`ewsqkid` AND `p`.`version` = `e`.`version`
    JOIN `ew_scnl` s ON `p`.`scnlid` = `s`.`id`
    WHERE `e`.`ot_dt` >= in_start_time 
        AND `e`.`ot_dt` <= in_end_time
    ORDER BY `e`.`ewsqkid` DESC, `e`.`version` DESC, `p`.`dist` ASC;
END $$
DELIMITER ;

DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_ew_instance` $$
CREATE PROCEDURE  `sp_ew_instance`(
)
BEGIN
    SELECT * FROM ew_instance;
END $$
DELIMITER ;


DROP VIEW IF EXISTS `vw_cmplocations`;
CREATE VIEW `vw_cmplocations` AS 
SELECT e.ewsqkid, e.arc_quality, e.ot_dt, e.lat, e.lon, e.z, e.mag_type, e.mag, e.region, e.version, m.modname modarc, k.modname modmag, e.ewname, e.qkseq, 
sec_to_time(DATEDIFF(DATE(e.arc_modified), DATE(e.ot_dt))  + TIME_TO_SEC(TIME(e.arc_modified)) - TIME_TO_SEC(TIME(e.ot_dt))) as diff_ot_loc ,
sec_to_time(DATEDIFF(DATE(e.mag_modified), DATE(e.ot_dt))  + TIME_TO_SEC(TIME(e.mag_modified)) - TIME_TO_SEC(TIME(e.ot_dt))) as diff_ot_mag 

FROM ew_events_summary e
JOIN ew_module m ON e.arc_ewmodid = m.id
JOIN ew_module k ON e.mag_ewmodid = k.id
;


