using System;
using System.Windows.Forms;
using System.Text;
using System.Data.SQLite;
using System.IO;
using System.Collections;
using System.Collections.Generic;
using System.Linq;

namespace MainProject.Libs
{
    public class cSQLite
    {
        public SQLiteConnection cn = null;
        public string lastError = string.Empty;

		public static Hashtable htNames = new Hashtable();        
		public static Dictionary<string, string>  htStr = new Dictionary<string, string>();
		public static Dictionary<string, int>  htInt = new Dictionary<string, int>();
		public static Dictionary<string, long>  htLong = new Dictionary<string, long>();
		public static Dictionary<string, double> htDbl = new Dictionary<string, double>();

		public long TraceStart = DateTime.Now.Ticks;
		public object TraceSync = new object();
		public string TraceFile = Path.Combine(Application.StartupPath, "tracedsql.txt");
		public StringBuilder TracedSQL = new StringBuilder(100);
		
		public void SQLiteTrace(object sender, TraceEventArgs e) {
			lock (TraceSync) {
				TracedSQL.AppendLine(((DateTime.Now.Ticks - TraceStart) / 10000000F).ToString("#0.000").PadLeft(15) + " " + e.Statement);
				if (TracedSQL.Length > 4096) {
					File.AppendAllText(TraceFile, TracedSQL.ToString());
					TracedSQL.Clear();
				}
			}
		}
		
        public bool Connect(string FileName, bool enableTrace = false)
        {
    		string connectionString = "Data Source=" + FileName + ";Version=3;FailIfMissing=True;";
    		try {
    			cn = new SQLiteConnection(connectionString);
    			cn.Open();
    			if (enableTrace) {
	    			cn.Trace += SQLiteTrace;
    			}

    			UpdateStats();
    			
    			cn.BindFunction(new SQLiteRegexp());
    			cn.BindFunction(new SQLiteGetFileName());
    			cn.BindFunction(new SQLiteGetFileNameWithoutExt());
    			
    			if (GetScalar("PRAGMA integrity_check").ToString().ToLower() != "ok") throw new Exception();
    			if (!Execute("VACUUM")) throw new ArgumentException("VACUUM failed!");

    			return true;
    		}
    		catch {}
    		
    		try {
    			cn.Close();
    			Application.DoEvents();
    		} catch {}
    		
    		try {
    			string bakFileName = FileName.Replace(".sqlite", "_empty.sqlite");
    			File.Copy(bakFileName, FileName, true);
    			Application.DoEvents();
    		} catch{}
    		
    		try {
    			cn = new SQLiteConnection(connectionString);
    			cn.Open();
    			return true;
    		}
    		catch (Exception ex) {
    			lastError = "db_Init: " + ex.Message;
    			return false;
    		}

        }


        public bool IsConnected()
        {
            try
            {
                if (cn == null) return false;
                return (cn.State == System.Data.ConnectionState.Open);
            }
            catch (Exception ex)
            {
                lastError = "IsConnected::" + ex.Message;
                return false;
            }
        }

        public bool Close()
        {
            try
            {
                cn.Close();
                cn.Dispose();
                return true;
            }
            catch (Exception ex)
            {
                lastError = "Close::" + ex.Message;
                return false;
            }
        }

        public void UpdateStats() {
        	// il Query Planner di SQLite usa le statistiche per calcolare la strategia migliore
        	// per eseguire la query. Le statistiche non sono aggiornate in automatico.
        	if (IsConnected()) {
				Execute("ANALYZE");
				Execute("ANALYZE sqlite_master");
        	}
        }
        
        public bool MaintRequired() {
			long mm = long.Parse("0" + GetScalar("SELECT COUNT(*) FROM maintenance WHERE Target <= Data AND Target > 0").ToString());
			return (mm > 0 ) ? true: false;;
		}

        public bool Execute(string command, params SQLiteParameter[] sqlParams)
        {
            //esegue un comando di aggiornamento
            try
            {
                using (SQLiteCommand cmd = new SQLiteCommand(command, cn))
                {
                	if (sqlParams != null) cmd.Parameters.AddRange(sqlParams);
                    cmd.ExecuteNonQuery();
                    return true;
                }
            }
            catch (Exception ex)
            {
                lastError = "Execute::" + ex.Message + "\nSQL=" + command;
                return false;
            }
        }

