# this keeps track of which files have been used to populate the database
TABLE SourceFiles(
FullPath VARCHAR(255) NOT NULL,
FileType ENUM('other','DPT', 'ssf list', 'planned delete list', 'planned retransmit list') NOT NULL,
CreateTime VARCHAR(19) NOT NULL,
rover ENUM('A','B') NOT NULL,
test VARCHAR(15) NOT NULL,
sol VARCHAR(3),
FullyProcessed ENUM('Y', 'N'),
PRIMARY KEY (FullPath));

# this keeps track of all the RDRs
TABLE RDRLevel(
# the product ID (filename) of the the EDR from which this RDR was made
EDRParentProductID VARCHAR(50) NOT NULL,
# what kind of RDR - ie 'RAD' or 'IOT'
FileTypeCode VARCHAR(3) NOT NULL,
FullPath VARCHAR(255) NOT NULL,
# a bitfield describing whether or not this RDR has been looked at, and whether there was anything anomalous relating to it. not currently used for the RDRs. Is used in the EDRLevel below . . .
QualityFlag INT UNSIGNED,
# optional descriptive text associated with the bitfield qualityflag
QualityComments VARCHAR(100),
CreateTime VARCHAR(19) NOT NULL,
# not currently used
FileHistory VARCHAR(255),
# currently set to 'CCC generated RDR' or null
Description VARCHAR(255),
PRIMARY KEY (FullPath));

# keep track of all the EDRs
TABLE EDRLevel(
# the product ID of the data product from which this EDR was made
ParentProductID VARCHAR(50) NOT NULL,
# the three letter descriptive code - ie 'ETH', 'EFF'
FileTypeCode VARCHAR(3) NOT NULL,
FullPath VARCHAR(255) NOT NULL,
# the unique identifier for the EDR - in this case just the filename with no path information
ProductID VARCHAR(50) NOT NULL,
# a bitfield describing whether or not this RDR has been looked at, and whether there was anything anomalous relating to it. see the code behind 'modify image quality flags' in merstamps for more detail
QualityFlag INT UNSIGNED,
# optional descriptive text associated with the bitfield qualityflag
QualityComments VARCHAR(100),
CreateTime VARCHAR(19) NOT NULL,
# deprecated. this is actually the local true solar time. it is identical to the local_true_solar_time field in HeaderLevel.
LST VARCHAR(8),
PRIMARY KEY (ParentProductID),
UNIQUE UC_ParentProductID (ParentProductID));

# selected header information for EDRs. link this tabel to the EDRLevel table using fullpath. follow along in a header and it should be obvious which key came from which header element
TABLE HeaderLevel(
FullPath VARCHAR(255) NOT NULL,
command_sequence_number INT UNSIGNED,
frame_id VARCHAR(10),
frame_type VARCHAR(10),
instrument_serial_number INT UNSIGNED,
observation_id BIGINT UNSIGNED,
planet_day_number INT UNSIGNED,
product_creation_time VARCHAR(24),
sclk_start DOUBLE UNSIGNED,
sclk_stop DOUBLE UNSIGNED,
start_time VARCHAR(24),
stop_time VARCHAR(24),
local_true_solar_time VARCHAR(8),
exposure_duration FLOAT UNSIGNED,
exposure_duration_count INT UNSIGNED,
# rover motion counter
rmc_site INT UNSIGNED,
rmc_drive INT UNSIGNED,
sequence_id VARCHAR(5),
sequence_version_id INT UNSIGNED,
solar_longitude DOUBLE,
# earth received time
ert_start VARCHAR(24),
ert_stop VARCHAR(24),
# az and el in site frame
inst_az_site DOUBLE,
inst_el_site DOUBLE,
solar_az_site DOUBLE,
solar_el_site DOUBLE,
# az and el in rover frame
inst_az_rover DOUBLE,
inst_el_rover DOUBLE,
# az and el in mast frame (instrument articulation state)
inst_az_measured DOUBLE,
inst_el_measured DOUBLE,
# subframe
first_line INT UNSIGNED,
first_line_sample INT UNSIGNED,
n_lines INT UNSIGNED,
n_line_samples INT UNSIGNED,
# temperatures
pcb_temp FLOAT,
ccd_temp FLOAT,
# shutter correction
shutter_correction_flag ENUM ('TRUE', 'FALSE'),
# sample_bit_mode_id (lookup table)
sample_bit_mode_id VARCHAR(8) NOT NULL,
# downsampling
pixel_averaging_height INT UNSIGNED,
pixel_averaging_width INT UNSIGNED,
#filter info
filter_number TINYINT UNSIGNED,
filter_name VARCHAR(30),
# layers - may be userful someday in the future
bands INT UNSIGNED,
# compression info
cmprs_mode TINYINT UNSIGNED,
cmprs_name VARCHAR(255),
cmprs_rate FLOAT UNSIGNED,
cmprs_ratio FLOAT UNSIGNED,
cmprs_segment_quality VARCHAR(100),
# site to rover conversion
site_rover_origin_rotation_quaternian_a DOUBLE,
site_rover_origin_rotation_quaternian_b DOUBLE,
site_rover_origin_rotation_quaternian_c DOUBLE,
site_rover_origin_rotation_quaternian_d DOUBLE,
site_rover_origin_offset_vector_a DOUBLE,
site_rover_origin_offset_vector_b DOUBLE,
site_rover_origin_offset_vector_c DOUBLE,
PRIMARY KEY (FullPath));

