src/db/index.js

import pg from "pg";

const { Pool } = pg;

const DATABASE_URL = process.env.DATABASE_URL
  ?? `postgres://${process.env.PGUSER ?? "postgres"}:${process.env.PGPASSWORD ?? "postgres"}@${process.env.PGHOST ?? "localhost"}:${process.env.PGPORT ?? "5432"}/${process.env.PGDATABASE ?? "nexus"}`;

export const pool = new Pool({
  connectionString: DATABASE_URL,
  ssl: process.env.PGSSL === "true" ? { rejectUnauthorized: false } : false,
});

export async function initDb() {
  await pool.query(`
    CREATE TABLE IF NOT EXISTS users (
      email         TEXT PRIMARY KEY,
      "displayName" TEXT NOT NULL,
      "passwordHash" TEXT NOT NULL,
      role          TEXT NOT NULL DEFAULT 'guest',
      avatar        TEXT,
      "nameTagColor" TEXT NOT NULL DEFAULT 'default',
      "lastSeen"    BIGINT,
      "createdAt"   BIGINT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS friends (
      "userA" TEXT NOT NULL,
      "userB" TEXT NOT NULL,
      PRIMARY KEY ("userA", "userB")
    );

    CREATE TABLE IF NOT EXISTS friend_requests (
      id          TEXT PRIMARY KEY,
      "fromEmail" TEXT NOT NULL,
      "toEmail"   TEXT NOT NULL,
      "fromName"  TEXT,
      "fromAvatar" TEXT,
      "createdAt" BIGINT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS rooms (
      "roomId"   TEXT PRIMARY KEY,
      name       TEXT,
      type       TEXT NOT NULL,
      creator    TEXT,
      admins     JSONB,
      "createdAt" BIGINT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS room_members (
      "roomId" TEXT NOT NULL,
      email    TEXT NOT NULL,
      PRIMARY KEY ("roomId", email)
    );

    CREATE TABLE IF NOT EXISTS messages (
      id            TEXT PRIMARY KEY,
      "roomId"      TEXT NOT NULL,
      sender        TEXT NOT NULL,
      "senderName"  TEXT,
      "senderRole"  TEXT,
      "senderAvatar" TEXT,
      "nameTagColor" TEXT,
      content       TEXT,
      type          TEXT,
      "fileUrl"     TEXT,
      "fileName"    TEXT,
      "fileSize"    BIGINT,
      timestamp     BIGINT,
      recalled      BOOLEAN NOT NULL DEFAULT FALSE,
      "recalledAt"  BIGINT
    );

    CREATE TABLE IF NOT EXISTS invites (
      id          TEXT PRIMARY KEY,
      "roomId"    TEXT NOT NULL,
      "roomName"  TEXT,
      "fromEmail" TEXT NOT NULL,
      "fromName"  TEXT,
      "toEmail"   TEXT NOT NULL,
      "createdAt" BIGINT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS push_subscriptions (
      email        TEXT PRIMARY KEY,
      subscription JSONB NOT NULL
    );

    CREATE TABLE IF NOT EXISTS local_deletes (
      email TEXT NOT NULL,
      "msgId" TEXT NOT NULL,
      PRIMARY KEY (email, "msgId")
    );

    CREATE INDEX IF NOT EXISTS idx_messages_room ON messages ("roomId");
    CREATE INDEX IF NOT EXISTS idx_room_members_email ON room_members (email);
    CREATE INDEX IF NOT EXISTS idx_friends_userA ON friends ("userA");
    CREATE INDEX IF NOT EXISTS idx_friends_userB ON friends ("userB");
  `);
}

// ── Users ───────────────────────────────────────────────────────
export async function getUser(email) {
  const { rows } = await pool.query("SELECT * FROM users WHERE email = $1", [email]);
  return rows[0] ?? null;
}

export async function saveUser(u) {
  const v = {
    avatar: null,
    lastSeen: null,
    role: "guest",
    nameTagColor: "default",
    ...u,
  };
  await pool.query(`
    INSERT INTO users (email, "displayName", "passwordHash", role, avatar, "nameTagColor", "lastSeen", "createdAt")
    VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
    ON CONFLICT (email) DO UPDATE SET
      "displayName" = EXCLUDED."displayName",
      "passwordHash" = EXCLUDED."passwordHash",
      role = EXCLUDED.role,
      avatar = EXCLUDED.avatar,
      "nameTagColor" = EXCLUDED."nameTagColor",
      "lastSeen" = EXCLUDED."lastSeen",
      "createdAt" = EXCLUDED."createdAt"
  `, [v.email, v.displayName, v.passwordHash, v.role, v.avatar, v.nameTagColor, v.lastSeen, v.createdAt]);
}

export async function updateUser(email, patch) {
  const fields = Object.keys(patch);
  if (!fields.length) return;
  const setClause = fields.map((f, i) => `"${f}" = $${i + 2}`).join(", ");
  const values = fields.map(f => patch[f]);
  await pool.query(`UPDATE users SET ${setClause} WHERE email = $1`, [email, ...values]);
}

