/*
** Source for the kronosdb Database
*/

CREATE TABLE kronos_sources (
	source    char(2),
	name      varchar(128),
	need      char(2),
	att       char(4),
	eph       char(4),
	body      varchar(128),
	radius    real,
	PRIMARY KEY (source),
	FOREIGN KEY (need) REFERENCES kronos_sources (source)
);

INSERT INTO  kronos_sources (source, name, need, att, eph, body, radius) VALUES 
	('su', 'Sun', NULL, 'qsun', 'vsun', 'Sun', 695500.),
	('ea', 'Earth', NULL, 'qgse', 'vear', 'Earth', 6378.14),
	('ju', 'Jupiter', NULL, 'qjse', 'vjup', 'Jupiter', 71492.),
	('sc', 'Saturn (Center) - ecliptic frame', NULL, 'qsse', 'vsat', 'Saturn', 60268.),
	('sq', 'Saturn (Center) - equatorial frame', NULL, 'qssq', 'vsat', 'Saturn', 60268.),
	('sj', 'Saturn (Barycenter) - J2000 frame', NULL, 'qj20', 'vstb', 'Saturn', 60268.),
	('ti', 'Titan - ecliptic frame', 'sc', 'qsse', 'vtit', 'Titan' , 2575.0),
	('tq', 'Titan - equatorial frame', 'sq', 'qssq', 'vtit', 'Titan', 2575.0)
	--('--', 'Dummy Source', NULL, NULL, NULL, NULL, NULL)
;

CREATE TABLE kronos_antennae (
	ant       char(1),
	name      varchar(128),
	filename  varchar(128),
	PRIMARY KEY (ant)
);

INSERT INTO  kronos_antennae (ant, name, filename) VALUES
	('j', 'Jupiter-Cal(calJGR2004)','calJGR2004'),
	('d', 'December-04-cal', 'calDec04'),
	('r', 'Rheometry', 'rheometry.ant'),
	('x', 'Default Calibration', '')
;

CREATE TABLE kronos_mfl (
	model     varchar(32),
	name      varchar(128),
	body      varchar(128),
	filename  varchar(128),
	PRIMARY KEY (model)
);

INSERT INTO  kronos_mfl (model, name, body, filename) VALUES
	('D', '7 Gauss (Z96) model', 'Jupiter',  NULL),
	('O6', 'O6 Octupole model (Connerney et al. 1993)', 'Jupiter', NULL),
	('VIP4', 'VIP4 model (Connerney et al. 1998)', 'Jupiter', NULL),
	('VIT4', 'VIT4 model (Connerney et al. 2007)', 'Jupiter', NULL),
	('Z3', 'Z3 model (Connerney et al. 1982)', 'Saturn', NULL),
	('SPV', 'Saturn Pioneer Voyager model (David and Smith, 1990)', 'Saturn', 'iso_fc_grid_SPV.sav'),
	('SPVR', 'SPV-R model, 5th order truncated', 'Saturn', NULL)
;

CREATE TABLE kronos_periods (
	period     varchar(128),
	start_year smallint CHECK (start_year >= 1990 AND start_year <= 2020),
	start_day  smallint CHECK (start_day >= 1 AND start_day <= 366),
	end_year   smallint CHECK (end_year >= 1990 AND end_year <= 2020),
	end_day    smallint CHECK (end_day >= 1 AND end_day <= 366),
	bg         timestamp without time zone,
	bg_tmp     timestamp without time zone,
	PRIMARY KEY (period)
);

CREATE TABLE kronos_days (
	year      smallint CHECK (year >= 1990 AND year <= 2020),
	day       smallint CHECK (day >= 1 AND day <= 366),
	period    varchar(128), --NOT NULL
	source    char(2),
	ant       char(1),
	raw       timestamp without time zone,
	sve       timestamp without time zone,
	svb       timestamp without time zone,
	ltb       timestamp without time zone,
	lis       timestamp without time zone,
	skr       timestamp without time zone,
	PRIMARY KEY (year, day),
	FOREIGN KEY (period) REFERENCES kronos_periods (period) ON DELETE CASCADE,
	FOREIGN KEY (source) REFERENCES kronos_sources (source) ON DELETE CASCADE,
	FOREIGN KEY (ant) REFERENCES kronos_antennae (ant) ON DELETE CASCADE
);

CREATE TABLE kronos_ephem (
	year     smallint,
	day      smallint,
	source   char(2),
	att      timestamp without time zone,
	eph      timestamp without time zone,
	PRIMARY KEY (year, day, source),
	FOREIGN KEY (year, day) REFERENCES kronos_days (year, day) ON DELETE CASCADE,
	FOREIGN KEY (source) REFERENCES kronos_sources (source) ON DELETE CASCADE
);

CREATE TABLE kronos_2hours (
	year     smallint,
	day      smallint,
	hour     smallint CHECK (hour >= 0 AND hour <= 22 and hour % 2 = 0),
	img_raw1 timestamp without time zone,
	img_raw2 timestamp without time zone,
	img_sve1 timestamp without time zone,
	img_sve2 timestamp without time zone,
	img_svb1 timestamp without time zone,
	img_svb2 timestamp without time zone,
	img_ltb1 timestamp without time zone,
	img_ltb2 timestamp without time zone,
	dist     real,
	ra       real,
	lat      real,
	PRIMARY KEY (year, day, hour),
	FOREIGN KEY (year, day) REFERENCES kronos_days (year, day) ON DELETE CASCADE
);

