import { Injectable, BadRequestException, NotFoundException, ConflictException } from '@nestjs/common';
import { PrismaService } from '../../common/prisma.service';
import { 
  CreateFareConfigurationDto, 
  UpdateFareConfigurationDto,
  FareTestScenarioDto,
  FareCalculationResultDto,
  MigrateLegacyDto,
  CreateNewFormulaDto,
  ToggleFeatureDto,
  NationalityType,
  CoachType,
  BedPosition,
  ComponentType,
  CalculationMethod,
  AppliesTo,
  PricingType
} from './configurable-fare.dto';

@Injectable()
export class ConfigurableFareService {
  constructor(private prisma: PrismaService) {}

  // Helper method to map nationality to type
  private mapNationalityToType(nationality: string): NationalityType {
    const upperNationality = nationality.toUpperCase();
    if (upperNationality === 'ETHIOPIAN' || upperNationality === 'DJIBOUTIAN') {
      return NationalityType.LOCAL;
    }
    return NationalityType.INTERNATIONAL;
  }

  // Generate unique IDs
  private generateId(prefix: string): string {
    return `${prefix}-${Date.now()}-${Math.random().toString(36).substr(2, 9)}`;
  }

  // Configuration Management
  async getAllConfigurations() {
    return this.prisma.$queryRaw`
      SELECT 
        fc.*,
        COUNT(DISTINCT frr.id) as rate_rules_count,
        COUNT(DISTINCT fcmp.id) as components_count,
        COUNT(DISTINCT apr.id) as age_rules_count
      FROM fare_configurations fc
      LEFT JOIN fare_rate_rules frr ON fc.id = frr.fare_config_id AND frr.is_active = true
      LEFT JOIN fare_components fcmp ON fc.id = fcmp.fare_config_id AND fcmp.is_active = true
      LEFT JOIN age_pricing_rules apr ON fc.id = apr.fare_config_id AND apr.is_active = true
      GROUP BY fc.id, fc.name, fc.description, fc.effective_date, fc.expiry_date, 
               fc.is_active, fc.is_default, fc.created_by, fc.approved_by, 
               fc.approved_at, fc.created_at, fc.updated_at
      ORDER BY fc.created_at DESC
    `;
  }

  async getConfigurationById(id: string) {
    const config = await this.prisma.$queryRaw`
      SELECT * FROM fare_configurations WHERE id = ${id}
    `;

    if (!Array.isArray(config) || config.length === 0) {
      throw new NotFoundException(`Fare configuration ${id} not found`);
    }

    const rateRules = await this.prisma.$queryRaw`
      SELECT * FROM fare_rate_rules WHERE fare_config_id = ${id} ORDER BY nationality_type, coach_type, bed_position
    `;

    const components = await this.prisma.$queryRaw`
      SELECT * FROM fare_components WHERE fare_config_id = ${id} ORDER BY apply_order
    `;

    const ageRules = await this.prisma.$queryRaw`
      SELECT * FROM age_pricing_rules WHERE fare_config_id = ${id} ORDER BY min_age
    `;

    return {
      ...config[0],
      rateRules,
      components,
      ageRules
    };
  }

