studio/services/bot/scripts/import-gchat.ts

/**
 * Import Google Chat export JSON into Tinqs Chat.
 *
 * Usage: npx tsx scripts/import-gchat.ts /path/to/gchat-export
 *
 * Reads all JSON files from spaces/ and dms/ directories,
 * creates chat_spaces and inserts chat_messages.
 */

import { readFileSync, readdirSync } from "fs";
import { join } from "path";
import pg from "pg";

const DB_URL = process.env.DATABASE_URL || "postgresql://admin:admin@localhost:5432/tinqs_hub";

async function main() {
  const exportDir = process.argv[2];
  if (!exportDir) {
    console.error("Usage: npx tsx scripts/import-gchat.ts /path/to/gchat-export");
    process.exit(1);
  }

  const pool = new pg.Pool({ connectionString: DB_URL, max: 3 });

  // Ensure tables
  await pool.query(`CREATE TABLE IF NOT EXISTS chat_spaces (
    id TEXT PRIMARY KEY, name TEXT NOT NULL, type TEXT DEFAULT 'space', created_at TIMESTAMPTZ DEFAULT NOW()
  )`);
  await pool.query(`CREATE TABLE IF NOT EXISTS chat_messages (
    id SERIAL PRIMARY KEY, space_id TEXT NOT NULL, user_name TEXT NOT NULL, machine TEXT,
    text TEXT, image_url TEXT, image_meta TEXT, thread_id INTEGER, created_at TIMESTAMPTZ DEFAULT NOW()
  )`);

  let totalMessages = 0;

  for (const subdir of ["spaces", "dms"]) {
    const dir = join(exportDir, subdir);
    let files: string[];
    try {
      files = readdirSync(dir).filter((f) => f.endsWith(".json"));
    } catch {
      continue;
    }

    for (const file of files) {
      const data = JSON.parse(readFileSync(join(dir, file), "utf-8"));
      const spaceName = data.space || file.replace(".json", "");
      const spaceId = spaceName.toLowerCase().replace(/[^a-z0-9-]/g, "-");
      const spaceType = subdir === "dms" ? "dm" : "space";

      // Create space
      await pool.query(
        `INSERT INTO chat_spaces (id, name, type) VALUES ($1, $2, $3) ON CONFLICT (id) DO NOTHING`,
        [spaceId, spaceName, spaceType]
      );

      // Insert messages
      const messages = data.messages || [];
      let count = 0;
      for (const m of messages) {
        const sender = m.sender?.displayName || "Unknown";
        const text = m.text || m.formattedText || "";
        if (!text.trim()) continue;

        const createdAt = m.createTime || new Date().toISOString();

        await pool.query(
          `INSERT INTO chat_messages (space_id, user_name, text, created_at)
           VALUES ($1, $2, $3, $4)`,
          [spaceId, sender, text, createdAt]
        );
        count++;
      }

      totalMessages += count;
      console.log(`  ${spaceId}: ${count} messages (${spaceType})`);
    }
  }

  console.log(`\nDone: ${totalMessages} messages imported.`);
  await pool.end();
}

main().catch(console.error);