-- MySQL Script generated by MySQL Workbench
-- Fri Mar 18 17:41:27 2016
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema pyros
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `pyros` ;

-- -----------------------------------------------------
-- Schema pyros
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `pyros` DEFAULT CHARACTER SET utf8 ;
USE `pyros` ;

-- -----------------------------------------------------
-- Table `pyros`.`country`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pyros`.`country` ;

CREATE TABLE IF NOT EXISTS `pyros`.`country` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `desc` TINYTEXT NULL,
  `quota` FLOAT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `pyros`.`userlevel`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pyros`.`userlevel` ;

CREATE TABLE IF NOT EXISTS `pyros`.`userlevel` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `desc` MEDIUMTEXT NULL,
  `priority` INT NULL,
  `quota` FLOAT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `pyros`.`user`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pyros`.`user` ;

CREATE TABLE IF NOT EXISTS `pyros`.`user` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `country_id` INT NOT NULL,
  `userlevel_id` INT NOT NULL COMMENT '\n(ros leveladmin)',
  `name` VARCHAR(45) NULL COMMENT '\n(',
  `desc` TINYTEXT NULL,
  `created` DATETIME NULL,
  `updated` DATETIME NULL,
  `firstname` VARCHAR(45) NULL,
  `email` VARCHAR(45) NULL,
  `url` VARCHAR(45) NULL,
  `tel1` VARCHAR(45) NULL,
  `tel2` VARCHAR(45) NULL,
  `address` VARCHAR(45) NULL,
  `login` VARCHAR(15) NULL,
  `pass` VARCHAR(45) NULL,
  `last_connect` DATETIME NULL,
  `cur_connect` DATETIME NULL COMMENT '\ncurrent time during connection',
  `putvalid_beg` DATETIME NULL COMMENT '\npermission first date of connection',
  `putvalid_end` DATETIME NULL COMMENT '\npermission last date of connection',
  `acqvalid_beg` VARCHAR(45) NULL COMMENT '\nfirst date to acquire images',
  `acqvalid_end` VARCHAR(45) NULL COMMENT '\nlast date to acquire images',
  `quota` FLOAT NULL COMMENT '\nquota in % of allocated observation time',
  `quota_rea` FLOAT NULL,
  `u_priority` INT NULL COMMENT '\ndefault priority level for requests (average level)',
  `p_priority` INT NULL COMMENT '\ndefault priority level for image processing queue ',
  `dir_level` INT NULL COMMENT '\nif <0 ability to explore data system',
  `can_del_void_req` TINYINT(1) NULL COMMENT '\nALLOW_TO_DELETE_EMPTY_REQUEST_(WITHOUT_SCENE)',
  PRIMARY KEY (`id`),
  INDEX `fk_user_country1_idx` (`country_id` ASC),
  INDEX `fk_users_uprofile1_idx` (`userlevel_id` ASC),
  CONSTRAINT `fk_user_country1`
    FOREIGN KEY (`country_id`)
    REFERENCES `pyros`.`country` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_users_uprofile1`
    FOREIGN KEY (`userlevel_id`)
    REFERENCES `pyros`.`userlevel` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = 'Default user for a SVOM alert is « SVOM_alert »';


-- -----------------------------------------------------
-- Table `pyros`.`scientificprogram`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pyros`.`scientificprogram` ;

CREATE TABLE IF NOT EXISTS `pyros`.`scientificprogram` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `desc` TINYTEXT NULL,
  `quota` FLOAT NULL,
  `priority` INT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `pyros`.`request`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pyros`.`request` ;

