<?php

ze\dbAdm::revision(2
, <<<_sql
	DROP TABLE IF EXISTS `[[DB_PREFIX]][[ZENARIO_VIDEOS_MANAGER_PREFIX]]videos`
_sql
, <<<_sql
	CREATE TABLE `[[DB_PREFIX]][[ZENARIO_VIDEOS_MANAGER_PREFIX]]videos` (
		`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
		`url` varchar(255) NOT NULL,
		`title` varchar(255) NOT NULL,
		`description` text,
		`date` date NOT NULL,
		PRIMARY KEY (`id`)
	) ENGINE=[[ZENARIO_TABLE_ENGINE]] CHARSET=[[ZENARIO_TABLE_CHARSET]] COLLATE=[[ZENARIO_TABLE_COLLATION]]
_sql

, <<<_sql
	DROP TABLE IF EXISTS `[[DB_PREFIX]][[ZENARIO_VIDEOS_MANAGER_PREFIX]]video_categories`
_sql

, <<<_sql
	DROP TABLE IF EXISTS `[[DB_PREFIX]][[ZENARIO_VIDEOS_MANAGER_PREFIX]]category_video_link`
_sql

, <<<_sql
	CREATE TABLE `[[DB_PREFIX]][[ZENARIO_VIDEOS_MANAGER_PREFIX]]video_categories` (
		`video_id` int(10) unsigned NOT NULL,
		`name` varchar(255) NOT NULL,
		PRIMARY KEY (`video_id`, `name`)
	) ENGINE=[[ZENARIO_TABLE_ENGINE]] CHARSET=[[ZENARIO_TABLE_CHARSET]] COLLATE=[[ZENARIO_TABLE_COLLATION]]
_sql

, <<<_sql
	DROP TABLE IF EXISTS `[[DB_PREFIX]][[ZENARIO_VIDEOS_MANAGER_PREFIX]]videos_custom_data`
_sql
, <<<_sql
	CREATE TABLE [[DB_PREFIX]][[ZENARIO_VIDEOS_MANAGER_PREFIX]]videos_custom_data (
		`video_id` int(10) unsigned NOT NULL,
		PRIMARY KEY (`video_id`)
	) ENGINE=[[ZENARIO_TABLE_ENGINE]] CHARSET=[[ZENARIO_TABLE_CHARSET]] COLLATE=[[ZENARIO_TABLE_COLLATION]]
_sql

);

ze\dbAdm::revision(4
, <<<_sql
	ALTER TABLE `[[DB_PREFIX]][[ZENARIO_VIDEOS_MANAGER_PREFIX]]videos`
	ADD COLUMN `image_id` int(10) unsigned NOT NULL DEFAULT 0 AFTER `url`
_sql

); ze\dbAdm::revision(5
, <<<_sql
	DROP TABLE IF EXISTS `[[DB_PREFIX]][[ZENARIO_VIDEOS_MANAGER_PREFIX]]categories`
_sql
, <<<_sql
	CREATE TABLE `[[DB_PREFIX]][[ZENARIO_VIDEOS_MANAGER_PREFIX]]categories` (
		`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
		`name` varchar(250) CHARACTER SET [[ZENARIO_TABLE_CHARSET]] COLLATE [[ZENARIO_TABLE_COLLATION]] NOT NULL,
		PRIMARY KEY (`id`)
	) ENGINE=[[ZENARIO_TABLE_ENGINE]] CHARSET=[[ZENARIO_TABLE_CHARSET]] COLLATE=[[ZENARIO_TABLE_COLLATION]]
_sql

, <<<_sql
	DELETE FROM `[[DB_PREFIX]][[ZENARIO_VIDEOS_MANAGER_PREFIX]]video_categories`
_sql
, <<<_sql
	ALTER TABLE `[[DB_PREFIX]][[ZENARIO_VIDEOS_MANAGER_PREFIX]]video_categories`
	DROP COLUMN `name`,
	ADD COLUMN `category_id` int(10) unsigned NOT NULL,
	DROP PRIMARY KEY,
	ADD PRIMARY KEY (`video_id`, `category_id`)
_sql

, <<<_sql
	ALTER TABLE `[[DB_PREFIX]][[ZENARIO_VIDEOS_MANAGER_PREFIX]]videos`
	MODIFY COLUMN `url` varchar(255) CHARACTER SET [[ZENARIO_TABLE_CHARSET]] COLLATE [[ZENARIO_TABLE_COLLATION]] NOT NULL,
	MODIFY COLUMN `title` varchar(255) CHARACTER SET [[ZENARIO_TABLE_CHARSET]] COLLATE [[ZENARIO_TABLE_COLLATION]] NOT NULL,
	MODIFY COLUMN `description` text CHARACTER SET [[ZENARIO_TABLE_CHARSET]] COLLATE [[ZENARIO_TABLE_COLLATION]]
_sql

); ze\dbAdm::revision(6
, <<<_sql
	ALTER TABLE `[[DB_PREFIX]][[ZENARIO_VIDEOS_MANAGER_PREFIX]]videos`
	ADD COLUMN `short_description` text CHARACTER SET [[ZENARIO_TABLE_CHARSET]] COLLATE [[ZENARIO_TABLE_COLLATION]] AFTER `title`
_sql

); ze\dbAdm::revision(11
, <<<_sql
	ALTER TABLE `[[DB_PREFIX]][[ZENARIO_VIDEOS_MANAGER_PREFIX]]videos`
	ADD COLUMN `created` datetime DEFAULT NULL,
	ADD COLUMN `created_admin_id` int(10) unsigned DEFAULT NULL,
	ADD COLUMN `created_user_id` int(10) unsigned DEFAULT NULL,
	ADD COLUMN `created_username` varchar(255) DEFAULT NULL,
	ADD COLUMN `last_edited` datetime DEFAULT NULL,
	ADD COLUMN `last_edited_admin_id` int(10) unsigned DEFAULT NULL,
	ADD COLUMN `last_edited_user_id` int(10) unsigned DEFAULT NULL,
	ADD COLUMN `last_edited_username` varchar(255) DEFAULT NULL
_sql

); ze\dbAdm::revision(12
, <<<_sql
	ALTER TABLE `[[DB_PREFIX]][[ZENARIO_VIDEOS_MANAGER_PREFIX]]videos`
	ADD COLUMN `language_id` varchar(10) DEFAULT NULL
_sql

//Rename a table to have a less confusing name
); ze\dbAdm::revision(13
, <<<_sql
	RENAME TABLE `[[DB_PREFIX]][[ZENARIO_VIDEOS_MANAGER_PREFIX]]video_categories`
	TO `[[DB_PREFIX]][[ZENARIO_VIDEOS_MANAGER_PREFIX]]category_video_link`
_sql

);

