-- MySQL-Server Table Script
-- Created from Oracle Script
-- By Stanley Ye (sye@sutron.com)

-- 06/09/04
-- Adapt to MySQL


USE XC_DATA;

CREATE TABLE XC_SITES
 (
    STATION_ID VARCHAR(30) NOT NULL ,
    ENABLED VARCHAR(1) NOT NULL     ,
    UNIT_ID VARCHAR(15)             ,
    SITE_TYPE INT                   ,
    STATUS INT                      ,
    IPADDRESS VARCHAR(20)           ,
    RADIO_PATH_LIST VARCHAR(30)     ,
    PHONE_NUMBER_LIST VARCHAR(40)   ,
    PHONE_NUMBER2 VARCHAR(20)       ,
    USER_NAME VARCHAR(10)           ,
    PASSWORD VARCHAR(10)            ,
    COMPORT_LIST VARCHAR(15)        ,
    ACKDELAY_LIST VARCHAR(10)       ,
    RESPONSE_TIMEOUT_LIST VARCHAR(10) ,
    POLL_TYPE INT                   ,
    LOGIN_REQD VARCHAR(1)           ,
    QUICK_PROTOCOL VARCHAR(1)       ,
    AUTO_PROTOCOL VARCHAR(1)        ,
    CONCENTRATOR VARCHAR(1)         ,
    HOURS_AHEAD DOUBLE              ,
    RDI_BASE_TIME DATETIME          ,
    RDI_INTERVAL DATETIME           ,
    RDI_STAYON DATETIME             ,
    TEMPLATE_NAME VARCHAR(50)       ,
    SITE_COMMENT VARCHAR(32)        ,
    SITE_LONG_NAME VARCHAR(50)      ,
    SITE_CODE INTEGER               ,
    USGS_ID VARCHAR(10)             ,
    INSTALLATION_DATE DATETIME      ,
    AGENCY VARCHAR(32)              ,
    COUNTRY VARCHAR(32)             ,
    DISTRICT VARCHAR(32)            ,
    CITY VARCHAR(32)                ,
    STATE VARCHAR(20)               ,
    COUNTY VARCHAR(32)              ,
    RESERVOIR VARCHAR(32)           ,
    BASIN VARCHAR(32)               ,
    RIVER_MILE DOUBLE               ,
    LATITUDE DOUBLE                 ,
    LONGITUDE DOUBLE                ,
    ELEVATION DOUBLE                ,
    COEFFICIENT1 DOUBLE             ,
    COEFFICIENT2 DOUBLE             ,
    COEFFICIENT3 DOUBLE             ,
    WEIGHTING_FACTOR DOUBLE         ,
    SATELLITE_ID VARCHAR(8)         ,
    PRIMARY_CHANNEL INT             ,
    RANDOM_CHANNEL INT              ,
    REPORTING_TIME DATETIME         ,
    REPORTING_INTERVAL DOUBLE       ,
    LAST_UPDATE DATETIME            ,
    TZONE_CODE VARCHAR(4)           ,
    GMT_OFFSET DOUBLE               ,
    SHEF_ID VARCHAR(8)              ,
    ALTERNATE_CHAR_ID_1 VARCHAR(10) ,
    ALTERNATE_CHAR_ID_2 VARCHAR(10) ,
    GPSTRING1 VARCHAR(20)           ,
    GPSTRING2 VARCHAR(20)           ,
    GPNUMBER1 DOUBLE                ,
    GPNUMBER2 DOUBLE                ,
    SETUP_FILE_NAME VARCHAR(255)    ,
    LOG_FILE_NAME VARCHAR(255)      ,
    PICTURE_FILE_NAME VARCHAR(255)  ,
    MODEM_INIT_STRING VARCHAR(20)   ,
    MODEM_HANGUP_STRING VARCHAR(20) ,
    LAST_POLL DATETIME              ,
    TEMPLATE_FLAG        INT NULL DEFAULT 1,
    MBDEVICE_ID VARCHAR(4)          ,
    MBCONSEC_REGISTER VARCHAR(1)    ,
    MBBASE_REGISTER INT             ,
    MBREGISTER_TYPE INT             ,
    MBREGISTER_COUNT INT            ,
    MSGPARITY INT                   ,
    IMEI_ID VARCHAR(15)             ,
    SMS_ID VARCHAR(15)              ,

    CONSTRAINT STE_PK PRIMARY KEY(STATION_ID)
);

