-- Aggie-server counterpart of webhook_subscriptions.sql — this table was written for local dev
-- (db_abos_v0.1) but never created here, which is exactly why WorkitemService.createWorkitem
-- (and updateWorkitem's status-change path, AutomationRuleService's WEBHOOK action, and
-- EmailExtractionService) started throwing "Table 'stacie_Aggie_v1.0.dim_webhook_subscriptions'
-- doesn't exist" — confirmed live via System Logs, every task-creation attempt failed with a 500
-- because of this missing table, not because of any form/payload issue.
-- Idempotent — safe to re-run.
-- Deploy: mysql -u <user> -p 'stacie_Aggie_v1.0' < db/aggie_webhook_subscriptions.sql

USE `stacie_Aggie_v1.0`;

CREATE TABLE IF NOT EXISTS dim_webhook_subscriptions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    target_url VARCHAR(1000) NOT NULL,
    secret VARCHAR(255) NOT NULL COMMENT 'HMAC-SHA256 signing key — never returned by the API after creation',
    event_types VARCHAR(500) NOT NULL COMMENT 'CSV, e.g. "workitem.status_changed,workitem.created"',
    is_enabled TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by INT NULL,
    updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    updated_by INT NULL,
    is_deleted TINYINT(1) NOT NULL DEFAULT 0,
    INDEX idx_webhook_subscriptions_tenant (tenant_id, is_deleted, is_enabled)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS fact_webhook_deliveries (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    subscription_id BIGINT NOT NULL,
    event_type VARCHAR(128) NOT NULL,
    attempt INT NOT NULL DEFAULT 1,
    response_status INT NULL,
    success TINYINT(1) NOT NULL DEFAULT 0,
    error_message VARCHAR(1000) NULL,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    INDEX idx_webhook_deliveries_subscription (tenant_id, subscription_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;