CREATE TABLE tblsms_templates (
	id BIGINT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
    tenant_id BIGINT UNSIGNED NOT NULL,
    FOREIGN KEY (tenant_id) REFERENCES tbltenants(id) ON DELETE CASCADE ON UPDATE CASCADE,
    type ENUM ('estimate', 'invoice', 'post_inspection') NOT NULL,
    template TEXT NOT NULL,
    created_on DATETIME NOT NULL,
    updated_on DATETIME NULL DEFAULT NULL
);

CREATE TABLE tblsms_templates_static (
	id BIGINT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
    type ENUM ('estimate', 'invoice', 'post_inspection') NOT NULL,
    template TEXT NOT NULL
);

INSERT INTO `tblsms_templates_static` (`id`, `type`, `template`) VALUES (NULL, 'estimate', "We're excited about the possibility of working with you. Your estimate can be viewed here: {estimate_link}");
INSERT INTO `tblsms_templates_static` (`id`, `type`, `template`) VALUES (NULL, 'invoice', 'Thanks for your business! Your invoice can be viewed here: {invoice_link}');
INSERT INTO `tblsms_templates_static` (`id`, `type`, `template`) VALUES (NULL, 'post_inspection', 'Your work is completed. You can review the Post Inspection report here: {post_inspection_link}');

INSERT INTO tblsms_templates (tenant_id, type, template, created_on, updated_on)
SELECT t.id, s.type, s.template, NOW(), NOW()
FROM tbltenants t
JOIN tblsms_templates_static s ON 1 = 1;