# Desain Database Global MySQL (NexusScholar)

Sesuai dengan batasan *Shared Hosting* MySQL/MariaDB, database ini dirancang agar sederhana (tidak over-engineered), rasional, mengandalkan Engine InnoDB untuk relasi Foreign Key (Cascade Delete), dan performa optimal dengan kombinasi Indeks.

## Pembagian Prioritas Tabel

*   **[MVP 1.0] Core Foundation:** `users`, `journals`, `articles`, `article_authors`, `article_files`, `editorial_logs`
*   **[MVP 2.0] Review & Publish:** `issues`, `reviews`, `ai_reports`
*   **[Advanced] Ekosistem:** `analytics_logs`, `user_profiles`, `user_specializations`, `discussions`

---

## 1. Daftar Tabel, Field, PK & FK
### A. Tabel MVP 1.0 (Core System & Submission)
1.  **`users`**
    *   **PK:** `id` (INT, Auto Increment)
    *   **Fields:** `name`, `email` (UNIQUE), `password_hash`, `role` (ENUM), `created_at`
2.  **`journals`**
    *   **PK:** `id`
    *   **Fields:** `title`, `description`, `issn`, `guidelines`, `created_at`
3.  **`articles`**
    *   **PK:** `id`
    *   **FK:** `journal_id` -> `journals.id`, `issue_id` -> `issues.id` (Nullable), `submitter_id` -> `users.id`
    *   **Fields:** `title`, `abstract`, `keywords`, `status` (ENUM), `created_at`, `updated_at`
4.  **`article_authors`**
    *   **PK:** `id`
    *   **FK:** `article_id` -> `articles.id`
    *   **Fields:** `name`, `email`, `affiliation`, `is_corresponding` (BOOLEAN)
5.  **`article_files`**
    *   **PK:** `id`
    *   **FK:** `article_id` -> `articles.id`
    *   **Fields:** `file_path`, `file_type` (ENUM), `uploaded_at`
6.  **`editorial_logs`**
    *   **PK:** `id`
    *   **FK:** `article_id` -> `articles.id`, `user_id` -> `users.id`
    *   **Fields:** `action` (VARCHAR), `note` (TEXT), `created_at`

### B. Tabel MVP 2.0 (Peer Review & Publication)
7.  **`issues`**
    *   **PK:** `id`
    *   **FK:** `journal_id` -> `journals.id`
    *   **Fields:** `volume`, `number`, `year`, `is_published` (BOOLEAN), `published_at`
8.  **`reviews`**
    *   **PK:** `id`
    *   **FK:** `article_id` -> `articles.id`, `reviewer_id` -> `users.id`
    *   **Fields:** `status` (ENUM), `recommendation` (ENUM), `comments_author` (TEXT), `comments_editor` (TEXT), `due_date`, `completed_at`
9.  **`ai_reports`**
    *   **PK:** `id`
    *   **FK:** `article_id` -> `articles.id`
    *   **Fields:** `summary` (TEXT), `plagiarism_score` (FLOAT), `format_score` (FLOAT), `raw_response` (JSON/TEXT)

### C. Tabel Advanced (Ecosystem)
10. **`analytics_logs`**
    *   **PK:** `id`
    *   **FK:** `article_id` -> `articles.id`
    *   **Fields:** `event_type` (ENUM), `ip_address`, `created_at`
11. **`user_profiles`**
    *   **PK:** `id`
    *   **FK:** `user_id` -> `users.id`
    *   **Fields:** `bio`, `orcid`, `affiliation`, `google_scholar_link`
12. **`user_specializations`**
    *   **PK:** `id`
    *   **FK:** `user_id` -> `users.id`
    *   **Fields:** `keyword` (VARCHAR)
13. **`discussions`**
    *   **PK:** `id`
    *   **FK:** `article_id` -> `articles.id`, `user_id` -> `users.id`
    *   **Fields:** `message` (TEXT), `created_at`

---

## 2. Status String/ENUM yang Dibutuhkan
*   **users.role**: `'superadmin', 'editor', 'reviewer', 'author', 'reader'`
*   **articles.status**: `'draft', 'submitted', 'desk_review', 'under_review', 'revision_required', 'accepted', 'rejected', 'published'`
*   **article_files.file_type**: `'manuscript', 'cover_letter', 'supplementary', 'galley_proof'`
*   **reviews.status**: `'assigned', 'accepted', 'declined', 'completed'`
*   **reviews.recommendation**: `'accept', 'minor_revision', 'major_revision', 'reject'`
*   **analytics_logs.event_type**: `'view', 'download'`

---

## 3. Index Prioritas (Performa Query Cepat di Shared Hosting)
Untuk menghindari _table scan_ yang melambatkan server cPanel (CPU/Memory usage), tambahkan index pada:
*   `users (email)` -> Validasi login dan cek email ganda.
*   `articles (status)` -> Untuk dashboard antrean Editor yang memfilter status.
*   `articles (submitter_id)` -> Pemanggilan tabel dashboard Author secara Cepat.
*   `user_specializations (keyword)` -> `FULLTEXT` atau `INDEX` Biasa Untuk mempercepat _Matchmaking Reviewer_.

---

## 4. SQL Statement (CREATE TABLE) Khusus MVP 1.0

Berikut perintah eksekusi SQL rasional yang saling terhubung menggunakan InnoDB untuk `MVP 1.0`.

```sql
-- 1. Table users
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('superadmin', 'editor', 'reviewer', 'author', 'reader') DEFAULT 'reader',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Table journals
CREATE TABLE journals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    issn VARCHAR(50),
    guidelines TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Table articles
CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    journal_id INT NOT NULL,
    issue_id INT NULL,
    submitter_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    abstract TEXT NOT NULL,
    keywords VARCHAR(255),
    status ENUM('draft', 'submitted', 'desk_review', 'under_review', 'revision_required', 'accepted', 'rejected', 'published') DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (journal_id) REFERENCES journals(id) ON DELETE CASCADE,
    FOREIGN KEY (submitter_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Set Index pencarian
CREATE INDEX idx_article_status ON articles(status);

-- 4. Table article_authors (Co-authors)
CREATE TABLE article_authors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    article_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    affiliation VARCHAR(255),
    is_corresponding BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Table article_files
CREATE TABLE article_files (
    id INT AUTO_INCREMENT PRIMARY KEY,
    article_id INT NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    file_type ENUM('manuscript', 'cover_letter', 'supplementary', 'galley_proof') NOT NULL,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. Table editorial_logs
CREATE TABLE editorial_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    article_id INT NOT NULL,
    user_id INT NOT NULL,
    action VARCHAR(255) NOT NULL,
    note TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```