//  
//  storeddbs.cs
//  
//  Author:
//       Robert BRACCAGNI alias Gai-Luron <lfsgailuron@free.fr>
// 
//  Copyright (c) 2010 Gai-Luron
// 
//  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/>.


using System;
using System.Text;
using System.Data;
#if MONOSQLITE
    using Mono.Data.SqliteClient;
#else
    using System.Data.SQLite;
#endif
using LFSLapper;
using System.Threading;
using LapperThreads;
using Configurator;

namespace LFSDbs
{
    public class storedDbs
    {
        private static Mutex mut = new Mutex();
        public DbsAccess dbCon;
        private GLDebug.Debug myDebug;
        public storedDbs(GLDebug.Debug pmyDebug,string DbName)
        {

            string sql;
            this.myDebug = pmyDebug;
            dbCon = new DbsAccess( myDebug,DbName);

            if (!dbCon.isExistTable("fi_stored") )
            {
                myDebug.WriteLine("mss","Table creation stored Values");
                sql = "CREATE TABLE fi_stored ( "
                                        + " idStored INTEGER PRIMARY KEY  NOT NULL DEFAULT ''"
                                        + ",key CHAR( 50 )"
                                        + ",value CHAR( 100 )"
                                        + ")";
                dbCon.executeNonQuery(sql);

                sql = "CREATE UNIQUE INDEX i_fi_stored ON fi_stored( key )";
                dbCon.executeNonQuery(sql);
            }
            if (!dbCon.isExistTable("fi_param"))
            {
                sql = "CREATE TABLE fi_param ( "
                        + " cle CHAR( 30 ) "
                        + ",vartxt CHAR( 30 ) "
                        + ",varint INTEGER"
                        + ")";
                dbCon.executeNonQuery(sql);

                sql = "CREATE INDEX i_fi_param1 ON fi_param( cle )";
                dbCon.executeNonQuery(sql);
                myDebug.WriteLine("mss","Table creation Param");
            }
            if (!dbCon.isExistIndex("i_fi_stored2"))
            {
                sql = "CREATE UNIQUE INDEX i_fi_stored2 ON fi_stored( idStored )";
                dbCon.executeNonQuery(sql);
                myDebug.WriteLine("ess", sql );
            }
            if (!dbCon.isExistTable("fi_user_value"))
            {
                myDebug.WriteLine("mss", "Table creation result Values");
                sql = "CREATE TABLE fi_user_value ( "
                                        + " idUserValue INTEGER PRIMARY KEY  NOT NULL DEFAULT ''"
                                        + ",key CHAR( 50 )"
                                        + ",userName CHAR( 20 )"
                                        + ",nickName CHAR( 20 )"
                                        + ",nickNameStripped CHAR( 20 )"
                                        + ",numval INTEGER"
                                        + ",strval CHAR(50)"
                                        + ")";
                dbCon.executeNonQuery(sql);

                sql = "CREATE UNIQUE INDEX fi_user_value1 ON fi_user_value( key,userName )";
                dbCon.executeNonQuery(sql);
                sql = "CREATE INDEX fi_user_value2 ON fi_user_value( userName )";
                dbCon.executeNonQuery(sql);
            }
            if (false) // Only For Test
            {
                Random random = new Random();

                Console.WriteLine("Insertion fi_user_value.TXT");
                dbCon.executeNonQuery("BEGIN TRANSACTION");
                dbCon.executeNonQuery("DELETE FROM fi_user_value");
                dbCon.executeNonQuery("COMMIT TRANSACTION");
                dbCon.executeNonQuery("BEGIN TRANSACTION");
                string uname = "";
                string nname = "";
                int numVal;
                for (int i = 0; i < 500; i++)
                {
                    uname = UTILS.utils.RandomString(8, true) + i;
                    nname = UTILS.utils.RandomString(10, true) + i;
                    for (int j = 0; j < 10; j++)
                    {
                        numVal = (int)(random.NextDouble() * 100);
                        sql = "INSERT INTO fi_user_value ("
                                    + "userName"
                                    + ",nickName"
                                    + ",nickNameStripped"
                                    + ",numVal"
                                    + ",key"
                                + ") VALUES ("
                                    + "'" + uname.ToLower().Replace("'", "''") + "'"
                                    + ",'" + nname.Replace("'", "''").Replace("\0", "") + "'"
                                    + ",'" + UTILS.utils.stripLFSColor(nname.Replace("'", "''").Replace("\0", "")) + "'"
                                    + "," + numVal
                                    + ",'testKey" + j.ToString().Replace("'", "''") + "'"
                                + ")";
                        dbCon.executeNonQuery(sql);
                    }
                }
                dbCon.executeNonQuery("COMMIT TRANSACTION");
                uname = "Gai-Luron";
                nname = "FRH Gai-Luron";
                for (int j = 0; j < 10; j++)
                {
                    numVal = (int)(random.NextDouble() * 100);
                    sql = "INSERT INTO fi_user_value ("
                                + "userName"
                                + ",nickName"
                                + ",nickNameStripped"
                                + ",numVal"
                                + ",key"
                            + ") VALUES ("
                                + "'" + uname.ToLower().Replace("'", "''") + "'"
                                + ",'" + nname.Replace("'", "''").Replace("\0", "").ToLower() + "'"
                                + ",'" + UTILS.utils.stripLFSColor(nname.Replace("'", "''").Replace("\0", "")) + "'"
                                + "," + numVal
                                + ",'testKey" + j.ToString().Replace("'", "''") + "'"
                            + ")";
                    dbCon.executeNonQuery(sql);
                }
                Console.WriteLine("Fin Insertion");
            }

        }
        public void lockBase()
        {
            mut.WaitOne();
        }
        public void unlockBase()
        {
            mut.ReleaseMutex();
        }

