-- ============================================================
--  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 <title> 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;


-- ============================================================
--  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');
