'use strict';
/**
 * ============================================================
 * © 2025 Diploy — a brand of Bisht Technologies Private Limited
 * Original Author: BTPL Engineering Team
 * Website: https://diploy.in
 * Contact: cs@diploy.in
 *
 * Distributed under the Envato / CodeCanyon License Agreement.
 * Licensed to the purchaser for use as defined by the
 * Envato Market (CodeCanyon) Regular or Extended License.
 *
 * You are NOT permitted to redistribute, resell, sublicense,
 * or share this source code, in whole or in part.
 * Respect the author's rights and Envato licensing terms.
 * ============================================================
 */
import { db } from "../db";
import { elevenLabsCredentials, agents, incomingAgents, phoneNumbers, users } from "@shared/schema";
import { eq, and, desc, asc, sql, isNull, notInArray, or } from "drizzle-orm";
import type { InsertElevenLabsCredential, ElevenLabsCredential } from "@shared/schema";
import { ElevenLabsService } from "./elevenlabs";
import { NotificationService } from "./notification-service";
import { getCorrelationHeaders } from "../middleware/correlation-id";

// Track last notification sent for each threshold to prevent flooding
const lastNotificationThreshold = new Map<number, number>();

export class ElevenLabsPoolService {
  /**
   * Add a new ElevenLabs API key to the pool
   */
  static async addCredential(data: InsertElevenLabsCredential): Promise<ElevenLabsCredential> {
    // Test the API key first
    const isValid = await this.testCredential(data.apiKey);
    if (!isValid) {
      throw new Error("Invalid ElevenLabs API key");
    }

    const [credential] = await db
      .insert(elevenLabsCredentials)
      .values({
        ...data,
        healthStatus: "healthy",
        lastHealthCheck: new Date(),
      })
      .returning();

    console.log(`✅ Added ElevenLabs credential: ${credential.name}`);
    return credential;
  }



  static async updateCredential(id: string, data: any) {
  const [updated] = await db
    .update(elevenLabsCredentials)
    .set({
      ...data,
      updatedAt: new Date(), // optional but good
    })
    .where(eq(elevenLabsCredentials.id, id))
    .returning();

  return updated;
}

  /**
   * Test if an API key is valid
   * Tests both v2 voices endpoint and v1 agents endpoint
   */
  // static async testCredential(apiKey: string): Promise<boolean> {
  //   // Create AbortController for timeout (15 seconds for simple validation)
  //   const controller = new AbortController();
  //   const timeoutId = setTimeout(() => controller.abort(), 15000);
    
  //   try {
  //     // Test v2 voices endpoint
  //     const voicesResponse = await fetch("https://api.elevenlabs.io/v2/voices?page_size=10", {
  //       signal: controller.signal,
  //       headers: {
  //         "xi-api-key": apiKey,
  //         ...getCorrelationHeaders(), // Propagate correlation ID for distributed tracing
  //       },
  //     });
      
  //     if (!voicesResponse.ok) {
  //       console.error("❌ ElevenLabs v2 voices API test failed");
  //       return false;
  //     }
      
  //     // Test v1 agents endpoint
  //     const agentsResponse = await fetch("https://api.elevenlabs.io/v1/convai/agents", {
  //       signal: controller.signal,
  //       headers: {
  //         "xi-api-key": apiKey,
  //         ...getCorrelationHeaders(), // Propagate correlation ID for distributed tracing
  //       },
  //     });
      
  //     if (!agentsResponse.ok) {
  //       console.error("❌ ElevenLabs v1 agents API test failed");
  //       return false;
  //     }
      
  //     console.log("✅ ElevenLabs API key validated (v2 voices + v1 agents)");
  //     return true;
  //   } catch (error: any) {
  //     if (error.name === 'AbortError') {
  //       console.error("❌ ElevenLabs API key test timed out after 15s");
  //     } else {
  //       console.error("❌ ElevenLabs API key test failed:", error);
  //     }
  //     return false;
  //   } finally {
  //     clearTimeout(timeoutId);
  //   }
  // }


  static async testCredential(apiKey: string): Promise<boolean> {
  const controller = new AbortController();
  const timeoutId = setTimeout(() => controller.abort(), 10000);

  try {
    const res = await fetch("https://api.elevenlabs.io/v1/models", {
      method: "GET",
      signal: controller.signal,
      headers: {
        "xi-api-key": apiKey,
      },
    });

    const text = await res.text();
    console.log("🔍 ElevenLabs response:", res.status, text);

    if (!res.ok) {
      return false;
    }

    return true;
  } catch (error: any) {
    if (error.name === "AbortError") {
      console.error("❌ Timeout");
    } else {
      console.error("❌ Error:", error);
    }
    return false;
  } finally {
    clearTimeout(timeoutId);
  }
}

