import { randomUUID } from 'node:crypto';

import { hash } from 'bcryptjs';
import type { Pool, PoolClient, QueryResult, QueryResultRow } from 'pg';

import {
  AuthError,
  type AuthenticatedUser,
  type IssuedSession,
  authService,
} from '../auth/service.js';
import { isUsablePassword } from '../auth/password.js';
import { db } from '../db.js';
import { storageService } from '../storage/service.js';

const KV_TABLE_NAME = 'kv_store_7249dcd9';
const KV_TABLE_SQL = 'public.kv_store_7249dcd9';

export interface CompatibilityError {
  code: string;
  message: string;
  details: string;
  hint: string;
  name?: string;
  status?: number;
}

export interface CompatibilityResponse<T = any> {
  data: T | null;
  error: CompatibilityError | null;
  count: number | null;
}

type Operation = 'select' | 'insert' | 'upsert' | 'update' | 'delete';
type SingularMode = 'single' | 'maybeSingle' | null;
type Projection = 'key' | 'value';

interface Filter {
  operator: 'eq' | 'neq' | 'in' | 'like' | 'contains' | 'or';
  column: string;
  value: unknown;
}

interface OrderClause {
  column: string;
  ascending: boolean;
  nullsFirst: boolean | null;
}

interface Selector {
  kind: 'text' | 'jsonb';
  column: Projection;
  jsonOperator: '->' | '->>' | null;
  path: string | null;
}

class QueryBuildError extends Error {
  constructor(
    readonly code: string,
    message: string,
    readonly details = '',
    readonly hint = '',
  ) {
    super(message);
  }
}

function mapError(error: unknown): CompatibilityError {
  if (error instanceof QueryBuildError) {
    return {
      code: error.code,
      message: error.message,
      details: error.details,
      hint: error.hint,
    };
  }

  const candidate = error as {
    code?: unknown;
    message?: unknown;
    detail?: unknown;
    hint?: unknown;
  };

  return {
    code: typeof candidate?.code === 'string' ? candidate.code : 'LOCAL_DB_ERROR',
    message:
      typeof candidate?.message === 'string'
        ? candidate.message
        : 'The local PostgreSQL query failed.',
    details: typeof candidate?.detail === 'string' ? candidate.detail : '',
    hint: typeof candidate?.hint === 'string' ? candidate.hint : '',
  };
}

function failure(error: unknown): CompatibilityResponse<never> {
  return { data: null, error: mapError(error), count: null };
}

function serializeJson(value: unknown): string {
  let serialized: string | undefined;

  try {
    serialized = JSON.stringify(value);
  } catch (error) {
    throw new QueryBuildError(
      'LOCAL_INVALID_JSON',
      error instanceof Error ? error.message : 'The value is not valid JSON.',
    );
  }

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

  return serialized;
}

function parseProjection(columns: string): Projection[] {
  const normalized = columns.trim();
  if (normalized === '' || normalized === '*') return ['key', 'value'];

  const result: Projection[] = [];
  for (const rawColumn of normalized.split(',')) {
    const column = rawColumn.trim();
    if (column !== 'key' && column !== 'value') {
      throw new QueryBuildError(
        'LOCAL_COLUMN_NOT_ALLOWED',
        `Column projection is not allowed for the local KV table: ${column}`,
      );
    }
    if (!result.includes(column)) result.push(column);
  }

  if (result.length === 0) {
    throw new QueryBuildError('LOCAL_INVALID_SELECT', 'At least one column is required.');
  }

  return result;
}

function parseSelector(column: string): Selector {
  if (column === 'key') {
    return { kind: 'text', column: 'key', jsonOperator: null, path: null };
  }
  if (column === 'value') {
    return { kind: 'jsonb', column: 'value', jsonOperator: null, path: null };
  }

  const jsonPath = /^value(->>|->)([A-Za-z0-9_-]+)$/.exec(column);
  if (jsonPath?.[1] && jsonPath[2]) {
    const operator = jsonPath[1] as '->' | '->>';
    return {
      kind: operator === '->>' ? 'text' : 'jsonb',
      column: 'value',
      jsonOperator: operator,
      path: jsonPath[2],
    };
  }

  throw new QueryBuildError(
    'LOCAL_COLUMN_NOT_ALLOWED',
    `Filter column is not allowed for the local KV table: ${column}`,
  );
}

function compileSelector(selector: Selector, parameters: unknown[]): string {
  const columnSql = selector.column === 'key' ? '"key"' : '"value"';
  if (!selector.jsonOperator || selector.path === null) return columnSql;

  parameters.push(selector.path);
  return `${columnSql} ${selector.jsonOperator} $${parameters.length}`;
}

function compileComparison(
  filter: Filter,
  parameters: unknown[],
): string {
  const selector = parseSelector(filter.column);
  const expression = compileSelector(selector, parameters);

  if (filter.value === null) {
    return `${expression} IS ${filter.operator === 'neq' ? 'NOT ' : ''}NULL`;
  }

  const value =
    selector.kind === 'jsonb' ? serializeJson(filter.value) : filter.value;
  parameters.push(value);
  const cast = selector.kind === 'jsonb' ? '::jsonb' : '';
  return `${expression} ${filter.operator === 'eq' ? '=' : '<>'} $${parameters.length}${cast}`;
}

