-- Track explicit Hub -> vTiger case and time exports so retries stay idempotent.
CREATE TABLE IF NOT EXISTS hub_case_vtiger_exports (
    hub_sag_id INTEGER PRIMARY KEY REFERENCES sag_sager(id) ON DELETE RESTRICT,
    vtiger_case_id VARCHAR(80) NOT NULL UNIQUE,
    ticket_number VARCHAR(80),
    status VARCHAR(20) NOT NULL DEFAULT 'partial' CHECK (status IN ('partial', 'complete')),
    exported_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_error TEXT
);

CREATE TABLE IF NOT EXISTS hub_time_vtiger_exports (
    hub_time_id INTEGER PRIMARY KEY REFERENCES tmodule_times(id) ON DELETE RESTRICT,
    hub_sag_id INTEGER NOT NULL REFERENCES sag_sager(id) ON DELETE RESTRICT,
    vtiger_timelog_id VARCHAR(80) NOT NULL UNIQUE,
    exported_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_hub_time_vtiger_exports_case
    ON hub_time_vtiger_exports(hub_sag_id, exported_at DESC);