/**
 * PostgreSQL implementation of MetricsServerConfigStore
 */

import { Pool, PoolClient } from 'pg';
import type { PoolConfig } from 'pg';
import {
  MetricsServerConfigStore,
  MetricsServerConfigEntry,
  CreateMetricsServerConfigRequest,
  UpdateMetricsServerConfigRequest,
  MetricsServerConfig,
} from '@axiom-lattice/protocols';
import { MigrationManager } from '../migrations/migration';
import { createMetricsConfigsTable } from '../migrations/metrics_config_migrations';
import { encrypt, decrypt } from '@axiom-lattice/core';

/**
 * PostgreSQL MetricsServerConfigStore options
 */
export interface PostgreSQLMetricsServerConfigStoreOptions {
  /**
   * PostgreSQL connection pool configuration
   * Can be a connection string or PoolConfig object
   */
  poolConfig: string | PoolConfig;

  /**
   * Whether to run migrations automatically on initialization
   * @default true
   */
  autoMigrate?: boolean;
}

/**
 * PostgreSQL implementation of MetricsServerConfigStore
 * 
 * Features:
 * - Multi-tenant isolation via tenant_id
 * - Automatic apiKey and password encryption/decryption
 * - Unique constraint on (tenant_id, key)
 */
export class PostgreSQLMetricsServerConfigStore implements MetricsServerConfigStore {
  private pool: Pool;
  private migrationManager: MigrationManager;
  private initialized: boolean = false;
  private initPromise: Promise<void> | null = null;

  constructor(options: PostgreSQLMetricsServerConfigStoreOptions) {
    // Create Pool from config
    if (typeof options.poolConfig === 'string') {
      this.pool = new Pool({ connectionString: options.poolConfig });
    } else {
      this.pool = new Pool(options.poolConfig);
    }

    this.migrationManager = new MigrationManager(this.pool);
    this.migrationManager.register(createMetricsConfigsTable);

    // Auto-migrate by default
    if (options.autoMigrate !== false) {
      this.initialize().catch((error) => {
        console.error('Failed to initialize PostgreSQLMetricsServerConfigStore:', error);
        throw error;
      });
    }
  }

  /**
   * Initialize the store and run migrations
   * Uses a promise-based lock to prevent concurrent initialization
   */
  async initialize(): Promise<void> {
    if (this.initialized) {
      return;
    }

    if (this.initPromise) {
      return this.initPromise;
    }

    this.initPromise = (async () => {
      try {
        await this.migrationManager.migrate();
        this.initialized = true;
      } finally {
        this.initPromise = null;
      }
    })();

    return this.initPromise;
  }

  /**
   * Get all metrics server configurations for a tenant
   */
  async getAllConfigs(tenantId: string): Promise<MetricsServerConfigEntry[]> {
    await this.ensureInitialized();

    const result = await this.pool.query<{
      id: string;
      tenant_id: string;
      key: string;
      name: string | null;
      description: string | null;
      config: any;
      created_at: Date;
      updated_at: Date;
    }>(
      `
      SELECT id, tenant_id, key, name, description, config, created_at, updated_at
      FROM lattice_metrics_configs
      WHERE tenant_id = $1
      ORDER BY created_at DESC
    `,
      [tenantId]
    );

    return result.rows.map((row) => this.mapRowToEntry(row));
  }

  /**
   * Get all metrics server configurations across all tenants
   */
  async getAllConfigsWithoutTenant(): Promise<MetricsServerConfigEntry[]> {
    await this.ensureInitialized();

    const result = await this.pool.query<{
      id: string;
      tenant_id: string;
      key: string;
      name: string | null;
      description: string | null;
      config: any;
      created_at: Date;
      updated_at: Date;
    }>(
      `
      SELECT id, tenant_id, key, name, description, config, created_at, updated_at
      FROM lattice_metrics_configs
      ORDER BY created_at DESC
    `
    );

    return result.rows.map((row) => this.mapRowToEntry(row));
  }

  /**
   * Get metrics server configuration by ID
   */
  async getConfigById(tenantId: string, id: string): Promise<MetricsServerConfigEntry | null> {
    await this.ensureInitialized();

    const result = await this.pool.query<{
      id: string;
      tenant_id: string;
      key: string;
      name: string | null;
      description: string | null;
      config: any;
      created_at: Date;
      updated_at: Date;
    }>(
      `
      SELECT id, tenant_id, key, name, description, config, created_at, updated_at
      FROM lattice_metrics_configs
      WHERE tenant_id = $1 AND id = $2
    `,
      [tenantId, id]
    );

    if (result.rows.length === 0) {
      return null;
    }

    return this.mapRowToEntry(result.rows[0]);
  }

  /**
   * Get metrics server configuration by business key
   */
  async getConfigByKey(tenantId: string, key: string): Promise<MetricsServerConfigEntry | null> {
    await this.ensureInitialized();

    const result = await this.pool.query<{
      id: string;
      tenant_id: string;
      key: string;
      name: string | null;
      description: string | null;
      config: any;
      created_at: Date;
      updated_at: Date;
    }>(
      `
      SELECT id, tenant_id, key, name, description, config, created_at, updated_at
      FROM lattice_metrics_configs
      WHERE tenant_id = $1 AND key = $2
    `,
      [tenantId, key]
    );

    if (result.rows.length === 0) {
      return null;
    }

    return this.mapRowToEntry(result.rows[0]);
  }

