-- rem # ###########################################################
-- rem #  CALIBRATION TRENDING TABLES
-- rem # ###########################################################


-- rem # ###########################################################
-- rem #
-- rem # Table Name: CalibSource
-- rem # Table Use: enumerates Calibration Sources
-- rem #
-- rem # ###########################################################
rem create and populate Table: CalibSource

CREATE TABLE CalibSource (
CalSrc_PK INTEGER NOT NULL,
Description VARCHAR2(20) NOT NULL,
CONSTRAINT PK_CalibSource PRIMARY KEY (CalSrc_PK),
CONSTRAINT UK_CalibSource UNIQUE(CalSrc_PK, Description)
);

INSERT INTO CalibSource VALUES (0, 'Cosm_ray');
INSERT INTO CalibSource VALUES (1, 'Proton');
INSERT INTO CalibSource VALUES (2, 'Photon');
INSERT INTO CalibSource VALUES (3, 'Positron');
INSERT INTO CalibSource VALUES (4, 'Charge_Inj');

-- rem # ###########################################################
-- rem #
-- rem # Table Name: Instrument
-- rem # Table Use: enumerates Calibration Instruments
-- rem # Hardware 'Description' corresponds to 'Instrument' in Calib DTD
-- rem #
-- rem # ###########################################################
rem create and populate Table: Hardware

CREATE TABLE Instrument(
Instrument_PK INTEGER NOT NULL,
Description VARCHAR2(30) NOT NULL,
CONSTRAINT PK_Hardware PRIMARY KEY (Instrument_PK),
CONSTRAINT UK_Hardware UNIQUE(Instrument_PK, Description)
);
--(Engineering Model)
INSERT INTO Instrument VALUES (0, 'EM'); 
-- (Calibration Unit)		
INSERT INTO Instrument VALUES (1, 'CU'); 		
INSERT INTO Instrument VALUES (2, 'LAT');
INSERT INTO Instrument VALUES (3, 'BTEM');
INSERT INTO Instrument VALUES (4, 'BTEM');
INSERT INTO Instrument VALUES (5, 'EM2');

-- rem # ###########################################################
-- rem #
-- rem # Table Name: Location 
-- rem # Table Use: enumerates Locations
-- rem #
-- rem # -- DOnt know if this table is needed??? 
-- rem # ###########################################################

rem create and populate Table: Location

CREATE TABLE Location(
Location_PK INTEGER,
Description VARCHAR2(30),
CONSTRAINT PK_Location PRIMARY KEY (Location_PK),
CONSTRAINT UK_Location UNIQUE(Location_PK, Description)
);

INSERT INTO Location VALUES (0, 'SLAC-Clean Room');
INSERT INTO Location VALUES (1, 'SLAC-Beam Test');
INSERT INTO Location VALUES (2, 'NRL-Enviromental test');
INSERT INTO Location VALUES (3, 'Airplane');
INSERT INTO Location VALUES (4, 'Mission Integration');
INSERT INTO Location VALUES (5, 'On Orbit');

-- rem # ###########################################################
-- rem #
-- rem # Table Name: CalibType
-- rem # Table Use: enumerates types of Calibrations (CAL, TKR, ACD)
-- rem # 'Description' corresponds to 'calibtype' in Calib DTD
-- rem #
-- rem # ###########################################################
rem create and populate Table: CalibType

CREATE TABLE CalibType(
CalType_PK INTEGER NOT NULL,
Description VARCHAR2(50) NOT NULL,
CONSTRAINT PK_CalibType PRIMARY KEY (CalType_PK),
CONSTRAINT UK_CalibType UNIQUE(CalType_PK, Description)
);

-- CAL Calibration Types (0-19):
INSERT INTO CalibType VALUES (0, 'CAL_ElecGain');
INSERT INTO CalibType VALUES (1, 'CAL_Ped');
INSERT INTO CalibType VALUES (2, 'CAL_LightYield');
INSERT INTO CalibType VALUES (3, 'CAL_LightAsym');
INSERT INTO CalibType VALUES (4, 'CAL_LightAtt');
INSERT INTO CalibType VALUES (5, 'CAL_ScintEff');
INSERT INTO CalibType VALUES (6, 'CAL_IntNonLin');
INSERT INTO CalibType VALUES (7, 'CAL_DiffNonlin');
INSERT INTO CalibType VALUES (8, 'CAL_MuSlope');
INSERT INTO CalibType VALUES (9, 'CAL_Asym');
INSERT INTO CalibType VALUES (10, 'CAL_MevPerDac');
INSERT INTO CalibType VALUES (11, 'CAL_TholdCI');
INSERT INTO CalibType VALUES (12, 'CAL_TholdMuon');
-- TKR Calibration types (20-39):
INSERT INTO CalibType VALUES (20, 'TKR_DeadChan');
INSERT INTO CalibType VALUES (21, 'TKR_HotChan ');
INSERT INTO CalibType VALUES (22, 'TKR_Splits');
INSERT INTO CalibType VALUES (23, 'TKR_TOTSignal');
INSERT INTO CalibType VALUES (24, 'TKR_ChargeScale');
--ACD Calibration types (40-):
INSERT INTO CalibType VALUES (40, 'ACD_Ped');
INSERT INTO CalibType VALUES (41, 'ACD_ElecGain');
INSERT INTO CalibType VALUES (42, 'ACD_Veto');
INSERT INTO CalibType VALUES (43, 'ACD_Cno');

