const { query } = require('../utils/db');
const { calculateRiskScore, getRiskColor } = require('./riskCalculator');

/**
 * Get all in-transit shipments with risk scores
 */
async function getAtRiskShipments(options = {}) {
  const { warehouseId, region, minRiskScore = 0 } = options;

  let sql = `
    SELECT
      s.id,
      s.status,
      s.scheduled_departure,
      s.actual_departure,
      s.scheduled_arrival,
      s.shipping_cost,
      s.destination_lat,
      s.destination_lng,
      w.id as warehouse_id,
      w.name as warehouse_name,
      w.region,
      d.id as driver_id,
      d.name as driver_name,
      d.behavior_score,
      d.fatigue_score,
      v.vehicle_type,
      v.capacity_kg,
      sc.traffic_congestion_level,
      sc.weather_severity,
      sc.route_risk_level,
      sc.cargo_condition
    FROM shipments s
    JOIN warehouses w ON s.warehouse_id = w.id
    JOIN drivers d ON s.driver_id = d.id
    JOIN vehicles v ON s.vehicle_id = v.id
    LEFT JOIN shipment_conditions sc ON s.id = sc.shipment_id
    WHERE s.status = 'In Transit'
  `;

  const params = [];

  if (warehouseId) {
    sql += ' AND s.warehouse_id = ?';
    params.push(warehouseId);
  }

  if (region) {
    sql += ' AND w.region = ?';
    params.push(region);
  }

  sql += ' ORDER BY s.scheduled_arrival ASC';

  const shipments = await query(sql, params);

  // Calculate risk score for each shipment
  const shipmentsWithRisk = shipments.map(shipment => {
    const risk = calculateRiskScore(shipment);
    return {
      ...shipment,
      risk_score: risk.score,
      risk_level: risk.riskLevel,
      risk_color: getRiskColor(risk.riskLevel),
      risk_breakdown: risk.breakdown
    };
  });

  // Filter by minimum risk score and sort by risk (highest first)
  return shipmentsWithRisk
    .filter(s => s.risk_score >= minRiskScore)
    .sort((a, b) => b.risk_score - a.risk_score);
}

/**
 * Get summary statistics for dashboard
 */
async function getDashboardStats() {
  // Get current in-transit stats
  const inTransitSql = `
    SELECT
      COUNT(*) as total_in_transit,
      w.region,
      COUNT(*) as count
    FROM shipments s
    JOIN warehouses w ON s.warehouse_id = w.id
    WHERE s.status = 'In Transit'
    GROUP BY w.region
  `;

  // Get historical on-time performance
  const historicalSql = `
    SELECT
      COUNT(*) as total_delivered,
      SUM(CASE WHEN actual_arrival <= scheduled_arrival THEN 1 ELSE 0 END) as on_time,
      SUM(CASE WHEN actual_arrival > scheduled_arrival THEN 1 ELSE 0 END) as late
    FROM shipments
    WHERE status = 'Delivered'
      AND actual_arrival IS NOT NULL
  `;

  // Get recent delivery metrics
  const recentMetricsSql = `
    SELECT
      report_date,
      SUM(total_shipments) as total,
      SUM(on_time_count) as on_time,
      SUM(late_count) as late,
      ROUND(AVG(avg_delay_hours), 2) as avg_delay
    FROM daily_delivery_metrics
    WHERE report_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
    GROUP BY report_date
    ORDER BY report_date
  `;

  const [byRegion, historical, recentMetrics] = await Promise.all([
    query(inTransitSql),
    query(historicalSql),
    query(recentMetricsSql)
  ]);

  const hist = historical[0] || { total_delivered: 0, on_time: 0, late: 0 };

  return {
    inTransitByRegion: byRegion,
    totalInTransit: byRegion.reduce((sum, r) => sum + r.count, 0),
    historicalPerformance: {
      totalDelivered: hist.total_delivered,
      onTime: hist.on_time,
      late: hist.late,
      onTimeRate: hist.total_delivered > 0
        ? ((hist.on_time / hist.total_delivered) * 100).toFixed(1)
        : 0
    },
    recentTrend: recentMetrics
  };
}

/**
 * Get list of warehouses for filters
 */
async function getWarehouses() {
  return query('SELECT id, name, region FROM warehouses ORDER BY region, name');
}

/**
 * Get list of regions for filters
 */
async function getRegions() {
  return query('SELECT DISTINCT region FROM warehouses ORDER BY region');
}

/**
 * Get a single shipment with full details
 */
async function getShipmentDetails(shipmentId) {
  const sql = `
    SELECT
      s.*,
      w.name as warehouse_name,
      w.region,
      d.name as driver_name,
      d.behavior_score,
      d.fatigue_score,
      v.vehicle_type,
      v.capacity_kg,
      sc.traffic_congestion_level,
      sc.weather_severity,
      sc.route_risk_level,
      sc.cargo_condition,
      sc.gps_latitude,
      sc.gps_longitude
    FROM shipments s
    JOIN warehouses w ON s.warehouse_id = w.id
    JOIN drivers d ON s.driver_id = d.id
    JOIN vehicles v ON s.vehicle_id = v.id
    LEFT JOIN shipment_conditions sc ON s.id = sc.shipment_id
    WHERE s.id = ?
  `;

  const results = await query(sql, [shipmentId]);
  if (results.length === 0) return null;

  const shipment = results[0];
  const risk = calculateRiskScore(shipment);

  return {
    ...shipment,
    risk_score: risk.score,
    risk_level: risk.riskLevel,
    risk_color: getRiskColor(risk.riskLevel),
    risk_breakdown: risk.breakdown
  };
}

module.exports = {
  getAtRiskShipments,
  getDashboardStats,
  getWarehouses,
  getRegions,
  getShipmentDetails
};