CREATE TABLE XC_SITESENSORS
 (
    STE_STATION_ID VARCHAR(30) NOT NULL ,
    SENSORNAME VARCHAR(15) NOT NULL     ,
    ENABLED VARCHAR(1) NOT NULL         ,
    DATA_TABLE VARCHAR(12) NOT NULL     ,
    TAGNAME VARCHAR(15)                 ,
    RAWLIST VARCHAR(30)                 ,
    RIGHT_DIGIT INT                     ,
    LEFT_DIGIT INT                      ,
    IS_TEXT VARCHAR(7)                  ,
    ROUNDOFF_TIME INT                   ,
    DATA_STORE_INTERVAL INT             ,
    UNITS VARCHAR(10)                   ,
    TEMPLATE_NAME VARCHAR(50)           ,
    STATUS INT                          ,
    DESCRIPTION VARCHAR(32)             ,
    MANUFACTURER VARCHAR(20)            ,
    MODEL_NUMBER VARCHAR(20)            ,
    SHEF_PE_CODE VARCHAR(7)             ,
    PARAMETER_CODE VARCHAR(12)          ,
    ALT_PARAMETER_CODE_1 VARCHAR(12)    ,
    ALT_PARAMETER_CODE_2 VARCHAR(12)    ,
    EQUATION VARCHAR(128)               ,
    RATING_TABLE VARCHAR(128)           ,
    OFFSET DOUBLE                       ,
    SHIFT DOUBLE                        ,
    HIGH_HIGH_LIMIT DOUBLE              ,
    HIGH_HIGH_ALARM INT                 ,
    HIGH_LIMIT DOUBLE                   ,
    HIGH_ALARM INT                      ,
    LOW_LIMIT DOUBLE                    ,
    LOW_ALARM INT                       ,
    LOW_LOW_LIMIT DOUBLE                ,
    LOW_LOW_ALARM INT                   ,
    ROC_LIMIT DOUBLE                    ,
    ROC_ALARM INT                       ,
    ROC_INTERVAL DOUBLE                 ,
    DEADBAND DOUBLE                     ,
    NO_CHANGE_INTERVAL DOUBLE           ,
    NO_CHANGE_ALARM INT                 ,
    TEXT_LIMIT VARCHAR(9)               ,
    TEXT_ALARM INT                      ,
    STD_DEV_LIMIT DOUBLE                ,
    STD_DEV_ALARM INT                   ,
    STD_DEV_INTERVAL DOUBLE             ,
    AVG_LIMIT DOUBLE                    ,
    AVG_ALARM INT                       ,
    AVG_INTERVAL DOUBLE                 ,
    GPNUMBER1 DOUBLE                    ,
    GPNUMBER2 DOUBLE                    ,
    GPSTRING1 VARCHAR(20)               ,
    GPSTRING2 VARCHAR(20)               ,
    ALERT_ID DOUBLE                     ,
    ALERT_TYPE VARCHAR(10)              ,
    COEFFICIENT1 DOUBLE                 ,
    COEFFICIENT2 DOUBLE                 ,
    COEFFICIENT3 DOUBLE                 ,
    COEFFICIENT4 DOUBLE                 ,
    COEFFICIENT5 DOUBLE                 ,
    REFERENCE_1 DOUBLE                  ,
    REFERENCE_2 DOUBLE                  ,
    DATA_EXCH_NUM VARCHAR(100)          ,
    MIKE11_LABEL_1 VARCHAR(50)          ,
    MIKE11_LABEL_2 VARCHAR(50)          ,
    NO_ALARM_INTERVAL DOUBLE            ,
    MBBASE_REGISTER INT                 ,
    MBREGISTER_TYPE INT                 ,
    MBTAG_TYPE INT                      ,
    MBMSW INT                           ,
    USE_REJECT_LOW VARCHAR(1)           ,
    REJECT_LOW_LIMIT DOUBLE             ,
    USE_REJECT_HIGH VARCHAR(1)          ,
    REJECT_HIGH_LIMIT DOUBLE            ,
        CONSTRAINT SITSEN_PK PRIMARY KEY (SENSORNAME,STE_STATION_ID),
        CONSTRAINT SITSEN_STE_FK FOREIGN KEY (STE_STATION_ID) REFERENCES XC_SITES (STATION_ID)
 );

