USE bulo;

CREATE TABLE IF NOT EXISTS motorista_sessoes_online (
  id INT AUTO_INCREMENT PRIMARY KEY,
  id_motorista INT NOT NULL,
  inicio_em DATETIME NOT NULL,
  fim_em DATETIME NULL,
  duracao_segundos INT NOT NULL DEFAULT 0,
  criado_em TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_sessoes_online_motorista FOREIGN KEY (id_motorista) REFERENCES usuarios(id),
  INDEX idx_sessoes_online_motorista_inicio (id_motorista, inicio_em),
  INDEX idx_sessoes_online_fim (fim_em)
);

INSERT INTO ganhos_diarios (id_motorista, data_ref, total_corridas, total_valor) VALUES
  (1, '2026-06-02', 4, 86.50),
  (1, '2026-06-03', 6, 143.20),
  (1, '2026-06-05', 5, 118.75),
  (1, '2026-06-08', 3, 72.40),
  (1, '2026-06-09', 7, 168.90),
  (1, '2026-06-11', 4, 97.30),
  (1, '2026-06-13', 8, 205.10),
  (1, '2026-06-15', 5, 126.80),
  (1, '2026-06-16', 6, 151.45),
  (1, '2026-06-18', 4, 92.60),
  (1, '2026-06-19', 7, 184.25),
  (1, '2026-06-21', 3, 68.70),
  (1, '2026-06-22', 2, 49.90)
ON DUPLICATE KEY UPDATE
  total_corridas = VALUES(total_corridas),
  total_valor = VALUES(total_valor),
  atualizado_em = CURRENT_TIMESTAMP;

INSERT INTO motorista_sessoes_online (id_motorista, inicio_em, fim_em, duracao_segundos)
SELECT 1, '2026-06-02 07:40:00', '2026-06-02 11:55:00', TIMESTAMPDIFF(SECOND, '2026-06-02 07:40:00', '2026-06-02 11:55:00')
WHERE NOT EXISTS (SELECT 1 FROM motorista_sessoes_online WHERE id_motorista = 1 AND inicio_em = '2026-06-02 07:40:00');

INSERT INTO motorista_sessoes_online (id_motorista, inicio_em, fim_em, duracao_segundos)
SELECT 1, '2026-06-03 13:10:00', '2026-06-03 19:35:00', TIMESTAMPDIFF(SECOND, '2026-06-03 13:10:00', '2026-06-03 19:35:00')
WHERE NOT EXISTS (SELECT 1 FROM motorista_sessoes_online WHERE id_motorista = 1 AND inicio_em = '2026-06-03 13:10:00');

INSERT INTO motorista_sessoes_online (id_motorista, inicio_em, fim_em, duracao_segundos)
SELECT 1, '2026-06-05 08:20:00', '2026-06-05 14:10:00', TIMESTAMPDIFF(SECOND, '2026-06-05 08:20:00', '2026-06-05 14:10:00')
WHERE NOT EXISTS (SELECT 1 FROM motorista_sessoes_online WHERE id_motorista = 1 AND inicio_em = '2026-06-05 08:20:00');

INSERT INTO motorista_sessoes_online (id_motorista, inicio_em, fim_em, duracao_segundos)
SELECT 1, '2026-06-08 09:00:00', '2026-06-08 12:30:00', TIMESTAMPDIFF(SECOND, '2026-06-08 09:00:00', '2026-06-08 12:30:00')
WHERE NOT EXISTS (SELECT 1 FROM motorista_sessoes_online WHERE id_motorista = 1 AND inicio_em = '2026-06-08 09:00:00');

INSERT INTO motorista_sessoes_online (id_motorista, inicio_em, fim_em, duracao_segundos)
SELECT 1, '2026-06-09 07:15:00', '2026-06-09 15:40:00', TIMESTAMPDIFF(SECOND, '2026-06-09 07:15:00', '2026-06-09 15:40:00')
WHERE NOT EXISTS (SELECT 1 FROM motorista_sessoes_online WHERE id_motorista = 1 AND inicio_em = '2026-06-09 07:15:00');

