/**
 * Database operations using better-sqlite3
 */

import Database from 'better-sqlite3';
import { Deployment, Alert, Cost, Metric } from './types';

export class DatabaseManager {
  private db: Database.Database;

  constructor(dbPath: string) {
    this.db = new Database(dbPath);
    this.db.pragma('journal_mode = WAL');
    this.initDatabase();
  }

  private initDatabase(): void {
    // Deployments table
    this.db.exec(`
      CREATE TABLE IF NOT EXISTS deployments (
        id TEXT PRIMARY KEY,
        environment TEXT NOT NULL,
        status TEXT NOT NULL,
        started_at TEXT NOT NULL,
        completed_at TEXT,
        triggered_by TEXT,
        strategy TEXT,
        version TEXT,
        logs_path TEXT
      )
    `);

    // Metrics table
    this.db.exec(`
      CREATE TABLE IF NOT EXISTS metrics (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        environment TEXT NOT NULL,
        metric_name TEXT NOT NULL,
        metric_value REAL NOT NULL,
        timestamp TEXT NOT NULL
      )
    `);

    // Alerts table
    this.db.exec(`
      CREATE TABLE IF NOT EXISTS alerts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        environment TEXT NOT NULL,
        severity TEXT NOT NULL,
        message TEXT NOT NULL,
        timestamp TEXT NOT NULL,
        acknowledged BOOLEAN DEFAULT 0
      )
    `);

    // Costs table
    this.db.exec(`
      CREATE TABLE IF NOT EXISTS costs (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        environment TEXT NOT NULL,
        provider TEXT NOT NULL,
        cost REAL NOT NULL,
        currency TEXT DEFAULT 'USD',
        period_start TEXT NOT NULL,
        period_end TEXT NOT NULL,
        resource_type TEXT
      )
    `);

    // Admin users table
    this.db.exec(`
      CREATE TABLE IF NOT EXISTS admin_users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT UNIQUE NOT NULL,
        email TEXT UNIQUE NOT NULL,
        password_hash TEXT NOT NULL,
        role TEXT DEFAULT 'admin',
        created_at TEXT NOT NULL,
        last_login TEXT,
        is_active BOOLEAN DEFAULT 1
      )
    `);

    // Service configuration table
    this.db.exec(`
      CREATE TABLE IF NOT EXISTS service_config (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        service_name TEXT UNIQUE NOT NULL,
        enabled BOOLEAN DEFAULT 1,
        config_json TEXT,
        updated_at TEXT NOT NULL,
        updated_by TEXT
      )
    `);

    // Provider configuration table
    this.db.exec(`
      CREATE TABLE IF NOT EXISTS provider_config (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        provider_name TEXT UNIQUE NOT NULL,
        enabled BOOLEAN DEFAULT 1,
        config_json TEXT,
        updated_at TEXT NOT NULL,
        updated_by TEXT
      )
    `);

    // Admin audit log table
    this.db.exec(`
      CREATE TABLE IF NOT EXISTS admin_audit_log (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        admin_user TEXT NOT NULL,
        action TEXT NOT NULL,
        resource_type TEXT NOT NULL,
        resource_id TEXT,
        details TEXT,
        ip_address TEXT,
        timestamp TEXT NOT NULL
      )
    `);

    // Service monitoring table
    this.db.exec(`
      CREATE TABLE IF NOT EXISTS service_monitoring (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        service_name TEXT NOT NULL,
        service_type TEXT NOT NULL,
        environment TEXT,
        metric_name TEXT NOT NULL,
        metric_value REAL NOT NULL,
        metric_unit TEXT,
        status TEXT NOT NULL,
        timestamp TEXT NOT NULL,
        metadata TEXT
      )
    `);

    // Create indexes for faster queries
    this.db.exec(`
      CREATE INDEX IF NOT EXISTS idx_service_monitoring_service ON service_monitoring(service_name, timestamp);
      CREATE INDEX IF NOT EXISTS idx_service_monitoring_type ON service_monitoring(service_type, timestamp);
      CREATE INDEX IF NOT EXISTS idx_service_monitoring_env ON service_monitoring(environment, timestamp);
    `);
  }

