use dbname;  

/* update forum topic counts */

create temporary table f_t_count (
    forum_id int(11),
    t_count int (11)
);

update forum_forum set f_topics = 0;

insert into f_t_count
  select forum_id, count(forum_id) from forum_topics where t_status<=1 group by forum_id ;

update forum_forum f inner join f_t_count t on t.forum_id=f.forum_id
  set f_topics = t_count;

/* update forum archived topics count  */

delete from f_t_count;

update forum_forum set f_a_topics = 0;

insert into f_t_count
  select forum_id, count(forum_id) from forum_a_topics where t_status<=1 group by forum_id ;

update forum_forum f inner join f_t_count t on t.forum_id=f.forum_id
  set f_a_topics = t_count;

drop table f_t_count;

/* update topic replies counts now */

create temporary table t_r_count (
   topic_id int(11),
   r_count int(11)
);

insert into t_r_count 
  select topic_id, count(reply_id) from forum_reply where r_status<=1 group by topic_id ;

update forum_topics set t_replies = 0 where t_status<=1;

update forum_topics t inner join t_r_count tr on t.topic_id = tr.topic_id
   set t_replies = r_count where t.t_status<=1;

/* update archived topics replies count now */

delete from t_r_count;

insert into t_r_count 
  select topic_id, count(reply_id) from forum_a_reply group by topic_id;

update forum_a_topics set t_replies = 0;

update forum_a_topics t inner join t_r_count tr on t.topic_id = tr.topic_id
   set t_replies = r_count;

drop table t_r_count;

/* update last post date */ 

create temporary table t_post_data (
   topic_id int(11),
   last_post varchar(50)
);

insert into t_post_data
  select topic_id, max(r_date) from forum_reply where r_status<=1 group by topic_id;

update forum_topics t inner join t_post_data tp  on t.topic_id = tp.topic_id
  set t.t_last_post = last_post;

delete from t_post_data;

update forum_topics set t_last_post=t_date, t_last_post_author=t_author, t_last_post_reply_id=0 where t_replies=0;

/* update last post date */ 

insert into t_post_data
  select topic_id, max(r_date) from forum_a_reply group by topic_id;

update forum_a_topics t inner join t_post_data tp  on t.topic_id = tp.topic_id
  set t.t_last_post = tp.last_post;

update forum_a_topics set t_last_post=t_date, t_last_post_author=t_author,t_last_post_reply_id=0 where t_replies=0;

drop table t_post_data;

/* now find the reply id for the posts that have more than 0 replies */

create temporary table t_l_reply_id (
   topic_id int(11),
   reply_id int(11)
);

insert into t_l_reply_id
  select t.topic_id, max(reply_id) from forum_reply r inner join forum_topics t on r.topic_id=t.topic_id
       where t.t_last_post=r_date and t_status<=1 group by t.topic_id;

update forum_topics t inner join t_l_reply_id tl on tl.topic_id = t.topic_id
  set t.t_last_post_reply_id = tl.reply_id;

delete from t_l_reply_id;

/* now find the reply id for the posts that have more than 0 replies in archived topics */

insert into t_l_reply_id
  select t.topic_id, max(reply_id) from forum_a_reply r inner join forum_a_topics t on r.topic_id=t.topic_id
       where t.t_last_post=r_date group by t.topic_id;

update forum_a_topics t inner join t_l_reply_id tl on tl.topic_id = t.topic_id
  set t.t_last_post_reply_id = tl.reply_id;

drop table t_l_reply_id;

/* now found the author id for the last reply */

create temporary table t_l_reply_author(
   topic_id int(11),
   author int(11)
);

insert into t_l_reply_author
  select t.topic_id, r.r_author from forum_topics t inner join forum_reply r on t.topic_id=r.topic_id
    where t.t_last_post_reply_id = r.reply_id and t_status<=1;

update forum_topics t inner join t_l_reply_author tl on tl.topic_id = t.topic_id
  set t.t_last_post_author = tl.author;

delete from t_l_reply_author;

insert into t_l_reply_author
  select t.topic_id, r.r_author from forum_a_topics t inner join forum_a_reply r on t.topic_id=r.topic_id
    where t.t_last_post_reply_id = r.reply_id;