-- rem # ###########################################################
-- rem #
-- rem # Table Name: CalibProcLevel
-- rem # Table Use: enumerates types Procedure Levels
-- rem # 'Description' corresponds to 'Proc_Level' in rdbGUI
-- rem #
-- rem # ###########################################################
rem create and populate Table: CalibProcLevel

CREATE TABLE CalibProcLevel(
CalProcLevel_PK INTEGER,
Description VARCHAR2(30),
CONSTRAINT PK_CalibProcLevel PRIMARY KEY (CalProcLevel_PK),
CONSTRAINT UK_CalibProcLevel UNIQUE(CalProcLevel_PK, Description)
);

INSERT INTO CalibProcLevel VALUES (0, 'TEST');
INSERT INTO CalibProcLevel VALUES (1, 'DEV');
INSERT INTO CalibProcLevel VALUES (2, 'PROD');
INSERT INTO CalibProcLevel VALUES (3, 'SUPSED');

-- rem # ###########################################################
-- rem #
-- rem # Table Name: CalibStatus
-- rem # Table Use: enumerates types Calibration Status
-- rem # 'Description' corresponds to 'Completion' in rdbGUI
-- rem #
-- rem # ###########################################################
rem create and populate Table: CalStatus

CREATE TABLE CalibStatus(
CalStatus_PK INTEGER,
Description VARCHAR2(30),
CONSTRAINT PK_CalStatus PRIMARY KEY (CalStatus_PK),
CONSTRAINT UK_CalStatus UNIQUE(CalStatus_PK, Description)
);

INSERT INTO CalibStatus VALUES (0, 'OK');
INSERT INTO CalibStatus VALUES (1, 'INC');
INSERT INTO CalibStatus VALUES (2, 'ABORT');

-- rem # ###########################################################
-- rem #
-- rem # Table Name: CalibDescription
-- rem # Table Use: Description of Calibration
-- rem # Columns corresponds to 'InputSample'/'Generic' in Calib DTD
-- rem #
-- rem # ###########################################################
rem create Table: CalDescription

CREATE TABLE CalibDescription(
CalDesc_PK INTEGER,
CalType_FK INTEGER,
CalSrc_FK INTEGER,
Location_FK INTEGER,
ValidStartTime DATE,
ValidEndTime DATE,
/* start time of run(s) used to produce calibration constants */
RunStartTime DATE,  
/* end time of run(s) used to produce calibration constants */
RunEndTime DATE,
/* time when calibration constants were produced */
TimeStamp DATE,
Instrument_FK INTEGER,
--NEEDED?
Software Varchar2(20),                  
CalProcLevel_FK INTEGER,                     
CalStatus_FK INTEGER,
Creator Varchar2(30),
Version Varchar2(20),
Description Varchar2(500),
Triggers Varchar2(40),
--Mode = ('NA'|)
Modes Varchar2(4), 
CONSTRAINT PK_CalDescription PRIMARY KEY (CalDesc_PK),
CONSTRAINT FK_CalDes_CalType FOREIGN KEY (CalType_FK) REFERENCES CalibType(CalType_PK),
CONSTRAINT FK_CalDes_Src FOREIGN KEY (CalSrc_FK) REFERENCES CalibSource(CalSrc_PK),
CONSTRAINT FK_CalDes_Location FOREIGN KEY (Location_FK) REFERENCES Location(Location_PK),
CONSTRAINT FK_CalDes_ProcLevel FOREIGN KEY (CalProcLevel_FK) REFERENCES CalibProcLevel(CalProcLevel_PK),
CONSTRAINT FK_CalDes_Status FOREIGN KEY (CalStatus_FK) REFERENCES CalibStatus(CalStatus_PK),
CONSTRAINT FK_CalDes_Hardware FOREIGN KEY (Instrument_FK) REFERENCES Instrument(Instrument_PK)
);

CREATE SEQUENCE Seq_CalDescID INCREMENT by 1 START WITH 1;
--Add others to this index such as RunStartTime, Endtime?
CREATE BITMAP INDEX CalDes_LocationID on CalibDescription(Location_FK);

-- rem # ###########################################################
-- rem #
-- rem # Table Name: CALDimension
-- rem # Table Use: CALORIMETER Dimensions
-- rem # Columns corresponds to 'Dimension' in Calib DTD
-- rem #
-- rem # ###########################################################
rem create Table CALDimension

CREATE TABLE CALDimension(
CALDimension_PK INTEGER,
--(0|1)
NXPos INTEGER,      
--(0)  	
NDacCol INTEGER,      
NFace  INTEGER,
NRow INTEGER,
NCol INTEGER,
NLayer INTEGER,
NXtal INTEGER,
-- ('true'|'false')
Exact Varchar2(5),  
--(4)  	
NRange INTEGER,  		
CONSTRAINT PK_CALDIMENSION PRIMARY KEY (CALDimension_PK)
);

-- rem # ###########################################################
-- rem #
-- rem # Table Name: CALRange
-- rem # Table Use: CALORIMETER  DAC RANGE
-- rem # Columns corresponds to 'Range' in CAL DTD
-- rem #
-- rem # ###########################################################
rem create and populate Table: CalRange

