USE vts_v2;

INSERT INTO devices (public_id, name, imei, iccid, ingest_secret, status, last_seen, battery_percent)
VALUES (
  'DEV003',
  'Office Van',
  '359000000000003',
  '89910000000000000003',
  REPEAT('c', 64),
  'online',
  NOW(),
  74
)
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  status = 'online',
  last_seen = NOW(),
  battery_percent = VALUES(battery_percent);

INSERT INTO device_activation (device_id, token, status, activated_at)
SELECT id, 'SAMPLE-OFFICE-VAN', 'USED', NOW()
FROM devices
WHERE public_id = 'DEV003'
ON DUPLICATE KEY UPDATE status = 'USED', activated_at = NOW();

INSERT INTO device_ownership (device_id, owner_id)
SELECT d.id, u.id
FROM devices d
JOIN users u ON u.mobile = '9999999999'
LEFT JOIN device_ownership o
  ON o.device_id = d.id AND o.owner_id = u.id AND o.ended_at IS NULL
WHERE d.public_id = 'DEV003'
  AND o.id IS NULL;

DELETE l FROM locations l
INNER JOIN devices d ON d.id = l.device_id
WHERE d.public_id = 'DEV003';

INSERT INTO locations (device_id, recorded_at, lat, lng, speed, direction, accuracy)
SELECT
  d.id,
  DATE_SUB(NOW(), INTERVAL (80 - n.n) MINUTE),
  28.4595000 + (n.n * 0.00055),
  77.0266000 + (n.n * 0.00040),
  18 + (n.n % 22),
  (n.n * 12) % 360,
  8
FROM devices d
CROSS JOIN (
  SELECT a.n + b.n * 10 AS n
  FROM (
    SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
    UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
  ) a
  CROSS JOIN (
    SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
    UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
  ) b
) n
WHERE d.public_id = 'DEV003'
  AND n.n BETWEEN 0 AND 79;

INSERT INTO locations (device_id, recorded_at, lat, lng, speed, direction, accuracy)
SELECT
  d.id,
  DATE_SUB(NOW(), INTERVAL (60 - n.n) MINUTE),
  28.6139000 + (n.n * 0.00090),
  77.2090000 + (n.n * 0.00070),
  22 + (n.n % 18),
  (n.n * 9) % 360,
  7
FROM devices d
CROSS JOIN (
  SELECT a.n + b.n * 10 AS n
  FROM (
    SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
    UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
  ) a
  CROSS JOIN (
    SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
    UNION ALL SELECT 4 UNION ALL SELECT 5
  ) b
) n
WHERE d.public_id = 'DEV001'
  AND n.n BETWEEN 0 AND 59;

UPDATE devices
SET last_seen = NOW(), status = 'online'
WHERE public_id IN ('DEV001', 'DEV003');

INSERT INTO alerts (device_id, type, message, lat, lng, created_at)
SELECT d.id, 'geofence_enter', 'Entered Office', 28.4700000, 77.0400000, DATE_SUB(NOW(), INTERVAL 40 MINUTE)
FROM devices d
WHERE d.public_id = 'DEV003';

INSERT INTO alerts (device_id, type, message, lat, lng, created_at)
SELECT d.id, 'overspeed', 'Speed 84 km/h', 28.4750000, 77.0450000, DATE_SUB(NOW(), INTERVAL 18 MINUTE)
FROM devices d
WHERE d.public_id = 'DEV003';

INSERT INTO geofences (device_id, name, lat, lng, radius_m, active)
SELECT d.id, 'Office', 28.4595000, 77.0266000, 300, 1
FROM devices d
LEFT JOIN geofences g ON g.device_id = d.id AND g.name = 'Office'
WHERE d.public_id = 'DEV003'
  AND g.id IS NULL;

SELECT d.public_id, d.name, d.status, COUNT(l.id) AS location_points
FROM devices d
LEFT JOIN locations l ON l.device_id = d.id
WHERE d.public_id IN ('DEV001', 'DEV003')
GROUP BY d.id, d.public_id, d.name, d.status;