function compileIn(filter: Filter, parameters: unknown[]): string {
  if (!Array.isArray(filter.value)) {
    throw new QueryBuildError('LOCAL_INVALID_FILTER', 'in() requires an array.');
  }
  if (filter.value.length === 0) return 'FALSE';

  const selector = parseSelector(filter.column);
  const expression = compileSelector(selector, parameters);
  if (selector.kind === 'text') {
    parameters.push(filter.value.map((value) => String(value)));
    return `${expression} = ANY($${parameters.length}::text[])`;
  }

  const placeholders = filter.value.map((value) => {
    parameters.push(serializeJson(value));
    return `$${parameters.length}::jsonb`;
  });
  return `${expression} IN (${placeholders.join(', ')})`;
}

function compileLike(filter: Filter, parameters: unknown[]): string {
  const selector = parseSelector(filter.column);
  if (selector.kind !== 'text') {
    throw new QueryBuildError('LOCAL_INVALID_FILTER', 'like() requires a text column.');
  }

  const expression = compileSelector(selector, parameters);
  parameters.push(String(filter.value));
  return `${expression} LIKE $${parameters.length}`;
}

function compileContains(filter: Filter, parameters: unknown[]): string {
  const selector = parseSelector(filter.column);
  if (selector.kind !== 'jsonb') {
    throw new QueryBuildError(
      'LOCAL_INVALID_FILTER',
      'contains() requires a JSONB column or JSON path.',
    );
  }

  const expression = compileSelector(selector, parameters);
  parameters.push(serializeJson(filter.value));
  return `${expression} @> $${parameters.length}::jsonb`;
}

function compileOr(expression: string, parameters: unknown[]): string {
  const conditions = expression.split(',').map((condition) => condition.trim());
  if (conditions.length === 0 || conditions.some((condition) => condition === '')) {
    throw new QueryBuildError('LOCAL_INVALID_FILTER', 'or() expression is empty.');
  }

  const compiled = conditions.map((condition) => {
    const match = /^key\.(like|ilike)\.(.+)$/.exec(condition);
    if (!match?.[1] || match[2] === undefined) {
      throw new QueryBuildError(
        'LOCAL_UNSUPPORTED_FILTER',
        `Unsupported local or() condition: ${condition}`,
        'Only key.like and key.ilike conditions used by the legacy backend are supported.',
      );
    }

    parameters.push(match[2]);
    return `"key" ${match[1] === 'ilike' ? 'ILIKE' : 'LIKE'} $${parameters.length}`;
  });

  return `(${compiled.join(' OR ')})`;
}

function compileWhere(filters: Filter[], parameters: unknown[]): string {
  if (filters.length === 0) return '';

  const conditions = filters.map((filter) => {
    switch (filter.operator) {
      case 'eq':
      case 'neq':
        return compileComparison(filter, parameters);
      case 'in':
        return compileIn(filter, parameters);
      case 'like':
        return compileLike(filter, parameters);
      case 'contains':
        return compileContains(filter, parameters);
      case 'or':
        return compileOr(String(filter.value), parameters);
    }
  });

  return ` WHERE ${conditions.join(' AND ')}`;
}

function readBooleanOption(
  options: unknown,
  key: string,
  fallback: boolean,
): boolean {
  if (!options || typeof options !== 'object') return fallback;
  const value = (options as Record<string, unknown>)[key];
  return typeof value === 'boolean' ? value : fallback;
}

function wantsCount(options: unknown): boolean {
  if (!options || typeof options !== 'object') return false;
  const count = (options as Record<string, unknown>).count;
  return count === 'exact' || count === 'planned' || count === 'estimated';
}

function requireKvRow(value: unknown): { key: string; value: unknown } {
  if (!value || typeof value !== 'object' || Array.isArray(value)) {
    throw new QueryBuildError('LOCAL_INVALID_PAYLOAD', 'A KV row must be an object.');
  }

  const row = value as Record<string, unknown>;
  const unexpected = Object.keys(row).filter((column) => column !== 'key' && column !== 'value');
  if (unexpected.length > 0) {
    throw new QueryBuildError(
      'LOCAL_COLUMN_NOT_ALLOWED',
      `KV payload contains unsupported columns: ${unexpected.join(', ')}`,
    );
  }
  if (typeof row.key !== 'string') {
    throw new QueryBuildError('LOCAL_INVALID_PAYLOAD', 'KV row key must be a string.');
  }
  if (!Object.hasOwn(row, 'value')) {
    throw new QueryBuildError('LOCAL_INVALID_PAYLOAD', 'KV row value is required.');
  }

  return { key: row.key, value: row.value };
}

