-- ============================================================
-- AdvEIE — Complete Database Schema
-- File: install.sql
-- Import via phpMyAdmin or: mysql -u user -p adveie_db < install.sql
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- ==========================================
-- DROP TABLES (clean install)
-- ==========================================
DROP TABLE IF EXISTS `contact_tickets`;
DROP TABLE IF EXISTS `rate_limits`;
DROP TABLE IF EXISTS `financials`;
DROP TABLE IF EXISTS `review_assignments`;
DROP TABLE IF EXISTS `submissions`;
DROP TABLE IF EXISTS `users`;
DROP TABLE IF EXISTS `journal_settings`;

-- ==========================================
-- 1. USERS TABLE
-- ==========================================
CREATE TABLE `users` (
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name`          VARCHAR(255) NOT NULL,
    `email`         VARCHAR(191) NOT NULL,
    `password_hash` VARCHAR(255) NOT NULL,
    `role`          ENUM('admin','editor','reviewer','author') NOT NULL DEFAULT 'author',
    `orcid_local`   VARCHAR(50) NULL DEFAULT NULL,
    `institution`   VARCHAR(255) NOT NULL DEFAULT '',
    `status`        ENUM('active','suspended','pending_verification') NOT NULL DEFAULT 'active',
    `created_at`    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `idx_unique_email` (`email`),
    INDEX `idx_user_role_status` (`role`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==========================================
-- 2. SUBMISSIONS TABLE
-- ==========================================
CREATE TABLE `submissions` (
    `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `author_id`       BIGINT UNSIGNED NOT NULL,
    `title`           TEXT NOT NULL,
    `abstract`        TEXT NOT NULL,
    `keywords`        VARCHAR(500) NOT NULL DEFAULT '',
    `file_path`       VARCHAR(512) NOT NULL DEFAULT '',
    `current_status`  ENUM('submitted','under_review','revisions_required','accepted','published','rejected') NOT NULL DEFAULT 'submitted',
    `plagiarism_score` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    `ai_score`         DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    `local_doi`        VARCHAR(100) NULL DEFAULT NULL,
    `volume`           INT UNSIGNED NULL DEFAULT NULL,
    `issue`            INT UNSIGNED NULL DEFAULT NULL,
    `created_at`       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `idx_unique_local_doi` (`local_doi`),
    INDEX `idx_submission_status` (`current_status`),
    INDEX `idx_submission_author` (`author_id`),
    CONSTRAINT `fk_submissions_author`
        FOREIGN KEY (`author_id`) REFERENCES `users` (`id`)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==========================================
-- 3. REVIEW ASSIGNMENTS TABLE
-- ==========================================
CREATE TABLE `review_assignments` (
    `id`                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `submission_id`       BIGINT UNSIGNED NOT NULL,
    `reviewer_id`         BIGINT UNSIGNED NOT NULL,
    `status`              ENUM('pending','accepted','declined','completed','expired') NOT NULL DEFAULT 'pending',
    `deadline`            DATETIME NOT NULL,
    `rating_originality`  TINYINT UNSIGNED NULL DEFAULT NULL,
    `rating_methodology`  TINYINT UNSIGNED NULL DEFAULT NULL,
    `rating_language`     TINYINT UNSIGNED NULL DEFAULT NULL,
    `comments`            TEXT NULL DEFAULT NULL,
    `confidential_notes`  TEXT NULL DEFAULT NULL,
    `submitted_at`        DATETIME NULL DEFAULT NULL,
    `created_at`          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `idx_unique_assignment` (`submission_id`, `reviewer_id`),
    INDEX `idx_assignment_status` (`status`),
    INDEX `idx_assignment_reviewer` (`reviewer_id`),
    CONSTRAINT `fk_assignments_submission`
        FOREIGN KEY (`submission_id`) REFERENCES `submissions` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_assignments_reviewer`
        FOREIGN KEY (`reviewer_id`) REFERENCES `users` (`id`)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==========================================
-- 4. FINANCIALS TABLE
-- ==========================================
CREATE TABLE `financials` (
    `id`                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `submission_id`         BIGINT UNSIGNED NOT NULL,
    `author_id`             BIGINT UNSIGNED NOT NULL,
    `amount`                DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `currency`              VARCHAR(3) NOT NULL DEFAULT 'USD',
    `payment_status`        ENUM('pending','submitted_for_verification','paid','rejected') NOT NULL DEFAULT 'pending',
    `transaction_reference` VARCHAR(255) NOT NULL DEFAULT '',
    `invoice_pdf`           VARCHAR(512) NULL DEFAULT NULL,
    `verified_by_admin_id`  BIGINT UNSIGNED NULL DEFAULT NULL,
    `paid_at`               DATETIME NULL DEFAULT NULL,
    `created_at`            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_financial_status` (`payment_status`),
    INDEX `idx_financial_author` (`author_id`),
    CONSTRAINT `fk_financials_submission`
        FOREIGN KEY (`submission_id`) REFERENCES `submissions` (`id`)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT `fk_financials_author`
        FOREIGN KEY (`author_id`) REFERENCES `users` (`id`)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==========================================
-- 5. JOURNAL SETTINGS TABLE
-- ==========================================
CREATE TABLE `journal_settings` (
    `meta_key`   VARCHAR(191) NOT NULL,
    `meta_value` LONGTEXT NULL DEFAULT NULL,
    PRIMARY KEY (`meta_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==========================================
-- 6. RATE LIMITS TABLE
-- ==========================================
CREATE TABLE `rate_limits` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `ip_address` VARCHAR(45) NOT NULL,
    `action`     VARCHAR(50) NOT NULL DEFAULT 'login',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_rate_ip_action` (`ip_address`, `action`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==========================================
-- 7. CONTACT TICKETS TABLE
-- ==========================================
CREATE TABLE `contact_tickets` (
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name`         VARCHAR(255) NOT NULL,
    `email`        VARCHAR(191) NOT NULL,
    `subject`      VARCHAR(255) NULL DEFAULT NULL,
    `enquiry_type` VARCHAR(100) NULL DEFAULT NULL,
    `message`      TEXT NOT NULL,
    `status`       ENUM('open','resolved') NOT NULL DEFAULT 'open',
    `created_at`   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_ticket_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- ==========================================
-- SEED DATA
-- ==========================================
INSERT INTO `journal_settings` (`meta_key`, `meta_value`) VALUES
('journal_title',    'Advances in Educational Inclusion and Equity'),
('brand_short_form', 'AdvEIE'),
('issn_online',      '2709-124X'),
('apc_fee_usd',      '500.00'),
('current_volume',   '1'),
('current_issue',    '1'),
('local_doi_prefix', '10.adveie');

-- Default admin account: admin@adveie.org / Admin@2025!
-- PASSWORD HASH for 'Admin@2025!' (bcrypt cost 12)
INSERT INTO `users` (`name`, `email`, `password_hash`, `role`, `institution`, `status`) VALUES
('System Administrator', 'admin@adveie.org',
 '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
 'admin', 'AdvEIE Editorial Office', 'active');

-- NOTE: Change admin password immediately after first login!
-- Generate new hash with: echo password_hash('YourNewPassword', PASSWORD_BCRYPT, ['cost'=>12]);