ALTER TABLE `mt_quote_list` ADD `bl_issue_agent_code` text;
ALTER TABLE `mt_quote_list` ADD `bl_issue_agent` text;
ALTER TABLE `mt_quote_list` ADD `mbl_status` text;
ALTER TABLE `mt_quote_list` ADD `hbl_status` text;
ALTER TABLE `mt_quote_list` ADD `free_days` text;
ALTER TABLE `mt_quote_list` ADD `do_issued` int NOT NULL DEFAULT '0';

CREATE TABLE `truck_master` (
  `id` int(11) NOT NULL,
  `truck_no` text COLLATE utf8_unicode_ci,
  `make` text COLLATE utf8_unicode_ci,
  `model_no` text COLLATE utf8_unicode_ci,
  `start_km` text COLLATE utf8_unicode_ci,
  `truck_type` text COLLATE utf8_unicode_ci,
  `fuel_cap` text COLLATE utf8_unicode_ci,
  `status` text COLLATE utf8_unicode_ci,
  `ownership` text COLLATE utf8_unicode_ci,
  `purchase_date` text COLLATE utf8_unicode_ci,
  `register_date` text COLLATE utf8_unicode_ci,
  `sale_date` text COLLATE utf8_unicode_ci,
  `engine_no` text COLLATE utf8_unicode_ci,
  `chassis_no` text COLLATE utf8_unicode_ci,
  `fuel_type` text COLLATE utf8_unicode_ci,
  `body_type` text COLLATE utf8_unicode_ci,
  `notes` text COLLATE utf8_unicode_ci,
  `location_id` text COLLATE utf8_unicode_ci,
  `company_id` text COLLATE utf8_unicode_ci,
  `created_by` text COLLATE utf8_unicode_ci,
  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `ip` text COLLATE utf8_unicode_ci,
  `ua` text COLLATE utf8_unicode_ci,
  `ekey` text COLLATE utf8_unicode_ci,
  `temp_code` text COLLATE utf8_unicode_ci,
  `modified_date` timestamp NULL DEFAULT NULL,
  `modified_by` text COLLATE utf8_unicode_ci,
  `current_location` text COLLATE utf8_unicode_ci
)

