print "Using database observations"
use observations
go

print "Creating new data_products table"
go

Create table data_products
(
	dp_id		 varchar(45)  not null ,
	ob_id		 int          null ,
	tel_id	         varchar(30)  null ,
	ins_id		 varchar(30)  null ,
	prog_id		 varchar(20)  null ,
	dp_class	 varchar(20)  null ,
	dp_cat		 varchar(30)  null ,
	dp_type		 varchar(30)  null ,
	dp_tech		 varchar(30)  null ,
	light_path	 varchar(255) null ,
	exp_start	 datetime     null ,
	mjd_obs	         float        null ,
	exptime		 float        null ,
	tpl_seqno	 int          null ,
	tpl_expno	 int          null ,
	det_id		 varchar(30)  null ,
	det_expid	 int          null ,
	qual_flag	 varchar(10)  null ,
	ra		 int          null ,
	dec		 int          null ,
	object		 varchar(64)  null ,
	ins_mode	 varchar(40)  null ,
	obs_name	 varchar(70)  null ,
	naxis1		 smallint     null ,
	naxis2		 smallint     null ,
	tel_airm_start	 float        null ,
	origfile	 varchar(128) null , 
	det_chip1id	 varchar(64)  null ,
	rel_date	 datetime     null ,
	public_f	 char(1)      null ,
	filter_path	 varchar(255) null ,
	gris_path	 varchar(255) null ,
	grat_path	 varchar(255) null ,
	slit_path	 varchar(255) null ,
	det_dit      float null,
	det_ndit     smallint null,
	det_chop_cycles smallint null
)
go

print "Creating indexes on data_products table"
go

create unique clustered index dp_id 
	on data_products ( dp_id ) 
go

create index dp_tel_id on data_products (tel_id)
go

create index dp_exp_start on data_products (exp_start)
go

create index dp_mjd_obs on data_products (mjd_obs)
go

create index dp_prog_id on data_products (prog_id)
go
create index dp_ra on data_products (ra)
go
create index dp_public_f on data_products (public_f)
go

grant select on data_products to public
grant insert on data_products to frameingest
grant update on data_products to asto
grant all on data_products to archeso
go

