-- Alias des tables pour les requêtes SQL lors de jointures :
-- {cameras_brands} : cam_b
-- {cameras_items} : cam_i
-- {cameras_models} : cam_m
-- {categories} : cat
-- {comments} : com
-- {config}
-- {favorites} : fav
-- {groups} : g
-- {groups_permissions} : gp
-- {items} : i
-- {items_pending} : ip
-- {passwords} : p
-- {search}
-- {selection} : sel
-- {sessions} : s
-- {sessions_categories} : sc
-- {sessions_users} : su
-- {stats}
-- {tags} : t
-- {tags_items} : ti
-- {users} : u
-- {users_logs} : ul
-- {votes} : v

CREATE TABLE IF NOT EXISTS {cameras_brands} (
	camera_brand_id INTEGER PRIMARY KEY AUTO_INCREMENT,
	camera_brand_name VARCHAR(191) NOT NULL,
	camera_brand_url VARCHAR(191) NOT NULL,
	CONSTRAINT {cameras_brands_uk1} UNIQUE (camera_brand_name)
);

CREATE TABLE IF NOT EXISTS {config} (
	conf_name VARCHAR(60) NOT NULL PRIMARY KEY,
	conf_value TEXT,
	CONSTRAINT {config_uk1} UNIQUE (conf_name)
);

CREATE TABLE IF NOT EXISTS {groups} (
	group_id INTEGER PRIMARY KEY AUTO_INCREMENT,
	group_name TEXT NOT NULL,
	group_title TEXT NOT NULL,
	group_desc TEXT,
	group_crtdt DATETIME NOT NULL,
	group_perms TEXT NOT NULL,
	group_admin CHAR(1) NOT NULL DEFAULT '0'
);

CREATE TABLE IF NOT EXISTS {stats} (
	stat_date DATE NOT NULL PRIMARY KEY,
	stat_albums INTEGER NOT NULL,
	stat_images INTEGER NOT NULL,
	stat_videos INTEGER NOT NULL,
	stat_size BIGINT NOT NULL,
	stat_hits BIGINT NOT NULL,
	stat_comments INTEGER NOT NULL,
	stat_votes INTEGER NOT NULL,
	stat_favorites INTEGER NOT NULL,
	stat_users SMALLINT NOT NULL
);

CREATE TABLE IF NOT EXISTS {search} (
	search_id CHAR(12) NOT NULL PRIMARY KEY,
	search_query VARCHAR(255) NOT NULL,
	search_options TEXT,
	search_date DATETIME NOT NULL
);

CREATE TABLE IF NOT EXISTS {tags} (
	tag_id INTEGER PRIMARY KEY AUTO_INCREMENT,
	tag_name VARCHAR(191) NOT NULL,
	tag_url VARCHAR(191) NOT NULL,
	CONSTRAINT {tags_uk1} UNIQUE (tag_name)
);