  /**
   * Get the least-loaded active credential for auto-assignment
   * Considers both current load and total assigned agents for optimal distribution
   */
  static async getLeastLoadedCredential(): Promise<ElevenLabsCredential | null> {
    const credentials = await db
      .select()
      .from(elevenLabsCredentials)
      .where(eq(elevenLabsCredentials.isActive, true));

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

    // Calculate utilization for each key and sort by lowest utilization
    const sorted = credentials
      .map((c) => ({
        ...c,
        utilization: c.maxConcurrency > 0 ? c.currentLoad / c.maxConcurrency : 0,
        agentDensity: c.totalAssignedAgents,
      }))
      .sort((a, b) => {
        // First priority: utilization (current load)
        if (Math.abs(a.utilization - b.utilization) > 0.1) {
          return a.utilization - b.utilization;
        }
        // Second priority: total assigned agents (for long-term distribution)
        return a.agentDensity - b.agentDensity;
      });

    return sorted[0];
  }

  /**
   * Get the least-loaded active credential with row locking for concurrent use
   * Uses FOR UPDATE SKIP LOCKED to prevent race conditions in credential selection
   * @param transaction - The transaction context to use for the query
   */
  static async getLeastLoadedCredentialWithLock(transaction: { execute: typeof db.execute }): Promise<ElevenLabsCredential | null> {
    // Use raw SQL for FOR UPDATE SKIP LOCKED which isn't natively supported by Drizzle
    const result = await transaction.execute(
      sql`SELECT * FROM ${elevenLabsCredentials} 
          WHERE ${elevenLabsCredentials.isActive} = true 
          ORDER BY 
            CASE WHEN ${elevenLabsCredentials.maxConcurrency} > 0 
              THEN ${elevenLabsCredentials.currentLoad}::float / ${elevenLabsCredentials.maxConcurrency}::float 
              ELSE 0 
            END ASC,
            ${elevenLabsCredentials.totalAssignedAgents} ASC
          LIMIT 1
          FOR UPDATE SKIP LOCKED`
    );
    
    if (!result.rows || result.rows.length === 0) {
      return null;
    }
    
    // Map the raw row to the credential type
    const row = result.rows[0] as any;
    return {
      id: row.id,
      name: row.name,
      apiKey: row.api_key,
      webhookSecret: row.webhook_secret || null,
      maxConcurrency: row.max_concurrency,
      currentLoad: row.current_load,
      totalAssignedAgents: row.total_assigned_agents,
      totalAssignedUsers: row.total_assigned_users,
      maxAgentsThreshold: row.max_agents_threshold,
      isActive: row.is_active,
      healthStatus: row.health_status,
      lastHealthCheck: row.last_health_check ? new Date(row.last_health_check) : null,
      metadata: row.metadata,
      createdAt: row.created_at ? new Date(row.created_at) : new Date(),
      updatedAt: row.updated_at ? new Date(row.updated_at) : new Date(),
    };
  }

  /**
   * Get credential by ID
   */
  static async getCredentialById(credentialId: string): Promise<ElevenLabsCredential | null> {
    const [credential] = await db
      .select()
      .from(elevenLabsCredentials)
      .where(eq(elevenLabsCredentials.id, credentialId))
      .limit(1);

    return credential || null;
  }

  /**
   * Get credential for a specific agent (with load balancing fallback)
   */
  static async getCredentialForAgent(agentId: string): Promise<ElevenLabsCredential | null> {
    // First try to get the agent's assigned credential
    const [agent] = await db
      .select()
      .from(agents)
      .where(eq(agents.id, agentId))
      .limit(1);

    if (agent?.elevenLabsCredentialId) {
      const [credential] = await db
        .select()
        .from(elevenLabsCredentials)
        .where(
          and(
            eq(elevenLabsCredentials.id, agent.elevenLabsCredentialId),
            eq(elevenLabsCredentials.isActive, true)
          )
        )
        .limit(1);

      if (credential) {
        return credential;
      }
    }

    // Fallback: get least loaded credential
    return this.getLeastLoadedCredential();
  }

