CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  name VARCHAR(120) NOT NULL,
  role ENUM('admin','motoboy') NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  phone VARCHAR(20) NOT NULL UNIQUE,
  name VARCHAR(120) NOT NULL,
  address TEXT NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  customer_name VARCHAR(120) NOT NULL,
  customer_phone VARCHAR(20) NOT NULL,
  address TEXT NOT NULL,
  note_image_path VARCHAR(255) NULL,
  note_original_name VARCHAR(255) NULL,
  track_token VARCHAR(80) NOT NULL UNIQUE,
  status ENUM('created','started','arrived','delivered') NOT NULL DEFAULT 'created',
  created_by INT NOT NULL,
  assigned_motoboy_id INT NULL,
  created_at DATETIME NOT NULL,
  started_at DATETIME NULL,
  arrived_at DATETIME NULL,
  delivered_at DATETIME NULL,
  start_lat DECIMAL(10,7) NULL,
  start_lng DECIMAL(10,7) NULL,
  dest_lat DECIMAL(10,7) NULL,
  dest_lng DECIMAL(10,7) NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id),
  FOREIGN KEY (created_by) REFERENCES users(id),
  FOREIGN KEY (assigned_motoboy_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS order_gps_points (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  order_id INT NOT NULL,
  motoboy_id INT NOT NULL,
  lat DECIMAL(10,7) NOT NULL,
  lng DECIMAL(10,7) NOT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (motoboy_id) REFERENCES users(id),
  INDEX (order_id),
  INDEX (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS order_events (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  order_id INT NOT NULL,
  event VARCHAR(30) NOT NULL,
  note TEXT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id),
  INDEX(order_id),
  INDEX(created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed users (troque as senhas depois)
INSERT INTO users (username, name, role, password_hash, is_active, created_at)
VALUES
('admin','Atendente','admin',  '$2y$10$D4o2J0cKQDTmJXlP2P0p6e87y9mI5obqM2s7b7B/8bQLeZrVDm5ue', 1, NOW()),
('moto','Motoboy','motoboy',  '$2y$10$G9x1D5uJpM0J0q3iVbS7VesU8QvVdGQ2rB3f.8q4w4kYQFzKqXbK2', 1, NOW())
ON DUPLICATE KEY UPDATE username=username;

-- hashes acima correspondem a admin123 e moto123