CREATE TABLE kronos_hours (
	year     smallint,
	day      smallint,
	hour     smallint CHECK (hour >= 0 AND hour <= 23),
	k        timestamp without time zone,
	bad      boolean,
	src_dir  varchar(128),
	n1       timestamp without time zone,
	n2       timestamp without time zone,
	n3g      timestamp without time zone,
	index    timestamp without time zone,
	index_3a timestamp without time zone,
	PRIMARY KEY (year, day, hour),
	FOREIGN KEY (year, day) REFERENCES kronos_days (year, day) ON DELETE CASCADE
);

CREATE TABLE kronos_loc (
	year       smallint,
	day        smallint,
	hour       smallint,
	mfl        varchar(32),
	loc        timestamp without time zone,
	loc_3a     timestamp without time zone,
	PRIMARY KEY (year, day, hour, mfl),
	FOREIGN KEY (mfl) REFERENCES kronos_mfl (model) ON DELETE CASCADE,
	FOREIGN KEY (year, day, hour) REFERENCES kronos_hours (year, day, hour) ON DELETE CASCADE
);

CREATE TABLE kronos_loc_err (
	year       smallint,
	day        smallint,
	hour       smallint,
	mfl        varchar(32),
	n_error    smallint,
	loc_err    timestamp without time zone,
	loc_err_3a timestamp without time zone,
	PRIMARY KEY (year, day, hour, mfl, n_error),
	FOREIGN KEY (year, day, hour, mfl) REFERENCES kronos_loc (year, day, hour, mfl) ON DELETE CASCADE
);

CREATE TABLE kronos_n3 (
	year     smallint,
	day      smallint,
	hour     smallint,
	source   char(2),
	ant      char(1),
	n3a      timestamp without time zone,
	n3b      timestamp without time zone,
	n3bfull  timestamp without time zone,
	n3c      timestamp without time zone,
	n3cfull  timestamp without time zone,
	n3d      timestamp without time zone,
	n3dfull  timestamp without time zone,
	n3e      timestamp without time zone,
	n3efull  timestamp without time zone,
	n3f      timestamp without time zone,
	n3       timestamp without time zone,
	PRIMARY KEY (year, day, hour, source, ant),
	FOREIGN KEY (year, day, hour) REFERENCES kronos_hours (year, day, hour) ON DELETE CASCADE,
	FOREIGN KEY (year, day, source) REFERENCES kronos_ephem (year, day, source) ON DELETE CASCADE,
	FOREIGN KEY (ant) REFERENCES kronos_antennae (ant) ON DELETE CASCADE
);


/*
** Function definitions
** All upsert_* functions try to insert or update a row in the corresponding table with the parameters provided.
** They return a boolean reesenting whether the insert/update worked.
*/

CREATE OR REPLACE FUNCTION upsert_period(period_str text, colname text DEFAULT NULL, ts timestamp DEFAULT NULL) RETURNS boolean AS $body$
	DECLARE
		worked smallint;
		interval smallint[];
	BEGIN
		-- This is to ease compatibility with the next function
		IF period_str IS NULL THEN
			RETURN TRUE;
		END IF;
		-- 
		IF colname IS NOT NULL THEN
			-- Dynamically built query using provided column name
			EXECUTE format('UPDATE kronos_periods SET %I = $1 WHERE period = $2', colname)
				USING period_str;
		ELSE
			-- Simply checks whether a corresonding row is already present
			PERFORM 1 FROM kronos_periods WHERE period = period_str;
		END IF;
		-- This is the number of rows affected by the last query.
		GET DIAGNOSTICS worked = ROW_COUNT;
		IF worked <> 1 THEN
			-- The update or perform returned nothing, so try an insert
			interval := regexp_matches(period_str, '^([0-9]{4})_([0-9]{3})_([0-9]{3})$')::smallint[];		
			IF array_length(interval, 1) = 3 THEN
				-- The period has a canonical name, so set the corresponding interval accordingly.
				IF interval[3] = 366 THEN
					-- Check whether the year really does have 366 days
					interval[3] = EXTRACT(DOY FROM ('01/01/' || (interval[1] + 1)::text)::timestamp - '1 day'::interval)::smallint;
				END IF;
				INSERT INTO kronos_periods (period, start_year, start_day, end_year, end_day, bg) VALUES 
					(period_str, interval[1], interval[2], interval[1], interval[3], ts);
			ELSE
				-- Just insert the period and timestamp (in case it was provided)
				INSERT INTO kronos_periods (period, bg) VALUES 
					(period_str, ts);
			END IF;
		END IF;
		-- This should never happen unless there is a serious problem in the database
		IF worked <> 1 THEN
			RAISE NOTICE 'Tried hard to upsert period % to no avail.', period_str;
		END IF;
		RETURN (worked = 1);
	END;
$body$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION upsert_day(year_int smallint, day_int smallint, period_str text DEFAULT NULL, colname text DEFAULT NULL, ts timestamp DEFAULT NULL) RETURNS boolean AS $body$
	DECLARE
		worked smallint;
	BEGIN
		-- First make sure the period alreay exists
		IF upsert_period(period_str) THEN
			IF colname IS NOT NULL THEN
				-- Dynamically built query using provided column name
				EXECUTE format('UPDATE kronos_days SET %I = $1 WHERE year = $2 AND day = $3', colname)
					USING ts, year_int, day_int;
			ELSE
				-- Simply checks whether a corresonding row is already present
				PERFORM 1 FROM kronos_days WHERE year = year_int AND day = day_int;
			END IF;
			-- This is the number of rows affected by the last query.
			GET DIAGNOSTICS worked = ROW_COUNT;
			IF worked <> 1 THEN
				-- The update or perform returned nothing, so try an insert
				IF colname IS NOT NULL THEN
					-- Dynamically built query using provided column name
					EXECUTE format('INSERT INTO kronos_days (year, day, period, %I ) VALUES ($1, $2, $3, $4)', colname)
						USING year_int, day_int, period_str, ts;
				ELSE
					-- Just insert a blank row
					INSERT INTO kronos_days (year, day, period) VALUES (year_int, day_int, period_str);
				END IF;
			END IF;
			-- This is the number of rows affected by the last query.
			GET DIAGNOSTICS worked = ROW_COUNT;
			-- This should never happen unless there is a serious problem in the database
			IF worked <> 1 THEN
				RAISE NOTICE 'Tried hard to upsert day %/% to no avail.', year_int, day_int;
			END IF;
			RETURN (worked = 1);
		ELSE
			-- In case the upsert of period failed
			RAISE NOTICE 'Something went wrong checking for period %.', period_str;
			RETURN FALSE;
		END IF;		
	END;