  /**
   * Get credential with available capacity for making calls
   * Checks both current load and concurrency limit
   * 
   * NOTE: This is a READ-ONLY check. For starting calls, use reserveSlot()
   * which atomically checks AND reserves in one operation to prevent race conditions.
   */
  static async getAvailableCredential(): Promise<ElevenLabsCredential | null> {
    const credentials = await db
      .select()
      .from(elevenLabsCredentials)
      .where(eq(elevenLabsCredentials.isActive, true))
      .orderBy(asc(elevenLabsCredentials.currentLoad));

    // Find first credential with available capacity
    for (const credential of credentials) {
      if (credential.currentLoad < credential.maxConcurrency) {
        return credential;
      }
    }

    // No capacity available
    return null;
  }

  /**
   * Atomically reserve a slot on the least-loaded credential with available capacity.
   * This prevents race conditions where multiple concurrent requests could exceed limits.
   * 
   * Uses a single UPDATE with WHERE condition to atomically:
   * 1. Find a credential with current_load < max_concurrency
   * 2. Increment current_load by 1
   * 3. Return the updated credential
   * 
   * @returns The credential with reserved slot, or null if no capacity available
   */
  static async reserveSlot(): Promise<ElevenLabsCredential | null> {
    try {
      // Use raw SQL for atomic UPDATE with subquery selection
      // This finds the least-loaded credential with capacity and reserves a slot in one operation
      const result = await db.execute(sql`
        UPDATE eleven_labs_credentials
        SET current_load = current_load + 1,
            updated_at = NOW()
        WHERE id = (
          SELECT id FROM eleven_labs_credentials
          WHERE is_active = true
            AND current_load < max_concurrency
          ORDER BY current_load ASC
          LIMIT 1
          FOR UPDATE SKIP LOCKED
        )
        RETURNING *
      `);

      if (result.rows && result.rows.length > 0) {
        const row = result.rows[0] as any;
        console.log(`🔒 [Pool] Reserved slot on credential ${row.name} (load: ${row.current_load}/${row.max_concurrency})`);
        
        // Map snake_case DB columns to camelCase for TypeScript
        return {
          id: row.id,
          name: row.name,
          apiKey: row.api_key,
          maxConcurrency: row.max_concurrency,
          currentLoad: row.current_load,
          totalAssignedAgents: row.total_assigned_agents,
          totalAssignedUsers: row.total_assigned_users,
          maxAgentsThreshold: row.max_agents_threshold,
          isActive: row.is_active,
          healthStatus: row.health_status,
          lastHealthCheck: row.last_health_check ? new Date(row.last_health_check) : null,
          createdAt: row.created_at ? new Date(row.created_at) : new Date(),
          updatedAt: row.updated_at ? new Date(row.updated_at) : new Date(),
        } as ElevenLabsCredential;
      }

      console.log(`⚠️ [Pool] No available capacity - all credentials at max load`);
      return null;
    } catch (error) {
      console.error(`❌ [Pool] Failed to reserve slot:`, error);
      return null;
    }
  }

  /**
   * Reserve a slot on a specific credential (for agent-assigned calls)
   * Atomically checks capacity and increments load
   * 
   * @param credentialId - The specific credential to reserve on
   * @returns The credential with reserved slot, or null if no capacity
   */
  static async reserveSlotOnCredential(credentialId: string): Promise<ElevenLabsCredential | null> {
    try {
      const result = await db.execute(sql`
        UPDATE eleven_labs_credentials
        SET current_load = current_load + 1,
            updated_at = NOW()
        WHERE id = ${credentialId}
          AND is_active = true
          AND current_load < max_concurrency
        RETURNING *
      `);

      if (result.rows && result.rows.length > 0) {
        const row = result.rows[0] as any;
        console.log(`🔒 [Pool] Reserved slot on credential ${row.name} (load: ${row.current_load}/${row.max_concurrency})`);
        
        return {
          id: row.id,
          name: row.name,
          apiKey: row.api_key,
          maxConcurrency: row.max_concurrency,
          currentLoad: row.current_load,
          totalAssignedAgents: row.total_assigned_agents,
          totalAssignedUsers: row.total_assigned_users,
          maxAgentsThreshold: row.max_agents_threshold,
          isActive: row.is_active,
          healthStatus: row.health_status,
          lastHealthCheck: row.last_health_check ? new Date(row.last_health_check) : null,
          createdAt: row.created_at ? new Date(row.created_at) : new Date(),
          updatedAt: row.updated_at ? new Date(row.updated_at) : new Date(),
        } as ElevenLabsCredential;
      }

      console.log(`⚠️ [Pool] Credential ${credentialId} has no capacity or is inactive`);
      return null;
    } catch (error) {
      console.error(`❌ [Pool] Failed to reserve slot on credential ${credentialId}:`, error);
      return null;
    }
  }

