CREATE TABLE `tblpdf_designs` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `codename` VARCHAR(191) NOT NULL,
  `name` VARCHAR(191) NOT NULL,
  `description` TEXT NULL DEFAULT NULL,
  `design_image` VARCHAR(191) NULL DEFAULT NULL,
  `type` ENUM('basic_estimate', 'detail_estimate', 'invoice', 'post_inspection', 'shopping_list') NOT NULL,
  `is_default` TINYINT(1) NOT NULL DEFAULT '0',
  `is_active` TINYINT(1) NOT NULL DEFAULT '1',
  UNIQUE (codename)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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

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



CREATE TABLE `tbltenant_pdf_designs` (
  `uniquecode` VARCHAR(191) NOT NULL,
  `tenant_id` BIGINT(20) UNSIGNED NOT NULL,
  `type` ENUM('basic_estimate', 'detail_estimate', 'invoice', 'post_inspection', 'shopping_list') NOT NULL,
  `pdf_designs_id` BIGINT(20) UNSIGNED NOT NULL,
  `added_by` BIGINT(20) UNSIGNED NOT NULL,
  `addedon` datetime NOT NULL,
  `updated_by` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
  `updated_on` datetime NULL DEFAULT NULL,
  UNIQUE KEY `tpd_tenant_id_type` (`tenant_id`,`type`)
  
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `tbltenant_pdf_designs`
  ADD PRIMARY KEY (`uniquecode`);


INSERT INTO `tblpdf_designs` (`id`, `codename`, `name`, `description`, `design_image`, `type`, `is_default`, `is_active`) 
VALUES 
(NULL, 'basic_estimate_type_1', 'Basic estimate type 1', 'Type on', 'default.png', 'basic_estimate', '1', '1'), 
(NULL, 'detail_estimate_type_1', 'Detail estimate type 1', 'Detail estimate', 'default.png', 'detail_estimate', '1', '1'),
(NULL, 'invoice_type_1', 'Invoice type 1', 'Invoie type 1', 'default.png', 'invoice', '1', '1'), 
(NULL, 'post_inspection_type_1', 'Post Inspection type 1', 'Post Inspection', 'default.png', 'post_inspection', '1', '1'),
(NULL, 'shopping_list_type_1', 'Shopping list type 1', 'Shopping List', 'default.png', 'shopping_list', '1', '1');


INSERT INTO `tbltenant_pdf_designs` (`uniquecode`, `tenant_id`, `type`, `pdf_designs_id`, `added_by`, `addedon`, `updated_by`, `updated_on`)
SELECT RAND(), id, 'basic_estimate', (SELECT id from tblpdf_designs where `type` = 'basic_estimate' and is_default = 1) as pdf_designs_id, 
id, NOW(), id, NOW()
FROM tbltenants;
INSERT INTO `tbltenant_pdf_designs` (`uniquecode`, `tenant_id`, `type`, `pdf_designs_id`, `added_by`, `addedon`, `updated_by`, `updated_on`)
SELECT RAND(), id, 'detail_estimate', (SELECT id from tblpdf_designs where `type` = 'detail_estimate' and is_default = 1) as pdf_designs_id, 
id, NOW(), id, NOW()
FROM tbltenants;
INSERT INTO `tbltenant_pdf_designs` (`uniquecode`, `tenant_id`, `type`, `pdf_designs_id`, `added_by`, `addedon`, `updated_by`, `updated_on`)
SELECT RAND(), id, 'invoice', (SELECT id from tblpdf_designs where `type` = 'invoice' and is_default = 1) as pdf_designs_id, 
id, NOW(), id, NOW()
FROM tbltenants;
INSERT INTO `tbltenant_pdf_designs` (`uniquecode`, `tenant_id`, `type`, `pdf_designs_id`, `added_by`, `addedon`, `updated_by`, `updated_on`)
SELECT RAND(), id, 'post_inspection', (SELECT id from tblpdf_designs where `type` = 'post_inspection' and is_default = 1) as pdf_designs_id, 
id, NOW(), id, NOW()
FROM tbltenants;
INSERT INTO `tbltenant_pdf_designs` (`uniquecode`, `tenant_id`, `type`, `pdf_designs_id`, `added_by`, `addedon`, `updated_by`, `updated_on`)
SELECT RAND(), id, 'shopping_list', (SELECT id from tblpdf_designs where `type` = 'shopping_list' and is_default = 1) as pdf_designs_id, 
id, NOW(), id, NOW()
FROM tbltenants;