-- API-Schlüssel-Tabelle
CREATE TABLE IF NOT EXISTS api_keys (
    id INT AUTO_INCREMENT PRIMARY KEY,
    api_key VARCHAR(64) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NULL,
    last_used_at TIMESTAMP NULL,
    created_by INT NULL
);

-- API-Nutzungs-Tabelle
CREATE TABLE IF NOT EXISTS api_usage (
    id INT AUTO_INCREMENT PRIMARY KEY,
    api_key_id INT,
    endpoint VARCHAR(255) NOT NULL,
    method VARCHAR(10) NOT NULL,
    status_code INT NOT NULL,
    response_time FLOAT NOT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX (api_key_id)
);

-- Batch-Jobs-Tabelle
CREATE TABLE IF NOT EXISTS api_batch_jobs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    batch_id VARCHAR(36) NOT NULL UNIQUE,
    api_key_id INT,
    status VARCHAR(20) NOT NULL,
    total_items INT NOT NULL,
    processed_items INT DEFAULT 0,
    successful_items INT DEFAULT 0,
    failed_items INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP NULL,
    INDEX (api_key_id)
);

-- Batch-Items-Tabelle
CREATE TABLE IF NOT EXISTS api_batch_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    batch_id VARCHAR(36) NOT NULL,
    item_index INT NOT NULL,
    lerncode VARCHAR(20) NOT NULL,
    essay_text TEXT NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    submission_id VARCHAR(36) NULL,
    error_message TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    processed_at TIMESTAMP NULL,
    INDEX (batch_id)
);

-- Testschlüssel einfügen
INSERT INTO api_keys (api_key, name, description, active)
VALUES ('test_api_key_1', 'Test API-Schlüssel', 'Für Testzwecke erstellt', 1)
ON DUPLICATE KEY UPDATE name = 'Test API-Schlüssel', description = 'Für Testzwecke erstellt', active = 1;