using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.IO;
using System.Text.RegularExpressions;
using NewLife.Reflection;
using XCode.Common;

namespace XCode.DataAccessLayer
{
    class Firebird : FileDbBase
    {
        #region 属性
        /// <summary>返回数据库类型。</summary>
        public override DatabaseType Type
        {
            get { return DatabaseType.Firebird; }
        }

        private static DbProviderFactory _dbProviderFactory;
        /// <summary>提供者工厂</summary>
        static DbProviderFactory DbProviderFactory
        {
            get
            {
                //if (_dbProviderFactory == null) _dbProviderFactory = DbProviderFactories.GetFactory("FirebirdSql.Data.FirebirdClient");
                if (_dbProviderFactory == null)
                {
                    lock (typeof(Firebird))
                    {
                        if (_dbProviderFactory == null) _dbProviderFactory = GetProviderFactory("FirebirdSql.Data.FirebirdClient.dll", "FirebirdSql.Data.FirebirdClient.FirebirdClientFactory");
                    }
                }

                return _dbProviderFactory;
            }
        }

        /// <summary>工厂</summary>
        public override DbProviderFactory Factory
        {
            get { return DbProviderFactory; }
        }

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

            if (!builder.TryGetValue("Database", out var file)) return;

            file = ResolveFile(file);
            builder["Database"] = file;
            FileName = file;
        }
        #endregion

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

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

        #region 分页
        /// <summary>已重写。获取分页</summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="startRowIndex">开始行，0表示第一行</param>
        /// <param name="maximumRows">最大返回行数，0表示所有行</param>
        /// <param name="keyColumn">主键列。用于not in分页</param>
        /// <returns></returns>
        public override String PageSplit(String sql, Int64 startRowIndex, Int64 maximumRows, String keyColumn)
        {
            // 从第一行开始，不需要分页
            if (startRowIndex <= 0)
            {
                if (maximumRows < 1)
                    return sql;
                else
                    return String.Format("{0} rows 1 to {1}", sql, maximumRows);
            }
            if (maximumRows < 1)
                throw new NotSupportedException("不支持取第几条数据之后的所有数据！");
            else
                sql = String.Format("{0} rows {1} to {2}", sql, startRowIndex + 1, maximumRows);
            return sql;
        }

        /// <summary>构造分页SQL</summary>
        /// <remarks>
        /// 两个构造分页SQL的方法，区别就在于查询生成器能够构造出来更好的分页语句，尽可能的避免子查询。
        /// MS体系的分页精髓就在于唯一键，当唯一键带有Asc/Desc/Unkown等排序结尾时，就采用最大最小值分页，否则使用较次的TopNotIn分页。
        /// TopNotIn分页和MaxMin分页的弊端就在于无法完美的支持GroupBy查询分页，只能查到第一页，往后分页就不行了，因为没有主键。
        /// </remarks>
        /// <param name="builder">查询生成器</param>
        /// <param name="startRowIndex">开始行，0表示第一行</param>
        /// <param name="maximumRows">最大返回行数，0表示所有行</param>
        /// <returns>分页SQL</returns>
        public override SelectBuilder PageSplit(SelectBuilder builder, Int64 startRowIndex, Int64 maximumRows)
        {
            // 从第一行开始，不需要分页
            if (startRowIndex <= 0)
            {
                if (maximumRows > 0) builder.OrderBy += String.Format(" rows 1 to {0}", maximumRows);
                return builder;
            }
            if (maximumRows < 1) throw new NotSupportedException("不支持取第几条数据之后的所有数据！");

            builder.OrderBy += String.Format(" rows {0} to {1}", startRowIndex, maximumRows);
            return builder;
        }
        #endregion

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

