-- Location tracking table setup
USE sts_v2;

-- Device locations table to store GPS coordinates
CREATE TABLE IF NOT EXISTS device_locations (
  id INT PRIMARY KEY AUTO_INCREMENT,
  device_id VARCHAR(20) NOT NULL,
  latitude DECIMAL(10, 8) NOT NULL,
  longitude DECIMAL(11, 8) NOT NULL,
  altitude DECIMAL(8, 2) DEFAULT NULL,
  accuracy DECIMAL(8, 2) DEFAULT NULL,
  speed DECIMAL(8, 2) DEFAULT NULL,
  heading DECIMAL(5, 2) DEFAULT NULL,
  address TEXT DEFAULT NULL,
  timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE,
  INDEX idx_device_timestamp (device_id, timestamp)
);

-- Insert sample location data for existing devices
INSERT INTO device_locations (device_id, latitude, longitude, address) VALUES 
('DEV001', 19.076090, 72.877426, 'Andheri West, Mumbai, Maharashtra, India'),
('DEV002', 19.017615, 72.856164, 'Bandra West, Mumbai, Maharashtra, India'),
('DEV003', 19.054489, 72.833336, 'Juhu, Mumbai, Maharashtra, India'),
('DEV004', 19.103498, 72.827847, 'Malad West, Mumbai, Maharashtra, India');

-- Add some historical data (older timestamps)
INSERT INTO device_locations (device_id, latitude, longitude, address, timestamp) VALUES 
('DEV001', 19.075090, 72.876426, 'Near Andheri Station, Mumbai', DATE_SUB(NOW(), INTERVAL 5 MINUTE)),
('DEV002', 19.016615, 72.855164, 'Bandra Kurla Complex, Mumbai', DATE_SUB(NOW(), INTERVAL 10 MINUTE)),
('DEV003', 19.053489, 72.832336, 'Juhu Beach, Mumbai', DATE_SUB(NOW(), INTERVAL 15 MINUTE)),
('DEV004', 19.102498, 72.826847, 'Malad Railway Station, Mumbai', DATE_SUB(NOW(), INTERVAL 20 MINUTE));