<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="workHistory">

  <sql id="workHistorySearchCondition">
    AND DELETED_DATE IS NULL
    <if test="work_type != null and work_type != ''">
      AND work_type = #{work_type}
    </if>
    <if test="status != null and status != ''">
      AND status = #{status}
    </if>
    <if test="vehicle_number != null and vehicle_number != ''">
      AND vehicle_number ILIKE '%' || #{vehicle_number} || '%'
    </if>
    <if test="driver_name != null and driver_name != ''">
      AND driver_name ILIKE '%' || #{driver_name} || '%'
    </if>
    <if test="start_date != null and start_date != ''">
      AND work_date >= #{start_date}::DATE
    </if>
    <if test="end_date != null and end_date != ''">
      AND work_date &lt;= #{end_date}::DATE
    </if>
    <if test="search != null and search != ''">
      AND (work_number ILIKE '%' || #{search} || '%'
        OR vehicle_number ILIKE '%' || #{search} || '%'
        OR driver_name ILIKE '%' || #{search} || '%'
        OR cargo_name ILIKE '%' || #{search} || '%')
    </if>
  </sql>

  <select id="getWorkHistoryList" parameterType="map" resultType="map">
    SELECT *

    FROM work_history

    WHERE 1=1
    <include refid="workHistorySearchCondition"/>

    ORDER BY work_date DESC
    <include refid="common.pagination"/>
  </select>

  <select id="getWorkHistoryListCnt" parameterType="map" resultType="int">
    SELECT COUNT(*)

    FROM work_history

    WHERE 1=1
    <include refid="workHistorySearchCondition"/>
  </select>

  <select id="getWorkHistoryInfo" parameterType="map" resultType="map">
    SELECT *

    FROM work_history

    WHERE id = #{id} AND DELETED_DATE IS NULL
  </select>

  <insert id="insertWorkHistory" parameterType="map" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO work_history (
      work_type, vehicle_number, driver_name, origin, destination,
      cargo_name, cargo_weight, cargo_unit, distance, distance_unit,
      status, scheduled_time, estimated_arrival, notes, created_by,
      work_date, CREATED_DATE, UPDATED_DATE
    ) VALUES (
      #{work_type, jdbcType=VARCHAR},
      #{vehicle_number, jdbcType=VARCHAR},
      #{driver_name, jdbcType=VARCHAR},
      #{origin, jdbcType=VARCHAR},
      #{destination, jdbcType=VARCHAR},
      #{cargo_name, jdbcType=VARCHAR},
      #{cargo_weight, jdbcType=NUMERIC},
      COALESCE(#{cargo_unit, jdbcType=VARCHAR}, 'ton'),
      #{distance, jdbcType=NUMERIC},
      COALESCE(#{distance_unit, jdbcType=VARCHAR}, 'km'),
      COALESCE(#{status, jdbcType=VARCHAR}, 'pending'),
      #{scheduled_time, jdbcType=TIMESTAMP},
      #{estimated_arrival, jdbcType=TIMESTAMP},
      #{notes, jdbcType=VARCHAR},
      #{created_by, jdbcType=VARCHAR},
      COALESCE(#{work_date, jdbcType=DATE}, CURRENT_DATE),
      NOW(), NOW()
    )
  </insert>

  <update id="updateWorkHistory" parameterType="map">
    UPDATE work_history SET UPDATED_DATE = NOW()
    <if test="work_type != null">, work_type = #{work_type}</if>
    <if test="vehicle_number != null">, vehicle_number = #{vehicle_number}</if>
    <if test="driver_name != null">, driver_name = #{driver_name}</if>
    <if test="origin != null">, origin = #{origin}</if>
    <if test="destination != null">, destination = #{destination}</if>
    <if test="cargo_name != null">, cargo_name = #{cargo_name}</if>
    <if test="cargo_weight != null">, cargo_weight = #{cargo_weight}</if>
    <if test="cargo_unit != null">, cargo_unit = #{cargo_unit}</if>
    <if test="distance != null">, distance = #{distance}</if>
    <if test="distance_unit != null">, distance_unit = #{distance_unit}</if>
    <if test="status != null">, status = #{status}</if>
    <if test="scheduled_time != null">, scheduled_time = #{scheduled_time}</if>
    <if test="estimated_arrival != null">, estimated_arrival = #{estimated_arrival}</if>
    <if test="actual_arrival != null">, actual_arrival = #{actual_arrival}</if>
    <if test="is_on_time != null">, is_on_time = #{is_on_time}</if>
    <if test="work_date != null">, work_date = #{work_date}</if>
    <if test="notes != null">, notes = #{notes}</if>

    WHERE id = #{id} AND DELETED_DATE IS NULL
  </update>

  <update id="deleteWorkHistory" parameterType="map">
    UPDATE work_history SET DELETED_DATE = NOW(), UPDATED_DATE = NOW()

    WHERE id = #{id} AND DELETED_DATE IS NULL
  </update>

  <select id="getWorkHistoryTodayStats" parameterType="map" resultType="map">
    SELECT
      COUNT(*) AS today_total,
      COUNT(*) FILTER (WHERE status = 'completed') AS today_completed

    FROM work_history

    WHERE DATE(work_date) = CURRENT_DATE AND DELETED_DATE IS NULL
  </select>

  <select id="getWorkHistoryTotalStats" parameterType="map" resultType="map">
    SELECT
      COALESCE(SUM(cargo_weight), 0) AS total_weight,
      COALESCE(SUM(distance), 0) AS total_distance

    FROM work_history

    WHERE DELETED_DATE IS NULL AND status = 'completed'
  </select>

  <select id="getWorkHistoryOnTimeRate" parameterType="map" resultType="map">
    SELECT
      COALESCE(
        COUNT(*) FILTER (WHERE is_on_time = TRUE) * 100.0 / NULLIF(COUNT(*), 0),
        0
      ) AS on_time_rate

    FROM work_history

    WHERE DELETED_DATE IS NULL
      AND status = 'completed'
      AND is_on_time IS NOT NULL
  </select>

  <select id="getWorkHistoryTypeDistribution" parameterType="map" resultType="map">
    SELECT work_type, COUNT(*) AS count

    FROM work_history

    WHERE DELETED_DATE IS NULL

    GROUP BY work_type
  </select>

  <select id="getWorkHistoryMonthlyTrend" parameterType="map" resultType="map">
    SELECT
      TO_CHAR(work_date, 'YYYY-MM') AS month,
      COUNT(*) AS total,
      COUNT(*) FILTER (WHERE status = 'completed') AS completed,
      COALESCE(SUM(cargo_weight), 0) AS weight,
      COALESCE(SUM(distance), 0) AS distance

    FROM work_history

    WHERE DELETED_DATE IS NULL
      AND work_date >= CURRENT_DATE - (#{months}::INTEGER * INTERVAL '1 month')

    GROUP BY TO_CHAR(work_date, 'YYYY-MM')

    ORDER BY month DESC
  </select>

  <select id="getWorkHistoryTopRoutes" parameterType="map" resultType="map">
    SELECT
      origin,
      destination,
      COUNT(*) AS count,
      COALESCE(SUM(cargo_weight), 0) AS total_weight

    FROM work_history

    WHERE DELETED_DATE IS NULL
      AND origin IS NOT NULL
      AND destination IS NOT NULL
      AND work_type IN ('inbound', 'outbound', 'transfer')

    GROUP BY origin, destination

    ORDER BY count DESC

    LIMIT #{limit}
  </select>

</mapper>