  async createConfiguration(dto: CreateFareConfigurationDto, createdBy?: string) {
    return this.prisma.$transaction(async (tx) => {
      const configId = this.generateId('fc');

      // Validate no overlapping active configurations
      if (dto.isDefault) {
        await tx.$executeRaw`
          UPDATE fare_configurations SET is_default = false WHERE is_default = true
        `;
      }

      // Create main configuration
      await tx.$executeRaw`
        INSERT INTO fare_configurations (
          id, name, description, effective_date, expiry_date, 
          is_active, is_default, created_by, created_at, updated_at
        ) VALUES (
          ${configId}, ${dto.name}, ${dto.description}, ${dto.effectiveDate},
          ${dto.expiryDate}, false, ${dto.isDefault || false}, ${createdBy},
          NOW(), NOW()
        )
      `;

      // Create rate rules
      for (const rule of dto.rateRules) {
        const ruleId = this.generateId('frr');
        await tx.$executeRaw`
          INSERT INTO fare_rate_rules (
            id, fare_config_id, nationality_type, coach_type, bed_position,
            rate_per_km_minor, is_active, created_at, updated_at
          ) VALUES (
            ${ruleId}, ${configId}, ${rule.nationalityType}, ${rule.coachType},
            ${rule.bedPosition || null}, ${rule.ratePerKmMinor}, ${rule.isActive !== false},
            NOW(), NOW()
          )
        `;
      }

      // Create components
      for (const component of dto.components) {
        const componentId = this.generateId('fcmp');
        await tx.$executeRaw`
          INSERT INTO fare_components (
            id, fare_config_id, component_type, component_name, calculation_method,
            value_minor, percentage_value, applies_to, apply_order, is_active,
            created_at, updated_at
          ) VALUES (
            ${componentId}, ${configId}, ${component.componentType}, ${component.componentName},
            ${component.calculationMethod}, ${component.valueMinor || null}, 
            ${component.percentageValue || null}, ${component.appliesTo}, 
            ${component.applyOrder}, ${component.isActive !== false}, NOW(), NOW()
          )
        `;
      }

      // Create age rules
      for (const ageRule of dto.ageRules) {
        const ageRuleId = this.generateId('apr');
        await tx.$executeRaw`
          INSERT INTO age_pricing_rules (
            id, fare_config_id, rule_name, min_age, max_age, pricing_type,
            discount_percentage, max_free_passengers, applies_to_components,
            is_active, created_at, updated_at
          ) VALUES (
            ${ageRuleId}, ${configId}, ${ageRule.ruleName}, ${ageRule.minAge},
            ${ageRule.maxAge || null}, ${ageRule.pricingType}, 
            ${ageRule.discountPercentage || null}, ${ageRule.maxFreePassengers || null}, 
            ${ageRule.appliesToComponents !== false}, ${ageRule.isActive !== false}, 
            NOW(), NOW()
          )
        `;
      }

      // Log audit entry
      await this.createAuditEntry(tx, configId, 'CREATED', createdBy, { action: 'Configuration created' });

      return this.getConfigurationById(configId);
    });
  }

  async updateConfiguration(id: string, dto: UpdateFareConfigurationDto, updatedBy?: string) {
    await this.getConfigurationById(id); // Validate exists
    
    return this.prisma.$transaction(async (tx) => {
      // Update main configuration
      if (dto.name || dto.description !== undefined || dto.effectiveDate || dto.expiryDate !== undefined) {
        await tx.$executeRaw`
          UPDATE fare_configurations 
          SET 
            name = COALESCE(${dto.name}, name),
            description = COALESCE(${dto.description}, description),
            effective_date = COALESCE(${dto.effectiveDate}, effective_date),
            expiry_date = COALESCE(${dto.expiryDate}, expiry_date),
            updated_at = NOW()
          WHERE id = ${id}
        `;
      }

      // Update rate rules if provided
      if (dto.rateRules) {
        await tx.$executeRaw`DELETE FROM fare_rate_rules WHERE fare_config_id = ${id}`;
        
        for (const rule of dto.rateRules) {
          const ruleId = this.generateId('frr');
          await tx.$executeRaw`
            INSERT INTO fare_rate_rules (
              id, fare_config_id, nationality_type, coach_type, bed_position,
              rate_per_km_minor, is_active, created_at, updated_at
            ) VALUES (
              ${ruleId}, ${id}, ${rule.nationalityType}, ${rule.coachType},
              ${rule.bedPosition || null}, ${rule.ratePerKmMinor}, ${rule.isActive !== false},
              NOW(), NOW()
            )
          `;
        }
      }

      // Update components if provided
      if (dto.components) {
        await tx.$executeRaw`DELETE FROM fare_components WHERE fare_config_id = ${id}`;
        
        for (const component of dto.components) {
          const componentId = this.generateId('fcmp');
          await tx.$executeRaw`
            INSERT INTO fare_components (
              id, fare_config_id, component_type, component_name, calculation_method,
              value_minor, percentage_value, applies_to, apply_order, is_active,
              created_at, updated_at
            ) VALUES (
              ${componentId}, ${id}, ${component.componentType}, ${component.componentName},
              ${component.calculationMethod}, ${component.valueMinor || null}, 
              ${component.percentageValue || null}, ${component.appliesTo}, 
              ${component.applyOrder}, ${component.isActive !== false}, NOW(), NOW()
            )
          `;
        }
      }

      // Update age rules if provided
      if (dto.ageRules) {
        await tx.$executeRaw`DELETE FROM age_pricing_rules WHERE fare_config_id = ${id}`;
        
        for (const ageRule of dto.ageRules) {
          const ageRuleId = this.generateId('apr');
          await tx.$executeRaw`
            INSERT INTO age_pricing_rules (
              id, fare_config_id, rule_name, min_age, max_age, pricing_type,
              discount_percentage, max_free_passengers, applies_to_components,
              is_active, created_at, updated_at
            ) VALUES (
              ${ageRuleId}, ${id}, ${ageRule.ruleName}, ${ageRule.minAge},
              ${ageRule.maxAge || null}, ${ageRule.pricingType}, 
              ${ageRule.discountPercentage || null}, ${ageRule.maxFreePassengers || null}, 
              ${ageRule.appliesToComponents !== false}, ${ageRule.isActive !== false}, 
              NOW(), NOW()
            )
          `;
        }
      }

      await this.createAuditEntry(tx, id, 'UPDATED', updatedBy, { changes: dto });

      return this.getConfigurationById(id);
    });
  }

