USE BeezifiIOM;

CREATE TABLE IF NOT EXISTS accounting_integrations (
    id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id        INT UNSIGNED NOT NULL,
    platform         ENUM('xero', 'quickbooks', 'beezifi') NOT NULL,
    access_token     TEXT NOT NULL,
    refresh_token    TEXT NOT NULL,
    token_expires_at TIMESTAMP NOT NULL,
    xero_tenant_id   VARCHAR(100) NULL,
    realm_id         VARCHAR(100) NULL,
    account_mapping  JSON NULL,
    status           ENUM('active', 'inactive', 'token_expired') DEFAULT 'active',
    last_sync_at     TIMESTAMP NULL,
    auto_sync        TINYINT(1) DEFAULT 1,
    created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_tenant (tenant_id),
    UNIQUE KEY uq_tenant_platform (tenant_id, platform),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS accounting_sync_log (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id           INT UNSIGNED NOT NULL,
    integration_id      INT UNSIGNED NOT NULL,
    iom_order_id        INT UNSIGNED NOT NULL,
    external_invoice_id VARCHAR(100) NULL,
    status              ENUM('pending', 'synced', 'error') DEFAULT 'pending',
    error_message       TEXT NULL,
    synced_at           TIMESTAMP NULL,
    created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tenant (tenant_id),
    INDEX idx_order (iom_order_id),
    UNIQUE KEY uq_order_integration (integration_id, iom_order_id),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (integration_id) REFERENCES accounting_integrations(id) ON DELETE CASCADE
) ENGINE=InnoDB;
