-- Insert comprehensive test tracking history data
USE sts_v2;

-- Clear existing test data (optional)
-- DELETE FROM device_locations WHERE device_id IN ('DEV001', 'DEV002', 'DEV003', 'DEV004');

-- Insert detailed tracking history for DEV001 (School route - Home to School)
INSERT INTO device_locations (device_id, latitude, longitude, altitude, accuracy, speed, heading, address, timestamp) VALUES
-- Morning journey: Home to School (8:00 AM - 8:30 AM)
('DEV001', 19.076090, 72.877426, 14, 5, 0, 0, 'Home - Andheri West, Mumbai', '2024-01-15 08:00:00'),
('DEV001', 19.076500, 72.877200, 15, 4, 15, 45, 'Walking to bus stop', '2024-01-15 08:05:00'),
('DEV001', 19.077000, 72.876800, 16, 3, 25, 90, 'Bus stop - Andheri West', '2024-01-15 08:08:00'),
('DEV001', 19.078500, 72.875500, 18, 4, 35, 120, 'On school bus', '2024-01-15 08:12:00'),
('DEV001', 19.081000, 72.873000, 20, 3, 40, 135, 'Moving towards school', '2024-01-15 08:18:00'),
('DEV001', 19.084500, 72.870500, 22, 5, 20, 150, 'Near school area', '2024-01-15 08:25:00'),
('DEV001', 19.086000, 72.869000, 25, 2, 0, 0, 'St. Mary School, Andheri', '2024-01-15 08:30:00'),

-- Afternoon journey: School to Home (3:00 PM - 3:30 PM)
('DEV001', 19.086000, 72.869000, 25, 3, 0, 0, 'St. Mary School, Andheri', '2024-01-15 15:00:00'),
('DEV001', 19.084000, 72.871000, 22, 4, 25, 300, 'Leaving school', '2024-01-15 15:05:00'),
('DEV001', 19.081500, 72.873500, 20, 3, 35, 315, 'On return bus', '2024-01-15 15:12:00'),
('DEV001', 19.078000, 72.876000, 18, 4, 30, 270, 'Heading home', '2024-01-15 15:20:00'),
('DEV001', 19.076800, 72.877000, 16, 5, 15, 225, 'Near home area', '2024-01-15 15:28:00'),
('DEV001', 19.076090, 72.877426, 14, 2, 0, 0, 'Home - Andheri West, Mumbai', '2024-01-15 15:30:00'),

-- Insert tracking history for DEV002 (Tuition route)
-- Morning: Home to Tuition (10:00 AM - 10:20 AM)
('DEV002', 19.017615, 72.856164, 8, 4, 0, 0, 'Home - Bandra West, Mumbai', '2024-01-15 10:00:00'),
('DEV002', 19.018500, 72.855800, 10, 3, 20, 60, 'Walking to tuition', '2024-01-15 10:05:00'),
('DEV002', 19.020000, 72.854500, 12, 4, 25, 90, 'Bandra West Road', '2024-01-15 10:10:00'),
('DEV002', 19.022500, 72.852000, 15, 3, 15, 120, 'Near tuition center', '2024-01-15 10:18:00'),
('DEV002', 19.023000, 72.851500, 16, 2, 0, 0, 'Bright Minds Tuition, Bandra', '2024-01-15 10:20:00'),

-- Return: Tuition to Home (12:00 PM - 12:20 PM)
('DEV002', 19.023000, 72.851500, 16, 3, 0, 0, 'Bright Minds Tuition, Bandra', '2024-01-15 12:00:00'),
('DEV002', 19.021500, 72.853000, 14, 4, 20, 240, 'Leaving tuition', '2024-01-15 12:05:00'),
('DEV002', 19.019000, 72.855000, 11, 3, 25, 270, 'Bandra West Road', '2024-01-15 12:12:00'),
('DEV002', 19.018000, 72.856000, 9, 4, 15, 300, 'Near home', '2024-01-15 12:18:00'),
('DEV002', 19.017615, 72.856164, 8, 2, 0, 0, 'Home - Bandra West, Mumbai', '2024-01-15 12:20:00'),