  async activateConfiguration(id: string, activatedBy?: string) {
    return this.prisma.$transaction(async (tx) => {
      // Deactivate all other configurations
      await tx.$executeRaw`UPDATE fare_configurations SET is_active = false`;
      
      // Activate this one
      await tx.$executeRaw`
        UPDATE fare_configurations 
        SET is_active = true, approved_by = ${activatedBy}, approved_at = NOW()
        WHERE id = ${id}
      `;

      await this.createAuditEntry(tx, id, 'ACTIVATED', activatedBy, {});

      return { success: true, message: `Configuration ${id} activated successfully` };
    });
  }

  async deleteConfiguration(id: string, deletedBy?: string) {
    const config = await this.getConfigurationById(id);
    
    if ((config as any).is_active) {
      throw new ConflictException('Cannot delete active configuration. Deactivate first.');
    }

    await this.prisma.$executeRaw`DELETE FROM fare_configurations WHERE id = ${id}`;
    
    return { success: true, message: `Configuration ${id} deleted successfully` };
  }

  // Fare Calculation
  async testConfiguration(id: string, scenario: FareTestScenarioDto): Promise<FareCalculationResultDto> {
    const config = await this.getConfigurationById(id);
    
    // Find matching rate rule
    const nationalityType = this.mapNationalityToType(scenario.nationality);
    
    const rateRule = (config.rateRules as any[]).find((rule: any) => 
      rule.nationality_type === nationalityType &&
      rule.coach_type === scenario.coachType &&
      (scenario.bedPosition ? rule.bed_position === scenario.bedPosition : !rule.bed_position)
    );

    if (!rateRule) {
      throw new BadRequestException(`No rate rule found for ${nationalityType}/${scenario.coachType}${scenario.bedPosition ? `/${scenario.bedPosition}` : ''}`);
    }

    // Calculate base fare
    const baseFareMinor = scenario.distanceKm * rateRule.rate_per_km_minor;
    const breakdown = [
      {
        step: '1',
        description: `Base fare: ${scenario.distanceKm}km × ${rateRule.rate_per_km_minor} minor units/km`,
        amount: baseFareMinor,
        runningTotal: baseFareMinor
      }
    ];

    let runningTotal = baseFareMinor;

    // Apply age-based pricing
    const { adultCount = 1, childCount = 0 } = scenario;
    let totalPassengerFare = 0;

    // Process adults
    totalPassengerFare += adultCount * baseFareMinor;
    breakdown.push({
      step: '2a',
      description: `Adult passengers: ${adultCount} × ${baseFareMinor}`,
      amount: adultCount * baseFareMinor,
      runningTotal: adultCount * baseFareMinor
    });

    // Process children with age rules
    if (childCount > 0) {
      const childRule = (config.ageRules as any[]).find((rule: any) => 
        rule.pricing_type === PricingType.FREE && rule.max_free_passengers > 0
      );
      
      if (childRule) {
        const freeChildren = Math.min(childCount, adultCount);
        const paidChildren = Math.max(0, childCount - freeChildren);
        
        if (freeChildren > 0) {
          breakdown.push({
            step: '2b',
            description: `Free children: ${freeChildren} × 0 (first ${childRule.max_free_passengers} free)`,
            amount: 0,
            runningTotal: totalPassengerFare
          });
        }
        
        if (paidChildren > 0) {
          const paidChildrenFare = paidChildren * baseFareMinor;
          totalPassengerFare += paidChildrenFare;
          breakdown.push({
            step: '2c',
            description: `Paid children: ${paidChildren} × ${baseFareMinor}`,
            amount: paidChildrenFare,
            runningTotal: totalPassengerFare
          });
        }
      } else {
        // All children pay
        const childrenFare = childCount * baseFareMinor;
        totalPassengerFare += childrenFare;
        breakdown.push({
          step: '2b',
          description: `Child passengers: ${childCount} × ${baseFareMinor}`,
          amount: childrenFare,
          runningTotal: totalPassengerFare
        });
      }
    }

    runningTotal = totalPassengerFare;

    // Apply components in order
    let componentsTotal = 0;
    const components = (config.components as any[])
      .filter((c: any) => c.is_active)
      .sort((a: any, b: any) => a.apply_order - b.apply_order);

    for (const component of components) {
      let componentAmount = 0;
      let baseAmount = runningTotal;

      if (component.applies_to === 'BASE_FARE') {
        baseAmount = baseFareMinor;
      } else if (component.applies_to === 'SUBTOTAL') {
        baseAmount = runningTotal;
      }

      switch (component.calculation_method) {
        case 'PERCENTAGE':
          componentAmount = Math.round(baseAmount * (component.percentage_value || 0));
          break;
        case 'MULTIPLIER':
          componentAmount = Math.round(baseAmount * (component.percentage_value || 0));
          break;
        case 'FIXED_AMOUNT':
          componentAmount = component.value_minor || 0;
          break;
      }

      componentsTotal += componentAmount;
      runningTotal += componentAmount;

      breakdown.push({
        step: `3${String.fromCharCode(97 + component.apply_order - 1)}`,
        description: `${component.component_name}: ${component.calculation_method} on ${component.applies_to}`,
        amount: componentAmount,
        runningTotal
      });
    }

    return {
      baseFareMinor,
      componentsTotal,
      totalBeforeDiscounts: runningTotal,
      discountsTotal: 0, // TODO: Implement promo code discounts
      finalTotalMinor: runningTotal,
      breakdown,
      currency: 'ETB',
      calculationTimestamp: new Date()
    };
  }