CREATE TABLE IF NOT EXISTS {users} (
	user_id INTEGER PRIMARY KEY AUTO_INCREMENT,
		-- Les deux premiers identifiants sont réservés :
		-- 	1 : superadmin
		-- 	2 : invité (guest)
	group_id INTEGER NOT NULL DEFAULT 3,
	user_login VARCHAR(24) NOT NULL,
	user_password VARCHAR(191) NOT NULL,
	user_nickname VARCHAR(24),
	user_name VARCHAR(64),
	user_firstname VARCHAR(64),
	user_gender CHAR(1) NOT NULL DEFAULT '?',
	user_birthdate DATE,
	user_location VARCHAR(64),
	user_description TEXT,
	user_email VARCHAR(128),
	user_website VARCHAR(128),
	user_custom_1 VARCHAR(128),
	user_custom_2 VARCHAR(128),
	user_custom_3 VARCHAR(128),
	user_custom_4 VARCHAR(128),
	user_custom_5 VARCHAR(128),
	user_prefs TEXT,
	user_watermark TEXT,
	user_avatar CHAR(1) NOT NULL DEFAULT '0',
	user_nohits CHAR(1) NOT NULL DEFAULT '0',
	user_alert CHAR(6) NOT NULL DEFAULT '000000',
		-- Valeurs pour user_alert :
		-- 	Chaque chiffre ne peut prendre que deux valeurs possibles : 0 (désactivé) ou 1 (activé).
		-- 	Premier		: inscriptions
		-- 	Deuxième	: nouveaux commentaires
		-- 	Troisième	: nouveaux commentaires en attente de validation
		-- 	Quatrième	: nouveaux fichiers
		-- 	Cinquième	: nouveaux fichiers en attente de validation
		-- 	Sixième		: suivi de commentaires
	user_lang VARCHAR(5) NOT NULL,
	user_tz VARCHAR(32) NOT NULL,
	user_status VARCHAR(2) NOT NULL DEFAULT '1',
		-- Valeurs pour user_status :
		-- 	-2 : en attente de validation par courriel
		-- 	-1 : en attente de validation par un admin
		--   0 : suspendu
		--   1 : activé
	user_crtdt DATETIME NOT NULL,
	user_crtip VARCHAR(39) NOT NULL,
	user_lastvstdt DATETIME,
	user_lastvstip VARCHAR(39),
	user_rkey VARCHAR(128),
	user_rdate DATETIME,
	CONSTRAINT {users_uk1} UNIQUE (user_login),
	CONSTRAINT {users_uk2} UNIQUE (user_password),
	CONSTRAINT {users_fk1} FOREIGN KEY (group_id) REFERENCES {groups} (group_id)
);

