-- MS Access Table Script  - XConnect Version 2
-- This script is only to update table DDL from older version

-- 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

-- 10/25/06
-- Add <FIELD>STD_DEV_LIMIT</FIELD>, <FIELD>STD_DEV_ALARM</FIELD> and <FIELD>STD_DEV_INTERVAL</FIELD> to <TABLE>XC_SITESENSORS</TABLE>
-- Add <FIELD>AVG_LIMIT</FIELD>, <FIELD>AVG_ALARM</FIELD> and <FIELD>AVG_INTERVAL</FIELD> to <TABLE>XC_SITESENSORS</TABLE>
-- Add <FIELD>REFERENCE_1</FIELD>, <FIELD>REFERENCE_2</FIELD> and <FIELD>DATA_EXCH_NUM</FIELD> to <TABLE>XC_SITESENSORS</TABLE>
-- Add <FIELD>DATA_EXCH_NUM to <TABLE>XC_SITESENSORS</TABLE>
-- Add <FIELD>MIKE11_LABEL_1</FIELD>, <FIELD>MIKE11_LABEL_2</FIELD> to <TABLE>XC_SITESENSORS</TABLE>
-- Add <FIELD>STD_DEV_FLAG</FIELD>, <FIELD>AVG_FLAG</FIELD> to <TABLE>XC_DATA1</TABLE>
-- Make <TABLE>XC_VER</TABLE> as version 7

-- 06/03/09
-- Add <Field>NO_ALARM_INTERVAL</Field> to <Table>XC_SITESENSORS</Table>
-- Make <Table>XC_VER</Table> as version 8

-- 02/10/10
-- XConnect version 2 started.
-- Add -->
-- Make <Table>XC_VER</Table> as version 100

-- 08/07/2010
-- Add <Field>TEMPLATE_FLAG</Field>, <Field>MBDEVICE_ID</Field>, <Field>MBCONSEC_REGISTER</Field> to <Table>XC_SITES</Table>
-- Add <Field>MBBASE_REGISTER</Field>, <Field>MBREGISTER_TYPE</Field>, <Field>MBREGISTER_COUNT</Field> to <Table>XC_SITES</Table>
-- Add <Field>MSGPARITY</Field> to <Table>XC_SITES</Table>
-- Add <Field>NO_ALARM_INTERVAL</Field>, <Field>MBBASE_REGISTER</Field>, <Field>MBREGISTER_TYPE</Field> to <Table>XC_SITESENSORS</Table>
-- Add <Field>MBTAG_TYPE</Field>, <Field>MBMSW</Field> to <Table>XC_SITESENSORS</Table>
-- Add <Field>IMEI_ID</Field>, <Field>SMS_ID</Field> to <Table>XC_SITESENSORS</Table>
-- Make <Table>XC_VER</Table> as version 101

-- 06/12/2013
-- Add <Field>LOG_FILE_NAME</Field> to <Table>XC_SITES</Table>
-- Add <Field>SKIP_PLUS</Field> to <Table>XC_SITESSENSORSDECODE</Table>
-- Make <Table>XC_VER</Table> as version 102

-- 07/03/2013
-- Add <Table>XC_LASTVALUES</Table>
-- Changed <Field>OFFSET_TIME</Field>, <Field>ROUNDOFF_TIME</Field> in <Table>XC_SITESENSORSDECODE</Table> changd for DATETIME to INTEGER type
-- Renamed <Field>RAW</Field> to <Field>RawList</Field> in <Table>XC_SITESENSORSDECODE</Table>
-- Renamed <Field>USER</Field> to <Field>USER_ACCESS</Field> in <Table>XC_USERS</Table>
-- Renamed <Field>NAME</Field> to <Field>TEMPLATE_NAME</Field> and <Field>TYPE</Field> to <Field>TEMPLATE_TYPE</Field> in <Table>XC_TEMPLATES</Table>
-- Make <Table>XC_VER</Table> as version 103


-- VERSION=4
ALTER TABLE XC_SITESENSORS
ADD
  OFFSET NUMBER,
  SHIFT NUMBER,
  RIGHT_DIGIT NUMBER;

CREATE TABLE XC_VER
( VERSION NUMBER );

INSERT INTO XC_VER VALUES ( 4 );

-- VERSION=5
CREATE TABLE XC_RTUQC
(
    STATION_ID          TEXT(20) NOT NULL      ,
    TIME_TAG            DATETIME NOT NULL      ,
    RX_TX               TEXT(1) NOT NULL       ,
    PORT                TEXT(30)               ,
    SEQUENCENUM         NUMBER                 ,
    OPCODE              NUMBER                 ,
    STATUS              NUMBER                 ,
    RETRIES             NUMBER                 ,
    GOOD                NUMBER                 ,
    BAD                 NUMBER                 ,
    CRC                 NUMBER                 ,
    BATTERY_VOLTAGE     NUMBER                 ,
    SPARE1              NUMBER                 ,
    SPARE2              NUMBER                 ,
    CONSTRAINT RTU_PK PRIMARY KEY (
        STATION_ID,
        TIME_TAG,
        RX_TX
    )
);

