using System;
using System.Data.SQLite;
using System.IO;

namespace HMI.Simple.Util
{
    public class SerialDataHelper
    {
        private static string FileName = "SerialData";
        /// <summary>
        /// 防止多线程创建文件（但是不知道teststand会不会多进程调用）
        /// </summary>
        private static object fileLock = new object();

        private static string createTableSql = @"CREATE TABLE IF NOT EXISTS serialData (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    sn TEXT NOT NULL,
                    station TEXT NOT NULL,
                    step TEXT NOT NULL,
                    limitDown TEXT,
                    limitUp TEXT,
                    unit TEXT,
                    offset TEXT,
                    value TEXT,
                    status TEXT,
                    finalStatus TEXT,
                    consumeTime BIGINT, 
                    totalConsumeTime BIGINT, 
                    time DATETIME NOT NULL);";

        private static string GetConnectionStr(string dbFile)
        {
            return $"DataSource={dbFile};Version=3;Pooling=False;Max Pool Size=100";
        }

        private static void CreateDbFile(string dbFile)
        {
            lock (fileLock)
            {
                if (!File.Exists(dbFile))
                {
                    string dir = Path.GetDirectoryName(dbFile);
                    if (!Directory.Exists(dir))
                    {
                        Directory.CreateDirectory(dir);
                    }
                    SQLiteConnection.CreateFile(dbFile);

                    using (SQLiteConnection conn = new SQLiteConnection())
                    using (SQLiteCommand cmd = new SQLiteCommand(conn))
                    {
                        conn.ConnectionString = GetConnectionStr(dbFile);
                        conn.Open();
                        cmd.CommandText = createTableSql;
                        cmd.ExecuteNonQuery();
                    }
                }
            }
        }

        private static string GetDbFile()
        {
            string value = RegUtil.GetLocaltion();
            string path = Path.Combine(value, "Config", "DataBoard", $"{FileName}_{DateTime.Now:yyyy-MM}.db");
            if (!File.Exists(path))
            {
                CreateDbFile(path);
            }
            return path;
        }

        public static bool InsertSerialData(string sn, string station, string step, string status, out string errMsg)
        {
            errMsg = string.Empty;
            try
            {
                string dbFile = GetDbFile();
                using (SQLiteConnection conn = new SQLiteConnection())
                using (SQLiteCommand cmd = new SQLiteCommand(conn))
                {
                    conn.ConnectionString = GetConnectionStr(dbFile);
                    conn.Open();
                    cmd.CommandText =
                        $@"INSERT INTO serialData (sn, station, step, status, time) VALUES ('{sn}', '{station}', '{step}', '{status}', '{DateTime.Now:yyyy-MM-dd HH:mm:ss}');";
                    if (cmd.ExecuteNonQuery() > 0)
                    {
                        return true;
                    }
                    errMsg = "插入失败";
                    return false;
                }
            }
            catch (Exception ex)
            {
                errMsg = ex.Message;
                return false;
            }
        }

        public static bool UpdateStatusBySn(string dbFile, string sn, string status, out string errMsg)
        {
            errMsg = string.Empty;
            try
            {
                if (!File.Exists(dbFile))
                {
                    errMsg = "数据库文件不存在";
                    return false;
                }
                using (SQLiteConnection conn = new SQLiteConnection())
                using (SQLiteCommand cmd = new SQLiteCommand(conn))
                {
                    conn.ConnectionString = GetConnectionStr(dbFile);
                    conn.Open();
                    cmd.CommandText =
                        $@"UPDATE serialData SET status='{status}', time='{DateTime.Now:yyyy-MM-dd HH:mm:ss}' WHERE sn='{sn}';";
                    if (cmd.ExecuteNonQuery() > 0)
                    {
                        return true;
                    }
                    errMsg = "更新失败";
                    return false;
                }
            }
            catch (Exception ex)
            {
                errMsg = ex.Message;
                return false;
            }
        }
    }
}