$body$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION upsert_ephem(year_int smallint, day_int smallint, period_str text, source_str char(2), ext_str char(4) DEFAULT NULL, ts timestamp DEFAULT NULL) RETURNS boolean AS $body$
	BEGIN
		-- First make sure that parent day is in DB
		IF upsert_day(year_int, day_int, period_str) THEN
			-- Ignore last 2 params in case source is provided
			IF source_str IS NOT NULL THEN
				-- Check whether the row already exists
				PERFORM 1 FROM kronos_ephem
					WHERE year = year_int AND day = day_int AND source = source_str;
				-- If update failed (no corresponding ephemeris row yet)
				IF NOT FOUND THEN
					INSERT INTO kronos_ephem (year, day, source) 
						VALUES (year_int, day_int, source_str);
				END IF;
			ELSIF left(ext_str, 1) = 'q' THEN
			 	-- Extension is of an att file
				UPDATE kronos_ephem AS e SET att = ts
					-- Try to update the corrseponding timestamp
					FROM kronos_sources AS s
					WHERE e.year = year_int AND e.day = day_int AND e.source = s.source AND s.att = ext_str;
				IF NOT FOUND THEN
					-- If update failed (no corresponding ephemeris row yet)
					UPDATE kronos_ephem AS e SET att = ts, source = s1.source
						-- Try to group with a partial ephemeris (that has an eph but no att)
						FROM kronos_sources AS s1, kronos_sources AS s2
						WHERE e.year = year_int AND e.day = day_int AND e.att IS NULL AND e.source = s2.source AND s1.att = ext_str AND s2.eph = s1.eph;
					IF NOT FOUND THEN
						-- If no such ephemeris exists
						INSERT INTO kronos_ephem (year, day, source, att, eph)
							-- Try to insert a row such that corresponding eph is already in database
							SELECT year_int, day_int, s2.source, ts, e.eph
							FROM kronos_sources AS s1, kronos_sources AS s2, kronos_ephem AS e
							WHERE year_int = e.year AND day_int = e.day AND e.source = s1.source AND s1.eph = s2.eph AND s2.att = ext_str;
						IF NOT FOUND THEN						
							INSERT INTO kronos_ephem (year, day, source, att)
								-- Insert a row with any corresponding source
								SELECT year_int, day_int, s.source, ts
								FROM kronos_sources AS s
								WHERE s.att = ext_str
								LIMIT 1;
						END IF;
					END IF;
				END IF;
			ELSIF left(ext_str, 1) = 'v' THEN
				-- Extension is of an eph file
				UPDATE kronos_ephem AS e SET eph = ts
					FROM kronos_sources AS s
					WHERE e.year = year_int AND e.day = day_int AND e.source = s.source AND s.eph = ext_str;
				IF NOT FOUND THEN
					-- If update failed (no corresponding ephemeris row yet)
					UPDATE kronos_ephem AS e SET eph = ts, source = s1.source
						-- Try to group with a partial ephemeris (that has an att but no eph)
						FROM kronos_sources AS s1, kronos_sources AS s2
						WHERE e.year = year_int AND e.day = day_int AND e.eph IS NULL AND e.source = s2.source AND s1.eph = ext_str AND s2.att = s1.att;
					IF NOT FOUND THEN
						-- If no such ephemeris exists
						INSERT INTO kronos_ephem (year, day, source, att, eph)
							-- Try to insert a row such that corresponding att is already in database
							SELECT year_int, day_int, s2.source, e.att, ts
							FROM kronos_sources AS s1, kronos_sources AS s2, kronos_ephem AS e
							WHERE year_int = e.year AND day_int = e.day AND e.source = s1.source AND s1.att = s2.att AND s2.eph = ext_str;
						IF NOT FOUND THEN
							INSERT INTO kronos_ephem (year, day, source, eph)
								-- Insert a row with any corresponding source
								SELECT year_int, day_int, s.source, ts
								FROM kronos_sources AS s
								WHERE s.eph = ext_str
								LIMIT 1;
						END IF;
					END IF;
				END IF;		
			END IF;
			-- This should never happen unless there is a serious problem in the database
			IF NOT FOUND THEN
				RAISE NOTICE 'Tried hard to upsert ephem %/% % to no avail.', year_int, day_int, ext_str;
			END IF;
			RETURN FOUND;
		ELSE
			-- In case the upsert of period failed
			RAISE NOTICE 'Something went wrong checking for day %/%.', year_int, day_int;
			RETURN FALSE;
		END IF;		
	END;