ALTER TABLE `truck_master`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `truck_master`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=16;

  CREATE TABLE `driver_list` (
  `id` int(11) NOT NULL,
  `driver_name` text COLLATE utf8_unicode_ci,
  `status` text COLLATE utf8_unicode_ci,
  `driver_contact_no` text COLLATE utf8_unicode_ci,
  `citizen_id_no` text COLLATE utf8_unicode_ci,
  `license_no` text COLLATE utf8_unicode_ci,
  `license_issue_state` text COLLATE utf8_unicode_ci,
  `license_validity` text COLLATE utf8_unicode_ci,
  `home_address` text COLLATE utf8_unicode_ci,
  `village` text COLLATE utf8_unicode_ci,
  `district` text COLLATE utf8_unicode_ci,
  `police_station` text COLLATE utf8_unicode_ci,
  `state` text COLLATE utf8_unicode_ci,
  `country` text COLLATE utf8_unicode_ci,
  `joining_date` text COLLATE utf8_unicode_ci,
  `leaving_date` text COLLATE utf8_unicode_ci,
  `referred_by` text COLLATE utf8_unicode_ci,
  `ref_mobile_no` text COLLATE utf8_unicode_ci,
  `ref_vehicle_no` text COLLATE utf8_unicode_ci,
  `relation_with_ref_driver` text COLLATE utf8_unicode_ci,
  `license_upload_file` text COLLATE utf8_unicode_ci,
  `location_id` text COLLATE utf8_unicode_ci,
  `company_id` text COLLATE utf8_unicode_ci,
  `created_by` text COLLATE utf8_unicode_ci,
  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `ip` text COLLATE utf8_unicode_ci,
  `ua` text COLLATE utf8_unicode_ci,
  `ekey` text COLLATE utf8_unicode_ci,
  `temp_code` text COLLATE utf8_unicode_ci,
  `modified_date`  timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modified_by` text COLLATE utf8_unicode_ci,
  `bank_name` text COLLATE utf8_unicode_ci,
  `account_no` text COLLATE utf8_unicode_ci,
  `account_name` text COLLATE utf8_unicode_ci,
  `ifsc_code` text COLLATE utf8_unicode_ci
)

ALTER TABLE `driver_list`
  ADD PRIMARY KEY (`id`);


ALTER TABLE `driver_list`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=19;

  CREATE TABLE `truck_statutory_list` (
  `id` int(11) NOT NULL,
  `truck_id` text COLLATE utf8_unicode_ci,
  `doc_type` text COLLATE utf8_unicode_ci,
  `reference` text COLLATE utf8_unicode_ci,
  `valid_from` text COLLATE utf8_unicode_ci,
  `valid_to` text COLLATE utf8_unicode_ci,
  `remarks` text COLLATE utf8_unicode_ci,
  `currency` text COLLATE utf8_unicode_ci,
  `fee` text COLLATE utf8_unicode_ci,
  `file_name` text COLLATE utf8_unicode_ci,
  `file_url` text COLLATE utf8_unicode_ci,
  `location_id` text COLLATE utf8_unicode_ci,
  `company_id` text COLLATE utf8_unicode_ci,
  `created_by` text COLLATE utf8_unicode_ci,
  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `ip` text COLLATE utf8_unicode_ci,
  `ua` text COLLATE utf8_unicode_ci,
  `ekey` text COLLATE utf8_unicode_ci,
  `temp_code` text COLLATE utf8_unicode_ci,
  `modified_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified_by` text COLLATE utf8_unicode_ci
) 

  ALTER TABLE `truck_statutory_list`
  ADD PRIMARY KEY (`id`);


ALTER TABLE `truck_statutory_list`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;



CREATE TABLE `truck_driver_activity` (
  `id` int(11) NOT NULL,
  `truck_id` text COLLATE utf8_unicode_ci,
  `driver_id` text COLLATE utf8_unicode_ci,
  `driver_name` text COLLATE utf8_unicode_ci,
  `activity_date` text COLLATE utf8_unicode_ci,
  `status` text COLLATE utf8_unicode_ci,
  `location_id` text COLLATE utf8_unicode_ci,
  `company_id` text COLLATE utf8_unicode_ci,
  `created_by` text COLLATE utf8_unicode_ci,
  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `ip` text COLLATE utf8_unicode_ci,
  `ua` text COLLATE utf8_unicode_ci,
  `ekey` text COLLATE utf8_unicode_ci,
  `temp_code` text COLLATE utf8_unicode_ci,
  `modified_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modified_by` text COLLATE utf8_unicode_ci
)

ALTER TABLE `truck_driver_activity`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `truck_driver_activity`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=21;

CREATE TABLE `country_master` (
  `id` int(11) NOT NULL,
  `country_code` text,
  `country_name` text,
  `country_uncode` text
)

ALTER TABLE `country_master`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `country_master`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=238;

  CREATE TABLE `driver_documents_list` (
  `id` int(11) NOT NULL,
  `driver_id` text COLLATE utf8_unicode_ci,
  `doc_type` text COLLATE utf8_unicode_ci,
  `notes` text COLLATE utf8_unicode_ci,
  `file_name` text COLLATE utf8_unicode_ci,
  `file_url` text COLLATE utf8_unicode_ci,
  `location_id` text COLLATE utf8_unicode_ci,
  `company_id` text COLLATE utf8_unicode_ci,
  `created_by` text COLLATE utf8_unicode_ci,
  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `ip` text COLLATE utf8_unicode_ci,
  `ua` text COLLATE utf8_unicode_ci,
  `ekey` text COLLATE utf8_unicode_ci,
  `temp_code` text COLLATE utf8_unicode_ci,
  `modified_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modified_by` text COLLATE utf8_unicode_ci
)
ALTER TABLE `driver_documents_list`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `driver_documents_list`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

  CREATE TABLE `driver_duty_history` (
  `id` int(11) NOT NULL,
  `driver_id` text COLLATE utf8_unicode_ci,
  `joining_date` text COLLATE utf8_unicode_ci,
  `leaving_date` text COLLATE utf8_unicode_ci,
  `reason_for_leaving` text COLLATE utf8_unicode_ci,
  `years_of_exp` text COLLATE utf8_unicode_ci,
  `vehicle_class` text COLLATE utf8_unicode_ci,
  `salary` text COLLATE utf8_unicode_ci,
  `pf` text COLLATE utf8_unicode_ci,
  `location_id` text COLLATE utf8_unicode_ci,
  `company_id` text COLLATE utf8_unicode_ci,
  `created_by` text COLLATE utf8_unicode_ci,
  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `ip` text COLLATE utf8_unicode_ci,
  `ua` text COLLATE utf8_unicode_ci,
  `ekey` text COLLATE utf8_unicode_ci,
  `temp_code` text COLLATE utf8_unicode_ci,
  `modified_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modified_by` text COLLATE utf8_unicode_ci
)
ALTER TABLE `driver_duty_history`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `driver_duty_history`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
  CREATE TABLE `order_list` (
  `id` int(11) NOT NULL,
  `order_no` text COLLATE utf8_unicode_ci,
  `customer` text COLLATE utf8_unicode_ci,
  `customer_reference` text COLLATE utf8_unicode_ci,
  `quote_no` text COLLATE utf8_unicode_ci,
  `inclusive_of_toll` text COLLATE utf8_unicode_ci,
  `sales_person` text COLLATE utf8_unicode_ci,
  `round_trip` text COLLATE utf8_unicode_ci,
  `odo` text COLLATE utf8_unicode_ci,
  `quantity` text COLLATE utf8_unicode_ci,
  `uom` text COLLATE utf8_unicode_ci,
  `no_of_vehicles_required` text COLLATE utf8_unicode_ci,
  `cargo_description` text COLLATE utf8_unicode_ci,
  `transportation_type` text COLLATE utf8_unicode_ci,
  `transporter` text COLLATE utf8_unicode_ci,
  `routing_code` text COLLATE utf8_unicode_ci,
  `status` text COLLATE utf8_unicode_ci,
  `notes` text COLLATE utf8_unicode_ci,
  `service_type` text COLLATE utf8_unicode_ci,
  `vehicle_category` text COLLATE utf8_unicode_ci,
  `location_id` text COLLATE utf8_unicode_ci,
  `company_id` text COLLATE utf8_unicode_ci,
  `created_by` text COLLATE utf8_unicode_ci,
  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `ip` text COLLATE utf8_unicode_ci,
  `ua` text COLLATE utf8_unicode_ci,
  `ekey` text COLLATE utf8_unicode_ci,
  `temp_code` text COLLATE utf8_unicode_ci,
  `modified_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modified_by` text COLLATE utf8_unicode_ci,
  `truck_no` text COLLATE utf8_unicode_ci,
  `pod_status` text COLLATE utf8_unicode_ci
)

  ALTER TABLE `order_list`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `order_list`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=887;

  CREATE TABLE `trip_list` (
  `id` int(11) NOT NULL,
  `order_id` text,
  `trip_no` text,
  `trip_date` date DEFAULT NULL,
  `truck` text,
  `driver` text,
  `notes` text,
  `gr_wgt` text,
  `do_no` text,
  `container1` text,
  `seal1` text,
  `container2` text,
  `seal2` text,
  `grn` text,
  `lr_no` text,
  `lr_date` date DEFAULT NULL,
  `company_id` text,
  `location_id` text,
  `ip` text,
  `ua` text,
  `ekey` text,
  `created_by` text,
  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modified_by` text,
  `modified_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `temp_code` text,
  `inclusive_of_toll` text,
  `round_trip` text,
  `odo` text,
  `transportation_type` text,
  `routing_code` text,
  `transporter` text
)

ALTER TABLE `trip_list`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `trip_list`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1156;

  CREATE TABLE `order_party` (
  `id` int(11) NOT NULL,
  `order_id` text COLLATE utf8_unicode_ci,
  `party_type` text COLLATE utf8_unicode_ci,
  `party_name` text COLLATE utf8_unicode_ci,
  `location_id` text COLLATE utf8_unicode_ci,
  `company_id` text COLLATE utf8_unicode_ci,
  `created_by` text COLLATE utf8_unicode_ci,
  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `ip` text COLLATE utf8_unicode_ci,
  `ua` text COLLATE utf8_unicode_ci,
  `ekey` text COLLATE utf8_unicode_ci,
  `temp_code` text COLLATE utf8_unicode_ci,
  `modified_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modified_by` text COLLATE utf8_unicode_ci
)

ALTER TABLE `order_party`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `order_party`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

  CREATE TABLE `order_route` (
  `id` int(11) NOT NULL,
  `trip_id` int(11) DEFAULT NULL,
  `order_id` text COLLATE utf8_unicode_ci,
  `leg_number` text COLLATE utf8_unicode_ci,
  `pickup_location` text COLLATE utf8_unicode_ci,
  `pickup_pin` text COLLATE utf8_unicode_ci,
  `pickup_address` text COLLATE utf8_unicode_ci,
  `dropoff_location` text COLLATE utf8_unicode_ci,
  `dropoff_pin` text COLLATE utf8_unicode_ci,
  `dropoff_address` text COLLATE utf8_unicode_ci,
  `notes` text COLLATE utf8_unicode_ci,
  `location_id` text COLLATE utf8_unicode_ci,
  `company_id` text COLLATE utf8_unicode_ci,
  `created_by` text COLLATE utf8_unicode_ci,
  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `ip` text COLLATE utf8_unicode_ci,
  `ua` text COLLATE utf8_unicode_ci,
  `ekey` text COLLATE utf8_unicode_ci,
  `temp_code` text COLLATE utf8_unicode_ci,
  `modified_date` timestamp NULL DEFAULT NULL,
  `modified_by` text COLLATE utf8_unicode_ci
)
ALTER TABLE `order_route`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `order_route`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1156;

  CREATE TABLE `order_rate` (
  `id` int(11) NOT NULL,
  `trip_id` int(11) DEFAULT NULL,
  `order_id` text COLLATE utf8_unicode_ci,
  `charge_code` text COLLATE utf8_unicode_ci,
  `basis` text COLLATE utf8_unicode_ci,
  `currency` text COLLATE utf8_unicode_ci,
  `ex_rate` text COLLATE utf8_unicode_ci,
  `tariff` text COLLATE utf8_unicode_ci,
  `customer` text COLLATE utf8_unicode_ci,
  `is_invoicable` text COLLATE utf8_unicode_ci,
  `remarks` text COLLATE utf8_unicode_ci,
  `location_id` text COLLATE utf8_unicode_ci,
  `company_id` text COLLATE utf8_unicode_ci,
  `created_by` text COLLATE utf8_unicode_ci,
  `created_date` timestamp NULL DEFAULT NULL,
  `ip` text COLLATE utf8_unicode_ci,
  `ua` text COLLATE utf8_unicode_ci,
  `ekey` text COLLATE utf8_unicode_ci,
  `temp_code` text COLLATE utf8_unicode_ci,
  `modified_date` timestamp NULL DEFAULT NULL,
  `modified_by` text COLLATE utf8_unicode_ci,
  `invoiced` text COLLATE utf8_unicode_ci,
  `inv_pay_no` text COLLATE utf8_unicode_ci,
  `proforma_no` text COLLATE utf8_unicode_ci
)

CREATE TABLE `schedule_task` (
  `id` int NOT NULL,
  `report_no` text,
  `schedule_task_description` text,
  `tname` text,
  `cnames` text,
  `order_by` text,
  `file_name` text,
  `report_filters` json DEFAULT NULL,
  `recurrence_pattern` text,
  `rp_daily` text,
  `rp_daily_days` text,
  `rp_daily_scheduled_run_time` text,
  `range_of_recurrence` text,
  `rr_start_date` date DEFAULT NULL,
  `rr_end_after_occurrences` text,
  `rr_end_by` text,
  `email_to` text,
  `email_cc` text,
  `email_bcc` text,
  `ip` text,
  `uid` text,
  `ua` text,
  `ekey` text,
  `created_date` date DEFAULT NULL,
  `location_id` text,
  `company_id` text,
  `modified_by` text,
  `modified_date` date DEFAULT NULL,
  `temp_code` text,
  `rp_wk_monday` text,
  `rp_wk_tuesday` text,
  `rp_wk_wednesday` text,
  `rp_wk_thursday` text,
  `rp_wk_friday` text,
  `rp_wk_saturday` text,
  `rp_wk_sunday` text,
  `rp_wk_weeks_on` text,
  `rp_mon_pap_yr_day` text,
  `rp_mon_pap_yr_day_n` text,
  `rp_mon_pap_yr_every_n_month` text
)
ALTER TABLE `schedule_task`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `schedule_task`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=19;

  CREATE TABLE `schedule_task_no_autogen` (
  `id` int NOT NULL,
  `company_name` text,
  `location_name` text,
  `doc_date` text,
  `doc_type` text,
  `order_id` int DEFAULT NULL,
  `order_no` text,
  `created_by` text,
  `created_date` text,
  `temp_code` text
)
ALTER TABLE `schedule_task_no_autogen`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `schedule_task_no_autogen`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15;

  CREATE TABLE `scheduled_task_email_sent_list` (
  `id` int NOT NULL,
  `schedule_task_description` text,
  `email_to` text,
  `email_cc` text,
  `email_bcc` text,
  `success` text,
  `aws_ses_msg` text,
  `created_date` text
)
ALTER TABLE `scheduled_task_email_sent_list`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `scheduled_task_email_sent_list`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=269;

  ALTER TABLE `salesman_effective_list` ADD `uid` TEXT NULL AFTER `temp_code`;
  ALTER TABLE `salesman_effective_list` ADD `modified_date` DATE NULL AFTER `uid`;
  ALTER TABLE `salesman_effective_list` ADD `modified_by` TEXT NULL AFTER `modified_date`;

--TOP NAV BAR NEW
ALTER TABLE `page_list` ADD `product_type` TEXT NULL;
ALTER TABLE page_list 
MODIFY COLUMN product_type ENUM('FX', 'TX', 'AX', 'NX', 'SHARED') NULL;

update `page_list` set product_type='FX' where module_name in (
'Booking',
'Box Control',
'Documentation',
'Analytics',
'Operations',
'Sales',
'Quote',
'KPI'
    );

update `page_list` set product_type='TX' where module_name in (
'BookingTX'
    );    

update `page_list` set product_type='SHARED' where module_name in (
'Configuration',
'Job Accounting',
'Finance',
'Finance Report',
'User Admin',
'Setup',
'Intelligize',
'Fixed Assets'
    );

UPDATE `bluelogis_software_product_info` 
SET 
`product_name` = 'BlueLogis',
`product_title` = 'ERP - BLUELOGIS CONSULTING';

ALTER TABLE `user_softwareproduct_list` 
ADD COLUMN `product_type` ENUM('FX', 'TX', 'AX', 'NX') NOT NULL AFTER `product_url`,
ADD COLUMN `is_primary` TINYINT(1) DEFAULT 0 AFTER `product_type`;

UPDATE `user_softwareproduct_list` SET `is_primary` = 1 WHERE product_name='BlueLogis FX';

insert into `user_softwareproduct_list`
(username,product_name,product_desc,product_url,product_type,is_primary,company,location)
select username,'BlueLogis FX','Forwarding ERP Module','https://gpl.bluelogis.app/fx/app','FX',1,'GPL','CHENNAI' from user_master;

insert into `user_softwareproduct_list`
(username,product_name,product_desc,product_url,product_type,is_primary,company,location)
SELECT username,'BlueLogis TX','Transport ERP Module','https://gpl.bluelogis.app/tx/app','TX',1,'GPL','CHENNAI' FROM `user_softwareproduct_list` WHERE `username` LIKE '%%bluelogis%';

INSERT INTO `page_list` (`id`, `cat`, `subcat`, `module_name`, `module_url`, `page_url`, `page_url1`, `page_name`, `page_icon`, `menu_icon`, `created_date`, `ip`, `ua`, `ekey`, `temp_code`, `created_by`, `page_description`, `kb_link`) VALUES
(null, 16, 1.20, 'Fixed Assets', 'fixed-assets', 'fa-report', '', 'Depreciation Report', 'assets/newicons/fixed-assets.svg', NULL, '2026-02-09 12:46:22', '49.204.136.47', 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/144.0.0.0 Safari/537.36', '828a1e816a77031d6b310020eef3990e37c773c2891d8a9e274937', '446989321f4dc44', 'admin_bluelogis', 'To track the monthly depreciation of your active fleet.', ''),
(null, 16, 1.00, 'Fixed Assets', 'fixed-assets', 'fixed-asset-master', 'fixed-asset-master', 'Asset Group', 'assets/newicons/fixed-assets.svg', NULL, '2026-02-09 13:41:44', '49.204.136.47', 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/144.0.0.0 Safari/537.36', '893c2dace568a7893065614389a68495fc5e5efbc31fbf2c6c048c16246c89', '44698932a964b13', 'admin_bluelogis', 'Fixed Asset Group Master', ''),
(null, 16, 1.10, 'Fixed Assets', 'fixed-assets', 'fa-list', 'fa', 'Assets', 'assets/newicons/fixed-assets.svg', NULL, '2026-02-09 13:41:29', '49.204.136.47', 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/144.0.0.0 Safari/537.36', '893c2dace568a7893065614389a68495fc5e5efbc31fbf2c6c048c16246c89', '44698932e162350', 'admin_bluelogis', 'Assets List', ''),
(null, 16, 1.30, 'Fixed Assets', 'fixed-assets', 'asset-disposal-report', 'Disposal Report', 'Disposal Report', 'assets/newicons/fixed-assets.svg', NULL, '2026-02-09 12:40:41', '49.204.136.47', 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/144.0.0.0 Safari/537.36', '828a1e816a77031d6b310020eef3990e37c773c2891d8a9e274937', '446989d5c9dbb39', 'admin_bluelogis', 'A dedicated report to analyze the financial performance (Gain/Loss) of assets retired.', '');

INSERT INTO `page_list` (`id`, `cat`, `subcat`, `module_name`, `module_url`, `page_url`, `page_url1`, `page_name`, `announcement_id`, `announcement_msg`, `is_announcement_active`, `page_icon`, `menu_icon`, `created_date`, `ip`, `ua`, `ekey`, `temp_code`, `created_by`, `page_description`, `kb_link`, `module_level2`, `page_tooltip`, `show_in_list`, `show_in_detail`, `product_type`) VALUES
(null, 16, '1.20', 'KPI', 'kpi', 'product-head-kpi', '', 'Product Head KPI', NULL, NULL, 0, 'assets/newicons/kpi.svg', NULL, '2025-11-12 09:05:15', '49.204.117.79', 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/142.0.0.0 Safari/537.36', 'b9f37c2fa8c86b66635751fdbfb86d12fe63f74a315f695f148cea792623f11bf9d927', '7689f6fc0251ba', 'ops_bluelogis', 'Product Head KPI for Sea & Air Jobs', 'https://crm.bluelogis.app/crm/knowledge-base/article/understanding-the-kpi-dashboard-for-air-export-jobs', NULL, NULL, 0, 0, 'FX'),
(null, 16, '1.10', 'KPI', 'kpi', 'finance-head-kpi', '', 'Fin. Incharge KPI', NULL, NULL, 0, 'assets/newicons/kpi.svg', NULL, '2025-11-12 09:05:15', '49.204.117.79', 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/142.0.0.0 Safari/537.36', 'b9f37c2fa8c86b66635751fdbfb86d12fe63f74a315f695f148cea792623f11bf9d927', '768ef4f929a6ec', 'ops_bluelogis', 'Finance Incharge KPI for Sea & Air Jobs', 'https://crm.bluelogis.app/crm/knowledge-base/article/understanding-the-kpi-dashboard-for-air-export-jobs', NULL, NULL, 0, 0, 'FX'),
(null, 16, '1.00', 'KPI', 'kpi', 'cs-dashboard', '', 'CS KPI', NULL, NULL, 0, 'assets/newicons/kpi.svg', NULL, '2025-10-29 20:53:01', '49.204.134.15', 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/141.0.0.0 Safari/537.36', '4f2ae3f28dd8464dc2d27e8b1f167571d2c2f1f3fbaa9c618b781a6a13dc77', '76901f2975fcfe', 'ops_bluelogis', 'CS KPI for Sea & Air Jobs', 'https://crm.bluelogis.app/crm/knowledge-base/article/understanding-the-kpi-dashboard-for-air-export-jobs', NULL, NULL, 0, 0, 'FX');

INSERT INTO `access_rights_master` 
( `pid`, `add_edit`, `view`, `approve_cancel`, `role`, `cancel`, `url`, `ptype`, `editr`, `deleter`, `sub_cat`) 
select  id, 'true', 'true', 'true', 'ADMIN', 'true', NULL, NULL, 'true', 'true', NULL 
from page_list where module_name in ('KPI','Fixed Assets');

ALTER TABLE `mt_order_entry` ADD `expected_clearance_date` DATE NULL AFTER `delivery_agent_name`, ADD `actual_clearance_date` DATE NULL AFTER `expected_clearance_date`;
ALTER TABLE `mt_order_route_air` ADD `atd` DATE NULL AFTER `flight_no`, ADD `ata` DATE NULL AFTER `atd`;


ALTER TABLE `mt_order_cost` ADD `is_amount_changed` TEXT NULL AFTER `accrual`;

ALTER TABLE `fixed_asset_list` ADD `is_sold` TEXT NULL AFTER `temp_code`, ADD `sale_date` DATE NULL AFTER `is_sold`, ADD `sale_invoice` TEXT NULL AFTER `sale_date`, ADD `sale_amount` TEXT NULL AFTER `sale_invoice`;
ALTER TABLE `fixed_asset_list` ADD `purchase_foreign_amount` TEXT NULL AFTER `sale_amount`;
ALTER TABLE `fixed_asset_list` ADD `purchase_currency` TEXT NULL AFTER `purchase_foreign_amount`, ADD `purchase_ex_rate` TEXT NULL AFTER `purchase_currency`;
ALTER TABLE `fixed_asset_list` ADD `sale_foreign_amount` TEXT NULL AFTER `purchase_ex_rate`, ADD `sale_currency` TEXT NULL AFTER `sale_foreign_amount`, ADD `sale_ex_rate` TEXT NULL AFTER `sale_currency`, ADD `accumulated_depreciation` TEXT NULL AFTER `sale_ex_rate`, ADD `realized_profit_loss` TEXT NULL AFTER `accumulated_depreciation`;
ALTER TABLE `fixed_asset_list` ADD `last_depreciation_date` DATE NULL AFTER `realized_profit_loss`;
ALTER TABLE `fixed_asset_list` ADD `locations_name` TEXT NULL AFTER `last_depreciation_date`;

ALTER TABLE `mt_order_entry` ADD `vessel` TEXT NULL AFTER `actual_clearance_date`, ADD `voyage` TEXT NULL AFTER `vessel`, ADD `etd` TEXT NULL AFTER `voyage`, ADD `eta` TEXT NULL AFTER `etd`, ADD `atd` TEXT NULL AFTER `eta`, ADD `ata` TEXT NULL AFTER `atd`;

DELIMITER $$

CREATE TRIGGER after_mt_order_route_insert
AFTER INSERT ON mt_order_route
FOR EACH ROW
BEGIN
    UPDATE mt_order_entry 
    SET 
        vessel = NEW.vessel,
        voyage = NEW.voyage,
        etd    = NEW.etd,
        eta    = NEW.eta,
        atd    = NEW.atd,
        ata    = NEW.ata
    WHERE id = NEW.order_id;
END $$

CREATE TRIGGER after_mt_order_route_update
AFTER UPDATE ON mt_order_route
FOR EACH ROW
BEGIN
    UPDATE mt_order_entry 
    SET 
        vessel = NEW.vessel,
        voyage = NEW.voyage,
        etd    = NEW.etd,
        eta    = NEW.eta,
        atd    = NEW.atd,
        ata    = NEW.ata
    WHERE id = NEW.order_id;
END $$

CREATE TRIGGER after_mt_order_route_delete
AFTER DELETE ON mt_order_route
FOR EACH ROW
BEGIN
    UPDATE mt_order_entry 
    SET 
        vessel = NULL,
        voyage = NULL,
        etd    = NULL,
        eta    = NULL,
        atd    = NULL,
        ata    = NULL
    WHERE id = OLD.order_id;
END $$

DELIMITER ;

DROP TRIGGER IF EXISTS after_mt_order_route_insert;
DROP TRIGGER IF EXISTS after_mt_order_route_update;
DROP TRIGGER IF EXISTS after_mt_order_route_delete;

UPDATE mt_order_route
SET 
    etd = CASE 
        -- Pattern 1: DD-MM-YYYY (The main error)
        WHEN etd REGEXP '^[0-9]{2}-[0-9]{2}-[0-9]{4}$' 
            THEN DATE_FORMAT(STR_TO_DATE(etd, '%d-%m-%Y'), '%Y-%m-%d')
        -- Pattern 2: YYYY-MM-DD HH:MM:SS (The timestamp)
        WHEN etd REGEXP '^[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}$' 
            THEN LEFT(etd, 10)
        ELSE etd 
    END,
    eta = CASE 
        -- Same logic for ETA
        WHEN eta REGEXP '^[0-9]{2}-[0-9]{2}-[0-9]{4}$' 
            THEN DATE_FORMAT(STR_TO_DATE(eta, '%d-%m-%Y'), '%Y-%m-%d')
        WHEN eta REGEXP '^[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}$' 
            THEN LEFT(eta, 10)
        ELSE eta 
    END
WHERE etd IS NOT NULL OR eta IS NOT NULL;

update mt_order_route set etd=null where length(etd)=0;
update mt_order_route set eta=null where length(eta)=0;

ALTER TABLE `mt_order_route` CHANGE `eta` `eta` DATE NULL DEFAULT NULL, CHANGE `etd` `etd` DATE NULL DEFAULT NULL;

UPDATE mt_manifest_entry_route
SET 
    etd = CASE 
        -- Pattern 1: DD-MM-YYYY (The main error)
        WHEN etd REGEXP '^[0-9]{2}-[0-9]{2}-[0-9]{4}$' 
            THEN DATE_FORMAT(STR_TO_DATE(etd, '%d-%m-%Y'), '%Y-%m-%d')
        -- Pattern 2: YYYY-MM-DD HH:MM:SS (The timestamp)
        WHEN etd REGEXP '^[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}$' 
            THEN LEFT(etd, 10)
        ELSE etd 
    END,
    eta = CASE 
        -- Same logic for ETA
        WHEN eta REGEXP '^[0-9]{2}-[0-9]{2}-[0-9]{4}$' 
            THEN DATE_FORMAT(STR_TO_DATE(eta, '%d-%m-%Y'), '%Y-%m-%d')
        WHEN eta REGEXP '^[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}$' 
            THEN LEFT(eta, 10)
        ELSE eta 
    END
WHERE etd IS NOT NULL OR eta IS NOT NULL;

update `mt_manifest_entry_route` set eta=null where length(eta)=0;
update `mt_manifest_entry_route` set etd=null where length(etd)=0;

--AIR JOB LIST
ALTER TABLE mt_order_entry 
ADD COLUMN shipment_from_country CHAR(2) AFTER shipment_from,
ADD COLUMN shipment_to_country CHAR(2) AFTER shipment_to;

DELIMITER //

CREATE TRIGGER trg_mt_order_entry_insert
BEFORE INSERT ON mt_order_entry
FOR EACH ROW
BEGIN
    -- Logic for the 'From' Country
    IF NEW.order_type LIKE 'AIR%' THEN
        SET NEW.shipment_from_country = (SELECT PORT_COUNTRY FROM airport_master WHERE port_code = NEW.shipment_from LIMIT 1);
    ELSE
        SET NEW.shipment_from_country = (SELECT PORT_COUNTRY FROM mt_port_master WHERE port_code = NEW.shipment_from LIMIT 1);
    END IF;

    -- Logic for the 'To' Country
    IF NEW.order_type LIKE 'AIR%' THEN
        SET NEW.shipment_to_country = (SELECT PORT_COUNTRY FROM airport_master WHERE port_code = NEW.shipment_to LIMIT 1);
    ELSE
        SET NEW.shipment_to_country = (SELECT PORT_COUNTRY FROM mt_port_master WHERE port_code = NEW.shipment_to LIMIT 1);
    END IF;
END; //

DELIMITER ;

DELIMITER //

CREATE TRIGGER trg_mt_order_entry_update
BEFORE UPDATE ON mt_order_entry
FOR EACH ROW
BEGIN
    -- Logic for the 'From' Country
    IF NEW.order_type LIKE 'AIR%' THEN
        SET NEW.shipment_from_country = (SELECT PORT_COUNTRY FROM airport_master WHERE port_code = NEW.shipment_from LIMIT 1);
    ELSE
        SET NEW.shipment_from_country = (SELECT PORT_COUNTRY FROM mt_port_master WHERE port_code = NEW.shipment_from LIMIT 1);
    END IF;

    -- Logic for the 'To' Country
    IF NEW.order_type LIKE 'AIR%' THEN
        SET NEW.shipment_to_country = (SELECT PORT_COUNTRY FROM airport_master WHERE port_code = NEW.shipment_to LIMIT 1);
    ELSE
        SET NEW.shipment_to_country = (SELECT PORT_COUNTRY FROM mt_port_master WHERE port_code = NEW.shipment_to LIMIT 1);
    END IF;
END; //

DELIMITER ;

-- Sync FROM country for Air Jobs
UPDATE mt_order_entry moe
JOIN airport_master am ON moe.shipment_from = am.port_code
SET moe.shipment_from_country = am.PORT_COUNTRY
WHERE moe.order_type LIKE 'AIR%';

-- Sync TO country for Air Jobs
UPDATE mt_order_entry moe
JOIN airport_master am ON moe.shipment_to = am.port_code
SET moe.shipment_to_country = am.PORT_COUNTRY
WHERE moe.order_type LIKE 'AIR%';

-- Sync TO country for Sea Jobs
UPDATE mt_order_entry moe
JOIN mt_port_master mpm_from ON moe.shipment_from = mpm_from.port_code
JOIN mt_port_master mpm_to ON moe.shipment_to = mpm_to.port_code
SET 
    moe.shipment_from_country = mpm_from.PORT_COUNTRY,
    moe.shipment_to_country = mpm_to.PORT_COUNTRY
WHERE moe.order_type LIKE 'SEA%'

ALTER TABLE `mt_port_master` CHANGE `PORT_COUNTRY` `PORT_COUNTRY` TEXT NOT NULL;

ALTER TABLE `mt_order_route_air` ADD `eline_code` TEXT NULL AFTER `eline`;
ALTER TABLE `mt_order_entry` ADD `airlines_code` TEXT NULL AFTER `airlines`;
ALTER TABLE `mt_order_entry` ADD `flight_no` TEXT NULL AFTER `airlines_code`;

update `mt_order_route_air` t1 set t1.eline_code=(select code from airlines_master where name=t1.eline); 
update `mt_order_entry` t1 set t1.airlines_code=(select code from airlines_master where name=t1.airlines);

-- 1. First, update exact matches (ignoring trailing/leading spaces)
UPDATE mt_order_entry e
JOIN airlines_master m ON TRIM(e.airlines) = TRIM(m.name)
SET e.airlines_code = m.code
WHERE e.airlines_code IS NULL;

-- 2. Update where the entry starts with the master name (e.g., "EMIRATES " matching "EMIRATES")
UPDATE mt_order_entry e
JOIN airlines_master m ON TRIM(e.airlines) LIKE CONCAT(TRIM(m.name), '%')
SET e.airlines_code = m.code
WHERE e.airlines_code IS NULL;

-- 3. Specialized cleanup for "AIRLINES" vs "AIRLINE" variations
UPDATE mt_order_entry e
JOIN airlines_master m ON 
    REPLACE(REPLACE(e.airlines, 'AIRLINES', ''), 'AIRLINE', '') LIKE 
    CONCAT('%', REPLACE(REPLACE(m.name, 'AIRLINES', ''), 'AIRLINE', ''), '%')
SET e.airlines_code = m.code
WHERE e.airlines_code IS NULL 
  AND LENGTH(TRIM(e.airlines)) > 2; -- Avoid false positives on short codes

ALTER TABLE airlines_master 
ADD CONSTRAINT UNIQUE_AIRLINE_CODE UNIQUE (code);

DELIMITER //

-- 1. Trigger for New Records (INSERT)
CREATE TRIGGER after_mt_order_route_air_insert
AFTER INSERT ON mt_order_route_air
FOR EACH ROW
BEGIN
    UPDATE mt_order_entry
    SET 
        airlines      = NEW.eline,
        airlines_code = NEW.eline_code,
        flight_no     = NEW.flight_no,
        etd    = NEW.etd,
        eta    = NEW.eta,
        atd    = NEW.atd,
        ata    = NEW.ata
    WHERE id = NEW.order_id;
END //

-- 2. Trigger for Changes (UPDATE)
CREATE TRIGGER after_mt_order_route_air_update
AFTER UPDATE ON mt_order_route_air
FOR EACH ROW
BEGIN
    UPDATE mt_order_entry
    SET 
        airlines      = NEW.eline,
        airlines_code = NEW.eline_code,
        flight_no     = NEW.flight_no,
        etd    = NEW.etd,
        eta    = NEW.eta,
        atd    = NEW.atd,
        ata    = NEW.ata
    WHERE id = NEW.order_id;
END //

DELIMITER ;

DROP TRIGGER IF EXISTS after_mt_order_route_air_insert;
DROP TRIGGER IF EXISTS after_mt_order_route_air_update;

ALTER TABLE `mt_order_entry` CHANGE `etd` `etd` DATE NULL DEFAULT NULL, CHANGE `eta` `eta` DATE NULL DEFAULT NULL, CHANGE `atd` `atd` DATE NULL DEFAULT NULL, CHANGE `ata` `ata` DATE NULL DEFAULT NULL;
ALTER TABLE `mt_order_route_air` CHANGE `eta` `eta` DATE NULL DEFAULT NULL, CHANGE `etd` `etd` DATE NULL DEFAULT NULL;

--EXPORT VOYAGE ETD ATD CLEANUP
ALTER TABLE `mt_voyage` CHANGE `eta` `eta` DATE NULL DEFAULT NULL, CHANGE `etd` `etd` DATE NULL DEFAULT NULL, CHANGE `ata` `ata` DATE NULL DEFAULT NULL, CHANGE `atd` `atd` DATE NULL DEFAULT NULL;
ALTER TABLE `mt_voyage` CHANGE `cutoff_date2` `cutoff_date2` DATE NULL DEFAULT NULL;

--FX MERGE GPL/UNICON
CREATE TABLE `mt_order_party_new` (
  `id` int(11) NOT NULL,
  `order_id` text,
  `party_type` varchar(50) DEFAULT NULL,
  `equipment_serial` text,
  `party_name` text,
  `party` text,
  `address` text,
  `telephone` text,
  `email` text,
  `poc` text,
  `address_on_bl` text,
  `status` text,
  `location_id` text,
  `company_id` text,
  `created_by` text,
  `created_date` date DEFAULT NULL,
  `ip` text,
  `ua` text,
  `ekey` text,
  `uid` text,
  `temp_code` text,
  `gstin` text,
  `address_id` text
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


ALTER TABLE `mt_order_party_new`
  ADD PRIMARY KEY (`id`),
  ADD KEY `order_id_ind` (`order_id`(15)),
  ADD KEY `ptype_party_ind` (`party_type`(25));

ALTER TABLE `mt_order_party_new`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--TX SCREENS
CREATE TABLE `mst_vehicle_type` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `code` VARCHAR(50) NOT NULL UNIQUE,
  `name` VARCHAR(100) NOT NULL,
  `status` TINYINT(1) DEFAULT 1
) ENGINE=InnoDB;

INSERT INTO `mst_vehicle_type` (`code`, `name`) VALUES
('VTYPE_20_14W', "20' / 14 Wheeler"),
('VTYPE_20_16W', "20' / 16 Wheeler"),
('VTYPE_40_DA', "40' / Double Axle"),
('VTYPE_40_TA', "40' / Triple Axle"),
('VTYPE_OTHERS', 'Others');  

CREATE TABLE `mst_vehicle_make` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `code` VARCHAR(50) NOT NULL UNIQUE,
  `name` VARCHAR(100) NOT NULL,
  `status` TINYINT(1) DEFAULT 1
) ENGINE=InnoDB;

INSERT INTO `mst_vehicle_make` (`code`, `name`) VALUES
('MAKE_AL', 'Ashok Leyland'),
('MAKE_BB', 'BharatBenz'),
('MAKE_EICHER', 'Eicher'),
('MAKE_MAHINDRA', 'Mahindra'),
('MAKE_TATA', 'TATA'),
('MAKE_OTHERS', 'Others');

CREATE TABLE `mst_payload` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `code` VARCHAR(50) NOT NULL UNIQUE,
  `name` VARCHAR(100) NOT NULL,
  `status` TINYINT(1) DEFAULT 1
) ENGINE=InnoDB;

INSERT INTO `mst_payload` (`code`, `name`) VALUES
('PAYLOAD_20T', '20 Metric Tons'),
('PAYLOAD_25T', '25 Metric Tons'),
('PAYLOAD_30T', '30 Metric Tons'),
('PAYLOAD_35T', '35 Metric Tons'),
('PAYLOAD_40T', '40 Metric Tons'),
('PAYLOAD_OTHERS', 'Others');

CREATE TABLE `mst_body_type` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `code` VARCHAR(50) NOT NULL UNIQUE,
  `name` VARCHAR(100) NOT NULL,
  `status` TINYINT(1) DEFAULT 1
) ENGINE=InnoDB;

INSERT INTO `mst_body_type` (`code`, `name`) VALUES
('BODY_OPEN', 'Open Platform'),
('BODY_PLATFORM', 'Platform Trailer'),
('BODY_SKELTON', 'Skelton Trailer'),
('BODY_OTHERS', 'Others');

ALTER TABLE `mt_order_route` ADD `modified_by` TEXT NULL, ADD `modified_date` TIMESTAMP NULL;
ALTER TABLE `mt_manifest_entry_route` ADD `modified_by` TEXT NULL;

ALTER TABLE `truck_master` ADD `ownership_type` TEXT NULL AFTER `status`;

ALTER TABLE `company_master` ADD `pan` TEXT NULL AFTER `bl_pdf_description_char_limit`, ADD `cin` TEXT NULL AFTER `pan`;

ALTER TABLE `driver_list` ADD `years_of_exp` TEXT NULL AFTER `leaving_date`;
ALTER TABLE `driver_list` ADD `license_type` TEXT NULL AFTER `license_no`;

UPDATE driver_list
SET 
    license_validity = CASE 
        -- Pattern 1: ISO 8601 Format (2026-01-01T18:30:00.000Z)
        WHEN license_validity REGEXP '^[0-9]{4}-[0-9]{2}-[0-9]{2}T[0-9]{2}:[0-9]{2}:[0-9]{2}' 
            THEN LEFT(license_validity, 10)
            
        -- Pattern 2: DD-MM-YYYY (Common manual entry error)
        WHEN license_validity REGEXP '^[0-9]{2}-[0-9]{2}-[0-9]{4}$' 
            THEN DATE_FORMAT(STR_TO_DATE(license_validity, '%d-%m-%Y'), '%Y-%m-%d')
            
        -- Pattern 3: YYYY-MM-DD HH:MM:SS (Standard Timestamp)
        WHEN license_validity REGEXP '^[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}$' 
            THEN LEFT(license_validity, 10)
            
        ELSE license_validity 
    END
WHERE license_validity IS NOT NULL;

ALTER TABLE `driver_list` CHANGE `license_validity` `license_validity` DATE NULL DEFAULT NULL;

ALTER TABLE `mt_order_entry` ADD `customer_reference` TEXT NULL AFTER `art_code`;
ALTER TABLE `mt_order_entry` ADD `do_validity` DATE NULL AFTER `customer_reference`;
ALTER TABLE `mt_order_entry` ADD `quote_no` TEXT NULL AFTER `do_validity`;
ALTER TABLE `mt_order_container_detail` ADD `lr_number` TEXT NULL AFTER `shipment_type`, ADD `eway_bill_number` TEXT NULL AFTER `lr_number`;

ALTER TABLE `mt_order_entry` ADD from_location TEXT NULL;
ALTER TABLE `mt_order_entry` ADD from_lat TEXT NULL;
ALTER TABLE `mt_order_entry` ADD from_lng TEXT NULL;
ALTER TABLE `mt_order_entry` ADD to_location TEXT NULL;
ALTER TABLE `mt_order_entry` ADD to_lat TEXT NULL;
ALTER TABLE `mt_order_entry` ADD to_lng TEXT NULL;
ALTER TABLE `mt_order_entry` ADD from_pincode TEXT NULL;
ALTER TABLE `mt_order_entry` ADD to_pincode TEXT NULL;
ALTER TABLE `mt_order_entry` ADD estimated_distance TEXT NULL;
ALTER TABLE `mt_order_entry` ADD ff_order_no TEXT NULL;

INSERT INTO `page_list` (`cat`, `subcat`, `module_name`, `module_url`, `page_url`, `page_name`, `page_icon`, `menu_icon`, `created_date`, `id`,product_type) VALUES
(15, '0.10', 'Booking', 'booking-tx', 'order-list', 'Transport Job', 'assets/newicons/truck.svg', NULL, '2019-12-03 17:43:00', null,'TX');

INSERT INTO `access_rights_master` 
( `pid`, `add_edit`, `view`, `approve_cancel`, `role`, `cancel`, `url`, `ptype`, `editr`, `deleter`, `sub_cat`) 
select  id, 'true', 'true', 'true', 'ADMIN', 'true', NULL, NULL, 'true', 'true', NULL 
from page_list where page_name in ('Transport Job');

ALTER TABLE mt_order_entry ADD COLUMN no_of_trips INT DEFAULT 0;
ALTER TABLE mt_order_entry ADD COLUMN no_of_containers INT DEFAULT 0;

ALTER TABLE `trip_list` ADD ff_order_no TEXT NULL;

