Add WS push events, PWA/FCM scaffolding, and direct messaging MVP

2026-04-04 18:10:25 +03:00
parent cf5460c5c7
commit 32c046233b
40 changed files with 1300 additions and 114 deletions
@@ -328,6 +328,54 @@ public final class DatabaseInitializer {
                ON message_stats (to_login);
                """);

+            // 9) direct_messages
+            st.executeUpdate("""
+                CREATE TABLE IF NOT EXISTS direct_messages (
+                    message_id     TEXT    NOT NULL PRIMARY KEY,
+                    from_login     TEXT    NOT NULL,
+                    to_login       TEXT    NOT NULL,
+                    text           TEXT    NOT NULL,
+                    created_at_ms  INTEGER NOT NULL,
+                    FOREIGN KEY (from_login) REFERENCES solana_users(login),
+                    FOREIGN KEY (to_login) REFERENCES solana_users(login)
+                );
+                """);
+
+            st.executeUpdate("""
+                CREATE INDEX IF NOT EXISTS idx_direct_messages_to_login
+                ON direct_messages (to_login, created_at_ms);
+                """);
+
+            st.executeUpdate("""
+                CREATE INDEX IF NOT EXISTS idx_direct_messages_from_login
+                ON direct_messages (from_login, created_at_ms);
+                """);
+
+            // 10) user_push_tokens
+            st.executeUpdate("""
+                CREATE TABLE IF NOT EXISTS user_push_tokens (
+                    token_id       TEXT    NOT NULL PRIMARY KEY,
+                    login          TEXT    NOT NULL,
+                    session_id     TEXT    NOT NULL,
+                    provider       TEXT    NOT NULL,
+                    token          TEXT    NOT NULL,
+                    platform       TEXT,
+                    user_agent     TEXT,
+                    updated_at_ms  INTEGER NOT NULL,
+                    FOREIGN KEY (login) REFERENCES solana_users(login)
+                );
+                """);
+
+            st.executeUpdate("""
+                CREATE INDEX IF NOT EXISTS idx_user_push_tokens_login
+                ON user_push_tokens (login);
+                """);
+
+            st.executeUpdate("""
+                CREATE INDEX IF NOT EXISTS idx_user_push_tokens_login_session
+                ON user_push_tokens (login, session_id);
+                """);
+
            DatabaseTriggersInstaller.createAllTriggers(st);
        }
    }
@@ -0,0 +1,53 @@
+package shine.db.dao;
+
+import shine.db.SqliteDbController;
+import shine.db.entities.DirectMessageEntry;
+
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+
+public final class DirectMessagesDAO {
+    private static volatile DirectMessagesDAO instance;
+    private final SqliteDbController db = SqliteDbController.getInstance();
+
+    private DirectMessagesDAO() {}
+
+    public static DirectMessagesDAO getInstance() {
+        if (instance == null) {
+            synchronized (DirectMessagesDAO.class) {
+                if (instance == null) instance = new DirectMessagesDAO();
+            }
+        }
+        return instance;
+    }
+
+    public void insert(DirectMessageEntry entry) throws Exception {
+        try (Connection c = db.getConnection()) {
+            String sql = """
+                INSERT INTO direct_messages (
+                    message_id, from_login, to_login, text, created_at_ms
+                ) VALUES (?, ?, ?, ?, ?)
+                """;
+            try (PreparedStatement ps = c.prepareStatement(sql)) {
+                ps.setString(1, entry.getMessageId());
+                ps.setString(2, entry.getFromLogin());
+                ps.setString(3, entry.getToLogin());
+                ps.setString(4, entry.getText());
+                ps.setLong(5, entry.getCreatedAtMs());
+                ps.executeUpdate();
+            }
+        }
+    }
+
+    public boolean existsFromTo(String fromLogin, String toLogin) throws Exception {
+        try (Connection c = db.getConnection()) {
+            String sql = "SELECT 1 FROM direct_messages WHERE from_login = ? AND to_login = ? LIMIT 1";
+            try (PreparedStatement ps = c.prepareStatement(sql)) {
+                ps.setString(1, fromLogin);
+                ps.setString(2, toLogin);
+                return ps.executeQuery().next();
+            }
+        }
+    }
+
+}
@@ -0,0 +1,116 @@
+package shine.db.dao;
+
+import shine.db.SqliteDbController;
+import shine.db.entities.PushTokenEntry;
+
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.util.ArrayList;
+import java.util.List;
+
+public final class PushTokensDAO {
+    private static volatile PushTokensDAO instance;
+    private final SqliteDbController db = SqliteDbController.getInstance();
+
+    private PushTokensDAO() {}
+
+    public static PushTokensDAO getInstance() {
+        if (instance == null) {
+            synchronized (PushTokensDAO.class) {
+                if (instance == null) instance = new PushTokensDAO();
+            }
+        }
+        return instance;
+    }
+
+    public void upsert(PushTokenEntry entry) throws Exception {
+        try (Connection c = db.getConnection()) {
+            String sql = """
+                INSERT INTO user_push_tokens (
+                    token_id, login, session_id, provider, token, platform, user_agent, updated_at_ms
+                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
+                ON CONFLICT(token_id) DO UPDATE SET
+                    login=excluded.login,
+                    session_id=excluded.session_id,
+                    provider=excluded.provider,
+                    token=excluded.token,
+                    platform=excluded.platform,
+                    user_agent=excluded.user_agent,
+                    updated_at_ms=excluded.updated_at_ms
+                """;
+            try (PreparedStatement ps = c.prepareStatement(sql)) {
+                ps.setString(1, entry.getTokenId());
+                ps.setString(2, entry.getLogin());
+                ps.setString(3, entry.getSessionId());
+                ps.setString(4, entry.getProvider());
+                ps.setString(5, entry.getToken());
+                ps.setString(6, entry.getPlatform());
+                ps.setString(7, entry.getUserAgent());
+                ps.setLong(8, entry.getUpdatedAtMs());
+                ps.executeUpdate();
+            }
+        }
+    }
+
+    public List<PushTokenEntry> listByLogin(String login) throws Exception {
+        try (Connection c = db.getConnection()) {
+            String sql = """
+                SELECT token_id, login, session_id, provider, token, platform, user_agent, updated_at_ms
+                FROM user_push_tokens
+                WHERE login = ?
+                ORDER BY updated_at_ms DESC
+                """;
+            try (PreparedStatement ps = c.prepareStatement(sql)) {
+                ps.setString(1, login);
+                try (ResultSet rs = ps.executeQuery()) {
+                    List<PushTokenEntry> out = new ArrayList<>();
+                    while (rs.next()) {
+                        PushTokenEntry e = new PushTokenEntry();
+                        e.setTokenId(rs.getString("token_id"));
+                        e.setLogin(rs.getString("login"));
+                        e.setSessionId(rs.getString("session_id"));
+                        e.setProvider(rs.getString("provider"));
+                        e.setToken(rs.getString("token"));
+                        e.setPlatform(rs.getString("platform"));
+                        e.setUserAgent(rs.getString("user_agent"));
+                        e.setUpdatedAtMs(rs.getLong("updated_at_ms"));
+                        out.add(e);
+                    }
+                    return out;
+                }
+            }
+        }
+    }
+
+    public List<PushTokenEntry> listByLoginAndSession(String login, String sessionId) throws Exception {
+        try (Connection c = db.getConnection()) {
+            String sql = """
+                SELECT token_id, login, session_id, provider, token, platform, user_agent, updated_at_ms
+                FROM user_push_tokens
+                WHERE login = ? AND session_id = ?
+                ORDER BY updated_at_ms DESC
+                """;
+            try (PreparedStatement ps = c.prepareStatement(sql)) {
+                ps.setString(1, login);
+                ps.setString(2, sessionId);
+                try (ResultSet rs = ps.executeQuery()) {
+                    List<PushTokenEntry> out = new ArrayList<>();
+                    while (rs.next()) {
+                        PushTokenEntry e = new PushTokenEntry();
+                        e.setTokenId(rs.getString("token_id"));
+                        e.setLogin(rs.getString("login"));
+                        e.setSessionId(rs.getString("session_id"));
+                        e.setProvider(rs.getString("provider"));
+                        e.setToken(rs.getString("token"));
+                        e.setPlatform(rs.getString("platform"));
+                        e.setUserAgent(rs.getString("user_agent"));
+                        e.setUpdatedAtMs(rs.getLong("updated_at_ms"));
+                        out.add(e);
+                    }
+                    return out;
+                }
+            }
+        }
+    }
+}
@@ -0,0 +1,24 @@
+package shine.db.entities;
+
+public class DirectMessageEntry {
+    private String messageId;
+    private String fromLogin;
+    private String toLogin;
+    private String text;
+    private long createdAtMs;
+
+    public String getMessageId() { return messageId; }
+    public void setMessageId(String messageId) { this.messageId = messageId; }
+
+    public String getFromLogin() { return fromLogin; }
+    public void setFromLogin(String fromLogin) { this.fromLogin = fromLogin; }
+
+    public String getToLogin() { return toLogin; }
+    public void setToLogin(String toLogin) { this.toLogin = toLogin; }
+
+    public String getText() { return text; }
+    public void setText(String text) { this.text = text; }
+
+    public long getCreatedAtMs() { return createdAtMs; }
+    public void setCreatedAtMs(long createdAtMs) { this.createdAtMs = createdAtMs; }
+}
@@ -0,0 +1,36 @@
+package shine.db.entities;
+
+public class PushTokenEntry {
+    private String tokenId;
+    private String login;
+    private String sessionId;
+    private String provider;
+    private String token;
+    private String platform;
+    private String userAgent;
+    private long updatedAtMs;
+
+    public String getTokenId() { return tokenId; }
+    public void setTokenId(String tokenId) { this.tokenId = tokenId; }
+
+    public String getLogin() { return login; }
+    public void setLogin(String login) { this.login = login; }
+
+    public String getSessionId() { return sessionId; }
+    public void setSessionId(String sessionId) { this.sessionId = sessionId; }
+
+    public String getProvider() { return provider; }
+    public void setProvider(String provider) { this.provider = provider; }
+
+    public String getToken() { return token; }
+    public void setToken(String token) { this.token = token; }
+
+    public String getPlatform() { return platform; }
+    public void setPlatform(String platform) { this.platform = platform; }
+
+    public String getUserAgent() { return userAgent; }
+    public void setUserAgent(String userAgent) { this.userAgent = userAgent; }
+
+    public long getUpdatedAtMs() { return updatedAtMs; }
+    public void setUpdatedAtMs(long updatedAtMs) { this.updatedAtMs = updatedAtMs; }
+}