  // Deployment operations
  createDeployment(deployment: Deployment): void {
    const stmt = this.db.prepare(`
      INSERT INTO deployments (id, environment, status, started_at, completed_at, triggered_by, strategy, version, logs_path)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    `);
    stmt.run(
      deployment.id,
      deployment.environment,
      deployment.status,
      deployment.started_at,
      deployment.completed_at || null,
      deployment.triggered_by || null,
      deployment.strategy || null,
      deployment.version || null,
      deployment.logs_path || null
    );
  }

  getDeployments(filters: {
    environment?: string;
    status?: string;
    limit?: number;
  } = {}): Deployment[] {
    let query = 'SELECT id, environment, status, started_at, completed_at, strategy, version FROM deployments WHERE 1=1';
    const params: any[] = [];

    if (filters.environment) {
      query += ' AND environment = ?';
      params.push(filters.environment);
    }

    if (filters.status) {
      query += ' AND status = ?';
      params.push(filters.status);
    }

    query += ' ORDER BY started_at DESC LIMIT ?';
    params.push(filters.limit || 50);

    const stmt = this.db.prepare(query);
    const rows = stmt.all(...params) as any[];

    return rows.map(row => ({
      id: row.id,
      environment: row.environment,
      status: row.status,
      started_at: row.started_at,
      completed_at: row.completed_at,
      strategy: row.strategy,
      version: row.version
    }));
  }

  getDeploymentLogsPath(deploymentId: string): string | null {
    const stmt = this.db.prepare('SELECT logs_path FROM deployments WHERE id = ?');
    const row = stmt.get(deploymentId) as any;
    return row?.logs_path || null;
  }

  getDeploymentCount(environment: string, status: string = 'completed'): number {
    const stmt = this.db.prepare(`
      SELECT COUNT(*) as count FROM deployments 
      WHERE environment = ? AND status = ?
    `);
    const row = stmt.get(environment, status) as any;
    return row?.count || 0;
  }

  getLastDeployment(environment: string): string | null {
    const stmt = this.db.prepare(`
      SELECT started_at FROM deployments 
      WHERE environment = ? 
      ORDER BY started_at DESC LIMIT 1
    `);
    const row = stmt.get(environment) as any;
    return row?.started_at || null;
  }

  // Metrics operations
  getMetrics(environment: string, hours: number = 24): Record<string, Metric[]> {
    const since = new Date(Date.now() - hours * 60 * 60 * 1000).toISOString();
    const stmt = this.db.prepare(`
      SELECT metric_name, metric_value, timestamp 
      FROM metrics 
      WHERE environment = ? AND timestamp >= ?
      ORDER BY timestamp ASC
    `);
    const rows = stmt.all(environment, since) as any[];

    const metrics: Record<string, Metric[]> = {};
    for (const row of rows) {
      if (!metrics[row.metric_name]) {
        metrics[row.metric_name] = [];
      }
      metrics[row.metric_name].push({
        value: row.metric_value,
        timestamp: row.timestamp
      });
    }

    return metrics;
  }

  insertMetric(environment: string, metricName: string, value: number, timestamp: string): void {
    const stmt = this.db.prepare(`
      INSERT INTO metrics (environment, metric_name, metric_value, timestamp)
      VALUES (?, ?, ?, ?)
    `);
    stmt.run(environment, metricName, value, timestamp);
  }

