BEGIN TRANSACTION;

CREATE TABLE IF NOT EXISTS "category_belongs_to" (
	"category_id"	INTEGER NOT NULL,
	"path"	TEXT NOT NULL,
	PRIMARY KEY("category_id"),
	FOREIGN KEY("category_id") REFERENCES "category"
		ON DELETE CASCADE
		ON UPDATE RESTRICT
);

CREATE TABLE IF NOT EXISTS "entry" (
	"id"	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	"activity_id"	INTEGER NOT NULL,
	"start"	INTEGER NOT NULL,
	"stop"	INTEGER CHECK(stop >= start),
	FOREIGN KEY("activity_id") REFERENCES "activity"
		ON DELETE RESTRICT
		ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS "activity_belongs_to" (
	"activity_id"	INTEGER NOT NULL,
	"category_id"	INTEGER NOT NULL,
	FOREIGN KEY("category_id") REFERENCES "category"
		ON DELETE RESTRICT
		ON UPDATE CASCADE,
	FOREIGN KEY("activity_id") REFERENCES "activity"
		ON DELETE CASCADE
		ON UPDATE CASCADE,
	PRIMARY KEY("activity_id","category_id")
);

CREATE TABLE IF NOT EXISTS "category" (
	"id"	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	"name"	TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS "activity" (
	"id"	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	"name"	TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS "time_zone" (
	"id"	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	"name"	TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS "time_zone_encounter" (
	"timestamp"	INTEGER NOT NULL PRIMARY KEY,
	"time_zone_id"	INTEGER NOT NULL,
	FOREIGN KEY("time_zone_id") REFERENCES "time_zone"
		ON DELETE RESTRICT
		ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS "streak_definition" (
	"id"	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	"interval"	INTEGER NOT NULL CHECK(interval > 0),
	"start"	INTEGER NOT NULL,
	"stop"	INTEGER CHECK(stop >= start),
	"added"	INTEGER NOT NULL,
	"removed"	INTEGER CHECK(removed >= added)
);

CREATE TABLE IF NOT EXISTS "activity_streak_rule" (
	"streak_definition_id"	INTEGER NOT NULL PRIMARY KEY,
	"activity_id"	INTEGER NOT NULL,
	"min_duration"	INTEGER NOT NULL,
	FOREIGN KEY("streak_definition_id") REFERENCES "streak_definition"
		ON DELETE RESTRICT
		ON UPDATE CASCADE,
	FOREIGN KEY("activity_id") REFERENCES "activity"
		ON DELETE RESTRICT
		ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS "category_streak_rule" (
	"streak_definition_id"	INTEGER NOT NULL PRIMARY KEY,
	"category_id"	INTEGER NOT NULL,
	"min_duration"	INTEGER NOT NULL,
	FOREIGN KEY("streak_definition_id") REFERENCES "streak_definition"
		ON DELETE RESTRICT
		ON UPDATE CASCADE,
	FOREIGN KEY("category_id") REFERENCES "category"
		ON DELETE RESTRICT
		ON UPDATE CASCADE
);

CREATE VIEW streak AS
	SELECT 
		streak_definition.id,
		streak_definition.interval,
		streak_definition.start,
		streak_definition.stop,
		streak_definition.added,
		streak_definition.removed,
		'activity' AS rule_type,
		activity_streak_rule.activity_id,
		activity_streak_rule.min_duration AS min_duration,
		NULL AS category_id
	FROM activity_streak_rule
	JOIN streak_definition
		ON activity_streak_rule.streak_definition_id
		== streak_definition.id

	UNION SELECT
		streak_definition.id,
		streak_definition.interval,
		streak_definition.start,
		streak_definition.stop,
		streak_definition.added,
		streak_definition.removed,
		'category' AS rule_type,
		NULL AS activity_id,
		category_streak_rule.min_duration AS min_duration,
		category_id
	FROM category_streak_rule
	JOIN streak_definition
		ON category_streak_rule.streak_definition_id
		== streak_definition.id;

CREATE TRIGGER category_belongs_to_path_integrity
	BEFORE DELETE ON category
	BEGIN
		SELECT RAISE(FAIL, "category contains other categories")
		FROM category_belongs_to
		WHERE category_belongs_to.path LIKE '%/' || OLD.id || '/%';
	END;

CREATE TRIGGER no_double_start_on_insert
	BEFORE INSERT ON entry
	WHEN NEW.stop IS NULL
	BEGIN
		SELECT RAISE(FAIL, "activity has been started already")
		FROM entry
		WHERE stop IS NULL AND activity_id == NEW.activity_id;
	END;

CREATE TRIGGER no_double_start_on_update
	BEFORE UPDATE ON entry
	WHEN NEW.stop IS NULL
	BEGIN
		SELECT RAISE(FAIL, "activity has been started already")
		FROM entry
		WHERE stop IS NULL AND activity_id == NEW.activity_id
		AND id != NEW.id;
	END;

--	Overlap: (^new^ |old|)
--		| ^ ^ |	inside
--		^ | ^ |	front
--		| ^ | ^	back
--		^ | | ^	around
--
--	"touching" is allowed: 
--		BETWEEN operator is inclusive, so "AND NEW.start != stop" and
--		vice versa were added to allow for it
--		inside overlap does not need a special condition 

CREATE TRIGGER overlapping_entry_insert_constraint
	BEFORE INSERT ON entry
	BEGIN
		SELECT RAISE(FAIL, "entry overlaps another entry of this activity")
		FROM entry
		WHERE activity_id == NEW.activity_id
		AND ((start > NEW.start AND NEW.stop > stop)	-- around overlap
			OR (NEW.stop BETWEEN start AND stop			-- front overlap
				AND NEW.stop != start)
			OR (NEW.start BETWEEN start AND stop		-- back overlap
				AND NEW.start != stop)
		);
	END;

CREATE TRIGGER overlapping_entry_update_constraint
	BEFORE UPDATE ON entry
	BEGIN
		SELECT RAISE(FAIL, "entry overlaps another entry of this activity")
		FROM entry
		WHERE activity_id == NEW.activity_id
		AND id != NEW.id
		AND ((start > NEW.start AND NEW.stop > stop)	-- around overlap
			OR (NEW.stop BETWEEN start AND stop			-- front overlap
				AND NEW.stop != start)
			OR (NEW.start BETWEEN start AND stop		-- back overlap
				AND NEW.start != stop)
		);
	END;

CREATE TRIGGER valid_activity_streak_rule_insert
	BEFORE INSERT ON activity_streak_rule
	BEGIN
		SELECT RAISE(FAIL, "streak_definition already has a streak_rule")
		FROM streak
		WHERE NEW.streak_definition_id = streak.id;

		SELECT RAISE(FAIL, "min_duration larger than interval")
		FROM streak_definition
		WHERE NEW.streak_definition_id == streak_definition.id
		AND NEW.min_duration > streak_definition.interval;

		SELECT
			(SELECT start
				FROM streak_definition
				WHERE id = NEW.streak_definition_id
			) AS new_start,
			(SELECT stop
				FROM streak_definition
				WHERE id = NEW.streak_definition_id
			) AS new_stop,
			RAISE(FAIL, "activity has an active streak_definition already")
		FROM streak
		WHERE streak.activity_id = NEW.activity_id
		AND (new_stop IS NULL AND stop IS NULL);

		SELECT
			(SELECT start
				FROM streak_definition
				WHERE id = NEW.streak_definition_id
			) AS new_start,
			(SELECT stop
				FROM streak_definition
				WHERE id = NEW.streak_definition_id
			) AS new_stop,
			RAISE(FAIL, "overlapping streak_definitions")
		FROM streak
		WHERE streak.activity_id = NEW.activity_id
		AND ((start > new_start AND new_stop > stop)	-- around overlap
			OR (new_stop BETWEEN start AND stop			-- front overlap
				AND new_stop != start)
			OR (new_start BETWEEN start AND stop		-- back overlap
				AND new_start != stop)
		);
	END;


CREATE TRIGGER valid_category_streak_rule_insert
	BEFORE INSERT ON category_streak_rule
	BEGIN
		SELECT RAISE(FAIL, "streak_definition already has a streak_rule")
		FROM streak
		WHERE NEW.streak_definition_id = streak.id;

		SELECT RAISE(FAIL, "min_duration larger than interval")
		FROM streak_definition
		WHERE NEW.streak_definition_id == streak_definition.id
		AND NEW.min_duration > streak_definition.interval;

		SELECT
			(SELECT start
				FROM streak_definition
				WHERE id = NEW.streak_definition_id
			) AS new_start,
			(SELECT stop
				FROM streak_definition
				WHERE id = NEW.streak_definition_id
			) AS new_stop,
			RAISE(FAIL, "category has an active streak_definition already")
		FROM streak
		WHERE streak.category_id = NEW.category_id
		AND (new_stop IS NULL AND stop IS NULL);

		SELECT
			(SELECT start
				FROM streak_definition
				WHERE id = NEW.streak_definition_id
			) AS new_start,
			(SELECT stop
				FROM streak_definition
				WHERE id = NEW.streak_definition_id
			) AS new_stop,
			RAISE(FAIL, "overlapping streak_definitions")
		FROM streak
		WHERE streak.category_id = NEW.category_id
		AND ((start > new_start AND new_stop > stop)	-- around overlap
			OR (new_stop BETWEEN start AND stop			-- front overlap
				AND new_stop != start)
			OR (new_start BETWEEN start AND stop		-- back overlap
				AND new_start != stop)
		);
	END;

CREATE TRIGGER valid_activity_streak_rule_update
	BEFORE UPDATE ON activity_streak_rule
	BEGIN
		SELECT RAISE(FAIL, "streak_definition already has a streak_rule")
		FROM streak
		WHERE NEW.streak_definition_id = streak.id
		AND NEW.streak_definition_id != OLD.streak_definition_id;

		SELECT RAISE(FAIL, "min_duration larger than interval")
		FROM streak_definition
		WHERE NEW.streak_definition_id = streak_definition.id
		AND NEW.min_duration > streak_definition.interval;

		SELECT
			(SELECT stop
				FROM streak_definition
				WHERE id = NEW.streak_definition_id
			) AS new_stop,
			RAISE(FAIL, "activity has an active streak_definition already")
		FROM streak
		WHERE streak.activity_id == NEW.activity_id
		AND (new_stop IS NULL AND stop IS NULL)
		AND NEW.streak_definition_id != streak.id;

		SELECT
			(SELECT start
				FROM streak_definition
				WHERE id == NEW.streak_definition_id
			) AS new_start,
			(SELECT stop
				FROM streak_definition
				WHERE id == NEW.streak_definition_id
			) AS new_stop,
			RAISE(FAIL, "overlapping streak_definitions")
		FROM streak
		WHERE streak.activity_id == NEW.activity_id
		AND ((start > new_start AND new_stop > stop)	-- around overlap
			OR (new_stop BETWEEN start AND stop			-- front overlap
				AND new_stop != start)
			OR (new_start BETWEEN start AND stop		-- back overlap
				AND new_start != stop)
		)
		AND NEW.streak_definition_id != streak.id;
	END;

CREATE TRIGGER valid_category_streak_rule_update
	BEFORE UPDATE ON category_streak_rule
	BEGIN
		SELECT RAISE(FAIL, "streak_definition already has a streak_rule")
		FROM streak
		WHERE NEW.streak_definition_id = streak.id
		AND NEW.streak_definition_id != OLD.streak_definition_id;

		SELECT RAISE(FAIL, "min_duration larger than interval")
		FROM streak_definition
		WHERE NEW.streak_definition_id = streak_definition.id
		AND NEW.min_duration > streak_definition.interval;

		SELECT
			(SELECT stop
				FROM streak_definition
				WHERE id = NEW.streak_definition_id
			) AS new_stop,
			RAISE(FAIL, "category has an active streak_definition already")
		FROM streak
		WHERE streak.category_id = NEW.category_id
		AND (new_stop IS NULL AND stop IS NULL)
		AND NEW.streak_definition_id != streak.id;

		SELECT
			(SELECT start
				FROM streak_definition
				WHERE id == NEW.streak_definition_id
			) AS new_start,
			(SELECT stop
				FROM streak_definition
				WHERE id == NEW.streak_definition_id
			) AS new_stop,
			RAISE(FAIL, "overlapping streak_definitions")
		FROM streak
		WHERE streak.category_id == NEW.category_id
		AND ((start > new_start AND new_stop > stop)	-- around overlap
			OR (new_stop BETWEEN start AND stop			-- front overlap
				AND new_stop != start)
			OR (new_start BETWEEN start AND stop		-- back overlap
				AND new_start != stop)
		)
		AND NEW.streak_definition_id != streak.id;
	END;

CREATE TRIGGER valid_streak_definition_update
	BEFORE UPDATE ON streak_definition
	BEGIN
		SELECT RAISE(FAIL, "min_duration larger than interval")
		FROM streak
		WHERE NEW.id = streak.id
		AND streak.min_duration > NEW.interval;

		SELECT
			(SELECT activity_id
				FROM streak
				WHERE id = NEW.id
			) AS updated_activity_id,
			RAISE(FAIL, "activity has an active streak_definition already")
		FROM streak
		WHERE streak.activity_id = updated_activity_id
		AND stop IS NULL
		AND NEW.stop IS NULL
		AND NEW.id != streak.id;

		SELECT
			(SELECT category_id
				FROM streak
				WHERE id = NEW.id
			) AS updated_category_id,
			RAISE(FAIL, "category has an active streak_definition already")
		FROM streak
		WHERE streak.category_id = updated_category_id
		AND stop IS NULL
		AND NEW.stop IS NULL
		AND NEW.id != streak.id;

		SELECT
			(SELECT activity_id
				FROM streak
				WHERE id = NEW.id
			) AS updated_activity_id,
			RAISE(FAIL, "overlapping streak_definitions")
		FROM streak
		WHERE streak.activity_id = updated_activity_id
		AND ((start > NEW.start AND NEW.stop > stop)	-- around overlap
			OR (NEW.stop BETWEEN start AND stop			-- front overlap
				AND NEW.stop != start)
			OR (NEW.start BETWEEN start AND stop		-- back overlap
				AND NEW.start != stop)
		)
		AND NEW.id != streak.id;
		
		SELECT
			(SELECT category_id
				FROM streak
				WHERE id = NEW.id
			) AS updated_category_id,
			RAISE(FAIL, "overlapping streak_definitions")
		FROM streak
		WHERE streak.category_id = updated_category_id
		AND ((start > NEW.start AND NEW.stop > stop)	-- around overlap
			OR (NEW.stop BETWEEN start AND stop			-- front overlap
				AND NEW.stop != start)
			OR (NEW.start BETWEEN start AND stop		-- back overlap
				AND NEW.start != stop)
		)
		AND NEW.id != streak.id;
	END;

CREATE TRIGGER only_new_time_zone_encounters
	BEFORE INSERT ON time_zone_encounter
	BEGIN
		SELECT RAISE(FAIL, "outdated time_zone_encounter")
		FROM time_zone_encounter
		WHERE timestamp >= NEW.timestamp;
	END;


CREATE VIEW entry_list AS
	SELECT 
		activity.name AS 'Activity', 
		datetime(entry.start, 'unixepoch', 'localtime') AS Start,
		datetime(entry.stop, 'unixepoch', 'localtime') AS Stop
	FROM entry
	JOIN activity ON entry.activity_id == activity.id;

CREATE TRIGGER unique_activities_in_category_insert
	BEFORE INSERT ON activity_belongs_to
	BEGIN
		SELECT
			(SELECT name FROM activity WHERE id = NEW.activity_id) AS new_name,
			RAISE(FAIL, "activity with this name already exists in category")
		FROM activity_belongs_to
		JOIN activity ON activity.id == activity_id
		WHERE name == new_name AND category_id == NEW.category_id;
	END;

CREATE TRIGGER unique_activities_in_category_update
	BEFORE UPDATE ON activity
	BEGIN
		SELECT
			(
				SELECT category_id
				FROM activity_belongs_to WHERE NEW.id = activity_id
			) AS new_category_id,
			RAISE(FAIL, "activity with this name already exists in category")
		FROM activity_belongs_to
		JOIN activity ON activity.id == activity_id
		WHERE name == NEW.name
			AND category_id == new_category_id
			AND id != NEW.id;
	END;

CREATE TRIGGER unique_categories_in_category_insert
	BEFORE INSERT ON category_belongs_to
	BEGIN
		SELECT
			(
				SELECT name
				FROM category WHERE NEW.category_id = category.id
			) AS new_category_name,
			RAISE(FAIL, "category with this name already exists in category")
		FROM category_belongs_to
		JOIN category ON category.id == category_id
		WHERE category.name == new_category_name
			AND path == NEW.path
			AND category.id != NEW.category_id;
	END;


COMMIT;