import { query, queryOne } from "../database/db";
import { logger } from "../utils/logger";

// 조건 노드 타입 정의
interface ConditionNode {
  id: string; // 고유 ID
  type: "condition" | "group-start" | "group-end";
  field?: string;
  operator?: "=" | "!=" | ">" | "<" | ">=" | "<=" | "LIKE";
  value?: any;
  dataType?: string;
  logicalOperator?: "AND" | "OR"; // 다음 조건과의 논리 연산자
  groupId?: string; // 그룹 ID (group-start와 group-end가 같은 groupId를 가짐)
  groupLevel?: number; // 중첩 레벨 (0, 1, 2, ...)
}

// 조건 제어 정보
interface ConditionControl {
  triggerType: "insert" | "update" | "delete" | "insert_update";
  conditionTree: ConditionNode | ConditionNode[] | null;
}

// 연결 카테고리 정보
interface ConnectionCategory {
  type: "simple-key" | "data-save" | "external-call" | "conditional-link";
  rollbackOnError?: boolean;
  enableLogging?: boolean;
  maxRetryCount?: number;
}

// 대상 액션
interface TargetAction {
  id: string;
  actionType: "insert" | "update" | "delete" | "upsert";
  targetTable: string;
  enabled: boolean;
  fieldMappings: FieldMapping[];
  conditions?: Array<{
    field: string;
    operator: "=" | "!=" | ">" | "<" | ">=" | "<=" | "LIKE";
    value: string;
    logicalOperator?: "AND" | "OR";
  }>;
  splitConfig?: {
    sourceField: string;
    delimiter: string;
    targetField: string;
  };
  description?: string;
}

// 필드 매핑
interface FieldMapping {
  sourceField: string;
  targetField: string;
  transformFunction?: string;
  defaultValue?: string;
}

// 실행 계획
interface ExecutionPlan {
  sourceTable: string;
  targetActions: TargetAction[];
}

// 실행 결과
interface ExecutionResult {
  success: boolean;
  executedActions: number;
  failedActions: number;
  errors: string[];
  executionTime: number;
}

/**
 * 조건부 연결 실행을 위한 이벤트 트리거 서비스
 */
export class EventTriggerService {
  /**
   * 특정 테이블에 대한 이벤트 트리거 실행
   */
  static async executeEventTriggers(
    triggerType: "insert" | "update" | "delete",
    tableName: string,
    data: Record<string, any>,
    companyCode: string
  ): Promise<ExecutionResult[]> {
    const startTime = Date.now();
    const results: ExecutionResult[] = [];

    try {
      // 🔥 수정: Raw SQL을 사용하여 JSON 배열 검색
      const diagrams = await query<any>(
        `SELECT * FROM dataflow_diagrams 
         WHERE company_code = $1
         AND (
           category::text = '"data-save"' OR 
           category::jsonb ? 'data-save' OR
           category::jsonb @> '["data-save"]'
         )`,
        [companyCode]
      );

      // 각 관계도에서 해당 테이블을 소스로 하는 데이터 저장 관계들 필터링
      const matchingDiagrams = diagrams.filter((diagram) => {
        // category 배열에서 data-save 연결이 있는지 확인
        const categories = diagram.category as any[];
        const hasDataSave = Array.isArray(categories)
          ? categories.some((cat) => cat.category === "data-save")
          : false;

        if (!hasDataSave) return false;

        // plan 배열에서 해당 테이블을 소스로 하는 항목이 있는지 확인
        const plans = diagram.plan as any[];
        const hasMatchingPlan = Array.isArray(plans)
          ? plans.some((plan) => plan.sourceTable === tableName)
          : false;

        // control 배열에서 해당 트리거 타입이 있는지 확인
        const controls = diagram.control as any[];
        const hasMatchingControl = Array.isArray(controls)
          ? controls.some((control) => control.triggerType === triggerType)
          : false;

        return hasDataSave && hasMatchingPlan && hasMatchingControl;
      });

      logger.info(
        `Found ${matchingDiagrams.length} matching data-save connections for table ${tableName} with trigger ${triggerType}`
      );

      // 각 다이어그램에 대해 조건부 연결 실행
      for (const diagram of matchingDiagrams) {
        try {
          const result = await this.executeDiagramTrigger(
            diagram,
            data,
            companyCode
          );
          results.push(result);
        } catch (error) {
          logger.error(`Error executing diagram ${diagram.diagram_id}:`, error);
          results.push({
            success: false,
            executedActions: 0,
            failedActions: 1,
            errors: [error instanceof Error ? error.message : "Unknown error"],
            executionTime: Date.now() - startTime,
          });
        }
      }

      return results;
    } catch (error) {
      logger.error("Error in executeEventTriggers:", error);
      throw error;
    }
  }

