print "Using database observations"
use observations
go

if exists (select name from sysobjects where name = 'obs_columns' and type = 'U')
		begin
		    print "Dropping existing obs_columns table"
		    drop table obs_columns
		end
go

print "Creating table obs_columns"
Create table obs_columns
(
	tname		varchar(30) not null,
	name		varchar(30) not null,
	type		varchar(30) not null,
	length		smallint null,
	format		varchar(10) null,
	keyword		varchar(50) null,
	mandatory	tinyint default 0,
	chk		tinyint default 0,
	keyword_hotf	varchar(50) null
)
go

print "Creating indices on obs_columns table"
create unique clustered index tname_name
        on obs_columns (tname, name)
go

print "Granting"
grant select on obs_columns to public
grant update on obs_columns to frameingest
go

INSERT obs_columns (tname,name,type,length)
SELECT obj.name,col.name,typ.name,col.length
FROM syscolumns col, sysobjects obj, systypes typ
WHERE col.id = obj.id
AND col.usertype = typ.usertype
AND obj.type = 'U'
AND obj.name in (SELECT DISTINCT name FROM obs_tables)
go

UPDATE obs_columns
SET keyword = "HIERARCH ESO " +
upper(substring(name,1,charindex("_",name)-1)) + " " + 
upper(substring(name,charindex("_",name)+1,char_length(name)))
WHERE charindex("_",name) != 0
go

UPDATE obs_columns
SET keyword = substring(keyword,1,charindex("_",keyword)-1) + " " + 
substring(keyword,charindex("_",keyword)+1,char_length(keyword))
WHERE charindex("_",keyword) != 0
go

UPDATE obs_columns SET keyword = upper(name) WHERE charindex("_",name) = 0
go

UPDATE obs_columns SET keyword = NULL WHERE name = 'dimm_fwhm_avg'
UPDATE obs_columns SET keyword = NULL WHERE name = 'dimm_fwhm_rms'
UPDATE obs_columns SET keyword = NULL WHERE name = 'night_flag'
UPDATE obs_columns SET keyword = NULL WHERE name = 'moon_illu'
go

UPDATE obs_columns SET keyword = NULL WHERE name = 'dp_class'
UPDATE obs_columns SET keyword = NULL WHERE name = 'light_path'
UPDATE obs_columns SET keyword = 'HIERARCH ESO FILTER PATH' WHERE name = 'filter_path'
UPDATE obs_columns SET keyword = NULL WHERE name = 'gris_path'
UPDATE obs_columns SET keyword = NULL WHERE name = 'grat_path'
UPDATE obs_columns SET keyword = NULL WHERE name = 'slit_path'
UPDATE obs_columns SET keyword = NULL WHERE name = 'exp_start'
UPDATE obs_columns SET keyword = NULL WHERE name = 'qual_flag'
UPDATE obs_columns SET keyword = NULL WHERE name = 'rel_date'
UPDATE obs_columns SET keyword = NULL WHERE name = 'public_f'
go

UPDATE obs_columns SET keyword = 'ARCFILE' WHERE name = 'dp_id'
UPDATE obs_columns SET keyword = 'HIERARCH ESO OBS ID' WHERE name = 'ob_id'
UPDATE obs_columns SET keyword = 'TELESCOP' WHERE name = 'tel_id'
UPDATE obs_columns SET keyword = 'INSTRUME' WHERE name = 'ins_id'
UPDATE obs_columns SET keyword = 'HIERARCH ESO OBS PROG ID' WHERE name = 'prog_id'
UPDATE obs_columns SET keyword = 'HIERARCH ESO DPR CATG' WHERE name = 'dp_cat'
UPDATE obs_columns SET keyword = 'HIERARCH ESO DPR TYPE' WHERE name = 'dp_type'
UPDATE obs_columns SET keyword = 'HIERARCH ESO DPR TECH' WHERE name = 'dp_tech'
UPDATE obs_columns SET keyword = 'MJD-OBS' WHERE name = 'mjd_obs'
UPDATE obs_columns SET keyword = 'HIERARCH ESO OBS TPLNO' WHERE name = 'tpl_seqno'
UPDATE obs_columns SET keyword = 'HIERARCH ESO DET EXP NO' WHERE name = 'det_expid'
UPDATE obs_columns SET keyword = 'HIERARCH ESO DET CHIP1 ID' WHERE name = 'det_chip1id'
UPDATE obs_columns SET keyword = 'HIERARCH ESO DET CHOP NCYCLES' WHERE name = 'det_chop_cycles'
go

-- Filling the column keyword_hotf (new column added to obs_columns table)
print "Filling the column keyword_hotf"
UPDATE obs_columns SET keyword_hotf = keyword
go

UPDATE obs_columns SET keyword_hotf = NULL
WHERE name = 'det_chop_cycles'
AND tname = 'data_products'
go

UPDATE obs_columns SET keyword_hotf = NULL
WHERE name = 'det_dit'
AND tname = 'data_products'
go

UPDATE obs_columns SET keyword_hotf = NULL
WHERE name = 'det_ndit'
AND tname = 'data_products'
go

UPDATE obs_columns SET keyword_hotf = NULL
WHERE name = 'filter_path'
AND tname = 'data_products'
go