export async function getAllUserEmails() {
  const { rows } = await pool.query("SELECT email FROM users");
  return rows.map(r => r.email);
}

export async function deleteUser(email) {
  await pool.query("DELETE FROM users WHERE email = $1", [email]);
}

export async function touchLastSeen(email) {
  await pool.query('UPDATE users SET "lastSeen" = $1 WHERE email = $2', [Date.now(), email]);
}

// ── Friends ─────────────────────────────────────────────────────
export async function areFriends(a, b) {
  const [u, v] = [a, b].sort();
  const { rows } = await pool.query('SELECT 1 FROM friends WHERE "userA" = $1 AND "userB" = $2', [u, v]);
  return rows.length > 0;
}

export async function getFriends(email) {
  const { rows } = await pool.query('SELECT "userA", "userB" FROM friends WHERE "userA" = $1 OR "userB" = $1', [email]);
  return rows.map(r => (r.userA === email ? r.userB : r.userA));
}

export async function addFriend(a, b) {
  const [u, v] = [a, b].sort();
  await pool.query('INSERT INTO friends ("userA", "userB") VALUES ($1, $2) ON CONFLICT DO NOTHING', [u, v]);
}

export async function removeFriend(a, b) {
  const [u, v] = [a, b].sort();
  await pool.query('DELETE FROM friends WHERE "userA" = $1 AND "userB" = $2', [u, v]);
}

export async function deleteAllFriends(email) {
  await pool.query('DELETE FROM friends WHERE "userA" = $1 OR "userB" = $1', [email]);
}

// ── Friend Requests ─────────────────────────────────────────────
export async function getFriendRequests(toEmail) {
  const { rows } = await pool.query('SELECT * FROM friend_requests WHERE "toEmail" = $1 ORDER BY "createdAt"', [toEmail]);
  return rows;
}

export async function hasSentRequest(fromEmail, toEmail) {
  const { rows } = await pool.query('SELECT 1 FROM friend_requests WHERE "fromEmail" = $1 AND "toEmail" = $2', [fromEmail, toEmail]);
  return rows.length > 0;
}

export async function addFriendRequest(req) {
  const v = { fromName: null, fromAvatar: null, ...req, createdAt: Date.now() };
  await pool.query(`
    INSERT INTO friend_requests (id, "fromEmail", "toEmail", "fromName", "fromAvatar", "createdAt")
    VALUES ($1, $2, $3, $4, $5, $6)
    ON CONFLICT (id) DO NOTHING
  `, [v.id, v.fromEmail, v.toEmail, v.fromName, v.fromAvatar, v.createdAt]);
}

export async function removeFriendRequest(fromEmail, toEmail) {
  await pool.query('DELETE FROM friend_requests WHERE "fromEmail" = $1 AND "toEmail" = $2', [fromEmail, toEmail]);
}

export async function deleteAllFriendRequests(email) {
  await pool.query('DELETE FROM friend_requests WHERE "fromEmail" = $1 OR "toEmail" = $1', [email]);
}

// ── Rooms ────────────────────────────────────────────────────────
export async function getRoom(roomId) {
  const { rows } = await pool.query('SELECT * FROM rooms WHERE "roomId" = $1', [roomId]);
  const row = rows[0];
  if (!row) return null;
  const members = await getRoomMembers(roomId);
  return { ...row, admins: row.admins ?? [], members };
}

export async function saveRoom(r) {
  await pool.query(`
    INSERT INTO rooms ("roomId", name, type, creator, admins, "createdAt")
    VALUES ($1, $2, $3, $4, $5, $6)
    ON CONFLICT ("roomId") DO UPDATE SET
      name = EXCLUDED.name,
      type = EXCLUDED.type,
      creator = EXCLUDED.creator,
      admins = EXCLUDED.admins,
      "createdAt" = EXCLUDED."createdAt"
  `, [r.roomId, r.name ?? "", r.type, r.creator ?? null, JSON.stringify(r.admins ?? []), r.createdAt]);
}

export async function getRoomMembers(roomId) {
  const { rows } = await pool.query('SELECT email FROM room_members WHERE "roomId" = $1', [roomId]);
  return rows.map(r => r.email);
}

export async function addRoomMember(roomId, email) {
  await pool.query('INSERT INTO room_members ("roomId", email) VALUES ($1, $2) ON CONFLICT DO NOTHING', [roomId, email]);
}

export async function removeRoomMember(roomId, email) {
  await pool.query('DELETE FROM room_members WHERE "roomId" = $1 AND email = $2', [roomId, email]);
}

export async function isRoomMember(roomId, email) {
  const { rows } = await pool.query('SELECT 1 FROM room_members WHERE "roomId" = $1 AND email = $2', [roomId, email]);
  return rows.length > 0;
}

export async function getUserRooms(email) {
  const { rows } = await pool.query('SELECT "roomId" FROM room_members WHERE email = $1', [email]);
  return rows.map(r => r.roomId);
}

