INSERT INTO tbloptions_static (name, value, autoload) VALUES ('job_number_prefix', 'JOB-', '1');
INSERT INTO tbloptions_static (name, value, autoload) VALUES ('next_job_number', '1', '1');
INSERT INTO tbloptions_static (name, value, autoload) VALUES ('job_number_format', '1', '1');
INSERT INTO tbloptions (tenant_id, name, value, autoload) SELECT id, 'job_number_prefix', 'JOB-', '1' FROM tbltenants;
INSERT INTO tbloptions (tenant_id, name, value, autoload) SELECT id, 'next_job_number', '1', '1' FROM tbltenants;
INSERT INTO tbloptions (tenant_id, name, value, autoload) SELECT id, 'job_number_format', '1', '1' FROM tbltenants;

CREATE TABLE tbljobs (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
    tenant_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(500) DEFAULT NULL,
    description LONGTEXT DEFAULT NULL,
    number BIGINT UNSIGNED,
    prefix VARCHAR(50),
    number_format BIGINT UNSIGNED,
    job_number_text VARCHAR(191),
    is_schedule_later TINYINT(1) DEFAULT 0,
    schedule_date DATE DEFAULT NULL,
    is_any_time TINYINT(1) DEFAULT 0,
    schedule_start_time TIME DEFAULT NULL,
    schedule_end_time TIME DEFAULT NULL,
    repeat_job_frequency VARCHAR(191) DEFAULT NULL,
    repeat_value INT UNSIGNED DEFAULT NULL,
    repeat_value_type VARCHAR(191) DEFAULT NULL,
    rel_type VARCHAR(191) DEFAULT NULL,
    rel_id BIGINT UNSIGNED DEFAULT NULL,
    invoice_reminder VARCHAR(191) DEFAULT NULL,
    status VARCHAR(191) DEFAULT NULL,
    client_id BIGINT UNSIGNED NOT NULL,
    property_street VARCHAR(200) DEFAULT NULL,
    property_street_2 VARCHAR(200) DEFAULT NULL,
    property_city VARCHAR(100) DEFAULT NULL,
    property_state VARCHAR(100) DEFAULT NULL,
    property_state_id BIGINT UNSIGNED DEFAULT NULL,
    property_zip VARCHAR(100) DEFAULT NULL,
    property_country BIGINT UNSIGNED DEFAULT NULL,
    property_id BIGINT UNSIGNED DEFAULT NULL,
    post_inspection_staff_id BIGINT UNSIGNED DEFAULT NULL,
    added_by BIGINT UNSIGNED NOT NULL,
    added_on DATETIME NOT NULL
);
CREATE TABLE tbljob_visits (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
    tenant_id BIGINT UNSIGNED NOT NULL,
    job_id BIGINT UNSIGNED NOT NULL,
    description LONGTEXT DEFAULT NULL,
    status VARCHAR(200) NOT NULL,
    is_invoice_created TINYINT(1) DEFAULT 0,
    is_schedule_later TINYINT(1) DEFAULT 0,
    schedule_date DATE DEFAULT NULL,
    schedule_start_time TIME DEFAULT NULL,
    schedule_end_time TIME DEFAULT NULL,
    is_any_time TINYINT(1) DEFAULT 0,
    added_on DATETIME NOT NULL
);
CREATE TABLE tbljob_visits_staff (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
    tenant_id BIGINT UNSIGNED NOT NULL,
    job_id BIGINT UNSIGNED NOT NULL,
    job_visit_id BIGINT UNSIGNED NOT NULL,
    staff_id BIGINT UNSIGNED NOT NULL,
    added_on DATETIME NOT NULL
);

ALTER TABLE tbljobs ADD term_contracts_id BIGINT UNSIGNED NULL DEFAULT NULL AFTER post_inspection_staff_id;
ALTER TABLE tbljobs ADD term_contracts_title VARCHAR(1000) NULL DEFAULT NULL AFTER term_contracts_id;
ALTER TABLE tbljobs ADD term_contracts_description LONGTEXT NULL DEFAULT NULL AFTER term_contracts_title;
ALTER TABLE tbljobs ADD FOREIGN KEY (term_contracts_id) REFERENCES tblterm_contracts (id) ON DELETE SET NULL ON UPDATE CASCADE;

