-- Lobster execution unify: schema extension

ALTER TABLE company_tag_template_binding
    ADD COLUMN exclude_tags VARCHAR(2000) NULL COMMENT 'exclude tag codes, comma separated' AFTER match_condition,
    ADD COLUMN target_channels VARCHAR(500) NULL COMMENT 'target channels QW,WX,IM empty=all' AFTER exclude_tags;

ALTER TABLE company_lobster_tag_user_rel
    ADD COLUMN channel_type VARCHAR(30) NOT NULL DEFAULT 'QW' COMMENT 'channel type' AFTER template_id,
    ADD COLUMN channel_contact_id BIGINT NULL COMMENT 'channel contact id' AFTER channel_type,
    ADD COLUMN channel_user_id VARCHAR(128) NULL COMMENT 'channel user id' AFTER channel_contact_id,
    ADD COLUMN channel_context_json TEXT NULL COMMENT 'channel context json' AFTER channel_user_id;

UPDATE company_lobster_tag_user_rel
SET channel_contact_id = external_contact_id, channel_type = 'QW'
WHERE channel_contact_id IS NULL AND external_contact_id IS NOT NULL;

ALTER TABLE company_workflow_lobster_task
    ADD COLUMN channel_type VARCHAR(30) NOT NULL DEFAULT 'QW' COMMENT 'channel type' AFTER external_contact_id,
    ADD COLUMN channel_contact_id BIGINT NULL COMMENT 'channel contact id' AFTER channel_type,
    ADD COLUMN channel_context_json TEXT NULL COMMENT 'channel context json' AFTER channel_contact_id;

UPDATE company_workflow_lobster_task
SET channel_contact_id = external_contact_id, channel_type = 'QW'
WHERE channel_contact_id IS NULL AND external_contact_id IS NOT NULL;

ALTER TABLE lobster_workflow_instance
    ADD COLUMN binding_id BIGINT NULL COMMENT 'tag binding id' AFTER workflow_id;

CREATE INDEX idx_lobster_rel_channel_contact
    ON company_lobster_tag_user_rel (company_id, channel_type, channel_contact_id, del_flag);

CREATE INDEX idx_lobster_instance_binding
    ON lobster_workflow_instance (company_id, binding_id, del_flag);