//  
//  driftdbs.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;


namespace LFSDbs
{
    public class driftDbs
    {
        private static Mutex mut = new Mutex();
        public DbsAccess dbCon;
        private int LapTimeUsedForPb = 1;
        LapperThreads.ftpDbsUpload threadDbsUploadDrift;
        private string uniqueConnectionId = "";
        Thread threadUploadDrift = null;
        private GLDebug.Debug myDebug;

        public driftDbs(GLDebug.Debug pmyDebug, string puniqueConnectionId,string DbName, string FtpServer, string FtpLogin, string FtpPasswd, string FtpRemotePath,  string dateFormat )
        {

            string sql;
            this.myDebug = pmyDebug;
            this.LapTimeUsedForPb = 1;
            this.uniqueConnectionId = puniqueConnectionId;


            
            dbCon = new DbsAccess(myDebug, DbName);
            IDataReader retQuery;

            if (!dbCon.isExistTable("fi_driftpb"))
            {

                myDebug.WriteLine("mss","Table creation PB");
                sql = "CREATE TABLE fi_driftpb ( "
                                        + " idEpb INTEGER PRIMARY KEY  NOT NULL DEFAULT ''"
                                        + ",userName CHAR( 20 )"
                                        + ",nickName CHAR( 20 )"
                                        + ",nickNameStripped CHAR( 20 )"
                                        + ",date CHAR( 10 )"
                                        + ",time CHAR( 10 )"
                                        + ",carName CHAR( 4 )"
                                        + ",driftPoints INTEGER"
                                        + ",trackName CHAR( 4 )"

                                        + ")";
                dbCon.executeNonQuery(sql);

                sql = "CREATE UNIQUE INDEX i_fi_driftpb ON fi_driftpb( userName,carName,trackName )";
                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);
            }
            if (!dbCon.isExistColum("fi_driftpb", "nickNameStripped"))
            {
                myDebug.WriteLine("mss","ALTER TABLE fi_driftpb ADD nickNameStripped CHAR(20)");
                sql = "ALTER TABLE fi_driftpb ADD nickNameStripped CHAR(20)";
                dbCon.executeNonQuery(sql);
                dbCon.executeNonQuery("BEGIN TRANSACTION");
                sql = "SELECT * FROM fi_driftpb";
                retQuery = dbCon.executeQuery(sql);
                while (retQuery.Read())
                {
                    long idEpb = retQuery.GetInt64(retQuery.GetOrdinal("idEpb"));
                    string nickNameStripped = UTILS.utils.stripLFSColor(retQuery.GetString(retQuery.GetOrdinal("nickName")));
                    string sql2 = "UPDATE fi_driftpb SET nickNameStripped = '" + nickNameStripped + "'"
                                    + " WHERE idEpb = " + idEpb;
                    dbCon.executeNonQuery(sql2);
                }
                retQuery.Close();
                retQuery.Dispose();
                dbCon.executeNonQuery("COMMIT TRANSACTION");

            }
            string myTable = "fi_" + uniqueConnectionId;
            if (!dbCon.isExistTable(myTable))
            {

                sql = "CREATE TABLE " + myTable + " ( "
                                        + "userName CHAR( 20 )"
                                        + ",CarName CHAR( 3 )"
                                        + ")";
                dbCon.executeNonQuery(sql);

                sql = "CREATE INDEX i_" + myTable + " ON " + myTable + "( userName,carName )";
                dbCon.executeNonQuery(sql);

            }
            /*
                                    Console.WriteLine("Insertion DRIFTPB.TXT");
                                    dbCon.executeNonQuery("BEGIN TRANSACTION");
                                    dbCon.executeNonQuery("DELETE FROM fi_driftpb");
                                    dbCon.executeNonQuery("COMMIT TRANSACTION");
                                    dbCon.executeNonQuery("BEGIN TRANSACTION");
                                    string uname = "";
                                    string nname = "";
                                    for (int i = 0; i < 1000; i++)
                                    {
                                        uname = UTILS.utils.RandomString(8, true)+i;
                                        nname = UTILS.utils.RandomString(10, true)+i;
                                        sql = "INSERT INTO fi_driftpb ("
                                                    + "userName"
                                                    + ",nickName"
                                                    + ",nickNameStripped"
                                                    + ",carName"
                                                    + ",trackName"
                                                    + ",driftPoints"
                                                    + ",date"
                                                    + ",time"
                                                + ") VALUES ("
                                                    + "'" +  uname.Replace("'", "''") + "'"
                                                    + ",'" + nname.Replace("'", "''").Replace("\0", "") + "'"
                                                    + ",'" + UTILS.utils.stripLFSColor(nname.Replace("'", "''").Replace("\0", "")) + "'"
                                                    + ",'" + "FXO" + "'"
                                                    + ",'" + "BL1" + "'"
                                                    + "," + i*10
                                                    + ",'" + "2009/05/01" + "'"
                                                    + ",'" + "00:00" + "'"
                                                + ")";
                                        dbCon.executeNonQuery(sql);
                                    }
                                    uname = "Gai-Luron";
                                    nname = "FRH Gai-Luron";

                                    dbCon.executeNonQuery("COMMIT TRANSACTION");
                                        sql = "INSERT INTO fi_driftpb ("
                                                    + "userName"
                                                    + ",nickName"
                                                    + ",nickNameStripped"
                                                    + ",carName"
                                                    + ",trackName"
                                                    + ",driftPoints"
                                                    + ",date"
                                                    + ",time"
                                                + ") VALUES ("
                                                    + "'" +  uname.Replace("'", "''") + "'"
                                                    + ",'" + nname.Replace("'", "''").Replace("\0", "") + "'"
                                                    + ",'" + UTILS.utils.stripLFSColor(nname.Replace("'", "''").Replace("\0", "")) + "'"
                                                    + ",'" + "FXO" + "'"
                                                    + ",'" + "BL1" + "'"
                                                    + "," + 7250
                                                    + ",'" + "2009/05/01" + "'"
                                                    + ",'" + "00:00" + "'"
                                                + ")";
                                        dbCon.executeNonQuery(sql);
                                    Console.WriteLine("Fin Insertion");
            */
            this.threadDbsUploadDrift = new ftpDbsUpload(myDebug, DbName, FtpServer, FtpLogin, FtpPasswd, FtpRemotePath, dateFormat, LapTimeUsedForPb);
            threadUploadDrift = new Thread(new ThreadStart(threadDbsUploadDrift.SendDriftDbsToFtp));
            threadUploadDrift.Start();
            
            
        }
        public void killThreads(){
            if (threadUploadDrift != null)
            {
                threadUploadDrift.Abort();
                threadDbsUploadDrift.dbCon.dbCon.Close();
                threadDbsUploadDrift.dbCon.dbCon.Dispose();
            }
            dbCon.dbCon.Close();
            dbCon.dbCon.Dispose();
        }
        public bool isThreadsAlive()
        {
            if (threadUploadDrift != null)
                return threadUploadDrift.IsAlive;
            return false;
        }

//        public void updateRow(bool immediateUpload, LFSLapper.infoPlayer currInfoPlayer, string trackName, bool modeSync, string SyncID, string SyncDir, string[] SyncIDsToSync)
        public void lockBase()
        {
            mut.WaitOne();
        }
        public void unlockBase()
        {
            mut.ReleaseMutex();
        }