CREATE TABLE CALRange(
CALRange_PK INTEGER NOT NULL,
Description VARCHAR2(20) NOT NULL,
CONSTRAINT PK_CalRange PRIMARY KEY (CALRange_PK)
);

INSERT INTO CALRange VALUES (0, 'LEX8');
INSERT INTO CALRange VALUES (1, 'LEX1');
INSERT INTO CALRange VALUES (2, 'HEX8');
INSERT INTO CALRange VALUES (3, 'HEX1');

-- rem # ###########################################################
-- rem #
-- rem # Table Name: ACDDimension
-- rem # Table Use: ACD Dimensions
-- rem # Columns corresponds to 'Dimension' in ACD DTD
-- rem #
-- rem # ###########################################################
rem create Table ACDDimension

CREATE TABLE ACDDimension(
AcdDimension_PK INTEGER,
NTile INTEGER,
NFace  INTEGER default(7),
-- default occurs in top face
NRow INTEGER default(5), 		
-- default occurs in top face
NCol INTEGER default(5), 
-- Number of PMT		
NPmt INTEGER default(2), 		
-- Number of unconnected channels
Nna INTEGER default(11), 		
-- ('true'|'false')
Exact Varchar2(5), 				
CONSTRAINT PK_ACDDIMENSION PRIMARY KEY (AcdDimension_PK)
);

-- rem # ###########################################################
-- rem #
-- rem # Table Name: TKRDimension
-- rem # Table Use: TKR Dimensions
-- rem # Columns corresponds to 'Dimension' in TKR DTD
-- rem #
-- rem # ###########################################################
rem create Table TKRDimension

CREATE TABLE TKRDimension(
TkrDimension_PK INTEGER,
-- default occurs in top face
NBayRow INTEGER default(5), 	
NBayCol INTEGER default(5), 	
-- Number of PMT
NTray INTEGER default(2), 		
CONSTRAINT PK_TKRDIMENSION PRIMARY KEY (TkrDimension_PK));

-- rem # ###########################################################
-- rem #
-- rem # Table Name: CalibIndex
-- rem # Table Use: Calibration Index (Identifies the Calibration)
-- rem # This index (CalIndexID) links all related tables
-- rem #
-- rem # ###########################################################
--rem create and populate Table: CalibIndex

CREATE TABLE CalibIndex(
CalIndex_PK INTEGER NOT NULL,
CalDesc_FK INTEGER,
CalInstrument_FK INTEGER,
CalDimension_FK INTEGER,
CONSTRAINT PK_CalibIndex PRIMARY KEY (CalIndex_PK),
CONSTRAINT FK_CalibIndex_Des FOREIGN KEY (CalDesc_FK) REFERENCES CalibDescription(CalDesc_PK),
CONSTRAINT FK_CalibIndex_Dim FOREIGN KEY (CalDimension_FK) REFERENCES CALDimension(CalDimension_PK),
CONSTRAINT FK_AcdIndex_Dimension FOREIGN KEY (CalDimension_FK) REFERENCES ACDDimension(AcdDimension_PK),
CONSTRAINT FK_CalibIndex_Inst FOREIGN KEY (CalInstrument_FK) REFERENCES Instrument(Instrument_PK)
);

CREATE SEQUENCE Seq_CalIndex_PK INCREMENT BY 1 START WITH 1;
--CHECK IF THESE GOT CREATED?
CREATE INDEX CalibIndex_Desc on CalibIndex(CalIndex_PK, CalDesc_FK);
CREATE INDEX CalibIndex_Ins on CalibIndex(CalIndex_PK, CalInstrument_FK);


-- rem # ###########################################################
-- rem # CAL OFFLINE TABLES
-- rem # ###########################################################

-- rem # ###########################################################
-- rem #
-- rem # Table Name: CALPedestal
-- rem # Table Use:  CALORIMETER Pedestal Calibration Data
-- rem # calibType 1 - Cal_Ped
-- rem # 
-- rem # ###########################################################
rem create and populate Table: CALPedestal

CREATE TABLE CALPedestal(
CALPedestal_PK INTEGER NOT NULL,
CalIndex_FK INTEGER,
TowerCol INTEGER,
TowerRow INTEGER,
Layer INTEGER,
Xtal INTEGER,
Face VARCHAR2(3),
Range_FK Integer, 
Avg NUMBER,
Sig NUMBER,
Cos NUMBER,
CONSTRAINT PK_CALPedestal PRIMARY KEY (CALPedestal_PK),
CONSTRAINT FK_CALPed_Index FOREIGN KEY (CALIndex_FK) REFERENCES CalibIndex(CalIndex_PK),
CONSTRAINT FK_CALPed_Range FOREIGN KEY (Range_FK) REFERENCES CALRange(CALRange_PK)
);

CREATE SEQUENCE Seq_CALPedestal_PK INCREMENT BY 1 START WITH 1;

CREATE INDEX CALPedestal_Index on CALPedestal(CalPedestal_PK, CALIndex_FK, TowerCol, TowerRow, Layer, Xtal, Face);