CREATE TABLE IF NOT EXISTS `pyros`.`request` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `scientificprogram_id` INT NOT NULL,
  `name` VARCHAR(45) NULL,
  `desc` TINYTEXT NULL,
  `created` DATETIME NULL,
  `updated` DATETIME NULL,
  `is_alert` TINYINT(1) NULL DEFAULT 0 COMMENT '\n0 = routine\n1 = alert',
  `type` ENUM('alert', 'routine', 'svom TOO', 'svom GP') NULL,
  `status` ENUM('INCOMPLETE', 'SUBMITTED', 'PLANNED', 'EXECUTING', 'EXECUTED') NULL,
  `target_or_theme` VARCHAR(45) NULL,
  `priority` INT NULL,
  `autodeposit` TINYINT(1) NULL COMMENT '\n=1 si automatic =0 si manual (web)',
  `checkpoint` VARCHAR(45) NULL,
  `flag` VARCHAR(45) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_request_user_idx` (`user_id` ASC),
  INDEX `fk_request_scientificprogram_idx` (`scientificprogram_id` ASC),
  CONSTRAINT `fk_request_user`
    FOREIGN KEY (`user_id`)
    REFERENCES `pyros`.`user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_request_scientificprogram`
    FOREIGN KEY (`scientificprogram_id`)
    REFERENCES `pyros`.`scientificprogram` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = 'A complete observation (set of sequences)\n\nIncludes at least one sequence and meets following criteria:\n\r• Observation of the same target or eventually a set of coherent targets or a specific field for calibration\n\r• The sequences of a request are not executed contiguously in time\r\r• Any observation demand is defined as one or several Requests\n\r• The observer is the request owner\n';


-- -----------------------------------------------------
-- Table `pyros`.`sequencetype`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pyros`.`sequencetype` ;

CREATE TABLE IF NOT EXISTS `pyros`.`sequencetype` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `desc` TINYTEXT NULL,
  `priority` INT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `pyros`.`schedule`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pyros`.`schedule` ;

CREATE TABLE IF NOT EXISTS `pyros`.`schedule` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `created` DATETIME NULL,
  `day_start` DATETIME NULL,
  `day_stop` DATETIME NULL,
  `flag` VARCHAR(45) NULL COMMENT '\nflag de synchro',
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `pyros`.`sequence`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pyros`.`sequence` ;

CREATE TABLE IF NOT EXISTS `pyros`.`sequence` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `request_id` INT NOT NULL,
  `sequencetype_id` INT NOT NULL,
  `schedule_id` INT NOT NULL,
  `name` VARCHAR(45) NULL,
  `desc` TINYTEXT NULL,
  `created` DATETIME NULL,
  `updated` DATETIME NULL,
  `is_alert` TINYINT(1) NULL,
  `status` ENUM('INCOMPLETE', 'SUBMITTED', 'TOBEPLANNED', 'OBSERVABLE', 'PLANNED', 'EXECUTING', 'EXECUTED') NULL,
  `duration` FLOAT NULL COMMENT '\n20 mn max (mmss)',
  `pointing` VARCHAR(45) NULL,
  `with_drift` TINYINT(1) NULL,
  `priority` INT NULL COMMENT '\nu_priotity + r_priority',
  `analysis_method` VARCHAR(45) NULL,
  `exec_start` DATETIME NOT NULL COMMENT '\nif exec_start_datetime only, then sequence must start at a precise date and time\n\nif exec_stop_datetime is not null, then sequence must start between a time slot [exec_start_datetime, exec_stop_datetime]',
  `exec_stop` DATETIME NULL,
  `moon_min` INT NULL COMMENT '\nangle de garde à la lune',
  `alt_min` INT NULL COMMENT '\nangle de garde par rapport à l\'horizon',
  `type` ENUM('image', 'alerte', 'dark', 'bias', 'flat') NULL COMMENT '\nImage, alerte; dark,Bias, flat',
  `img_current` VARCHAR(45) NULL,
  `img_total` VARCHAR(45) NULL,
  `not_obs` TINYINT(1) NULL,
  `obsolete` TINYINT(1) NULL,
  `processing` TINYINT(1) NULL,
  `flag` VARCHAR(45) NULL COMMENT '\nflag de synchro',
  PRIMARY KEY (`id`),
  INDEX `fk_sequence_sequencetype_idx` (`sequencetype_id` ASC),
  INDEX `fk_sequence_request_idx` (`request_id` ASC),
  INDEX `fk_sequence_schedule_idx` (`schedule_id` ASC),
  CONSTRAINT `fk_sequence_sequencetype`
    FOREIGN KEY (`sequencetype_id`)
    REFERENCES `pyros`.`sequencetype` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_sequence_request`
    FOREIGN KEY (`request_id`)
    REFERENCES `pyros`.`request` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_sequence_schedule`
    FOREIGN KEY (`schedule_id`)
    REFERENCES `pyros`.`schedule` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = 'Collection of Albums, and thus of Plans with the following features:\n\r• Same pointing with or without drift\r\r• The sequence is endowed with a priority; its execution will be conditioned by the\rplanning of other sequences with more priority; during execution, the sequence\rcan be interrupted every time to give way to a higher priority sequence\r\r• The observations are carried out with one or two cameras\r\r• The length of a sequence is variable and cannot exceed 20 minutes (TBC)\r\r• The sequence can include parameters that indicate which method must be used to\ranalyze data (RT or remote)\n\r• For each sequence the observer defines an execution date and time. It is a time\rslot or a precise time for starting the observation\r\r• The sequence ends synchronously for all involved instruments. \n\n• The sequence includes several Albums, one per detector\r\r• The owner of the sequence is a heritage of the Request owner\n';


