##----------------------------------------------------------------------------------#
 # MusicMiner Web Server															#
 # Name:		source/app/sql/upgrade_0-5-6_to_0-5-7.sql							#
 # Author:		Jesse J Morrow	(jesse@jessejmorrow.com)							#
 # Created:		2007-05-17															#
 # Updated:		2007-05-24															#
 # Compat:		MySQL 5																#
 # Copyright:	Copyright 2007 Soundminer Inc.										#
 # Purpose:		Upgrade the installed MusicMiner databases.							#
 # Version:		0.5.6 to 0.5.7														#
 #----------------------------------------------------------------------------------##

# Alter `users` table, add new root `folder_id`
ALTER TABLE `mm_admin`.`users` ADD COLUMN `folder_id` INT NOT NULL COMMENT 'Root folder of user\'s personal project content.' AFTER `perm_id`,
 ADD INDEX `folder_index`(`folder_id`, `id`);

# Alter `groups` table, add new root `folder_id`
ALTER TABLE `mm_admin`.`groups` ADD COLUMN `folder_id` INT NOT NULL COMMENT 'Root folder of group\'s shared project content.' AFTER `perm_id`,
 ADD INDEX `folder_index`(`folder_id`, `id`);

# Install new root folders for each existing user and relink existing folders to the new roots
UPDATE `mm_admin`.`folders` SET `folder_id` = '-1' WHERE `folder_id` = '0';
INSERT INTO `mm_admin`.`folders` (`id`, `user_id`, `folder_id`, `name`, `state`) SELECT NULL, `id`, '0', `user`, '1' FROM `mm_admin`.`users`;
UPDATE `mm_admin`.`folders` `a` LEFT JOIN `mm_admin`.`folders` `b` ON `a`.`user_id` = `b`.`user_id` SET `a`.`folder_id` = `b`.`id` WHERE `a`.`folder_id` = '-1' AND `b`.`folder_id` = '0';

# Point existing tracks and searches `folder_id` to new root folders
UPDATE `mm_admin`.`tracks` `t` LEFT JOIN `mm_admin`.`folders` `f` ON `t`.`user_id` = `f`.`user_id` SET `t`.`folder_id` = `f`.`id` WHERE `f`.`folder_id` = '0' AND `t`.`folder_id` = '0';
UPDATE `mm_admin`.`searches` `t` LEFT JOIN `mm_admin`.`folders` `f` ON `t`.`user_id` = `f`.`user_id` SET `t`.`folder_id` = `f`.`id` WHERE `f`.`folder_id` = '0' AND `t`.`folder_id` = '0';

# Point existing user's folder_id to new root folders
UPDATE `mm_admin`.`users` `u` LEFT JOIN `mm_admin`.`folders` `f` ON `u`.`id` = `f`.`user_id` SET `u`.`folder_id` = `f`.`id` WHERE `f`.`folder_id` = '0';

# Install new root folders for each existing group and point existing groups to new root folders
INSERT INTO `mm_admin`.`folders` (`id`, `user_id`, `folder_id`, `name`, `state`) SELECT NULL, -`id`, '0', `name`, '1' FROM `mm_admin`.`groups`;
UPDATE `mm_admin`.`groups` `g` LEFT JOIN `mm_admin`.`folders` `f` ON `g`.`id` = -`f`.`user_id` SET `g`.`folder_id` = `f`.`id` WHERE `f`.`folder_id` = '0';

# Create `folder_states` table
CREATE TABLE `mm_admin`.`folder_states` (
  `user_id` INT NOT NULL,
  `folder_id` INT NOT NULL,
  `state` ENUM('open','closed','hidden') NOT NULL,
  PRIMARY KEY(`user_id`, `folder_id`)
) ENGINE = MYISAM CHARACTER SET utf8 COMMENT = 'Many to one relationship between users and folder state (i.e. open, closed, etc)';

# Copy folder state from `folders` table to new `folder_states` table
INSERT INTO `mm_admin`.`folder_states` (`folder_id`, `user_id`, `state`) SELECT `id`, `user_id`, IF(`state`, 'open', 'closed') FROM `mm_admin`.`folders`;

# Alter `folders`, `tracks`, and `searches` table, drop the now unused `user_id` and `state` column
ALTER TABLE `mm_admin`.`folders` DROP COLUMN `state`, DROP COLUMN `user_id`;
ALTER TABLE `mm_admin`.`tracks` DROP COLUMN `user_id`;
ALTER TABLE `mm_admin`.`searches` DROP COLUMN `user_id`;

# Alter `databases` table, add `perm_columns` and `group_columns` columns
ALTER TABLE `mm_admin`.`databases` ADD COLUMN `perm_columns` VARCHAR(255) NOT NULL DEFAULT '[\"Library\",\"CDTitle\",\"Filename\"]' AFTER `proxy_id`,
  ADD COLUMN `group_columns` VARCHAR(255) NOT NULL DEFAULT '[\"Library\",\"CDTitle\",\"TrackTitle\"]' AFTER `perm_columns`;

# Alter `perms` table, add `file_exts` permission and set for Base, Administrators, and Admin
ALTER TABLE `mm_admin`.`perms` ADD COLUMN `file_exts` TINYINT(1) DEFAULT NULL AFTER `select_db`;
UPDATE `mm_admin`.`perms` SET `file_exts` = '1' WHERE `id` IN ('1', '3', '8');

# Alter `site` table, add `metadata_order` field
ALTER TABLE `mm_admin`.`site` ADD COLUMN `metadata_order` VARCHAR(5000) NOT NULL DEFAULT '[\"Filename\",\"TrackTitle\",\"CDTitle\",\"Library\",\"Database\",\"Category\",\"Composer\",\"Publisher\",\"Time\",\"Description\"]' AFTER `home_url`;

# Fix JSON single-quotes to double-quotes in various tables
UPDATE `mm_admin`.`users` SET `columns` = REPLACE(`columns`, '\'', '"'), `browses` = REPLACE(`browses`, '\'', '"'), `hash` = REPLACE(`hash`, '\'', '"');
UPDATE `mm_admin`.`perms` SET `columns` = REPLACE(`columns`, '\'', '"'), `assets` = REPLACE(`assets`, '\'', '"'), `sort_tracks` = REPLACE(`sort_tracks`, '\'', '"'), `sort_albums` = REPLACE(`sort_albums`, '\'', '"');

UPDATE `mm_admin`.`perms` SET `sort_tracks` = '{"Relevance":1,"Library":0,"Volume":1,"Track":0}', `sort_albums` = '{"Library":0,"Volume":1}';

UPDATE `mm_admin`.`schema` SET `db_ver` = '0.5.7', `mm_ver` = '0.5.7', `sm_ver` = '0.5.7';
UPDATE `mm_cache`.`schema` SET `mm_ver` = '0.5.7';
UPDATE `mm_logs`.`schema` SET `mm_ver` = '0.5.7';