using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Reflection;
using NewLife.IO;

namespace XCode.DataAccessLayer
{
    class Access : FileDbBase
    {
        #region 属性
        /// <summary>返回数据库类型。外部DAL数据库类请使用Other</summary>
        public override DatabaseType Type { get { return DatabaseType.Access; } }

        private static DbProviderFactory _dbProviderFactory;
        /// <summary>工厂</summary>
        public override DbProviderFactory Factory
        {
            get
            {
                if (_dbProviderFactory == null)
                {
                    _dbProviderFactory = OleDbFactory.Instance;
                }
                return _dbProviderFactory;
            }
        }

        protected override String DefaultConnectionString
        {
            get
            {
                var builder = Factory.CreateConnectionStringBuilder();
                if (builder != null)
                {
                    var name = Path.GetTempFileName();
                    FileSource.ReleaseFile(Assembly.GetExecutingAssembly(), "Database.mdb", name, true);

                    builder[_.DataSource] = name;
                    builder["Provider"] = "Microsoft.Jet.OLEDB.4.0";
                    return builder.ToString();
                }

                return base.DefaultConnectionString;
            }
        }
        #endregion

        #region 方法
        /// <summary>创建数据库会话</summary>
        /// <returns></returns>
        protected override IDbSession OnCreateSession() { return new AccessSession(this); }

        /// <summary>创建元数据对象</summary>
        /// <returns></returns>
        protected override IMetaData OnCreateMetaData() { return new AccessMetaData(); }

        public override Boolean Support(String providerName)
        {
            providerName = providerName.ToLower();
            if (providerName.Contains("microsoft.jet.oledb")) return true;
            if (providerName.Contains("access")) return true;
            if (providerName.Contains("oledb")) return true;

            return false;
        }

        protected override void OnSetConnectionString(ConnectionStringBuilder builder)
        {
            base.OnSetConnectionString(builder);

            // 特别处理一下Excel
            var fn = DatabaseName;
            if (!fn.IsNullOrEmpty())
            {
                var ext = Path.GetExtension(fn);
                if (ext.EqualIgnoreCase(".xls")) builder.TryAdd("Extended Properties", "Excel 8.0");
            }
        }
        #endregion

        #region 数据库特性
        ///// <summary>当前时间函数</summary>
        //public override String DateTimeNow { get { return "now()"; } }

        ///// <summary>最小时间</summary>
        //public override DateTime DateTimeMin { get { return DateTime.MinValue; } }

        /// <summary>长文本长度</summary>
        public override Int32 LongTextLength => 255;

        public override String FormatName(String name)
        {
            if (!String.IsNullOrEmpty(name) && name.Contains("$"))
                return FormatKeyWord(name);
            else
                return base.FormatName(name);
        }

        /// <summary>格式化时间为SQL字符串</summary>
        /// <param name="dateTime">时间值</param>
        /// <returns></returns>
        public override String FormatDateTime(DateTime dateTime) { return "#" + dateTime.ToFullString() + "#"; }
        //public override String FormatDateTime(DateTime dateTime)
        //{
        //    return String.Format("#{0:yyyy-MM-dd HH:mm:ss}#", dateTime);
        //}

        /// <summary>格式化关键字</summary>
        /// <param name="keyWord">关键字</param>
        /// <returns></returns>
        public override String FormatKeyWord(String keyWord)
        {
            //if (String.IsNullOrEmpty(keyWord)) throw new ArgumentNullException("keyWord");
            if (String.IsNullOrEmpty(keyWord)) return keyWord;

            if (keyWord.StartsWith("[") && keyWord.EndsWith("]")) return keyWord;

            return String.Format("[{0}]", keyWord);
            //return keyWord;
        }

        /// <summary>格式化数据为SQL数据</summary>
        /// <param name="field">字段</param>
        /// <param name="value">数值</param>
        /// <returns></returns>
        public override String FormatValue(IDataColumn field, Object value)
        {
            if (field != null && field.DataType == typeof(Boolean) || value != null && value.GetType() == typeof(Boolean))
            {
                if (value == null) return field.Nullable ? "null" : "";

                return value.ToString();
            }

            return base.FormatValue(field, value);
        }
        #endregion

        #region 分页
        public override SelectBuilder PageSplit(SelectBuilder builder, Int64 startRowIndex, Int64 maximumRows)
        {
            return MSPageSplit.PageSplit(builder, startRowIndex, maximumRows, false, b => CreateSession().QueryCount(b));
        }
        #endregion

        #region 平台检查
        /// <summary>是否支持</summary>
        public static void CheckSupport()
        {
            var module = typeof(Object).Module;

            module.GetPEKind(out var kind, out var machine);

            if (machine != ImageFileMachine.I386) throw new NotSupportedException("64位平台不支持OLEDB驱动！");
        }
        #endregion
    }

