ECS-FullStack/backend/pg.js

const { Pool } = require('pg');
const format = require('pg-format');

let pool;

function initPool() {
  if (pool) return pool;
  const connectionString = process.env.DATABASE_URL;
  if (!connectionString) throw new Error('DATABASE_URL is not set');
  pool = new Pool({ connectionString });
  return pool;
}

async function ensureSchema() {
  const p = initPool();
  // basic tables: servers (settings), invites
  await p.query(`
    CREATE TABLE IF NOT EXISTS servers (
      guild_id TEXT PRIMARY KEY,
      settings JSONB DEFAULT '{}'
    );
  `);

  await p.query(`
    CREATE TABLE IF NOT EXISTS invites (
      code TEXT PRIMARY KEY,
      guild_id TEXT NOT NULL,
      url TEXT,
      channel_id TEXT,
      created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
      max_uses INTEGER DEFAULT 0,
      max_age INTEGER DEFAULT 0,
      temporary BOOLEAN DEFAULT false
    );
  `);

  await p.query(`
    CREATE TABLE IF NOT EXISTS users (
      id SERIAL PRIMARY KEY,
      discord_id TEXT UNIQUE,
      data JSONB DEFAULT '{}'
    );
  `);

  await p.query(`
    CREATE TABLE IF NOT EXISTS admin_logs (
      id SERIAL PRIMARY KEY,
      guild_id TEXT NOT NULL,
      action TEXT NOT NULL, -- 'kick', 'ban', 'timeout'
      target_user_id TEXT NOT NULL,
      target_username TEXT NOT NULL,
      moderator_user_id TEXT NOT NULL,
      moderator_username TEXT NOT NULL,
      reason TEXT NOT NULL,
      duration TEXT, -- for timeout/ban (e.g., '1d', '30m', 'permanent')
      end_date TIMESTAMP WITH TIME ZONE, -- calculated end date for timeout/ban
      timestamp TIMESTAMP WITH TIME ZONE DEFAULT now()
    );
  `);

  await p.query(`
    CREATE TABLE IF NOT EXISTS reaction_roles (
      id SERIAL PRIMARY KEY,
      guild_id TEXT NOT NULL,
      channel_id TEXT NOT NULL,
      message_id TEXT, -- message created in channel (optional until created)
      name TEXT NOT NULL,
      embed JSONB NOT NULL,
      buttons JSONB NOT NULL, -- array of { customId, label, roleId }
      created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
    );
  `);
}

// Servers
async function getServerSettings(guildId) {
  const p = initPool();
  const res = await p.query('SELECT settings FROM servers WHERE guild_id = $1', [guildId]);
  if (res.rowCount === 0) return null;
  return res.rows[0].settings || {};
}

async function upsertServerSettings(guildId, settings) {
  const p = initPool();
  await p.query(`INSERT INTO servers(guild_id, settings) VALUES($1, $2)
    ON CONFLICT (guild_id) DO UPDATE SET settings = $2`, [guildId, settings]);
}

// Invites
async function listInvites(guildId) {
  const p = initPool();
  const res = await p.query('SELECT code, url, channel_id, created_at, max_uses, max_age, temporary FROM invites WHERE guild_id = $1 ORDER BY created_at DESC', [guildId]);
  return res.rows;
}

async function addInvite(inv) {
  const p = initPool();
  const q = `INSERT INTO invites(code, guild_id, url, channel_id, created_at, max_uses, max_age, temporary) VALUES($1,$2,$3,$4,$5,$6,$7,$8)
    ON CONFLICT (code) DO UPDATE SET url = EXCLUDED.url, channel_id = EXCLUDED.channel_id, max_uses = EXCLUDED.max_uses, max_age = EXCLUDED.max_age, temporary = EXCLUDED.temporary, created_at = EXCLUDED.created_at`;
  await p.query(q, [inv.code, inv.guildId, inv.url, inv.channelId, inv.createdAt ? new Date(inv.createdAt) : new Date(), inv.maxUses || 0, inv.maxAge || 0, inv.temporary || false]);
}

async function deleteInvite(guildId, code) {
  const p = initPool();
  await p.query('DELETE FROM invites WHERE guild_id = $1 AND code = $2', [guildId, code]);
}

// Admin Logs
async function addAdminLog(logData) {
  const p = initPool();
  const q = `INSERT INTO admin_logs(guild_id, action, target_user_id, target_username, moderator_user_id, moderator_username, reason, duration, end_date)
    VALUES($1, $2, $3, $4, $5, $6, $7, $8, $9)`;
  await p.query(q, [
    logData.guildId,
    logData.action,
    logData.targetUserId,
    logData.targetUsername,
    logData.moderatorUserId,
    logData.moderatorUsername,
    logData.reason,
    logData.duration || null,
    logData.endDate || null
  ]);
}

async function getAdminLogs(guildId, limit = 50) {
  const p = initPool();
  const res = await p.query('SELECT * FROM admin_logs WHERE guild_id = $1 ORDER BY timestamp DESC LIMIT $2', [guildId, limit]);
  return res.rows;
}

async function getAdminLogsByAction(guildId, action, limit = 50) {
  const p = initPool();
  const res = await p.query('SELECT * FROM admin_logs WHERE guild_id = $1 AND action = $2 ORDER BY timestamp DESC LIMIT $3', [guildId, action, limit]);
  return res.rows;
}