  /**
   * 단일 다이어그램의 트리거 실행
   */
  private static async executeDiagramTrigger(
    diagram: any,
    data: Record<string, any>,
    companyCode: string
  ): Promise<ExecutionResult> {
    const startTime = Date.now();
    let executedActions = 0;
    let failedActions = 0;
    const errors: string[] = [];

    try {
      const control = diagram.control as unknown as ConditionControl;
      const category = diagram.category as unknown as ConnectionCategory;
      const plan = diagram.plan as unknown as ExecutionPlan;

      logger.info(
        `Executing diagram ${diagram.diagram_id} (${diagram.diagram_name})`
      );

      // 조건 평가
      if (control.conditionTree) {
        const conditionMet = await this.evaluateCondition(
          control.conditionTree,
          data
        );
        if (!conditionMet) {
          logger.info(
            `Conditions not met for diagram ${diagram.diagram_id}, skipping execution`
          );
          return {
            success: true,
            executedActions: 0,
            failedActions: 0,
            errors: [],
            executionTime: Date.now() - startTime,
          };
        }
      }

      // 대상 액션들 실행
      for (const action of plan.targetActions) {
        if (!action.enabled) {
          continue;
        }

        try {
          await this.executeTargetAction(action, data, companyCode);
          executedActions++;

          if (category.enableLogging) {
            logger.info(
              `Successfully executed action ${action.id} on table ${action.targetTable}`
            );
          }
        } catch (error) {
          failedActions++;
          const errorMsg =
            error instanceof Error ? error.message : "Unknown error";
          errors.push(`Action ${action.id}: ${errorMsg}`);

          logger.error(`Failed to execute action ${action.id}:`, error);

          // 오류 시 롤백 처리
          if (category.rollbackOnError) {
            logger.warn(`Rolling back due to error in action ${action.id}`);
            // TODO: 롤백 로직 구현
            break;
          }
        }
      }

      return {
        success: failedActions === 0,
        executedActions,
        failedActions,
        errors,
        executionTime: Date.now() - startTime,
      };
    } catch (error) {
      logger.error(`Error executing diagram ${diagram.diagram_id}:`, error);
      return {
        success: false,
        executedActions: 0,
        failedActions: 1,
        errors: [error instanceof Error ? error.message : "Unknown error"],
        executionTime: Date.now() - startTime,
      };
    }
  }

  /**
   * 조건 평가 (플랫 구조 + 그룹핑 지원)
   */
  private static async evaluateCondition(
    condition: ConditionNode | ConditionNode[],
    data: Record<string, any>
  ): Promise<boolean> {
    // 단일 조건인 경우 (하위 호환성)
    if (!Array.isArray(condition)) {
      if (condition.type === "condition") {
        return this.evaluateSingleCondition(condition, data);
      }
      return true;
    }

    // 조건 배열인 경우 (새로운 그룹핑 시스템)
    return this.evaluateConditionList(condition, data);
  }

  /**
   * 조건 리스트 평가 (괄호 그룹핑 지원)
   */
  private static async evaluateConditionList(
    conditions: ConditionNode[],
    data: Record<string, any>
  ): Promise<boolean> {
    if (conditions.length === 0) {
      return true;
    }

    // 조건을 평가 가능한 표현식으로 변환
    const expression = await this.buildConditionExpression(conditions, data);

    // 표현식 평가
    return this.evaluateExpression(expression);
  }