INSERT INTO XC_VER VALUES ( 5 );


-- VERSION=6
ALTER TABLE XC_SITES
ADD
    COEFFICIENT1 NUMBER,
    COEFFICIENT2 NUMBER,
    COEFFICIENT3 NUMBER;

ALTER TABLE XC_SITESENSORS
ADD
    COEFFICIENT1 NUMBER,
    COEFFICIENT2 NUMBER,
    COEFFICIENT3 NUMBER,
    COEFFICIENT4 NUMBER,
    COEFFICIENT5 NUMBER;

INSERT INTO XC_VER VALUES ( 6 );


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

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

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

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

CREATE TABLE XC_RECORDIDS(
  STE_STATION_ID TEXT(30) NOT NULL ,
	RECORD_ID TEXT(10)			NOT NULL ,
  SENSORS TEXT(255)       NULL ,
        CONSTRAINT RECORDIDS_PK PRIMARY KEY (STE_STATION_ID, RECORD_ID)
);

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

ALTER TABLE XC_SITESENSORS
	DROP CONSTRAINT SITSEN_STE_FK;
ALTER TABLE XC_SITESENSORS
	DROP CONSTRAINT SITSEN_PK;
ALTER TABLE XC_SITESENSORS
	ALTER COLUMN STE_STATION_ID TEXT(30) NOT NULL;
ALTER TABLE XC_SITESENSORS
	ALTER COLUMN SENSORNAME TEXT(15) NOT NULL;
ALTER TABLE XC_SITESENSORS
	ADD CONSTRAINT SITSEN_PK PRIMARY KEY (SENSORNAME,STE_STATION_ID);
ALTER TABLE XC_SITESENSORS
	ALTER COLUMN ALERT_ID INTEGER NULL;

ALTER TABLE XC_SITESENSORS
  ADD   TAGNAME TEXT(15)          NULL ,
        RAWLIST TEXT(30)          NULL ,
        LEFT_DIGIT INTEGER        NULL ,
        IS_TEXT TEXT(1)           NULL ,
        ROUNDOFF_TIME DATE        NULL ,
    		DATA_STORE_INTERVAL DATE	NULL ,
        UNITS TEXT(10)            NULL ,
    		TEMPLATE_NAME TEXT(15)		NULL ,
        STATUS INTEGER            NULL ,
        STD_DEV_LIMIT NUMBER      NULL ,
        STD_DEV_ALARM INTEGER     NULL ,
    		STD_DEV_INTERVAL NUMBER		NULL ,
        AVG_LIMIT NUMBER          NULL ,
        AVG_ALARM INTEGER         NULL ,
        AVG_INTERVAL NUMBER       NULL ,
        REFERENCE_1 NUMBER        NULL ,
        REFERENCE_2 NUMBER        NULL ,
        DATA_EXCH_NUM TEXT(100)   NULL ,
        MIKE11_LABEL_1 TEXT(50)   NULL,
        MIKE11_LABEL_2 TEXT(50)   NULL;

ALTER TABLE XC_SITES
	DROP CONSTRAINT STE_PK;
ALTER TABLE XC_SITES
	ALTER COLUMN STATION_ID TEXT(30) NOT NULL;
ALTER TABLE XC_SITES
	ADD CONSTRAINT STE_PK PRIMARY KEY (STATION_ID);
ALTER TABLE XC_SITES
	ALTER COLUMN STATE TEXT(32) NULL;
ALTER TABLE XC_SITES
  ADD SITE_TYPE INTEGER             NULL ,
        STATUS INTEGER              NULL ,
        IPADDRESS TEXT(20)          NULL ,
    		RADIO_PATH_LIST TEXT(30)		NULL ,
        PHONE_NUMBER_LIST TEXT(40)  NULL ,
    		PHONE_NUMBER2 TEXT(20)			NULL ,
        USER_NAME TEXT(10)          NULL ,
        PASSWORD TEXT(10)           NULL ,
        COMPORT_LIST TEXT(15)       NULL ,
    		ACKDELAY_LIST TEXT(10)			NULL ,
    		RESPONSE_TIMEOUT_LIST TEXT(10)		NULL ,
        POLL_TYPE INTEGER           NULL ,
        LOGIN_REQD TEXT(1)          NULL ,
    		QUICK_PROTOCOL TEXT(1) 			NULL ,
        AUTO_PROTOCOL TEXT(1)       NULL ,
        CONCENTRATOR TEXT(1)        NULL ,
        HOURS_AHEAD NUMBER          NULL ,
        RDI_BASE_TIME DATE          NULL ,
        RDI_INTERVAL DATE           NULL ,
        RDI_STAYON DATE             NULL ,
    		TEMPLATE_NAME TEXT(30)			NULL ,
    		SITE_LONG_NAME TEXT(50)			NULL ,
        SITE_CODE INTEGER           NULL ,
        USGS_ID TEXT(10)            NULL ,
        RESERVOIR TEXT(32)          NULL ,
        RIVER_MILE NUMBER           NULL ,
        MODEM_INIT_STRING TEXT(20)  NULL ,
        MODEM_HANGUP_STRING TEXT(20) NULL ,
        LAST_POLL DATE              NULL ;

