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

-- DROP TABLE IF EXISTS `ew_params_picks_vs_phases`;
CREATE TABLE IF NOT EXISTS `ew_params_picks_vs_phases` (
    `ewid`                  BIGINT    NOT NULL COMMENT 'Id of the EW instance running',
    `starttime`             DATETIME  NOT NULL COMMENT 'Start time ',
    `endtime`               DATETIME  NOT NULL COMMENT 'End time ',
    `scnlid`                BIGINT    NOT NULL COMMENT 'Foreign key to ew_scnl',
    `ewmodidpick`           BIGINT    NOT NULL COMMENT 'Foreign key to ew_module for picker',
    `npicks`                BIGINT    NOT NULL COMMENT 'number of picks',
    `ewmodidloc`            BIGINT             COMMENT 'Foreign key to ew_module for locator',
    `nphases`               BIGINT    NOT NULL COMMENT 'number of phases',
    `ndiff`                 BIGINT    NOT NULL COMMENT 'difference between number of picks and phases',
    `modified`              TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last Review',        
    PRIMARY KEY (`starttime`, `endtime`, `scnlid`),
    FOREIGN KEY (`ewmodidpick`) REFERENCES ew_module(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
          INDEX (`ewmodidpick`),
    FOREIGN KEY (`ewmodidloc`) REFERENCES ew_module(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
          INDEX (`ewmodidloc`),
    FOREIGN KEY (`scnlid`) REFERENCES ew_scnl(`id`)   ON DELETE CASCADE ON UPDATE CASCADE,
          INDEX (`scnlid`),
    FOREIGN KEY (`ewid`) REFERENCES ew_instance(`id`) ON DELETE CASCADE ON UPDATE CASCADE ,
          INDEX (`ewid`),
    INDEX (`starttime`),
    INDEX (`endtime`),
    INDEX (`modified`)
)
ENGINE = MYISAM COMMENT 'Table for picks vs phases';

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

DROP VIEW IF EXISTS `v_ew_params_picks_vs_phases`;
CREATE ALGORITHM = MERGE VIEW `v_ew_params_picks_vs_phases` AS
SELECT i.ewname, p.starttime, p.endtime, s.sta, s.cha, s.net, s.loc, mp.modname as pmodname, p.npicks, ml.modname as lmodname, p.nphases, p.ndiff
FROM ew_params_picks_vs_phases p
JOIN ew_instance i ON (p.ewid = i.id)
JOIN ew_scnl s ON (p.scnlid = s.id)
JOIN ew_module mp ON (p.ewmodidpick = mp.id)
JOIN ew_module ml ON (p.ewmodidloc = ml.id)

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