import { Repository, LessThanOrEqual } from 'typeorm';
import { DataSource } from 'typeorm';
import {
  ReportDefinition,
  ReportExecution,
  ExecutionStatus,
  ExportFormat,
} from '../entities';
import {
  ExecuteReportDto,
  ReportExecutionFiltersDto,
} from '../dto';
import { NotFoundError, ValidationError } from '../../../shared/errors/index.js';
import { logger } from '../../../shared/utils/logger.js';

/**
 * ReportExecutionService
 *
 * Handles report execution, result storage, and execution history.
 */
export class ReportExecutionService {
  constructor(
    private readonly executionRepository: Repository<ReportExecution>,
    private readonly definitionRepository: Repository<ReportDefinition>,
    private readonly dataSource: DataSource
  ) {}

  /**
   * Execute a report
   */
  async executeReport(
    dto: ExecuteReportDto,
    tenantId: string,
    executedBy: string
  ): Promise<ReportExecution> {
    // Get definition
    const definition = await this.definitionRepository.findOne({
      where: { id: dto.reportDefinitionId, tenantId },
    });

    if (!definition) {
      throw new NotFoundError('Report definition not found');
    }

    // Validate parameters
    this.validateParameters(dto.parameters, definition.parametersSchema);

    // Create execution record
    const execution = this.executionRepository.create({
      tenantId,
      reportDefinitionId: dto.reportDefinitionId,
      executedBy,
      status: ExecutionStatus.PENDING,
      parameters: dto.parameters,
      exportFormat: dto.exportFormat || null,
    });

    const saved = await this.executionRepository.save(execution);

    // Execute asynchronously
    this.runExecution(saved.id, definition, dto.parameters, tenantId)
      .catch(err => logger.error('Report execution failed', { executionId: saved.id, error: err }));

    return saved;
  }

  /**
   * Run the actual report execution
   */
  private async runExecution(
    executionId: string,
    definition: ReportDefinition,
    parameters: Record<string, any>,
    tenantId: string
  ): Promise<void> {
    const startTime = Date.now();

    try {
      // Mark as running
      await this.executionRepository.update(executionId, {
        status: ExecutionStatus.RUNNING,
        startedAt: new Date(),
      });

      let resultData: any[];
      let rowCount = 0;

      if (definition.queryFunction) {
        // Execute PostgreSQL function
        const funcParams = this.buildFunctionParams(definition.queryFunction, parameters, tenantId);
        resultData = await this.dataSource.query(
          `SELECT * FROM ${definition.queryFunction}(${funcParams.placeholders})`,
          funcParams.values
        );
        rowCount = resultData.length;
      } else if (definition.baseQuery) {
        // Execute parameterized query
        const safeQuery = this.buildSafeQuery(definition.baseQuery, parameters, tenantId);
        resultData = await this.dataSource.query(safeQuery.sql, safeQuery.values);
        rowCount = resultData.length;
      } else {
        throw new Error('Report definition has no query or function defined');
      }

      const executionTimeMs = Date.now() - startTime;

      // Calculate summary
      const resultSummary = this.calculateSummary(resultData, definition.totalsConfig);

      // Update with results
      await this.executionRepository.update(executionId, {
        status: ExecutionStatus.COMPLETED,
        completedAt: new Date(),
        executionTimeMs,
        rowCount,
        resultData,
        resultSummary,
      });

      logger.info('Report execution completed', { executionId, rowCount, executionTimeMs });

    } catch (error: any) {
      const executionTimeMs = Date.now() - startTime;

      await this.executionRepository.update(executionId, {
        status: ExecutionStatus.FAILED,
        completedAt: new Date(),
        executionTimeMs,
        errorMessage: error.message,
        errorDetails: { stack: error.stack },
      });

      logger.error('Report execution failed', { executionId, error: error.message });
    }
  }

  /**
   * Get execution by ID
   */
  async findById(id: string, tenantId: string): Promise<ReportExecution> {
    const execution = await this.executionRepository.findOne({
      where: { id, tenantId },
      relations: ['reportDefinition'],
    });

    if (!execution) {
      throw new NotFoundError('Report execution not found');
    }

    return execution;
  }

  /**
   * Get execution history with filtering
   */
  async findAll(
    tenantId: string,
    filters: ReportExecutionFiltersDto = {}
  ): Promise<{ data: ReportExecution[]; total: number }> {
    const {
      reportDefinitionId,
      status,
      executedBy,
      exportFormat,
      dateFrom,
      dateTo,
      page = 1,
      limit = 20,
    } = filters;

    const queryBuilder = this.executionRepository
      .createQueryBuilder('exec')
      .leftJoinAndSelect('exec.reportDefinition', 'definition')
      .where('exec.tenant_id = :tenantId', { tenantId });

    if (reportDefinitionId) {
      queryBuilder.andWhere('exec.report_definition_id = :reportDefinitionId', { reportDefinitionId });
    }

    if (status) {
      queryBuilder.andWhere('exec.status = :status', { status });
    }

    if (executedBy) {
      queryBuilder.andWhere('exec.executed_by = :executedBy', { executedBy });
    }

    if (exportFormat) {
      queryBuilder.andWhere('exec.export_format = :exportFormat', { exportFormat });
    }

    if (dateFrom) {
      queryBuilder.andWhere('exec.created_at >= :dateFrom', { dateFrom: new Date(dateFrom) });
    }

    if (dateTo) {
      queryBuilder.andWhere('exec.created_at <= :dateTo', { dateTo: new Date(dateTo) });
    }

    const total = await queryBuilder.getCount();

    const data = await queryBuilder
      .orderBy('exec.created_at', 'DESC')
      .skip((page - 1) * limit)
      .take(limit)
      .getMany();

    return { data, total };
  }