export class LocalPostgrestBuilder
  implements PromiseLike<CompatibilityResponse<any>>
{
  private operation: Operation | null = null;
  private payload: unknown = null;
  private projection: Projection[] = ['key', 'value'];
  private returning = false;
  private filters: Filter[] = [];
  private orders: OrderClause[] = [];
  private singularMode: SingularMode = null;
  private countRequested = false;
  private head = false;
  private limitValue: number | null = null;
  private offsetValue: number | null = null;
  private ignoreDuplicates = false;
  private buildError: QueryBuildError | null = null;

  constructor(table: string) {
    if (table !== KV_TABLE_NAME) {
      this.buildError = new QueryBuildError(
        'LOCAL_TABLE_NOT_ALLOWED',
        `The local Supabase compatibility layer does not allow table: ${table}`,
      );
    }
  }

  select(columns = '*', options?: unknown): this {
    try {
      this.projection = parseProjection(columns);
    } catch (error) {
      this.rememberError(error);
    }

    if (this.operation === null || this.operation === 'select') {
      this.operation = 'select';
    } else {
      this.returning = true;
    }
    this.countRequested ||= wantsCount(options);
    this.head ||= readBooleanOption(options, 'head', false);
    return this;
  }

  insert(payload: unknown, options?: unknown): this {
    this.operation = 'insert';
    this.payload = payload;
    this.countRequested ||= wantsCount(options);
    return this;
  }

  upsert(payload: unknown, options?: unknown): this {
    this.operation = 'upsert';
    this.payload = payload;
    this.countRequested ||= wantsCount(options);
    this.ignoreDuplicates = readBooleanOption(options, 'ignoreDuplicates', false);

    if (options && typeof options === 'object') {
      const conflict = (options as Record<string, unknown>).onConflict;
      if (conflict !== undefined && conflict !== 'key') {
        this.rememberError(
          new QueryBuildError(
            'LOCAL_UNSUPPORTED_CONFLICT',
            'Only onConflict: "key" is supported for the local KV table.',
          ),
        );
      }
    }
    return this;
  }

  update(payload: unknown, options?: unknown): this {
    this.operation = 'update';
    this.payload = payload;
    this.countRequested ||= wantsCount(options);
    return this;
  }

  delete(options?: unknown): this {
    this.operation = 'delete';
    this.countRequested ||= wantsCount(options);
    return this;
  }

  eq(column: string, value: unknown): this {
    this.filters.push({ operator: 'eq', column, value });
    return this;
  }

  neq(column: string, value: unknown): this {
    this.filters.push({ operator: 'neq', column, value });
    return this;
  }

  in(column: string, values: unknown[]): this {
    this.filters.push({ operator: 'in', column, value: values });
    return this;
  }

  like(column: string, pattern: string): this {
    this.filters.push({ operator: 'like', column, value: pattern });
    return this;
  }

  contains(column: string, value: unknown): this {
    this.filters.push({ operator: 'contains', column, value });
    return this;
  }

  or(expression: string): this {
    this.filters.push({ operator: 'or', column: '', value: expression });
    return this;
  }

  order(column: string, options?: unknown): this {
    this.orders.push({
      column,
      ascending: readBooleanOption(options, 'ascending', true),
      nullsFirst:
        options && typeof options === 'object' &&
        typeof (options as Record<string, unknown>).nullsFirst === 'boolean'
          ? ((options as Record<string, unknown>).nullsFirst as boolean)
          : null,
    });
    return this;
  }

  limit(count: number): this {
    if (!Number.isSafeInteger(count) || count < 0) {
      this.rememberError(
        new QueryBuildError('LOCAL_INVALID_RANGE', 'limit() requires a non-negative integer.'),
      );
    } else {
      this.limitValue = count;
    }
    return this;
  }

  range(from: number, to: number): this {
    if (
      !Number.isSafeInteger(from) ||
      !Number.isSafeInteger(to) ||
      from < 0 ||
      to < from
    ) {
      this.rememberError(
        new QueryBuildError(
          'LOCAL_INVALID_RANGE',
          'range() requires inclusive non-negative integer bounds.',
        ),
      );
    } else {
      this.offsetValue = from;
      this.limitValue = to - from + 1;
    }
    return this;
  }

  maybeSingle(): this {
    this.singularMode = 'maybeSingle';
    return this;
  }

  single(): this {
    this.singularMode = 'single';
    return this;
  }

  then<TResult1 = CompatibilityResponse<any>, TResult2 = never>(
    onfulfilled?:
      | ((value: CompatibilityResponse<any>) => TResult1 | PromiseLike<TResult1>)
      | null,
    onrejected?: ((reason: any) => TResult2 | PromiseLike<TResult2>) | null,
  ): Promise<TResult1 | TResult2> {
    return this.execute().then(onfulfilled, onrejected);
  }

  private rememberError(error: unknown): void {
    if (this.buildError) return;
    this.buildError =
      error instanceof QueryBuildError
        ? error
        : new QueryBuildError('LOCAL_QUERY_ERROR', String(error));
  }

  private async execute(): Promise<CompatibilityResponse<any>> {
    try {
      if (this.buildError) throw this.buildError;
      if (!this.operation) {
        throw new QueryBuildError('LOCAL_MISSING_OPERATION', 'No query operation was selected.');
      }

      switch (this.operation) {
        case 'select':
          return await this.executeSelect();
        case 'insert':
        case 'upsert':
          return await this.executeInsert(this.operation === 'upsert');
        case 'update':
          return await this.executeUpdate();
        case 'delete':
          return await this.executeDelete();
      }
    } catch (error) {
      return failure(error);
    }
  }

  private projectionSql(): string {
    return this.projection.map((column) => `"${column}"`).join(', ');
  }

  private compileOrder(parameters: unknown[]): string {
    if (this.orders.length === 0) return '';

    const clauses = this.orders.map((order) => {
      const selector = parseSelector(order.column);
      const expression = compileSelector(selector, parameters);
      const nulls =
        order.nullsFirst === null ? '' : order.nullsFirst ? ' NULLS FIRST' : ' NULLS LAST';
      return `${expression} ${order.ascending ? 'ASC' : 'DESC'}${nulls}`;
    });
    return ` ORDER BY ${clauses.join(', ')}`;
  }

  private paginationSql(parameters: unknown[]): string {
    let sql = '';
    if (this.limitValue !== null) {
      parameters.push(this.limitValue);
      sql += ` LIMIT $${parameters.length}`;
    }
    if (this.offsetValue !== null) {
      parameters.push(this.offsetValue);
      sql += ` OFFSET $${parameters.length}`;
    }
    return sql;
  }

  private singularResult(
    rows: Record<string, unknown>[],
    count: number | null,
  ): CompatibilityResponse<any> {
    if (!this.singularMode) return { data: rows, error: null, count };
    if (rows.length === 0 && this.singularMode === 'maybeSingle') {
      return { data: null, error: null, count };
    }
    if (rows.length !== 1) {
      return failure(
        new QueryBuildError(
          'PGRST116',
          'JSON object requested, multiple (or no) rows returned',
          `The result contains ${rows.length} rows`,
        ),
      );
    }
    return { data: rows[0] ?? null, error: null, count };
  }

  private async executeSelect(): Promise<CompatibilityResponse<any>> {
    let count: number | null = null;
    if (this.countRequested) {
      const countParameters: unknown[] = [];
      const where = compileWhere(this.filters, countParameters);
      const result = await db.query<{ count: string }>(
        `SELECT count(*)::text AS count FROM ${KV_TABLE_SQL}${where}`,
        countParameters,
      );
      count = Number(result.rows[0]?.count ?? 0);
    }

    if (this.head) return { data: null, error: null, count };

    const parameters: unknown[] = [];
    const where = compileWhere(this.filters, parameters);
    const order = this.compileOrder(parameters);
    const pagination = this.paginationSql(parameters);
    const result = await db.query<Record<string, unknown>>(
      `SELECT ${this.projectionSql()} FROM ${KV_TABLE_SQL}${where}${order}${pagination}`,
      parameters,
    );
    return this.singularResult(result.rows, count);
  }

  private async executeInsert(upsert: boolean): Promise<CompatibilityResponse<any>> {
    const rawRows = Array.isArray(this.payload) ? this.payload : [this.payload];
    if (rawRows.length === 0) {
      return this.returning
        ? this.singularResult([], this.countRequested ? 0 : null)
        : { data: null, error: null, count: this.countRequested ? 0 : null };
    }

    const rows = rawRows.map(requireKvRow);
    const parameters: unknown[] = [];
    const values = rows.map((row) => {
      parameters.push(row.key, serializeJson(row.value));
      return `($${parameters.length - 1}, $${parameters.length}::jsonb)`;
    });
    let conflict = '';
    if (upsert) {
      conflict = this.ignoreDuplicates
        ? ' ON CONFLICT ("key") DO NOTHING'
        : ' ON CONFLICT ("key") DO UPDATE SET "value" = EXCLUDED."value"';
    }
    const returning = this.returning ? ` RETURNING ${this.projectionSql()}` : '';
    const result = await db.query<Record<string, unknown>>(
      `INSERT INTO ${KV_TABLE_SQL} ("key", "value") VALUES ${values.join(', ')}${conflict}${returning}`,
      parameters,
    );
    const count = this.countRequested ? result.rowCount ?? 0 : null;
    return this.returning
      ? this.singularResult(result.rows, count)
      : { data: null, error: null, count };
  }

  private updateAssignments(parameters: unknown[]): string {
    if (!this.payload || typeof this.payload !== 'object' || Array.isArray(this.payload)) {
      throw new QueryBuildError('LOCAL_INVALID_PAYLOAD', 'update() requires an object.');
    }

    const payload = this.payload as Record<string, unknown>;
    const unexpected = Object.keys(payload).filter(
      (column) => column !== 'key' && column !== 'value',
    );
    if (unexpected.length > 0) {
      throw new QueryBuildError(
        'LOCAL_COLUMN_NOT_ALLOWED',
        `KV payload contains unsupported columns: ${unexpected.join(', ')}`,
      );
    }

    const assignments: string[] = [];
    if (Object.hasOwn(payload, 'key')) {
      if (typeof payload.key !== 'string') {
        throw new QueryBuildError('LOCAL_INVALID_PAYLOAD', 'KV row key must be a string.');
      }
      parameters.push(payload.key);
      assignments.push(`"key" = $${parameters.length}`);
    }
    if (Object.hasOwn(payload, 'value')) {
      parameters.push(serializeJson(payload.value));
      assignments.push(`"value" = $${parameters.length}::jsonb`);
    }
    if (assignments.length === 0) {
      throw new QueryBuildError('LOCAL_INVALID_PAYLOAD', 'update() payload is empty.');
    }
    return assignments.join(', ');
  }

  private ensureMutationShape(): void {
    if (this.orders.length > 0 || this.limitValue !== null || this.offsetValue !== null) {
      throw new QueryBuildError(
        'LOCAL_UNSUPPORTED_MUTATION',
        'Ordering and pagination are only supported for local select queries.',
      );
    }
  }

  private async executeUpdate(): Promise<CompatibilityResponse<any>> {
    this.ensureMutationShape();
    const parameters: unknown[] = [];
    const assignments = this.updateAssignments(parameters);
    const where = compileWhere(this.filters, parameters);
    const returning = this.returning ? ` RETURNING ${this.projectionSql()}` : '';
    const result = await db.query<Record<string, unknown>>(
      `UPDATE ${KV_TABLE_SQL} SET ${assignments}${where}${returning}`,
      parameters,
    );
    return this.mutationResult(result);
  }

  private async executeDelete(): Promise<CompatibilityResponse<any>> {
    this.ensureMutationShape();
    const parameters: unknown[] = [];
    const where = compileWhere(this.filters, parameters);
    const returning = this.returning ? ` RETURNING ${this.projectionSql()}` : '';
    const result = await db.query<Record<string, unknown>>(
      `DELETE FROM ${KV_TABLE_SQL}${where}${returning}`,
      parameters,
    );
    return this.mutationResult(result);
  }

  private mutationResult(result: QueryResult<Record<string, unknown>>): CompatibilityResponse<any> {
    const count = this.countRequested ? result.rowCount ?? 0 : null;
    return this.returning
      ? this.singularResult(result.rows, count)
      : { data: null, error: null, count };
  }
}