        public object GetScalar(string command, params SQLiteParameter[] sqlParams)
        {
            try 
            {
                using (SQLiteCommand cmd = new SQLiteCommand(command, cn))
                {
                	if (sqlParams != null) cmd.Parameters.AddRange(sqlParams);
                    return cmd.ExecuteScalar();
                }
            }
            catch (Exception ex)
            {
                lastError = "GetScalar::" + ex.Message + "\nSQL=" + command;
                return (object) string.Empty;
            }
    	}

        public string GetScalarStr(string command, params SQLiteParameter[] sqlParams)
        {
            try 
            {
                using (SQLiteCommand cmd = new SQLiteCommand(command, cn))
                {
                	if (sqlParams != null) cmd.Parameters.AddRange(sqlParams);
                	return (string)(cmd.ExecuteScalar() ?? string.Empty);
                }
            }
            catch (Exception ex)
            {
                lastError = "GetScalar::" + ex.Message + "\nSQL=" + command;
                return string.Empty;
            }
    	}

        public int[] GetMultipleInt(string command, params SQLiteParameter[] sqlParams)
        {
        	SQLiteDataReader myReader = null;
        	int number = 1;
        	int[] res = new int[number];

			try
            {
                using (SQLiteCommand ReaderCmd = new SQLiteCommand(command, cn))
                {
                	if (sqlParams != null) ReaderCmd.Parameters.AddRange(sqlParams);
	                myReader = ReaderCmd.ExecuteReader();
		        	number = myReader.FieldCount;
		        	res = new int[number];
	                if (myReader.Read()) {
	                	for (int i = 0; i <number; ++i) res[i] = myReader.GetInt32(i);
	                }
	                myReader.Close();
	                myReader.Dispose();
                }
                return res;
			}
			catch (Exception ex)
			{
				lastError = "GetMultipleInt::" + ex.Message + "\nSQL=" + command;
				return res;
			}
        }
        
        public long[] GetMultipleLong(string command, params SQLiteParameter[] sqlParams)
        {
        	SQLiteDataReader myReader = null;
        	int number = 1;
        	long[] res = new long[number];

			try
            {
                using (SQLiteCommand ReaderCmd = new SQLiteCommand(command, cn))
                {
                	if (sqlParams != null) ReaderCmd.Parameters.AddRange(sqlParams);
	                myReader = ReaderCmd.ExecuteReader();
		        	number = myReader.FieldCount;
		        	res = new long[number];
	                if (myReader.Read()) {
	                	for (int i = 0; i <number; ++i) res[i] = myReader.GetInt64(i);
	                }
	                myReader.Close();
	                myReader.Dispose();
                }
                return res;
			}
			catch (Exception ex)
			{
				lastError = "GetMultipleLong::" + ex.Message + "\nSQL=" + command;
				return res;
			}
        }
        
        public float[] GetMultipleFloat(string command, params SQLiteParameter[] sqlParams)
        {
        	SQLiteDataReader myReader = null;
        	int number = 1;
        	float[] res = new float[number];

			try
            {
                using (SQLiteCommand ReaderCmd = new SQLiteCommand(command, cn))
                {
                	if (sqlParams != null) ReaderCmd.Parameters.AddRange(sqlParams);
	                myReader = ReaderCmd.ExecuteReader();
		        	number = myReader.FieldCount;
		        	res = new float[number];
	                if (myReader.Read()) {
	                	for (int i = 0; i <number; ++i) res[i] = myReader.GetFloat(i);
	                }
	                myReader.Close();
	                myReader.Dispose();
                }
                return res;
			}
			catch (Exception ex)
			{
				lastError = "GetMultipleFloat::" + ex.Message + "\nSQL=" + command;
				return res;
			}
        }
        public string[] GetMultipleString(string command, params SQLiteParameter[] sqlParams)
        {
        	SQLiteDataReader myReader = null;
        	int number = 1;
        	string[] res = new string[1];
        	
			try
            {
                using (SQLiteCommand ReaderCmd = new SQLiteCommand(command, cn))
                {
                	if (sqlParams != null) ReaderCmd.Parameters.AddRange(sqlParams);
	                myReader = ReaderCmd.ExecuteReader();
                	number = myReader.FieldCount;
		        	res = new string[number];
	                if (myReader.Read()) {
	                	for (int i = 0; i <number; ++i) res[i] = myReader.IsDBNull(i)? string.Empty: myReader.GetString(i);
	                }
	                myReader.Close();
	                myReader.Dispose();
                }
                return res;
			}
			catch (Exception ex)
			{
				lastError = "GetMultipleString::" + ex.Message + "\nSQL=" + command;
				return res;
			}
        }