  // Alerts operations
  getAlerts(filters: {
    environment?: string;
    unacknowledged_only?: boolean;
  } = {}): Alert[] {
    let query = 'SELECT id, environment, severity, message, timestamp FROM alerts WHERE 1=1';
    const params: any[] = [];

    if (filters.environment) {
      query += ' AND environment = ?';
      params.push(filters.environment);
    }

    if (filters.unacknowledged_only) {
      query += ' AND acknowledged = 0';
    }

    query += ' ORDER BY timestamp DESC';

    const stmt = this.db.prepare(query);
    const rows = stmt.all(...params) as any[];

    return rows.map(row => ({
      id: row.id,
      environment: row.environment,
      severity: row.severity,
      message: row.message,
      timestamp: row.timestamp
    }));
  }

  createAlert(alert: Omit<Alert, 'id'>): void {
    const stmt = this.db.prepare(`
      INSERT INTO alerts (environment, severity, message, timestamp)
      VALUES (?, ?, ?, ?)
    `);
    stmt.run(alert.environment, alert.severity, alert.message, alert.timestamp);
  }

  acknowledgeAlert(alertId: number): void {
    const stmt = this.db.prepare('UPDATE alerts SET acknowledged = 1 WHERE id = ?');
    stmt.run(alertId);
  }

  // Costs operations
  getCosts(filters: {
    environment?: string;
    days?: number;
  } = {}): Cost[] {
    const days = filters.days || 30;
    const since = new Date(Date.now() - days * 24 * 60 * 60 * 1000).toISOString();

    let query = `
      SELECT environment, provider, cost, currency, period_start, period_end, resource_type
      FROM costs 
      WHERE period_start >= ?
    `;
    const params: any[] = [since];

    if (filters.environment) {
      query += ' AND environment = ?';
      params.push(filters.environment);
    }

    query += ' ORDER BY period_start DESC';

    const stmt = this.db.prepare(query);
    const rows = stmt.all(...params) as any[];

    return rows.map(row => ({
      environment: row.environment,
      provider: row.provider,
      cost: row.cost,
      currency: row.currency,
      period_start: row.period_start,
      period_end: row.period_end,
      resource_type: row.resource_type
    }));
  }

  insertCost(cost: Cost): void {
    const stmt = this.db.prepare(`
      INSERT INTO costs (environment, provider, cost, currency, period_start, period_end, resource_type)
      VALUES (?, ?, ?, ?, ?, ?, ?)
    `);
    stmt.run(
      cost.environment,
      cost.provider,
      cost.cost,
      cost.currency || 'USD',
      cost.period_start,
      cost.period_end,
      cost.resource_type || null
    );
  }

  getMetricsCount(): number {
    const stmt = this.db.prepare('SELECT COUNT(*) as count FROM metrics');
    const row = stmt.get() as any;
    return row?.count || 0;
  }

  // Admin operations
  getServiceConfig(serviceName: string): { enabled: boolean; config: any } | null {
    const stmt = this.db.prepare('SELECT enabled, config_json FROM service_config WHERE service_name = ?');
    const row = stmt.get(serviceName) as any;
    if (!row) return null;
    return {
      enabled: row.enabled === 1,
      config: row.config_json ? JSON.parse(row.config_json) : null
    };
  }

  getAllServiceConfigs(): Array<{ service_name: string; enabled: boolean; config: any; updated_at: string; updated_by: string | null }> {
    const stmt = this.db.prepare('SELECT service_name, enabled, config_json, updated_at, updated_by FROM service_config');
    const rows = stmt.all() as any[];
    return rows.map(row => ({
      service_name: row.service_name,
      enabled: row.enabled === 1,
      config: row.config_json ? JSON.parse(row.config_json) : null,
      updated_at: row.updated_at,
      updated_by: row.updated_by
    }));
  }

  setServiceConfig(serviceName: string, enabled: boolean, config: any, updatedBy: string): void {
    const stmt = this.db.prepare(`
      INSERT INTO service_config (service_name, enabled, config_json, updated_at, updated_by)
      VALUES (?, ?, ?, ?, ?)
      ON CONFLICT(service_name) DO UPDATE SET
        enabled = excluded.enabled,
        config_json = excluded.config_json,
        updated_at = excluded.updated_at,
        updated_by = excluded.updated_by
    `);
    stmt.run(
      serviceName,
      enabled ? 1 : 0,
      config ? JSON.stringify(config) : null,
      new Date().toISOString(),
      updatedBy
    );
  }