-- rem # ###########################################################
-- rem #
-- rem # Table Name: CALIntNonLin
-- rem # Table Use:  CALORIMETER Integral Non Linear Calib Settings
-- rem # table 1 for calibType 6, 'Cal_IntNonLin' 
-- rem # 
-- rem # ###########################################################
rem create and populate Table: CALIntNonLin

CREATE TABLE CALIntNonLin(
CALIntNonLin_PK INTEGER NOT NULL,
CALIndex_FK INTEGER,
TowerCol INTEGER,
TowerRow INTEGER,
Layer INTEGER,
Xtal INTEGER,
Face VARCHAR2(3), 
Range_FK INTEGER,
CONSTRAINT PK_CALIntNonLin PRIMARY KEY (CALIntNonLin_PK),
CONSTRAINT FK_CALIntNonLin_Index FOREIGN KEY (CALIndex_FK) REFERENCES CalibIndex(CalIndex_PK),
CONSTRAINT FK_CALIntNonLin_Range FOREIGN KEY (Range_FK) REFERENCES CALRange(CalRange_PK)
);

CREATE SEQUENCE Seq_CALIntNonLin_PK INCREMENT BY 1 START WITH 1;
CREATE INDEX CALIntNonLin_Index on CALIntNonLin(CALIntNonLin_PK, CALIndex_FK, TowerCol, TowerRow, Layer, Xtal, Face, Range_FK);

-- rem # ###########################################################
-- rem #
-- rem # Table Name: CALIntNLVal
-- rem # Table Use:  CALORIMETER Integral Non Linear Cal Data
-- rem # Each (Tower- Layer- xtal - face - range) from prev - has these values
-- rem # table 2 for calibType 6, 'Cal_IntNonLin' 
-- rem # 
-- rem # ###########################################################
rem create and populate Table: CALIntNonLin

CREATE TABLE CALIntNLVal(
CALIntNLVal_PK INTEGER,
IntNLValues NUMBER,
--IS this in DTD????
Sdacs NUMBER, 				
Error NUMBER,
CONSTRAINT PK_CALIntNLVal PRIMARY KEY (CALIntNLVal_PK),
CONSTRAINT FK_CALIntNLVal_INL FOREIGN KEY (CALIntNLVal_PK) REFERENCES CALIntNonLin(CALIntNonLin_PK)
);

CREATE INDEX CALIntNLVal_Index on CALIntNLVal(CalIntNLVal_PK);

-- rem # ###########################################################
-- rem #
-- rem # Table Name: CALMevPerDac
-- rem # Table Use:  CALORIMETER MEV per DAC Calibration data
-- rem # calibType 10, 'CAL_MevPerDac' 
-- rem # 
-- rem # ###########################################################
rem create and populate Table: CALMevPerDac

CREATE TABLE CALMevPerDac (
CALMevPerDac_PK INTEGER,
CALIndex_FK INTEGER,
TowerCol INTEGER,
TowerRow INTEGER,
Layer INTEGER,
Xtal INTEGER,
--'NA' in this case - TAKE OUT??
Face VARCHAR2(3),  			
BigVal NUMBER,
BigSig NUMBER,
SmallVal NUMBER,
SmallSig NUMBER,
BigSmallRatioVals NUMBER, 
BigSmallRatioSigs NUMBER,
 -- ('NEG'|'POS')
BigSmallEnd VARCHAR2(4), 
CONSTRAINT PK_CALMevPerDac PRIMARY KEY (CALMevPerDac_PK),
CONSTRAINT FK_CALMevPerDac_Index FOREIGN KEY (CALIndex_FK) REFERENCES CalibIndex(CalIndex_PK)
);

CREATE SEQUENCE Seq_CalMevPerDacID INCREMENT BY 1 START WITH 1;

CREATE UNIQUE INDEX CALMevPerDac_Index on CALMevPerDac(CALMevPerDac_PK, CALIndex_FK, TowerCol,TowerRow, Layer, Xtal,Face);

-- rem # ###########################################################
-- rem #
-- rem # Table Name: CALAsym
-- rem # Table Use:  CALORIMETER Asymmetry Calibration Settings
-- rem # Table 1 for calibType 9, 'CAL_Asym' 
-- rem # 
-- rem # ###########################################################
rem create and populate Table: CALAsym

CREATE TABLE CALAsym (
CALAsym_PK INTEGER,
CALIndex_FK INTEGER,
TowerCol INTEGER,
TowerRow INTEGER,
Layer INTEGER,
Xtal INTEGER,
--'NA' in this case - TAKE OUT????
Face VARCHAR2(3),  		
CONSTRAINT PK_CALAsym PRIMARY KEY (CALAsym_PK),
CONSTRAINT FK_CALAsym_Index FOREIGN KEY (CALIndex_FK) REFERENCES CalibIndex(CalIndex_PK)
);

CREATE SEQUENCE Seq_CalAsymID INCREMENT BY 1 START WITH 1;

CREATE UNIQUE INDEX CalAsym_Index on CALAsym(CALAsym_PK, CalIndex_FK, TowerCol,TowerRow, Layer, Xtal);

-- rem # ###########################################################
-- rem #
-- rem # Table Name: CALAsymVal
-- rem # Table Use:  CALORIMETER Asymmetry Calibration Values
-- rem # Table 2 for calibType 9, 'CAL_Asym' 
-- rem # 
-- rem # ###########################################################
rem create and populate Table: CALAsymVal

