-- /nexus_scholar_php/database/modul6_reviewer_ecosystem_revisi.sql

-- 1. reviewer_profiles
CREATE TABLE reviewer_profiles (
    user_id INT PRIMARY KEY,
    affiliation VARCHAR(255) NULL,
    bio TEXT NULL,
    total_reviews INT DEFAULT 0,
    completion_rate DECIMAL(5,2) DEFAULT 0,
    avg_speed DECIMAL(8,2) DEFAULT 0,
    trust_level ENUM('Low', 'Medium', 'High') DEFAULT 'Medium',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. reviewer_expertise
CREATE TABLE reviewer_expertise (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    keyword VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. reviewer_assignments
-- (Terhubung ke submission dan user reviewer)
CREATE TABLE reviewer_assignments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    submission_id INT NOT NULL,
    reviewer_id INT NOT NULL,
    status ENUM('invited', 'accepted', 'declined', 'completed', 'canceled') DEFAULT 'invited',
    invited_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    responded_at TIMESTAMP NULL,
    due_date DATE NULL,
    FOREIGN KEY (submission_id) REFERENCES submissions(id) ON DELETE CASCADE,
    FOREIGN KEY (reviewer_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. review_reports
-- (Menyimpan komentar untuk editor dan author)
CREATE TABLE review_reports (
    id INT AUTO_INCREMENT PRIMARY KEY,
    assignment_id INT NOT NULL,
    recommendation ENUM('accept', 'minor_revision', 'major_revision', 'reject') NOT NULL,
    comments_for_author TEXT NOT NULL,
    comments_for_editor TEXT NULL,
    submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (assignment_id) REFERENCES reviewer_assignments(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. reviewer_scores
-- (Bisa diperbarui secara berkala)
CREATE TABLE reviewer_scores (
    user_id INT PRIMARY KEY,
    total_score INT DEFAULT 0,
    level ENUM('Novice', 'Intermediate', 'Expert', 'Master') DEFAULT 'Novice',
    last_calculated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. reviewer_badges
CREATE TABLE reviewer_badges (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT NULL,
    icon_url VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 7. reviewer_badge_logs
-- (Badge diberikan otomatis)
CREATE TABLE reviewer_badge_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    badge_id INT NOT NULL,
    awarded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (badge_id) REFERENCES reviewer_badges(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 8. review_helpfulness_ratings
CREATE TABLE review_helpfulness_ratings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    report_id INT NOT NULL,
    editor_id INT NOT NULL,
    rating INT NOT NULL COMMENT '1 to 5',
    feedback TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (report_id) REFERENCES review_reports(id) ON DELETE CASCADE,
    FOREIGN KEY (editor_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;