using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System.Text;

namespace EsotericSQL
{
    public static class SQLTools
    {
        public static Dictionary<string, SQLiteConnection> Connections = new Dictionary<string, SQLiteConnection>();
        public static void OpenFile(string FileName, string Key)
        {
            FileName = Path.GetFullPath(FileName);
            if (!File.Exists(FileName))
                SQLiteConnection.CreateFile(FileName);
            string ConnectionString = string.Format(@"
                Data Source={0};
                Version=3;",
                FileName
            );
            SQLiteConnection Connection = new SQLiteConnection(ConnectionString);
            Connection.Open();
            if (Connections.ContainsKey(Key))
                CloseFile(Key);
            Connections.Add(Key, Connection);
        }
        public static void CloseFile(string Key)
        {
            Connections[Key].Close();
            Connections[Key].Dispose();
            Connections.Remove(Key);
        }
        public static void DeleteFile(string FileName)
        {
            SortedSet<string> KeysToClose = new SortedSet<string>();
            foreach (KeyValuePair<string, SQLiteConnection> C in Connections)
                if (Path.GetFullPath(C.Value.FileName) == Path.GetFullPath(FileName))
                    KeysToClose.Add(C.Key);
            foreach (string Key in KeysToClose)
                CloseFile(Key);
            File.Delete(FileName);
        }

        public static void RunNonQuery(string Key, string Code)
        {
            using (SQLiteCommand CMD = new SQLiteCommand(Code, Connections[Key]))
                CMD.ExecuteNonQuery();
        }
        public static List<string> RunFullQuery(string Key, string Code)
        {
            List<string> Return = new List<string>();
            using (SQLiteCommand CMD = new SQLiteCommand(Code, Connections[Key]))
            using (SQLiteDataReader Reader = CMD.ExecuteReader())
            {
                int N = Reader.FieldCount;
                StringBuilder Row = new StringBuilder(256);
                while (Reader.Read())
                {
                    Row.Clear();
                    for (int i = 0; i < N; i++)
                    {
                        if (i != 0) Row.Append(";");
                        Row.Append(Reader.GetValue(i).ToString());
                    }
                    Return.Add(Row.ToString());
                }
            }
            return Return;
        }

        public static List<bool> RunLogicQuery(string Key, string Code)
        {
            List<bool> Return = new List<bool>();
            using (SQLiteCommand CMD = new SQLiteCommand(Code, Connections[Key]))
            using (SQLiteDataReader Reader = CMD.ExecuteReader())
                while (Reader.Read())
                    Return.Add(Reader.GetBoolean(0));
            return Return;
        }
        public static List<long> RunIntegerQuery(string Key, string Code)
        {
            List<long> Return = new List<long>();
            using (SQLiteCommand CMD = new SQLiteCommand(Code, Connections[Key]))
            using (SQLiteDataReader Reader = CMD.ExecuteReader())
                while (Reader.Read())
                    Return.Add(Reader.GetInt64(0));
            return Return;
        }
        public static List<double> RunRealQuery(string Key, string Code)
        {
            List<double> Return = new List<double>();
            using (SQLiteCommand CMD = new SQLiteCommand(Code, Connections[Key]))
            using (SQLiteDataReader Reader = CMD.ExecuteReader())
                while (Reader.Read())
                    Return.Add(Reader.GetDouble(0));
            return Return;
        }
    }
}