import { Injectable } from '@nestjs/common';
import { WorkflowVariableMasterRepository } from '../repositories/workflow-variable-master.repository';
import { WorkflowConfigurationVariableRepository } from '../repositories/workflow-configuration-variable.repository';
import { AppLoggerService } from 'src/common/services/logger.service';
import { WorkflowVariableMaster } from 'src/common/entities';

interface JoinInfo {
  table: string;
  alias: string;
  joinType: string;
  condition: string;
  order: number;
}

interface SelectField {
  expression: string;
  alias: string;
}

@Injectable()
export class WorkflowVariableService {
  constructor(
    private readonly variableMasterRepository: WorkflowVariableMasterRepository,
    private readonly configVariableRepository: WorkflowConfigurationVariableRepository,
    private readonly logger: AppLoggerService,
  ) {}

  /**
   * Build SQL query dynamically based on workflow configuration variables
   * @param workflowConfigurationId - The workflow configuration ID
   * @param baseTable - The base table name (e.g., 'hdb_program_registration')
   * @param baseTableAlias - The base table alias (e.g., 'pr')
   * @returns Generated SQL query string
   */
  async buildWorkflowQuery(
    workflowConfigurationId: number,
    baseTable: string = 'hdb_program_registration',
    baseTableAlias: string = 'pr',
  ): Promise<string> {
    this.logger.log('WorkflowVariableService.buildWorkflowQuery', { workflowConfigurationId, baseTable });

    try {
      // Get all variables for this workflow
      let configVariables = await this.configVariableRepository.findByWorkflowIdWithDetails(workflowConfigurationId);

      // Fallback to DEFAULT variables if workflow has none configured
      if (!configVariables || configVariables.length === 0) {
        this.logger.warn('No variables configured for workflow, fetching DEFAULT variables from master', { workflowConfigurationId });
        const defaultVariables = await this.variableMasterRepository.findDefaultVariables();
        
        if (!defaultVariables || defaultVariables.length === 0) {
          this.logger.warn('No DEFAULT variables found in master catalog');
          return this.buildEmptyQuery(baseTable, baseTableAlias);
        }

        // Convert master variables to config variable format for processing
        configVariables = defaultVariables.map(variable => ({
          variableMaster: variable,
          overrideAlias: null,
          overrideDefaultValue: null,
        })) as any;
        
        this.logger.log('Using DEFAULT variables from master catalog', { count: defaultVariables.length });
      }

      // Group variables by source table for joins
      const joinsMap = new Map<string, JoinInfo>();
      const selectFields: SelectField[] = [];

      for (const configVar of configVariables) {
        const variable = configVar.variableMaster;
        
        if (!variable) {
          continue;
        }

        // Add join if not base table and not already added
        if (variable.sourceTable !== baseTable) {
          const joinKey = variable.tableAlias || variable.sourceTable;
          
          if (!joinsMap.has(joinKey) && variable.joinCondition) {
            joinsMap.set(joinKey, {
              table: variable.sourceTable,
              alias: variable.tableAlias || variable.sourceTable,
              joinType: variable.joinType || 'LEFT',
              condition: variable.joinCondition,
              order: variable.executionOrder || 999,
            });
          }
        }

        // Build select expression
        const selectAlias = configVar.overrideAlias || variable.aliasName || variable.variableKey;
        let selectExpression: string;

        // Prefer workflow-specific override, then master default, then standard column reference
        if (configVar.overrideDefaultValue) {
          // Use workflow-specific override value/expression
          selectExpression = configVar.overrideDefaultValue;
        } else if (variable.defaultValue) {
          // Use master catalog default value/expression (e.g., COALESCE)
          selectExpression = variable.defaultValue;
        } else {
          // Standard column reference
          const tableRef = variable.sourceTable === baseTable 
            ? baseTableAlias 
            : (variable.tableAlias || variable.sourceTable);
          selectExpression = `${tableRef}.${variable.sourceColumn}`;
        }

        selectFields.push({
          expression: selectExpression,
          alias: selectAlias,
        });
      }

      // Sort joins by execution order
      const sortedJoins = Array.from(joinsMap.values()).sort((a, b) => a.order - b.order);

      // Build the query
      return this.constructQuery(baseTable, baseTableAlias, selectFields, sortedJoins);
    } catch (error) {
      this.logger.error('Failed to build workflow query', error?.stack, { workflowConfigurationId });
      // Return a safe fallback query
      return this.buildEmptyQuery(baseTable, baseTableAlias);
    }
  }

  /**
   * Construct the final SQL query
   */
  private constructQuery(
    baseTable: string,
    baseTableAlias: string,
    selectFields: SelectField[],
    joins: JoinInfo[],
  ): string {
    const selectClause = selectFields.map(f => `      ${f.expression} as "${f.alias}"`).join(',\n');
    
    const joinsClause = joins
      .map(j => `    ${j.joinType} JOIN ${j.table} ${j.alias} ON ${j.condition}`)
      .join('\n');

    return `
    SELECT 
${selectClause}
    FROM ${baseTable} ${baseTableAlias}
${joinsClause}
    WHERE ${baseTableAlias}.id = $1 AND ${baseTableAlias}.deleted_at IS NULL
  `.trim();
  }

  /**
   * Build an empty/fallback query with just the base table
   */
  private buildEmptyQuery(baseTable: string, baseTableAlias: string): string {
    return `
    SELECT 
      ${baseTableAlias}.id as "registrationId"
    FROM ${baseTable} ${baseTableAlias}
    WHERE ${baseTableAlias}.id = $1 AND ${baseTableAlias}.deleted_at IS NULL
  `.trim();
  }

  /**
   * Get all variables for a workflow
   */
  async getVariablesForWorkflow(workflowConfigurationId: number) {
    return await this.configVariableRepository.findByWorkflowIdWithDetails(workflowConfigurationId);
  }

  /**
   * Get all available variables from master catalog
   */
  async getAllVariables() {
    return await this.variableMasterRepository.findAllActive();
  }

  /**
   * Get variable by key
   */
  async getVariableByKey(variableKey: string) {
    return await this.variableMasterRepository.findByKey(variableKey);
  }

  /**
   * Create a new variable in master catalog
   */
  async createVariableMaster(data: Partial<WorkflowVariableMaster>) {
    return await this.variableMasterRepository.create(data);
  }

  /**
   * Link variables to a workflow configuration
   */
  async linkVariablesToWorkflow(
    workflowConfigurationId: number,
    variableIds: number[],
  ) {
    const linkData = variableIds.map((variableMasterId, index) => ({
      workflowConfigurationId,
      variableMasterId,
      variableOrder: index + 1,
      isRequired: false,
    }));

    return await this.configVariableRepository.bulkCreate(linkData);
  }
}