##----------------------------------------------------------------------------------#
 # MusicMiner Web Server															#
 # Name:		source/app/sql/upgrade_0-6-5_to_0-7-0.sql							#
 # Author:		Jesse J Morrow	(jesse@jessejmorrow.com)							#
 # Created:		2007-06-26															#
 # Updated:		2007-07-31															#
 # Compat:		MySQL 5																#
 # Copyright:	Copyright 2007 Soundminer Inc.										#
 # Purpose:		Upgrade the installed MusicMiner databases.							#
 # Version:		0.6.5 to 0.7.0														#
 #----------------------------------------------------------------------------------##

# Create `reports` table (drop previous)
CREATE TABLE `mm_admin`.`reports` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(64) NOT NULL default '' COMMENT 'Report title and template name.',
  `description` varchar(255) NOT NULL default '' COMMENT 'Report description.',
  `time` enum('cday','cweek','cmonth','cyear','lday','lweek','lmonth','lyear','all','custom') NOT NULL default 'cday' COMMENT 'Time period covered by report.',
  `begin` datetime default '0000-00-00 00:00:00' COMMENT 'Start of custom time period.',
  `end` datetime default '0000-00-00 00:00:00' COMMENT 'End of custom time period.',
  `users` int(11) default '0',
  `tables` varchar(10000) NOT NULL default '' COMMENT 'JSON array of structures representing report tables.',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

# Add default report types
INSERT INTO `mm_admin`.`reports` (`id`,`title`,`description`,`time`,`begin`,`end`,`users`,`tables`) VALUES ('1','Activity Log','Chronological log of a user\'s activity.','all',NULL,NULL,NULL,'[{\"type\":\"detail\",\"title\":\"\",\"events\":[\"login\",\"logout\",\"search\",\"audition\",\"download\",\"error\"],\"groups\":[[\"Users\",\"user\",\"asc\",0,0],[null,\"date\",\"asc\",0,0]],\"fields\":[[null,\"time\",\"\",1,0,0,0,0],[null,\"event\",\"\",2,0,0,0,0],[null,\"info\",\"\",3,0,0,0,0]],\"limit\":null,\"grand\":null}]');
INSERT INTO `mm_admin`.`reports` (`id`,`title`,`description`,`time`,`begin`,`end`,`users`,`tables`) VALUES ('2','Search Summary','','all','0000-00-00 00:00:00','0000-00-00 00:00:00',NULL,'[{\"type\":\"summary\",\"title\":\"Most Popular\",\"events\":[\"search\"],\"limit\":null,\"grand\":true,\"groups\":[[\"Database(s)\",\"s_databases\",null,true,true],[\"Search Query\",\"s_query\",null,true,true]],\"fields\":[[\"# Searches\",\"id\",\"desc\",null,\"count\"],[\"# Users\",\"user\",\"desc\",null,\"dcount\"],[\"Min Found\",\"s_total\",null,null,\"min\"],[\"Max Found\",\"s_total\",null,null,\"max\"],[\"Avg Found\",\"s_total\",null,null,\"avg\"]]},{\"type\":\"summary\",\"title\":\"Fewest Results\",\"events\":[\"search\"],\"limit\":null,\"grand\":true,\"groups\":[[\"Database(s)\",\"s_databases\",null,true,true],[\"Search Query\",\"s_query\",null,true,true]],\"fields\":[[\"# Searches\",\"id\",\"desc\",2,\"count\"],[\"# Users\",\"user\",\"desc\",3,\"dcount\"],[\"Min Found\",\"s_total\",\"asc\",0,\"min\"],[\"Max Found\",\"s_total\",null,null,\"max\"],[\"Avg Found\",\"s_total\",\"asc\",1,\"avg\"]]},{\"type\":\"detail\",\"title\":\"Failed Searches By User\",\"events\":[\"search\"],\"limit\":null,\"grand\":true,\"groups\":[[\"User\",\"user\",\"asc\",false,false]],\"fields\":[[\"Time\",\"datetime\",\"asc\",null,false,false,false,false],[\"Database(s)\",\"s_databases\",\"asc\",null,false,false,false,false],[\"Query\",\"s_query\",\"asc\",null,false,false,false,false],[\"Results\",\"s_total\",\"asc\",2,false,false,false,false]],\"where\":[[\"s_total\",\"=\",\"0\"]]}]');
INSERT INTO `mm_admin`.`reports` (`id`,`title`,`description`,`time`,`begin`,`end`,`users`,`tables`) VALUES ('3','Download Summary','','all',NULL,NULL,NULL,'[{\"type\":\"summary\",\"title\":\"Top Categories\",\"events\":[\"download\"],\"groups\":[[\"Category\",\"a_category\",\"asc\",1,1]],\"fields\":[[\"# Downloads\",\"id\",\"desc\",null,\"count\"],[\"# Assets\",\"a_id\",\"desc\",null,\"dcount\"]],\"limit\":null,\"grand\":null},{\"type\":\"summary\",\"title\":\"Top Libraries\",\"events\":[\"download\"],\"groups\":[[\"Library\",\"a_library\",\"asc\",1,1]],\"fields\":[[\"# Downloads\",\"id\",\"desc\",null,\"count\"],[\"# Assets\",\"a_id\",\"desc\",null,\"dcount\"]],\"limit\":null,\"grand\":null},{\"type\":\"summary\",\"title\":\"Top Albums\",\"events\":[\"download\"],\"groups\":[[\"Album\",\"a_cdtitle\",\"asc\",1,1]],\"fields\":[[\"# Downloads\",\"id\",\"desc\",null,\"count\"],[\"# Assets\",\"a_id\",\"desc\",null,\"dcount\"]],\"limit\":null,\"grand\":null},{\"type\":\"summary\",\"title\":\"Top Users\",\"events\":[\"download\"],\"groups\":[[\"User\",\"user\",\"asc\",1,1]],\"fields\":[[\"# Downloads\",\"id\",\"desc\",null,\"count\"],[\"# Assets\",\"a_id\",\"desc\",null,\"dcount\"]],\"limit\":null,\"grand\":null}]');
INSERT INTO `mm_admin`.`reports` (`id`,`title`,`description`,`time`,`begin`,`end`,`users`,`tables`) VALUES ('4','Download Log','Detailed log of downloads grouped by user.','all',NULL,NULL,NULL,'[{\"type\":\"detail\",\"title\":\"\",\"events\":[\"download\"],\"groups\":[[\"User\",\"user\",\"asc\",0,1]],\"fields\":[[null,\"datetime\",\"asc\",null,0,0,0,0],[null,\"host_ip\",\"\",null,0,0,0,0],[null,\"a_library\",\"\",null,0,0,0,0],[null,\"a_cdtitle\",\"\",null,0,0,0,0],[\"Type\",\"d_filetype\",\"\",null,0,0,0,0],[null,\"d_filename\",\"\",null,0,0,0,0],[\"Size\",\"d_filebytes\",\"\",null,1,0,0,0]],\"limit\":null,\"grand\":null}]');

