bmc_hub/migrations/1064_workflow_integration.sql

-- Shared workflow capture is disabled until explicitly enabled by local configuration.
CREATE TABLE IF NOT EXISTS bmc_workflow_runtime (
    id INTEGER PRIMARY KEY CHECK(id = 1),
    enabled BOOLEAN NOT NULL DEFAULT FALSE,
    mode TEXT NOT NULL DEFAULT 'shadow' CHECK(mode IN ('shadow','active')),
    customer_ids JSONB NOT NULL DEFAULT '[]'::jsonb,
    internal_enabled BOOLEAN NOT NULL DEFAULT FALSE,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
ALTER TABLE bmc_workflow_runtime ADD COLUMN IF NOT EXISTS internal_enabled BOOLEAN NOT NULL DEFAULT FALSE;
INSERT INTO bmc_workflow_runtime(id) VALUES (1) ON CONFLICT DO NOTHING;
ALTER TABLE tmodule_times ADD COLUMN IF NOT EXISTS workflow_revision BIGINT NOT NULL DEFAULT 0;
ALTER TABLE tticket_prepaid_transactions ADD COLUMN IF NOT EXISTS workflow_time_entry_id INTEGER REFERENCES tmodule_times(id);
CREATE UNIQUE INDEX IF NOT EXISTS prepaid_workflow_entry_once ON tticket_prepaid_transactions(workflow_time_entry_id) WHERE workflow_time_entry_id IS NOT NULL;
CREATE TABLE IF NOT EXISTS bmc_workflow_settlements (
    time_id INTEGER PRIMARY KEY REFERENCES tmodule_times(id),
    method TEXT NOT NULL,
    decision_id BIGINT,
    draft_id INTEGER REFERENCES ordre_drafts(id),
    transaction_id INTEGER REFERENCES tticket_prepaid_transactions(id),
    agreement_id INTEGER REFERENCES customer_fixed_price_agreements(id),
    hours NUMERIC(10,4) NOT NULL,
    actor_id INTEGER,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE OR REPLACE FUNCTION bmc_capture_time_workflow() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    IF TG_OP='UPDATE' AND (NEW.description,NEW.customer_id,NEW.sag_id,NEW.original_hours,NEW.worked_date,NEW.faktisk_tid_min,NEW.work_type)
       IS NOT DISTINCT FROM (OLD.description,OLD.customer_id,OLD.sag_id,OLD.original_hours,OLD.worked_date,OLD.faktisk_tid_min,OLD.work_type) THEN
        RETURN NEW;
    END IF;
    NEW.workflow_revision := CASE WHEN TG_OP='INSERT' THEN 1 ELSE OLD.workflow_revision+1 END;
    IF EXISTS(SELECT 1 FROM bmc_workflow_runtime WHERE id=1 AND enabled) AND NEW.vtiger_id IS NULL THEN
        INSERT INTO bmc_workflow_events(id,event_key,trigger,entity_type,entity_id,revision,context,chain)
        VALUES(gen_random_uuid()::text,'time:'||NEW.id||':'||NEW.workflow_revision,
            CASE WHEN TG_OP='INSERT' THEN 'time.created' ELSE 'time.updated' END,
            'time_entry',NEW.id::text,NEW.workflow_revision,jsonb_build_object('time_id',NEW.id),COALESCE(NULLIF(current_setting('bmc.workflow_chain',true),'')::jsonb,'[]'::jsonb))
        ON CONFLICT(event_key) DO NOTHING;
    END IF;
    RETURN NEW;
END $$;
DROP TRIGGER IF EXISTS bmc_time_workflow_capture ON tmodule_times;
CREATE TRIGGER bmc_time_workflow_capture BEFORE INSERT OR UPDATE ON tmodule_times FOR EACH ROW EXECUTE FUNCTION bmc_capture_time_workflow();
-- Case and tag changes invalidate affected un-settled registrations, using the same
-- transactional time trigger rather than a second queue or duplicate event publisher.
CREATE OR REPLACE FUNCTION bmc_invalidate_case_times() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    IF EXISTS(SELECT 1 FROM bmc_workflow_runtime WHERE id=1 AND enabled) THEN
        IF TG_OP='UPDATE' AND (NEW.customer_id,NEW.beskrivelse,NEW.status) IS DISTINCT FROM (OLD.customer_id,OLD.beskrivelse,OLD.status) THEN
            UPDATE tmodule_times SET workflow_revision=workflow_revision+1 WHERE sag_id=NEW.id AND status<>'billed' AND economy_order_draft_id IS NULL;
            INSERT INTO bmc_workflow_events(id,event_key,trigger,entity_type,entity_id,revision,context,chain)
            SELECT gen_random_uuid()::text,'time:'||id||':'||workflow_revision,'time.updated','time_entry',id::text,workflow_revision,jsonb_build_object('time_id',id),COALESCE(NULLIF(current_setting('bmc.workflow_chain',true),'')::jsonb,'[]'::jsonb)
            FROM tmodule_times WHERE sag_id=NEW.id AND status<>'billed' AND economy_order_draft_id IS NULL
            ON CONFLICT(event_key) DO NOTHING;
        END IF;
    END IF;
    RETURN NEW;
END $$;
DROP TRIGGER IF EXISTS bmc_case_workflow_capture ON sag_sager;
CREATE TRIGGER bmc_case_workflow_capture AFTER UPDATE ON sag_sager FOR EACH ROW EXECUTE FUNCTION bmc_invalidate_case_times();
CREATE OR REPLACE FUNCTION bmc_invalidate_tag_times() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE target_type TEXT; target_id INTEGER;
BEGIN
    target_type:=CASE WHEN TG_OP='DELETE' THEN OLD.entity_type ELSE NEW.entity_type END;
    target_id:=CASE WHEN TG_OP='DELETE' THEN OLD.entity_id ELSE NEW.entity_id END;
    IF EXISTS(SELECT 1 FROM bmc_workflow_runtime WHERE id=1 AND enabled) AND target_type IN ('case','time_entry') THEN
        UPDATE tmodule_times SET workflow_revision=workflow_revision+1
        WHERE ((target_type='case' AND sag_id=target_id) OR (target_type='time_entry' AND id=target_id))
          AND status<>'billed' AND economy_order_draft_id IS NULL;
        INSERT INTO bmc_workflow_events(id,event_key,trigger,entity_type,entity_id,revision,context,chain)
        SELECT gen_random_uuid()::text,'time:'||id||':'||workflow_revision,'time.updated','time_entry',id::text,workflow_revision,jsonb_build_object('time_id',id),COALESCE(NULLIF(current_setting('bmc.workflow_chain',true),'')::jsonb,'[]'::jsonb)
        FROM tmodule_times WHERE ((target_type='case' AND sag_id=target_id) OR (target_type='time_entry' AND id=target_id))
          AND status<>'billed' AND economy_order_draft_id IS NULL ON CONFLICT(event_key) DO NOTHING;
    END IF;
    IF TG_OP='DELETE' THEN RETURN OLD; ELSE RETURN NEW; END IF;
END $$;
DROP TRIGGER IF EXISTS bmc_tag_workflow_capture ON entity_tags;
CREATE TRIGGER bmc_tag_workflow_capture AFTER INSERT OR DELETE ON entity_tags FOR EACH ROW EXECUTE FUNCTION bmc_invalidate_tag_times();
-- Public event adapters reuse the same holdable queue across Hub modules.
CREATE OR REPLACE FUNCTION bmc_capture_module_workflow() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE payload JSONB; previous JSONB; etype TEXT; eid TEXT; event_trigger TEXT;
BEGIN
    IF NOT EXISTS(SELECT 1 FROM bmc_workflow_runtime WHERE id=1 AND enabled) THEN
        IF TG_OP='DELETE' THEN RETURN OLD; ELSE RETURN NEW; END IF;
    END IF;
    payload:=CASE WHEN TG_OP='DELETE' THEN to_jsonb(OLD) ELSE to_jsonb(NEW) END;
    previous:=CASE WHEN TG_OP='UPDATE' THEN to_jsonb(OLD) ELSE '{}'::jsonb END;
    etype:=TG_ARGV[0];
    -- Match the mail module's outgoing classification. Sending, draft and
    -- outbox records also represent authored mail, never incoming mail.
    IF etype='mail' AND (
        LOWER(COALESCE(payload->>'folder','')) LIKE 'sent%'
        OR LOWER(COALESCE(payload->>'folder','')) LIKE 'outbox%'
        OR LOWER(COALESCE(payload->>'folder','')) LIKE 'draft%'
        OR LOWER(COALESCE(payload->>'status','')) IN ('sent','sending','draft')
        OR LOWER(COALESCE(payload->>'direction','')) IN ('outbound','outgoing')
    ) THEN RETURN NEW; END IF;
    eid:=payload->>'id';
    event_trigger:=CASE WHEN TG_OP='INSERT' THEN TG_ARGV[1] WHEN TG_OP='UPDATE' THEN TG_ARGV[2] ELSE TG_ARGV[3] END;
    IF event_trigger IS NULL OR event_trigger='' THEN
        IF TG_OP='DELETE' THEN RETURN OLD; ELSE RETURN NEW; END IF;
    END IF;
    IF etype='case' AND TG_OP='UPDATE' AND payload->>'status'='Lukket' AND previous->>'status' IS DISTINCT FROM 'Lukket' THEN
        event_trigger:='case.closed';
    END IF;
    IF TG_OP='UPDATE' AND payload-ARRAY['updated_at','last_synced_at'] IS NOT DISTINCT FROM previous-ARRAY['updated_at','last_synced_at'] THEN RETURN NEW; END IF;
    IF etype='tag_assignment' THEN
        etype:=payload->>'entity_type';eid:=payload->>'entity_id';
    END IF;
    INSERT INTO bmc_workflow_events(id,event_key,trigger,entity_type,entity_id,context,chain)
    VALUES(gen_random_uuid()::text,event_trigger||':'||eid||':'||gen_random_uuid()::text,
        event_trigger,etype,eid,payload,COALESCE(NULLIF(current_setting('bmc.workflow_chain',true),'')::jsonb,'[]'::jsonb));
    IF TG_OP='DELETE' THEN RETURN OLD; ELSE RETURN NEW; END IF;
END $$;
DROP TRIGGER IF EXISTS bmc_case_generic_workflow ON sag_sager;
CREATE TRIGGER bmc_case_generic_workflow AFTER INSERT OR UPDATE ON sag_sager FOR EACH ROW EXECUTE FUNCTION bmc_capture_module_workflow('case','case.created','case.updated','');
DROP TRIGGER IF EXISTS bmc_tags_generic_workflow ON entity_tags;
CREATE TRIGGER bmc_tags_generic_workflow AFTER INSERT OR DELETE ON entity_tags FOR EACH ROW EXECUTE FUNCTION bmc_capture_module_workflow('tag_assignment','tags.added','','tags.removed');
DROP TRIGGER IF EXISTS bmc_customer_generic_workflow ON customers;
CREATE TRIGGER bmc_customer_generic_workflow AFTER INSERT OR UPDATE ON customers FOR EACH ROW EXECUTE FUNCTION bmc_capture_module_workflow('customer','customer.created','customer.updated','');
DROP TRIGGER IF EXISTS bmc_mail_generic_workflow ON email_messages;
CREATE TRIGGER bmc_mail_generic_workflow AFTER INSERT ON email_messages FOR EACH ROW EXECUTE FUNCTION bmc_capture_module_workflow('mail','mail.received','','');
DROP TRIGGER IF EXISTS bmc_task_generic_workflow ON sag_todo_steps;
CREATE TRIGGER bmc_task_generic_workflow AFTER INSERT OR UPDATE ON sag_todo_steps FOR EACH ROW EXECUTE FUNCTION bmc_capture_module_workflow('task','task.created','task.updated','');