# $Id: upgrade4500_Beta103to104.sql,v 1.24 2003/12/08 23:01:20 eddieajau Exp $

# Beta 1.0.3 to 1.0.4

# rwc

UPDATE `mos_modules` SET params='registration_enabled=1' WHERE module='mod_login';
INSERT INTO `mos_modules` VALUES ('', 'Related Items', '', 9, 'left', 0, '0000-00-00 00:00:00', 1, 'mod_related_items', 0, 0,
1, '', 1);


# aje :: ACL stuff
# author - can submit and edit own work
# editor - can submit and edit all work
# publisher - can submit, edit and publish all work
# manager - restricted acces to backend

INSERT INTO `mos_usertypes` VALUES (4, 'author', '');
INSERT INTO `mos_usertypes` VALUES (5, 'publisher', '');
INSERT INTO `mos_usertypes` VALUES (6, 'manager', '');

#
# Mambo Open Source MySQL-Dump
# http://www.mamboserver.com
#
# Host: MOS Testing
# Generation Time: Nov 17, 2003 at 12:59
# Server version: 3.23.52-max-nt
# PHP Version: 4.3.2-RC1
# Database : `mos45`
# --------------------------------------------------------
#
# Table structure for table `mos_core_acl_aro`
#

CREATE TABLE `mos_core_acl_aro` (
  `aro_id` int(11) NOT NULL auto_increment,
  `section_value` varchar(240) NOT NULL default '0',
  `value` varchar(240) NOT NULL default '',
  `order_value` int(11) NOT NULL default '0',
  `name` varchar(255) NOT NULL default '',
  `hidden` int(11) NOT NULL default '0',
  PRIMARY KEY  (`aro_id`),
  UNIQUE KEY `section_value_value_aro` (`section_value`,`value`),
  UNIQUE KEY `mos_gacl_section_value_value_aro` (`section_value`,`value`),
  KEY `hidden_aro` (`hidden`),
  KEY `mos_gacl_hidden_aro` (`hidden`)
) TYPE=MyISAM;

INSERT INTO `mos_core_acl_aro`
	 (aro_id,section_value,value,order_value,name,hidden)
SELECT mos_users.id, 'users', mos_users.id, '0', mos_users.name, '0'
	FROM mos_users;

#
# Table structure for table `mos_core_acl_aro_groups`
#
CREATE TABLE `mos_core_acl_aro_groups` (
  `group_id` int(11) NOT NULL auto_increment,
  `parent_id` int(11) NOT NULL default '0',
  `name` varchar(255) NOT NULL default '',
  `lft` int(11) NOT NULL default '0',
  `rgt` int(11) NOT NULL default '0',
  PRIMARY KEY  (`group_id`),
  KEY `parent_id_aro_groups` (`parent_id`),
  KEY `mos_gacl_parent_id_aro_groups` (`parent_id`),
  KEY `mos_gacl_lft_rgt_aro_groups` (`lft`,`rgt`)
) TYPE=MyISAM;

#
# Dumping data for table `mos_core_acl_aro_groups`
#
INSERT INTO `mos_core_acl_aro_groups` VALUES (17,0,'ROOT',1,22);
INSERT INTO `mos_core_acl_aro_groups` VALUES (28,17,'USERS',2,21);
INSERT INTO `mos_core_acl_aro_groups` VALUES (29,28,'Public Frontend',3,12);
INSERT INTO `mos_core_acl_aro_groups` VALUES (18,29,'Registered',4,11);
INSERT INTO `mos_core_acl_aro_groups` VALUES (19,18,'Author',5,10);
INSERT INTO `mos_core_acl_aro_groups` VALUES (20,19,'Editor',6,9);
INSERT INTO `mos_core_acl_aro_groups` VALUES (21,20,'Publisher',7,8);
INSERT INTO `mos_core_acl_aro_groups` VALUES (30,28,'Public Backend',13,20);
INSERT INTO `mos_core_acl_aro_groups` VALUES (23,30,'Manager',14,19);
INSERT INTO `mos_core_acl_aro_groups` VALUES (24,23,'Administrator',15,18);
INSERT INTO `mos_core_acl_aro_groups` VALUES (25,24,'Super Administrator',16,17);
#
# Table structure for table `mos_core_acl_groups_aro_map`
#
CREATE TABLE `mos_core_acl_groups_aro_map` (
  `group_id` int(11) NOT NULL default '0',
  `section_value` varchar(240) NOT NULL default '',
  `aro_id` int(11) NOT NULL default '0',
  UNIQUE KEY `group_id_aro_id_groups_aro_map` (`group_id`,`section_value`,`aro_id`)
) TYPE=MyISAM;

#
# Table structure for table `mos_core_acl_aro_sections`
#
CREATE TABLE `mos_core_acl_aro_sections` (
  `section_id` int(11) NOT NULL auto_increment,
  `value` varchar(230) NOT NULL default '',
  `order_value` int(11) NOT NULL default '0',
  `name` varchar(230) NOT NULL default '',
  `hidden` int(11) NOT NULL default '0',
  PRIMARY KEY  (`section_id`),
  UNIQUE KEY `value_aro_sections` (`value`),
  UNIQUE KEY `mos_gacl_value_aro_sections` (`value`),
  KEY `hidden_aro_sections` (`hidden`),
  KEY `mos_gacl_hidden_aro_sections` (`hidden`)
) TYPE=MyISAM;