  /**
   * Create a new metrics server configuration
   */
  async createConfig(
    tenantId: string,
    id: string,
    data: CreateMetricsServerConfigRequest
  ): Promise<MetricsServerConfigEntry> {
    await this.ensureInitialized();

    const now = new Date();
    const nowString = now.toISOString();
    const configWithEncryptedSecrets = this.encryptSecretsInConfig(data.config);

    await this.pool.query(
      `
      INSERT INTO lattice_metrics_configs (id, tenant_id, key, name, description, config, created_at, updated_at)
      VALUES ($1, $2, $3, $4, $5, $6, $7::timestamp, $8::timestamp)
      ON CONFLICT (tenant_id, id) DO UPDATE SET
        key = EXCLUDED.key,
        name = EXCLUDED.name,
        description = EXCLUDED.description,
        config = EXCLUDED.config,
        updated_at = EXCLUDED.updated_at
    `,
      [
        id,
        tenantId,
        data.key,
        data.name || null,
        data.description || null,
        JSON.stringify(configWithEncryptedSecrets),
        nowString,
        nowString,
      ]
    );

    return {
      id,
      tenantId,
      key: data.key,
      config: data.config,
      name: data.name,
      description: data.description,
      createdAt: now,
      updatedAt: now,
    };
  }

  /**
   * Update an existing metrics server configuration
   */
  async updateConfig(
    tenantId: string,
    id: string,
    updates: Partial<UpdateMetricsServerConfigRequest>
  ): Promise<MetricsServerConfigEntry | null> {
    await this.ensureInitialized();

    const existing = await this.getConfigById(tenantId, id);
    if (!existing) {
      return null;
    }

    // Build update fields and values as key-value pairs for clarity
    const updateData: Record<string, any> = {};

    if (updates.key !== undefined) {
      updateData.key = updates.key;
    }

    if (updates.name !== undefined) {
      updateData.name = updates.name || null;
    }

    if (updates.description !== undefined) {
      updateData.description = updates.description || null;
    }

    if (updates.config !== undefined) {
      const configWithEncryptedSecrets = this.encryptSecretsInConfig(updates.config);
      updateData.config = JSON.stringify(configWithEncryptedSecrets);
    }

    if (Object.keys(updateData).length === 0) {
      return existing;
    }

    // Always update the updated_at timestamp
    updateData.updated_at = new Date().toISOString();

    // Build SQL with tenantId and id at fixed positions at the end
    const fields = Object.keys(updateData);
    const values = Object.values(updateData);
    
    // Add WHERE clause values at the end
    values.push(tenantId);
    values.push(id);

    // Build SET clause with numbered parameters
    const setClauses = fields.map((field, index) => {
      if (field === 'updated_at') {
        return `${field} = $${index + 1}::timestamp`;
      }
      return `${field} = $${index + 1}`;
    });

    // WHERE clause parameters are at the end
    const whereTenantIndex = fields.length + 1;
    const whereIdIndex = fields.length + 2;

    const sql = `
      UPDATE lattice_metrics_configs
      SET ${setClauses.join(', ')}
      WHERE tenant_id = $${whereTenantIndex} AND id = $${whereIdIndex}
    `;

    await this.pool.query(sql, values);

    return await this.getConfigById(tenantId, id);
  }

  /**
   * Delete a metrics server configuration by ID
   */
  async deleteConfig(tenantId: string, id: string): Promise<boolean> {
    await this.ensureInitialized();

    const result = await this.pool.query(
      `
      DELETE FROM lattice_metrics_configs
      WHERE tenant_id = $1 AND id = $2
    `,
      [tenantId, id]
    );

    return result.rowCount !== null && result.rowCount > 0;
  }

  /**
   * Check if configuration exists
   */
  async hasConfig(tenantId: string, id: string): Promise<boolean> {
    await this.ensureInitialized();

    const result = await this.pool.query(
      `
      SELECT 1 FROM lattice_metrics_configs
      WHERE tenant_id = $1 AND id = $2
      LIMIT 1
    `,
      [tenantId, id]
    );

    return result.rows.length > 0;
  }

  /**
   * Dispose resources and close the connection pool
   */
  async dispose(): Promise<void> {
    await this.pool.end();
  }

  /**
   * Ensure store is initialized
   */
  private async ensureInitialized(): Promise<void> {
    if (!this.initialized) {
      await this.initialize();
    }
  }

  /**
   * Map database row to MetricsServerConfigEntry object
   * Automatically decrypts apiKey and password if present
   */
  private mapRowToEntry(row: {
    id: string;
    tenant_id: string;
    key: string;
    name: string | null;
    description: string | null;
    config: any;
    created_at: Date;
    updated_at: Date;
  }): MetricsServerConfigEntry {
    const config: MetricsServerConfig = typeof row.config === 'string'
      ? JSON.parse(row.config)
      : row.config;

    // Decrypt apiKey if present
    if (config.apiKey) {
      try {
        config.apiKey = decrypt(config.apiKey);
      } catch (error) {
        console.error('Failed to decrypt apiKey:', error);
        throw new Error('Failed to decrypt metrics server configuration');
      }
    }

    // Decrypt password if present
    if (config.password) {
      try {
        config.password = decrypt(config.password);
      } catch (error) {
        console.error('Failed to decrypt password:', error);
        throw new Error('Failed to decrypt metrics server configuration');
      }
    }

    return {
      id: row.id,
      tenantId: row.tenant_id,
      key: row.key,
      config,
      name: row.name || undefined,
      description: row.description || undefined,
      createdAt: row.created_at,
      updatedAt: row.updated_at,
    };
  }

  /**
   * Encrypt apiKey and password in config before storing
   */
  private encryptSecretsInConfig(config: MetricsServerConfig): MetricsServerConfig {
    const configCopy = { ...config };
    
    if (configCopy.apiKey) {
      configCopy.apiKey = encrypt(configCopy.apiKey);
    }
    
    if (configCopy.password) {
      configCopy.password = encrypt(configCopy.password);
    }
    
    return configCopy;
  }
}