-- -----------------------------------------------------
-- Table `pyros`.`device`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pyros`.`device` ;

CREATE TABLE IF NOT EXISTS `pyros`.`device` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `desc` TINYTEXT NULL,
  `created` DATETIME NULL,
  `updated` DATETIME NULL,
  `is_online` TINYINT(1) NULL DEFAULT 1 COMMENT '1=online, 0=offline',
  `status` ENUM('online', 'offline', 'maintenance', 'dead', 'busy', 'paused') NULL,
  `maintenance_date` DATETIME NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `pyros`.`telescope`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pyros`.`telescope` ;

CREATE TABLE IF NOT EXISTS `pyros`.`telescope` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `device_id` INT NOT NULL,
  `mount_type` ENUM('slewing', 'observing') NULL COMMENT '\nequ or altaz',
  `diameter` FLOAT NULL COMMENT '\naperture diameter (m)',
  `status` VARCHAR(45) NULL,
  `latitude` DOUBLE NULL COMMENT '\n(deg)',
  `longitude` DOUBLE NULL COMMENT '\n(deg)',
  `sens` VARCHAR(1) NULL COMMENT '\nE ou W',
  `altitude` FLOAT NULL COMMENT '\n(m)',
  `readout_time` INT NULL COMMENT '\ncontroler read out (s)',
  `slew_time` INT NULL COMMENT '\ntipical delay for pointing (s)',
  `slew_dead` INT NULL COMMENT '\ndelay after pointing (s)',
  `slew_rate_max` FLOAT NULL COMMENT '\nmost rapid slew for pointing (deg/s)',
  `horizon_type` VARCHAR(45) NULL COMMENT '\ntype of coordinates for horizondef (altaz or hadec)',
  `horizon_def` DOUBLE NULL COMMENT '\ndefine horizon elevation amers',
  `lim_dec_max` DOUBLE NULL COMMENT '\nhighest limit of declination (deg)',
  `lim_dec_min` DOUBLE NULL COMMENT '\nlowest limit of declination  (deg)',
  `lim_ha_rise` DOUBLE NULL COMMENT '\neastern rotation limit (deg)',
  `lim_ha_set` DOUBLE NULL COMMENT '\nwestern rotation limit (deg)',
  `address` VARCHAR(45) NULL COMMENT '\npost address',
  `night_elev_sun` DOUBLE NULL COMMENT '\nMax elevation of the sun to observe (deg)',
  `mpc_code` VARCHAR(45) NULL COMMENT '\nMinor Planet Center code of the observatory',
  PRIMARY KEY (`id`),
  INDEX `fk_telescope_device_idx` (`device_id` ASC),
  CONSTRAINT `fk_telescope_device`
    FOREIGN KEY (`device_id`)
    REFERENCES `pyros`.`device` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = 'Convention pour LIMHARISE et LIMASSET\rEn asimuth :\r0 --> sud\r90 --> est\r\r';


