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