// 计划（伪代码）：
// 1. 在类中新增一个私有方法 `IsSupportedValue(object val)`，用于判断值是否为“常规类型”。
//    - 支持类型：null、string、bool、各类数值类型（包括 byte/short/int/long/float/double/decimal 等）、char、DateTime、DateTimeOffset、Guid、枚举。
//    - 判定可以使用 Type.IsPrimitive 并单独包含 decimal、string、DateTime 等非 primitive 类型，以及枚举判断。
// 2. 在 `AddCameraRecords` 方法中，遍历 `result` 输出时：
//    - 对每个输出取出 name 和 value。
//    - 使用 `IsSupportedValue` 判断 value 类型。
//    - 如果不支持，跳过该输出（可写日志记录被跳过的输出名和类型）。
//    - 如果支持，按原逻辑构造列名并加入 `outputs` 列表。
// 3. 其余逻辑保持不变：
//    - 若表不存在则创建，创建列时只为已收集的 `outputs` 创建列。
//    - 插入时为 null 写入 DBNull.Value，其他类型统一以 ToString() 写入（保留原行为，以兼容 TEXT 列）。
// 4. 将伪代码以注释形式放在文件顶部，随后是修改后的完整类代码，保证格式与现有代码风格一致，能在 .NET Framework 4.8 / C# 7.3 下编译。

using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using TeamAAS_VP;
using System.IO;
using TeamAAS_VP.Models;
using TeamAAS_VP.Enums;
using TeamAAS_VP.Core;
using Cognex.VisionPro.ToolBlock;

namespace TeamAAS_VP.Data
{
    public class DatabaseHelper
    {
        /// <summary>
        /// 初始化数据库
        /// </summary>
        /// <returns></returns>
        public static bool InitDatabase()
        {
            try
            {
                // 保留初始化占位（若需扩展统一建表，可在此实现）
                return true;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLogError("数据库初始化失败！", ex);
                return false;
            }
        }

        // ------------------- 新增：产品相机记录相关方法 -------------------
        private static string SanitizeIdentifier(string name)
        {
            if (string.IsNullOrWhiteSpace(name)) return "unnamed";
            var sb = new StringBuilder();
            foreach (var ch in name.Trim())
            {
                if (char.IsLetterOrDigit(ch) || ch == '_')
                    sb.Append(ch);
                else
                    sb.Append('_');
            }
            var s = sb.ToString();
            while (s.Contains("__")) s = s.Replace("__", "_");
            s = s.Trim('_');
            if (string.IsNullOrEmpty(s)) s = "unnamed";
            if (char.IsDigit(s[0])) s = "a" + s;
            if (s.Length > 64) s = s.Substring(0, 64);
            return s;
        }

        private static string SanitizeFileName(string name)
        {
            if (string.IsNullOrWhiteSpace(name)) return "unnamed";
            var invalid = Path.GetInvalidFileNameChars();
            var sb = new StringBuilder();
            foreach (var ch in name.Trim())
            {
                if (invalid.Contains(ch))
                    sb.Append('_');
                else
                    sb.Append(ch);
            }
            var s = sb.ToString();
            if (char.IsDigit(s[0])) s = "a" + s;
            s = s.Replace(" ", "_");
            if (s.Length > 100) s = s.Substring(0, 100);
            return s;
        }

        private static string GetProductDbPath(string productName)
        {
            var safeProduct = SanitizeFileName(productName);
            var folder = Path.Combine(FilePath.ProductsPath, productName);
            Directory.CreateDirectory(folder);
            var dbFileName = safeProduct + ".db";
            return Path.Combine(folder, dbFileName);
        }