-- -----------------------------------------------------
-- Table `pyros`.`detector`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pyros`.`detector` ;

CREATE TABLE IF NOT EXISTS `pyros`.`detector` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `device_id` INT NOT NULL,
  `telescope_id` INT NOT NULL,
  `status` ENUM('idle', 'working', 'hs') NULL,
  `nb_photo_x` INT NULL COMMENT '\nnumber  of photosite along X',
  `nb_photo_y` INT NULL COMMENT '\nnumber of photosite along Y',
  `photo_size_x` INT NULL COMMENT '\nsize of photosite along X (um/photosite)',
  `photo_size_y` INT NULL COMMENT '\nsize of photosite along Y (um/photosite)',
  `has_shutter` TINYINT(1) NULL,
  `equivalent_foc_len` VARCHAR(45) NULL COMMENT '\nequivalent focal lenght (m)',
  `acq_start` DATETIME NULL COMMENT '\ndate of beginning acquisition',
  `acq_stop` DATETIME NULL COMMENT '\ndate of stopping acquisition',
  `check_temp` DOUBLE NULL COMMENT '\noperating temperature (Celsius)',
  `gain` FLOAT NULL COMMENT '\ngain (electron/ADU)',
  `readout_noise` FLOAT NULL COMMENT '\nreadout noise (electron)',
  `readout_time` FLOAT NULL COMMENT '\nsec delay for coder reading',
  `idcam_readout_mode` INT NULL COMMENT '\n\nmode of pixel reading',
  PRIMARY KEY (`id`),
  INDEX `fk_detector_telescope_idx` (`telescope_id` ASC),
  INDEX `fk_detector_device_idx` (`device_id` ASC),
  CONSTRAINT `fk_detector_telescope`
    FOREIGN KEY (`telescope_id`)
    REFERENCES `pyros`.`telescope` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_detector_device`
    FOREIGN KEY (`device_id`)
    REFERENCES `pyros`.`device` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `pyros`.`album`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pyros`.`album` ;

CREATE TABLE IF NOT EXISTS `pyros`.`album` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `sequence_id` INT NOT NULL,
  `detector_id` INT NOT NULL,
  `name` VARCHAR(45) NULL,
  `desc` TINYTEXT NULL,
  `created` DATETIME NULL,
  `updated` DATETIME NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_album_detector_idx` (`detector_id` ASC),
  INDEX `fk_album_sequence_idx` (`sequence_id` ASC),
  CONSTRAINT `fk_album_sequence`
    FOREIGN KEY (`sequence_id`)
    REFERENCES `pyros`.`sequence` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_album_detector`
    FOREIGN KEY (`detector_id`)
    REFERENCES `pyros`.`detector` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = 'Series of Plans belonging to a Sequence and observed with the same detector';


-- -----------------------------------------------------
-- Table `pyros`.`filter`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pyros`.`filter` ;

CREATE TABLE IF NOT EXISTS `pyros`.`filter` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `device_id` INT NOT NULL,
  `detector_id` INT NOT NULL,
  `category` CHAR(1) NULL COMMENT '\nOne letter to describe the filter family :\n\'E,F,G,H,I,J,K...\'',
  `transmission_curve_doc` VARCHAR(45) NULL COMMENT '\npdf doc that describes the transmission curve',
  PRIMARY KEY (`id`),
  INDEX `fk_filter_detector_idx` (`detector_id` ASC),
  INDEX `fk_filter_device_idx` (`device_id` ASC),
  CONSTRAINT `fk_filter_detector`
    FOREIGN KEY (`detector_id`)
    REFERENCES `pyros`.`detector` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_filter_device`
    FOREIGN KEY (`device_id`)
    REFERENCES `pyros`.`device` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `pyros`.`plan`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pyros`.`plan` ;

CREATE TABLE IF NOT EXISTS `pyros`.`plan` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `album_id` INT NOT NULL,
  `filter_id` INT NOT NULL,
  `name` VARCHAR(45) NULL,
  `desc` VARCHAR(45) NULL,
  `created` DATETIME NULL,
  `updated` DATETIME NULL,
  `duration` FLOAT NULL,
  `position` VARCHAR(45) NULL,
  `exposure_time` FLOAT NULL,
  `nb_images` INT NULL,
  `dithering` TINYINT(1) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_plan_album_idx` (`album_id` ASC),
  INDEX `fk_plan_filter_idx` (`filter_id` ASC),
  CONSTRAINT `fk_plan_album`
    FOREIGN KEY (`album_id`)
    REFERENCES `pyros`.`album` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_plan_filter`
    FOREIGN KEY (`filter_id`)
    REFERENCES `pyros`.`filter` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = 'Acquisition of a series of images by one of the instruments of the focal plane with the same features:\n\n• Same filter for all the duration of a Plan\r• Same exposure time\n• Number of images defined\n• Same mode Dithering/ or not for NIR instrument\n';


-- -----------------------------------------------------
-- Table `pyros`.`nrtanalysis`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pyros`.`nrtanalysis` ;

CREATE TABLE IF NOT EXISTS `pyros`.`nrtanalysis` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `desc` TINYTEXT NULL,
  `created` DATETIME NULL,
  `updated` DATETIME NULL,
  `analysis` LONGTEXT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
COMMENT = 'Level 1b analysis results';


-- -----------------------------------------------------
-- Table `pyros`.`image`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pyros`.`image` ;

