CREATE TABLE service_requests (
    id CHAR(36) PRIMARY KEY,
    customer_id CHAR(36) NOT NULL,
    service_type ENUM('Planting', 'Maintenance', 'Harvesting') NOT NULL,
    location VARCHAR(500) NOT NULL,
    preferred_date DATE NOT NULL,
    description TEXT NOT NULL,
    service_fee DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'assigned', 'in_progress', 'completed', 'cancelled') DEFAULT 'pending',
    assigned_to CHAR(36) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT,
    INDEX idx_customer_id (customer_id),
    INDEX idx_status (status),
    INDEX idx_assigned_to (assigned_to)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;