CREATE TABLE IF NOT EXISTS `tblrequest_form_sections` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `section_name` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,  
  `is_customer_availability` tinyint(1) NOT NULL DEFAULT 0,
  `section_order` bigint(20) unsigned DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `datecreated` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `tenant_id` (`tenant_id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE IF NOT EXISTS `tblrequest_form_fields` (  
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL DEFAULT 1,
  `section_id` bigint(20) unsigned NOT NULL,
  `field_name` varchar(500) NOT NULL,
  `field_type` varchar(20) NOT NULL,
  `options` mediumtext DEFAULT NULL,
  `is_required` tinyint(1) NOT NULL DEFAULT 0,
  `field_order` bigint(20) unsigned DEFAULT 0,
  `default_value` text DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL,
  `created_by` bigint(20) DEFAULT NULL,
  `updated_at` datetime NOT NULL,
  `updated_by` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tenant_id` (`tenant_id`),
  KEY `section_id` (`section_id`),
  KEY `field_type` (`field_type`),
  KEY `active` (`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `tblrequest_form_fields_values` (    
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL DEFAULT 1,
  `request_form_id` bigint(20) unsigned NOT NULL,
  `field_id` bigint(20) unsigned NOT NULL,
  `value` text NOT NULL,
  `options` mediumtext DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `request_form_id` (`request_form_id`),
  KEY `field_id` (`field_id`),
  KEY `tenant_id` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `tbltenant_request_forms` (    
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL DEFAULT 1,
  `client_id` bigint(20) unsigned NOT NULL,
  `property_id` bigint(20) unsigned NOT NULL,
  `form_title` varchar(500),
  `status` enum('new','converted','scheduled','unscheduled','overdue','assessment_completed','archived') NOT NULL DEFAULT 'new',
  `created_at` datetime NOT NULL,
  `created_by` bigint(20) DEFAULT NULL,
  `updated_at` datetime NOT NULL,
  `updated_by` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `property_id` (`property_id`),
  KEY `client_id` (`client_id`),
  KEY `tenant_id` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `tbltenant_converted_request_forms` (    
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL DEFAULT 1,
  `client_id` bigint(20) unsigned NOT NULL,
  `property_id` bigint(20) unsigned NOT NULL,
  `request_form_id` bigint(20) unsigned NOT NULL,
  `rel_id` bigint(20) unsigned NOT NULL,
  `rel_type` varchar(20) NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `rel_type` (`rel_type`),
  KEY `rel_id` (`rel_id`),
  KEY `request_form_id` (`request_form_id`),
  KEY `property_id` (`property_id`),
  KEY `client_id` (`client_id`),
  KEY `tenant_id` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


INSERT INTO tblplan_features (name, codename, description,type,is_highlight,added_on) 
VALUES('Client Request Form', 'client_requests_module', 'Client Request Form','allow',1,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":"Client Request Form is an ultimate feature, in order to use it Upgrade to ULTIMATE","inside_message":"Client Request Form 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":"Client Request Form is an ultimate feature, in order to use it Upgrade to ULTIMATE","inside_message":"Client Request Form 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":"Client Request Form is an ultimate feature, in order to use it Upgrade to ULTIMATE","inside_message":"Client Request Form 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":"Client Request Form is an ultimate feature, in order to use it Upgrade to ULTIMATE","inside_message":"Client Request Form 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":"Client Request Form is an ultimate feature, in order to use it Upgrade to ULTIMATE","inside_message":"Client Request Form 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());

CREATE TABLE `tblrequest_form_layouts` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT 1,
  `form_desc` VARCHAR(500) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_at` DATETIME NOT NULL,
  `created_by` BIGINT(20) DEFAULT NULL,
  `updated_at` DATETIME NOT NULL,
  `updated_by` BIGINT(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tenant_id` (`tenant_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE tblrequest_form_sections ADD layout_id BIGINT(20) UNSIGNED NOT NULL AFTER tenant_id;
ALTER TABLE tblrequest_form_fields ADD layout_id BIGINT(20) UNSIGNED NOT NULL AFTER section_id;

ALTER TABLE tbltenant_request_forms ADD additional_info text DEFAULT NULL AFTER status;

ALTER TABLE tblrequest_form_fields_values ADD field_name varchar(150) COLLATE utf8_unicode_ci NOT NULL AFTER field_id;

ALTER TABLE tbltenant_request_forms ADD schedule_start_date date DEFAULT NULL AFTER additional_info;
ALTER TABLE tbltenant_request_forms ADD schedule_end_date date DEFAULT NULL AFTER schedule_start_date;
ALTER TABLE tbltenant_request_forms ADD start_time time DEFAULT NULL AFTER schedule_end_date;
ALTER TABLE tbltenant_request_forms ADD end_time time DEFAULT NULL AFTER start_time;
ALTER TABLE tbltenant_request_forms ADD is_any_time tinyint(1) NOT NULL DEFAULT 0 AFTER end_time;

ALTER TABLE tbltenant_request_forms ADD is_assessment_scheduled TINYINT(1) NOT NULL DEFAULT 0 AFTER additional_info;
ALTER TABLE tbltenant_request_forms ADD schedule_instructions varchar(250) DEFAULT NULL AFTER additional_info;

CREATE TABLE `tblrequest_form_assessment_staff` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` BIGINT(20) UNSIGNED NOT NULL,
  `request_form_id` BIGINT(20) UNSIGNED NOT NULL,
  `staff_id` BIGINT(20) UNSIGNED NOT NULL,
  `added_on` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `tenant_id` (`tenant_id`),
  KEY `staff_id` (`staff_id`),
  KEY `request_form_id` (`request_form_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE tbltenant_request_forms ADD number bigint(20) unsigned NOT NULL DEFAULT 0 AFTER property_id;
ALTER TABLE tbltenant_request_forms ADD prefix varchar(50) DEFAULT NULL AFTER number;
ALTER TABLE tbltenant_request_forms ADD number_format bigint(20) unsigned NOT NULL DEFAULT 0 AFTER prefix;
ALTER TABLE tbltenant_request_forms ADD request_number_text varchar(191) DEFAULT NULL AFTER number_format;

ALTER TABLE `tbltenant_request_forms` CHANGE `form_title` `form_title` VARCHAR(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, CHANGE `status` `status` ENUM('new','converted','scheduled','unscheduled','overdue','assessment_completed','archived') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'new';
ALTER TABLE `tbltenant_request_forms` CHANGE `request_number_text` `request_number_text` VARCHAR(191) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

--umesh client request number settings--
INSERT INTO tbloptions_static (name, value, autoload) VALUES('request_prefix', 'RQ-', 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('request_prefix');


INSERT INTO tbloptions_static (name, value, autoload) VALUES('next_request_number', '1', 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('next_request_number');


INSERT INTO tbloptions_static (name, value, autoload) VALUES('request_number_format', '1', 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('request_number_format');

ALTER TABLE tbltenant_request_forms ADD is_schedule_assessment_completed tinyint(1) NOT NULL DEFAULT 0 AFTER end_time;

-- Notification for Schedule assessment
INSERT INTO tblmobile_push_notification_config (codename,is_active) VALUES ('assessment_scheduled',1);

-- Reminders from  CRON
INSERT INTO tblmobile_push_notification_config (codename,is_active) VALUES ('assessment_reminder_3hrs',1);
INSERT INTO tblmobile_push_notification_config (codename,is_active) VALUES ('assessment_reminder_1day',1);
INSERT INTO tblmobile_push_notification_config (codename,is_active) VALUES ('assessment_reminder_all_day',1);
INSERT INTO tblmobile_push_notification_config (codename,is_active) VALUES ('reminder_assessment_running_late',1);
INSERT INTO tblmobile_push_notification_config (codename,is_active) VALUES ('missed_assessment_reminder',1);
INSERT INTO tblmobile_push_notification_config (codename,is_active) VALUES ('assessment_overdue',1);

-- client request mail & sms query --
ALTER TABLE `tblsms_templates` CHANGE `type` `type` ENUM('estimate','invoice','post_inspection','business_review','client_referral','estimate_expiration_reminder','invoice_overdue_reminder','work_order','payment_schedule_reminder','change_order','change_order_payment_schedule_reminder','payment_confirmation','job_booking_confirmation','client_request_confirmation') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL;

ALTER TABLE `tblsms_templates_static` CHANGE `type` `type` ENUM('estimate','invoice','post_inspection','business_review','client_referral','estimate_expiration_reminder','invoice_overdue_reminder','work_order','payment_schedule_reminder','change_order','change_order_payment_schedule_reminder','payment_confirmation','job_booking_confirmation','client_request_confirmation') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL;

INSERT INTO `tblemailtemplates_static` (`type`, `slug`, `language`, `name`, `subject`, `message`, `fromname`, `fromemail`, `plaintext`, `active`, `order`) VALUES ('client_request', 'client-request-confirmation', 'english', 'Client Request Confirmation', 'Booking confirmed with {companyname}', '<span style="font-size: 12pt;">Hi {client_first_name},</span><br><br><span style="font-size: 12pt;">You\'re booking is confirmed with {companyname} for {client_address_street} at {start_time} on {schedule_start_date}.</span><br><br><span style="font-size: 12pt;">Regards,</span><br><span style="font-size: 12pt;">{companyname}</span>', '', NULL, '0', '1', '0');

INSERT INTO `tblemailtemplates` (`tenant_id`, `type`, `slug`, `language`, `name`, `subject`, `message`, `fromname`, `fromemail`, `plaintext`, `active`, `order`) SELECT id, 'client_request', 'client-request-confirmation', 'english', 'Client Request Confirmation', 'Booking confirmed with {companyname}', '<span style="font-size: 12pt;">Hi {client_first_name},</span><br><br><span style="font-size: 12pt;">You\'re booking is confirmed with {companyname} for {client_address_street} at {start_time} on {schedule_start_date}.</span><br><br><span style="font-size: 12pt;">Regards,</span><br><span style="font-size: 12pt;">{companyname}</span>', '', NULL, '0', '1', '0' FROM tbltenants;


INSERT INTO tblsms_templates_static (type, template) 
VALUES('client_request_confirmation',"Hi, You\'re booking is confirmed with {companyname} for {client_address_street} at {start_time} on {schedule_start_date}. If you need to make any changes to this booking please call {companyphone} or email {companyemail}");


INSERT INTO tblsms_templates (tenant_id, type,template,created_on,updated_on )
SELECT t.id,s.type,s.template,UTC_TIMESTAMP(),UTC_TIMESTAMP()
FROM tbltenants t
JOIN tblsms_templates_static s ON 1 = 1 AND s.type ='client_request_confirmation';
-- client request mail & sms query --

ALTER TABLE tbltenant_request_forms ADD `source` enum('web','app','external_link','client_portal') DEFAULT NULL AFTER property_id;

INSERT INTO tblrequest_form_layouts (tenant_id,created_by,updated_by, created_at, updated_at) 
SELECT s.tenant_id,s.staffid as created_by ,s.staffid as updated_by,UTC_TIMESTAMP(),UTC_TIMESTAMP()
FROM tblstaff s
WHERE is_tenant = 1;

INSERT INTO tblrequest_form_sections (tenant_id,layout_id,section_name, is_customer_availability, section_order,active,datecreated) 
SELECT tenant_id,id AS layout_id,'Customer Availability Section' AS section_name, '1' AS is_customer_availability,'1' AS `order`,'1' AS `active` ,UTC_TIMESTAMP()
FROM tblrequest_form_layouts;

INSERT INTO tblrequest_form_fields (tenant_id,section_id,layout_id, field_name, field_type,field_order,`active`,created_by,updated_by,created_at,updated_at) 
SELECT sec.tenant_id,sec.id AS section_id, sec.layout_id,'Which day would be best for an assessment of the work?' AS field_name,'date' AS field_type,'1' AS field_order,'1' AS active,frm.created_by AS created_by,frm.created_by AS updated_by,UTC_TIMESTAMP(),UTC_TIMESTAMP()
FROM tblrequest_form_layouts frm 
LEFT JOIN tblrequest_form_sections sec ON sec.layout_id = frm.id;


INSERT INTO tblrequest_form_fields (tenant_id,section_id,layout_id, field_name, field_type,field_order,`active`,created_by,updated_by,created_at,updated_at) 
SELECT sec.tenant_id,sec.id AS section_id, sec.layout_id,'What is another day that works for you?' AS field_name,'date' AS field_type,'2' AS field_order,'1' AS active,frm.created_by AS created_by,frm.created_by AS updated_by,UTC_TIMESTAMP(),UTC_TIMESTAMP()
FROM tblrequest_form_layouts frm 
LEFT JOIN tblrequest_form_sections sec ON sec.layout_id = frm.id;

INSERT INTO tblrequest_form_fields (tenant_id,section_id,layout_id, field_name, field_type,field_order,`active`,created_by,updated_by,created_at,updated_at,options) 
SELECT sec.tenant_id,sec.id AS section_id, sec.layout_id,'What are your preferred arrival times?' AS field_name,'checkbox' AS field_type,'3' AS field_order,'1' AS active,frm.created_by AS created_by,frm.created_by AS updated_by,UTC_TIMESTAMP(),UTC_TIMESTAMP(),'{"anytime":"Anytime","morning":"Morning","afternoon":"Afternoon","evening":"Evening"}' AS options
FROM tblrequest_form_layouts frm 
LEFT JOIN tblrequest_form_sections sec ON sec.layout_id = frm.id;







