STHApplication

using System.Security.Cryptography;
using System.Text;
using System.Text.Json;
using System.Text.RegularExpressions;
using JiDangJia.Contracts;
using Microsoft.Data.Sqlite;

namespace JiDangJia.Messaging;

public sealed record ChatStorageSize(long Messages,long Media,long Bytes,long Pending);
public sealed record LegacyChatPosition(long After,long Before,bool Complete);

// This is a chat-only database in the application's private data directory.
// No accounting database, password or server token is stored here.
public sealed class LocalChatStore
{
    private static readonly JsonSerializerOptions Json=new(JsonSerializerDefaults.Web);
    private readonly object gate=new();
    private readonly string database,mediaDirectory;
    private readonly TimeProvider clock;
    private bool active=true;
    public string AccountId{get;}
    public string Server{get;}
    public string DirectoryPath{get;}

    public LocalChatStore(string appDataDirectory,string accountId,string server,TimeProvider? clock=null,string? archiveServer=null)
    {
        this.clock=clock??TimeProvider.System;
        Id(accountId);AccountId=accountId;
        if(!Uri.TryCreate(server,UriKind.Absolute,out var uri)||uri.Scheme is not ("http" or "https")||uri.UserInfo.Length>0)throw new ArgumentException("服务器地址无效");
        Server=uri.AbsoluteUri.TrimEnd('/');
        // A trusted deployment address change can reuse its original archive key.
        // The transport still uses Server; this alias is never a request destination.
        string archive=Server;
        if(archiveServer is not null)
        {
            if(!Uri.TryCreate(archiveServer,UriKind.Absolute,out var archiveUri)||archiveUri.Scheme is not ("http" or "https")||archiveUri.UserInfo.Length>0)throw new ArgumentException("聊天存储地址标识无效");
            archive=archiveUri.AbsoluteUri.TrimEnd('/');
        }
        string hash=Convert.ToHexString(SHA256.HashData(Encoding.UTF8.GetBytes(archive)))[..24];
        DirectoryPath=Path.Combine(Path.GetFullPath(appDataDirectory),"accounts",accountId,"chat-"+hash);
        mediaDirectory=Path.Combine(DirectoryPath,"media");database=Path.Combine(DirectoryPath,"messages.db");
        Directory.CreateDirectory(mediaDirectory);
        Write(c=>
        {
            Exec(c,"""
                CREATE TABLE IF NOT EXISTS Messages(id INTEGER PRIMARY KEY,peerId TEXT NOT NULL,senderId TEXT NOT NULL,createdAt TEXT NOT NULL,json TEXT NOT NULL,isRead INTEGER NOT NULL);
                CREATE INDEX IF NOT EXISTS idx_chat_peer ON Messages(peerId,id);
                CREATE TABLE IF NOT EXISTS Peers(id TEXT PRIMARY KEY,json TEXT NOT NULL);
                CREATE TABLE IF NOT EXISTS State(name TEXT PRIMARY KEY,value TEXT NOT NULL);
                CREATE TABLE IF NOT EXISTS PendingReads(peerId TEXT PRIMARY KEY,messageId INTEGER NOT NULL);
                CREATE TABLE IF NOT EXISTS Media(id TEXT PRIMARY KEY,json TEXT NOT NULL,state INTEGER NOT NULL DEFAULT 0,retryAt TEXT NOT NULL DEFAULT '');
                CREATE TABLE IF NOT EXISTS MessageMedia(messageId INTEGER NOT NULL REFERENCES Messages(id) ON DELETE CASCADE,mediaId TEXT NOT NULL,PRIMARY KEY(messageId,mediaId));
                """);
            string previous=GetState(c,"transport-server");
            if(previous.Length>0&&previous!=Server||previous.Length==0&&archive!=Server)
                Exec(c,"DELETE FROM State WHERE name='cursor' OR name GLOB 'legacy-*'");
            SetState(c,"transport-server",Server);return 0;
        });
    }
    public void Deactivate(){lock(gate)active=false;}
    private void Check(){if(!active)throw new OperationCanceledException("聊天账户已切换");}
    private static void Id(string value){if(!Regex.IsMatch(value,"^[a-f0-9]{32}$"))throw new ArgumentException("账户或媒体编号无效");}
    private string MediaPath(string id){Id(id);return Path.Combine(mediaDirectory,id+".media");}
    private SqliteConnection Open()
    {
        Check();var c=new SqliteConnection(new SqliteConnectionStringBuilder{DataSource=database,Pooling=false}.ToString());c.Open();Exec(c,"PRAGMA foreign_keys=ON; PRAGMA secure_delete=ON; PRAGMA busy_timeout=5000;");return c;
    }
    private T Read<T>(Func<SqliteConnection,T> read){lock(gate){using var c=Open();return read(c);}}
    private T Write<T>(Func<SqliteConnection,T> write)=>Read(c=>
    {
        Exec(c,"BEGIN IMMEDIATE");try{var value=write(c);Exec(c,"COMMIT");return value;}catch{Exec(c,"ROLLBACK");throw;}
    });
    private static SqliteCommand Command(SqliteConnection c,string sql,params object?[] values)
    {
        var command=c.CreateCommand();command.CommandText=sql;for(int i=0;i<values.Length;i++)command.Parameters.AddWithValue("@p"+i,values[i]??DBNull.Value);return command;
    }
    private static int Exec(SqliteConnection c,string sql,params object?[] values){using var cmd=Command(c,sql,values);return cmd.ExecuteNonQuery();}
    private static List<T> Query<T>(SqliteConnection c,string sql,Func<SqliteDataReader,T> read,params object?[] values)
    {
        using var cmd=Command(c,sql,values);using var reader=cmd.ExecuteReader();List<T> result=[];while(reader.Read())result.Add(read(reader));return result;
    }
    private static T Parse<T>(string json)=>JsonSerializer.Deserialize<T>(json,Json)??throw new IOException("聊天记录无效");
    private static string Serialize<T>(T value)=>JsonSerializer.Serialize(value,Json);
    private static string GetState(SqliteConnection c,string name)=>Query(c,"SELECT value FROM State WHERE name=@p0",r=>r.GetString(0),name).FirstOrDefault()??"";
    private static void SetState(SqliteConnection c,string name,object value)=>Exec(c,"INSERT INTO State VALUES(@p0,@p1) ON CONFLICT(name) DO UPDATE SET value=excluded.value",name,value.ToString());
    public long Cursor=>Read(c=>long.TryParse(GetState(c,"cursor"),out var value)?value:0);
    public string ClearedAt=>Read(c=>GetState(c,"clearedAt"));
    public string LegacyNextPeer
    {
        get=>Read(c=>GetState(c,"legacy-next-peer"));
        set=>Write(c=>{SetState(c,"legacy-next-peer",value);return 0;});
    }
    public LegacyChatPosition LegacyPosition(string peer)=>Read(c=>new LegacyChatPosition(
        long.TryParse(GetState(c,"legacy-after-"+peer),out var after)?after:0,
        long.TryParse(GetState(c,"legacy-before-"+peer),out var before)?before:0,
        GetState(c,"legacy-complete-"+peer)=="1"));
    public void SaveLegacy(MessagePage page,Conversation conversation,bool older)=>Write(c=>
    {
        var peer=new CommunityAccount(conversation.PeerId,conversation.PeerUsername,conversation.PeerName,conversation.Following,conversation.Remark,conversation.IdentityKind,conversation.Verified,conversation.CreditLevel);
        foreach(var message in page.Items)Save(c,message,peer,conversation.UnreadCount==0);
        long previous=long.TryParse(GetState(c,"legacy-after-"+peer.Id),out var value)?value:0;
        SetState(c,"legacy-after-"+peer.Id,Math.Max(previous,page.Items.Select(m=>m.Sequence).DefaultIfEmpty().Max()));
        if(older||previous==0)
        {
            SetState(c,"legacy-before-"+peer.Id,page.Items.Select(m=>m.Sequence).DefaultIfEmpty().Min());
            SetState(c,"legacy-complete-"+peer.Id,page.HasMore?0:1);
        }
        // Older servers expose only a conversation unread count. Keep the newest
        // incoming messages unread, preserving any reads already made on this device.
        Exec(c,"UPDATE Messages SET isRead=1 WHERE peerId=@p0 AND senderId=@p0 AND id NOT IN (SELECT id FROM Messages WHERE peerId=@p0 AND senderId=@p0 ORDER BY id DESC LIMIT @p1)",peer.Id,Math.Max(0,conversation.UnreadCount));
        return 0;
    });