CREATE TABLE CALAysmVal(
CALAsymVal_PK NUMBER,
BigVals NUMBER,
BigSigs NUMBER,
SmallVals NUMBER,
SmallSigs NUMBER,
PsmallNbigVals NUMBER,
PsmallNbigSigs NUMBER,
NsmallPbigVals NUMBER,
NsmallPbigSigs NUMBER,
CONSTRAINT PK_CALAsymVal PRIMARY KEY (CALAsymVal_PK),
CONSTRAINT FK_CALAsymVal_Asym FOREIGN KEY (CALAsymVal_PK) REFERENCES CALAsym(CALAsym_PK)
);

CREATE UNIQUE INDEX CalAsymVal_Index on CALAsymVal(CalAsymVal_PK);

-- rem # ###########################################################
-- rem #
-- rem # Table Name: CALTholdCI
-- rem # Table Use:  CALORIMETER Threshold Charge Injection Calibration Values --CHECK????
-- rem # calibType 11, 'CAL_TholdCI' 
-- rem # 
-- rem # ###########################################################
rem create and populate Table: CALTholdCI

CREATE TABLE CALTholdCI(
CALTholdCI_PK INTEGER,
CALIndex_FK INTEGER,
TowerCol INTEGER,
TowerRow INTEGER,
Layer INTEGER,
Xtal INTEGER,
Face VARCHAR2(3),
LACSig NUMBER,
FHEVal NUMBER,
FLESig NUMBER,
FLEVal NUMBER,
LACVal NUMBER,
FHESig NUMBER,
Range_FK INTEGER,
ULDVAL NUMBER,
PedVal NUMBER,
ULDSig NUMBER,
CONSTRAINT PK_CALTholdCI PRIMARY KEY (CALTholdCI_PK),
CONSTRAINT FK_CALTholdCI_Index FOREIGN KEY (CALIndex_FK) REFERENCES CalibIndex(CalIndex_PK),
CONSTRAINT FK_CALTholdCI_Range FOREIGN KEY (Range_FK) REFERENCES CALRange(CALRange_PK)
) ;

CREATE SEQUENCE Seq_CalTholdCI INCREMENT BY 1 START WITH 1;

CREATE UNIQUE INDEX CALTholdCI_Index on CALTholdCI(CALTholdCI_PK, CALIndex_FK, TowerCol,TowerRow, Layer, Xtal);

----------------------------------------------------------------------
-- ACD OFFLINE TABLES
-----------------------------------------------------------------------

-- rem # ###########################################################
-- rem #
-- rem # Table Name: ACDPedestal
-- rem # Table Use:  ACD Pedestal Calibration Data
-- rem # calibType 40, 'ACD_Ped'
-- rem # 
-- rem # ###########################################################
rem create Table: ACDPedestal

CREATE TABLE ACDPedestal (
AcdPedestal_PK INTEGER,
AcdIndex_FK INTEGER,
TileID INTEGER,
PmtID INTEGER,
Mean NUMBER,
Width NUMBER,
Status NUMBER,
CONSTRAINT PK_AcdPedestal PRIMARY KEY (AcdPedestal_PK),
CONSTRAINT FK_AcdIndex_Index FOREIGN KEY (AcdIndex_FK) REFERENCES CalibIndex(CalIndex_PK)
);

CREATE SEQUENCE Seq_AcdPedestal_PK INCREMENT BY 1 START WITH 1;

CREATE INDEX ACDPedestal_Index on ACDPedestal(AcdPedestal_PK, AcdIndex_FK, TileID, PmtID);

-- rem # ###########################################################
-- rem #
-- rem # Table Name: ACDElecGain
-- rem # Table Use:  ACD Electronic Gain Data
-- rem # calibType 41, 'ACD_ElecGain'
-- rem # 
-- rem # ###########################################################
rem create and populate Table: ACDElecGain

CREATE TABLE ACDElecGain(
AcdElecGain_PK INTEGER,
AcdIndex_FK INTEGER,
TileID INTEGER,
PmtID INTEGER,
Peak NUMBER,
Width NUMBER,
Status NUMBER,
CONSTRAINT PK_AcdElecGain PRIMARY KEY (AcdElecGain_PK),
CONSTRAINT FK_AcdElecGainIndex FOREIGN KEY (AcdIndex_FK) REFERENCES CalibIndex(CalIndex_PK)
);

CREATE SEQUENCE Seq_AcdElecGain_PK INCREMENT BY 1 START WITH 1;

CREATE INDEX ACDElecGain_Index on ACDElecGain(AcdElecGain_PK, AcdIndex_FK, TileID, PmtID);

-----------------------------------------------------------------------
--TKR OFFLINE TABLES
-----------------------------------------------------------------------
-- rem # ###########################################################
-- rem #
-- rem # Table Name: TKRInstrument
-- rem # Table Use:  TKR Instrument Definitions
-- rem # 
-- rem # ###########################################################
rem create Table: TKRInstrument

