-- ============================================================
-- MULTI-JOURNAL PLATFORM - CORE SCHEMA (Migration 001)
-- Pure PHP/PDO/MySQL | RatechPlus Enterprise Development Standard
-- ============================================================

SET FOREIGN_KEY_CHECKS = 0;

-- ------------------------------------------------------------
-- 1. SETTINGS (site identity, branding, contact, SEO, theme)
-- Loaded ONCE into a PHP array by SettingsManager. Never hardcode.
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_group VARCHAR(50) NOT NULL DEFAULT 'general', -- general, branding, contact, seo, theme, email
    setting_key VARCHAR(100) NOT NULL,
    setting_value TEXT NULL,
    updated_by INT NULL,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_setting_key (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- 2. JOURNALS (each journal = a "context", multi-tenant style)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS journals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    journal_code VARCHAR(30) NOT NULL,      -- used in URL path e.g. /ijaimr/
    journal_name VARCHAR(255) NOT NULL,
    short_name VARCHAR(100) NULL,
    issn VARCHAR(20) NULL,
    e_issn VARCHAR(20) NULL,
    description TEXT NULL,
    logo_path VARCHAR(255) NULL,
    cover_path VARCHAR(255) NULL,
    theme_color VARCHAR(20) NULL,
    contact_email VARCHAR(150) NULL,
    status ENUM('Active','Inactive','Draft','Archived') NOT NULL DEFAULT 'Draft',
    display_order INT NOT NULL DEFAULT 0,
    created_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,  -- soft delete
    UNIQUE KEY uniq_journal_code (journal_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- 3. USERS (global — one account can work across many journals)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(150) NOT NULL,
    email VARCHAR(150) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    affiliation VARCHAR(255) NULL,
    orcid VARCHAR(50) NULL,
    country VARCHAR(100) NULL,
    phone VARCHAR(30) NULL,
    profile_photo VARCHAR(255) NULL,
    status ENUM('Active','Inactive','Suspended') NOT NULL DEFAULT 'Active',
    email_verified_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    UNIQUE KEY uniq_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- 4. ROLES (system-wide role catalog)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    role_key VARCHAR(50) NOT NULL,   -- site_admin, journal_manager, editor, section_editor, reviewer, author, reader
    role_label VARCHAR(100) NOT NULL,
    role_scope ENUM('site','journal') NOT NULL DEFAULT 'journal', -- site_admin is site-wide, rest are per-journal
    UNIQUE KEY uniq_role_key (role_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO roles (role_key, role_label, role_scope) VALUES
('site_admin', 'Site Administrator', 'site'),
('journal_manager', 'Journal Manager', 'journal'),
('editor', 'Editor', 'journal'),
('section_editor', 'Section Editor', 'journal'),
('reviewer', 'Reviewer', 'journal'),
('author', 'Author', 'journal'),
('reader', 'Reader', 'journal')
ON DUPLICATE KEY UPDATE role_label = VALUES(role_label);

-- ------------------------------------------------------------
-- 5. USER-JOURNAL-ROLE ASSIGNMENT (RBAC, many-to-many)
-- A user can hold different roles in different journals.
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS user_journal_roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    journal_id INT NULL,      -- NULL = site-wide role (e.g. site_admin)
    role_id INT NOT NULL,
    assigned_by INT NULL,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (journal_id) REFERENCES journals(id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    UNIQUE KEY uniq_user_journal_role (user_id, journal_id, role_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- 6. SECTIONS (journal subject sections, e.g. "Research Articles")
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS sections (
    id INT AUTO_INCREMENT PRIMARY KEY,
    journal_id INT NOT NULL,
    section_name VARCHAR(150) NOT NULL,
    status ENUM('Active','Inactive') NOT NULL DEFAULT 'Active',
    display_order INT NOT NULL DEFAULT 0,
    FOREIGN KEY (journal_id) REFERENCES journals(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- 7. SUBMISSIONS (the core workflow object authors track)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS submissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    journal_id INT NOT NULL,
    section_id INT NULL,
    submitting_author_id INT NOT NULL,
    title VARCHAR(500) NOT NULL,
    abstract TEXT NULL,
    keywords VARCHAR(500) NULL,
    current_status ENUM(
        'registered',
        'submitted',
        'editorial_screening',
        'under_review',
        'revision_requested',
        'revision_submitted',
        'accepted',
        'rejected',
        'copyediting',
        'production',
        'scheduled',
        'published'
    ) NOT NULL DEFAULT 'registered',
    submitted_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    FOREIGN KEY (journal_id) REFERENCES journals(id) ON DELETE CASCADE,
    FOREIGN KEY (section_id) REFERENCES sections(id) ON DELETE SET NULL,
    FOREIGN KEY (submitting_author_id) REFERENCES users(id),
    INDEX idx_submission_status (journal_id, current_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Co-authors (a submission can have multiple authors)
CREATE TABLE IF NOT EXISTS submission_authors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    submission_id INT NOT NULL,
    user_id INT NULL,             -- linked account if author has one
    full_name VARCHAR(150) NOT NULL,
    email VARCHAR(150) NULL,
    affiliation VARCHAR(255) NULL,
    author_order INT NOT NULL DEFAULT 0,
    is_corresponding TINYINT(1) NOT NULL DEFAULT 0,
    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;

-- ------------------------------------------------------------
-- 8. SUBMISSION STATUS LOG (drives the Author Tracking Dashboard)
-- Every status change is logged here — this IS the timeline.
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS submission_status_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    submission_id INT NOT NULL,
    status VARCHAR(50) NOT NULL,
    notes TEXT NULL,              -- editor remarks visible to author (mediated, not raw reviewer comments)
    changed_by INT NULL,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (submission_id) REFERENCES submissions(id) ON DELETE CASCADE,
    FOREIGN KEY (changed_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_submission_timeline (submission_id, changed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- 9. SUBMISSION FILES (versioned manuscript files)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS submission_files (
    id INT AUTO_INCREMENT PRIMARY KEY,
    submission_id INT NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    original_name VARCHAR(255) NOT NULL,
    file_type ENUM('manuscript','revision','copyedit','galley','supplementary') NOT NULL DEFAULT 'manuscript',
    version INT NOT NULL DEFAULT 1,
    uploaded_by INT NULL,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (submission_id) REFERENCES submissions(id) ON DELETE CASCADE,
    FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- 10. REVIEWS (peer review assignments — blind by default)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS reviews (
    id INT AUTO_INCREMENT PRIMARY KEY,
    submission_id INT NOT NULL,
    reviewer_id INT NOT NULL,
    round INT NOT NULL DEFAULT 1,
    status ENUM('invited','accepted','declined','in_progress','completed','cancelled') NOT NULL DEFAULT 'invited',
    recommendation ENUM('accept','minor_revision','major_revision','reject') NULL,
    comments_for_editor TEXT NULL,
    comments_for_author TEXT NULL,
    due_date DATE NULL,
    completed_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    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;

-- ------------------------------------------------------------
-- 11. ISSUES (volume/number groupings for publication)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS issues (
    id INT AUTO_INCREMENT PRIMARY KEY,
    journal_id INT NOT NULL,
    volume VARCHAR(20) NULL,
    issue_number VARCHAR(20) NULL,
    year YEAR NULL,
    title VARCHAR(255) NULL,
    cover_path VARCHAR(255) NULL,
    status ENUM('Draft','Published') NOT NULL DEFAULT 'Draft',
    published_at TIMESTAMP NULL,
    FOREIGN KEY (journal_id) REFERENCES journals(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- 12. ARTICLES (final published output of a submission)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    submission_id INT NOT NULL,
    issue_id INT NULL,
    doi VARCHAR(100) NULL,
    pages VARCHAR(30) NULL,
    galley_pdf_path VARCHAR(255) NULL,
    views INT NOT NULL DEFAULT 0,
    downloads INT NOT NULL DEFAULT 0,
    published_at TIMESTAMP NULL,
    FOREIGN KEY (submission_id) REFERENCES submissions(id) ON DELETE CASCADE,
    FOREIGN KEY (issue_id) REFERENCES issues(id) ON DELETE SET NULL,
    UNIQUE KEY uniq_submission_article (submission_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- 13. MEDIA LIBRARY (reusable uploads, no duplicates)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS media_library (
    id INT AUTO_INCREMENT PRIMARY KEY,
    file_path VARCHAR(255) NOT NULL,
    original_name VARCHAR(255) NOT NULL,
    file_type VARCHAR(50) NULL,
    file_size INT NULL,
    uploaded_by INT NULL,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- 14. AUDIT LOG (created/updated/deleted by+at, system-wide)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(100) NOT NULL,
    record_id INT NOT NULL,
    action ENUM('create','update','delete','restore') NOT NULL,
    changed_by INT NULL,
    changed_data TEXT NULL,   -- JSON snapshot of what changed
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (changed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;

-- ------------------------------------------------------------
-- SEED: default settings (placeholder — editable from Admin later)
-- ------------------------------------------------------------
INSERT INTO settings (setting_group, setting_key, setting_value) VALUES
('branding', 'site_name', 'Journal Hub (Placeholder)'),
('branding', 'site_logo', ''),
('branding', 'primary_color', '#0b3d91'),
('contact', 'contact_email', ''),
('contact', 'contact_phone', ''),
('seo', 'site_description', ''),
('general', 'default_review_days', '21')
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value);