##----------------------------------------------------------------------------------#
 # MusicMiner Web Server															#
 # Name:		source/app/sql/upgrade_0-5-12_to_0-6-0.sql							#
 # Author:		Jesse J Morrow	(jesse@jessejmorrow.com)							#
 # Created:		2007-06-08															#
 # Updated:		2007-06-15															#
 # Compat:		MySQL 5																#
 # Copyright:	Copyright 2007 Soundminer Inc.										#
 # Purpose:		Upgrade the installed MusicMiner databases.							#
 # Version:		0.5.12 to 0.6.0														#
 #----------------------------------------------------------------------------------##

# Drop and recreate `cuesheets` table with new structure (cuesheets were not implemented in prior versions)
DROP TABLE `mm_admin`.`cuesheets`;
CREATE TABLE `mm_admin`.`cuesheets` (
  `id` int(11) NOT NULL auto_increment COMMENT 'Primary key.',
  `folder_id` int(11) NOT NULL COMMENT 'Parent project folder that contains this cuesheet.',
  `name` varchar(128) NOT NULL COMMENT 'Name of the cuesheet as it appears in parent project folder.',
  `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 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 this cuesheet row.',
  `updated` datetime NOT NULL COMMENT 'Timestamp of last modification of this cuesheet row.',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Holds program info, contact info, and settings for cuesheets';

# Drop and recreate `cues` table with new structure (cuesheets were not implemented in prior versions)
DROP TABLE `mm_admin`.`cues`;
CREATE TABLE `mm_admin`.`cues` (
  `id` int(11) NOT NULL auto_increment COMMENT 'Primary key.',
  `cuesheet_id` int(11) NOT NULL COMMENT 'Parent cuesheet containing this cue.',
  `ndx` int(11) NOT NULL default '0' COMMENT 'The sequence index of this cue within the cuesheet. cue = ndx + 1',
  `asset_id` int(11) default NULL COMMENT 'Entry in `assets` table that is source track of this song. NULL means this is a hand-entered cue.',
  `title` varchar(255) default NULL COMMENT 'Song title.',
  `composer` varchar(255) default NULL COMMENT 'Composer, with affiliations and percentages, of the song.',
  `publisher` varchar(255) default NULL COMMENT 'Publisher, with affiliations and percentages, of the song.',
  `usage` varchar(32) NOT NULL COMMENT 'Standardized code indicating how the song was used in the work.',
  `time` time NOT NULL COMMENT 'Duration of song usage within the work.',
  `created` datetime NOT NULL COMMENT 'Timestamp of the original creation of row.',
  `updated` datetime NOT NULL COMMENT 'Timestamp of the last modification of row.',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Each row is a cue entry in it''s associated cuesheet.';

# Alter `folders` table to shorten `name` to VARCHAR(128) and add new `created`, and `updated` columns
ALTER TABLE `mm_admin`.`folders` MODIFY COLUMN `name` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'Folder' COMMENT 'Name of this folder as it appears in parent project folder.',
 ADD COLUMN `created` DATETIME NOT NULL COMMENT 'Timestamp of the original creation of row.' AFTER `name`,
 ADD COLUMN `updated` DATETIME NOT NULL COMMENT 'Timestamp of the last modification of row.' AFTER `created`;

# Alter `searches` table to shorten `name` to VARCHAR(128) and add new `created`, and `updated` columns
ALTER TABLE `mm_admin`.`searches` MODIFY COLUMN `name` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'Name of this search as it appears in parent project folder.',
 ADD COLUMN `created` DATETIME NOT NULL COMMENT 'Timestamp of the original creation of row.' AFTER `text`,
 ADD COLUMN `updated` DATETIME NOT NULL COMMENT 'Timestamp of the last modification of row.' AFTER `created`;

# Truncate `tracks` table, starting over with new structure
# Alter `tracks` to drop `name` column and add new `created`, and `updated` columns
# Alter `tracks` table to replace `db_id` and `track_id` with `asset_id` and reset auto increment
TRUNCATE TABLE `mm_admin`.`tracks`;
ALTER TABLE `mm_admin`.`tracks` DROP COLUMN `name`,
 DROP COLUMN `db_id`,
 DROP COLUMN `track_id`,
 DROP COLUMN `comment`,
 DROP COLUMN `timestamp`,
 DROP COLUMN `query`,
 ADD COLUMN `asset_id` INT NOT NULL AFTER `folder_id`,
 ADD COLUMN `created` DATETIME NOT NULL COMMENT 'Timestamp of the original creation of row.' AFTER `asset_id`,
 ADD COLUMN `updated` DATETIME NOT NULL COMMENT 'Timestamp of the last modification of row.' AFTER `created`,
 AUTO_INCREMENT = 1;

# Alter `folder_states` table to generalize it to associate with the `cuesheets` table in addition to the `folders` table
ALTER TABLE `mm_admin`.`folder_states`  DROP PRIMARY KEY,
 CHANGE COLUMN `folder_id` `id` INTEGER NOT NULL,
 ADD COLUMN `type` ENUM('folder','cuesheet') NOT NULL DEFAULT 'folder' COMMENT 'Indicates which table, `folders` or `cuesheets`, that `id` is associated with.' AFTER `user_id`,
 ADD PRIMARY KEY(`user_id`, `type`, `id`);

# Alter `site` table to include new `css_enabled` field
ALTER TABLE `mm_admin`.`site` ADD COLUMN `css_enabled` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Flag to enable custom CSS stylesheets.' AFTER `metadata_order`;

# Create new `styles` table
-- CREATE TABLE `mm_admin`.`styles` (
--   `id` INT NOT NULL AUTO_INCREMENT,
--   `name` VARCHAR(32) NOT NULL COMMENT 'Display name of style.',
--   `bk_background` VARCHAR(128) NOT NULL,
--   PRIMARY KEY(`id`)
-- ) ENGINE = MYISAM CHARACTER SET utf8 COMMENT = 'Custom parameters for interface styling.';

# Update version fields
UPDATE `mm_admin`.`schema` SET `db_ver` = '0.6.0', `mm_ver` = '0.6.0', `sm_ver` = '0.6.0';
UPDATE `mm_cache`.`schema` SET `mm_ver` = '0.6.0';
UPDATE `mm_logs`.`schema` SET `mm_ver` = '0.6.0';