SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS `users`;
DROP TABLE IF EXISTS `roles`;
DROP TABLE IF EXISTS `permissions`;
DROP TABLE IF EXISTS `role_permissions`;
DROP TABLE IF EXISTS `user_roles`;
DROP TABLE IF EXISTS `journals`;
DROP TABLE IF EXISTS `journal_scopes`;
DROP TABLE IF EXISTS `journal_settings`;
DROP TABLE IF EXISTS `editorial_boards`;
DROP TABLE IF EXISTS `submissions`;
DROP TABLE IF EXISTS `submission_files`;
DROP TABLE IF EXISTS `submission_authors`;
DROP TABLE IF EXISTS `ai_review_tasks`;
DROP TABLE IF EXISTS `ai_review_insights`;
DROP TABLE IF EXISTS `editorial_decisions`;
DROP TABLE IF EXISTS `revision_requests`;
DROP TABLE IF EXISTS `revisions`;
DROP TABLE IF EXISTS `revision_files`;
DROP TABLE IF EXISTS `review_assignments`;
DROP TABLE IF EXISTS `review_forms`;
DROP TABLE IF EXISTS `review_questions`;
DROP TABLE IF EXISTS `review_responses`;
DROP TABLE IF EXISTS `reviewer_expertises`;
DROP TABLE IF EXISTS `issues`;
DROP TABLE IF EXISTS `issue_articles`;
DROP TABLE IF EXISTS `published_articles`;
DROP TABLE IF EXISTS `article_galleys`;
DROP TABLE IF EXISTS `article_citations`;
DROP TABLE IF EXISTS `interactive_comments`;
DROP TABLE IF EXISTS `article_views`;
DROP TABLE IF EXISTS `article_downloads`;
DROP TABLE IF EXISTS `author_metrics`;
DROP TABLE IF EXISTS `research_profiles`;
DROP TABLE IF EXISTS `researcher_publications`;
DROP TABLE IF EXISTS `matchmaking_requests`;
DROP TABLE IF EXISTS `matchmaking_results`;
DROP TABLE IF EXISTS `collaboration_invitations`;
DROP TABLE IF EXISTS `communities`;
DROP TABLE IF EXISTS `community_members`;
DROP TABLE IF EXISTS `community_posts`;
DROP TABLE IF EXISTS `community_comments`;
DROP TABLE IF EXISTS `community_resources`;
DROP TABLE IF EXISTS `community_events`;

