// API Response Type (from Stripe API)
export interface StripeCustomer {
  id: string;
  email: string | null;
  name: string | null;
  created: number;
  metadata: Record<string, string>;
}

// Database Record Type (PostgreSQL)
export interface StripeCustomerRecord {
  id: string;
  email: string | null;
  name: string | null;
  metadata: Record<string, string>;
  created_at: Date;
  updated_at: Date;
  synced_at: Date;
  deleted_at: Date | null;
}

// Configuration Type
export interface StripePluginConfig {
  apiKey: string;
  webhookSecret?: string;
  port: number;
  database: DatabaseConfig;
}

export class StripeClient {
  private stripe: Stripe;  // Official SDK
  private rateLimiter: RateLimiter;

  constructor(apiKey: string) {
    this.stripe = new Stripe(apiKey);
  }

  // Generator for memory-efficient pagination
  async *listCustomers(): AsyncGenerator<StripeCustomerRecord[]> {
    for await (const customer of this.stripe.customers.list({ limit: 100 })) {
      yield [this.mapCustomer(customer)];
    }
  }

  // Traditional pagination with auto-fetch
  async listAllCustomers(): Promise<StripeCustomerRecord[]> {
    const customers: StripeCustomerRecord[] = [];
    let hasMore = true;
    let startingAfter: string | undefined;

    while (hasMore) {
      const response = await this.stripe.customers.list({
        limit: 100,
        starting_after: startingAfter,
      });

      customers.push(...response.data.map(c => this.mapCustomer(c)));
      hasMore = response.has_more;

      if (response.data.length > 0) {
        startingAfter = response.data[response.data.length - 1].id;
      }
    }

    return customers;
  }

  // Type mapping: API → Database
  private mapCustomer(api: Stripe.Customer): StripeCustomerRecord {
    return {
      id: api.id,
      email: api.email ?? null,
      name: api.name ?? null,
      metadata: api.metadata,
      created_at: new Date(api.created * 1000),  // Unix → Date
      updated_at: new Date(),
      synced_at: new Date(),
      deleted_at: null,
    };
  }
}

// From shared utilities
export class RateLimiter {
  private tokens: number;
  private lastRefill: number;
  private maxTokens: number;
  private refillRate: number; // tokens per second

  constructor(requestsPerSecond: number) {
    this.maxTokens = requestsPerSecond;
    this.tokens = requestsPerSecond;
    this.refillRate = requestsPerSecond;
    this.lastRefill = Date.now();
  }

  async acquire(): Promise<void> {
    this.refill();

    if (this.tokens < 1) {
      const waitTime = (1 - this.tokens) / this.refillRate * 1000;
      await new Promise(resolve => setTimeout(resolve, waitTime));
      this.refill();
    }

    this.tokens -= 1;
  }

  private refill(): void {
    const now = Date.now();
    const elapsed = (now - this.lastRefill) / 1000;
    this.tokens = Math.min(
      this.maxTokens,
      this.tokens + elapsed * this.refillRate
    );
    this.lastRefill = now;
  }
}

async upsertCustomer(record: StripeCustomerRecord): Promise<void> {
  await this.db.execute(
    `INSERT INTO stripe_customers (
      id, email, name, phone, description, balance,
      delinquent, metadata, address, shipping,
      created_at, synced_at
    ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, NOW())
    ON CONFLICT (id) DO UPDATE SET
      email = EXCLUDED.email,
      name = EXCLUDED.name,
      phone = EXCLUDED.phone,
      description = EXCLUDED.description,
      balance = EXCLUDED.balance,
      delinquent = EXCLUDED.delinquent,
      metadata = EXCLUDED.metadata,
      address = EXCLUDED.address,
      shipping = EXCLUDED.shipping,
      updated_at = NOW(),
      synced_at = NOW()`,
    [
      record.id,
      record.email,
      record.name,
      record.phone,
      record.description,
      record.balance,
      record.delinquent,
      JSON.stringify(record.metadata),
      JSON.stringify(record.address),
      JSON.stringify(record.shipping),
      record.created_at,
    ]
  );
}

async deleteCustomer(id: string): Promise<void> {
  await this.db.execute(
    `UPDATE stripe_customers
     SET deleted_at = NOW(), updated_at = NOW()
     WHERE id = $1`,
    [id]
  );
}

export class StripeSyncService {
  private client: StripeClient;
  private db: StripeDatabase;
  private syncing = false;