CREATE TABLE IF NOT EXISTS `pyros`.`image` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `plan_id` INT NOT NULL,
  `nrtanalysis_id` INT NULL,
  `name` VARCHAR(45) NULL,
  `desc` TINYTEXT NULL,
  `created` DATETIME NULL,
  `updated` DATETIME NULL,
  `date_from_gps` VARCHAR(45) NULL COMMENT '\n =1 : heure donnée par le GPS,=0  heure donnée par le PC',
  `level` INT NULL COMMENT '0 = level 0\n1 = level 1a (+ corrections)\n2 = level 1b (+ analysis)',
  `type` ENUM('image', 'dark', 'flat', 'bias') NULL,
  `quality` VARCHAR(45) NULL,
  `flaggps` VARCHAR(45) NULL,
  `exposure` VARCHAR(45) NULL,
  `tempext` VARCHAR(45) NULL,
  `pressure` VARCHAR(45) NULL,
  `humidext` VARCHAR(45) NULL,
  `wind` VARCHAR(45) NULL,
  `wind_dir` VARCHAR(45) NULL,
  `dwnimg` VARCHAR(45) NULL,
  `dwncata` VARCHAR(45) NULL,
  `dwn` VARCHAR(45) NULL,
  `level0_fits_name` VARCHAR(45) NULL COMMENT 'name of FITS image level 0 on disk',
  `level1a_fits_name` VARCHAR(45) NULL COMMENT 'name of FITS image level 1a on disk (after corrections)',
  `level1b_fits_name` VARCHAR(45) NULL COMMENT 'name of FITS image level 1b on disk (after analysis)',
  PRIMARY KEY (`id`),
  INDEX `fk_image_plan_idx` (`plan_id` ASC),
  INDEX `fk_image_nrtanalysis_idx` (`nrtanalysis_id` ASC),
  CONSTRAINT `fk_image_plan`
    FOREIGN KEY (`plan_id`)
    REFERENCES `pyros`.`plan` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_image_nrtanalysis`
    FOREIGN KEY (`nrtanalysis_id`)
    REFERENCES `pyros`.`nrtanalysis` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = 'Basis component. Its acquisition can be interrupted by an interruption of sequence but it must be saved before quitting';


-- -----------------------------------------------------
-- Table `pyros`.`strategyobs`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pyros`.`strategyobs` ;

CREATE TABLE IF NOT EXISTS `pyros`.`strategyobs` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `desc` MEDIUMTEXT NULL,
  `json_file` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `pyros`.`alert`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pyros`.`alert` ;

CREATE TABLE IF NOT EXISTS `pyros`.`alert` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `request_id` INT NOT NULL,
  `strategyobs_id` INT NOT NULL,
  `voevent_xml` LONGTEXT NULL,
  `type` VARCHAR(45) NULL COMMENT '\nalert type',
  `client` VARCHAR(45) NULL,
  `burst_jd` VARCHAR(45) NULL,
  `burst_ra` VARCHAR(45) NULL COMMENT '\nRight ascension (deg)',
  `burst_dec` VARCHAR(45) NULL COMMENT '\ndeclinaison (deg)',
  `equinox` VARCHAR(45) NULL,
  `jd_pkt` INT NULL COMMENT '\n JD of packet creation by GCN',
  `jd_send` INT NULL COMMENT '\nJD of packet send by GCN',
  `jd_received` INT NULL COMMENT '\nJD of packet received',
  `trigger_instrum` VARCHAR(45) NULL,
  `trigger_num` VARCHAR(45) NULL,
  `grb_error` VARCHAR(45) NULL COMMENT '\nerror box (arcmin)',
  `def_not_grb` TINYINT(1) NULL COMMENT '\n =1 if not GRB',
  `editor` VARCHAR(45) NULL COMMENT '\n\nclient that sent the notice',
  `flag` VARCHAR(45) NULL COMMENT '\n\nFlag de synchro',
  `idgcn_notice` INT NULL COMMENT '\nidentifiant notice',
  PRIMARY KEY (`id`),
  INDEX `fk_reqalert_request1_idx` (`request_id` ASC),
  INDEX `fk_alertrequests_strategyobs1_idx` (`strategyobs_id` ASC),
  CONSTRAINT `fk_reqalert_requests1`
    FOREIGN KEY (`request_id`)
    REFERENCES `pyros`.`request` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_alertrequests_strategyobs1`
    FOREIGN KEY (`strategyobs_id`)
    REFERENCES `pyros`.`strategyobs` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `pyros`.`schedulehistory`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pyros`.`schedulehistory` ;

CREATE TABLE IF NOT EXISTS `pyros`.`schedulehistory` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `created` DATETIME NULL,
  `day_start` DATETIME NULL,
  `day_stop` DATETIME NULL,
  `flag` VARCHAR(45) NULL COMMENT '\nflag de synchro',
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `pyros`.`weatherwatch`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pyros`.`weatherwatch` ;