        public void updateRow(string key, string value)
        {
            lockBase();
            updateRow2(key, value);
            unlockBase();
        }
        private void updateRow2( string key, string value )
        {
            long idStored;
            dbCon.executeNonQuery("BEGIN TRANSACTION");
            string sql = "SELECT idStored FROM fi_stored WHERE " + "key = '" + key.Replace("'", "''") + "'";
            IDataReader retQuery = dbCon.executeQuery( sql );
            if( retQuery.Read() ){
                idStored = retQuery.GetInt64( 0 );

                sql = "UPDATE fi_stored SET "
                        + " value = '" + value.Replace("'", "''") + "'"
                        + " WHERE idStored = " + idStored
                ;
                dbCon.executeNonQuery(sql);
            }
            else{
                dbCon.executeNonQuery("INSERT INTO fi_stored ("
                                            + "key"
                                            + ",value"
                                        + ") VALUES ("
                                            + "'" + key.Replace("'", "''") + "'"
                                            + ",'" + value.Replace("'", "''") + "'"
                                        + ")");
            }
            retQuery.Close();
            retQuery.Dispose();
            dbCon.executeNonQuery("COMMIT TRANSACTION");


        }
        public string retreiveRow( string key )
        {
            string value = "";
            dbCon.executeNonQuery("BEGIN TRANSACTION");
            string sql = "SELECT value FROM fi_stored WHERE "
                            + "key = '" + key.Replace("'", "''") + "'";
            IDataReader retQuery = dbCon.executeQuery(sql);

            if (retQuery.Read())
                value = retQuery.GetString(0);
            retQuery.Close();
            retQuery.Dispose();
            dbCon.executeNonQuery("COMMIT TRANSACTION");

            return value;

        }
        public string retreiveRowUser(string key, string userName)
        {
            string value = "";
			double doubleValue = 0;
            dbCon.executeNonQuery("BEGIN TRANSACTION");
            string sql = "SELECT numval,strval FROM fi_user_value WHERE "
                            + "key = '" + key.Replace("'", "''") + "'"
                            + " AND userName = '" + userName.ToLower().Replace("'", "''") + "'";

            IDataReader retQuery = dbCon.executeQuery(sql);

			if (retQuery.Read())
			{
				value = "";
				if (retQuery.IsDBNull(1) || retQuery.GetString(1) == "")
				{
					//					intValue = retQuery.GetInt32(0);
					doubleValue = retQuery.GetDouble(0);
					if (doubleValue != 0)
						value = doubleValue.ToString();
				}
				else
					value = retQuery.GetString(1);
			}
            retQuery.Close();
            retQuery.Dispose();
            dbCon.executeNonQuery("COMMIT TRANSACTION");

            return value;

        }
        public void deletestoredvalue(string key)
        {
            try
            {
                string sql = "";
                dbCon.executeNonQuery("BEGIN TRANSACTION");
               
                sql = "DELETE FROM fi_stored WHERE " + "key = '" + key.Replace("'", "''") + "'";

                dbCon.executeNonQuery(sql);
                dbCon.executeNonQuery("COMMIT TRANSACTION");
            }
            catch
            {
                
            }
        }
        public void deletestoredvalueuser(string UserName, string key)
        {
            try
            {
                string sql = "";
                dbCon.executeNonQuery("BEGIN TRANSACTION");
                
                    sql = "DELETE FROM fi_user_value WHERE " + "key = '" + key.Replace("'", "''") + "'" + " AND userName = '" + UserName.ToLower().Replace("'", "''") + "'";

                dbCon.executeNonQuery(sql);
                dbCon.executeNonQuery("COMMIT TRANSACTION");
            }
            catch
            {
                
            }
        }