        public void updateRow(bool immediateUpload, LFSLapper.infoPlayer currInfoPlayer, string trackName )
        {
            updateRow(immediateUpload, currInfoPlayer.userName,
                    currInfoPlayer.nickName,
                    currInfoPlayer.CName,
                    trackName,
                    currInfoPlayer.driftDriverLapInfo
                    
            );

        }
        public void updateRow(bool immediateUpload, string userName, string nickName, string carName, string trackName, DriverLapEntry driverLapInfo)
        {
            lockBase();
			try
			{
				updateRow2(immediateUpload, userName, nickName, carName, trackName, driverLapInfo);
			}
			finally
			{
				unlockBase();
			}
        }
        public void updateRow2(bool immediateUpload, string userName, string nickName, string carName, string trackName, DriverLapEntry driverLapInfo)
        {
            long idEpb;
            userName = userName.ToLower();
            dbCon.executeNonQuery("BEGIN TRANSACTION");
            string sql = "SELECT idEpb FROM fi_driftpb WHERE "
                            + "userName = '" + userName.Replace("'", "''") + "'"
                            + " AND carName = '" + carName + "'"
                            + " AND trackName = '" + trackName + "'";
            IDataReader retQuery = dbCon.executeQuery( sql );
            if( retQuery.Read() ){
                idEpb = retQuery.GetInt64( 0 );

                sql = "UPDATE fi_driftpb SET "
                        + " nickName = '" + nickName.Replace("'", "''").Replace("\0", "") + "'"
                        + " ,nickNameStripped = '" + UTILS.utils.stripLFSColor(nickName.Replace("'", "''").Replace("\0", "")) + "'"
                        + " ,driftPoints = " + driverLapInfo.driftPoints
                        + ",date ='" + driverLapInfo.datePb + "'"
                        + ",time = '" + driverLapInfo.timePb + "'"
                        + " WHERE idEpb = " + idEpb
                ;
                dbCon.executeNonQuery(sql);
            }
            else{
                dbCon.executeNonQuery("INSERT INTO fi_driftpb ("
                                            + "userName"
                                            + ",nickName"
                                            + ",nickNameStripped"
                                            + ",carName"
                                            + ",trackName"
                                            + ",driftPoints"
                                            + ",date"
                                            + ",time"
                                        + ") VALUES ("
                                            + "'" + userName.ToLower().Replace("'", "''") + "'"
                                            + ",'" + nickName.Replace("'", "''").Replace("\0", "") + "'"
                                            + ",'" + UTILS.utils.stripLFSColor(nickName.Replace("'", "''").Replace("\0", "")) + "'"
                                            + ",'" + carName + "'"
                                            + ",'" + trackName + "'"
                                            + "," + driverLapInfo.driftPoints
                                            + ",'" + driverLapInfo.datePb + "'"
                                            + ",'" + driverLapInfo.timePb + "'"
                                        + ")");
            }
            retQuery.Close();
            retQuery.Dispose();
            dbCon.executeNonQuery("COMMIT TRANSACTION");
            if (immediateUpload)
                threadDbsUploadDrift.immediateUpload();
            else
                threadDbsUploadDrift.delayedUpload();


        }
        public void FORCED_UPLOAD()
        {
            threadDbsUploadDrift.immediateUpload();
        }
        public void retreiveRow(infoPlayer currInfoPlayer, string trackName)
        {
            dbCon.executeNonQuery("BEGIN TRANSACTION");
            currInfoPlayer.driftDriverLapInfo = new DriverLapEntry( dbCon,currInfoPlayer.userName.ToLower(),currInfoPlayer.nickName,currInfoPlayer.CName,trackName );
            currInfoPlayer.TName = trackName;
            dbCon.executeNonQuery("COMMIT TRANSACTION");
            currInfoPlayer.sessLaps = 0;

        }
        public System.Collections.ArrayList MkGroup(int MaxGroupQual, int MaxUserGroupQual, int MinUserGroupQual, int NbRacer)
        {

            System.Collections.ArrayList group = new System.Collections.ArrayList();

            int NbGroup = NbRacer / MaxUserGroupQual;
            int reste = NbRacer - (NbGroup * MaxUserGroupQual);
            //        Console.WriteLine("NbGroup:" + NbGroup + " Reste:" + reste);
            if (NbGroup >= MaxGroupQual)
            {
                NbGroup = MaxGroupQual - 1;
                reste = MaxUserGroupQual;
            }
            // Mettre le nombre de pilote pour chaque poule
            int i;
            for (i = 0; i < NbGroup; i++)
                group.Add(MaxUserGroupQual);
            if (reste != 0)
                group.Add(reste);

            // Equilibration des poules
            if ((reste < MinUserGroupQual) && (NbGroup > 0) && (reste != 0))
            {
                //            Console.WriteLine(group[i]);
                group[i] = MinUserGroupQual;
                int diff = MinUserGroupQual - reste;
                while (true)
                {
                    for (int j = NbGroup - 1; j >= 0; j--)
                    {
                        group[j] = (int)group[j] - 1;
                        diff--;
                        if (diff == 0)
                        {
                            return group;
                        }
                    }
                }
            }
            else
                return group;

        }
        public double retreiveDriftPoint(string userName, string nickName, string carName, string trackName)
        {
            dbCon.executeNonQuery("BEGIN TRANSACTION");
            DriverLapEntry retValue = new DriverLapEntry(dbCon, userName, nickName, carName, trackName);
            dbCon.executeNonQuery("COMMIT TRANSACTION");
            if (retValue == null || retValue.driftPoints == 0)
                return -1;
            else
                return retValue.driftPoints;
        }

