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

-- Update:
-- 9/12/02
-- Change varchar2 to varchar
-- Change Number(...) to DECIMAL(...)

-- 9/18/02
-- Change <Field>Station_ID</Field> to primary key from Name with length 20
-- Change <Field>Unit_ID</Field> to normal Fields
-- Increase length of <Field>SETUP_FILE_NAME</Field> and <Field>PICTURE_FILE_NAME</Field> to 255

-- 9/24/02
-- Add <Field>CITY</Field> and <Field>DISTRICT</Field> with both length 32
-- Change <Field>REPORTING_TIME</Field> to DATE
-- Add <Field>PARITY</Field>, <Field>BUFFER_EMPTY</Field> to <Table>XC_GOESQC<Table>
 
-- 9/25/02
-- Add <Field>ACTUAL_LEN<Field> with NUMBER to <Table>XC_GOESQC<Table>

-- 9/30/02
-- Change NESDISID to <Field>NESDIS_ID<Field> in <Table>XC_GOESQC<Table>

-- 10/08/02
-- Add <Field>BATTERY_VOLTAGE<Field> in <Table>XC_GOESQC<Table>

-- 2/19/03
-- <Field>DESCRIPTION</Field> in <Table>XC_PP_INSTRUCTIONS</Table> can be NULL now

-- 04/10/03
-- Change DECIMAL(...) to INT or FLOAT(53)

-- 5/22/03
-- Delete <Table>XC_SITSENS_PPI</Table>, <Table>XC_PP_INSTRUCTIONS</Table>.  
-- Change table structure <Table>XC_PPDATA1</Table>.

-- 6/24/03
-- Change <Table>XC_SiteSensors</Table> <Field>Data_Table</Field> length to 12.
-- Update the constraint for 'XC_TXTDATA1'

-- 11/3/03
-- Add <FIELD>SHIFT</FIELD>, <FIELD>OFFSET</FIELD> and <FIELD>RIGHT_DIGIT</FIELD> to <TABLE>XC_SITESENSORS</TABLE>
-- Create <TABLE>XC_VER</TABLE> as version 4

-- 12/26/03
-- Add <TABLE>XC_RTUQC</TABLE>
-- Make <TABLE>XC_VER</TABLE> as version 5

-- 02/20/04
-- Add <TABLE>XC_SITES</TABLE> <FIELDS>COEFFICIENT 1-3</FIELDS>
-- Add <TABLE>XC_SITESENSORS</TABLE> <FIELDS>COEFFICIENT 1-5</FIELDS> 
-- Make <TABLE>XC_VER</TABLE> as version 6

USE XC_DATA
GO



CREATE TABLE XCManager.XC_SITES
 (  STATION_ID VARCHAR(20) NOT NULL ,
    ENABLED VARCHAR(1) NOT NULL     ,
    UNIT_ID VARCHAR(15) NULL        ,
    SITE_COMMENT VARCHAR(32) NULL   ,
    INSTALLATION_DATE DATETIME NULL ,
    AGENCY VARCHAR(32)    NULL      ,
    COUNTRY VARCHAR(32)   NULL      ,
    DISTRICT VARCHAR(32)  NULL      ,
    CITY VARCHAR(32)      NULL      ,
    STATE VARCHAR(20)     NULL      ,
    COUNTY VARCHAR(32)    NULL      ,
    BASIN VARCHAR(32)     NULL      ,
    LATITUDE FLOAT(53)    NULL      ,
    LONGITUDE FLOAT(53)   NULL      ,
    ELEVATION FLOAT(53)   NULL      ,
    COEFFICIENT1 FLOAT(53)   NULL   ,
    COEFFICIENT2 FLOAT(53)   NULL   ,
    COEFFICIENT3 FLOAT(53)   NULL       ,
    WEIGHTING_FACTOR FLOAT(53)  NULL    ,
    SATELLITE_ID VARCHAR(8)     NULL    ,
    PRIMARY_CHANNEL INT         NULL    ,
    RANDOM_CHANNEL INT          NULL    ,
    REPORTING_TIME DATETIME     NULL    ,
    REPORTING_INTERVAL FLOAT(53) NULL   ,
    LAST_UPDATE DATETIME        NULL    ,
    TZONE_CODE VARCHAR(3)       NULL    ,
    GMT_OFFSET FLOAT            NULL    ,
    SHEF_ID VARCHAR(8)          NULL    ,
    ALTERNATE_CHAR_ID_1 VARCHAR(10) NULL,
    ALTERNATE_CHAR_ID_2 VARCHAR(10) NULL,
    GPSTRING1 VARCHAR(20)      NULL     ,
    GPSTRING2 VARCHAR(20)      NULL     ,
    GPNUMBER1 FLOAT(53)        NULL     ,
    GPNUMBER2 FLOAT(53)        NULL     ,
    SETUP_FILE_NAME VARCHAR(55)   NULL  ,
    PICTURE_FILE_NAME VARCHAR(55) NULL  ,
    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))
)
GO

