using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Drawing.Printing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using CommonHelper;
using Dapper;
using GasolineBlend.Entity;
using Google.Protobuf.WellKnownTypes;
using Microsoft.Office.Interop.Excel;
using MySql.Data.MySqlClient;

namespace GasolineBlend.DAL
{
    public class ChatHistoryDAL : BaseMySQLDAL
    {
        public List<ChatHistoryDataPage> GetChatHistoryList(int UserId, int AgentId, string Keyword, int PageNum, int PageSize)
        {
            using (IDbConnection connection = new MySqlConnection(connectionString))
            {
                var sql = $"SELECT * FROM  chathistorydata WHERE userid = '{UserId}' AND agentid = '{AgentId}' ";
                if (!string.IsNullOrEmpty(Keyword))
                {
                    sql += $" and content like '%{Keyword.Trim()}%' ";
                }
                int offset = (PageNum - 1) * PageSize;
                sql += $" ORDER BY id DESC  LIMIT {PageSize} OFFSET {offset}";

                return connection.Query<ChatHistoryDataPage>(sql).ToList();
            }
        }

        public int GetChatHistoryCount(int UserId, int AgentId, string Keyword)
        {
            using (IDbConnection connection = new MySqlConnection(connectionString))
            {
                var sql = $"SELECT COUNT(*) FROM  chathistorydata WHERE userid = '{UserId}' AND agentid = '{AgentId}' ";
                if (!string.IsNullOrEmpty(Keyword))
                {
                    sql += $" and content like '%{Keyword.Trim()}%' ";
                }
                sql += $"  ORDER BY id DESC ";
                int Count = connection.Query<int>(sql).FirstOrDefault();
                return Count;
            }
        }

        public List<ChatHistoryDataPage> GetChatHistory6List(int UserId, int AgentId)
        {
            using (IDbConnection connection = new MySqlConnection(connectionString))
            {
                var sql = $"SELECT content,role FROM  chathistorydata WHERE userid ='{UserId}' AND agentid = '{AgentId}' ORDER BY id DESC LIMIT 6";
                return connection.Query<ChatHistoryDataPage>(sql).ToList();
            }
        }

        public int AddChatHistoryData(ChatHistoryDataPage chatHistoryDataPage)
        {
            using (IDbConnection connection = new MySqlConnection(connectionString))
            {
                var sql = @"
            INSERT INTO chathistorydata (content, createtime, totaltokens, model, created, chatid, object, userid, agentid, role)
            VALUES (@Content, NOW(), @TotalTokens, @Model, @Created, @Chatid, @Object, @UserId, @AgentId, @Role)";

                var parameters = new
                {
                    Content = chatHistoryDataPage.Content,
                    TotalTokens = chatHistoryDataPage.TotalTokens,
                    Model = chatHistoryDataPage.Model,
                    Created = chatHistoryDataPage.Created,
                    Chatid = chatHistoryDataPage.Chatid,
                    Object = chatHistoryDataPage.Object,
                    UserId = chatHistoryDataPage.UserId,
                    AgentId = chatHistoryDataPage.AgentId,
                    Role = chatHistoryDataPage.Role,
                };
                connection.Open(); // 打开数据库连接

                // 执行插入操作
                int affectedRows = connection.Execute(sql, parameters);
                if (affectedRows > 0)
                {
                    // 获取最后插入的ID
                    var lastInsertIdSql = "SELECT LAST_INSERT_ID()";
                    var lastInsertId = connection.Query<int>(lastInsertIdSql).FirstOrDefault();
                    return lastInsertId;
                }
                return -1; // 如果没有行受影响，返回-1或其他错误代码
            }
        }

        public bool DelChatHistoryDataById(int Id)
        {
            using (IDbConnection connection = new MySqlConnection(connectionString))
            {
                var sql = $"delete from chathistorydata where id={Id}";
                connection.Open();
                return connection.Execute(sql) > 0;
            }
        }
        public string GetPromptData(int id)
        {
            using (IDbConnection connection = new MySqlConnection(connectionString))
            {
                return connection.QueryFirstOrDefault<string>($"SELECT prompt FROM agentdata WHERE id={id}");
            }
        }

        public bool UpdataUserNoById(int AgentId, int UserId)
        {
            using (IDbConnection connection = new MySqlConnection(connectionString))
            {
                string sqlUpdateQuestion = $"UPDATE agentdata SET questionno = COALESCE(questionno, 0) + 1 WHERE id = {AgentId}";
                string sqlCount = $"SELECT COUNT(*) FROM chathistorydata WHERE userid = {UserId} and agentid={AgentId}";
                string sqlUpdateUser = $"UPDATE agentdata SET userno = COALESCE(userno, 0) + 1 WHERE id = {AgentId}";
                int count = 0;
                connection.Open();
                using (IDataReader reader = connection.ExecuteReader(sqlCount))
                {
                    if (reader.Read())
                    {
                        count = reader.GetInt32(0);
                    }
                }
                if (count == 0)
                {
                    connection.Execute(sqlUpdateUser);
                }
                return connection.Execute(sqlUpdateQuestion) > 0;
            }
        }
    }
}