DELIMITER $$

DROP PROCEDURE IF EXISTS `uspUpdateCounts`$$

CREATE PROCEDURE `uspUpdateCounts`()
BEGIN
       /* 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;
    END$$

DELIMITER ;