CREATE TABLE IF NOT EXISTS {categories} (
	cat_id INTEGER PRIMARY KEY AUTO_INCREMENT,
	user_id INTEGER NOT NULL,
	thumb_id INTEGER NOT NULL DEFAULT 0,
		-- Valeurs de thumb_id :
		-- 		-1 : catégorie vide
		--  	 0 : vignette externe
		--  	sinon, identifiant de l'image/vidéo servant de vignette
	password_id INTEGER,
	cat_parents VARCHAR(255) NOT NULL DEFAULT '1.',
	parent_id INTEGER NOT NULL DEFAULT 1,
	cat_path VARCHAR(767) NOT NULL,
	cat_name VARCHAR(255) NOT NULL,
	cat_url VARCHAR(255) NOT NULL,
	cat_desc TEXT,
	cat_lat VARCHAR(255),
	cat_long VARCHAR(255),
	cat_tb_params VARCHAR(255),
	cat_a_size BIGINT NOT NULL DEFAULT 0,
	cat_a_subalbs INTEGER NOT NULL DEFAULT 0,
	cat_a_subcats INTEGER NOT NULL DEFAULT 0,
	cat_a_albums INTEGER NOT NULL DEFAULT 0,
	cat_a_images INTEGER NOT NULL DEFAULT 0,
	cat_a_videos INTEGER NOT NULL DEFAULT 0,
	cat_a_hits BIGINT NOT NULL DEFAULT 0,
	cat_a_comments INTEGER NOT NULL DEFAULT 0,
	cat_a_votes INTEGER NOT NULL DEFAULT 0,
	cat_a_rating DOUBLE PRECISION NOT NULL DEFAULT 0,
	cat_a_favorites INTEGER NOT NULL DEFAULT 0,
	cat_d_size BIGINT NOT NULL DEFAULT 0,
	cat_d_subalbs INTEGER NOT NULL DEFAULT 0,
	cat_d_subcats INTEGER NOT NULL DEFAULT 0,
	cat_d_albums INTEGER NOT NULL DEFAULT 0,
	cat_d_images INTEGER NOT NULL DEFAULT 0,
	cat_d_videos INTEGER NOT NULL DEFAULT 0,
	cat_d_hits BIGINT NOT NULL DEFAULT 0,
	cat_d_comments INTEGER NOT NULL DEFAULT 0,
	cat_d_votes INTEGER NOT NULL DEFAULT 0,
	cat_d_rating DOUBLE PRECISION NOT NULL DEFAULT 0,
	cat_d_favorites INTEGER NOT NULL DEFAULT 0,
	cat_votable CHAR(1) NOT NULL DEFAULT '1',
	cat_commentable CHAR(1) NOT NULL DEFAULT '1',
	cat_downloadable CHAR(1) NOT NULL DEFAULT '1',
	cat_uploadable CHAR(1) NOT NULL DEFAULT '1',
	cat_creatable CHAR(1) NOT NULL DEFAULT '1',
	cat_crtdt DATETIME NOT NULL,
	cat_lastpubdt DATETIME,
	cat_filemtime DATETIME,
	cat_orderby VARCHAR(255),
	cat_watermark TEXT,
	cat_status CHAR(1) NOT NULL DEFAULT '1',
		-- Valeurs de cat_status :
		--  	 0 : non publié
		--  	 1 : publié
	cat_position INTEGER NOT NULL DEFAULT 0,
	CONSTRAINT {categories_uk1} UNIQUE (cat_path),
	CONSTRAINT {categories_fk1} FOREIGN KEY (user_id) REFERENCES {users} (user_id),
	CONSTRAINT {categories_fk2} FOREIGN KEY (parent_id) REFERENCES {categories} (cat_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS {items} (
	item_id INTEGER PRIMARY KEY AUTO_INCREMENT,
	user_id INTEGER NOT NULL,
	cat_id INTEGER NOT NULL,
	item_type SMALLINT NOT NULL,
	item_path VARCHAR(767) NOT NULL,
	item_url VARCHAR(255) NOT NULL,
	item_width INTEGER NOT NULL,
	item_height INTEGER NOT NULL,
	item_duration SMALLINT,
	item_tb_params VARCHAR(255),
	item_filesize INTEGER NOT NULL,
	item_exif TEXT,
	item_iptc TEXT,
	item_xmp TEXT,
	item_orientation CHAR(1) DEFAULT '1',
	item_lat VARCHAR(255),
	item_long VARCHAR(255),
	item_name TEXT NOT NULL,
	item_desc TEXT,
	item_adddt DATETIME NOT NULL,
	item_pubdt DATETIME,
	item_expdt DATETIME,
	item_crtdt DATETIME,
	item_hits BIGINT NOT NULL DEFAULT 0,
	item_comments INTEGER NOT NULL DEFAULT 0,
	item_votes INTEGER NOT NULL DEFAULT 0,
	item_rating DOUBLE PRECISION NOT NULL DEFAULT 0,
	item_favorites INTEGER NOT NULL DEFAULT 0,
	item_votable CHAR(1) NOT NULL DEFAULT '1',
	item_commentable CHAR(1) NOT NULL DEFAULT '1',
	item_downloadable CHAR(1) NOT NULL DEFAULT '1',
	item_status VARCHAR(2) NOT NULL DEFAULT '1',
		-- Valeurs de item_status :
		-- 		-1 : jamais publié
		--  	 0 : non publié
		--  	 1 : publié
	item_position INTEGER NOT NULL DEFAULT 0,
	CONSTRAINT {items_uk1} UNIQUE (item_path),
	CONSTRAINT {items_fk1} FOREIGN KEY (user_id) REFERENCES {users} (user_id),
	CONSTRAINT {items_fk2} FOREIGN KEY (cat_id) REFERENCES {categories} (cat_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS {cameras_models} (
	camera_model_id INTEGER PRIMARY KEY AUTO_INCREMENT,
	camera_brand_id INTEGER NOT NULL,
	camera_model_name VARCHAR(191) NOT NULL,
	camera_model_url VARCHAR(191) NOT NULL,
	CONSTRAINT {cameras_models_uk1} UNIQUE (camera_model_name),
	CONSTRAINT {cameras_models_fk1} FOREIGN KEY (camera_brand_id) REFERENCES {cameras_brands} (camera_brand_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS {cameras_items} (
	camera_model_id INTEGER NOT NULL,
	item_id INTEGER NOT NULL,
	CONSTRAINT {cameras_items_uk1} UNIQUE (item_id),
	CONSTRAINT {cameras_items_fk1} FOREIGN KEY (camera_model_id) REFERENCES {cameras_models} (camera_model_id) ON DELETE CASCADE,
	CONSTRAINT {cameras_items_fk2} FOREIGN KEY (item_id) REFERENCES {items} (item_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS {comments} (
	com_id INTEGER PRIMARY KEY AUTO_INCREMENT,
	item_id INTEGER NOT NULL,
	user_id INTEGER NOT NULL DEFAULT 2, -- valeur par défaut = invité
	com_crtdt DATETIME NOT NULL,
	com_lastupddt DATETIME NOT NULL,
	com_author VARCHAR(24) NOT NULL,
	com_email VARCHAR(128),
	com_website VARCHAR(128),
	com_ip VARCHAR(40) NOT NULL,
	com_message TEXT NOT NULL,
	com_status VARCHAR(2) NOT NULL DEFAULT '1',
		-- Valeurs de com_status :
		-- 		-1 : en attente de validation
		--  	 0 : non publié
		--  	 1 : publié
	CONSTRAINT {comments_fk1} FOREIGN KEY (user_id) REFERENCES {users} (user_id),
	CONSTRAINT {comments_fk2} FOREIGN KEY (item_id) REFERENCES {items} (item_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS {favorites} (
	user_id INTEGER NOT NULL,
	item_id INTEGER NOT NULL,
	fav_date DATETIME NOT NULL,
	CONSTRAINT {favorites_uk1} UNIQUE (user_id, item_id),
	CONSTRAINT {favorites_fk1} FOREIGN KEY (user_id) REFERENCES {users} (user_id) ON DELETE CASCADE,
	CONSTRAINT {favorites_fk2} FOREIGN KEY (item_id) REFERENCES {items} (item_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS {selection} (
	user_id INTEGER NOT NULL,
	item_id INTEGER NOT NULL,
	selection_date DATETIME NOT NULL,
	CONSTRAINT {selection_uk1} UNIQUE (user_id, item_id),
	CONSTRAINT {selection_fk1} FOREIGN KEY (user_id) REFERENCES {users} (user_id) ON DELETE CASCADE,
	CONSTRAINT {selection_fk2} FOREIGN KEY (item_id) REFERENCES {items} (item_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS {groups_permissions} (
	group_id INTEGER NOT NULL,
	cat_id INTEGER NOT NULL,
	perm_list VARCHAR(40) NOT NULL,
	CONSTRAINT {groups_permissions_uk1} UNIQUE (group_id, cat_id, perm_list),
	CONSTRAINT {groups_permissions_fk1} FOREIGN KEY (group_id) REFERENCES {groups} (group_id) ON DELETE CASCADE,
	CONSTRAINT {groups_permissions_fk2} FOREIGN KEY (cat_id) REFERENCES {categories} (cat_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS {passwords} (
	password_id INTEGER PRIMARY KEY AUTO_INCREMENT,
	cat_id INTEGER NOT NULL,
	password_hash VARCHAR(191) NOT NULL,
	CONSTRAINT {passwords_uk1} UNIQUE (password_hash),
	CONSTRAINT {passwords_fk1} FOREIGN KEY (cat_id) REFERENCES {categories} (cat_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS {sessions} (
	session_id INTEGER PRIMARY KEY AUTO_INCREMENT,
	session_token VARCHAR(191) NOT NULL,
	session_expire DATETIME NOT NULL,
	CONSTRAINT {sessions_uk1} UNIQUE (session_token)
);

CREATE TABLE IF NOT EXISTS {sessions_categories} (
	session_id INTEGER NOT NULL,
	cat_id INTEGER NOT NULL,
	CONSTRAINT {sessions_categories_uk1} UNIQUE (session_id, cat_id),
	CONSTRAINT {sessions_categories_fk1} FOREIGN KEY (session_id) REFERENCES {sessions} (session_id) ON DELETE CASCADE,
	CONSTRAINT {sessions_categories_fk2} FOREIGN KEY (cat_id) REFERENCES {categories} (cat_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS {sessions_users} (
	session_id INTEGER NOT NULL,
	user_id INTEGER NOT NULL,
	CONSTRAINT {sessions_users_uk1} UNIQUE (session_id, user_id),
	CONSTRAINT {sessions_users_fk1} FOREIGN KEY (session_id) REFERENCES {sessions} (session_id) ON DELETE CASCADE,
	CONSTRAINT {sessions_users_fk2} FOREIGN KEY (user_id) REFERENCES {users} (user_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS {tags_items} (
	tag_id INTEGER NOT NULL,
	item_id INTEGER NOT NULL,
	CONSTRAINT {tags_items_uk1} UNIQUE (tag_id, item_id),
	CONSTRAINT {tags_items_fk1} FOREIGN KEY (tag_id) REFERENCES {tags} (tag_id) ON DELETE CASCADE,
	CONSTRAINT {tags_items_fk2} FOREIGN KEY (item_id) REFERENCES {items} (item_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS {items_pending} (
	pending_id INTEGER PRIMARY KEY AUTO_INCREMENT,
	cat_id INTEGER NOT NULL,
	user_id INTEGER NOT NULL,
	pending_file VARCHAR(191) NOT NULL,
	pending_type SMALLINT NOT NULL,
	pending_filesize INTEGER NOT NULL,
	pending_exif TEXT,
	pending_iptc TEXT,
	pending_xmp TEXT,
	pending_name VARCHAR(255),
	pending_height SMALLINT NOT NULL,
	pending_width SMALLINT NOT NULL,
	pending_duration SMALLINT,
	pending_adddt DATETIME NOT NULL,
	pending_ip VARCHAR(40) NOT NULL,
	CONSTRAINT {items_pending_uk1} UNIQUE (pending_file, cat_id),
	CONSTRAINT {items_pending_fk1} FOREIGN KEY (cat_id) REFERENCES {categories} (cat_id) ON DELETE CASCADE,
	CONSTRAINT {items_pending_fk2} FOREIGN KEY (user_id) REFERENCES {users} (user_id)
);

CREATE TABLE IF NOT EXISTS {users_logs} (
	log_id INTEGER PRIMARY KEY AUTO_INCREMENT,
	user_id INTEGER NOT NULL,
	log_page TEXT NOT NULL,
	log_date DATETIME NOT NULL,
	log_action VARCHAR(64) NOT NULL,
	log_match VARCHAR(255),
	log_post TEXT,
	log_ip VARCHAR(40) NOT NULL,
	CONSTRAINT {users_logs_fk1} FOREIGN KEY (user_id) REFERENCES {users} (user_id)
);

CREATE TABLE IF NOT EXISTS {votes} (
	vote_id INTEGER PRIMARY KEY AUTO_INCREMENT,
	item_id INTEGER NOT NULL,
	user_id INTEGER NOT NULL DEFAULT 2, -- valeur par défaut = invité
	vote_rating INTEGER NOT NULL,
	vote_date DATETIME NOT NULL,
	vote_ip VARCHAR(40) NOT NULL,
	vote_cookie VARCHAR(255) NOT NULL,
	CONSTRAINT {votes_fk1} FOREIGN KEY (user_id) REFERENCES {users} (user_id),
	CONSTRAINT {votes_fk2} FOREIGN KEY (item_id) REFERENCES {items} (item_id) ON DELETE CASCADE
);

CREATE INDEX {items_pubdt_idx} ON {items} (item_pubdt);
