CREATE DATABASE app_entregador CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE app_entregador;

CREATE TABLE companies (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(120) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE motoboys (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(120) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  is_online TINYINT(1) NOT NULL DEFAULT 0,
  last_lat DECIMAL(10,7) NULL,
  last_lng DECIMAL(10,7) NULL,
  last_seen TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE deliveries (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company_id INT NOT NULL,
  motoboy_id INT NULL,
  status ENUM('waiting','accepted','picked_up','delivering','delivered','canceled') NOT NULL DEFAULT 'waiting',

  pickup_address VARCHAR(255) NOT NULL,
  pickup_lat DECIMAL(10,7) NULL,
  pickup_lng DECIMAL(10,7) NULL,

  dropoff_address VARCHAR(255) NOT NULL,
  dropoff_lat DECIMAL(10,7) NULL,
  dropoff_lng DECIMAL(10,7) NULL,

  customer_name VARCHAR(120) NULL,
  customer_phone VARCHAR(40) NULL,

  tracking_token CHAR(40) NOT NULL UNIQUE,

  accepted_at TIMESTAMP NULL,
  delivered_at TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  proof_photo_path VARCHAR(255) NULL,
  rating TINYINT NULL,
  rating_comment VARCHAR(255) NULL,

  FOREIGN KEY (company_id) REFERENCES companies(id),
  FOREIGN KEY (motoboy_id) REFERENCES motoboys(id)
);

CREATE TABLE delivery_locations (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  delivery_id INT NOT NULL,
  motoboy_id INT NOT NULL,
  lat DECIMAL(10,7) NOT NULL,
  lng DECIMAL(10,7) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX (delivery_id),
  FOREIGN KEY (delivery_id) REFERENCES deliveries(id),
  FOREIGN KEY (motoboy_id) REFERENCES motoboys(id)
);
