import { Client } from 'pg';
import * as dotenv from 'dotenv';
import * as path from 'path';
import * as fs from 'fs';

// Load env variables
dotenv.config({ path: path.join(__dirname, '../.env.local') });

const connectionString = process.env.DATABASE_URL;

if (!connectionString) {
  console.error("DATABASE_URL is not set in .env.local");
  process.exit(1);
}

async function main() {
  const client = new Client({ connectionString });
  await client.connect();

  console.log("Connected to PostgreSQL DB...");

  const projectId = '013f032c-ee82-42e5-9a89-b396c982bbf5';
  const threadId = '70983a8b-ec26-4241-91c2-d9a4a9ead973';

  // 1. Fetch project info
  const projectRes = await client.query(
    "SELECT id, slug, data FROM fs_projects WHERE id = $1",
    [projectId]
  );
  if (projectRes.rows.length === 0) {
    console.error(`Project ${projectId} not found.`);
    await client.end();
    process.exit(1);
  }
  const project = projectRes.rows[0];

  // 2. Fetch thread messages
  const messagesRes = await client.query(
    "SELECT id, created_at, data FROM fs_chat_messages WHERE thread_id = $1 ORDER BY created_at ASC",
    [threadId]
  );
  const messages = messagesRes.rows;
  console.log(`Fetched ${messages.length} messages for Ajay's thread ${threadId}`);

  const turns = [];
  let userMsg = null;

  for (const msg of messages) {
    const role = msg.data.role;
    if (role === 'user') {
      userMsg = msg;
    } else if (role === 'assistant' || role === 'model') {
      if (userMsg) {
        const rawToolResults = msg.data._rawToolResults || [];
        const actionsRun = rawToolResults.map((tr: any) => {
          let stdout = tr.result;
          let ok = true;
          let status = "success";

          try {
            const parsedRes = JSON.parse(tr.result);
            if (parsedRes.ok === false || parsedRes.error || (parsedRes.errors && parsedRes.errors.length > 0)) {
              ok = false;
              status = "error";
            }
          } catch (e) {}

          return {
            tool_name: tr.name,
            tool_call_id: tr.id || `tc-${Date.now()}-${Math.random().toString(36).substr(2, 9)}`,
            input_args: tr.args || {},
            execution_outcome: {
              ok,
              status,
              stdout
            }
          };
        });

        turns.push({
          turn_metadata: {
            message_id: userMsg.id,
            timestamp_utc: userMsg.created_at.toISOString(),
            conversation_id: threadId,
            audit_tag: msg.data.content.includes("Unauthorized") ? 'FAIL: Workspace Session Auth Blockout' : 'WORK: Conversational Planning'
          },
          "1_user_interaction": {
            prompt_text: userMsg.data.content
          },
          "2_payload_sent_to_google": {
            endpoint_url: "https://us-central1-aiplatform.googleapis.com/v1/projects/gen-lang-client-0980079410/locations/us-central1/publishers/google/models/gemini-3.1-pro-preview:generateContent",
            system_instruction: "Configured via VIBN Coder System Prompt (coder.ts / buildSystemPrompt)",
            contents: [
              {
                role: "user",
                parts: [{ text: userMsg.data.content }]
              }
            ]
          },
          "3_payload_received_from_google": {
            timestamp_utc: msg.created_at.toISOString(),
            raw_candidates: {
              content: {
                role: "model",
                parts: [{ thought: null, text: msg.data.content }]
              }
            }
          },
          "4_platform_executions_and_telemetry": {
            actions_run: actionsRun
          },
          "5_git_version_control_diffs": []
        });

        userMsg = null; // reset
      }
    }
  }

  const dataset = {
    dataset_metadata: {
      title: "VIBN Telemetry Dataset — ThathaPaati Workspace Lockout",
      purpose: "Telemetry report capturing the exact server-side tool execution results and 401 Unauthorized lockout loop experienced by Ajay Sridharan.",
      source_project: {
        id: projectId,
        name: project.data.name || "ThathaPaati",
        slug: project.slug
      },
      compiled_at: new Date().toISOString(),
      total_turns_audited: turns.length
    },
    turns
  };

  const currentDate = new Date().toISOString().split('T')[0]; // YYYY-MM-DD
  const outputPath = path.join(__dirname, `../../opus_telemetry_audit_dataset_ajay_${currentDate}.json`);
  fs.writeFileSync(outputPath, JSON.stringify(dataset, null, 2));
  console.log(`\n🎉 Ajay's QA dataset successfully written to: ${outputPath}`);

  await client.end();
}

main().catch(console.error);