async function deleteAdminLog(guildId, logId) {
  const p = initPool();
  await p.query('DELETE FROM admin_logs WHERE guild_id = $1 AND id = $2', [guildId, logId]);
}

async function deleteAllAdminLogs(guildId) {
  const p = initPool();
  await p.query('DELETE FROM admin_logs WHERE guild_id = $1', [guildId]);
}

// Reaction Roles
async function listReactionRoles(guildId) {
  const p = initPool();
  const res = await p.query('SELECT id, guild_id, channel_id, message_id, name, embed, buttons, created_at FROM reaction_roles WHERE guild_id = $1 ORDER BY created_at DESC', [guildId]);
  return res.rows;
}

async function getReactionRole(id) {
  const p = initPool();
  const res = await p.query('SELECT id, guild_id, channel_id, message_id, name, embed, buttons, created_at FROM reaction_roles WHERE id = $1', [id]);
  return res.rows[0] || null;
}

async function createReactionRole(rr) {
  const p = initPool();
  const q = `INSERT INTO reaction_roles(guild_id, channel_id, message_id, name, embed, buttons) VALUES($1,$2,$3,$4,$5,$6) RETURNING *`;
  // Ensure embed/buttons are proper JSON objects/arrays (some clients may send them as JSON strings)
  let embed = rr.embed || {};
  let buttons = rr.buttons || [];
  // If the payload is double-encoded (string containing a JSON string), keep parsing until it's a non-string
  try {
    while (typeof embed === 'string') {
      embed = JSON.parse(embed);
    }
  } catch (e) {
    // fall through and let Postgres reject invalid JSON if it's still malformed
  }
  try {
    while (typeof buttons === 'string') {
      buttons = JSON.parse(buttons);
    }
    // If buttons is an array but elements are themselves JSON strings, parse each element
    if (Array.isArray(buttons)) {
      buttons = buttons.map(b => {
        if (typeof b === 'string') {
          try {
            let parsed = b;
            while (typeof parsed === 'string') {
              parsed = JSON.parse(parsed);
            }
            return parsed;
          } catch (e) {
            return b; // leave as-is
          }
        }
        return b;
      });
    }
  } catch (e) {
    // leave as-is
  }

    // Validate shapes before inserting to DB to avoid Postgres JSON errors
    if (!embed || typeof embed !== 'object' || Array.isArray(embed)) {
      throw new Error('Invalid reaction role payload: `embed` must be a JSON object');
    }
    if (!Array.isArray(buttons) || buttons.length === 0 || !buttons.every(b => b && typeof b === 'object')) {
      throw new Error('Invalid reaction role payload: `buttons` must be a non-empty array of objects');
    }

  const res = await p.query(q, [rr.guildId, rr.channelId, rr.messageId || null, rr.name, embed, buttons]);
  return res.rows[0];
}

async function updateReactionRole(id, updates) {
  const p = initPool();
  const parts = [];
  const vals = [];
  let idx = 1;
  for (const k of ['channel_id','message_id','name','embed','buttons']) {
    if (typeof updates[k] !== 'undefined') {
      parts.push(`${k} = $${idx}`);
      // coerce JSON strings to objects for JSONB columns
      if ((k === 'embed' || k === 'buttons') && typeof updates[k] === 'string') {
        try {
          vals.push(JSON.parse(updates[k]));
        } catch (e) {
          vals.push(updates[k]);
        }
      } else {
        vals.push(updates[k]);
      }
      idx++;
    }
  }
  if (parts.length === 0) return getReactionRole(id);
  const q = `UPDATE reaction_roles SET ${parts.join(', ')} WHERE id = $${idx} RETURNING *`;
  vals.push(id);
  // Validate embed/buttons if they are being updated
  if (typeof updates.embed !== 'undefined') {
    const embed = vals[parts.indexOf('embed = $' + (parts.findIndex(p => p.startsWith('embed')) + 1))];
    if (!embed || typeof embed !== 'object' || Array.isArray(embed)) {
      throw new Error('Invalid reaction role payload: `embed` must be a JSON object');
    }
  }
  if (typeof updates.buttons !== 'undefined') {
    const buttons = vals[parts.indexOf('buttons = $' + (parts.findIndex(p => p.startsWith('buttons')) + 1))];
    if (!Array.isArray(buttons) || buttons.length === 0 || !buttons.every(b => b && typeof b === 'object')) {
      throw new Error('Invalid reaction role payload: `buttons` must be a non-empty array of objects');
    }
  }
  const res = await p.query(q, vals);
  return res.rows[0] || null;
}

async function deleteReactionRole(id) {
  const p = initPool();
  await p.query('DELETE FROM reaction_roles WHERE id = $1', [id]);
}

// Users
async function getUserData(discordId) {
  const p = initPool();
  const res = await p.query('SELECT data FROM users WHERE discord_id = $1', [discordId]);
  if (res.rowCount === 0) return null;
  return res.rows[0].data || {};
}

async function upsertUserData(discordId, data) {
  const p = initPool();
  await p.query(`INSERT INTO users(discord_id, data) VALUES($1, $2) ON CONFLICT (discord_id) DO UPDATE SET data = $2`, [discordId, data]);
}

module.exports = { initPool, ensureSchema, getServerSettings, upsertServerSettings, listInvites, addInvite, deleteInvite, getUserData, upsertUserData, addAdminLog, getAdminLogs, getAdminLogsByAction, deleteAdminLog, deleteAllAdminLogs, listReactionRoles, getReactionRole, createReactionRole, updateReactionRole, deleteReactionRole };