ALTER TABLE XC_SITESENSORS
	ADD CONSTRAINT SITSEN_STE_FK FOREIGN KEY (STE_STATION_ID) REFERENCES XC_SITES (STATION_ID);

ALTER TABLE XC_DATA1
	DROP CONSTRAINT DAT1_PK;
ALTER TABLE XC_DATA1
	ALTER COLUMN STATION_ID TEXT(30) NOT NULL;
ALTER TABLE XC_DATA1
	ALTER COLUMN SENSORNAME TEXT(15) NOT NULL;
ALTER TABLE XC_DATA1
	ADD CONSTRAINT DAT1_PK PRIMARY KEY (TIME_TAG,SOURCE,STATION_ID,SENSORNAME);
ALTER TABLE XC_DATA1
	ADD	STD_DEV_FLAG NUMBER	NULL ,
      AVG_FLAG NUMBER    NULL ;

ALTER TABLE XC_GOESQC
	DROP CONSTRAINT GOESQC_PK;
ALTER TABLE XC_GOESQC
	ALTER COLUMN STATION_ID TEXT(30) NULL;
ALTER TABLE XC_GOESQC
	ADD CONSTRAINT GOESQC_PK PRIMARY KEY (NESDIS_ID,TIME_TAG,SOURCE);

ALTER TABLE XC_RTUQC
	DROP CONSTRAINT RTU_PK;
ALTER TABLE XC_RTUQC
	ALTER COLUMN STATION_ID TEXT(30) NOT NULL;
ALTER TABLE XC_RTUQC
	ADD CONSTRAINT RTU_PK PRIMARY KEY (STATION_ID,TIME_TAG,RX_TX);

ALTER TABLE XC_PPLIST
	ALTER COLUMN STATION_ID TEXT(30) NOT NULL;
ALTER TABLE XC_PPLIST
	ALTER COLUMN SENSORNAME TEXT(15) NOT NULL;


ALTER TABLE XC_PPDATA1
	DROP CONSTRAINT PPDAT1_PK;
ALTER TABLE XC_PPDATA1
	ALTER COLUMN STATION_ID TEXT(30) NOT NULL;
ALTER TABLE XC_PPDATA1
	ALTER COLUMN SENSORNAME TEXT(15) NOT NULL;
ALTER TABLE XC_PPDATA1
	ADD 	CONSTRAINT PPDAT1_PK PRIMARY KEY (STATION_ID,SENSORNAME ,DATA_LABEL,FUNCTION_NAME,TIME_TAG,TIME_PERIOD);

ALTER TABLE XC_TXTDATA1
	DROP CONSTRAINT TXD1_PK;
ALTER TABLE XC_TXTDATA1
	ALTER COLUMN STATION_ID TEXT(30) NOT NULL;
ALTER TABLE XC_TXTDATA1
	ALTER COLUMN SENSORNAME TEXT(15) NOT NULL;
ALTER TABLE XC_TXTDATA1
	ADD CONSTRAINT TXD1_PK PRIMARY KEY (TIME_TAG,SOURCE,STATION_ID,SENSORNAME);

INSERT INTO XC_VER VALUES ( 100 );


-- VERSION=101
ALTER TABLE XC_SITES
ADD
  TEMPLATE_FLAG        INTEGER NULL,
  MBDEVICE_ID          TEXT(4) NULL,
  MBCONSEC_REGISTER    TEXT(1) NULL,
  MBBASE_REGISTER      INTEGER NULL,
  MBREGISTER_TYPE      INTEGER NULL,
  MBREGISTER_COUNT     INTEGER NULL,
  MSGPARITY            INTEGER NULL,
  IMEI_ID              TEXT(15) NULL,
  SMS_ID               TEXT(15) NULL;

ALTER TABLE XC_SITESENSORS
ADD
  NO_ALARM_INTERVAL    FLOAT NULL,
  MBBASE_REGISTER      INTEGER NULL,
  MBREGISTER_TYPE      INTEGER NULL,
  MBTAG_TYPE           INTEGER NULL,
  MBMSW                INTEGER NULL;

INSERT INTO XC_VER VALUES ( 101 );

-- Version 102
ALTER TABLE XC_SITES
ADD
  LOG_FILE_NAME TEXT(255) NULL;

ALTER TABLE XC_SITESENSORSDECODE
ADD
  SKIP_PLUS INTEGER         NULL;

INSERT INTO XC_VER VALUES ( 102 );

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

INSERT INTO XC_VER VALUES ( 103 );
