-- Migration 016: Visual workflow engine
-- Phase 15  -  GHL-compete feature

CREATE TABLE IF NOT EXISTS workflow_definitions (
    id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id        BIGINT UNSIGNED NOT NULL,
    name          VARCHAR(120) NOT NULL,
    trigger_type  ENUM(
        'lead_created',
        'lead_status_changed',
        'campaign_sent',
        'campaign_opened',
        'campaign_clicked',
        'form_submitted',
        'time_delay',
        'manual'
    ) NOT NULL DEFAULT 'manual',
    status        ENUM('draft','active','paused') NOT NULL DEFAULT 'draft',
    run_count     INT UNSIGNED NOT NULL DEFAULT 0,
    created_by    BIGINT UNSIGNED NULL,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_org_status (org_id, status),
    KEY idx_trigger (trigger_type, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS workflow_nodes (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    workflow_id BIGINT UNSIGNED NOT NULL,
    node_type   ENUM('trigger','condition','action','delay') NOT NULL,
    config_json TEXT NOT NULL,
    position_x  INT NOT NULL DEFAULT 0,
    position_y  INT NOT NULL DEFAULT 0,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_workflow (workflow_id),
    CONSTRAINT fk_wn_workflow FOREIGN KEY (workflow_id)
        REFERENCES workflow_definitions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS workflow_edges (
    id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    workflow_id   BIGINT UNSIGNED NOT NULL,
    from_node_id  BIGINT UNSIGNED NOT NULL,
    to_node_id    BIGINT UNSIGNED NOT NULL,
    condition_label VARCHAR(120) NULL,
    PRIMARY KEY (id),
    KEY idx_workflow (workflow_id),
    CONSTRAINT fk_we_workflow  FOREIGN KEY (workflow_id)
        REFERENCES workflow_definitions(id) ON DELETE CASCADE,
    CONSTRAINT fk_we_from FOREIGN KEY (from_node_id)
        REFERENCES workflow_nodes(id) ON DELETE CASCADE,
    CONSTRAINT fk_we_to   FOREIGN KEY (to_node_id)
        REFERENCES workflow_nodes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS workflow_runs (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    workflow_id BIGINT UNSIGNED NOT NULL,
    org_id      BIGINT UNSIGNED NOT NULL,
    context_json TEXT NOT NULL,
    status      ENUM('running','completed','failed') NOT NULL DEFAULT 'running',
    started_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    finished_at DATETIME NULL,
    error_msg   TEXT NULL,
    PRIMARY KEY (id),
    KEY idx_workflow (workflow_id),
    CONSTRAINT fk_wr_workflow FOREIGN KEY (workflow_id)
        REFERENCES workflow_definitions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
