STHApplication

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

namespace JiDangJia.Server;

public sealed partial class CommunityStore
{
    private readonly ServerStore store;
    private readonly CommunityMediaStore media;
    public CommunityStore(ServerStore store,CommunityMediaStore media)
    {
        this.store=store;this.media=media;
        store.Write(c=>Exec(c,"""
        CREATE TABLE IF NOT EXISTS CommunityPosts(id INTEGER PRIMARY KEY AUTOINCREMENT,authorId TEXT NOT NULL,title TEXT NOT NULL,content TEXT NOT NULL,clientId TEXT NOT NULL,createdAt TEXT NOT NULL,deletedAt TEXT,UNIQUE(authorId,clientId));
        CREATE INDEX IF NOT EXISTS idx_posts_visible ON CommunityPosts(deletedAt,id DESC);
        CREATE TABLE IF NOT EXISTS CommunityComments(id INTEGER PRIMARY KEY AUTOINCREMENT,postId INTEGER NOT NULL,authorId TEXT NOT NULL,content TEXT NOT NULL,clientId TEXT NOT NULL,createdAt TEXT NOT NULL,deletedAt TEXT,UNIQUE(authorId,clientId));
        CREATE INDEX IF NOT EXISTS idx_comments_post ON CommunityComments(postId,deletedAt,id DESC);
        CREATE TABLE IF NOT EXISTS CommunityLikes(postId INTEGER NOT NULL,accountId TEXT NOT NULL,PRIMARY KEY(postId,accountId));
        CREATE TABLE IF NOT EXISTS ChatMessages(id INTEGER PRIMARY KEY AUTOINCREMENT,senderId TEXT NOT NULL,recipientId TEXT NOT NULL,content TEXT NOT NULL,clientId TEXT NOT NULL,createdAt TEXT NOT NULL,UNIQUE(senderId,clientId));
        CREATE INDEX IF NOT EXISTS idx_chat_pair ON ChatMessages(senderId,recipientId,id);
        CREATE INDEX IF NOT EXISTS idx_chat_recipient ON ChatMessages(recipientId,id);
        CREATE TABLE IF NOT EXISTS ChatReads(accountId TEXT NOT NULL,peerId TEXT NOT NULL,lastId INTEGER NOT NULL DEFAULT 0,PRIMARY KEY(accountId,peerId));
        """));
        store.Write(c=>{InitializeSocial(c);InitializeModeration(c);InitializeChatRetention(c);Exec(c,"PRAGMA secure_delete=ON");return 0;});
    }
    private static long Number(Dictionary<string,object?> row,string key)=>Convert.ToInt64(row.GetValueOrDefault(key)??0);
    private static string Input(string? value,int max,string label)
    {
        value=(value??"").Replace("\r\n","\n").Trim();
        if(value.Length is 0||value.Length>max||value.Any(c=>char.IsControl(c)&&c is not '\n' and not '\t'))throw new ArgumentException($"{label}应为 1–{max} 字，不能含无效控制字符");
        return value;
    }
    private static string Nonce(string? value)
    {
        if(value is null||!System.Text.RegularExpressions.Regex.IsMatch(value,"^[a-fA-F0-9]{32}$"))throw new ArgumentException("请求标识无效");return value.ToLowerInvariant();
    }
    internal static bool Actor(SqliteConnection c,Account actor)
    {
        SocialRead(c,actor);
        var row=Rows(c,"SELECT role FROM Accounts WHERE id=@p0 AND active=1",actor.Id).FirstOrDefault()??throw new CommunityException(401,"账户已停用，请重新登录");
        return Text(row,"role")=="admin";
    }
    private static void Peer(SqliteConnection c,Account actor,string? peer,bool send=false)
    {
        if(string.IsNullOrWhiteSpace(peer)||peer==actor.Id||peer.Length>64)throw new ArgumentException("请选择其他账户进行私聊");
        var row=Rows(c,"SELECT active FROM Accounts WHERE id=@p0",peer).FirstOrDefault()??throw new CommunityException(404,"聊天账户不存在");
        if(send&&Text(row,"active")!="1")throw new CommunityException(403,"对方账户已停用，暂不能发送消息");
    }
    private static void Own(bool admin,string author,Account actor)
    {if(!admin&&author!=actor.Id)throw new CommunityException(403,"只能删除自己发布的内容");}
    public List<CommunityAccount> Contacts(Account actor,string? query)=>store.Read(c=>
    {
        Actor(c,actor);query=(query??"").Trim();if(query.Length>64)throw new ArgumentException("搜索内容最多 64 字");
        return ContactRows(c,actor,query,false);
    });
    private const string PostSql="""
        SELECT p.*,a.name authorName,
        (SELECT COUNT(*) FROM CommunityLikes l WHERE l.postId=p.id) likeCount,
        CASE WHEN p.kind='SUPPLY' THEN 0 ELSE (SELECT COUNT(*) FROM CommunityComments x WHERE x.postId=p.id AND x.deletedAt IS NULL AND x.moderationStatus='APPROVED') END commentCount,
        EXISTS(SELECT 1 FROM CommunityLikes l WHERE l.postId=p.id AND l.accountId=@p0) liked
        ,EXISTS(SELECT 1 FROM CommunityFollows f WHERE f.accountId=@p0 AND f.peerId=p.authorId) following
        ,COALESCE((SELECT remark FROM CommunityFollows f WHERE f.accountId=@p0 AND f.peerId=p.authorId),'') authorRemark,a.identityKind,a.identityStatus,a.communityCredit
        FROM CommunityPosts p JOIN Accounts a ON a.id=p.authorId
        WHERE p.deletedAt IS NULL AND a.active=1 AND (p.moderationStatus='APPROVED' OR p.authorId=@p0) AND (p.shelfStatus='ACTIVE' OR p.authorId=@p0)
        """;
    private static CommunityPost Post(Dictionary<string,object?> r,Account a,bool admin)=>new(Number(r,"id"),Text(r,"authorId"),Text(r,"authorName"),Text(r,"title"),Text(r,"content"),Text(r,"createdAt"),(int)Number(r,"likeCount"),(int)Number(r,"commentCount"),Number(r,"liked")==1,admin||Text(r,"authorId")==a.Id,Text(r,"kind"),ReadJson<PublishLocation>(Text(r,"locationJson"))?.City??"历史位置未核验",Text(r,"kind")=="SQUARE"?"":ReadJson<PublishLocation>(Text(r,"locationJson"))?.Area??"",ReadJson<List<CommunityMedia>>(Text(r,"mediaJson"))??[],ReadJson<SupplyCard>(Text(r,"supplyJson")),Text(r,"refreshedAt"),Number(r,"following")==1,Text(r,"authorRemark"),Text(r,"identityKind"),Text(r,"identityStatus")=="APPROVED",Text(r,"moderationStatus"),ReadJson<ServiceCard>(Text(r,"serviceJson")),Text(r,"communityCredit"),Text(r,"shelfStatus"),Number(r,"pinned")==1,ReadJson<PublishLocation>(Text(r,"locationJson"))?.Area??"",Text(r,"locationJson").Length>0);
    private static CommunityPost FindPost(SqliteConnection c,Account actor,bool admin,long id)
    {var row=Rows(c,PostSql+" AND p.id=@p1",actor.Id,id).FirstOrDefault()??throw new CommunityException(404,"帖子不存在或已删除");return Post(row,actor,admin);}
    public PostPage Posts(Account actor,long before=0)=>store.Read(c=>
    {
        var admin=Actor(c,actor);if(before<0)throw new ArgumentException("分页标识无效");
        var rows=Rows(c,PostSql+" AND p.shelfStatus='ACTIVE' AND (@p1=0 OR p.id<@p1) ORDER BY p.id DESC LIMIT 21",actor.Id,before);
        var items=rows.Take(20).Select(r=>Post(r,actor,admin)).ToList();return new PostPage(items,rows.Count>20?items[^1].Id:null);
    });
    public CommunityPost GetPost(Account actor,long id)=>store.Read(c=>FindPost(c,actor,Actor(c,actor),id));
    public CommunityPost Publish(Account actor,PublishPostRequest r)=>SavePost(actor,r,null);
    public void DeletePost(Account actor,long id)=>store.Write(c=>
    {
        var admin=Actor(c,actor);var post=FindPost(c,actor,admin,id);Own(admin,post.AuthorId,actor);
        Exec(c,"UPDATE CommunityPosts SET deletedAt=@p0 WHERE id=@p1",Now,id);Log(c,actor,"POST_DELETE",id);return 0;
    });
    public CommunityPost Like(Account actor,long id,bool liked)=>store.Write(c=>
    {
        SocialWrite(c,actor);var admin=Actor(c,actor);if(FindPost(c,actor,admin,id).ModerationStatus!="APPROVED")throw new CommunityException(403,"内容通过审核后才可互动");
        if(liked)Exec(c,"INSERT OR IGNORE INTO CommunityLikes VALUES(@p0,@p1)",id,actor.Id);else Exec(c,"DELETE FROM CommunityLikes WHERE postId=@p0 AND accountId=@p1",id,actor.Id);
        return FindPost(c,actor,admin,id);
    });
    private static CommunityComment Comment(Dictionary<string,object?> r,Account actor,bool admin)=>new(Number(r,"id"),Number(r,"postId"),Text(r,"authorId"),Text(r,"authorName"),Text(r,"content"),Text(r,"createdAt"),admin||Text(r,"authorId")==actor.Id,Text(r,"moderationStatus"),Text(r,"identityKind"),Text(r,"communityCredit"),Text(r,"identityStatus")=="APPROVED");
    public CommentPage Comments(Account actor,long postId,long before=0)=>store.Read(c=>
    {
        var admin=Actor(c,actor);if(FindPost(c,actor,admin,postId).Kind=="SUPPLY")return new CommentPage([],null);if(before<0)throw new ArgumentException("分页标识无效");
        var rows=Rows(c,"SELECT x.*,a.name authorName,a.identityKind,a.identityStatus,a.communityCredit FROM CommunityComments x JOIN Accounts a ON a.id=x.authorId WHERE postId=@p0 AND x.deletedAt IS NULL AND (x.moderationStatus='APPROVED' OR x.authorId=@p2) AND (@p1=0 OR x.id<@p1) ORDER BY x.id DESC LIMIT 31",postId,before,actor.Id);
        var items=rows.Take(30).Select(r=>Comment(r,actor,admin)).ToList();return new CommentPage(items,rows.Count>30?items[^1].Id:null);
    });
    public CommunityComment AddComment(Account actor,long postId,PublishCommentRequest r)
    {
        string content=Input(r.Content,1000,"评论"),nonce=Nonce(r.ClientId);
        return store.Write(c=>
        {
            SocialWrite(c,actor);var admin=Actor(c,actor);var post=FindPost(c,actor,admin,postId);if(post.Kind=="SUPPLY")throw new CommunityException(403,"供需卡通过私信联系，不开放评论");if(post.ModerationStatus!="APPROVED")throw new CommunityException(403,"内容通过审核后才能评论");
            var old=Rows(c,"SELECT x.*,a.name authorName,a.identityKind,a.identityStatus,a.communityCredit FROM CommunityComments x JOIN Accounts a ON a.id=x.authorId WHERE authorId=@p0 AND clientId=@p1",actor.Id,nonce).FirstOrDefault();
            if(old is not null){if(Number(old,"postId")!=postId||Text(old,"content")!=content)throw new CommunityException(409,"重复请求的内容不同");if(Text(old,"deletedAt").Length>0)throw new CommunityException(404,"评论已删除");return Comment(old,actor,admin);}
            Exec(c,"INSERT INTO CommunityComments(postId,authorId,content,clientId,createdAt) VALUES(@p0,@p1,@p2,@p3,@p4)",postId,actor.Id,content,nonce,Now);
            long id=Number(Rows(c,"SELECT last_insert_rowid() id")[0],"id");Queue(c,"COMMENT",id,actor.Id,content,[],admin);return Comment(Rows(c,"SELECT x.*,a.name authorName,a.identityKind,a.identityStatus,a.communityCredit FROM CommunityComments x JOIN Accounts a ON a.id=x.authorId WHERE x.id=@p0",id)[0],actor,admin);
        });
    }
    public void DeleteComment(Account actor,long id)=>store.Write(c=>
    {
        var admin=Actor(c,actor);var row=Rows(c,"SELECT * FROM CommunityComments WHERE id=@p0 AND deletedAt IS NULL",id).FirstOrDefault()??throw new CommunityException(404,"评论不存在或已删除");
        Own(admin,Text(row,"authorId"),actor);Exec(c,"UPDATE CommunityComments SET deletedAt=@p0 WHERE id=@p1",Now,id);Log(c,actor,"COMMENT_DELETE",id);return 0;
    });
    private const string Pair="((senderId=@p0 AND recipientId=@p1) OR (senderId=@p1 AND recipientId=@p0))";
    private static ChatMessage Message(Dictionary<string,object?> r)=>new(Number(r,"id"),Text(r,"senderId"),Text(r,"senderName"),Text(r,"recipientId"),Text(r,"content"),Text(r,"createdAt"),Text(r,"clientId"),Text(r,"kind"),ReadJson<CommunityMedia>(Text(r,"mediaJson")),Text(r,"moderationStatus"),Text(r,"exchangeId"),Number(r,"sequence"));
    private static void Deliver(SqliteConnection c,Account actor)=>Exec(c,"INSERT OR IGNORE INTO ChatDeliveries(accountId,messageId) SELECT @p0,id FROM ChatMessages WHERE (senderId=@p0 OR recipientId=@p0) AND createdAt>@p1 AND id>COALESCE((SELECT throughId FROM AccountChatClears WHERE accountId=@p0),0) AND (moderationStatus='APPROVED' OR senderId=@p0) ORDER BY id",actor.Id,ChatCutoff);
    public MessagePage Messages(Account actor,string peer,long after=0,long before=0)=>store.Write(c=>
    {
        Actor(c,actor);Peer(c,actor,peer);if(after<0||before<0||(after>0&&before>0))throw new ArgumentException("分页标识无效");
        Deliver(c,actor);var sql="SELECT m.*,a.name senderName,d.sequence FROM ChatMessages m JOIN Accounts a ON a.id=m.senderId JOIN ChatDeliveries d ON d.messageId=m.id AND d.accountId=@p0 WHERE "+Pair+" AND m.createdAt>@p4 AND m.id>COALESCE((SELECT throughId FROM AccountChatClears WHERE accountId=@p0),0) AND (m.moderationStatus='APPROVED' OR m.senderId=@p0) AND (@p2=0 OR d.sequence>@p2) AND (@p3=0 OR d.sequence<@p3) ORDER BY d.sequence "+(after>0?"ASC":"DESC")+" LIMIT 51";
        var rows=Rows(c,sql,actor.Id,peer,after,before,ChatCutoff);var items=rows.Take(50).Select(Message).ToList();if(after==0)items.Reverse();return new MessagePage(items,rows.Count>50);
    });
    public ChatMessage Send(Account actor,SendMessageRequest r)
    {
        string kind=r.Kind??"TEXT",content=kind=="TEXT"?Input(r.Content,2000,"消息"):(r.Content??"").Trim(),nonce=Nonce(r.ClientId);
        if(kind is not ("TEXT" or "IMAGE" or "AUDIO" or "VIDEO")||content.Length>2000)throw new ArgumentException("消息类型或内容无效");
        return store.Write(c=>
        {
            SocialWrite(c,actor,r.PeerId);bool admin=Actor(c,actor);Peer(c,actor,r.PeerId,true);
            if(kind=="TEXT"&&!string.IsNullOrEmpty(r.MediaId))throw new ArgumentException("文字消息不能附带媒体编号");
            var old=Rows(c,"SELECT m.*,a.name senderName FROM ChatMessages m JOIN Accounts a ON a.id=m.senderId WHERE senderId=@p0 AND clientId=@p1",actor.Id,nonce).FirstOrDefault();
            if(old is not null){if(Text(old,"recipientId")!=r.PeerId||Text(old,"content")!=content||Text(old,"kind")!=kind||ReadJson<CommunityMedia>(Text(old,"mediaJson"))?.Id!=(kind=="TEXT"?null:r.MediaId))throw new CommunityException(409,"重复请求的内容不同");return Message(old);}
            var attachment=kind=="TEXT"?null:media.ForChat(c,actor,r.MediaId,kind);
            var now=Now;Exec(c,"INSERT INTO ChatMessages(senderId,recipientId,content,clientId,createdAt,kind,mediaJson) VALUES(@p0,@p1,@p2,@p3,@p4,@p5,@p6)",actor.Id,r.PeerId,content,nonce,now,kind,attachment is null?"":Json(attachment));
            long id=Number(Rows(c,"SELECT last_insert_rowid() id")[0],"id");Queue(c,"MESSAGE",id,actor.Id,content,attachment is null?[]:[attachment],admin);return Message(Rows(c,"SELECT m.*,a.name senderName FROM ChatMessages m JOIN Accounts a ON a.id=m.senderId WHERE m.id=@p0",id)[0]);
        });
    }
    public List<Conversation> Conversations(Account actor)=>store.Read(c=>
    {
        Actor(c,actor);
        return Rows(c,"""
        SELECT a.id peerId,a.name peerName,a.username peerUsername,a.identityKind,a.identityStatus,a.communityCredit,CASE m.kind WHEN 'IMAGE' THEN '[图片]' WHEN 'AUDIO' THEN '[语音]' WHEN 'VIDEO' THEN '[视频]' ELSE m.content END lastContent,m.createdAt updatedAt,
        COALESCE((SELECT remark FROM CommunityFollows f WHERE f.accountId=@p0 AND f.peerId=a.id),'') remark,
        EXISTS(SELECT 1 FROM CommunityFollows f WHERE f.accountId=@p0 AND f.peerId=a.id) following,
        (SELECT COUNT(*) FROM ChatMessages n WHERE n.senderId=a.id AND n.recipientId=@p0 AND n.createdAt>@p1 AND n.moderationStatus='APPROVED' AND n.id>COALESCE((SELECT throughId FROM AccountChatClears WHERE accountId=@p0),0) AND NOT EXISTS(SELECT 1 FROM ChatReadMessages r WHERE r.accountId=@p0 AND r.messageId=n.id)) unreadCount
        FROM ChatMessages m JOIN Accounts a ON a.id=CASE WHEN m.senderId=@p0 THEN m.recipientId ELSE m.senderId END
        WHERE (m.senderId=@p0 OR m.recipientId=@p0) AND m.createdAt>@p1 AND m.id>COALESCE((SELECT throughId FROM AccountChatClears WHERE accountId=@p0),0) AND (m.moderationStatus='APPROVED' OR m.senderId=@p0) AND m.id=(SELECT MAX(n.id) FROM ChatMessages n WHERE ((n.senderId=@p0 AND n.recipientId=a.id) OR (n.senderId=a.id AND n.recipientId=@p0)) AND n.createdAt>@p1 AND n.id>COALESCE((SELECT throughId FROM AccountChatClears WHERE accountId=@p0),0) AND (n.moderationStatus='APPROVED' OR n.senderId=@p0))
        ORDER BY m.id DESC LIMIT 100
        """,actor.Id,ChatCutoff).Select(r=>new Conversation(Text(r,"peerId"),Text(r,"peerName"),Text(r,"peerUsername"),Text(r,"lastContent"),Text(r,"updatedAt"),(int)Number(r,"unreadCount"),Text(r,"remark"),Number(r,"following")==1,Text(r,"identityKind"),Text(r,"identityStatus")=="APPROVED",Text(r,"communityCredit"))).ToList();
    });
    public void MarkRead(Account actor,MarkReadRequest r)=>store.Write(c=>
    {
        Actor(c,actor);Peer(c,actor,r.PeerId);
        if(r.MessageId<=0||Rows(c,"SELECT id FROM ChatMessages WHERE "+Pair+" AND id=@p2",actor.Id,r.PeerId,r.MessageId).Count==0)throw new ArgumentException("已读消息标识无效");
        Exec(c,"INSERT OR IGNORE INTO ChatReadMessages(accountId,messageId) SELECT @p0,id FROM ChatMessages WHERE senderId=@p1 AND recipientId=@p0 AND moderationStatus='APPROVED' AND id<=@p2 AND id>COALESCE((SELECT throughId FROM AccountChatClears WHERE accountId=@p0),0)",actor.Id,r.PeerId,r.MessageId);Exec(c,"INSERT INTO ChatReads(accountId,peerId,lastId) VALUES(@p0,@p1,@p2) ON CONFLICT(accountId,peerId) DO UPDATE SET lastId=MAX(ChatReads.lastId,excluded.lastId)",actor.Id,r.PeerId,r.MessageId);return 0;
    });
    private static void Log(SqliteConnection c,Account actor,string action,long id)=>Exec(c,"INSERT INTO Audit VALUES(@p0,@p1,@p2,@p3,'',@p4)",Guid.NewGuid().ToString("N"),actor.Id,action,id.ToString(),Now);
}