import { execFileSync } from 'node:child_process';
import { resolve } from 'node:path';
import { E2E } from '../../playwright.config';

/**
 * Direct access to the E2E database.
 *
 * Shelling out to `psql` rather than adding a `pg` dependency to the frontend
 * workspace: the suite needs a handful of reads and a cleanup delete, and a
 * driver in `emaui/package.json` would follow the frontend build around
 * forever for the sake of them.
 */
const PSQL_ENV = {
  ...process.env,
  PGPASSWORD: process.env.E2E_DB_PASSWORD ?? 'TradingTria@2090',
  PGOPTIONS: `--search_path=${process.env.E2E_DB_SCHEMA ?? 'ema'},public`,
};

/**
 * Where `psql` lives.
 *
 * A local client is used when there is one. When there is not — Postgres
 * running only as a container is the common setup — the same query goes
 * through `docker compose exec` instead, so the suite does not require a
 * developer to install a database client for the sake of a few assertions.
 *
 * Set `E2E_DB_CONTAINER` to the compose service name to force the container
 * path, or `E2E_PSQL=1` to insist on a local binary.
 */
const DB_CONTAINER = process.env.E2E_DB_CONTAINER ?? 'ema-postgres';
const COMPOSE_DIR =
  process.env.E2E_COMPOSE_DIR ??
  // Anchored on the repo root rather than this file: Playwright may load the
  // suite as ESM, where `__dirname` does not exist. `process.cwd()` is the
  // emaui workspace when the config is run from there.
  resolve(process.cwd(), '../emaapi');

function hasLocalPsql(): boolean {
  if (process.env.E2E_PSQL === '1') return true;
  try {
    execFileSync('psql', ['--version'], { stdio: 'ignore' });
    return true;
  } catch {
    return false;
  }
}

const USE_CONTAINER = !hasLocalPsql();

export function sql(query: string): string[][] {
  // Queries name EMA tables unqualified (`license_applications`), which only
  // resolves with the app's own schema on the path. `iam.` stays explicit.
  //
  // Passed as a connection option rather than a leading `SET` statement: psql
  // prints a result row per command, and a `SET` would land in every caller's
  // rows as a phantom first entry.
  const schema = process.env.E2E_DB_SCHEMA ?? 'ema';
  const psqlArgs = [
    '-U', process.env.E2E_DB_USER ?? 'postgres',
    '-d', E2E.database,
    '-v', 'ON_ERROR_STOP=1',
    '-tAF', '\t',
    '-c', query,
  ];

  const out = USE_CONTAINER
    ? execFileSync(
        'docker',
        [
          'compose', 'exec', '-T',
          // `exec` does not forward the caller's environment, so the schema
          // search path has to be handed across explicitly.
          '-e', `PGOPTIONS=${PSQL_ENV.PGOPTIONS}`,
          DB_CONTAINER, 'psql', ...psqlArgs,
        ],
        { cwd: COMPOSE_DIR, env: PSQL_ENV, encoding: 'utf8' },
      )
    : execFileSync(
        'psql',
        [
          '-h', process.env.E2E_DB_HOST ?? 'localhost',
          '-p', process.env.E2E_DB_PORT ?? '5432',
          ...psqlArgs,
        ],
        { env: PSQL_ENV, encoding: 'utf8' },
      );
  return out
    .split('\n')
    .filter((line) => line.trim() !== '')
    .map((line) => line.split('\t'));
}

/** First column of the first row, or null when the query found nothing. */
export function sqlValue(query: string): string | null {
  const rows = sql(query);
  return rows.length > 0 ? rows[0][0] : null;
}

/** Removes everything a run created, so the next run starts from the same place. */
export function deleteApplicant(email: string): void {
  const safe = email.replace(/'/g, "''");
  const userId = sqlValue(`SELECT id FROM iam.users WHERE email = '${safe}'`);
  if (!userId) return;

  sql(`
    DELETE FROM attachment_files WHERE attachment_id IN (
      SELECT id FROM attachments WHERE owner_type = 'SEAFARER_REGISTRATION'
        AND owner_id IN (SELECT id FROM seafarer_registrations WHERE applicant_user_id = '${userId}')
    );
    DELETE FROM attachments WHERE owner_type = 'SEAFARER_REGISTRATION'
      AND owner_id IN (SELECT id FROM seafarer_registrations WHERE applicant_user_id = '${userId}');
    DELETE FROM seafarer_registrations WHERE applicant_user_id = '${userId}';
    DELETE FROM application_payments WHERE document_id IN (SELECT id FROM seafarer_documents WHERE applicant_user_id = '${userId}');
    DELETE FROM seafarer_documents WHERE applicant_user_id = '${userId}';
    DELETE FROM licenses WHERE holder_user_id = '${userId}';
    DELETE FROM license_applications WHERE applicant_user_id = '${userId}';
    DELETE FROM profile_operator_types
      WHERE profile_id IN (SELECT id FROM profiles WHERE user_id = '${userId}');
    WITH gone AS (
      DELETE FROM profiles WHERE user_id = '${userId}' RETURNING address_id
    )
    DELETE FROM addresses WHERE id IN (SELECT address_id FROM gone WHERE address_id IS NOT NULL);
    DELETE FROM iam.notifications WHERE recipient_id = '${userId}';
    DELETE FROM iam.user_verifications WHERE user_id = '${userId}';
    DELETE FROM iam.user_credentials WHERE user_id = '${userId}';
    DELETE FROM iam.user_roles WHERE user_id = '${userId}';
    DELETE FROM iam.users WHERE id = '${userId}';
  `);
}
