Data - SQL Database

import { Tenzro } from '@tenzro/cloud';

const client = new Tenzro({
  apiKey: process.env.TENZRO_API_KEY,
  projectId: 'your-project-id',
});

// Get database by name
const db = await client.data.db('my-app');

// Create a table (SQL or raw queries)
await db.execute(`
  CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(255),
    created_at TIMESTAMP DEFAULT NOW()
  )
`);

// Insert data (ORM-style)
await db.insert('users', {
  email: 'john@example.com',
  name: 'John Doe',
});

// Query data (ORM-style)
const users = await db.findMany('users', {
  where: { email: 'john@example.com' },
});
console.log(users[0]);  // { id: 1, email: 'john@example.com', ... }

// Get existing database by name
const db = await client.data.db('my-app');

const users = await db.findMany<User>('users', {
  where: { isActive: true },
  orderBy: { column: 'createdAt', direction: 'DESC' },
  limit: 10,
  offset: 0,
});

const user = await db.findOne<User>('users', {
  where: { id: '123' }
});

const newUser = await db.insert('users', {
  email: 'alice@example.com',
  name: 'Alice',
});

const updated = await db.update('users',
  { isActive: false },           // Data to update
  { id: '123' }                  // Where clause
);

const deleted = await db.deleteRows('users', { id: '123' });

const count = await db.count('users');
const activeCount = await db.count('users', { isActive: true });

const result = await db.execute(
  'SELECT * FROM users WHERE age > $1',
  [18]
);

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

// 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();
}