using System;
using System.Collections.Generic;
using System.Data;
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 AgentDataDAL : BaseMySQLDAL
    {
        public List<AgentDataPage> GetAgentDataList(string Keyword, int PageNum, int PageSize, string AgentType, string CreatorId, string FavoriteFlag)
        {
            using (IDbConnection connection = new MySqlConnection(connectionString))
            {
                var sql = $"SELECT a.id,a.name,a.prompt,a.permission,a.creationtime,a.modificationtime,a.avatarlink,a.creatorid,a.agenttype,a.description,a.userno,a.questionno,a.modelapi,a.modelkey,a.systemrouting,CASE WHEN s.agentid IS NOT NULL THEN 1 ELSE 0 END AS favoriteflag FROM agentdata a LEFT JOIN staragent s ON a.id = s.agentid AND s.userid = '{CreatorId}' WHERE 1=1";
                if (!string.IsNullOrEmpty(Keyword)) {
                    sql += $" and a.name like '%{Keyword.Trim()}%' ";
                }
                if (!string.IsNullOrEmpty(FavoriteFlag)&& FavoriteFlag=="1")
                {
                    sql += $" and s.userid = '{CreatorId.Trim()}' and (CASE WHEN s.agentid IS NOT NULL THEN 1 ELSE 0 END) = 1 ";
                }
                else {
                    if (!string.IsNullOrEmpty(AgentType))
                    {
                        sql += $" and a.agenttype = '{AgentType.Trim()}' ";
                    }
                    sql += $" and a.permission = 1 "; 
                }
                int offset = (PageNum - 1) * PageSize;
                sql += $" ORDER BY a.userno DESC  LIMIT {PageSize} OFFSET {offset}";

                return connection.Query<AgentDataPage>(sql).ToList();
            }
        }
      
        public int GetAgentDataCount(string Keyword, string AgentType, string CreatorId, string FavoriteFlag)
        {
            using (IDbConnection connection = new MySqlConnection(connectionString))
            {
                var sql = $"SELECT COUNT(*) FROM agentdata a LEFT JOIN staragent s ON a.id = s.agentid AND s.userid = '{CreatorId}' WHERE 1=1 ";
                if (!string.IsNullOrEmpty(Keyword))
                {
                    sql += $" and a.name like '%{Keyword.Trim()}%' ";
                }
                if (!string.IsNullOrEmpty(FavoriteFlag) && FavoriteFlag == "1")
                {
                    sql += $" and s.userid = '{CreatorId.Trim()}' and (CASE WHEN s.agentid IS NOT NULL THEN 1 ELSE 0 END) = 1 ";
                }
                else
                {
                    if (!string.IsNullOrEmpty(AgentType))
                    {
                        sql += $" and a.agenttype = '{AgentType.Trim()}' ";
                    }
                    sql += $" and a.permission = 1 ";
                }
                sql += $"  ORDER BY modificationtime DESC ";
                int Count = connection.Query<int>(sql).FirstOrDefault();
                return Count;
            }
        }

        public List<AgentDataPage> GetMyAgentDataList(string Keyword, int PageNum, int PageSize, string AgentType, string CreatorId)
        {
            using (IDbConnection connection = new MySqlConnection(connectionString))
            {
                var sql = $"SELECT a.id,a.name,a.prompt,a.permission,a.creationtime,a.modificationtime,a.avatarlink,a.creatorid,a.agenttype,a.description,a.userno,a.questionno,a.modelapi,a.modelkey,a.systemrouting,CASE WHEN s.agentid IS NOT NULL THEN 1 ELSE 0 END AS favoriteflag FROM agentdata a LEFT JOIN staragent s ON a.id = s.agentid AND s.userid = '{CreatorId}' WHERE a.creatorid = '{CreatorId.Trim()}' ";
                if (!string.IsNullOrEmpty(Keyword))
                {
                    sql += $" and a.name like '%{Keyword.Trim()}%' ";
                }
                if (!string.IsNullOrEmpty(AgentType))
                {
                        sql += $" and a.agenttype = '{AgentType.Trim()}' ";
                }
                
                int offset = (PageNum - 1) * PageSize;
                sql += $" ORDER BY a.userno DESC  LIMIT {PageSize} OFFSET {offset}";

                return connection.Query<AgentDataPage>(sql).ToList();
            }
        }
        public int GetMyAgentDataCount(string Keyword, string AgentType, string CreatorId)
        {
            using (IDbConnection connection = new MySqlConnection(connectionString))
            {
                var sql = $"SELECT COUNT(*) FROM agentdata a LEFT JOIN staragent s ON a.id = s.agentid AND s.userid = '{CreatorId}' WHERE  a.creatorid = '{CreatorId.Trim()}' ";
                if (!string.IsNullOrEmpty(Keyword))
                {
                    sql += $" and a.name like '%{Keyword.Trim()}%' ";
                }
                if (!string.IsNullOrEmpty(AgentType))
                 {
                        sql += $" and a.agenttype = '{AgentType.Trim()}' ";
                 }
                sql += $"  ORDER BY modificationtime DESC ";
                int Count = connection.Query<int>(sql).FirstOrDefault();
                return Count;
            }
        }

        public bool DelAgentDataById(int id)
        {
            using (IDbConnection connection = new MySqlConnection(connectionString))
            {
                var sql = $"delete from agentdata where id={id}";
                connection.Open();
                return connection.Execute(sql) > 0;
            }
        }

        public bool UpdateAgentdata(AgentDataPage agentDataPage)
        {
            using (IDbConnection connection = new MySqlConnection(connectionString))
            {
                var sql = @"
                UPDATE agentdata
                SET
                    name = @Name,
                    prompt = @Prompt,
                    permission = @Permission,
                    agenttype = @AgentType,
                    modificationTime = NOW(),
                    avatarLink = @AvatarLink,
                    description = @Description,
                    modelapi = @ModelApi,
                    modelkey = @ModelKey,
                    systemrouting = @SystemRouting
                WHERE id = @Id";

                var parameters = new
                {
                    Name = agentDataPage.Name,
                    Prompt = agentDataPage.Prompt,
                    Permission = agentDataPage.Permission,
                    AvatarLink = agentDataPage.AvatarLink,
                    Id = agentDataPage.Id,
                    AgentType = agentDataPage.AgentType,
                    Description = agentDataPage.Description,
                    ModelApi = agentDataPage.ModelApi,
                    ModelKey = agentDataPage.ModelKey,
                    SystemRouting = agentDataPage.SystemRouting
                };

                connection.Open(); // 打开数据库连接
                int affectedRows = connection.Execute(sql, parameters); // 执行更新操作
                return affectedRows > 0;
            }
        }

        public bool AddAgentData(AgentDataPage agentDataPage)
        {
            using (IDbConnection connection = new MySqlConnection(connectionString))
            {
                var sql = @"
                INSERT INTO agentdata (name, prompt, permission, creationtime, modificationtime, avatarLink, creatorid,agenttype,description,modelapi,modelkey,systemrouting)
                VALUES ( @Name, @Prompt, @Permission,NOW(), NOW(), @AvatarLink, @CreatorId,@AgentType,@Description,@ModelApi,@ModelKey,@SystemRouting)";

                var parameters = new 
                {
                    Name = agentDataPage.Name,
                    Description = agentDataPage.Description,
                    Permission = agentDataPage.Permission,
                    AvatarLink = agentDataPage.AvatarLink,
                    CreatorId = agentDataPage.CreatorId,
                    AgentType = agentDataPage.AgentType,
                    Prompt = agentDataPage.Prompt,
                    ModelApi = agentDataPage.ModelApi,
                    ModelKey = agentDataPage.ModelKey,
                    SystemRouting = agentDataPage.SystemRouting
                };

                connection.Open(); // 打开数据库连接
                int affectedRows = connection.Execute(sql, parameters); // 执行更新操作
                return affectedRows > 0;
            }
        }

    }
}