using MgTestAdmin.Model;
using SqliteDotNet;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MgTestAdmin
{
    public static class SqliteDbExtensions
    {
        //Checks if a file is read only
        public static bool IsReadOnly(string fileName)
        {
            bool readOnly = false;
            int i = 0;
            FileInfo file = new FileInfo(fileName);
            //Get all attributes and put them into array
            string[] attributes = file.Attributes.ToString().Split(',');

            //Parse the array
            while ((i < attributes.Length) && ("ReadOnly" != attributes[i]))
            {
                i++;
            }

            if (i < attributes.Length)
            {
                readOnly = true;
            }
            return readOnly;
        }

        public static string[] GetTables(this SqliteDb db)
        {
            var tables = new List<string>();
            var vm = new SqliteVm(db, true);
            try
            {
                string tableList = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;";

                int fini = vm.Execute(tableList);
                while (fini == Sqlite.Row)
                {
                    string tableName = vm.GetString("name");

                    tables.Add(tableName);

                    fini = vm.NextRow();
                }
            }
            finally
            {
                vm.SqlFinalize();
            }
            return tables.ToArray();
        }

        public static bool HasTestCaseTable(this SqliteDb db)
        {
            string sqlStmt = "SELECT name FROM sqlite_master WHERE type='table' AND name='TestCase';";
            var vm = new SqliteVm(db, true);
            bool exists = false;
            try
            {
                exists = (vm.Execute(sqlStmt) == Sqlite.Row);
            }
            finally
            {
                vm.SqlFinalize();
            }
            return exists;
        }

        public static TestCaseInfo[] GetTests(this SqliteDb db)
        {
            var tests = new List<TestCaseInfo>();
            var vm = new SqliteVm(db, true);
            try
            {
                var status = vm.Execute("Select TestName, ExecuteSequence from TestCase");
                while (Sqlite.Row == status)
                {
                    tests.Add(new TestCaseInfo
                    {
                        ExecuteSequence = vm.GetInt(nameof(TestCaseInfo.ExecuteSequence)),
                        TestName = vm.GetString(nameof(TestCaseInfo.TestName))
                    });
                    status = vm.NextRow();
                }
            }
            finally
            {
                vm.SqlFinalize();
            }
            return tests.ToArray();
        }

        public static TestCase GetTest(this SqliteDb db, string name)
        {
            var vm = new SqliteVm(db, true);
            try
            {
                var status = vm.Execute($"Select * from TestCase where TestName=\"{name}\"");
                if ((Sqlite.Done == status) || (Sqlite.Row == status))
                {
                    string psets = vm.GetString(nameof(TestCase.ParamSets));
                    var test = new TestCase
                    {
                        TestName = name,
                        ExecuteSequence = vm.GetInt(nameof(TestCase.ExecuteSequence)),
                        Description = vm.GetString(nameof(TestCase.Description)),
                        Prerequsite = vm.GetString(nameof(TestCase.Prerequsite)),
                        TestType = vm.GetString(nameof(TestCase.TestType)),
                        ParamSets = psets.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries).Select(s =>
                        {
                            if (int.TryParse(s, out var i))
                                return i;
                            return (int?)i;
                        }).Where(i => i.HasValue).Select(i => i.Value).ToArray()
                    };
                    return test;
                }
            }
            finally
            {
                vm.SqlFinalize();
            }
            return null;
        }

        public static ParameterSetInfo[] GetParamSets(this SqliteDb db)
        {
            var paramSets = new List<ParameterSetInfo>();
            var vm = new SqliteVm(db, true);
            try
            {
                var status = vm.Execute($"Select ParamSet, ParamValue from Params where ParamName = 'OPERATION' group by ParamSet, ParamValue");
                while (Sqlite.Row == status)
                {
                    paramSets.Add(new ParameterSetInfo
                    {
                        Id = vm.GetInt("ParamSet"),
                        Operation = vm.GetString("ParamValue")
                    });

                    status = vm.NextRow();
                }
            }
            finally
            {
                vm.SqlFinalize();
            }
            return paramSets.ToArray();
        }

        public static ParameterSet GetParamSet(this SqliteDb db, int paramSet)
        {
            var ps = new ParameterSet { Id = paramSet };
            var param = new List<Parameter>();
            var vm = new SqliteVm(db, true);
            try
            {
                var status = vm.Execute($"Select ParamName, ParamValue from Params where ParamSet={paramSet}");
                while (Sqlite.Row == status)
                {
                    param.Add(new Parameter
                    {
                        ParamName = vm.GetString(nameof(Parameter.ParamName)),
                        ParamValue = vm.GetString(nameof(Parameter.ParamValue))
                    });

                    status = vm.NextRow();
                }
                ps.Params = param.ToArray();
            }
            finally
            {
                vm.SqlFinalize();
            }
            return ps;
        }

        public static void CreateOrUpdateTest(this SqliteDb db, TestCase test)
        {
            var vm = new SqliteVm(db, true);
            try
            {
                string sql;
                if (Sqlite.Row == vm.Execute($"Select * From TestCase where TestName=\'{test.TestName}\'"))
                {
                    sql = $"UPDATE TestCase SET ExecuteSequence = {test.ExecuteSequence}, ParamSets=\"{string.Join(",", test.ParamSets)}\", Description = \"{test.Description}\", TestType = \"{test.TestType}\", Prerequisite = \"{test.Prerequsite}\" WHERE TestName = \'{test.TestName}\'";
                }
                else
                {
                    sql = $"INSERT INTO TestCase(ExecuteSequence, TestName, ParamSets, Description, TestType, Prerequisite) VALUES ({test.ExecuteSequence}, \'{test.TestName}\', \'{string.Join(",", test.ParamSets)}\', \'{test.Description}\', \'{test.TestType}\', \'{test.Prerequsite}\')";
                }
                vm.Execute(sql);
            }
            finally
            {
                vm.SqlFinalize();
            }
        }

        public static bool DeleteTest(this SqliteDb db, TestCase test)
        {
            bool deleted = true;
            var vm = new SqliteVm(db, true);
            try
            {
                foreach (var p in test.ParamSets)
                {
                    db.DeleteParamSet(p, test.TestType);
                }
                vm.Execute($"Delete from TestCase where TestName = {test.TestName}");
            }
            finally
            {
                vm.SqlFinalize();
            }
            return deleted;
        }

        public static bool DeleteParamSet(this SqliteDb db, int paramSet, string testType)
        {
            bool deleted = true;
            var vm = new SqliteVm(db, true);
            try
            {
                vm.Execute($"DELETE FROM Params WHERE ParamSet = {paramSet}");
                //Check which result table to modify.
                //If the test type is invalid nothing will be deleted. Shall we display error message?
                if ("Api" == testType)
                {
                    vm.Execute($"DELETE FROM ApiTestResults WHERE ParamSet = {paramSet}");
                }
                else if ("Http" == testType)
                {
                    vm.Execute($"DELETE FROM HttpTestResults WHERE ParamSet = {paramSet}");
                }
            }
            finally
            {
                vm.SqlFinalize();
            }
            return deleted;
        }

        public static TestResultSet GetTestResultSet(this SqliteDb db, int paramSet)
        {
            return new TestResultSet
            {
                ApiResult = db.GetTestResult(paramSet, "Api"),
                HttpResult = db.GetTestResult(paramSet, "Http")
            };
        }

        public static TestResult GetTestResult(this SqliteDb db, int paramSet, string testType)
        {
            TestResult res = null;
            var vm = new SqliteVm(db, true);
            try
            {
                int status = Sqlite.Done;
                if ("Api" == testType)
                {
                    status = vm.Execute($"Select * from ApiTestResults where ParamSet = {paramSet}");
                }
                else if ("Http" == testType)
                {
                    status = vm.Execute($"Select * from HttpTestResults where ParamSet = {paramSet}");
                }

                if (Sqlite.Row == status)
                {
                    res = new TestResult
                    {
                        ParamSet = paramSet,
                        Description = vm.GetString(nameof(TestResult.Description)),
                        ContentType = vm.GetString(nameof(TestResult.ContentType)),
                        Result = vm.GetString(nameof(TestResult.Result))
                    };
                }
            }
            finally
            {
                vm.SqlFinalize();
            }
            return res;
        }

        public static void LoadFromDump(this SqliteDb db, string dumpPath)
        {
            var vm = new SqliteVm(db, true);
            try
            {
                var content = File.ReadAllText(dumpPath);
                vm.Execute(content);
            }
            finally
            {
                vm.SqlFinalize();
            }
        }

        public static void CreateTemplate(this SqliteDb db)
        {
            var templateVm = new SqliteVm(db, true);
            try
            {
                templateVm.Execute("CREATE TABLE TestCase (ExecuteSequence INTEGER, TestName TEXT, ParamSets TEXT, Description TEXT, TestType TEXT, Prerequisite TEXT)");
                templateVm.Execute("CREATE TABLE Params (ParamSet INTEGER, ParamName TEXT, ParamValue TEXT)");
                templateVm.Execute("CREATE TABLE CommonParams (ParamName text, ParamValue text)");
                templateVm.Execute("CREATE TABLE ApiTestResults (Description TEXT, ParamSet INTEGER, ContentType TEXT, Result TEXT)");
                templateVm.Execute("CREATE TABLE HttpTestResults (Description TEXT, ParamSet INTEGER, ContentType TEXT, Result BLOB)");
            }
            finally
            {
                templateVm.SqlFinalize();
            }
        }

        public static int GetNewParamSetId(this SqliteDb db)
        {
            var vm = new SqliteVm(db, true);
            int id = -1;
            try
            {
                if (vm.Execute("Select COUNT(*) AS Total From Params") == Sqlite.Row)
                {
                    int count = vm.GetInt("Total");
                    if (count > 0 && vm.Execute("Select MAX(ParamSet) + 1 AS NewID From Params") == Sqlite.Row)
                    {
                        id = vm.GetInt("NewID");
                    }
                    else
                    {
                        id = 1;
                    }
                }
            }
            finally
            {
                vm.SqlFinalize();
            }
            return id;
        }

        public static void GenerateDump(this SqliteDb db, string dumpPath)
        {
            using (var sw = new StreamWriter(dumpPath))
            {
                var psi = new ProcessStartInfo("sqlite3.exe", $"\"{db.GetName()}\" .dump");
                psi.UseShellExecute = false;
                psi.RedirectStandardOutput = true;
                var proc = new Process()
                {
                    StartInfo = psi
                };
                proc.OutputDataReceived += (s, e) => sw.WriteLine(e.Data);
                proc.Start();
                proc.BeginOutputReadLine();
                proc.WaitForExit();
            }
        }
    }
}