CREATE TABLE `tblmileage_trips` (
    `id` BIGINT(20) NOT NULL,
    `tenant_id` BIGINT(20) NOT NULL,
    `code` VARCHAR(32) COLLATE utf8_unicode_ci NOT NULL,
    `task_id` BIGINT(20) DEFAULT NULL,
    `visit_id` BIGINT(20) DEFAULT NULL,
    `trip_for` ENUM('general', 'task', 'visit') DEFAULT 'general',
    `staff_id` BIGINT(20) DEFAULT NULL,
    `status` ENUM('in_progress','completed') DEFAULT 'in_progress',
    `completed_on` DATETIME DEFAULT NULL,
    `start_latitude` VARCHAR(100) DEFAULT NULL,
    `start_longitude` VARCHAR(100) DEFAULT NULL,
    `start_location` TEXT DEFAULT NULL,
    `end_latitude` VARCHAR(100) DEFAULT NULL,
    `end_longitude` VARCHAR(100) DEFAULT NULL,
    `end_location` TEXT DEFAULT NULL,
    `distance_covered` INT DEFAULT 0 COMMENT 'In Meters',
    `note` TEXT COLLATE utf8_unicode_ci DEFAULT NULL,
    `added_on` DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `tblmileage_trips`
    ADD PRIMARY KEY (`id`),
    ADD KEY `task_id` (`task_id`),
    ADD KEY `visit_id` (`visit_id`),
    ADD KEY `trip_for` (`trip_for`),
    ADD KEY `status` (`status`),
    ADD KEY `code` (`code`),
    ADD KEY `staff_id` (`staff_id`);

ALTER TABLE `tblmileage_trips` MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;