using LB_SmartVisionCommon;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace LB_SmartVision.SQL
{
    public class DatabaseRecordProductDataHelper
    {
        private static string connectionString = "Server=localhost;Database=product_db;Uid=root;Pwd=root;";
        private static object databaseRecordProductDataHelperObject = new object();
        public static MySqlConnection GetConnection()
        {
            return new MySqlConnection(connectionString);
        }
        // 创建数据库和表
        public static void InitializeDatabase()
        {
            lock (databaseRecordProductDataHelperObject)
            {
                try
                {
                    using (var connection = GetConnection())
                    {
                        connection.Open();
                        // 创建数据库
                        string createDbSql = @"CREATE DATABASE IF NOT EXISTS product_db;";
                        using (var cmd = new MySqlCommand(createDbSql, connection))
                        {
                            cmd.ExecuteNonQuery();
                        }
                        // 使用数据库
                        string useDbSql = @"USE product_db;";
                        using (var cmd = new MySqlCommand(useDbSql, connection))
                        {
                            cmd.ExecuteNonQuery();
                        }
                        // 创建表（增加索引以提高查询性能）
                        string createTableSql = @"
                                                 CREATE TABLE IF NOT EXISTS RecordProductData (
                                                 Id INT AUTO_INCREMENT PRIMARY KEY,
                                                 ProductName VARCHAR(255) NOT NULL,
                                                 ProductSN VARCHAR(255) NOT NULL,
                                                 InspectionOperator VARCHAR(255),
                                                 NGType VARCHAR(255),
                                                 NGSize VARCHAR(255),
                                                 DetectionTime VARCHAR(255)),
                                                 CameraInspection VARCHAR(255),
                                                 CreatedDate DATETIME DEFAULT CURRENT_TIMESTAMP,
                                                 INDEX idx_created_date (CreatedDate),
                                                 INDEX idx_product_number (ProductSN),
                                                 INDEX idx_operator (InspectionOperator)
                                                 );";
                        using (var cmd = new MySqlCommand(createTableSql, connection))
                        {
                            cmd.ExecuteNonQuery();
                            AsyncLogHelper.Info("数据库和表创建成功！");
                        }
                    }
                }
                catch (Exception ex)
                {
                    AsyncLogHelper.Error($"数据库初始化失败: {ex.Message}");
                    throw;
                }
            }
        }
    }
}