        //protected override string ReservedWordsStr
        //{
        //    get
        //    {
        //        return "ACTION,ACTIVE,ADD,ADMIN,AFTER,ALL,ALTER,AND,ANY,AS,ASC,ASCENDING,AT,AUTO,AVG,BASE_NAME,BEFORE,BEGIN,BETWEEN,BIGINT,BLOB,BREAK,BY,CACHE,CASCADE,CASE,CAST,CHAR,CHARACTER,CHECK,CHECK_POINT_LENGTH,COALESCE,COLLATE,COLUMN,COMMIT,COMMITTED,COMPUTED,CONDITIONAL,CONNECTION_ID,CONSTRAINT,CONTAINING,COUNT,CREATE,CSTRING,CURRENT,CURRENT_DATE,CURRENT_ROLE,CURRENT_TIME,CURRENT_TIMESTAMP,CURRENT_USER,CURSOR,DATABASE,DATE,DAY,DEBUG,DEC,DECIMAL,DECLARE,DEFAULT,DELETE,DESC,DESCENDING,DESCRIPTOR,DISTINCT,DO,DOMAIN,DOUBLE,DROP,ELSE,END,ENTRY_POINT,ESCAPE,EXCEPTION,EXECUTE,EXISTS,EXIT,EXTERNAL,EXTRACT,FILE,FILTER,FIRST,FLOAT,FOR,FOREIGN,FREE_IT,FROM,FULL,FUNCTION,GDSCODE,GENERATOR,GEN_ID,GRANT,GROUP,GROUP_COMMIT_WAIT_TIME,HAVING,HOUR,IF,IN,INACTIVE,INDEX,INNER,INPUT_TYPE,INSERT,INT,INTEGER,INTO,IS,ISOLATION,JOIN,KEY,LAST,LEFT,LENGTH,LEVEL,LIKE,LOGFILE,LOG_BUFFER_SIZE,LONG,MANUAL,MAX,MAXIMUM_SEGMENT,MERGE,MESSAGE,MIN,MINUTE,MODULE_NAME,MONTH,NAMES,NATIONAL,NATURAL,NCHAR,NO,NOT,NULLIF,NULL,NULLS,LOCK,NUMERIC,NUM_LOG_BUFFERS,OF,ON,ONLY,OPTION,OR,ORDER,OUTER,OUTPUT_TYPE,OVERFLOW,PAGE,PAGES,PAGE_SIZE,PARAMETER,PASSWORD,PLAN,POSITION,POST_EVENT,PRECISION,PRIMARY,PRIVILEGES,PROCEDURE,PROTECTED,RAW_PARTITIONS,RDB$DB_KEY,READ,REAL,RECORD_VERSION,RECREATE,REFERENCES,RESERV,RESERVING,RESTRICT,RETAIN,RETURNING_VALUES,RETURNS,REVOKE,RIGHT,ROLE,ROLLBACK,ROWS_AFFECTED,SAVEPOINT,SCHEMA,SECOND,SEGMENT,SELECT,SET,SHADOW,SHARED,SINGULAR,SIZE,SKIP,SMALLINT,SNAPSHOT,SOME,SORT,SQLCODE,STABILITY,STARTING,STARTS,STATISTICS,SUBSTRING,SUB_TYPE,SUM,SUSPEND,TABLE,THEN,TIME,TIMESTAMP,TO,TRANSACTION,TRANSACTION_ID,TRIGGER,TYPE,UNCOMMITTED,UNION,UNIQUE,UPDATE,UPPER,USER,USING,VALUE,VALUES,VARCHAR,VARIABLE,VARYING,VIEW,WAIT,WEEKDAY,WHEN,WHERE,WHILE,WITH,WORK,WRITE,YEAR,YEARDAY";
        //    }
        //}

        //protected override string ReservedWordsStr { get { return "Log"; } }

