CREATE TABLE tbltenant_qb_sync(
    id BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    tenant_id BIGINT UNSIGNED NOT NULL,
    qb_oauth_resp_json LONGTEXT NOT NULL,
    ids_realm_id VARCHAR(260) NOT NULL,
    last_sync_timestamp DATETIME NULL DEFAULT NULL,
    last_ids_timestamp DATETIME NULL DEFAULT NULL,
    qb_version ENUM('us','global') NULL DEFAULT NULL,
    sync_preferences_json LONGTEXT NULL DEFAULT NULL,
    addedon DATETIME,
    modifiedon DATETIME
);

ALTER TABLE `tbltenant_qb_sync` ADD `is_consent_needed` TINYINT(1) DEFAULT 0 AFTER `qb_oauth_resp_json`;

CREATE TABLE tbltenant_qb_sync_jobs(
    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,
    staff_id BIGINT UNSIGNED NOT NULL,
    FOREIGN KEY (staff_id) REFERENCES tblstaff (staffid) ON DELETE CASCADE ON UPDATE CASCADE,
    sync_pref TEXT DEFAULT NULL,
    `status` ENUM ('pending', 'in_progress', 'completed', 'failed') NOT NULL DEFAULT 'pending',
    data_used_for_sync LONGTEXT DEFAULT NULL,
    sync_job_msg MEDIUMTEXT DEFAULT NULL,
    started_on DATETIME NULL DEFAULT NULL,
    ended_on DATETIME NULL DEFAULT NULL,
    added_on DATETIME NOT NULL
);

CREATE TABLE tbltenant_sync_details(
    id BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    tenant_id BIGINT UNSIGNED NOT NULL,
    FOREIGN KEY (tenant_id) REFERENCES tbltenants (id) ON DELETE CASCADE ON UPDATE CASCADE,
    staff_id BIGINT UNSIGNED NOT NULL,
    FOREIGN KEY (staff_id) REFERENCES tblstaff (staffid) ON DELETE CASCADE ON UPDATE CASCADE,
    rel_type ENUM('client', 'property', 'product_service', 'invoice', 'estimate') NOT NULL,
    rel_id BIGINT UNSIGNED NOT NULL,
    qb_id VARCHAR(500) DEFAULT NULL,
    qb_sync_status TINYINT(1) DEFAULT 0 COMMENT '(0=not synced, 1=queued for sync, 2=synced already)',
    qb_synced_on DATETIME DEFAULT NULL,
    tenant_qb_sync_job_id BIGINT UNSIGNED DEFAULT NULL,
    FOREIGN KEY (tenant_qb_sync_job_id) REFERENCES tbltenant_qb_sync_jobs (id) ON DELETE CASCADE ON UPDATE CASCADE
);

-- 2021-05-19
ALTER TABLE `tbltenant_sync_details` CHANGE `rel_id` `rel_id` VARCHAR(191) NOT NULL;

ALTER TABLE `tbltenant_sync_details` CHANGE `rel_type` `rel_type` ENUM('client','property','product_service','invoice','estimate','payment','payment_method', 'timelog', 'staff') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;

ALTER TABLE `tbltenant_qb_sync` ADD `company_info` LONGTEXT DEFAULT NULL AFTER `qb_oauth_resp_json`;

ALTER TABLE `tbltenant_qb_sync` ADD `company_preference` LONGTEXT DEFAULT NULL AFTER `company_info`;