  async sync(options: SyncOptions = {}): Promise<SyncResult> {
    if (this.syncing) {
      throw new Error('Sync already in progress');
    }

    this.syncing = true;
    const startTime = Date.now();
    const stats = this.initializeStats();

    try {
      // Sync in dependency order
      const resources = options.resources ?? [
        'customers',      // Must sync before subscriptions
        'products',       // Must sync before prices
        'prices',         // Must sync before subscriptions
        'subscriptions',  // Depends on customers, prices
        'invoices',       // Depends on customers, subscriptions
        'payment_intents',
        'charges',
        'refunds',
      ];

      for (const resource of resources) {
        await this.syncResource(resource, stats);
      }

      return {
        success: true,
        stats,
        errors: [],
        duration: Date.now() - startTime,
      };
    } catch (error) {
      const message = error instanceof Error ? error.message : 'Unknown error';
      logger.error('Sync failed', { error: message });

      return {
        success: false,
        stats,
        errors: [message],
        duration: Date.now() - startTime,
      };
    } finally {
      this.syncing = false;
    }
  }

  private async syncResource(
    resource: string,
    stats: SyncStats
  ): Promise<void> {
    logger.info(`Syncing ${resource}...`);
    const startTime = Date.now();
    let count = 0;

    switch (resource) {
      case 'customers':
        for await (const batch of this.client.listCustomers()) {
          for (const customer of batch) {
            await this.db.upsertCustomer(customer);
            count++;
          }
          logger.debug(`Synced ${count} customers`);
        }
        stats.customers = count;
        break;

      case 'products':
        const products = await this.client.listAllProducts();
        for (const product of products) {
          await this.db.upsertProduct(product);
        }
        stats.products = products.length;
        count = products.length;
        break;

      // ... more resources
    }

    const duration = Date.now() - startTime;
    logger.info(`Synced ${count} ${resource} in ${duration}ms`);
  }
}

async incrementalSync(since: Date): Promise<SyncResult> {
  // Fetch only records updated since timestamp
  const customers = await this.client.listAllCustomers({
    created: { gte: Math.floor(since.getTime() / 1000) }
  });

  for (const customer of customers) {
    await this.db.upsertCustomer(customer);
  }

  return {
    success: true,
    synced: customers.length,
    duration: Date.now() - startTime,
  };
}

export class StripeWebhookHandler {
  private handlers: Map<string, WebhookHandlerFn>;

  constructor(
    private client: StripeClient,
    private db: StripeDatabase,
    private syncService: StripeSyncService
  ) {
    this.handlers = new Map();
    this.registerDefaultHandlers();
  }

  private registerDefaultHandlers(): void {
    // Customer events
    this.register('customer.created', this.handleCustomerCreated.bind(this));
    this.register('customer.updated', this.handleCustomerUpdated.bind(this));
    this.register('customer.deleted', this.handleCustomerDeleted.bind(this));

    // Subscription events
    this.register('customer.subscription.created',
      this.handleSubscriptionCreated.bind(this));
    this.register('customer.subscription.updated',
      this.handleSubscriptionUpdated.bind(this));
    this.register('customer.subscription.deleted',
      this.handleSubscriptionDeleted.bind(this));

    // Invoice events
    this.register('invoice.paid', this.handleInvoicePaid.bind(this));
    this.register('invoice.payment_failed',
      this.handleInvoicePaymentFailed.bind(this));

    // ... 70+ total events
  }

  async processEvent(event: Stripe.Event): Promise<void> {
    // 1. Store raw event for audit trail
    await this.db.insertWebhookEvent({
      id: event.id,
      type: event.type,
      data: event.data,
      created_at: new Date(event.created * 1000),
      processed: false,
    });

    try {
      // 2. Route to handler
      const handler = this.handlers.get(event.type);

      if (handler) {
        await handler(event);
      } else {
        logger.warn(`No handler for event type: ${event.type}`);
      }

      // 3. Mark as processed
      await this.db.markEventProcessed(event.id);
    } catch (error) {
      const message = error instanceof Error ? error.message : 'Unknown error';
      logger.error(`Failed to process event ${event.id}`, { error: message });

      // Store error for debugging
      await this.db.markEventProcessed(event.id, message);
      throw error;
    }
  }

  private async handleCustomerCreated(event: Stripe.Event): Promise<void> {
    const customer = event.data.object as Stripe.Customer;
    const record = this.client.mapCustomer(customer);
    await this.db.upsertCustomer(record);
    logger.info(`Customer created: ${customer.id}`);
  }

