STHApplication

using JiDangJia.Contracts;
using Microsoft.Data.Sqlite;
using static JiDangJia.Server.ServerStore;

namespace JiDangJia.Server;

public sealed partial class CommunityStore
{
    private static string ChatCutoff=>DateTime.UtcNow.AddDays(-ChatRetention.MessageDays).ToString("O");
    private static void InitializeChatRetention(SqliteConnection c)
    {
        Exec(c,"CREATE INDEX IF NOT EXISTS idx_chat_expiry ON ChatMessages(createdAt)");
        // Existing chat attachments acquire the same upload-based expiry as newly sent ones.
        Exec(c,"""
        UPDATE CommunityMedia SET chatExpiresAt=strftime('%Y-%m-%dT%H:%M:%fZ',julianday(createdAt)+CASE kind WHEN 'IMAGE' THEN 7 WHEN 'VIDEO' THEN 3 ELSE 30 END)
        WHERE chatExpiresAt='' AND EXISTS(SELECT 1 FROM ChatMessages m WHERE json_extract(NULLIF(m.mediaJson,''),'$.id')=CommunityMedia.id);
        """);
    }

    public ChatSyncPage SyncMessages(Account actor,long after)=>store.Write(c=>
    {
        Actor(c,actor);if(after<0)throw new ArgumentException("同步游标无效");Deliver(c,actor);
        var rows=Rows(c,"""
        SELECT m.*,a.name senderName,d.sequence,p.id peerId,p.username peerUsername,p.name peerName,p.identityKind,p.identityStatus,p.communityCredit,
        COALESCE((SELECT remark FROM CommunityFollows f WHERE f.accountId=@p0 AND f.peerId=p.id),'') remark,
        EXISTS(SELECT 1 FROM CommunityFollows f WHERE f.accountId=@p0 AND f.peerId=p.id) following,
        EXISTS(SELECT 1 FROM ChatReadMessages r WHERE r.accountId=@p0 AND r.messageId=m.id) isRead
        FROM ChatDeliveries d JOIN ChatMessages m ON m.id=d.messageId JOIN Accounts a ON a.id=m.senderId
        JOIN Accounts p ON p.id=CASE WHEN m.senderId=@p0 THEN m.recipientId ELSE m.senderId END
        WHERE d.accountId=@p0 AND d.sequence>@p1 AND m.createdAt>@p2
        AND m.id>COALESCE((SELECT throughId FROM AccountChatClears WHERE accountId=@p0),0)
        AND (m.moderationStatus='APPROVED' OR m.senderId=@p0) ORDER BY d.sequence LIMIT 101
        """,actor.Id,after,ChatCutoff);
        var items=rows.Take(100).Select(r=>new ChatSyncItem(Message(r),new CommunityAccount(Text(r,"peerId"),Text(r,"peerUsername"),Text(r,"peerName"),Number(r,"following")==1,Text(r,"remark"),Text(r,"identityKind"),Text(r,"identityStatus")=="APPROVED",Text(r,"communityCredit")),Text(r,"senderId")==actor.Id||Number(r,"isRead")==1)).ToList();
        var read=Rows(c,"SELECT peerId,lastId FROM ChatReads WHERE accountId=@p0",actor.Id).Select(r=>new MarkReadRequest(Text(r,"peerId"),Number(r,"lastId"))).ToList();
        var cleared=Rows(c,"SELECT throughId,clearedAt FROM AccountChatClears WHERE accountId=@p0",actor.Id).FirstOrDefault();
        return new ChatSyncPage(items,items.Count>0?items[^1].Message.Sequence:after,rows.Count>100,read,cleared is null?0:Number(cleared,"throughId"),cleared is null?"":Text(cleared,"clearedAt"));
    });
}

public sealed record ChatCleanupResult(int Messages,int Media);

// This job touches chat-owned material only. Public posts, identity documents,
// reports and application releases have independent stores and lifecycles.
public sealed class CommunityChatRetention(ServerStore store,CommunityStore community,TimeProvider clock)
{
    public ChatCleanupResult Sweep()=>store.Write(c=>
    {
        _=community;Exec(c,"PRAGMA secure_delete=ON");var now=clock.GetUtcNow();int files=0;
        foreach(var row in Rows(c,"""
            SELECT id FROM CommunityMedia m WHERE chatExpiresAt<>'' AND expiredAt='' AND julianday(chatExpiresAt)<=julianday(@p0)
            AND NOT EXISTS(SELECT 1 FROM CommunityPosts p WHERE instr(p.mediaJson,m.id)>0) LIMIT 1000
            """,now.ToString("O")))
        {
            var id=Text(row,"id");if(!System.Text.RegularExpressions.Regex.IsMatch(id,"^[a-f0-9]{32}$"))continue;
            try
            {
                var file=Path.Combine(store.DataDirectory,"community-media",id+".media");
                if(File.Exists(file)){File.Delete(file);files++;}
                Exec(c,"UPDATE CommunityMedia SET expiredAt=CASE WHEN expiredAt='' THEN @p0 ELSE expiredAt END WHERE id=@p1",now.ToString("O"),id);
            }
            catch(IOException){}catch(UnauthorizedAccessException){}
        }
        var cutoff=now.AddDays(-ChatRetention.MessageDays).UtcDateTime.ToString("O");
        Exec(c,"DELETE FROM CommunityModeration WHERE kind='MESSAGE' AND targetId IN (SELECT id FROM ChatMessages WHERE createdAt<=@p0)",cutoff);
        Exec(c,"DELETE FROM ChatReadMessages WHERE messageId IN (SELECT id FROM ChatMessages WHERE createdAt<=@p0)",cutoff);
        Exec(c,"DELETE FROM ChatDeliveries WHERE messageId IN (SELECT id FROM ChatMessages WHERE createdAt<=@p0)",cutoff);
        int messages=Exec(c,"DELETE FROM ChatMessages WHERE createdAt<=@p0",cutoff);
        Exec(c,"""
            DELETE FROM CommunityMedia WHERE expiredAt<>'' AND NOT EXISTS(SELECT 1 FROM ChatMessages m WHERE json_extract(NULLIF(m.mediaJson,''),'$.id')=CommunityMedia.id)
            AND NOT EXISTS(SELECT 1 FROM CommunityPosts p WHERE instr(p.mediaJson,CommunityMedia.id)>0)
            """);
        // Expired contents must not linger in SQLite's freelist/WAL after a completed checkpoint.
        return new ChatCleanupResult(messages,files);
    });
    public void Checkpoint()=>store.Read(c=>{Exec(c,"PRAGMA wal_checkpoint(TRUNCATE)");return 0;});
}

public sealed class CommunityChatRetentionWorker(CommunityChatRetention retention,ILogger<CommunityChatRetentionWorker> logger) : BackgroundService
{
    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        using var timer=new PeriodicTimer(TimeSpan.FromMinutes(15));
        do
        {
            try{var result=retention.Sweep();retention.Checkpoint();if(result.Messages>0||result.Media>0)logger.LogInformation("私信保留期清理：消息 {Messages} 条，附件 {Media} 个",result.Messages,result.Media);}
            catch(Exception ex)when(ex is IOException or SqliteException or UnauthorizedAccessException){logger.LogWarning(ex,"私信到期清理未完成，将自动重试");}
        }while(await timer.WaitForNextTickAsync(stoppingToken));
    }
}