  /**
   * Get recent executions for a definition
   */
  async findRecentByDefinition(
    definitionId: string,
    tenantId: string,
    limit: number = 10
  ): Promise<ReportExecution[]> {
    return this.executionRepository.find({
      where: { reportDefinitionId: definitionId, tenantId },
      order: { createdAt: 'DESC' },
      take: limit,
    });
  }

  /**
   * Cancel a pending or running execution
   */
  async cancelExecution(id: string, tenantId: string): Promise<ReportExecution> {
    const execution = await this.findById(id, tenantId);

    if (execution.status !== ExecutionStatus.PENDING && execution.status !== ExecutionStatus.RUNNING) {
      throw new ValidationError('Can only cancel pending or running executions');
    }

    execution.status = ExecutionStatus.CANCELLED;
    execution.completedAt = new Date();

    const updated = await this.executionRepository.save(execution);
    logger.info('Report execution cancelled', { id, tenantId });

    return updated;
  }

  /**
   * Delete old executions (cleanup)
   */
  async deleteOldExecutions(tenantId: string, olderThanDays: number = 30): Promise<number> {
    const cutoffDate = new Date();
    cutoffDate.setDate(cutoffDate.getDate() - olderThanDays);

    const result = await this.executionRepository.delete({
      tenantId,
      createdAt: LessThanOrEqual(cutoffDate),
    });

    const deletedCount = result.affected || 0;
    logger.info('Old report executions deleted', { tenantId, deletedCount, olderThanDays });

    return deletedCount;
  }

  /**
   * Validate parameters against schema
   */
  private validateParameters(
    params: Record<string, any>,
    schema: Record<string, any>[]
  ): void {
    for (const paramDef of schema) {
      const value = params[paramDef.name];

      if (paramDef.required && (value === undefined || value === null)) {
        throw new ValidationError(`Required parameter: ${paramDef.name}`);
      }
    }
  }

  /**
   * Build function parameters
   */
  private buildFunctionParams(
    functionName: string,
    parameters: Record<string, any>,
    tenantId: string
  ): { placeholders: string; values: any[] } {
    const values: any[] = [tenantId];
    let idx = 2;

    // Common patterns for financial reports
    if (functionName.includes('trial_balance') || functionName.includes('balance_sheet')) {
      values.push(
        parameters.company_id || null,
        parameters.date_from,
        parameters.date_to,
        parameters.include_zero || false
      );
      return { placeholders: '$1, $2, $3, $4, $5', values };
    }

    if (functionName.includes('general_ledger')) {
      values.push(
        parameters.company_id || null,
        parameters.account_id,
        parameters.date_from,
        parameters.date_to
      );
      return { placeholders: '$1, $2, $3, $4, $5', values };
    }

    if (functionName.includes('income_statement')) {
      values.push(
        parameters.company_id || null,
        parameters.date_from,
        parameters.date_to
      );
      return { placeholders: '$1, $2, $3, $4', values };
    }

    // Default: only tenant_id
    return { placeholders: '$1', values };
  }

  /**
   * Build safe parameterized query
   */
  private buildSafeQuery(
    baseQuery: string,
    parameters: Record<string, any>,
    tenantId: string
  ): { sql: string; values: any[] } {
    let sql = baseQuery;
    const values: any[] = [tenantId];
    let idx = 2;

    // Replace {{tenant_id}} with $1
    sql = sql.replace(/\{\{tenant_id\}\}/g, '$1');

    // Replace other parameters
    for (const [key, value] of Object.entries(parameters)) {
      const placeholder = `{{${key}}}`;
      if (sql.includes(placeholder)) {
        sql = sql.replace(new RegExp(placeholder.replace(/[{}]/g, '\\$&'), 'g'), `$${idx}`);
        values.push(value);
        idx++;
      }
    }

    return { sql, values };
  }

  /**
   * Calculate summary totals from data
   */
  private calculateSummary(
    data: any[],
    totalsConfig: Record<string, any>[]
  ): Record<string, any> {
    if (!totalsConfig || totalsConfig.length === 0) {
      return {};
    }

    const summary: Record<string, number> = {};

    for (const config of totalsConfig) {
      if (config.column && config.aggregation === 'sum') {
        summary[config.column] = data.reduce(
          (sum, row) => sum + (parseFloat(row[config.column]) || 0),
          0
        );
      }
    }

    return summary;
  }
}