  getProviderConfig(providerName: string): { enabled: boolean; config: any } | null {
    const stmt = this.db.prepare('SELECT enabled, config_json FROM provider_config WHERE provider_name = ?');
    const row = stmt.get(providerName) as any;
    if (!row) return null;
    return {
      enabled: row.enabled === 1,
      config: row.config_json ? JSON.parse(row.config_json) : null
    };
  }

  getAllProviderConfigs(): Array<{ provider_name: string; enabled: boolean; config: any; updated_at: string; updated_by: string | null }> {
    const stmt = this.db.prepare('SELECT provider_name, enabled, config_json, updated_at, updated_by FROM provider_config');
    const rows = stmt.all() as any[];
    return rows.map(row => ({
      provider_name: row.provider_name,
      enabled: row.enabled === 1,
      config: row.config_json ? JSON.parse(row.config_json) : null,
      updated_at: row.updated_at,
      updated_by: row.updated_by
    }));
  }

  setProviderConfig(providerName: string, enabled: boolean, config: any, updatedBy: string): void {
    const stmt = this.db.prepare(`
      INSERT INTO provider_config (provider_name, enabled, config_json, updated_at, updated_by)
      VALUES (?, ?, ?, ?, ?)
      ON CONFLICT(provider_name) DO UPDATE SET
        enabled = excluded.enabled,
        config_json = excluded.config_json,
        updated_at = excluded.updated_at,
        updated_by = excluded.updated_by
    `);
    stmt.run(
      providerName,
      enabled ? 1 : 0,
      config ? JSON.stringify(config) : null,
      new Date().toISOString(),
      updatedBy
    );
  }

  logAdminAction(adminUser: string, action: string, resourceType: string, resourceId: string | null, details: string | null, ipAddress: string): void {
    const stmt = this.db.prepare(`
      INSERT INTO admin_audit_log (admin_user, action, resource_type, resource_id, details, ip_address, timestamp)
      VALUES (?, ?, ?, ?, ?, ?, ?)
    `);
    stmt.run(
      adminUser,
      action,
      resourceType,
      resourceId,
      details,
      ipAddress,
      new Date().toISOString()
    );
  }

  getAuditLogs(limit: number = 100): Array<{ id: number; admin_user: string; action: string; resource_type: string; resource_id: string | null; details: string | null; ip_address: string; timestamp: string }> {
    const stmt = this.db.prepare(`
      SELECT id, admin_user, action, resource_type, resource_id, details, ip_address, timestamp
      FROM admin_audit_log
      ORDER BY timestamp DESC
      LIMIT ?
    `);
    const rows = stmt.all(limit) as any[];
    return rows.map(row => ({
      id: row.id,
      admin_user: row.admin_user,
      action: row.action,
      resource_type: row.resource_type,
      resource_id: row.resource_id,
      details: row.details,
      ip_address: row.ip_address,
      timestamp: row.timestamp
    }));
  }

  // Service monitoring operations
  insertServiceMetric(
    serviceName: string,
    serviceType: string,
    metricName: string,
    metricValue: number,
    status: string,
    environment?: string,
    metricUnit?: string,
    metadata?: any
  ): void {
    const stmt = this.db.prepare(`
      INSERT INTO service_monitoring 
      (service_name, service_type, environment, metric_name, metric_value, metric_unit, status, timestamp, metadata)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    `);
    stmt.run(
      serviceName,
      serviceType,
      environment || null,
      metricName,
      metricValue,
      metricUnit || null,
      status,
      new Date().toISOString(),
      metadata ? JSON.stringify(metadata) : null
    );
  }

