import { db, dbRemote } from '../db.js';

const KV_TABLE = 'public.kv_store_7249dcd9';

// ✅ Replica cada escrita local para a base remota (ex: cPanel), quando
// configurada — "fire and forget": nunca aguardado pelo caller e nunca
// lanca excepcao, para uma falha (ou lentidao) na ligacao remota nunca
// atrasar/bloquear/reprovar uma operacao local. Erros ficam so registados.
function mirrorToRemote(operation: string, run: () => Promise<unknown>): void {
  if (!dbRemote) return;

  run().catch((error) => {
    console.error(`[kv-store sync] Falha ao replicar '${operation}' na base remota:`, error);
  });
}

interface ValueRow<T> {
  value: T;
}

interface KeyValueRow<T> extends ValueRow<T> {
  key: string;
}

interface MultipleValueRow<T> extends ValueRow<T | null> {
  found: boolean;
}

function serializeJson(value: unknown): string {
  const serialized = JSON.stringify(value);

  if (serialized === undefined) {
    throw new TypeError('KV values must be representable as JSON.');
  }

  return serialized;
}

function escapeLikePattern(value: string): string {
  return value
    .replaceAll('\\', '\\\\')
    .replaceAll('%', '\\%')
    .replaceAll('_', '\\_');
}

function prefixPattern(prefix: string): string {
  return `${escapeLikePattern(prefix)}%`;
}

// Set stores a key-value pair in the database.
export async function set(key: string, value: any): Promise<void> {
  const serialized = serializeJson(value);

  await db.query(
    `INSERT INTO ${KV_TABLE} (key, value)
     VALUES ($1, $2::jsonb)
     ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value`,
    [key, serialized],
  );

  mirrorToRemote('set', () =>
    dbRemote!.query(
      `INSERT INTO ${KV_TABLE} (key, value)
       VALUES ($1, $2::jsonb)
       ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value`,
      [key, serialized],
    ),
  );
}

// Get keeps the original helper's missing-key contract: undefined, not null.
export async function get<T = any>(key: string): Promise<T | undefined> {
  const result = await db.query<ValueRow<T>>(
    `SELECT value
     FROM ${KV_TABLE}
     WHERE key = $1`,
    [key],
  );

  return result.rows[0]?.value;
}

// Delete deletes a key-value pair. Missing keys are intentionally ignored.
export async function del(key: string): Promise<void> {
  await db.query(`DELETE FROM ${KV_TABLE} WHERE key = $1`, [key]);

  mirrorToRemote('del', () =>
    dbRemote!.query(`DELETE FROM ${KV_TABLE} WHERE key = $1`, [key]),
  );
}

// Sets a complete batch atomically in one PostgreSQL statement.
export async function mset(keys: string[], values: any[]): Promise<void> {
  if (keys.length !== values.length) {
    throw new RangeError('mset requires one value for every key.');
  }

  if (keys.length === 0) {
    return;
  }

  const rows = keys.map(
    (key, index) =>
      `{"key":${JSON.stringify(key)},"value":${serializeJson(values[index])}}`,
  );
  const batch = `[${rows.join(',')}]`;

  await db.query(
    `INSERT INTO ${KV_TABLE} (key, value)
     SELECT batch.key, batch.value
     FROM jsonb_to_recordset($1::jsonb) AS batch(key text, value jsonb)
     ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value`,
    [batch],
  );

  mirrorToRemote('mset', () =>
    dbRemote!.query(
      `INSERT INTO ${KV_TABLE} (key, value)
       SELECT batch.key, batch.value
       FROM jsonb_to_recordset($1::jsonb) AS batch(key text, value jsonb)
       ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value`,
      [batch],
    ),
  );
}

/**
 * Gets values in the same order as the requested keys.
 *
 * Unlike the former unordered PostgREST query, this result has exactly one
 * slot per requested key. A missing key is represented by `undefined`, just
 * like `get`, and duplicate requested keys produce duplicate result slots.
 */
export async function mget<T = any>(
  keys: string[],
): Promise<Array<T | undefined>> {
  if (keys.length === 0) {
    return [];
  }

  const result = await db.query<MultipleValueRow<T>>(
    `SELECT stored.key IS NOT NULL AS found, stored.value
     FROM unnest($1::text[]) WITH ORDINALITY AS requested(key, position)
     LEFT JOIN ${KV_TABLE} AS stored ON stored.key = requested.key
     ORDER BY requested.position`,
    [keys],
  );

  return result.rows.map((row) => (row.found ? (row.value as T) : undefined));
}