  /**
   * Increment current load for a credential (when starting a call)
   * @deprecated Use reserveSlot() or reserveSlotOnCredential() for atomic reservation
   */
  static async incrementLoad(credentialId: string): Promise<void> {
    await db
      .update(elevenLabsCredentials)
      .set({
        currentLoad: sql`${elevenLabsCredentials.currentLoad} + 1`,
        updatedAt: new Date(),
      })
      .where(eq(elevenLabsCredentials.id, credentialId));
  }

  /**
   * Decrement current load for a credential (when call ends)
   * Uses GREATEST(0, ...) to prevent negative counts
   */
  static async decrementLoad(credentialId: string): Promise<void> {
    await db
      .update(elevenLabsCredentials)
      .set({
        currentLoad: sql`GREATEST(0, ${elevenLabsCredentials.currentLoad} - 1)`,
        updatedAt: new Date(),
      })
      .where(eq(elevenLabsCredentials.id, credentialId));
  }

  /**
   * Release a slot (alias for decrementLoad for clarity)
   * Should be called in finally block after reserveSlot/reserveSlotOnCredential
   */
  static async releaseSlot(credentialId: string): Promise<void> {
    await this.decrementLoad(credentialId);
    console.log(`🔓 [Pool] Released slot on credential ${credentialId}`);
  }

  /**
   * Update agent assignment count for a credential
   * Uses GREATEST(0, ...) to prevent negative counts on repeated decrements
   * @param credentialId - The credential to update
   * @param increment - True to increment, false to decrement
   * @param transaction - Optional transaction context for atomic operations
   */
  static async updateAssignmentCount(credentialId: string, increment: boolean, transaction?: typeof db): Promise<void> {
    const dbContext = transaction || db;
    if (increment) {
      await dbContext
        .update(elevenLabsCredentials)
        .set({
          totalAssignedAgents: sql`${elevenLabsCredentials.totalAssignedAgents} + 1`,
          updatedAt: new Date(),
        })
        .where(eq(elevenLabsCredentials.id, credentialId));
    } else {
      // Use GREATEST to ensure count never goes below zero
      await dbContext
        .update(elevenLabsCredentials)
        .set({
          totalAssignedAgents: sql`GREATEST(0, ${elevenLabsCredentials.totalAssignedAgents} - 1)`,
          updatedAt: new Date(),
        })
        .where(eq(elevenLabsCredentials.id, credentialId));
    }
  }

  /**
   * Get all credentials with stats (sanitized - no API keys or secrets)
   */
  // static async getAllWithStats() {
  //   const credentials = await db.select().from(elevenLabsCredentials).orderBy(desc(elevenLabsCredentials.createdAt));
    
  //   // Remove API keys and webhook secrets from response for security
  //   return credentials.map(({ apiKey, webhookSecret, ...rest }) => ({
  //     ...rest,
  //     hasWebhookSecret: !!webhookSecret,
  //   }));



  static async getAllWithStats() {
  const credentials = await db
    .select()
    .from(elevenLabsCredentials)
    .orderBy(desc(elevenLabsCredentials.createdAt));

  return credentials.map((item) => ({
    ...item,
    hasWebhookSecret: !!item.webhookSecret,
  }));
}
  // }

  /**
   * Get pool capacity statistics and check for capacity warnings
   */
  static async getPoolStats() {
    const credentials = await db
      .select()
      .from(elevenLabsCredentials);
      // .where(eq(elevenLabsCredentials.isActive, true));

    const totalCapacity = credentials.reduce((sum: number, c) => sum + c.maxConcurrency, 0);
    const totalLoad = credentials.reduce((sum: number, c) => sum + c.currentLoad, 0);
    const totalAgents = credentials.reduce((sum: number, c) => sum + c.totalAssignedAgents, 0);
    const totalUsers = credentials.reduce((sum: number, c) => sum + c.totalAssignedUsers, 0);
    const availableCapacity = totalCapacity - totalLoad;
    const utilizationPercent = totalCapacity > 0 ? (totalLoad / totalCapacity) * 100 : 0;

    // Proactively check capacity and notify admins if needed
    await this.checkCapacityAndNotify(utilizationPercent);

    return {
      totalKeys: credentials.length,
      totalCapacity,
      totalLoad,
      availableCapacity,
      utilizationPercent: Math.round(utilizationPercent * 100) / 100,
      totalAgents,
      totalUsers,
      credentials: credentials.map((c: ElevenLabsCredential) => ({
        id: c.id,
        name: c.name,
        maxConcurrency: c.maxConcurrency,
        currentLoad: c.currentLoad,
        totalAssignedAgents: c.totalAssignedAgents,
        totalAssignedUsers: c.totalAssignedUsers,
        maxAgentsThreshold: c.maxAgentsThreshold,
        agentFillPercent: c.maxAgentsThreshold > 0 
          ? Math.round((c.totalAssignedAgents / c.maxAgentsThreshold) * 10000) / 100 
          : 0,
        utilizationPercent:
          c.maxConcurrency > 0 ? Math.round((c.currentLoad / c.maxConcurrency) * 10000) / 100 : 0,
        healthStatus: c.healthStatus,
        isActive: c.isActive,
        hasWebhookSecret: !!c.webhookSecret,
      })),
    };
  }