    /// <summary>Access数据库</summary>
    internal class AccessSession : FileDbSession
    {
        #region 构造函数
        public AccessSession(IDatabase db) : base(db)
        {
            Access.CheckSupport();
        }
        #endregion

        #region 方法
        ///// <summary>打开。已重写，为了建立数据库</summary>
        //public override void Open()
        //{
        //    Access.CheckSupport();

        //    base.Open();
        //}
        #endregion

        #region 基本方法 查询/执行
        /// <summary>执行插入语句并返回新增行的自动编号</summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="type">命令类型，默认SQL文本</param>
        /// <param name="ps">命令参数</param>
        /// <returns>新增行的自动编号</returns>
        public override Int64 InsertAndGetIdentity(String sql, CommandType type = CommandType.Text, params IDataParameter[] ps)
        {
            BeginTransaction(IsolationLevel.Serializable);
            try
            {
                Int64 rs = Execute(sql, type, ps);
                if (rs > 0) rs = ExecuteScalar<Int64>("Select @@Identity");
                Commit();
                return rs;
            }
            catch { Rollback(true); throw; }
            //finally
            //{
            //    AutoClose();
            //}
        }
        #endregion
    }

    /// <summary>Access元数据</summary>
    class AccessMetaData : FileDbMetaData
    {
        #region 构架
        protected override List<IDataTable> OnGetTables(String[] names)
        {
            var dt = GetSchema(_.Tables, null);
            if (dt?.Rows == null || dt.Rows.Count < 1) return null;

            // 默认列出所有字段
            var rows = dt.Select(String.Format("{0}='Table' Or {0}='View'", "TABLE_TYPE"));

            return GetTables(rows, names);
        }

        protected override void FixField(IDataColumn field, DataRow drColumn)
        {
            base.FixField(field, drColumn);

            //// 字段标识
            //var flag = GetDataRowValue<Int64>(drColumn, "COLUMN_FLAGS");

            //Boolean? isLong = null;

            //if (Int32.TryParse(GetDataRowValue<String>(drColumn, "DATA_TYPE"), out var id))
            //{
            //    var drs = FindDataType(field, "" + id, isLong);
            //    if (drs != null && drs.Length > 0)
            //    {
            //        var typeName = GetDataRowValue<String>(drs[0], "TypeName");
            //        field.RawType = typeName;

            //        if (TryGetDataRowValue(drs[0], "DataType", out typeName)) field.DataType = typeName.GetTypeEx();

            //        // 修正备注类型
            //        if (field.DataType == typeof(String) && drs.Length > 1)
            //        {
            //            isLong = (flag & 0x80) == 0x80;
            //            drs = FindDataType(field, "" + id, isLong);
            //            if (drs != null && drs.Length > 0)
            //            {
            //                typeName = GetDataRowValue<String>(drs[0], "TypeName");
            //                field.RawType = typeName;
            //            }
            //        }
            //    }
            //}

            //// 修正原始类型
            //if (TryGetDataRowValue(drDataType, "TypeName", out String typeName)) field.RawType = typeName;
        }

        //protected override void FixField(IDataColumn field, DataRow drColumn, DataRow drDataType)
        //{
        //    base.FixField(field, drColumn, drDataType);

        //    // 修正原始类型
        //    if (TryGetDataRowValue(drDataType, "TypeName", out String typeName)) field.RawType = typeName;
        //}

        /// <summary>获取索引</summary>
        /// <param name="table"></param>
        /// <param name="indexes">索引</param>
        /// <param name="indexColumns">索引列</param>
        /// <returns></returns>
        protected override List<IDataIndex> GetIndexes(IDataTable table, DataTable indexes, DataTable indexColumns)
        {
            var list = base.GetIndexes(table, indexes, indexColumns);
            if (list != null && list.Count > 0)
            {
                // Access的索引直接以索引字段的方式排布，所以需要重新组合起来
                var dic = new Dictionary<String, IDataIndex>();
                foreach (var item in list)
                {
                    if (!dic.TryGetValue(item.Name, out var di))
                    {
                        dic.Add(item.Name, item);
                    }
                    else
                    {
                        var ss = new List<String>(di.Columns);
                        if (item.Columns != null && item.Columns.Length > 0 && !ss.Contains(item.Columns[0]))
                        {
                            ss.Add(item.Columns[0]);
                            di.Columns = ss.ToArray();
                        }
                    }
                }
                list.Clear();
                foreach (var item in dic.Values)
                {
                    list.Add(item);
                }
            }
            return list;
        }

        protected override String GetFieldConstraints(IDataColumn field, Boolean onlyDefine)
        {
            var str = base.GetFieldConstraints(field, onlyDefine);

            if (field.Identity) str = " AUTOINCREMENT(1,1)" + str;

            return str;
        }
        #endregion

        #region 反向工程创建表
        //protected override void CreateTable(StringBuilder sb, IDataTable table, Boolean onlySql)
        //{
        //    base.CreateTable(sb, table, onlySql);

