-- ============================================================
-- Editorial Board + Per-Journal Navigation Menu (Migration 004)
-- Run after 001/002/003.
-- ============================================================

CREATE TABLE IF NOT EXISTS editorial_board (
    id INT AUTO_INCREMENT PRIMARY KEY,
    journal_id INT NOT NULL,
    full_name VARCHAR(150) NOT NULL,
    role_title VARCHAR(100) NOT NULL,   -- e.g. Editor-in-Chief, Associate Editor, Reviewer Board Member
    affiliation VARCHAR(255) NULL,
    photo_path VARCHAR(255) NULL,
    display_order INT NOT NULL DEFAULT 0,
    status ENUM('Active','Inactive') NOT NULL DEFAULT 'Active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (journal_id) REFERENCES journals(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS journal_menu_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    journal_id INT NOT NULL,
    label VARCHAR(100) NOT NULL,
    url VARCHAR(255) NOT NULL,          -- relative (e.g. #editorial-board) or absolute URL
    display_order INT NOT NULL DEFAULT 0,
    status ENUM('Active','Inactive') NOT NULL DEFAULT 'Active',
    FOREIGN KEY (journal_id) REFERENCES journals(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;