CREATE TABLE XCManager.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)  NULL           ,
    MANUFACTURER VARCHAR(20) NULL           ,
    MODEL_NUMBER VARCHAR(20) NULL           ,
    SHEF_PE_CODE VARCHAR(7)  NULL           ,
    PARAMETER_CODE VARCHAR(12)  NULL        ,
    ALT_PARAMETER_CODE_1 VARCHAR(12)  NULL  ,
    ALT_PARAMETER_CODE_2 VARCHAR(12)  NULL  ,
    EQUATION VARCHAR(128)         NULL      ,
    RATING_TABLE VARCHAR(128)      NULL     ,
    OFFSET FLOAT(53)               NULL     ,
    SHIFT FLOAT(53)                NULL     ,
    RIGHT_DIGIT INT                NULL     ,
    HIGH_HIGH_LIMIT FLOAT(53)      NULL     ,
    HIGH_HIGH_ALARM INT            NULL     ,
    HIGH_LIMIT FLOAT(53)           NULL     ,
    HIGH_ALARM INT                 NULL     ,
    LOW_LIMIT FLOAT(53)            NULL     ,
    LOW_ALARM INT                  NULL     ,
    LOW_LOW_LIMIT FLOAT(53)        NULL     ,
    LOW_LOW_ALARM INT              NULL     ,
    ROC_LIMIT FLOAT(53)            NULL     ,
    ROC_ALARM INT                  NULL     ,
    ROC_INTERVAL FLOAT(53)         NULL     ,
    DEADBAND FLOAT(53)             NULL     ,
    NO_CHANGE_INTERVAL FLOAT(53)   NULL     ,
    NO_CHANGE_ALARM INT            NULL     ,
    TEXT_LIMIT VARCHAR(9)          NULL     ,
    TEXT_ALARM INT                 NULL     ,
    GPNUMBER1 FLOAT(53)            NULL     ,
    GPNUMBER2 FLOAT(53)            NULL     ,
    GPSTRING1 VARCHAR(20)          NULL     ,
    GPSTRING2 VARCHAR(20)          NULL     ,
    ALERT_ID FLOAT(53)             NULL     ,
    ALERT_TYPE VARCHAR(10)         NULL     ,
    COEFFICIENT1 FLOAT(53)         NULL     ,
    COEFFICIENT2 FLOAT(53)         NULL     ,
    COEFFICIENT3 FLOAT(53)         NULL     ,
    COEFFICIENT4 FLOAT(53)         NULL     ,
    COEFFICIENT5 FLOAT(53)         NULL     ,
        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 XCManager.XC_SITES (STATION_ID)
 )
GO

CREATE TABLE XCManager.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 FLOAT(53) NOT NULL   ,
    ED_VALUE FLOAT(53)    NULL          ,
    FLAG1 VARCHAR(1)      NULL          ,
    FLAG2 VARCHAR(1)      NULL          ,
    FLAG3 VARCHAR(1)      NULL          ,
    FLAG4 VARCHAR(1)      NULL          ,
    HIGH_HIGH_FLAG INT    NULL          ,
    HIGH_FLAG INT         NULL          ,
    LOW_FLAG INT          NULL          ,
    LOW_LOW_FLAG INT      NULL          ,
    ROC_FLAG INT          NULL          ,
    NO_CHG_FLAG INT       NULL          ,
    ALARM_FLAG INT        NULL          ,
    CONSTRAINT DAT1_PK PRIMARY KEY 
    (
        TIME_TAG ,
        SOURCE    ,
        STATION_ID,
        SENSORNAME
    )
)
GO

CREATE TABLE XCManager.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 FLOAT(53)
 ,FREQ_OFFSET FLOAT(53)
 ,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 FLOAT(53)
 ,SPARE1 FLOAT(53)
 ,SPARE2 FLOAT(53)
 ,CONSTRAINT GOESQC_PK PRIMARY KEY 
  (NESDIS_ID
  ,TIME_TAG
  ,SOURCE)
 )
GO 

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

CREATE TABLE XCManager.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)
 )
GO

CREATE TABLE XCManager.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 FLOAT(53) NOT NULL
 ,PP_VALUE FLOAT(53) 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)
 )
GO

CREATE TABLE XCManager.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(256) 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)
 )
GO

-- XC_VER
CREATE TABLE XCManager.XC_VER
( VERSION INT )
GO

INSERT INTO XCManager.XC_VER VALUES ( 6 )
GO