<?php

/**
 * Support functions for sanitizing the DB
 *
 * @addtogroup Maintenance
 * @author Hans Musil <hansm@wikivoyage.org>
 */

function sanitize( $san=false, $check=false, $verbose=false)
{

	$dbw = wfGetDB( DB_MASTER );
	$tbl_user = $dbw->tableName( 'user' );
	$tbl_page = $dbw->tableName( 'page' );
	$tbl_img  = $dbw->tableName( 'image' );

	$sancheck = 'check';
	if( $san) $sancheck = 'san';

	/*
	 * ON DELETE CASCADE references:
	 */

	doTable( $sancheck, $verbose, 'del',
	    'revision', $tbl_page,
	    array( 'rev_id'),
	    array( 'rev_page', 'rev_user'),
	    'page_id=rev_page', 'page_id IS NULL'
	);

	doTable( $sancheck, $verbose, 'del',
	    'page_restrictions', $tbl_page,
	    array( 'pr_id'),
	    array( ),
	    'page_id=pr_page', 'page_id IS NULL'
	);

	doTable( $sancheck, $verbose, 'del',
	    'redirect', $tbl_page,
	    array( 'rd_from'),
	    array( 'rd_namespace', 'rd_title'),
	    'page_id=rd_from', 'page_id IS NULL'
	);

	doTable( $sancheck, $verbose, 'del',
	    'pagelinks', $tbl_page,
	    array( 'pl_from'),
	    array( 'pl_namespace', 'pl_title'),
	    'page_id=pl_from', 'page_id IS NULL'
	);

	doTable( $sancheck, $verbose, 'del',
	    'templatelinks', $tbl_page,
	    array( 'tl_from'),
	    array( 'tl_namespace', 'tl_title'),
	    'page_id=tl_from', 'page_id IS NULL'
	);

	doTable( $sancheck, $verbose, 'del',
	    'imagelinks', $tbl_page,
	    array( 'il_from'),
	    array( 'il_to'),
	    'page_id=il_from', 'page_id IS NULL'
	);

	doTable( $sancheck, $verbose, 'del',
	    'categorylinks', $tbl_page,
	    array( 'cl_from'),
	    array( 'cl_to'),
	    'page_id=cl_from', 'page_id IS NULL'
	);

	doTable( $sancheck, $verbose, 'del',
	    'externallinks', $tbl_page,
	    array( 'el_from'),
	    array( 'el_to'),
	    'page_id=el_from', 'page_id IS NULL'
	);

	doTable( $sancheck, $verbose, 'del',
	    'langlinks', $tbl_page,
	    array( 'll_from'),
	    array( 'll_lang', 'll_title'),
	    'page_id=ll_from', 'page_id IS NULL'
	);

	doTable( $sancheck, $verbose, 'del',
	    'trackbacks', $tbl_page,
	    array( 'tb_id'),
	    array( ),
	    'page_id=tb_page', 'page_id IS NULL'
	);


	doTable( $sancheck, $verbose, 'del',
	    'user_groups', $tbl_user,
	    array( 'ug_user'),
	    array( 'ug_group'),
	    'user_id=ug_user', 'user_id IS NULL'
	);

	doTable( $sancheck, $verbose, 'del',
	    'watchlist', $tbl_user,
	    array( 'wl_user'),
	    array( 'wl_namespace', 'wl_title'),
	    'user_id=wl_user', 'user_id IS NULL'
	);

	doTable( $sancheck, $verbose, 'del',
	    'ipblocks', $tbl_user,
	    array( 'ipb_id'),
	    array( 'ipb_reason', 'ipb_timestamp'),
	    'user_id=ipb_by', 'user_id IS NULL'
	);

	# Problematic since there are equaly named fields in both tables.
	# Checks run reliable, but verbose ones don't. Sanitizing either.
	#
	$tbl_unt = $dbw->tableName( 'user_newtalk' );
	doTable( $sancheck, $verbose, 'del',
	    'user_newtalk', $tbl_user,
	    array( "$tbl_unt.user_id"),
	    array( "$tbl_unt.user_ip"),
	    "$tbl_user.user_id=$tbl_unt.user_id", "$tbl_unt.user_id!=0 AND $tbl_user.user_id IS NULL"
	);

	/*
	 * ON DELETE <NO ACTION>:
	 */

	doTable( $sancheck, $verbose, 'del',
	    'oldimage', $tbl_img,
	    array( 'oi_name'),
	    array( ),
	    'img_name=oi_name', 'img_name IS NULL'
	);

	/*
	 * ON DELETE RESTRICT:
	 */

	doTable( $sancheck, $verbose, 'set0',
	    'revision', $tbl_user,
	    array( 'rev_id'),
	    array( 'rev_page', 'rev_user'),
	    'user_id=rev_user', 'rev_user!=0 AND user_id IS NULL',
			'rev_user'
	);

	/*
	 * ON DELETE SET NULL references:
	 */

	doTable( $sancheck, $verbose, 'null',
	    'archive', $tbl_user,
	    array( 'ar_user'),
	    array( 'ar_namespace', 'ar_title'),
	    'user_id=ar_user', 'ar_user!=0 AND user_id IS NULL',
			'ar_user'
	);

	doTable( $sancheck, $verbose, 'null',
	    'ipblocks', $tbl_user,
	    array( 'ipb_id'),
	    array( 'ipb_user'),
	    'user_id=ipb_user', 'ipb_user!=0 AND user_id IS NULL',
	    'ipb_user'
	);

	doTable( $sancheck, $verbose, 'null',
	    'image', $tbl_user,
	    array( 'img_name'),
	    array( 'img_user'),
	    'user_id=img_user', 'img_user!=0 AND user_id IS NULL',
	    'img_user'
	);

	doTable( $sancheck, $verbose, 'null',
	    'oldimage', $tbl_user,
	    array( 'oi_user'),
	    array( 'oi_name'),
	    'user_id=oi_user', 'oi_user!=0 AND user_id IS NULL',
	    'oi_user'
	);

	doTable( $sancheck, $verbose, 'null',
	    'filearchive', $tbl_user,
	    array( 'fa_id'),
	    array( 'fa_name'),
	    'user_id=fa_deleted_user', 'fa_deleted_user!=0 AND user_id IS NULL',
	    'fa_deleted_user'
	);

	doTable( $sancheck, $verbose, 'null',
	    'filearchive', $tbl_user,
	    array( 'fa_id'),
	    array( 'fa_name'),
	    'user_id=fa_user', 'fa_user!=0 AND user_id IS NULL',
	    'fa_user'
	);

	doTable( $sancheck, $verbose, 'null',
	    'recentchanges', $tbl_user,
	    array( 'rc_id'),
	    array( 'rc_timestamp'),
	    'user_id=rc_user', 'rc_user!=0 AND user_id IS NULL',
	    'rc_user'
	);

	doTable( $sancheck, $verbose, 'null',
	    'logging', $tbl_user,
	    array( 'log_id'),
	    array( 'log_user', 'log_timestamp', 'log_type'),
	    'user_id=log_user', 'log_user!=0 AND user_id IS NULL',
	    'log_user'
	);

	doTable( $sancheck, $verbose, 'null',
	    'recentchanges', $tbl_page,
	    array( 'rc_id'),
	    array( 'rc_timestamp'),
	    'page_id=rc_cur_id', 'rc_cur_id!=0 AND page_id IS NULL',
	    'rc_cur_id'
	);


	/*
	doTable( $sancheck, $verbose, 'null',
	    '', $tbl_user,
	    array( '_user'),
	    array( ''),
	    'user_id=_user', '_user!=0 AND user_id IS NULL',
	    ''
	);
	*/

};