  private async handleSubscriptionUpdated(event: Stripe.Event): Promise<void> {
    const subscription = event.data.object as Stripe.Subscription;

    // Re-fetch to get complete data with expansions
    const fullSubscription = await this.client.getSubscription(subscription.id);

    if (fullSubscription) {
      await this.db.upsertSubscription(fullSubscription);
      logger.info(`Subscription updated: ${subscription.id}`);
    }
  }
}

// From shared utilities
export function verifyStripeSignature(
  payload: string,
  signature: string,
  secret: string
): boolean {
  const [timestampPart, signaturePart] = signature.split(',');
  const timestamp = timestampPart.split('=')[1];
  const expectedSignature = signaturePart.split('=')[1];

  // Prevent replay attacks (5 minute window)
  const currentTime = Math.floor(Date.now() / 1000);
  if (Math.abs(currentTime - parseInt(timestamp)) > 300) {
    return false;
  }

  // Verify HMAC-SHA256 signature
  const signedPayload = `${timestamp}.${payload}`;
  const expectedSig = crypto
    .createHmac('sha256', secret)
    .update(signedPayload)
    .digest('hex');

  return crypto.timingSafeEqual(
    Buffer.from(expectedSignature),
    Buffer.from(expectedSig)
  );
}

export async function createServer(config: Config) {
  const app = Fastify({ logger: false });

  // Initialize components
  const client = new StripeClient(config.apiKey);
  const db = new StripeDatabase();
  const syncService = new StripeSyncService(client, db);
  const webhookHandler = new StripeWebhookHandler(client, db, syncService);

  await db.connect();
  await db.initializeSchema();

  // Middleware
  await app.register(cors);

  // Rate limiting
  const rateLimiter = new ApiRateLimiter(100, 60000); // 100 req/min
  app.addHook('preHandler', createRateLimitHook(rateLimiter));

  // API key authentication (optional)
  if (config.security.apiKey) {
    app.addHook('preHandler', createAuthHook(config.security.apiKey));
  }

  // Raw body parser for webhook verification
  app.addContentTypeParser('application/json', { parseAs: 'string' },
    (req, body, done) => {
      try {
        const json = JSON.parse(body as string);
        req.rawBody = body as string;
        done(null, json);
      } catch (err) {
        done(err, undefined);
      }
    }
  );

  // Health checks
  app.get('/health', async () => ({
    status: 'ok',
    plugin: 'stripe',
    timestamp: new Date().toISOString(),
  }));

  app.get('/ready', async (_, reply) => {
    try {
      await db.query('SELECT 1');
      return { ready: true, timestamp: new Date().toISOString() };
    } catch (error) {
      return reply.status(503).send({
        ready: false,
        error: 'Database unavailable'
      });
    }
  });

  // Webhook endpoint
  app.post('/webhook', async (request, reply) => {
    const signature = request.headers['stripe-signature'] as string;

    if (!signature || !config.webhookSecret) {
      return reply.status(400).send({ error: 'Missing signature' });
    }

    // Verify signature
    const isValid = verifyStripeSignature(
      request.rawBody,
      signature,
      config.webhookSecret
    );

    if (!isValid) {
      logger.warn('Invalid webhook signature');
      return reply.status(401).send({ error: 'Invalid signature' });
    }

    // Process event
    try {
      const event = request.body as Stripe.Event;
      await webhookHandler.processEvent(event);
      return { received: true };
    } catch (error) {
      const message = error instanceof Error ? error.message : 'Unknown error';
      logger.error('Webhook processing failed', { error: message });
      return reply.status(500).send({ error: 'Processing failed' });
    }
  });

  // Sync endpoint
  app.post('/api/sync', async () => {
    const result = await syncService.sync();
    return result;
  });

  // Status endpoint
  app.get('/api/status', async () => {
    const stats = await db.getStats();
    return {
      plugin: 'stripe',
      version: '1.0.0',
      stats,
      timestamp: new Date().toISOString(),
    };
  });

  // Data query endpoints
  app.get('/api/customers', async (request) => {
    const { limit = 100, offset = 0 } = request.query as Record<string, string>;
    const customers = await db.listCustomers(
      parseInt(limit),
      parseInt(offset)
    );
    return { data: customers };
  });

  app.get('/api/subscriptions', async (request) => {
    const { status } = request.query as Record<string, string>;
    const subscriptions = await db.listSubscriptions({ status });
    return { data: subscriptions };
  });

  app.get('/api/mrr', async () => {
    const mrr = await db.getMRR();
    return { data: mrr };
  });

  return app;
}

