/**
 * PostgreSQL Adapter for lazy-render-server
 * Provides pagination support for PostgreSQL
 */

import { calculatePagination, PaginationParams } from '../index';

export interface PostgresPaginationResult<T> {
  data: T[];
  pagination: {
    page: number;
    limit: number;
    total: number;
    totalPages: number;
    hasMore: boolean;
    nextCursor?: string;
    prevCursor?: string;
  };
  meta?: {
    sortBy?: string;
    sortOrder?: 'asc' | 'desc';
    timestamp: string;
  };
}

/**
 * Paginate PostgreSQL query results
 * 
 * @param db - Database client (node-postgres)
 * @param table - Table name
 * @param params - Pagination parameters
 * @param where - WHERE clause (optional)
 * @param orderBy - ORDER BY clause (optional)
 * @returns Paginated results
 */
export async function paginatePostgres<T>(
  db: any, // node-postgres client
  table: string,
  params: PaginationParams,
  where: string = '',
  orderBy: string = 'created_at DESC'
): Promise<PostgresPaginationResult<T>> {
  const { page, limit, sortBy, sortOrder } = params;
  const offset = (page - 1) * limit;
  
  // Build query with parameterized values to prevent SQL injection
  const values: any[] = [limit, offset];
  let whereClause = where ? `WHERE ${where}` : '';
  
  // Add sort if provided
  let orderClause = orderBy;
  if (sortBy) {
    orderClause = `${sortBy} ${sortOrder === 'asc' ? 'ASC' : 'DESC'}`;
  }
  
  // Execute queries in parallel
  const [itemsResult, totalResult] = await Promise.all([
    db.query(
      `SELECT * FROM ${table} ${whereClause} ORDER BY ${orderClause} LIMIT $1 OFFSET $2`,
      values
    ),
    db.query(`SELECT COUNT(*) FROM ${table} ${whereClause}`)
  ]);
  
  const total = parseInt(totalResult.rows[0].count);
  
  return calculatePagination(itemsResult.rows, page, limit, total, {
    sortBy,
    sortOrder
  }) as PostgresPaginationResult<T>;
}

/**
 * Paginate with custom SQL query
 * 
 * @param db - Database client
 * @param params - Pagination parameters
 * @param sql - Custom SQL query (without LIMIT/OFFSET)
 * @param countSql - Count query SQL
 * @param values - Query parameters
 * @returns Paginated results
 */
export async function paginatePostgresCustom<T>(
  db: any,
  params: PaginationParams,
  sql: string,
  countSql: string,
  values: any[] = []
): Promise<PostgresPaginationResult<T>> {
  const { page, limit } = params;
  const offset = (page - 1) * limit;
  
  // Add LIMIT and OFFSET to query
  const paginatedSql = `${sql} LIMIT $${values.length + 1} OFFSET $${values.length + 2}`;
  const paginatedValues = [...values, limit, offset];
  
  // Execute queries in parallel
  const [itemsResult, totalResult] = await Promise.all([
    db.query(paginatedSql, paginatedValues),
    db.query(countSql, values)
  ]);
  
  const total = parseInt(totalResult.rows[0].count);
  
  return calculatePagination(itemsResult.rows, page, limit, total) as PostgresPaginationResult<T>;
}

export default {
  paginatePostgres,
  paginatePostgresCustom
};