# Create log tables in `mm_admin`

CREATE TABLE `mm_admin`.`log` (
  `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Unique sequence event ID.',
  `time` datetime NOT NULL COMMENT 'Date/time of event occurrence.',
  `event` enum('login','logout','search','audition','download','error') NOT NULL COMMENT 'The event that occurred and indicates which log tables contain further info.',
  `user_id` int(11) default NULL COMMENT 'ID of logged in user causing the event.',
  `group_id` int(11) default NULL COMMENT 'ID of logged in user''s group causing the event.',
  `role_id` int(11) default NULL COMMENT 'ID of logged in user''s role causing the event.',
  `host_id` int(11) NOT NULL COMMENT 'ID of client machine accessing the server during the event.',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `mm_admin`.`log_logins` (
  `log_id` int(11) NOT NULL default '0',
  `login_id` int(11) default NULL COMMENT 'The matching logout, for logins, or login, for logouts, event ID.',
  `method` enum('user','auto') NOT NULL COMMENT 'The method or reason for the login or logout.',
  PRIMARY KEY  (`log_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `mm_admin`.`log_searches` (
  `log_id` int(10) unsigned NOT NULL,
  `query_id` int(11) NOT NULL default '0',
  `lock` tinyint(1) NOT NULL,
  `text` text NOT NULL,
  `total` int(11) NOT NULL,
  PRIMARY KEY  (`log_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `mm_admin`.`log_downloads` (
  `log_id` int(11) NOT NULL,
  `asset_id` int(11) NOT NULL,
  `file_path` varchar(3000) NOT NULL,
  `file_size` int(10) unsigned NOT NULL,
  `file_type` varchar(255) NOT NULL,
  `completed` tinyint(1) NOT NULL,
  PRIMARY KEY  (`log_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `mm_admin`.`hosts` (
  `id` int(11) NOT NULL auto_increment,
  `ip` varchar(64) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `index` (`ip`,`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `mm_admin`.`queries` (
  `id` int(11) NOT NULL auto_increment,
  `databases` varchar(512) NOT NULL,
  `query` varchar(16383) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

# Drop now unused `mm_logs` database
DROP DATABASE IF EXISTS `mm_logs`;

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