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

namespace HMI.Core.Util
{
    public class SqliteHelper
    {
        private string DataBaseFile = string.Empty;

        private string ConnectString = string.Empty;

        private SQLiteConnection conn = null;

        private SQLiteCommand cmd = null;

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

        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 ExecuteSql(string Sql)
        {
            try
            {
                Connect();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = Sql;
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                string message = "ExecuteSql Error:" + ex.Message;
                throw new Exception(message);
            }
            finally
            {
                DisConnect();
            }
        }

        public void ExecuteBatchSql(IEnumerable<string> sqls)
        {
            SQLiteTransaction tr = null;
            try
            {
                Connect();
                tr = conn.BeginTransaction();
                cmd.CommandType = CommandType.Text;
                foreach (string sql in sqls)
                {
                    if (string.IsNullOrEmpty(sql) || string.IsNullOrWhiteSpace(sql)) continue;
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                }
                tr.Commit();
            }
            catch (Exception ex)
            {
                tr?.Rollback();
                throw ex;
            }
            finally
            {
                DisConnect();
            }
        }

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

            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 (cmd != null)
                {
                    cmd.Dispose();
                }

                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;
                cmd.Connection = conn;
                conn.Open();
            }
        }

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