-- 2021-01-07
ALTER TABLE `tbltaskstimers` ADD `visit_id` BIGINT UNSIGNED NULL DEFAULT NULL AFTER `task_id`;
ALTER TABLE `tbltaskstimers` CHANGE `type` `type` ENUM('general','task','visit') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'general';

-- 2021-01-16
ALTER TABLE tbljob_visits ADD status_updated_on DATETIME NULL DEFAULT NULL AFTER status;

-- 2021-01-18
CREATE TABLE tbljob_visits_invoices(
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
    tenant_id BIGINT UNSIGNED NOT NULL,
    job_id BIGINT UNSIGNED NOT NULL,
    job_visit_id BIGINT UNSIGNED NOT NULL,
    invoice_id BIGINT UNSIGNED NOT NULL,
    added_on DATETIME NOT NULL    
);

-- 2021-01-19
INSERT INTO `tblplan_features` (`id`, `name`, `codename`, `description`, `type`, `added_on`) VALUES (NULL, 'Job Schedule Module', 'job_schedule_module', 'Job Schedule Module', 'allow', current_timestamp());

INSERT INTO `tblplan_features_value` (`id`, `plans_id`, `plans_features_id`, `value`, `other_data`, `added_on`) VALUES (NULL, '9', '74', '0', NULL, current_timestamp());
INSERT INTO `tblplan_features_value` (`id`, `plans_id`, `plans_features_id`, `value`, `other_data`, `added_on`) VALUES (NULL, '10', '74', '1', NULL, current_timestamp());
INSERT INTO `tblplan_features_value` (`id`, `plans_id`, `plans_features_id`, `value`, `other_data`, `added_on`) VALUES (NULL, '11', '74', '1', NULL, current_timestamp());
INSERT INTO `tblplan_features_value` (`id`, `plans_id`, `plans_features_id`, `value`, `other_data`, `added_on`) VALUES (NULL, '12', '74', '1', NULL, current_timestamp());

ALTER TABLE tbltaskstimers ADD FOREIGN KEY (visit_id) REFERENCES tbljob_visits(id) ON DELETE SET NULL;
ALTER TABLE tbljob_visits_invoices CHANGE job_visit_id job_visit_id BIGINT(20) UNSIGNED NULL DEFAULT NULL;
ALTER TABLE tbljob_visits_invoices ADD FOREIGN KEY (job_visit_id) REFERENCES tbljob_visits(id) ON DELETE SET NULL;
ALTER TABLE tbljob_visits_staff ADD FOREIGN KEY (job_visit_id) REFERENCES tbljob_visits(id) ON DELETE CASCADE;


-- 2021-05-26 Trial Module
CREATE TABLE tbltrial_details (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
    module VARCHAR(191) NOT NULL,
    no_of_trial_days INT UNSIGNED NOT NULL,
    module_live_date DATE NOT NULL,
    details MEDIUMTEXT NOT NULL,
    addedon DATE NOT NULL
);

INSERT INTO tbltrial_details (module, no_of_trial_days, module_live_date, details, addedon) VALUES ('job_schedule_module', '5', '2021-05-26', '{\"trial_active_details\":{\"icon\":\"assets\\/images\\/trial_icons\\/trial_active.png\",\"title\":\"Job Schedule (Trial)\",\"description\":\"The job schedule is a PRO 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 the Job Schedule has expired. To continue using the job schedule, choose a PRO plan now.\",\"button_text\":\"UPGRADE PLAN\"}}', '2021-05-26');


-- 2021-06-07 Emails