CREATE TABLE TKRInstrument(
TkrInstr_PK INTEGER,
Tower INTEGER,    -- use idents::TowerId to convert from row, col to tower id
Tray INTEGER,     -- note the label is (19) trays instead of (18) layers
Pos INTEGER,      -- pos=0 means bottom, pos=1 means top
CONSTRAINT PK_TKRInstrument PRIMARY KEY (TkrInstr_PK)
);
CREATE INDEX TKRIns_Index on TKRInstrument(Tower, Tray, Pos);

-- rem # ###########################################################
-- rem #
-- rem # Table Name: TKRIndex
-- rem # Table Use: TKR Index (Identifies a TKR Calibration)
-- rem # This index is linked to (CalIndexID)
-- rem #
-- rem # ###########################################################
rem create and populate Table: TKRIndex

CREATE TABLE TKRIndex(
TkrIndex_PK INTEGER,
TkrDesc_FK INTEGER,
TkrInstr_FK INTEGER,
TkrDimension_FK INTEGER,
CONSTRAINT PK_TkrIndex PRIMARY KEY (TkrIndex_PK),
CONSTRAINT FK_TkrDes_CalDes FOREIGN KEY (TkrDesc_FK) REFERENCES CalibDescription(CalDesc_PK),
CONSTRAINT FK_TkrDes_CalDim FOREIGN KEY (TkrDimension_FK) REFERENCES TKRDimension(TkrDimension_PK),
CONSTRAINT FK_TkrIns_Ins FOREIGN KEY (TkrInstr_FK) REFERENCES TKRInstrument(TkrInstr_PK)
);

CREATE INDEX TKRDesIndex_DesID on TKRIndex(TkrIndex_PK);

CREATE SEQUENCE Seq_TkrIndex_PK INCREMENT BY 1 START WITH 1;

-- rem # ###########################################################
-- rem #
-- rem # Table Name: TKRDeadChannel
-- rem # Table Use: TKR Dead Channel Calibration data
-- rem # Table 1 for Calib Type 20, 'TKR_DeadChan'
-- rem # 
-- rem # Originates from <badStrips badType='dead'> in TKR DTD
-- rem # ###########################################################
rem create and populate Table: TKRDeadChannel

CREATE TABLE TKRDeadChannel (
TkrDeadCh_PK INTEGER,
TkrIndex_FK INTEGER,
-- Do we need Cuts?
Cuts_tight NUMBER,  			
Cuts_loose NUMBER, 				
Cuts_expected NUMBER, 			
Tower_row INTEGER,
Tower_col INTEGER,
Tower_hwserial VARCHAR2(10),
--('true'|'false')
Tower_n0nbdCalib varchar2(5),		
--('true'|'false')
Tower_n0nbdTrig varchar2(5), 		
--('true'|'false')
Tower_n0nbdData varchar2(5), 		
CONSTRAINT PK_TKRDeadChannel PRIMARY KEY (TkrDeadCh_PK),
CONSTRAINT FK_TkrIndexID_Index FOREIGN KEY (TkrIndex_FK) REFERENCES TKRIndex(TkrIndex_PK)
);

CREATE SEQUENCE Seq_TkrDeadCh_PK INCREMENT BY 1 START WITH 1;

CREATE UNIQUE INDEX TkrDeadCh_Index on TKRDeadChannel(TkrIndex_FK,Tower_row, Tower_col);

-- rem # ###########################################################
-- rem #
-- rem # Table Name: TKRDC_Uniplane
-- rem # Table Use: TKR Dead Channel (TKRDC) Uniplane data
-- rem # Table 2 for Calib Type 20, 'TKR_DeadChan'
-- rem # 
-- rem # ###########################################################

CREATE TABLE TKRDC_Uniplane(
Uniplane_PK NUMBER,
Uniplane_tray INTEGER,
--('top'|'bot')
Uniplane_which varchar2(3), 		
--('true'|'false')
Uniplane_n0nbdCalib varchar2(5), 	
--('true'|'false')
Uniplane_n0nbdTrig varchar2(5), 	
--('true'|'false')
Uniplane_n0nbdData varchar2(5), 	
Uniplane_howbad NUMBER,
--('true'|'false'),
Uniplane_allbad varchar2(5), 		
CONSTRAINT PK_DCUniplane PRIMARY KEY (Uniplane_PK),
CONSTRAINT FK_DCUniplane_DC FOREIGN KEY (Uniplane_PK) REFERENCES TKRDeadChannel(TkrDeadCh_PK)
);

-- rem # ###########################################################
-- rem #
-- rem # Table Name: TKRDC_Strips
-- rem # Table Use: TKR Dead Channel (TKRDC) Uniplane - Strips data
-- rem # Table 3 for Calib Type 20, 'TKR_DeadChan'
-- rem # 
-- rem # ###########################################################

CREATE TABLE TKRDC_Strips(
Strips_PK NUMBER,
Striplist_strips NUMBER, 
Stripspan_first NUMBER,
Stripspan_last NUMBER,
CONSTRAINT PK_DCStrips PRIMARY KEY (Strips_PK),
CONSTRAINT FK_DCStripsID_DCUni FOREIGN KEY (Strips_PK) REFERENCES TKRDC_Uniplane(Uniplane_PK)
);