        /// <summary>
        /// 增加一条相机拍照记录（若表或列不存在则创建）
        /// </summary>
        /// <param name="ProductName"></param>
        /// <param name="ProcedureName"></param>
        /// <param name="result"></param>
        public static void AddCameraRecords(string ProductName, string ProcedureName, CogToolBlockTerminalCollection result)
        {
            try
            {
                if (string.IsNullOrWhiteSpace(ProductName) || string.IsNullOrWhiteSpace(ProcedureName) || result == null)
                {
                    return;
                }

                var dbpath = GetProductDbPath(ProductName);
                if (!File.Exists(dbpath))
                {
                    SQLiteConnection.CreateFile(dbpath);
                }

                var tableName = SanitizeIdentifier(ProcedureName);
                var outputs = new List<(string Col, object Value)>();
                foreach (CogToolBlockTerminal output in result)
                {
                    var col = "_" + SanitizeIdentifier(output.Name ?? "unnamed");
                    outputs.Add((col, output.Value));
                }

                if (!IsHaveTable(tableName, dbpath))
                {
                    var colsSb = new StringBuilder();
                    colsSb.Append("_Time DATETIME");
                    foreach (var o in outputs)
                    {
                        colsSb.Append($", {o.Col} TEXT");
                    }
                    CreateTable(tableName, $"({colsSb})", dbpath);
                }
                else
                {
                    foreach (var o in outputs)
                    {
                        if (!IsHaveColumn(o.Col, tableName, dbpath))
                        {
                            AddColumn(o.Col, "TEXT", tableName, dbpath);
                        }
                    }
                    if (!IsHaveColumn("_Time", tableName, dbpath))
                    {
                        AddColumn("_Time", "DATETIME", tableName, dbpath);
                    }
                }

                using (var conn = new SQLiteConnection(string.Format("Data Source={0};Version=3;", dbpath)))
                {
                    conn.Open();
                    using (var cmd = new SQLiteCommand(conn))
                    {
                        var colNames = new List<string> { "_Time" };
                        var paramNames = new List<string> { "@p_time" };
                        cmd.Parameters.AddWithValue("@p_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                        for (int i = 0; i < outputs.Count; i++)
                        {
                            var pName = $"@p{i}";
                            colNames.Add($"\"{outputs[i].Col}\"");
                            var val = outputs[i].Value;
                            if (val == null) cmd.Parameters.AddWithValue(pName, DBNull.Value);
                            else cmd.Parameters.AddWithValue(pName, val.ToString());
                            paramNames.Add(pName);
                        }

                        var sql = $"INSERT INTO \"{tableName}\" ({string.Join(", ", colNames)}) VALUES ({string.Join(", ", paramNames)});";
                        cmd.CommandText = sql;
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLogError("增加一条相机拍照记录至数据库时出错！", ex);
            }
        }

        /// <summary>
        /// 获取最近20条相机拍照记录（按时间倒序）
        /// </summary>
        /// <param name="ProductName">产品名</param>
        /// <param name="ProcedureName">工序名</param>
        /// <returns>最近20条记录集合</returns>
        public static List<Dictionary<string, object>> GetLast20CameraRecords(string ProductName, string ProcedureName)
        {
            var records = new List<Dictionary<string, object>>();
            try
            {
                var dbpath = GetProductDbPath(ProductName);
                if (!File.Exists(dbpath))
                    return records;

                var tableName = SanitizeIdentifier(ProcedureName);
                if (!IsHaveTable(tableName, dbpath))
                    return records;

                using (var conn = new SQLiteConnection(string.Format("Data Source={0};Version=3;", dbpath)))
                {
                    conn.Open();
                    using (var cmd = new SQLiteCommand(conn))
                    {
                        // 按时间倒序取最新20条
                        cmd.CommandText = $"SELECT * FROM \"{tableName}\" ORDER BY _Time DESC LIMIT 20;";

                        using (var reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                var row = new Dictionary<string, object>();
                                for (int i = 0; i < reader.FieldCount; i++)
                                {
                                    var colName = reader.GetName(i);
                                    var value = reader.IsDBNull(i) ? null : reader.GetValue(i);
                                    row[colName] = value;
                                }
                                records.Add(row);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLogError("获取最近20条相机拍照记录时出错！", ex);
            }
            return records;
        }

        /// <summary>
        /// 根据产品名、流程名与可选时间段查询相机记录，返回 DataTable（表不存在时返回空表）
        /// </summary>
        public static DataTable GetCameraRecords(string ProductName, string ProcedureName, DateTime? from = null, DateTime? to = null)
        {
            var table = new DataTable();
            try
            {
                if (string.IsNullOrWhiteSpace(ProductName) || string.IsNullOrWhiteSpace(ProcedureName))
                    return table;

                var dbpath = GetProductDbPath(ProductName);
                var tableName = SanitizeIdentifier(ProcedureName);

                if (!File.Exists(dbpath) || !IsHaveTable(tableName, dbpath))
                    return table;

                var sb = new StringBuilder();
                sb.Append($"SELECT * FROM \"{tableName}\"");

                var parameters = new List<SQLiteParameter>();
                if (from.HasValue || to.HasValue)
                {
                    var where = new List<string>();
                    if (from.HasValue)
                    {
                        where.Add("_Time >= @from");
                        parameters.Add(new SQLiteParameter("@from", from.Value.ToString("yyyy-MM-dd HH:mm:ss")));
                    }
                    if (to.HasValue)
                    {
                        where.Add("_Time <= @to");
                        parameters.Add(new SQLiteParameter("@to", to.Value.ToString("yyyy-MM-dd HH:mm:ss")));
                    }
                    if (where.Any())
                    {
                        sb.Append(" WHERE " + string.Join(" AND ", where));
                    }
                }
                sb.Append(" ORDER BY _Time DESC");

                using (var conn = new SQLiteConnection(string.Format("Data Source={0};Version=3;", dbpath)))
                {
                    conn.Open();
                    using (var cmd = new SQLiteCommand(sb.ToString(), conn))
                    {
                        if (parameters.Any()) cmd.Parameters.AddRange(parameters.ToArray());
                        using (var da = new SQLiteDataAdapter(cmd))
                        {
                            da.Fill(table);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLogError("查询相机拍照记录时出错！", ex);
            }
            return table;
        }

        /// <summary>
        /// 根据产品名、流程名与可选时间段查询相机记录，返回 DataTable（表不存在时返回空表）
        /// </summary>
        /// <param name="ProductName"></param>
        /// <param name="ProcedureName"></param>
        /// <param name="from"></param>
        /// <param name="to"></param>
        /// <returns></returns>
        public static Task<DataTable> GetCameraRecordsAsync(string ProductName, string ProcedureName, DateTime? from = null, DateTime? to = null)
        {
            return Task.Run(() => GetCameraRecords(ProductName, ProcedureName, from, to));
        }
        // ------------------- 结束新增 -------------------

        /// <summary>
        /// 检测数据库库中是否存在表
        /// </summary>
        /// <param name="TableName">表名称</param>
        public static bool IsHaveTable(string TableName, string dbpath)
        {
            try
            {
                using (var conn = new SQLiteConnection(string.Format("Data Source={0};Version=3;", dbpath)))
                {
                    if (conn.State != ConnectionState.Open) conn.Open();
                    string cmdStr = $"select COUNT(*) from sqlite_master where type='table' and name ='{TableName}' ";
                    using (var cmd = new SQLiteCommand(cmdStr, conn))
                    {
                        return Convert.ToInt32(cmd.ExecuteScalar()) > 0;
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLogError($"检测数据库库中是否存在表（{TableName}）时出错", ex);
                return false;
            }
        }

        /// <summary>
        /// 为数据创建表
        /// </summary>
        /// <param name="TableName">表的名称</param>
        /// <param name="column">列的名称和类型字符串(Name varchar,Team varchar, Number varchar)</param>
        public static bool CreateTable(string TableName, string column, string dbpath)
        {
            string query = "CREATE TABLE " + TableName + " " + column;
            return WriteCommandToDatabase(query, dbpath);
        }

        /// <summary>
        /// 为表增加列
        /// </summary>
        /// <param name="ColumnName"></param>
        /// <param name="ColumnType"></param>
        /// <param name="TableName"></param>
        /// <param name="dbpath"></param>
        public static void AddColumn(string ColumnName, string ColumnType, string TableName, string dbpath)
        {
            using (SQLiteConnection connection = new SQLiteConnection(string.Format("Data Source={0};Version=3;", dbpath)))
            {
                connection.Open();
                string query = $"ALTER TABLE {TableName} ADD COLUMN {ColumnName} {ColumnType}";
                using (SQLiteCommand command = new SQLiteCommand(query, connection))
                {
                    command.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 将命令写入数据库
        /// </summary>
        /// <param name="cmdstr"></param>
        public static bool WriteCommandToDatabase(string cmdstr, string dbpath)
        {
            try
            {
                using (var conn = new SQLiteConnection(string.Format("Data Source={0};Version=3;", dbpath)))
                {
                    if (conn.State != ConnectionState.Open) conn.Open();
                    using (var cmd = new SQLiteCommand(cmdstr, conn))
                    {
                        cmd.ExecuteNonQuery();
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLogError($"写入命令至数据库时出错:【{cmdstr}】", ex);
                return false;
            }
        }

        /// <summary>
        /// 检索数据
        /// </summary>
        /// <param name="strCmd"></param>
        /// <param name="tab"></param>
        /// <returns></returns>
        public static bool Select(string strCmd, ref DataTable tab, string dbPath)
        {
            try
            {
                using (var conn = new SQLiteConnection(string.Format("Data Source={0};Version=3;", dbPath)))
                {
                    if (conn.State != ConnectionState.Open) conn.Open();
                    using (var cmd = new SQLiteCommand(strCmd, conn))
                    {
                        using (var da = new SQLiteDataAdapter(cmd))
                        {
                            da.Fill(tab);
                        }
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLogError("检索数据库时出错！", ex);
                return false;
            }
        }

        /// <summary>
        /// 检查是否存在包含特定字符串的记录
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="ColumnName"></param>
        /// <param name="searchString"></param>
        /// <returns></returns>
        public static bool RecordExists(string TableName, string ColumnName, string searchString, string dbPath)
        {
            try
            {
                using (var conn = new SQLiteConnection(string.Format("Data Source={0};Version=3;", dbPath)))
                {
                    if (conn.State != ConnectionState.Open) conn.Open();
                    using (var command = new SQLiteCommand($"SELECT COUNT(*) FROM {TableName} WHERE {ColumnName} LIKE @pattern", conn))
                    {
                        command.Parameters.AddWithValue("@pattern", $"%{searchString}%");
                        var obj = command.ExecuteScalar();
                        if (obj == null) return false;
                        long count = Convert.ToInt64(obj);
                        return count > 0;
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLogError($"检查是否存在包含特定字符串的记录时出错", ex);
                return false;
            }
        }

        /// <summary>
        /// 检测表中是否存在列
        /// </summary>
        /// <param name="columnName"></param>
        /// <param name="TableName"></param>
        /// <param name="dbpath"></param>
        /// <returns></returns>
        public static bool IsHaveColumn(string columnName, string TableName, string dbpath)
        {
            try
            {
                using (SQLiteConnection connection = new SQLiteConnection(string.Format("Data Source={0};Version=3;", dbpath)))
                {
                    connection.Open();
                    string query = $"PRAGMA table_info({TableName});";
                    using (SQLiteCommand command = new SQLiteCommand(query, connection))
                    {
                        using (SQLiteDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                string columnNameFromDB = reader["name"].ToString();
                                if (columnNameFromDB == columnName)
                                {
                                    return true;
                                }
                            }
                        }
                    }
                }
                return false;
            }
            catch (Exception)
            {
                return false;
            }
        }
    }
}