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

namespace JiDangJia.Core;

public static class RecordLifecycle
{
    public static readonly string[] Tables=["Customer","PawnTicket","RecycleOrder","RecycleItem","SaleRecord","BusinessOrder","FinanceRecord","FundingAccount","FundingSettlement"];
    internal static HashSet<(string Table,string Id)> Related(SqliteConnection c,string table,string id)
    {
        var nodes=new HashSet<(string,string)>();
        void Visit(string t,string key)
        {
            if(!nodes.Add((t,key)))return;
            void Children(string child,string field){foreach(var r in Database.Query(c,$"SELECT id FROM [{child}] WHERE [{field}]=@p0",key))Visit(child,r.Id);}
            if(t=="PawnTicket")Children("RecycleItem","ticketId");
            if(t=="FundingAccount")Children("FundingSettlement","accountId");
            if(t=="FundingSettlement")Children("FinanceRecord","ticketId");
            if(t=="FinanceRecord"&&FundingCost.LinkedSettlement(c,t,key) is {} funding)Visit("FundingSettlement",funding.Id);
            if(t=="RecycleOrder")Children("RecycleItem","orderId");
            if(t=="BusinessOrder"){Children("RecycleItem","originBusinessId");Children("SaleRecord","rentalOrderId");}
            if(t=="RecycleItem")
            {
                Children("SaleRecord","itemId");
                foreach(var r in Database.Query(c,"SELECT * FROM BusinessOrder WHERE kind='RENT'"))if(RentalInventory.Items(r).Any(i=>i.Text("stockId")==key))Visit("BusinessOrder",r.Id);
            }
            if(t is "PawnTicket" or "RecycleOrder" or "BusinessOrder" or "SaleRecord")Children("FinanceRecord","ticketId");
        }
        Visit(table,id);return nodes;
    }
}