$body$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION upsert_2hour(year_int smallint, day_int smallint, hour_int smallint, period_str text DEFAULT NULL, colname text DEFAULT NULL, ts anyelement DEFAULT NULL::timestamp) RETURNS boolean AS $body$
	DECLARE
		worked smallint;
	BEGIN
		-- First make sure the day alreay exists
		IF upsert_day(year_int, day_int, period_str) THEN
			IF colname IS NOT NULL THEN
				-- Dynamically built query using provided column name
				EXECUTE format('UPDATE kronos_2hours SET %I = $1 WHERE year = $2 AND day = $3 AND hour = $4', colname)
					USING ts, year_int, day_int, hour_int;
			ELSE
				-- Simply checks whether a corresonding row is already present
				PERFORM 1 FROM kronos_2hours WHERE year = year_int AND day = day_int AND hour = hour_int;
			END IF;
			GET DIAGNOSTICS worked = ROW_COUNT;
			IF worked <> 1 THEN
				-- The update or perform returned nothing, so try an insert
				IF colname IS NOT NULL THEN
					-- Dynamically built query using provided column name
					EXECUTE format('INSERT INTO kronos_2hours (year, day, hour, %I) VALUES ($1, $2, $3, $4)', colname)
						USING year_int, day_int, hour_int, ts;
				ELSE
					-- Just insert a blank row
					INSERT INTO kronos_2hours (year, day, hour) VALUES (year_int, day_int, hour_int);
				END IF;
			END IF;
			GET DIAGNOSTICS worked = ROW_COUNT;
			-- This should never happen unless there is a serious problem in the database
			IF worked <> 1 THEN
				RAISE NOTICE 'Tried hard to upsert 2hour %/%.% to no avail.', year_int, day_int, hour_int;
			END IF;
			RETURN (worked = 1);
		ELSE
			-- In case the upsert of day failed
			RAISE NOTICE 'Something went wrong checking for day %/%.', year_int, day_int;
			RETURN FALSE;
		END IF;		
	END;
$body$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION upsert_hour(year_int smallint, day_int smallint, hour_int smallint, period_str text DEFAULT NULL, colname text DEFAULT NULL, ts anyelement DEFAULT NULL::timestamp) RETURNS boolean AS $body$
	DECLARE
		worked smallint;
	BEGIN
		-- First make sure the day alreay exists
		IF upsert_day(year_int, day_int, period_str) THEN
			IF colname IS NOT NULL THEN
				-- Dynamically built query using provided column name
				EXECUTE format('UPDATE kronos_hours SET %I = $1 WHERE year = $2 AND day = $3 AND hour = $4', colname)
					USING ts, year_int, day_int, hour_int;
			ELSE
				-- Simply checks whether a corresonding row is already present
				PERFORM 1 FROM kronos_hours WHERE year = year_int AND day = day_int AND hour = hour_int;
			END IF;
			GET DIAGNOSTICS worked = ROW_COUNT;
			IF worked <> 1 THEN
				-- The update or perform returned nothing, so try an insert
				IF colname IS NOT NULL THEN
					-- Dynamically built query using provided column name
					EXECUTE format('INSERT INTO kronos_hours (year, day, hour, %I) VALUES ($1, $2, $3, $4)', colname)
						USING year_int, day_int, hour_int, ts;
				ELSE
					-- Just insert a blank row
					INSERT INTO kronos_hours (year, day, hour) VALUES (year_int, day_int, hour_int);
				END IF;
			END IF;
			GET DIAGNOSTICS worked = ROW_COUNT;
			-- This should never happen unless there is a serious problem in the database
			IF worked <> 1 THEN
				RAISE NOTICE 'Tried hard to upsert hour %/%.% to no avail.', year_int, day_int, hour_int;
			END IF;
			RETURN (worked = 1);
		ELSE
			-- In case the upsert of day failed
			RAISE NOTICE 'Something went wrong checking for day %/%.', year_int, day_int;
			RETURN FALSE;
		END IF;		
	END;
$body$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION upsert_loc(year_int smallint, day_int smallint, hour_int smallint, mfl_str text, period_str text DEFAULT NULL, colname text DEFAULT NULL, ts timestamp DEFAULT NULL) RETURNS boolean AS $body$
	DECLARE
		worked smallint;
	BEGIN
		-- First make sure the hour alreay exists
		IF upsert_hour(year_int, day_int, hour_int, period_str) THEN
			IF colname IS NOT NULL THEN
				-- Dynamically built query using provided column name
				EXECUTE format('UPDATE kronos_loc SET %I = $1 WHERE year = $2 AND day = $3 AND hour = $4 AND mfl = $5', colname)
					USING ts, year_int, day_int, hour_int, mfl_str;
			ELSE
				-- Simply checks whether a corresonding row is already present
				PERFORM 1 FROM kronos_loc WHERE year = year_int AND day = day_int AND hour = hour_int AND mfl = mfl_str;
			END IF;
			GET DIAGNOSTICS worked = ROW_COUNT;
			IF worked <> 1 THEN
				-- The update or perform returned nothing, so try an insert
				IF colname IS NOT NULL THEN
					-- Dynamically built query using provided column name
					EXECUTE format('INSERT INTO kronos_loc (year, day, hour, mfl, %I) VALUES ($1, $2, $3, $4, $5)', colname)
						USING year_int, day_int, hour_int, mfl_str, ts;
				ELSE
					-- Just insert a blank row
					INSERT INTO kronos_loc (year, day, hour, mfl) VALUES (year_int, day_int, hour_int, mfl_str);
				END IF;
			END IF;
			GET DIAGNOSTICS worked = ROW_COUNT;
			-- This should never happen unless there is a serious problem in the database
			IF worked <> 1 THEN
				RAISE NOTICE 'Tried hard to upsert loc %/%.% (%) to no avail.', year_int, day_int, hour_int, mfl_str;
			END IF;
			RETURN (worked = 1);
		ELSE
			-- In case the upsert of hour failed
			RAISE NOTICE 'Something went wrong checking for hour %/%.% .', year_int, day_int, hour_int;
			RETURN FALSE;
		END IF;		
	END;
