
CREATE TABLE roads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    road_name VARCHAR(150),
    latitude DECIMAL(10,7),
    longitude DECIMAL(10,7),
    baseline_speed INT,
    city VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE traffic_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    road_id INT,
    avg_speed INT,
    status ENUM('LANCAR','PADAT','MACET'),
    updated_at DATETIME,
    FOREIGN KEY (road_id) REFERENCES roads(id)
);
CREATE TABLE route_cache (
    id INT AUTO_INCREMENT PRIMARY KEY,
    start_lat DECIMAL(10,7),
    start_lng DECIMAL(10,7),
    end_lat DECIMAL(10,7),
    recommended_road VARCHAR(150),
    estimated_time INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
---------------------------------------------------

-- QUERY UTAMA SISTEM --
-- Cari Jalan fitur Search
SELECT * FROM roads
WHERE road_name LIKE '%Sudirman%'
AND city = 'Pekanbaru';

-- Status Jalan
SELECT r.road_name, t.avg_speed, t.status
FROM roads r
JOIN traffic_data t ON r.id = t.road_id
WHERE r.id = 1
ORDER BY t.updated_at DESC
LIMIT 1;

-- Deteksi Kemacetan (Rule-Based)
SELECT
CASE
  WHEN avg_speed >= baseline_speed * 0.7 THEN 'LANCAR'
  WHEN avg_speed >= baseline_speed * 0.4 THEN 'PADAT'
  ELSE 'MACET'
END AS status
FROM roads r
JOIN traffic_data t ON r.id = t.road_id;

-- Rekomendasi Jalur Tercepat
SELECT r.road_name, t.avg_speed
FROM roads r
JOIN traffic_data t ON r.id = t.road_id
WHERE t.status = 'LANCAR'
AND r.city = 'Pekanbaru'
ORDER BY t.avg_speed DESC
LIMIT 1;
