-- ============================================================ -- ScholarPress - MySQL Database Initialization Script -- ============================================================ -- Run this script to create the database schema and seed an -- initial admin user. -- -- Usage: -- mysql -u root -p < init_db.sql -- -- Default admin credentials: -- Username: admin -- Email: admin@scholarpress.com -- Password: admin123 (CHANGE THIS AFTER FIRST LOGIN) -- ============================================================ CREATE DATABASE IF NOT EXISTS scholarpress CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE scholarpress; -- ============================================================ -- Users -- ============================================================ CREATE TABLE IF NOT EXISTS `user` ( id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(120) NOT NULL, username VARCHAR(80) NOT NULL, name VARCHAR(200), password_hash VARCHAR(256) NOT NULL, role VARCHAR(20) NOT NULL DEFAULT 'editor', is_active TINYINT(1) DEFAULT 1, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_user_email (email), UNIQUE KEY uq_user_username (username), INDEX idx_user_email (email) ) ENGINE=InnoDB; -- ============================================================ -- Journals -- ============================================================ CREATE TABLE IF NOT EXISTS journal ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(200) NOT NULL, slug VARCHAR(200) NOT NULL, description TEXT, cover_image VARCHAR(500), issn VARCHAR(20), founded_year INT, impact_factor VARCHAR(20), frequency VARCHAR(50), editor VARCHAR(200), is_open_access TINYINT(1) DEFAULT 0, is_active TINYINT(1) DEFAULT 1, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_journal_slug (slug), INDEX idx_journal_slug (slug) ) ENGINE=InnoDB; -- ============================================================ -- Tags -- ============================================================ CREATE TABLE IF NOT EXISTS tag ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, slug VARCHAR(100) NOT NULL, UNIQUE KEY uq_tag_name (name), UNIQUE KEY uq_tag_slug (slug), INDEX idx_tag_name (name) ) ENGINE=InnoDB; -- ============================================================ -- Articles -- ============================================================ CREATE TABLE IF NOT EXISTS article ( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(500) NOT NULL, slug VARCHAR(500) NOT NULL, abstract TEXT, content TEXT, authors VARCHAR(1000), doi VARCHAR(100), pdf_file VARCHAR(500), citation_count INT DEFAULT 0, view_count INT DEFAULT 0, download_count INT DEFAULT 0, is_published TINYINT(1) DEFAULT 0, is_open_access TINYINT(1) DEFAULT 1, published_date DATETIME, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Publication details keywords VARCHAR(500), volume VARCHAR(50), issue VARCHAR(50), pages VARCHAR(50), -- SEO fields meta_title VARCHAR(200), meta_description VARCHAR(500), meta_keywords VARCHAR(500), -- Foreign keys journal_id INT NOT NULL, author_id INT, submission_id INT, UNIQUE KEY uq_article_slug (slug), UNIQUE KEY uq_article_doi (doi), INDEX idx_article_slug (slug), CONSTRAINT fk_article_journal FOREIGN KEY (journal_id) REFERENCES journal (id), CONSTRAINT fk_article_author FOREIGN KEY (author_id) REFERENCES `user` (id), CONSTRAINT fk_article_submission FOREIGN KEY (submission_id) REFERENCES submission (id) ) ENGINE=InnoDB; -- ============================================================ -- Article–Tag association -- ============================================================ CREATE TABLE IF NOT EXISTS article_tags ( article_id INT NOT NULL, tag_id INT NOT NULL, PRIMARY KEY (article_id, tag_id), CONSTRAINT fk_at_article FOREIGN KEY (article_id) REFERENCES article (id) ON DELETE CASCADE, CONSTRAINT fk_at_tag FOREIGN KEY (tag_id) REFERENCES tag (id) ON DELETE CASCADE ) ENGINE=InnoDB; -- ============================================================ -- Submissions -- ============================================================ CREATE TABLE IF NOT EXISTS submission ( id INT AUTO_INCREMENT PRIMARY KEY, tracking_id VARCHAR(50) NOT NULL, title VARCHAR(500) NOT NULL, abstract TEXT NOT NULL, authors VARCHAR(1000) NOT NULL, contact_email VARCHAR(120) NOT NULL, affiliation VARCHAR(500), orcid VARCHAR(50), file_path VARCHAR(500) NOT NULL, file_type VARCHAR(10), status VARCHAR(50) DEFAULT 'pending', admin_notes TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Foreign keys journal_id INT NOT NULL, UNIQUE KEY uq_submission_tracking (tracking_id), INDEX idx_submission_tracking (tracking_id), INDEX idx_submission_email (contact_email), CONSTRAINT fk_submission_journal FOREIGN KEY (journal_id) REFERENCES journal (id) ) ENGINE=InnoDB; -- ============================================================ -- Submission–Tag association -- ============================================================ CREATE TABLE IF NOT EXISTS submission_tags ( submission_id INT NOT NULL, tag_id INT NOT NULL, PRIMARY KEY (submission_id, tag_id), CONSTRAINT fk_st_submission FOREIGN KEY (submission_id) REFERENCES submission (id) ON DELETE CASCADE, CONSTRAINT fk_st_tag FOREIGN KEY (tag_id) REFERENCES tag (id) ON DELETE CASCADE ) ENGINE=InnoDB; -- ============================================================ -- Submission Status Updates -- ============================================================ CREATE TABLE IF NOT EXISTS submission_status_update ( id INT AUTO_INCREMENT PRIMARY KEY, submission_id INT NOT NULL, old_status VARCHAR(50), new_status VARCHAR(50) NOT NULL, notes TEXT, updated_by_id INT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_ssu_submission FOREIGN KEY (submission_id) REFERENCES submission (id) ON DELETE CASCADE, CONSTRAINT fk_ssu_user FOREIGN KEY (updated_by_id) REFERENCES `user` (id) ) ENGINE=InnoDB; -- ============================================================ -- Contact Threads -- ============================================================ CREATE TABLE IF NOT EXISTS contact_thread ( id INT AUTO_INCREMENT PRIMARY KEY, secure_token VARCHAR(100) NOT NULL, name VARCHAR(100) NOT NULL, email VARCHAR(120) NOT NULL, subject VARCHAR(500) NOT NULL, is_closed TINYINT(1) DEFAULT 0, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_thread_token (secure_token), INDEX idx_thread_token (secure_token), INDEX idx_thread_email (email) ) ENGINE=InnoDB; -- ============================================================ -- Contact Messages -- ============================================================ CREATE TABLE IF NOT EXISTS contact_message ( id INT AUTO_INCREMENT PRIMARY KEY, thread_id INT NOT NULL, message TEXT NOT NULL, is_from_user TINYINT(1) DEFAULT 1, responder_id INT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_cm_thread FOREIGN KEY (thread_id) REFERENCES contact_thread (id) ON DELETE CASCADE, CONSTRAINT fk_cm_responder FOREIGN KEY (responder_id) REFERENCES `user` (id) ) ENGINE=InnoDB; -- ============================================================ -- Site Settings -- ============================================================ CREATE TABLE IF NOT EXISTS site_settings ( id INT AUTO_INCREMENT PRIMARY KEY, -- General site_name VARCHAR(200) DEFAULT 'ScholarPress', site_tagline VARCHAR(500), site_description TEXT, site_logo VARCHAR(500), site_favicon VARCHAR(500), contact_email VARCHAR(120), -- Hero section hero_title VARCHAR(500), hero_subtitle TEXT, hero_image VARCHAR(500), hero_cta_text VARCHAR(100) DEFAULT 'Submit Your Research', hero_cta_link VARCHAR(500) DEFAULT '/submit', -- About section about_title VARCHAR(200), about_content TEXT, about_image VARCHAR(500), -- Features section feature1_title VARCHAR(200) DEFAULT 'Open Access', feature1_description TEXT, feature1_icon VARCHAR(100) DEFAULT 'fa-unlock-alt', feature2_title VARCHAR(200) DEFAULT 'Peer Review', feature2_description TEXT, feature2_icon VARCHAR(100) DEFAULT 'fa-users', feature3_title VARCHAR(200) DEFAULT 'Fast Publication', feature3_description TEXT, feature3_icon VARCHAR(100) DEFAULT 'fa-rocket', -- Footer & social footer_text TEXT, social_twitter VARCHAR(200), social_linkedin VARCHAR(200), social_facebook VARCHAR(200), -- Analytics google_analytics_id VARCHAR(50), updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB; -- ============================================================ -- Content Blocks -- ============================================================ CREATE TABLE IF NOT EXISTS content_block ( id INT AUTO_INCREMENT PRIMARY KEY, page VARCHAR(100) NOT NULL, block_key VARCHAR(100) NOT NULL, title VARCHAR(500), content TEXT, image VARCHAR(500), `order` INT DEFAULT 0, is_active TINYINT(1) DEFAULT 1, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_page_block (page, block_key), INDEX idx_content_page (page) ) ENGINE=InnoDB; -- ============================================================ -- Page Images -- ============================================================ CREATE TABLE IF NOT EXISTS page_image ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(200) NOT NULL, filename VARCHAR(500) NOT NULL, alt_text VARCHAR(500), page VARCHAR(100), created_at DATETIME DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB; -- ============================================================ -- Seed: Admin User -- ============================================================ -- Password: admin123 (Werkzeug scrypt hash) -- IMPORTANT: Change this password immediately after first login! INSERT INTO `user` (email, username, name, password_hash, role, is_active, created_at, updated_at) VALUES ( 'admin@scholarpress.com', 'admin', 'Administrator', 'scrypt:32768:8:1$VTB6DbLOTSvT6iDs$138ccc8bc8ff40ff4532ab11adbf38e7e3f6c95f5b9a528f618980f3c2a48485161fe61bcb89588de3209644263869aed0c9ec3aa2558097c05efc62ecafbb63', 'admin', 1, NOW(), NOW() ); -- ============================================================ -- Seed: Default Site Settings -- ============================================================ INSERT INTO site_settings ( site_name, site_tagline, hero_title, hero_subtitle, hero_cta_text, hero_cta_link, feature1_title, feature1_description, feature1_icon, feature2_title, feature2_description, feature2_icon, feature3_title, feature3_description, feature3_icon ) VALUES ( 'ScholarPress', 'Advancing Knowledge Through Open Research', 'Publish Your Research With ScholarPress', 'A modern platform for academic publishing, peer review, and open access research dissemination.', 'Submit Your Research', '/submit', 'Open Access', 'Making research freely accessible to everyone worldwide.', 'fa-unlock-alt', 'Peer Review', 'Rigorous peer review process ensuring quality research.', 'fa-users', 'Fast Publication', 'Streamlined publication process for timely dissemination.', 'fa-rocket' );