-- Teams Webhook API Database Schema
-- Creates logging table for tracking all API requests and webhook configurations

-- Table for storing webhook URLs (Power Automate incoming webhooks)
CREATE TABLE IF NOT EXISTS teams_webhooks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    description TEXT,
    webhook_url TEXT NOT NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_name (name),
    INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table for logging API requests
CREATE TABLE IF NOT EXISTS teams_webhook_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    source VARCHAR(255) NOT NULL,
    title VARCHAR(500),
    message_text TEXT,
    status VARCHAR(50) NOT NULL,
    request_ip VARCHAR(45),
    request_data JSON,
    auth_details JSON,
    is_authenticated TINYINT(1) DEFAULT 0,
    http_code INT,
    response_data JSON,
    processing_time DECIMAL(10, 4),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_source (source),
    INDEX idx_status (status),
    INDEX idx_created (created_at),
    INDEX idx_authenticated (is_authenticated)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default Tech Leaders webhook
INSERT INTO teams_webhooks (name, description, webhook_url, is_active) VALUES
('tech_leaders', 'Tech Leaders chat incoming webhook', 'https://default997c8e6f20c1426080cb7666671778.30.environment.api.powerplatform.com/powerautomate/automations/direct/workflows/26c3cd3102fd4f34890942f8579d1ad3/triggers/manual/paths/invoke?api-version=1&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=iVdnveyrrrG3-pt4DPtrBTQh1FxZUTfa-nKT5wVAoo8', 1)
ON DUPLICATE KEY UPDATE
    webhook_url = VALUES(webhook_url),
    description = VALUES(description);