$body$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION upsert_loc_err(year_int smallint, day_int smallint, hour_int smallint, mfl_str text, nerr_int smallint, period_str text DEFAULT NULL, colname text DEFAULT NULL, ts timestamp DEFAULT NULL) RETURNS boolean AS $body$
	DECLARE
		worked smallint;
	BEGIN
		-- First make sure the corresponding loc alreay exists
		IF upsert_loc(year_int, day_int, hour_int, mfl_str, period_str) THEN
			IF colname IS NOT NULL THEN
				-- Dynamically built query using provided column name
				EXECUTE format('UPDATE kronos_loc_err SET %I = $1 WHERE year = $2 AND day = $3 AND hour = $4 AND mfl = $5 AND n_error = $6', colname)
					USING ts, year_int, day_int, hour_int, mfl_str, nerr_int;
			ELSE
				-- Simply checks whether a corresonding row is already present
				PERFORM 1 FROM kronos_loc_err WHERE year = year_int AND day = day_int AND hour = hour_int AND mfl = mfl_str AND n_error = nerr_int;
			END IF;
			GET DIAGNOSTICS worked = ROW_COUNT;
			IF worked <> 1 THEN
				-- The update or perform returned nothing, so try an insert
				IF colname IS NOT NULL THEN
					-- Dynamically built query using provided column name
					EXECUTE format('INSERT INTO kronos_loc_err (year, day, hour, mfl, n_error, %I) VALUES ($1, $2, $3, $4, $5, $6)', colname)
						USING year_int, day_int, hour_int, mfl_str, nerr_int, ts;
				ELSE
					-- Just insert a blank row
					INSERT INTO kronos_loc_err (year, day, hour, mfl, n_error) VALUES (year_int, day_int, hour_int, mfl_str, nerr_int);
				END IF;
			END IF;
			GET DIAGNOSTICS worked = ROW_COUNT;
			IF worked <> 1 THEN
				-- This should never happen unless there is a serious problem in the database
				RAISE NOTICE 'Tried hard to upsert loc_err %/%.% (%,%) to no avail.', year_int, day_int, hour_int, mfl_str, nerr_int;
			END IF;
			RETURN (worked = 1);
		ELSE
			-- In case the upsert of loc failed
			RAISE NOTICE 'Something went wrong checking for loc %/%.% (%) .', year_int, day_int, hour_int, mfl_str;
			RETURN FALSE;
		END IF;		
	END;
$body$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION upsert_n3(year_int smallint, day_int smallint, hour_int smallint, source_str char(2), ant_chr char(1), period_str text DEFAULT NULL, colname text DEFAULT NULL, ts anyelement DEFAULT NULL::timestamp) RETURNS boolean AS $body$
	DECLARE
		worked smallint;
	BEGIN
		-- First make sure the corresponding hour and ephem alreay exist
		IF upsert_hour(year_int, day_int, hour_int, period_str) AND upsert_ephem(year_int, day_int, period_str, source_str) THEN
			IF colname IS NOT NULL THEN
				-- Dynamically built query using provided column name
				EXECUTE format('UPDATE kronos_n3 SET %I = $1 WHERE year = $2 AND day = $3 AND hour = $4 AND source = $5 AND ant = $6', colname)
					USING ts, year_int, day_int, hour_int, source_str, ant_chr;
			ELSE
				-- Simply checks whether a corresonding row is already present
				PERFORM 1 FROM kronos_n3 WHERE year = year_int AND day = day_int AND hour = hour_int AND source = source_str AND ant = ant_chr;
			END IF;
			GET DIAGNOSTICS worked = ROW_COUNT;
			IF worked <> 1 THEN
				-- The update or perform returned nothing, so try an insert
				IF colname IS NOT NULL THEN
					-- Dynamically built query using provided column name
					EXECUTE format('INSERT INTO kronos_n3 (year, day, hour, source, ant, %I) VALUES ($1, $2, $3, $4, $5, $6)', colname)
						USING year_int, day_int, hour_int, source_str, ant_chr, ts;
				ELSE
					-- Just insert a blank row
					INSERT INTO kronos_n3 (year, day, hour, source, ant) VALUES (year_int, day_int, hour_int, source_str, ant_chr);
				END IF;
			END IF;
			GET DIAGNOSTICS worked = ROW_COUNT;
			IF worked <> 1 THEN
				-- This should never happen unless there is a serious problem in the database
				RAISE NOTICE 'Tried hard to upsert n3 %/%.% %% to no avail.', year_int, day_int, hour_int, ant_chr, source_str;
			END IF;
			RETURN (worked = 1);
		ELSE
			-- In case the upsert of hour or ephem failed
			RAISE NOTICE 'Something went wrong checking for hour %/%.% or ephem %/% %.', year_int, day_int, hour_int, year_int, day_int, source_str;
			RETURN FALSE;
		END IF;		
	END;
$body$ LANGUAGE plpgsql;

/*
** upsert_file
** This function takes a file name (full path) and timestamp as input, and tries to
** insert or update the corresponding data in the database and all its dependencies
** In case of a K file, the iowa directory can also be specified.
*/

