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


DROP TABLE IF EXISTS `ew_params_pick_sta`;
CREATE TABLE `ew_params_pick_sta` (
    `ewmodid`               BIGINT     NOT NULL COMMENT 'Foreign key to ew_module',
    `scnlid`                BIGINT     NOT NULL COMMENT 'Foreign key to ew_scnl',
    `revision`              BIGINT     NOT NULL DEFAULT '0' COMMENT 'Revision number for channel',
    `pick_flag`             INT        DEFAULT NULL COMMENT '0, ew_params_pick will not try to pick P-wave arrivals, 1 yes',
    `pin_numb`              BIGINT     DEFAULT NULL COMMENT 'this field is not used by ew_params_pick, but exists for posterity',
    `itr1`                  BIGINT     DEFAULT NULL COMMENT '(i5) used to calculate the zero-crossing termination count',
    `minsmallzc`            BIGINT     DEFAULT NULL COMMENT '(i6) Defines minimum number of zero-crossings for valid pick',
    `minbigzc`              BIGINT     DEFAULT NULL COMMENT '(i7) Defines min number "big zero-crossings" for valid pick',
    `minpeaksize`           BIGINT     DEFAULT NULL COMMENT '(i8) Defines min amplitude (digital counts) for a valid pick',
    `maxmint`               BIGINT     DEFAULT NULL COMMENT 'Maximum interval (in samples) between zero crossings',
    `i9`                    BIGINT     DEFAULT NULL COMMENT 'Defines the minimum coda length (seconds) for a valid pick',
    `rawdatafilt`           DOUBLE     DEFAULT NULL COMMENT '(c1) Sets filter parameter RawDataFilt applied to raw trace',
    `charfuncfilt`          DOUBLE     DEFAULT NULL COMMENT '(c2) Sets filter parameter CharFuncFilt characteristic fun.',
    `stafilt`               DOUBLE     DEFAULT NULL COMMENT '(c3) Sets filter parameter (time constant) StaFilt (STA)',
    `ltafilt`               DOUBLE     DEFAULT NULL COMMENT '(c4) Sets filter parameter (time constant) LtaFilt (LTA)',
    `eventthresh`           DOUBLE     DEFAULT NULL COMMENT '(c5) Sets STA/LTA event threshold',
    `rmavfilt`              DOUBLE     DEFAULT NULL COMMENT 'Filter parameter (time constant)running mean of the absolute',
    `deadsta`               DOUBLE     DEFAULT NULL COMMENT '(c6) Sets the dead station threshold (counts).',
    `codaterm`              DOUBLE     DEFAULT NULL COMMENT '(c7) Sets the "normal" coda termination threshold (counts).',
    `altcoda`               DOUBLE     DEFAULT NULL COMMENT '(c8) Defines "noisy station level"',
    `preevent`              DOUBLE     DEFAULT NULL COMMENT '(c9) Defines alternate coda termination thresh for noisy sta',
    `erefs`                 DOUBLE     DEFAULT NULL COMMENT 'Used calculating the increment (crtinc) to be added to ecrit',
    `clipcount`             BIGINT     DEFAULT NULL COMMENT 'Specifies maximum absolute amplitude, in counts zero-to-peak',
    `ext_id`                BIGINT     DEFAULT NULL COMMENT 'Link to an external channel id',
    `modified`              TIMESTAMP  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last Review',
    PRIMARY KEY (`scnlid`,`ewmodid`,`revision`),
          INDEX (`scnlid`,`ewmodid`,`revision`),
    FOREIGN KEY (`ewmodid`)  REFERENCES ew_module(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
          INDEX (`ewmodid`),
    FOREIGN KEY (`scnlid`)  REFERENCES ew_scnl(`id`)   ON DELETE CASCADE ON UPDATE CASCADE,
          INDEX (`scnlid`),
          INDEX (`revision`),
          INDEX (`ext_id`)
)
ENGINE = MYISAM COMMENT='Configuration EarthWorm module pick_ew';


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

DROP VIEW IF EXISTS `v_ew_params_pick_id_last_revision`;
CREATE ALGORITHM = MERGE VIEW `v_ew_params_pick_id_last_revision` AS
SELECT ewmodid, scnlid, MAX(revision) as last_revision
FROM ew_params_pick_sta
GROUP BY ewmodid, scnlid
ORDER BY ewmodid, scnlid;

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

DROP VIEW IF EXISTS `v_ew_params_pick_sta_last_revision`;
CREATE ALGORITHM = MERGE VIEW `v_ew_params_pick_sta_last_revision` AS
SELECT ew_scnl.sta,ew_scnl.cha,ew_scnl.net,ew_scnl.loc, a.*
FROM ew_params_pick_sta as a
JOIN v_ew_params_pick_id_last_revision b ON (a.ewmodid = b.ewmodid AND a.scnlid = b.scnlid AND a.revision = b.last_revision)
JOIN ew_scnl ON (ew_scnl.id = a.scnlid);


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