type JsonObject = Record<string, unknown>;
type AuthQueryable = Pick<Pool, 'query'> | Pick<PoolClient, 'query'>;

interface AuthUserRow extends QueryResultRow {
  id: string;
  aud: string | null;
  role: string | null;
  email: string | null;
  email_confirmed_at: Date | null;
  phone: string | null;
  phone_confirmed_at: Date | null;
  confirmed_at: Date | null;
  last_sign_in_at: Date | null;
  raw_app_meta_data: unknown;
  raw_user_meta_data: unknown;
  created_at: Date;
  updated_at: Date;
  banned_until: Date | null;
  is_anonymous: boolean;
}

interface AuthIdentityRow extends QueryResultRow {
  id: string;
  user_id: string;
  identity_data: unknown;
  provider: string;
  email: string | null;
  last_sign_in_at: Date | null;
  created_at: Date;
  updated_at: Date;
}

export interface LocalAuthResponse<T> {
  data: T;
  error: CompatibilityError | null;
}

class AuthCompatibilityError extends Error {
  constructor(
    readonly code: string,
    message: string,
    readonly status: number,
  ) {
    super(message);
    this.name = 'AuthApiError';
  }
}

const AUTH_USER_COLUMNS = `id, aud, role, email, email_confirmed_at, phone,
  phone_confirmed_at, confirmed_at, last_sign_in_at, raw_app_meta_data,
  raw_user_meta_data, created_at, updated_at, banned_until, is_anonymous`;
