database

import { Client } from "pg";
import type { QueryResult, TableInfo } from "./sqlite";

export async function postgresQuery(dsn: string, sql: string, maxRows: number): Promise<QueryResult> {
  const client = new Client({ connectionString: dsn });
  await client.connect();
  try {
    const result = await client.query(sql);
    const columns = result.fields.map(f => f.name);
    const truncated = result.rows.length > maxRows;
    return {
      columns,
      rows: result.rows.slice(0, maxRows).map((r: Record<string, unknown>) => columns.map(c => r[c])),
      rowCount: result.rows.length,
      truncated,
    };
  } finally {
    await client.end();
  }
}

export async function postgresTables(dsn: string): Promise<string[]> {
  const client = new Client({ connectionString: dsn });
  await client.connect();
  try {
    const result = await client.query(
      "SELECT tablename FROM pg_tables WHERE schemaname = 'public' ORDER BY tablename"
    );
    return result.rows.map((r: { tablename: string }) => r.tablename);
  } finally {
    await client.end();
  }
}

export async function postgresSchema(dsn: string, tableName: string): Promise<TableInfo> {
  const client = new Client({ connectionString: dsn });
  await client.connect();
  try {
    const result = await client.query(
      `SELECT column_name, data_type,
              is_nullable = 'NO' AS notnull,
              (SELECT COUNT(*) FROM information_schema.table_constraints tc
               JOIN information_schema.key_column_usage kcu
                 ON tc.constraint_name = kcu.constraint_name
               WHERE tc.constraint_type = 'PRIMARY KEY'
                 AND kcu.table_name = $1 AND kcu.column_name = c.column_name) > 0 AS pk
       FROM information_schema.columns c
       WHERE table_name = $1 AND table_schema = 'public'
       ORDER BY ordinal_position`,
      [tableName]
    );
    return {
      name: tableName,
      columns: result.rows.map((r: { column_name: string; data_type: string; notnull: boolean; pk: boolean }) => ({
        name: r.column_name,
        type: r.data_type,
        notnull: r.notnull,
        pk: r.pk,
      })),
    };
  } finally {
    await client.end();
  }
}