-- Migration 0009: create_automation
-- Auto-generated from database/schema.sql by database/build_migrations.php
-- Do not hand-edit this file — edit schema.sql and re-run that script instead.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- =====================================================================
-- SECTION 8 — AUTOMATION
-- =====================================================================

CREATE TABLE IF NOT EXISTS automation_workflows (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name          VARCHAR(191)    NOT NULL,
    trigger_type  VARCHAR(80)     NOT NULL COMMENT 'lead_created, invoice_overdue, appointment_completed...',
    trigger_config JSON           NULL,
    is_active     TINYINT(1)      NOT NULL DEFAULT 1,
    created_by    BIGINT UNSIGNED NULL,
    updated_by    BIGINT UNSIGNED NULL,
    created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at    DATETIME        NULL,
    KEY idx_automation_workflows_trigger (trigger_type),
    CONSTRAINT fk_automation_workflows_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_automation_workflows_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS automation_steps (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    workflow_id  BIGINT UNSIGNED NOT NULL,
    step_type    ENUM('send_email','wait','condition','assign_owner','update_field','webhook') NOT NULL,
    config       JSON            NULL COMMENT 'Step-specific settings, e.g. {"template_id":12,"delay_hours":24}',
    sort_order   SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    parent_branch VARCHAR(20)    NULL COMMENT 'yes/no branch label for condition steps',
    KEY idx_automation_steps_workflow (workflow_id, sort_order),
    CONSTRAINT fk_automation_steps_workflow FOREIGN KEY (workflow_id) REFERENCES automation_workflows(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS automation_runs (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    workflow_id  BIGINT UNSIGNED NOT NULL,
    entity_type  VARCHAR(40)     NOT NULL,
    entity_id    BIGINT UNSIGNED NOT NULL,
    status       ENUM('running','completed','failed','cancelled') NOT NULL DEFAULT 'running',
    current_step_id BIGINT UNSIGNED NULL,
    started_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at DATETIME        NULL,
    error_message VARCHAR(500)   NULL,
    KEY idx_automation_runs_workflow (workflow_id),
    KEY idx_automation_runs_entity (entity_type, entity_id),
    CONSTRAINT fk_automation_runs_workflow FOREIGN KEY (workflow_id) REFERENCES automation_workflows(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_automation_runs_step FOREIGN KEY (current_step_id) REFERENCES automation_steps(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


SET FOREIGN_KEY_CHECKS = 1;
