ALTER TABLE `tmp_hard_turn`
  ADD COLUMN IF NOT EXISTS `origin_adm` int(10) DEFAULT NULL AFTER `id`,
  ADD COLUMN IF NOT EXISTS `cve_vehicle` int(10) DEFAULT NULL AFTER `origin_adm`,
  ADD COLUMN IF NOT EXISTS `send_time` datetime DEFAULT NULL AFTER `cve_vehicle`,
  ADD COLUMN IF NOT EXISTS `prev_send_time` datetime DEFAULT NULL AFTER `send_time`,
  ADD COLUMN IF NOT EXISTS `speed_ini` decimal(10,2) DEFAULT NULL AFTER `prev_send_time`,
  ADD COLUMN IF NOT EXISTS `speed_end` decimal(10,2) DEFAULT NULL AFTER `speed_ini`,
  ADD COLUMN IF NOT EXISTS `turn_speed` decimal(10,2) DEFAULT NULL AFTER `speed_end`,
  ADD COLUMN IF NOT EXISTS `created_at` datetime NOT NULL DEFAULT current_timestamp() AFTER `type`;

CREATE UNIQUE INDEX IF NOT EXISTS `uq_tmp_hard_turn_event`
ON `tmp_hard_turn` (`origin_adm`, `cve_vehicle`, `send_time`, `direction`);

CREATE INDEX IF NOT EXISTS `idx_tmp_hard_turn_vehicle_time`
ON `tmp_hard_turn` (`origin_adm`, `cve_vehicle`, `send_time`);

CREATE TABLE IF NOT EXISTS `tmp_hard_turn_cluster` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `direction` varchar(10) NOT NULL,
  `cluster_lat_key` int(11) NOT NULL,
  `cluster_lon_key` int(11) NOT NULL,
  `avg_lat` decimal(10,7) NOT NULL,
  `avg_lon` decimal(10,7) NOT NULL,
  `avg_angle` decimal(10,2) NOT NULL,
  `min_angle` int(10) NOT NULL,
  `max_angle` int(10) NOT NULL,
  `avg_speed_ini` decimal(10,2) DEFAULT NULL,
  `avg_speed_end` decimal(10,2) DEFAULT NULL,
  `avg_turn_speed` decimal(10,2) DEFAULT NULL,
  `coincidences` int(10) NOT NULL,
  `unique_vehicles` int(10) NOT NULL,
  `suave_hits` int(10) NOT NULL DEFAULT 0,
  `media_hits` int(10) NOT NULL DEFAULT 0,
  `cerrada_hits` int(10) NOT NULL DEFAULT 0,
  `muy_cerrada_hits` int(10) NOT NULL DEFAULT 0,
  `dominant_type` varchar(30) NOT NULL,
  `first_seen` datetime DEFAULT NULL,
  `last_seen` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_tmp_hard_turn_cluster` (`direction`, `cluster_lat_key`, `cluster_lon_key`),
  KEY `idx_tmp_hard_turn_cluster_hits` (`coincidences`, `unique_vehicles`),
  KEY `idx_tmp_hard_turn_cluster_time` (`last_seen`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

TRUNCATE TABLE `tmp_hard_turn_cluster`;

INSERT INTO `tmp_hard_turn_cluster` (
  `direction`,
  `cluster_lat_key`,
  `cluster_lon_key`,
  `avg_lat`,
  `avg_lon`,
  `avg_angle`,
  `min_angle`,
  `max_angle`,
  `avg_speed_ini`,
  `avg_speed_end`,
  `avg_turn_speed`,
  `coincidences`,
  `unique_vehicles`,
  `suave_hits`,
  `media_hits`,
  `cerrada_hits`,
  `muy_cerrada_hits`,
  `dominant_type`,
  `first_seen`,
  `last_seen`
)
SELECT
  `direction`,
  ROUND(`lat_ini` * 1000, 0) AS `cluster_lat_key`,
  ROUND(`lon_ini` * 1000, 0) AS `cluster_lon_key`,
  ROUND(AVG(`lat_ini`), 7) AS `avg_lat`,
  ROUND(AVG(`lon_ini`), 7) AS `avg_lon`,
  ROUND(AVG(`angle`), 2) AS `avg_angle`,
  MIN(`angle`) AS `min_angle`,
  MAX(`angle`) AS `max_angle`,
  ROUND(AVG(`speed_ini`), 2) AS `avg_speed_ini`,
  ROUND(AVG(`speed_end`), 2) AS `avg_speed_end`,
  ROUND(AVG(`turn_speed`), 2) AS `avg_turn_speed`,
  COUNT(*) AS `coincidences`,
  COUNT(DISTINCT CASE
    WHEN `origin_adm` IS NOT NULL AND `cve_vehicle` IS NOT NULL
    THEN CONCAT(`origin_adm`, ':', `cve_vehicle`)
    ELSE NULL
  END) AS `unique_vehicles`,
  SUM(CASE WHEN `type` = 'Suave' THEN 1 ELSE 0 END) AS `suave_hits`,
  SUM(CASE WHEN `type` = 'Media' THEN 1 ELSE 0 END) AS `media_hits`,
  SUM(CASE WHEN `type` = 'Cerrada' THEN 1 ELSE 0 END) AS `cerrada_hits`,
  SUM(CASE WHEN `type` = 'Muy cerrada' THEN 1 ELSE 0 END) AS `muy_cerrada_hits`,
  CASE
    WHEN SUM(CASE WHEN `type` = 'Muy cerrada' THEN 1 ELSE 0 END) >= GREATEST(
      SUM(CASE WHEN `type` = 'Cerrada' THEN 1 ELSE 0 END),
      SUM(CASE WHEN `type` = 'Media' THEN 1 ELSE 0 END),
      SUM(CASE WHEN `type` = 'Suave' THEN 1 ELSE 0 END)
    ) THEN 'Muy cerrada'
    WHEN SUM(CASE WHEN `type` = 'Cerrada' THEN 1 ELSE 0 END) >= GREATEST(
      SUM(CASE WHEN `type` = 'Media' THEN 1 ELSE 0 END),
      SUM(CASE WHEN `type` = 'Suave' THEN 1 ELSE 0 END)
    ) THEN 'Cerrada'
    WHEN SUM(CASE WHEN `type` = 'Media' THEN 1 ELSE 0 END) >= SUM(CASE WHEN `type` = 'Suave' THEN 1 ELSE 0 END)
    THEN 'Media'
    ELSE 'Suave'
  END AS `dominant_type`,
  MIN(`send_time`) AS `first_seen`,
  MAX(`send_time`) AS `last_seen`
FROM `tmp_hard_turn`
WHERE `lat_ini` BETWEEN -90 AND 90
  AND `lon_ini` BETWEEN -180 AND 180
  AND `direction` IN ('left', 'right')
  AND `angle` > 10
GROUP BY
  `direction`,
  ROUND(`lat_ini` * 1000, 0),
  ROUND(`lon_ini` * 1000, 0);