CREATE OR REPLACE FUNCTION upsert_file(fullpath text, ts timestamp DEFAULT NULL, origin_dir text DEFAULT NULL) RETURNS void AS $body$
	DECLARE
		-- split the path into an array of succesive directories then file name
		-- this works even if there are repeated slashes in the input
		path_arr text[] := regexp_split_to_array(trim(both '/' FROM fullpath), '/+');
		period_str text;
		type_str text;
		filename_str text;
		fileinfo text[];
		ydh smallint[];
	BEGIN
		-- Check wheter the path contains at least a parent and grandparent directories
		IF array_length(path_arr, 1) >= 3 THEN
			-- Name the last three elements in the array
			period_str := path_arr[array_length(path_arr, 1) - 2];
			type_str := path_arr[array_length(path_arr, 1) - 1];
			filename_str := path_arr[array_length(path_arr, 1)];

			CASE type_str
				-- Choose what to do based on the subdirectory (type of file)
				WHEN 'bg' THEN
					-- Try to match the filename against the expected pattern for background files
					fileinfo := regexp_matches(filename_str, '^bg_' || period_str '(_tmp)?$');
					IF array_length(fileinfo, 1) = 1 THEN
						-- The colname is either bg or bg_tmp
						PERFORM upsert_period(period_str, 'bg' || fileinfo[1], ts);
					ELSE
						-- No match
						RAISE NOTICE 'Bad % filename %.', type_str, filename_str;
					END IF;
				WHEN 'pdf' THEN
					-- Try to match the filename against the expected pattern for pdf files
					fileinfo := regexp_matches(filename_str, '^([0-9]{4})([0-9]{3})(|-LTb|-SVb|-SVe)\.pdf$');
					IF array_length(fileinfo, 1) = 3 THEN
						-- Rename and cast the first two matches (year and day)
						ydh := fileinfo[1:2]::smallint[];
						-- Choose the colname based on the third match
						CASE fileinfo[3]
							WHEN '' THEN
								PERFORM upsert_day(ydh[1], ydh[2], period_str, 'raw', ts);
							WHEN '-SVe' THEN
								PERFORM upsert_day(ydh[1], ydh[2], period_str, 'sve', ts);
							WHEN '-SVb' THEN
								PERFORM upsert_day(ydh[1], ydh[2], period_str, 'svb', ts);
							WHEN '-LTb' THEN
								PERFORM upsert_day(ydh[1], ydh[2], period_str, 'ltb', ts);
							ELSE -- Should never happen really...
								RAISE NOTICE 'Unknown pdf file suffix %.', fileinfo[3];
						END CASE;
					ELSE
						-- No match
						RAISE NOTICE 'Bad % filename %.', type_str, filename_str;
					END IF;
				WHEN 'lis', 'skr' THEN
					-- Try to match the filename against the expected pattern for lis and skr files, then cast matches (year, day) to int
					ydh := regexp_matches(filename_str, '^([0-9]{4})([0-9]{3})\.' || type_str || '$')::smallint[];
					IF array_length(ydh, 1) = 2 THEN
						-- The colname is the same as the type string (skr or lis)
						PERFORM upsert_day(ydh[1], ydh[2], period_str, type_str, ts);
					ELSE
						RAISE NOTICE 'Bad % filename %.', type_str, filename_str;
					END IF;						

				WHEN 'ephem' THEN
					-- Try to match the filename to the expected pattern for ephemeris files
					fileinfo := regexp_matches(filename_str, '^([0-9]{4})([0-9]{3})\.((?:q|v).{3})$');
					IF array_length(fileinfo, 1) = 3 THEN
						-- Rename and cast the first two matches (year and day)
						ydh := fileinfo[1:2]::smallint[];
						-- The source is not known yet, so not provided, and the 4th parameter is the extension (qxx or vyy)
						PERFORM upsert_ephem(ydh[1], ydh[2], period_str, NULL, fileinfo[3], ts);
					ELSE
						-- No match
						RAISE NOTICE 'Bad % filename %.', type_str, filename_str;
					END IF;

				WHEN 'img' THEN
					-- Try to match the filename to the expected pattern for image files
					fileinfo := regexp_matches(filename_str, '^([0-9]{4})([0-9]{3})(|-LTb|-SVb|-SVe)-(?:([12])-([0-9]{2})\.png|small\.(png|jpg))$');
					IF array_length(fileinfo, 1) = 6 THEN
						IF fileinfo[6] IS NULL THEN
							CASE fileinfo[3]
								-- The image is a 2hour plot
								WHEN '' THEN
									-- Raw plot, up or down
									PERFORM upsert_2hour(fileinfo[1]::smallint, fileinfo[2]::smallint, fileinfo[5]::smallint, period_str, 'img_raw' || fileinfo[4], ts);
								WHEN '-SVe' THEN
									-- 2 antenna S or V plot
									PERFORM upsert_2hour(fileinfo[1]::smallint, fileinfo[2]::smallint, fileinfo[5]::smallint, period_str, 'img_sve' || fileinfo[4], ts);
								WHEN '-SVb' THEN
									-- 3 antenna S or V plot
									PERFORM upsert_2hour(fileinfo[1]::smallint, fileinfo[2]::smallint, fileinfo[5]::smallint, period_str, 'img_svb' || fileinfo[4], ts);
								WHEN '-LTb' THEN
									-- 3 antenna L or T plot
									PERFORM upsert_2hour(fileinfo[1]::smallint, fileinfo[2]::smallint, fileinfo[5]::smallint, period_str, 'img_ltb' || fileinfo[4], ts);
								ELSE
									-- This case should be unnecessary given the regex
									RAISE NOTICE 'Unknown pdf file suffix %.', fileinfo[3];
							END CASE;
						ELSE
							-- The image is a quickview (ends in small.png or jpg)
							RAISE NOTICE 'Not handling quickviews yet.';
						END IF;
					ELSE
						-- No match
						RAISE NOTICE 'Bad % filename %.', type_str, filename_str;
					END IF;

				WHEN 'k', 'k_bad' THEN
					-- Try to match the filename to the expected pattern for K files, then cast matches (year, day, hour) to int
					ydh := regexp_matches(filename_str, '^K([0-9]{4})([0-9]{3})\.([0-9]{2})$')::smallint[];
					IF array_length(ydh, 1) = 3 THEN
						-- First insert/update a line with just the timestamp
						PERFORM upsert_hour(ydh[1], ydh[2], ydh[3], period_str, 'k', ts);
						-- Then update the 'bad' column according to the subdirectory
						PERFORM upsert_hour(ydh[1], ydh[2], ydh[3], period_str, 'bad', type_str = 'k_bad');
						-- In case Iowa dir was provided
						IF origin_dir IS NOT NULL THEN
							-- Update the 'src_dir' column
							PERFORM upsert_hour(ydh[1], ydh[2], ydh[3], period_str, 'src_dir', origin_dir);
						END IF;
					ELSE
						-- No match
						RAISE NOTICE 'Bad % filename %.', type_str, filename_str;
					END IF;
				WHEN 'n1' THEN
					-- Try to match the filename to the expected pattern for N1 files, then cast matches (year, day, hour) to int
					ydh := regexp_matches(filename_str, '^R([0-9]{4})([0-9]{3})\.([0-9]{2})$')::smallint[];
					IF array_length(ydh, 1) = 3 THEN
						-- Colname is n1
						PERFORM upsert_hour(ydh[1], ydh[2], ydh[3], period_str, 'n1', ts);
					ELSE
						-- No match
						RAISE NOTICE 'Bad % filename %.', type_str, filename_str;
					END IF;
				WHEN 'n2' THEN
					-- Try to match the filename to the expected pattern for N2 files, then cast matches (year, day, hour) to int
					ydh := regexp_matches(filename_str, '^P([0-9]{4})([0-9]{3})\.([0-9]{2})$')::smallint[];
					IF array_length(ydh, 1) = 3 THEN
						-- Colname is n2
						PERFORM upsert_hour(ydh[1], ydh[2], ydh[3], period_str, 'n2', ts);
					ELSE
						-- No match
						RAISE NOTICE 'Bad % filename %.', type_str, filename_str;
					END IF;
				WHEN 'n3g' THEN
					-- Try to match the filename to the expected pattern for N3g files, then cast matches (year, day, hour) to int
					ydh := regexp_matches(filename_str, '^F([0-9]{4})([0-9]{3})\.([0-9]{2})$')::smallint[];
					IF array_length(ydh, 1) = 3 THEN
						-- Colname is n3g
						PERFORM upsert_hour(ydh[1], ydh[2], ydh[3], period_str, 'n3g', ts);
					ELSE
						-- No match
						RAISE NOTICE 'Bad % filename %.', type_str, filename_str;
					END IF;
				WHEN 'index' THEN
					-- Try to match the filename to the expected pattern for index files
					fileinfo := regexp_matches(filename_str, '^INDEX_(|3A_)([0-9]{4})([0-9]{3})\.([0-9]{2})$');
					IF array_length(fileinfo, 1) = 4 THEN
						-- Rename and cast matches 2, 3 and 4 (year and day)
						ydh := fileinfo[2:4]::smallint[];
						-- If file starts with 'INDEX_3A_'
						IF fileinfo[1] = '3A_' THEN
							-- Colname is 'index_3a'
							PERFORM upsert_hour(ydh[1], ydh[2], ydh[3], period_str, 'index_3a', ts);
						ELSE
							-- Colname is 'index'
							PERFORM upsert_hour(ydh[1], ydh[2], ydh[3], period_str, 'index', ts);
						END IF;
					ELSE
						-- No match
						RAISE NOTICE 'Bad % filename %.', type_str, filename_str;
					END IF;

				WHEN 'loc' THEN
					-- Try to match the filename to the expected pattern for loc files
					fileinfo := regexp_matches(filename_str, '^(loc(_err)?(?:_3A)?)_([^_]*)(?:_n([0-9]+))?_([0-9]{4})([0-9]{3})\.([0-9]{2})$');
					IF array_length(fileinfo, 1) = 7 THEN
						-- Rename and cast matches 4, 5, 6 and 7 (year, day, hour and number of error vectors)
						ydh := fileinfo[4:7]::smallint[];
						-- If file starts with 'loc_err'
						IF fileinfo[2] IS NULL THEN
							-- Colname is 'loc' or 'loc_3a' (the 'a' is downcased)
							PERFORM upsert_loc(ydh[2], ydh[3], ydh[4], fileinfo[3], period_str, lower(fileinfo[1]), ts);
						ELSIF ydh[1] IS NOT NULL THEN
							-- Colname is 'loc_err' or 'loc_err_3a' (the 'a' is downcased)
							PERFORM upsert_loc_err(ydh[2], ydh[3], ydh[4], fileinfo[3], ydh[1], period_str, lower(fileinfo[1]), ts);
						ELSE
							-- Probably useless
							RAISE NOTICE 'Bad % filename %.', type_str, filename_str;
						END IF;
					ELSE
						-- No match
						RAISE NOTICE 'Bad % filename %.', type_str, filename_str;
					END IF;

				WHEN 'n3a', 'n3b', 'n3c', 'n3d', 'n3e', 'n3f' THEN
					-- Try to match the filename to the expected pattern for N3a,b,c,d,e, or f files.
					-- File prefix should be the same as subdirectory with Capital letter on first character
					fileinfo := regexp_matches(filename_str, '^' || initcap(type_str) || '(\-full)?_(.)(..)([0-9]{4})([0-9]{3})\.([0-9]{2})$');
					IF array_length(fileinfo, 1) = 6 THEN
						-- Rename and cast matches 4, 5 and 6 (year, day, and hour)
						ydh := fileinfo[4:6]::smallint[];
						-- Append 'full' to the subdirectory name in case '-full' is found in filename
						IF fileinfo[1] = '-full' THEN
							type_str := type_str || 'full';
						END IF;
						-- Colname is subdirectory name
						PERFORM upsert_n3(ydh[1], ydh[2], ydh[3], fileinfo[3], fileinfo[2], period_str, type_str, ts);
					ELSE
						-- No match
						RAISE NOTICE 'Bad % filename %.', type_str, filename_str;
					END IF;

				WHEN 'n3' THEN
					-- Unknown filename template!
					RAISE NOTICE 'Not handling % files yet.', type_str;

				ELSE
					-- Any other subdirectory
					RAISE NOTICE 'Unknown subdirectory %.', type_str;
			END CASE;
		ELSE
			-- Path was not long enough
			RAISE NOTICE 'Bad filename % (needs at least 2 parent dirs).', fullpath;
		END IF;
	END;