INSERT INTO mos_core_acl_aro_sections VALUES (10,'users',1,'Users',0);


INSERT INTO `mos_core_acl_groups_aro_map`
	(group_id,aro_id)
SELECT arg.group_id, mos_users.id AS aro_id
FROM mos_users
LEFT JOIN `mos_core_acl_aro_groups` AS arg ON arg.name='Super Administrator'
WHERE usertype='superadministrator';

INSERT INTO `mos_core_acl_groups_aro_map`
	(group_id,aro_id)
SELECT arg.group_id, mos_users.id AS aro_id
FROM mos_users
LEFT JOIN `mos_core_acl_aro_groups` AS arg ON arg.name='Administrator'
WHERE usertype='administrator';

INSERT INTO `mos_core_acl_groups_aro_map`
	(group_id,aro_id)
SELECT arg.group_id, mos_users.id AS aro_id
FROM mos_users
LEFT JOIN `mos_core_acl_aro_groups` AS arg ON arg.name='Editor'
WHERE usertype='editor';

INSERT INTO `mos_core_acl_groups_aro_map`
	(group_id,aro_id)
SELECT arg.group_id, mos_users.id AS aro_id
FROM mos_users
LEFT JOIN `mos_core_acl_aro_groups` AS arg ON arg.name='Registered'
WHERE usertype NOT IN ('editor','administrator','superadministrator');

UPDATE `mos_users` SET gid='25' WHERE usertype='superadministrator';
UPDATE `mos_users` SET gid='24' WHERE usertype='administrator';
UPDATE `mos_users` SET gid='20' WHERE usertype='editor';
UPDATE `mos_users` SET gid='18' WHERE usertype NOT IN ('editor','administrator','superadministrator');

#
# Add indexes to tables to improve DB performance
#
ALTER TABLE `mos_content` ADD INDEX `idx_state` (`state`);
ALTER TABLE `mos_content` ADD INDEX `idx_checkout` (`checked_out`);
ALTER TABLE `mos_content` ADD INDEX `idx_access` (`access`);
ALTER TABLE `mos_content` ADD INDEX `idx_catid` (`catid`);
ALTER TABLE `mos_content` ADD INDEX `idx_section` (`sectionid`);
ALTER TABLE `mos_content` ADD INDEX `idx_mask`(`mask`);
ALTER TABLE `mos_categories` ADD INDEX `idx_section` (`section`);
ALTER TABLE `mos_categories` ADD INDEX `idx_access` (`access`);
ALTER TABLE `mos_categories` ADD INDEX `idx_checkout` (`checked_out`);
ALTER TABLE `mos_content_frontpage` ADD INDEX `idx_contentid` (`content_id`);
ALTER TABLE `mos_users` ADD INDEX `idx_name`(`name`);
ALTER TABLE `mos_sections` ADD INDEX `idx_scope`(`scope`);

RENAME TABLE `mos_votes` to `mos_content_rating`;

ALTER TABLE `mos_content_rating` DROP `scope`;
ALTER TABLE `mos_content_rating` DROP `id`;
ALTER TABLE `mos_content_rating` DROP `votes`;
ALTER TABLE `mos_content_rating` DROP `allvotes`;
ALTER TABLE `mos_content_rating` DROP `rating`;
ALTER TABLE `mos_content_rating` ADD `rating_sum` int(11) unsigned NOT NULL default '0';
ALTER TABLE `mos_content_rating` ADD `rating_count` int(11) unsigned NOT NULL default '0';
ALTER TABLE `mos_content_rating` ADD PRIMARY KEY (`content_id`);


#
# Messaging
#
CREATE TABLE `mos_messages` (
  `message_id` int(10) unsigned NOT NULL auto_increment,
  `user_id_from` int(10) unsigned NOT NULL default '0',
  `user_id_to` int(10) unsigned NOT NULL default '0',
  `folder_id` int(10) unsigned NOT NULL default '0',
  `date_time` datetime NOT NULL default '0000-00-00 00:00:00',
  `state` int(11) NOT NULL default '0',
  `priority` int(1) unsigned NOT NULL default '0',
  `subject` varchar(230) NOT NULL default '',
  `message` text NOT NULL,
  PRIMARY KEY  (`message_id`)
) TYPE=MyISAM;

CREATE TABLE `mos_messages_cfg` (
  `user_id` int(10) unsigned NOT NULL default '0',
  `cfg_name` varchar(100) NOT NULL default '',
  `cfg_value` varchar(255) NOT NULL default '',
  UNIQUE `idx_user_var_name` (`user_id`,`cfg_name`)
) TYPE=MyISAM;


# Add field for title content
ALTER TABLE `mos_content` ADD `title_alias` VARCHAR( 100 ) NOT NULL AFTER `title` ;

INSERT INTO `mos_components` VALUES ('', 'Media Manager', 'option=com_media', 0, 0, 'option=com_media', 'Media Manager', 'com_media', 0, 'js/ThemeOffice/media.png', 1, '', '');
INSERT INTO `mos_components` VALUES ('', 'Login', 'option=com_login', 0, 0, '', '', 'com_login', 0, '', 1, '', '');