        public class PbLapEntry : System.IComparable
        {
            public string datePb;
            public string timePb;
            public long personalBestLapTime;
            public long[] splitTime = new long[(int)paramLapper.maxSplit];
            public PbLapEntry(long PB, long split1, long split2, long split3, string dPB, string tPB)
            {
                this.personalBestLapTime = PB;
                this.splitTime[0] = split1;
                this.splitTime[1] = split2;
                this.splitTime[2] = split3;
                this.datePb = dPB;
                this.timePb = tPB;

            }

            public int CompareTo(object x)
            {
                if ((x as PbLapEntry).personalBestLapTime < personalBestLapTime)
                    return 1;
                else if ((x as PbLapEntry).personalBestLapTime > personalBestLapTime)
                    return -1;
                else
                {
                    string myDT1 = (x as PbLapEntry).datePb + " " + (x as PbLapEntry).timePb;
                    string myDT2 = datePb + " " + timePb;
                    return myDT1.CompareTo(myDT2);
                }
            }

        }


        public class DriverLapEntry : System.IComparable
        {
            public string userName;
            public string nickName;
            public string carName;
            public string datePb;
            public string timePb;
            public int pos = -1;
            public int total = -1;
            public long driftPoints;
            public int group;

            public DriverLapEntry( DbsAccess dbCon, string uN, string nN, string cN, string tN )
            {
                string sqlStr;

                this.userName = uN;
                this.nickName = nN;
                this.carName = cN;
                this.driftPoints = 0;
                sqlStr = "SELECT * FROM fi_driftpb"
                    + " WHERE carName = '" + cN + "'"
                    + " AND trackName = '" + tN + "'"
                    + " AND userName = '" + uN.ToLower().Replace("'", "''") + "'"
                ;
                IDataReader retQuery = dbCon.executeQuery(sqlStr);
                if (retQuery.Read())
                {
                    long idEpb = retQuery.GetInt64(retQuery.GetOrdinal("idEpb"));
                    this.driftPoints = (long)retQuery.GetInt64(retQuery.GetOrdinal("driftPoints"));
                    this.datePb = retQuery.GetString(retQuery.GetOrdinal("date"));
                    this.timePb = retQuery.GetString(retQuery.GetOrdinal("time"));
                }
                retQuery.Close();
                retQuery.Dispose();
            }


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

        }
        public System.Collections.ArrayList GetTable(int from, string relativeToUserName, int nbRead, string trackName, string carName, string Filter, bool onlyQualUser, int MaxGroupQual, int MaxUserGroupQual, int MinUserGroupQual)
        {
            string sqlStr = "";
            string sqlSelect = "";
            string sqlCount = "";
            string sqlWhere = "";
            string sqlOrder = "";
            long cnt = 0;
            IDataReader retQuery;
            IDataReader retQuery2;
            System.Collections.ArrayList list = new System.Collections.ArrayList();
            string filterUserTable = "fi_" + uniqueConnectionId;



            carName = UTILS.utils.replaceGroupCar(carName);
            string[] lcarName = carName.Split('+');
            carName = "'" + carName.Replace("+", "','") + "'";

            relativeToUserName = relativeToUserName.ToLower();


            if (onlyQualUser)
            {
                sqlSelect = "SELECT a.* FROM fi_driftpb a , " + filterUserTable + " b";
                sqlCount = "SELECT COUNT( * ) cnt FROM fi_driftpb a , " + filterUserTable + " b";
                sqlWhere = "WHERE a.carName IN ( " + carName + " )"
                        + " AND a.trackName = '" + trackName + "'"
                        + " AND a.userName = b.userName"
                        + " AND ( b.carName = '' OR a.carName = b.carName )";
            }
            else
            {
                sqlSelect = "SELECT a.* FROM fi_driftpb a";
                sqlCount = "SELECT COUNT(*) cnt FROM fi_driftpb a";
                sqlWhere = "WHERE carName IN ( " + carName + " )"
                        + " AND trackName = '" + trackName + "'";
            }

            if (Filter != "" && !onlyQualUser)
            {
                sqlWhere = sqlWhere + " AND nickNameStripped LIKE '%" + Filter.Replace("'", "''") + "%'";
            }
            cnt = 0;
            if (relativeToUserName != "")
            {
                sqlStr = sqlSelect + " " + sqlWhere + " AND a.userName = '" + relativeToUserName.Replace("'", "''") + "'";
                retQuery = dbCon.executeQuery(sqlStr);
                if (retQuery.Read())
                {
                    dbCon.executeNonQuery("BEGIN TRANSACTION");
                    int driftPoints = (int)retQuery.GetInt64(retQuery.GetOrdinal("driftPoints"));

                    sqlStr = sqlCount + " " + sqlWhere + " AND a.driftPoints <= " + driftPoints;
                    retQuery2 = dbCon.executeQuery(sqlStr);
                    retQuery2.Read();
                    cnt = retQuery2.GetInt32(retQuery2.GetOrdinal("cnt"));
                    retQuery2.Close();
                    retQuery2.Dispose();

                    sqlStr = sqlSelect + " " + sqlWhere + " AND a.driftPoints = " + driftPoints + " ORDER BY driftPoints DESC,date,time";
                    retQuery2 = dbCon.executeQuery(sqlStr);
                    int i = 0;
                    int pos = 0;
                    while (retQuery2.Read())
                    {
                        string userName = retQuery2.GetString(retQuery2.GetOrdinal("userName"));
                        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 driftPoints DESC,date,time LIMIT " + nbRead + " OFFSET  " + from;


            cnt = 0;

            sqlStr = sqlCount + " " + sqlWhere;
            retQuery = dbCon.executeQuery(sqlStr);
            retQuery.Read();
            cnt = retQuery.GetInt32(0);
            retQuery.Close();
            retQuery.Dispose();

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

            dbCon.executeNonQuery("BEGIN TRANSACTION");
            retQuery = dbCon.executeQuery(sqlStr);
            int topPos = from;
            while (retQuery.Read())
            {
                string userName = retQuery.GetString(retQuery.GetOrdinal("userName"));
                string nickName = retQuery.GetString(retQuery.GetOrdinal("nickName"));
                string curr_carName = retQuery.GetString(retQuery.GetOrdinal("carName"));
                int driftPoints = (int)retQuery.GetInt64(retQuery.GetOrdinal("driftPoints"));
                long idEpb = retQuery.GetInt64(retQuery.GetOrdinal("idEpb"));

                list.Add(new DriverLapEntry(dbCon, userName, nickName, curr_carName, trackName));
                int last = list.Count - 1;
                (list[last] as DriverLapEntry).pos = topPos + 1;
                (list[last] as DriverLapEntry).total = (int)cnt;
                //if noPB, remove entry
                //                if ((list[last] as DriverLapEntry).personalBestLapTime == 0)
                //                   list.RemoveAt(last);
                topPos++;

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

            // Mise à jour des poules, par défaut tous le monde est -1 = Not Qualified
            if (onlyQualUser && MaxGroupQual != 0)
            {
                System.Collections.ArrayList group = MkGroup(MaxGroupQual, MaxUserGroupQual, MinUserGroupQual, (int)cnt);
                int j = 0;
                for (int i = 0; i < group.Count; i++)
                {
                    int k = (int)group[i];
                    while (k-- != 0)
                    {
                        if (j >= from && j < (from + list.Count))
                            (list[j - from] as DriverLapEntry).group = i;
                        j++;
                    }
                }
            }
            return list;

        }
        public void addPlayerFilter(string userName, string carName)
        {
            string myTable = "fi_" + uniqueConnectionId;
            string sql = "INSERT INTO " + myTable + "("
                        + "userName"
                        + ",carName"
                        + ") VALUES ("
                        + "'" + userName.ToLower().Replace("'", "''") + "'"
                        + ",'" + carName + "'"
                        + ")";

            dbCon.executeNonQuery(sql);
        }
        public void clearPlayerFilter()
        {
            string sql = "";
            string myTable = "fi_" + uniqueConnectionId;

            dbCon.executeNonQuery("BEGIN TRANSACTION");
            sql = "DELETE FROM " + myTable;
            dbCon.executeNonQuery(sql);
            dbCon.executeNonQuery("COMMIT TRANSACTION");

        }


        public System.Collections.ArrayList GetCars(string trackName)
        {
            string sqlStr = "";
            System.Collections.ArrayList list = new System.Collections.ArrayList();

            sqlStr = "SELECT DISTINCT( carName ) FROM fi_driftpb WHERE trackName = '" + trackName + "'";
            IDataReader retQuery = dbCon.executeQuery( sqlStr );
            while (retQuery.Read())
            {
                list.Add( retQuery.GetString( 0 ) );
            }
            retQuery.Close();
            retQuery.Dispose();
            list.Sort();
            return list;
        }





    }

}