-- rem # ###########################################################
-- rem #
-- rem # Table Name: TKRHotChannel
-- rem # Table Use: TKR Hot Channel Calibration data
-- rem # Table 1 for Calib Type 21, 'TKR_HotChan'
-- rem #
-- rem # Originates from <badStrips badType='hot'> in TKR DTD
-- rem # ###########################################################
--rem create table TKRHotChannel

CREATE TABLE TKRHotChannel(
TkrHotCh_PK INTEGER,
TkrIndex_FK INTEGER,
--CUTS needed?
Cuts_tight NUMBER, 					
Cuts_loose NUMBER, 					
Cuts_expected NUMBER, 				
Tower_col INTEGER,
Tower_row INTEGER,
Tower_hwserial VARCHAR2(10),
--('true'|'false')   -- next three needed here or in uniplane?
Tower_n0nbdCalib varchar2(5), 	
Tower_n0nbdTrig varchar2(5), 	
Tower_n0nbdData varchar2(5), 	
CONSTRAINT PK_TKRHotChannel PRIMARY KEY (TkrHotCh_PK),
CONSTRAINT FK_TkrHotCh_Index FOREIGN KEY (TkrIndex_FK) REFERENCES TKRIndex(TkrIndex_PK)
);

CREATE SEQUENCE Seq_TkrHotCh_PK INCREMENT BY 1 START WITH 1;

CREATE UNIQUE INDEX TkrHotCh_Index on TKRHotChannel(TkrIndex_FK, Tower_row, Tower_col);

-- rem # ###########################################################
-- rem #
-- rem # Table Name: TKRHC_Uniplane
-- rem # Table Use: TKR Hot Channel (TKRHC) Uniplane data
-- rem # Table 2 for Calib Type 21, 'TKR_HotChan'
-- rem # 
-- rem # ###########################################################

CREATE TABLE TKRHC_Uniplane(
Uniplane_PK NUMBER,
Uniplane_tray INTEGER,
--('top'|'bot')
Uniplane_which varchar2(3), 	
--('true'|'false')	
Uniplane_n0nbdCalib varchar2(5), 
--('true'|'false')	
Uniplane_n0nbdTrig varchar2(5), 	
--('true'|'false')
Uniplane_n0nbdData varchar2(5), 	
Uniplane_howbad NUMBER,
--('true'|'false')
Uniplane_allbad varchar2(5), 		
CONSTRAINT PK_HCUniplane PRIMARY KEY (Uniplane_PK),
CONSTRAINT FK_HCUniplaneID_HC FOREIGN KEY (Uniplane_PK) REFERENCES TKRHotChannel(TkrHotCh_PK)
);

-- rem # ###########################################################
-- rem #
-- rem # Table Name: TKRHC_Strips
-- rem # Table Use: TKR Hot Channel (TKRHC) Uniplane - Strips data
-- rem # Table 3 for Calib Type 21, 'TKR_HotChan'
-- rem # 
-- rem # ###########################################################

CREATE TABLE TKRHC_Strips(
Strips_PK NUMBER,
Striplist_strips NUMBER, 
Stripspan_first NUMBER,
Stripspan_last NUMBER,
CONSTRAINT PK_HCStrips PRIMARY KEY (Strips_PK),
CONSTRAINT FK_HCStripsID_HCUni FOREIGN KEY (Strips_PK) REFERENCES TKRHC_Uniplane(Uniplane_PK)
);

-- rem # ###########################################################
-- rem #
-- rem # Table Name: TKRSplits
-- rem # Table Use: TKR Splits Calibration data
-- rem # Calib Type 22, 'TKR_Splits'
-- rem #
-- rem # ###########################################################
rem create and populate Table: TKRSplits

CREATE TABLE TKRSplits(
TkrSplits_PK INTEGER,
TKRIndex_FK INTEGER,
Split_bayrow INTEGER,
Split_baycol INTEGER,
Split_tray INTEGER,
--('true'|'false')
Split_top varchar2(5), 		
CONSTRAINT PK_TKRSplits PRIMARY KEY (TkrSplits_PK),
CONSTRAINT FK_TkrSplits_Index FOREIGN KEY (TkrIndex_FK) REFERENCES TKRIndex(TkrIndex_PK)
);

CREATE SEQUENCE Seq_TkrSplits_PK INCREMENT BY 1 START WITH 1;

CREATE UNIQUE INDEX TkrSplits_Index on TKRSplits(TkrIndex_FK, Split_bayrow, split_baycol);

-- rem # ###########################################################
-- rem #
-- rem # Table Name: TKRTOTGains
-- rem # Table Use: TKR Time Over Threshold Gains Tower data
-- rem # Table 1 for Calib Type 23, 'TKR_TOTSignal'
-- rem #
-- rem # ###########################################################
rem create and populate table TKRTOTGains

CREATE TABLE TKRTOTGains(
TKRTOTGains_PK INTEGER,
TKRIndex_FK INTEGER,
--Need cuts?
Cuts_tight NUMBER, 			
Cuts_loose NUMBER, 			
Cuts_expected NUMBER, 		
Tower_row INTEGER,
Tower_col INTEGER,
Tower_hwserial VARCHAR2(10),
CONSTRAINT PK_TKRTOTGains PRIMARY KEY (TkrTOTGains_PK),
CONSTRAINT FK_TkrTOTGains_Index FOREIGN KEY (TkrIndex_FK) REFERENCES TKRIndex(TkrIndex_PK)
) ;