  /**
   * 조건들을 평가 가능한 표현식으로 변환
   */
  private static async buildConditionExpression(
    conditions: ConditionNode[],
    data: Record<string, any>
  ): Promise<string> {
    const tokens: string[] = [];

    for (let i = 0; i < conditions.length; i++) {
      const condition = conditions[i];

      if (condition.type === "group-start") {
        // 이전 조건과의 논리 연산자 추가
        if (i > 0 && condition.logicalOperator) {
          tokens.push(condition.logicalOperator);
        }
        tokens.push("(");
      } else if (condition.type === "group-end") {
        tokens.push(")");
      } else if (condition.type === "condition") {
        // 이전 조건과의 논리 연산자 추가
        if (i > 0 && condition.logicalOperator) {
          tokens.push(condition.logicalOperator);
        }

        // 조건 평가 결과를 토큰으로 추가
        const result = await this.evaluateSingleCondition(condition, data);
        tokens.push(result.toString());
      }
    }

    return tokens.join(" ");
  }

  /**
   * 논리 표현식 평가 (괄호 우선순위 지원)
   */
  private static evaluateExpression(expression: string): boolean {
    try {
      // 안전한 논리 표현식 평가
      // true/false와 AND/OR/괄호만 포함된 표현식을 평가
      const sanitizedExpression = expression
        .replace(/\bAND\b/g, "&&")
        .replace(/\bOR\b/g, "||")
        .replace(/\btrue\b/g, "true")
        .replace(/\bfalse\b/g, "false");

      // 보안을 위해 허용된 문자만 확인
      if (!/^[true|false|\s|&|\||\(|\)]+$/.test(sanitizedExpression)) {
        logger.warn(`Invalid expression: ${expression}`);
        return false;
      }

      // Function constructor를 사용한 안전한 평가
      const result = new Function(`return ${sanitizedExpression}`)();
      return Boolean(result);
    } catch (error) {
      logger.error(`Error evaluating expression: ${expression}`, error);
      return false;
    }
  }

  /**
   * 액션별 조건들 평가 (AND/OR 연산자 지원)
   */
  private static async evaluateActionConditions(
    conditions: Array<{
      field: string;
      operator: "=" | "!=" | ">" | "<" | ">=" | "<=" | "LIKE";
      value: string;
      logicalOperator?: "AND" | "OR";
    }>,
    data: Record<string, any>
  ): Promise<boolean> {
    if (conditions.length === 0) {
      return true;
    }

    let result = await this.evaluateActionCondition(conditions[0], data);

    for (let i = 1; i < conditions.length; i++) {
      const prevCondition = conditions[i - 1];
      const currentCondition = conditions[i];
      const currentResult = await this.evaluateActionCondition(
        currentCondition,
        data
      );

      if (prevCondition.logicalOperator === "OR") {
        result = result || currentResult;
      } else {
        // 기본값은 AND
        result = result && currentResult;
      }
    }

    return result;
  }

  /**
   * 액션 단일 조건 평가
   */
  private static async evaluateActionCondition(
    condition: {
      field: string;
      operator: "=" | "!=" | ">" | "<" | ">=" | "<=" | "LIKE";
      value: string;
    },
    data: Record<string, any>
  ): Promise<boolean> {
    const fieldValue = data[condition.field];
    const conditionValue = condition.value;

    switch (condition.operator) {
      case "=":
        return fieldValue == conditionValue;
      case "!=":
        return fieldValue != conditionValue;
      case ">":
        return Number(fieldValue) > Number(conditionValue);
      case "<":
        return Number(fieldValue) < Number(conditionValue);
      case ">=":
        return Number(fieldValue) >= Number(conditionValue);
      case "<=":
        return Number(fieldValue) <= Number(conditionValue);
      case "LIKE":
        return String(fieldValue).includes(String(conditionValue));
      default:
        return false;
    }
  }

