-- Kunena 2.0

ALTER TABLE	`#__kunena_users`			MODIFY `avatar` varchar(255) NULL; -- Also new in Kunena 1.6.5
ALTER TABLE	`#__kunena_users`			ADD `thankyou` int(11) NULL default '0' AFTER `showOnline`;

ALTER TABLE	`#__kunena_user_categories`	DROP INDEX `category_id`;
ALTER TABLE `#__kunena_user_categories`	ADD `role` tinyint(4) NOT NULL default '0' AFTER `category_id`;
ALTER TABLE `#__kunena_user_categories`	ADD KEY `category_subscribed` (category_id,subscribed);
ALTER TABLE `#__kunena_user_categories`	ADD KEY `role` (role);


INSERT INTO `#__kunena_user_categories` (user_id, category_id, role)
	SELECT u.userid AS user_id, IF(m.catid>0,m.catid,0) AS category_id, 1 AS role
		FROM `#__kunena_users` AS u
		LEFT JOIN `#__kunena_moderation` AS m ON u.userid=m.userid
		LEFT JOIN `#__kunena_categories` AS c ON m.catid=c.id
		WHERE u.moderator='1' AND (m.catid IS NULL OR c.moderated='1')
	ON DUPLICATE KEY UPDATE role=1;