import { Command } from 'commander';

const program = new Command();

program
  .name('nself-stripe')
  .description('Stripe plugin for nself')
  .version('1.0.0');

// Initialize database
program
  .command('init')
  .description('Initialize database schema')
  .action(async () => {
    const db = new StripeDatabase();
    await db.connect();
    await db.initializeSchema();
    console.log('✓ Database schema initialized');
    await db.disconnect();
  });

// Sync data
program
  .command('sync')
  .description('Sync all Stripe data to database')
  .option('-r, --resources <items>', 'Resources to sync (comma-separated)')
  .option('-i, --incremental', 'Incremental sync only')
  .action(async (options) => {
    const client = new StripeClient(config.apiKey);
    const db = new StripeDatabase();
    const syncService = new StripeSyncService(client, db);

    await db.connect();

    const syncOptions: SyncOptions = {
      incremental: options.incremental,
      resources: options.resources?.split(','),
    };

    console.log('Starting sync...');
    const result = await syncService.sync(syncOptions);

    console.log('✓ Sync complete');
    console.log(`  Customers: ${result.stats.customers}`);
    console.log(`  Subscriptions: ${result.stats.subscriptions}`);
    console.log(`  Duration: ${result.duration}ms`);

    await db.disconnect();
  });

// Start server
program
  .command('server')
  .description('Start webhook server')
  .option('-p, --port <number>', 'Port number', '3001')
  .action(async (options) => {
    const config = loadConfig({ port: parseInt(options.port) });
    const server = await createServer(config);

    await server.listen({
      port: config.port,
      host: config.host
    });

    console.log(`✓ Server running on http://${config.host}:${config.port}`);
    console.log(`  Webhook: http://${config.host}:${config.port}/webhook`);
    console.log(`  API: http://${config.host}:${config.port}/api`);
  });

// Status command
program
  .command('status')
  .description('Show sync status and statistics')
  .action(async () => {
    const db = new StripeDatabase();
    await db.connect();

    const stats = await db.getStats();

    console.log('Plugin Status:');
    console.log(`  Customers: ${stats.customers}`);
    console.log(`  Subscriptions: ${stats.subscriptions}`);
    console.log(`  Invoices: ${stats.invoices}`);
    console.log(`  Last Sync: ${stats.lastSyncedAt?.toISOString() ?? 'Never'}`);

    await db.disconnect();
  });

// Query commands
program
  .command('customers')
  .description('List customers')
  .option('-l, --limit <number>', 'Number of results', '10')
  .action(async (options) => {
    const db = new StripeDatabase();
    await db.connect();

    const customers = await db.listCustomers(parseInt(options.limit));
    console.table(customers.map(c => ({
      id: c.id,
      email: c.email,
      name: c.name,
      created: c.created_at,
    })));

    await db.disconnect();
  });

program.parse();

// ✓ Correct
import { StripeClient } from './client.js';
import { createLogger } from '@nself/plugin-utils';

// ✗ Wrong
import { StripeClient } from './client';
import { StripeClient } from './client.ts';

async initializeSchema(): Promise<void> {
  // 1. Create extension for UUID generation
  await this.db.execute('CREATE EXTENSION IF NOT EXISTS "uuid-ossp"');

  // 2. Create tables in dependency order
  await this.createCoreObjectTables();
  await this.createBillingTables();
  await this.createPaymentTables();
  await this.createWebhookTables();

  // 3. Create indexes for performance
  await this.createIndexes();

  // 4. Create analytics views
  await this.createViews();

  // 5. Create functions and triggers (if needed)
  await this.createFunctions();
}

class StripeWebhookHandler {
  private handlers: Map<string, WebhookHandlerFn> = new Map();

