ALTER TABLE tblestimates MODIFY COLUMN type ENUM('detail','normal','quickbid_estimate','hybrid') NOT NULL DEFAULT 'normal';
ALTER TABLE tblestimates ADD is_grouping_on TINYINT(1) NOT NULL DEFAULT 0 AFTER type;
ALTER TABLE tblestimates ADD labor_markup DOUBLE(15,2) NOT NULL DEFAULT 0.00 COMMENT 'In Percent for Tax Type Global' AFTER global_tax_for;
ALTER TABLE tblestimates ADD supply_markup DOUBLE(15,2) NOT NULL DEFAULT 0.00 COMMENT 'In Percent for Tax Type Global' AFTER labor_markup;
ALTER TABLE tblmodule_taxes MODIFY COLUMN rel_type ENUM('estimate','invoice','detail_estimate','quickbid_estimate','change_order','hybrid_estimate','hybrid_invoice','hybrid_change_order') NOT NULL;
ALTER TABLE tblestimates ADD default_tax_on enum('on_supply','on_labour','on_both','job_to_job') DEFAULT 'on_both' COMMENT 'default_tax_on setting for estimate' AFTER is_grouping_on;

CREATE TABLE `tblestimate_groups` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `estimate_id` bigint(20) unsigned NOT NULL,  
  `name` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `datecreated` datetime NOT NULL,
  `item_order` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tenant_id` (`tenant_id`),
  KEY `estimate_id` (`estimate_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `tblestimate_tasks` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `estimate_id` bigint(20) unsigned NOT NULL,
  `group_id` bigint(20) DEFAULT NULL,  
  `title` varchar(200) COLLATE utf8_general_ci DEFAULT NULL,
  `description` text COLLATE utf8_general_ci DEFAULT NULL,
  `comment` text COLLATE utf8_general_ci DEFAULT NULL,
  `quantity` decimal(15,2) NOT NULL DEFAULT 0.00 COMMENT 'Quantity',
  `rate` decimal(15,2) NOT NULL DEFAULT 0.00 COMMENT 'Rate',
  `unit` varchar(191) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'hour',
  `total_labor_cost` decimal(15,2) NOT NULL DEFAULT 0.00 COMMENT 'With Markup',
  `total_supply_cost` decimal(15,2) NOT NULL DEFAULT 0.00 COMMENT 'With Markup',
  `tax_on` enum('on_supply','on_labour','on_both') COLLATE utf8_unicode_ci DEFAULT NULL,
  `total_tax` decimal(15,2) NOT NULL DEFAULT 0.00 COMMENT 'With TAX',
  `total` decimal(15,2) NOT NULL DEFAULT 0.00 COMMENT 'With TAX',
  `supply_markup` double(15,2) NOT NULL DEFAULT 0.00 COMMENT 'In Percent ',
  `labor_markup` double(15,2) NOT NULL DEFAULT 0.00 COMMENT 'In Percent',
  `datecreated` datetime NOT NULL,
  `item_order` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tenant_id` (`tenant_id`),
  KEY `estimate_id` (`estimate_id`),
  KEY `group_id` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;


CREATE TABLE `tblestimate_supplies` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` BIGINT(20) UNSIGNED NOT NULL,
  `estimate_id` BIGINT(20) UNSIGNED NOT NULL,
  `group_id` BIGINT(20) DEFAULT NULL, 
  `task_id` BIGINT(20) DEFAULT NULL,    
  `cp_supply_id` BIGINT(20) UNSIGNED DEFAULT NULL,
  `my_supply_id` BIGINT(20) UNSIGNED DEFAULT NULL,
  `type` ENUM('my_supply','cp_supply') COLLATE utf8_unicode_ci DEFAULT NULL,
  `name` VARCHAR(191) COLLATE utf8_unicode_ci NOT NULL,
  `source_product_sku` VARCHAR(500) COLLATE utf8_unicode_ci DEFAULT NULL,
  `image` VARCHAR(191) COLLATE utf8_unicode_ci DEFAULT NULL,
  `image_url` VARCHAR(191) COLLATE utf8_unicode_ci DEFAULT NULL,
  `product_url` VARCHAR(800) COLLATE utf8_unicode_ci DEFAULT NULL,
  `category_id` BIGINT(20) UNSIGNED DEFAULT NULL,
  `category_name` VARCHAR(191) COLLATE utf8_unicode_ci DEFAULT NULL,
  `supplier_id` BIGINT(20) UNSIGNED DEFAULT NULL,
  `supplier_name` VARCHAR(191) COLLATE utf8_unicode_ci DEFAULT NULL,
  `supplier_image` VARCHAR(191) COLLATE utf8_unicode_ci DEFAULT NULL,
  `tag` VARCHAR(191) COLLATE utf8_unicode_ci DEFAULT NULL,
  `price` DECIMAL(15,2) DEFAULT 0.00,
  `quantity` BIGINT(20) UNSIGNED NOT NULL,
  `markup_total` DECIMAL(15,2) DEFAULT 0.00 ,
  `supply_total_price` DECIMAL(15,2) NOT NULL,
  `dateadded` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP(),
  `item_order` BIGINT(20) UNSIGNED NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  KEY `tenant_id` (`tenant_id`),
  KEY `estimate_id` (`estimate_id`),
  KEY `group_id` (`group_id`),
  KEY `task_id` (`task_id`),
  KEY `cp_supply_id` (`cp_supply_id`),
  KEY `my_supply_id` (`my_supply_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;

CREATE TABLE `tblestimate_task_files` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` BIGINT(20) UNSIGNED NOT NULL,
  `estimate_id` BIGINT(20) UNSIGNED NOT NULL,
  `task_id` BIGINT(20) NOT NULL,    
  `file_name` VARCHAR(191) COLLATE utf8_unicode_ci NOT NULL,
  `filetype` VARCHAR(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `task_file_type` ENUM('pre_photos','post_photos') COLLATE utf8_unicode_ci DEFAULT NULL,
  `visible_to_customer` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
  `attachment_key` VARCHAR(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  `external` VARCHAR(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `external_link` TEXT COLLATE utf8_unicode_ci DEFAULT NULL,
  `thumbnail_link` TEXT COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'For external usage',
  `original_file_name` VARCHAR(191) COLLATE utf8_unicode_ci DEFAULT NULL,
  `dateadded` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `tenant_id` (`tenant_id`),
  KEY `estimate_id` (`estimate_id`),
  KEY `task_id` (`task_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `tblapad_options`
  (`type`, `name`, `value`, `autoload`)
  VALUES ('hybrid_estimates', 'allowed_file_extention', '{"image":["jpeg","jpg","png","gif","webp"],"video":[],"audio":[],"document":[]}', 1),
  ('hybrid_estimates', 'allowed_file_size', '16000', 1),
  ('hybrid_estimates', 'total_file_count', '10', 1),
  ('hybrid_estimates', 'allowed_file_count', '10', 1);

INSERT INTO tbloptions_static (name, value, autoload) VALUES('default_estimate_labor_rate', '75', 1);
INSERT INTO tbloptions_static (name, value, autoload) VALUES('default_estimate_supply_markup', '30', 1);
INSERT INTO tbloptions_static (name, value, autoload) VALUES('default_estimate_labor_markup', '30', 1);
INSERT INTO tbloptions_static (name, value, autoload) VALUES('default_estimate_tax_on', 'on_both', 1);

INSERT INTO tbloptions (tenant_id, name, value, autoload)
SELECT t.id,s.name,s.value,s.autoload
FROM tbltenants t
JOIN tbloptions_static s ON 1 = 1 AND s.name in ('default_estimate_labor_rate','default_estimate_supply_markup','default_estimate_labor_markup','default_estimate_tax_on');

-- Invoice Related Queries -- 
ALTER TABLE tblinvoices ADD is_grouping_on TINYINT(1) NOT NULL DEFAULT 0 AFTER title;
ALTER TABLE tblinvoices ADD global_tax_for enum('on_supply','on_labour','on_both','') DEFAULT NULL COMMENT 'If tax_type is global than use this flag' AFTER tax_type;
ALTER TABLE tblinvoices ADD labor_markup DOUBLE(15,2) NOT NULL DEFAULT 0.00 COMMENT 'In Percent for Tax Type Global' AFTER global_tax_for;
ALTER TABLE tblinvoices ADD supply_markup DOUBLE(15,2) NOT NULL DEFAULT 0.00 COMMENT 'In Percent for Tax Type Global' AFTER labor_markup;
ALTER TABLE tblinvoices ADD default_tax_on enum('on_supply','on_labour','on_both','job_to_job') DEFAULT 'on_both' COMMENT 'default_tax_on setting for invoice' AFTER is_grouping_on;
ALTER TABLE tblinvoices ADD final_labour_charges decimal(15,2) NOT NULL DEFAULT 0.00;
ALTER TABLE tblinvoices ADD final_supply_charges decimal(15,2) NOT NULL DEFAULT 0.00;

CREATE TABLE `tblinvoice_groups` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `invoice_id` bigint(20) unsigned NOT NULL,  
  `name` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `datecreated` datetime NOT NULL,
  `item_order` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tenant_id` (`tenant_id`),
  KEY `invoice_id` (`invoice_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `tblinvoice_tasks` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `invoice_id` bigint(20) unsigned NOT NULL,
  `group_id` bigint(20) DEFAULT NULL,  
  `title` varchar(200) COLLATE utf8_general_ci DEFAULT NULL,
  `description` text COLLATE utf8_general_ci DEFAULT NULL,
  `comment` text COLLATE utf8_general_ci DEFAULT NULL,
  `quantity` decimal(15,2) NOT NULL DEFAULT 0.00 COMMENT 'Quantity',
  `rate` decimal(15,2) NOT NULL DEFAULT 0.00 COMMENT 'Rate',
  `unit` varchar(191) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'hour',
  `total_labor_cost` decimal(15,2) NOT NULL DEFAULT 0.00 COMMENT 'With Markup',
  `total_supply_cost` decimal(15,2) NOT NULL DEFAULT 0.00 COMMENT 'With Markup',
  `tax_on` enum('on_supply','on_labour','on_both') COLLATE utf8_unicode_ci DEFAULT NULL,
  `total_tax` decimal(15,2) NOT NULL DEFAULT 0.00 COMMENT 'With TAX',
  `total` decimal(15,2) NOT NULL DEFAULT 0.00 COMMENT 'With TAX',
  `supply_markup` double(15,2) NOT NULL DEFAULT 0.00 COMMENT 'In Percent ',
  `labor_markup` double(15,2) NOT NULL DEFAULT 0.00 COMMENT 'In Percent',
  `datecreated` datetime NOT NULL,
  `item_order` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tenant_id` (`tenant_id`),
  KEY `invoice_id` (`invoice_id`),
  KEY `group_id` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;


CREATE TABLE `tblinvoice_supplies` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` BIGINT(20) UNSIGNED NOT NULL,
  `invoice_id` BIGINT(20) UNSIGNED NOT NULL,
  `group_id` BIGINT(20) DEFAULT NULL, 
  `task_id` BIGINT(20) DEFAULT NULL,    
  `cp_supply_id` BIGINT(20) UNSIGNED DEFAULT NULL,
  `my_supply_id` BIGINT(20) UNSIGNED DEFAULT NULL,
  `type` ENUM('my_supply','cp_supply') COLLATE utf8_unicode_ci DEFAULT NULL,
  `name` VARCHAR(191) COLLATE utf8_unicode_ci NOT NULL,
  `source_product_sku` VARCHAR(500) COLLATE utf8_unicode_ci DEFAULT NULL,
  `image` VARCHAR(191) COLLATE utf8_unicode_ci DEFAULT NULL,
  `image_url` VARCHAR(191) COLLATE utf8_unicode_ci DEFAULT NULL,
  `product_url` VARCHAR(800) COLLATE utf8_unicode_ci DEFAULT NULL,
  `category_id` BIGINT(20) UNSIGNED DEFAULT NULL,
  `category_name` VARCHAR(191) COLLATE utf8_unicode_ci DEFAULT NULL,
  `supplier_id` BIGINT(20) UNSIGNED DEFAULT NULL,
  `supplier_name` VARCHAR(191) COLLATE utf8_unicode_ci DEFAULT NULL,
  `supplier_image` VARCHAR(191) COLLATE utf8_unicode_ci DEFAULT NULL,
  `tag` VARCHAR(191) COLLATE utf8_unicode_ci DEFAULT NULL,
  `price` DECIMAL(15,2) DEFAULT 0.00,
  `quantity` BIGINT(20) UNSIGNED NOT NULL,
  `markup_total` DECIMAL(15,2) DEFAULT 0.00 ,
  `supply_total_price` DECIMAL(15,2) NOT NULL,
  `dateadded` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP(),
  `item_order` BIGINT(20) UNSIGNED NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  KEY `tenant_id` (`tenant_id`),
  KEY `invoice_id` (`invoice_id`),
  KEY `group_id` (`group_id`),
  KEY `task_id` (`task_id`),
  KEY `cp_supply_id` (`cp_supply_id`),
  KEY `my_supply_id` (`my_supply_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;

CREATE TABLE `tblinvoice_task_files` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` BIGINT(20) UNSIGNED NOT NULL,
  `invoice_id` BIGINT(20) UNSIGNED NOT NULL,
  `task_id` BIGINT(20) NOT NULL,    
  `file_name` VARCHAR(191) COLLATE utf8_unicode_ci NOT NULL,
  `filetype` VARCHAR(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `task_file_type` ENUM('pre_photos','post_photos') COLLATE utf8_unicode_ci DEFAULT NULL,
  `visible_to_customer` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
  `attachment_key` VARCHAR(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  `external` VARCHAR(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `external_link` TEXT COLLATE utf8_unicode_ci DEFAULT NULL,
  `thumbnail_link` TEXT COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'For external usage',
  `original_file_name` VARCHAR(191) COLLATE utf8_unicode_ci DEFAULT NULL,
  `dateadded` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `tenant_id` (`tenant_id`),
  KEY `invoice_id` (`invoice_id`),
  KEY `task_id` (`task_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `tblapad_options`
  (`type`, `name`, `value`, `autoload`)
  VALUES ('hybrid_invoices', 'allowed_file_extention', '{"image":["jpeg","jpg","png","gif","webp"],"video":[],"audio":[],"document":[]}', 1),
  ('hybrid_invoices', 'allowed_file_size', '16000', 1),
  ('hybrid_invoices', 'total_file_count', '10', 1),
  ('hybrid_invoices', 'allowed_file_count', '10', 1);

INSERT INTO tbloptions_static (name, value, autoload) VALUES('default_invoice_labor_rate', '75', 1);
INSERT INTO tbloptions_static (name, value, autoload) VALUES('default_invoice_supply_markup', '30', 1);
INSERT INTO tbloptions_static (name, value, autoload) VALUES('default_invoice_labor_markup', '30', 1);
INSERT INTO tbloptions_static (name, value, autoload) VALUES('default_invoice_tax_on', 'on_both', 1);

INSERT INTO tbloptions (tenant_id, name, value, autoload)
SELECT t.id,s.name,s.value,s.autoload
FROM tbltenants t
JOIN tbloptions_static s ON 1 = 1 AND s.name in ('default_invoice_labor_rate','default_invoice_supply_markup','default_invoice_labor_markup','default_invoice_tax_on');

-- Change Order Related Queries -- 
ALTER TABLE tblchange_orders ADD is_grouping_on TINYINT(1) NOT NULL DEFAULT 0 AFTER title;
ALTER TABLE tblchange_orders ADD global_tax_for enum('on_supply','on_labour','on_both','') DEFAULT NULL COMMENT 'If tax_type is global than use this flag' AFTER tax_type;
ALTER TABLE tblchange_orders ADD labor_markup DOUBLE(15,2) NOT NULL DEFAULT 0.00 COMMENT 'In Percent for Tax Type Global' AFTER global_tax_for;
ALTER TABLE tblchange_orders ADD supply_markup DOUBLE(15,2) NOT NULL DEFAULT 0.00 COMMENT 'In Percent for Tax Type Global' AFTER labor_markup;
ALTER TABLE tblchange_orders ADD default_tax_on enum('on_supply','on_labour','on_both','job_to_job') DEFAULT 'on_both' COMMENT 'default_tax_on setting for change_order' AFTER is_grouping_on;
ALTER TABLE tblchange_orders ADD final_labour_charges decimal(15,2) NOT NULL DEFAULT 0.00;
ALTER TABLE tblchange_orders ADD final_supply_charges decimal(15,2) NOT NULL DEFAULT 0.00;

CREATE TABLE `tblchange_order_groups` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `change_order_id` bigint(20) unsigned NOT NULL,  
  `name` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `datecreated` datetime NOT NULL,
  `item_order` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tenant_id` (`tenant_id`),
  KEY `change_order_id` (`change_order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `tblchange_order_tasks` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `change_order_id` bigint(20) unsigned NOT NULL,
  `group_id` bigint(20) DEFAULT NULL,  
  `title` varchar(200) COLLATE utf8_general_ci DEFAULT NULL,
  `description` text COLLATE utf8_general_ci DEFAULT NULL,
  `comment` text COLLATE utf8_general_ci DEFAULT NULL,
  `quantity` decimal(15,2) NOT NULL DEFAULT 0.00 COMMENT 'Quantity',
  `rate` decimal(15,2) NOT NULL DEFAULT 0.00 COMMENT 'Rate',
  `unit` varchar(191) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'hour',
  `total_labor_cost` decimal(15,2) NOT NULL DEFAULT 0.00 COMMENT 'With Markup',
  `total_supply_cost` decimal(15,2) NOT NULL DEFAULT 0.00 COMMENT 'With Markup',
  `tax_on` enum('on_supply','on_labour','on_both') COLLATE utf8_unicode_ci DEFAULT NULL,
  `total_tax` decimal(15,2) NOT NULL DEFAULT 0.00 COMMENT 'With TAX',
  `total` decimal(15,2) NOT NULL DEFAULT 0.00 COMMENT 'With TAX',
  `supply_markup` double(15,2) NOT NULL DEFAULT 0.00 COMMENT 'In Percent ',
  `labor_markup` double(15,2) NOT NULL DEFAULT 0.00 COMMENT 'In Percent',
  `datecreated` datetime NOT NULL,
  `item_order` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tenant_id` (`tenant_id`),
  KEY `change_order_id` (`change_order_id`),
  KEY `group_id` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;


CREATE TABLE `tblchange_order_supplies` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` BIGINT(20) UNSIGNED NOT NULL,
  `change_order_id` BIGINT(20) UNSIGNED NOT NULL,
  `group_id` BIGINT(20) DEFAULT NULL, 
  `task_id` BIGINT(20) DEFAULT NULL,    
  `cp_supply_id` BIGINT(20) UNSIGNED DEFAULT NULL,
  `my_supply_id` BIGINT(20) UNSIGNED DEFAULT NULL,
  `type` ENUM('my_supply','cp_supply') COLLATE utf8_unicode_ci DEFAULT NULL,
  `name` VARCHAR(191) COLLATE utf8_unicode_ci NOT NULL,
  `source_product_sku` VARCHAR(500) COLLATE utf8_unicode_ci DEFAULT NULL,
  `image` VARCHAR(191) COLLATE utf8_unicode_ci DEFAULT NULL,
  `image_url` VARCHAR(191) COLLATE utf8_unicode_ci DEFAULT NULL,
  `product_url` VARCHAR(800) COLLATE utf8_unicode_ci DEFAULT NULL,
  `category_id` BIGINT(20) UNSIGNED DEFAULT NULL,
  `category_name` VARCHAR(191) COLLATE utf8_unicode_ci DEFAULT NULL,
  `supplier_id` BIGINT(20) UNSIGNED DEFAULT NULL,
  `supplier_name` VARCHAR(191) COLLATE utf8_unicode_ci DEFAULT NULL,
  `supplier_image` VARCHAR(191) COLLATE utf8_unicode_ci DEFAULT NULL,
  `tag` VARCHAR(191) COLLATE utf8_unicode_ci DEFAULT NULL,
  `price` DECIMAL(15,2) DEFAULT 0.00,
  `quantity` BIGINT(20) UNSIGNED NOT NULL,
  `markup_total` DECIMAL(15,2) DEFAULT 0.00 ,
  `supply_total_price` DECIMAL(15,2) NOT NULL,
  `dateadded` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP(),
  `item_order` BIGINT(20) UNSIGNED NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  KEY `tenant_id` (`tenant_id`),
  KEY `change_order_id` (`change_order_id`),
  KEY `group_id` (`group_id`),
  KEY `task_id` (`task_id`),
  KEY `cp_supply_id` (`cp_supply_id`),
  KEY `my_supply_id` (`my_supply_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;

CREATE TABLE `tblchange_order_task_files` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` BIGINT(20) UNSIGNED NOT NULL,
  `change_order_id` BIGINT(20) UNSIGNED NOT NULL,
  `task_id` BIGINT(20) NOT NULL,    
  `file_name` VARCHAR(191) COLLATE utf8_unicode_ci NOT NULL,
  `filetype` VARCHAR(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `task_file_type` ENUM('pre_photos','post_photos') COLLATE utf8_unicode_ci DEFAULT NULL,
  `visible_to_customer` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
  `attachment_key` VARCHAR(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  `external` VARCHAR(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `external_link` TEXT COLLATE utf8_unicode_ci DEFAULT NULL,
  `thumbnail_link` TEXT COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'For external usage',
  `original_file_name` VARCHAR(191) COLLATE utf8_unicode_ci DEFAULT NULL,
  `dateadded` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `tenant_id` (`tenant_id`),
  KEY `change_order_id` (`change_order_id`),
  KEY `task_id` (`task_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `tblapad_options`
  (`type`, `name`, `value`, `autoload`)
  VALUES ('hybrid_change_orders', 'allowed_file_extention', '{"image":["jpeg","jpg","png","gif","webp"],"video":[],"audio":[],"document":[]}', 1),
  ('hybrid_change_orders', 'allowed_file_size', '16000', 1),
  ('hybrid_change_orders', 'total_file_count', '10', 1),
  ('hybrid_change_orders', 'allowed_file_count', '10', 1);

ALTER TABLE tblestimate_supplies ADD product_upc varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL AFTER source_product_sku;
ALTER TABLE tblinvoice_supplies  ADD product_upc varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL AFTER source_product_sku;
ALTER TABLE tblchange_order_supplies  ADD product_upc varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL AFTER source_product_sku;

-- Queries Related To Local Pricing 
INSERT INTO `tblplan_features`
  VALUES (NULL, 'Home Depot Local Pricing Credits Limit', 'local_pricing_credits_limit', 'Home Depot Local Pricing Credits Limit', 'restrict', 1, 999, CURRENT_TIMESTAMP);

SET @plan_features_id = LAST_INSERT_ID();  

INSERT INTO `tblplan_features_value`
VALUES (NULL, 9, @plan_features_id, 10, '{"outside_message":"You have limit of 10 Credits on your plan. If you want to add more credits please upgrade.",
  "inside_message":"You have limit of 10 Credits on your plan, we have used {!current_count!} credits. If you want to add more credits please upgrade.",
  "tag_color":"#eb0e0e","tag_title":"UPGRADE","link":"https:\/\/my.contractorplus.app\/","count":"10"}', CURRENT_TIMESTAMP),
(NULL, 10, @plan_features_id, 25, '{"outside_message":"You have limit of 25 Credits on your plan. If you want to add more credits please upgrade.",
  "inside_message":"You have limit of 25 Credits on your plan, we have used {!current_count!} credits. If you want to add more credits please upgrade.",
  "tag_color":"#eb0e0e","tag_title":"UPGRADE","link":"https:\/\/my.contractorplus.app\/","count":"25"}', CURRENT_TIMESTAMP),
(NULL, 11, @plan_features_id, 25, '{"outside_message":"You have limit of 25 Credits on your plan. If you want to add more credits please upgrade.",
  "inside_message":"You have limit of 25 Credits on your plan, we have used {!current_count!} credits. If you want to add more credits please upgrade.",
  "tag_color":"#eb0e0e","tag_title":"UPGRADE","link":"https:\/\/my.contractorplus.app\/","count":"25"}', CURRENT_TIMESTAMP),
(NULL, 12, @plan_features_id, 250, '{"outside_message":"You have limit of 250 Credits on your plan. If you want to add more credits please upgrade.",
  "inside_message":"You have limit of 250 Credits on your plan, we have used {!current_count!} credits. If you want to add more credits please upgrade.",
  "tag_color":"#eb0e0e","tag_title":"UPGRADE","link":"https:\/\/my.contractorplus.app\/","count":"250"}', CURRENT_TIMESTAMP),
(NULL, 13, @plan_features_id, 10, '{"outside_message":"You have limit of 10 Credits on your plan. If you want to add more credits please upgrade.",
  "inside_message":"You have limit of 10 Credits on your plan, we have used {!current_count!} credits. If you want to add more credits please upgrade.",
  "tag_color":"#eb0e0e","tag_title":"UPGRADE","link":"https:\/\/my.contractorplus.app\/","count":"10"}', CURRENT_TIMESTAMP),
(NULL, 14, @plan_features_id, 25, '{"outside_message":"You have limit of 25 Credits on your plan. If you want to add more credits please upgrade.",
  "inside_message":"You have limit of 25 Credits on your plan, we have used {!current_count!} credits. If you want to add more credits please upgrade.",
  "tag_color":"#eb0e0e","tag_title":"UPGRADE","link":"https:\/\/my.contractorplus.app\/","count":"25"}', CURRENT_TIMESTAMP),
(NULL, 15, @plan_features_id, 250, '{"outside_message":"You have limit of 250 Credits on your plan. If you want to add more credits please upgrade.",
  "inside_message":"You have limit of 250 Credits on your plan, we have used {!current_count!} credits. If you want to add more credits please upgrade.",
  "tag_color":"#eb0e0e","tag_title":"UPGRADE","link":"https:\/\/my.contractorplus.app\/","count":"250"}', CURRENT_TIMESTAMP);

INSERT INTO tbloptions_static (name, value, autoload) VALUES('local_pricing_credits_left', '10', 1);
INSERT INTO tbloptions (tenant_id, NAME, VALUE, autoload)
SELECT t.id,'local_pricing_credits_left',
(CASE WHEN (plans_id = 12 OR plans_id = 15) THEN '250' 
WHEN (plans_id = 10 OR plans_id = 11 OR plans_id = 14) THEN '25' 
ELSE '10' END ) AS credit_value ,1
FROM tbltenants t
LEFT JOIN tbltenant_plan_information tpi ON t.id = tpi.tenant_id
WHERE tpi.is_plan_expired = 0 ;

ALTER TABLE tblestimate_supplies MODIFY COLUMN type ENUM('my_supply','cp_supply','local_pricing','my_local_supply') NOT NULL;
ALTER TABLE tblinvoice_supplies MODIFY COLUMN type ENUM('my_supply','cp_supply','local_pricing','my_local_supply') NOT NULL;
ALTER TABLE tblchange_order_supplies MODIFY COLUMN type ENUM('my_supply','cp_supply','local_pricing','my_local_supply') NOT NULL;
ALTER TABLE tblshopping_list_items MODIFY COLUMN type ENUM('my_supply','cp_supply','local_pricing','my_local_supply') NOT NULL;


INSERT INTO tblemailtemplates_static (type,slug,language,name,subject,message,fromname,fromemail,plaintext,active,`order`)
VALUES('estimate','hybrid-estimate-send-to-client','english','Send Hybrid Estimate to Customer','New Estimate for {estimate_client_property_street_1} {estimate_client_property_street_2}','<span style="font-size: 12pt;">Hi {contact_firstname},</span><br/><br/><span style="font-size: 12pt;">Attached is a new estimate for {estimate_client_address_street}. The grand total of your estimate is {estimate_total}. You can see the details on the attached PDF file or via the link below.</span><br/><br/><span style="font-size: 12pt;">View Estimate: <a href="{estimate_link}">{estimate_number}-{estimate_client_property_street_1}{estimate_client_property_street_2}.</a></span><br/><br/><span style="font-size: 12pt;">If you have any questions, let me know!</span><br/><br/><span style="font-size: 12pt;">{email_signature}</span>','{companyname}','',0,1,0);


INSERT INTO tblemailtemplates_static (type,slug,language,name,subject,message,fromname,fromemail,plaintext,active,`order`)
VALUES('estimate','hybrid-estimate-already-send','english','Hybrid Estimate Already Sent to Customer','New Estimate for {estimate_client_property_street_1} {estimate_client_property_street_2}','<span style="font-size: 12pt;">Hi {contact_firstname},</span><br/><br/><span style="font-size: 12pt;">Attached is a new estimate for {estimate_client_address_street}. The grand total of your estimate is {estimate_total}. You can see the details on the attached PDF file or via the link below.</span><br/><br/><span style="font-size: 12pt;">View Estimate: <a href="{estimate_link}">{estimate_number}-{estimate_client_property_street_1}{estimate_client_property_street_2}.</a></span><br/><br/><span style="font-size: 12pt;">If you have any questions, let me know!</span><br/><br/><span style="font-size: 12pt;">{email_signature}</span>','{companyname}','',0,1,0);

INSERT INTO tblemailtemplates (tenant_id, type, slug, language, name, subject,message,fromname,fromemail,plaintext,active,`order`,is_tenant_email_config_allowed,is_tenant )
SELECT t.id,s.type,s.slug,s.language,s.name,s.subject,s.message,s.fromname,s.fromemail,s.plaintext,s.active,s.order,0,0
FROM tbltenants t
JOIN tblemailtemplates_static s ON 1 = 1 AND s.slug ='hybrid-estimate-send-to-client';

INSERT INTO tblemailtemplates (tenant_id, type, slug, language, name, subject,message,fromname,fromemail,plaintext,active,`order`,is_tenant_email_config_allowed,is_tenant )
SELECT t.id,s.type,s.slug,s.language,s.name,s.subject,s.message,s.fromname,s.fromemail,s.plaintext,s.active,s.order,0,0
FROM tbltenants t
JOIN tblemailtemplates_static s ON 1 = 1 AND s.slug ='hybrid-estimate-already-send';

ALTER TABLE tblinvoices ADD type ENUM('normal','hybrid') NOT NULL DEFAULT 'normal' AFTER title;

ALTER TABLE tblchange_orders ADD type ENUM('normal','hybrid') NOT NULL DEFAULT 'normal' AFTER title;

-- Invoice Mail template --

INSERT INTO tblemailtemplates_static (type,slug,language,name,subject,message,fromname,fromemail,plaintext,active,`order`)
VALUES('estimate','hybrid-invoice-send-to-client','english','Send Hybrid Invoice to Client','New Invoice for {invoice_client_property_street_1} {invoice_client_property_street_2}','<span style="font-size: 12pt;">Hi {contact_firstname},</span><br/><br/><span style="font-size: 12pt;">Attached is new invoice for {invoice_client_property_street_1} {invoice_client_property_street_2}</span><br/><br/><span style="font-size: 12pt;">You can view the invoice and payment options at this link: <a href="{invoice_link}">{invoice_number}-{invoice_client_property_street_1} {invoice_client_property_street_2}.</a></span><br/><br/><span style="font-size: 12pt;">If you have any questions, I’m here to help!</span><br/><br/><span style="font-size: 12pt;">{email_signature}</span>','{companyname}','',0,1,0);

INSERT INTO tblemailtemplates_static (type,slug,language,name,subject,message,fromname,fromemail,plaintext,active,`order`)
VALUES('estimate','hybrid-invoice-already-send','english','Hybrid Invoice Already Sent to Client','New Invoice for {invoice_client_property_street_1} {invoice_client_property_street_2}','<span style="font-size: 12pt;">Hi {contact_firstname},</span><br/><br/><span style="font-size: 12pt;">Attached is new invoice for {invoice_client_property_street_1} {invoice_client_property_street_2}</span><br/><br/><span style="font-size: 12pt;">You can view the invoice and payment options at this link: <a href="{invoice_link}">{invoice_number}-{invoice_client_property_street_1} {invoice_client_property_street_2}.</a></span><br/><br/><span style="font-size: 12pt;">If you have any questions, I’m here to help!</span><br/><br/><span style="font-size: 12pt;">{email_signature}</span>','{companyname}','',0,1,0);

INSERT INTO tblemailtemplates (tenant_id, type, slug, language, name, subject,message,fromname,fromemail,plaintext,active,`order`,is_tenant_email_config_allowed,is_tenant )
SELECT t.id,s.type,s.slug,s.language,s.name,s.subject,s.message,s.fromname,s.fromemail,s.plaintext,s.active,s.order,0,0
FROM tbltenants t
JOIN tblemailtemplates_static s ON 1 = 1 AND s.slug ='hybrid-invoice-send-to-client';

INSERT INTO tblemailtemplates (tenant_id, type, slug, language, name, subject,message,fromname,fromemail,plaintext,active,`order`,is_tenant_email_config_allowed,is_tenant )
SELECT t.id,s.type,s.slug,s.language,s.name,s.subject,s.message,s.fromname,s.fromemail,s.plaintext,s.active,s.order,0,0
FROM tbltenants t
JOIN tblemailtemplates_static s ON 1 = 1 AND s.slug ='hybrid-invoice-already-send';

-- Change Order  Mail template --

INSERT INTO tblemailtemplates_static (type,slug,language,name,subject,message,fromname,fromemail,plaintext,active,`order`)
VALUES('change_order','hybrid-change-order-send-to-client','english','Send Change Order to Client','{change_order_no} for {invoice_client_property_street_1} {invoice_client_property_street_2}','<span style="font-size: 12pt;">Hi {contact_firstname},</span><br/><br/><span style="font-size: 12pt;">{change_order_no} is ready for your review and requires your approval.</span><br/><br/><span style="font-size: 12pt;">You can view it here: <a href="{change_order_link}">{change_order_no}-{invoice_client_property_street_1} {invoice_client_property_street_2}.</a></span><br/><br/><span style="font-size: 12pt;"><span style="font-size: 12pt;">{email_signature}</span>','{companyname}','',0,1,0);


INSERT INTO tblemailtemplates_static (type,slug,language,name,subject,message,fromname,fromemail,plaintext,active,`order`)
VALUES('change_order','hybrid-change-order-already-send','english','Change Order Already Sent to Client','{change_order_no} for {invoice_client_property_street_1} {invoice_client_property_street_2}','<span style="font-size: 12pt;">Hi {contact_firstname},</span><br/><br/><span style="font-size: 12pt;">{change_order_no} is ready for your review and requires your approval.</span><br/><br/><span style="font-size: 12pt;">You can view it here: <a href="{change_order_link}">{change_order_no}-{invoice_client_property_street_1} {invoice_client_property_street_2}.</a></span><br/><br/><span style="font-size: 12pt;"><span style="font-size: 12pt;">{email_signature}</span>','{companyname}','',0,1,0);

INSERT INTO tblemailtemplates (tenant_id, type, slug, language, name, subject,message,fromname,fromemail,plaintext,active,`order`,is_tenant_email_config_allowed,is_tenant )
SELECT t.id,s.type,s.slug,s.language,s.name,s.subject,s.message,s.fromname,s.fromemail,s.plaintext,s.active,s.order,0,0
FROM tbltenants t
JOIN tblemailtemplates_static s ON 1 = 1 AND s.slug IN ('hybrid-change-order-send-to-client','hybrid-change-order-already-send');

-- New Hybrid Invoice Settings --

INSERT INTO tbloptions_static (name, value, autoload) VALUES('invoice_show_labour_hours', 'per_task,per_area,per_invoice', 1);
INSERT INTO tbloptions_static (name, value, autoload) VALUES('invoice_show_labour_totals', 'per_task,per_area,per_invoice', 1);
INSERT INTO tbloptions_static (name, value, autoload) VALUES('invoice_show_supply_totals', 'per_task,per_area,per_invoice', 1);
INSERT INTO tbloptions_static (name, value, autoload) VALUES('invoice_show_totals', 'per_task,per_area', 1);
INSERT INTO tbloptions_static (name, value, autoload) VALUES('invoice_show_supplies_to_client', '0', 1);

INSERT INTO tbloptions (tenant_id, name, value, autoload)
SELECT t.id,s.name,s.value,s.autoload
FROM tbltenants t
JOIN tbloptions_static s ON 1 = 1 AND s.name in('invoice_show_labour_hours','invoice_show_labour_totals','invoice_show_supply_totals','invoice_show_totals','invoice_show_supplies_to_client');
-- Line Item Templates Queries



ALTER TABLE tblestimate_supplies ADD uom VARCHAR(191) NOT NULL DEFAULT 'unit' AFTER quantity;
ALTER TABLE tblinvoice_supplies ADD uom VARCHAR(191) NOT NULL DEFAULT 'unit' AFTER quantity;
ALTER TABLE tblchange_order_supplies ADD uom VARCHAR(191) NOT NULL DEFAULT 'unit' AFTER quantity;
ALTER TABLE tblshopping_list_items ADD uom VARCHAR(191) NOT NULL DEFAULT 'unit' AFTER quantity;


-- RUN IN CP_SUPPLIES DB 
ALTER TABLE tblsystemwide_supplies ADD  uom VARCHAR(191) NOT NULL DEFAULT 'unit' AFTER price;


INSERT INTO tblplan_features (name, codename, description,type,added_on) 
VALUES('1Build Search', 'onebuild_search', '1Build search widget for Line Item and Material Search','restrict',UTC_TIMESTAMP());

SET @plans_features_id = LAST_INSERT_ID();
INSERT INTO tblplan_features_value (plans_id, plans_features_id, value,other_data,added_on) 
VALUES(9, @plans_features_id, 0,'{"outside_message":"1Build Search is an ultimate feature, in order to use it Upgrade to ULTIMATE","inside_message":"1Build Search is an ultimate feature, in order to use it Upgrade to ULTIMATE","tag_color":"#eb0e0e","tag_title":"ULTIMATE","link":"https:\/\/my.contractorplus.app\/","count":"0"}',UTC_TIMESTAMP()),
(10, @plans_features_id, 0,'{"outside_message":"1Build Search is an ultimate feature, in order to use it Upgrade to ULTIMATE","inside_message":"1Build Search is an ultimate feature, in order to use it Upgrade to ULTIMATE","tag_color":"#eb0e0e","tag_title":"ULTIMATE","link":"https:\/\/my.contractorplus.app\/","count":"0"}',UTC_TIMESTAMP()),
(11, @plans_features_id, 0,'{"outside_message":"1Build Search is an ultimate feature, in order to use it Upgrade to ULTIMATE","inside_message":"1Build Search is an ultimate feature, in order to use it Upgrade to ULTIMATE","tag_color":"#eb0e0e","tag_title":"ULTIMATE","link":"https:\/\/my.contractorplus.app\/","count":"0"}',UTC_TIMESTAMP()),
(12, @plans_features_id, 1,NULL,UTC_TIMESTAMP()),
(13, @plans_features_id, 0,'{"outside_message":"1Build Search is an ultimate feature, in order to use it Upgrade to ULTIMATE","inside_message":"1Build Search is an ultimate feature, in order to use it Upgrade to ULTIMATE","tag_color":"#eb0e0e","tag_title":"ULTIMATE","link":"https:\/\/my.contractorplus.app\/","count":"0"}',UTC_TIMESTAMP()),
(14, @plans_features_id, 0,'{"outside_message":"1Build Search is an ultimate feature, in order to use it Upgrade to ULTIMATE","inside_message":"1Build Search is an ultimate feature, in order to use it Upgrade to ULTIMATE","tag_color":"#eb0e0e","tag_title":"ULTIMATE","link":"https:\/\/my.contractorplus.app\/","count":"0"}',UTC_TIMESTAMP()),
(15, @plans_features_id, 1,NULL,UTC_TIMESTAMP());


ALTER TABLE `tblestimate_supplies` CHANGE `quantity` `quantity` DECIMAL(15,2) NOT NULL;
ALTER TABLE `tblinvoice_supplies` CHANGE `quantity` `quantity` DECIMAL(15,2) NOT NULL;
ALTER TABLE `tblchange_order_supplies` CHANGE `quantity` `quantity` DECIMAL(15,2) NOT NULL;

-- Queries From Re-Order Branch 
ALTER TABLE `tblquickbid_items_description` ADD COLUMN `item_order` bigint(20) unsigned DEFAULT NULL;
ALTER TABLE `tbldetail_estimate_areas` ADD COLUMN `item_order` bigint(20) unsigned DEFAULT NULL;
ALTER TABLE `tbldetail_estimate_area_tasks` ADD COLUMN `item_order` BIGINT(20) UNSIGNED DEFAULT NULL;
ALTER TABLE `tblpost_inspection_areas` ADD COLUMN `item_order` BIGINT(20) UNSIGNED DEFAULT NULL;
ALTER TABLE `tblarea_tasks` ADD COLUMN `item_order` BIGINT(20) UNSIGNED DEFAULT NULL;


-- Queries Related Markup Feature 
INSERT INTO `tblplan_features`
  VALUES (NULL, 'Apply Markup', 'apply_markup_allowed', 'Apply Markup', 'restrict', 0, 999, CURRENT_TIMESTAMP);

SET @plan_features_id_for_marup = LAST_INSERT_ID(); 
INSERT INTO `tblplan_features_value`
VALUES (NULL, 9, @plan_features_id_for_marup, 0, '{"outside_message":"Apply Markup is a pro feature, in order to use it Upgrade to PRO","inside_message":"Apply Markup is a pro feature, in order to use it Upgrade to PRO","tag_color":"#eb0e0e","tag_title":"UPGRADE","link":"https:\/\/my.contractorplus.app\/","count":"0"}', CURRENT_TIMESTAMP),
(NULL, 10, @plan_features_id_for_marup, 1, '', CURRENT_TIMESTAMP),
(NULL, 11, @plan_features_id_for_marup, 1, '', CURRENT_TIMESTAMP),
(NULL, 12, @plan_features_id_for_marup, 1, '', CURRENT_TIMESTAMP),
(NULL, 13, @plan_features_id_for_marup, 0, '{"outside_message":"Apply Markup is a pro feature, in order to use it Upgrade to PRO","inside_message":"Apply Markup is a pro feature, in order to use it Upgrade to PRO","tag_color":"#eb0e0e","tag_title":"UPGRADE","link":"https:\/\/my.contractorplus.app\/","count":"0"}', CURRENT_TIMESTAMP),
(NULL, 14, @plan_features_id_for_marup, 1, '', CURRENT_TIMESTAMP),
(NULL, 15, @plan_features_id_for_marup, 1, '', CURRENT_TIMESTAMP);

ALTER TABLE tblitems ADD `labor_markup` DOUBLE(15,2) NOT NULL DEFAULT 0.00 COMMENT 'Labor Markup In Percent' AFTER group_id;

UPDATE tblplan_features_value SET other_data = '{"outside_message":"Shopping List is a pro feature, in order to use it Upgrade to PRO","inside_message":"Shopping List is a pro feature, in order to use it Upgrade to PRO","tag_color":"#eb0e0e","tag_title":"UPGRADE","link":"https:\/\/my.contractorplus.app\/","count":"0"}'
WHERE plans_features_id = 6 AND plans_id IN (9,13);

UPDATE tblplan_features_value SET other_data = '{"outside_message":"Expense Tracking is a pro feature, in order to use it Upgrade to PRO","inside_message":"Expense Tracking is a pro feature, in order to use it Upgrade to PRO","tag_color":"#eb0e0e","tag_title":"UPGRADE","link":"https:\/\/my.contractorplus.app\/","count":"0"}'
WHERE plans_features_id = 19 AND plans_id IN (9,13);

-- Queries Related to shopping list Quantity column change data type
ALTER TABLE `tblshopping_list_items` CHANGE COLUMN `quantity` `quantity` DECIMAL(15,2) UNSIGNED NOT NULL DEFAULT '0.00';
ALTER TABLE `tblshopping_list` CHANGE COLUMN `total_quantity` `total_quantity` DECIMAL(15,2) UNSIGNED NULL DEFAULT '0.00';


INSERT INTO tbltrial_details (module,plans, no_of_trial_days, module_live_date,details,addedon) 
VALUES('onebuild_search','[10,11,14]',15,'2023-10-03', '{"trial_active_details":{"icon":"assets\/images\/trial_icons\/trial_active.png","title":"1Build Search (Trial)","description":"1Build Search is an ULTIMATE feature. You have {no_of_trial_days_left} left on your free trial, after that, you\'ll need to choose a plan.","button_text":"UPGRADE PLAN"},"trial_expired_detials":{"icon":"assets\/images\/trial_icons\/trial_expired.png","title":"Trial Expired!","description":"Your trial of 1Build Search Module has expired. To continue using 1Build Search Module, choose a ULTIMATE plan now.","button_text":"UPGRADE PLAN"}}', '2023-10-03');

insert into `tblnew_feature_announcements` (`name`, `mobile_type`, `mobile_app_version`, `data`, `navigation_data`, `show_to_staff`, `is_active`, `added_by_admin_id`, `updated_by_admin_id`, `added_on`, `updated_on`) values('New Estimate','android','8.0','[{"slide_no":"1", "slide_img_src":"Estimate.svg", "slide_title":"NEW & IMPROVED ESTIMATES ARE HERE", "slide_desc":"We\'ve consolidated the three estimate types into a single, streamlined, drag and drop estimation experience.", "slide_btn_text":"Next", "is_last_slide":"0" },{ "slide_no":"2", "slide_img_src":"1_Build_Integration.svg", "slide_title":"DIRECT 1BUILD INTEGRATION", "slide_desc":"Ditch your dated cost books! Now you have direct access to LIVE local cost data for Labor, Materials & Equipment & Assemblies.", "slide_btn_text":"Next", "is_last_slide":"0" },{ "slide_no":"3", "slide_img_src":"Home_Depot.svg", "slide_title":"HOME DEPOT MATERIAL INTEGRATION", "slide_desc":"Now you will be able to access LIVE local material pricing plus inventory levels from your local Home Depot.", "slide_btn_text":"Next", "is_last_slide":"0" },{ "slide_no":"3", "slide_img_src":"Markup.svg", "slide_title":"COMPLETE CONTROL OVER YOUR MARKUP", "slide_desc":"No more limitations with how you add markup to an estimate or invoice. Now it\'s flexible, like it should be.", "slide_btn_text":"Next", "is_last_slide":"0" },{ "slide_no":"3", "slide_img_src":"More.svg", "slide_title":"MORE IMPROVEMENTS COMING THIS MONTH!", "slide_desc":"We\'re working hard to further improve your experience by enhancing the design & flow of these modules.", "slide_btn_text":"Let\'s GO!", "is_last_slide":"1" } ]','{"link_type":"internal_activity","link_data":{"android":{"internal_activity":{"nav_graph_key":"nav_hybrid_estimate_add","data":[]},"internal_webview":{"showHeader":"","title":"","internal_webview_url":"","contrefresh":""},"external_webview":{"external_webview_url":""}},"ios":{"internal_activity":{"story_vc_key":"nav_hybrid_estimate_add"},"internal_webview":{"showHeader":"","title":"","internal_webview_url":"","contrefresh":""},"external_webview":{"external_webview_url":""}}}}','0','1','1','1',CURRENT_TIMESTAMP,CURRENT_TIMESTAMP);

insert into `tblnew_feature_announcements` (`name`, `mobile_type`, `mobile_app_version`, `data`, `navigation_data`, `show_to_staff`, `is_active`, `added_by_admin_id`, `updated_by_admin_id`, `added_on`, `updated_on`) values('New Estimate','ios','6.0.0','[{"slide_no":"1", "slide_img_src":"Estimate.svg", "slide_title":"NEW & IMPROVED ESTIMATES ARE HERE", "slide_desc":"We\'ve consolidated the three estimate types into a single, streamlined, drag and drop estimation experience.", "slide_btn_text":"Next", "is_last_slide":"0" },{ "slide_no":"2", "slide_img_src":"1_Build_Integration.svg", "slide_title":"DIRECT 1BUILD INTEGRATION", "slide_desc":"Ditch your dated cost books! Now you have direct access to LIVE local cost data for Labor, Materials & Equipment & Assemblies.", "slide_btn_text":"Next", "is_last_slide":"0" },{ "slide_no":"3", "slide_img_src":"Home_Depot.svg", "slide_title":"HOME DEPOT MATERIAL INTEGRATION", "slide_desc":"Now you will be able to access LIVE local material pricing plus inventory levels from your local Home Depot.", "slide_btn_text":"Next", "is_last_slide":"0" },{ "slide_no":"3", "slide_img_src":"Markup.svg", "slide_title":"COMPLETE CONTROL OVER YOUR MARKUP", "slide_desc":"No more limitations with how you add markup to an estimate or invoice. Now it\'s flexible, like it should be.", "slide_btn_text":"Next", "is_last_slide":"0" },{ "slide_no":"3", "slide_img_src":"More.svg", "slide_title":"MORE IMPROVEMENTS COMING THIS MONTH!", "slide_desc":"We\'re working hard to further improve your experience by enhancing the design & flow of these modules.", "slide_btn_text":"Let\'s GO!", "is_last_slide":"1" } ]','{"link_type":"internal_activity","link_data":{"android":{"internal_activity":{"nav_graph_key":"nav_hybrid_estimate_add","data":[]},"internal_webview":{"showHeader":"","title":"","internal_webview_url":"","contrefresh":""},"external_webview":{"external_webview_url":""}},"ios":{"internal_activity":{"story_vc_key":"nav_hybrid_estimate_add"},"internal_webview":{"showHeader":"","title":"","internal_webview_url":"","contrefresh":""},"external_webview":{"external_webview_url":""}}}}','0','1','1','1',CURRENT_TIMESTAMP,CURRENT_TIMESTAMP);

insert into `tblnew_feature_announcements` (`name`, `mobile_type`, `mobile_app_version`, `data`, `navigation_data`, `show_to_staff`, `is_active`, `added_by_admin_id`, `updated_by_admin_id`, `added_on`, `updated_on`) values('New Estimate','web','','[{"slide_no":"1", "slide_img_src":"Estimate.png", "slide_title":"NEW & IMPROVED ESTIMATES ARE HERE", "slide_desc":"We\'ve consolidated the three estimate types into a single, streamlined, drag and drop estimation experience.", "slide_btn_text":"Next", "is_last_slide":"0" },{ "slide_no":"2", "slide_img_src":"1_Build_Integration.png", "slide_title":"DIRECT 1BUILD INTEGRATION", "slide_desc":"Ditch your dated cost books! Now you have direct access to LIVE local cost data for Labor, Materials & Equipment & Assemblies.", "slide_btn_text":"Next", "is_last_slide":"0" },{ "slide_no":"3", "slide_img_src":"Home_Depot.png", "slide_title":"HOME DEPOT MATERIAL INTEGRATION", "slide_desc":"Now you will be able to access LIVE local material pricing plus inventory levels from your local Home Depot.", "slide_btn_text":"Next", "is_last_slide":"0" },{ "slide_no":"3", "slide_img_src":"Markup.png", "slide_title":"COMPLETE CONTROL OVER YOUR MARKUP", "slide_desc":"No more limitations with how you add markup to an estimate or invoice. Now it\'s flexible, like it should be.", "slide_btn_text":"Next", "is_last_slide":"0" },{ "slide_no":"3", "slide_img_src":"More.png", "slide_title":"MORE IMPROVEMENTS COMING THIS MONTH!", "slide_desc":"We\'re working hard to further improve your experience by enhancing the design & flow of these modules.", "slide_btn_text":"Let\'s GO!", "is_last_slide":"1" } ]','hybrid_estimates/estimate','0','1','1','1',CURRENT_TIMESTAMP,CURRENT_TIMESTAMP);


ALTER TABLE `tblitems_supplies` ADD COLUMN `item_order` BIGINT(20) UNSIGNED NULL DEFAULT NULL AFTER `product_upc`;
ALTER TABLE `tblitems_supplies`	CHANGE COLUMN `quantity` `quantity` DECIMAL(15,2) UNSIGNED NOT NULL DEFAULT '0.00';
ALTER TABLE tblitems_supplies MODIFY COLUMN type ENUM('my_supply','cp_supply','local_pricing','my_local_supply') NOT NULL;
ALTER TABLE tblitems_supplies ADD  uom VARCHAR(191) NOT NULL DEFAULT 'unit' AFTER quantity;

-- Default UOM in Invoice & Estimate For Materials --

INSERT INTO tbloptions_static (name, value, autoload) VALUES('default_estimate_uom', 'unit', 1);
INSERT INTO tbloptions_static (name, value, autoload) VALUES('default_invoice_uom', 'unit', 1);

INSERT INTO tbloptions (tenant_id, name, value, autoload)
SELECT t.id,s.name,s.value,s.autoload
FROM tbltenants t
JOIN tbloptions_static s ON 1 = 1 AND s.name in('default_estimate_uom','default_invoice_uom');

-- tblitems markup changes to supply_markup --

ALTER TABLE `tblitems` CHANGE `markup` `supply_markup` DOUBLE(15,2) NULL DEFAULT NULL;