import_database.sql

-- fetch data from emp.users into gazelle.users_main

insert into `gazelle`.`users_main` (`ID`, `Username`, `Email`, `PassHash`, `Secret`, `Title`, `PermissionID`, `Enabled`, `Uploaded`, `Downloaded`, `LastLogin`, `LastAccess`, `IP`, `torrent_pass`, `Credits`)
SELECT `Id`, `username`, `email`, `passhash`, `secret`, `title`, '2', '1', `uploaded`, `downloaded`, from_unixtime(`last_login`), from_unixtime(`last_access`), `Ip`, `passkey`, `Bonuspoints` FROM emp.users;

-- fetch data from emp.users into gazelle.users_info

insert into `gazelle`.`users_info` (`UserID`, `StyleID`, `Avatar`, `JoinDate`, `Inviter`, `AdminComment`, `Info`)
select `Id`, '1', `avatar`, from_unixtime(`added`), '0', `modcomment`, `info` from `emp`.`users`;

-- set gazelle.users_main.enabled to 1 where emp.users.enabled='yes'

UPDATE `gazelle`.`users_main`
SET `enabled` = '1'
WHERE EXISTS (SELECT 1 from `emp`.`users` WHERE `emp`.`users`.`id`=`gazelle`.`users_main`.`id` and `enabled`='yes');

-- lets auto confirm users stuck in pending status or we'll lose them

UPDATE `gazelle`.`users_main`
SET `enabled` = '1'
WHERE EXISTS (SELECT 1 from `emp`.`users` WHERE `emp`.`users`.`id`=`gazelle`.`users_main`.`id` and `status`='pending' and `enabled`='no');

-- disable users that are confirmed and enable=0, banned users.
UPDATE `gazelle`.`users_main`
SET `enabled` = '2'
WHERE EXISTS (SELECT 1 from `emp`.`users` WHERE `emp`.`users`.`id`=`gazelle`.`users_main`.`id` and `status`='confirmed' and `enabled`='no');

-- set the correct class for the user

-- Apprentice
UPDATE `gazelle`.`users_main`
SET `PermissionID` = '2'
WHERE EXISTS (SELECT 1 from `emp`.`users` WHERE `emp`.`users`.`id`=`gazelle`.`users_main`.`id` and `class`='0');

-- Good Perv
UPDATE `gazelle`.`users_main`
SET `PermissionID` = '4'
WHERE EXISTS (SELECT 1 from `emp`.`users` WHERE `emp`.`users`.`id`=`gazelle`.`users_main`.`id` and `class`='1');

-- Sextreme Perv
UPDATE `gazelle`.`users_main`
SET `PermissionID` = '5'
WHERE EXISTS (SELECT 1 from `emp`.`users` WHERE `emp`.`users`.`id`=`gazelle`.`users_main`.`id` and `class`='2');

-- Smut Peddler
UPDATE `gazelle`.`users_main`
SET `PermissionID` = '6'
WHERE EXISTS (SELECT 1 from `emp`.`users` WHERE `emp`.`users`.`id`=`gazelle`.`users_main`.`id` and `class`='3');

--  MODS
UPDATE `gazelle`.`users_main`
SET `PermissionID` = '11'
WHERE EXISTS (SELECT 1 from `emp`.`users` WHERE `emp`.`users`.`id`=`gazelle`.`users_main`.`id` and `class`='4');

-- ADMINS
UPDATE `gazelle`.`users_main`
SET `PermissionID` = '1'
WHERE EXISTS (SELECT 1 from `emp`.`users` WHERE `emp`.`users`.`id`=`gazelle`.`users_main`.`id` and `class`='5');

-- SYSOP
UPDATE `gazelle`.`users_main`
SET `PermissionID` = '15'
WHERE EXISTS (SELECT 1 from `emp`.`users` WHERE `emp`.`users`.`id`=`gazelle`.`users_main`.`id` and `class`='6');

INSERT INTO `gazelle`.`invite_tree` (`UserID`, `InviterID`, `TreePosition`, `TreeID`, `TreeLevel`) VALUES ('0', '0', '1', '0', '1');

-- Import the forum
insert into `gazelle`.`forums_posts` (`ID`, `TopicID`, `AuthorID`, `AddedTime`, `Body`, `EditedUserID`, `EditedTime`)
select `id`, `topicid`, `userid`, from_unixtime(`added`), `body`, `editedby`, from_unixtime(`editedat`) from `emp`.`posts`;

--

INSERT INTO gazelle.forums_topics (ID, Title, AuthorID, IsLocked, IsSticky, ForumID, NumPosts, LastPostID, LastPostTime, LastPostAuthorID)
SELECT 
id, 
subject, 
userid, 
0, 
if(sticky='yes', '1', '0') as sticky, 
forumid,
(select count(*) as count from emp.posts where emp.posts.topicid=emp.topics.id) as numposts,
lastpost, 
(select from_unixtime(added) as added from emp.posts where emp.posts.id=emp.topics.id) as time,
(select userid from emp.posts where emp.posts.id=emp.topics.lastpost) as authorid
FROM
emp.topics;

--

insert into gazelle.forums (ID, CategoryID, Sort, Name, Description, NumTopics, NumPosts, LastPostID, LastPostAuthorID, LastPostTopicID, LastPostTime)
select 
id, 
1,
sort, 
Name, 
description, 
topiccount, 
postcount,

(select p.id from emp.topics as t
inner join emp.posts as p on t.id=p.topicid
where t.forumid = emp.forums.id
order by p.added desc limit 1) as LastPostId,

(select p.userid from emp.topics as t
inner join emp.posts as p on t.id=p.topicid
where t.forumid = emp.forums.id
order by p.added desc limit 1) as LastPostAuthorID,

(select p.topicid from emp.topics as t
inner join emp.posts as p on t.id=p.topicid
where t.forumid = emp.forums.id
order by p.added desc limit 1) as LastPostTopicID,

(select from_unixtime(p.added) from emp.topics as t
inner join emp.posts as p on t.id=p.topicid
where t.forumid = emp.forums.id
order by p.added desc limit 1) as LastPostTime

from emp.forums;

--

insert into gazelle.forums_last_read_topics (UserID, TopicID, PostID)
select userid, topicid, lastpostread
from emp.readposts
group by userid, topicid;

-- Import PM's
insert into gazelle.pm_conversations (ID, Subject)
select id, if(subject<>'', subject, 'no subject') as subject from emp.messages;

insert into gazelle.pm_messages (ConvID, SentDate, SenderID, Body)
select id, from_unixtime(added), sender, msg from emp.messages;

insert into gazelle.pm_conversations_users (UserID, ConvID, InInbox, InSentbox, SentDate, ReceivedDate, UnRead)
select sender, id, 0, 1, from_unixtime(added), from_unixtime(added), 0 from emp.messages where sender > 0 and sender <> receiver;

insert into gazelle.pm_conversations_users (UserID, ConvID, InInbox, InSentbox, SentDate, ReceivedDate, UnRead)
select receiver, id, 1, 0, from_unixtime(added), from_unixtime(added), 0 from emp.messages where sender > 0 and sender <> receiver;