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

namespace HMI.Core.Util
{
    public class Sqlite3Helper : IDisposable
    {
        public string DataBaseFile { get; private set; } = string.Empty;

        private string ConnectString = string.Empty;

        public const int MAX_ATTACHED = 30;

        private SQLiteConnection conn = null;

        public Sqlite3Helper(string DBFile)
        {
            DataBaseFile = DBFile;
            ConnectString = "DataSource=" + DataBaseFile + ";Version=3;Pooling=False;Max Pool Size=100;";
            conn = new SQLiteConnection(ConnectString);
            Connect();
        }

        public static bool CreateDatabase(string DataBaseFile)
        {
            try
            {
                SQLiteConnection.CreateFile(DataBaseFile);
                return true;
            }
            catch (Exception ex)
            {
                throw new Exception($"Create Database [${DataBaseFile}] Failed.", ex);
            }
        }

        public int Execute(string Sql)
        {
            try
            {
                using (var cmd = new SQLiteCommand(conn))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = Sql;
                    return cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                string message = "ExecuteSql Error:" + ex.Message;
                throw new Exception(message);
            }
        }

        public long InsertWithId(string Sql)
        {
            try
            {
                if (Sql.EndsWith(";"))
                {
                    Sql += "SELECT last_insert_rowid();";
                }
                else
                {
                    Sql += ";SELECT last_insert_rowid();";
                }
                using (var cmd = new SQLiteCommand(Sql, conn))
                {
                    return (long)cmd.ExecuteScalar();
                }
            }
            catch (Exception ex)
            {
                string message = "ExecuteSql Error:" + ex.Message;
                throw new Exception(message);
            }
        }

        public object SelectOne(string Sql)
        {
            try
            {
                using (var cmd = new SQLiteCommand(Sql, conn))
                {
                    return cmd.ExecuteScalar();
                }
            }
            catch (Exception ex)
            {
                string message = "ExecuteSql Error:" + ex.Message;
                throw new Exception(message);
            }
        }

        public int ExecuteBatch(IEnumerable<string> sqls)
        {
            SQLiteTransaction tr = null;
            int res = 0;
            try
            {
                Connect();
                tr = conn.BeginTransaction();
                using (var cmd = new SQLiteCommand(conn))
                {
                    cmd.CommandType = CommandType.Text;
                    foreach (string sql in sqls)
                    {
                        if (string.IsNullOrEmpty(sql) || string.IsNullOrWhiteSpace(sql))
                            continue;
                        cmd.CommandText = sql;
                        res += cmd.ExecuteNonQuery();
                    }
                }
                tr.Commit();
                return res;
            }
            catch (Exception ex)
            {
                tr?.Rollback();
                throw ex;
            }
        }

        public DataTable GetDataTable(string Sql)
        {
            DataTable dataTable = new DataTable();
            try
            {
                Connect();
                using (var cmd = new SQLiteCommand(conn)
                {
                    CommandType = CommandType.Text,
                    CommandText = Sql
                })
                using (SQLiteDataAdapter sQLiteDataAdapter = new SQLiteDataAdapter()
                {
                    SelectCommand = cmd
                })
                {
                    sQLiteDataAdapter.Fill(dataTable);
                }
            }
            catch (Exception ex)
            {
                string message = "ExecuteSql Error:" + ex.Message;
                throw new Exception(message);
            }
            return dataTable;
        }

        public string[,] GetDataTableToArray(string Sql)
        {
            try
            {
                DataTable dataTable = GetDataTable(Sql);
                string[,] array = new string[dataTable.Rows.Count, dataTable.Columns.Count];
                int num = 0;
                int num2 = 0;
                foreach (DataRow row in dataTable.Rows)
                {
                    num2 = 0;
                    object[] itemArray = row.ItemArray;
                    foreach (object obj in itemArray)
                    {
                        array[num, num2] = obj.ToString();
                        num2++;
                    }
                    num++;
                }
                return array;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public void Close()
        {
            try
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                if (conn != null)
                {
                    conn.Dispose();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public bool HasData(string sql)
        {
            DataTable dataTable = GetDataTable(sql);
            return dataTable.Rows.Count > 0;
        }

        public string[] GetColumnNameFromTable(string TableName)
        {
            try
            {
                string sql = $"PRAGMA table_info('{TableName}')";
                DataTable dataTable = GetDataTable(sql);
                string[] array = new string[dataTable.Rows.Count];
                int num = 0;
                foreach (DataRow row in dataTable.Rows)
                {
                    array[num] = row.ItemArray[1].ToString();
                    num++;
                }
                return array;
            }
            catch (Exception ex)
            {
                throw new Exception("获取表字段名称失败:" + ex.Message);
            }
        }

        public string[] GetAllTableInFile()
        {
            try
            {
                string sql = "select name from sqlite_master where type = 'table' order by name";
                DataTable dataTable = GetDataTable(sql);
                string[] array = new string[dataTable.Rows.Count];
                int num = 0;
                foreach (DataRow row in dataTable.Rows)
                {
                    array[num] = row.ItemArray[0].ToString();
                    num++;
                }

                return array;
            }
            catch (Exception ex)
            {
                throw new Exception("获取表名失败:" + ex.Message);
            }
        }

        private void Connect()
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.ConnectionString = ConnectString;
                conn.Open();
            }
        }

        private void DisConnect()
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }

        public void Dispose()
        {
            this.Close();
        }
    }
}