  /**
   * 단일 조건 평가
   */
  private static evaluateSingleCondition(
    condition: ConditionNode,
    data: Record<string, any>
  ): boolean {
    const { field, operator, value } = condition;

    if (!field || !operator) {
      return false;
    }

    const fieldValue = data[field];

    switch (operator) {
      case "=":
        return fieldValue == value;
      case "!=":
        return fieldValue != value;
      case ">":
        return Number(fieldValue) > Number(value);
      case "<":
        return Number(fieldValue) < Number(value);
      case ">=":
        return Number(fieldValue) >= Number(value);
      case "<=":
        return Number(fieldValue) <= Number(value);
      case "LIKE":
        return String(fieldValue).includes(String(value));
      default:
        return false;
    }
  }

  /**
   * 대상 액션 실행
   */
  private static async executeTargetAction(
    action: TargetAction,
    sourceData: Record<string, any>,
    companyCode: string
  ): Promise<void> {
    // 액션별 조건 평가
    if (action.conditions && action.conditions.length > 0) {
      const conditionMet = await this.evaluateActionConditions(
        action.conditions,
        sourceData
      );
      if (!conditionMet) {
        logger.info(
          `Action conditions not met for action ${action.id}, skipping execution`
        );
        return;
      }
    }

    // 필드 매핑을 통해 대상 데이터 생성
    const targetData: Record<string, any> = {};

    for (const mapping of action.fieldMappings) {
      let value = sourceData[mapping.sourceField];

      // 변환 함수 적용
      if (mapping.transformFunction) {
        value = this.applyTransformFunction(value, mapping.transformFunction);
      }

      // 기본값 설정
      if (value === undefined || value === null) {
        value = mapping.defaultValue;
      }

      targetData[mapping.targetField] = value;
    }

    // 회사 코드 추가
    targetData.company_code = companyCode;

    // 액션 타입별 실행
    switch (action.actionType) {
      case "insert":
        await this.executeInsertAction(action.targetTable, targetData);
        break;
      case "update":
        await this.executeUpdateAction(
          action.targetTable,
          targetData,
          undefined // 액션별 조건은 이미 평가했으므로 WHERE 조건은 undefined
        );
        break;
      case "delete":
        await this.executeDeleteAction(
          action.targetTable,
          targetData,
          undefined // 액션별 조건은 이미 평가했으므로 WHERE 조건은 undefined
        );
        break;
      case "upsert":
        await this.executeUpsertAction(action.targetTable, targetData);
        break;
      default:
        throw new Error(`Unsupported action type: ${action.actionType}`);
    }
  }

  /**
   * INSERT 액션 실행
   */
  private static async executeInsertAction(
    tableName: string,
    data: Record<string, any>
  ): Promise<void> {
    // 동적 테이블 INSERT 실행
    // PostgreSQL 파라미터 플레이스홀더로 변경 (? → $1, $2, ...)
    const values = Object.values(data);
    const placeholders = values.map((_, i) => `$${i + 1}`).join(", ");
    const sql = `INSERT INTO ${tableName} (${Object.keys(data).join(
      ", "
    )}) VALUES (${placeholders})`;

    await query(sql, values);
    logger.info(`Inserted data into ${tableName}:`, data);
  }

  /**
   * UPDATE 액션 실행
   */
  private static async executeUpdateAction(
    tableName: string,
    data: Record<string, any>,
    conditions?: ConditionNode
  ): Promise<void> {
    // 조건이 없으면 실행하지 않음 (안전장치)
    if (!conditions) {
      throw new Error(
        "UPDATE action requires conditions to prevent accidental mass updates"
      );
    }

    // 동적 테이블 UPDATE 실행
    const values = Object.values(data);
    const setClause = Object.keys(data)
      .map((key, i) => `${key} = $${i + 1}`)
      .join(", ");
    const whereClause = this.buildWhereClause(conditions);

    const sql = `UPDATE ${tableName} SET ${setClause} WHERE ${whereClause}`;

    await query(sql, values);
    logger.info(`Updated data in ${tableName}:`, data);
  }