  // Migration Methods
  async migrateLegacySystem(dto: MigrateLegacyDto) {
    const results = {
      scheduleFareRules: 0,
      segmentFareRules: 0,
      configurationsCreated: 0,
      dryRun: dto.dryRun || false
    };

    if (dto.dryRun) {
      // Count what would be migrated
      const scheduleFares = await this.prisma.$queryRaw`
        SELECT COUNT(*) as count FROM "FareRule" WHERE migrated_to_config_id IS NULL
      `;
      
      const segmentFares = await this.prisma.$queryRaw`
        SELECT COUNT(*) as count FROM "SegmentFareRule" WHERE migrated_to_config_id IS NULL
      `;
      
      results.scheduleFareRules = Number((scheduleFares as any[])[0]?.count || 0);
      results.segmentFareRules = Number((segmentFares as any[])[0]?.count || 0);
      
      return results;
    }

    // Create a migration configuration based on existing rules
    const migrationConfig: CreateFareConfigurationDto = {
      name: 'Legacy Migration Configuration',
      description: 'Automatically migrated from existing fare rules',
      effectiveDate: new Date().toISOString(),
      rateRules: [
        // Default rates based on current system
        { nationalityType: NationalityType.LOCAL, coachType: CoachType.REGULAR_SEAT, ratePerKmMinor: 3000 },
        { nationalityType: NationalityType.LOCAL, coachType: CoachType.ECONOMY_BED, bedPosition: BedPosition.UPPER, ratePerKmMinor: 4000 },
        { nationalityType: NationalityType.LOCAL, coachType: CoachType.ECONOMY_BED, bedPosition: BedPosition.MIDDLE, ratePerKmMinor: 5500 },
        { nationalityType: NationalityType.LOCAL, coachType: CoachType.ECONOMY_BED, bedPosition: BedPosition.LOWER, ratePerKmMinor: 6000 },
        { nationalityType: NationalityType.INTERNATIONAL, coachType: CoachType.REGULAR_SEAT, ratePerKmMinor: 6000 },
        { nationalityType: NationalityType.INTERNATIONAL, coachType: CoachType.ECONOMY_BED, bedPosition: BedPosition.UPPER, ratePerKmMinor: 8000 },
        { nationalityType: NationalityType.INTERNATIONAL, coachType: CoachType.ECONOMY_BED, bedPosition: BedPosition.MIDDLE, ratePerKmMinor: 11000 },
        { nationalityType: NationalityType.INTERNATIONAL, coachType: CoachType.ECONOMY_BED, bedPosition: BedPosition.LOWER, ratePerKmMinor: 12000 },
      ],
      components: [
        {
          componentType: ComponentType.INSURANCE,
          componentName: 'Travel Insurance',
          calculationMethod: CalculationMethod.PERCENTAGE,
          percentageValue: 0.02,
          appliesTo: AppliesTo.BASE_FARE,
          applyOrder: 1
        },
        {
          componentType: ComponentType.TAX,
          componentName: 'Government Tax',
          calculationMethod: CalculationMethod.PERCENTAGE,
          percentageValue: 0.05,
          appliesTo: AppliesTo.SUBTOTAL,
          applyOrder: 2
        }
      ],
      ageRules: [
        {
          ruleName: 'Adult Passengers',
          minAge: 5,
          pricingType: PricingType.FULL_FARE
        },
        {
          ruleName: 'Child Passengers (First Free)',
          minAge: 0,
          maxAge: 4,
          pricingType: PricingType.FREE,
          maxFreePassengers: 1
        }
      ]
    };

    const newConfig = await this.createConfiguration(migrationConfig, 'system-migration');
    results.configurationsCreated = 1;

    return results;
  }

