-- Full schema for the client/admin portal.
--
-- Safe to re-run: every statement is CREATE TABLE IF NOT EXISTS. Changes to
-- existing installs are applied by database/migrate.php, which owns the
-- incremental ALTERs.

CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  role VARCHAR(50) NOT NULL DEFAULT 'client',
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX users_role_index (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Failed login counter, keyed by "email|ip". Held in the database rather than
-- the session because an attacker simply drops the session cookie between
-- attempts, which makes a session-based counter throttle nobody.
CREATE TABLE IF NOT EXISTS login_attempts (
  identifier VARCHAR(255) NOT NULL PRIMARY KEY,
  attempts INT UNSIGNED NOT NULL DEFAULT 0,
  last_attempt_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX login_attempts_last_attempt_index (last_attempt_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS languages (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  direction ENUM('LTR','RTL') NOT NULL,
  attribute VARCHAR(20) NOT NULL UNIQUE,
  font VARCHAR(100) NOT NULL,
  display_on_ui TINYINT(1) NOT NULL DEFAULT 1,
  -- The order the language switcher lists them in, arranged on the Languages screen.
  sort_order INT NOT NULL DEFAULT 0,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX languages_display_on_ui_index (display_on_ui)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pages (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  language_id BIGINT UNSIGNED NOT NULL,
  is_published TINYINT(1) NOT NULL DEFAULT 1,
  allow_search_indexing TINYINT(1) NOT NULL DEFAULT 1,
  page_name VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL,
  meta_title VARCHAR(255) NOT NULL,
  meta_description TEXT NOT NULL,
  meta_keywords TEXT NULL,
  featured_image VARCHAR(255) NULL,
  -- Banner shown above the main content. All optional: a page with no banner
  -- title simply renders without one.
  banner_title VARCHAR(255) NULL,
  banner_description TEXT NULL,
  input_placeholder VARCHAR(255) NULL,
  search_button_text VARCHAR(100) NULL,
  -- Which downloader the banner search drives on this page.
  downloader_type VARCHAR(20) NOT NULL DEFAULT 'instagram',
  content_description LONGTEXT NOT NULL,
  feature_heading LONGTEXT NOT NULL,
  feature_descriptions LONGTEXT NOT NULL,
  instructions_description LONGTEXT NOT NULL,
  -- "How to Use": a heading and a JSON list of steps. Optional, so both may
  -- be empty and the section is then left off the page.
  how_to_heading LONGTEXT NULL,
  how_to_steps LONGTEXT NULL,
  -- "Additional Content": one optional block shown just above the FAQ.
  additional_content LONGTEXT NULL,
  faq_heading LONGTEXT NOT NULL,
  faq_tabs LONGTEXT NOT NULL,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY pages_language_slug_unique (language_id, slug),
  -- Covers the admin listing filters (language + publish + index flags) and the
  -- created_at ordering it always applies.
  INDEX pages_language_published_index (language_id, is_published),
  INDEX pages_language_indexing_index (language_id, allow_search_indexing),
  INDEX pages_created_at_index (created_at),
  INDEX pages_featured_image_index (featured_image),
  CONSTRAINT pages_language_id_foreign FOREIGN KEY (language_id) REFERENCES languages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS footer_pages (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  language_id BIGINT UNSIGNED NOT NULL,
  is_published TINYINT(1) NOT NULL DEFAULT 1,
  allow_search_indexing TINYINT(1) NOT NULL DEFAULT 1,
  add_contact_form TINYINT(1) NOT NULL DEFAULT 0,
  sort_order INT NOT NULL DEFAULT 0,
  page_name VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL,
  meta_title VARCHAR(255) NOT NULL,
  meta_description TEXT NOT NULL,
  meta_keywords TEXT NULL,
  content LONGTEXT NOT NULL,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY footer_pages_language_slug_unique (language_id, slug),
  INDEX footer_pages_language_published_index (language_id, is_published),
  INDEX footer_pages_language_indexing_index (language_id, allow_search_indexing),
  INDEX footer_pages_sort_order_index (sort_order),
  CONSTRAINT footer_pages_language_id_foreign FOREIGN KEY (language_id) REFERENCES languages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS bottom_footer_settings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  show_footer TINYINT(1) NOT NULL DEFAULT 1,
  background_color VARCHAR(50) NOT NULL,
  item_color VARCHAR(50) NOT NULL,
  item_hover_color VARCHAR(50) NOT NULL,
  item_active_color VARCHAR(50) NOT NULL,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS language_switcher_settings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  background_color VARCHAR(50) NOT NULL,
  item_color VARCHAR(50) NOT NULL,
  item_hover_color VARCHAR(50) NOT NULL,
  -- Nullable, unlike the four above: blank means "use the built-in tint".
  item_hover_background_color VARCHAR(50) NULL,
  item_active_color VARCHAR(50) NOT NULL,
  item_active_background_color VARCHAR(50) NULL,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS header_settings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  sticky_header TINYINT(1) NOT NULL DEFAULT 1,
  background_color VARCHAR(50) NULL,
  item_color VARCHAR(50) NULL,
  item_hover_color VARCHAR(50) NULL,
  item_hover_background_color VARCHAR(50) NULL,
  item_active_color VARCHAR(50) NULL,
  item_active_background_color VARCHAR(50) NULL,
  submenu_background_color VARCHAR(50) NULL,
  submenu_item_color VARCHAR(50) NULL,
  submenu_item_hover_color VARCHAR(50) NULL,
  submenu_item_hover_background_color VARCHAR(50) NULL,
  submenu_item_active_color VARCHAR(50) NULL,
  submenu_item_active_background_color VARCHAR(50) NULL,
  mobile_menu_background_color VARCHAR(50) NULL,
  mobile_menu_item_color VARCHAR(50) NULL,
  mobile_menu_item_hover_color VARCHAR(50) NULL,
  mobile_menu_item_hover_background_color VARCHAR(50) NULL,
  mobile_menu_item_active_color VARCHAR(50) NULL,
  mobile_menu_item_active_background_color VARCHAR(50) NULL,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS custom_scripts_settings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  header_scripts LONGTEXT NULL,
  body_scripts LONGTEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Site-wide options that are not tied to one area of the UI. default_language_id
-- is the language served without a URL prefix; NULL falls back to the earliest
-- created language that is shown on the site. proxy_url lived on the Ads screen
-- until it was moved here, being a site-wide address rather than an ad setting.
CREATE TABLE IF NOT EXISTS general_settings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  default_language_id BIGINT UNSIGNED NULL,
  proxy_url VARCHAR(255) NULL,
  -- A new site is hidden from search engines until someone opens it up.
  discourage_search_engines TINYINT(1) NOT NULL DEFAULT 1,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX general_settings_default_language_id_index (default_language_id),
  CONSTRAINT general_settings_default_language_id_foreign
    FOREIGN KEY (default_language_id) REFERENCES languages (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Blog articles. Same shape as a footer page — one language, one slug,
-- one body — with a featured image for its share preview.
CREATE TABLE IF NOT EXISTS blogs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  language_id BIGINT UNSIGNED NOT NULL,
  is_published TINYINT(1) NOT NULL DEFAULT 1,
  -- The one article held at the top of the blog index. At most one per
  -- language: marking a second one clears the first.
  is_featured TINYINT(1) NOT NULL DEFAULT 0,
  allow_search_indexing TINYINT(1) NOT NULL DEFAULT 1,
  title VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL,
  -- The card blurb on the blog index. Optional: an empty one is taken from
  -- the opening of the article instead.
  excerpt TEXT NULL,
  meta_title VARCHAR(255) NOT NULL,
  meta_description TEXT NOT NULL,
  meta_keywords TEXT NULL,
  featured_image VARCHAR(255) NULL,
  content LONGTEXT NOT NULL,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY blogs_language_slug_unique (language_id, slug),
  INDEX blogs_language_published_index (language_id, is_published),
  INDEX blogs_language_featured_index (language_id, is_featured),
  INDEX blogs_created_at_index (created_at),
  CONSTRAINT blogs_language_id_foreign FOREIGN KEY (language_id) REFERENCES languages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Search and download activity from the public downloader.
--
-- One row per attempt, updated in place as it moves through its steps, so the
-- dashboard shows both what is happening now and what happened before. Written
-- by visitors rather than admins, hence no created_by.
CREATE TABLE IF NOT EXISTS activity_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  service VARCHAR(20) NOT NULL DEFAULT "instagram",
  event VARCHAR(20) NOT NULL,
  status VARCHAR(40) NOT NULL,
  query VARCHAR(500) NULL,
  media_type VARCHAR(20) NULL,
  result_count INT UNSIGNED NULL,
  message VARCHAR(500) NULL,
  ip_address VARCHAR(45) NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX activity_logs_service_index (service, created_at),
  INDEX activity_logs_event_index (event),
  INDEX activity_logs_status_index (status),
  INDEX activity_logs_created_at_index (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Admin-authored notes shown on the public site directly below the banner.
-- Each note belongs to one language so it only appears on that language's
-- pages; show_on_ui lets a note be kept as a draft without deleting it.
CREATE TABLE IF NOT EXISTS notes (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  language_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(255) NULL,
  description LONGTEXT NOT NULL,
  show_on_ui TINYINT(1) NOT NULL DEFAULT 1,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX notes_language_visible_index (language_id, show_on_ui),
  CONSTRAINT notes_language_id_foreign
    FOREIGN KEY (language_id) REFERENCES languages (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Colours for the banner at the top of the public site. Every column is
-- nullable: blank means "use the site default" rather than black, which is what
-- an empty colour input would otherwise store.
CREATE TABLE IF NOT EXISTS banner_settings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  show_banner TINYINT(1) NOT NULL DEFAULT 1,
  background_color VARCHAR(50) NULL,
  heading_color VARCHAR(50) NULL,
  sub_heading_color VARCHAR(50) NULL,
  search_button_border_color VARCHAR(50) NULL,
  search_button_bg_color VARCHAR(50) NULL,
  search_button_text_color VARCHAR(50) NULL,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Optional hand-written replacements for the generated sitemap.xml and
-- robots.txt. Blank means "serve the generated one". Two tables rather than
-- one, because Setting::save() writes every column of the table it owns and two
-- screens sharing a table would blank each other's field.
CREATE TABLE IF NOT EXISTS sitemap_settings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  custom_sitemap LONGTEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS robots_settings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  custom_robots LONGTEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Settings > Layout. Every column optional: blank means the built-in default
-- (the original section order, 1290px wide, 50px between sections).
CREATE TABLE IF NOT EXISTS layout_settings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  section_order VARCHAR(255) NULL,
  container_width SMALLINT UNSIGNED NULL,
  section_spacing SMALLINT UNSIGNED NULL,
  -- Below this width the header shows the hamburger; blank means 1024.
  menu_breakpoint SMALLINT UNSIGNED NULL,
  -- "default" shows the default language's footer on every language; blank
  -- means each language shows its own.
  footer_source VARCHAR(20) NULL,
  -- Which bottom corner holds the "back to top" button; blank means none.
  scroll_to_top VARCHAR(20) NULL,
  -- How the Features section is drawn; blank means the original cards.
  feature_design VARCHAR(30) NULL,
  -- How the How to Use section is drawn; blank means the original journey.
  how_to_design VARCHAR(30) NULL,
  -- How the FAQ is drawn; blank means the original cards.
  faq_design VARCHAR(30) NULL,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS custom_css_settings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  custom_css LONGTEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS seo_settings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  site_name VARCHAR(255) NULL,
  site_url VARCHAR(255) NULL,
  site_description TEXT NULL,
  site_logo VARCHAR(255) NULL,
  favicon_ico VARCHAR(255) NULL,
  favicon_192 VARCHAR(255) NULL,
  favicon_512 VARCHAR(255) NULL,
  open_graph_image VARCHAR(255) NULL,
  google_site_verification VARCHAR(255) NULL,
  bing_site_verification VARCHAR(255) NULL,
  google_analytics_id VARCHAR(100) NULL,
  google_tag_manager_id VARCHAR(100) NULL,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS menu_redirects (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  redirect_from VARCHAR(255) NOT NULL UNIQUE,
  redirect_to VARCHAR(255) NOT NULL,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ads_settings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  enable_ads TINYINT(1) NOT NULL DEFAULT 0,
  header_script LONGTEXT NULL,
  body_script LONGTEXT NULL,
  -- Rendered on the public banner, directly under the search field.
  banner_ad LONGTEXT NULL,
  -- Rendered at the top of the results page (/viewer), which has no banner.
  viewer_page_ad LONGTEXT NULL,
  download_button_ad VARCHAR(255) NULL,
  -- Opened in a new tab when a visitor submits a search.
  search_button_ad VARCHAR(255) NULL,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Submissions from the contact form that footer pages can opt into via
-- footer_pages.add_contact_form.
CREATE TABLE IF NOT EXISTS contact_messages (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  footer_page_id BIGINT UNSIGNED NULL,
  language_id BIGINT UNSIGNED NULL,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  subject VARCHAR(255) NULL,
  message TEXT NOT NULL,
  ip_address VARCHAR(45) NULL,
  is_read TINYINT(1) NOT NULL DEFAULT 0,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX contact_messages_created_index (created_at),
  INDEX contact_messages_read_index (is_read),
  -- The message outlives the page it came from, so the link is nulled rather
  -- than cascading the submission away with it.
  CONSTRAINT contact_messages_footer_page_foreign FOREIGN KEY (footer_page_id) REFERENCES footer_pages(id) ON DELETE SET NULL,
  CONSTRAINT contact_messages_language_foreign FOREIGN KEY (language_id) REFERENCES languages(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS menus (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  language_id BIGINT UNSIGNED NOT NULL,
  menu_name VARCHAR(255) NOT NULL DEFAULT 'Main Menu',
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY menus_language_id_unique (language_id),
  CONSTRAINT menus_language_id_foreign FOREIGN KEY (language_id) REFERENCES languages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS menu_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  menu_id BIGINT UNSIGNED NOT NULL,
  parent_id BIGINT UNSIGNED NULL,
  item_type VARCHAR(50) NOT NULL,
  reference_id BIGINT UNSIGNED NULL,
  title VARCHAR(255) NOT NULL,
  url VARCHAR(500) NOT NULL,
  target VARCHAR(20) NOT NULL DEFAULT '_self',
  sort_order INT NOT NULL DEFAULT 0,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX menu_items_menu_sort_index (menu_id, sort_order),
  INDEX menu_items_parent_id_index (parent_id),
  -- Used when a page or footer page is deleted and its menu rows go with it.
  INDEX menu_items_reference_index (item_type, reference_id),
  CONSTRAINT menu_items_menu_id_foreign FOREIGN KEY (menu_id) REFERENCES menus(id) ON DELETE CASCADE,
  CONSTRAINT menu_items_parent_id_foreign FOREIGN KEY (parent_id) REFERENCES menu_items(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*
 * The bottom footer's wording, one row per language. Visibility and colours
 * stay on bottom_footer_settings: those describe the bar itself, and a site
 * that changed colour when a visitor switched language would look broken.
 */
CREATE TABLE IF NOT EXISTS bottom_footer_texts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  language_id BIGINT UNSIGNED NOT NULL,
  description LONGTEXT NOT NULL,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY bottom_footer_texts_language_unique (language_id),
  CONSTRAINT bottom_footer_texts_language_id_foreign FOREIGN KEY (language_id) REFERENCES languages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*
 * The blog index page (/blog), written once per language: its heading and
 * intro, plus the metadata search engines read. Every field is optional — a
 * blank one falls back to the built-in wording.
 */
CREATE TABLE IF NOT EXISTS blog_page_settings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  language_id BIGINT UNSIGNED NOT NULL,
  page_title VARCHAR(255) NULL,
  -- Rich text from the admin editor, hence LONGTEXT like every other body field.
  page_description LONGTEXT NULL,
  meta_title VARCHAR(255) NULL,
  meta_description TEXT NULL,
  meta_keywords TEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY blog_page_settings_language_unique (language_id),
  CONSTRAINT blog_page_settings_language_id_foreign FOREIGN KEY (language_id) REFERENCES languages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*
 * The Chrome extension the admin generates from Settings > Chrome Extension.
 * One row for the whole site: the wording that goes in the popup and in the
 * extension's manifest. Artwork is not stored here — the builder takes the
 * site logo and favicon already uploaded on the SEO screen.
 */
CREATE TABLE IF NOT EXISTS chrome_extension_settings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  popup_title VARCHAR(255) NULL,
  popup_description TEXT NULL,
  manifest_title VARCHAR(255) NULL,
  manifest_description TEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;