  private registerDefaultHandlers(): void {
    // Customer lifecycle
    this.register('customer.created', async (event) => {
      const customer = event.data.object as Stripe.Customer;
      await this.db.upsertCustomer(this.client.mapCustomer(customer));
    });

    this.register('customer.updated', async (event) => {
      const customer = event.data.object as Stripe.Customer;
      await this.db.upsertCustomer(this.client.mapCustomer(customer));
    });

    this.register('customer.deleted', async (event) => {
      const customer = event.data.object as Stripe.Customer;
      await this.db.deleteCustomer(customer.id);
    });

    // Subscription lifecycle
    this.register('customer.subscription.created', async (event) => {
      const sub = event.data.object as Stripe.Subscription;
      // Re-fetch with expansions for complete data
      const fullSub = await this.client.getSubscription(sub.id);
      if (fullSub) {
        await this.db.upsertSubscription(fullSub);
        // Also sync subscription items
        for (const item of fullSub.items) {
          await this.db.upsertSubscriptionItem(item);
        }
      }
    });

    // Invoice events
    this.register('invoice.paid', async (event) => {
      const invoice = event.data.object as Stripe.Invoice;
      await this.db.upsertInvoice(this.client.mapInvoice(invoice));
      // Update subscription status if applicable
      if (invoice.subscription) {
        const sub = await this.client.getSubscription(
          invoice.subscription as string
        );
        if (sub) {
          await this.db.upsertSubscription(sub);
        }
      }
    });

    this.register('invoice.payment_failed', async (event) => {
      const invoice = event.data.object as Stripe.Invoice;
      await this.db.upsertInvoice(this.client.mapInvoice(invoice));
      // Potentially trigger alerting logic here
      logger.warn(`Payment failed for invoice ${invoice.id}`, {
        customerId: invoice.customer,
        amount: invoice.amount_due,
      });
    });

    // ... 70+ more handlers
  }

  register(eventType: string, handler: WebhookHandlerFn): void {
    this.handlers.set(eventType, handler);
  }

  async processEvent(event: Stripe.Event): Promise<void> {
    const handler = this.handlers.get(event.type);

    if (!handler) {
      logger.warn(`No handler registered for ${event.type}`);
      return;
    }

    await handler(event);
  }
}

// Background job to retry failed webhook events
async retryFailedEvents(): Promise<void> {
  const failedEvents = await this.db.query<WebhookEventRecord>(
    `SELECT * FROM stripe_webhook_events
     WHERE processed = FALSE
       AND retry_count < 3
       AND created_at > NOW() - INTERVAL '24 hours'
     ORDER BY created_at ASC
     LIMIT 100`
  );

  for (const eventRecord of failedEvents.rows) {
    try {
      // Reconstruct Stripe event
      const event: Stripe.Event = {
        id: eventRecord.id,
        type: eventRecord.type,
        data: eventRecord.data,
        created: Math.floor(eventRecord.created_at.getTime() / 1000),
        // ... other fields
      };

      await this.processEvent(event);

      await this.db.execute(
        `UPDATE stripe_webhook_events
         SET processed = TRUE, processed_at = NOW()
         WHERE id = $1`,
        [event.id]
      );

      logger.info(`Retry successful for event ${event.id}`);
    } catch (error) {
      const message = error instanceof Error ? error.message : 'Unknown error';

      await this.db.execute(
        `UPDATE stripe_webhook_events
         SET retry_count = retry_count + 1, error = $2
         WHERE id = $1`,
        [eventRecord.id, message]
      );

      logger.error(`Retry failed for event ${eventRecord.id}`, { error: message });
    }
  }
}

// Basic liveness
app.get('/health', async () => ({
  status: 'ok',
  plugin: 'stripe',
  timestamp: new Date().toISOString(),
}));

// Readiness (database check)
app.get('/ready', async (_, reply) => {
  try {
    await db.query('SELECT 1');
    return { ready: true };
  } catch (error) {
    return reply.status(503).send({ ready: false, error: 'Database unavailable' });
  }
});

// Liveness with metrics
app.get('/live', async () => {
  const stats = await db.getStats();
  return {
    alive: true,
    uptime: process.uptime(),
    memory: process.memoryUsage(),
    stats: {
      customers: stats.customers,
      subscriptions: stats.subscriptions,
    },
  };
});

app.post('/webhook', async (request, reply) => {
  // Already covered in webhook section
});

// Full sync
app.post('/api/sync', async (request) => {
  const options = request.body as SyncOptions;
  const result = await syncService.sync(options);
  return result;
});

// Incremental sync
app.post('/api/sync/incremental', async (request) => {
  const { since } = request.body as { since: string };
  const result = await syncService.incrementalSync(new Date(since));
  return result;
});

// Sync specific resource
app.post('/api/sync/:resource', async (request) => {
  const { resource } = request.params;
  const result = await syncService.sync({ resources: [resource] });
  return result;
});