  async createNewFormulaConfiguration(dto: CreateNewFormulaDto) {
    const config = await this.createConfiguration({
      name: dto.name,
      description: dto.description || 'System-generated default configuration',
      effectiveDate: new Date().toISOString(),
      rateRules: [
        // Default rates for all combinations
        { nationalityType: NationalityType.LOCAL, coachType: CoachType.REGULAR_SEAT, ratePerKmMinor: 3000 },
        { nationalityType: NationalityType.LOCAL, coachType: CoachType.ECONOMY_BED, bedPosition: BedPosition.UPPER, ratePerKmMinor: 4000 },
        { nationalityType: NationalityType.LOCAL, coachType: CoachType.ECONOMY_BED, bedPosition: BedPosition.MIDDLE, ratePerKmMinor: 5500 },
        { nationalityType: NationalityType.LOCAL, coachType: CoachType.ECONOMY_BED, bedPosition: BedPosition.LOWER, ratePerKmMinor: 6000 },
        { nationalityType: NationalityType.LOCAL, coachType: CoachType.VIP_BED, bedPosition: BedPosition.LOWER, ratePerKmMinor: 8000 },
        // International rates (2x local)
        { nationalityType: NationalityType.INTERNATIONAL, coachType: CoachType.REGULAR_SEAT, ratePerKmMinor: 6000 },
        { nationalityType: NationalityType.INTERNATIONAL, coachType: CoachType.ECONOMY_BED, bedPosition: BedPosition.UPPER, ratePerKmMinor: 8000 },
        { nationalityType: NationalityType.INTERNATIONAL, coachType: CoachType.ECONOMY_BED, bedPosition: BedPosition.MIDDLE, ratePerKmMinor: 11000 },
        { nationalityType: NationalityType.INTERNATIONAL, coachType: CoachType.ECONOMY_BED, bedPosition: BedPosition.LOWER, ratePerKmMinor: 12000 },
        { nationalityType: NationalityType.INTERNATIONAL, coachType: CoachType.VIP_BED, bedPosition: BedPosition.LOWER, ratePerKmMinor: 16000 },
      ],
      components: [
        {
          componentType: ComponentType.INSURANCE,
          componentName: 'Travel Insurance',
          calculationMethod: CalculationMethod.PERCENTAGE,
          percentageValue: 0.02,
          appliesTo: AppliesTo.BASE_FARE,
          applyOrder: 1
        },
        {
          componentType: ComponentType.SERVICE_CHARGE,
          componentName: 'Service Charge',
          calculationMethod: CalculationMethod.PERCENTAGE,
          percentageValue: 0.03,
          appliesTo: AppliesTo.SUBTOTAL,
          applyOrder: 2
        },
        {
          componentType: ComponentType.TAX,
          componentName: 'Government Tax',
          calculationMethod: CalculationMethod.PERCENTAGE,
          percentageValue: 0.05,
          appliesTo: AppliesTo.TOTAL,
          applyOrder: 3
        }
      ],
      ageRules: [
        {
          ruleName: 'Adult Passengers',
          minAge: 5,
          pricingType: PricingType.FULL_FARE
        },
        {
          ruleName: 'Child Passengers (First Free)',
          minAge: 0,
          maxAge: 4,
          pricingType: PricingType.FREE,
          maxFreePassengers: 1
        }
      ],
      isDefault: true
    }, 'system');

    if (dto.activateImmediately) {
      await this.activateConfiguration((config as any).id, 'system');
    }

    return config;
  }

