STHApplication

using System.Globalization;
using System.Text.Json;
using Microsoft.Data.Sqlite;

namespace JiDangJia.Core;

public sealed record FundingPreview(DbRow Account,DateTime Start,DateTime End,int Days,decimal Amount,bool HasTrash);

public static class FundingCost
{
    public static readonly Dictionary<string,string> Providers=new(){["BANK"]="银行信贷",["PERSON"]="个人",["OTHER"]="其他"};
    public static readonly Dictionary<string,string> RatePeriods=new(){["ANNUAL"]="年利率",["MONTHLY"]="月利率"};
    public static Dictionary<string,string> Bases(string period)=>period=="MONTHLY"
        ?new(){["30"]="每月按 30 天"}:new(){["365"]="每年按 365 天",["360"]="每年按 360 天"};
    public static DateTime Date(string value,string label)
    {
        if(!DateTime.TryParseExact(value,"yyyy-MM-dd",CultureInfo.InvariantCulture,DateTimeStyles.None,out var date))throw new ArgumentException(label+"格式不正确");
        return date;
    }
    public static void Validate(DbRow account)
    {
        if(string.IsNullOrWhiteSpace(account.Text("name"))||account.Text("name").Length>200)throw new ArgumentException("请填写资方名称，最多 200 字");
        if(!Providers.ContainsKey(account.Text("providerType")))throw new ArgumentException("请选择资方类型");
        BusinessService.Positive(account.Money("principal"),"资方本金");
        if(account.Money("principal")!=Fees.Round(account.Money("principal")))throw new ArgumentException("本金最多保留两位小数");
        if(!decimal.TryParse(account.Text("rate"),NumberStyles.Number,CultureInfo.InvariantCulture,out var rate)||rate<0||rate>100||decimal.Round(rate,6)!=rate)throw new ArgumentException("利率应为 0% 至 100%，最多六位小数");
        if(!RatePeriods.ContainsKey(account.Text("ratePeriod"))||!Bases(account.Text("ratePeriod")).ContainsKey(account.Text("dayBasis")))throw new ArgumentException("请选择有效的利率周期与计息口径");
        if(Date(account.Text("startDate"),"起息日期")>DateTime.Today)throw new ArgumentException("起息日期不能晚于今天");
    }
    public static string Rule(DbRow account)=>$"{RatePeriods.GetValueOrDefault(account.Text("ratePeriod"),"利率")} {account.Money("rate"):0.######}% · {Bases(account.Text("ratePeriod")).GetValueOrDefault(account.Text("dayBasis"),"")}";
    public static decimal Interest(DbRow account,DateTime start,DateTime end)
    {
        Validate(account);var origin=Date(account.Text("startDate"),"起息日期");start=start.Date;end=end.Date;
        if(start<origin||end<start)throw new ArgumentException("计息区间不能早于起息日，结束日不能早于开始日");
        decimal daily=account.Money("principal")*account.Money("rate")/100/account.Money("dayBasis");
        // Round cumulative interest at both boundaries so split settlements conserve cents.
        return Fees.Round(daily*(end-origin).Days)-Fees.Round(daily*(start-origin).Days);
    }
    internal static FundingPreview Preview(SqliteConnection c,string id,DateTime end)
    {
        var account=Database.Query(c,"SELECT * FROM FundingAccount WHERE id=@p0 AND deletedAt IS NULL",id).FirstOrDefault()??throw new InvalidOperationException("资方资金不存在或已移入回收站");
        Validate(account);var rows=Database.Query(c,"SELECT * FROM FundingSettlement WHERE accountId=@p0 ORDER BY periodEnd",id);
        var start=rows.Count>0?Date(rows[^1].Text("periodEnd"),"已结算日期"):Date(account.Text("startDate"),"起息日期");
        end=end.Date;
        if(account.Text("endDate").Length>0&&end>Date(account.Text("endDate"),"停止计息日期"))end=Date(account.Text("endDate"),"停止计息日期");
        if(end>DateTime.Today)throw new ArgumentException("计息截止日不能晚于今天");
        var amount=Interest(account,start,end);
        return new(account,start,end,(end-start).Days,amount,rows.Any(r=>r.Text("deletedAt").Length>0));
    }
    internal static DbRow? LinkedSettlement(SqliteConnection c,string table,string id)=>table=="FundingSettlement"
        ?Database.Query(c,"SELECT * FROM FundingSettlement WHERE id=@p0",id).FirstOrDefault()
        :table=="FinanceRecord"?Database.Query(c,"SELECT s.* FROM FundingSettlement s JOIN FinanceRecord f ON f.ticketId=s.id WHERE f.id=@p0",id).FirstOrDefault():null;
    internal static void ValidateDeletion(SqliteConnection c,string table,string id)
    {
        if(LinkedSettlement(c,table,id) is not {} settlement)return;
        if(Database.Query(c,"SELECT id FROM FundingSettlement WHERE accountId=@p0 AND periodEnd>@p1",settlement.Text("accountId"),settlement.Text("periodEnd")).Count>0)throw new InvalidOperationException("请从最新一笔资金成本结算开始删除，保持计息区间连续");
    }
    internal static void ValidateRestored(SqliteConnection c,HashSet<(string Table,string Id)> restored)
    {
        foreach(var settlement in Database.Query(c,"SELECT * FROM FundingSettlement WHERE deletedAt IS NULL"))
        {
            if(!restored.Contains(("FundingSettlement",settlement.Id)))continue;
            if(Database.Query(c,"SELECT id FROM FundingAccount WHERE id=@p0 AND deletedAt IS NULL",settlement.Text("accountId")).Count==0)throw new InvalidOperationException("请先还原资方资金记录");
            if(Database.Query(c,"SELECT id FROM FundingSettlement WHERE accountId=@p0 AND id<>@p1 AND periodStart<@p2 AND periodEnd>@p3 AND deletedAt IS NULL",settlement.Text("accountId"),settlement.Id,settlement.Text("periodEnd"),settlement.Text("periodStart")).Count>0)throw new InvalidOperationException("存在重叠的资金成本结算区间，无法还原");
        }
    }
}