CREATE SEQUENCE Seq_TOTGainsID INCREMENT BY 1 START WITH 1;

CREATE UNIQUE INDEX TkrTOTGains_Index on TKRTOTGains(TkrIndex_FK, Tower_row,Tower_col);

-- rem # ###########################################################
-- rem #
-- rem # Table Name: TKRTOT_Uniplane
-- rem # Table Use: TKR Time over Threshold (TKRTOT) Uniplane data
-- rem # Table 2 for Calib Type 23, 'TKR_TOTSignal'
-- rem # 
-- rem # ###########################################################

CREATE TABLE TKRTOT_Uniplane(
Uniplane_PK NUMBER,
Uniplane_tray INTEGER,
--('top'|'bot')
Uniplane_which varchar2(3), 		
CONSTRAINT PK_TOTUniplane PRIMARY KEY (Uniplane_PK),
CONSTRAINT FK_TOTUniplaneID_TOT FOREIGN KEY (Uniplane_PK) REFERENCES TKRTOTGains(TkrTOTGains_PK)
);

-- rem # ###########################################################
-- rem #
-- rem # Table Name: TKRTOT_Strips
-- rem # Table Use: TKR TOT (TKRHC) Uniplane - Strips data
-- rem # Table 3 for Calib Type 23, 'TKR_TOTSignal'
-- rem # 
-- rem # <uniplane (stripTot)> from DTD
-- rem # ###########################################################

CREATE TABLE TKRTOT_Strips(
Strips_PK NUMBER,
StripTot_id NUMBER, 
StripTot_chi2 NUMBER,
StripTot_df NUMBER,
StripTot_intercept NUMBER,
StripTot_quad NUMBER, 
StripTot_slope NUMBER,
CONSTRAINT PK_TOTStrips PRIMARY KEY (Strips_PK),
CONSTRAINT FK_TOTStripsID_TOTUni FOREIGN KEY (Strips_PK) REFERENCES TKRTOT_Uniplane(Uniplane_PK)
);

-- rem # ###########################################################
-- rem #
-- rem # Table Name: TKRChargeScale
-- rem # Table Use: TKR Charge Scale data
-- rem # Table 1 for Calib Type 24, 'TKR_ChargeScale'
-- rem #
-- rem # <uniplane (gtfeScale)> from DTD
-- rem # ###########################################################
rem create and populate table TKRChargeScale

CREATE TABLE TKRChargeScale(
TKRChargeScale_PK INTEGER,
TKRIndex_FK INTEGER,
--Cuts needed?
Cuts_tight NUMBER, 					
Cuts_loose NUMBER, 					
Cuts_expected NUMBER, 				
Tower_row INTEGER,
Tower_col INTEGER,
Tower_hwserial VARCHAR2(10),
CONSTRAINT PK_TKRChargeScale PRIMARY KEY (TKRChargeScale_PK),
CONSTRAINT FK_TkrChargeSc_Index FOREIGN KEY (TKRIndex_FK) REFERENCES TKRIndex(TKRIndex_PK)
) ;

CREATE SEQUENCE Seq_TkrChargeScale_PK INCREMENT BY 1 START WITH 1;

--THIS ONE INCORRECTLT CREATED
CREATE UNIQUE INDEX TkrSplits_Index on TKRChargeScale(TKRIndex_FK, Tower_row,Tower_col);

-- rem # ###########################################################
-- rem #
-- rem # Table Name: TKRCS_Uniplane
-- rem # Table Use: TKR Charge Scale (TKRCS) Uniplane data
-- rem # Table 2 for Calib Type 24, 'TKR_ChargeScale'
-- rem # 
-- rem # ###########################################################

CREATE TABLE TKRCS_Uniplane(
Uniplane_PK NUMBER,
Uniplane_tray INTEGER,
--('top'|'bot')
Uniplane_which varchar2(3), 		
CONSTRAINT PK_CSUniplane PRIMARY KEY (Uniplane_PK),
CONSTRAINT FK_CSUniplaneID_CS FOREIGN KEY (Uniplane_PK) REFERENCES TKRChargeScale(TKRChargeScale_PK)
);

-- rem # ###########################################################
-- rem #
-- rem # Table Name: TKRCS_Gtfe
-- rem # Table Use: TKR Charge Scale(TKRCS) Uniplane - GTFEScale data
-- rem # Table 3 for Calib Type 24, 'TKR_ChargeScale'
-- rem # 
-- rem # <uniplane (gtfeScale)> from DTD
-- rem # ###########################################################

CREATE TABLE TKRCS_Gtfe(
Gtfe_PK INTEGER,
GtfeScale_ID INTEGER,
GtfeScale_chargeScale NUMBER,
--used?
GtfeScale_error NUMBER,                	
--used?
GtfeScale_chi2 NUMBER, 					
--used?
GtfeScale_df NUMBER,					
CONSTRAINT PK_CS_Gtfe PRIMARY KEY (Gtfe_PK),
CONSTRAINT FK_CSGtfe_CS FOREIGN KEY (Gtfe_PK) REFERENCES TKRCS_Uniplane(Uniplane_PK)
);

-- rem # ###########################################################
-- rem # Commit all tables created
-- rem # ###########################################################

commit;