  // Feature Management
  async toggleFeature(dto: ToggleFeatureDto) {
    return this.prisma.$transaction(async (tx) => {
      await tx.$executeRaw`
        INSERT INTO system_features (id, feature_name, is_enabled, config, created_at, updated_at)
        VALUES (${this.generateId('sf')}, ${dto.featureName}, ${dto.enabled}, 
                ${JSON.stringify(dto.config || {})}, NOW(), NOW())
        ON CONFLICT (feature_name) DO UPDATE SET
          is_enabled = ${dto.enabled},
          config = ${JSON.stringify(dto.config || {})},
          updated_at = NOW()
      `;

      return { success: true, message: `Feature ${dto.featureName} ${dto.enabled ? 'enabled' : 'disabled'}` };
    });
  }

  async getFeatureStatus(featureName: string) {
    const result = await this.prisma.$queryRaw`
      SELECT * FROM system_features WHERE feature_name = ${featureName}
    `;

    if (!Array.isArray(result) || result.length === 0) {
      return { enabled: false, config: {} };
    }

    const feature = result[0] as any;
    return {
      enabled: feature.is_enabled,
      config: feature.config || {}
    };
  }

  async getAuditTrail(configId: string) {
    return this.prisma.$queryRaw`
      SELECT * FROM fare_configuration_audit 
      WHERE fare_config_id = ${configId} 
      ORDER BY timestamp DESC
    `;
  }

  // Private helper methods
  private async createAuditEntry(tx: any, configId: string, action: string, changedBy?: string, changes?: any) {
    const auditId = this.generateId('fca');
    await tx.$executeRaw`
      INSERT INTO fare_configuration_audit (
        id, fare_config_id, action, changed_by, changes, timestamp
      ) VALUES (
        ${auditId}, ${configId}, ${action}, ${changedBy || 'system'}, 
        ${JSON.stringify(changes || {})}, NOW()
      )
    `;
  }
}