// List customers
app.get('/api/customers', async (request) => {
  const { limit = '100', offset = '0', email } = request.query as Record<string, string>;

  let query = 'SELECT * FROM stripe_customers WHERE deleted_at IS NULL';
  const params: unknown[] = [];

  if (email) {
    params.push(`%${email}%`);
    query += ` AND email ILIKE $${params.length}`;
  }

  query += ` ORDER BY created_at DESC LIMIT $${params.length + 1} OFFSET $${params.length + 2}`;
  params.push(parseInt(limit), parseInt(offset));

  const result = await db.query(query, params);

  return {
    data: result.rows,
    total: result.rowCount,
    limit: parseInt(limit),
    offset: parseInt(offset),
  };
});

// Get customer by ID
app.get('/api/customers/:id', async (request, reply) => {
  const { id } = request.params;
  const customer = await db.getCustomer(id);

  if (!customer) {
    return reply.status(404).send({ error: 'Customer not found' });
  }

  return { data: customer };
});

// List subscriptions
app.get('/api/subscriptions', async (request) => {
  const { status, customer_id } = request.query as Record<string, string>;
  const subscriptions = await db.listSubscriptions({ status, customer_id });
  return { data: subscriptions };
});

// Get MRR
app.get('/api/mrr', async () => {
  const mrr = await db.query('SELECT * FROM stripe_mrr ORDER BY month DESC LIMIT 12');
  return { data: mrr.rows };
});

// Get customer LTV
app.get('/api/ltv', async (request) => {
  const { limit = '100' } = request.query as Record<string, string>;
  const ltv = await db.query(
    'SELECT * FROM stripe_customer_ltv ORDER BY total_revenue DESC LIMIT $1',
    [parseInt(limit)]
  );
  return { data: ltv.rows };
});

app.get('/api/status', async () => {
  const stats = await db.getStats();
  return {
    plugin: 'stripe',
    version: '1.0.0',
    stats,
    timestamp: new Date().toISOString(),
  };
});

app.get('/api/stats/overview', async () => {
  const [customers, subscriptions, invoices, mrr] = await Promise.all([
    db.query('SELECT COUNT(*) FROM stripe_customers WHERE deleted_at IS NULL'),
    db.query('SELECT COUNT(*) FROM stripe_subscriptions WHERE status IN (\'active\', \'trialing\') AND deleted_at IS NULL'),
    db.query('SELECT COUNT(*), SUM(amount_paid) FROM stripe_invoices WHERE status = \'paid\' AND deleted_at IS NULL'),
    db.query('SELECT SUM(mrr_cents) AS total_mrr FROM stripe_mrr WHERE month = DATE_TRUNC(\'month\', NOW())'),
  ]);

  return {
    customers: parseInt(customers.rows[0].count),
    activeSubscriptions: parseInt(subscriptions.rows[0].count),
    paidInvoices: parseInt(invoices.rows[0].count),
    totalRevenue: parseFloat(invoices.rows[0].sum ?? 0) / 100,
    currentMRR: parseFloat(mrr.rows[0]?.total_mrr ?? 0),
  };
});

program
  .name('nself-stripe')
  .description('Stripe plugin for nself')
  .version('1.0.0');

// Global options
program
  .option('-v, --verbose', 'Enable verbose logging')
  .option('--config <path>', 'Path to config file')
  .hook('preAction', (thisCommand) => {
    const options = thisCommand.opts();
    if (options.verbose) {
      process.env.LOG_LEVEL = 'debug';
    }
  });

// Commands grouped by category

program
  .command('init')
  .description('Initialize database schema')
  .option('--drop', 'Drop existing tables before creating')
  .action(async (options) => {
    const db = new StripeDatabase();
    await db.connect();

    if (options.drop) {
      console.log('⚠️  Dropping existing tables...');
      await db.dropSchema();
    }

    await db.initializeSchema();
    console.log('✓ Database schema initialized');

    await db.disconnect();
  });

program
  .command('setup')
  .description('Interactive setup wizard')
  .action(async () => {
    const inquirer = (await import('inquirer')).default;

    const answers = await inquirer.prompt([
      {
        type: 'password',
        name: 'apiKey',
        message: 'Stripe API Key:',
        validate: (input) => input.startsWith('sk_') || 'Invalid API key format',
      },
      {
        type: 'password',
        name: 'webhookSecret',
        message: 'Webhook Secret (optional):',
      },
      {
        type: 'input',
        name: 'databaseUrl',
        message: 'Database URL:',
        default: 'postgresql://localhost:5432/nself',
      },
    ]);

    // Save to .env file
    const envContent = `
STRIPE_API_KEY=${answers.apiKey}
STRIPE_WEBHOOK_SECRET=${answers.webhookSecret}
DATABASE_URL=${answers.databaseUrl}
PORT=3001
HOST=0.0.0.0
    `.trim();

    await fs.writeFile('.env', envContent);
    console.log('✓ Configuration saved to .env');
  });

