#!/usr/bin/perl -w
# jabberdeluser - delete user from jabberd2 MySQL database
# Status: Works For Me (TM)
# Written by Filip Rembialkowski (mailto:plk.zuber@gmail.com)
# This code is placed by the author in public domain

use strict;

use CGI qw/:standard -no_xhtml/;
use DBI;
use DBD::mysql;

$|=1;

my $title="Jabber Admin at " . `hostname -f` . " -- delete user";

my $realm = 'jabberserver.sample.xx';

my $mysql_server='localhost';
my $mysql_database='jabberd2';
my $mysql_user='jabberd2';
my $mysql_password='secret';

my $mysql_dbh;

sub mysql_db_connect {
    my $dsn = "DBI:mysql:database=$mysql_database;host=$mysql_server";
    $mysql_dbh = DBI->connect($dsn, $mysql_user, $mysql_password, { PrintError => 0})
        or die "Cannot connect to MS SQL ($!), error: $DBI::errstr\n";
}

my $CSS=<<ENDCSS;
body { background-color: ivory; font-family: verdana, arial, sans-serif; }
.error { color: red; background-color: black; border: 1px solid red; padding: 0.25em; }
ENDCSS

print header(-type => 'text/html; charset=ISO-8859-1'),
	start_html( -title => $title,
        -style => { -code => $CSS }
        ), p,
	hr(),
    start_form(),
     "Delete user: ", p,
	 "Username: ", textfield('username', '', 20, 60), p,
     checkbox('delete_other_rosters', 1, 1, "Delete this user from other users' rosters"), p,
     submit(),
    end_form(),
    hr();

if(param('username')) {
	&delete_user( param('username'), param('delete_other_rosters') );
}

print end_html();

sub delete_user { # login, delete_other_rosters
	my($login, $delall) = @_;
	my @SQL=();
	# authreg delete
	push @SQL, "DELETE FROM `authreg` WHERE `username` = '$login' and `realm` = '$realm'; ";
	# data delete
	push @SQL, map "DELETE FROM `$_` WHERE `collection-owner` = '$login\@$realm'; ",
	qw/	active 
		disco-items
		logout
		motd-message
		motd-times
		privacy-default
		privacy-items
		private 
		queue
		roster-groups
		roster-items
		vacation-settings
		vcard/; 

	$delall and push @SQL, map "DELETE FROM `$_` WHERE `jid` = '$login\@$realm'; ",
	qw/roster-groups roster-items/;

	&mysql_db_connect;
	foreach my $line ( @SQL ) {
		print pre($line);
		my $r = $mysql_dbh->do($line);
		print p( $r ? "$r rows affected" : $mysql_dbh->errstr );
	}
}