export async function deleteRoom(roomId) {
  await pool.query('DELETE FROM messages WHERE "roomId" = $1', [roomId]);
  await pool.query('DELETE FROM room_members WHERE "roomId" = $1', [roomId]);
  await pool.query('DELETE FROM rooms WHERE "roomId" = $1', [roomId]);
}

export async function deleteAllUserRooms(email) {
  const roomIds = await getUserRooms(email);
  await pool.query('DELETE FROM room_members WHERE email = $1', [email]);
  for (const roomId of roomIds) {
    const remaining = await getRoomMembers(roomId);
    if (remaining.length === 0) {
      await pool.query('DELETE FROM messages WHERE "roomId" = $1', [roomId]);
      await pool.query('DELETE FROM rooms WHERE "roomId" = $1', [roomId]);
    }
  }
}

// ── Messages ─────────────────────────────────────────────────────
const MSG_TTL = {
  admin: null, vip: null,
  member: 30 * 24 * 3600 * 1000,
  guest: 7 * 24 * 3600 * 1000,
};

export async function addMessage(msg) {
  const v = {
    senderName: null, senderRole: null, senderAvatar: null, nameTagColor: null,
    fileUrl: null, fileName: null, fileSize: null, recalledAt: null,
    ...msg,
    recalled: !!msg.recalled,
  };
  await pool.query(`
    INSERT INTO messages (id, "roomId", sender, "senderName", "senderRole", "senderAvatar", "nameTagColor",
      content, type, "fileUrl", "fileName", "fileSize", timestamp, recalled, "recalledAt")
    VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15)
  `, [v.id, v.roomId, v.sender, v.senderName, v.senderRole, v.senderAvatar, v.nameTagColor,
      v.content, v.type, v.fileUrl, v.fileName, v.fileSize, v.timestamp, v.recalled, v.recalledAt]);
}

export async function getRoomMessages(roomId) {
  const now = Date.now();
  const { rows } = await pool.query('SELECT * FROM messages WHERE "roomId" = $1 ORDER BY timestamp', [roomId]);
  return rows.filter(m => {
    if (m.recalled) return true;
    const ttl = MSG_TTL[m.senderRole ?? "guest"];
    if (ttl === null || ttl === undefined) return true;
    return now - Number(m.timestamp ?? 0) < ttl;
  });
}

export async function updateMessage(msgId, patch) {
  const fields = Object.keys(patch);
  if (fields.length) {
    const data = { ...patch };
    if ("recalled" in data) data.recalled = !!data.recalled;
    const setClause = fields.map((f, i) => `"${f}" = $${i + 2}`).join(", ");
    const values = fields.map(f => data[f]);
    await pool.query(`UPDATE messages SET ${setClause} WHERE id = $1`, [msgId, ...values]);
  }
  const { rows } = await pool.query("SELECT * FROM messages WHERE id = $1", [msgId]);
  return rows[0] ?? null;
}

// ── Invites ──────────────────────────────────────────────────────
export async function getInvites(toEmail) {
  const { rows } = await pool.query('SELECT * FROM invites WHERE "toEmail" = $1', [toEmail]);
  return rows;
}

export async function addInvite(inv) {
  const v = { roomName: null, fromName: null, ...inv, createdAt: Date.now() };
  await pool.query(`
    INSERT INTO invites (id, "roomId", "roomName", "fromEmail", "fromName", "toEmail", "createdAt")
    VALUES ($1,$2,$3,$4,$5,$6,$7)
    ON CONFLICT (id) DO NOTHING
  `, [v.id, v.roomId, v.roomName, v.fromEmail, v.fromName, v.toEmail, v.createdAt]);
}

export async function removeInvite(id) {
  await pool.query("DELETE FROM invites WHERE id = $1", [id]);
}

export async function deleteAllInvites(email) {
  await pool.query('DELETE FROM invites WHERE "toEmail" = $1 OR "fromEmail" = $1', [email]);
}

// ── Push subscriptions ───────────────────────────────────────────
export async function savePushSub(email, subscription) {
  await pool.query(`
    INSERT INTO push_subscriptions (email, subscription) VALUES ($1, $2)
    ON CONFLICT (email) DO UPDATE SET subscription = EXCLUDED.subscription
  `, [email, JSON.stringify(subscription)]);
}

export async function getPushSub(email) {
  const { rows } = await pool.query("SELECT subscription FROM push_subscriptions WHERE email = $1", [email]);
  return rows[0]?.subscription ?? null;
}

export async function deletePushSub(email) {
  await pool.query("DELETE FROM push_subscriptions WHERE email = $1", [email]);
}

// ── Local deletes ────────────────────────────────────────────────
export async function addLocalDelete(email, msgId) {
  await pool.query('INSERT INTO local_deletes (email, "msgId") VALUES ($1, $2) ON CONFLICT DO NOTHING', [email, msgId]);
}

export async function getLocalDeletes(email) {
  const { rows } = await pool.query('SELECT "msgId" FROM local_deletes WHERE email = $1', [email]);
  return rows.map(r => r.msgId);
}

export async function deleteAllLocalDeletes(email) {
  await pool.query("DELETE FROM local_deletes WHERE email = $1", [email]);
}