program
  .command('sync')
  .description('Sync all Stripe data to database')
  .option('-r, --resources <items>', 'Resources to sync (comma-separated)')
  .option('-i, --incremental', 'Incremental sync only')
  .option('--since <date>', 'Sync data since date (ISO format)')
  .action(async (options) => {
    const config = loadConfig();
    const client = new StripeClient(config.stripeApiKey);
    const db = new StripeDatabase();
    const syncService = new StripeSyncService(client, db);

    await db.connect();

    const syncOptions: SyncOptions = {
      incremental: options.incremental,
      since: options.since ? new Date(options.since) : undefined,
      resources: options.resources?.split(','),
    };

    console.log('🔄 Starting sync...');
    const result = await syncService.sync(syncOptions);

    console.log('✓ Sync complete');
    console.log(`  Duration: ${result.duration}ms`);
    console.log(`  Customers: ${result.stats.customers}`);
    console.log(`  Subscriptions: ${result.stats.subscriptions}`);
    console.log(`  Invoices: ${result.stats.invoices}`);

    await db.disconnect();
  });

program
  .command('server')
  .description('Start webhook server')
  .option('-p, --port <number>', 'Port number', '3001')
  .option('-h, --host <string>', 'Host address', '0.0.0.0')
  .option('--no-auth', 'Disable API key authentication')
  .action(async (options) => {
    const config = loadConfig({
      port: parseInt(options.port),
      host: options.host,
      security: {
        ...loadConfig().security,
        apiKey: options.auth ? loadConfig().security.apiKey : undefined,
      },
    });

    const server = await createServer(config);

    await server.listen({ port: config.port, host: config.host });

    console.log('✓ Server running');
    console.log(`  URL: http://${config.host}:${config.port}`);
    console.log(`  Webhook: http://${config.host}:${config.port}/webhook`);
    console.log(`  API: http://${config.host}:${config.port}/api`);
    console.log(`  Auth: ${config.security.apiKey ? 'Enabled' : 'Disabled'}`);
  });

program
  .command('customers')
  .description('List customers')
  .option('-l, --limit <number>', 'Number of results', '10')
  .option('-e, --email <email>', 'Filter by email')
  .option('--json', 'Output as JSON')
  .action(async (options) => {
    const db = new StripeDatabase();
    await db.connect();

    const customers = await db.listCustomers({
      limit: parseInt(options.limit),
      email: options.email,
    });

    if (options.json) {
      console.log(JSON.stringify(customers, null, 2));
    } else {
      console.table(customers.map(c => ({
        ID: c.id.substring(0, 20),
        Email: c.email ?? 'N/A',
        Name: c.name ?? 'N/A',
        Created: c.created_at.toLocaleDateString(),
      })));
    }

    await db.disconnect();
  });

program
  .command('subscriptions')
  .description('List subscriptions')
  .option('-s, --status <status>', 'Filter by status')
  .option('-c, --customer <id>', 'Filter by customer ID')
  .action(async (options) => {
    const db = new StripeDatabase();
    await db.connect();

    const subscriptions = await db.listSubscriptions({
      status: options.status,
      customer_id: options.customer,
    });

    console.table(subscriptions.map(s => ({
      ID: s.id.substring(0, 20),
      Customer: s.customer_id.substring(0, 20),
      Status: s.status,
      'Current Period': `${s.current_period_start.toLocaleDateString()} - ${s.current_period_end.toLocaleDateString()}`,
    })));

    await db.disconnect();
  });

program
  .command('mrr')
  .description('Show monthly recurring revenue')
  .option('-m, --months <number>', 'Number of months', '12')
  .action(async (options) => {
    const db = new StripeDatabase();
    await db.connect();

    const mrr = await db.query(
      'SELECT * FROM stripe_mrr ORDER BY month DESC LIMIT $1',
      [parseInt(options.months)]
    );

    console.table(mrr.rows.map(row => ({
      Month: new Date(row.month).toLocaleDateString('en-US', { year: 'numeric', month: 'long' }),
      Subscriptions: row.subscription_count,
      Customers: row.unique_customers,
      'MRR ($)': row.mrr_cents.toFixed(2),
    })));

    await db.disconnect();
  });

