STHApplication

using JiDangJia.Contracts;
using Microsoft.Data.Sqlite;
using System.Globalization;
using System.Security.Cryptography;
using System.Text;
using System.Text.RegularExpressions;
using static JiDangJia.Server.ServerStore;

namespace JiDangJia.Server;

public sealed class PurchaseStore
{
    private readonly ServerStore store;
    private readonly AlipayGateway gateway;
    private readonly byte[] key;
    public PurchaseStore(ServerStore store,AlipayGateway gateway)
    {
        this.store=store;this.gateway=gateway;
        key=store.Write(c=>
        {
            Exec(c,"""
                CREATE TABLE IF NOT EXISTS PurchaseOrders(id TEXT PRIMARY KEY,planId TEXT NOT NULL,requestId TEXT NOT NULL,receiptHash TEXT NOT NULL,amountCents INTEGER NOT NULL,days INTEGER NOT NULL,permanent INTEGER NOT NULL,status TEXT NOT NULL DEFAULT 'PENDING',alipayTradeNo TEXT UNIQUE,encryptedCard TEXT NOT NULL DEFAULT '',cardId TEXT,createdAt TEXT NOT NULL,expiresAt TEXT NOT NULL,paidAt TEXT NOT NULL DEFAULT '',UNIQUE(receiptHash,requestId));
                CREATE INDEX IF NOT EXISTS idx_purchase_created ON PurchaseOrders(createdAt);
                CREATE TABLE IF NOT EXISTS PurchasePlanPrices(planId TEXT PRIMARY KEY,priceCents INTEGER NOT NULL,originalPriceCents INTEGER NOT NULL);
                CREATE TABLE IF NOT EXISTS PurchasePriceRevision(id INTEGER PRIMARY KEY CHECK(id=1),revision INTEGER NOT NULL);
                INSERT OR IGNORE INTO PurchasePriceRevision VALUES(1,1);
                """);
            foreach(var plan in PurchaseCatalog.Plans)Exec(c,"INSERT OR IGNORE INTO PurchasePlanPrices VALUES(@p0,@p1,@p2)",plan.Id,plan.PriceCents,plan.OriginalPriceCents);
            var path=Path.Combine(store.DataDirectory,"purchase-vault.key");
            if(!File.Exists(path)){using var file=new FileStream(path,FileMode.CreateNew,FileAccess.Write,FileShare.None);file.Write(RandomNumberGenerator.GetBytes(32));}
            var bytes=File.ReadAllBytes(path);if(bytes.Length!=32)throw new InvalidOperationException("购买订单密钥无效，请从服务器备份恢复");return bytes;
        });
    }
    private static PurchasePrices Prices(SqliteConnection c)
    {
        var rows=Rows(c,"SELECT * FROM PurchasePlanPrices");
        var plans=PurchaseCatalog.Plans.Select(plan=>
        {
            var price=rows.Single(r=>Text(r,"planId")==plan.Id);
            return plan with{PriceCents=Convert.ToInt32(price["priceCents"]),OriginalPriceCents=Convert.ToInt32(price["originalPriceCents"])};
        }).ToList();
        return new(Convert.ToInt64(Rows(c,"SELECT revision FROM PurchasePriceRevision WHERE id=1")[0]["revision"]),plans);
    }
    public PurchaseOffer Offer()=>store.Read(c=>
    {
        var prices=Prices(c);return new PurchaseOffer(prices.Plans,gateway.Enabled,gateway.Message,PurchaseUrl(c),prices.Revision);
    });
    private static string PurchaseUrl(SqliteConnection c)
    {
#if COMMUNITY_APP
        return Text(Rows(c,"SELECT purchaseUrl FROM CommunityPolicy WHERE id=1")[0],"purchaseUrl");
#else
        return "";
#endif
    }
    private static void RequireAdmin(SqliteConnection c,Account actor)
    {
        if(Rows(c,"SELECT id FROM Accounts WHERE id=@p0 AND role='admin' AND active=1",actor.Id).Count==0)throw new UnauthorizedAccessException("需要管理员权限");
    }
    public PurchasePrices AdminPrices(Account actor)=>store.Read(c=>{RequireAdmin(c,actor);return Prices(c);});
    public PurchasePrices UpdatePrices(Account actor,UpdatePurchasePricesRequest request)=>store.Write(c=>
    {
        RequireAdmin(c,actor);
        var current=Prices(c);
        if(request.ExpectedRevision!=current.Revision)throw new CommunityException(409,"价格已被其他管理员更新，请重新加载价格后修改");
        if(request.Plans is null||request.Plans.Count!=2||request.Plans.Any(p=>p is null)||request.Plans.Select(p=>p.PlanId).Distinct().Count()!=2||request.Plans.Any(p=>!current.Plans.Any(plan=>plan.Id==p.PlanId)))throw new ArgumentException("请完整设置一年卡和永久卡的价格");
        foreach(var price in request.Plans)
        {
            if(price.PriceCents<1||price.OriginalPriceCents<1||price.PriceCents>PurchaseCatalog.MaximumPriceCents||price.OriginalPriceCents>PurchaseCatalog.MaximumPriceCents)throw new ArgumentException("价格须在 0.01 至 1000000 元之间，最多两位小数");
            if(price.OriginalPriceCents<price.PriceCents)throw new ArgumentException("原价不能低于实际售价");
        }
        foreach(var price in request.Plans)Exec(c,"UPDATE PurchasePlanPrices SET priceCents=@p0,originalPriceCents=@p1 WHERE planId=@p2",price.PriceCents,price.OriginalPriceCents,price.PlanId);
        Exec(c,"UPDATE PurchasePriceRevision SET revision=revision+1 WHERE id=1");
        var detail=string.Join("；",current.Plans.Select(old=>{var price=request.Plans.Single(p=>p.PlanId==old.Id);return $"{old.Id} 售价分 {old.PriceCents}→{price.PriceCents} 原价分 {old.OriginalPriceCents}→{price.OriginalPriceCents}";}));
        Exec(c,"INSERT INTO Audit VALUES(@p0,@p1,'PURCHASE_PRICES_UPDATE','PLANS',@p2,@p3)",Guid.NewGuid().ToString("N"),actor.Id,detail,Now);
        return Prices(c);
    });
    private static void ValidateKey(string? receipt){if(receipt is null||!Regex.IsMatch(receipt,@"\A[a-fA-F0-9]{64}\z"))throw Missing();}
    private static CommunityException Missing()=>new(404,"订单不存在或查询凭证不正确");
    private static Dictionary<string,object?> Find(SqliteConnection c,string id,string receipt)
    {
        ValidateKey(receipt);if(!Regex.IsMatch(id,@"\AJDP[a-f0-9]{32}\z"))throw Missing();
        var row=Rows(c,"SELECT * FROM PurchaseOrders WHERE id=@p0",id).FirstOrDefault();
        if(row is null||!CryptographicOperations.FixedTimeEquals(Encoding.ASCII.GetBytes(Text(row,"receiptHash")),Encoding.ASCII.GetBytes(Hash(receipt.ToUpperInvariant()))))throw Missing();return row;
    }
    public PurchaseOrder Create(CreatePurchaseRequest request)
    {
        PurchaseCatalog.Plan(request.PlanId);ValidateKey(request.ReceiptKey);
        if(request.RequestId is null||!Regex.IsMatch(request.RequestId,@"\A[a-f0-9]{32}\z"))throw new ArgumentException("购买请求无效");
        if(!gateway.Enabled)throw new CommunityException(503,gateway.Message);
        return store.Write(c=>
        {
            // Capture current price and entitlement in the order's creation transaction.
            var plan=Prices(c).Plans.Single(p=>p.Id==request.PlanId);
            var hash=Hash(request.ReceiptKey.ToUpperInvariant());
            var old=Rows(c,"SELECT * FROM PurchaseOrders WHERE receiptHash=@p0 AND requestId=@p1",hash,request.RequestId).FirstOrDefault();
            if(old is not null){if(Text(old,"planId")!=plan.Id)throw new CommunityException(409,"此购买请求已用于其他套餐");return View(old);}
            var id="JDP"+Guid.NewGuid().ToString("N");
            Exec(c,"INSERT INTO PurchaseOrders(id,planId,requestId,receiptHash,amountCents,days,permanent,createdAt,expiresAt) VALUES(@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8)",id,plan.Id,request.RequestId,hash,plan.PriceCents,plan.Days,plan.Permanent?1:0,Now,DateTimeOffset.UtcNow.AddMinutes(30).ToString("O"));
            Log(c,"PURCHASE_CREATE",id,plan.Id);return View(Find(c,id,request.ReceiptKey));
        });
    }
    public PurchaseOrder Get(string id,string receipt)=>store.Read(c=>View(Find(c,id,receipt)));
    public PurchaseCheckout Checkout(string id,string receipt)=>store.Read(c=>
    {
        var row=Find(c,id,receipt);if(Text(row,"status")!="PENDING")throw new CommunityException(409,"此订单已付款或已关闭，请查看订单状态");
        var expires=DateTimeOffset.Parse(Text(row,"expiresAt"),CultureInfo.InvariantCulture);if(expires<=DateTimeOffset.UtcNow)throw new CommunityException(409,"订单已过期，请重新创建订单");
        return new PurchaseCheckout(gateway.Checkout(id,Convert.ToInt32(row["amountCents"]),expires));
    });
    private PurchaseOrder View(Dictionary<string,object?> row)
    {
        var status=Text(row,"status");if(status=="PENDING"&&DateTimeOffset.Parse(Text(row,"expiresAt"),CultureInfo.InvariantCulture)<=DateTimeOffset.UtcNow)status="EXPIRED";
        return new(Text(row,"id"),Text(row,"planId"),Convert.ToInt32(row["amountCents"]),status,Text(row,"createdAt"),Text(row,"expiresAt"),Text(row,"paidAt"),status=="PAID"?Unseal(Text(row,"id"),Text(row,"encryptedCard")):"");
    }
    public bool Notify(IReadOnlyDictionary<string,string> values)
    {
        if(!gateway.Verify(values))return false;
        var id=values.GetValueOrDefault("out_trade_no")??"";var trade=values.GetValueOrDefault("trade_no")??"";var amount=values.GetValueOrDefault("total_amount")??"";var state=values.GetValueOrDefault("trade_status");
        if(!Regex.IsMatch(id,@"\AJDP[a-f0-9]{32}\z")||!Regex.IsMatch(trade,@"\A[0-9]{16,64}\z")||!Regex.IsMatch(amount,@"\A[0-9]{1,8}(\.[0-9]{1,2})?\z")||!decimal.TryParse(amount,NumberStyles.AllowDecimalPoint,CultureInfo.InvariantCulture,out var money)||state is not ("WAIT_BUYER_PAY" or "TRADE_SUCCESS" or "TRADE_FINISHED" or "TRADE_CLOSED"))return false;
        return store.Write(c=>
        {
            var row=Rows(c,"SELECT * FROM PurchaseOrders WHERE id=@p0",id).FirstOrDefault();if(row is null||money*100!=Convert.ToInt32(row["amountCents"]))return false;
            var oldTrade=Text(row,"alipayTradeNo");if(oldTrade.Length>0&&oldTrade!=trade||Rows(c,"SELECT id FROM PurchaseOrders WHERE alipayTradeNo=@p0 AND id<>@p1",trade,id).Count>0)return false;
            var status=Text(row,"status");if(state=="WAIT_BUYER_PAY")return true;
            if(state=="TRADE_CLOSED")
            {
                if(status=="PAID"){Exec(c,"UPDATE PurchaseOrders SET status='REFUND_REVIEW' WHERE id=@p0",id);Exec(c,"UPDATE Cards SET active=0 WHERE id=@p0",row["cardId"]);Log(c,"PURCHASE_REFUND_REVIEW",id,"请人工核对已兑换授权");}
                else if(status=="PENDING")Exec(c,"UPDATE PurchaseOrders SET status='CLOSED',alipayTradeNo=@p0 WHERE id=@p1",trade,id);
                return true;
            }
            if(status=="PAID")return true;if(status!="PENDING")return false;
            // A verified late notification still completes payment; browser return never reaches this path.
            var code="JDJ-"+Convert.ToHexString(RandomNumberGenerator.GetBytes(12));var cardId=Guid.NewGuid().ToString("N");var encrypted=Seal(id,code);
            Exec(c,"INSERT INTO Cards(id,codeHash,maskedCode,batch,days,expiresAt,createdAt,permanent) VALUES(@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7)",cardId,Hash(code),code[..8]+"••••"+code[^4..],"支付宝 "+id,row["days"],DateTime.UtcNow.AddYears(10).ToString("O"),Now,row["permanent"]);
            Exec(c,"UPDATE PurchaseOrders SET status='PAID',alipayTradeNo=@p0,encryptedCard=@p1,cardId=@p2,paidAt=@p3 WHERE id=@p4",trade,encrypted,cardId,Now,id);Log(c,"PURCHASE_PAID",id,Text(row,"planId"));return true;
        });
    }
    public object Admin(Account actor,string? query)
    {
        query=(query??"").Trim();if(query.Length>64)throw new ArgumentException("搜索内容最多 64 字");
        return store.Read(c=>
        {
            RequireAdmin(c,actor);
            return new{alipayEnabled=gateway.Enabled,message=gateway.Message,orders=Rows(c,"SELECT o.id,o.planId,o.amountCents,o.status,o.createdAt,o.expiresAt,o.paidAt,o.alipayTradeNo,c.maskedCode,c.usedBy,c.usedDevice FROM PurchaseOrders o LEFT JOIN Cards c ON c.id=o.cardId WHERE @p0='' OR instr(o.id,@p0)>0 OR instr(COALESCE(o.alipayTradeNo,''),@p0)>0 ORDER BY o.createdAt DESC LIMIT 100",query)};
        });
    }
    private static void Log(SqliteConnection c,string action,string id,string detail)=>Exec(c,"INSERT INTO Audit VALUES(@p0,'PAYMENT',@p1,@p2,@p3,@p4)",Guid.NewGuid().ToString("N"),action,id,detail,Now);
    private string Seal(string id,string value)
    {
        var plain=Encoding.UTF8.GetBytes(value);var nonce=RandomNumberGenerator.GetBytes(12);var cipher=new byte[plain.Length];var tag=new byte[16];using var aes=new AesGcm(key,16);aes.Encrypt(nonce,plain,cipher,tag,Encoding.UTF8.GetBytes(id));return Convert.ToBase64String(nonce.Concat(tag).Concat(cipher).ToArray());
    }
    private string Unseal(string id,string value)
    {
        var bytes=Convert.FromBase64String(value);var plain=new byte[bytes.Length-28];using var aes=new AesGcm(key,16);aes.Decrypt(bytes.AsSpan(0,12),bytes.AsSpan(28),bytes.AsSpan(12,16),plain,Encoding.UTF8.GetBytes(id));return Encoding.UTF8.GetString(plain);
    }
}