if (ze\dbAdm::needRevision(17)) {
	$keySql = "
		ALTER TABLE `" . ze\escape::sql(DB_PREFIX . ZENARIO_VIDEOS_MANAGER_PREFIX . "videos") . "`
		ADD FULLTEXT KEY `title_fulltext_key` (`title`)";
	ze\sql::update($keySql);
	
	$keySql = "
		ALTER TABLE `" . ze\escape::sql(DB_PREFIX . ZENARIO_VIDEOS_MANAGER_PREFIX . "videos") . "`
		ADD FULLTEXT KEY `short_description_fulltext_key` (`short_description`)";
	ze\sql::update($keySql);
	
	$keySql = "
		ALTER TABLE `" . ze\escape::sql(DB_PREFIX . ZENARIO_VIDEOS_MANAGER_PREFIX . "videos") . "`
		ADD FULLTEXT KEY `description_fulltext_key` (`description`)";
	ze\sql::update($keySql);
	
	ze\dbAdm::revision(17);
}

//In 9.7 (backpatched to 9.6), we changed the Vimeo privacy handling.
//Now we store the privacy code in the DB instead of having to get it via the API every time.
//This greatly speeds up loading the Videos panel when multiple Vimeo videos exist.
ze\dbAdm::revision(18
, <<<_sql
	ALTER TABLE `[[DB_PREFIX]][[ZENARIO_VIDEOS_MANAGER_PREFIX]]videos`
	ADD COLUMN `vimeo_privacy_setting` varchar(100) CHARACTER SET [[ZENARIO_TABLE_CHARSET]] COLLATE [[ZENARIO_TABLE_COLLATION]] DEFAULT NULL,
	ADD COLUMN `vimeo_privacy_last_cached` datetime DEFAULT NULL
_sql

);

//In 10.0, we removed any auto-population or fallback logic for the date.
//Update the column definition to allow NULL.
ze\dbAdm::revision(19
, <<<_sql
	ALTER TABLE `[[DB_PREFIX]][[ZENARIO_VIDEOS_MANAGER_PREFIX]]videos`
	CHANGE COLUMN `date` `date` date DEFAULT NULL
_sql

//In 10.1, we added support for starting a video at a specific time.
); ze\dbAdm::revision(21
, <<<_sql
	ALTER TABLE `[[DB_PREFIX]][[ZENARIO_VIDEOS_MANAGER_PREFIX]]videos`
	ADD COLUMN `start_time` varchar(255) CHARACTER SET [[ZENARIO_TABLE_CHARSET]] COLLATE [[ZENARIO_TABLE_COLLATION]] NOT NULL DEFAULT ''
_sql

);