INSERT INTO motorista_sessoes_online (id_motorista, inicio_em, fim_em, duracao_segundos)
SELECT 1, '2026-06-11 16:00:00', '2026-06-11 21:20:00', TIMESTAMPDIFF(SECOND, '2026-06-11 16:00:00', '2026-06-11 21:20:00')
WHERE NOT EXISTS (SELECT 1 FROM motorista_sessoes_online WHERE id_motorista = 1 AND inicio_em = '2026-06-11 16:00:00');

INSERT INTO motorista_sessoes_online (id_motorista, inicio_em, fim_em, duracao_segundos)
SELECT 1, '2026-06-13 10:30:00', '2026-06-13 19:00:00', TIMESTAMPDIFF(SECOND, '2026-06-13 10:30:00', '2026-06-13 19:00:00')
WHERE NOT EXISTS (SELECT 1 FROM motorista_sessoes_online WHERE id_motorista = 1 AND inicio_em = '2026-06-13 10:30:00');

INSERT INTO motorista_sessoes_online (id_motorista, inicio_em, fim_em, duracao_segundos)
SELECT 1, '2026-06-15 08:45:00', '2026-06-15 14:25:00', TIMESTAMPDIFF(SECOND, '2026-06-15 08:45:00', '2026-06-15 14:25:00')
WHERE NOT EXISTS (SELECT 1 FROM motorista_sessoes_online WHERE id_motorista = 1 AND inicio_em = '2026-06-15 08:45:00');

INSERT INTO motorista_sessoes_online (id_motorista, inicio_em, fim_em, duracao_segundos)
SELECT 1, '2026-06-16 12:00:00', '2026-06-16 20:10:00', TIMESTAMPDIFF(SECOND, '2026-06-16 12:00:00', '2026-06-16 20:10:00')
WHERE NOT EXISTS (SELECT 1 FROM motorista_sessoes_online WHERE id_motorista = 1 AND inicio_em = '2026-06-16 12:00:00');

INSERT INTO motorista_sessoes_online (id_motorista, inicio_em, fim_em, duracao_segundos)
SELECT 1, '2026-06-18 07:30:00', '2026-06-18 12:05:00', TIMESTAMPDIFF(SECOND, '2026-06-18 07:30:00', '2026-06-18 12:05:00')
WHERE NOT EXISTS (SELECT 1 FROM motorista_sessoes_online WHERE id_motorista = 1 AND inicio_em = '2026-06-18 07:30:00');

INSERT INTO motorista_sessoes_online (id_motorista, inicio_em, fim_em, duracao_segundos)
SELECT 1, '2026-06-19 14:20:00', '2026-06-19 22:45:00', TIMESTAMPDIFF(SECOND, '2026-06-19 14:20:00', '2026-06-19 22:45:00')
WHERE NOT EXISTS (SELECT 1 FROM motorista_sessoes_online WHERE id_motorista = 1 AND inicio_em = '2026-06-19 14:20:00');

INSERT INTO motorista_sessoes_online (id_motorista, inicio_em, fim_em, duracao_segundos)
SELECT 1, '2026-06-21 09:30:00', '2026-06-21 13:10:00', TIMESTAMPDIFF(SECOND, '2026-06-21 09:30:00', '2026-06-21 13:10:00')
WHERE NOT EXISTS (SELECT 1 FROM motorista_sessoes_online WHERE id_motorista = 1 AND inicio_em = '2026-06-21 09:30:00');

INSERT INTO motorista_sessoes_online (id_motorista, inicio_em, fim_em, duracao_segundos)
SELECT 1, '2026-06-22 08:00:00', '2026-06-22 10:40:00', TIMESTAMPDIFF(SECOND, '2026-06-22 08:00:00', '2026-06-22 10:40:00')
WHERE NOT EXISTS (SELECT 1 FROM motorista_sessoes_online WHERE id_motorista = 1 AND inicio_em = '2026-06-22 08:00:00');