INSERT INTO `tblemailtemplates_static` (`type`, `slug`, `language`, `name`, `subject`, `message`, `fromname`, `fromemail`, `plaintext`, `active`, `order`) VALUES ('job_schedule', 'job-schedule-confirmation', 'english', 'Job Schedule 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 {visit_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, 'job_schedule', 'job-schedule-confirmation', 'english', 'Job Schedule 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 {visit_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 `tblemailtemplates_static` (`type`, `slug`, `language`, `name`, `subject`, `message`, `fromname`, `fromemail`, `plaintext`, `active`, `order`) VALUES ('job_schedule', 'job-schedule-followup', 'english', 'Job Schedule Follow-Up', 'Thank You: Your Job by {companyname} is complete', '<span style="font-size: 12pt;">Hi {client_first_name},</span><br><br><span style="font-size: 12pt;">If you have any questions or concerns about this job that our team has completed, please don\'t hesitate to contact us.</span><br><span style="font-size: 12pt;">We work really hard to provide the best experience for our customers and are always looking for ways to better meet your needs.</span><br><br><span style="font-size: 12pt;">Thank you {client_first_name}.</span><br><br><span style="font-size: 12pt;">The team at {companyname}</span>', '', NULL, '0', '1', '0');

INSERT INTO `tblemailtemplates` (`tenant_id`, `type`, `slug`, `language`, `name`, `subject`, `message`, `fromname`, `fromemail`, `plaintext`, `active`, `order`) SELECT id, 'job_schedule', 'job-schedule-followup', 'english', 'Job Schedule Follow-Up', 'Thank You: Your Job by {companyname} is complete', '<span style="font-size: 12pt;">Hi {client_first_name},</span><br><br><span style="font-size: 12pt;">If you have any questions or concerns about this job that our team has completed, please don\'t hesitate to contact us.</span><br><span style="font-size: 12pt;">We work really hard to provide the best experience for our customers and are always looking for ways to better meet your needs.</span><br><br><span style="font-size: 12pt;">Thank you {client_first_name}.</span><br><br><span style="font-size: 12pt;">The team at {companyname}</span>', '', NULL, '0', '1', '0' FROM tbltenants;

-- 2021-06-09 Notifications

INSERT INTO `tblmobile_push_notification_config` (`id`, `codename`, `is_active`) VALUES (NULL, 'job_visit_cancelled', '1');
INSERT INTO `tblin_app_notification_config` (`id`, `codename`, `is_active`) VALUES (NULL, 'job_visit_cancelled', '1');

INSERT INTO `tblmobile_push_notification_config` (`id`, `codename`, `is_active`) VALUES (NULL, 'visit_reminder_3hrs', '1');
INSERT INTO `tblin_app_notification_config` (`id`, `codename`, `is_active`) VALUES (NULL, 'visit_reminder_3hrs', '1');

INSERT INTO `tblmobile_push_notification_config` (`id`, `codename`, `is_active`) VALUES (NULL, 'job_reminder_1day', '1');
INSERT INTO `tblin_app_notification_config` (`id`, `codename`, `is_active`) VALUES (NULL, 'job_reminder_1day', '1');

INSERT INTO `tblmobile_push_notification_config` (`id`, `codename`, `is_active`) VALUES (NULL, 'visit_rescheduled', '1');
INSERT INTO `tblin_app_notification_config` (`id`, `codename`, `is_active`) VALUES (NULL, 'visit_rescheduled', '1');

INSERT INTO `tblmobile_push_notification_config` (`id`, `codename`, `is_active`) VALUES (NULL, 'visit_reminder', '1');
INSERT INTO `tblin_app_notification_config` (`id`, `codename`, `is_active`) VALUES (NULL, 'visit_reminder', '1');

INSERT INTO `tblmobile_push_notification_config` (`id`, `codename`, `is_active`) VALUES (NULL, 'visit_reminder_all_day', '1');
INSERT INTO `tblin_app_notification_config` (`id`, `codename`, `is_active`) VALUES (NULL, 'visit_reminder_all_day', '1');

INSERT INTO `tblmobile_push_notification_config` (`id`, `codename`, `is_active`) VALUES (NULL, 'reminder_job_running_late', '1');
INSERT INTO `tblin_app_notification_config` (`id`, `codename`, `is_active`) VALUES (NULL, 'reminder_job_running_late', '1');

INSERT INTO `tblmobile_push_notification_config` (`id`, `codename`, `is_active`) VALUES (NULL, 'job_cancelled', '1');
INSERT INTO `tblin_app_notification_config` (`id`, `codename`, `is_active`) VALUES (NULL, 'job_cancelled', '1');

INSERT INTO `tblmobile_push_notification_config` (`id`, `codename`, `is_active`) VALUES (NULL, 'missed_visit_reminder', '1');
INSERT INTO `tblin_app_notification_config` (`id`, `codename`, `is_active`) VALUES (NULL, 'missed_visit_reminder', '1');

INSERT INTO `tblmobile_push_notification_config` (`id`, `codename`, `is_active`) VALUES (NULL, 'monthly_job_reminder', '1');
INSERT INTO `tblin_app_notification_config` (`id`, `codename`, `is_active`) VALUES (NULL, 'monthly_job_reminder', '1');