  getServiceMetrics(filters: {
    serviceName?: string;
    serviceType?: string;
    environment?: string;
    hours?: number;
    limit?: number;
  } = {}): Array<{
    id: number;
    service_name: string;
    service_type: string;
    environment: string | null;
    metric_name: string;
    metric_value: number;
    metric_unit: string | null;
    status: string;
    timestamp: string;
    metadata: any;
  }> {
    const hours = filters.hours || 24;
    const since = new Date(Date.now() - hours * 60 * 60 * 1000).toISOString();
    
    let query = `
      SELECT id, service_name, service_type, environment, metric_name, metric_value, 
             metric_unit, status, timestamp, metadata
      FROM service_monitoring
      WHERE timestamp >= ?
    `;
    const params: any[] = [since];

    if (filters.serviceName) {
      query += ' AND service_name = ?';
      params.push(filters.serviceName);
    }

    if (filters.serviceType) {
      query += ' AND service_type = ?';
      params.push(filters.serviceType);
    }

    if (filters.environment) {
      query += ' AND environment = ?';
      params.push(filters.environment);
    }

    query += ' ORDER BY timestamp DESC LIMIT ?';
    params.push(filters.limit || 1000);

    const stmt = this.db.prepare(query);
    const rows = stmt.all(...params) as any[];

    return rows.map(row => ({
      id: row.id,
      service_name: row.service_name,
      service_type: row.service_type,
      environment: row.environment,
      metric_name: row.metric_name,
      metric_value: row.metric_value,
      metric_unit: row.metric_unit,
      status: row.status,
      timestamp: row.timestamp,
      metadata: row.metadata ? JSON.parse(row.metadata) : null
    }));
  }

  getServiceStatus(serviceName: string, serviceType: string): {
    status: string;
    lastUpdate: string;
    metrics: Record<string, number>;
  } | null {
    const stmt = this.db.prepare(`
      SELECT status, metric_name, metric_value, timestamp
      FROM service_monitoring
      WHERE service_name = ? AND service_type = ?
      ORDER BY timestamp DESC
      LIMIT 50
    `);
    const rows = stmt.all(serviceName, serviceType) as any[];

    if (rows.length === 0) return null;

    const metrics: Record<string, number> = {};
    let latestStatus = 'unknown';
    let latestTimestamp = '';

    for (const row of rows) {
      if (!latestTimestamp) {
        latestTimestamp = row.timestamp;
        latestStatus = row.status;
      }
      metrics[row.metric_name] = row.metric_value;
    }

    return {
      status: latestStatus,
      lastUpdate: latestTimestamp,
      metrics
    };
  }

  getServiceHealthSummary(serviceType: string): {
    total: number;
    healthy: number;
    degraded: number;
    unhealthy: number;
    services: Array<{ name: string; status: string; lastUpdate: string }>;
  } {
    const stmt = this.db.prepare(`
      SELECT DISTINCT service_name, status, MAX(timestamp) as last_update
      FROM service_monitoring
      WHERE service_type = ?
      GROUP BY service_name, status
      ORDER BY last_update DESC
    `);
    const rows = stmt.all(serviceType) as any[];

    const services: Array<{ name: string; status: string; lastUpdate: string }> = [];
    const statusCounts = { healthy: 0, degraded: 0, unhealthy: 0 };

    for (const row of rows) {
      services.push({
        name: row.service_name,
        status: row.status,
        lastUpdate: row.last_update
      });
      
      if (row.status === 'healthy') statusCounts.healthy++;
      else if (row.status === 'degraded') statusCounts.degraded++;
      else if (row.status === 'unhealthy') statusCounts.unhealthy++;
    }

    return {
      total: services.length,
      healthy: statusCounts.healthy,
      degraded: statusCounts.degraded,
      unhealthy: statusCounts.unhealthy,
      services
    };
  }

  close(): void {
    this.db.close();
  }
}

