import { createDb } from '@launchpad/db-engine';

// Create database client
const db = await createDb({
  connectionString: process.env.DATABASE_URL!,
  migrationsPath: './migrations',
});

// Tenant context (from authenticated request)
const ctx = {
  appId: 'my-app',
  organizationId: 'org_123',
};

// Query with automatic tenant injection
const users = await db.table('users', ctx)
  .select('id', 'email', 'name')
  .where('status', '=', 'active')
  .orderBy('created_at', 'desc')
  .limit(10)
  .execute();

// Insert with automatic tenant columns
await db.table('users', ctx)
  .insert()
  .values({
    email: 'john@example.com',
    name: 'John Doe',
  })
  .execute();

// Transaction
await db.transaction(ctx, async (trx) => {
  const org = await trx.table('organizations')
    .insert()
    .values({ name: 'Acme Inc' })
    .returning('id')
    .execute();

  await trx.table('users')
    .insert()
    .values({
      email: 'admin@acme.com',
      organization_id: org[0].id,
    })
    .execute();
});

// Close connection
await db.close();

// Run migrations on startup
await db.migrations.up({ scope: 'core' });

// Check status
const status = await db.migrations.status();
console.log(`Applied: ${status.applied.length}, Pending: ${status.pending.length}`);

// Define schema
const crmSchema = {
  tables: {
    contacts: {
      columns: {
        id: { type: 'uuid', primaryKey: true, default: 'gen_random_uuid()' },
        app_id: { type: 'string', tenant: true },
        organization_id: { type: 'uuid', tenant: true },
        first_name: { type: 'string' },
        last_name: { type: 'string' },
        email: { type: 'string', unique: true },
        created_at: { type: 'datetime', default: 'now()' },
      },
      indexes: [
        { columns: ['app_id', 'organization_id'] },
      ],
    },
  },
};

// Register schema
await db.schema.register({
  appId: 'crm-app',
  schemaName: 'crm',
  version: '1.0.0',
  schema: crmSchema,
});

export namespace Crm {
  // Row type - all columns
  export interface Contacts {
    id: string;
    app_id: string;
    organization_id: string;
    first_name: string;
    last_name: string;
    email: string;
    created_at: Date;
  }

  // Insert type - omits auto-generated fields (id, created_at, updated_at)
  // and tenant columns (app_id, organization_id)
  export interface ContactsInsert {
    first_name: string;
    last_name: string;
    email: string;
  }

  // Update type - all fields optional for partial updates
  export interface ContactsUpdate {
    first_name?: string | null;
    last_name?: string | null;
    email?: string | null;
  }

  export type TableName = 'contacts';
}

import { z } from 'zod';

export const crmContactsSchema = z.object({
  id: z.string().uuid(),
  first_name: z.string(),
  last_name: z.string(),
  email: z.string(),
  created_at: z.coerce.date(),
});

export const crmContactsInsertSchema = z.object({
  first_name: z.string(),
  last_name: z.string(),
  email: z.string(),
});

export const crmContactsUpdateSchema = z.object({
  first_name: z.string().nullable().optional(),
  last_name: z.string().nullable().optional(),
  email: z.string().nullable().optional(),
});

export type Contacts = z.infer<typeof crmContactsSchema>;
export type ContactsInsert = z.infer<typeof crmContactsInsertSchema>;
export type ContactsUpdate = z.infer<typeof crmContactsUpdateSchema>;

// PostgreSQL (auto-detected from connection string)
const pgDb = await createDb({
  connectionString: 'postgresql://localhost/mydb',
});

// MySQL
const mysqlDb = await createDb({
  connectionString: 'mysql://localhost/mydb',
});

// SQLite
const sqliteDb = await createDb({
  connectionString: 'sqlite://./data.db',
});

const db = await createDb(options);

// Query builder
db.table<T>(name, ctx)          // Returns TableBuilder with tenant injection
db.tableWithoutTenant<T>(name)  // Returns TableBuilder without tenant injection

// Transactions
db.transaction(ctx, async (trx) => { ... })

// Raw queries
db.raw<T>(sql, params)
db.rawWithTenant<T>(ctx, sql, params)
db.execute(sql, params)

// Migrations
db.migrations.up(options)
db.migrations.down(options)
db.migrations.status(options)
db.migrations.verify(options)

// Schema registry
db.schema.register(options)
db.schema.get(appId, schemaName)
db.schema.list(appId?)

// Cleanup
db.close()

db.table('users', ctx)
  .select('id', 'email')
  .where('status', '=', 'active')
  .whereIn('role', ['admin', 'user'])
  .whereNull('deleted_at')
  .orderBy('created_at', 'desc')
  .limit(10)
  .offset(0)
  .execute()

db.table('users', ctx)
  .insert()
  .values({ email: 'test@example.com' })
  .returning('id')
  .execute()

db.table('users', ctx)
  .update()
  .set({ status: 'inactive' })
  .where('id', '=', userId)
  .execute()

db.table('users', ctx)
  .delete()
  .where('id', '=', userId)
  .execute()