using ClosedXML.Excel;
using IronPython.Hosting;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Http.HttpResults;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Query.Internal;
using Microsoft.EntityFrameworkCore.Storage.ValueConversion.Internal;
using Microsoft.IdentityModel.Tokens;
using MySqlConnector;
using NLog;
using System.Data;
using System.Text;
using ViewModels;
using WebAPIServer.Common;
using WebAPIServer.Models;

namespace WebAPIServer.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class TestLogsController : ControllerBase
    {
        /// <summary>
        /// 每个步骤返回多条测试记录
        /// </summary>
        [HttpPost()]
        [Authorize()]
        public ReturnInfo GetLogListBySN([FromBody] TestLogsQueryModel T)
        {
            ReturnInfo returnInfo = new();
            try
            {
                using (var q = new UtsManageContext())
                {
                    using IDbConnection conn = q.Database.GetDbConnection();
                    conn.Open();

                    List<ReturnTestLogs> tl = [];
                    foreach (var item in T.DbList)
                    {
                        string tableName = $"{T.DBName}.tbl_{item.ProId}_{item.Step}_testlog";
                        // 修改点1：移除了MAX聚合函数，查询所有匹配记录
                        string sql = $"SELECT * FROM {tableName} WHERE DUT_SN = '{T.SnCode}'";

                        using MySqlCommand command = new(sql, (MySqlConnection)conn);
                        using MySqlDataAdapter adapter = new(command);
                        DataSet ds = new();
                        adapter.Fill(ds);

                        if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
                        {
                            // 没有数据时仍创建空对象（保持原有结构）
                            tl.Add(new ReturnTestLogs
                            {
                                Step = item.Step,
                                ID = "",
                                StartTime = "",
                                UsedTime = "",
                                FailSteps = "",
                                FailMsg = "",
                                TestResult = "",
                                RObj = new List<Dictionary<string, string>>()
                            });
                        }
                        else
                        {
                            // 修改点2：处理所有行数据
                            foreach (DataRow row in ds.Tables[0].Rows)
                            {
                                var log = new ReturnTestLogs
                                {
                                    Step = item.Step,
                                    ID = row["ID"]?.ToString() ?? "",
                                    StartTime = row["StartTime"]?.ToString() ?? "",
                                    UsedTime = row["UsedTime"]?.ToString() ?? "",
                                    FailSteps = row["FailSteps"]?.ToString() ?? "",
                                    FailMsg = row["FailMsg"]?.ToString() ?? "",
                                    TestResult = row["TestResult"]?.ToString() ?? "",
                                    RObj = []
                                };

                                // 修改点3：每行数据单独创建字典
                                var rowData = new Dictionary<string, string>();
                                foreach (DataColumn col in ds.Tables[0].Columns)
                                {
                                    rowData[col.ColumnName] = row[col]?.ToString() ?? "";
                                }
                                log.RObj.Add(rowData);

                                tl.Add(log);
                            }
                        }
                    }

                    returnInfo.isok = true;
                    returnInfo.response = tl;
                }
            }
            catch (Exception ex)
            {
                returnInfo.isok = false;
                returnInfo.message = ex.Message;
            }
            return returnInfo;
        }

        /// <summary>
        /// 测试总表Excel
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        [HttpPost()]
        [Authorize()]
        public IActionResult GetTestSummaryTableExcel([FromBody] TestLogExportRequest request)
        {
            try
            {
                string? dbName = request.dbName;
                string? tbName = request.tbName;
                string? startDate = request.StartTime;
                string? endDate = request.EndTime;
                string? testResult = request.TestResult;
                List<string>? failSteps = request.FailSteps;
                string? columnList = request.ColumnList;
                string distinctType = request.DistinctType ?? "None"; // 获取去重类型

                if (string.IsNullOrEmpty(startDate) || string.IsNullOrEmpty(endDate))
                {
                    return BadRequest("开始时间或结束时间不能为空");
                }

                using (var q = new UtsManageContext())
                {
                    using IDbConnection conn = q.Database.GetDbConnection();

                    if (conn.State != ConnectionState.Open)
                    {
                        conn.Open();
                    }

                    // 构建查询
                    StringBuilder dataQuery = new StringBuilder();

                    if (distinctType == "None")
                    {
                        // 不去重的情况
                        dataQuery.Append("SELECT ID, ServiceID AS SID, StartTime, DUT_SN, TestResult, Failsteps");

                        if (!string.IsNullOrEmpty(columnList))
                        {
                            dataQuery.Append(columnList);
                        }

                        dataQuery.Append($" FROM {dbName}.{tbName} ");
                        dataQuery.Append($"WHERE StartTime BETWEEN '{startDate} 00:00:00' AND '{endDate} 23:59:59' ");
                    }
                    else
                    {
                        // 去重的情况
                        string orderDirection = distinctType == "Latest" ? "DESC" : "ASC";

                        dataQuery.Append("SELECT ID, ServiceID AS SID, StartTime, DUT_SN, TestResult, Failsteps");

                        if (!string.IsNullOrEmpty(columnList))
                        {
                            dataQuery.Append(columnList);
                        }

                        dataQuery.Append(" FROM (");
                        dataQuery.Append("SELECT *, ROW_NUMBER() OVER (PARTITION BY DUT_SN ORDER BY StartTime ");
                        dataQuery.Append(orderDirection);
                        dataQuery.Append($") AS rn FROM {dbName}.{tbName} ");
                        dataQuery.Append($"WHERE StartTime BETWEEN '{startDate} 00:00:00' AND '{endDate} 23:59:59' ");
                    }

                    if (!string.IsNullOrEmpty(testResult))
                    {
                        dataQuery.Append($"AND TestResult = '{testResult}' ");
                    }

                    if (failSteps != null && failSteps.Count > 0)
                    {
                        string failStepsCondition = string.Join(",", failSteps.Select(f => $"'{MySqlHelper.EscapeString(f)}'"));
                        dataQuery.Append($"AND Failsteps IN ({failStepsCondition}) ");
                    }

                    if (distinctType != "None")
                    {
                        dataQuery.Append(") t WHERE rn = 1 ");
                    }

                    dataQuery.Append("ORDER BY StartTime DESC");

                    // 执行数据查询
                    IDbCommand dataCmd = conn.CreateCommand();
                    dataCmd.CommandText = dataQuery.ToString();

                    MySqlCommand command = dataCmd as MySqlCommand;
                    MySqlDataAdapter adapter = new(command);
                    DataSet ds = new();
                    adapter.Fill(ds);

                    DataTable dt = ds.Tables[0];

                    // 使用ClosedXML创建Excel
                    using (var workbook = new XLWorkbook())
                    {
                        var worksheet = workbook.Worksheets.Add(dt, "Sheet");

                        // 设置标题行样式
                        var headerRow = worksheet.Row(1);
                        headerRow.Style.Font.Bold = true;
                        // 自动调整列宽
                        worksheet.Columns().AdjustToContents();
                        using (var stream = new MemoryStream())
                        {
                            workbook.SaveAs(stream);
                            var content = stream.ToArray();
                            return File(content, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", $"{tbName}_Export.xlsx");
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                return StatusCode(500, $"导出失败: {ex.Message}");
            }
        }


        public ReturnInfoPage GetTestSummaryTable([FromBody] TestLog T)
        {
            ReturnInfoPage returnInfo = new();
            string? dbName = T.dbName;
            string? tbName = T.tbName;
            string? StartTime = T.StartTime;
            string? EndTime = T.EndTime;
            string? TestResult = T.TestResult;
            List<string>? FailSteps = T.FailSteps;
            string? ColumnList = T.ColumnList;
            int page = T.Page;
            int pageSize = T.PageSize;
            string distinctType = T.DistinctType ?? "None"; // 获取去重类型，默认为不去重

            if (string.IsNullOrEmpty(StartTime) || string.IsNullOrEmpty(EndTime))
            {
                returnInfo.isok = false;
                returnInfo.message = "开始时间或结束时间不能为空";
                return returnInfo;
            }

            try
            {
                using (var q = new UtsManageContext())
                {
                    using IDbConnection conn = q.Database.GetDbConnection();

                    if (conn.State != ConnectionState.Open)
                    {
                        conn.Open();
                    }

                    // 1. 构建基础查询（总数查询）
                    StringBuilder baseQuery = new StringBuilder();

                    if (distinctType == "None")
                    {
                        // 不去重的情况
                        baseQuery.Append($"SELECT COUNT(*) AS Total FROM {dbName}.{tbName} ");
                        baseQuery.Append($"WHERE StartTime BETWEEN '{StartTime} 00:00:00' AND '{EndTime} 23:59:59' ");
                    }
                    else
                    {
                        // 去重的情况，需要计算去重后的总数
                        baseQuery.Append($"SELECT COUNT(DISTINCT DUT_SN) AS Total FROM {dbName}.{tbName} ");
                        baseQuery.Append($"WHERE StartTime BETWEEN '{StartTime} 00:00:00' AND '{EndTime} 23:59:59' ");
                    }

                    if (!string.IsNullOrEmpty(TestResult))
                    {
                        baseQuery.Append($"AND TestResult = '{TestResult}' ");
                    }

                    if (FailSteps != null && FailSteps.Count > 0)
                    {
                        // 使用IN语句查询多个失败步骤
                        string failStepsCondition = string.Join(",", FailSteps.Select(f => $"'{MySqlHelper.EscapeString(f)}'"));
                        baseQuery.Append($"AND Failsteps IN ({failStepsCondition}) ");
                    }

                    // 执行总数查询
                    IDbCommand countCmd = conn.CreateCommand();
                    countCmd.CommandText = baseQuery.ToString();
                    long totalCount = Convert.ToInt64(countCmd.ExecuteScalar());

                    // 2. 计算总通过数
                    long totalPassCount = 0;
                    StringBuilder totalPassQuery = new StringBuilder();

                    if (distinctType == "None")
                    {
                        // 不去重的情况：统计所有通过的记录
                        totalPassQuery.Append($"SELECT COUNT(*) FROM {dbName}.{tbName} ");
                        totalPassQuery.Append($"WHERE StartTime BETWEEN '{StartTime} 00:00:00' AND '{EndTime} 23:59:59' ");
                        totalPassQuery.Append($"AND TestResult = '1'");

                        if (!string.IsNullOrEmpty(TestResult))
                        {
                            totalPassQuery.Append($"AND TestResult = '{TestResult}' ");
                        }

                        if (FailSteps != null && FailSteps.Count > 0)
                        {
                            // 使用IN语句查询多个失败步骤
                            string failStepsCondition = string.Join(",", FailSteps.Select(f => $"'{MySqlHelper.EscapeString(f)}'"));
                            totalPassQuery.Append($"AND Failsteps IN ({failStepsCondition}) ");
                        }
                    }
                    else
                    {
                        // 去重的情况：统计去重后记录中通过的数量
                        string orderDirection = distinctType == "Latest" ? "DESC" : "ASC";

                        totalPassQuery.Append($"SELECT COUNT(*) FROM (");
                        totalPassQuery.Append($"SELECT *, ROW_NUMBER() OVER (PARTITION BY DUT_SN ORDER BY StartTime {orderDirection}) AS rn ");
                        totalPassQuery.Append($"FROM {dbName}.{tbName} ");
                        totalPassQuery.Append($"WHERE StartTime BETWEEN '{StartTime} 00:00:00' AND '{EndTime} 23:59:59' ");

                        if (!string.IsNullOrEmpty(TestResult))
                        {
                            totalPassQuery.Append($"AND TestResult = '{TestResult}' ");
                        }

                        if (FailSteps != null && FailSteps.Count > 0)
                        {
                            // 使用IN语句查询多个失败步骤
                            string failStepsCondition = string.Join(",", FailSteps.Select(f => $"'{MySqlHelper.EscapeString(f)}'"));
                            totalPassQuery.Append($"AND Failsteps IN ({failStepsCondition}) ");
                        }

                        totalPassQuery.Append($") as t WHERE rn = 1 AND TestResult = '1'");
                    }

                    IDbCommand totalPassCmd = conn.CreateCommand();
                    totalPassCmd.CommandText = totalPassQuery.ToString();
                    totalPassCount = Convert.ToInt64(totalPassCmd.ExecuteScalar());

                    // 3. 计算通过率
                    double passRate = totalCount > 0 ? Math.Round((double)totalPassCount / totalCount * 100, 2) : 0;
                    // 4. 构建数据查询
                    StringBuilder dataQuery = new StringBuilder();

                    if (distinctType == "None")
                    {
                        // 不去重的情况
                        dataQuery.Append("SELECT ID, ServiceID AS SID, StartTime, DUT_SN, TestResult, Failsteps");

                        if (!string.IsNullOrEmpty(ColumnList))
                        {
                            dataQuery.Append(ColumnList);
                        }

                        dataQuery.Append($" FROM {dbName}.{tbName} ");
                        dataQuery.Append($"WHERE StartTime BETWEEN '{StartTime} 00:00:00' AND '{EndTime} 23:59:59' ");
                    }
                    else
                    {
                        // 去重的情况
                        string orderDirection = distinctType == "Latest" ? "DESC" : "ASC";

                        dataQuery.Append("SELECT ID, ServiceID AS SID, StartTime, DUT_SN, TestResult, Failsteps");

                        if (!string.IsNullOrEmpty(ColumnList))
                        {
                            dataQuery.Append(ColumnList);
                        }

                        dataQuery.Append(" FROM (");
                        dataQuery.Append("SELECT *, ROW_NUMBER() OVER (PARTITION BY DUT_SN ORDER BY StartTime ");
                        dataQuery.Append(orderDirection);
                        dataQuery.Append($") AS rn FROM {dbName}.{tbName} ");
                        dataQuery.Append($"WHERE StartTime BETWEEN '{StartTime} 00:00:00' AND '{EndTime} 23:59:59' ");

                        if (!string.IsNullOrEmpty(TestResult))
                        {
                            dataQuery.Append($"AND TestResult = '{TestResult}' ");
                        }

                        if (FailSteps != null && FailSteps.Count > 0)
                        {
                            // 使用IN语句查询多个失败步骤
                            string failStepsCondition = string.Join(",", FailSteps.Select(f => $"'{MySqlHelper.EscapeString(f)}'"));
                            dataQuery.Append($"AND Failsteps IN ({failStepsCondition}) ");
                        }

                        dataQuery.Append(") t WHERE rn = 1 ");
                    }

                    if (distinctType == "None")
                    {
                        // 不去重的情况下添加条件
                        if (!string.IsNullOrEmpty(TestResult))
                        {
                            dataQuery.Append($"AND TestResult = '{TestResult}' ");
                        }

                        if (FailSteps != null && FailSteps.Count > 0)
                        {
                            // 使用IN语句查询多个失败步骤
                            string failStepsCondition = string.Join(",", FailSteps.Select(f => $"'{MySqlHelper.EscapeString(f)}'"));
                            dataQuery.Append($"AND Failsteps IN ({failStepsCondition}) ");
                        }
                    }

                    // 添加排序和分页
                    dataQuery.Append($"ORDER BY StartTime DESC LIMIT {pageSize} OFFSET {(page - 1) * pageSize}");

                    // 执行数据查询
                    IDbCommand dataCmd = conn.CreateCommand();
                    dataCmd.CommandText = dataQuery.ToString();

                    MySqlCommand command = dataCmd as MySqlCommand;
                    MySqlDataAdapter adapter = new(command);
                    DataSet ds = new();
                    adapter.Fill(ds);

                    DataTable dt = ds.Tables[0];

                    // 处理结果
                    List<Dictionary<string, string>> list = new List<Dictionary<string, string>>();
                    foreach (DataRow item in dt.Rows)
                    {
                        Dictionary<string, string> dic = new Dictionary<string, string>();
                        foreach (DataColumn col in dt.Columns)
                        {
                            dic.Add(col.ColumnName, item[col]?.ToString() ?? "");
                        }
                        list.Add(dic);
                    }

                    returnInfo.isok = true;
                    returnInfo.total = totalCount;
                    returnInfo.response = list;
                    returnInfo.passCount = totalPassCount; // 修改字段名
                    returnInfo.passRate = passRate;
                }
            }
            catch (Exception ex)
            {
                returnInfo.isok = false;
                returnInfo.message = ex.Message;
            }
            return returnInfo;
        }
    }


    public class TestLogExportRequest
    {
        public string? dbName { get; set; }
        public string? tbName { get; set; }
        public string StartTime { get; set; }
        public string EndTime { get; set; }
        public string? TestResult { get; set; }
        public List<string>? FailSteps { get; set; }
        public string? ColumnList { get; set; }
        public string DistinctType { get; set; } = "None"; // 新增字段：None-不去重, Latest-保留最新, Earliest-保留最早
    }

    public class ReturnTestLogs
    {

        public string Step { get; set; }
        public string ID { get; set; }
        public string StartTime { get; set; }
        public string UsedTime { get; set; }
        public string FailSteps { get; set; }
        public string FailMsg { get; set; }
        public string TestResult { get; set; }
        public List<Dictionary<string, string>> RObj { get; set; }
    }

    public class TestLogsQueryModel
    {
        public string DBName { get; set; }
        public string SnCode { get; set; }

        public List<DBL> DbList { get; set; }
    }
    public class DBL
    {
        public string ProId { get; set; }
        public string Step { get; set; }
    }


    public class TestLog : TestLogExportRequest
    {
        public int Page { get; set; }
        public int PageSize { get; set; }
    }

    // 扩展ReturnInfoPage类以包含一次通过数和通过率
    public class ReturnInfoPage
    {
        public bool isok { get; set; }
        public string message { get; set; }
        public long total { get; set; }
        public object response { get; set; }
        public long passCount { get; set; }
        public double passRate { get; set; }
    }
}