  /**
   * DELETE 액션 실행
   */
  private static async executeDeleteAction(
    tableName: string,
    data: Record<string, any>,
    conditions?: ConditionNode
  ): Promise<void> {
    // 조건이 없으면 실행하지 않음 (안전장치)
    if (!conditions) {
      throw new Error(
        "DELETE action requires conditions to prevent accidental mass deletions"
      );
    }

    // 동적 테이블 DELETE 실행
    const whereClause = this.buildWhereClause(conditions);
    const sql = `DELETE FROM ${tableName} WHERE ${whereClause}`;

    await query(sql, []);
    logger.info(`Deleted data from ${tableName} with conditions`);
  }

  /**
   * UPSERT 액션 실행
   */
  private static async executeUpsertAction(
    tableName: string,
    data: Record<string, any>
  ): Promise<void> {
    // PostgreSQL UPSERT 구현
    const columns = Object.keys(data);
    const values = Object.values(data);
    const conflictColumns = ["id", "company_code"]; // 기본 충돌 컬럼
    const placeholders = values.map((_, i) => `$${i + 1}`).join(", ");

    const sql = `
      INSERT INTO ${tableName} (${columns.join(", ")})
      VALUES (${placeholders})
      ON CONFLICT (${conflictColumns.join(", ")})
      DO UPDATE SET ${columns.map((col) => `${col} = EXCLUDED.${col}`).join(", ")}
    `;

    await query(sql, values);
    logger.info(`Upserted data into ${tableName}:`, data);
  }

  /**
   * WHERE 절 구성
   */
  private static buildWhereClause(conditions: ConditionNode): string {
    // 간단한 WHERE 절 구성 (실제 구현에서는 더 복잡한 로직 필요)
    if (
      conditions.type === "condition" &&
      conditions.field &&
      conditions.operator
    ) {
      return `${conditions.field} ${conditions.operator} '${conditions.value}'`;
    }

    return "1=1"; // 기본값
  }

  /**
   * 변환 함수 적용
   */
  private static applyTransformFunction(
    value: any,
    transformFunction: string
  ): any {
    try {
      // 안전한 변환 함수들만 허용
      switch (transformFunction) {
        case "UPPER":
          return String(value).toUpperCase();
        case "LOWER":
          return String(value).toLowerCase();
        case "TRIM":
          return String(value).trim();
        case "NOW":
          return new Date();
        case "UUID":
          return require("crypto").randomUUID();
        default:
          logger.warn(`Unknown transform function: ${transformFunction}`);
          return value;
      }
    } catch (error) {
      logger.error(
        `Error applying transform function ${transformFunction}:`,
        error
      );
      return value;
    }
  }

  /**
   * 조건부 연결 테스트 (개발/디버깅용)
   */
  static async testConditionalConnection(
    diagramId: number,
    testData: Record<string, any>,
    companyCode: string
  ): Promise<{ conditionMet: boolean; result?: ExecutionResult }> {
    try {
      const diagram = await queryOne<any>(
        `SELECT * FROM dataflow_diagrams WHERE diagram_id = $1`,
        [diagramId]
      );

      if (!diagram) {
        throw new Error(`Diagram ${diagramId} not found`);
      }

      const control = diagram.control as unknown as ConditionControl;

      // 조건 평가만 수행
      const conditionMet = control.conditionTree
        ? await this.evaluateCondition(control.conditionTree, testData)
        : true;

      if (conditionMet) {
        // 실제 실행 (테스트 모드)
        const result = await this.executeDiagramTrigger(
          diagram,
          testData,
          companyCode
        );
        return { conditionMet: true, result };
      }

      return { conditionMet: false };
    } catch (error) {
      logger.error("Error testing conditional connection:", error);
      throw error;
    }
  }
}

export default EventTriggerService;
