-- 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(20) NOT NULL ,
    ENABLED VARCHAR(1) NOT NULL     ,
    UNIT_ID VARCHAR(15)             ,
    SITE_COMMENT VARCHAR(32)        ,
    INSTALLATION_DATE DATETIME      ,
    AGENCY VARCHAR(32)              ,
    COUNTRY VARCHAR(32)             ,
    DISTRICT VARCHAR(32)            ,
    CITY VARCHAR(32)                ,
    STATE VARCHAR(20)               ,
    COUNTY VARCHAR(32)              ,
    BASIN VARCHAR(32)               ,
    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(3)           ,
    GMT_OFFSET FLOAT                ,
    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(55)     ,
    PICTURE_FILE_NAME VARCHAR(55)   ,
    CONSTRAINT STE_PK PRIMARY KEY(STATION_ID),
    CONSTRAINT AVCON_5863_ENABL_000 CHECK (ENABLED IN ('Y', 'N')),
    CONSTRAINT AVCON_5863_TIME__000 CHECK (GMT_OFFSET 
        IN (0, -12, 11, -11, 12, 1, -10, 2, -9, 3, -8, 4, -7, 5, -6, 6, -5, 
        7, -4, 8, -3, 9, -2, 10, -1))
);

CREATE TABLE XC_SITESENSORS
 (STE_STATION_ID VARCHAR(20) NOT NULL   ,
    SENSORNAME VARCHAR(12) NOT NULL     ,
    ENABLED VARCHAR(1) NOT NULL         ,
    DATA_TABLE VARCHAR(12) NOT NULL     ,
    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                        ,
    RIGHT_DIGIT INT                     ,
    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                      ,
    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                 ,
        CONSTRAINT SITSEN_PK PRIMARY KEY (SENSORNAME,STE_STATION_ID),
        CONSTRAINT CON_DATA_TABLE CHECK (DATA_TABLE 
            IN ('XC_DATA7', 'XC_DATA8', 'XC_DATA9', 'XC_DATA10', 'XC_DATA11', 
            'XC_DATA1', 'XC_DATA2', 'XC_DATA12', 'XC_DATA3', 'XC_DATA4', 
            'XC_DATA5', 'XC_DATA6', 'XC_TXTDATA1')),
        CONSTRAINT CON_ALERT_TYPE CHECK (ALERT_TYPE IN ('NORMAL', 'WIND RUN', 'WIND DIR')),
        CONSTRAINT SITSEN_STE_FK FOREIGN KEY (STE_STATION_ID) REFERENCES XC_SITES (STATION_ID)
 );

CREATE TABLE XC_DATA1
(   STATION_ID VARCHAR(20) NOT NULL ,
    SENSORNAME VARCHAR(12) 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                  ,
    CONSTRAINT DAT1_PK PRIMARY KEY 
    (
        TIME_TAG ,
        SOURCE    ,
        STATION_ID,
        SENSORNAME
    )
)
MAX_ROWS=4000000000000;


CREATE TABLE XC_GOESQC
 (NESDIS_ID VARCHAR(8) NOT NULL
 ,TIME_TAG DATETIME NOT NULL
 ,SOURCE VARCHAR(1) NOT NULL
 ,STATION_ID VARCHAR(20)
 ,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(20) 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(20) NOT NULL
 ,DATA_LABEL VARCHAR(20) NOT NULL
 ,SENSORNAME VARCHAR(12) 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(20) NOT NULL
 ,DATA_LABEL VARCHAR(20) NOT NULL
 ,SENSORNAME VARCHAR(12) 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_TXTDATA1
 (STATION_ID VARCHAR(20) NOT NULL
 ,SENSORNAME VARCHAR(12) 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)
 );

-- XC_VER
CREATE TABLE XC_VER
( VERSION INT );

INSERT INTO XC_VER VALUES ( 6 );
