#!/usr/bin/perl -w
# ==================================================================
# Gossamer Forum - Advanced web community
#
#   Website  : http://gossamer-threads.com/
#   Support  : http://gossamer-threads.com/scripts/support/
#   Revision : $Id: repair_post_table.pl,v 1.1.2.2 2003/05/24 05:41:24 jagerman Exp $
#
# Copyright (c) 2003 Gossamer Threads Inc.  All Rights Reserved.
# Redistribution in part or in whole strictly prohibited. Please
# see LICENSE file for full details.
# ==================================================================
#
# This script goes through the Post table, looking for broken threads.  This falls
# into two categories:
#  - threads without all required tree records
#  - broken reply counts
# The first one is required for the second one - but luckily, both can be
# completely rebuilt as long as post_id, post_root_id, and post_father_id, and
# post_depth are intact.  Any broken threads found are automatically repaired.
#
# Note that this could take quite a while on a very large forum.

use lib '..';
use strict;

use GForum qw/$DB/;
use GT::SQL::Tree; # Don't remove me, or the Post/tree join breaks - it stops recognizing $Anc_Join
GForum::init("..");
$|++;

my $post = $DB->table("Post");
my $tree = $DB->table("Post_tree");
my $pt = $DB->table("Post", "Post_tree");

# SELECT post_id, post_depth, COUNT(tree_id_fk)
# FROM gforum_Post LEFT JOIN gforum_Post_tree ON tree_id_fk = post_id
# GROUP BY post_id, post_depth
# HAVING COUNT(tree_id_fk) != post_depth
# ORDER BY post_depth;
print "Scanning for post tree inconsistencies...\n\n";
$pt->select_options('GROUP BY post_id, post_depth', 'HAVING COUNT(tree_id_fk) != post_depth', 'ORDER BY post_depth');
my $sth = $pt->select(left_join => 'post_id', 'post_depth', 'COUNT(tree_id_fk)')
    or die $GT::SQL::error;
my @broken_trees;
while (my ($post_id) = $sth->fetchrow) {
    push @broken_trees, $post_id;
}

my (%father, %depth);
$sth = $post->select(qw/post_id post_father_id post_depth/  => { post_id => \@broken_trees });
while (my ($id, $fid, $depth) = $sth->fetchrow) {
    $father{$id} = $fid;
    $depth{$id} = $depth;
}

if (@broken_trees) {
    for my $post (@broken_trees) {
        my $father = $father{$post};
        next unless $father;
        my $sth = $tree->select('tree_anc_id_fk', 'tree_dist' => { tree_id_fk => $father });
        my @tree_inserts = [$post, $father, 1];
        while (my ($anc, $dist) = $sth->fetchrow) {
            push @tree_inserts, [$post, $anc, $dist + 1];
        }
        $tree->delete({ tree_id_fk => $post });
        $tree->insert_multiple([qw/tree_id_fk tree_anc_id_fk tree_dist/], @tree_inserts)
            or die "Could not insert_multiple: $GT::SQL::error";
    }
    print "Tree inconsistencies found and corrected!\n\n";
}
else {
    print "No post tree inconsistencies found!\n\n";
}


print "Scanning for broken post reply counts...\n\n";

# SELECT post_id, COUNT(tree_anc_id_fk), post_replies
# FROM gforum_Post LEFT JOIN gforum_Post_tree ON post_id = tree_anc_id_fk
# GROUP BY post_id
# HAVING post_replies != COUNT(tree_anc_id_fk);
my %change;
{
    require GT::SQL::Tree;
    $GT::SQL::Tree::Relation::Anc_Join = 1;
    $pt->select_options('GROUP BY post_id, post_replies', 'HAVING post_replies != COUNT(tree_anc_id_fk)');
    my $sth = $pt->select('post_id', 'COUNT(tree_anc_id_fk)', 'post_replies');
    while (my ($pid, $real, $current) = $sth->fetchrow) {
        push @{$change{$real - $current}}, $pid;
    }
    $GT::SQL::Tree::Relation::Anc_Join = 0;
}

if (keys %change) {
    for (sort { $a <=> $b } keys %change) {
        $post->update({ post_replies => \"post_replies + $_" }, { post_id => $change{$_} });
    }
    print "Broken counts found and repaired!\n\n";
}
else {
    print "No broken counts found!\n\n";
}