update forum_a_topics t inner join t_l_reply_author tl on tl.topic_id = t.topic_id
  set t.t_last_poster = tl.author;

drop table t_l_reply_author;

/* now to current step 3, unmoderated replies per topic */


/* update topic replies counts now */

create temporary table t_r_count (
   topic_id int(11),
   r_count int(11)
);

insert into t_r_count 
  select topic_id, count(reply_id) from forum_reply where r_status=2 or r_status=3 group by topic_id ;

update forum_topics set t_ureplies = 0 where t_status<=1;

update forum_topics t inner join t_r_count tr on t.topic_id = tr.topic_id
   set t_ureplies = r_count where t.t_status<=1;

drop table t_r_count;

/* now to step 4 */

/* count replies per forum */

create temporary table f_r_count (
    forum_id int(11),
    r_count int (11)
);

insert into f_r_count 
  select r.forum_id, count(reply_id) from forum_topics t inner join forum_reply r on t.topic_id=r.topic_id
     where t.t_status<=1 and r_status<=1 group by r.forum_id;

update forum_forum set f_count=f_topics where f_type<>1;

update forum_forum f inner join f_r_count fr on f.forum_id = fr.forum_id
   set f.f_count = f.f_count + fr.r_count;

delete from f_r_count;

insert into f_r_count 
  select r.forum_id, count(reply_id) from forum_a_topics t inner join forum_a_reply r on t.topic_id=r.topic_id group by r.forum_id;

update forum_forum set f_a_count=f_a_topics where f_type<>1;

update forum_forum f inner join f_r_count fr on f.forum_id = fr.forum_id
   set f.f_a_count = f.f_a_count + fr.r_count;

drop table f_r_count;

/* update last post per forum */

create temporary table f_post_data (
   forum_id int(11),
   last_post varchar(50)
);


insert into f_post_data
  select forum_id, max(t_last_post) from forum_topics where t_status<=1 group by forum_id;

update forum_forum f inner join f_post_data fp  on f.forum_id = fp.forum_id
  set f.f_last_post = last_post;

drop table f_post_data;

/* update last post topic_id */


create temporary table f_topic_id (
   forum_id int(11),
   topic_id int(11)
);

insert into f_topic_id
  select f.forum_id, max(t.topic_id) from forum_forum f inner join forum_topics t on f.forum_id=t.forum_id
   where f.f_last_post = t.t_last_post and t.t_status<=1 group by f.forum_id;

update forum_forum f inner join f_topic_id ft on f.forum_id = ft.forum_id
   set f.f_last_post_topic_id = ft.topic_id;

drop table f_topic_id;

/* now update for author id */

update forum_forum f inner join forum_topics t on f.f_last_post_topic_id=t.topic_id
set f.f_last_post_author=t.t_last_post_author, f.f_last_post_reply_id=t.t_last_post_reply_id;

create temporary table t_topics (
   count_id int(11),
   topics int(11),
   a_topics int(11)
);

insert into t_topics
  select 1, sum(f_topics), sum(f_a_topics) from forum_forum;

update forum_totals ft inner join t_topics tt on ft.count_id=tt.count_id
  set ft.t_count = tt.topics, ft.t_a_count = a_topics;

drop table t_topics;

create temporary table t_topics (
   count_id int(11),
   topics int(11),
   a_topics int(11),
   posts int(11),
   a_posts int(11)
);

insert into t_topics
  select 1, sum(f_topics), sum(f_a_topics),sum(f_count),sum(f_a_count) from forum_forum where f_type<>1;

update forum_totals ft inner join t_topics tt on ft.count_id=tt.count_id
  set ft.t_count = tt.topics, ft.t_a_count = a_topics, ft.p_count=tt.posts, ft.p_a_count=tt.a_posts;

drop table t_topics;

create temporary table t_members (
   count_id int(11),
   members int(11)
);
 
insert into t_members
  select 1, count(member_id) from forum_members;

update forum_totals ft inner join t_members tm on ft.count_id=tm.count_id
  set ft.u_count = tm.members; 

drop table t_members;