        public int GetScalarInt(string command, params SQLiteParameter[] sqlParams)
        {
        	int res  = 0;
        	try
            {
                using (SQLiteCommand cmd = new SQLiteCommand(command, cn))
                {
                	if (sqlParams != null) cmd.Parameters.AddRange(sqlParams);
                	if (command.Trim().ToLower().StartsWith("insert ")) {
                		using (var t = cn.BeginTransaction()) {
	                		cmd.ExecuteScalar();
	                		res = (int) cn.LastInsertRowId;
	                		t.Commit();
                		}
                	} else {
                		res  = int.Parse(cmd.ExecuteScalar().ToString());
                	}
                	return res;
                }
            }
            catch (Exception ex)
            {
                lastError = "GetScalarInt::" + ex.Message + "\nSQL=" + command;
                return 0;
            }
        }


        public long GetScalarLong(string command, params SQLiteParameter[] sqlParams)
        {
        	long res = 0;
            try
            {
                using (SQLiteCommand cmd = new SQLiteCommand(command, cn))
                {
	                if (sqlParams != null && sqlParams.Length > 0) cmd.Parameters.AddRange(sqlParams);
                	if (command.Trim().ToLower().StartsWith("insert ")) {
                		using (var t = cn.BeginTransaction()) {
	                		cmd.ExecuteScalar();
	                		res = cn.LastInsertRowId;
	                		t.Commit();
                		}
                	} else {
                		res  = long.Parse(cmd.ExecuteScalar().ToString());
                	}
                    cmd.Parameters.Clear();
                    return long.Parse(res.ToString());
                }
            }
            catch (Exception ex)
            {
                lastError = "GetScalarLong::" + ex.Message + "\nSQL=" + command;
                return 0;
            }
        }


        public float GetScalarFloat(string command, params SQLiteParameter[] sqlParams)
        {
            try
            {
                using (SQLiteCommand cmd = new SQLiteCommand(command, cn))
                {
	                if (sqlParams != null && sqlParams.Length > 0) cmd.Parameters.AddRange(sqlParams);
                    object res = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    return float.Parse(res.ToString());
                }
            }
            catch (Exception ex)
            {
                lastError = "GetScalarFloat::" + ex.Message + "\nSQL=" + command;
                return 0.0F;
            }
        }

        public long GetCounter(string counterName)
        {
            return GetCounter(counterName, 1);
        }

        public long GetCounter(string counterName, int step)
        {
            object myobj = 0;
            using (SQLiteCommand cmd = new SQLiteCommand("SELECT GEN_ID(" + counterName + "," + step.ToString() + ") FROM RDB$DATABASE;", cn))
            {
                myobj = cmd.ExecuteScalar();
                return long.Parse(myobj.ToString());
            }
        }
        
        public SQLiteCommand GetReader(string command, ref SQLiteDataReader myReader, params SQLiteParameter[] sqlParams)
        {
        	SQLiteCommand ReaderCmd = null;

            try
            {
                ReaderCmd = new SQLiteCommand(command, cn);
                for (int i = 0; i < sqlParams.Length; ++i) ReaderCmd.Parameters.Add(sqlParams[i]);
                myReader = ReaderCmd.ExecuteReader();
                ReaderCmd.Parameters.Clear();
                return ReaderCmd;
            }
            catch (Exception ex)
            {
                lastError = "GetReader::" + ex.Message + "\nSQL=" + command;
                return ReaderCmd;
            }
        }
        

    }
}