        public void updateRowUser(string key, string userName, string nickName, string strValue)
        {
            float numVal = 0;
            try { numVal = float.Parse(strValue); } catch { }
            lockBase();
            updateRowUser2(key, userName, nickName, strValue, numVal);
            unlockBase();
        }
/*
        public void updateRowUser(string key, string userName, string nickName, float numValue)
        {
            lockBase();
            updateRowUser2(key, userName, nickName, "", numValue,true);
            unlockBase();
        }
*/

        private void updateRowUser2(string key, string userName, string nickName, string strValue, float numValue)
        {
            long idUserValue;
            string nickInsert = "";
            userName = userName.ToLower();
            dbCon.executeNonQuery("BEGIN TRANSACTION");
            string sql = "SELECT idUserValue FROM fi_user_value WHERE "
                            + "key = '" + key.Replace("'", "''") + "'"
                            + " AND userName = '" + userName.Replace("'", "''") + "'"
                            ;
            IDataReader retQuery = dbCon.executeQuery(sql);
            nickInsert = nickName.Replace("'", "''").Replace("\0", "");
            if (retQuery.Read())
            {
                idUserValue = retQuery.GetInt64(0);

                sql = "UPDATE fi_user_value SET ";
                sql = sql + " numval = " + numValue;
                sql = sql + " ,strval = '" + strValue.Replace("'", "''") + "'";
                sql = sql + " ,nickName = '" + nickInsert + "'";
                sql = sql + " ,nickNameStripped = '" + UTILS.utils.stripLFSColor(nickName.Replace("'", "''").Replace("\0", "")) + "'";
                sql = sql + " WHERE idUserValue = " + idUserValue;
                dbCon.executeNonQuery(sql);

            }
            else
            {
                dbCon.executeNonQuery("INSERT INTO fi_user_value ("
                                            + "key"
                                            + ",userName"
                                            + ",nickName"
                                            + ",nickNameStripped"
                                            + ",numval"
                                            + ",strval"
                                        + ") VALUES ("
                                            + "'" + key.Replace("'", "''") + "'"
                                            + ",'" + userName.Replace("'", "''") + "'"
                                            + ",'" + nickName.Replace("'", "''").Replace("\0", "") + "'"
                                            + ",'" + UTILS.utils.stripLFSColor(nickName.Replace("'", "''").Replace("\0", "")) + "'"
                                            + "," + numValue
                                            + ",'" + strValue.Replace("'", "''") + "'"
                                        + ")");
            }
            retQuery.Close();
            retQuery.Dispose();
//Update the Nickname with the Last One
            sql = "UPDATE fi_user_value SET ";
            sql = sql + " nickName = '" + nickInsert + "'";
            sql = sql + " ,nickNameStripped = '" + UTILS.utils.stripLFSColor(nickName.Replace("'", "''").Replace("\0", "")) + "'";
            sql = sql + " WHERE userName = '" + userName.Replace("'", "''") + "'";
            sql = sql + " AND nickName != '" + nickInsert + "'";
            dbCon.executeNonQuery(sql);

            dbCon.executeNonQuery("COMMIT TRANSACTION");

        }
        public class DriverLapEntry : System.IComparable
        {
            public string userName;
            public string nickName;
            public int pos = -1;
            public int total = -1;
            public int sumNumVal;

            public DriverLapEntry(DbsAccess dbCon, string uN, string nN, int sumNumVal )
            {
                
                this.userName = uN;
                this.nickName = nN;
                this.sumNumVal = sumNumVal;
            }


