-- Create shipments table
CREATE TABLE IF NOT EXISTS `shipments` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `shipment_number` VARCHAR(50) UNIQUE NOT NULL COMMENT 'Unique shipment identifier',
  `initiator_type` ENUM('customer', 'vendor') NOT NULL COMMENT 'Who created the shipment',
  `initiator_id` INT(11) NOT NULL COMMENT 'ID of the person who created the shipment',
  `rider_id` INT(11) DEFAULT NULL COMMENT 'Assigned rider/dispatcher',
  `status` ENUM('pending', 'rider_assigned', 'accepted', 'picked_up', 'on_the_way', 'delivered', 'cancelled', 'rejected') NOT NULL DEFAULT 'pending',
  `pickup_name` VARCHAR(255) NOT NULL,
  `pickup_phone` VARCHAR(50) NOT NULL,
  `pickup_address` TEXT NOT NULL,
  `pickup_location` POINT NOT NULL COMMENT 'Pickup coordinates',
  `pickup_lat` DECIMAL(10, 8) NOT NULL,
  `pickup_lng` DECIMAL(11, 8) NOT NULL,
  `pickup_notes` TEXT,
  `delivery_name` VARCHAR(255) NOT NULL,
  `delivery_phone` VARCHAR(50) NOT NULL,
  `delivery_address` TEXT NOT NULL,
  `delivery_location` POINT NOT NULL COMMENT 'Delivery coordinates',
  `delivery_lat` DECIMAL(10, 8) NOT NULL,
  `delivery_lng` DECIMAL(11, 8) NOT NULL,
  `delivery_notes` TEXT,
  `preferred_delivery_date` DATE NOT NULL,
  `preferred_delivery_time` TIME NOT NULL,
  `items_description` TEXT NOT NULL,
  `package_images` TEXT NULL COMMENT 'JSON array of package image filenames (uploaded by sender)',
  `items_weight` DECIMAL(8, 2) DEFAULT 0.00 COMMENT 'Weight in kg',
  `items_dimensions` VARCHAR(100) DEFAULT NULL COMMENT 'Length x Width x Height in cm',
  `fragile` BOOLEAN DEFAULT FALSE,
  `initial_price` DECIMAL(10, 2) NOT NULL COMMENT 'Initial calculated price',
  `negotiated_price` DECIMAL(10, 2) DEFAULT NULL COMMENT 'Final agreed price after negotiation',
  `payment_method` ENUM('wallet', 'card', 'cash_on_delivery') NOT NULL,
  `payment_status` ENUM('pending', 'paid') DEFAULT 'pending',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  KEY `idx_shipment_initiator` (`initiator_type`, `initiator_id`),
  KEY `idx_shipment_rider` (`rider_id`),
  KEY `idx_shipment_status` (`status`),
  KEY `idx_shipment_date` (`preferred_delivery_date`),
  SPATIAL INDEX `idx_pickup_location` (`pickup_location`),
  SPATIAL INDEX `idx_delivery_location` (`delivery_location`),
  
  CONSTRAINT `fk_shipment_rider` FOREIGN KEY (`rider_id`) REFERENCES `dispatchers` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Create shipment negotiation history table
CREATE TABLE IF NOT EXISTS `shipment_negotiations` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `shipment_id` INT(11) NOT NULL,
  `initiator_type` ENUM('customer', 'vendor', 'rider') NOT NULL COMMENT 'Who made the offer',
  `initiator_id` INT(11) NOT NULL COMMENT 'ID of the person making the offer',
  `price_offered` DECIMAL(10,2) NOT NULL COMMENT 'Price offered in this negotiation',
  `message` TEXT COMMENT 'Additional message or reason for the offer',
  `status` ENUM('pending', 'accepted', 'rejected', 'counter_offered') NOT NULL DEFAULT 'pending',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  KEY `idx_negotiation_shipment` (`shipment_id`),
  KEY `idx_negotiation_status` (`status`),
  
  CONSTRAINT `fk_negotiation_shipment` FOREIGN KEY (`shipment_id`) REFERENCES `shipments` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Create shipment status updates table
CREATE TABLE IF NOT EXISTS `shipment_status_updates` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `shipment_id` INT(11) NOT NULL,
  `rider_id` INT(11) NULL COMMENT 'Can be NULL when shipment is first created',
  `status` ENUM('pending', 'rider_assigned', 'accepted', 'picked_up', 'on_the_way', 'delivered', 'cancelled', 'rejected') NOT NULL,
  `location` POINT NULL COMMENT 'Current location when status was updated',
  `notes` TEXT COMMENT 'Additional notes about the status update',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  KEY `idx_status_shipment` (`shipment_id`),
  KEY `idx_status_rider` (`rider_id`),
  KEY `idx_status_created` (`created_at`),
  
  CONSTRAINT `fk_status_shipment` FOREIGN KEY (`shipment_id`) REFERENCES `shipments` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_status_rider` FOREIGN KEY (`rider_id`) REFERENCES `dispatchers` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Create shipment ratings table
CREATE TABLE IF NOT EXISTS `shipment_ratings` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `shipment_id` INT(11) NOT NULL,
  `rider_id` INT(11) NOT NULL,
  `rater_type` ENUM('customer', 'vendor') NOT NULL,
  `rater_id` INT(11) NOT NULL,
  `rating` TINYINT NOT NULL CHECK (rating >= 1 AND rating <= 5),
  `review` TEXT COMMENT 'Written review of the shipment service',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_shipment_rating` (`shipment_id`, `rider_id`, `rater_type`, `rater_id`),
  KEY `idx_rating_rider` (`rider_id`),
  KEY `idx_rating_created` (`created_at`),
  
  CONSTRAINT `fk_rating_shipment` FOREIGN KEY (`shipment_id`) REFERENCES `shipments` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_rating_rider` FOREIGN KEY (`rider_id`) REFERENCES `dispatchers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Create shipment tips table (separate from ratings to allow tipping without rating)
CREATE TABLE IF NOT EXISTS `shipment_tips` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `shipment_id` INT(11) NOT NULL,
  `tipper_type` ENUM('customer', 'vendor') NOT NULL,
  `tipper_id` INT(11) NOT NULL,
  `dispatcher_id` INT(11) NOT NULL,
  `amount` DECIMAL(12,2) NOT NULL,
  `payment_method` ENUM('wallet', 'paystack') NOT NULL,
  `payment_reference` VARCHAR(255) DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_shipment_tipper` (`shipment_id`, `tipper_type`, `tipper_id`),
  KEY `idx_tip_shipment` (`shipment_id`),
  KEY `idx_tip_dispatcher` (`dispatcher_id`),
  CONSTRAINT `fk_tip_shipment` FOREIGN KEY (`shipment_id`) REFERENCES `shipments` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_tip_dispatcher` FOREIGN KEY (`dispatcher_id`) REFERENCES `dispatchers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Add indexes for better performance
CREATE INDEX IF NOT EXISTS `idx_shipments_initiator_status` ON `shipments` (`initiator_type`, `initiator_id`, `status`);
CREATE INDEX IF NOT EXISTS `idx_shipments_delivery_date` ON `shipments` (`preferred_delivery_date`, `status`);
CREATE INDEX IF NOT EXISTS `idx_shipments_price_range` ON `shipments` (`initial_price`, `status`);