-- Insert tracking history for DEV003 (Weekend outing)
-- Saturday outing: Home to Mall (2:00 PM - 6:00 PM)
('DEV003', 19.054489, 72.833336, 12, 3, 0, 0, 'Home - Juhu, Mumbai', '2024-01-13 14:00:00'),
('DEV003', 19.055000, 72.834000, 13, 4, 30, 45, 'Leaving for mall', '2024-01-13 14:10:00'),
('DEV003', 19.058000, 72.837000, 15, 3, 40, 90, 'Juhu Road', '2024-01-13 14:20:00'),
('DEV003', 19.063000, 72.842000, 18, 4, 35, 120, 'Towards Infinity Mall', '2024-01-13 14:35:00'),
('DEV003', 19.070000, 72.848000, 20, 2, 0, 0, 'Infinity Mall, Malad', '2024-01-13 14:45:00'),
('DEV003', 19.070000, 72.848000, 20, 3, 0, 0, 'Shopping at mall', '2024-01-13 17:30:00'),
('DEV003', 19.065000, 72.843000, 18, 4, 35, 270, 'Returning home', '2024-01-13 17:45:00'),
('DEV003', 19.058000, 72.837000, 15, 3, 40, 225, 'Juhu Road', '2024-01-13 18:00:00'),
('DEV003', 19.054489, 72.833336, 12, 2, 0, 0, 'Home - Juhu, Mumbai', '2024-01-13 18:15:00'),

-- Insert tracking history for DEV004 (Sports activity)
-- Evening: Home to Sports Complex (5:00 PM - 8:00 PM)
('DEV004', 19.103498, 72.827847, 25, 4, 0, 0, 'Home - Malad West, Mumbai', '2024-01-15 17:00:00'),
('DEV004', 19.104000, 72.828500, 26, 3, 25, 30, 'Walking to sports complex', '2024-01-15 17:10:00'),
('DEV004', 19.106000, 72.830000, 28, 4, 20, 60, 'Malad West Road', '2024-01-15 17:18:00'),
('DEV004', 19.108500, 72.832500, 30, 3, 15, 90, 'Near sports complex', '2024-01-15 17:25:00'),
('DEV004', 19.110000, 72.834000, 32, 2, 0, 0, 'Malad Sports Complex', '2024-01-15 17:30:00'),
('DEV004', 19.110000, 72.834000, 32, 3, 0, 0, 'Playing football', '2024-01-15 19:30:00'),
('DEV004', 19.108000, 72.832000, 30, 4, 20, 270, 'Leaving sports complex', '2024-01-15 19:40:00'),
('DEV004', 19.105000, 72.829000, 27, 3, 25, 240, 'Heading home', '2024-01-15 19:50:00'),
('DEV004', 19.103498, 72.827847, 25, 2, 0, 0, 'Home - Malad West, Mumbai', '2024-01-15 20:00:00'),

-- Add some recent data for today's testing
-- DEV001 current day journey
('DEV001', 19.076090, 72.877426, 14, 3, 0, 0, 'Home - Andheri West', DATE_SUB(NOW(), INTERVAL 2 HOUR)),
('DEV001', 19.078000, 72.875000, 18, 4, 30, 90, 'On the way to school', DATE_SUB(NOW(), INTERVAL 90 MINUTE)),
('DEV001', 19.082000, 72.872000, 22, 3, 25, 120, 'School route', DATE_SUB(NOW(), INTERVAL 60 MINUTE)),
('DEV001', 19.086000, 72.869000, 25, 2, 0, 0, 'At school', DATE_SUB(NOW(), INTERVAL 30 MINUTE)),

-- DEV002 current day
('DEV002', 19.017615, 72.856164, 8, 4, 0, 0, 'Home - Bandra West', DATE_SUB(NOW(), INTERVAL 3 HOUR)),
('DEV002', 19.020000, 72.854000, 12, 3, 20, 60, 'Going to library', DATE_SUB(NOW(), INTERVAL 150 MINUTE)),
('DEV002', 19.025000, 72.850000, 16, 2, 0, 0, 'At library', DATE_SUB(NOW(), INTERVAL 45 MINUTE)),

-- DEV003 current day
('DEV003', 19.054489, 72.833336, 12, 3, 0, 0, 'Home - Juhu', DATE_SUB(NOW(), INTERVAL 4 HOUR)),
('DEV003', 19.060000, 72.840000, 18, 4, 35, 90, 'Going to friend house', DATE_SUB(NOW(), INTERVAL 180 MINUTE)),
('DEV003', 19.065000, 72.845000, 22, 2, 0, 0, 'At friend house', DATE_SUB(NOW(), INTERVAL 60 MINUTE)),

-- DEV004 current day
('DEV004', 19.103498, 72.827847, 25, 4, 0, 0, 'Home - Malad West', DATE_SUB(NOW(), INTERVAL 1 HOUR)),
('DEV004', 19.107000, 72.831000, 28, 3, 25, 45, 'Going to coaching', DATE_SUB(NOW(), INTERVAL 30 MINUTE)),
('DEV004', 19.112000, 72.836000, 32, 2, 0, 0, 'At coaching center', NOW());