$body$ LANGUAGE plpgsql;


/*
** Function that does the reverse (builds filename from parameters)
** Not finished, probably not necessary
*/

/*
CREATE OR REPLACE FUNCTION get_filename(year smallint, day smallint, hour smallint DEFAULT NULL, level text DEFAULT 'k', full boolean DEFAULT TRUE) RETURNS text AS $body$
	DECLARE
		filename text;
	BEGIN
		CASE level 
			-- WHEN 'bg' THEN
			WHEN 'pdf' THEN
			WHEN 'lis', 'skr' THEN
			WHEN 'ephem' THEN
			WHEN 'img' THEN
			WHEN 'k', 'k_bad' THEN
			WHEN 'n1' THEN
			WHEN 'n2' THEN
			WHEN 'n3g' THEN
			WHEN 'index' THEN
			WHEN 'loc' THEN
			WHEN 'n3a', 'n3b', 'n3c', 'n3d', 'n3e', 'n3f' THEN
			WHEN 'n3' THEN
			ELSE
				RAISE NOTICE 'Unknown level %.', level;
		END CASE;
		IF full THEN
			RAISE NOTICE 'nothing';
		END IF;
	END;
$body$ LANGUAGE plpgsql STABLE;
*/

/*
** This function updates periods with special names with the range current range of files in it
*/

