CREATE TABLE tblterm_contracts (
    id BIGINT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
    tenant_id BIGINT NOT NULL,
    title VARCHAR(1000) NOT NULL,
    description LONGTEXT NOT NULL,
    is_default_for_invoice TINYINT(1) DEFAULT 0,
    is_default_for_estimate TINYINT(1) DEFAULT 0,
    added_on DATETIME
);

CREATE TABLE tblterm_contracts_static (
    id BIGINT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
    title VARCHAR(1000) NOT NULL,
    description LONGTEXT NOT NULL,
    is_default_for_invoice TINYINT(1) DEFAULT 0,
    is_default_for_estimate TINYINT(1) DEFAULT 0,
    added_on DATETIME
);

INSERT INTO `tblterm_contracts_static` (`id`, `title`, `description`, `is_default_for_invoice`, `is_default_for_estimate`, `added_on`) VALUES (NULL, 'Generic', 'By signing this document, the customer agrees to the services and conditions outlined in this document.', '1', '1', NOW());

ALTER TABLE tblestimates ADD term_contracts_id BIGINT UNSIGNED NULL DEFAULT NULL AFTER final_supply_charges;
ALTER TABLE tblestimates ADD term_contracts_title VARCHAR(1000) NULL DEFAULT NULL AFTER term_contracts_id;
ALTER TABLE tblestimates ADD term_contracts_description LONGTEXT NULL DEFAULT NULL AFTER term_contracts_title;

ALTER TABLE tblinvoices ADD term_contracts_id BIGINT UNSIGNED NULL DEFAULT NULL AFTER po_number;
ALTER TABLE tblinvoices ADD term_contracts_title VARCHAR(1000) NULL DEFAULT NULL AFTER term_contracts_id;
ALTER TABLE tblinvoices ADD term_contracts_description LONGTEXT NULL DEFAULT NULL AFTER term_contracts_title;

ALTER TABLE tblestimates ADD FOREIGN KEY (term_contracts_id) REFERENCES tblterm_contracts (id) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE tblinvoices ADD FOREIGN KEY (term_contracts_id) REFERENCES tblterm_contracts (id) ON DELETE SET NULL ON UPDATE CASCADE;

INSERT INTO `tblplan_features` (`id`, `name`, `codename`, `description`, `type`, `added_on`) VALUES (NULL, 'Term Contracts', 'term_contracts_module', 'Term Contracts', 'allow', NOW());

-- NOT TO RUN BECAUSE ID MIGHT BE SOMETHING ELSE ON LIVE
INSERT INTO `tblplan_features_value` (`id`, `plans_id`, `plans_features_id`, `value`, `other_data`, `added_on`) VALUES (NULL, '9', '73', '0', NULL, NOW());
INSERT INTO `tblplan_features_value` (`id`, `plans_id`, `plans_features_id`, `value`, `other_data`, `added_on`) VALUES (NULL, '10', '73', '1', NULL, NOW());
INSERT INTO `tblplan_features_value` (`id`, `plans_id`, `plans_features_id`, `value`, `other_data`, `added_on`) VALUES (NULL, '11', '73', '1', NULL, NOW());
INSERT INTO `tblplan_features_value` (`id`, `plans_id`, `plans_features_id`, `value`, `other_data`, `added_on`) VALUES (NULL, '12', '73', '1', '{\"outside_message\":\"Contracts is a PRO feature. In order to attach contract to your estimate\\/invoice, upgrade to pro.\",\"inside_message\":\"Contracts is a PRO feature. In order to attach contract to your estimate\\/invoice, upgrade to pro.\",\"tag_color\":\"#eb0e0e\",\"text_color\":\"#FFFFFF\",\"tag_title\":\"PRO\",\"link\":\"https:\\/\\/dev.my.contractorplus.app\",\"count\":\"0\"}', NOW());

-- NOT TO RUN FOR NOW
INSERT INTO tblterm_contracts (`tenant_id`, `title`, `description`, `is_default_for_invoice`, `is_default_for_estimate`, `added_on`) SELECT t.id, c.title, c.description, c.is_default_for_invoice, c.is_default_for_estimate, c.added_on
FROM tbltenants t
JOIN tblterm_contracts_static c ON 1=1
ORDER BY t.id;