#!/usr/bin/env bun
// Idempotent additive migration for platform-worker persistent retry state.
import mariadb from 'mariadb';

const url = process.env.DATABASE_URL;
if (!url) throw new Error('DATABASE_URL is not set');
const match = url.match(/^[a-z]+:\/\/([^:@]+):(.*)@([^:/@]+):?(\d+)?\/([^?]+)/);
if (!match)
  throw new Error('DATABASE_URL does not match user:pass@host:port/db shape');
const [, user, password, urlHost, urlPort, database] = match;
const pool = mariadb.createPool({
  host: process.env.MIGRATE_TUNNEL_HOST || urlHost,
  port: Number(process.env.MIGRATE_TUNNEL_PORT || urlPort || 3306),
  user: decodeURIComponent(user),
  password: decodeURIComponent(password),
  database,
  connectionLimit: 1,
  connectTimeout: 15_000,
});
const conn = await pool.getConnection();

async function exists(kind: 'column' | 'index', name: string) {
  const table =
    kind === 'column'
      ? 'information_schema.columns'
      : 'information_schema.statistics';
  const field = kind === 'column' ? 'column_name' : 'index_name';
  const rows = await conn.query(
    `SELECT COUNT(*) n FROM ${table} WHERE table_schema = DATABASE() AND table_name = 'tasks' AND ${field} = ?`,
    [name],
  );
  return Number(rows[0].n) > 0;
}

try {
  for (const [column, ddl] of [
    ['nextAttemptAt', 'ADD COLUMN nextAttemptAt DATETIME(0) NULL'],
    ['lastError', 'ADD COLUMN lastError VARCHAR(500) NULL'],
    ['lastErrorAt', 'ADD COLUMN lastErrorAt DATETIME(0) NULL'],
  ] as const) {
    if (await exists('column', column)) console.log(`SKIP tasks.${column}`);
    else {
      await conn.query(`ALTER TABLE tasks ${ddl}`);
      console.log(`ADDED tasks.${column}`);
    }
  }
  if (await exists('index', 'TasksPlatformRetryIndex')) {
    console.log('SKIP tasks.TasksPlatformRetryIndex');
  } else {
    await conn.query(
      'ALTER TABLE tasks ADD INDEX TasksPlatformRetryIndex (nodeId, nextAttemptAt, id)',
    );
    console.log('ADDED tasks.TasksPlatformRetryIndex');
  }
} finally {
  conn.release();
  await pool.end();
}