# information about the data products
TABLE DataProductLevel(
# links the data product to an observation in the Observations table
ObsID VARCHAR(28) NOT NULL,
SCLK BIGINT UNSIGNED NOT NULL,
# apid and apid subtype
APID TINYINT UNSIGNED NOT NULL,
APIDsub TINYINT UNSIGNED NOT NULL,
# which command in the sequence/observation this data product was generated by
CommandNumber INT UNSIGNED NOT NULL,
# whether this product is marked as sent onboard the rover
DownlinkStatus ENUM('Y','N') NOT NULL,
# to what degree this data product can be found in the oss on the ground - 'F' = full product, 'P' = partial product, 'N' = no product
ProductStatus ENUM('F','P','N') NOT NULL,
# taken from the columns of same name in the data product table / data product list which is downlinked with every pass (ie /oss/mera/ops/ops/surface/tactical/staging/ssw/products/050/068/f0007-000-0000_001_0126407614-209.txt)
XbandTime VARCHAR(10),
UHFTime VARCHAR(10),
# the full path of this data product
FullPath VARCHAR(255),
# the line from the data product table which refers to this product
DPTLine VARCHAR(160),
DownlinkPriority TINYINT UNSIGNED NOT NULL,
ProductSize FLOAT NOT NULL,
# whether or not this data product is on a delete list on the ground
DeleteStatus ENUM('Y','N'),
# not currently used
ReXmitStatus ENUM('Y','N'),
# whether or not this product has been deleted off the rover
RoverDelete ENUM('Y','N'),
# creation time for the ground copy of the data product
CreateTime VARCHAR(19),
CompressionFactor FLOAT,
# a unique string for each data product
ProductID VARCHAR(50) NOT NULL,
# the earliest data product table in which this data product appears
EarliestDPTFullPath VARCHAR(255),
# the latest data product table in which this data product appears
LatestDPTFullPath VARCHAR(255),
FOREIGN KEY (ProductID) REFERENCES EDRLevel (ParentProductID),
PRIMARY KEY (ObsID, ProductID));

# keeps track of observations. there will be one row for every execution of a sequence
TABLE Observations(
rover ENUM('A','B') NOT NULL,
# almost always 'ops'
test VARCHAR(15) NOT NULL,
sol VARCHAR(3) NOT NULL,
# ie 'p1234'
SeqID VARCHAR(5) NOT NULL,
SeqVer VARCHAR(2) NOT NULL,
# which instance of this sequence on a given sol this is. ie if a sequence was run three times, this would take on the values 1, 2, and 3 for the three rows
RepeatID TINYINT UNSIGNED NOT NULL,
# unique identifier for the rows in this table
ObsID VARCHAR(28) NOT NULL,
NumETH INT UNSIGNED NOT NULL,
NumECS INT UNSIGNED NOT NULL,
NumERS INT UNSIGNED NOT NULL,
NumEHG INT UNSIGNED NOT NULL,
NumERP INT UNSIGNED NOT NULL,
NumEFF INT UNSIGNED NOT NULL,
NumEDN INT UNSIGNED NOT NULL,
NumESF INT UNSIGNED NOT NULL,
# neither of the next 2 are currently used
n_pointings INT UNSIGNED,
n_pointings_no_degenerate INT UNSIGNED,
# this one is populated n_pan_positions INT UNSIGNED,
RMLFullPath VARCHAR(255),
SSFFullPath VARCHAR(255),
# the observations description originating in SAP
Description VARCHAR(175),
PancamEstDuration FLOAT,
APGENEstDuration FLOAT,
PancamEstDataVolume FLOAT,
APGENEstDataVolume FLOAT,
SCLKAsRunDuration FLOAT,
SRETAsRunDuration FLOAT,
# any of dimes, engineering, hazcam, mi, navcam, pancam, unknown. we only distinguish between the different cameras and everythign else at this level. use the APID key in DataProductLevel otherwise.
Instrument VARCHAR(12),
# the Sol_all_ssf_list.txt file (if any) which provided the information for this observation
SSFListFullPath VARCHAR(255),
FOREIGN KEY (ObsID) REFERENCES DataProductLevel (ObsID),
PRIMARY KEY (rover,test,sol,SeqID,SeqVer, RepeatID));