-- =============================================
-- File: modul1_rbac.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,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Table roles
CREATE TABLE roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    description VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Table permissions
CREATE TABLE permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    description VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Table user_roles (Many-to-Many between users and roles)
CREATE TABLE user_roles (
    user_id INT NOT NULL,
    role_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Table role_permissions (Many-to-Many between roles and permissions)
CREATE TABLE role_permissions (
    role_id INT NOT NULL,
    permission_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (role_id, permission_id),
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =============================================
-- File: modul2_journal.sql
-- =============================================
-- /nexus_scholar_php/database/modul2_journal.sql

-- 1. Table journals
CREATE TABLE journals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    journal_manager_id INT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    issn_print VARCHAR(50),
    issn_online VARCHAR(50),
    doi_prefix VARCHAR(50) NULL,
    publication_frequency VARCHAR(100),
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (journal_manager_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Table journal_scopes
CREATE TABLE journal_scopes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    journal_id INT NOT NULL,
    scope_name VARCHAR(255) NOT NULL,
    description TEXT,
    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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Table journal_settings
CREATE TABLE journal_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    journal_id INT NOT NULL,
    setting_key VARCHAR(100) NOT NULL,
    setting_value TEXT,
    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,
    UNIQUE KEY unique_journal_setting (journal_id, setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Table editorial_boards
CREATE TABLE editorial_boards (
    id INT AUTO_INCREMENT PRIMARY KEY,
    journal_id INT NOT NULL,
    user_id INT NOT NULL,
    role_title VARCHAR(100) NOT NULL, -- e.g., Editor-in-Chief, Section Editor
    display_order INT DEFAULT 0,
    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 (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Table author_guidelines
CREATE TABLE author_guidelines (
    id INT AUTO_INCREMENT PRIMARY KEY,
    journal_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    display_order INT DEFAULT 0,
    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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. Table review_policies
CREATE TABLE review_policies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    journal_id INT NOT NULL,
    policy_type VARCHAR(100), -- Example: 'double-blind', 'single-blind'
    content TEXT NOT NULL,
    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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =============================================
-- File: modul3_submission.sql
-- =============================================
-- /nexus_scholar_php/database/modul3_submission.sql

-- 1. Table submissions
CREATE TABLE submissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    journal_id INT NOT NULL,
    user_id INT NOT NULL, -- submitting author
    title VARCHAR(255) NOT NULL,
    abstract TEXT,
    status VARCHAR(50) DEFAULT 'draft',
    submitted_at TIMESTAMP NULL,
    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 (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Table submission_files
CREATE TABLE submission_files (
    id INT AUTO_INCREMENT PRIMARY KEY,
    submission_id INT NOT NULL,
    file_type VARCHAR(50) NOT NULL, -- 'main_article', 'supplementary'
    original_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    mime_type VARCHAR(100),
    file_size INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (submission_id) REFERENCES submissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Table submission_authors
CREATE TABLE submission_authors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    submission_id INT NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    affiliation VARCHAR(255),
    is_corresponding BOOLEAN DEFAULT FALSE,
    display_order INT DEFAULT 0,
    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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Table submission_keywords
CREATE TABLE submission_keywords (
    id INT AUTO_INCREMENT PRIMARY KEY,
    submission_id INT NOT NULL,
    keyword VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (submission_id) REFERENCES submissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Table submission_status_logs
CREATE TABLE submission_status_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    submission_id INT NOT NULL,
    user_id INT NULL, -- Context of who changed it, optional
    old_status VARCHAR(50),
    new_status VARCHAR(50) NOT NULL,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (submission_id) REFERENCES submissions(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =============================================
-- File: modul4_ai_review.sql
-- =============================================
-- /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;


-- =============================================
-- File: modul5_editorial_workflow.sql
-- =============================================
-- /nexus_scholar_php/database/modul5_editorial_workflow.sql

-- 1. Table editor_assignments
CREATE TABLE editor_assignments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    submission_id INT NOT NULL,
    editor_id INT NOT NULL,
    role VARCHAR(50) DEFAULT 'handling_editor',
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (submission_id) REFERENCES submissions(id) ON DELETE CASCADE,
    FOREIGN KEY (editor_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Table editorial_decisions
CREATE TABLE editorial_decisions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    submission_id INT NOT NULL,
    editor_id INT NOT NULL,
    round INT DEFAULT 1,
    decision ENUM('accept', 'minor_revision', 'major_revision', 'reject', 'desk_reject') NOT NULL,
    editorial_note TEXT NULL,
    decided_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (submission_id) REFERENCES submissions(id) ON DELETE CASCADE,
    FOREIGN KEY (editor_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Table revision_requests
CREATE TABLE revision_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    submission_id INT NOT NULL,
    decision_id INT NOT NULL,
    round INT DEFAULT 1,
    author_note TEXT NULL,
    status ENUM('pending', 'submitted', 'accepted') DEFAULT 'pending',
    requested_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    submitted_at TIMESTAMP NULL,
    FOREIGN KEY (submission_id) REFERENCES submissions(id) ON DELETE CASCADE,
    FOREIGN KEY (decision_id) REFERENCES editorial_decisions(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Table revision_files
CREATE TABLE revision_files (
    id INT AUTO_INCREMENT PRIMARY KEY,
    revision_id INT NOT NULL,
    original_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    mime_type VARCHAR(100) NOT NULL,
    file_size INT NOT NULL,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (revision_id) REFERENCES revision_requests(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Table editorial_status_logs
CREATE TABLE editorial_status_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    submission_id INT NOT NULL,
    user_id INT NULL, -- User who triggered the status change (could be system/scheduler so NULL allowed)
    old_status VARCHAR(50) NULL,
    new_status VARCHAR(50) NOT NULL,
    notes TEXT NULL,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (submission_id) REFERENCES submissions(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =============================================
-- File: modul6_reviewer_ecosystem_revisi.sql
-- =============================================
-- /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;


-- =============================================
-- File: modul7_publication_system.sql
-- =============================================
-- /nexus_scholar_php/database/modul7_publication_system.sql

-- 1. issues
-- Menyimpan data edisi terbitan jurnal (Volume, Nomor, Tahun)
CREATE TABLE issues (
    id INT AUTO_INCREMENT PRIMARY KEY,
    journal_id INT NOT NULL,
    volume VARCHAR(50) NOT NULL,
    number VARCHAR(50) NOT NULL,
    year INT NOT NULL,
    title VARCHAR(255) NULL,
    description TEXT NULL,
    cover_image_url VARCHAR(255) NULL,
    is_published TINYINT(1) DEFAULT 0,
    published_datetime TIMESTAMP NULL,
    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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. published_articles
-- Menyimpan data artikel yang sudah diterima (accepted) dan disiapkan untuk publikasi
CREATE TABLE published_articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    submission_id INT NOT NULL,
    status ENUM('scheduled', 'published', 'retracted') DEFAULT 'scheduled',
    views_count INT DEFAULT 0,
    published_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (submission_id) REFERENCES submissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. issue_articles
-- Menghubungkan artikel ke dalam suatu issue, lengkap dengan halaman dan urutan
CREATE TABLE issue_articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    issue_id INT NOT NULL,
    published_article_id INT NOT NULL,
    section_title VARCHAR(100) DEFAULT 'Articles', -- e.g. "Editorial", "Research Articles"
    sequence_order INT DEFAULT 0,
    pages VARCHAR(50) NULL, -- e.g., '12-24'
    FOREIGN KEY (issue_id) REFERENCES issues(id) ON DELETE CASCADE,
    FOREIGN KEY (published_article_id) REFERENCES published_articles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. article_galleys
-- Menyimpan file final publikasi (PDF, HTML, XML) untuk didownload/dilihat user
CREATE TABLE article_galleys (
    id INT AUTO_INCREMENT PRIMARY KEY,
    published_article_id INT NOT NULL,
    label VARCHAR(50) NOT NULL, -- 'PDF', 'HTML', 'XML'
    file_path VARCHAR(255) NOT NULL,
    views_count INT DEFAULT 0,
    downloads_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (published_article_id) REFERENCES published_articles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. doi_records
-- Menyimpan identifier DOI (Digital Object Identifier) yang diassign ke issue atau artikel. DOI bisa nullable
CREATE TABLE doi_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    target_type ENUM('issue', 'article', 'galley') NOT NULL,
    target_id INT NOT NULL,
    doi VARCHAR(255) NULL UNIQUE,
    registration_status ENUM('not_registered', 'registered', 'error') DEFAULT 'not_registered',
    registered_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX (target_type, target_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. indexing_metadata
-- Menyimpan metadata dinamis untuk keperluan indexing (Google Scholar, DOAJ, Scopus, dll)
CREATE TABLE indexing_metadata (
    id INT AUTO_INCREMENT PRIMARY KEY,
    published_article_id INT NOT NULL,
    meta_name VARCHAR(100) NOT NULL, -- e.g., 'DC.Description', 'citation_author'
    meta_content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (published_article_id) REFERENCES published_articles(id) ON DELETE CASCADE,
    INDEX (meta_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- =============================================
-- File: modul8_interactive_article.sql
-- =============================================
-- /nexus_scholar_php/database/modul8_interactive_article.sql

-- 1. article_html_contents
-- Menyimpan content HTML interaktif artikel
CREATE TABLE article_html_contents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    published_article_id INT NOT NULL,
    section_title VARCHAR(255) NULL,
    html_content TEXT NOT NULL,
    sequence_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (published_article_id) REFERENCES published_articles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. article_references
-- Menyimpan daftar pustaka dari artikel
CREATE TABLE article_references (
    id INT AUTO_INCREMENT PRIMARY KEY,
    published_article_id INT NOT NULL,
    citation_key VARCHAR(100) NOT NULL,
    raw_reference TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (published_article_id) REFERENCES published_articles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. reference_previews
-- Menyimpan preview metadata untuk referensi (misal: abstrak dari sumber yang disitasi)
CREATE TABLE reference_previews (
    id INT AUTO_INCREMENT PRIMARY KEY,
    reference_id INT NOT NULL,
    preview_html TEXT NOT NULL,
    source_url VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (reference_id) REFERENCES article_references(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. article_concept_maps
-- Menyimpan Visual Research Map dalam format JSON
CREATE TABLE article_concept_maps (
    id INT AUTO_INCREMENT PRIMARY KEY,
    published_article_id INT NOT NULL,
    map_data_json TEXT NOT NULL, -- Nodes & Edges relationship JSON
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (published_article_id) REFERENCES published_articles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. article_discussions
-- Topik diskusi pembaca pada suatu artikel
CREATE TABLE article_discussions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    published_article_id INT NOT NULL,
    user_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    moderation_status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (published_article_id) REFERENCES published_articles(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. discussion_comments
-- Balasan/Komentar pada diskusi
CREATE TABLE discussion_comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    discussion_id INT NOT NULL,
    user_id INT NOT NULL,
    content TEXT NOT NULL,
    moderation_status ENUM('pending', 'approved', 'rejected') DEFAULT 'approved',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (discussion_id) REFERENCES article_discussions(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 7. ai_article_summaries
-- Hasil rangkuman AI dari artikel
CREATE TABLE ai_article_summaries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    published_article_id INT NOT NULL,
    brief_summary TEXT NULL,
    key_findings TEXT NULL,
    practical_implications TEXT NULL,
    limitations TEXT NULL,
    future_research TEXT NULL,
    generated_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (published_article_id) REFERENCES published_articles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- =============================================
-- File: modul9_author_analytics.sql
-- =============================================
-- /nexus_scholar_php/database/modul9_author_analytics.sql

-- 1. article_views
CREATE TABLE article_views (
    id INT AUTO_INCREMENT PRIMARY KEY,
    published_article_id INT NOT NULL,
    viewer_ip VARCHAR(45) NULL,
    viewed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (published_article_id) REFERENCES published_articles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. article_downloads
CREATE TABLE article_downloads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    published_article_id INT NOT NULL,
    downloader_ip VARCHAR(45) NULL,
    downloaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (published_article_id) REFERENCES published_articles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. citation_records
CREATE TABLE citation_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    published_article_id INT NOT NULL,
    citing_paper_title VARCHAR(255) NOT NULL,
    citing_paper_authors VARCHAR(255) NULL,
    citing_paper_journal VARCHAR(255) NULL,
    citing_paper_year INT NULL,
    citing_paper_url VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (published_article_id) REFERENCES published_articles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. writing_sessions
CREATE TABLE writing_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    submission_id INT NULL,
    start_time DATETIME NOT NULL,
    end_time DATETIME NOT NULL,
    words_added INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (submission_id) REFERENCES submissions(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. revision_logs
CREATE TABLE revision_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    submission_id INT NOT NULL,
    user_id INT NOT NULL,
    revision_note TEXT NULL,
    time_spent_minutes INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT 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;

-- 6. author_analytics_snapshots
CREATE TABLE author_analytics_snapshots (
    id INT AUTO_INCREMENT PRIMARY KEY,
    author_id INT NOT NULL,
    publication_stats JSON NULL, 
    writing_stats JSON NULL,     
    interest_map JSON NULL,      
    generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY (author_id),
    FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 7. impact_metrics
CREATE TABLE impact_metrics (
    id INT AUTO_INCREMENT PRIMARY KEY,
    published_article_id INT NOT NULL,
    total_views INT DEFAULT 0,
    total_downloads INT DEFAULT 0,
    total_citations INT DEFAULT 0,
    engagement_score DECIMAL(5,2) DEFAULT 0.00,
    last_updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY (published_article_id),
    FOREIGN KEY (published_article_id) REFERENCES published_articles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- =============================================
-- File: modul10_research_profile.sql
-- =============================================
-- /nexus_scholar_php/database/modul10_research_profile.sql

-- 1. research_profiles
-- Menyimpan profil publik dari author/researcher
CREATE TABLE research_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL UNIQUE,
    bio TEXT NULL,
    affiliation VARCHAR(255) NULL,
    academic_title VARCHAR(100) NULL,
    orcid_id VARCHAR(50) NULL,
    google_scholar_url VARCHAR(255) NULL,
    is_public TINYINT(1) DEFAULT 1,
    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. research_fields
-- Master tabel untuk bidang kajian/penelitian
CREATE TABLE research_fields (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. research_methods
-- Master tabel untuk metode penelitian
CREATE TABLE research_methods (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. profile_research_fields
-- Relasi many-to-many user dengan bidang penelitian
CREATE TABLE profile_research_fields (
    user_id INT NOT NULL,
    field_id INT NOT NULL,
    PRIMARY KEY (user_id, field_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (field_id) REFERENCES research_fields(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. profile_research_methods
-- Relasi many-to-many user dengan metode penelitian
CREATE TABLE profile_research_methods (
    user_id INT NOT NULL,
    method_id INT NOT NULL,
    PRIMARY KEY (user_id, method_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (method_id) REFERENCES research_methods(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. profile_publications
-- Daftar publikasi dari luar platform (opsional) atau yang dikurasi oleh user
CREATE TABLE profile_publications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    journal_name VARCHAR(255) NULL,
    publication_year INT NULL,
    doi VARCHAR(100) NULL,
    url VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 7. collaboration_preferences
-- Preferensi kolaborasi dari author
CREATE TABLE collaboration_preferences (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL UNIQUE,
    is_open_to_collaborate TINYINT(1) DEFAULT 1,
    collaboration_types JSON NULL, -- (e.g., ["Co-Writing", "Data Analysis", "Peer Review", "Mentoring"])
    availability_notes TEXT NULL,
    preferred_communication TEXT NULL,
    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;


-- =============================================
-- File: modul11_matchmaking.sql
-- =============================================
-- /nexus_scholar_php/database/modul11_matchmaking.sql

-- 1. research_match_requests
CREATE TABLE research_match_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    search_type VARCHAR(50) NOT NULL, -- e.g., 'Co-Author', 'Reviewer', 'Mentor', 'Research Partner'
    keywords TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. research_match_results
CREATE TABLE research_match_results (
    id INT AUTO_INCREMENT PRIMARY KEY,
    request_id INT NOT NULL,
    candidate_id INT NOT NULL,
    score INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (request_id) REFERENCES research_match_requests(id) ON DELETE CASCADE,
    FOREIGN KEY (candidate_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. collaboration_invitations
DROP TABLE IF EXISTS collaboration_invitations;
CREATE TABLE collaboration_invitations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sender_id INT NOT NULL,
    receiver_id INT NOT NULL,
    collaboration_type VARCHAR(100) NOT NULL, -- Co-Author, Reviewer, Mentor, Research Partner
    message TEXT NOT NULL,
    status ENUM('pending', 'accepted', 'declined', 'cancelled') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (receiver_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. mentor_requests
CREATE TABLE mentor_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    mentee_id INT NOT NULL,
    mentor_id INT NOT NULL,
    message TEXT NOT NULL,
    goals TEXT NULL,
    status ENUM('pending', 'accepted', 'declined', 'cancelled') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (mentee_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (mentor_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- =============================================
-- File: modul12_community.sql
-- =============================================
-- /nexus_scholar_php/database/modul12_community.sql

-- 1. communities
DROP TABLE IF EXISTS community_events;
DROP TABLE IF EXISTS community_resources;
DROP TABLE IF EXISTS community_comments;
DROP TABLE IF EXISTS community_posts;
DROP TABLE IF EXISTS community_members;
DROP TABLE IF EXISTS communities;

CREATE TABLE communities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    description TEXT NULL,
    category VARCHAR(100) NULL, -- Kategori bidang/topik
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. community_members
CREATE TABLE community_members (
    community_id INT NOT NULL,
    user_id INT NOT NULL,
    role ENUM('member', 'moderator', 'admin') DEFAULT 'member', -- member/moderator/admin
    joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (community_id, user_id),
    FOREIGN KEY (community_id) REFERENCES communities(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. community_posts
CREATE TABLE community_posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    community_id INT NOT NULL,
    user_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    body TEXT NOT NULL,
    post_type ENUM('discussion', 'article_share', 'call_for_paper', 'call_for_collaboration', 'reading_group') DEFAULT 'discussion',
    reference_url VARCHAR(255) NULL,
    status ENUM('published', 'hidden') DEFAULT 'published',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (community_id) REFERENCES communities(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. community_comments
CREATE TABLE community_comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    post_id INT NOT NULL,
    user_id INT NOT NULL,
    body TEXT NOT NULL,
    status ENUM('published', 'hidden') DEFAULT 'published', -- Untuk moderasi
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES community_posts(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. community_resources
CREATE TABLE community_resources (
    id INT AUTO_INCREMENT PRIMARY KEY,
    community_id INT NOT NULL,
    user_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    resource_url VARCHAR(255) NOT NULL,
    description TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (community_id) REFERENCES communities(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. community_events
CREATE TABLE community_events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    community_id INT NOT NULL,
    user_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    event_date DATETIME NOT NULL,
    event_url VARCHAR(255) NULL, -- Link meeting/location
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (community_id) REFERENCES communities(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



INSERT IGNORE INTO `roles` (`id`, `name`, `description`) VALUES
(1, 'super_admin', 'System Administrator'),
(2, 'editor', 'Journal Editor'),
(3, 'reviewer', 'Peer Reviewer'),
(4, 'author', 'Author / Researcher');

INSERT IGNORE INTO `users` (`id`, `name`, `email`, `password_hash`, `status`, `created_at`) VALUES
(1, 'Super Admin', 'admin@nexusscholar.test', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'active', NOW()),
(2, 'John Editor', 'editor@nexusscholar.test', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'active', NOW()),
(3, 'Jane Reviewer', 'reviewer@nexusscholar.test', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'active', NOW()),
(4, 'Bob Author', 'author@nexusscholar.test', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'active', NOW());

INSERT IGNORE INTO `user_roles` (`user_id`, `role_id`) VALUES
(1, 1),
(2, 2),
(3, 3),
(4, 4);

SET FOREIGN_KEY_CHECKS=1;