import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import actyondev.commons.dbcp.BasicDataSource;
import actyondev.commons.dbutils.DbUtils;


public class DataBaseTest {

	public static void launch()
	{
		ArrayList<Integer> charId = new ArrayList<Integer>();
		Connection con = null;
		PreparedStatement statementChar = null;
		PreparedStatement statementItem = null;
		PreparedStatement statementMail = null;
		PreparedStatement statementItemDelete = null;
		PreparedStatement statementMailDelete = null;
		PreparedStatement statementMailAttachments = null;
		PreparedStatement statementMailAttachmentsDelete = null;
		int countItems = 0;
		int countMail = 0;
		int countMailAttachments = 0;
		int countConItem = 0;
		boolean doNext = true;

		try
		{
			//con = new BasicDataSource("com.mysql.jdbc.Driver", "jdbc:mysql://127.0.0.1/omega10x", "root", "", 10, 10, 60, 60, false).getConnection(null);
			con = new BasicDataSource("com.mysql.jdbc.Driver", "jdbc:mysql://37.59.18.67/h5test", "omegateam", "lin&team&omega&sql", 10, 10, 60, 60, false).getConnection(null);

			//For Clean mail Table
			statementMail = con.prepareStatement("SELECT sender_id FROM mail");
			ResultSet rsetMail = statementMail.executeQuery();

			while(rsetMail.next())
			{
				statementChar = con.prepareStatement("SELECT obj_Id FROM characters WHERE obj_Id = '" + rsetMail.getInt(1) + "'");
				ResultSet rsetChar = statementChar.executeQuery();
				if(!rsetChar.next())
				{
					statementMailDelete = con.prepareStatement("DELETE FROM mail WHERE sender_id='" + rsetMail.getInt(1) + "'");
					int i;
					if((i = statementMailDelete.executeUpdate()) >= 1)
						countMail += i;
				}
			}
			DbUtils.closeQuietly(con, statementMail);
			DbUtils.closeQuietly(con, statementChar);
			DbUtils.closeQuietly(con, statementMailDelete);

			con = new BasicDataSource("com.mysql.jdbc.Driver", "jdbc:mysql://127.0.0.1/omega10x", "root", "", 10, 10, 60, 60, false).getConnection(null);
			statementMail = con.prepareStatement("SELECT receiver_id FROM mail");
			rsetMail = statementMail.executeQuery();

			while(rsetMail.next())
			{
				statementChar = con.prepareStatement("SELECT obj_Id FROM characters WHERE obj_Id = '" + rsetMail.getInt(1) + "'");
				ResultSet rsetChar = statementChar.executeQuery();
				if(!rsetChar.next())
				{
					statementMailDelete = con.prepareStatement("DELETE FROM mail WHERE receiver_id='" + rsetMail.getInt(1) + "'");
					int i;
					if((i = statementMailDelete.executeUpdate()) >= 1)
						countMail += i;
				}
			}
			DbUtils.closeQuietly(con, statementMail);
			DbUtils.closeQuietly(con, statementChar);
			DbUtils.closeQuietly(con, statementMailDelete);

			// For Clean mail_attachments Table
			con = new BasicDataSource("com.mysql.jdbc.Driver", "jdbc:mysql://127.0.0.1/omega10x", "root", "", 10, 10, 60, 60, false).getConnection(null);
			statementMailAttachments = con.prepareStatement("SELECT message_id FROM mail_attachments");
			ResultSet rsetMailAttachments = statementMailAttachments.executeQuery();

			while(rsetMailAttachments.next())
			{
				statementMail = con.prepareStatement("SELECT message_id FROM mail");
				rsetMail = statementMail.executeQuery();
				if(!rsetMail.next())
				{
					statementMailAttachmentsDelete = con.prepareStatement("DELETE FROM mail_attachments WHERE message_id='" + rsetMailAttachments.getInt(1) + "'");
					int i;
					if((i = statementMailAttachmentsDelete.executeUpdate()) >= 1)
						countMailAttachments += i;
				}
			}
			DbUtils.closeQuietly(con, statementMailAttachments);
			DbUtils.closeQuietly(con, statementMail);
			DbUtils.closeQuietly(con, statementMailAttachmentsDelete);

			// For Clean items Table
			con = new BasicDataSource("com.mysql.jdbc.Driver", "jdbc:mysql://127.0.0.1/omega10x", "root", "", 10, 10, 60, 60, false).getConnection(null);
			statementChar = con.prepareStatement("SELECT obj_Id FROM characters");
			ResultSet rsetChar = statementChar.executeQuery();

			while(rsetChar.next())
			{
				if(!charId.contains(rsetChar.getInt(1)))
					charId.add(rsetChar.getInt(1));
			}
			DbUtils.closeQuietly(con, statementChar);

			while(doNext)
			{
				con = new BasicDataSource("com.mysql.jdbc.Driver", "jdbc:mysql://127.0.0.1/omega10x", "root", "", 10, 10, 60, 60, false).getConnection(null);
				statementItem = con.prepareStatement("SELECT owner_id FROM items");
				ResultSet rsetItems = statementItem.executeQuery();

				if(!rsetItems.next())
				{
					System.out.println("WTF !!! on stop deja?");
					doNext = false;
				}
				Boolean continu = true;
				while(rsetItems.next() && continu)
				{
					countConItem++;
					if(!charId.contains(rsetItems.getInt(1)))
					{
						statementItemDelete = con.prepareStatement("DELETE FROM items WHERE owner_id='" + rsetItems.getInt(1) + "'");
						int i;
						if((i = statementItemDelete.executeUpdate()) >= 1)
							countItems += i;
					}
					if(countConItem >= 15000)
					{
						countConItem = 0;
						continu = false;
					}
				}

				DbUtils.closeQuietly(con, statementItem);
				DbUtils.closeQuietly(con, statementItemDelete);
				System.out.println("En cours " + countItems + " lignes delete de la table items");
				Thread.sleep(3000);
			}

			System.out.println("On a delete " + countItems + " lignes de la table items");
			System.out.println("On a delete " + countMail + " lignes de la table mails");
			System.out.println("On a delete " + countMailAttachments + " lignes de la table mail_attachments");
		}
		catch(SQLException e)
		{
			System.out.println("fail: " + e);
		}
		catch(Exception e)
		{
			System.out.println("fail: " + e);
		}
		finally
		{
			DbUtils.closeQuietly(con, statementChar);
			DbUtils.closeQuietly(con, statementItem);
			DbUtils.closeQuietly(con, statementMail);
			DbUtils.closeQuietly(con, statementItemDelete);
			DbUtils.closeQuietly(con, statementMailDelete);
			DbUtils.closeQuietly(con, statementMailAttachments);
			DbUtils.closeQuietly(con, statementMailAttachmentsDelete);
		}
	}
}