public sealed partial class BusinessService
{
    public string SaveFunding(DbRow input)
    {
        var row=new DbRow(input);FundingCost.Validate(row);
        return db.Write(c=>
        {
            if(row.Id.Length>0)
            {
                var old=Database.Query(c,"SELECT * FROM FundingAccount WHERE id=@p0 AND deletedAt IS NULL",row.Id).FirstOrDefault()??throw new InvalidOperationException("资方资金记录不存在");
                if(old.Text("endDate").Length>0)throw new InvalidOperationException("资金已停止计息，不能修改；请新建资金记录");
                if(Database.Query(c,"SELECT id FROM FundingSettlement WHERE accountId=@p0",row.Id).Count>0&&(new[]{"principal","rate","dayBasis"}.Any(k=>old.Money(k)!=row.Money(k))||new[]{"ratePeriod","startDate"}.Any(k=>old.Text(k)!=row.Text(k))))throw new InvalidOperationException("已有结算，不能修改本金、利率或起息日；请先结清本笔，再新建资金记录");
                row["createdAt"]=old["createdAt"];
            }
            else{row["id"]=Database.Id("fund");row["createdAt"]=Database.Now;}
            // Only editable fields enter this table; callers cannot set closing/trash metadata.
            var safe=new DbRow{["id"]=row.Id,["name"]=row.Text("name").Trim(),["providerType"]=row.Text("providerType"),["principal"]=row.Money("principal"),["rate"]=row.Money("rate"),["ratePeriod"]=row.Text("ratePeriod"),["dayBasis"]=row.Int("dayBasis"),["startDate"]=row.Text("startDate"),["remark"]=row.Text("remark"),["updatedAt"]=Database.Now};
            if(row.ContainsKey("createdAt"))safe["createdAt"]=row["createdAt"];
            Database.Upsert(c,"FundingAccount",safe);Database.Log(c,"FUNDING_SAVE","FundingAccount",row.Id,safe.Text("name"));return row.Id;
        });
    }
    public FundingPreview FundingPreview(string id,DateTime end)=>db.Read(c=>FundingCost.Preview(c,id,end));
    public List<DbRow> FundingAccounts()=>db.Query("SELECT * FROM FundingAccount WHERE deletedAt IS NULL ORDER BY (endDate IS NOT NULL AND endDate<>'') ASC,createdAt DESC");
    public string SettleFunding(string id,DateTime expectedStart,DateTime end,DateTime paidDate,string remark="")
        =>db.Write(c=>SettleFunding(c,id,expectedStart,end,paidDate,remark));
    private string SettleFunding(SqliteConnection c,string id,DateTime expectedStart,DateTime end,DateTime paidDate,string remark)
    {
            var preview=FundingCost.Preview(c,id,end);
            if(preview.HasTrash)throw new InvalidOperationException("有资金结算在回收站，请先还原或永久删除后再结算");
            if(preview.Start!=expectedStart.Date)throw new InvalidOperationException("结算区间已变化，请刷新后重试，避免重复支出");
            if(preview.Days<=0)throw new ArgumentException("请选择晚于已结算截止日的日期");
            paidDate=paidDate.Date;if(paidDate<preview.End||paidDate>DateTime.Today)throw new ArgumentException("支出发生日期应在计息截止日至今天之间");
            var settlementId=Database.Id("fundsettle");
            var settlement=new DbRow{["id"]=settlementId,["accountId"]=id,["name"]=preview.Account.Text("name")+" · 资金成本",["periodStart"]=Display.Day(preview.Start),["periodEnd"]=Display.Day(preview.End),["days"]=preview.Days,["amount"]=preview.Amount,["occurDate"]=Display.Day(paidDate),["principal"]=preview.Account.Money("principal"),["rate"]=preview.Account.Money("rate"),["ratePeriod"]=preview.Account.Text("ratePeriod"),["dayBasis"]=preview.Account.Int("dayBasis"),["remark"]=remark,["createdAt"]=Database.Now};
            Database.Upsert(c,"FundingSettlement",settlement);
            if(preview.Amount>0)
            {
                var finance=new DbRow{["id"]=Database.Id("finance"),["type"]="EXPENSE",["category"]="FUNDING_COST",["majorClass"]="STORE",["detailClass"]="FUNDING_COST",["amount"]=preview.Amount,["ticketId"]=settlementId,["operatorId"]=Owner.Id,["occurDate"]=Display.Day(paidDate),["remark"]=$"{preview.Account.Text("name")}（{FundingCost.Providers[preview.Account.Text("providerType")] }） · {Display.Day(preview.Start)} 至 {Display.Day(preview.End)}（不含） · {preview.Days} 天 · {FundingCost.Rule(preview.Account)}"+(remark.Length>0?" · "+remark:""),["createdAt"]=Database.Now};
                finance["allocations"]=JsonSerializer.Serialize(new[]{new DbRow{["major"]="STORE",["detail"]="FUNDING_COST",["amount"]=preview.Amount,["referenceTable"]="FundingSettlement",["referenceId"]=settlementId}});
                Database.Upsert(c,"FinanceRecord",finance);
            }
            Database.Log(c,"FUNDING_SETTLE","FundingSettlement",settlementId,settlement.Text("name"),$"{preview.Days} 天 / {preview.Amount:F2} 元");return settlementId;
    }
    public void EndFunding(string id,DateTime end)
        =>db.Write(c=>{EndFunding(c,id,end);return 0;});
    private static void EndFunding(SqliteConnection c,string id,DateTime end)
    {
            var p=FundingCost.Preview(c,id,end);
            if(p.Account.Text("endDate").Length>0)throw new InvalidOperationException("这笔资金已停止计息");
            if(p.HasTrash)throw new InvalidOperationException("请先处理回收站中的资金结算");
            if(p.Days!=0)throw new InvalidOperationException("请先将利息结算到停止计息日期");
            Database.Execute(c,"UPDATE FundingAccount SET endDate=@p0,updatedAt=@p1 WHERE id=@p2",Display.Day(p.End),Database.Now,id);Database.Log(c,"FUNDING_END","FundingAccount",id,p.Account.Text("name"));
    }
    public void CompleteFunding(string id,DateTime expectedStart,DateTime end,DateTime paidDate,string remark,bool finish,decimal? expectedAmount=null)
    {
        db.Write(c=>
        {
            var p=FundingCost.Preview(c,id,end);
            if(p.Start!=expectedStart.Date)throw new InvalidOperationException("结算区间已变化，请刷新后重试，避免重复支出");
            if(expectedAmount.HasValue&&p.Amount!=expectedAmount.Value)throw new InvalidOperationException("资金条件或费用已变化，请刷新预览后再确认");
            if(p.Days>0||!finish)SettleFunding(c,id,expectedStart,end,paidDate,remark);
            if(finish)EndFunding(c,id,end);
            return 0;
        });
    }
}