-- ============================================================ -- Aircrew Remembered Archive Manager -- Database Schema v2.0 -- -- Run this once in phpMyAdmin on your MySQL database. -- All tables use utf8mb4 for full Unicode support -- including special characters in foreign names. -- ============================================================ -- ============================================================ -- REPORTS — one row per published page -- ============================================================ CREATE TABLE IF NOT EXISTS `ar_reports` ( `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -- Page identity `slug` VARCHAR(255) NOT NULL UNIQUE COMMENT 'URL filename e.g. hopgood-617-squadron', `template_type` VARCHAR(50) NOT NULL DEFAULT 'loss' COMMENT 'loss | luftwaffe | us | soviet | korea | waaf | 625sqd | obit | telegraph_obit | poetry | general | editorial | listing | rob_philips', `parent_section` VARCHAR(255) DEFAULT '' COMMENT 'Folder path e.g. /reports/allied/', -- SEO & navigation (from Perch Details tab) `page_title` VARCHAR(500) DEFAULT '' COMMENT 'Browser tag', `nav_text` VARCHAR(500) DEFAULT '' COMMENT 'Text shown on listing pages — SEO anchor', `meta_description` TEXT COMMENT 'Meta description for Google', `meta_keywords` VARCHAR(500) DEFAULT '' COMMENT 'Meta keywords comma separated', `noindex` TINYINT(1) DEFAULT 0, `nofollow` TINYINT(1) DEFAULT 0, `nosnippet` TINYINT(1) DEFAULT 0, -- Status `is_draft` TINYINT(1) DEFAULT 0 COMMENT '1 = draft, 0 = published', `is_hidden_nav` TINYINT(1) DEFAULT 0 COMMENT 'Hide from main navigation', -- Core content fields (shared by almost all templates) `picture` TEXT COMMENT 'Squadron badge / pilot portrait img HTML', `picture_desc` VARCHAR(500) DEFAULT '' COMMENT 'Alt text / description for picture — required', `headline` TEXT COMMENT 'Mission title / main heading', `story` LONGTEXT COMMENT 'Main report body — rich HTML', `supplementary` LONGTEXT COMMENT 'Additional content added after original publication', `author_date` VARCHAR(500) DEFAULT '' COMMENT 'Reviewer name and date stamp e.g. RS 31.05.2024', `hidden_content` TEXT COMMENT 'Hidden div — search highlight keywords', `reader_comment` LONGTEXT COMMENT 'Published reader responses (editorial approval required)', -- Obituary fields `obit_body` LONGTEXT COMMENT 'Obituary main text (may contain Textile markup)', `obit_library` LONGTEXT COMMENT 'Obituary library sidebar addition', -- General content fields `top_strap` TEXT COMMENT 'Strapline below headline (EditorialTemplate)', -- Poetry fields `poem_title` VARCHAR(500) DEFAULT '', `poet_name` VARCHAR(500) DEFAULT '', `poem_text` LONGTEXT, `poetry_picture_1` TEXT, `poetry_picture_2` TEXT, `poetry_picture_3` TEXT, `poetry_picture_4` TEXT, `poetry_picture_5` TEXT, `poetry_picture_6` TEXT, `poetry_picture_7` TEXT, -- Rob Philips / multi-chapter fields `loss_table` LONGTEXT COMMENT 'Structured loss data table', `crash_site_story` LONGTEXT COMMENT 'Crash site narrative', `previous_chapter` VARCHAR(500) DEFAULT '' COMMENT 'URL of previous chapter', `next_chapter` VARCHAR(500) DEFAULT '' COMMENT 'URL of next chapter', `contents_summary` TEXT COMMENT 'Chapter contents summary', `abbreviations` TEXT COMMENT 'Abbreviations list', `story_top_paras` TEXT COMMENT 'Opening paragraphs above main story', -- Author archive association `author_archive_id` INT UNSIGNED DEFAULT NULL COMMENT 'FK to ar_author_archives if part of a named archive', -- Timestamps `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `created_by` INT UNSIGNED DEFAULT NULL COMMENT 'FK to ar_users', `updated_by` INT UNSIGNED DEFAULT NULL COMMENT 'FK to ar_users', INDEX `idx_slug` (`slug`), INDEX `idx_template` (`template_type`), INDEX `idx_draft` (`is_draft`), INDEX `idx_author_archive`(`author_archive_id`), FULLTEXT INDEX `idx_search` (`headline`, `nav_text`, `story`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================ -- NAVIGATION GROUPS (tags) -- Replaces Perch's navigation group system. -- Each group corresponds to a listing page. -- ============================================================ CREATE TABLE IF NOT EXISTS `ar_nav_groups` ( `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR(255) NOT NULL COMMENT 'Display name e.g. "1943" or "Supermarine Spitfire"', `slug` VARCHAR(255) NOT NULL UNIQUE COMMENT 'URL slug e.g. "1943" or "supermarine-spitfire"', `listing_page` VARCHAR(500) DEFAULT '' COMMENT 'Path to the listing page e.g. /list-1943.php', `is_active` TINYINT(1) DEFAULT 1 COMMENT '1 = has an active listing page, 0 = dormant', `sort_order` SMALLINT DEFAULT 0, `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX `idx_slug` (`slug`), INDEX `idx_active` (`is_active`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================ -- REPORT ↔ NAV GROUP (many-to-many join) -- ============================================================ CREATE TABLE IF NOT EXISTS `ar_report_groups` ( `report_id` INT UNSIGNED NOT NULL, `group_id` INT UNSIGNED NOT NULL, PRIMARY KEY (`report_id`, `group_id`), INDEX `idx_group` (`group_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================ -- MEDIA LIBRARY -- Replaces Perch's flat resources folder. -- Handles images, documents, and audio. -- ============================================================ CREATE TABLE IF NOT EXISTS `ar_media` ( `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, `filename` VARCHAR(255) NOT NULL COMMENT 'Storage filename (unique, slugified)', `original_name` VARCHAR(500) DEFAULT '' COMMENT 'Original filename as uploaded', `media_type` ENUM('image','document','audio','video','other') DEFAULT 'image', `path_original` VARCHAR(500) DEFAULT '' COMMENT 'Web path to original file', `path_display` VARCHAR(500) DEFAULT '' COMMENT 'Web path to display version (images: max 1200px, q85)', `path_thumb` VARCHAR(500) DEFAULT '' COMMENT 'Web path to thumbnail (images: 280×200px)', `caption` VARCHAR(500) DEFAULT '' COMMENT 'Optional caption / description', `width` SMALLINT UNSIGNED DEFAULT 0, `height` SMALLINT UNSIGNED DEFAULT 0, `filesize_orig` INT UNSIGNED DEFAULT 0 COMMENT 'Original file size in bytes', `filesize_disp` INT UNSIGNED DEFAULT 0 COMMENT 'Display version file size in bytes', `perch_path` VARCHAR(500) DEFAULT '' COMMENT 'Original Perch resources path — for migration reference', `uploaded_at` DATETIME DEFAULT CURRENT_TIMESTAMP, `uploaded_by` INT UNSIGNED DEFAULT NULL, INDEX `idx_filename` (`filename`), INDEX `idx_type` (`media_type`), FULLTEXT INDEX `idx_caption` (`caption`, `original_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================ -- AUTHOR ARCHIVES -- Self-contained collections attributed to a single author. -- e.g. Rob Philips Memorial Archive, McGuiness Archive -- ============================================================ CREATE TABLE IF NOT EXISTS `ar_author_archives` ( `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR(255) NOT NULL COMMENT 'e.g. Rob Philips Memorial Archive', `slug` VARCHAR(255) NOT NULL UNIQUE, `author_name` VARCHAR(255) DEFAULT '', `author_bio` TEXT COMMENT 'Biographical note about the author', `author_portrait` VARCHAR(500) DEFAULT '' COMMENT 'Path to author portrait image', `description` TEXT COMMENT 'Introduction to the archive', `status` ENUM('active','memorial','inactive') DEFAULT 'active' COMMENT 'memorial = author has passed', `memorial_note` TEXT COMMENT 'e.g. "Rob Philips 1940–2014"', `custodian_name` VARCHAR(255) DEFAULT '' COMMENT 'Person responsible for maintaining the archive', `custodian_email` VARCHAR(255) DEFAULT '', `landing_page` VARCHAR(500) DEFAULT '' COMMENT 'URL of the archive home page', `template_type` VARCHAR(50) DEFAULT 'general', `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX `idx_slug` (`slug`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================ -- REPORT VERSIONS (audit trail — Release 2) -- Every save creates a version snapshot. -- ============================================================ CREATE TABLE IF NOT EXISTS `ar_report_versions` ( `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, `report_id` INT UNSIGNED NOT NULL, `saved_by` INT UNSIGNED DEFAULT NULL, `saved_at` DATETIME DEFAULT CURRENT_TIMESTAMP, `headline` TEXT, `story` LONGTEXT, `nav_text` VARCHAR(500) DEFAULT '', `change_note` VARCHAR(500) DEFAULT '' COMMENT 'Optional note about what changed', INDEX `idx_report` (`report_id`), INDEX `idx_saved` (`saved_at`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================ -- ADMIN USERS -- ============================================================ CREATE TABLE IF NOT EXISTS `ar_users` ( `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, `username` VARCHAR(100) NOT NULL UNIQUE, `display_name` VARCHAR(255) DEFAULT '', `email` VARCHAR(255) DEFAULT '', `password_hash` VARCHAR(255) NOT NULL, `role` ENUM('admin','editor','author') DEFAULT 'editor', `is_active` TINYINT(1) DEFAULT 1, `last_login` DATETIME DEFAULT NULL, `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Default admin user — password: changeme123 -- CHANGE THIS IMMEDIATELY after first login INSERT INTO `ar_users` (`username`, `display_name`, `password_hash`, `role`) VALUES ('admin', 'Administrator', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin'); -- ============================================================ -- SEED: Active navigation groups from your live listing pages -- ============================================================ INSERT INTO `ar_nav_groups` (`name`, `slug`, `listing_page`, `is_active`, `sort_order`) VALUES ('1914 - 1918', '1914-1918', '/list-of-pages-related-to-1914-1918.html', 1, 1), ('1939', '1939', '/list-of-pages-related-to-1939.html', 1, 2), ('1940', '1940', '/list-of-pages-related-to-1940.html', 1, 3), ('1941', '1941', '/list-of-pages-related-to-1941.html', 1, 4), ('1942', '1942', '/list-of-pages-relating-to-1942.html', 1, 5), ('1943', '1943', '/list-of-pages-relating-to-1943.html', 1, 6), ('1944', '1944', '/list-of-pages-relating-to-1944.html', 1, 7), ('1945', '1945', '/list-of-pages-relating-to-1945.html', 1, 8), ('2024 Archive Reports', '2024archivereports', '', 1, 9), ('2025 Archive Reports', '2025archivereports', '', 1, 10), ('625 Sqd RAF', '625-sqd-raf', '/list-of-pages-related-to-625-sqd-raf.html', 1, 20), ('Accident', 'accident', '', 1, 30), ('Afghanistan', 'afghanistan', '/list-of-pages-relating-to-afghanistan.html', 1, 31), ('Air Sea Rescue', 'air-sea-rescue', '/list-of-pages-relating-to-air-sea-rescue.html', 1, 32), ('Airborne Support', 'airborne-support', '/list-of-pages-relating-to-airborne-support.html', 1, 33), ('Awards', 'awards', '/awards-list-of-relevant-pages.html', 1, 40), ('Belgium', 'belgium', '/belgium-list-of-relevant-pages.html', 1, 41), ('Coastal Command', 'coastal-command', '', 1, 50), ('Editorial Content', 'editorial-content', '', 1, 60), ('Exceptional Interest', 'exceptional-interest', '/list-of-pages-of-exceptional-interest.html', 1, 61), ('Greece and Middle East', 'greece-middle-east', '/list-of-pages-related-to-greece-and-middle-east.html', 1, 70), ('Intelligence Reports', 'intelligence-reports', '/intelligence-reports-list-of-relevant-pages.html', 1, 71), ('Korea', 'korea', '/list-of-pages-related-to-korea.html', 1, 72), ('Mosquito', 'mosquito', '/list-of-pages-relating-to-mosquito.html', 1, 80), ('Norway Denmark Sweden Finland','norway-denmark-sweden', '/list-of-pages-related-to-norway-denmark-sweden-and-finland.html', 1, 81), ('Post-War', 'post-war', '/list-of-pages-relating-to-post-war.html', 1, 82), ('Prisoners of War', 'prisoners-of-war', '/prisoners-of-war-list-of-relevant-pages.html', 1, 83), ('Profiles', 'profiles', '', 1, 84), ('Recent Archive Reports', 'recent-archive-reports', '/recent-archive-reports.html', 1, 85), ('Rob Philips Memorial Archive','rob-philips', '/rob-philips-memorial-archive.html', 1, 86), ('Russia and Soviet Union', 'russia-soviet-union', '/russia-and-soviet-union-relevant-pages.html', 1, 87), ('Seeking Identities', 'seeking-identities', '/list-of-pages-related-to-seeking-identities-and-information.html', 1, 88), ('South Africa', 'south-africa', '/list-of-pages-related-to-south-africa.html', 1, 89), ('Special Significance', 'special-significance', '/list-of-pages-of-special-significance.html', 1, 90), ('Supermarine Spitfire', 'spitfire', '/list-of-pages-relating-to-supermarine-spitfire.html', 1, 91), ('B-24 Liberator', 'b-24-liberator', '/list-of-pages-relating-to-b-24-liberator.html', 1, 92), ('Veterans', 'veterans', '/veterans-list-of-relevant-pages.html', 1, 93), ('Vietnam', 'vietnam', '/vietnam-list-of-relevant-pages.html', 1, 94), ('WAAF/WRAF', 'waaf-wraf', '', 1, 95), ('War Crime', 'war-crime', '/list-of-pages-relating-to-war-crimes.html', 1, 96), ('War Memorial', 'war-memorial', '', 1, 97), ('West Indies', 'west-indies', '', 1, 98), ('zzEverything', 'zzeverything', '', 1, 999); -- ============================================================ -- SEED: Author archives -- ============================================================ INSERT INTO `ar_author_archives` (`name`, `slug`, `author_name`, `status`, `memorial_note`, `custodian_name`, `template_type`) VALUES ('Rob Philips Memorial Archive', 'rob-philips-memorial-archive', 'Rob Philips', 'memorial', 'Rob Philips dedicated decades to researching Dutch aviation losses of the Second World War. He entrusted this archive to Aircrew Remembered shortly before his passing.', 'Kelvin Youngs', 'rob_philips'), ('McGuiness Archive', 'mcguiness-archive', 'Paul McGuiness', 'active', '', 'Kelvin Youngs', 'general');