#!/usr/bin/env node
import mysql from 'mysql2/promise';
import {
  parseMindSpacePublicUrl,
} from '../mindspace-canonical-url.mjs';
import {
  loadMemindEnvFiles,
} from './memind-runtime-profile.mjs';

function usage() {
  return [
    'Usage: node scripts/trace-mindspace-artifact.mjs (--session-id <id> | --package-id <id> | --asset-id <id> | --public-url <url>) [--user-id <id>] [--json]',
    '',
    'Traces MindSpace package/artifact/asset records for a session, package, asset, or public URL.',
  ].join('\n');
}

function parseArgs(argv) {
  const args = {
    sessionId: '',
    packageId: '',
    assetId: '',
    publicUrl: '',
    userId: '',
    json: false,
  };
  for (let i = 2; i < argv.length; i += 1) {
    const arg = argv[i];
    if (arg === '--session-id' && argv[i + 1]) args.sessionId = argv[++i];
    else if (arg === '--package-id' && argv[i + 1]) args.packageId = argv[++i];
    else if (arg === '--asset-id' && argv[i + 1]) args.assetId = argv[++i];
    else if (arg === '--public-url' && argv[i + 1]) args.publicUrl = argv[++i];
    else if (arg === '--user-id' && argv[i + 1]) args.userId = argv[++i];
    else if (arg === '--json') args.json = true;
    else if (arg === '--help' || arg === '-h') {
      console.log(usage());
      process.exit(0);
    } else {
      console.error(`Unknown argument: ${arg}`);
      console.error(usage());
      process.exit(2);
    }
  }
  if (!args.sessionId && !args.packageId && !args.assetId && !args.publicUrl) {
    console.error(usage());
    process.exit(2);
  }
  return args;
}

function createPoolFromEnv() {
  loadMemindEnvFiles(process.cwd());
  const poolOptions = { connectionLimit: 3 };
  if (process.env.DATABASE_URL) {
    return mysql.createPool({
      uri: process.env.DATABASE_URL,
      ...poolOptions,
    });
  }
  if (!process.env.MYSQL_HOST && !process.env.MYSQL_DATABASE) {
    throw new Error(
      'MindSpace trace requires DATABASE_URL or MYSQL_* configuration',
    );
  }
  return mysql.createPool({
    host: process.env.MYSQL_HOST ?? 'localhost',
    port: Number(process.env.MYSQL_PORT ?? 3306),
    user: process.env.MYSQL_USER ?? 'boot',
    password: process.env.MYSQL_PASSWORD ?? '',
    database: process.env.MYSQL_DATABASE ?? 'tkmind',
    ...poolOptions,
  });
}

async function resolvePublicUrlTarget(pool, publicUrl) {
  const parsed = parseMindSpacePublicUrl(publicUrl);
  const ownerKey = parsed.ownerKey;
  let userId = ownerKey;
  const [users] = await pool.query(
    `SELECT id, username FROM h5_users
     WHERE id = ? OR username = ?
     LIMIT 1`,
    [ownerKey, ownerKey],
  );
  if (users[0]?.id) userId = users[0].id;
  const [assets] = await pool.query(
    `SELECT id FROM h5_assets
     WHERE user_id = ? AND workspace_relative_path = ?
     ORDER BY updated_at DESC
     LIMIT 1`,
    [userId, parsed.relativePath],
  );
  return {
    userId,
    relativePath: parsed.relativePath,
    assetId: assets[0]?.id ?? '',
  };
}

