import { createHash, randomBytes, randomUUID } from 'node:crypto';
import { spawn } from 'node:child_process';

interface SeededCredential {
  apiKey: string;
  apiKeyId: string;
  userId: string;
}

type SqlExecutor = (sql: string) => Promise<void>;

function literal(value: string): string {
  return `'${value.replaceAll("'", "''")}'`;
}

function oracleExecutor(host: string): SqlExecutor {
  return (sql) => new Promise((resolve, reject) => {
    const child = spawn('ssh', [
      `root@${host}`,
      "su - press-api -c 'podman exec -i tpz-postgres psql -v ON_ERROR_STOP=1 -U translate_user -d translate_db'",
    ], { stdio: ['pipe', 'ignore', 'pipe'] });
    let error = '';
    child.stderr.setEncoding('utf8');
    child.stderr.on('data', (chunk: string) => { error += chunk; });
    child.on('error', reject);
    child.on('close', (code) => code === 0 ? resolve() : reject(new Error(`Oracle database command failed (${code}): ${error.trim()}`)));
    child.stdin.end(sql);
  });
}

export class LegacyFixtureSeeder {
  private readonly seeded: SeededCredential[] = [];
  private executor?: SqlExecutor;

  constructor(private readonly env: NodeJS.ProcessEnv, executor?: SqlExecutor) {
    this.executor = executor;
  }

  async initialize(): Promise<void> {
    if (this.executor) return;
    const host = this.env.LEGACY_ORACLE_DB_HOST ?? '100.116.176.87';
    this.executor = oracleExecutor(host);
  }

  async seedCredential(plugin: 'international' | 'multilingual', credits: number, label: string): Promise<SeededCredential> {
    if (!this.executor) throw new Error('Legacy fixture seeder is not initialized');
    const namespace = `cap_oracle_${Date.now()}_${randomBytes(4).toString('hex')}`;
    const userId = randomUUID();
    const subscriptionId = randomUUID();
    const apiKeyId = randomUUID();
    const creditId = randomUUID();
    const apiKey = `sk_test_${randomBytes(24).toString('hex')}`;
    const keyHash = createHash('sha256').update(apiKey).digest('hex');
    const sql = `BEGIN;
INSERT INTO users (id, email, password_hash, email_verified, status, created_at, updated_at)
VALUES (${literal(userId)}::uuid, ${literal(`${namespace}@example.invalid`)}, ${literal(namespace)}, true, 'active', NOW(), NOW());
INSERT INTO subscriptions (id, user_id, plan_tier, billing_cycle, status, paypal_subscription_id, current_period_start, current_period_end, cancel_at_period_end, created_at, updated_at, plugin)
VALUES (${literal(subscriptionId)}::uuid, ${literal(userId)}::uuid, 'starter', 'monthly', 'active', ${literal(`${namespace}_${label}`)}, NOW(), NOW() + INTERVAL '1 day', false, NOW(), NOW(), ${literal(plugin)});
INSERT INTO api_keys (id, user_id, key_hash, prefix, name, is_active, created_at)
VALUES (${literal(apiKeyId)}::uuid, ${literal(userId)}::uuid, ${literal(keyHash)}, ${literal(apiKey.slice(0, 8))}, ${literal(`${namespace}_${label}`)}, true, NOW());
INSERT INTO credit_transactions (id, user_id, type, amount, balance_after, description, created_at)
VALUES (${literal(creditId)}::uuid, ${literal(userId)}::uuid, 'allocation', ${credits}, ${credits}, ${literal(`${namespace}_${label}`)}, NOW());
COMMIT;
`;
    await this.executor(sql);
    const fixture = { apiKey, apiKeyId, userId };
    this.seeded.push(fixture);
    return fixture;
  }

  async revoke(fixture: SeededCredential): Promise<void> {
    if (!this.executor) throw new Error('Legacy fixture seeder is not initialized');
    await this.executor(`UPDATE api_keys SET is_active = false WHERE id = ${literal(fixture.apiKeyId)}::uuid;\n`);
  }

  async cleanup(): Promise<void> {
    if (!this.executor || this.seeded.length === 0) return;
    const ids = this.seeded.map(({ userId }) => `${literal(userId)}::uuid`).join(', ');
    await this.executor(`DELETE FROM users WHERE id IN (${ids});\n`);
    this.seeded.length = 0;
  }
}