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

namespace BLL
{
    /// <summary>
    /// SQLite数据库
    /// </summary>
    public class SQLite
    {
        private string _path;
        private SQLiteConnection _con;

        /// <summary>
        /// SQLite数据库
        /// </summary>
        /// <param name="path"></param>
        public SQLite(string path)
        {
            _path = path;

            if (!System.IO.File.Exists(path))
                Create();
        }

        /// <summary>
        /// 是否连接
        /// </summary>
        public bool IsCon { private set; get; } = false;

        /// <summary>
        /// 错误信息
        /// </summary>
        public string ErrInfo { private set; get; }

        /// <summary>
        /// 创建数据库
        /// </summary>
        /// <returns></returns>
        public bool Create()
        {
            try
            {
                _con = new SQLiteConnection("data source=" + _path);
                SQLiteCommand cmd = new SQLiteCommand(_con);
                _con.Open();
                cmd.CommandText = "CREATE TABLE Retrospect (ID INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE,QRCode STRING,Height INT,Width INT,Count INT,ImagePath STRING,CreateDate DATETIME)";
                cmd.ExecuteNonQuery();

                cmd.Dispose();
                _con.Close();
                _con.Dispose();

                ErrInfo = "OK";
                return true;
            }
            catch (Exception ex)
            {
                ErrInfo = ex.Message;
                return false;
            }
        }

        /// <summary>
        /// 连接到数据库
        /// </summary>
        /// <returns></returns>
        public bool Connect()
        {
            try
            {
                _con = new SQLiteConnection("data source=" + _path);
                _con.Open();
                ErrInfo = "OK";
                IsCon = true;
                return true;
            }
            catch (Exception ex)
            {
                ErrInfo = ex.Message;
                IsCon = false;
                return false;
            }
        }

        /// <summary>
        /// 关闭连接数据库
        /// </summary>
        public void Close()
        {
            if (_con != null)
            {
                _con.Close();
                _con.Dispose();
            }
        }

        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="qrCode"></param>
        /// <param name="width"></param>
        /// <param name="height"></param>
        /// <param name="count"></param>
        /// <param name="imagePath"></param>
        /// <returns></returns>
        public int Insert(string qrCode, int width, int height, int count, string imagePath)
        {
            if (!IsCon) return -1;
            string sql = "INSERT INTO Retrospect([QRCode],[Height],[Width],[Count],[ImagePath],[CreateDate]) VALUES('" + qrCode + "'," + height + "," + width + "," + count + ",'" + imagePath + "','" + string.Format("{0:yyyy-MM-dd HH:mm:ss}", DateTime.Now) + "')";
            try
            {
                SQLiteCommand cmd = new SQLiteCommand(_con) { CommandText = sql };
                int n = cmd.ExecuteNonQuery();
                cmd.Dispose();
                ErrInfo = "OK";
                return n;
            }
            catch (Exception ex)
            {
                ErrInfo = ex.Message;
                return -1;
            }
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="qrcode"></param>
        /// <param name="dataLower"></param>
        /// <param name="dataUpper"></param>
        /// <param name="data"></param>
        /// <returns></returns>
        public bool Select(string qrcode, string dataLower, string dataUpper, out string[][] data)
        {
            data = null;
            if (!IsCon) return false;

            string sql = "SELECT [QRCode],[Height],[Width],[Count],[ImagePath],[CreateDate] FROM Retrospect WHERE";
            sql += " [QRCode] LIKE '%" + qrcode + "%'";
            sql += " AND [CreateDate]>='" + dataLower + "'";
            sql += " AND [CreateDate]<='" + dataUpper + "'";

            try
            {
                SQLiteCommand cmd = new SQLiteCommand(_con) { CommandText = sql };
                SQLiteDataReader dr = cmd.ExecuteReader();
                List<string[]> content = new List<string[]>();
                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();
                ErrInfo = "OK";
                return true;
            }
            catch (Exception ex)
            {
                ErrInfo = ex.Message;
                return false;
            }

        }

    }


}