function doTable( $sancheck, $verbose, $sanhow, $tblchk, $tblref, $keyfields, $infofields, $oncond, $wherecond, $reffield=NULL)
{
	$dbw = wfGetDB( DB_MASTER );
	$tblchk   = $dbw->tableName( $tblchk );

	$unsane = searchUnsane( $keyfields, $infofields, $tblchk, $tblref, $oncond, $wherecond);
	displayUnsane( $unsane, $verbose, $keyfields, $infofields);
	if( $sancheck === 'san') sanitizeUnsane( $sanhow, $unsane, $verbose, $keyfields, $tblchk, $reffield);
};

function searchUnsane( $keyfields, $infofields, $tblchk, $tblref, $oncond, $wherecond)
{
	echo( "Searching for non existing references in table $tblchk ..." );

	$query = 'SELECT ' . implode( ',', $keyfields) . 
	             ( $infofields ? ',' : '') . implode( ',', $infofields) .
	             " FROM $tblchk LEFT JOIN $tblref ON $oncond WHERE $wherecond";
	# echo "$query\n";

	$unsane = array();

	$dbw = wfGetDB( DB_MASTER );
	$dbw->begin();

	$res = $dbw->query( $query );
	while( $row = $dbw->fetchRow( $res ) ) {
		$kres = array();
		foreach( $keyfields as $k)
		{
			$kres[] = $row[$k];
		};
		$ires = array();
		foreach( $infofields as $k)
		{
			$ires[] = $row[$k];
		};

		$unsane[] = array( 'key' => $kres, 'info' => $ires);
	}
	$dbw->commit();
	echo( "done.\t" );

	return $unsane;
};

function sanitizeUnsane( $how, $unsane, $verbose, $kfields, $tblsan, $reffield)
{
	if( !$unsane){ return;};
	echo "Sanitizing table $tblsan ...";
	if( $verbose) echo "\n";

	$querybase = '';
	switch( $how)
	{
		case 'del':
			$querybase = "DELETE FROM $tblsan WHERE ";
			break;
		case 'null':
			$querybase = "UPDATE $tblsan SET $reffield=NULL WHERE ";
			break;
		case 'set0':
			$querybase = "UPDATE $tblsan SET $reffield=0 WHERE ";
			break;
		default:
			echo "No sanhow given. This is an internal error.\n";
			exit;
	};

	$delrow = array();

	$dbw = wfGetDB( DB_MASTER );
	$dbw->begin();

	foreach( $unsane as $us)
	{
		$usk = $us['key'];
		$kf = implode( ",", $usk );
		if( ! array_key_exists( $kf, $delrow) )
		{
			$delrow[$kf] = 1;
			$kv = array_combine( $kfields, $usk);
			$cond = array();
			foreach( $kv as $k => $v)
			{
				$cond[] = "$k=$v";
			};
			$query = $querybase . implode( ' AND ', $cond);
			if($verbose) echo "$query;\n";
			$dbw->query( $query );
		};
	};

	$dbw->commit();
	echo "done.\n";
};

function displayUnsane( $unsane, $verbose, $kfields, $ifields)
{
	if( $unsane)
	{
		echo count( $unsane) . " rows found.\n";
		if( $verbose)
		{
			$kf = implode( "\t| ", $kfields);
			$if = implode( "\t| ", $ifields);
			$prt = $kf . ( $if ? "\t: $if" : '') . "\n";
			echo $prt . "--------------------------------------------------------------------------\n";

			foreach( $unsane as $us)
			{
				$kf = implode( "\t| ", $us['key']);
				$if = implode( "\t| ", $us['info']);
				$prt = $kf . ( $if ? "\t: $if" : '') . "\n";
				echo $prt;
			};
		};
	}else
	{
		echo "OK\n";
	};
};


?>