import { eq, lt } from "drizzle-orm";
import db, { schema } from "@/database";
import logger from "@/logging";
import { isUniqueConstraintError } from "@/utils/db";

/**
 * Model for tracking processed incoming emails.
 *
 * Uses database with unique constraint for atomic, distributed deduplication
 * across multiple pod replicas. The unique constraint on messageId ensures
 * only the first INSERT succeeds, preventing race conditions.
 */
class ProcessedEmailModel {
  /**
   * Attempt to mark an email as processed.
   * Uses INSERT with unique constraint for atomic deduplication.
   *
   * @param messageId - The email provider's message ID
   * @returns true if successfully marked (first to process), false if already processed
   */
  static async tryMarkAsProcessed(messageId: string): Promise<boolean> {
    try {
      await db.insert(schema.processedEmailsTable).values({ messageId });
      return true;
    } catch (error) {
      // Check if this is a unique constraint violation (email already processed)
      // Different database drivers/wrappers may format the error differently:
      // - PostgreSQL native: "duplicate key value"
      // - PGlite: error code "23505" (unique_violation)
      // - Drizzle wraps errors, so check cause too
      if (isUniqueConstraintError(error)) {
        return false;
      }
      // Re-throw unexpected errors
      throw error;
    }
  }

  /**
   * Check if an email has been processed.
   * Note: For deduplication, prefer tryMarkAsProcessed() which is atomic.
   * This method is mainly for debugging/monitoring.
   *
   * @param messageId - The email provider's message ID
   * @returns true if the email has been processed
   */
  static async isProcessed(messageId: string): Promise<boolean> {
    const [record] = await db
      .select({ id: schema.processedEmailsTable.id })
      .from(schema.processedEmailsTable)
      .where(eq(schema.processedEmailsTable.messageId, messageId))
      .limit(1);

    return !!record;
  }

  /**
   * Release the processed-email claim for a messageId.
   *
   * Used to undo an optimistic tryMarkAsProcessed() when the agent run fails
   * transiently, so a provider redelivery can be reprocessed instead of being
   * deduplicated away and lost forever.
   *
   * @param messageId - The email provider's message ID
   * @returns true if a record was deleted, false if none existed
   */
  static async deleteByMessageId(messageId: string): Promise<boolean> {
    const result = await db
      .delete(schema.processedEmailsTable)
      .where(eq(schema.processedEmailsTable.messageId, messageId));

    return (result.rowCount ?? 0) > 0;
  }

  /**
   * Delete old processed email records.
   * Should be called periodically to prevent unbounded table growth.
   *
   * @param olderThan - Delete records older than this date
   * @returns Number of records deleted
   */
  static async cleanupOldRecords(olderThan: Date): Promise<number> {
    const result = await db
      .delete(schema.processedEmailsTable)
      .where(lt(schema.processedEmailsTable.processedAt, olderThan));

    const deleted = result.rowCount ?? 0;
    if (deleted > 0) {
      logger.info(
        { deleted, olderThan },
        "[ProcessedEmail] Cleaned up old records",
      );
    }

    return deleted;
  }
}

export default ProcessedEmailModel;
