-- /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;