##----------------------------------------------------------------------------------#
 # MusicMiner Web Server															#
 # Name:		source/app/sql/upgrade_1-0-6_to_1-1-0.sql							#
 # Author:		Jesse J Morrow	(jesse@jessejmorrow.com)							#
 # Created:		2008-09-09															#
 # Updated:		2008-12-02															#
 # Compat:		MySQL 5																#
 # Copyright:	Copyright 2007 Soundminer Inc.										#
 # Purpose:		Upgrade the installed MusicMiner databases.							#
 # Version:		1.0.6 to 1.1.0														#
 #----------------------------------------------------------------------------------##

# Add `cue_columns` to `site` table
ALTER TABLE `mm_admin`.`site`
 ADD COLUMN `cue_columns` TEXT NOT NULL COMMENT 'Json array of columns to include in cuesheets.' AFTER `login_body2`,
 ADD COLUMN `cue_pdf` ENUM('table','vertical') NOT NULL DEFAULT 'table' COMMENT 'Cuesheet PDF report format.' AFTER `cue_columns`,
 ADD COLUMN `cue_renames` TEXT NOT NULL COMMENT 'Cuesheet field rename mapping.' AFTER `cue_pdf`,
 ADD COLUMN `cue_top` float NOT NULL COMMENT 'Cuesheet PDF top margin in mm.' AFTER `cue_renames`,
 ADD COLUMN `cue_bottom` float NOT NULL COMMENT 'Cuesheet PDF bottom margin in mm.' AFTER `cue_top`,
 ADD COLUMN `cue_left` float NOT NULL COMMENT 'Cuesheet PDF left margin in mm.' AFTER `cue_bottom`,
 ADD COLUMN `cue_right` float NOT NULL COMMENT 'Cuesheet PDF right margin in mm.' AFTER `cue_left`,
 ADD COLUMN `cue_header` float NOT NULL COMMENT 'Cuesheet PDF header offset in mm.' AFTER `cue_right`,
 ADD COLUMN `cue_footer` float NOT NULL COMMENT 'Cuesheet PDF footer offset in mm.' AFTER `cue_header`;
UPDATE `mm_admin`.`site` SET
 `cue_columns` = '["CueNo", "TrackTitle", "Composer", "Publisher", "CueUsage", "CueTime"]',
 `cue_renames` = '{}',
 `cue_top` = '10',
 `cue_bottom` = '15',
 `cue_left` = '10',
 `cue_right` = '10',
 `cue_header` = '5',
 `cue_footer` = '5';

# Add cue columns to `columns` table
ALTER TABLE `mm_admin`.`columns` ADD COLUMN `context` ENUM('metadata','cuesheet') NOT NULL DEFAULT 'metadata' COMMENT 'Indicates the application context where this column is valid.' AFTER `browse`;
INSERT INTO `mm_admin`.`columns` (`id`, `display`, `active`, `browse`, `context`) VALUES('CueNo', 'Cue #', 1, 0, 'cuesheet'), ('CueUsage', 'Usage', 1, 0, 'cuesheet'), ('CueTime', 'CueTime', 1, 0, 'cuesheet');

# Add `isci` column to `playlists` table
ALTER TABLE `mm_admin`.`playlists` ADD COLUMN `isci` VARCHAR(14) DEFAULT NULL COMMENT 'Commercial spot ISCI Code or Ad-ID.' AFTER `director`;

# Update version fields
UPDATE `mm_admin`.`schema` SET `db_ver` = '1.1.0';