STHApplication

using System.Globalization;
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 readonly JsonSerializerOptions SocialJson=new(JsonSerializerDefaults.Web);
    private static string Json<T>(T value)=>JsonSerializer.Serialize(value,SocialJson);
    private static T? ReadJson<T>(string value)=>string.IsNullOrWhiteSpace(value)?default:JsonSerializer.Deserialize<T>(value,SocialJson);
    private static void Column(SqliteConnection c,string table,string name,string definition)
    {if(!Rows(c,"PRAGMA table_info("+table+")").Any(r=>Text(r,"name")==name))Exec(c,$"ALTER TABLE {table} ADD COLUMN {name} {definition}");}
    private static void InitializeSocial(SqliteConnection c)
    {
        Column(c,"CommunityPosts","kind","TEXT NOT NULL DEFAULT 'SQUARE'");Column(c,"CommunityPosts","city","TEXT NOT NULL DEFAULT ''");Column(c,"CommunityPosts","address","TEXT NOT NULL DEFAULT ''");
        Column(c,"CommunityPosts","mediaJson","TEXT NOT NULL DEFAULT '[]'");Column(c,"CommunityPosts","supplyJson","TEXT NOT NULL DEFAULT ''");Column(c,"CommunityPosts","fingerprint","TEXT NOT NULL DEFAULT ''");Column(c,"CommunityPosts","refreshedAt","TEXT NOT NULL DEFAULT ''");Column(c,"CommunityPosts","lastBumpedAt","TEXT NOT NULL DEFAULT ''");
        Exec(c,"UPDATE CommunityPosts SET refreshedAt=createdAt WHERE refreshedAt=''");
        Column(c,"ChatMessages","kind","TEXT NOT NULL DEFAULT 'TEXT'");Column(c,"ChatMessages","mediaJson","TEXT NOT NULL DEFAULT ''");
        Column(c,"CommunityPosts","shelfStatus","TEXT NOT NULL DEFAULT 'ACTIVE'");Column(c,"CommunityPosts","pinned","INTEGER NOT NULL DEFAULT 0");
        Column(c,"Accounts","communityCredit","TEXT NOT NULL DEFAULT 'GOOD'");Column(c,"Accounts","communityLabelRevision","INTEGER NOT NULL DEFAULT 1");
        Column(c,"Accounts","communityPublishLimit","INTEGER");
        Column(c,"CommunityPosts","locationJson","TEXT NOT NULL DEFAULT ''");
        // 旧鉴定/联营卡片保留在作者历史中；新的配货代购使用独立字段，避免篡改旧卡语义。
        Exec(c,"UPDATE CommunityPosts SET shelfStatus='OFF' WHERE kind='SUPPLY' AND json_valid(supplyJson) AND json_extract(supplyJson,'$.intent') IN ('APPRAISE','COOPERATE')");
        Exec(c,"""
        CREATE TABLE IF NOT EXISTS CommunityFollows(accountId TEXT NOT NULL,peerId TEXT NOT NULL,remark TEXT NOT NULL DEFAULT '',createdAt TEXT NOT NULL,PRIMARY KEY(accountId,peerId));
        CREATE TABLE IF NOT EXISTS CommunitySettings(id INTEGER PRIMARY KEY CHECK(id=1),revision INTEGER NOT NULL,notice TEXT NOT NULL);
        CREATE TABLE IF NOT EXISTS CommunityPostRequests(accountId TEXT NOT NULL,clientId TEXT NOT NULL,fingerprint TEXT NOT NULL,postId INTEGER NOT NULL,PRIMARY KEY(accountId,clientId));
        CREATE INDEX IF NOT EXISTS idx_social_feed ON CommunityPosts(deletedAt,kind,refreshedAt DESC,id DESC);
        CREATE INDEX IF NOT EXISTS idx_social_fingerprint ON CommunityPosts(authorId,fingerprint,deletedAt);
        """);
        Exec(c,"INSERT OR IGNORE INTO CommunitySettings(id,revision,notice) VALUES(1,1,@p0)",CommunityCatalog.DefaultNotice);
        Column(c,"CommunitySettings","personalSupplyLimit","INTEGER NOT NULL DEFAULT 10");Column(c,"CommunitySettings","enterpriseSupplyLimit","INTEGER NOT NULL DEFAULT 20");
    }
    private static List<CommunityAccount> ContactRows(SqliteConnection c,Account actor,string query,bool followed)
    =>Rows(c,"""
        SELECT a.id,a.username,a.name,a.identityKind,a.identityStatus,a.communityCredit,f.remark,CASE WHEN f.peerId IS NULL THEN 0 ELSE 1 END following FROM Accounts a
        LEFT JOIN CommunityFollows f ON f.peerId=a.id AND f.accountId=@p0
        WHERE a.active=1 AND a.id<>@p0 AND (@p2=0 OR f.peerId IS NOT NULL)
        AND (instr(lower(a.username),lower(@p1))>0 OR instr(a.name,@p1)>0 OR instr(COALESCE(f.remark,''),@p1)>0)
        ORDER BY following DESC,a.name,a.id LIMIT 50
        """,actor.Id,query,followed?1:0).Select(r=>new CommunityAccount(Text(r,"id"),Text(r,"username"),Text(r,"name"),Number(r,"following")==1,Text(r,"remark"),Text(r,"identityKind"),Text(r,"identityStatus")=="APPROVED",Text(r,"communityCredit"))).ToList();
    public List<CommunityAccount> Followed(Account actor,string? q)=>store.Read(c=>{Actor(c,actor);var query=(q??"").Trim();if(query.Length>64)throw new ArgumentException("搜索最多 64 字");return ContactRows(c,actor,query,true);});
    public CommunityAccount Follow(Account actor,string peer,FollowRequest request)=>store.Write(c=>
    {
        Actor(c,actor);Peer(c,actor,peer,true);var remark=(request.Remark??"").Trim();if(remark.Length>60||remark.Any(char.IsControl))throw new ArgumentException("备注最多 60 字，不能含控制字符");
        if(request.Following)Exec(c,"INSERT INTO CommunityFollows VALUES(@p0,@p1,@p2,@p3) ON CONFLICT(accountId,peerId) DO UPDATE SET remark=CASE WHEN @p4=1 THEN excluded.remark ELSE CommunityFollows.remark END",actor.Id,peer,remark,Now,request.Remark is null?0:1);
        else Exec(c,"DELETE FROM CommunityFollows WHERE accountId=@p0 AND peerId=@p1",actor.Id,peer);
        var row=Rows(c,"SELECT a.id,a.username,a.name,a.identityKind,a.identityStatus,a.communityCredit,f.remark,CASE WHEN f.peerId IS NULL THEN 0 ELSE 1 END following FROM Accounts a LEFT JOIN CommunityFollows f ON f.peerId=a.id AND f.accountId=@p0 WHERE a.id=@p1",actor.Id,peer)[0];
        return new CommunityAccount(Text(row,"id"),Text(row,"username"),Text(row,"name"),Number(row,"following")==1,Text(row,"remark"),Text(row,"identityKind"),Text(row,"identityStatus")=="APPROVED",Text(row,"communityCredit"));
    });
    private static CommunitySettings SettingsFrom(Dictionary<string,object?> r)=>new(Number(r,"revision"),Text(r,"notice"),(int)Number(r,"personalSupplyLimit"),(int)Number(r,"enterpriseSupplyLimit"));
    public CommunitySettings Settings(Account actor)=>store.Read(c=>{Actor(c,actor);return SettingsFrom(Rows(c,"SELECT * FROM CommunitySettings WHERE id=1")[0]);});
    public CommunitySettings UpdateSettings(Account actor,UpdateCommunitySettingsRequest request)=>store.Write(c=>
    {
        if(!Actor(c,actor))throw new CommunityException(403,"只有管理员可以修改社群风险提示");var notice=Input(request.Notice,2000,"风险提示");var row=Rows(c,"SELECT * FROM CommunitySettings WHERE id=1")[0];
        if(Number(row,"revision")!=request.ExpectedRevision)throw new CommunityException(409,"提示已被更新，请刷新后再修改");
        var personal=request.PersonalSupplyLimit??(int)Number(row,"personalSupplyLimit");var enterprise=request.EnterpriseSupplyLimit??(int)Number(row,"enterpriseSupplyLimit");
        if(personal is <0 or >100000||enterprise is <0 or >100000)throw new ArgumentException("服务与供需合计数量应为 0–100000 的整数");
        Exec(c,"UPDATE CommunitySettings SET revision=revision+1,notice=@p0,personalSupplyLimit=@p1,enterpriseSupplyLimit=@p2 WHERE id=1",notice,personal,enterprise);Log(c,actor,"COMMUNITY_SETTINGS",1);return SettingsFrom(Rows(c,"SELECT * FROM CommunitySettings WHERE id=1")[0]);
    });
    public PostPage Feed(Account actor,FeedFilter filter)=>store.Read(c=>
    {
        var admin=Actor(c,actor);if(filter.Before<0||filter.Tab is not ("ALL" or "FOLLOW" or "CITY" or "RECOMMEND" or "SUPPLY" or "SERVICE" or "SQUARE" or "MINE"))throw new ArgumentException("社群筛选无效");
        if(new[]{filter.City,filter.Brand,filter.Condition,filter.Query,filter.Intent,filter.Category,filter.Subcategory}.Any(s=>s is null||s.Length>120))throw new ArgumentException("筛选内容过长");
        if(filter.Tab=="CITY"&&string.IsNullOrWhiteSpace(filter.City))throw new ArgumentException("请先填写城市");
        if(filter.MinPrice is <0||filter.MaxPrice is <0||filter.MinPrice>filter.MaxPrice)throw new ArgumentException("价格区间无效");
        if(filter.Intent.Length>0&&!CommunityCatalog.Intents.ContainsKey(filter.Intent)||filter.Category.Length>0&&!CommunityCatalog.Categories.ContainsKey(filter.Category)||filter.Condition.Length>0&&!CommunityCatalog.Conditions.ContainsKey(filter.Condition))throw new ArgumentException("品类筛选无效");
        if(filter.Subcategory.Length>0&&(filter.Category.Length==0||!CommunityCatalog.Subcategories(filter.Category,filter.Intent).ContainsKey(filter.Subcategory)))throw new ArgumentException("细类筛选无效");
        if(filter.Intent=="SOURCING"&&filter.Category.Length>0&&!CommunityCatalog.CategoriesFor(filter.Intent).ContainsKey(filter.Category))throw new ArgumentException("配货代购仅支持贵金属首饰与其他");
        var sql=PostSql;var args=new List<object?>{actor.Id};
        void Where(string clause,object? value){sql+=" AND "+clause.Replace("?","@p"+args.Count);args.Add(value);}
        if(filter.Tab=="FOLLOW")Where("EXISTS(SELECT 1 FROM CommunityFollows f WHERE f.accountId=? AND f.peerId=p.authorId)",actor.Id);
        else if(filter.Tab=="MINE")Where("p.authorId=?",actor.Id);
        else if(filter.Tab is "RECOMMEND" or "SUPPLY" or "SERVICE" or "SQUARE")Where("p.kind=?",filter.Tab);
        if(filter.Tab!="MINE")sql+=" AND p.shelfStatus='ACTIVE'";
        if(!string.IsNullOrEmpty(filter.ServiceCategory)){if(!ServiceCatalog.Categories.ContainsKey(filter.ServiceCategory))throw new ArgumentException("服务类型无效");Where("CASE WHEN json_valid(p.serviceJson) THEN json_extract(p.serviceJson,'$.category') ELSE '' END=?",filter.ServiceCategory);}
        if(filter.City.Trim().Length>0)Where("CASE WHEN substr(trim(p.city),-1)='市' THEN substr(trim(p.city),1,length(trim(p.city))-1) ELSE trim(p.city) END=?",filter.City.Trim().EndsWith('市')?filter.City.Trim()[..^1]:filter.City.Trim());
        foreach(var (key,value) in new[]{("intent",filter.Intent),("category",filter.Category),("subcategory",filter.Subcategory),("fields.brand",filter.Brand),("fields.condition",filter.Condition)})
            if(value.Length>0)Where(key=="fields.brand"?$"instr(lower(CASE WHEN json_valid(p.supplyJson) THEN json_extract(p.supplyJson,'$.{key}') ELSE '' END),lower(?))>0":$"CASE WHEN json_valid(p.supplyJson) THEN json_extract(p.supplyJson,'$.{key}') ELSE '' END=?",value.Trim());
        if(filter.Query.Length>0)Where("instr(lower(p.title||' '||p.content||' '||a.name||' '||p.address),lower(?))>0",filter.Query.Trim());
        if(filter.MinPrice.HasValue||filter.MaxPrice.HasValue)
        {
            sql+=" AND CASE WHEN json_valid(p.supplyJson) THEN COALESCE(json_extract(p.supplyJson,'$.fields.priceMode'),'FIXED')='FIXED' AND length(COALESCE(json_extract(p.supplyJson,'$.fields.price'),''))>0 ELSE 0 END";
            if(filter.MinPrice.HasValue)Where("CAST(json_extract(p.supplyJson,'$.fields.price') AS REAL)>=?",filter.MinPrice.Value);
            if(filter.MaxPrice.HasValue)Where("CAST(json_extract(p.supplyJson,'$.fields.price') AS REAL)<=?",filter.MaxPrice.Value);
        }
        if(filter.Before>0)
        {
            var boundary=Rows(c,"SELECT id,refreshedAt,pinned FROM CommunityPosts WHERE id=@p0 AND deletedAt IS NULL",filter.Before).FirstOrDefault()??throw new ArgumentException("分页标识已失效，请刷新");
            sql+=$" AND (p.pinned<@p{args.Count} OR (p.pinned=@p{args.Count} AND (p.refreshedAt<@p{args.Count+1} OR (p.refreshedAt=@p{args.Count+1} AND p.id<@p{args.Count+2}))))";args.Add(Number(boundary,"pinned"));args.Add(Text(boundary,"refreshedAt"));args.Add(filter.Before);
        }
        var rows=Rows(c,sql+" ORDER BY p.pinned DESC,p.refreshedAt DESC,p.id DESC LIMIT 21",args.ToArray());var items=rows.Take(20).Select(r=>Post(r,actor,admin)).ToList();return new PostPage(items,rows.Count>20?items[^1].Id:null);
    });
    private static SupplyCard ValidateSupply(SupplyCard value)
    {
        if(value is null||value.Intent is null||value.Category is null||value.Subcategory is null||!CommunityCatalog.Intents.ContainsKey(value.Intent)||!CommunityCatalog.Categories.ContainsKey(value.Category)||value.Fields is null||value.Delivery is null)throw new ArgumentException("请选择供需意向与物品大类");
        if(!CommunityCatalog.CategoriesFor(value.Intent).ContainsKey(value.Category))throw new ArgumentException(value.Intent=="OTHER"?"其他供需请使用图文卡片":"配货代购仅支持贵金属首饰与其他");
        var subs=CommunityCatalog.Subcategories(value.Category,value.Intent);if(subs.Count>0&&!subs.ContainsKey(value.Subcategory)||subs.Count==0&&value.Subcategory.Length>0)throw new ArgumentException("请选择有效的物品细类");
        if(value.Intent=="OTHER"){if(value.Delivery.Count>0)throw new ArgumentException("其他供需只需填写图文简介和发布位置");}
        else if(value.Delivery.Count is <1 or >3||value.Delivery.Distinct().Count()!=value.Delivery.Count||value.Delivery.Any(d=>d is null||!CommunityCatalog.Deliveries.ContainsKey(d))||value.Delivery.Contains("COD")&&value.Delivery.Contains("FREE"))throw new ArgumentException("请选择到付或包邮，可同时选择面交");
        var fields=new SortedDictionary<string,string>();var definitions=CommunityCatalog.Fields(value.Category,value.Intent);
        if(value.Fields.Keys.Any(k=>!definitions.Any(d=>d.Key==k)))throw new ArgumentException("卡片包含不属于此品类的字段");
        foreach(var d in definitions)
        {
            var text=(value.Fields.GetValueOrDefault(d.Key)??"").Trim();if(text.Length>300||text.Any(char.IsControl))throw new ArgumentException(d.Label+"最多 300 字");
            if(d.Key=="exchangeRatio"&&value.Fields.GetValueOrDefault("supportsExchange")!="YES")continue;
            var required=d.Required||(d.Key=="sourceCustom"&&value.Fields.GetValueOrDefault("source")=="CUSTOM")||(d.Key=="exchangeRatio"&&value.Fields.GetValueOrDefault("supportsExchange")=="YES");if(required&&text.Length==0)throw new ArgumentException("请填写"+d.Label);if(text.Length==0)continue;
            if(d.Key=="exchangeRatio")
            {
                var pair=text.Replace('：',':').Split(':');if(pair.Length!=2||pair.Any(p=>!decimal.TryParse(p.Trim(),NumberStyles.AllowDecimalPoint,CultureInfo.InvariantCulture,out var part)||part<=0||part>1_000_000_000))throw new ArgumentException("兑换比例应为两个正数，如 1:0.998");
                fields[d.Key]=string.Join(":",pair.Select(p=>decimal.Parse(p.Trim(),CultureInfo.InvariantCulture).ToString("0.###########################",CultureInfo.InvariantCulture)));continue;
            }
            if(d.Kind=="choice"&&!d.Choices!.ContainsKey(text))throw new ArgumentException(d.Label+"选项无效");
            if(d.Kind is "number" or "signed")
            {
                if(!decimal.TryParse(text,NumberStyles.AllowLeadingSign|NumberStyles.AllowDecimalPoint,CultureInfo.InvariantCulture,out var n)||n is <-1_000_000_000 or >1_000_000_000||d.Kind=="number"&&n<0)throw new ArgumentException(d.Label+"数值无效");
                if((d.Key=="weight"&&d.Required||d.Key=="minimumWeight")&&n<=0)throw new ArgumentException(d.Label+"必须大于 0");
                fields[d.Key]=n.ToString("0.####",CultureInfo.InvariantCulture);
            }
            else if(d.Kind=="date"){if(!DateTime.TryParseExact(text,"yyyy-MM-dd",CultureInfo.InvariantCulture,DateTimeStyles.None,out _))throw new ArgumentException(d.Label+"格式应为 yyyy-MM-dd");fields[d.Key]=text;}
            else fields[d.Key]=text;
        }
        if(value.Category=="BULLION"&&fields.GetValueOrDefault("priceMode")=="FIXED"&&decimal.Parse(fields["price"],CultureInfo.InvariantCulture)<0)throw new ArgumentException("固定价格不能为负数");
        if(fields.TryGetValue("minimumWeight",out var minimum)&&fields.TryGetValue("weight",out var weight)&&decimal.Parse(minimum,CultureInfo.InvariantCulture)>decimal.Parse(weight,CultureInfo.InvariantCulture))throw new ArgumentException("起拿克重不能超过总克重");
        return value with{Fields=new Dictionary<string,string>(fields),Delivery=value.Delivery.Order().ToList()};
    }
    private CommunityPost SavePost(Account actor,PublishPostRequest r,long? editing)=>store.Write(c=>
    {
        SocialWrite(c,actor);var admin=Actor(c,actor);if(r.Kind is not ("SQUARE" or "RECOMMEND" or "SUPPLY" or "SERVICE"))throw new ArgumentException("发布类型无效");if(r.Kind=="RECOMMEND"&&!admin)throw new CommunityException(403,"推荐内容由管理员发布");
        var location=LocationFor(c,actor,r,editing);
        string title=Input(r.Title,120,"物品简介 / 标题"),content=Input(r.Content,r.Kind=="RECOMMEND"?int.MaxValue:r.Kind=="SUPPLY"?int.MaxValue:5000,"内容"),city=location.City,address=r.Kind=="SQUARE"?"":location.Area,nonce=Nonce(r.ClientId);
        if(r.Kind=="SUPPLY"&&CommunityText.Length(content)>CommunityText.SupplyLength)throw new ArgumentException("供需简介正文最多 200 个字符");
        if(r.Kind!="SUPPLY"&&r.Supply is not null||r.Kind=="SUPPLY"&&r.Supply is null)throw new ArgumentException("发布类型与供需卡片不匹配");var supply=r.Supply is null?null:ValidateSupply(r.Supply);
        if(r.Kind=="SERVICE"&&r.Service is null||r.Kind!="SERVICE"&&r.Service is not null)throw new ArgumentException("发布类型与服务卡片不匹配");ServiceCard? service=null;
        if(r.Service is { } v){if(!ServiceCatalog.Categories.ContainsKey(v.Category)||v.FeeMin<0||v.FeeMax<v.FeeMin||v.FeeMax>1_000_000_000m)throw new ArgumentException("服务类型或收费区间无效");service=v with{Project=Input(v.Project,120,"服务项目"),Unit=Input(v.Unit,24,"计费单位"),Description=Input(v.Description,2000,"详细说明")};}
        var ids=r.MediaIds??[];int limit=r.Kind=="RECOMMEND"?int.MaxValue:CommunityCatalog.DefaultIcon(supply,service)?0:supply is null?9:CommunityCatalog.PhotoLimit(supply.Category,supply.Intent);if(ids.Count>limit||ids.Distinct().Count()!=ids.Count)throw new ArgumentException(limit==0?"此类卡片使用系统标识，无需上传图片":$"此品类最多 {limit} 张照片，不能重复");
        var attachments=ids.Select(id=>media.Owned(c,actor,id,r.Kind=="RECOMMEND"?"POST":"IMAGE",r.Kind=="RECOMMEND",publicPost:true)).ToList();string mediaJson=Json(attachments),supplyJson=supply is null?"":Json(supply),serviceJson=service is null?"":Json(service);
        var fingerprint=Hash(Json(new{title,content,kind=r.Kind,city,address,mediaIds=ids,supply,service}));
        if(editing.HasValue)
        {
            var current=FindPost(c,actor,admin,editing.Value);if(current.AuthorId!=actor.Id)throw new CommunityException(403,"只能编辑自己发布的内容");if(current.Kind!=r.Kind)throw new ArgumentException("编辑不能更改发布类型");
            if(Rows(c,"SELECT id FROM CommunityPosts WHERE authorId=@p0 AND fingerprint=@p1 AND deletedAt IS NULL AND id<>@p2",actor.Id,fingerprint,editing.Value).Count>0)throw new CommunityException(409,"已有相同卡片，请刷新原卡片");
            Exec(c,"UPDATE CommunityPosts SET title=@p0,content=@p1,city=@p2,address=@p3,mediaJson=@p4,supplyJson=@p5,fingerprint=@p6,serviceJson=@p8,locationJson=@p9 WHERE id=@p7",title,content,city,address,mediaJson,supplyJson,fingerprint,editing.Value,serviceJson,Json(location));Queue(c,"POST",editing.Value,actor.Id,title+"\n"+content+"\n"+supplyJson+"\n"+serviceJson,attachments,admin);return FindPost(c,actor,admin,editing.Value);
        }
        var old=Rows(c,"SELECT postId,fingerprint FROM CommunityPostRequests WHERE accountId=@p0 AND clientId=@p1",actor.Id,nonce).FirstOrDefault();
        if(old is not null){if(Text(old,"fingerprint")!=fingerprint)throw new CommunityException(409,"重复请求的内容不同");return FindPost(c,actor,admin,Number(old,"postId"));}
        var legacy=Rows(c,"SELECT id FROM CommunityPosts WHERE authorId=@p0 AND clientId=@p1",actor.Id,nonce).FirstOrDefault();if(legacy is not null)throw new CommunityException(409,"请求标识已使用");
        var duplicate=r.Kind is "SUPPLY" or "SERVICE"?Rows(c,"SELECT id FROM CommunityPosts WHERE authorId=@p0 AND fingerprint=@p1 AND deletedAt IS NULL AND shelfStatus='ACTIVE'",actor.Id,fingerprint).FirstOrDefault():null;
        long id;if(duplicate is not null)id=Number(duplicate,"id");else
        {
            if(r.Kind is "SUPPLY" or "SERVICE")RequireSupplySlot(c,actor.Id);
            Exec(c,"INSERT INTO CommunityPosts(authorId,title,content,clientId,createdAt,kind,city,address,mediaJson,supplyJson,fingerprint,refreshedAt,serviceJson) VALUES(@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10,@p4,@p11)",actor.Id,title,content,nonce,Now,r.Kind,city,address,mediaJson,supplyJson,fingerprint,serviceJson);id=Number(Rows(c,"SELECT last_insert_rowid() id")[0],"id");Queue(c,"POST",id,actor.Id,title+"\n"+content+"\n"+supplyJson+"\n"+serviceJson,attachments,admin);
        }
        Exec(c,"UPDATE CommunityPosts SET locationJson=@p0 WHERE id=@p1 AND locationJson=''",Json(location),id);
        Exec(c,"INSERT INTO CommunityPostRequests VALUES(@p0,@p1,@p2,@p3)",actor.Id,nonce,fingerprint,id);return FindPost(c,actor,admin,id);
    });
    public CommunityPost EditPost(Account actor,long id,PublishPostRequest request)=>SavePost(actor,request,id);
    public CommunityPost Bump(Account actor,long id)=>store.Write(c=>
    {
        SocialWrite(c,actor);var admin=Actor(c,actor);var post=FindPost(c,actor,admin,id);if(post.AuthorId!=actor.Id||post.Kind is not ("SUPPLY" or "SERVICE")||post.ModerationStatus!="APPROVED"||post.ShelfStatus!="ACTIVE")throw new CommunityException(403,"只能刷新自己正在展示的供需或服务卡片");
        var row=Rows(c,"SELECT lastBumpedAt FROM CommunityPosts WHERE id=@p0",id)[0];if(DateTimeOffset.TryParse(Text(row,"lastBumpedAt"),out var last)&&last>DateTimeOffset.UtcNow.AddMinutes(-1))throw new CommunityException(429,"每张卡片每分钟可刷新一次");
        Exec(c,"UPDATE CommunityPosts SET refreshedAt=@p0,lastBumpedAt=@p0 WHERE id=@p1",Now,id);return FindPost(c,actor,admin,id);
    });
    public RefreshCardsResult BumpAll(Account actor)=>store.Write(c=>
    {
        SocialWrite(c,actor);Actor(c,actor);var now=DateTimeOffset.UtcNow;int refreshed=0,cooling=0,unavailable=0;
        foreach(var row in Rows(c,"SELECT id,moderationStatus,shelfStatus,lastBumpedAt FROM CommunityPosts WHERE authorId=@p0 AND kind IN ('SUPPLY','SERVICE') AND deletedAt IS NULL",actor.Id))
        {
            if(Text(row,"moderationStatus")!="APPROVED"||Text(row,"shelfStatus")!="ACTIVE"){unavailable++;continue;}
            if(DateTimeOffset.TryParse(Text(row,"lastBumpedAt"),out var last)&&last>now.AddMinutes(-1)){cooling++;continue;}
            Exec(c,"UPDATE CommunityPosts SET refreshedAt=@p0,lastBumpedAt=@p0 WHERE id=@p1",now.ToString("O"),Number(row,"id"));refreshed++;
        }
        return new RefreshCardsResult(refreshed,cooling,unavailable);
    });
}