MokoSuiteLogistics/source/packages/plg_system_mokosuitelogistics/sql/install.mysql.sql

CREATE TABLE IF NOT EXISTS `#__mokosuitelogistics_orders` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_ref` VARCHAR(20) NOT NULL,
  `customer_contact_id` INT DEFAULT NULL,
  `customer_name` VARCHAR(255) NOT NULL,
  `customer_phone` VARCHAR(50) NOT NULL DEFAULT '',
  `customer_email` VARCHAR(255) NOT NULL DEFAULT '',
  `status` ENUM('pending','confirmed','assigned','picked_up','in_transit','delivered','failed','cancelled','returned') NOT NULL DEFAULT 'pending',
  `order_type` ENUM('standard','express','same_day','scheduled','bulk') NOT NULL DEFAULT 'standard',
  `pickup_address` VARCHAR(500) NOT NULL,
  `pickup_lat` DECIMAL(10,7) DEFAULT NULL,
  `pickup_lng` DECIMAL(10,7) DEFAULT NULL,
  `delivery_address` VARCHAR(500) NOT NULL,
  `delivery_lat` DECIMAL(10,7) DEFAULT NULL,
  `delivery_lng` DECIMAL(10,7) DEFAULT NULL,
  `package_type` ENUM('envelope','small_box','medium_box','large_box','pallet','fragile','perishable','custom') NOT NULL DEFAULT 'small_box',
  `weight_kg` DECIMAL(8,2) DEFAULT NULL,
  `estimated_distance_km` DECIMAL(10,2) DEFAULT NULL,
  `delivery_fee` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `total_charge` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `payment_method` ENUM('cash','card','account','prepaid','cod') NOT NULL DEFAULT 'card',
  `payment_status` ENUM('pending','paid','refunded','failed') NOT NULL DEFAULT 'pending',
  `notes` TEXT,
  `created` DATETIME NOT NULL,
  `created_by` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_ref` (`order_ref`),
  KEY `idx_customer` (`customer_contact_id`),
  KEY `idx_status` (`status`),
  KEY `idx_type` (`order_type`),
  KEY `idx_created` (`created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuitelogistics_deliveries` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_id` INT UNSIGNED NOT NULL,
  `driver_id` INT UNSIGNED DEFAULT NULL,
  `route_id` INT UNSIGNED DEFAULT NULL,
  `status` ENUM('assigned','en_route_pickup','picked_up','en_route_delivery','delivered','failed','returned') NOT NULL DEFAULT 'assigned',
  `assigned_at` DATETIME DEFAULT NULL,
  `picked_up_at` DATETIME DEFAULT NULL,
  `delivered_at` DATETIME DEFAULT NULL,
  `failed_at` DATETIME DEFAULT NULL,
  `failure_reason` VARCHAR(500) NOT NULL DEFAULT '',
  `attempt_number` TINYINT UNSIGNED NOT NULL DEFAULT 1,
  `actual_distance_km` DECIMAL(10,2) DEFAULT NULL,
  `driver_notes` TEXT,
  `created` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_order` (`order_id`),
  KEY `idx_driver` (`driver_id`),
  KEY `idx_route` (`route_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuitelogistics_routes` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `route_ref` VARCHAR(20) NOT NULL,
  `driver_id` INT UNSIGNED DEFAULT NULL,
  `status` ENUM('planned','active','completed','cancelled') NOT NULL DEFAULT 'planned',
  `planned_date` DATE NOT NULL,
  `started_at` DATETIME DEFAULT NULL,
  `completed_at` DATETIME DEFAULT NULL,
  `total_stops` INT UNSIGNED NOT NULL DEFAULT 0,
  `total_distance_km` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `notes` TEXT,
  `created` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_ref` (`route_ref`),
  KEY `idx_driver` (`driver_id`),
  KEY `idx_status` (`status`),
  KEY `idx_date` (`planned_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuitelogistics_route_stops` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `route_id` INT UNSIGNED NOT NULL,
  `order_id` INT UNSIGNED DEFAULT NULL,
  `stop_type` ENUM('pickup','delivery','warehouse','return') NOT NULL DEFAULT 'delivery',
  `address` VARCHAR(500) NOT NULL,
  `lat` DECIMAL(10,7) DEFAULT NULL,
  `lng` DECIMAL(10,7) DEFAULT NULL,
  `sequence_order` INT UNSIGNED NOT NULL DEFAULT 0,
  `estimated_arrival` DATETIME DEFAULT NULL,
  `actual_arrival` DATETIME DEFAULT NULL,
  `status` ENUM('pending','arrived','completed','skipped') NOT NULL DEFAULT 'pending',
  `notes` TEXT,
  PRIMARY KEY (`id`),
  KEY `idx_route` (`route_id`),
  KEY `idx_order` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuitelogistics_delivery_proofs` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `delivery_id` INT UNSIGNED NOT NULL,
  `proof_type` ENUM('signature','photo','both') NOT NULL DEFAULT 'photo',
  `recipient_name` VARCHAR(255) NOT NULL DEFAULT '',
  `signature_data` TEXT,
  `photo_path` VARCHAR(500) NOT NULL DEFAULT '',
  `geolocation_lat` DECIMAL(10,7) DEFAULT NULL,
  `geolocation_lng` DECIMAL(10,7) DEFAULT NULL,
  `notes` TEXT,
  `created` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_delivery` (`delivery_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuitelogistics_pricing_rules` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `order_type` ENUM('standard','express','same_day','scheduled','bulk','all') NOT NULL DEFAULT 'all',
  `base_fee` DECIMAL(10,2) NOT NULL DEFAULT 5.00,
  `per_km` DECIMAL(10,2) NOT NULL DEFAULT 1.00,
  `per_kg` DECIMAL(10,2) NOT NULL DEFAULT 0.50,
  `minimum_charge` DECIMAL(10,2) NOT NULL DEFAULT 5.00,
  `express_multiplier` DECIMAL(4,2) NOT NULL DEFAULT 1.50,
  `published` TINYINT NOT NULL DEFAULT 1,
  `created` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_order_type` (`order_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuitelogistics_drivers` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `contact_id` INT DEFAULT NULL,
  `name` VARCHAR(255) NOT NULL,
  `phone` VARCHAR(50) NOT NULL DEFAULT '',
  `email` VARCHAR(255) NOT NULL DEFAULT '',
  `license_number` VARCHAR(50) NOT NULL DEFAULT '',
  `vehicle_description` VARCHAR(255) NOT NULL DEFAULT '',
  `status` ENUM('available','on_delivery','off_duty','inactive') NOT NULL DEFAULT 'available',
  `current_lat` DECIMAL(10,7) DEFAULT NULL,
  `current_lng` DECIMAL(10,7) DEFAULT NULL,
  `rating` DECIMAL(3,2) NOT NULL DEFAULT 5.00,
  `total_deliveries` INT UNSIGNED NOT NULL DEFAULT 0,
  `published` TINYINT NOT NULL DEFAULT 1,
  `created` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_contact` (`contact_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuitelogistics_warehouses` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `address` VARCHAR(500) NOT NULL,
  `lat` DECIMAL(10,7) DEFAULT NULL,
  `lng` DECIMAL(10,7) DEFAULT NULL,
  `manager_contact_id` INT DEFAULT NULL,
  `phone` VARCHAR(50) NOT NULL DEFAULT '',
  `published` TINYINT NOT NULL DEFAULT 1,
  `created` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_manager` (`manager_contact_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;