# one row for each caltarget image. provides information on the calibration pipeline
TABLE rc_cal_sel(
# links to the caltarget image in EDRLevel
edrproductid VARCHAR(50) NOT NULL,
fullpath_rc VARCHAR(255),
fullpath_rad VARCHAR(255),
fullpath_roi VARCHAR(255),
createtime_rc VARCHAR(19) NOT NULL,
createtime_rad VARCHAR(19),
createtime_roi VARCHAR(19),
# the calibration coefficients
rad_coeff FLOAT NOT NULL,
rad_coeff_slope FLOAT NOT NULL,
rad_coeff_off FLOAT NOT NULL,
# the raw values for each of these regions from the RAD file
val_black FLOAT,
val_gray FLOAT,
val_white FLOAT,
val_black_shadow FLOAT,
val_gray_shadow FLOAT,
val_white_shadow FLOAT,
val_red FLOAT,
val_green FLOAT,
val_blue FLOAT,
val_yellow FLOAT,
PRIMARY KEY (edrproductid));

TABLE Cubes(
ObsID VARCHAR(28) NOT NULL,
FullPath VARCHAR(255) NOT NULL,
ProductID VARCHAR(50) NOT NULL,
LayerBitMask BIGINT UNSIGNED,
Source_IOF_01 VARCHAR(255),
Source_IOF_02 VARCHAR(255),
Source_IOF_03 VARCHAR(255),
Source_IOF_04 VARCHAR(255),
Source_IOF_05 VARCHAR(255),
Source_IOF_06 VARCHAR(255),
Source_IOF_07 VARCHAR(255),
Source_IOF_08 VARCHAR(255),
Source_IOF_09 VARCHAR(255),
Source_IOF_10 VARCHAR(255),
Source_IOF_11 VARCHAR(255),
Source_IOF_12 VARCHAR(255),
Source_IOF_13 VARCHAR(255),
Source_IOF_14 VARCHAR(255),
Source_IOF_15 VARCHAR(255),
Source_IOF_16 VARCHAR(255),
Source_L_DIS_1 VARCHAR(255),
Source_L_DIS_2 VARCHAR(255),
Source_L_ARG VARCHAR(255),
Source_L_AXZ_X VARCHAR(255),
Source_L_AXZ_Y VARCHAR(255),
Source_L_AXZ_Z VARCHAR(255),
Source_L_AUW_U VARCHAR(255),
Source_L_AUW_V VARCHAR(255),
Source_L_AUW_W VARCHAR(255),
Source_L_AEP_I VARCHAR(255),
Source_L_AEP_E VARCHAR(255),
Source_L_AEP_P VARCHAR(255),
Source_L_MSK VARCHAR(255),
Source_R_DIS_1 VARCHAR(255),
Source_R_DIS_2 VARCHAR(255),
Source_R_ARG VARCHAR(255),
Source_R_AXZ_X VARCHAR(255),
Source_R_AXZ_Y VARCHAR(255),
Source_R_AXZ_Z VARCHAR(255),
Source_R_AUW_U VARCHAR(255),
Source_R_AUW_V VARCHAR(255),
Source_R_AUW_W VARCHAR(255),
Source_R_AEP_I VARCHAR(255),
Source_R_AEP_E VARCHAR(255),
Source_R_AEP_P VARCHAR(255),
Source_R_MSK VARCHAR(255),
PRIMARY KEY (ProductID));