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

-- DROP TABLE IF EXISTS `ew_scnl`;
-- DROP TABLE IF EXISTS `ew_instance`;
-- DROP TABLE IF EXISTS `ew_module`;
-- DROP TABLE IF EXISTS `ew_spkseq`;
-- DROP TABLE IF EXISTS `ew_sqkseq`;
-- DROP TABLE IF EXISTS `ew_tracebuf`;
-- DROP TABLE IF EXISTS `ew_pick_scnl`;
-- DROP TABLE IF EXISTS `ew_link`;
-- DROP TABLE IF EXISTS `ew_quake2k`;
-- DROP TABLE IF EXISTS `ew_arc_summary`;
-- DROP TABLE IF EXISTS `ew_arc_phase`;
-- DROP TABLE IF EXISTS `ew_magnitude_summary`;
-- DROP TABLE IF EXISTS `ew_magnitude_phase`;
-- DROP TABLE IF EXISTS `ew_strongmotionII`;
-- DROP TABLE IF EXISTS `ew_error`;

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

CREATE TABLE `ew_scnl` (
    `id`                    BIGINT    NOT NULL AUTO_INCREMENT  COMMENT 'Unique incremental id',
    `sta`                   CHAR(5)   NOT NULL     COMMENT 'Station code',
    `cha`                   CHAR(3)   NOT NULL     COMMENT 'Channel/Component code',
    `net`                   CHAR(2)   NOT NULL     COMMENT 'Network code',
    `loc`                   CHAR(2)   NOT NULL     COMMENT 'Location code',
    `lat`                   DOUBLE    DEFAULT NULL COMMENT 'Latitude  (North=positive)',
    `lon`                   DOUBLE    DEFAULT NULL COMMENT 'Longitude (East=positive)',
    `ele`                   DOUBLE    DEFAULT NULL COMMENT 'Elevation',
    `name`                  CHAR(80)  DEFAULT NULL COMMENT 'Site name',
    `ext_id`                BIGINT    DEFAULT NULL COMMENT 'External table id',
    `modified`              TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last Review',        
    PRIMARY KEY (`id`),
    UNIQUE (`sta`, `cha`, `net`, `loc`),
    INDEX (`ext_id`),
    INDEX (`modified`)
)
ENGINE = MYISAM COMMENT 'SCNL information';

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

CREATE TABLE `ew_instance` (
    `id`                    BIGINT    NOT NULL AUTO_INCREMENT  COMMENT 'Unique incremental id',
    `ewname`                CHAR(80)  NOT NULL COMMENT 'Name of the EW instance running',
    `hostname`              CHAR(80)  DEFAULT NULL COMMENT 'Hostname where the instance is running',
    `ewinstallation`        CHAR(80)  DEFAULT NULL COMMENT 'EW installation of the instance (i.e. INST_INGV)',
    `modified`              TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last Review',        
    PRIMARY KEY (`id`),
    UNIQUE (`ewname` ),
    INDEX (`modified`)
)
ENGINE = MYISAM COMMENT 'EW instance name';

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

CREATE TABLE `ew_module` (
    `id`                    BIGINT    NOT NULL AUTO_INCREMENT  COMMENT 'Unique incremental id',
    `ewid`                  BIGINT    NOT NULL COMMENT 'Id of the EW instance running',
    `modname`               CHAR(32)  NOT NULL COMMENT 'EW module name',
    `modified`              TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last Review',        
    PRIMARY KEY (`id`),
    UNIQUE (`ewid`, `modname`),
    FOREIGN KEY (`ewid`) REFERENCES ew_instance(`id`) ON DELETE CASCADE ON UPDATE CASCADE ,
          INDEX (`ewid`),
    INDEX (`modified`)
)
ENGINE = MYISAM COMMENT 'EW module name';

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

