CREATE TABLE `tblpost_inspections` (
  `tenant_id` bigint(20) NOT NULL DEFAULT 1,
  `id` bigint(20) NOT NULL,
  `sent` tinyint(1) NOT NULL DEFAULT 0,
  `datesend` datetime DEFAULT NULL,
  `clientid` bigint(20) NOT NULL,
  `deleted_customer_name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `project_id` bigint(20) DEFAULT NULL,
  `estimate_id` bigint(20) DEFAULT NULL,
  `property_id` bigint(20) DEFAULT NULL,
  `type` enum('normal','via_detailed_estimate') DEFAULT 'normal',
  `number` bigint(20) NOT NULL,
  `prefix` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `number_format` bigint(20) NOT NULL DEFAULT 0,
  `hash` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  `datecreated` datetime NOT NULL,
  `date` date NOT NULL,
  `post_inspection_date` date DEFAULT NULL,
  `addedfrom` bigint(20) NOT NULL,
  `status` enum('pending','in-progress','completed','cancelled') DEFAULT 'pending',
  `clientnote` text COLLATE utf8_unicode_ci DEFAULT NULL,
  `adminnote` text COLLATE utf8_unicode_ci DEFAULT NULL,
  `staff_id` bigint(20) DEFAULT NULL,
  `property_street` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `property_city` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `property_state` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `property_zip` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `property_country` bigint(20) DEFAULT NULL,
  `pipeline_order` bigint(20) NOT NULL DEFAULT 0,
  `is_expiry_notified` bigint(20) NOT NULL DEFAULT 0,
  `acceptance_firstname` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `acceptance_lastname` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `acceptance_email` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `acceptance_date` datetime DEFAULT NULL,
  `acceptance_ip` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `signature` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `tblpost_inspections`
  ADD PRIMARY KEY (`id`),
  ADD KEY `clientid` (`clientid`),
  ADD KEY `project_id` (`project_id`),
  ADD KEY `estimate_id` (`estimate_id`),
  ADD KEY `property_id` (`property_id`),
  ADD KEY `staff_id` (`staff_id`);

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


CREATE TABLE `tblpost_inspection_areas` (
  `id` bigint(20) NOT NULL,
  `post_inspection_id` bigint(20) NOT NULL,
  `datecreated` datetime NOT NULL,
  `area` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `tblpost_inspection_areas`
  ADD PRIMARY KEY (`id`),
  ADD KEY `post_inspection_id` (`post_inspection_id`);

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


CREATE TABLE `tblarea_tasks` (
  `id` bigint(20) NOT NULL,
  `area_id` bigint(20) NOT NULL,
  `datecreated` datetime NOT NULL,
  `title` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `description` text,
  `comment` text,
  `status` enum('in-complete','completed','cancelled') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `tblarea_tasks`
  ADD PRIMARY KEY (`id`),
  ADD KEY `area_id` (`area_id`);

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


CREATE TABLE `tbltask_files` (
  `id` bigint(20) NOT NULL,
  `task_id` bigint(20) DEFAULT 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') DEFAULT NULL,
  `visible_to_customer` bigint(20) 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
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `tbltask_files`
  ADD PRIMARY KEY (`id`),
  ADD KEY `task_id` (`task_id`);

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

CREATE TABLE `tbltemp_files` (
  `id` bigint(20) NOT NULL,
  `file_name` varchar(191) COLLATE utf8_unicode_ci NOT NULL,
  `filetype` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `image_for` varchar(191) COLLATE utf8_unicode_ci NOT NULL,
  `task_file_type` enum('pre_photos','post_photos') DEFAULT NULL,
  `original_file_name` varchar(191) COLLATE utf8_unicode_ci DEFAULT NULL,
  `dateadded` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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

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


INSERT INTO `tbloptions` (`tenant_id`, `id`, `name`, `value`, `autoload`) VALUES ('1', NULL, 'allow_staff_view_post_inspections_assigned', '1', '1');
INSERT INTO `tbloptions` (`tenant_id`, `id`, `name`, `value`, `autoload`) VALUES ('1', NULL, 'predefined_clientnote_estimate', '', '1');
ALTER TABLE `tblpost_inspections` CHANGE `status` `post_inspection_status` ENUM('pending','in-progress','completed','cancelled') CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;
ALTER TABLE `tblpost_inspections` ADD `status` BIGINT(10) NOT NULL DEFAULT '1' AFTER `post_inspection_status`;
ALTER TABLE `tblpost_inspections` ADD `post_inspection_title` VARCHAR(191) NULL DEFAULT NULL AFTER `id`;