/**
 * PostgreSQL client types.
 */

import type { z } from "zod";

import type { BaseIntegrationClient } from "../../types.js";
import type { TraceMetadata } from "../registry.js";

/**
 * PostgreSQL client for database operations.
 *
 * Provides typed methods for executing SQL queries and statements
 * against a PostgreSQL database.
 *
 * @example
 * ```typescript
 * import { z } from 'zod';
 * // Declare in api(): integrations: { db: postgres(INTEGRATION_ID) }
 * // In run(), access via ctx.integrations.db
 *
 * // Query with runtime validation
 * const UserSchema = z.object({
 *   id: z.string(),
 *   name: z.string(),
 *   email: z.string().email(),
 * });
 *
 * const users = await ctx.integrations.db.query(
 *   'SELECT * FROM users WHERE status = $1',
 *   UserSchema,
 *   ['active']
 * );
 * // Type is automatically inferred: Array<{ id: string; name: string; email: string }>
 *
 * // Execute a statement (no validation needed)
 * const result = await ctx.integrations.db.execute(
 *   'UPDATE users SET last_login = NOW() WHERE id = $1',
 *   [userId]
 * );
 * console.log(`Updated ${result.rowCount} rows`);
 * ```
 */
export interface PostgresClient extends BaseIntegrationClient {
  /**
   * Execute a SQL query and return validated typed results.
   *
   * Type parameter T is automatically inferred from the schema.
   * All query results are validated at runtime against the provided Zod schema.
   *
   * Use parameterized queries with $1, $2, etc. placeholders
   * to prevent SQL injection.
   *
   * @param sql - The SQL query to execute
   * @param schema - Zod schema for runtime validation (REQUIRED)
   * @param params - Optional array of parameter values
   * @returns Array of validated result rows
   * @throws {QueryValidationError} If any row fails schema validation
   *
   * @example
   * ```typescript
   * import { z } from 'zod';
   *
   * const UserSchema = z.object({
   *   id: z.number(),
   *   email: z.string().email(),
   * });
   *
   * const users = await ctx.integrations.db.query(
   *   'SELECT * FROM users WHERE email = $1',
   *   UserSchema,
   *   ['user@example.com']
   * );
   * // Type is automatically z.infer<typeof UserSchema>[]
   * ```
   */
  query<T>(
    sql: string,
    schema: z.ZodSchema<T>,
    params?: unknown[],
    metadata?: TraceMetadata,
  ): Promise<T[]>;

  /**
   * Execute a SQL statement (INSERT, UPDATE, DELETE) and return affected row count.
   *
   * Use this method when you don't need the returned data, only the count
   * of affected rows.
   *
   * @param sql - The SQL statement to execute
   * @param params - Optional array of parameter values
   * @returns Object with the count of affected rows
   *
   * @example
   * ```typescript
   * const result = await ctx.integrations.db.execute(
   *   'DELETE FROM sessions WHERE expires_at < NOW()'
   * );
   * console.log(`Deleted ${result.rowCount} expired sessions`);
   * ```
   */
  execute(
    sql: string,
    params?: unknown[],
    metadata?: TraceMetadata,
  ): Promise<{ rowCount: number }>;
}