Pricing Configuration

CREATE TABLE historial_precios (
  id INT AUTO_INCREMENT PRIMARY KEY,
  configuracion_id INT NOT NULL,
  campo_modificado VARCHAR(100) NOT NULL,
  valor_anterior TEXT,
  valor_nuevo TEXT,
  modificado_por INT,
  fecha_modificacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (configuracion_id) REFERENCES configuracion_precios(id),
  FOREIGN KEY (modificado_por) REFERENCES usuarios(id)
);

Subtotal = Base Fare + (Distance × Cost/km) + (Duration × Cost/min)

Discount = If distance ≥ 15km → Subtotal × 10%

Surcharge = Subtotal × (Percentage based on time period)
  - Peak hours (7-9am, 5-7pm): +15-20%
  - Night (10pm-6am): +20-25%
  - Holidays: +25-30%

Total = Subtotal - Discount + Surcharge
Total = MAX(Total, Minimum Fare)

Platform Commission = Total × (Commission %)
Driver Earnings = Total - Platform Commission

double calculateTripPrice({
  required double distanceKm,
  required int durationMinutes,
  required String vehicleType,
  required DateTime tripTime,
}) {
  final config = getPricingConfig(vehicleType);
  
  // Base calculation
  double subtotal = config.tarifaBase +
      (distanceKm * config.costoPorKm) +
      (durationMinutes * config.costoPorMinuto);
  
  // Distance discount
  double discount = 0;
  if (distanceKm >= 15) {
    discount = subtotal * (config.descuentoDistancia / 100);
  }
  
  // Time-based surcharge
  double surcharge = 0;
  final period = getCurrentPeriod(tripTime, config);
  
  if (period == 'hora_pico') {
    surcharge = subtotal * (config.recargoHoraPico / 100);
  } else if (period == 'nocturno') {
    surcharge = subtotal * (config.recargoNocturno / 100);
  } else if (isHoliday(tripTime)) {
    surcharge = subtotal * (config.recargoFestivo / 100);
  }
  
  // Final total
  double total = subtotal - discount + surcharge;
  total = max(total, config.tarifaMinima);
  
  return total;
}

CREATE VIEW vista_precios_activos AS
SELECT 
  cp.*,
  CASE
    WHEN CURRENT_TIME BETWEEN cp.hora_pico_inicio_manana AND cp.hora_pico_fin_manana THEN 'hora_pico'
    WHEN CURRENT_TIME BETWEEN cp.hora_pico_inicio_tarde AND cp.hora_pico_fin_tarde THEN 'hora_pico'
    WHEN CURRENT_TIME >= cp.hora_nocturna_inicio OR CURRENT_TIME <= cp.hora_nocturna_fin THEN 'nocturno'
    ELSE 'normal'
  END AS periodo_actual,
  CASE
    WHEN CURRENT_TIME BETWEEN cp.hora_pico_inicio_manana AND cp.hora_pico_fin_manana THEN cp.recargo_hora_pico
    WHEN CURRENT_TIME BETWEEN cp.hora_pico_inicio_tarde AND cp.hora_pico_fin_tarde THEN cp.recargo_hora_pico
    WHEN CURRENT_TIME >= cp.hora_nocturna_inicio OR CURRENT_TIME <= cp.hora_nocturna_fin THEN cp.recargo_nocturno
    ELSE 0
  END AS recargo_actual
FROM configuracion_precios cp
WHERE cp.activo = TRUE;

class TripEarnings {
  final double tripTotal;
  final double platformCommissionPercentage;
  
  double get platformEarnings {
    return tripTotal * (platformCommissionPercentage / 100);
  }
  
  double get driverEarnings {
    return tripTotal - platformEarnings;
  }
  
  double get companyCommission {
    // If driver belongs to a company, company may take additional %
    final companyRate = driver.company?.commissionRate ?? 0;
    return driverEarnings * (companyRate / 100);
  }
  
  double get driverFinalEarnings {
    return driverEarnings - companyCommission;
  }
}

if (distanceKm >= 15.0) {
  final discountPercentage = 10.0;
  discount = subtotal * (discountPercentage / 100);
}

bool isHoliday(DateTime date) {
  final holidays = [
    DateTime(date.year, 1, 1),   // New Year
    DateTime(date.year, 5, 1),   // Labor Day
    DateTime(date.year, 7, 20),  // Independence Day
    DateTime(date.year, 8, 7),   // Battle of Boyacá
    DateTime(date.year, 12, 25), // Christmas
    // Add more holidays
  ];
  
  return holidays.any((holiday) => 
    date.year == holiday.year &&
    date.month == holiday.month &&
    date.day == holiday.day
  );
}

SELECT tipo_vehiculo, periodo_actual, recargo_actual 
FROM vista_precios_activos;

-- Test pricing for 10km, 20min motorcycle trip
SELECT 
  tarifa_base + (10 * costo_por_km) + (20 * costo_por_minuto) AS subtotal,
  periodo_actual,
  recargo_actual
FROM vista_precios_activos
WHERE tipo_vehiculo = 'moto';

UPDATE configuracion_precios 
SET hora_pico_inicio_manana = '14:00:00',
    hora_pico_fin_manana = '16:00:00'
WHERE tipo_vehiculo = 'moto';