voice-activity-bot.ts

import { Client, Intents, VoiceState } from 'discord.js';
import { Client as PGClient } from 'pg';

// Discord bot token
const DISCORD_BOT_TOKEN = 'YOUR_DISCORD_BOT_TOKEN';

// PostgreSQL connection configuration
const pgClient = new PGClient({
  user: 'your_pg_user',
  host: 'localhost',
  database: 'your_pg_database',
  password: 'your_pg_password',
  port: 5432,
});

// Initialize PostgreSQL client
async function initPostgres() {
  await pgClient.connect();
  await pgClient.query(`
    CREATE TABLE IF NOT EXISTS voice_activity (
      user_id TEXT NOT NULL,
      channel_id TEXT NOT NULL,
      joined_at TIMESTAMP NOT NULL,
      left_at TIMESTAMP,
      PRIMARY KEY (user_id, joined_at)
    );
  `);
  console.log('PostgreSQL initialized.');
}

// Initialize Discord client
const client = new Client({
  intents: [Intents.FLAGS.GUILD_VOICE_STATES, Intents.FLAGS.GUILDS],
});

// Map to keep track of users' join times in case of bot restarts
const activeVoiceStates = new Map<string, { channelId: string; joinedAt: Date }>();

// Handle voice state updates
client.on('voiceStateUpdate', async (oldState: VoiceState, newState: VoiceState) => {
  const userId = newState.id;
  const oldChannelId = oldState.channelId;
  const newChannelId = newState.channelId;
  const timestamp = new Date();

  // User disconnected from a voice channel
  if (oldChannelId && !newChannelId) {
    const joinedAt = activeVoiceStates.get(userId)?.joinedAt;
    if (joinedAt) {
      await pgClient.query(
        `
        UPDATE voice_activity
        SET left_at = $1
        WHERE user_id = $2 AND joined_at = $3
      `,
        [timestamp, userId, joinedAt]
      );
      activeVoiceStates.delete(userId);
    }
  }

  // User connected to a voice channel
  if (!oldChannelId && newChannelId) {
    activeVoiceStates.set(userId, { channelId: newChannelId, joinedAt: timestamp });
    await pgClient.query(
      `
      INSERT INTO voice_activity (user_id, channel_id, joined_at)
      VALUES ($1, $2, $3)
    `,
      [userId, newChannelId, timestamp]
    );
  }

  // User switched voice channels
  if (oldChannelId && newChannelId && oldChannelId !== newChannelId) {
    const joinedAt = activeVoiceStates.get(userId)?.joinedAt;
    if (joinedAt) {
      // Update the old session
      await pgClient.query(
        `
        UPDATE voice_activity
        SET left_at = $1
        WHERE user_id = $2 AND joined_at = $3
      `,
        [timestamp, userId, joinedAt]
      );
    }
    // Start a new session
    activeVoiceStates.set(userId, { channelId: newChannelId, joinedAt: timestamp });
    await pgClient.query(
      `
      INSERT INTO voice_activity (user_id, channel_id, joined_at)
      VALUES ($1, $2, $3)
    `,
      [userId, newChannelId, timestamp]
    );
  }
});

// On bot ready
client.once('ready', async () => {
  console.log(`Logged in as ${client.user?.tag}!`);

  // Initialize active voice states
  client.guilds.cache.forEach((guild) => {
    guild.voiceStates.cache.forEach((voiceState) => {
      if (voiceState.channelId) {
        const userId = voiceState.id;
        const channelId = voiceState.channelId;
        const joinedAt = new Date(); // Assuming they joined when the bot started
        activeVoiceStates.set(userId, { channelId, joinedAt });
        // Insert into database
        pgClient.query(
          `
          INSERT INTO voice_activity (user_id, channel_id, joined_at)
          VALUES ($1, $2, $3)
        `,
          [userId, channelId, joinedAt]
        );
      }
    });
  });
});

// Initialize everything and start the bot
(async () => {
  try {
    await initPostgres();
    await client.login(DISCORD_BOT_TOKEN);
  } catch (error) {
    console.error('Error initializing bot:', error);
  }
})();