CREATE TABLE main.profile(
	profile_id INTEGER PRIMARY KEY,
	machine_name TEXT NOT NULL, -- Check which machine this is on and load those app settings
	default_new_content_folder_name TEXT,
	profile_name TEXT NOT NULL UNIQUE
);

CREATE TABLE main.folder_root(
	root_id INTEGER PRIMARY KEY,
	name TEXT NOT NULL, -- IE Anime, Movies, etc.
	path TEXT NOT NULL,
	pattern_id INTEGER DEFAULT -1,
	profile_id INTEGER NOT NULL
	/*
	FOREIGN KEY (profile_id)
		REFERENCES profile (profile_id)
	*/
);

CREATE TABLE main.file(
	file_id INTEGER PRIMARY KEY,
	full_path TEXT NOT NULL,
	name TEXT NOT NULL,
	size INTEGER NOT NULL,
	date_created TEXT NOT NULL,
	date_modified TEXT NOT NULL,
	folder_root_id INTEGER NOT NULL,
	profile_id INTEGER NOT NULL
	/*
	FOREIGN KEY (folder_root_id)
		REFERENCES folder_root (root_id),
	FOREIGN KEY (profile_id)
		REFERENCES profile (profile_id)
	*/
);


CREATE TABLE main.naming_pattern(
	pattern_id INTEGER PRIMARY KEY,
	regex TEXT NOT NULL,
	name TEXT NOT NULL UNIQUE,
	example_text TEXT NOT NULL
);

INSERT INTO main.naming_pattern (name, example_text, regex) VALUES ('TV Shows', 'Show Name - SXXEXX - Episode Name', '^\b(?<show>.+?(?=\b - \b{1}S{1}\d{2}))\b\b - \b{1}S{1}(?<season>\d{2})E{1}(?<ep_num>\d{2})\b - \b{1}\b(?<ep_name>.*)');
INSERT INTO main.naming_pattern (name, example_text, regex) VALUES ('Movies', 'Movie Name (YEAR)', '^\b(?<movie>.+?(?=\(\d{4}\)))(?<year>\(\d{4}\)$)');

INSERT INTO main.profile (machine_name, default_new_content_folder_name, profile_name) VALUES ("DESKTOP-DOP1TG3", "c:\WUT", "The Choosen One");
INSERT INTO main.profile (machine_name, default_new_content_folder_name, profile_name) VALUES ("RYZEN-PC", "c:\WTWO", "ペペトランプ");

INSERT INTO main.folder_root (name, path, pattern_id, profile_id) VALUES ("Anime", "X:\Anime", 1, 1);
INSERT INTO main.folder_root (name, path, pattern_id, profile_id) VALUES ("Movies", "X:\Movies", 2, 1);
INSERT INTO main.folder_root (name, path, pattern_id, profile_id) VALUES ("TV Shows", "X:\TV Shows", 1, 1);

INSERT INTO main.folder_root (name, path, pattern_id, profile_id) VALUES ("Anime", "Y:\Anime", 1, 2);
INSERT INTO main.folder_root (name, path, pattern_id, profile_id) VALUES ("Movies", "Y:\Movies", 2, 2);