-- *******************************************************************
--  tsrcdbScript: Script for creating Texas Spatial Research Center
--			Database
--   Usage:
--       $ sqlite3 tsrcdb.db < tsrcdbScript
--
-- *******************************************************************
--  Script for creating description table
--
--   Note: The trigger insert_description_last_updated
--          updates last_update in description
-- *******************************************************************
CREATE TABLE description (desc_id	INTEGER PRIMARY KEY,
			  mark_id		INTEGER,
	           	  desc			TEXT NOT NULL DEFAULT '',
                   	  last_contrib		INTEGER,
                   	  last_updated		DATE,
			  UNIQUE(mark_id) );

CREATE TRIGGER insert_description_last_updated AFTER  INSERT ON description

BEGIN

UPDATE description SET last_updated = DATETIME('NOW')
         WHERE rowid = new.rowid;
END;

-- *******************************************************************
--  Script for creating mark table
-- *******************************************************************
CREATE TABLE mark (mark_id	INTEGER PRIMARY KEY,
	           pid		VARCHAR(10),
                   desc_id	INTEGER,
                   prim_obs	INTEGER,
		   stamping	VARCHAR(25),
		   stability	TEXT,
		   setting	TEXT,
		   mark_cond	CHAR(1));

-- *******************************************************************
--  Script for creating contributor table
--
--   Note: The trigger insert_contributor_dateAdded
--          updates dateAdded in contributor
-- *******************************************************************
CREATE TABLE contributor (contrib_id	INTEGER PRIMARY KEY,
	           	  fname		TEXT COLLATE NOCASE,
                   	  lname		TEXT COLLATE NOCASE,
                   	  pword		VARCHAR(15),
                   	  email		TEXT NOT NULL DEFAULT 'N/A' COLLATE NOCASE,
			  phone		TEXT NOT NULL DEFAULT 'N/A',
			  institute	TEXT NOT NULL DEFAULT 'N/A',,
			  access_type	INTEGER,
                   	  dateAdded	DATE,
			  UNIQUE (fname, lname) );

CREATE TRIGGER insert_contributor_dateAdded AFTER  INSERT ON contributor

BEGIN

UPDATE contributor SET dateAdded = DATETIME('NOW')
         WHERE rowid = new.rowid;
END;
-- *******************************************************************
--  Script for creating observation table
-- *******************************************************************
CREATE TABLE observation (obs_id	INTEGER PRIMARY KEY,
	           	  mark_id	INTEGER NOT NULL,
			  pid		VARCHAR(10),
			  dsgn		VARCHAR(25),
			  observed	VARCHAR(25),
			  source	VARCHAR(25),
			  ortho_hgt	NUMERIC,
			  ortho_uncert	NUMERIC,
			  ortho_srce	TEXT,
			  utm_zone	TEXT,
			  utm_north	NUMERIC,
			  utm_east	NUMERIC,
			  utm_converg	NUMERIC,
			  utm_pt_scl	NUMERIC,
			  utm_comb_fct	NUMERIC,
			  spc_zone	TEXT,
			  spc_north	NUMERIC,
			  spc_east	NUMERIC,
			  spc_converg	NUMERIC,
			  spc_pt_scl	NUMERIC,
			  spc_comb_fct	NUMERIC,
			  re_frame	TEXT,
			  epoch		NUMERIC,
			  lat_deg	NUMERIC,
			  lat_min	NUMERIC,
			  lat_sec	NUMERIC,
			  lat_uncert	NUMERIC,
			  lon_deg	NUMERIC,
			  lon_min	NUMERIC,
			  lon_sec	NUMERIC,
			  lon_uncert	NUMERIC,
			  ell_ht	NUMERIC,
			  ell_ht_uncert	NUMERIC,
			  x		NUMERIC,
			  x_uncert	NUMERIC,
			  y		NUMERIC,
			  y_uncert	NUMERIC,
			  z		NUMERIC,
			  z_uncert	NUMERIC, 
			  contrib_id	INTEGER NOT NULL,
			  dateAdded	DATE);

CREATE TRIGGER insert_observation_dateAdded AFTER  INSERT ON observation

BEGIN

UPDATE observation SET dateAdded = DATETIME('NOW')
         WHERE rowid = new.rowid;
END;