export class Logger {
  private name: string;
  private level: LogLevel;

  constructor(name: string, level: LogLevel = 'info') {
    this.name = name;
    this.level = level;
  }

  debug(message: string, meta?: Record<string, unknown>): void {
    if (this.shouldLog('debug')) {
      this.log('debug', message, meta);
    }
  }

  info(message: string, meta?: Record<string, unknown>): void {
    if (this.shouldLog('info')) {
      this.log('info', message, meta);
    }
  }

  warn(message: string, meta?: Record<string, unknown>): void {
    if (this.shouldLog('warn')) {
      this.log('warn', message, meta);
    }
  }

  error(message: string, error?: Error | unknown): void {
    const meta = error instanceof Error
      ? { error: error.message, stack: error.stack }
      : { error };
    this.log('error', message, meta);
  }

  success(message: string, meta?: Record<string, unknown>): void {
    this.log('info', `✓ ${message}`, meta);
  }

  private log(level: LogLevel, message: string, meta?: Record<string, unknown>): void {
    const timestamp = new Date().toISOString();
    const levelStr = level.toUpperCase().padEnd(5);
    const metaStr = meta ? ` ${JSON.stringify(meta)}` : '';

    console.log(`${timestamp} ${levelStr} [${this.name}] ${message}${metaStr}`);
  }
}

export function createLogger(name: string): Logger {
  const level = (process.env.LOG_LEVEL ?? 'info') as LogLevel;
  return new Logger(name, level);
}

export class Database {
  private pool: pg.Pool;
  private connected = false;

  constructor(config: DatabaseConfig) {
    this.pool = new Pool({
      host: config.host,
      port: config.port,
      database: config.database,
      user: config.user,
      password: config.password,
      ssl: config.ssl ? { rejectUnauthorized: false } : undefined,
      max: config.maxConnections ?? 10,
      idleTimeoutMillis: 30000,
      connectionTimeoutMillis: 5000,
    });

    this.pool.on('error', (err) => {
      logger.error('Unexpected database pool error', err);
    });
  }

  async connect(): Promise<void> {
    if (this.connected) return;

    const client = await this.pool.connect();
    client.release();
    this.connected = true;
  }

  async query<T>(text: string, params?: unknown[]): Promise<pg.QueryResult<T>> {
    return this.pool.query<T>(text, params);
  }

  async execute(text: string, params?: unknown[]): Promise<number> {
    const result = await this.pool.query(text, params);
    return result.rowCount ?? 0;
  }

  async transaction<T>(callback: (client: pg.PoolClient) => Promise<T>): Promise<T> {
    const client = await this.pool.connect();
    try {
      await client.query('BEGIN');
      const result = await callback(client);
      await client.query('COMMIT');
      return result;
    } catch (error) {
      await client.query('ROLLBACK');
      throw error;
    } finally {
      client.release();
    }
  }
}

export function createDatabase(config?: DatabaseConfig): Database {
  const dbConfig = config ?? {
    host: process.env.DB_HOST ?? 'localhost',
    port: parseInt(process.env.DB_PORT ?? '5432'),
    database: process.env.DB_NAME ?? 'nself',
    user: process.env.DB_USER ?? 'postgres',
    password: process.env.DB_PASSWORD ?? '',
    ssl: process.env.DB_SSL === 'true',
  };

  return new Database(dbConfig);
}

export class HttpClient {
  private config: HttpClientConfig;

  async get<T>(endpoint: string, params?: Record<string, string>): Promise<T> {
    return this.request<T>('GET', endpoint, { params });
  }

  async post<T>(endpoint: string, data?: unknown): Promise<T> {
    return this.request<T>('POST', endpoint, { body: data });
  }

  private async request<T>(
    method: string,
    endpoint: string,
    options?: { body?: unknown; params?: Record<string, string> }
  ): Promise<T> {
    let url = `${this.config.baseUrl}${endpoint}`;

    if (options?.params) {
      const searchParams = new URLSearchParams(options.params);
      url += `?${searchParams.toString()}`;
    }

    const response = await fetch(url, {
      method,
      headers: {
        'Content-Type': 'application/json',
        ...this.config.headers,
      },
      body: options?.body ? JSON.stringify(options.body) : undefined,
    });

    if (!response.ok) {
      throw new Error(`HTTP ${response.status}: ${response.statusText}`);
    }

    return response.json();
  }
}