        //    if (!onlySql)
        //    {
        //        IDatabase entityDb = null;
        //        foreach (IDataColumn dc in table.Columns)
        //        {
        //            // 如果实体存在默认值，则增加
        //            if (!String.IsNullOrEmpty(dc.Default))
        //            {
        //                var tc = Type.GetTypeCode(dc.DataType);
        //                String dv = dc.Default;
        //                // 特殊处理时间
        //                if (tc == TypeCode.DateTime)
        //                {
        //                    if (entityDb != null && dv == entityDb.DateTimeNow) dc.Default = Database.DateTimeNow;
        //                }
        //                // 特殊处理Guid
        //                else if (tc == TypeCode.String || dc.DataType == typeof(Guid))
        //                {
        //                    if (entityDb != null && dv == entityDb.NewGuid) dc.Default = Database.NewGuid;
        //                }

        //                PerformSchema(sb, onlySql, DDLSchema.AddDefault, dc);

        //                // 还原
        //                dc.Default = dv;
        //            }
        //        }
        //    }
        //}

        public override String CreateTableSQL(IDataTable table)
        {
            var sql = base.CreateTableSQL(table);

            var pks = table.PrimaryKeys;
            if (String.IsNullOrEmpty(sql) || pks.Length < 2) return sql;

            // 处理多主键
            var names = pks.Select(e => e.ColumnName).ToArray();
            var di = ModelHelper.GetIndex(table, names);
            if (di == null)
            {
                di = table.CreateIndex();
                di.PrimaryKey = true;
                di.Unique = true;
                di.Columns = names;
            }
            // Access里面的主键索引名必须叫这个
            di.Name = "PrimaryKey";

            sql += ";" + Environment.NewLine;
            sql += CreateIndexSQL(di);
            return sql;
        }

        public override String CreateIndexSQL(IDataIndex index)
        {
            var sql = base.CreateIndexSQL(index);
            if (String.IsNullOrEmpty(sql) || !index.PrimaryKey) return sql;

            return sql + " WITH PRIMARY";
        }
        #endregion

        #region 数据类型
        //protected override DataRow[] FindDataType(IDataColumn field, String typeName, Boolean? isLong)
        //{
        //    var drs = base.FindDataType(field, typeName, isLong);
        //    if (drs != null && drs.Length > 0) return drs;

        //    //// 处理SByte类型
        //    //if (typeName == typeof(SByte).FullName)
        //    //{
        //    //    typeName = typeof(Byte).FullName;
        //    //    drs = base.FindDataType(field, typeName, isLong);
        //    //    if (drs != null && drs.Length > 0) return drs;
        //    //}

        //    var dt = DataTypes;
        //    if (dt == null) return null;

        //    // 转为整数
        //    if (!Int32.TryParse(typeName, out var n)) return null;

        //    try
        //    {
        //        if (isLong == null)
        //        {
        //            drs = dt.Select(String.Format("NativeDataType={0}", n));
        //            if (drs == null || drs.Length < 1) drs = dt.Select(String.Format("ProviderDbType={0}", n));
        //        }
        //        else
        //        {
        //            drs = dt.Select(String.Format("NativeDataType={0} And IsLong={1}", n, isLong.Value));
        //            if (drs == null || drs.Length < 1) drs = dt.Select(String.Format("ProviderDbType={0} And IsLong={1}", n, isLong.Value));
        //        }
        //    }
        //    catch { }

        //    return drs;
        //}

        protected override String GetFieldType(IDataColumn field)
        {
            var typeName = base.GetFieldType(field);

            //if (typeName.StartsWith("VarChar")) return typeName.Replace("VarChar", "Text");
            if (field.Identity) return null;

            return typeName;
        }

        /// <summary>数据类型映射</summary>
        private static Dictionary<Type, String[]> _DataTypes = new Dictionary<Type, String[]>
        {
            { typeof(Byte[]), new String[] { "binary", "varbinary", "blob", "image", "general", "oleobject" } },
            { typeof(Guid), new String[] { "uniqueidentifier", "guid" } },
            { typeof(Boolean), new String[] { "bit", "yesno", "logical", "bool", "boolean" } },
            { typeof(Byte), new String[] { "tinyint" } },
            { typeof(Int16), new String[] { "smallint" } },
            { typeof(Int32), new String[] { "int" } },
            { typeof(Int64), new String[] { "integer", "counter", "autoincrement", "identity", "long", "bigint" } },
            { typeof(Single), new String[] { "single" } },
            { typeof(Double), new String[] { "real", "float", "double" } },
            { typeof(Decimal), new String[] { "money", "decimal", "currency", "numeric" } },
            { typeof(DateTime), new String[] { "datetime", "smalldate", "timestamp", "date", "time" } },
            { typeof(String), new String[] { "nvarchar({0})", "ntext", "varchar({0})", "memo({0})", "longtext({0})", "note({0})", "text({0})", "string({0})", "char({0})", "char({0})" } }
        };
        #endregion
    }
}