CREATE TABLE IF NOT EXISTS sales_cadences (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(160) NOT NULL,
    goal VARCHAR(190) NULL,
    status ENUM('draft', 'active', 'paused', 'archived') NOT NULL DEFAULT 'active',
    created_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_sales_cadences_name (tenant_id, name),
    INDEX idx_sales_cadences_tenant (tenant_id, status, created_at),
    CONSTRAINT fk_sales_cadences_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_sales_cadences_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sales_cadence_steps (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    cadence_id BIGINT UNSIGNED NOT NULL,
    step_order INT UNSIGNED NOT NULL DEFAULT 1,
    channel ENUM('task', 'call', 'email', 'whatsapp') NOT NULL DEFAULT 'task',
    title_template VARCHAR(190) NOT NULL,
    details_template TEXT NULL,
    offset_days INT UNSIGNED NOT NULL DEFAULT 0,
    template_id BIGINT UNSIGNED NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_sales_cadence_steps_tenant (tenant_id, cadence_id, step_order),
    CONSTRAINT fk_sales_cadence_steps_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_sales_cadence_steps_cadence FOREIGN KEY (cadence_id) REFERENCES sales_cadences(id) ON DELETE CASCADE,
    CONSTRAINT fk_sales_cadence_steps_template FOREIGN KEY (template_id) REFERENCES activity_templates(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sales_cadence_enrollments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    cadence_id BIGINT UNSIGNED NOT NULL,
    contact_id BIGINT UNSIGNED NOT NULL,
    owner_user_id BIGINT UNSIGNED NULL,
    status ENUM('active', 'paused', 'completed', 'stopped') NOT NULL DEFAULT 'active',
    started_at DATETIME NULL,
    last_activity_at DATETIME NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_sales_cadence_enrollments_tenant (tenant_id, status, created_at),
    CONSTRAINT fk_sales_cadence_enrollments_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_sales_cadence_enrollments_cadence FOREIGN KEY (cadence_id) REFERENCES sales_cadences(id) ON DELETE CASCADE,
    CONSTRAINT fk_sales_cadence_enrollments_contact FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE,
    CONSTRAINT fk_sales_cadence_enrollments_owner FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_sales_cadence_enrollments_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sales_cadence_enrollment_steps (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    enrollment_id BIGINT UNSIGNED NOT NULL,
    cadence_step_id BIGINT UNSIGNED NOT NULL,
    activity_id BIGINT UNSIGNED NULL,
    status ENUM('scheduled', 'done', 'cancelled', 'skipped') NOT NULL DEFAULT 'scheduled',
    scheduled_for DATETIME NULL,
    completed_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_sales_cadence_enrollment_steps_tenant (tenant_id, enrollment_id, status),
    CONSTRAINT fk_sales_cadence_enrollment_steps_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_sales_cadence_enrollment_steps_enrollment FOREIGN KEY (enrollment_id) REFERENCES sales_cadence_enrollments(id) ON DELETE CASCADE,
    CONSTRAINT fk_sales_cadence_enrollment_steps_step FOREIGN KEY (cadence_step_id) REFERENCES sales_cadence_steps(id) ON DELETE CASCADE,
    CONSTRAINT fk_sales_cadence_enrollment_steps_activity FOREIGN KEY (activity_id) REFERENCES sales_activities(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
