-- /nexus_scholar_php/database/modul4_ai_review.sql

-- 1. Table ai_analysis_jobs
CREATE TABLE ai_analysis_jobs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    submission_id INT NOT NULL,
    user_id INT NOT NULL, -- User who requested the analysis
    status ENUM('pending', 'processing', 'completed', 'failed') DEFAULT 'pending',
    provider VARCHAR(50) DEFAULT 'gemini',
    model VARCHAR(100) DEFAULT 'gemini-pro',
    token_usage INT NULL,
    error_message TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (submission_id) REFERENCES submissions(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Table ai_article_reviews
CREATE TABLE ai_article_reviews (
    id INT AUTO_INCREMENT PRIMARY KEY,
    job_id INT NOT NULL,
    submission_id INT NOT NULL,
    rejection_risk_score INT DEFAULT 0,
    editorial_readiness_score INT DEFAULT 0,
    structure_score INT DEFAULT 0,
    methodology_score INT DEFAULT 0,
    novelty_score INT DEFAULT 0,
    citation_score INT DEFAULT 0,
    academic_tone_score INT DEFAULT 0,
    raw_json_response TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (job_id) REFERENCES ai_analysis_jobs(id) ON DELETE CASCADE,
    FOREIGN KEY (submission_id) REFERENCES submissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Table ai_review_recommendations
CREATE TABLE ai_review_recommendations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    review_id INT NOT NULL,
    recommendation TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (review_id) REFERENCES ai_article_reviews(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Table ai_review_weaknesses
CREATE TABLE ai_review_weaknesses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    review_id INT NOT NULL,
    weakness TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (review_id) REFERENCES ai_article_reviews(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;