const UUID_PATTERN =
  /^[0-9a-f]{8}-[0-9a-f]{4}-[1-8][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$/i;
const BCRYPT_COST = 10;

function asJsonObject(value: unknown): JsonObject {
  return value !== null && typeof value === 'object' && !Array.isArray(value)
    ? (value as JsonObject)
    : {};
}

function requireAuthObject(value: unknown, field: string): JsonObject {
  if (value === null || typeof value !== 'object' || Array.isArray(value)) {
    throw new AuthCompatibilityError(
      'validation_failed',
      `${field} must be an object.`,
      400,
    );
  }
  return value as JsonObject;
}

function normalizeAuthEmail(value: unknown): string {
  if (typeof value !== 'string') {
    throw new AuthCompatibilityError(
      'email_address_invalid',
      'A valid email address is required.',
      400,
    );
  }

  const email = value.trim().toLowerCase();
  if (
    email.length < 3 ||
    email.length > 320 ||
    !/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(email)
  ) {
    throw new AuthCompatibilityError(
      'email_address_invalid',
      'A valid email address is required.',
      400,
    );
  }
  return email;
}

function requirePassword(value: unknown): string {
  if (!isUsablePassword(value) || value.length < 6) {
    throw new AuthCompatibilityError(
      'weak_password',
      'Password must contain at least 6 characters and at most 72 UTF-8 bytes.',
      422,
    );
  }
  return value;
}

function requireUserId(value: unknown): string {
  if (typeof value !== 'string' || !UUID_PATTERN.test(value)) {
    throw new AuthCompatibilityError('user_id_invalid', 'User ID is invalid.', 400);
  }
  return value;
}

function parseBanDuration(value: unknown): Date | null {
  if (value === 'none') return null;
  if (typeof value !== 'string') {
    throw new AuthCompatibilityError(
      'validation_failed',
      'ban_duration must be "none" or a duration such as 24h.',
      400,
    );
  }

  const match = /^(\d+)(s|m|h|d)$/.exec(value.trim().toLowerCase());
  if (!match?.[1] || !match[2]) {
    throw new AuthCompatibilityError(
      'validation_failed',
      'ban_duration must be "none" or a duration such as 24h.',
      400,
    );
  }

  const amount = Number(match[1]);
  const units: Record<string, number> = {
    s: 1_000,
    m: 60_000,
    h: 3_600_000,
    d: 86_400_000,
  };
  const milliseconds = amount * (units[match[2]] ?? 0);
  const maximum = 200 * 366 * 86_400_000;
  if (!Number.isSafeInteger(amount) || milliseconds <= 0 || milliseconds > maximum) {
    throw new AuthCompatibilityError(
      'validation_failed',
      'ban_duration is outside the supported range.',
      400,
    );
  }

  return new Date(Date.now() + milliseconds);
}

function isoDate(value: Date | string | null): string | null {
  if (value === null) return null;
  const date = value instanceof Date ? value : new Date(value);
  return Number.isNaN(date.getTime()) ? null : date.toISOString();
}

function publicIdentity(identity: AuthIdentityRow): JsonObject {
  return {
    identity_id: identity.id,
    id: identity.id,
    user_id: identity.user_id,
    identity_data: asJsonObject(identity.identity_data),
    provider: identity.provider,
    email: identity.email,
    last_sign_in_at: isoDate(identity.last_sign_in_at),
    created_at: isoDate(identity.created_at),
    updated_at: isoDate(identity.updated_at),
  };
}

function publicAuthUser(
  user: AuthUserRow,
  identities: AuthIdentityRow[],
): JsonObject {
  return {
    id: user.id,
    aud: user.aud || 'authenticated',
    role: user.role || 'authenticated',
    email: user.email,
    email_confirmed_at: isoDate(user.email_confirmed_at),
    phone: user.phone || '',
    phone_confirmed_at: isoDate(user.phone_confirmed_at),
    confirmed_at: isoDate(user.confirmed_at),
    last_sign_in_at: isoDate(user.last_sign_in_at),
    app_metadata: asJsonObject(user.raw_app_meta_data),
    user_metadata: asJsonObject(user.raw_user_meta_data),
    identities: identities.map(publicIdentity),
    created_at: isoDate(user.created_at),
    updated_at: isoDate(user.updated_at),
    banned_until: isoDate(user.banned_until),
    is_anonymous: user.is_anonymous,
  };
}

async function loadPublicAuthUsers(
  queryable: AuthQueryable,
  users: AuthUserRow[],
): Promise<JsonObject[]> {
  if (users.length === 0) return [];

  const identities = await queryable.query<AuthIdentityRow>(
    `SELECT id, user_id, identity_data, provider, email, last_sign_in_at,
            created_at, updated_at
     FROM app_auth.identities
     WHERE user_id = ANY($1::uuid[])
     ORDER BY created_at, id`,
    [users.map((user) => user.id)],
  );
  const byUser = new Map<string, AuthIdentityRow[]>();
  for (const identity of identities.rows) {
    const current = byUser.get(identity.user_id) ?? [];
    current.push(identity);
    byUser.set(identity.user_id, current);
  }

  return users.map((user) => publicAuthUser(user, byUser.get(user.id) ?? []));
}

async function loadPublicAuthUser(
  queryable: AuthQueryable,
  userId: string,
): Promise<JsonObject> {
  const result = await queryable.query<AuthUserRow>(
    `SELECT ${AUTH_USER_COLUMNS}
     FROM app_auth.users
     WHERE id = $1 AND deleted_at IS NULL`,
    [userId],
  );
  const user = result.rows[0];
  if (!user) {
    throw new AuthCompatibilityError('user_not_found', 'User not found.', 404);
  }
  return (await loadPublicAuthUsers(queryable, [user]))[0]!;
}

async function inAuthTransaction<T>(
  work: (client: PoolClient) => Promise<T>,
): Promise<T> {
  const client = await db.connect();
  try {
    await client.query('BEGIN');
    const result = await work(client);
    await client.query('COMMIT');
    return result;
  } catch (error) {
    try {
      await client.query('ROLLBACK');
    } catch {
      // Preserve the original authentication failure.
    }
    throw error;
  } finally {
    client.release();
  }
}

function safeAuthError(error: unknown): CompatibilityError {
  if (error instanceof AuthError) {
    return {
      code: error.code,
      message: error.message,
      details: '',
      hint: '',
      name: error.name,
      status: error.status,
    };
  }
  if (error instanceof AuthCompatibilityError) {
    return {
      code: error.code,
      message: error.message,
      details: '',
      hint: '',
      name: error.name,
      status: error.status,
    };
  }

  const databaseError = error as { code?: unknown };
  if (databaseError?.code === '23505') {
    return {
      code: 'email_exists',
      message: 'A user with this email address has already been registered.',
      details: '',
      hint: '',
      name: 'AuthApiError',
      status: 422,
    };
  }

  return {
    code: 'unexpected_failure',
    message: 'The local authentication operation failed.',
    details: '',
    hint: '',
    name: 'AuthApiError',
    status: 500,
  };
}

async function authOperation<T>(
  emptyData: T,
  operation: () => Promise<T>,
): Promise<LocalAuthResponse<T>> {
  try {
    return { data: await operation(), error: null };
  } catch (error) {
    return { data: emptyData, error: safeAuthError(error) };
  }
}

async function assertEmailAvailable(
  client: PoolClient,
  email: string,
  exceptUserId?: string,
): Promise<void> {
  await client.query('SELECT pg_advisory_xact_lock(hashtextextended($1, 0))', [email]);
  const result = await client.query<{ id: string }>(
    `SELECT id
     FROM app_auth.users
     WHERE lower(email) = $1
       AND deleted_at IS NULL
       AND ($2::uuid IS NULL OR id <> $2::uuid)
     LIMIT 1`,
    [email, exceptUserId ?? null],
  );
  if (result.rowCount !== 0) {
    throw new AuthCompatibilityError(
      'email_exists',
      'A user with this email address has already been registered.',
      422,
    );
  }
}

export class LocalAuthAdminClient {
  async listUsers(options?: unknown): Promise<LocalAuthResponse<JsonObject>> {
    return authOperation(
      { users: [], aud: 'authenticated', nextPage: null, lastPage: 0, total: 0 },
      async () => {
        const input = options === undefined ? {} : requireAuthObject(options, 'options');
        const page = input.page === undefined ? 1 : Number(input.page);
        const perPage = input.perPage === undefined ? 50 : Number(input.perPage);
        if (
          !Number.isSafeInteger(page) ||
          !Number.isSafeInteger(perPage) ||
          page < 1 ||
          perPage < 1 ||
          perPage > 1_000
        ) {
          throw new AuthCompatibilityError(
            'validation_failed',
            'page and perPage must be positive integers; perPage cannot exceed 1000.',
            400,
          );
        }

        const [countResult, usersResult] = await Promise.all([
          db.query<{ count: string }>(
            `SELECT count(*)::text AS count
             FROM app_auth.users
             WHERE deleted_at IS NULL`,
          ),
          db.query<AuthUserRow>(
            `SELECT ${AUTH_USER_COLUMNS}
             FROM app_auth.users
             WHERE deleted_at IS NULL
             ORDER BY created_at, id
             LIMIT $1 OFFSET $2`,
            [perPage, (page - 1) * perPage],
          ),
        ]);
        const total = Number(countResult.rows[0]?.count ?? 0);
        const lastPage = Math.ceil(total / perPage);
        return {
          users: await loadPublicAuthUsers(db, usersResult.rows),
          aud: 'authenticated',
          nextPage: page < lastPage ? page + 1 : null,
          lastPage,
          total,
        };
      },
    );
  }

  async createUser(attributes: unknown): Promise<LocalAuthResponse<JsonObject>> {
    return authOperation<JsonObject>({ user: null }, async () => {
      const input = requireAuthObject(attributes, 'attributes');
      const email = normalizeAuthEmail(input.email);
      const password = requirePassword(input.password);
      const userMetadata =
        input.user_metadata === undefined
          ? {}
          : requireAuthObject(input.user_metadata, 'user_metadata');
      const suppliedAppMetadata =
        input.app_metadata === undefined
          ? {}
          : requireAuthObject(input.app_metadata, 'app_metadata');
      const appMetadata = {
        ...suppliedAppMetadata,
        provider: 'email',
        providers: ['email'],
      };
      const confirmed = input.email_confirm === true;
      const encryptedPassword = await hash(password, BCRYPT_COST);
      const userId = randomUUID();
      const identityId = randomUUID();

      const user = await inAuthTransaction(async (client) => {
        await assertEmailAvailable(client, email);
        const result = await client.query<AuthUserRow>(
          `INSERT INTO app_auth.users
             (id, aud, role, email, encrypted_password, email_confirmed_at,
              confirmed_at, raw_app_meta_data, raw_user_meta_data, created_at,
              updated_at, is_sso_user, is_anonymous, source_record)
           VALUES
             ($1, 'authenticated', 'authenticated', $2, $3,
              CASE WHEN $6::boolean THEN transaction_timestamp() ELSE NULL END,
              CASE WHEN $6::boolean THEN transaction_timestamp() ELSE NULL END,
              $4::jsonb, $5::jsonb, transaction_timestamp(), transaction_timestamp(),
              false, false, '{"created_by":"local-supabase-compat"}'::jsonb)
           RETURNING ${AUTH_USER_COLUMNS}`,
          [
            userId,
            email,
            encryptedPassword,
            serializeJson(appMetadata),
            serializeJson(userMetadata),
            confirmed,
          ],
        );
        await client.query(
          `INSERT INTO app_auth.identities
             (id, user_id, provider_id, identity_data, provider, email,
              created_at, updated_at, source_record)
           VALUES
             ($1::uuid, $2::uuid, ($2::uuid)::text, $3::jsonb, 'email', $4,
              transaction_timestamp(), transaction_timestamp(),
              '{"created_by":"local-supabase-compat"}'::jsonb)`,
          [
            identityId,
            userId,
            serializeJson({
              sub: userId,
              email,
              email_verified: confirmed,
              phone_verified: false,
            }),
            email,
          ],
        );
        return (await loadPublicAuthUsers(client, result.rows))[0]!;
      });

      return { user };
    });
  }

  async updateUserById(
    userIdInput: unknown,
    attributes: unknown,
  ): Promise<LocalAuthResponse<JsonObject>> {
    return authOperation<JsonObject>({ user: null }, async () => {
      const userId = requireUserId(userIdInput);
      const input = requireAuthObject(attributes, 'attributes');
      const hasEmail = Object.hasOwn(input, 'email');
      const email = hasEmail ? normalizeAuthEmail(input.email) : null;
      const hasPassword = Object.hasOwn(input, 'password');
      const encryptedPassword = hasPassword
        ? await hash(requirePassword(input.password), BCRYPT_COST)
        : null;
      const hasUserMetadata = Object.hasOwn(input, 'user_metadata');
      const userMetadata = hasUserMetadata
        ? requireAuthObject(input.user_metadata, 'user_metadata')
        : {};
      const hasAppMetadata = Object.hasOwn(input, 'app_metadata');
      const appMetadata = hasAppMetadata
        ? requireAuthObject(input.app_metadata, 'app_metadata')
        : {};
      const confirmEmail = input.email_confirm === true;
      const hasBanDuration = Object.hasOwn(input, 'ban_duration');
      const bannedUntil = hasBanDuration ? parseBanDuration(input.ban_duration) : null;

      const user = await inAuthTransaction(async (client) => {
        const current = await client.query<{ id: string; email: string | null }>(
          `SELECT id, email
           FROM app_auth.users
           WHERE id = $1 AND deleted_at IS NULL
           FOR UPDATE`,
          [userId],
        );
        if (!current.rows[0]) {
          throw new AuthCompatibilityError('user_not_found', 'User not found.', 404);
        }
        if (email && email !== current.rows[0].email?.toLowerCase()) {
          await assertEmailAvailable(client, email, userId);
        }

        const result = await client.query<AuthUserRow>(
          `UPDATE app_auth.users
           SET email = CASE WHEN $2::boolean THEN $3::text ELSE email END,
               encrypted_password = CASE WHEN $4::boolean THEN $5::text
                 ELSE encrypted_password END,
               raw_user_meta_data = CASE WHEN $6::boolean
                 THEN COALESCE(raw_user_meta_data, '{}'::jsonb) || $7::jsonb
                 ELSE raw_user_meta_data END,
               raw_app_meta_data = CASE WHEN $8::boolean
                 THEN COALESCE(raw_app_meta_data, '{}'::jsonb) || $9::jsonb
                 ELSE raw_app_meta_data END,
               email_confirmed_at = CASE WHEN $10::boolean
                 THEN COALESCE(email_confirmed_at, transaction_timestamp())
                 ELSE email_confirmed_at END,
               confirmed_at = CASE WHEN $10::boolean
                 THEN COALESCE(confirmed_at, transaction_timestamp())
                 ELSE confirmed_at END,
               banned_until = CASE WHEN $11::boolean THEN $12::timestamptz
                 ELSE banned_until END,
               updated_at = transaction_timestamp()
           WHERE id = $1 AND deleted_at IS NULL
           RETURNING ${AUTH_USER_COLUMNS}`,
          [
            userId,
            hasEmail,
            email,
            hasPassword,
            encryptedPassword,
            hasUserMetadata,
            serializeJson(userMetadata),
            hasAppMetadata,
            serializeJson(appMetadata),
            confirmEmail,
            hasBanDuration,
            bannedUntil,
          ],
        );
        const updated = result.rows[0]!;
        if (hasEmail || confirmEmail) {
          const identityEmail = updated.email;
          const verified =
            updated.confirmed_at !== null || updated.email_confirmed_at !== null;
          await client.query(
            `UPDATE app_auth.identities
             SET email = $2,
                 identity_data = COALESCE(identity_data, '{}'::jsonb)
                   || jsonb_build_object('email', $2::text, 'email_verified', $3::boolean),
                 updated_at = transaction_timestamp()
             WHERE user_id = $1 AND provider = 'email'`,
            [userId, identityEmail, verified],
          );
        }
        return (await loadPublicAuthUsers(client, [updated]))[0]!;
      });

      return { user };
    });
  }

  async deleteUser(
    userIdInput: unknown,
    _shouldSoftDelete = false,
  ): Promise<LocalAuthResponse<JsonObject>> {
    return authOperation<JsonObject>({ user: null }, async () => {
      const userId = requireUserId(userIdInput);
      const user = await inAuthTransaction(async (client) => {
        const result = await client.query<AuthUserRow>(
          `SELECT ${AUTH_USER_COLUMNS}
           FROM app_auth.users
           WHERE id = $1 AND deleted_at IS NULL
           FOR UPDATE`,
          [userId],
        );
        const existing = result.rows[0];
        if (!existing) {
          throw new AuthCompatibilityError('user_not_found', 'User not found.', 404);
        }
        const publicUser = (await loadPublicAuthUsers(client, [existing]))[0]!;
        await client.query('DELETE FROM app_auth.users WHERE id = $1', [userId]);
        return publicUser;
      });
      return { user };
    });
  }
}

function localSession(session: IssuedSession, user: JsonObject): JsonObject {
  return {
    access_token: session.accessToken,
    refresh_token: session.refreshToken,
    token_type: session.tokenType.toLowerCase(),
    expires_in: session.expiresIn,
    expires_at: Math.floor(new Date(session.expiresAt).getTime() / 1_000),
    user,
  };
}

export class LocalAuthClient {
  readonly admin = new LocalAuthAdminClient();
  private accessToken: string | null = null;
  private refreshToken: string | null = null;

  async getUser(token?: unknown): Promise<LocalAuthResponse<JsonObject>> {
    return authOperation<JsonObject>({ user: null }, async () => {
      const accessToken = token ?? this.accessToken;
      const authenticated: AuthenticatedUser = await authService.getUser(accessToken);
      return { user: await loadPublicAuthUser(db, authenticated.id) };
    });
  }

  async signInWithPassword(credentials: unknown): Promise<LocalAuthResponse<JsonObject>> {
    return authOperation<JsonObject>({ user: null, session: null }, async () => {
      const input = requireAuthObject(credentials, 'credentials');
      const issued = await authService.signIn(input.email, input.password);
      const user = await loadPublicAuthUser(db, issued.user.id);
      this.accessToken = issued.accessToken;
      this.refreshToken = issued.refreshToken;
      return { user, session: localSession(issued, user) };
    });
  }

  async signOut(options?: unknown): Promise<LocalAuthResponse<null>> {
    return authOperation(null, async () => {
      const input =
        options !== undefined && options !== null && typeof options === 'object'
          ? (options as JsonObject)
          : {};
      const accessToken =
        input.accessToken ?? input.access_token ?? this.accessToken;
      const refreshToken =
        input.refreshToken ?? input.refresh_token ?? this.refreshToken ?? undefined;
      await authService.signOut(accessToken, refreshToken);
      this.accessToken = null;
      this.refreshToken = null;
      return null;
    });
  }
}

function unsupported(scope: string): CompatibilityResponse<never> {
  return failure(
    new QueryBuildError(
      'LOCAL_UNSUPPORTED_SERVICE',
      `${scope} is not implemented by the local backend yet.`,
      '',
      'Migrate this service before enabling the corresponding route.',
    ),
  );
}

function unsupportedNamespace(scope: string): any {
  return new Proxy(Object.create(null) as Record<string, unknown>, {
    get(_target, property) {
      if (property === 'then') return undefined;
      if (property === 'from') {
        return (bucket: unknown) => unsupportedNamespace(`${scope}.from(${String(bucket)})`);
      }
      if (property === 'getPublicUrl') {
        return () => unsupported(`${scope}.getPublicUrl`);
      }
      return () => Promise.resolve(unsupported(`${scope}.${String(property)}`));
    },
  });
}

export interface LocalSupabaseClient {
  from(table: string): LocalPostgrestBuilder;
  auth: LocalAuthClient;
  storage: any;
  rpc(functionName: string, parameters?: unknown): Promise<CompatibilityResponse<never>>;
}

export function createClient(
  _supabaseUrl?: string,
  _supabaseKey?: string,
  _options?: unknown,
): LocalSupabaseClient {
  return {
    from(table: string) {
      return new LocalPostgrestBuilder(table);
    },
    auth: new LocalAuthClient(),
    storage: storageService,
    rpc(functionName: string, _parameters?: unknown) {
      return Promise.resolve(unsupported(`Supabase RPC ${functionName}`));
    },
  };
}