CREATE TABLE XC_DATA1
(
   STATION_ID VARCHAR(30) NOT NULL ,
   SENSORNAME VARCHAR(15) NOT NULL ,
   TIME_TAG DATETIME NOT NULL      ,
   SOURCE VARCHAR(1) NOT NULL      ,
   ORIG_VALUE DOUBLE    NOT NULL   ,
   ED_VALUE DOUBLE                 ,
   FLAG1 VARCHAR(1)                ,
   FLAG2 VARCHAR(1)                ,
   FLAG3 VARCHAR(1)                ,
   FLAG4 VARCHAR(1)                ,
   HIGH_HIGH_FLAG INT              ,
   HIGH_FLAG INT                   ,
   LOW_FLAG INT                    ,
   LOW_LOW_FLAG INT                ,
   ROC_FLAG INT                    ,
   NO_CHG_FLAG INT                 ,
   ALARM_FLAG INT                  ,
   STD_DEV_FLAG INT                ,
   AVG_FLAG INT                    ,
   CONSTRAINT DAT1_PK PRIMARY KEY
   (
       TIME_TAG ,
       SOURCE    ,
       STATION_ID,
       SENSORNAME
   )
);

CREATE TABLE XC_TXTDATA1
 (
    STATION_ID VARCHAR(30) NOT NULL        ,
    SENSORNAME VARCHAR(15) NOT NULL        ,
    TIME_TAG DATETIME NOT NULL             ,
    SOURCE VARCHAR(1) NOT NULL             ,
    TEXT_VALUE VARCHAR(255) NOT NULL       ,
    FLAG1 VARCHAR(1)                       ,
    FLAG2 VARCHAR(1)                       ,
    FLAG3 VARCHAR(1)                       ,
    FLAG4 VARCHAR(1)                       ,
    MESSAGE_STATUS VARCHAR(2)              ,
    TXT_ALARM_FLAG INT                     ,
    CONSTRAINT TXD1_PK PRIMARY KEY
     (TIME_TAG
     ,SOURCE
     ,STATION_ID
     ,SENSORNAME)
 );


CREATE TABLE XC_GOESQC
 (
    NESDIS_ID VARCHAR(8) NOT NULL      ,
    TIME_TAG DATETIME NOT NULL         ,
    SOURCE VARCHAR(1) NOT NULL         ,
    STATION_ID VARCHAR(30)             ,
    FAILURE_CODE VARCHAR(1)            ,
    SIG_STRENGTH DOUBLE                ,
    FREQ_OFFSET DOUBLE                 ,
    MOD_INDEX VARCHAR(1)               ,
    MOD_QUAL VARCHAR(1)                ,
    CHANNEL INT                        ,
    SAT_LOC VARCHAR(1)                 ,
    UC_STATUS VARCHAR(2)               ,
    MESSAGE_LEN INT                    ,
    ACTUAL_LEN INT                     ,
    PARITY INT                         ,
    BUFFER_EMPTY INT                   ,
    BATTERY_VOLTAGE DOUBLE             ,
    SPARE1 DOUBLE                      ,
    SPARE2 DOUBLE                      ,
    CONSTRAINT GOESQC_PK PRIMARY KEY
     (NESDIS_ID
     ,TIME_TAG
     ,SOURCE)
 );