  /**
   * Check capacity and send notifications to admins if needed (throttled to prevent flooding)
   */
  private static async checkCapacityAndNotify(utilizationPercent: number): Promise<void> {
    const now = Date.now();
    const THROTTLE_PERIOD = 4 * 60 * 60 * 1000; // 4 hours

    // Determine which threshold we're at
    let threshold = 0;
    if (utilizationPercent >= 95) {
      threshold = 95;
    } else if (utilizationPercent >= 90) {
      threshold = 90;
    } else if (utilizationPercent >= 80) {
      threshold = 80;
    }

    // If no threshold crossed, reset and return
    if (threshold === 0) {
      lastNotificationThreshold.clear();
      return;
    }

    // Check if we've already sent a notification for this threshold recently
    const lastSent = lastNotificationThreshold.get(threshold);
    if (lastSent && now - lastSent < THROTTLE_PERIOD) {
      return; // Throttle - don't send duplicate notification
    }

    // Send notification based on threshold
    if (threshold === 95) {
      await NotificationService.createNotificationForAllAdmins({
        type: "system",
        title: "CRITICAL: ElevenLabs Pool at 95%+ Capacity",
        message: `The ElevenLabs API key pool is at ${utilizationPercent.toFixed(1)}% capacity. Add more API keys immediately to prevent service disruption.`,
        link: "/admin/elevenlabs-pool",
      });
      lastNotificationThreshold.set(95, now);
    } else if (threshold === 90) {
      await NotificationService.createNotificationForAllAdmins({
        type: "system",
        title: "WARNING: ElevenLabs Pool at 90%+ Capacity",
        message: `The ElevenLabs API key pool is at ${utilizationPercent.toFixed(1)}% capacity. Consider adding more API keys soon.`,
        link: "/admin/elevenlabs-pool",
      });
      lastNotificationThreshold.set(90, now);
    } else if (threshold === 80) {
      await NotificationService.createNotificationForAllAdmins({
        type: "system",
        title: "Notice: ElevenLabs Pool at 80%+ Capacity",
        message: `The ElevenLabs API key pool is at ${utilizationPercent.toFixed(1)}% capacity. Monitor usage and plan to add more keys if needed.`,
        link: "/admin/elevenlabs-pool",
      });
      lastNotificationThreshold.set(80, now);
    }
  }

  /**
   * Deactivate a credential
   */
  static async deactivateCredential(id: string): Promise<void> {
    await db
      .update(elevenLabsCredentials)
      .set({
        isActive: false,
        updatedAt: new Date(),
      })
      .where(eq(elevenLabsCredentials.id, id));
  }

  /**
   * Delete a credential - unassigns any agents/phone numbers using it first
   */
  static async deleteCredential(id: string): Promise<{ unassignedAgents: number; unassignedPhoneNumbers: number }> {
    const [credential] = await db
      .select()
      .from(elevenLabsCredentials)
      .where(eq(elevenLabsCredentials.id, id))
      .limit(1);

    if (!credential) {
      throw new Error("Credential not found");
    }

    // Unassign all agents using this credential
    const agentResult = await db
      .update(agents)
      .set({ elevenLabsCredentialId: null })
      .where(eq(agents.elevenLabsCredentialId, id));
    
    // Unassign all incoming agents using this credential  
    const incomingAgentResult = await db
      .update(incomingAgents)
      .set({ elevenLabsCredentialId: null })
      .where(eq(incomingAgents.elevenLabsCredentialId, id));
    
    // Unassign all phone numbers using this credential
    const phoneResult = await db
      .update(phoneNumbers)
      .set({ elevenLabsCredentialId: null })
      .where(eq(phoneNumbers.elevenLabsCredentialId, id));

    // Delete the credential
    await db.delete(elevenLabsCredentials).where(eq(elevenLabsCredentials.id, id));
    
    console.log(`🗑️ Deleted ElevenLabs credential: ${credential.name} (unassigned ${credential.totalAssignedAgents} agents)`);
    
    return { 
      unassignedAgents: credential.totalAssignedAgents || 0,
      unassignedPhoneNumbers: 0 // phoneResult doesn't return count easily
    };
  }

