Data - SQL Database

import { Tenzro } from 'tenzro';

const tenzro = new Tenzro({ apiKey: 'your-api-key' });

// Create a SQL database
const db = await tenzro.data.create({
  projectId: 'project-id',
  db_name: 'my-app-db',
  maxConnections: 20,
});

// Create a table
await tenzro.data.createTable(db.data_db_id, 'users', [
  { name: 'id', type: 'SERIAL', primaryKey: true },
  { name: 'email', type: 'VARCHAR(255)', unique: true },
  { name: 'name', type: 'VARCHAR(255)' },
  { name: 'created_at', type: 'TIMESTAMP', default: 'NOW()' },
]);

// Insert data
await tenzro.data.query(db.data_db_id,
  'INSERT INTO users (email, name) VALUES ($1, $2)',
  ['john@example.com', 'John Doe']
);

// Query data
const { rows } = await tenzro.data.query(db.data_db_id,
  'SELECT * FROM users WHERE email = $1',
  ['john@example.com']
);
console.log(rows[0]);  // { id: 1, email: 'john@example.com', ... }

const db = await tenzro.data.create({
  projectId: string,       // Required
  db_name: string,         // Required
  description?: string,    // Optional
  maxConnections?: number, // Default: 20
});

const { data, count } = await tenzro.data.list(projectId);

const stats = await tenzro.data.getStats(dataDbId);
// Returns: { tableCount, totalRows, diskUsage, connectionCount }

const result = await tenzro.data.query(
  dataDbId,
  sql: string,          // SQL query
  params?: unknown[],   // Parameterized values
  transaction?: boolean // Run in transaction
);

// Result type:
interface QueryResult {
  rows: any[];
  rowCount: number;
  fields: { name: string; type: string }[];
}

const results = await tenzro.data.batch(dataDbId, [
  { sql: 'INSERT INTO orders (user_id) VALUES ($1)', params: [1] },
  { sql: 'UPDATE users SET order_count = order_count + 1 WHERE id = $1', params: [1] },
  { sql: 'SELECT * FROM orders WHERE user_id = $1', params: [1] },
]);
// Returns: QueryResult[] - one result per query

await tenzro.data.createTable(dataDbId, 'products', [
  { name: 'id', type: 'SERIAL', primaryKey: true },
  { name: 'name', type: 'VARCHAR(255)', nullable: false },
  { name: 'price', type: 'DECIMAL(10,2)', nullable: false },
  { name: 'category_id', type: 'INTEGER', references: { table: 'categories', column: 'id' } },
  { name: 'created_at', type: 'TIMESTAMP', default: 'NOW()' },
]);

const { tables, count } = await tenzro.data.listTables(dataDbId);
// tables: string[] - table names

const info = await tenzro.data.getTableInfo(dataDbId, 'users');
// Returns: { name, columns: [...], rowCount }

await tenzro.data.dropTable(dataDbId, 'old_table');

// Using batch with implicit transaction
const results = await tenzro.data.batch(dataDbId, [
  { sql: 'BEGIN' },
  { sql: 'INSERT INTO orders (user_id, total) VALUES ($1, $2)', params: [1, 99.99] },
  { sql: 'UPDATE inventory SET quantity = quantity - 1 WHERE product_id = $1', params: [1] },
  { sql: 'COMMIT' },
]);

// Or using single query with transaction flag
await tenzro.data.query(dataDbId, `
  INSERT INTO orders (user_id, total) VALUES ($1, $2);
  UPDATE inventory SET quantity = quantity - 1 WHERE product_id = $3;
`, [1, 99.99, 1], true);

// Create users table
await tenzro.data.createTable(dataDbId, 'users', [
  { name: 'id', type: 'UUID', primaryKey: true, default: 'gen_random_uuid()' },
  { name: 'email', type: 'VARCHAR(255)', unique: true, nullable: false },
  { name: 'password_hash', type: 'VARCHAR(255)', nullable: false },
  { name: 'name', type: 'VARCHAR(255)' },
  { name: 'metadata', type: 'JSONB', default: "'{}'" },
  { name: 'created_at', type: 'TIMESTAMP', default: 'NOW()' },
  { name: 'updated_at', type: 'TIMESTAMP', default: 'NOW()' },
]);

// Register user
async function registerUser(email: string, passwordHash: string, name: string) {
  const { rows } = await tenzro.data.query(dataDbId, `
    INSERT INTO users (email, password_hash, name)
    VALUES ($1, $2, $3)
    RETURNING id, email, name, created_at
  `, [email, passwordHash, name]);
  return rows[0];
}

// Find user by email
async function findUserByEmail(email: string) {
  const { rows } = await tenzro.data.query(dataDbId, `
    SELECT * FROM users WHERE email = $1
  `, [email]);
  return rows[0] || null;
}

// Create conversation table
await tenzro.data.createTable(dataDbId, 'conversations', [
  { name: 'id', type: 'UUID', primaryKey: true, default: 'gen_random_uuid()' },
  { name: 'agent_id', type: 'VARCHAR(255)', nullable: false },
  { name: 'user_id', type: 'VARCHAR(255)', nullable: false },
  { name: 'created_at', type: 'TIMESTAMP', default: 'NOW()' },
]);

await tenzro.data.createTable(dataDbId, 'messages', [
  { name: 'id', type: 'UUID', primaryKey: true, default: 'gen_random_uuid()' },
  { name: 'conversation_id', type: 'UUID', references: { table: 'conversations', column: 'id' } },
  { name: 'role', type: 'VARCHAR(20)', nullable: false },
  { name: 'content', type: 'TEXT', nullable: false },
  { name: 'metadata', type: 'JSONB', default: "'{}'" },
  { name: 'created_at', type: 'TIMESTAMP', default: 'NOW()' },
]);

// Get conversation history
async function getConversationHistory(conversationId: string, limit = 50) {
  const { rows } = await tenzro.data.query(dataDbId, `
    SELECT role, content, metadata, created_at
    FROM messages
    WHERE conversation_id = $1
    ORDER BY created_at DESC
    LIMIT $2
  `, [conversationId, limit]);
  return rows.reverse();
}