START TRANSACTION;

CREATE TABLE `tbldetail_estimate_areas` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `estimate_id` bigint(20) UNSIGNED 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 `tbldetail_estimate_areas`
  ADD PRIMARY KEY (`id`),
  ADD KEY `estimate_id` (`estimate_id`);

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

CREATE TABLE `tbldetail_estimate_area_tasks` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `detail_estimate_area_id` bigint(20) UNSIGNED NOT NULL,
  `datecreated` datetime NOT NULL,
  `title` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `description` text COLLATE utf8_unicode_ci,
  `comment` text COLLATE utf8_unicode_ci,
  `total_labour_cost` decimal(15,2) NOT NULL DEFAULT '0.00',
  `total_supply_cost` decimal(15,2) NOT NULL DEFAULT '0.00',
  `tax_on` enum('on_supply','on_labour','on_both') COLLATE utf8_unicode_ci NOT NULL,
  `subtotal` decimal(15,2) NOT NULL DEFAULT '0.00',
  `total_tax` decimal(15,2) NOT NULL DEFAULT '0.00',
  `total` decimal(15,2) NOT NULL DEFAULT '0.00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `tbldetail_estimate_area_tasks`
  ADD PRIMARY KEY (`id`),
  ADD KEY `detail_estimate_area_id` (`detail_estimate_area_id`);

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


CREATE TABLE `tbldetail_estimate_area_task_files` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `detail_estimate_area_task_id` bigint(20) UNSIGNED 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') 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,
  `thumbnail_link` text COLLATE utf8_unicode_ci 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 `tbldetail_estimate_area_task_files`
  ADD PRIMARY KEY (`id`),
  ADD KEY `detail_estimate_area_task_id` (`detail_estimate_area_task_id`);

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


CREATE TABLE `tbldetail_estimate_area_task_labour_chareges` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `detail_estimate_area_task_id` bigint(20) UNSIGNED DEFAULT NULL,
  `time_estimate` decimal(15,2) NOT NULL DEFAULT '0.00',
  `labour_rate` decimal(15,2) NOT NULL DEFAULT '0.00',
  `dateadded` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `tbldetail_estimate_area_task_labour_chareges`
  ADD PRIMARY KEY (`id`),
  ADD KEY `detail_estimate_area_task_id` (`detail_estimate_area_task_id`);

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


CREATE TABLE `tbldetail_estimate_area_task_items` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `detail_estimate_area_task_id` bigint(20) UNSIGNED NOT 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,
  `image` varchar(191) COLLATE utf8_unicode_ci DEFAULT NULL,
  `category_name` varchar(191) COLLATE utf8_unicode_ci DEFAULT NULL,
  `supplier_name` 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_percent` decimal(15,2) DEFAULT '0.00',
  `markup_total` decimal(15,2) DEFAULT '0.00',
  `supply_total_price` decimal(15,2) NOT NULL,
  `purchase_status` enum('purchased','unpurchased') COLLATE utf8_unicode_ci DEFAULT 'unpurchased',
  `dateadded` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `datefinished` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `item_order` bigint(20) UNSIGNED NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `tbldetail_estimate_area_task_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `detail_estimate_area_task_id` (`detail_estimate_area_task_id`),
  ADD KEY `cp_supply_id` (`cp_supply_id`),
  ADD KEY `my_supply_id` (`my_supply_id`);


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


ALTER TABLE `tbldetail_estimate_area_task_items`
  ADD CONSTRAINT `tbldeat_items_ibfk_1` FOREIGN KEY (`cp_supply_id`) REFERENCES `tblsystemwide_supplies` (`id`) ON DELETE SET NULL ON UPDATE SET NULL,
  ADD CONSTRAINT `tbldeat_items_ibfk_2` FOREIGN KEY (`my_supply_id`) REFERENCES `tbltenantspecific_supplies` (`id`) ON DELETE SET NULL ON UPDATE SET NULL;

INSERT INTO `tblapad_options` (`id`, `type`, `name`, `value`, `autoload`) VALUES 
(NULL, 'detail_estimates', 'allowed_file_extention', '{\"image\":[\"jpeg\",\"jpg\",\"png\",\"gif\"],\"video\":[],\"audio\":[],\"document\":[]}', '1'), 
(NULL, 'detail_estimates', 'allowed_file_count', '5', '1'), 
(NULL, 'detail_estimates', 'allowed_file_size', '5120', '1'), 
(NULL, 'detail_estimates', 'total_file_count', '10', '1');

ALTER TABLE `tblestimates` ADD `type` ENUM('detail','normal') NOT NULL DEFAULT 'normal' AFTER `title`;
ALTER TABLE `tblestimates` ADD `labour_rate` DECIMAL(15,2) NOT NULL DEFAULT '0.00' AFTER `property_id`;
ALTER TABLE `tblestimates` ADD `markup` DOUBLE NULL DEFAULT NULL AFTER `labour_rate`, ADD `markup_type` ENUM('percent','fixed') NULL DEFAULT NULL AFTER `markup`;
ALTER TABLE `tblestimates` ADD `final_labour_charges` DECIMAL(15,2) NOT NULL DEFAULT '0.00' AFTER `markup_type`, ADD `final_supply_charges` DECIMAL(15,2) NOT NULL DEFAULT '0.00' AFTER `final_labour_charges`;
ALTER TABLE `tbldetail_estimate_area_tasks` DROP `subtotal`;

COMMIT;