CREATE TABLE IF NOT EXISTS integrations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    type VARCHAR(32) NOT NULL,
    name VARCHAR(128) NOT NULL,
    base_url VARCHAR(255) NOT NULL,
    api_key_encrypted TEXT NULL,
    timeout_seconds SMALLINT UNSIGNED NOT NULL DEFAULT 10,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    last_test_status VARCHAR(16) NULL,
    last_test_http_code SMALLINT UNSIGNED NULL,
    last_test_message VARCHAR(255) NULL,
    last_test_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY integrations_type_name_unique (type, name),
    KEY integrations_type_active_idx (type, is_active),
    KEY integrations_last_test_at_idx (last_test_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS integration_test_logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    integration_id INT UNSIGNED NOT NULL,
    status VARCHAR(16) NOT NULL,
    http_code SMALLINT UNSIGNED NULL,
    message VARCHAR(255) NOT NULL,
    endpoint_url VARCHAR(255) NULL,
    tested_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY integration_test_logs_integration_idx (integration_id),
    KEY integration_test_logs_tested_at_idx (tested_at),
    CONSTRAINT integration_test_logs_integration_fk
        FOREIGN KEY (integration_id) REFERENCES integrations(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;