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

namespace DAL
{
    public class SQLite
    {
        private SQLiteConnection _con;

        public SQLite()
        {
        }

        public bool IsCon { private set; get; } = false;

        public bool Connect()
        {
            try
            {
                _con = new SQLiteConnection("data source='" + Environment.CurrentDirectory + "\\config\\database.db3';Version=3");
                _con.Open();
                IsCon = true;
                LogUtil.info("数据库连接成功");
                return true;
            }
            catch (Exception ex)
            {
                IsCon = false;
                LogUtil.error("数据库连接失败：" + ex.Message);
                return false;
            }
        }

        public void Close()
        {
            if (_con != null)
            {
                _con.Close();
                _con.Dispose();
                LogUtil.info("数据库关闭");
            }
        }

        //public bool QueryUserName(string username, string password)
        //{
        //    bool succeed = false;
        //    string sql = "SELECT * FROM Account WHERE Username='" + username + "' AND Password='" + password + "'";
        //    bool rtn = Select(sql, out string[][] data);
        //    if (rtn)
        //    {
        //        if (data.Length > 0)
        //            succeed = true;
        //    }
        //    return succeed;
        //}

        public bool QueryHistory(string barCode, string dateTimeFront, string dateTimeBack, out string[][] data)
        {
            string sql = "SELECT ID,ProName,ProType,BarCode,BoardWidth,BoardLength,AoiResult,UserName,CreateDate FROM OperateInfo WHERE 1=1";
            if (!string.IsNullOrEmpty(barCode))
                sql += " AND BarCode LIKE '%" + barCode + "%'";
            if (!string.IsNullOrEmpty(TSA_V.DeviceLibrary.DB.userName))
                sql += " AND UserName = '" + TSA_V.DeviceLibrary.DB.userName + "'";
            if (!string.IsNullOrEmpty(dateTimeFront))
                sql += " AND CreateDate >= '" + dateTimeFront + "'";
            if (!string.IsNullOrEmpty(dateTimeBack))
                sql += " AND CreateDate <= '" + dateTimeBack + "'";

            bool rtn = Select(sql, out data);
            return rtn;
        }

        public bool QueryPointInfo(string id, out string[][] data)
        {
            string sql = "SELECT ID,PartNum,PointName,CreateDate FROM PointInfo WHERE ID=" + id;
            bool rtn = Select(sql, out data);
            return rtn;
        }

        //public bool AddHistory(TSA_V.DeviceLibrary.OpInfo info, out int id)
        //{
        //    string sql;
        //    bool rtn;
        //    id = info.ID;

        //    //查询可写入的ID
        //    if (info.ID == 0)
        //    {
        //        sql = "SELECT MAX(ID) FROM PointInfo";
        //        rtn = Select(sql, out string[][] data);
        //        if (!rtn) return false;
        //        int.TryParse(data[0][0], out id);
        //        id++;
        //    }

        //    //添加操作信息记录
        //    string createDate = string.Format("{0:yyyy-MM-dd HH:mm:ss}", DateTime.Now);
        //    sql = "INSERT INTO OperateInfo(ID,ProName,ProType,BarCode,BoardWidth,BoardLength,AoiResult,UserName,CreateDate) " +
        //        "VALUES(" + id + ",'" + info.ProName + "','" + info.ProType + "','" + info.BarCode + "'," + info.BoardWidth + "," + 
        //        info.BoardLength + ",'" + info.AoiResult + "','" + TSA_V.DeviceLibrary.DB.userName + "','" + createDate + "')";
        //    rtn = Execute(sql);
        //    if (!rtn) return false;

        //    //添加操作点信息
        //    if (info.ID == 0)
        //    {
        //        SQLiteTransaction tr = _con.BeginTransaction();
        //        for (int i = 0; i < info.pointList.Count; i++)
        //        {
        //            sql = "INSERT INTO PointInfo(ID,PartNum,PointName,CreateDate) " +
        //                "VALUES(" + id + ",'" + info.pointList[i].PartNum + "','" + info.pointList[i].PointName + "','" + createDate + "')";
        //            rtn = Execute(sql);
        //            if (!rtn) break;
        //        }
        //        LogUtil.info("数据库Commit");
        //        if (rtn)
        //            tr.Commit();
        //        else
        //            tr.Rollback();
                         
        //        LogUtil.info("数据库 完成");
        //    }

        //    return rtn;
        //}

        public bool AddHistory(TSA_V.DeviceLibrary.OpInfo info, out int id)
        {
            string sql;
            bool rtn;
            id = info.ID;

            // 查询可写入的ID
            if (info.ID == 0)
            {
                sql = "SELECT MAX(ID) FROM PointInfo";
                rtn = Select(sql, out string[][] data);
                if (!rtn) return false;
                int.TryParse(data[0][0], out id);
                id++;
            }

            // 添加操作信息记录
            string createDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            sql = $"INSERT INTO OperateInfo(ID, ProName, ProType, BarCode, BoardWidth, BoardLength, AoiResult, UserName, CreateDate) " +
                  $"VALUES({id}, '{info.ProName}', '{info.ProType}', '{info.BarCode}', {info.BoardWidth}, {info.BoardLength}, " +
                  $"'{info.AoiResult}', '{TSA_V.DeviceLibrary.DB.userName}', '{createDate}')";
            rtn = Execute(sql);
            if (!rtn) return false;

            // 添加操作点信息
            if (info.ID == 0)
            {
                using (SQLiteTransaction tr = _con.BeginTransaction())
                {
                    try
                    {
                        foreach (var point in info.pointList)
                        {
                            sql = $"INSERT INTO PointInfo(ID, PartNum, PointName, CreateDate) " +
                                  $"VALUES({id}, '{point.PartNum}', '{point.PointName}', '{createDate}')";
                            rtn = Execute(sql);
                            if (!rtn) break;
                        }
                        LogUtil.info("数据库Commit");
                        tr.Commit();
                    }
                    catch
                    {
                        tr.Rollback();
                        return false;
                    }
                }
                LogUtil.info("数据库 完成");
            }

            return rtn;
        }




        private bool Select(string sql, out string[][] data)
        {
            data = null;
            if (!IsCon) return false;
            List<string[]> content = new List<string[]>();

            try
            {
                SQLiteCommand cmd = new SQLiteCommand(_con) { CommandText = sql };
                SQLiteDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    string[] s = new string[dr.FieldCount];
                    for (int i = 0; i < s.Length; i++)
                        s[i] = dr[i].ToString();
                    content.Add(s);
                }
                dr.Close();
                cmd.Dispose();
                data = content.ToArray();
                LogUtil.info("数据库Select，SQL=" + sql);
                return true;
            }
            catch (Exception ex)
            {
                LogUtil.error("数据库Select：" + ex.Message);
                return false;
            }

        }

        private bool Execute(string sql)
        {
            if (!IsCon) return false;

            try
            {
                SQLiteCommand cmd = new SQLiteCommand(_con) { CommandText = sql };
                int n = cmd.ExecuteNonQuery();
                cmd.Dispose();
                LogUtil.info("数据库Execute，SQL=" + sql + "，修改了" + n + "行");
                return true;
            }
            catch (Exception ex)
            {
                LogUtil.error("数据库Execute：" + ex.Message);
                return false;
            }
        }

    }
}