--up
CREATE TYPE sync_status AS ENUM ('synced', 'error');
CREATE TYPE sync_type AS ENUM('manual', 'user_signup', 'subscription_update');

CREATE TABLE zoho_sync_status (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id integer REFERENCES users (id) ON UPDATE RESTRICT ON DELETE CASCADE, -- user id if sync only affects one user
    user_ids jsonb, -- array of user ids, if sync affects multiple users
    sync_type sync_type NOT NULL,
    sync_status sync_status NOT NULL,
    error_message text,
    created_at timestamp NOT NULL DEFAULT now()
);

COMMENT ON COLUMN zoho_sync_status.user_id IS 'User id if sync only affects one user';
COMMENT ON COLUMN zoho_sync_status.user_ids IS 'Array of integer user ids if sync affects multiple users';

--down

DROP TABLE zoho_sync_status;
DROP TYPE sync_status;
DROP TYPE sync_type;