CREATE OR REPLACE FUNCTION fill_periods() RETURNS void AS $body$
	WITH des AS (
			SELECT distinct ON (period) period, year, day
			FROM kronos_days
			ORDER BY period, year DESC, day DESC
		), ascc AS (
			SELECT distinct ON (period) period, year, day
			FROM kronos_days
			ORDER BY period, year, day
		)
	UPDATE kronos_periods as p
	SET start_year = ascc.year, start_day = ascc.day, end_year = des.year, end_day = des.day
	FROM des, ascc
	WHERE des.period = ascc.period and des.period = p.period AND p.start_day IS NULL;
$body$ LANGUAGE SQL;

/*
** Converts year/day/hour to timestamp
*/

CREATE OR REPLACE FUNCTION get_timestamp(smallint, smallint, smallint DEFAULT 0) RETURNS timestamp AS $body$
	SELECT ('01/01/' || $1::text)::timestamp + (($2 - 1)::text || ' days')::interval + ($3::text || ' hours')::interval;
$body$ LANGUAGE SQL IMMUTABLE;

/*
** Converts timestamp to year/day/hour
*/

CREATE OR REPLACE FUNCTION get_ydh(timestamp, OUT year smallint, OUT day smallint, OUT hour smallint) AS $body$
	SELECT EXTRACT(YEAR FROM $1)::smallint, EXTRACT(DOY FROM $1)::smallint, EXTRACT(HOUR FROM $1)::smallint;
$body$ LANGUAGE SQL IMMUTABLE;

/*
** Latest recorded date
*/

CREATE VIEW START_DATE AS SELECT get_timestamp(start_year, start_day) FROM kronos_periods ORDER BY start_year, start_day LIMIT 1;

/*
** Earliest recorded date
*/

CREATE VIEW END_DATE AS SELECT get_timestamp(end_year, end_day) FROM kronos_periods WHERE end_year IS NOT NULL AND end_day IS NOT NULL ORDER BY end_year DESC, end_day DESC LIMIT 1;

/*
** This function adds blank rows in the kronos_horus and kronos_days tables for intermediate dates with no file.
** Should be run after upserts to ensure continuity.
** Continuity is useful for displaying the status of the database as used in timeline.js and timeline.php
*/

CREATE OR REPLACE FUNCTION fill_holes() RETURNS SETOF boolean AS $body$
	SELECT upsert_hour((get_ydh(ser)).year,(get_ydh(ser)).day, (get_ydh(ser)).hour) FROM generate_series('01/01/2000', (SELECT * FROM end_date) + interval '1 day' - interval '1 second', '1 hour') AS ser
$body$ LANGUAGE SQL;

/*
-- Prints code to drop all tables
SELECT 'DROP TABLE IF EXISTS "' || tablename || '" CASCADE;' 
	FROM pg_tables
	WHERE schemaname = 'public';

-- Prints code to drop all functions
SELECT 'DROP FUNCTION ' || ns.nspname || '.' || proname || '(' || oidvectortypes(proargtypes) || ');'
	FROM pg_proc INNER JOIN pg_namespace ns ON (pg_proc.pronamespace = ns.oid)
	WHERE ns.nspname = 'public' ORDER BY proname;

-- Prints code to drop all views
SELECT 'DROP VIEW ' || t.oid::regclass || ';' -- CASCADE?
	FROM pg_class t
	JOIN pg_namespace n ON n.oid = t.relnamespace
	WHERE t.relkind = 'v' AND n.nspname = 'public'
ORDER BY 1;
*/