using Dapper;
using MySql.Data.MySqlClient;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using Wicture.DbRESTFul.Configuration;
using Wicture.DbRESTFul.Resources.Csi;

namespace Wicture.DbRESTFul.PMT.SpecGenerators
{
    public class ColumnSpec
    {
        public string table { get; set; }
        public string name { get; set; }
        public string type { get; set; }
        public string description { get; set; }
        public string @default { get; set; }
        public bool primary_key { get; set; }
        public bool nullable { get; set; }
    }

    public class TableSpec
    {
        public string title { get; set; }
        public string name { get; set; }
        public string description { get; set; }

        public List<ColumnSpec> columns { get; set; }
    }

    public class DbSpecSource
    {
        public string projectName { get; set; }
        public string moduleName { get; set; }
        public List<TableSpec> tables { get; set; }

        public DatabaseType databaseType { get; set; }

        public IDbConnection GetConnection(string connectionString, DatabaseType DatabaseType = DatabaseType.MySQL)
        {
            if (DatabaseType == DatabaseType.SQLServer) return new SqlConnection(connectionString) as IDbConnection;
            else return new MySqlConnection(connectionString) as IDbConnection;
        }
    }


    public class SpecHelper
    {
        private readonly string databaseName = string.Empty;
        private readonly string query_table_names = "SELECT `TABLE_NAME` AS `name`, `TABLE_COMMENT` AS description FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA` = '{0}' AND `TABLE_TYPE` = 'BASE TABLE';";
        private readonly string query_table_names_sqlserver = "SELECT st.name,sep.VALUE  [description] FROM 	sys.tables st  LEFT JOIN sys.extended_properties sep ON st.object_id = sep.major_id AND sep.minor_id=0;";
        private readonly string query_all_column_names = "SELECT `TABLE_NAME` AS `table`, `COLUMN_NAME` AS `name`, `COLUMN_TYPE` AS `type`, (CASE `IS_NULLABLE` WHEN 'NO' THEN 0 ELSE 1 END) AS nullable, (CASE `COLUMN_KEY` WHEN 'PRI' THEN 1 ELSE 0 END) AS primary_key, `COLUMN_DEFAULT` AS `default`,`COLUMN_COMMENT` AS description FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA` = '{0}';";
        private readonly string query_all_column_names_sqlserver = "SELECT st.name [table],	sc.is_nullable [nullable],	sc.name [name],	sep.VALUE [description],	TYPE_NAME(sc.system_type_id) [type],0 [default]  FROM 	sys.tables st INNER JOIN sys.columns sc ON st.object_id = sc.object_id LEFT JOIN sys.extended_properties sep ON st.object_id = sep.major_id AND sc.column_id = sep.minor_id;";
        private readonly string query_api_for_project = "SELECT a.* FROM api AS a LEFT JOIN project AS p ON p.id = a.projectId WHERE p.name = @projectName;";
        private readonly string query_module_for_project = "select m.`name` as moduleName, m.description as moduleTitle from module as m join project as p on p.id = m.projectId where p.`name` = @projectName;";
        private readonly string get_databaseType = "SELECT databaseType FROM project WHERE id=@projectId;";
        private static readonly List<string> ObjectProperties = new List<string> { "cache", "implementation", "parameter", "result", "mock" };

        private readonly DbSpecSource source;
        private string pmtConncetString = "";
        private string projectConncetString = "";


        public SpecHelper(string connectionString, int projectId = -1)
        {
            source = new DbSpecSource();
            projectConncetString = connectionString;
            using (var conn = source.GetConnection(connectionString))
            {
                databaseName = conn.Database;
            }

            pmtConncetString = ConfigurationManager.Settings.GetConfig<CsiSectionConfig>("CSI").DbConnection.ReadConnectionString;

            if (projectId != -1)
            {
                using (var conn = source.GetConnection(pmtConncetString))
                {
                    var databaseType = SqlMapper.Query<int?>(conn, get_databaseType, new { projectId = projectId }).FirstOrDefault();
                    source.databaseType = databaseType == 1 ? DatabaseType.SQLServer : DatabaseType.MySQL;
                }
            }
        }

        public List<TableSpec> ListTables()
        {
            using (var conn = source.GetConnection(projectConncetString, source.databaseType))
            {
                if (conn.State == ConnectionState.Closed) conn.Open();
                //choose sqls
                var query_all_columns = source.databaseType == DatabaseType.MySQL ? query_all_column_names : query_all_column_names_sqlserver;
                var query_all_tables = source.databaseType == DatabaseType.MySQL ? query_table_names : query_table_names_sqlserver;
                //get columns
                var columns = SqlMapper.Query<ColumnSpec>(conn, string.Format(query_all_columns, databaseName)).ToList();
                //get tables
                var data = SqlMapper.Query<TableSpec>(conn, string.Format(query_all_tables, databaseName));
                //make result for return
                data.ForEach(t =>
                {
                    t.title = t.description;
                    t.columns = columns.Where(c => c.table == t.name).ToList();
                });
                return data.ToList();
            }
        }

        public List<JObject> ListAPIForProject(string projectName, string moduleName)
        {
            var result = new List<JObject>();
            using (var conn = source.GetConnection(pmtConncetString))
            {
                var param = new { projectName = projectName };
                IEnumerable<dynamic> apis = SqlMapper.Query(conn, query_api_for_project, param);

                foreach (var item in apis)
                {
                    // If moduleName is not specified, list all.
                    if (string.IsNullOrEmpty(moduleName) || item.module == moduleName)
                    {
                        JObject ca = JObject.FromObject(item);
                        ObjectProperties.ForEach(p =>
                        {
                            ca[p] = JToken.Parse(ca.Value<string>(p) ?? "{}");

                            //处理mock属性，将其input与output参数取出来
                            if (p == "mock")
                            {
                                var mockerArr = ca.Value<JArray>(p) ?? new JArray();
                                //var mockerArr = JArray.FromObject(ca.Value<string>(p));

                                if (mockerArr != null && mockerArr.Count > 0)
                                {
                                    ca["input"] = mockerArr[0].Value<JObject>("input");
                                    ca["output"] = mockerArr[0].Value<JObject>("output");
                                }
                            }
                        }
                        );
                        result.Add(ca);
                    }
                }
            }

            return result;
        }

        public dynamic ListModules(string projectName)
        {
            using (var conn = source.GetConnection(pmtConncetString))
            {
                var param = new { projectName = projectName };
                return SqlMapper.Query(conn, query_module_for_project, param);
            }
        }
    }
}