            public int CompareTo(object x)
            {
                if ((x as DriverLapEntry).sumNumVal < sumNumVal)
                    return 1;
                else if ((x as DriverLapEntry).sumNumVal > sumNumVal)
                    return -1;
                else
                    return 0;
            }

        }
        public System.Collections.ArrayList GetTable(string key, int from, string relativeToUserName, int nbRead, string Filter, bool flagDesc )
        {
            string sqlStr = "";
            string sqlSelect = "";
            string sqlWhere = "";
            string sqlOrder = "";
            string sqlGroup = "";
            long cnt = 0;
            string compare = ">=";
            string orderWay = "DESC";
            IDataReader retQuery;
            IDataReader retQuery2;
            System.Collections.ArrayList list = new System.Collections.ArrayList();



            relativeToUserName = relativeToUserName.ToLower();
            if (flagDesc == false)
            {
                orderWay = "ASC";
                compare = "<=";
            }

            sqlSelect = "SELECT a.userName,a.nickName, SUM( numVal ) sumNumVal FROM fi_user_value a";
            sqlGroup = " GROUP BY a.userName,a.nickName ";
            string compString = "=";
            if (key.IndexOf('%') != -1)
                compString = "LIKE";
            sqlWhere = "WHERE key " + compString + " '" + key.Replace("'", "''") + "'";

            if ( Filter != "" )
            {
                sqlWhere = sqlWhere + " AND nickNameStripped LIKE '%" + Filter.Replace("'", "''") + "%'";
            }
            cnt = 0;
            if (relativeToUserName != "")
            {
                sqlStr = sqlSelect + " " + sqlWhere + " AND a.userName = '" + relativeToUserName.Replace("'", "''") + "'" + sqlGroup;
                retQuery = dbCon.executeQuery(sqlStr);
                if (retQuery.Read())
                {
                    dbCon.executeNonQuery("BEGIN TRANSACTION");
                    //Console.WriteLine("Ordinal 0");

                    float sumNumVal = (int)retQuery.GetFloat(retQuery.GetOrdinal("sumNumVal"));

                    cnt = 0;
                    sqlStr = sqlSelect + " " + sqlWhere + sqlGroup + " HAVING( SUM( numVal ) " + compare + " " + sumNumVal + ")";
                    retQuery2 = dbCon.executeQuery(sqlStr);
                    while (retQuery2.Read())
                    {
                        cnt++;
                    }
                    retQuery2.Close();
                    retQuery2.Dispose();

                    sqlStr = sqlSelect + " " + sqlWhere + " " + sqlGroup + " HAVING( SUM( numVal ) = " + sumNumVal + ") ORDER BY sumNumVal " + orderWay + " ,nickName";
                    retQuery2 = dbCon.executeQuery(sqlStr);
                    int i = 0;
                    int pos = 0;
                    while (retQuery2.Read())
                    {
                        //Console.WriteLine("Ordinal 1");
                        //                        string userName = retQueryZ.GetString(retQuery2.GetOrdinal("a.userName"));
                        string userName = retQuery2.GetString(0);
                        if (userName == relativeToUserName)
                        {
                            pos = i;
                        }
                        i++;
                    }
                    retQuery2.Close();
                    retQuery2.Dispose();

                    from = (int)cnt - nbRead / 2 - (i - pos);
                    if (from < 0)
                        from = 0;
                    dbCon.executeNonQuery("END TRANSACTION");
                }
                retQuery.Close();
                retQuery.Dispose();
            }


            sqlOrder = "ORDER BY sumNumVal " + orderWay + ",nickName LIMIT " + nbRead + " OFFSET  " + from;


            cnt = 0;
            sqlStr = sqlSelect + " " + sqlWhere + sqlGroup;
            retQuery = dbCon.executeQuery(sqlStr);
            while (retQuery.Read())
            {
                cnt++;
            }
            retQuery.Close();
            retQuery.Dispose();

            sqlStr = sqlSelect + " " + sqlWhere + " " + sqlGroup + " " + sqlOrder;

            dbCon.executeNonQuery("BEGIN TRANSACTION");
            retQuery = dbCon.executeQuery(sqlStr);
            int topPos = from;
            while (retQuery.Read())
            {
//                Console.WriteLine("Ordinal 2");
//                string userName = retQuery.GetString(retQuery.GetOrdinal("a.userName"));
                string userName = retQuery.GetString(0);
                //                Console.WriteLine("Ordinal 3");
//                string nickName = retQuery.GetString(retQuery.GetOrdinal("a.nickName"));
                string nickName = retQuery.GetString(1);
                //                Console.WriteLine("Ordinal 4");
                int sumNumVal = (int)retQuery.GetFloat(retQuery.GetOrdinal("sumNumVal"));

                list.Add(new DriverLapEntry(dbCon, userName, nickName, sumNumVal ));
                int last = list.Count - 1;
                (list[last] as DriverLapEntry).pos = topPos + 1;
                (list[last] as DriverLapEntry).total = (int)cnt;

                topPos++;

            }
            retQuery.Close();
            retQuery.Dispose();
            dbCon.executeNonQuery("COMMIT TRANSACTION");
            return list;

        }



    }

}
