Files
SHiNE-server/SHiNE-server/shine-server-db/src/main/java/shine/db/dao/UserProfileStateDAO.java
T

159 lines
10 KiB
Java

package shine.db.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import shine.db.MsgSubType;
/** Fast denormalized projection for user cards shown in dialogs/relations/profile lists. */
public final class UserProfileStateDAO {
private static volatile UserProfileStateDAO instance;
private UserProfileStateDAO() {}
public static UserProfileStateDAO getInstance() {
if (instance == null) synchronized (UserProfileStateDAO.class) {
if (instance == null) instance = new UserProfileStateDAO();
}
return instance;
}
public ProfileCard get(Connection c, String login) throws SQLException {
try (PreparedStatement ps = c.prepareStatement("""
SELECT login, first_name, last_name, ava_ar, account_role, shine_status
FROM user_profile_state WHERE LOWER(login)=LOWER(?) LIMIT 1
""")) {
ps.setString(1, login);
try (ResultSet rs = ps.executeQuery()) {
if (!rs.next()) return new ProfileCard(login, "", "", "", null, null);
return map(rs);
}
}
}
/**
* Effective outgoing social relation with priority close_friend > friend > contact > none.
*
* Use the canonical relation lookup so all UI paths share the same normalized login matching.
* Stage 2 connections_state identity is logical and no longer contains a physical target fork.
*/
public String getEffectiveRelationType(Connection c, String ownerLogin, String targetLogin) throws SQLException {
if (ownerLogin == null || ownerLogin.isBlank() || targetLogin == null || targetLogin.isBlank()) return "none";
ConnectionsStateDAO relations = ConnectionsStateDAO.getInstance();
if (relations.hasOutgoingByRelTypeCanonical(c, ownerLogin, targetLogin, MsgSubType.CONNECTION_CLOSE_FRIEND)) {
return "close_friend";
}
if (relations.hasOutgoingByRelTypeCanonical(c, ownerLogin, targetLogin, MsgSubType.CONNECTION_FRIEND)) {
return "friend";
}
if (relations.hasOutgoingByRelTypeCanonical(c, ownerLogin, targetLogin, MsgSubType.CONNECTION_CONTACT)) {
return "contact";
}
return "none";
}
public List<RelationCard> listRelations(Connection c, String ownerLogin, String listType, int limit, int offset) throws SQLException {
int safeLimit = Math.max(1, Math.min(limit <= 0 ? 100 : limit, 500));
int safeOffset = Math.max(0, offset);
String where = switch (String.valueOf(listType)) {
case "friends" -> "LOWER(cs.login)=LOWER(?) AND cs.rel_type=14 AND NOT EXISTS (SELECT 1 FROM connections_state cf WHERE LOWER(cf.login)=LOWER(cs.login) AND LOWER(cf.to_login)=LOWER(cs.to_login) AND cf.rel_type=10)";
case "close_friends" -> "LOWER(cs.login)=LOWER(?) AND cs.rel_type=10";
case "primary_received" -> "LOWER(cs.to_login)=LOWER(?) AND cs.rel_type=80 AND shine_is_primary(cs.login) AND shine_target_accepts_primary_confirmation(cs.to_login)";
case "primary_given" -> "LOWER(cs.login)=LOWER(?) AND cs.rel_type=80 AND shine_is_primary(cs.login) AND shine_target_accepts_primary_confirmation(cs.to_login)";
case "shine_received" -> "LOWER(cs.to_login)=LOWER(?) AND cs.rel_type=70 AND shine_is_primary(cs.login) AND shine_is_shining(cs.login) AND shine_target_accepts_shine_confirmation(cs.to_login)";
case "shine_given" -> "LOWER(cs.login)=LOWER(?) AND cs.rel_type=70 AND shine_is_primary(cs.login) AND shine_is_shining(cs.login) AND shine_target_accepts_shine_confirmation(cs.to_login)";
default -> throw new IllegalArgumentException("Unsupported listType: " + listType);
};
boolean received = "primary_received".equals(listType) || "shine_received".equals(listType);
String personExpr = received ? "cs.login" : "cs.to_login";
String sql = """
SELECT DISTINCT ON (LOWER(%s))
%s AS person_login,
COALESCE(up.first_name,'') AS first_name,
COALESCE(up.last_name,'') AS last_name,
COALESCE(up.ava_ar,'') AS ava_ar,
up.account_role, up.shine_status,
EXISTS(SELECT 1 FROM connections_state x WHERE LOWER(x.login)=LOWER(?) AND LOWER(x.to_login)=LOWER(%s) AND x.rel_type=80
AND shine_is_primary(x.login) AND shine_target_accepts_primary_confirmation(x.to_login)) AS primary_confirmed,
EXISTS(SELECT 1 FROM connections_state x WHERE LOWER(x.login)=LOWER(?) AND LOWER(x.to_login)=LOWER(%s) AND x.rel_type=70
AND shine_is_primary(x.login) AND shine_is_shining(x.login) AND shine_target_accepts_shine_confirmation(x.to_login)) AS shine_confirmed,
CASE
WHEN EXISTS(SELECT 1 FROM connections_state x WHERE LOWER(x.login)=LOWER(?) AND LOWER(x.to_login)=LOWER(%s) AND x.rel_type=10) THEN 'close_friend'
WHEN EXISTS(SELECT 1 FROM connections_state x WHERE LOWER(x.login)=LOWER(?) AND LOWER(x.to_login)=LOWER(%s) AND x.rel_type=14) THEN 'friend'
WHEN EXISTS(SELECT 1 FROM connections_state x WHERE LOWER(x.login)=LOWER(?) AND LOWER(x.to_login)=LOWER(%s) AND x.rel_type=20) THEN 'contact'
ELSE 'none' END AS relation_type,
COALESCE(us.primary_confirmations_received_count,0) AS primary_confirmations_count,
COALESCE(us.shine_confirmations_received_count,0) AS shine_confirmations_count
FROM connections_state cs
LEFT JOIN user_profile_state up ON LOWER(up.login)=LOWER(%s)
LEFT JOIN user_stats_state us ON LOWER(us.login)=LOWER(%s)
WHERE %s
ORDER BY LOWER(%s), %s
LIMIT ? OFFSET ?
""".formatted(personExpr, personExpr, personExpr, personExpr, personExpr, personExpr, personExpr, personExpr, personExpr, where, personExpr, personExpr);
List<RelationCard> out = new ArrayList<>();
try (PreparedStatement ps = c.prepareStatement(sql)) {
int i=1;
ps.setString(i++, ownerLogin); ps.setString(i++, ownerLogin); ps.setString(i++, ownerLogin); ps.setString(i++, ownerLogin); ps.setString(i++, ownerLogin);
ps.setString(i++, ownerLogin);
ps.setInt(i++, safeLimit); ps.setInt(i, safeOffset);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) out.add(new RelationCard(
rs.getString("person_login"), rs.getString("first_name"), rs.getString("last_name"), rs.getString("ava_ar"),
rs.getString("account_role"), rs.getString("shine_status"), rs.getBoolean("primary_confirmed"), rs.getBoolean("shine_confirmed"),
rs.getString("relation_type"), rs.getInt("primary_confirmations_count"), rs.getInt("shine_confirmations_count")
));
}
}
return out;
}
public List<ChannelCard> listPublicChannels(Connection c, String login, String mode, boolean includeDeleted, int limit, int offset) throws SQLException {
int safeLimit=Math.max(1,Math.min(limit<=0?100:limit,500)), safeOffset=Math.max(0,offset);
String sql;
if ("owned".equals(mode)) {
sql="""
SELECT cn.owner_login, cn.slug, cn.display_name, cn.ava_ar, cn.ava_pr, cn.owner_bch_name,
cn.channel_root_block_number, encode(cn.channel_root_block_hash,'hex') root_hash,
cn.is_deleted, cn.deleted_at_ms, cn.deleted_block_number
FROM channel_names_state cn
WHERE LOWER(cn.owner_login)=LOWER(?) AND cn.channel_type_code=1 AND (? OR NOT cn.is_deleted)
ORDER BY cn.display_name, cn.slug LIMIT ? OFFSET ?
""";
} else if ("following".equals(mode)) {
sql="""
SELECT DISTINCT cn.owner_login, cn.slug, cn.display_name, cn.ava_ar, cn.ava_pr, cn.owner_bch_name,
cn.channel_root_block_number, encode(cn.channel_root_block_hash,'hex') root_hash,
cn.is_deleted, cn.deleted_at_ms, cn.deleted_block_number
FROM connections_state cs
JOIN channel_names_state cn
ON LOWER(cn.owner_login)=LOWER(cs.to_login)
AND cn.channel_root_block_number=cs.to_block_number
AND cn.channel_root_block_hash=cs.to_block_hash
WHERE LOWER(cs.login)=LOWER(?) AND cs.rel_type=30 AND cn.channel_type_code=1 AND (? OR NOT cn.is_deleted)
ORDER BY cn.display_name, cn.slug LIMIT ? OFFSET ?
""";
} else throw new IllegalArgumentException("Unsupported channel mode: "+mode);
List<ChannelCard> out=new ArrayList<>();
try(PreparedStatement ps=c.prepareStatement(sql)){
ps.setString(1,login); ps.setBoolean(2,includeDeleted); ps.setInt(3,safeLimit); ps.setInt(4,safeOffset);
try(ResultSet rs=ps.executeQuery()){
while(rs.next()) out.add(new ChannelCard(
rs.getString("owner_login"), rs.getString("slug"), rs.getString("display_name"), rs.getString("ava_ar"), rs.getString("ava_pr"),
rs.getString("owner_bch_name"), rs.getInt("channel_root_block_number"), rs.getString("root_hash"),
rs.getBoolean("is_deleted"), rs.getLong("deleted_at_ms"), (Integer) rs.getObject("deleted_block_number")
));
}
}
return out;
}
private static ProfileCard map(ResultSet rs)throws SQLException{return new ProfileCard(rs.getString("login"),rs.getString("first_name"),rs.getString("last_name"),rs.getString("ava_ar"),rs.getString("account_role"),rs.getString("shine_status"));}
public record ProfileCard(String login,String firstName,String lastName,String avatarAr,String accountRole,String shineStatus){}
public record RelationCard(String login,String firstName,String lastName,String avatarAr,String accountRole,String shineStatus,boolean primaryConfirmed,boolean shineConfirmed,String relationType,int primaryConfirmationsCount,int shineConfirmationsCount){}
public record ChannelCard(String ownerLogin,String slug,String displayName,String avatarAr,String avatarPr,String ownerBlockchainName,int rootBlockNumber,String rootBlockHashHex,boolean deleted,long deletedAtMs,Integer deletedBlockNumber){}
}