  /**
   * Perform health check on all credentials
   */
  static async performHealthChecks(): Promise<void> {
    const credentials = await db.select().from(elevenLabsCredentials);

    for (const credential of credentials) {
      const isHealthy = await this.testCredential(credential.apiKey);
      await db
        .update(elevenLabsCredentials)
        .set({
          healthStatus: isHealthy ? "healthy" : "unhealthy",
          lastHealthCheck: new Date(),
          updatedAt: new Date(),
        })
        .where(eq(elevenLabsCredentials.id, credential.id));
    }
  }

  /**
   * Sync existing agents with null or inactive elevenLabsCredentialId to the pool
   * This handles both unassigned agents AND agents assigned to inactive credentials
   */
  static async syncExistingAgents(): Promise<{ synced: number; reassigned: number; total: number; credentialId: string | null }> {
    // Get all active credential IDs
    const activeCredentials = await db
      .select({ id: elevenLabsCredentials.id })
      .from(elevenLabsCredentials)
      .where(eq(elevenLabsCredentials.isActive, true));
    
    const activeCredentialIds = activeCredentials.map(c => c.id);
    
    // Find all ElevenLabs agents (those with an elevenLabsAgentId)
    const allElevenLabsAgents = await db
      .select()
      .from(agents)
      .where(sql`${agents.elevenLabsAgentId} IS NOT NULL`);
    
    // Filter to find agents that need reassignment:
    // 1. No credential assigned (NULL)
    // 2. Assigned to an inactive/deleted credential (not in activeCredentialIds)
    const agentsToReassign = allElevenLabsAgents.filter(agent => {
      if (!agent.elevenLabsCredentialId) {
        return true; // No credential assigned
      }
      // Check if assigned credential is in the active list
      return !activeCredentialIds.includes(agent.elevenLabsCredentialId);
    });
    
    // Count how many were unassigned vs reassigned from inactive
    const unassignedCount = agentsToReassign.filter(a => !a.elevenLabsCredentialId).length;
    const reassignedCount = agentsToReassign.filter(a => a.elevenLabsCredentialId).length;

    if (agentsToReassign.length === 0) {
      return { synced: 0, reassigned: 0, total: 0, credentialId: null };
    }

    // Get the least-loaded credential
    const credential = await this.getLeastLoadedCredential();
    
    if (!credential) {
      throw new Error("No active ElevenLabs credentials available. Please add at least one API key to the pool.");
    }

    // Reassign each agent individually and update assignment counts
    const oldCredentialIds = new Set<string>();
    for (const agent of agentsToReassign) {
      // Track old credentials that need count decremented
      if (agent.elevenLabsCredentialId) {
        oldCredentialIds.add(agent.elevenLabsCredentialId);
      }
      
      await db
        .update(agents)
        .set({ elevenLabsCredentialId: credential.id })
        .where(eq(agents.id, agent.id));
    }

    // Update assignment counts: increment for new credential, decrement for old credentials
    // Increment by total number of agents reassigned
    for (let i = 0; i < agentsToReassign.length; i++) {
      await this.updateAssignmentCount(credential.id, true);
    }
    // Decrement old credential counts
    for (const oldCredentialId of Array.from(oldCredentialIds)) {
      const countForOld = agentsToReassign.filter(a => a.elevenLabsCredentialId === oldCredentialId).length;
      for (let i = 0; i < countForOld; i++) {
        await this.updateAssignmentCount(oldCredentialId, false);
      }
    }

    console.log(`[ElevenLabs Pool] Synced ${unassignedCount} unassigned + ${reassignedCount} reassigned agents to credential ${credential.name}`);

    return {
      synced: unassignedCount,
      reassigned: reassignedCount,
      total: agentsToReassign.length,
      credentialId: credential.id,
    };
  }