-- PROMPT Creating Table 'XC_RTUQC'
CREATE TABLE XC_RTUQC
(
   STATION_ID           VARCHAR(30) NOT NULL  ,
   TIME_TAG            DATETIME NOT NULL      ,
   RX_TX               VARCHAR(1) NOT NULL    ,
   PORT                VARCHAR(30)            ,
   SEQUENCENUM         DOUBLE                 ,
   OPCODE              DOUBLE                 ,
   STATUS              INT                    ,
   RETRIES             INT                    ,
   GOOD                INT                    ,
   BAD                 INT                    ,
   CRC                 INT                    ,
   BATTERY_VOLTAGE     DOUBLE                 ,
   SPARE1              DOUBLE                 ,
   SPARE2              DOUBLE                 ,
   CONSTRAINT RTU_PK PRIMARY KEY (
       STATION_ID,
       TIME_TAG,
       RX_TX
   )
);

CREATE TABLE XC_PPLIST
(
   STATION_ID VARCHAR(30) NOT NULL      ,
   DATA_LABEL VARCHAR(20) NOT NULL      ,
   SENSORNAME VARCHAR(15) NOT NULL      ,
   FUNCTION_NAME VARCHAR(10) NOT NULL   ,
   CONSTRAINT PPLIST_PK PRIMARY KEY
     (STATION_ID
     ,SENSORNAME
     ,DATA_LABEL
     ,FUNCTION_NAME)
 );