async function loadTrace(pool, args) {
  let userId = args.userId;
  let assetId = args.assetId;
  let relativePath = '';
  if (args.publicUrl) {
    const resolved = await resolvePublicUrlTarget(pool, args.publicUrl);
    userId = userId || resolved.userId;
    assetId = assetId || resolved.assetId;
    relativePath = resolved.relativePath;
  }

  const packageClauses = [];
  const packageValues = [];
  if (args.packageId) {
    packageClauses.push('p.id = ?');
    packageValues.push(args.packageId);
  }
  if (args.sessionId) {
    packageClauses.push('p.session_id = ?');
    packageValues.push(args.sessionId);
  }
  if (userId) {
    packageClauses.push('p.user_id = ?');
    packageValues.push(userId);
  }
  if (assetId) {
    packageClauses.push(
      `p.id IN (
        SELECT package_id FROM h5_conversation_artifacts WHERE asset_id = ?
      )`,
    );
    packageValues.push(assetId);
  }

  const [packages] = packageClauses.length
    ? await pool.query(
        `SELECT p.id, p.user_id, u.username, p.session_id, p.title,
                p.status, p.storage_prefix, p.manifest_asset_id,
                p.created_at, p.updated_at
         FROM h5_conversation_packages p
         LEFT JOIN h5_users u ON u.id = p.user_id
         WHERE ${packageClauses.join(' AND ')}
         ORDER BY p.updated_at DESC
         LIMIT 20`,
        packageValues,
      )
    : [[]];

  const packageIds = packages.map((item) => item.id);
  let artifacts = [];
  if (packageIds.length > 0) {
    const placeholders = packageIds.map(() => '?').join(',');
    [artifacts] = await pool.query(
      `SELECT ca.id, ca.package_id, ca.asset_id, ca.page_id,
              ca.publication_id, ca.agent_run_id, ca.message_id, ca.role,
              ca.artifact_kind, ca.display_name, ca.mime_type,
              ca.size_bytes, ca.storage_key, ca.canonical_url,
              ca.sort_order, ca.created_at
       FROM h5_conversation_artifacts ca
       WHERE ca.package_id IN (${placeholders})
       ORDER BY ca.package_id ASC, ca.sort_order ASC, ca.created_at ASC`,
      packageIds,
    );
  } else if (assetId) {
    [artifacts] = await pool.query(
      `SELECT ca.id, ca.package_id, ca.asset_id, ca.page_id,
              ca.publication_id, ca.agent_run_id, ca.message_id, ca.role,
              ca.artifact_kind, ca.display_name, ca.mime_type,
              ca.size_bytes, ca.storage_key, ca.canonical_url,
              ca.sort_order, ca.created_at
       FROM h5_conversation_artifacts ca
       WHERE ca.asset_id = ?
       ORDER BY ca.created_at DESC
       LIMIT 20`,
      [assetId],
    );
  }

  const assetIds = [
    ...new Set(
      [
        assetId,
        ...artifacts.map((item) => item.asset_id),
      ].filter(Boolean),
    ),
  ];
  let assets = [];
  if (assetIds.length > 0) {
    const placeholders = assetIds.map(() => '?').join(',');
    [assets] = await pool.query(
      `SELECT a.id, a.user_id, u.username, a.display_name,
              a.workspace_relative_path, a.asset_type, a.mime_type,
              a.size_bytes, a.status, a.visibility, a.updated_at
       FROM h5_assets a
       LEFT JOIN h5_users u ON u.id = a.user_id
       WHERE a.id IN (${placeholders})
       ORDER BY a.updated_at DESC`,
      assetIds,
    );
  } else if (userId && relativePath) {
    [assets] = await pool.query(
      `SELECT a.id, a.user_id, u.username, a.display_name,
              a.workspace_relative_path, a.asset_type, a.mime_type,
              a.size_bytes, a.status, a.visibility, a.updated_at
       FROM h5_assets a
       LEFT JOIN h5_users u ON u.id = a.user_id
       WHERE a.user_id = ? AND a.workspace_relative_path = ?
       ORDER BY a.updated_at DESC`,
      [userId, relativePath],
    );
  }

  return {
    target: {
      sessionId: args.sessionId || null,
      packageId: args.packageId || null,
      assetId: assetId || null,
      publicUrl: args.publicUrl || null,
      userId: userId || null,
      relativePath: relativePath || null,
    },
    packages,
    artifacts,
    assets,
  };
}

function printTrace(trace) {
  console.log('MindSpace trace target:');
  console.log(JSON.stringify(trace.target, null, 2));
  console.log(`packages: ${trace.packages.length}`);
  for (const item of trace.packages) {
    console.log(
      `- ${item.id} session=${item.session_id} user=${item.username ?? item.user_id} updated=${item.updated_at}`,
    );
  }
  console.log(`artifacts: ${trace.artifacts.length}`);
  for (const item of trace.artifacts) {
    console.log(
      `- ${item.id} kind=${item.artifact_kind} package=${item.package_id} asset=${item.asset_id ?? '-'} url=${item.canonical_url ?? '-'}`,
    );
  }
  console.log(`assets: ${trace.assets.length}`);
  for (const item of trace.assets) {
    console.log(
      `- ${item.id} ${item.workspace_relative_path ?? '-'} status=${item.status} size=${item.size_bytes ?? 0}`,
    );
  }
}

const args = parseArgs(process.argv);
const pool = createPoolFromEnv();
try {
  const trace = await loadTrace(pool, args);
  if (args.json) console.log(JSON.stringify(trace, null, 2));
  else printTrace(trace);
} finally {
  await pool.end();
}