  /**
   * Recalculate agent counts for all credentials based on actual agents in database.
   * This fixes any drift between stored totalAssignedAgents and actual agent counts.
   */
  static async recalculateAgentCounts(): Promise<{ 
    updated: number; 
    credentials: Array<{ id: string; name: string; oldCount: number; newCount: number }> 
  }> {
    // Get all credentials
    const credentials = await db.select().from(elevenLabsCredentials);
    
    const updates: Array<{ id: string; name: string; oldCount: number; newCount: number }> = [];
    
    for (const credential of credentials) {
      // Count actual agents using this credential
      const [result] = await db
        .select({ count: sql<number>`count(*)::int` })
        .from(agents)
        .where(eq(agents.elevenLabsCredentialId, credential.id));
      
      const actualCount = result?.count || 0;
      const oldCount = credential.totalAssignedAgents;
      
      // Only update if counts differ
      if (actualCount !== oldCount) {
        await db
          .update(elevenLabsCredentials)
          .set({
            totalAssignedAgents: actualCount,
            updatedAt: new Date(),
          })
          .where(eq(elevenLabsCredentials.id, credential.id));
        
        updates.push({
          id: credential.id,
          name: credential.name,
          oldCount,
          newCount: actualCount,
        });
        
        console.log(`🔄 [ElevenLabs Pool] Recalculated ${credential.name}: ${oldCount} → ${actualCount} agents`);
      }
    }
    
    return {
      updated: updates.length,
      credentials: updates,
    };
  }

  // ============================================================
  // USER CREDENTIAL AFFINITY SYSTEM
  // Ensures all user's resources (agents, phone numbers) stay on same ElevenLabs account
  // Uses threshold-based distribution: fill keys to threshold, then round-robin
  // ============================================================

  /**
   * Get or assign a credential for a user.
   * This is the PRIMARY method for getting credentials for user operations.
   * 
   * Logic:
   * 1. If user already has a credential assigned, return it (if still active)
   * 2. If user has existing agents with credentials, adopt that credential
   * 3. Otherwise, assign new credential using threshold-based selection:
   *    - Pick credential under threshold (default 100 agents)
   *    - If all at/above threshold, pick least-loaded for round-robin balance
   * 
   * @param userId - The user ID to get/assign credential for
   * @returns The assigned credential, or null if no active credentials available
   */
  static async getUserCredential(userId: string): Promise<ElevenLabsCredential | null> {
    // Step 1: Check if user already has a credential assigned
    const [user] = await db
      .select({ elevenLabsCredentialId: users.elevenLabsCredentialId })
      .from(users)
      .where(eq(users.id, userId))
      .limit(1);

    if (user?.elevenLabsCredentialId) {
      // User has credential - verify it's still active
      const [existingCredential] = await db
        .select()
        .from(elevenLabsCredentials)
        .where(
          and(
            eq(elevenLabsCredentials.id, user.elevenLabsCredentialId),
            eq(elevenLabsCredentials.isActive, true)
          )
        )
        .limit(1);

      if (existingCredential) {
        console.log(`🔑 [User Affinity] User ${userId} using existing credential: ${existingCredential.name}`);
        return existingCredential;
      }
      // Credential is inactive/deleted - need to reassign
      console.log(`⚠️ [User Affinity] User ${userId}'s credential is inactive, reassigning...`);
    }

    // Step 2: Check if user has existing agents with credentials (migration path)
    const [existingAgent] = await db
      .select({ elevenLabsCredentialId: agents.elevenLabsCredentialId })
      .from(agents)
      .where(
        and(
          eq(agents.userId, userId),
          sql`${agents.elevenLabsCredentialId} IS NOT NULL`
        )
      )
      .limit(1);

    if (existingAgent?.elevenLabsCredentialId) {
      const [agentCredential] = await db
        .select()
        .from(elevenLabsCredentials)
        .where(
          and(
            eq(elevenLabsCredentials.id, existingAgent.elevenLabsCredentialId),
            eq(elevenLabsCredentials.isActive, true)
          )
        )
        .limit(1);

      if (agentCredential) {
        // Adopt this credential for the user
        await this.assignUserToCredential(userId, agentCredential.id);
        console.log(`🔑 [User Affinity] User ${userId} adopted credential from existing agent: ${agentCredential.name}`);
        return agentCredential;
      }
    }

    // Step 3: Assign new credential using threshold-based selection
    const credential = await this.selectCredentialForNewUser();
    if (!credential) {
      console.error(`❌ [User Affinity] No active credentials available for user ${userId}`);
      return null;
    }

    // Assign user to this credential
    await this.assignUserToCredential(userId, credential.id);
    console.log(`🔑 [User Affinity] User ${userId} assigned to credential: ${credential.name} (${credential.totalAssignedAgents}/${credential.maxAgentsThreshold} agents)`);
    return credential;
  }

