STHApplication

using System.Net.Http.Json;
using System.Security.Cryptography;
using System.Text;
using System.Text.Json;
using JiDangJia.Contracts;
using Microsoft.Data.Sqlite;
using static JiDangJia.Server.ServerStore;

namespace JiDangJia.Server;

public sealed partial class CommunityStore
{
    private static void InitializeModeration(SqliteConnection c)
    {
        Column(c,"CommunityPosts","moderationStatus","TEXT NOT NULL DEFAULT 'PENDING'");Column(c,"CommunityPosts","moderationId","INTEGER NOT NULL DEFAULT 0");Column(c,"CommunityPosts","serviceJson","TEXT NOT NULL DEFAULT ''");
        Column(c,"CommunityComments","moderationStatus","TEXT NOT NULL DEFAULT 'PENDING'");Column(c,"CommunityComments","moderationId","INTEGER NOT NULL DEFAULT 0");
        Column(c,"ChatMessages","moderationStatus","TEXT NOT NULL DEFAULT 'APPROVED'");Column(c,"ChatMessages","moderationId","INTEGER NOT NULL DEFAULT 0");Column(c,"ChatMessages","exchangeId","TEXT NOT NULL DEFAULT ''");
        Exec(c,"""
        CREATE TABLE IF NOT EXISTS CommunityModeration(id INTEGER PRIMARY KEY AUTOINCREMENT,kind TEXT NOT NULL,targetId INTEGER NOT NULL,authorId TEXT NOT NULL,text TEXT NOT NULL,mediaJson TEXT NOT NULL,status TEXT NOT NULL,reason TEXT NOT NULL DEFAULT '',createdAt TEXT NOT NULL,reviewerId TEXT NOT NULL DEFAULT '');
        CREATE TABLE IF NOT EXISTS ChatDeliveries(sequence INTEGER PRIMARY KEY AUTOINCREMENT,accountId TEXT NOT NULL,messageId INTEGER NOT NULL,UNIQUE(accountId,messageId));
        CREATE TABLE IF NOT EXISTS ChatReadMessages(accountId TEXT NOT NULL,messageId INTEGER NOT NULL,PRIMARY KEY(accountId,messageId));
        CREATE INDEX IF NOT EXISTS idx_delivery_account ON ChatDeliveries(accountId,sequence);
        """);
        Exec(c,"INSERT OR IGNORE INTO ChatReadMessages(accountId,messageId) SELECT r.accountId,m.id FROM ChatReads r JOIN ChatMessages m ON m.recipientId=r.accountId AND m.senderId=r.peerId AND m.id<=r.lastId WHERE m.moderationStatus='APPROVED'");
        // 取消公开发布的预审；只释放等待中的内容，不恢复已拒绝或删除的内容。
        Exec(c,"UPDATE CommunityPosts SET moderationStatus='APPROVED',moderationId=0 WHERE moderationStatus='PENDING' AND deletedAt IS NULL");
        Exec(c,"UPDATE CommunityComments SET moderationStatus='APPROVED',moderationId=0 WHERE moderationStatus='PENDING' AND deletedAt IS NULL");
        Exec(c,"UPDATE ChatMessages SET moderationStatus='APPROVED',moderationId=0 WHERE moderationStatus='PENDING'");
        Exec(c,"UPDATE CommunityModeration SET status='SUPERSEDED',reason='已取消内容预审' WHERE kind IN ('POST','COMMENT','MESSAGE') AND status IN ('QUEUED','REVIEW')");
    }
    private static string ModeratedTable(string kind)=>kind switch{"POST"=>"CommunityPosts","COMMENT"=>"CommunityComments","MESSAGE"=>"ChatMessages",_=>throw new ArgumentException("审核类型无效")};
    private static long Queue(SqliteConnection c,string kind,long target,string author,string text,List<CommunityMedia> media,bool admin)
    {
        Exec(c,"UPDATE CommunityModeration SET status='SUPERSEDED' WHERE kind=@p0 AND targetId=@p1 AND status IN ('QUEUED','REVIEW')",kind,target);
        Exec(c,$"UPDATE {ModeratedTable(kind)} SET moderationStatus='APPROVED',moderationId=0 WHERE id=@p0",target);return 0;
    }
    private static ModerationItem ModerationFrom(Dictionary<string,object?> r)=>new(Number(r,"id"),Text(r,"kind"),Number(r,"targetId"),Text(r,"authorId"),Text(r,"text"),ReadJson<List<CommunityMedia>>(Text(r,"mediaJson"))??[],Text(r,"status"),Text(r,"reason"),Text(r,"createdAt"));
    public List<ModerationItem> ModerationQueue(Account admin)=>store.Read(c=>
    {if(!Actor(c,admin))throw new CommunityException(403,"需要管理员权限");return Rows(c,"SELECT * FROM CommunityModeration WHERE status IN ('REVIEW','QUEUED') ORDER BY id LIMIT 100").Select(ModerationFrom).ToList();});
    internal ModerationItem? NextModeration()=>store.Read(c=>Rows(c,"SELECT * FROM CommunityModeration WHERE status='QUEUED' ORDER BY id LIMIT 1").Select(ModerationFrom).FirstOrDefault());
    public ModerationItem DecideModeration(Account admin,long id,ModerationDecision decision)
    {
        store.Read(c=>{if(!Actor(c,admin))throw new CommunityException(403,"需要管理员权限");return 0;});if(!decision.Approved&&string.IsNullOrWhiteSpace(decision.Reason))throw new ArgumentException("拒绝须填写原因");return FinishModeration(id,decision.Approved?"APPROVED":"REJECTED",decision.Reason,admin.Id);
    }
    internal ModerationItem FinishModeration(long id,string result,string? reason,string reviewer="")=>store.Write(c=>
    {
        if(result is not ("APPROVED" or "REJECTED" or "REVIEW"))throw new ArgumentException("审核结论无效");reason=(reason??"").Trim();if(reason.Length>500)reason=reason[..500];var r=Rows(c,"SELECT * FROM CommunityModeration WHERE id=@p0",id).FirstOrDefault()??throw new CommunityException(404,"审核任务不存在");
        if(Text(r,"status") is not ("QUEUED" or "REVIEW"))return ModerationFrom(r);string table=ModeratedTable(Text(r,"kind"));long target=Number(r,"targetId");
        var current=Rows(c,$"SELECT moderationId FROM {table} WHERE id=@p0",target).FirstOrDefault();if(current is null||Number(current,"moderationId")!=id){Exec(c,"UPDATE CommunityModeration SET status='SUPERSEDED' WHERE id=@p0",id);return ModerationFrom(Rows(c,"SELECT * FROM CommunityModeration WHERE id=@p0",id)[0]);}
        Exec(c,"UPDATE CommunityModeration SET status=@p0,reason=@p1,reviewerId=@p2 WHERE id=@p3",result,reason,reviewer,id);Exec(c,$"UPDATE {table} SET moderationStatus=@p0 WHERE id=@p1",result=="REVIEW"?"PENDING":result,target);
        if(Text(r,"kind")=="MESSAGE")Exec(c,"DELETE FROM ChatDeliveries WHERE messageId=@p0 AND accountId=@p1",target,Text(r,"authorId"));
        Exec(c,"INSERT INTO Audit VALUES(@p0,@p1,'SOCIAL_CONTENT_REVIEW',@p2,@p3,@p4)",Guid.NewGuid().ToString("N"),reviewer,id.ToString(),result,Now);return ModerationFrom(Rows(c,"SELECT * FROM CommunityModeration WHERE id=@p0",id)[0]);
    });
    internal byte[] ModerationMedia(ModerationItem item,string id)
    {if(!item.Media.Any(m=>m.Id==id))throw new CommunityException(403,"附件不属于此社群审核任务");return media.ScanBytes(id,item.AuthorId);}
    public (byte[] Bytes,string Mime) ReviewMedia(Account admin,long job,string id)
    {
        var item=store.Read(c=>{if(!Actor(c,admin))throw new CommunityException(403,"需要管理员权限");return ModerationFrom(Rows(c,"SELECT * FROM CommunityModeration WHERE id=@p0 AND status IN ('REVIEW','QUEUED')",job).FirstOrDefault()??throw new CommunityException(404,"待审核任务不存在"));});var m=item.Media.FirstOrDefault(m=>m.Id==id)??throw new CommunityException(403,"附件不属于此审核任务");return(ModerationMedia(item,id),m.MimeType);
    }
}

