// Database.cs
// 
// Copyright (C) 2009 Bartek Jasicki
//
// This file is part of Grubng.
//
// This program is free software: you can redistribute it and/or modify
// it under the terms of the GNU General Public License as published by
// the Free Software Foundation, either version 3 of the License, or
// (at your option) any later version.
//
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU General Public License for more details.
//
// You should have received a copy of the GNU General Public License
// along with this program.  If not, see <http://www.gnu.org/licenses/>.
//

/**@file Database.cs
 * Provide function for create and manipulate database.*/

using System;
using System.Data;
using Mono.Data.Sqlite;

namespace grubupload
{
	
	/**Provide function for create and manipulate database.*/
	internal sealed class Database
	{
		/**IDbConnection class object.*/
		private IDbConnection dbcon;
		
		/**Standard class constructor.
		 * @param dbpath Full path (include filename) to database.*/
		public Database(string dbpath)
		{
			string connectionString = "Data Source=" + dbpath;
			this.dbcon = (IDbConnection) new SqliteConnection(connectionString);
			this.dbcon.Open();
		}
		
		/**Create database.*/
		public static void CreateDB()
		{
			if (Config.ReadConfig("/configuration/enabledb") == "Y")
			{
				string connectionString = "Data Source=" + Config.ReadConfig("/configuration/dbpath");
				System.Data.IDbConnection dbcon = (IDbConnection) new SqliteConnection(connectionString);
				dbcon.Open();
				IDbCommand dbcmd = dbcon.CreateCommand();
				dbcmd.CommandText = "CREATE TABLE weekly (user varchar(32), senddate bigint, urls smallint);" +
					"CREATE INDEX idx_wuser ON weekly(user);" + 
					"CREATE TABLE monthly (month smallint, user varchar(32), arcs integer, urls integer, year smallint);" +
						"CREATE INDEX idx_muser ON monthly(user);" + 
						"CREATE TABLE yearly (year smallint, user varchar(32), arcs integer, urls integer);" +
						"CREATE INDEX idx_yuser ON yearly(user);";
				int tmp = dbcmd.ExecuteNonQuery();
				tmp ++;
				dbcmd.Dispose();
				dbcon.Close();
			}
		}
		
		/**Update database.*/
		public static void UpdateDB()
		{
			if (Config.ReadConfig("/configuration/enabledb") == "Y")
			{
				string connectionString = "Data Source=" + Config.ReadConfig("/configuration/dbpath");
				System.Data.IDbConnection dbcon = (IDbConnection) new SqliteConnection(connectionString);
				dbcon.Open();
				IDbCommand dbcmd = dbcon.CreateCommand();
				dbcmd.CommandText = "CREATE INDEX idx_wuser ON weekly(user);" +
					"CREATE INDEX idx_muser ON monthly(user);" + 
						"CREATE INDEX idx_yuser ON yearly(user);";
				int tmp = dbcmd.ExecuteNonQuery();
				tmp ++;
				dbcmd.Dispose();
				dbcon.Close();
			}
		}
		
		/**Read from database.
		 * @param sqlcommand SQL command to execute.
		 * @param columns Amount of columns to read.
		 * @return result of executed SQL command.*/
		public string ReadDB(string sqlcommand, int columns)
		{
			System.Text.StringBuilder returnvalue = new System.Text.StringBuilder();
			IDbCommand dbcmd = this.dbcon.CreateCommand();
			dbcmd.CommandText = sqlcommand;
			IDataReader reader = dbcmd.ExecuteReader();
			while (reader.Read())
			{
				for (int i = 0; i < columns; i++)
				{
					returnvalue.Append(Convert.ToString(reader.GetValue(i)));
					returnvalue.Append(",");
				}
				returnvalue.Replace(',', '\n', (returnvalue.Length - 1), 1);
			}
			reader.Close();
			reader.Dispose();
			dbcmd.Dispose();
			return returnvalue.ToString();
		}
		
		/**Write info about uploaded .arc file to database.
		 * @param username Username to write in database.
		 * @param urls Amount of url's in .arc file.*/
		public void WriteDB(string username, string urls)
		{
			//Insert record to 'weekly' table
			username = username.ToLower();
			IDbCommand dbcmd = this.dbcon.CreateCommand();
			string date = DateTime.UtcNow.ToFileTime().ToString();
			dbcmd.CommandText = "INSERT INTO weekly VALUES('" + username + "'," + date + "," + urls + ")";
			int tmp = dbcmd.ExecuteNonQuery();
			tmp ++;
			//Delete records older than one week
			date = DateTime.UtcNow.AddDays(-7).ToFileTime().ToString();
			dbcmd.CommandText = "DELETE FROM weekly WHERE user='" + username + "' AND senddate<" + date;
			tmp = dbcmd.ExecuteNonQuery();
			//Insert or update record in 'monthly' table
			string month = DateTime.UtcNow.Month.ToString();
			string year = DateTime.UtcNow.Year.ToString();
			dbcmd.CommandText = "SELECT arcs FROM monthly WHERE user='" + username + 
				"' AND month=" + month + " AND year=" + year;
			IDataReader reader = dbcmd.ExecuteReader();
			bool exist = reader.Read();
			reader.Close();
			if (!exist)
			{
				dbcmd.CommandText = "INSERT INTO monthly VALUES(" + month + ",'" + username + "', 1, " +
					urls + ", " + year + ")";
			}
			else
			{
				dbcmd.CommandText = "UPDATE monthly SET arcs=arcs+1, urls=urls+" + urls + " WHERE user='" + 
					username + "' AND month=" + month + " AND year=" + year;
			}
			tmp = dbcmd.ExecuteNonQuery();
			dbcmd.CommandText = "DELETE FROM monthly WHERE user='" + username + "' AND month=" + month + 
				" AND year<" + year;
			tmp = dbcmd.ExecuteNonQuery();
			//Insert or update record in 'yearly' table
			dbcmd.CommandText = "SELECT arcs FROM yearly WHERE user='" + username + 
				"' AND year=" + year;
			reader = dbcmd.ExecuteReader();
			exist = reader.Read();
			reader.Close();
			if (!exist)
			{
				dbcmd.CommandText = "INSERT INTO yearly VALUES(" + year + ",'" + username + "', 1, " +
					urls + ")";
			}
			else
			{
				dbcmd.CommandText = "UPDATE yearly SET arcs=arcs+1, urls=urls+" + urls + " WHERE user='" + 
					username + "' AND year=" + year;
			}
			tmp = dbcmd.ExecuteNonQuery();
			dbcmd.Dispose();
		}
	}
}