CREATE TABLE XC_PPDATA1
(
   STATION_ID VARCHAR(30) NOT NULL      ,
   DATA_LABEL VARCHAR(20) NOT NULL      ,
   SENSORNAME VARCHAR(15) NOT NULL      ,
   FUNCTION_NAME VARCHAR(10) NOT NULL   ,
   TIME_TAG DATETIME NOT NULL           ,
   TIME_PERIOD DOUBLE    NOT NULL       ,
   PP_VALUE DOUBLE    NOT NULL          ,
   FLAG1 VARCHAR(1)                     ,
   FLAG2 VARCHAR(1)                     ,
   FLAG3 VARCHAR(1)                     ,
   VALUE_TIME_TAG DATETIME              ,
   PROCESS_TIME_TAG DATETIME NOT NULL   ,
   ALTERNATE_LABEL VARCHAR(20)          ,
    CONSTRAINT PPDAT1_PK PRIMARY KEY
    (STATION_ID
    ,SENSORNAME
    ,DATA_LABEL
    ,FUNCTION_NAME
    ,TIME_TAG
    ,TIME_PERIOD)
 );

 CREATE TABLE XC_SITESENSORSDECODE
 (
    STATION VARCHAR(30) NOT NULL       ,
    SENSOR VARCHAR(15) NOT NULL        ,
    TOPIC VARCHAR(25) NOT NULL         ,
    SEQNUM INT NOT NULL                ,
    ENABLED VARCHAR(1) NOT NULL        ,
    FORMAT VARCHAR(10)                 ,
    RELATIVE_TIME VARCHAR(1)           ,
    RELATIVE_POS VARCHAR(1)            ,
    ADD_BASE VARCHAR(1)                ,
    BYTE_SIZE INT                      ,
    BIT_SIZE INT                       ,
    BOUND1 INT                         ,
    BOUND2 INT                         ,
    BLOCK_ID VARCHAR(50)               ,
    BYTE_POS INT                       ,
    BIT_POS INT                        ,
    NEXT INT                           ,
    BIT_NEXT INT                       ,
    AMOUNT INT                         ,
    DELTA_TIME INT                     ,
    OFFSET_TIME INT                    ,
    ROUNDOFF_TIME INT                  ,
    SKIP_BLANK INT                     ,
    SKIP_COMMA INT                     ,
    SKIP_LF INT                        ,
    SKIP_PLUS INT                      ,
    IGNORE_CASE VARCHAR(1)             ,
    TEMPLATE_NAME VARCHAR(50)          ,
    CONSTRAINT SITSENDCD_PK PRIMARY KEY (STATION, SENSOR, TOPIC, SEQNUM)
 );


 CREATE TABLE XC_POLLGROUPS
 (
   GROUP_NUM VARCHAR(2)      NOT NULL ,
  ENABLED VARCHAR(1)        NOT NULL ,
  STATION_LIST VARCHAR(2000)      NULL ,
  BASE_TIME INTEGER      NULL ,
  `INTERVAL` INTEGER       NULL ,
  POLL_TYPE INT          NULL ,
  CONTROL VARCHAR(128)      NULL ,
  CONTROL_ONLY VARCHAR(1)   NULL ,
  TIME_SYNC INTEGER      NULL ,
  TIME_SYNC_PORT VARCHAR(5) NULL ,
  TIME_LIMIT DATE        NULL ,
  OVERRIDE_ORDER VARCHAR(1) NULL ,
  DESCRIPTION VARCHAR(30)   NULL ,
  CLIENTS VARCHAR(255)      NULL ,
  LOG_FILE VARCHAR(20)      NULL ,
  LOG_FILE_PATH VARCHAR(50) NULL ,
  OWNER  VARCHAR(20)        NULL ,
  RESET_RTABLE VARCHAR(1)   NULL,
        CONSTRAINT POLLGROUPS_PK PRIMARY KEY (GROUP_NUM)
 );


 CREATE TABLE XC_USERS
 (
  USER_NAME VARCHAR(15)	  NOT NULL ,
  PASSWORD VARCHAR(64)       NOT NULL ,
  DESCRIPTION VARCHAR(64)    NULL ,
  USER_ACCESS VARCHAR(30)     NULL ,
        CONSTRAINT USERS_PK PRIMARY KEY (USER_NAME)
 );


 CREATE TABLE XC_LOG
 (
  TIME_TAG DATETIME          NOT NULL ,
  SOURCE VARCHAR(20)         NOT NULL ,
  DESCRIPTION VARCHAR(128)   NULL ,
	CONSTRAINT LOG_PK PRIMARY KEY (TIME_TAG, SOURCE)
 );

 CREATE TABLE XC_TEMPLATES
 (
  TEMPLATE_NAME VARCHAR(45)  NOT NULL ,
  TEMPLATE_TYPE INTEGER   NULL ,
        CONSTRAINT TEMPLATES_PK PRIMARY KEY (TEMPLATE_NAME)
 );

CREATE TABLE XC_LASTVALUES(
  STATION_ID           VARCHAR(30) NOT NULL,
  SENSORNAME           VARCHAR(15) NOT NULL,
  TIME_TAG             DATETIME NOT NULL,
  IS_TEXT              VARCHAR(7) NULL,
  NUM_VALUE            DOUBLE NULL,
  TEXT_VALUE           VARCHAR(255) NULL,
  PRIMARY KEY(STATION_ID,SENSORNAME)
);

CREATE TABLE XC_RECORDIDS(
  STE_STATION_ID VARCHAR(30) NOT NULL ,
  RECORD_ID VARCHAR(10)			NOT NULL ,
  SENSORS VARCHAR(255)       NULL ,
        CONSTRAINT RECORDIDS_PK PRIMARY KEY (STE_STATION_ID, RECORD_ID)
);
-- XC_VER
CREATE TABLE XC_VER
( VERSION INT );

INSERT INTO XC_VER VALUES ( 104 );
