
-- ---------------------------------------------------------------
-- Authors: Matteo Quintiliani and Stefano Pintore
--          I.N.G.V. - Italy
--          Istituto Nazionale di Geofisica e Vulcanologia
-- ---------------------------------------------------------------


-- ---------------------------------------------------------------

-- DROP TABLE IF EXISTS `mole`.`err_sp`;
CREATE TABLE IF NOT EXISTS `mole`.`err_sp` (
    `id`                BIGINT NOT NULL AUTO_INCREMENT  COMMENT 'Unique incremental id',
    `sp_name`           VARCHAR(255) NULL          COMMENT 'stored procedure name',
    `error`             VARCHAR(1024) NULL          COMMENT 'error description',
    `gtime`          DOUBLE                     COMMENT 'instance call identifier',
    `modified`          TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last Review',
    PRIMARY KEY (`id`)
)
ENGINE = MyISAM COMMENT 'Error from sp call by trigger';

-- ---------------------------------------------------------------
-- DROP TABLE IF EXISTS `ew_events_summary`;
CREATE TABLE IF NOT EXISTS `ew_events_summary` (
    `id`                    BIGINT    NOT NULL AUTO_INCREMENT  COMMENT 'Unique incremental id',
    `ewsqkid`               BIGINT    NOT NULL     COMMENT 'Foreign key to ew_sqkseq',
    `qkseq`                 BIGINT    NOT NULL COMMENT 'Quake sequence number from binder',
    `version`               BIGINT    NOT NULL     COMMENT 'version number of the origin',
    `ewname`                CHAR(80)  DEFAULT NULL COMMENT 'Name of the EW instance running',
    -- from ew_arc_summary
    `arc_ewmodid`           BIGINT    DEFAULT NULL COMMENT 'Foreign key to ew_module',
    `ot_dt`                 DATETIME  DEFAULT NULL COMMENT 'Origin Time - datetime part',
    `ot_usec`               INT       DEFAULT NULL COMMENT 'Origin Time - useconds',
    `lat`                   DOUBLE    DEFAULT NULL COMMENT 'latitude (North=positive)',
    `lon`                   DOUBLE    DEFAULT NULL COMMENT 'longitude(East=positive)',
    `z`                     DOUBLE    DEFAULT NULL COMMENT 'depth (down=positive)',
    `arc_quality`           CHAR(2)   DEFAULT NULL COMMENT 'INGV quality code of the location computed by ew2moledb',
    `erh`                   DOUBLE    DEFAULT NULL COMMENT 'horizontal error (km)',
    `erz`                   DOUBLE    DEFAULT NULL COMMENT 'vertical error (km)',
    `nphtot`                INT       DEFAULT NULL COMMENT '# phases (P&S) w/ weight >0.0',
    `gap`                   INT       DEFAULT NULL COMMENT 'maximum azimuthal gap',
    `dmin`                  INT       DEFAULT NULL COMMENT 'distance (km) to nearest station',
    `rms`                   DOUBLE    DEFAULT NULL COMMENT 'RMS travel time residual',
    `arc_modified`          DATETIME  DEFAULT NULL COMMENT 'Last Review',        
    -- from ew_magnitude_summary
    `mag_ewmodid`           BIGINT    DEFAULT NULL COMMENT 'Foreign key to ew_module',
    `mag`                   DOUBLE    DEFAULT NULL COMMENT 'REQUIRED: magnitude value',
    `mag_error`             DOUBLE    DEFAULT NULL COMMENT 'OPTIONAL: Error estimate (std deviation for Ml/AVG)',
    `mag_quality`           DOUBLE    DEFAULT NULL COMMENT 'OPTIONAL: [0.0 - 1.0], -1.0 for NULL',
    `mag_type`              CHAR(6)   DEFAULT NULL COMMENT 'Magnitude type string',
    `nstations`             INT       DEFAULT NULL COMMENT 'OPTIONAL: Number of stations used to compute magnitude',
    `nchannels`             INT       DEFAULT NULL COMMENT 'OPTIONAL: Number of data channels used to compute magnitude.',
    `mag_modified`          DATETIME  DEFAULT NULL COMMENT 'Last Review',        
    `region`                VARCHAR(255)  DEFAULT NULL COMMENT 'Seismic region',        
    -- other
    `modified`              TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last Review',        
    PRIMARY KEY (`id`, `ot_dt`),
    INDEX (`ot_dt`),
    UNIQUE (`arc_ewmodid`, `mag_ewmodid`, `ewsqkid`, `version`),
    FOREIGN KEY (`arc_ewmodid`) REFERENCES ew_module(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
          INDEX (`arc_ewmodid`),
    FOREIGN KEY (`mag_ewmodid`) REFERENCES ew_module(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
          INDEX (`mag_ewmodid`),
    FOREIGN KEY (`ewsqkid`) REFERENCES ew_sqkseq(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
          INDEX (`ewsqkid`),
    INDEX (`version`),
    INDEX (`modified`)
)
ENGINE = MYISAM COMMENT 'Event summary report from EW arc and mag messages';

-- ---------------------------------------------------------------

DELIMITER $$
DROP TRIGGER IF EXISTS `tr_ew_events_summary_from_arc_sum`$$
CREATE TRIGGER `tr_ew_events_summary_from_arc_sum`
AFTER INSERT ON `ew_arc_summary`
    FOR EACH ROW
    BEGIN
	DECLARE l_arc_ewmodid           BIGINT    DEFAULT NULL;
	DECLARE l_ewsqkid               BIGINT    DEFAULT NULL;
	DECLARE l_version               BIGINT    DEFAULT NULL;
	DECLARE l_ewname                CHAR(80)  DEFAULT NULL;
	DECLARE l_qkseq                 BIGINT    DEFAULT NULL;
	/* start definition INGV */
	DECLARE l_writer                VARCHAR(255)    DEFAULT NULL;
	DECLARE l_seisev_is             TINYINT         DEFAULT 0;
	DECLARE l_moleslave             TINYINT         DEFAULT 0;
	/* end definition INGV */
    
	-- Set l_ewname and l_qkseq from table ew_sqkseq
	SELECT ewname,qkseq INTO l_ewname,l_qkseq FROM ew_instance, ew_sqkseq
	WHERE ew_sqkseq.ewid = ew_instance.id 
	AND ew_sqkseq.id = NEW.ewsqkid;

	-- Set l_arc_ewmodid, l_ewsqkid, l_version
	SELECT DISTINCT arc_ewmodid, ewsqkid,version  INTO l_arc_ewmodid, l_ewsqkid, l_version
	FROM `ew_events_summary`
	WHERE
	    arc_ewmodid = NEW.ewmodid
	    AND ewsqkid = NEW.ewsqkid
	    AND version = NEW.version
	;

	-- Verifica che gia' esiste in ew_events_summary
	-- Se gia' e' presente esegue UPDATE altrimenti INSERT
	IF l_arc_ewmodid = NEW.ewmodid AND l_ewsqkid = NEW.ewsqkid AND l_version = NEW.version THEN
	UPDATE `ew_events_summary` 
	    SET ot_dt = NEW.ot_dt,
	    ot_usec = NEW.ot_usec,
	    lat = NEW.lat,
	    lon = NEW.lon,
	    z = NEW.z,
	    arc_quality = NEW.quality,
	    erh = NEW.erh,
	    erz = NEW.erz,
	    nphtot = NEW.nphtot,
	    gap = NEW.gap,
	    dmin = NEW.dmin,
	    rms = NEW.rms,
	    ewname = l_ewname,
	    arc_modified = NEW.modified
	WHERE
	    arc_ewmodid = NEW.ewmodid
	    AND ewsqkid = NEW.ewsqkid
	    AND version = NEW.version
	;
	ELSE
	INSERT INTO `ew_events_summary` 
	    SET ot_dt = NEW.ot_dt,
	    ot_usec = NEW.ot_usec,
	    lat = NEW.lat,
	    lon = NEW.lon,
	    z = NEW.z,
	    arc_quality = NEW.quality,
	    erh = NEW.erh,
	    erz = NEW.erz,
	    nphtot = NEW.nphtot,
	    gap = NEW.gap,
	    dmin = NEW.dmin,
	    rms = NEW.rms,
	    ewname = l_ewname,
	    arc_modified = NEW.modified,
	    arc_ewmodid = NEW.ewmodid,
	    ewsqkid = NEW.ewsqkid,
	    qkseq = l_qkseq,           
	    region = `fn_find_region_name`(NEW.lat, NEW.lon),
	    version = NEW.version
	;
	END IF;

--  START INGV CODE
	SELECT CONCAT(IF(ISNULL(l_ewname), 'null', l_ewname), '#', IF(ISNULL(modname), 'null', modname)) INTO l_writer
	FROM ew_module WHERE id = NEW.ewmodid;

	SELECT COUNT(*) INTO l_seisev_is 
	FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'daSostituire';
	-- controllo messo per non inserire da restore fatto sulla macchina (localhost)
	-- i dati provenienti da macchine di test, ovvero diverse da hew1 e hew2

	IF l_seisev_is > 0 THEN
	  IF host_fill_db.`fn_fill_seisev`() = 1 THEN
	    -- INSERT INTO `mole`.`err_sp` (sp_name, error, gtime)
	    -- VALUES('sp_ins_ev_ew', CONCAT(l_qkseq, ' ', l_writer, ' ', NEW.ot_dt, ' ', NEW.ot_usec, ' ', NEW.lat, ' ', NEW.lon, ' ',  NEW.z, ' ', NEW.nph, ' ', NEW.nphs, ' ', NEW.nphtot, ' ', NEW.nPfm, ' ', NEW.gap, ' ', NEW.dmin, ' ', NEW.rms, ' ', NEW.e0az, ' ', NEW.e0dp, ' ', NEW.e0, ' ', NEW.e1az, ' ', NEW.e1dp, ' ', NEW.e1, ' ', NEW.e2, ' ', NEW.erh, ' ', NEW.erz, ' ', NEW.version, ' ', NEW.quality), 1);

	    CALL daSostituire.`sp_ins_ev_ew`(l_qkseq, l_writer, NEW.ot_dt, NEW.ot_usec, NEW.lat, NEW.lon,  NEW.z, NEW.nph, NEW.nphs, NEW.nphtot, NEW.nPfm, NEW.gap, NEW.dmin, NEW.rms, NEW.e0az, NEW.e0dp, NEW.e0, NEW.e1az, NEW.e1dp, NEW.e1, NEW.e2, NEW.erh, NEW.erz, NEW.version, NEW.quality);
	  END IF;
	END IF;
--  END INGV CODE

    END;
$$
DELIMITER ;

-- ---------------------------------------------------------------

DELIMITER $$
DROP TRIGGER IF EXISTS `tr_ew_events_summary_from_mag_sum`$$
CREATE TRIGGER `tr_ew_events_summary_from_mag_sum`
AFTER INSERT ON `ew_magnitude_summary`
    FOR EACH ROW
    BEGIN
	DECLARE l_mag_ewmodid           BIGINT    DEFAULT NULL;
	DECLARE l_ewsqkid               BIGINT    DEFAULT NULL;
	DECLARE l_qkseq                 BIGINT    DEFAULT NULL;
	DECLARE l_version               BIGINT    DEFAULT NULL;

	DECLARE l_arc_ewmodid           BIGINT    DEFAULT NULL;
	DECLARE l_ot_dt                 DATETIME  DEFAULT NULL;
	DECLARE l_ot_usec               INT       DEFAULT NULL;
	DECLARE l_lat                   DOUBLE    DEFAULT NULL;
	DECLARE l_lon                   DOUBLE    DEFAULT NULL;
	DECLARE l_z                     DOUBLE    DEFAULT NULL;
	DECLARE l_arc_quality           CHAR(2)   DEFAULT NULL;
	DECLARE l_erh                   DOUBLE    DEFAULT NULL;
	DECLARE l_erz                   DOUBLE    DEFAULT NULL;
	DECLARE l_nphtot                INT       DEFAULT NULL;
	DECLARE l_gap                   INT       DEFAULT NULL;
	DECLARE l_dmin                  INT       DEFAULT NULL;
	DECLARE l_rms                   DOUBLE    DEFAULT NULL;
	DECLARE l_arc_modified          DATETIME  DEFAULT NULL;
	DECLARE l_ewname                CHAR(80)  DEFAULT NULL;
	DECLARE l_region                VARCHAR(80)  DEFAULT NULL;

	/* start definition INGV */
	DECLARE l_writer_mag            VARCHAR(255)    DEFAULT NULL;
	DECLARE l_writer_ev             VARCHAR(255)    DEFAULT NULL;
	DECLARE l_seisev_is             TINYINT         DEFAULT 0;
	DECLARE l_moleslave             TINYINT         DEFAULT 0;
	/* end definition INGV */

	-- Set l_ewname and l_qkseq from table ew_sqkseq
	SELECT ewname,qkseq INTO l_ewname,l_qkseq FROM ew_instance, ew_sqkseq
	WHERE ew_sqkseq.ewid = ew_instance.id 
	AND ew_sqkseq.id = NEW.ewsqkid;

	-- Set l_mag_ewmodid, l_ewsqkid, l_version
	SELECT DISTINCT mag_ewmodid, ewsqkid,version  INTO l_mag_ewmodid, l_ewsqkid, l_version
	FROM `ew_events_summary`
	WHERE
	    mag_ewmodid = NEW.ewmodid
	    AND ewsqkid = NEW.ewsqkid
	    AND version = NEW.version
	;

	-- Verifica che gia' esiste in ew_events_summary
	-- Se gia' e' presente esegue UPDATE altrimenti INSERT
	IF l_mag_ewmodid = NEW.ewmodid AND l_ewsqkid = NEW.ewsqkid AND l_version = NEW.version THEN
	UPDATE `ew_events_summary` 
	    SET mag = NEW.mag,
	    mag_error = NEW.error,
	    mag_quality = NEW.quality,
	    mag_type = NEW.szmagtype,
	    nstations = NEW.nstations,
	    nchannels = NEW.nchannels,
	    ewname = l_ewname,
	    mag_modified = NEW.modified,
	    mag_ewmodid = NEW.ewmodid
	WHERE
	    ewsqkid = NEW.ewsqkid
	    AND version = NEW.version
	;
	ELSE
	-- Verifica l'esitenza di mag_ewmodid (ovvero se 'e uguale o meno a NULL)
	SELECT DISTINCT mag_ewmodid, ewsqkid,version  INTO l_mag_ewmodid, l_ewsqkid, l_version
	FROM `ew_events_summary`
	WHERE
	    ewsqkid = NEW.ewsqkid
	    AND version = NEW.version
	;
	-- Se l_mag_ewmodid e' NULL, vuol dire che ho gia' inserito le info dall'arc_summary
	-- allora aggiorno solo i campi relativi alla magnitudo
	IF l_mag_ewmodid IS NULL THEN
	    UPDATE `ew_events_summary` 
	    SET mag = NEW.mag,
	    mag_error = NEW.error,
	    mag_quality = NEW.quality,
	    mag_type = NEW.szmagtype,
	    nstations = NEW.nstations,
	    nchannels = NEW.nchannels,
	    ewname = l_ewname,
	    mag_modified = NEW.modified,
	    mag_ewmodid = NEW.ewmodid
	    WHERE
	    ewsqkid = NEW.ewsqkid
	    AND version = NEW.version;
	ELSE
	-- Se l_mag_ewmodid e' diverso da NULL, vuol dire che 

	SELECT arc_ewmodid, ot_dt, ot_usec, lat, lon, z, arc_quality, erh, erz, nphtot, gap, dmin, rms, region, arc_modified
	    INTO l_arc_ewmodid, l_ot_dt, l_ot_usec, l_lat, l_lon, l_z, l_arc_quality, l_erh, l_erz, l_nphtot, l_gap, l_dmin, l_rms, l_region, l_arc_modified
	FROM `ew_events_summary`
	WHERE
	    ewsqkid = NEW.ewsqkid
	    AND version = NEW.version;

	INSERT INTO `ew_events_summary` 
	    SET mag = NEW.mag,
	    mag_error = NEW.error,
	    mag_quality = NEW.quality,
	    mag_type = NEW.szmagtype,
	    nstations = NEW.nstations,
	    nchannels = NEW.nchannels,
	    ewname = l_ewname,
	    mag_modified = NEW.modified,
	    mag_ewmodid = NEW.ewmodid,
	    ewsqkid = NEW.ewsqkid,
	    qkseq = l_qkseq,
	    version = NEW.version,
	    arc_ewmodid = l_arc_ewmodid ,
	    ot_dt = l_ot_dt,
	    ot_usec = l_ot_usec,
	    lat = l_lat,
	    lon = l_lon,
	    z = l_z,
	    arc_quality = l_arc_quality,
	    erh = l_erh,
	    erz = l_erz,
	    nphtot = l_nphtot,
	    gap = l_gap,
	    dmin = l_dmin,
	    rms = l_rms,
	    region = l_region,
	    arc_modified = l_arc_modified
	;
	END IF;
	END IF;

--  START INGV CODE
	SELECT CONCAT(IF(ISNULL(l_ewname), 'null', l_ewname), '#', IF(ISNULL(modname), 'null', modname)) INTO l_writer_mag
	FROM ew_module WHERE id = NEW.ewmodid;

	SELECT CONCAT(l_ewname, '#', m.modname) INTO l_writer_ev
	FROM ew_module m 
	JOIN ew_arc_summary e ON e.ewmodid = m.id
	WHERE e.ewsqkid = NEW.ewsqkid LIMIT 1;

	SELECT COUNT(*) INTO l_seisev_is 
	FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'daSostituire';

	IF l_seisev_is > 0 THEN
	  IF host_fill_db.`fn_fill_seisev`() = 1 THEN

	  --         INSERT INTO `mole`.`err_sp` (sp_name, error, gtime)
	  --         VALUES('sp_ins_mag_ew', 
	    --         CONCAT(
	      --         IF(ISNULL(l_qkseq), 'null', l_qkseq), ' ', 
	      --         IF(ISNULL(l_writer_mag), 'null', l_writer_mag), ' ', 
	      --         IF(ISNULL(l_writer_ev), 'null', l_writer_ev), ' ', 
	      --         IF(ISNULL(NEW.version), 'null', NEW.version), ' ', 
	      --         IF(ISNULL(NEW.mag), 'null', NEW.mag), ' ', 
	      --         IF(ISNULL(NEW.error), 'null', NEW.error), ' ', 
	      --         IF(ISNULL(NEW.quality), 'null', NEW.quality), ' ', 
	      --         IF(ISNULL(NEW.mindist), 'null', NEW.mindist), ' ', 
	      --         IF(ISNULL(NEW.azimuth), 'null', NEW.azimuth), ' ', 
	      --         IF(ISNULL(NEW.nstations), 'null', NEW.nstations), ' ', 
	      --         IF(ISNULL(NEW.nchannels), 'null', NEW.nchannels), ' ', 
	      --         IF(ISNULL(CONCAT(NEW.szmagtype, ' ', NEW.algorithm)), 'null', CONCAT(NEW.szmagtype, ' ', NEW.algorithm))), 
	    --         1);

	  CALL daSostituire.`sp_ins_mag_ew`(l_qkseq, l_writer_mag, l_writer_ev, NEW.version, NEW.mag, NEW.error, NEW.quality, NEW.mindist, NEW.azimuth, NEW.nstations, NEW.nchannels, NEW.mag_quality, CONCAT(NEW.szmagtype, '-', NEW.algorithm));
	END IF;
      END IF;
--  END INGV CODE


    END;
$$
DELIMITER ;

-- ---------------------------------------------------------------

DELIMITER $$
DROP TRIGGER IF EXISTS `tr_ew_arc_phase_a_i`$$
CREATE TRIGGER `tr_ew_arc_phase_a_i`
AFTER INSERT ON `ew_arc_phase`
    FOR EACH ROW
    BEGIN
    DECLARE l_mag_ewmodid           BIGINT    DEFAULT NULL;
    DECLARE l_ewsqkid               BIGINT    DEFAULT NULL;
    DECLARE l_qkseq                 BIGINT    DEFAULT NULL;
    DECLARE l_version               BIGINT    DEFAULT NULL;
    DECLARE l_ewname                CHAR(80)  DEFAULT NULL;

-- INGV definition
    DECLARE l_writer_ph             VARCHAR(255)    DEFAULT NULL;
    DECLARE l_writer_ev             VARCHAR(255)    DEFAULT NULL;
    DECLARE l_seisev_is             TINYINT         DEFAULT 0;
    DECLARE l_net                   CHAR(2)         DEFAULT NULL;
    DECLARE l_sta                   VARCHAR(5)      DEFAULT NULL;
    DECLARE l_cha                   CHAR(3)         DEFAULT NULL;
    DECLARE l_loc                   CHAR(2)         DEFAULT NULL;
-- end INGV definition

--  START INGV CODE
    SELECT ewname, qkseq INTO l_ewname,l_qkseq 
    FROM ew_instance, ew_sqkseq
    WHERE ew_sqkseq.ewid = ew_instance.id 
    AND ew_sqkseq.id = NEW.ewsqkid;
    
    SELECT CONCAT(IF(ISNULL(l_ewname), 'null', l_ewname), '#', IF(ISNULL(modname), 'null', modname)) INTO l_writer_ph
    FROM ew_module WHERE ew_module.id = NEW.ewmodid;

    SELECT CONCAT(l_ewname, '#', m.modname) INTO l_writer_ev
    FROM ew_module m 
    JOIN ew_arc_summary e ON e.ewmodid = m.id
    WHERE e.ewsqkid = NEW.ewsqkid 
    ORDER BY e.version DESC LIMIT 1;

    SELECT COUNT(*) INTO l_seisev_is 
    FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'daSostituire';

    IF l_seisev_is > 0 THEN
      IF host_fill_db.`fn_fill_seisev`() = 1 THEN

        SELECT net, sta, cha, loc INTO l_net, l_sta, l_cha, l_loc
        FROM `mole`.`ew_scnl`
        WHERE id = NEW.scnlid;
        
--          INSERT INTO `mole`.`err_sp` (sp_name, error, gtime)
--          VALUES('sp_ins_ph_ew', 
--          CONCAT(l_qkseq, ', ', l_writer_ph, ', ', l_writer_ph, ', ', NEW.version, ', ', l_net, ', ', l_sta, ', ', l_cha, ', ', l_loc, ', ', NEW.Plabel, ', ', NEW.Slabel, ', ', NEW.Ponset, ', ', NEW.Sonset, ', ', NEW.Pat_dt, ', ', NEW.Pat_usec, ', ', NEW.Sat_dt, ', ', NEW.Sat_usec, ', ', NEW.Pres, ', ', NEW.Sres, ', ', NEW.Pqual, ', ', NEW.Squal, ', ', NEW.codalen, ', ', NEW.codawt, ', ', NEW.Pfm, ', ', NEW.Sfm, ', ', NEW.datasrc, ', ', NEW.Md, ', ', NEW.azm, ', ', NEW.takeoff, ', ', NEW.dist, ', ', NEW.Pwt, ', ', NEW.Swt, ', ', NEW.pamp, ', ', NEW.codalenObs), 
--          1);

        CALL daSostituire.`sp_ins_ph_ew`(l_qkseq, l_writer_ph, l_writer_ev, NEW.version, l_net, l_sta, l_cha, l_loc, NEW.Plabel, NEW.Slabel, NEW.Ponset, NEW.Sonset, NEW.Pat_dt, NEW.Pat_usec, NEW.Sat_dt, NEW.Sat_usec, NEW.Pres, NEW.Sres, NEW.Pqual, NEW.Squal, NEW.codalen, NEW.codawt, NEW.Pfm, NEW.Sfm, NEW.datasrc, NEW.Md, NEW.azm, NEW.takeoff, NEW.dist, NEW.Pwt, NEW.Swt, NEW.pamp, NEW.codalenObs, NEW.ccntr_0, NEW.ccntr_1, NEW.ccntr_2, NEW.ccntr_3, NEW.ccntr_4, NEW.ccntr_5, NEW.caav_0, NEW.caav_1, NEW.caav_2, NEW.caav_3, NEW.caav_4, NEW.caav_5
        );
      END IF;
    END IF;
--  END INGV CODE

END;
$$
DELIMITER ;

-- ---------------------------------------------------------------

DELIMITER $$
DROP TRIGGER IF EXISTS `tr_ew_magnitude_phase_a_i`$$
CREATE TRIGGER `tr_ew_magnitude_phase_a_i`
AFTER INSERT ON `ew_magnitude_phase`
    FOR EACH ROW
    BEGIN
    DECLARE l_mag_ewmodid           BIGINT    DEFAULT NULL;
    DECLARE l_ewsqkid               BIGINT    DEFAULT NULL;
    DECLARE l_qkseq                 BIGINT    DEFAULT NULL;
    DECLARE l_version               BIGINT    DEFAULT NULL;
    DECLARE l_ewname                CHAR(80)  DEFAULT NULL;


-- INGV definition
    DECLARE l_writer_amp            VARCHAR(255)    DEFAULT NULL;
    DECLARE l_writer_ev             VARCHAR(255)    DEFAULT NULL;
    DECLARE l_seisev_is             TINYINT         DEFAULT 0;
    DECLARE l_net                   CHAR(2)         DEFAULT NULL;
    DECLARE l_sta                   VARCHAR(5)      DEFAULT NULL;
    DECLARE l_cha                   CHAR(3)         DEFAULT NULL;
    DECLARE l_loc                   CHAR(2)         DEFAULT NULL;
-- end INGV definition

--  START INGV CODE
    SELECT ewname, qkseq INTO l_ewname,l_qkseq 
    FROM ew_instance, ew_sqkseq
    WHERE ew_sqkseq.ewid = ew_instance.id 
    AND ew_sqkseq.id = NEW.ewsqkid;

    SELECT CONCAT(IF(ISNULL(l_ewname), 'null', l_ewname), '#', IF(ISNULL(modname), 'null', modname)) INTO l_writer_amp
    FROM ew_module WHERE ew_module.id = NEW.ewmodid;

    SELECT CONCAT(l_ewname, '#', m.modname) INTO l_writer_ev
    FROM ew_module m 
    JOIN ew_arc_summary e ON e.ewmodid = m.id
    WHERE e.ewsqkid = NEW.ewsqkid 
    ORDER BY e.version DESC LIMIT 1;

    SELECT COUNT(*) INTO l_seisev_is 
    FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'daSostituire';

    IF l_seisev_is > 0 THEN
      IF host_fill_db.`fn_fill_seisev`() = 1 THEN

        SELECT net, sta, cha, loc INTO l_net, l_sta, l_cha, l_loc
        FROM `mole`.`ew_scnl`
        WHERE id = NEW.scnlid;
        
--         INSERT INTO `mole`.`err_sp` (sp_name, error, gtime)
--         VALUES('sp_ins_amp_ew', 
--         CONCAT(l_qkseq, ' ', l_writer_amp, ' ', l_writer_ev, ' ', NEW.version, ' ', l_net, ' ', l_sta, ' ', l_cha, ' ', l_loc, ' ', NEW.mag, ' ', NEW.dist, ' ', NEW.mag_correction, ' ', NEW.Time1_dt, ' ', NEW.Time1_usec, ' ', NEW.Amp1, ' ', NEW.Period1, ' ', NEW.Time2_dt, ' ', NEW.Time2_usec, ' ', NEW.Amp2, ' ', NEW.Period2), 
--         1);

--    IF l_seisev_is > 0 THEN
        CALL daSostituire.`sp_ins_amp_ew`(l_qkseq, l_writer_amp, l_writer_ev, NEW.version, l_net, l_sta, l_cha, l_loc, NEW.mag, NEW.dist, NEW.mag_correction, NEW.Time1_dt, NEW.Time1_usec, NEW.Amp1, NEW.Period1, NEW.Time2_dt, NEW.Time2_usec, NEW.Amp2, NEW.Period2
        );
      END IF;
    END IF;
--  END INGV CODE

END;
$$
DELIMITER ;

-- ---------------------------------------------------------------

DELIMITER $$
DROP TRIGGER IF EXISTS `tr_ew_strongmotionII_a_i`$$
CREATE TRIGGER `tr_ew_strongmotionII_a_i`
AFTER INSERT ON `ew_strongmotionII`
    FOR EACH ROW
    BEGIN
    DECLARE l_mag_ewmodid           BIGINT    DEFAULT NULL;
    DECLARE l_ewsqkid               BIGINT    DEFAULT NULL;
    DECLARE l_qkseq                 BIGINT    DEFAULT NULL;
    DECLARE l_version               BIGINT    DEFAULT NULL;
    DECLARE l_ewname                CHAR(80)  DEFAULT NULL;
-- INGV definition
    DECLARE l_writer_amp            VARCHAR(255)    DEFAULT NULL;
    DECLARE l_writer_ev             VARCHAR(255)    DEFAULT NULL;
    DECLARE l_seisev_is             TINYINT         DEFAULT 0;
    DECLARE l_net                   CHAR(2)         DEFAULT NULL;
    DECLARE l_sta                   VARCHAR(5)      DEFAULT NULL;
    DECLARE l_cha                   CHAR(3)         DEFAULT NULL;
    DECLARE l_loc                   CHAR(2)         DEFAULT NULL;
-- end INGV definition


--  START INGV CODE
    SELECT ewname, qkseq INTO l_ewname,l_qkseq 
    FROM ew_instance, ew_sqkseq
    WHERE ew_sqkseq.ewid = ew_instance.id 
    AND ew_sqkseq.id = NEW.ewsqkid;

    SELECT CONCAT(IF(ISNULL(l_ewname), 'null', l_ewname), '#', IF(ISNULL(modname), 'null', modname)) INTO l_writer_amp
    FROM ew_module WHERE ew_module.id = NEW.ewmodid;

    SELECT CONCAT(l_ewname, '#', m.modname) INTO l_writer_ev
    FROM ew_module m 
    JOIN ew_arc_summary e ON e.ewmodid = m.id
    WHERE e.ewsqkid = NEW.ewsqkid 
    ORDER BY e.version DESC LIMIT 1;

    SELECT COUNT(*) INTO l_seisev_is 
    FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'daSostituire';

    IF l_seisev_is > 0 THEN
      IF host_fill_db.`fn_fill_seisev`() = 1 THEN

        SELECT net, sta, cha, loc INTO l_net, l_sta, l_cha, l_loc
        FROM `mole`.`ew_scnl`
        WHERE id = NEW.scnlid;
    
--         INSERT INTO `mole`.`err_sp` (sp_name, error, gtime)
--         VALUES('sp_ins_stm_ew', 
--         CONCAT(l_qkseq, l_writer_amp, l_writer_ev, NEW.version, l_net, l_sta, l_cha, l_loc, NEW.t_dt, NEW.t_usec, NEW.t_alt_dt, NEW.t_alt_usec, NEW.altcode, NEW.pga, NEW.tpga_dt, NEW.tpga_usec, NEW.pgv, NEW.tpgv_dt, NEW.tpgv_usec, NEW.pgd, NEW.tpgd_dt, NEW.tpgd_usec, NEW.rsa), 
--         1);

        CALL daSostituire.`sp_ins_stm_ew`(l_qkseq, l_writer_amp, l_writer_ev, NEW.version, l_net, l_sta, l_cha, l_loc, NEW.t_dt, NEW.t_usec, NEW.t_alt_dt, NEW.t_alt_usec, NEW.altcode, NEW.pga, NEW.tpga_dt, NEW.tpga_usec, NEW.pgv, NEW.tpgv_dt, NEW.tpgv_usec, NEW.pgd, NEW.tpgd_dt, NEW.tpgd_usec, NEW.rsa
        );
      END IF;
    END IF;
--  END INGV CODE

END;
$$
DELIMITER ;

-- ---------------------------------------------------------------


