print "Using database observations"
use observations
go

print "Creating new qc_reports table"
go

Create table qc_reports
(
   dp_id	 varchar(45)  not null,
   ob_id	 int          not null,
   created       smalldatetime null,
   updated  	 smalldatetime null,
   qcr_st	 char(1)      null, 
   qcr_com	 text         null, 
   do_cat	 varchar(30)  null, 
   dp_st	 char(1)      null, 
   dp_com	 text         null, 
   tp_name	 varchar(32)  null, 
   tp_st	 char(1)      null, 
   tp_com	 text         null
)
go

print "Creating index on qc_reports.dp_id column"
go
create unique clustered index dp_id 
	on qc_reports ( dp_id ) 
go

create index date_idx 
	on qc_reports ( updated ) 
go

print "Granting privileges on qc_reports"
go
grant select on qc_reports to public
grant all on qc_reports to archeso
grant insert, update, delete on qc_reports to frameingest
go

print "Creating new qcr_param table"
go
Create table qcr_param
(
   qcr_dp_id	        varchar(45) not null,
   updated  	        smalldatetime not null,
   seeing               int         null,
   seeing_tgt           int         null,
   seeing_min           int         null,
   seeing_max           int         null,
   seeing_st            char(1)     null,
   seeing_com           text        null,
   sky_transparency     varchar(4)  null,
   sky_transparency_tgt varchar(4)  null,
   sky_transparency_min varchar(4)  null,
   sky_transparency_max varchar(4)  null,
   sky_transparency_st  char(1)     null,
   sky_transparency_com text        null,
   air_mass             int         null,
   air_mass_tgt         int         null,
   air_mass_min         int         null,
   air_mass_max         int         null,
   air_mass_st          char(1)     null,
   air_mass_com         text        null,
   fli                  int         null,
   fli_tgt              int         null,
   fli_min              int         null,
   fli_max              int         null,
   fli_st               char(1)     null,
   fli_com              text        null,
   moon_distance        int         null,
   moon_distance_tgt    int         null,
   moon_distance_min    int         null,
   moon_distance_max    int         null,
   moon_distance_st     char(1)     null,
   moon_distance_com    text        null
)
go

print "Creating index on qcr_param.dp_id column"
go
create unique clustered index dp_id 
	on qcr_param ( qcr_dp_id ) 
go

create index date_idx 
	on qcr_param ( updated ) 
go

print "Granting privileges on qcr_param"
go
grant select on qcr_param to public
grant all on qcr_param to archeso
grant insert, update, delete on qcr_param to frameingest
go