// 出站协议固定只含社群文字和社群上传媒体；本服务没有业务数据库或认证资料的依赖。
public sealed class CommunityModerationWorker(CommunityStore community,IConfiguration configuration,IHostEnvironment environment) : BackgroundService
{
    private readonly HttpClient http=new(){Timeout=TimeSpan.FromSeconds(45)};
    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        string url=configuration["JDJ_MODERATION_URL"]??"",secret=configuration["JDJ_MODERATION_TOKEN"]??"";
        if(url.Length==0)return;
        if(!Uri.TryCreate(url,UriKind.Absolute,out var uri)||uri.UserInfo.Length>0||uri.Scheme!="https"&&!(environment.IsDevelopment()&&uri.IsLoopback&&uri.Scheme=="http"))throw new InvalidOperationException("社群审核适配器必须使用 HTTPS；本机开发测试允许回环 HTTP");
        while(!stoppingToken.IsCancellationRequested)
        {
            var job=community.NextModeration();if(job is null){await Task.Delay(1500,stoppingToken);continue;}
            try
            {
                var payload=new{ticket=job.Id.ToString(),kind=job.Kind,text=job.Text,media=job.Media.Select(m=>new{kind=m.Kind,mimeType=m.MimeType,bytesBase64=Convert.ToBase64String(community.ModerationMedia(job,m.Id))}).ToList()};
                using var request=new HttpRequestMessage(HttpMethod.Post,uri){Content=JsonContent.Create(payload)};if(secret.Length>0)request.Headers.Authorization=new("Bearer",secret);
                using var response=await http.SendAsync(request,stoppingToken);response.EnsureSuccessStatusCode();var result=await response.Content.ReadFromJsonAsync<ScanResult>(cancellationToken:stoppingToken);
                if(result is null||result.Ticket!=job.Id.ToString()||result.Decision is not ("APPROVED" or "REJECTED" or "REVIEW"))throw new InvalidOperationException("审核结论无效");
                community.FinishModeration(job.Id,result.Decision,result.Reason,"external");
            }
            catch(OperationCanceledException)when(stoppingToken.IsCancellationRequested){return;}
            catch{community.FinishModeration(job.Id,"REVIEW","自动审核未完成，转人工复核");}
        }
    }
    private sealed record ScanResult(string Ticket,string Decision,string? Reason);
    public override void Dispose(){http.Dispose();base.Dispose();}
}