// Deletes a complete batch atomically in one PostgreSQL statement.
export async function mdel(keys: string[]): Promise<void> {
  if (keys.length === 0) {
    return;
  }

  await db.query(`DELETE FROM ${KV_TABLE} WHERE key = ANY($1::text[])`, [keys]);

  mirrorToRemote('mdel', () =>
    dbRemote!.query(`DELETE FROM ${KV_TABLE} WHERE key = ANY($1::text[])`, [keys]),
  );
}

// Search for values by a literal prefix. JSON null values match legacy filtering.
export async function getByPrefix<T = any>(prefix: string): Promise<T[]> {
  const result = await db.query<ValueRow<T | null>>(
    `SELECT value
     FROM ${KV_TABLE}
     WHERE key LIKE $1 ESCAPE E'\\\\'
     ORDER BY key`,
    [prefixPattern(prefix)],
  );

  return result.rows
    .map((row) => row.value)
    .filter((value): value is T => value != null);
}

// Search by a literal prefix, returning the matching keys and non-null values.
export async function getByPrefixWithKeys<T = any>(
  prefix: string,
): Promise<Array<{ key: string; value: T }>> {
  const result = await db.query<KeyValueRow<T | null>>(
    `SELECT key, value
     FROM ${KV_TABLE}
     WHERE key LIKE $1 ESCAPE E'\\\\'
     ORDER BY key`,
    [prefixPattern(prefix)],
  );

  return result.rows.filter(
    (row): row is KeyValueRow<T> => row.value != null,
  );
}

// Atomically creates a key only when no row already owns it.
export async function createIfAbsent(
  key: string,
  value: any,
): Promise<{ created: boolean; error?: string }> {
  const serialized = serializeJson(value);
  const result = await db.query<{ key: string }>(
    `INSERT INTO ${KV_TABLE} (key, value)
     VALUES ($1, $2::jsonb)
     ON CONFLICT (key) DO NOTHING
     RETURNING key`,
    [key, serialized],
  );

  const created = result.rowCount === 1;

  if (created) {
    mirrorToRemote('createIfAbsent', () =>
      dbRemote!.query(
        `INSERT INTO ${KV_TABLE} (key, value)
         VALUES ($1, $2::jsonb)
         ON CONFLICT (key) DO NOTHING`,
        [key, serialized],
      ),
    );
  }

  return { created };
}

export async function deleteKey(key: string): Promise<void> {
  await del(key);
}

// getValue keeps the atomic helper's missing-key contract: null, not undefined.
export async function getValue<T = any>(key: string): Promise<T | null> {
  const value = await get<T>(key);
  return value === undefined ? null : value;
}

export async function listByPrefix<T = any>(
  prefix: string,
): Promise<Array<{ key: string; value: T }>> {
  const result = await db.query<KeyValueRow<T>>(
    `SELECT key, value
     FROM ${KV_TABLE}
     WHERE key LIKE $1 ESCAPE E'\\\\'
     ORDER BY key`,
    [prefixPattern(prefix)],
  );

  return result.rows;
}

// The predicate and deletion execute in one statement, so the token cannot change between them.
export async function deleteKeyIfTokenMatches(
  key: string,
  token: string,
): Promise<boolean> {
  const result = await db.query<{ key: string }>(
    `DELETE FROM ${KV_TABLE}
     WHERE key = $1
       AND value ->> 'token' = $2
     RETURNING key`,
    [key, token],
  );

  const deleted = result.rowCount === 1;

  // ✅ A base local ja confirmou que o token bate certo - replica-se so a
  // eliminacao da chave (sem repetir a condicao do token) para a base
  // remota nunca ficar dessincronizada so por ter um valor ligeiramente
  // diferente nesse momento.
  if (deleted) {
    mirrorToRemote('deleteKeyIfTokenMatches', () =>
      dbRemote!.query(`DELETE FROM ${KV_TABLE} WHERE key = $1`, [key]),
    );
  }

  return deleted;
}