STHApplication

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

namespace JiDangJia.Core;

public sealed record FinanceFilter(DateTime Start,DateTime End,string Major="ALL",string Detail="ALL",string Direction="ALL",string Query="",bool IncludeVoided=false);
public sealed record FinanceLine(DbRow Record,string Major,string Detail,decimal Amount)
{
    public bool Income=>Record.Text("type")=="INCOME";
}
public sealed record FinanceGroup(string Key,string Label,decimal Income,decimal Expense);
public sealed record FinanceResult(List<FinanceLine> Lines,List<FinanceGroup> Groups,decimal Profit)
{
    public decimal Income=>Lines.Where(l=>l.Income).Sum(l=>l.Amount);
    public decimal Expense=>Lines.Where(l=>!l.Income).Sum(l=>l.Amount);
}

public static class FinanceAnalytics
{
    public static readonly Dictionary<string,string> Majors=new(){["RECYCLE"]="回收",["PAWN"]="典当",["SALE"]="销售",["STORE"]="店铺",["OTHER"]="其他"};
    public static readonly HashSet<string> Capital=["PRINCIPAL_IN","PRINCIPAL","REDEEM","PAWN_OUT","PAWN","RECYCLE_OUT","RECYCLE_COST","RECYCLE","STOCK_OUT","STOCK_REFUND","SALE_REFUND","SALE_IN","SALE","DEPOSIT_IN","DEPOSIT_REFUND","RENT_SALE_BALANCE"];
    public static Dictionary<string,string> Details(string major)
    {
        if(major=="STORE")return new(){["RENT"]="房租",["UTILITIES"]="水电网",["SALARY"]="员工工资",["STORE_OTHER"]="店铺其他",["FUNDING_COST"]="资金成本"};
        return new Dictionary<string,string>{["GOLD"]="黄金",["SILVER"]="白银",["PLATINUM"]="铂金",["METAL_OTHER"]="其他贵金属"}
            .Concat(ItemCatalog.Categories.Where(p=>p.Key!="METAL")).Concat(new Dictionary<string,string>{["UNCLASSIFIED"]="未分类"}).ToDictionary(p=>p.Key,p=>p.Value);
    }
    public static string DetailLabel(string major,string detail)=>Details(major).GetValueOrDefault(detail,Display.Category(detail));
    public static Dictionary<string,string> AvailableDetails(Database db,string major,bool includeVoided=false)
    {
        var details=Read(db,new(DateTime.MinValue.Date,DateTime.MaxValue.Date.AddDays(-1),major,IncludeVoided:includeVoided)).Groups.ToDictionary(g=>g.Key,g=>g.Label);
        if(major=="OTHER")
        {
            var categories=db.Query("SELECT code,label,type,enabled FROM FinanceCategory WHERE code LIKE 'CUSTOM_%' ORDER BY sortOrder,createdAt,id");
            foreach(var row in categories.Where(r=>r.Int("enabled")==1))details[row.Text("code")]=row.Text("label");
            foreach(var group in details.GroupBy(p=>p.Value,StringComparer.OrdinalIgnoreCase).Where(g=>g.Count()>1).ToList())
                foreach(var choice in group)
                    if(categories.FirstOrDefault(r=>r.Text("code")==choice.Key) is { } row)details[choice.Key]=choice.Value+(row.Text("type")=="INCOME"?"（收入）":"（支出）");
        }
        return details;
    }
    public static string ItemDetail(DbRow item)=>ItemCatalog.Category(item.Text("type",item.Text("itemType")))=="METAL" ? ItemCatalog.Material(item) is "OTHER"?"METAL_OTHER":ItemCatalog.Material(item) : ItemCatalog.Category(item.Text("type",item.Text("itemType","UNCLASSIFIED")));
    public static List<decimal> Split(decimal amount,List<decimal> weights)
    {
        if(weights.Count==0)return [];
        if(weights.All(w=>w<=0))weights=weights.Select(_=>1m).ToList();else weights=weights.Select(w=>Math.Max(0,w)).ToList();
        var total=weights.Sum();var exact=weights.Select(w=>Fees.Round(amount)*100*w/total).ToList();
        var cents=exact.Select(decimal.Floor).ToList();int remaining=(int)(Fees.Round(amount)*100-cents.Sum());
        foreach(var i in Enumerable.Range(0,cents.Count).OrderByDescending(i=>exact[i]-cents[i]).ThenBy(i=>i).Take(remaining))cents[i]++;
        return cents.Select(v=>v/100).ToList();
    }
    private static string SourceMajor(string source)=>source switch{"RECYCLE"=>"RECYCLE","DEAD"=>"PAWN","PURCHASE"=>"SALE",_=>"OTHER"};
    private static string LegacyMajor(string category)=>category switch
    {
        "RECYCLE_OUT" or "RECYCLE_COST" or "RECYCLE" or "RECOVERY"=>"RECYCLE",
        "INTEREST" or "C_FEE" or "STORAGE_FEE" or "PENALTY_IN" or "PRINCIPAL_IN" or "PRINCIPAL" or "REDEEM" or "PAWN_OUT" or "PAWN" or "DEAD_SALE"=>"PAWN",
        "STOCK_OUT" or "SALE_IN" or "SALE_REFUND" or "SALE"=>"SALE",
        "RENT" or "SALARY" or "UTILITIES" or "FUNDING_COST"=>"STORE",_=>"OTHER"
    };
    internal static void EnsureSnapshots(SqliteConnection c)
    {
        foreach(var r in Database.Query(c,"SELECT * FROM FinanceRecord WHERE allocations IS NULL OR allocations=''"))
            Database.Execute(c,"UPDATE FinanceRecord SET allocations=@p0 WHERE id=@p1",Snapshot(c,r),r.Id);
    }
    internal static string Snapshot(SqliteConnection c,DbRow row)
    {
        var items=new List<DbRow>();var major=row.Text("majorClass");var id=row.Text("ticketId");
        void Add(DbRow item,string table,string reference){var copy=new DbRow(item){["referenceTable"]=table,["referenceId"]=reference};items.Add(copy);}
        if(major.Length==0)
        {
            var pawn=Database.Query(c,"SELECT * FROM PawnTicket WHERE id=@p0",id).FirstOrDefault();
            var intake=Database.Query(c,"SELECT * FROM RecycleOrder WHERE id=@p0",id).FirstOrDefault();
            var sale=Database.Query(c,"SELECT * FROM SaleRecord WHERE id=@p0 OR (@p1='RECOVERY' AND @p2='回收收入 '||saleNo) LIMIT 1",id,row.Text("category"),row.Text("remark")).FirstOrDefault();
            var business=Database.Query(c,"SELECT * FROM BusinessOrder WHERE id=@p0",id).FirstOrDefault();
            if(pawn is not null)
            {
                major="PAWN";foreach(var item in Database.Query(c,"SELECT * FROM PawnItem WHERE ticketId=@p0 ORDER BY id",id))Add(item,"PawnTicket",id);
                if(items.Count==0)Add(new(){["type"]=pawn.Text("itemType")},"PawnTicket",id);
            }
            else if(intake is not null)
            {
                major=SourceMajor(intake.Text("source"));foreach(var item in Database.Query(c,"SELECT * FROM RecycleItem WHERE orderId=@p0 ORDER BY id",id))Add(item,"RecycleItem",item.Id);
                if(items.Count==0)Add(new(){["type"]=intake.Text("itemType")},"RecycleOrder",id);
            }
            else if(sale is not null)
            {
                var item=Database.Query(c,"SELECT * FROM RecycleItem WHERE id=@p0",sale.Text("itemId")).FirstOrDefault();
                major=SourceMajor(item?.Text("source")??"");if(item is not null)Add(item,"RecycleItem",item.Id);
            }
            else if(business is not null)
            {
                major="OTHER";foreach(var item in RentalInventory.Items(business))
                {
                    var stockId=item.Text("stockId");var data=stockId.Length>0?Database.Query(c,"SELECT * FROM RecycleItem WHERE id=@p0",stockId).FirstOrDefault()??item:item;
                    Add(data,stockId.Length>0?"RecycleItem":"BusinessOrder",stockId.Length>0?stockId:id);
                }
                if(items.Count==0)Add(new(){["type"]=business.Text("itemType")},"BusinessOrder",id);
            }
            else major=LegacyMajor(row.Text("category"));
        }
        if(items.Count==0)items.Add(new(){["detail"]=row.Text("detailClass").Length>0?row.Text("detailClass"):major=="STORE"?row.Text("category"):"UNCLASSIFIED"});
        var amounts=Split(row.Money("amount"),items.Select(i=>i.Money("amount")>0?i.Money("amount"):i.Money("appraisalPrice")).ToList());
        return JsonSerializer.Serialize(items.Select((i,n)=>new DbRow{["major"]=major,["detail"]=i.Text("detail").Length>0?i.Text("detail"):ItemDetail(i),["amount"]=amounts[n],["referenceTable"]=i.Text("referenceTable"),["referenceId"]=i.Text("referenceId")}));
    }
    public static FinanceResult Read(Database db,FinanceFilter filter)
    {
        if(filter.End.Date<filter.Start.Date)throw new ArgumentException("结束日期不能早于开始日期");
        return db.Read<FinanceResult>(c=>
        {
            var lines=new List<FinanceLine>();var refs=new Dictionary<string,bool>();
            var categoryLabels=Database.Query(c,"SELECT code,label FROM FinanceCategory").GroupBy(r=>r.Text("code")).ToDictionary(g=>g.Key,g=>g.First().Text("label"));
            bool Visible(string table,string id)
            {
                if(table.Length==0 || id.Length==0)return true;
                if(!RecordLifecycle.Tables.Contains(table))return false;
                var key=table+":"+id;if(refs.TryGetValue(key,out var cached))return cached;
                var record=Database.Query(c,$"SELECT * FROM [{table}] WHERE id=@p0 AND deletedAt IS NULL",id).FirstOrDefault();
                bool ok=record is not null&&(filter.IncludeVoided||record.Text("status") is not ("VOID" or "CANCELLED"));
                if(ok&&table=="RecycleItem")
                {
                    foreach(var parent in new[]{("RecycleOrder","orderId"),("PawnTicket","ticketId"),("BusinessOrder","originBusinessId")})
                        if(record!.Text(parent.Item2).Length>0)ok&=Visible(parent.Item1,record.Text(parent.Item2));
                }
                refs[key]=ok;return ok;
            }
            bool LinkedVisible(DbRow row)
            {
                var id=row.Text("ticketId");if(id.Length==0)return true; // Independent manual entries have no business parent.
                foreach(var table in new[]{"PawnTicket","RecycleOrder","SaleRecord","BusinessOrder","FundingSettlement"})
                    if(Database.Query(c,$"SELECT id FROM [{table}] WHERE id=@p0",id).Count>0)return Visible(table,id);
                // Old versions may leave a snapshot after permanently erasing its source order.
                return false;
            }
            foreach(var row in Database.Query(c,"SELECT * FROM FinanceRecord WHERE deletedAt IS NULL AND occurDate>=@p0 AND occurDate<@p1 ORDER BY occurDate DESC,createdAt DESC",Display.Day(filter.Start),Display.Day(filter.End.AddDays(1))))
            {
                if(!LinkedVisible(row))continue;
                if(filter.Direction!="ALL" && row.Text("type")!=filter.Direction)continue;
                if(filter.Query.Length>0 && !new[]{row.Text("remark"),row.Text("occurDate"),categoryLabels.GetValueOrDefault(row.Text("category"),Display.Category(row.Text("category")))}.Any(v=>v.Contains(filter.Query,StringComparison.OrdinalIgnoreCase)))continue;
                var snapshot=row.Text("allocations");if(snapshot.Length==0)snapshot=Snapshot(c,row);
                List<DbRow> allocations;try{using var d=JsonDocument.Parse(snapshot);allocations=d.RootElement.EnumerateArray().Select(DbRow.FromJson).ToList();}catch(Exception ex)when(ex is JsonException or InvalidOperationException){allocations=[new(){["major"]=LegacyMajor(row.Text("category")),["detail"]="UNCLASSIFIED",["amount"]=row.Money("amount")}];}
                foreach(var a in allocations)
                    if(a.Money("amount")>0 && Visible(a.Text("referenceTable"),a.Text("referenceId")) && (filter.Major=="ALL" || filter.Major==a.Text("major")) && (filter.Detail=="ALL" || filter.Detail==a.Text("detail")))lines.Add(new(row,a.Text("major"),a.Text("detail"),a.Money("amount")));
            }
            var groups=lines.GroupBy(l=>filter.Major=="ALL"?l.Major:l.Detail).Select(g=>new FinanceGroup(g.Key,filter.Major=="ALL"?Majors.GetValueOrDefault(g.Key,g.Key):categoryLabels.GetValueOrDefault(g.Key,DetailLabel(filter.Major,g.Key)),g.Where(l=>l.Income).Sum(l=>l.Amount),g.Where(l=>!l.Income).Sum(l=>l.Amount))).OrderByDescending(g=>g.Income+g.Expense).ToList();
            var legacy=Database.Query(c,"SELECT saleNo FROM SaleRecord").Select(r=>"回收收入 "+r.Text("saleNo")).ToHashSet();
            var profit=lines.Where(l=>!Capital.Contains(l.Record.Text("category")) && !(l.Record.Text("category")=="RECOVERY"&&legacy.Contains(l.Record.Text("remark")))).Sum(l=>l.Amount*(l.Income?1:-1));
            if(filter.Direction!="EXPENSE")foreach(var sale in Database.Query(c,"SELECT s.*,i.type,i.material,i.source,i.name FROM SaleRecord s JOIN RecycleItem i ON i.id=s.itemId WHERE s.status='DONE' AND s.deletedAt IS NULL AND i.deletedAt IS NULL AND s.saleDate>=@p0 AND s.saleDate<@p1",Display.Day(filter.Start),Display.Day(filter.End.AddDays(1))))
            {
                if(!Visible("RecycleItem",sale.Text("itemId")))continue;
                var major=SourceMajor(sale.Text("source"));
                bool selected=filter.Query.Length==0 || lines.Any(l=>l.Income && (l.Record.Text("ticketId")==sale.Id || sale.Text("rentalOrderId").Length>0&&l.Record.Text("ticketId")==sale.Text("rentalOrderId") || l.Record.Text("category")=="RECOVERY"&&l.Record.Text("remark")=="回收收入 "+sale.Text("saleNo")));
                if((filter.Major=="ALL" || filter.Major==major) && (filter.Detail=="ALL" || filter.Detail==ItemDetail(sale)) && selected)profit+=sale.Money("profit");
            }
            return new(lines,groups,Fees.Round(profit));
        });
    }
}