        ///// <summary>格式化时间为SQL字符串</summary>
        ///// <param name="dateTime">时间值</param>
        ///// <returns></returns>
        //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);
        }

        ///// <summary>
        ///// 格式化数据为SQL数据
        ///// </summary>
        ///// <param name="field">字段</param>
        ///// <param name="value">数值</param>
        ///// <returns></returns>
        //public override string FormatValue(IDataColumn field, object value)
        //{
        //    if (field.DataType == typeof(String))
        //    {
        //        if (value == null) return field.Nullable ? "null" : "``";
        //        if (String.IsNullOrEmpty(value.ToString()) && field.Nullable) return "null";
        //        return "`" + value + "`";
        //    }
        //    else if (field.DataType == typeof(Boolean))
        //    {
        //        return (Boolean)value ? "'Y'" : "'N'";
        //    }

        //    return base.FormatValue(field, value);
        //}

        /// <summary>长文本长度</summary>
        public override Int32 LongTextLength { get { return 32767; } }

        /// <summary>格式化标识列，返回插入数据时所用的表达式，如果字段本身支持自增，则返回空</summary>
        /// <param name="field">字段</param>
        /// <param name="value">数值</param>
        /// <returns></returns>
        public override String FormatIdentity(IDataColumn field, Object value)
        {
            //return String.Format("GEN_ID(GEN_{0}, 1)", field.Table.TableName);
            return String.Format("next value for SEQ_{0}", field.Table.TableName);
        }

        ///// <summary>系统数据库名</summary>
        //public override String SystemDatabaseName { get { return "Firebird"; } }

        /// <summary>字符串相加</summary>
        /// <param name="left"></param>
        /// <param name="right"></param>
        /// <returns></returns>
        public override String StringConcat(String left, String right) { return (!String.IsNullOrEmpty(left) ? left : "\'\'") + "||" + (!String.IsNullOrEmpty(right) ? right : "\'\'"); }
        #endregion
    }

    /// <summary>Firebird数据库</summary>
    internal class FirebirdSession : FileDbSession
    {
        #region 构造函数
        public FirebirdSession(IDatabase db) : base(db) { }
        #endregion

        #region 基本方法 查询/执行
        static Regex reg_SEQ = new Regex(@"\bGEN_ID\((\w+)\b", RegexOptions.IgnoreCase | RegexOptions.Compiled);
        /// <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)
                {
                    var m = reg_SEQ.Match(sql);
                    if (m != null && m.Success && m.Groups != null && m.Groups.Count > 0)
                        rs = ExecuteScalar<Int64>(String.Format("Select {0}.currval", m.Groups[1].Value));
                }
                Commit();
                return rs;
            }
            catch { Rollback(true); throw; }
            finally
            {
                AutoClose();
            }
        }
        #endregion
    }

    /// <summary>Firebird元数据</summary>
    class FirebirdMetaData : FileDbMetaData
    {
        /// <summary>取得所有表构架</summary>
        /// <returns></returns>
        protected override List<IDataTable> OnGetTables(String[] names)
        {
            var dt = GetSchema(_.Tables, new String[] { null, null, null, "TABLE" });

            // 默认列出所有字段
            var rows = dt?.Rows.ToArray();

            return GetTables(rows, names);
        }

        protected override String GetFieldType(IDataColumn field)
        {
            if (field.DataType == typeof(Boolean)) return "smallint";

            return base.GetFieldType(field);
        }

        /// <summary>数据类型映射</summary>
        private static Dictionary<Type, String[]> _DataTypes = new Dictionary<Type, String[]>
        {
            { typeof(Byte[]), new String[] { "BLOB", "TINYBLOB", "MEDIUMBLOB", "LONGBLOB", "binary({0})", "varbinary({0})" } },
            //{ typeof(TimeSpan), new String[] { "TIME" } },
            //{ typeof(SByte), new String[] { "TINYINT" } },
            { typeof(Byte), new String[] { "TINYINT UNSIGNED" } },
            { typeof(Int16), new String[] { "SMALLINT" } },
            //{ typeof(UInt16), new String[] { "SMALLINT UNSIGNED" } },
            { typeof(Int32), new String[] { "INT", "YEAR", "MEDIUMINT" } },
            //{ typeof(UInt32), new String[] { "MEDIUMINT UNSIGNED", "INT UNSIGNED" } },
            { typeof(Int64), new String[] { "BIGINT" } },
            //{ typeof(UInt64), new String[] { "BIT", "BIGINT UNSIGNED" } },
            { typeof(Single), new String[] { "FLOAT" } },
            { typeof(Double), new String[] { "DOUBLE" } },
            { typeof(Decimal), new String[] { "DECIMAL" } },
            { typeof(DateTime), new String[] { "DATE", "DATETIME", "TIMESTAMP" } },
            { typeof(String), new String[] { "NVARCHAR({0})", "TEXT", "CHAR({0})", "NCHAR({0})", "VARCHAR({0})", "SET", "ENUM", "TINYTEXT", "TEXT", "MEDIUMTEXT", "LONGTEXT" } }
        };

        #region 架构定义
        protected override void CreateDatabase()
        {
            //base.CreateDatabase();

            if (String.IsNullOrEmpty(FileName) || File.Exists(FileName)) return;

            //The miminum you must specify:

            //Hashtable parameters = new Hashtable();
            //parameters.Add("User", "SYSDBA");
            //parameters.Add("Password", "masterkey");
            //parameters.Add("Database", @"c:\database.fdb");
            //FbConnection.CreateDatabase(parameters);

            DAL.WriteLog("创建数据库：{0}", FileName);

            var conn = Database.Factory.CreateConnection();
            //var method = Reflect.GetMethodEx(conn.GetType(), "CreateDatabase", typeof(String));
            var method = conn.GetType().GetMethodEx("CreateDatabase", typeof(String));
            if (method == null) return;

            Reflect.Invoke(null, method, Database.ConnectionString);
        }

        public override String CreateDatabaseSQL(String dbname, String file)
        {
            return String.Empty;
        }

        //public override string DropDatabaseSQL(string dbname)
        //{
        //    return String.Format("Drop Database If Exists {0}", FormatKeyWord(dbname));
        //}

        protected override String GetFieldConstraints(IDataColumn field, Boolean onlyDefine)
        {
            if (field.Nullable)
                return "";
            else
                return " not null";
        }

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

            //String sqlSeq = String.Format("Create GENERATOR GEN_{0}", table.TableName);
            //return sql + "; " + Environment.NewLine + sqlSeq;

            var sqlSeq = String.Format("Create Sequence SEQ_{0}", table.TableName);
            //return sql + "; " + Environment.NewLine + sqlSeq;
            // 去掉分号后的空格，Oracle不支持同时执行多个语句
            return sql + ";" + Environment.NewLine + sqlSeq;
        }

        public override String DropTableSQL(String tableName)
        {
            var sql = base.DropTableSQL(tableName);
            if (String.IsNullOrEmpty(sql)) return sql;

            //String sqlSeq = String.Format("Drop GENERATOR GEN_{0}", tableName);
            //return sql + "; " + Environment.NewLine + sqlSeq;

            var sqlSeq = String.Format("Drop Sequence SEQ_{0}", tableName);
            return sql + "; " + Environment.NewLine + sqlSeq;
        }

        //public override string AddTableDescriptionSQL(IDataTable table)
        //{
        //    if (String.IsNullOrEmpty(table.Description)) return null;

        //    return String.Format("Alter Table {0} Comment '{1}'", FormatKeyWord(table.Name), table.Description);
        //}

        //public override string AlterColumnSQL(IDataColumn field)
        //{
        //    return String.Format("Alter Table {0} Modify Column {1}", FormatKeyWord(field.Table.Name), FieldClause(field, false));
        //}

        //public override string AddColumnDescriptionSQL(IDataColumn field)
        //{
        //    // 返回String.Empty表示已经在别的SQL中处理
        //    return String.Empty;

        //    //if (String.IsNullOrEmpty(field.Description)) return null;

        //    //return String.Format("Alter Table {0} Modify {1} Comment '{2}'", FormatKeyWord(field.Table.Name), FormatKeyWord(field.Name), field.Description);
        //}
        #endregion
    }
}