  /**
   * Select a credential for a new user using threshold-based distribution.
   * 
   * Strategy:
   * 1. Find credentials under their maxAgentsThreshold
   * 2. Among those, pick the one with most agents (fill to threshold)
   * 3. If all at/above threshold, pick least-loaded (round-robin balance)
   */
  private static async selectCredentialForNewUser(): Promise<ElevenLabsCredential | null> {
    const credentials = await db
      .select()
      .from(elevenLabsCredentials)
      .where(eq(elevenLabsCredentials.isActive, true));

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

    // Find credentials under threshold
    const underThreshold = credentials.filter(c => c.totalAssignedAgents < c.maxAgentsThreshold);

    if (underThreshold.length > 0) {
      // Pick the one with most agents (fill to threshold before moving to next)
      const sorted = underThreshold.sort((a, b) => b.totalAssignedAgents - a.totalAssignedAgents);
      return sorted[0];
    }

    // All at/above threshold - round-robin by picking least loaded
    const sorted = credentials.sort((a, b) => a.totalAssignedAgents - b.totalAssignedAgents);
    return sorted[0];
  }

  /**
   * Assign a user to a specific credential and update counters.
   * This is called by the migration engine after successful migration to keep user's credential pointer in sync.
   * 
   * @param userId - User ID to assign
   * @param credentialId - Credential ID to assign to
   */
  static async assignUserToCredential(userId: string, credentialId: string): Promise<void> {
    // Check if user was previously assigned to a different credential
    const [user] = await db
      .select({ elevenLabsCredentialId: users.elevenLabsCredentialId })
      .from(users)
      .where(eq(users.id, userId))
      .limit(1);

    const oldCredentialId = user?.elevenLabsCredentialId;

    // Update user's credential assignment
    await db
      .update(users)
      .set({ 
        elevenLabsCredentialId: credentialId,
        updatedAt: new Date(),
      })
      .where(eq(users.id, userId));

    // Decrement old credential's user count if reassigning
    if (oldCredentialId && oldCredentialId !== credentialId) {
      await db
        .update(elevenLabsCredentials)
        .set({
          totalAssignedUsers: sql`GREATEST(0, ${elevenLabsCredentials.totalAssignedUsers} - 1)`,
          updatedAt: new Date(),
        })
        .where(eq(elevenLabsCredentials.id, oldCredentialId));
    }

    // Increment new credential's user count (only if not already assigned)
    if (!oldCredentialId || oldCredentialId !== credentialId) {
      await db
        .update(elevenLabsCredentials)
        .set({
          totalAssignedUsers: sql`${elevenLabsCredentials.totalAssignedUsers} + 1`,
          updatedAt: new Date(),
        })
        .where(eq(elevenLabsCredentials.id, credentialId));
    }
  }

  /**
   * Recalculate user assignment counts for all credentials.
   * This fixes any drift between stored totalAssignedUsers and actual user counts.
   */
  static async recalculateUserCounts(): Promise<{ 
    updated: number; 
    credentials: Array<{ id: string; name: string; oldCount: number; newCount: number }> 
  }> {
    const credentials = await db.select().from(elevenLabsCredentials);
    
    const updates: Array<{ id: string; name: string; oldCount: number; newCount: number }> = [];
    
    for (const credential of credentials) {
      // Count actual users assigned to this credential
      const [result] = await db
        .select({ count: sql<number>`count(*)::int` })
        .from(users)
        .where(eq(users.elevenLabsCredentialId, credential.id));
      
      const actualCount = result?.count || 0;
      const oldCount = credential.totalAssignedUsers;
      
      if (actualCount !== oldCount) {
        await db
          .update(elevenLabsCredentials)
          .set({
            totalAssignedUsers: actualCount,
            updatedAt: new Date(),
          })
          .where(eq(elevenLabsCredentials.id, credential.id));
        
        updates.push({
          id: credential.id,
          name: credential.name,
          oldCount,
          newCount: actualCount,
        });
        
        console.log(`🔄 [ElevenLabs Pool] Recalculated users for ${credential.name}: ${oldCount} → ${actualCount}`);
      }
    }
    
    return {
      updated: updates.length,
      credentials: updates,
    };
  }

  /**
   * Get credential stats including user assignments.
   */
  static async getCredentialWithUserStats(credentialId: string) {
    const [credential] = await db
      .select()
      .from(elevenLabsCredentials)
      .where(eq(elevenLabsCredentials.id, credentialId))
      .limit(1);

    if (!credential) return null;

    // Get users assigned to this credential
    const assignedUsers = await db
      .select({ id: users.id, email: users.email, name: users.name })
      .from(users)
      .where(eq(users.elevenLabsCredentialId, credentialId));

    return {
      ...credential,
      assignedUsers,
    };
  }
}