import { createNLPostgres } from 'praisonai-ts';

const db = await createNLPostgres({
  connectionUrl: process.env.DATABASE_URL!,
  readOnly: true, // Safety: read-only by default
});

// Query with natural language
const result = await db.query('Show me all users who signed up last month');
console.log(result.rows);
console.log('SQL:', result.sql);

import { createNLPostgres } from 'praisonai-ts';

const db = await createNLPostgres({
  // Connection
  connectionUrl: process.env.DATABASE_URL!,
  schema: 'public',
  
  // Safety
  readOnly: true,           // Only SELECT queries (default: true)
  allowedTables: ['users', 'orders'], // Whitelist tables
  blockedTables: ['secrets'],         // Blacklist tables
  
  // Limits
  maxRows: 100,             // Max rows returned (default: 100)
  timeout: 30000,           // Query timeout in ms
});

import { Agent, createPostgresTool } from 'praisonai-ts';

const dbTool = await createPostgresTool({
  connectionUrl: process.env.DATABASE_URL!,
  readOnly: true,
});

const agent = new Agent({
  name: 'DataAnalyst',
  instructions: 'You analyze data from the database. Always explain your findings.',
  tools: [dbTool],
});

const response = await agent.chat('What are the top 10 products by revenue?');

// Get database schema
const schema = await db.getSchema();

console.log('Tables:', schema.tables.map(t => t.name));

// Get specific table schema
const usersTable = await db.getTableSchema('users');
console.log('Columns:', usersTable.columns);

// Simple query
const users = await db.query('List all active users');

// Aggregation
const stats = await db.query('What is the average order value by month?');

// Joins
const orders = await db.query('Show orders with customer names from last week');

// Complex query
const analysis = await db.query(
  'Find customers who made more than 5 purchases but haven\'t ordered in 30 days'
);

interface NLQueryResult {
  query: string;      // Original natural language query
  sql: string;        // Generated SQL
  result: {
    rows: any[];      // Query results
    rowCount: number; // Number of rows
    fields: Array<{ name: string; type: string }>;
  };
  explanation?: string; // Optional explanation
}

const db = await createNLPostgres({
  connectionUrl: process.env.DATABASE_URL!,
  readOnly: true, // Only SELECT queries allowed
});

// This will throw an error
await db.query('Delete all users'); // Error: Write operations not allowed

const db = await createNLPostgres({
  connectionUrl: process.env.DATABASE_URL!,
  allowedTables: ['products', 'orders'], // Only these tables
  blockedTables: ['users', 'payments'],  // Never these tables
});

const db = await createNLPostgres({
  connectionUrl: process.env.DATABASE_URL!,
  readOnly: false, // Enable write operations
});

// Now write operations are allowed
await db.query('Update the status of order 123 to shipped');

postgresql://user:password@host:port/database?sslmode=require