using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SQLite;
using System.Data;
using System.Text;
using System.Threading;

namespace Kingo.RuleCheck.CheckHelper
{
    public class SQLiteDBOperate
    {
        private static object obj = new object();
        private static SQLiteDBOperate _instance = null;
        public static SQLiteDBOperate Instance
        {
            get
            {
                lock (obj)
                {
                    if (_instance == null)
                        _instance = new SQLiteDBOperate();
                }
                return _instance;
            }
        }

        public DataTable ExceDataTable(string connectionString, string sql, string password = "")
        {
            try
            {
                using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + connectionString))
                {
                    if (!string.IsNullOrWhiteSpace(password))
                    {
                        connection.SetPassword(password);
                    }
                    using (SQLiteCommand command = new SQLiteCommand(sql, connection))
                    {
                        using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(command))
                        {
                            DataTable data = new DataTable();
                            try
                            {
                                connection.SetPassword("009FA8952F59C8853AF0CC8DA4394D00FEDCD51361C7F1C9C2629EBEB8FDE5B2A1");
                                adapter.Fill(data);
                            }
                            catch
                            {
                                connection.SetPassword("");
                                adapter.Fill(data);
                            }
                            return data;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 只执行SQL
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="dbPath"></param>
        /// <returns></returns>
        public bool OnlySQL(string sql, string dbPath)
        {
            bool b = true;
            string connString = "Data Source=" + dbPath;
            using (SQLiteConnection conn = new SQLiteConnection(connString))
            {
                conn.Open();
                using (SQLiteCommand cmd = new SQLiteCommand(conn))
                {
                    try
                    {
                        cmd.CommandText = sql;
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
                conn.Close();
            }
            return b;
        }

        /// <summary>
        /// 修改密码
        /// </summary>
        /// <param name="newPassword">新密码</param>
        public void ChangePassword(string dbpath, string oldPassword, string newPassword)
        {
            using (SQLiteConnection _con = new SQLiteConnection())
            {
                _con.ConnectionString = "Data Source=" + dbpath;
                if (oldPassword.Length > 0)
                {
                    _con.ConnectionString += ";Password=" + oldPassword;
                }
                try
                {
                    _con.Open();
                }
                catch (Exception ex)
                {
                    throw new Exception("无法连接到数据库!" + ex.Message);
                }
                _con.ChangePassword(newPassword);
            }
        }

        /// <summary>
        /// 执行压缩SQLite数据库
        /// </summary>
        /// <returns>压缩数据库db路径</returns>
        public static void ExecuteZip(string dbPath)
        {
            using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + dbPath + ";"))
            {
                using (SQLiteCommand cmd = new SQLiteCommand("VACUUM", connection))
                {
                    try
                    {
                        connection.Open();
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        throw new Exception("输出成果压缩db异常:" + ex.Message);
                    }
                    finally
                    {
                        if (connection != null)
                        {
                            connection.Close();
                        }
                    }
                }
            }
        }
    }
}