USE <font color="red">dbname</font id="red">;  

/* 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;