using Yitter.IdGenerator;

namespace Admin.NET.Plugin.AiDOP.Order;

/// <summary>
/// 销售订单评审服务 🗂️
/// 路由前缀：/api/Order/seorder/...
/// </summary>
[ApiDescriptionSettings(Order = 300, Description = "销售订单评审")]
[Route("api/Order")]
[AllowAnonymous]
[NonUnify]
public class SeOrderService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly SqlSugarRepository<SeOrder> _seOrderRep;
    private readonly SqlSugarRepository<SeOrderEntry> _seOrderEntryRep;
    private readonly SqlSugarRepository<SeOrderChange> _seOrderChangeRep;
    private readonly UserManager _userManager;

    public SeOrderService(
        ISqlSugarClient db,
        SqlSugarRepository<SeOrder> seOrderRep,
        SqlSugarRepository<SeOrderEntry> seOrderEntryRep,
        SqlSugarRepository<SeOrderChange> seOrderChangeRep,
        UserManager userManager)
    {
        _db = db;
        _seOrderRep = seOrderRep;
        _seOrderEntryRep = seOrderEntryRep;
        _seOrderChangeRep = seOrderChangeRep;
        _userManager = userManager;
    }

    // ══════════════════════════════════════════════════════════════
    // 订单列表 GET /api/Order/seorder/list
    // ══════════════════════════════════════════════════════════════
    /// <summary>获取订单评审分页列表 🗂️</summary>
    [DisplayName("获取订单评审列表")]
    [HttpGet("seorder/list")]
    public async Task<object> GetSeOrderList([FromQuery] SeOrderListInput input)
    {
        var pars = new List<SugarParameter>();
        var innerConditions = new List<string> { "a.IsDeleted = 0" };

        if (!string.IsNullOrWhiteSpace(input.BillNo))
        {
            innerConditions.Add("a.bill_no LIKE @BillNo");
            pars.Add(new SugarParameter("@BillNo", $"%{input.BillNo.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.CustomNo))
        {
            innerConditions.Add("a.custom_no LIKE @CustomNo");
            pars.Add(new SugarParameter("@CustomNo", $"%{input.CustomNo.Trim()}%"));
        }
        if (input.OrderType.HasValue)
        {
            innerConditions.Add("a.order_type = @OrderType");
            pars.Add(new SugarParameter("@OrderType", input.OrderType.Value));
        }

        var baseSql = BuildListBaseSql(string.Join(" AND ", innerConditions));

        // state 是 CASE 计算列，需包一层再过滤
        var outerWhere = "";
        if (!string.IsNullOrWhiteSpace(input.State))
        {
            outerWhere = "WHERE t.State = @State";
            pars.Add(new SugarParameter("@State", input.State.Trim()));
        }

        var offset = (input.Page - 1) * input.PageSize;
        var total = await _db.Ado.GetIntAsync(
            $"SELECT COUNT(*) FROM ({baseSql}) AS t {outerWhere}", pars);
        var list = await _db.Ado.SqlQueryAsync<SeOrderListRow>(
            $"SELECT * FROM ({baseSql}) AS t {outerWhere} ORDER BY t.Id DESC LIMIT {input.PageSize} OFFSET {offset}", pars);

        return new { total, page = input.Page, pageSize = input.PageSize, list };
    }

    private static string BuildListBaseSql(string innerWhere) => $"""
        SELECT
            a.id               AS Id,
            a.bill_no          AS BillNo,
            a.order_type       AS OrderType,
            a.custom_no        AS CustomNo,
            b.SortName         AS SortName,
            a.rdate            AS RDate,
            a.flowstate        AS FlowState,
            entry.progress     AS Progress,
            CASE
                WHEN entry.progress = 1 THEN '新建'
                WHEN entry.progress = 2 THEN '评审'
                WHEN entry.progress = 0 THEN '再评审'
                WHEN entry.progress = 3 THEN '确认'
                ELSE '确认'
            END                AS State,
            chg.change_content AS ChangeContent,
            cN.changeNum       AS ChangeNum
        FROM crm_seorder a
        LEFT JOIN CustMaster b ON a.custom_no = b.Cust
        LEFT JOIN (
            SELECT sorderid, MAX(create_time) AS create_time
            FROM b_examine_result
            GROUP BY sorderid
        ) d ON a.id = d.sorderid
        LEFT JOIN (
            SELECT bill_no, change_content
            FROM (
                SELECT bill_no, change_content, update_time,
                       ROW_NUMBER() OVER (PARTITION BY bill_no ORDER BY update_time DESC) AS RowNum
                FROM crm_seorder_change
                WHERE bill_no IS NOT NULL
            ) ranked
            WHERE RowNum = 1
        ) chg ON chg.bill_no = a.bill_no
        LEFT JOIN (
            SELECT COUNT(*) AS changeNum, bill_no
            FROM crm_seorder_change
            WHERE bill_no IS NOT NULL
            GROUP BY bill_no
        ) cN ON cN.bill_no = a.bill_no
        LEFT JOIN (
            SELECT seorder_id, progress,
                   ROW_NUMBER() OVER (PARTITION BY seorder_id ORDER BY Id) AS rn
            FROM crm_seorderentry
            WHERE IsDeleted = 0
        ) entry ON a.id = entry.seorder_id AND entry.rn = 1
        WHERE {innerWhere}
        """;

    // ══════════════════════════════════════════════════════════════
    // 客户列表 GET /api/Order/seorder/customers
    // 注：字面量路由优先于 {id} 参数路由，无需额外排序
    // ══════════════════════════════════════════════════════════════
    /// <summary>获取客户选择列表 🗂️</summary>
    [DisplayName("获取客户列表")]
    [HttpGet("seorder/customers")]
    public async Task<object> GetCustomers([FromQuery] KeywordPageInput input)
    {
        var pars = new List<SugarParameter>();
        var where = "1=1";
        if (!string.IsNullOrWhiteSpace(input.Keyword))
        {
            where = "(Cust LIKE @Keyword OR SortName LIKE @Keyword)";
            pars.Add(new SugarParameter("@Keyword", $"%{input.Keyword.Trim()}%"));
        }

        var offset = (input.Page - 1) * input.PageSize;
        var total = await _db.Ado.GetIntAsync(
            $"SELECT COUNT(*) FROM CustMaster WHERE {where}", pars);
        var list = await _db.Ado.SqlQueryAsync<SimpleKvRow>(
            $"SELECT Cust AS Value, SortName AS Label, NULL AS Extra, NULL AS Id FROM CustMaster WHERE {where} ORDER BY Cust LIMIT {input.PageSize} OFFSET {offset}",
            pars);

        return new { total, page = input.Page, pageSize = input.PageSize, list };
    }

    // ══════════════════════════════════════════════════════════════
    // 物料列表 GET /api/Order/seorder/items
    // TODO: 将 bd_material 替换为实际物料主数据表名
    // ══════════════════════════════════════════════════════════════
    /// <summary>获取物料选择列表 🗂️</summary>
    [DisplayName("获取物料列表")]
    [HttpGet("seorder/items")]
    public async Task<object> GetItems([FromQuery] KeywordPageInput input)
    {
        var pars = new List<SugarParameter>();
        var where = "IsDeleted = 0";
        if (!string.IsNullOrWhiteSpace(input.Keyword))
        {
            where += " AND (item_number LIKE @Keyword OR item_name LIKE @Keyword)";
            pars.Add(new SugarParameter("@Keyword", $"%{input.Keyword.Trim()}%"));
        }

        var offset = (input.Page - 1) * input.PageSize;
        var total = await _db.Ado.GetIntAsync(
            $"SELECT COUNT(*) FROM bd_material WHERE {where}", pars);
        var list = await _db.Ado.SqlQueryAsync<SimpleKvRow>(
            $"SELECT item_number AS Value, item_name AS Label, CONCAT(IFNULL(specification,''),'|',IFNULL(unit,'')) AS Extra, NULL AS Id FROM bd_material WHERE {where} ORDER BY item_number LIMIT {input.PageSize} OFFSET {offset}",
            pars);

        return new { total, page = input.Page, pageSize = input.PageSize, list };
    }

    // ══════════════════════════════════════════════════════════════
    // 订单详情 GET /api/Order/seorder/{id}
    // ══════════════════════════════════════════════════════════════
    /// <summary>获取订单详情（含明细）🗂️</summary>
    [DisplayName("获取订单详情")]
    [HttpGet("seorder/{id}")]
    public async Task<object> GetSeOrderDetail(long id)
    {
        const string orderSql = """
            SELECT Id, bill_no AS BillNo, order_type AS OrderType,
                   custom_id AS CustomId, custom_no AS CustomNo, custom_name AS CustomName,
                   date AS Date, custom_level AS CustomLevel, urgent AS Urgent,
                   bill_from AS BillFrom, country AS Country, rdate AS RDate, flowstate AS FlowState
            FROM crm_seorder
            WHERE Id = @Id AND IsDeleted = 0
            LIMIT 1
            """;
        var order = (await _db.Ado.SqlQueryAsync<SeOrderDetailRow>(orderSql, new { Id = id }))
                    .FirstOrDefault()
                    ?? throw Oops.Oh("订单不存在");

        const string entrySql = """
            SELECT Id, entry_seq AS EntrySeq, item_number AS ItemNumber, item_name AS ItemName,
                   specification AS Specification, qty AS Qty, plan_date AS PlanDate,
                   sys_capacity_date AS SysCapacityDate, date AS Date, remark AS Remark,
                   unit AS Unit, deliver_count AS DeliverCount, progress AS Progress
            FROM crm_seorderentry
            WHERE seorder_id = @Id AND IsDeleted = 0
            ORDER BY entry_seq
            """;
        var entries = await _db.Ado.SqlQueryAsync<SeOrderEntryRow>(entrySql, new { Id = id });

        return new
        {
            order.Id, order.BillNo, order.OrderType,
            order.CustomId, order.CustomNo, order.CustomName,
            order.Date, order.CustomLevel, order.Urgent,
            order.BillFrom, order.Country, order.RDate, order.FlowState,
            entries
        };
    }

    // ══════════════════════════════════════════════════════════════
    // 新增 / 编辑 POST /api/Order/seorder/save
    // ══════════════════════════════════════════════════════════════
    /// <summary>保存销售订单（新增或编辑）🗂️</summary>
    [DisplayName("保存销售订单")]
    [ApiDescriptionSettings(Name = "SaveSeOrder"), HttpPost("seorder/save")]
    public async Task<object> SaveSeOrder([FromBody] SeOrderSaveInput input)
    {
        if (input.Id is null or 0)
        {
            // ── 新增：参照 SysJobService.AddJobDetail ──
            var isExist = await _seOrderRep.IsAnyAsync(u => u.BillNo == input.BillNo && u.IsDeleted == 0);
            if (isExist) throw Oops.Oh("订单编号已存在");

            var entity = input.Adapt<SeOrder>();
            entity.Id = YitIdHelper.NextId();
            entity.IsDeleted = 0;
            entity.CreateTime = DateTime.Now;
            await _seOrderRep.InsertAsync(entity);
            await SaveEntriesAsync(entity.Id, input.BillNo, input.Entries);
            return new { id = entity.Id, message = "新增成功" };
        }
        else
        {
            // ── 编辑：参照 SysJobService.UpdateJobDetail ──
            var entity = await _seOrderRep.GetFirstAsync(u => u.Id == input.Id.Value && u.IsDeleted == 0)
                         ?? throw Oops.Oh("订单不存在");

            input.Adapt(entity);
            entity.UpdateTime = DateTime.Now;
            await _seOrderRep.UpdateAsync(entity);

            await SaveEntriesAsync(input.Id.Value, input.BillNo, input.Entries);
            return new { id = input.Id, message = "编辑成功" };
        }
    }

    /// <summary>
    /// 明细三路合并：
    /// ① DB有且入参有（按 Id 匹配）→ 更新
    /// ② DB无但入参有              → 新增（补填 bill_no / progress=0 / create_by）
    /// ③ DB有但入参无              → 删除
    /// </summary>
    private async Task SaveEntriesAsync(long orderId, string billNo, List<SeOrderEntryInput> entries)
    {
        // 取 DB 现有明细，以 Id 为 key 建索引
        var dbEntries = await _seOrderEntryRep.GetListAsync(u => u.SeOrderId == orderId && u.IsDeleted == 0);
        var dbById = dbEntries.ToDictionary(u => u.Id);

        // 入参中携带 Id 的集合（用于判断 DB 行是否需要删除）
        var inputIds = new HashSet<long>(entries.Where(e => e.Id > 0).Select(e => e.Id!.Value));

        for (var i = 0; i < entries.Count; i++)
        {
            var e = entries[i];
            var seq = e.EntrySeq ?? (i + 1);

            if (e.Id > 0 && dbById.TryGetValue(e.Id.Value, out var existing))
            {
                // ① DB有、参数有 → 更新（参照 SysJobService.UpdateJobDetail）
                e.Adapt(existing);
                existing.EntrySeq = seq;
                existing.UpdateTime = DateTime.Now;
                await _seOrderEntryRep.UpdateAsync(existing);
            }
            else
            {
                // ② DB无、参数有 → 新增（参照 SysJobService.AddJobDetail）
                var entry = e.Adapt<SeOrderEntry>();
                entry.Id = YitIdHelper.NextId();
                entry.SeOrderId = orderId;
                entry.BillNo = billNo;
                entry.EntrySeq = seq;
                entry.Progress ??= 0;
                entry.CreateBy = _userManager.Account;
                entry.IsDeleted = 0;
                entry.CreateTime = DateTime.Now;
                await _seOrderEntryRep.InsertAsync(entry);
            }
        }

        // ③ DB有、参数无 → 删除（参照 SysJobService.DeleteJobDetail）
        foreach (var toDelete in dbEntries.Where(u => !inputIds.Contains(u.Id)))
        {
            await _seOrderEntryRep.DeleteAsync(u => u.Id == toDelete.Id);
        }
    }

    // ══════════════════════════════════════════════════════════════
    // 订单评审 POST /api/Order/seorder/{id}/review
    // ══════════════════════════════════════════════════════════════
    /// <summary>订单评审（设进度=2）⏰</summary>
    [DisplayName("订单评审")]
    [ApiDescriptionSettings(Name = "ReviewSeOrder"), HttpPost("seorder/{id}/review")]
    public async Task<object> ReviewSeOrder(long id)
    {
        // 参照 SysJobService 中 AsUpdateable().SetColumns().Where() 模式
        await _seOrderEntryRep.AsUpdateable()
            .SetColumns(u => new SeOrderEntry { Progress = 2, UpdateTime = DateTime.Now })
            .Where(u => u.SeOrderId == id && u.IsDeleted == 0)
            .ExecuteCommandAsync();

        return new { message = "评审完成", estimatedDeliveryDate = DateTime.Now.AddDays(30).ToString("yyyy-MM-dd") };
    }

    // ══════════════════════════════════════════════════════════════
    // 交期确认 POST /api/Order/seorder/{id}/confirm-delivery
    // ══════════════════════════════════════════════════════════════
    /// <summary>交期确认（设进度=3）⏰</summary>
    [DisplayName("交期确认")]
    [ApiDescriptionSettings(Name = "ConfirmSeOrderDelivery"), HttpPost("seorder/{id}/confirm-delivery")]
    public async Task<object> ConfirmDelivery(long id)
    {
        await _seOrderEntryRep.AsUpdateable()
            .SetColumns(u => new SeOrderEntry { Progress = 3, UpdateTime = DateTime.Now })
            .Where(u => u.SeOrderId == id && u.IsDeleted == 0)
            .ExecuteCommandAsync();

        return new { message = "交期已确认" };
    }

    // ══════════════════════════════════════════════════════════════
    // 资源解锁 POST /api/Order/seorder/{id}/unlock
    // ══════════════════════════════════════════════════════════════
    /// <summary>资源解锁 🔓</summary>
    [DisplayName("资源解锁")]
    [ApiDescriptionSettings(Name = "UnlockSeOrder"), HttpPost("seorder/{id}/unlock")]
    public async Task<object> UnlockSeOrder(long id)
    {
        return new { message = "解锁成功" };
    }

    // ══════════════════════════════════════════════════════════════
    // 变更申请 POST /api/Order/seorder/{id}/change
    // ══════════════════════════════════════════════════════════════
    /// <summary>提交订单变更申请 🔖</summary>
    [DisplayName("提交订单变更申请")]
    [ApiDescriptionSettings(Name = "CreateSeOrderChange"), HttpPost("seorder/{id}/change")]
    public async Task<object> CreateChange(long id, [FromBody] SeOrderChangeSaveInput input)
    {
        // 参照 SysJobService.AddJobDetail：Adapt → 设主键与审计字段 → InsertAsync
        var entity = input.Adapt<SeOrderChange>();
        entity.Id = YitIdHelper.NextId();
        entity.SeOrderId = id;
        entity.IsDeleted = 0;
        entity.CreateTime = DateTime.Now;
        await _seOrderChangeRep.InsertAsync(entity);
        return new { id = entity.Id, message = "变更申请已保存" };
    }

    // ──────────────── 内部查询结果映射类 ────────────────

    private sealed class SeOrderListRow
    {
        public long Id { get; set; }
        public string? BillNo { get; set; }
        public int? OrderType { get; set; }
        public string? CustomNo { get; set; }
        public string? SortName { get; set; }
        public DateTime? RDate { get; set; }
        public int? Progress { get; set; }
        public string? State { get; set; }
        public string? ChangeContent { get; set; }
        public int? ChangeNum { get; set; }
        public string? FlowState { get; set; }
    }

    private sealed class SeOrderDetailRow
    {
        public long Id { get; set; }
        public string? BillNo { get; set; }
        public int? OrderType { get; set; }
        public long? CustomId { get; set; }
        public string? CustomNo { get; set; }
        public string? CustomName { get; set; }
        public DateTime? Date { get; set; }
        public int? CustomLevel { get; set; }
        public int? Urgent { get; set; }
        public string? BillFrom { get; set; }
        public string? Country { get; set; }
        public DateTime? RDate { get; set; }
        public string? FlowState { get; set; }
    }

    private sealed class SeOrderEntryRow
    {
        public long Id { get; set; }
        public int? EntrySeq { get; set; }
        public string? ItemNumber { get; set; }
        public string? ItemName { get; set; }
        public string? Specification { get; set; }
        public decimal? Qty { get; set; }
        public DateTime? PlanDate { get; set; }
        public DateTime? SysCapacityDate { get; set; }
        public DateTime? Date { get; set; }
        public string? Remark { get; set; }
        public string? Unit { get; set; }
        public decimal? DeliverCount { get; set; }
        public int? Progress { get; set; }
    }

    private sealed class SimpleKvRow
    {
        public string? Value { get; set; }
        public string? Label { get; set; }
        public string? Extra { get; set; }
        public long? Id { get; set; }
    }
}