CREATE TABLE IF NOT EXISTS `pyros`.`weatherwatch` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `updated` DATETIME NULL,
  `humid_int` FLOAT NULL,
  `humid_ext` FLOAT NULL,
  `wind` FLOAT NULL COMMENT '\n(m/s)',
  `wind_dir` VARCHAR(1) NULL COMMENT '\n \'N=0 E=90 S=180 W=270\'',
  `temp_int` FLOAT NULL,
  `temp_ext` FLOAT NULL,
  `pressure` FLOAT NULL,
  `rain` FLOAT NULL,
  `dwn` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `pyros`.`sitewatch`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pyros`.`sitewatch` ;

CREATE TABLE IF NOT EXISTS `pyros`.`sitewatch` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `updated` DATETIME NULL,
  `lights` VARCHAR(45) NULL,
  `dome` VARCHAR(45) NULL,
  `doors` VARCHAR(45) NULL,
  `temperature` FLOAT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `pyros`.`sitewatchhistory`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pyros`.`sitewatchhistory` ;

CREATE TABLE IF NOT EXISTS `pyros`.`sitewatchhistory` (
  `id` INT NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `pyros`.`weatherwatchhistory`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pyros`.`weatherwatchhistory` ;

CREATE TABLE IF NOT EXISTS `pyros`.`weatherwatchhistory` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `datetime` DATETIME NULL,
  `humid_int` VARCHAR(45) NULL,
  `humid_ext` VARCHAR(45) NULL,
  `wind` VARCHAR(45) NULL COMMENT '\n(m/s)',
  `wind_dir` VARCHAR(45) NULL COMMENT '\n \'N=0 E=90 S=180 W=270\'',
  `temp_int` VARCHAR(45) NULL,
  `temp_ext` VARCHAR(45) NULL,
  `pressure` VARCHAR(45) NULL,
  `rain` VARCHAR(45) NULL,
  `dwn` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `pyros`.`schedule_has_sequences`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pyros`.`schedule_has_sequences` ;

CREATE TABLE IF NOT EXISTS `pyros`.`schedule_has_sequences` (
  `schedulehistory_id` INT NOT NULL,
  `sequence_id` INT NOT NULL,
  PRIMARY KEY (`schedulehistory_id`, `sequence_id`),
  INDEX `fk_schedules_has_sequences_sequences1_idx` (`sequence_id` ASC),
  INDEX `fk_schedules_has_sequences_schedules1_idx` (`schedulehistory_id` ASC),
  CONSTRAINT `fk_schedules_has_sequences_schedules1`
    FOREIGN KEY (`schedulehistory_id`)
    REFERENCES `pyros`.`schedulehistory` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_schedules_has_sequences_sequences1`
    FOREIGN KEY (`sequence_id`)
    REFERENCES `pyros`.`sequence` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `pyros`.`user_has_scientificprograms`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pyros`.`user_has_scientificprograms` ;

CREATE TABLE IF NOT EXISTS `pyros`.`user_has_scientificprograms` (
  `user_id` INT NOT NULL,
  `scientificprogram_id` INT NOT NULL,
  PRIMARY KEY (`user_id`, `scientificprogram_id`),
  INDEX `fk_users_has_scientificprograms_scientificprograms1_idx` (`scientificprogram_id` ASC),
  INDEX `fk_users_has_scientificprograms_users1_idx` (`user_id` ASC),
  CONSTRAINT `fk_users_has_scientificprograms_users1`
    FOREIGN KEY (`user_id`)
    REFERENCES `pyros`.`user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_users_has_scientificprograms_scientificprograms1`
    FOREIGN KEY (`scientificprogram_id`)
    REFERENCES `pyros`.`scientificprogram` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;