public sealed partial class BusinessService
{
    public List<DbRow> TrashEntries()=>db.Read(c=>RecordLifecycle.Tables.SelectMany(t=>Database.Query(c,$"SELECT * FROM [{t}] WHERE deletedAt IS NOT NULL AND (trashGroup=@p0||id OR trashGroup IS NULL OR trashGroup='')",t+":").Select(r=>new DbRow(r){["table"]=t})).OrderByDescending(r=>r.Text("deletedAt")).ToList());
    public void SoftDelete(string table,string id)
    {
        if(!RecordLifecycle.Tables.Contains(table))throw new ArgumentException("不能删除此数据表");
        db.Write(c=>
        {
            var row=Database.Query(c,$"SELECT * FROM [{table}] WHERE id=@p0 AND deletedAt IS NULL",id).FirstOrDefault()??throw new InvalidOperationException("记录已移入回收站或不存在");
            FundingCost.ValidateDeletion(c,table,id);
            if(table=="Customer" && (Database.Query(c,"SELECT id FROM PawnTicket WHERE customerId=@p0 AND status='ACTIVE' AND deletedAt IS NULL",id).Count>0 || Database.Query(c,"SELECT id FROM BusinessOrder WHERE customerId=@p0 AND status='OPEN' AND deletedAt IS NULL",id).Count>0))throw new InvalidOperationException("客户尚有进行中的业务，不能删除客户档案");
            FinanceAnalytics.EnsureSnapshots(c);var group=table+":"+id;var now=Database.Now;
            foreach(var n in RecordLifecycle.Related(c,table,id))Database.Execute(c,$"UPDATE [{n.Table}] SET deletedAt=@p0,trashGroup=@p1 WHERE id=@p2 AND deletedAt IS NULL",now,group,n.Id);
            RentalInventory.Reconcile(c);Database.Log(c,"DELETE",table,id,row.Text("name",row.Text("itemName")));return 0;
        });
    }
    public void Restore(string table,string id)
    {
        if(!RecordLifecycle.Tables.Contains(table))throw new ArgumentException("不能还原此数据表");
        db.Write(c=>
        {
            var row=Database.Query(c,$"SELECT * FROM [{table}] WHERE id=@p0 AND deletedAt IS NOT NULL",id).FirstOrDefault()??throw new InvalidOperationException("该记录不在回收站");
            var group=row.Text("trashGroup");
            if(group.Length>0 && group!=table+":"+id)throw new InvalidOperationException("请先还原关联的业务记录");
            if(table=="RecycleItem")
            {
                foreach(var parent in new[]{("RecycleOrder","orderId"),("PawnTicket","ticketId"),("BusinessOrder","originBusinessId")})
                    if(row.Text(parent.Item2).Length>0 && Database.Query(c,$"SELECT id FROM [{parent.Item1}] WHERE id=@p0 AND deletedAt IS NULL",row.Text(parent.Item2)).Count==0)throw new InvalidOperationException("请先还原来源单据");
            }
            var restored=new HashSet<(string Table,string Id)>{(table,id)};
            if(group.Length>0)foreach(var t in RecordLifecycle.Tables)foreach(var r in Database.Query(c,$"SELECT id FROM [{t}] WHERE trashGroup=@p0",group))restored.Add((t,r.Id));
            if(group.Length>0)foreach(var t in RecordLifecycle.Tables)Database.Execute(c,$"UPDATE [{t}] SET deletedAt=NULL,trashGroup=NULL WHERE trashGroup=@p0",group);
            else Database.Execute(c,$"UPDATE [{table}] SET deletedAt=NULL WHERE id=@p0",id);
            if(Database.Query(c,"SELECT s.id FROM SaleRecord s LEFT JOIN RecycleItem i ON i.id=s.itemId WHERE s.deletedAt IS NULL AND (i.id IS NULL OR i.deletedAt IS NOT NULL)").Any(r=>restored.Contains(("SaleRecord",r.Id))))throw new InvalidOperationException("请先还原销售物品及来源单据");
            foreach(var rental in Database.Query(c,"SELECT * FROM BusinessOrder WHERE kind='RENT' AND deletedAt IS NULL"))
                if(restored.Contains(("BusinessOrder",rental.Id)) && RentalInventory.Items(rental).Any(i=>i.Text("stockId").Length>0 && Database.Query(c,"SELECT id FROM RecycleItem WHERE id=@p0 AND deletedAt IS NULL",i.Text("stockId")).Count==0))throw new InvalidOperationException("请先还原租赁物品及来源单据");
            foreach(var stock in Database.Query(c,"SELECT * FROM RecycleItem WHERE deletedAt IS NULL"))
            {
                var sales=Database.Query(c,"SELECT id FROM SaleRecord WHERE itemId=@p0 AND status='DONE' AND deletedAt IS NULL",stock.Id).Count;
                var rentals=Database.Query(c,"SELECT * FROM BusinessOrder WHERE kind='RENT' AND status='OPEN' AND deletedAt IS NULL").Count(r=>RentalInventory.Items(r).Any(i=>i.Text("stockId")==stock.Id));
                if(sales>1 || rentals>1 || sales>0&&rentals>0)throw new InvalidOperationException("物品已有新的销售或租赁，不能还原旧业务");
            }
            FundingCost.ValidateRestored(c,restored);
            RentalInventory.Reconcile(c);Database.Log(c,"RESTORE",table,id,row.Text("name",row.Text("itemName")));return 0;
        });
    }
    public int PurgeTrash(IReadOnlyList<(string Table,string Id)> entries)
    {
        if(entries.Count==0)return 0;
        if(entries.Any(e=>!RecordLifecycle.Tables.Contains(e.Table)))throw new ArgumentException("不能删除此数据表");
        return db.Write(c=>
        {
            var nodes=new HashSet<(string Table,string Id)>();int count=0;
            foreach(var entry in entries.Distinct())
            {
                var row=Database.Query(c,$"SELECT * FROM [{entry.Table}] WHERE id=@p0 AND deletedAt IS NOT NULL",entry.Id).FirstOrDefault();
                if(row is null)continue;
                var group=row.Text("trashGroup");if(group.Length>0 && group!=entry.Table+":"+entry.Id)throw new InvalidOperationException("请选择回收站中的主记录");
                count++;
                if(group.Length>0)foreach(var table in RecordLifecycle.Tables)foreach(var r in Database.Query(c,$"SELECT id FROM [{table}] WHERE trashGroup=@p0",group))nodes.Add((table,r.Id));
                nodes.UnionWith(RecordLifecycle.Related(c,entry.Table,entry.Id));
            }
            var affectedIntakes=Database.Query(c,"SELECT * FROM RecycleItem").Where(r=>nodes.Contains(("RecycleItem",r.Id)) && r.Text("orderId").Length>0).Select(r=>r.Text("orderId")).ToHashSet();
            foreach(var intake in affectedIntakes)
                if(Database.Query(c,"SELECT id FROM RecycleItem WHERE orderId=@p0",intake).All(r=>nodes.Contains(("RecycleItem",r.Id))))nodes.UnionWith(RecordLifecycle.Related(c,"RecycleOrder",intake));
            FinanceAnalytics.EnsureSnapshots(c);
            // Remove the erased item's portion from mixed intake flows; retain the other items.
            foreach(var finance in Database.Query(c,"SELECT * FROM FinanceRecord WHERE allocations IS NOT NULL"))
            {
                if(nodes.Contains(("FinanceRecord",finance.Id)))continue;
                List<DbRow> old;
                try {using var d=JsonDocument.Parse(finance.Text("allocations"));old=d.RootElement.EnumerateArray().Select(DbRow.FromJson).ToList();}
                catch(Exception ex)when(ex is JsonException or InvalidOperationException){continue;}
                var kept=old.Where(a=>!nodes.Contains((a.Text("referenceTable"),a.Text("referenceId")))).ToList();
                if(kept.Count==old.Count)continue;
                if(kept.Count==0)nodes.Add(("FinanceRecord",finance.Id));else Database.Execute(c,"UPDATE FinanceRecord SET allocations=@p0,amount=@p1 WHERE id=@p2",JsonSerializer.Serialize(kept),kept.Sum(a=>a.Money("amount")),finance.Id);
            }
            foreach(var n in nodes)
            {
                if(n.Table=="PawnTicket")foreach(var child in new[]{"PawnItem","RenewRecord","RedeemRecord","Reminder"})Database.Execute(c,$"DELETE FROM [{child}] WHERE ticketId=@p0",n.Id);
                Database.Execute(c,"DELETE FROM OperationLog WHERE targetType=@p0 AND targetId=@p1",n.Table,n.Id);
                Database.Execute(c,$"DELETE FROM [{n.Table}] WHERE id=@p0",n.Id);
            }
            foreach(var intake in affectedIntakes.Where(i=>!nodes.Contains(("RecycleOrder",i))))
            {
                var items=Database.Query(c,"SELECT * FROM RecycleItem WHERE orderId=@p0 ORDER BY id",intake);
                Database.Execute(c,"UPDATE RecycleOrder SET amount=@p0,itemName=@p1,itemType=@p2 WHERE id=@p3",items.Sum(i=>i.Money("amount")),string.Join("、",items.Select(i=>i.Text("name"))),items.FirstOrDefault()?.Text("type")??"OTHER",intake);
            }
            RentalInventory.Reconcile(c);return count;
        });
    }
    public int ClearTrash()=>PurgeTrash(TrashEntries().Select(r=>(r.Text("table"),r.Id)).ToList());
}