##----------------------------------------------------------------------------------#
 # MusicMiner Web Server															#
 # Name:		source/app/sql/upgrade_0-9-22_to_1-0-0.sql							#
 # Author:		Jesse J Morrow	(jesse@jessejmorrow.com)							#
 # Created:		2008-02-28															#
 # Updated:		2008-12-02															#
 # Compat:		MySQL 5																#
 # Copyright:	Copyright 2008 Soundminer Inc.										#
 # Purpose:		Upgrade the installed MusicMiner databases.							#
 # Version:		0.9.22 to 1.0.0														#
 #----------------------------------------------------------------------------------##


#####################

# Create new `playlists` table, accommodates `cuesheets` table fields
CREATE TABLE `mm_admin`.`playlists` (
  `id` int(11) NOT NULL auto_increment,
  `folder_id` int(11) DEFAULT 0 COMMENT 'Parent folder containing this playlist. NULL = deleted.',
  `path` varchar(1000) NOT NULL COMMENT 'Tree hierachy path.',
  `type` enum('playlist','cuesheet') NOT NULL default 'playlist' COMMENT 'The list type as created by the user.',
  `name` varchar(128) NOT NULL default 'Playlist' COMMENT 'Name of this playlist as it appears to the user.',
  `title` varchar(128) default NULL COMMENT 'Film or program title.',
  `episode` varchar(128) default NULL COMMENT 'Episode title if part of a series.',
  `episode_no` varchar(12) default NULL COMMENT 'Episode number if part of a series.',
  `production_no` varchar(12) default NULL COMMENT 'Production number or ID.',
  `airdate` date default NULL COMMENT 'Original or estimated airdate.',
  `length` time default NULL COMMENT 'Normal run time of program or length of film.',
  `program_type` varchar(32) default NULL COMMENT 'Type of program, such as cartoon, game show, soap opera, etc.',
  `version_type` varchar(32) default NULL COMMENT 'Type of broadcast sources for which the cuesheet was created, such as network, cable, etc.',
  `station` varchar(128) default NULL COMMENT 'Network station associated with the program.',
  `producer` varchar(128) default NULL COMMENT 'Producer of the film or program.',
  `distributor` varchar(128) default NULL COMMENT 'Distributor of the film or program.',
  `director` varchar(128) default NULL COMMENT 'Director of the film or program.',
  `contact` varchar(1000) default NULL COMMENT 'Person or company, with address and contact info., who produced the program, prepared, or should be contacted regarding, the cuesheet.',
  `contact_type` enum('contact','prepared','produced') NOT NULL default 'contact' COMMENT 'The entity which the `contact` field refers.',
  `notes` varchar(10000) default NULL COMMENT 'User''s notes attached to this cuesheet.',
  `created` datetime NOT NULL COMMENT 'Timestamp of the original creation of row.',
  `updated` datetime NOT NULL COMMENT 'Timestamp of the last modification of row.',
  `updated_by` INT NOT NULL DEFAULT 0 COMMENT 'User ID of user making the last modification of row.',
  `orig_id` int(11) default NULL COMMENT 'Transient field used when doing complex copy operations.',
  PRIMARY KEY  (`id`),
  KEY `folder_id_index` (`folder_id`),
  KEY `path_index` (`path`(333)),
  KEY `orig_id_index` (`orig_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

# Prepare `folder_states` for `playlists` addition
ALTER TABLE `mm_admin`.`folder_states` MODIFY COLUMN `type` ENUM('section','folder','cuesheet','playlist') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'folder' COMMENT 'Indicates which table, `folders`, `cuesheets`, or `playlists` that `id` is associated with.';

#####################

# Delete any invalid `tracks` entries that have `folder_id` = '0'
DELETE FROM `mm_admin`.`tracks` WHERE `folder_id` = '0';

# Create temporary table to facilitate transition of folders to playlists
CREATE TABLE `mm_admin`.`tmp_counts`
  SELECT `folder_id` AS `id`, SUM(`folder_cnt`) AS `folder_cnt`, SUM(`cuesheet_cnt`) AS `cuesheet_cnt`, SUM(`track_cnt`) AS `track_cnt`, SUM(`search_cnt`) AS `search_cnt`
  FROM (
    SELECT `folder_id`, COUNT(*) AS `folder_cnt`, '0' AS `cuesheet_cnt`, '0' AS `search_cnt`, '0' AS `track_cnt` FROM `mm_admin`.`folders` GROUP BY `folder_id` UNION ALL
    SELECT `folder_id`, '0', COUNT(*), '0', '0' FROM `mm_admin`.`cuesheets` GROUP BY `folder_id` UNION ALL
    SELECT `folder_id`, '0', '0', COUNT(*), '0' FROM `mm_admin`.`searches` GROUP BY `folder_id` UNION ALL
    SELECT `folder_id`, '0', '0', '0', COUNT(*) FROM `mm_admin`.`tracks` GROUP BY `folder_id`) `t`
  GROUP BY `folder_id`;

# Convert empty (non-root) folders and (non-root) folders containing only tracks to playlists
INSERT INTO `mm_admin`.`playlists` (`id`, `folder_id`, `name`, `created`, `updated`, `orig_id`) SELECT NULL, `folders`.`folder_id`, `name`, `created`, `updated`, `folders`.`id` FROM `mm_admin`.`folders` LEFT JOIN `mm_admin`.`tmp_counts` ON `folders`.`id` = `tmp_counts`.`id` WHERE `folder_cnt` = 0 AND `cuesheet_cnt` = 0 AND `search_cnt` = 0 AND `folder_id` != 0;

# Convert folders with folders/cuesheets & tracks and root folders with tracks to folders with a new child playlist
INSERT INTO `mm_admin`.`playlists` (`id`, `folder_id`, `name`, `created`, `updated`, `orig_id`) SELECT NULL, `folders`.`id`, `name`, `created`, `updated`, `folders`.`id` FROM `mm_admin`.`folders` LEFT JOIN `mm_admin`.`tmp_counts` ON `folders`.`id` = `tmp_counts`.`id` WHERE (`folder_cnt` != 0 OR `cuesheet_cnt` != 0 OR `search_cnt` != 0 OR `folder_id` = '0') AND `track_cnt` != 0;

# Transfers tracks to the new playlists
UPDATE `mm_admin`.`tracks` LEFT JOIN `mm_admin`.`playlists` ON `tracks`.`folder_id` = `playlists`.`orig_id` SET `tracks`.`folder_id` = `playlists`.`id`;

# Clear `playlists`.`orig_id` field
UPDATE `mm_admin`.`playlists` SET `orig_id` = NULL;

# Delete empty (non-root) folders and (non-root) folders containing only tracks
DELETE `mm_admin`.`folders` FROM `mm_admin`.`folders` LEFT JOIN `mm_admin`.`tmp_counts` ON `folders`.`id` = `tmp_counts`.`id` WHERE `folder_cnt` = 0 AND `cuesheet_cnt` = 0 AND `search_cnt` = 0 AND `folder_id` != 0;

# Drop temporary transition table
DROP TABLE `mm_admin`.`tmp_counts`;

#####################

# Rename `tracks`.`folder_id` to `playlist_id`, modify `tracks` table to accommodate `cues` table fields
ALTER TABLE `mm_admin`.`tracks`
 MODIFY COLUMN `id` INTEGER NOT NULL DEFAULT NULL AUTO_INCREMENT COMMENT 'Primary key.',
 CHANGE COLUMN `folder_id` `playlist_id` INTEGER DEFAULT 0 COMMENT 'Parent playlist containing this track. NULL = deleted.',
 MODIFY COLUMN `asset_id` INTEGER NOT NULL COMMENT 'Entry in `assets` table that represents this track.',
 ADD COLUMN `path` VARCHAR(1000) DEFAULT '' COMMENT 'Tree hierachy path. empty = deleted.' AFTER `playlist_id`,
 ADD COLUMN `ndx` INT NOT NULL COMMENT 'The sequence index of this track within the playlist.' AFTER `asset_id`,
 ADD COLUMN `usage` VARCHAR(32) NOT NULL COMMENT 'Standardized code indicating how the track was used.' AFTER `ndx`,
 ADD COLUMN `time` TIME NOT NULL COMMENT 'Duration of song usage within the work.' AFTER `usage`,
 ADD COLUMN `updated_by` INT NOT NULL DEFAULT 0 COMMENT 'User ID of user making the last modification of row.' AFTER `updated`,
 ADD COLUMN `orig_id` INT COMMENT 'Transient field used when doing complex copy operations.' AFTER `updated_by`,
 ADD INDEX `path_index`(`path`),
 ADD INDEX `orig_id_index`(`orig_id`);

#####################

# Alter `users` to change `db_id` to `src_type` and `src_id` combo.
ALTER TABLE `mm_admin`.`users` CHANGE COLUMN `db_id` `src_id` INTEGER NOT NULL DEFAULT 0 COMMENT '0: all, >0:  db_id, <0: -folder_id',
 ADD COLUMN `src_type` ENUM('db','special','folder','playlist','smart','cuesheet') NOT NULL DEFAULT 'db' COMMENT 'The type of the results source selected.' AFTER `rep`;
UPDATE `mm_admin`.`users` SET `src_id` = '0';

# Alter `history` to change `db_id` to `src_type` and `src_id` combo.
ALTER TABLE `mm_admin`.`history` CHANGE COLUMN `db_id` `src_id` INTEGER NOT NULL DEFAULT 0,
 ADD COLUMN `src_type` ENUM('db','special','folder','playlist','smart','cuesheet') NOT NULL DEFAULT 'db' AFTER `ftext`;
TRUNCATE TABLE `mm_admin`.`history`;

#####################
# Convert `cuesheets` & `cues` to `playlists` & `tracks`

# Modify `assets` table's `db_id` and `track_id` to allow NULL to accommodate custom cues
ALTER TABLE `mm_admin`.`assets` MODIFY COLUMN `db_id` INTEGER, MODIFY COLUMN `track_id` INTEGER;

# UpgradeCtrl ensures that `TrackTitle` `Composer`, and `Publisher` exist first.
# Insert custom `cues` into `assets` table (create temporary negative `track_id` reference back to `cues`.`id`)
INSERT INTO `mm_admin`.`assets` (`track_id`, `TrackTitle`, `Composer`, `Publisher`) SELECT -`id`, `title`, `composer`, `publisher` FROM `mm_admin`.`cues` WHERE `asset_id` IS NULL;

# Point `cues`.`asset_id` to new `assets` rows and set temporary negative `track_id` to NULL
UPDATE `mm_admin`.`cues`, `mm_admin`.`assets` SET `cues`.`asset_id` = `assets`.`id`, `assets`.`track_id` = NULL WHERE `cues`.`id` = -`assets`.`track_id`;

# Insert `cues` into `tracks` table
INSERT INTO `mm_admin`.`tracks` (`playlist_id`, `asset_id`, `ndx`, `usage`, `time`, `created`, `updated`) SELECT -`cuesheet_id`, `asset_id`, `ndx`, `usage`, `time`, `created`, `updated` FROM `mm_admin`.`cues`;

# Insert `cuesheets` into `playlists` table
INSERT INTO `mm_admin`.`playlists` (`folder_id`, `type`, `name`, `title`, `episode`, `episode_no`, `production_no`, `airdate`, `length`, `program_type`, `version_type`, `station`, `producer`, `distributor`, `director`, `contact`, `contact_type`, `notes`, `created`, `updated`, `orig_id`) SELECT `folder_id`, 'cuesheet', `name`, `title`, `episode`, `episode_no`, `production_no`, `airdate`, `length`, `program_type`, `version_type`, `station`, `producer`, `distributor`, `director`, `contact`, `contact_type`, `notes`, `created`, `updated`, `id` FROM `mm_admin`.`cuesheets`;

# Point `tracks` entries that point to be `cuesheets` entries to their new `playlists` entries
UPDATE `mm_admin`.`tracks`, `mm_admin`.`playlists` SET `tracks`.`playlist_id` = `playlists`.`id` WHERE `tracks`.`playlist_id` = -`playlists`.`orig_id`;

# Clear `playlists`.`orig_id` field
UPDATE `mm_admin`.`playlists` SET `orig_id` = NULL;

# Drop `cues` table which is now represented in `tracks` table
DROP TABLE `mm_admin`.`cues`;

# Drop `cuesheets` table which is now represented in `playlists` table
DROP TABLE `mm_admin`.`cuesheets`;

#####################
# Generate `path` fields

ALTER TABLE `mm_admin`.`folders`
 MODIFY COLUMN `folder_id` INTEGER DEFAULT 0 COMMENT 'Parent folder containing this folder. NULL = deleted.',
 ADD COLUMN `path` VARCHAR(1000) NOT NULL COMMENT 'Tree hierachy path.' AFTER `folder_id`,
 ADD COLUMN `updated_by` INT NOT NULL DEFAULT 0 COMMENT 'User ID of user making the last modification of row.' AFTER `updated`,
 ADD COLUMN `orig_id` INT DEFAULT NULL COMMENT 'Transient field used when doing complex copy operations.' AFTER `updated_by`,
 ADD INDEX `folder_id`(`folder_id`),
 ADD INDEX `path_index`(`path`),
 ADD INDEX `orig_id_index`(`orig_id`);

ALTER TABLE `mm_admin`.`searches`
 MODIFY COLUMN `folder_id` INTEGER DEFAULT 0 COMMENT 'Parent folder containing this folder. NULL = deleted.',
 ADD COLUMN `path` varchar(1000) NOT NULL COMMENT 'Tree hierachy path. empty = deleted.' AFTER `folder_id`,
 ADD COLUMN `updated_by` INT NOT NULL DEFAULT 0 COMMENT 'User ID of user making the last modification of row.' AFTER `updated`,
 ADD COLUMN `orig_id` INT DEFAULT NULL COMMENT 'Transient field used when doing complex copy operations.' AFTER `updated_by`,
 ADD INDEX `folder_id`(`folder_id`),
 ADD INDEX `path_index`(`path`),
 ADD INDEX `orig_id_index`(`orig_id`);

# Fill in `folders`.`path` field for a folder depth of up to 20
UPDATE `mm_admin`.`folders` set path = '/' where folder_id = '0';
UPDATE `mm_admin`.`folders` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/') where a.path = '' AND b.path != '';
UPDATE `mm_admin`.`folders` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/') where a.path = '' AND b.path != '';
UPDATE `mm_admin`.`folders` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/') where a.path = '' AND b.path != '';
UPDATE `mm_admin`.`folders` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/') where a.path = '' AND b.path != '';
UPDATE `mm_admin`.`folders` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/') where a.path = '' AND b.path != '';
UPDATE `mm_admin`.`folders` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/') where a.path = '' AND b.path != '';
UPDATE `mm_admin`.`folders` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/') where a.path = '' AND b.path != '';
UPDATE `mm_admin`.`folders` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/') where a.path = '' AND b.path != '';
UPDATE `mm_admin`.`folders` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/') where a.path = '' AND b.path != '';
UPDATE `mm_admin`.`folders` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/') where a.path = '' AND b.path != '';
UPDATE `mm_admin`.`folders` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/') where a.path = '' AND b.path != '';
UPDATE `mm_admin`.`folders` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/') where a.path = '' AND b.path != '';
UPDATE `mm_admin`.`folders` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/') where a.path = '' AND b.path != '';
UPDATE `mm_admin`.`folders` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/') where a.path = '' AND b.path != '';
UPDATE `mm_admin`.`folders` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/') where a.path = '' AND b.path != '';
UPDATE `mm_admin`.`folders` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/') where a.path = '' AND b.path != '';
UPDATE `mm_admin`.`folders` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/') where a.path = '' AND b.path != '';
UPDATE `mm_admin`.`folders` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/') where a.path = '' AND b.path != '';
UPDATE `mm_admin`.`folders` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/') where a.path = '' AND b.path != '';
UPDATE `mm_admin`.`folders` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/') where a.path = '' AND b.path != '';

# Fill in `playlists`.`path` field
UPDATE `mm_admin`.`playlists` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/');

# Fill in `tracks`.`path` field
UPDATE `mm_admin`.`tracks` a LEFT JOIN `mm_admin`.playlists b ON a.playlist_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/');

# Fill in `searches`.`path` field
UPDATE `mm_admin`.`searches` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/');

# Fill in `tracks`.`ndx` fields, especially for `tracks` that were not `cues` before
SET @play = -1;
UPDATE `mm_admin`.`tracks` SET `ndx` = IF(@play!=`playlist_id`,0*(@play:=`playlist_id`)+(@ndx:=0),(@ndx:=@ndx+1)) ORDER BY `playlist_id`, `ndx` ASC;

#####################

ALTER TABLE `mm_admin`.`proxies` ADD COLUMN `channel_layout` enum('From Metadata','','L/R','L/C/R','L/R/S','L/R/Ls/Rs','L/C/R/S','L/C/R/Ls/Rs/LFE','A1/A2/A3/A4/A5/A6') NOT NULL DEFAULT 'From Metadata' AFTER `mime_name`;

ALTER TABLE `mm_admin`.`users` ADD COLUMN `download_zip` ENUM('never','folders','multiple','always') NOT NULL DEFAULT 'multiple' COMMENT 'Zip up multiple track downloads into a single zip file.' AFTER `prf_vista`,
 ADD COLUMN `download_flatten` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Flatten folder downloads by collapsing the folder structure.' AFTER `download_zip`;

UPDATE `mm_admin`.`users`, `mm_admin`.`site` SET `users`.`download_zip` = `site`.`zip_downloads`;

ALTER TABLE `mm_admin`.`site` DROP COLUMN `zip_downloads`;

#####################

# Update version fields
UPDATE `mm_admin`.`schema` SET `db_ver` = '1.0.0', `sm_ver` = '1.0.0';