    private void Save(SqliteConnection c,ChatMessage message,CommunityAccount peer,bool read)
    {
        Id(peer.Id);
        if(message.Id<=0||peer.Id==AccountId||(message.SenderId!=AccountId||message.RecipientId!=peer.Id)&&(message.RecipientId!=AccountId||message.SenderId!=peer.Id))throw new IOException("消息不属于当前账户");
        Exec(c,"INSERT INTO Peers VALUES(@p0,@p1) ON CONFLICT(id) DO UPDATE SET json=excluded.json",peer.Id,Serialize(peer));
        Exec(c,"INSERT INTO Messages VALUES(@p0,@p1,@p2,@p3,@p4,@p5) ON CONFLICT(id) DO UPDATE SET json=excluded.json,isRead=MAX(Messages.isRead,excluded.isRead)",message.Id,peer.Id,message.SenderId,message.CreatedAt,Serialize(message),read||message.SenderId==AccountId?1:0);
        if(message.Media is {} media)
        {
            Id(media.Id);_=ChatRetention.MediaDays(media.Kind);
            Exec(c,"INSERT INTO Media(id,json) VALUES(@p0,@p1) ON CONFLICT(id) DO UPDATE SET json=excluded.json",media.Id,Serialize(media));
            Exec(c,"INSERT OR IGNORE INTO MessageMedia VALUES(@p0,@p1)",message.Id,media.Id);
        }
    }
    public void SaveSent(ChatMessage message,CommunityAccount peer)=>Write(c=>{Save(c,message,peer,true);return 0;});
    public void UpdateProfiles(List<Conversation> conversations)=>Write(c=>
    {
        foreach(var row in conversations)
        {
            Id(row.PeerId);var peer=new CommunityAccount(row.PeerId,row.PeerUsername,row.PeerName,row.Following,row.Remark,row.IdentityKind,row.Verified,row.CreditLevel);
            Exec(c,"INSERT INTO Peers VALUES(@p0,@p1) ON CONFLICT(id) DO UPDATE SET json=excluded.json",peer.Id,Serialize(peer));
        }
        return 0;
    });
    public void Merge(ChatSyncPage page)=>Write(c=>
    {
        long previous=long.TryParse(GetState(c,"cursor"),out var value)?value:0;
        if(page.Cursor<previous||page.Items.Any(i=>i.Message.Sequence<=previous||i.Message.Sequence>page.Cursor))throw new IOException("聊天同步顺序无效");
        if(page.ClearedAt.Length>0&&page.ClearedAt!=GetState(c,"clearedAt"))
        {
            Exec(c,"DELETE FROM Messages WHERE id<=@p0 OR julianday(createdAt)<=julianday(@p1)",page.ClearedThroughId,page.ClearedAt);
            Exec(c,"DELETE FROM PendingReads");SetState(c,"clearedAt",page.ClearedAt);RemoveUnreferencedMedia(c);
        }
        foreach(var item in page.Items)Save(c,item.Message,item.Peer,item.Read);
        foreach(var read in page.ReadThrough)
        {
            Exec(c,"UPDATE Messages SET isRead=1 WHERE peerId=@p0 AND id<=@p1",read.PeerId,read.MessageId);
            Exec(c,"DELETE FROM PendingReads WHERE peerId=@p0 AND messageId<=@p1",read.PeerId,read.MessageId);
        }
        // Persist the cursor only in the transaction that saved every message in this page.
        SetState(c,"cursor",page.Cursor);return 0;
    });
    private void RemoveUnreferencedMedia(SqliteConnection c)
    {
        foreach(string id in Query(c,"SELECT id FROM Media WHERE NOT EXISTS(SELECT 1 FROM MessageMedia mm WHERE mm.mediaId=Media.id)",r=>r.GetString(0)))
        {File.Delete(MediaPath(id));Exec(c,"DELETE FROM Media WHERE id=@p0",id);}
    }
    public List<ChatMessage> Messages(string peerId,long before=0,long after=0,int limit=50)=>Read(c=>
    {
        Id(peerId);limit=Math.Clamp(limit,1,500);
        var rows=Query(c,"SELECT json FROM Messages WHERE peerId=@p0 AND (@p1=0 OR id<@p1) AND (@p2=0 OR id>@p2) ORDER BY id "+(after>0?"ASC":"DESC")+" LIMIT @p3",r=>Parse<ChatMessage>(r.GetString(0)),peerId,before,after,limit);
        if(after==0)rows.Reverse();return rows;
    });
    public bool HasEarlier(string peerId,long before)=>Read(c=>Query(c,"SELECT 1 FROM Messages WHERE peerId=@p0 AND id<@p1 LIMIT 1",r=>r.GetInt32(0),peerId,before).Count>0);
    public List<Conversation> Conversations()=>Read(c=>Query(c,"""
        SELECT p.json,m.json,(SELECT COUNT(*) FROM Messages n WHERE n.peerId=m.peerId AND n.senderId<>@p0 AND n.isRead=0)
        FROM Messages m JOIN Peers p ON p.id=m.peerId WHERE m.id=(SELECT MAX(id) FROM Messages n WHERE n.peerId=m.peerId) ORDER BY m.id DESC
        """,r=>
        {
            var peer=Parse<CommunityAccount>(r.GetString(0));var message=Parse<ChatMessage>(r.GetString(1));
            string content=message.Kind switch{"IMAGE"=>"[图片]","AUDIO"=>"[语音]","VIDEO"=>"[视频]",_=>message.Content};
            return new Conversation(peer.Id,peer.Name,peer.Username,content,message.CreatedAt,r.GetInt32(2),peer.Remark,peer.Following,peer.IdentityKind,peer.Verified,peer.CreditLevel);
        },AccountId));
    public void MarkRead(string peerId,long messageId)=>Write(c=>
    {
        Id(peerId);if(messageId<=0)return 0;
        Exec(c,"UPDATE Messages SET isRead=1 WHERE peerId=@p0 AND id<=@p1",peerId,messageId);
        Exec(c,"INSERT INTO PendingReads VALUES(@p0,@p1) ON CONFLICT(peerId) DO UPDATE SET messageId=MAX(PendingReads.messageId,excluded.messageId)",peerId,messageId);return 0;
    });
    public List<MarkReadRequest> PendingReads()=>Read(c=>Query(c,"SELECT peerId,messageId FROM PendingReads",r=>new MarkReadRequest(r.GetString(0),r.GetInt64(1))));
    public void ConfirmRead(MarkReadRequest read)=>Write(c=>Exec(c,"DELETE FROM PendingReads WHERE peerId=@p0 AND messageId<=@p1",read.PeerId,read.MessageId));
    public List<CommunityMedia> PendingMedia(int limit=10)=>Read(c=>Query(c,"SELECT json FROM Media WHERE state=0 AND (retryAt='' OR retryAt<=@p0) ORDER BY rowid DESC LIMIT @p1",r=>Parse<CommunityMedia>(r.GetString(0)),clock.GetUtcNow().UtcDateTime.ToString("O"),Math.Clamp(limit,1,100)));
    public bool KnowsMedia(string id)=>Read(c=>Query(c,"SELECT 1 FROM Media WHERE id=@p0",r=>r.GetInt32(0),id).Count>0);
    public byte[]? ReadMedia(string id)=>Read(c=>
    {
        string path=MediaPath(id);if(!File.Exists(path))return null;
        var bytes=File.ReadAllBytes(path);Exec(c,"UPDATE Media SET state=1,retryAt='' WHERE id=@p0",id);return bytes;
    });
    public void SaveMedia(string id,byte[] bytes)=>Write(c=>
    {
        if(bytes.Length==0||bytes.Length>ChatSyncClient.MaxMediaBytes)throw new IOException("私信附件大小无效");
        if(Query(c,"SELECT 1 FROM Media WHERE id=@p0",r=>r.GetInt32(0),id).Count==0)throw new OperationCanceledException("附件已从本机清除");
        string target=MediaPath(id),temp=target+".part";
        try{File.WriteAllBytes(temp,bytes);File.Move(temp,target,true);Exec(c,"UPDATE Media SET state=1,retryAt='' WHERE id=@p0",id);}
        finally{File.Delete(temp);}return 0;
    });
    public void MediaUnavailable(string id)=>Write(c=>Exec(c,"UPDATE Media SET state=2,retryAt='' WHERE id=@p0",id));
    public void RetryMediaLater(string id)=>Write(c=>Exec(c,"UPDATE Media SET retryAt=@p0 WHERE id=@p1 AND state=0",clock.GetUtcNow().AddMinutes(1).UtcDateTime.ToString("O"),id));
    public ChatStorageSize Size()=>Read(c=>new ChatStorageSize(Query(c,"SELECT COUNT(*) FROM Messages",r=>r.GetInt64(0))[0],Query(c,"SELECT COUNT(*) FROM Media WHERE state=1",r=>r.GetInt64(0))[0],Directory.EnumerateFiles(mediaDirectory,"*.media").Sum(f=>new FileInfo(f).Length),Query(c,"SELECT COUNT(*) FROM Media WHERE state=0",r=>r.GetInt64(0))[0]));
}