CREATE TABLE `ew_spkseq` (
    `id`                    BIGINT    NOT NULL AUTO_INCREMENT  COMMENT 'Unique incremental id',
    `pkseq`                 BIGINT    NOT NULL COMMENT 'Pick sequence number from picker',
    `ewid`                  BIGINT    NOT NULL COMMENT 'Id of the EW instance running',
    `modified`              TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last Review',        
    PRIMARY KEY (`id`),
    UNIQUE (`pkseq`, `ewid`),
    FOREIGN KEY (`ewid`) REFERENCES ew_instance(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
          INDEX (`ewid`),
    INDEX (`modified`)
)
ENGINE = MYISAM COMMENT 'EW Super Pick Sequence';

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

CREATE TABLE `ew_sqkseq` (
    `id`                    BIGINT    NOT NULL AUTO_INCREMENT  COMMENT 'Unique incremental id',
    `qkseq`                 BIGINT    NOT NULL COMMENT 'Quake sequence number from binder',
    `ewid`                  BIGINT    NOT NULL COMMENT 'Id of the EW instance running',
    `modified`              TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last Review',        
    PRIMARY KEY (`id`),
    UNIQUE (`qkseq`, `ewid`),
    FOREIGN KEY (`ewid`) REFERENCES ew_instance(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
          INDEX (`ewid`),
    INDEX (`modified`)
)
ENGINE = MYISAM COMMENT 'EW Super Quake Sequence';

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

CREATE TABLE `ew_tracebuf` (
    `id`                    BIGINT    NOT NULL AUTO_INCREMENT  COMMENT 'Unique incremental id',
    `ewmodid`               BIGINT    NOT NULL COMMENT 'Foreign key to ew_module',
    `pinno`                 BIGINT             COMMENT 'Pin number',
    `nsamp`                 BIGINT    NOT NULL COMMENT 'Number of samples in packet',
    `starttime_dt`          DATETIME  NOT NULL COMMENT 'datetime part of time of first sample in epoch seconds',
    `starttime_usec`        INT       NOT NULL COMMENT 'useconds part of time of first sample in epoch seconds',
    `endtime_dt`            DATETIME  NOT NULL COMMENT 'datetime part of time of last sample in epoch seconds',
    `endtime_usec`          INT       NOT NULL COMMENT 'useconds part of time of last sample in epoch seconds',
    `samprate`              DOUBLE    NOT NULL COMMENT 'Sample rate; nominal',
    `scnlid`                BIGINT    NOT NULL COMMENT 'Foreign key to ew_scnl',
    `version`               CHAR(10)           COMMENT 'numeric version fields (99-99)',
    `datatype`              CHAR(3)   NOT NULL COMMENT 'Data format code',
    `quality`               CHAR(10)           COMMENT 'Data-quality fields (99-99)',
    `modified`              TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last Review',        
    PRIMARY KEY (`id`),
    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 (starttime_dt),
    INDEX (endtime_dt),
    INDEX (`modified`)
)
ENGINE = MYISAM COMMENT 'Tracebuf messages from EW';

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

CREATE TABLE `ew_pick_scnl` (
    `id`                    BIGINT   NOT NULL AUTO_INCREMENT  COMMENT 'Unique incremental id',
    `ewmodid`               BIGINT   NOT NULL COMMENT 'Foreign key to ew_module',
    `ewspkid`               BIGINT   NOT NULL COMMENT 'Foreign key to ew_spkseq',
    `scnlid`                BIGINT   NOT NULL COMMENT 'Foreign key to ew_scnl',
    `dir`                   CHAR(1)           COMMENT 'First-motion descriptor (U,D,-space-,?) => pick->fm',
    `wt`                    TINYINT           COMMENT 'Pick weight or quality (0-4)',
    `tpick_dt`              DATETIME NOT NULL COMMENT 'Time of pick - datetime part',
    `tpick_usec`            INT      NOT NULL COMMENT 'Time of pick - useconds',
    `pamp_0`                BIGINT            COMMENT 'pick->pamp[0]',
    `pamp_1`                BIGINT            COMMENT 'pick->pamp[1]',    
    `pamp_2`                BIGINT            COMMENT 'pick->pamp[2]',
    `caav_0`                BIGINT DEFAULT NULL COMMENT 'coda->caav[0] coda avg abs value (counts) 2s window',
    `caav_1`                BIGINT DEFAULT NULL COMMENT 'coda->caav[1] coda avg abs value (counts) 2s window',
    `caav_2`                BIGINT DEFAULT NULL COMMENT 'coda->caav[2] coda avg abs value (counts) 2s window',
    `caav_3`                BIGINT DEFAULT NULL COMMENT 'coda->caav[3] coda avg abs value (counts) 2s window',
    `caav_4`                BIGINT DEFAULT NULL COMMENT 'coda->caav[4] coda avg abs value (counts) 2s window',
    `caav_5`                BIGINT DEFAULT NULL COMMENT 'coda->caav[5] coda avg abs value (counts) 2s window',
    `dur`                   BIGINT DEFAULT NULL COMMENT 'coda->dur coda duration (seconds since pick)',
    `modified`              TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last Review',        
    PRIMARY KEY (`id`),
    FOREIGN KEY (`ewmodid`)  REFERENCES ew_module(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
          INDEX (`ewmodid`),
    FOREIGN KEY (`ewspkid`) REFERENCES ew_spkseq(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
          INDEX (`ewspkid`),
    FOREIGN KEY (`scnlid`)  REFERENCES ew_scnl(`id`)   ON DELETE CASCADE ON UPDATE CASCADE,
          INDEX (`scnlid`),
    INDEX (`ewmodid`, `ewspkid`),
    INDEX (`tpick_dt`),
    INDEX (`modified`)
)
ENGINE = MYISAM COMMENT 'Automatic pick_scnl messages from EW';

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

CREATE TABLE `ew_link` (
    `id`                    BIGINT NOT NULL AUTO_INCREMENT  COMMENT 'Unique incremental id',
    `ewmodid`               BIGINT NOT NULL COMMENT 'Foreign key to ew_module',
    `ewsqkid`               BIGINT NOT NULL COMMENT 'Foreign key to ew_sqkseq',
    `ewspkid`               BIGINT NOT NULL COMMENT 'Foreign key to ew_spkseq',
    `iphs`                  INT             COMMENT 'Phase number',
    `modified`              TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last Review',            
    PRIMARY KEY (`id`),
    FOREIGN KEY (`ewmodid`)  REFERENCES ew_module(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
          INDEX (`ewmodid`),
    FOREIGN KEY (`ewsqkid`) REFERENCES ew_sqkseq(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
          INDEX (`ewsqkid`),
    FOREIGN KEY (`ewspkid`) REFERENCES ew_spkseq(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
          INDEX (`ewspkid`),
    INDEX (`ewmodid`, `ewsqkid`, `ewspkid`),
    INDEX (`ewmodid`, `ewsqkid`),
    INDEX (`ewmodid`, `ewspkid`),
    INDEX (`modified`)
)
ENGINE = MYISAM COMMENT 'Automatic link messages from EW';

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

CREATE TABLE `ew_quake2k` (
    `id`                    BIGINT   NOT NULL AUTO_INCREMENT  COMMENT 'Unique incremental id',
    `ewmodid`               BIGINT   NOT NULL COMMENT 'Foreign key to ew_module',
    `ewsqkid`               BIGINT   NOT NULL COMMENT 'Foreign key to ew_sqkseq',
    `quake_ot_dt`           DATETIME NOT NULL COMMENT 'Quake Origin Time - datetime part',
    `quake_ot_usec`         INT      NOT NULL COMMENT 'Quake Origin Time - useconds',   
    `lat`                   DOUBLE            COMMENT 'Latitude',
    `lon`                   DOUBLE            COMMENT 'Longitude',
    `depth`                 DOUBLE            COMMENT 'Depht => QUAKE2K->z',
    `rms`                   DOUBLE            COMMENT 'Rms Residual',
    `dmin`                  DOUBLE            COMMENT 'Distance to closest station',
    `ravg`                  DOUBLE            COMMENT 'Average or median station distance',
    `gap`                   DOUBLE            COMMENT 'Largest azimuth without picks',
    `nph`                   INT               COMMENT 'Linked phases',
    `modified`              TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last Review',            
    PRIMARY KEY (`id`),
    FOREIGN KEY (`ewmodid`) REFERENCES ew_module(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
          INDEX (`ewmodid`),
    FOREIGN KEY (`ewsqkid`) REFERENCES ew_sqkseq(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
          INDEX (`ewsqkid`),
    INDEX (`ewmodid`, `ewsqkid`),
    INDEX (`quake_ot_dt`),
    INDEX (`modified`)
)
ENGINE = MYISAM COMMENT 'Automatic link messages from EW';

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

CREATE TABLE `ew_arc_summary` (
    `id`                    BIGINT   NOT NULL AUTO_INCREMENT  COMMENT 'Unique incremental id',
    `ewmodid`               BIGINT   NOT NULL COMMENT 'Foreign key to ew_module',
    `ewsqkid`               BIGINT   NOT NULL COMMENT 'Foreign key to ew_sqkseq',
    `ot_dt`                 DATETIME NOT NULL COMMENT 'Origin Time - datetime part',
    `ot_usec`               INT      NOT NULL COMMENT 'Origin Time - useconds',
    `lat`                   DOUBLE            COMMENT 'latitude (North=positive)',
    `lon`                   DOUBLE            COMMENT 'longitude(East=positive)',
    `z`                     DOUBLE            COMMENT 'depth (down=positive)',
    `nph`                   INT               COMMENT '# phases (P&S) w/ weight >0.1',
    `nphS`                  INT               COMMENT '# S phases w/ weight >0.1',
    `nphtot`                INT               COMMENT '# phases (P&S) w/ weight >0.0',
    `nPfm`                  INT               COMMENT '# P first motions',
    `gap`                   INT               COMMENT 'maximum azimuthal gap',
    `dmin`                  INT               COMMENT 'distance (km) to nearest station',
    `rms`                   DOUBLE            COMMENT 'RMS travel time residual',
    `e0az`                  INT               COMMENT 'azimuth of largest principal error',
    `e0dp`                  INT               COMMENT 'dip of largest principal error',
    `e0`                    DOUBLE            COMMENT 'magnitude (km) of largest principal error',
    `e1az`                  INT               COMMENT 'azimuth of intermediate principal error',
    `e1dp`                  INT               COMMENT 'dip of intermediate principal error',
    `e1`                    DOUBLE            COMMENT 'magnitude (km) of intermed principal error',
    `e2`                    DOUBLE            COMMENT 'magnitude (km) of smallest principal error',
    `erh`                   DOUBLE            COMMENT 'horizontal error (km)',
    `erz`                   DOUBLE            COMMENT 'vertical error (km)',
    `Md`                    DOUBLE            COMMENT 'duration magnitude',
    `reg`                   CHAR(4)           COMMENT 'location region',
    `labelpref`             CHAR              COMMENT 'character describing preferred magnitude',
    `Mpref`                 DOUBLE            COMMENT 'preferred magnitude',
    `wtpref`                DOUBLE            COMMENT 'weight (~ # readings) of preferred Mag',
    `mdtype`                CHAR              COMMENT 'Coda duration magnitude type code',
    `mdmad`                 DOUBLE            COMMENT 'Median-absolute-difference of duration mags',
    `mdwt`                  DOUBLE            COMMENT 'weight (~ # readings) of Md',
    `version`               BIGINT  NOT NULL  COMMENT 'version number of the origin',
    `quality`               CHAR(2)           COMMENT 'INGV quality code of the location (computed by ew2moledb)',
    `modified`              TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last Review',            
    PRIMARY KEY (`id`),
    UNIQUE (`ewmodid`, `ewsqkid`, `version`),
    FOREIGN KEY (`ewmodid`) REFERENCES ew_module(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
          INDEX (`ewmodid`),
    FOREIGN KEY (`ewsqkid`) REFERENCES ew_sqkseq(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
          INDEX (`ewsqkid`),
-- ?    INDEX (`ewmodid`, `ewsqkid`, `version`),
    INDEX (`ot_dt`),
    INDEX (`modified`)
)
ENGINE = MYISAM COMMENT 'Automatic arc summary from EW arc messages';

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

CREATE TABLE `ew_arc_phase` (
    `id`                    BIGINT   NOT NULL AUTO_INCREMENT  COMMENT 'Unique incremental id',
    `ewmodid`               BIGINT   NOT NULL COMMENT 'Foreign key to ew_module',
    `ewsqkid`               BIGINT   NOT NULL COMMENT 'Foreign key to ew_sqkseq',
    `scnlid`                BIGINT   NOT NULL COMMENT 'Foreign key to ew_scnl',
    `Plabel`                CHAR              COMMENT 'P phase label',
    `Slabel`                CHAR              COMMENT 'S phase label',
    `Ponset`                CHAR              COMMENT 'P phase onset',
    `Sonset`                CHAR              COMMENT 'S phase onset',
    `Pat_dt`                DATETIME          COMMENT 'P-arrival-time - datetime part',
    `Pat_usec`              INT               COMMENT 'P-arrival-time - useconds',
    `Sat_dt`                DATETIME          COMMENT 'S-arrival-time - datetime part',
    `Sat_usec`              INT               COMMENT 'S-arrival-time - useconds',
    `Pres`                  DOUBLE            COMMENT 'P travel time residual',
    `Sres`                  DOUBLE            COMMENT 'S travel time residual',
    `Pqual`                 INT               COMMENT 'Assigned P weight code',
    `Squal`                 INT               COMMENT 'Assigned S weight code',
    `codalen`               INT               COMMENT 'Coda duration time',
    `codawt`                INT               COMMENT 'Coda weight',
    `Pfm`                   CHAR              COMMENT 'P first motion',
    `Sfm`                   CHAR              COMMENT 'S first motion',
    `datasrc`               CHAR              COMMENT 'Data source code.',
    `Md`                    DOUBLE            COMMENT 'Station duration magnitude',
    `azm`                   INT               COMMENT 'azimuth',
    `takeoff`               INT               COMMENT 'emergence angle at source',
    `dist`                  DOUBLE            COMMENT 'epicentral distance (km)',
    `Pwt`                   DOUBLE            COMMENT 'P weight actually used.',
    `Swt`                   DOUBLE            COMMENT 'S weight actually used.',
    `pamp`                  INT               COMMENT 'peak P-wave half amplitude',
    `codalenObs`            INT               COMMENT 'Coda duration time (Measured)',
    `ccntr_0`               INT               COMMENT 'Window center from P time',
    `ccntr_1`               INT               COMMENT 'Window center from P time',
    `ccntr_2`               INT               COMMENT 'Window center from P time',
    `ccntr_3`               INT               COMMENT 'Window center from P time',
    `ccntr_4`               INT               COMMENT 'Window center from P time',
    `ccntr_5`               INT               COMMENT 'Window center from P time',
    `caav_0`                INT               COMMENT 'Average Amplitude for ccntr[x]',
    `caav_1`                INT               COMMENT 'Average Amplitude for ccntr[x]',
    `caav_2`                INT               COMMENT 'Average Amplitude for ccntr[x]',
    `caav_3`                INT               COMMENT 'Average Amplitude for ccntr[x]',
    `caav_4`                INT               COMMENT 'Average Amplitude for ccntr[x]',
    `caav_5`                INT               COMMENT 'Average Amplitude for ccntr[x]',
    `version`               BIGINT   NOT NULL COMMENT 'version number of the origin',
    `modified`              TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last Review',            
    PRIMARY KEY  (`id`),
    FOREIGN KEY (`ewmodid`) REFERENCES ew_module(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
          INDEX (`ewmodid`),
    FOREIGN KEY (`ewsqkid`) REFERENCES ew_sqkseq(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
          INDEX (`ewsqkid`),
    FOREIGN KEY (`scnlid`) REFERENCES ew_scnl(`id`)   ON DELETE CASCADE ON UPDATE CASCADE,
          INDEX (`scnlid`),
    INDEX (`ewmodid`, `ewsqkid`, `version`),
    INDEX (`modified`)
)                       
ENGINE = MYISAM COMMENT 'Automatic arc phase from EW arc messages';

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

CREATE TABLE `ew_magnitude_summary` (
    `id`                    BIGINT NOT NULL AUTO_INCREMENT  COMMENT 'Unique incremental id',
    `ewmodid`               BIGINT NOT NULL COMMENT 'Foreign key to ew_module',
    `ewsqkid`               BIGINT   NOT NULL COMMENT 'Foreign key to ew_sqkseq',
    `version`               BIGINT NOT NULL COMMENT 'version number of the origin',
    `mag`                   DOUBLE          COMMENT 'REQUIRED: magnitude value',
    `error`                 DOUBLE          COMMENT 'OPTIONAL: Error estimate (std deviation for Ml/AVG)',
    `quality`               DOUBLE          COMMENT 'OPTIONAL: [0.0 - 1.0], -1.0 for NULL',
    `mindist`               DOUBLE          COMMENT 'OPTIONAL: Minumun distance from location to station',
    `azimuth`               INT             COMMENT 'OPTIONAL: Maximum azimuthal gap for stations',
    `nstations`             INT             COMMENT 'OPTIONAL: Number of stations used to compute magnitude',
    `nchannels`             INT             COMMENT 'OPTIONAL: Number of data channels used to compute magnitude.',
    `qauthor`               CHAR(50)        COMMENT 'REQUIRED: NTS, author of the eventid',
    `qdds_version`          INT             COMMENT 'qdds',
    `imagtype`              INT             COMMENT 'REQUIRED: Magnitude type from MagNames table above',
    `szmagtype`             CHAR(6)         COMMENT 'Magnitude type string',
    `algorithm`             CHAR(8)         COMMENT 'OPTIONAL: NTS, AVG for average',
    `mag_quality`           CHAR(2)           COMMENT 'INGV quality code of the magnitude (computed by ew2moledb)',
    `modified`              TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last Review',            
    PRIMARY KEY (`id`),                  
    UNIQUE (`ewmodid`, `ewsqkid`, `version`),
    FOREIGN KEY (`ewmodid`) REFERENCES ew_module(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
          INDEX (`ewmodid`),
    FOREIGN KEY (`ewsqkid`) REFERENCES ew_sqkseq(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
          INDEX (`ewsqkid`),
    INDEX (`modified`)
-- ?    INDEX (`ewmodid`, `ewsqkid`, `version`)
)
ENGINE = MYISAM COMMENT 'Automatic magnitude messages from EW, summary line';

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

CREATE TABLE `ew_magnitude_phase` (
    `id`                    BIGINT   NOT NULL AUTO_INCREMENT  COMMENT 'Unique incremental id',
    `ewmodid`               BIGINT   NOT NULL COMMENT 'Foreign key to ew_module',
    `ewsqkid`               BIGINT   NOT NULL COMMENT 'Foreign key to ew_sqkseq',
    `version`               BIGINT   NOT NULL COMMENT 'version number of the origin',
    `scnlid`                BIGINT   NOT NULL COMMENT 'Foreign key to ew_scnl',
    `mag`                   DOUBLE            COMMENT 'local magnitude for this channel',
    `dist`                  DOUBLE            COMMENT 'station-event distance used for local magnitude',
    `mag_correction`        DOUBLE            COMMENT 'correction that was added to get this local mag',
    `Time1_dt`              DATETIME          COMMENT 'time of the first pick - datetime part',
    `Time1_usec`            INT               COMMENT 'time of the first pick - usec',    
    `Amp1`                  DOUBLE            COMMENT 'amplitude of the first pick',
    `Period1`               DOUBLE            COMMENT 'period associated with the first pick',
    `Time2_dt`              DATETIME          COMMENT 'time of the second pick (if used) - datetime part',
    `Time2_usec`            INT               COMMENT 'time of the second pick (if used) - usec',
    `Amp2`                  DOUBLE            COMMENT 'amplitude of the second pick (if used)',
    `Period2`               DOUBLE            COMMENT 'period of the second pick (if used)',
    `modified`              TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last Review',            
    PRIMARY KEY (`id`),
    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`),
    FOREIGN KEY (`ewsqkid`) REFERENCES ew_sqkseq(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
          INDEX (`ewsqkid`),
    INDEX (`ewmodid`, `ewsqkid`, `version`),
    INDEX (`modified`)
)
ENGINE = MYISAM COMMENT 'Automatic magnitude messages from EW, phase line';

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

CREATE TABLE `ew_strongmotionII` (
    `id`                   BIGINT   NOT NULL AUTO_INCREMENT  COMMENT 'Unique incremental id',
    `ewmodid`              BIGINT   NOT NULL  COMMENT 'Foreign key to ew_module',
    `ewsqkid`              BIGINT   NOT NULL  COMMENT 'Foreign key to ew_sqkseq',
    `version`              BIGINT   DEFAULT NULL  COMMENT 'version number of the origin',
    `scnlid`               BIGINT   NOT NULL   COMMENT 'Foreign key to ew_scnl',
    `qauthor`              CHAR(50)            COMMENT 'OPTIONAL: NTS, author of the eventid',
    `t_dt`                 DATETIME            COMMENT 'time: trigger reported by SM box - datetime part',
    `t_usec`               INT                 COMMENT 'time: trigger reported by SM box -usec', 
    `t_alt_dt`             DATETIME            COMMENT 'alternate time: trigger reported by SM box - datetime part',
    `t_alt_usec`           INT                 COMMENT 'alternate time: trigger reported by SM box -usec',
    `altcode`              INT                 COMMENT 'code specifying the source of the alternate time field',
    `pga`                  DOUBLE              COMMENT 'REQUIRED: peak ground acceleration (cm/s/s)',
    `tpga_dt`              DATETIME            COMMENT 'OPTIONAL: time of pga - datetime part',
    `tpga_usec`            INT                 COMMENT 'OPTIONAL: time of pga - usec',
    `pgv`                  DOUBLE              COMMENT 'REQUIRED: peak ground velocity (cm/s)',
    `tpgv_dt`              DATETIME            COMMENT 'OPTIONAL: time of pgv - datetime part',
    `tpgv_usec`            INT                 COMMENT 'OPTIONAL: time of pgv - usec',
    `pgd`                  DOUBLE              COMMENT 'REQUIRED: peak ground displacement (cm)',
    `tpgd_dt`              DATETIME            COMMENT 'OPTIONAL: time of pgd - datetime part',
    `tpgd_usec`            INT                 COMMENT 'OPTIONAL: time of pgd - usec',
    `rsa`                  CHAR(255)           COMMENT 'RSA(response spectrum accel)  string format: NRSA/periodRSA valueRSA/periodRSA valueRSA',
    `modified`             TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last Review',
    PRIMARY KEY (`id`),                
    FOREIGN KEY (`ewmodid`) REFERENCES ew_module(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
          INDEX (`ewmodid`),
    FOREIGN KEY (`ewsqkid`) REFERENCES ew_sqkseq(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
          INDEX (`ewsqkid`),
    FOREIGN KEY (`scnlid`) REFERENCES ew_scnl(`id`)   ON DELETE CASCADE ON UPDATE CASCADE,
          INDEX (`scnlid`),
    INDEX (`ewmodid`, `ewsqkid`, `version`),
    INDEX (`modified`)
)   
ENGINE = MYISAM COMMENT 'Automatic strong motion messages from EW';

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

CREATE TABLE `ew_error` (
    `id`                    BIGINT          NOT NULL AUTO_INCREMENT  COMMENT 'Unique incremental id',
    `ewmodid`               BIGINT          NOT NULL COMMENT 'Foreign key to ew_module',
    `time_dt`               DATETIME        NOT NULL  COMMENT 'datetime part of time of first sample in epoch seconds',
    `message`               VARCHAR(256) DEFAULT NULL COMMENT 'Error message',
    `modified`              TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last Review',
    PRIMARY KEY (`id`),
    FOREIGN KEY (`ewmodid`) REFERENCES ew_module(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
          INDEX (`ewmodid`),
    INDEX (time_dt),
    INDEX (`modified`)
)
ENGINE = MYISAM COMMENT 'Error messages from EW';

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

