-- =====================================================================================
-- Morvia — target database schema (final design, 2026-11-12)
-- MySQL 8.0 · InnoDB · utf8mb4_unicode_ci · times stored in UTC · money in SAR
--
-- Source of truth for migrations. Rationale, status maps and money flows:
-- docs/database/schema-design.md. Every table cites its BRD feature / decision IDs
-- (docs/brd/BRD-Morvia-Project-Guide-en.md, docs/DECISIONS.md).
--
-- Conventions
--   * ids: every table has `id` (BIGINT UNSIGNED auto-increment, used ONLY for foreign keys and
--     joins) and `uuid` (CHAR(36), UNIQUE, the public id — all API routes, request bodies and
--     responses for the Flutter and Vue apps use the uuid, never the id; TD-14). The app
--     generates the uuid (ordered UUID); DEFAULT (UUID()) is only a backstop. Not on composite-key
--     pivot tables, `notifications` (its id already is a uuid) or `media` (has its own uuid).
--     Human codes (ENR-…, BKG-…) are VARCHAR(20) UNIQUE.
--   * money: DECIMAL(12,2); percentages: DECIMAL(5,2) (15.00 = 15 %). Never FLOAT.
--   * status / type columns: VARCHAR, values = a PHP backed enum (listed in COMMENT).
--   * user-facing text: {table}_translations (locale + fields, unique pair, cascade).
--   * files: Spatie `media` table; collection names are listed per table, never URL columns.
--   * history is never hard-deleted (BR-ACC-03): soft deletes on master data, no CASCADE
--     on history tables (only translations cascade with their parent).
--   * "one open row" rules use a STORED generated key that is NULL when the row is closed,
--     so a UNIQUE index enforces them without blocking history.
-- =====================================================================================

SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;

-- =====================================================================================
-- 1. BASE TABLES (kept from the project base; changes marked "TD-11")
-- Laravel infrastructure tables (cache, jobs, failed_jobs, telescope) are omitted.
-- Dropped from the base (TD-11): wallets, transactions, withdraw_requests, governorates,
-- governorate_translations, regions, shipping zones, addresses.
-- =====================================================================================

-- countries: nationality (TRR-002) and address country (DEC-03); TD-09
CREATE TABLE countries (
  id                     BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  short_name             VARCHAR(5)  NULL COMMENT 'ISO code, e.g. SA',
  phone_code             VARCHAR(5)  NULL,
  phone_starting_number  VARCHAR(255) NULL,
  phone_length           INT NULL,
  is_active              BOOLEAN NOT NULL DEFAULT 1,
  created_at             TIMESTAMP NULL,
  updated_at             TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY countries_uuid (uuid),
  KEY countries_active (is_active)
) ENGINE=InnoDB;

CREATE TABLE country_translations (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  country_id   BIGINT UNSIGNED NOT NULL,
  locale       VARCHAR(10) NOT NULL,
  name         VARCHAR(255) NOT NULL,
  nationality  VARCHAR(255) NULL,
  PRIMARY KEY (id),
  UNIQUE KEY country_translations_uuid (uuid),
  UNIQUE KEY country_translations_unique (country_id, locale),
  CONSTRAINT country_translations_country FOREIGN KEY (country_id) REFERENCES countries (id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- cities: ADM-043, BR-CRS-11 (cities belong directly to a country; no governorates — TD-11)
CREATE TABLE cities (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  country_id  BIGINT UNSIGNED NOT NULL,
  sort_order  INT UNSIGNED NOT NULL DEFAULT 0,
  is_active   BOOLEAN NOT NULL DEFAULT 1,
  created_at  TIMESTAMP NULL,
  updated_at  TIMESTAMP NULL,
  deleted_at  TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY cities_uuid (uuid),
  KEY cities_country_active (country_id, is_active),
  CONSTRAINT cities_country FOREIGN KEY (country_id) REFERENCES countries (id)
) ENGINE=InnoDB;

CREATE TABLE city_translations (
  id       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  city_id  BIGINT UNSIGNED NOT NULL,
  locale   VARCHAR(10) NOT NULL,
  name     VARCHAR(255) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY city_translations_uuid (uuid),
  UNIQUE KEY city_translations_unique (city_id, locale),
  CONSTRAINT city_translations_city FOREIGN KEY (city_id) REFERENCES cities (id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- roles / permissions: admin roles and permissions (ADM-049); base RBAC
CREATE TABLE roles (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  parent_id   BIGINT UNSIGNED NULL,
  level       SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  slug        VARCHAR(255) NULL COMMENT 'machine key',
  is_active   BOOLEAN NOT NULL DEFAULT 1,
  created_at  TIMESTAMP NULL,
  updated_at  TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY roles_uuid (uuid),
  CONSTRAINT roles_parent FOREIGN KEY (parent_id) REFERENCES roles (id)
) ENGINE=InnoDB;

CREATE TABLE role_translations (
  id       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  role_id  BIGINT UNSIGNED NOT NULL,
  locale   VARCHAR(10) NOT NULL,
  name     VARCHAR(255) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY role_translations_uuid (uuid),
  UNIQUE KEY role_translations_unique (role_id, locale),
  CONSTRAINT role_translations_role FOREIGN KEY (role_id) REFERENCES roles (id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE permissions (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  name        VARCHAR(255) NULL,
  route_name  VARCHAR(255) NULL COMMENT 'admin route name = permission key',
  resource    VARCHAR(50) NULL,
  action      VARCHAR(50) NULL,
  created_at  TIMESTAMP NULL,
  updated_at  TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY permissions_uuid (uuid),
  KEY permissions_resource_action (resource, action)
) ENGINE=InnoDB;

CREATE TABLE permission_translations (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  permission_id  BIGINT UNSIGNED NOT NULL,
  locale         VARCHAR(10) NOT NULL,
  title          VARCHAR(255) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY permission_translations_uuid (uuid),
  UNIQUE KEY permission_translations_unique (permission_id, locale),
  CONSTRAINT permission_translations_permission FOREIGN KEY (permission_id) REFERENCES permissions (id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE permission_role (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  role_id        BIGINT UNSIGNED NOT NULL,
  permission_id  BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY permission_role_uuid (uuid),
  UNIQUE KEY permission_role_unique (role_id, permission_id),
  CONSTRAINT permission_role_role FOREIGN KEY (role_id) REFERENCES roles (id),
  CONSTRAINT permission_role_permission FOREIGN KEY (permission_id) REFERENCES permissions (id)
) ENGINE=InnoDB;

-- users: one account per app (DEC-04): every row has exactly one user_type, and the mobile,
-- email and username are unique per user_type among live (not soft-deleted) accounts.
-- TRN-002, TRN-022, TRR-002, TRR-003, CTR-001, HPV-001, ADM-049; DEC-03, TD-08, TD-09.
-- Media: avatar.
CREATE TABLE users (
  id                       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  user_type                VARCHAR(16) NOT NULL COMMENT 'trainee, trainer, center, provider, admin',
  role_id                  BIGINT UNSIGNED NULL COMMENT 'admin role (ADM-049)',
  full_name                VARCHAR(255) NOT NULL,
  username                 VARCHAR(100) NULL COMMENT 'login for affiliated trainer, center, provider, admin (TRR-003, CTR-001, HPV-001)',
  email                    VARCHAR(255) NULL,
  phone_code               VARCHAR(10) NULL,
  phone                    VARCHAR(20) NULL,
  password                 VARCHAR(255) NULL,
  gender                   VARCHAR(10) NULL COMMENT 'male, female',
  birth_date               DATE NULL,
  country_id               BIGINT UNSIGNED NULL COMMENT 'country of residence (DEC-03, TD-09)',
  city_id                  BIGINT UNSIGNED NULL,
  address                  TEXT NULL COMMENT 'free text (DEC-03)',
  locale                   VARCHAR(5) NOT NULL DEFAULT 'ar' COMMENT 'ar, en — notification language',
  is_active                BOOLEAN NOT NULL DEFAULT 1 COMMENT 'false = suspended by the admin',
  suspension_reason        TEXT NULL,
  allow_notifications      BOOLEAN NOT NULL DEFAULT 1,
  terms_accepted_at        TIMESTAMP NULL,
  phone_verified_at        TIMESTAMP NULL,
  email_verified_at        TIMESTAMP NULL,
  last_login_at            TIMESTAMP NULL,
  last_password_change_at  TIMESTAMP NULL,
  remember_token           VARCHAR(100) NULL,
  created_at               TIMESTAMP NULL,
  updated_at               TIMESTAMP NULL,
  deleted_at               TIMESTAMP NULL,
  live_key                 TINYINT GENERATED ALWAYS AS (IF(deleted_at IS NULL, 1, NULL)) STORED,
  PRIMARY KEY (id),
  UNIQUE KEY users_uuid (uuid),
  UNIQUE KEY users_type_phone_live (user_type, phone_code, phone, live_key),
  UNIQUE KEY users_type_email_live (user_type, email, live_key),
  UNIQUE KEY users_type_username_live (user_type, username, live_key),
  KEY users_type_active (user_type, is_active),
  CONSTRAINT users_role FOREIGN KEY (role_id) REFERENCES roles (id) ON DELETE SET NULL,
  CONSTRAINT users_country FOREIGN KEY (country_id) REFERENCES countries (id),
  CONSTRAINT users_city FOREIGN KEY (city_id) REFERENCES cities (id)
) ENGINE=InnoDB;

-- permission_user: per-admin extra permissions (base)
CREATE TABLE permission_user (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  user_id        BIGINT UNSIGNED NOT NULL,
  permission_id  BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY permission_user_uuid (uuid),
  UNIQUE KEY permission_user_unique (user_id, permission_id),
  CONSTRAINT permission_user_user FOREIGN KEY (user_id) REFERENCES users (id),
  CONSTRAINT permission_user_permission FOREIGN KEY (permission_id) REFERENCES permissions (id)
) ENGINE=InnoDB;

-- categories: ADM-044, BR-CRS-11 (flat list — the BRD has no sub-categories). Media: icon.
CREATE TABLE categories (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  sort_order  INT UNSIGNED NOT NULL DEFAULT 0,
  is_active   BOOLEAN NOT NULL DEFAULT 1,
  created_at  TIMESTAMP NULL,
  updated_at  TIMESTAMP NULL,
  deleted_at  TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY categories_uuid (uuid),
  KEY categories_active (is_active, sort_order)
) ENGINE=InnoDB;

CREATE TABLE category_translations (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  category_id  BIGINT UNSIGNED NOT NULL,
  locale       VARCHAR(10) NOT NULL,
  name         VARCHAR(255) NOT NULL,
  description  TEXT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY category_translations_uuid (uuid),
  UNIQUE KEY category_translations_unique (category_id, locale),
  CONSTRAINT category_translations_category FOREIGN KEY (category_id) REFERENCES categories (id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- devices: push tokens (PLT-004)
CREATE TABLE devices (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  user_id     BIGINT UNSIGNED NOT NULL,
  token       TEXT NOT NULL,
  type        VARCHAR(10) NOT NULL COMMENT 'ios, android, huawei, web',
  created_at  TIMESTAMP NULL,
  updated_at  TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY devices_uuid (uuid),
  KEY devices_user (user_id),
  CONSTRAINT devices_user FOREIGN KEY (user_id) REFERENCES users (id)
) ENGINE=InnoDB;

-- personal_access_tokens: base (Sanctum); JWT carries the surface claim (TD-08)
CREATE TABLE personal_access_tokens (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  tokenable_type  VARCHAR(255) NOT NULL,
  tokenable_id    BIGINT UNSIGNED NOT NULL,
  name            VARCHAR(255) NOT NULL,
  token           VARCHAR(64) NOT NULL,
  abilities       TEXT NULL,
  last_used_at    TIMESTAMP NULL,
  expires_at      TIMESTAMP NULL,
  created_at      TIMESTAMP NULL,
  updated_at      TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY personal_access_tokens_uuid (uuid),
  UNIQUE KEY personal_access_tokens_token (token),
  KEY personal_access_tokens_tokenable (tokenable_type, tokenable_id)
) ENGINE=InnoDB;

-- static_pages: terms, privacy, about (TRN-023, ADM-045)
CREATE TABLE static_pages (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  type        VARCHAR(50) NOT NULL COMMENT 'terms, privacy, about, …',
  is_active   BOOLEAN NOT NULL DEFAULT 1,
  created_at  TIMESTAMP NULL,
  updated_at  TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY static_pages_uuid (uuid),
  UNIQUE KEY static_pages_type (type)
) ENGINE=InnoDB;

CREATE TABLE static_page_translations (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  static_page_id  BIGINT UNSIGNED NOT NULL,
  locale          VARCHAR(10) NOT NULL,
  title           VARCHAR(255) NOT NULL,
  content         LONGTEXT NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY static_page_translations_uuid (uuid),
  UNIQUE KEY static_page_translations_unique (static_page_id, locale),
  CONSTRAINT static_page_translations_page FOREIGN KEY (static_page_id) REFERENCES static_pages (id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- sliders: home banners (TRN-006). Media: image. Text moved to translations (TD-11).
CREATE TABLE sliders (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  link        VARCHAR(500) NULL,
  sort_order  INT NOT NULL DEFAULT 0,
  is_active   BOOLEAN NOT NULL DEFAULT 1,
  created_at  TIMESTAMP NULL,
  updated_at  TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY sliders_uuid (uuid),
  KEY sliders_active (is_active, sort_order)
) ENGINE=InnoDB;

CREATE TABLE slider_translations (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  slider_id  BIGINT UNSIGNED NOT NULL,
  locale     VARCHAR(10) NOT NULL,
  title      VARCHAR(255) NOT NULL,
  subtitle   VARCHAR(255) NULL,
  PRIMARY KEY (id),
  UNIQUE KEY slider_translations_uuid (uuid),
  UNIQUE KEY slider_translations_unique (slider_id, locale),
  CONSTRAINT slider_translations_slider FOREIGN KEY (slider_id) REFERENCES sliders (id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- f_a_q_s: FAQ (TRN-023) — base table name kept
CREATE TABLE f_a_q_s (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  type        VARCHAR(20) NOT NULL DEFAULT 'general',
  sort_order  INT NOT NULL DEFAULT 0,
  is_active   BOOLEAN NOT NULL DEFAULT 1,
  created_at  TIMESTAMP NULL,
  updated_at  TIMESTAMP NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE f_a_q_translations (
  id        BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  f_a_q_id  BIGINT UNSIGNED NOT NULL,
  locale    VARCHAR(10) NOT NULL,
  question  VARCHAR(500) NOT NULL,
  answer    TEXT NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY f_a_q_translations_uuid (uuid),
  UNIQUE KEY f_a_q_translations_unique (f_a_q_id, locale),
  CONSTRAINT f_a_q_translations_faq FOREIGN KEY (f_a_q_id) REFERENCES f_a_q_s (id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- contact_messages: support requests (TRN-023, ADM-046). Status New · Reviewed · Closed (§12).
CREATE TABLE contact_messages (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  user_id        BIGINT UNSIGNED NULL,
  name           VARCHAR(255) NOT NULL,
  email          VARCHAR(255) NULL,
  phone          VARCHAR(30) NULL,
  subject        VARCHAR(255) NULL,
  message        TEXT NOT NULL,
  status         VARCHAR(16) NOT NULL DEFAULT 'new' COMMENT 'new, reviewed, closed',
  reply          TEXT NULL,
  internal_note  TEXT NULL,
  replied_by     BIGINT UNSIGNED NULL,
  replied_at     TIMESTAMP NULL,
  created_at     TIMESTAMP NULL,
  updated_at     TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY contact_messages_uuid (uuid),
  KEY contact_messages_status (status, created_at),
  CONSTRAINT contact_messages_user FOREIGN KEY (user_id) REFERENCES users (id),
  CONSTRAINT contact_messages_replied_by FOREIGN KEY (replied_by) REFERENCES users (id)
) ENGINE=InnoDB;

-- audit_logs: admin activity log (ADM-050, BR-LOY-05); also records deadline extensions (D5)
CREATE TABLE audit_logs (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  auditable_type  VARCHAR(255) NOT NULL,
  auditable_id    BIGINT UNSIGNED NOT NULL,
  action          VARCHAR(50) NOT NULL COMMENT 'created, updated, deleted, or a named admin action',
  old_values      JSON NULL,
  new_values      JSON NULL,
  actor_id        BIGINT UNSIGNED NULL,
  ip_address      VARCHAR(45) NULL,
  user_agent      TEXT NULL,
  correlation_id  CHAR(36) NULL,
  created_at      TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY audit_logs_uuid (uuid),
  KEY audit_logs_auditable (auditable_type, auditable_id),
  KEY audit_logs_created (created_at),
  CONSTRAINT audit_logs_actor FOREIGN KEY (actor_id) REFERENCES users (id)
) ENGINE=InnoDB;

-- app_configs: platform settings (ADM-051): commission rates, hall commission rate (DEC-22),
-- points conversion rate, threshold deadlines, OTP limits (DEC-02)…
CREATE TABLE app_configs (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  `key`       VARCHAR(100) NOT NULL,
  type        VARCHAR(20) NOT NULL DEFAULT 'string',
  value       TEXT NULL,
  `group`     VARCHAR(50) NOT NULL,
  metadata    JSON NULL,
  created_at  TIMESTAMP NULL,
  updated_at  TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY app_configs_uuid (uuid),
  UNIQUE KEY app_configs_key (`key`),
  KEY app_configs_group (`group`)
) ENGINE=InnoDB;

-- feature_flags, integrations: base (payment gateway, Nafath, SMS drivers — PLT-002, PLT-003)
CREATE TABLE feature_flags (
  id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  `key`            VARCHAR(100) NOT NULL,
  is_enabled       BOOLEAN NOT NULL DEFAULT 0,
  targeting_rules  JSON NULL,
  description      TEXT NULL,
  created_at       TIMESTAMP NULL,
  updated_at       TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY feature_flags_uuid (uuid),
  UNIQUE KEY feature_flags_key (`key`)
) ENGINE=InnoDB;

CREATE TABLE integrations (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  `group`     VARCHAR(50) NOT NULL,
  `key`       VARCHAR(50) NOT NULL,
  driver      VARCHAR(255) NOT NULL,
  config      JSON NOT NULL,
  countries   JSON NULL,
  platforms   JSON NULL,
  is_active   BOOLEAN NOT NULL DEFAULT 1,
  priority    INT NOT NULL DEFAULT 0,
  created_at  TIMESTAMP NULL,
  updated_at  TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY integrations_uuid (uuid),
  UNIQUE KEY integrations_group_key (`group`, `key`)
) ENGINE=InnoDB;

-- search_histories: TRN-007
CREATE TABLE search_histories (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  user_id        BIGINT UNSIGNED NULL,
  query          VARCHAR(500) NOT NULL,
  type           VARCHAR(50) NOT NULL DEFAULT 'course',
  results_count  INT UNSIGNED NOT NULL DEFAULT 0,
  filters        JSON NULL,
  searched_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY search_histories_uuid (uuid),
  KEY search_histories_user (user_id, searched_at),
  CONSTRAINT search_histories_user FOREIGN KEY (user_id) REFERENCES users (id)
) ENGINE=InnoDB;

-- media: Spatie media library (PLT-005). Every image / file in the schema lives here.
CREATE TABLE media (
  id                     BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  model_type             VARCHAR(255) NOT NULL,
  model_id               BIGINT UNSIGNED NOT NULL,
  uuid                   CHAR(36) NULL,
  collection_name        VARCHAR(255) NOT NULL,
  name                   VARCHAR(255) NOT NULL,
  file_name              VARCHAR(255) NOT NULL,
  mime_type              VARCHAR(255) NULL,
  disk                   VARCHAR(255) NOT NULL,
  conversions_disk       VARCHAR(255) NULL,
  size                   BIGINT UNSIGNED NOT NULL,
  manipulations          JSON NOT NULL,
  custom_properties      JSON NOT NULL,
  generated_conversions  JSON NOT NULL,
  responsive_images      JSON NOT NULL,
  order_column           INT UNSIGNED NULL,
  created_at             TIMESTAMP NULL,
  updated_at             TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY media_uuid (uuid),
  KEY media_model (model_type, model_id, collection_name)
) ENGINE=InnoDB;

-- notifications: Laravel database notifications (PLT-004, TRN-021, TRR-029). Stored in BOTH
-- languages (TD-12): data = {"type": "...", "title": {"ar": "...", "en": "..."},
-- "body": {"ar": "...", "en": "..."}, "related": {"type": "course", "id": 12}}.
CREATE TABLE notifications (
  id               CHAR(36) NOT NULL,
  type             VARCHAR(255) NOT NULL,
  notifiable_type  VARCHAR(255) NOT NULL,
  notifiable_id    BIGINT UNSIGNED NOT NULL,
  data             JSON NOT NULL,
  read_at          TIMESTAMP NULL,
  created_at       TIMESTAMP NULL,
  updated_at       TIMESTAMP NULL,
  PRIMARY KEY (id),
  KEY notifications_notifiable (notifiable_type, notifiable_id, read_at)
) ENGINE=InnoDB;

-- =====================================================================================
-- 2. ACCOUNTS (one users row per app account; the business party lives in its own table
--    when it has its own lifecycle or other data points at it — trainers, centers, providers)
-- =====================================================================================

-- training_centers: CTR-001, CTR-015, ADM-010, ADM-012, D3. Created by the admin after the
-- external contract. Media: logo, licence. Sensitive fields (licence_number, iban) change only
-- through profile_change_requests (DEC-20).
CREATE TABLE training_centers (
  id                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  user_id            BIGINT UNSIGNED NOT NULL COMMENT 'the center account (user_type = center)',
  city_id            BIGINT UNSIGNED NOT NULL,
  address            TEXT NULL,
  contact_phone      VARCHAR(30) NULL,
  licence_number     VARCHAR(100) NULL,
  iban               VARCHAR(34) NULL,
  status             VARCHAR(20) NOT NULL DEFAULT 'pending_activation' COMMENT 'pending_activation, active, suspended',
  suspension_reason  TEXT NULL,
  activated_at       TIMESTAMP NULL,
  created_by         BIGINT UNSIGNED NULL COMMENT 'admin',
  created_at         TIMESTAMP NULL,
  updated_at         TIMESTAMP NULL,
  deleted_at         TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY training_centers_uuid (uuid),
  UNIQUE KEY training_centers_user (user_id),
  KEY training_centers_status (status),
  CONSTRAINT training_centers_user FOREIGN KEY (user_id) REFERENCES users (id),
  CONSTRAINT training_centers_city FOREIGN KEY (city_id) REFERENCES cities (id),
  CONSTRAINT training_centers_created_by FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE training_center_translations (
  id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  training_center_id  BIGINT UNSIGNED NOT NULL,
  locale              VARCHAR(10) NOT NULL,
  name                VARCHAR(255) NOT NULL,
  description         TEXT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY training_center_translations_uuid (uuid),
  UNIQUE KEY training_center_translations_unique (training_center_id, locale),
  CONSTRAINT training_center_translations_center FOREIGN KEY (training_center_id) REFERENCES training_centers (id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- trainers: independent and center-affiliated trainers (TRR-002, TRR-004, TRR-028, CTR-005,
-- ADM-005..008). national_id / nationality come from Nafath (TD-09) and are unique per live trainer.
-- Media: photo, cv, training_licence. Sensitive fields (iban, cv, training_licence) change only
-- through profile_change_requests (DEC-13).
CREATE TABLE trainers (
  id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  user_id             BIGINT UNSIGNED NOT NULL COMMENT 'the trainer account (user_type = trainer)',
  type                VARCHAR(16) NOT NULL COMMENT 'independent, affiliated',
  training_center_id  BIGINT UNSIGNED NULL COMMENT 'current center, affiliated trainers only',
  status              VARCHAR(20) NOT NULL COMMENT 'profile_incomplete, under_review, active, rejected, suspended',
  national_id         VARCHAR(20) NULL COMMENT 'verified by Nafath (independent)',
  nationality_id      BIGINT UNSIGNED NULL COMMENT 'from Nafath (TD-09)',
  id_expires_on       DATE NULL,
  iban                VARCHAR(34) NULL,
  external_links      JSON NULL COMMENT 'trainer page external content links (TRN-009, TRR-028)',
  rejection_reason    TEXT NULL,
  suspension_reason   TEXT NULL,
  reviewed_by         BIGINT UNSIGNED NULL,
  reviewed_at         TIMESTAMP NULL,
  created_at          TIMESTAMP NULL,
  updated_at          TIMESTAMP NULL,
  deleted_at          TIMESTAMP NULL,
  live_key            TINYINT GENERATED ALWAYS AS (IF(deleted_at IS NULL, 1, NULL)) STORED,
  PRIMARY KEY (id),
  UNIQUE KEY trainers_uuid (uuid),
  UNIQUE KEY trainers_user (user_id),
  UNIQUE KEY trainers_national_id_live (national_id, live_key),
  KEY trainers_type_status (type, status),
  KEY trainers_center (training_center_id),
  CONSTRAINT trainers_user FOREIGN KEY (user_id) REFERENCES users (id),
  CONSTRAINT trainers_center FOREIGN KEY (training_center_id) REFERENCES training_centers (id),
  CONSTRAINT trainers_nationality FOREIGN KEY (nationality_id) REFERENCES countries (id),
  CONSTRAINT trainers_reviewed_by FOREIGN KEY (reviewed_by) REFERENCES users (id),
  CONSTRAINT trainers_center_only_affiliated CHECK (type = 'affiliated' OR training_center_id IS NULL)
) ENGINE=InnoDB;

CREATE TABLE trainer_translations (
  id                   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  trainer_id           BIGINT UNSIGNED NOT NULL,
  locale               VARCHAR(10) NOT NULL,
  bio                  TEXT NULL,
  specialisations      TEXT NULL,
  experience           TEXT NULL,
  qualifications       TEXT NULL,
  intro_content        TEXT NULL COMMENT 'trainer page introductory content (TRN-009)',
  PRIMARY KEY (id),
  UNIQUE KEY trainer_translations_uuid (uuid),
  UNIQUE KEY trainer_translations_unique (trainer_id, locale),
  CONSTRAINT trainer_translations_trainer FOREIGN KEY (trainer_id) REFERENCES trainers (id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- trainer_categories: trainer specialisation categories (TRN-009, ADM-044)
CREATE TABLE trainer_categories (
  trainer_id   BIGINT UNSIGNED NOT NULL,
  category_id  BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (trainer_id, category_id),
  CONSTRAINT trainer_categories_trainer FOREIGN KEY (trainer_id) REFERENCES trainers (id),
  CONSTRAINT trainer_categories_category FOREIGN KEY (category_id) REFERENCES categories (id)
) ENGINE=InnoDB;

-- trainer_center_history: which center an affiliated trainer belonged to, and when (ADM-008, CTR-005)
CREATE TABLE trainer_center_history (
  id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  trainer_id          BIGINT UNSIGNED NOT NULL,
  training_center_id  BIGINT UNSIGNED NOT NULL,
  started_at          TIMESTAMP NOT NULL,
  ended_at            TIMESTAMP NULL,
  changed_by          BIGINT UNSIGNED NULL,
  PRIMARY KEY (id),
  UNIQUE KEY trainer_center_history_uuid (uuid),
  KEY trainer_center_history_trainer (trainer_id, started_at),
  CONSTRAINT trainer_center_history_trainer FOREIGN KEY (trainer_id) REFERENCES trainers (id),
  CONSTRAINT trainer_center_history_center FOREIGN KEY (training_center_id) REFERENCES training_centers (id),
  CONSTRAINT trainer_center_history_changed_by FOREIGN KEY (changed_by) REFERENCES users (id)
) ENGINE=InnoDB;

-- nafath_verifications: every Nafath attempt and its returned identity data (TRR-002, PLT-003)
CREATE TABLE nafath_verifications (
  id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  trainer_id          BIGINT UNSIGNED NOT NULL,
  national_id         VARCHAR(20) NOT NULL,
  status              VARCHAR(16) NOT NULL COMMENT 'pending, verified, failed, expired',
  full_name_ar        VARCHAR(255) NULL,
  full_name_en        VARCHAR(255) NULL,
  birth_date          DATE NULL,
  gender              VARCHAR(10) NULL,
  nationality_code    VARCHAR(5) NULL,
  verified_mobile     VARCHAR(20) NULL,
  id_expires_on       DATE NULL,
  provider_reference  VARCHAR(255) NULL,
  response            JSON NULL,
  verified_at         TIMESTAMP NULL,
  created_at          TIMESTAMP NULL,
  updated_at          TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY nafath_verifications_uuid (uuid),
  KEY nafath_verifications_trainer (trainer_id, status),
  CONSTRAINT nafath_verifications_trainer FOREIGN KEY (trainer_id) REFERENCES trainers (id)
) ENGINE=InnoDB;

-- hall_providers: hall & event providers (HPV-001, HPV-002, ADM-015). Media: logo.
CREATE TABLE hall_providers (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  user_id        BIGINT UNSIGNED NOT NULL COMMENT 'the provider account (user_type = provider)',
  entity_type    VARCHAR(30) NOT NULL COMMENT 'hotel, exhibition_venue, other',
  city_id        BIGINT UNSIGNED NOT NULL,
  address        TEXT NULL,
  contact_phone  VARCHAR(30) NULL,
  status         VARCHAR(16) NOT NULL DEFAULT 'active' COMMENT 'active, inactive',
  created_by     BIGINT UNSIGNED NULL COMMENT 'admin',
  created_at     TIMESTAMP NULL,
  updated_at     TIMESTAMP NULL,
  deleted_at     TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY hall_providers_uuid (uuid),
  UNIQUE KEY hall_providers_user (user_id),
  KEY hall_providers_status (status),
  CONSTRAINT hall_providers_user FOREIGN KEY (user_id) REFERENCES users (id),
  CONSTRAINT hall_providers_city FOREIGN KEY (city_id) REFERENCES cities (id),
  CONSTRAINT hall_providers_created_by FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE hall_provider_translations (
  id                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  hall_provider_id  BIGINT UNSIGNED NOT NULL,
  locale            VARCHAR(10) NOT NULL,
  name              VARCHAR(255) NOT NULL,
  description       TEXT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY hall_provider_translations_uuid (uuid),
  UNIQUE KEY hall_provider_translations_unique (hall_provider_id, locale),
  CONSTRAINT hall_provider_translations_provider FOREIGN KEY (hall_provider_id) REFERENCES hall_providers (id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- profile_change_requests: sensitive profile edits after approval wait for the admin
-- (DEC-13 trainer: training licence, IBAN, CV; DEC-20 center: licence, IBAN).
-- changes = {"iban": {"old": "...", "new": "..."}, "cv": {"media_id": 55}}; new files are
-- uploaded to media collection "pending_changes" and moved on approval.
CREATE TABLE profile_change_requests (
  id                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  user_id           BIGINT UNSIGNED NOT NULL COMMENT 'trainer or center account',
  changes           JSON NOT NULL,
  status            VARCHAR(16) NOT NULL DEFAULT 'pending' COMMENT 'pending, approved, rejected',
  rejection_reason  TEXT NULL,
  reviewed_by       BIGINT UNSIGNED NULL,
  reviewed_at       TIMESTAMP NULL,
  created_at        TIMESTAMP NULL,
  updated_at        TIMESTAMP NULL,
  open_user_id      BIGINT UNSIGNED GENERATED ALWAYS AS (IF(status = 'pending', user_id, NULL)) STORED,
  PRIMARY KEY (id),
  UNIQUE KEY profile_change_requests_uuid (uuid),
  UNIQUE KEY profile_change_requests_one_open (open_user_id),
  KEY profile_change_requests_status (status, created_at),
  CONSTRAINT profile_change_requests_user FOREIGN KEY (user_id) REFERENCES users (id),
  CONSTRAINT profile_change_requests_reviewed_by FOREIGN KEY (reviewed_by) REFERENCES users (id)
) ENGINE=InnoDB;

-- =====================================================================================
-- 3. TRAINER BUSINESS MODEL (independent trainers only — TRR-005..007, TRR-030, ADM-036/037)
-- =====================================================================================

-- trainer_plans: subscription packages the admin offers (ADM-036)
CREATE TABLE trainer_plans (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  billing_cycle  VARCHAR(10) NOT NULL COMMENT 'monthly, annual',
  price          DECIMAL(12,2) NOT NULL,
  duration_days  SMALLINT UNSIGNED NOT NULL,
  course_limit   SMALLINT UNSIGNED NULL COMMENT 'NULL = unlimited',
  is_active      BOOLEAN NOT NULL DEFAULT 1,
  sort_order     INT NOT NULL DEFAULT 0,
  created_at     TIMESTAMP NULL,
  updated_at     TIMESTAMP NULL,
  deleted_at     TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY trainer_plans_uuid (uuid),
  CONSTRAINT trainer_plans_price CHECK (price >= 0)
) ENGINE=InnoDB;

CREATE TABLE trainer_plan_translations (
  id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  trainer_plan_id  BIGINT UNSIGNED NOT NULL,
  locale           VARCHAR(10) NOT NULL,
  name             VARCHAR(255) NOT NULL,
  description      TEXT NULL,
  features         JSON NULL COMMENT 'bullet list shown on the plan card',
  PRIMARY KEY (id),
  UNIQUE KEY trainer_plan_translations_uuid (uuid),
  UNIQUE KEY trainer_plan_translations_unique (trainer_plan_id, locale),
  CONSTRAINT trainer_plan_translations_plan FOREIGN KEY (trainer_plan_id) REFERENCES trainer_plans (id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- trainer_subscriptions: one row per business-model period of an independent trainer.
-- model = subscription → plan, price, course_limit snapshotted; paid through payments.
-- model = commission  → commission_rate snapshotted at activation (BR-FIN-03).
-- One active row per trainer (BR-FIN-06); renewals are new rows (TRR-030, no carry-over).
CREATE TABLE trainer_subscriptions (
  id                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  trainer_id         BIGINT UNSIGNED NOT NULL,
  model              VARCHAR(16) NOT NULL COMMENT 'subscription, commission',
  trainer_plan_id    BIGINT UNSIGNED NULL,
  plan_snapshot      JSON NULL,
  price              DECIMAL(12,2) NULL,
  course_limit       SMALLINT UNSIGNED NULL,
  commission_rate    DECIMAL(5,2) NULL,
  status             VARCHAR(20) NOT NULL COMMENT 'pending_payment, active, expired, payment_failed, replaced',
  starts_at          TIMESTAMP NULL,
  ends_at            TIMESTAMP NULL COMMENT 'NULL for the commission model',
  created_at         TIMESTAMP NULL,
  updated_at         TIMESTAMP NULL,
  active_trainer_id  BIGINT UNSIGNED GENERATED ALWAYS AS (IF(status = 'active', trainer_id, NULL)) STORED,
  PRIMARY KEY (id),
  UNIQUE KEY trainer_subscriptions_uuid (uuid),
  UNIQUE KEY trainer_subscriptions_one_active (active_trainer_id),
  KEY trainer_subscriptions_trainer (trainer_id, status),
  KEY trainer_subscriptions_ends (status, ends_at),
  CONSTRAINT trainer_subscriptions_trainer FOREIGN KEY (trainer_id) REFERENCES trainers (id),
  CONSTRAINT trainer_subscriptions_plan FOREIGN KEY (trainer_plan_id) REFERENCES trainer_plans (id),
  CONSTRAINT trainer_subscriptions_model_fields CHECK (
    (model = 'subscription' AND trainer_plan_id IS NOT NULL AND price IS NOT NULL)
    OR (model = 'commission' AND commission_rate IS NOT NULL))
) ENGINE=InnoDB;

-- =====================================================================================
-- 4. COURSES
-- The courses row IS the live, approved course. While a course is draft / rejected the owner
-- edits it directly; once published, substantive edits wait in course_requests (type = edit)
-- and the row is only changed when the admin approves (D2, TD-10).
-- =====================================================================================

-- courses: TRR-009..015, TRR-020..023, TRR-027, CTR-009, ADM-025..032; D1–D5, DEC-08, DEC-17,
-- DEC-18, DEC-34. Media: images (first = cover), venue_photos (in-person, DEC-17).
CREATE TABLE courses (
  id                       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  code                     VARCHAR(20) NOT NULL,
  -- ownership (D3): an independent trainer's course, or a center's course with an optional
  -- responsible affiliated trainer. The center is the financial owner of its courses (BR-FIN-05).
  training_center_id       BIGINT UNSIGNED NULL,
  trainer_id               BIGINT UNSIGNED NULL,
  created_by               BIGINT UNSIGNED NOT NULL,
  trainer_subscription_id  BIGINT UNSIGNED NULL COMMENT 'the subscription period this course counts against (course_limit)',
  category_id              BIGINT UNSIGNED NOT NULL,
  city_id                  BIGINT UNSIGNED NOT NULL,
  type                     VARCHAR(16) NOT NULL COMMENT 'in_person, external',
  status                   VARCHAR(20) NOT NULL DEFAULT 'draft'
                           COMMENT 'draft, under_review, rejected, published, awaiting_date, completed, cancelled, suspended',
  -- pricing (DEC-34: price editable only until the first confirmed enrollment)
  is_free                  BOOLEAN NOT NULL DEFAULT 0,
  price                    DECIMAL(12,2) NOT NULL DEFAULT 0,
  proposed_price           DECIMAL(12,2) NULL COMMENT 'affiliated trainer proposal, the center decides (D3)',
  -- the offer = the course discount (DEC-18, DEC-08); set by the trainer, or by the admin at review
  discount_percentage      DECIMAL(5,2) NULL,
  discount_starts_on       DATE NULL,
  discount_ends_on         DATE NULL,
  -- capacity
  seats                    INT UNSIGNED NOT NULL,
  confirmed_count          INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'confirmed + completed enrollments; updated under the course row lock (DEC-29)',
  sessions_count           SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  -- schedule
  start_mode               VARCHAR(16) NOT NULL DEFAULT 'fixed_date' COMMENT 'fixed_date, threshold',
  starts_on                DATE NULL,
  ends_on                  DATE NULL,
  start_time               TIME NULL,
  end_time                 TIME NULL,
  weekdays                 JSON NULL COMMENT 'e.g. ["mon","tue","wed"]',
  ends_at                  DATETIME NULL COMMENT 'derived end moment used for auto-completion (TRR-022)',
  -- threshold start (TRR-015, ADM-032, D5): both deadlines can be extended by the admin;
  -- every extension is an audit_logs row
  min_enrollments          INT UNSIGNED NULL,
  expected_start_on        DATE NULL,
  expected_duration        VARCHAR(50) NULL COMMENT 'free text shown to trainees, e.g. "3 weeks"',
  minimum_deadline_at      DATETIME NULL COMMENT 'deadline to reach the minimum',
  minimum_reached_at       DATETIME NULL,
  date_deadline_at         DATETIME NULL COMMENT 'deadline for the owner to set the date once the minimum is reached',
  date_set_at              DATETIME NULL,
  -- in-person venue, typed by the owner and reviewed with the course (D1, DEC-17)
  venue_name               VARCHAR(255) NULL,
  venue_address            TEXT NULL,
  venue_map_url            VARCHAR(1000) NULL,
  venue_notes              TEXT NULL,
  -- external course access (TRR-011, TRR-027, BR-CRS-05): never exposed publicly
  external_link            VARCHAR(1000) NULL,
  access_instructions      TEXT NULL,
  session_link             VARCHAR(1000) NULL COMMENT 'current session link, operational edit without review',
  -- review (BR-CRS-01, ADM-026/027, D4)
  points                   INT UNSIGNED NULL COMMENT 'set by the admin at review, earned on completion (D4)',
  submitted_at             TIMESTAMP NULL,
  reviewed_by              BIGINT UNSIGNED NULL,
  reviewed_at              TIMESTAMP NULL,
  rejection_reason         TEXT NULL,
  published_at             TIMESTAMP NULL,
  completed_at             TIMESTAMP NULL,
  completed_by             BIGINT UNSIGNED NULL COMMENT 'NULL = completed automatically',
  cancelled_at             TIMESTAMP NULL,
  suspended_at             TIMESTAMP NULL,
  suspension_reason        TEXT NULL,
  created_at               TIMESTAMP NULL,
  updated_at               TIMESTAMP NULL,
  deleted_at               TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY courses_uuid (uuid),
  UNIQUE KEY courses_code (code),
  KEY courses_browse (status, category_id, city_id),
  KEY courses_trainer (trainer_id, status),
  KEY courses_center (training_center_id, status),
  KEY courses_offers (status, discount_starts_on, discount_ends_on),
  KEY courses_auto_complete (status, ends_at),
  KEY courses_threshold (status, minimum_deadline_at, date_deadline_at),
  CONSTRAINT courses_center FOREIGN KEY (training_center_id) REFERENCES training_centers (id),
  CONSTRAINT courses_trainer FOREIGN KEY (trainer_id) REFERENCES trainers (id),
  CONSTRAINT courses_created_by FOREIGN KEY (created_by) REFERENCES users (id),
  CONSTRAINT courses_subscription FOREIGN KEY (trainer_subscription_id) REFERENCES trainer_subscriptions (id),
  CONSTRAINT courses_category FOREIGN KEY (category_id) REFERENCES categories (id),
  CONSTRAINT courses_city FOREIGN KEY (city_id) REFERENCES cities (id),
  CONSTRAINT courses_reviewed_by FOREIGN KEY (reviewed_by) REFERENCES users (id),
  CONSTRAINT courses_completed_by FOREIGN KEY (completed_by) REFERENCES users (id),
  CONSTRAINT courses_owner CHECK (trainer_id IS NOT NULL OR training_center_id IS NOT NULL),
  CONSTRAINT courses_seats CHECK (confirmed_count <= seats),
  CONSTRAINT courses_minimum CHECK (min_enrollments IS NULL OR min_enrollments <= seats),
  CONSTRAINT courses_price CHECK (price >= 0 AND (is_free = 0 OR price = 0)),
  CONSTRAINT courses_discount CHECK (discount_percentage IS NULL OR (discount_percentage > 0 AND discount_percentage < 100))
) ENGINE=InnoDB;

-- course_translations: name, description and "What you will learn" (TRR-014) per language.
-- learning_points = ["Speak with confidence", "Structure a talk", …] (replaces 2 tables).
CREATE TABLE course_translations (
  id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  course_id        BIGINT UNSIGNED NOT NULL,
  locale           VARCHAR(10) NOT NULL,
  name             VARCHAR(255) NOT NULL,
  description      TEXT NOT NULL,
  learning_points  JSON NULL,
  PRIMARY KEY (id),
  UNIQUE KEY course_translations_uuid (uuid),
  UNIQUE KEY course_translations_unique (course_id, locale),
  FULLTEXT KEY course_translations_search (name, description),
  CONSTRAINT course_translations_course FOREIGN KEY (course_id) REFERENCES courses (id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- course_sessions: the session list of the create-course "Sessions" step (screens, SCR-05).
-- Information only — no attendance or per-session management (A-p.20). Dates may be empty
-- for a threshold course until the date is set.
CREATE TABLE course_sessions (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  course_id   BIGINT UNSIGNED NOT NULL,
  sort_order  SMALLINT UNSIGNED NOT NULL,
  title       VARCHAR(255) NOT NULL,
  starts_at   DATETIME NULL,
  ends_at     DATETIME NULL,
  created_at  TIMESTAMP NULL,
  updated_at  TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY course_sessions_uuid (uuid),
  UNIQUE KEY course_sessions_order (course_id, sort_order),
  KEY course_sessions_starts (course_id, starts_at),
  CONSTRAINT course_sessions_course FOREIGN KEY (course_id) REFERENCES courses (id),
  CONSTRAINT course_sessions_times CHECK (ends_at IS NULL OR starts_at IS NULL OR ends_at > starts_at)
) ENGINE=InnoDB;

-- course_requests: requests on a published course that the admin decides.
--   type = edit          → substantive edit (D2, TRR-021, ADM-028). payload = the proposed
--                          course fields, translations and sessions; applied on approval.
--   type = cancellation  → course cancellation (D5, TRR-023, ADM-031, C-§01–07, §24). Requested
--                          by an independent trainer or a center, with a reason.
-- One open request per course and type. Requester, date, reason and decision are shown in the
-- admin course details (C-§24).
CREATE TABLE course_requests (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  course_id       BIGINT UNSIGNED NOT NULL,
  type            VARCHAR(16) NOT NULL COMMENT 'edit, cancellation',
  status          VARCHAR(16) NOT NULL DEFAULT 'under_review' COMMENT 'under_review, approved, rejected',
  requested_by    BIGINT UNSIGNED NOT NULL,
  reason          TEXT NULL COMMENT 'required for cancellation',
  payload         JSON NULL COMMENT 'required for edit',
  decided_by      BIGINT UNSIGNED NULL,
  decided_at      TIMESTAMP NULL,
  decision_note   TEXT NULL COMMENT 'rejection reason / admin note',
  created_at      TIMESTAMP NULL,
  updated_at      TIMESTAMP NULL,
  open_course_id  BIGINT UNSIGNED GENERATED ALWAYS AS (IF(status = 'under_review', course_id, NULL)) STORED,
  PRIMARY KEY (id),
  UNIQUE KEY course_requests_uuid (uuid),
  UNIQUE KEY course_requests_one_open (open_course_id, type),
  KEY course_requests_queue (type, status, created_at),
  KEY course_requests_course (course_id, created_at),
  CONSTRAINT course_requests_course FOREIGN KEY (course_id) REFERENCES courses (id),
  CONSTRAINT course_requests_requested_by FOREIGN KEY (requested_by) REFERENCES users (id),
  CONSTRAINT course_requests_decided_by FOREIGN KEY (decided_by) REFERENCES users (id),
  CONSTRAINT course_requests_fields CHECK ((type = 'edit' AND payload IS NOT NULL) OR (type = 'cancellation' AND reason IS NOT NULL))
) ENGINE=InnoDB;

-- =====================================================================================
-- 5. COURSE PACKAGES (TRR-016, CTR-012, TRN-017, ADM-013/014; DEC-09, DEC-21, DEC-31, D9)
-- =====================================================================================

CREATE TABLE course_packages (
  id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  code                VARCHAR(20) NOT NULL,
  trainer_id          BIGINT UNSIGNED NULL COMMENT 'owner: independent trainer',
  training_center_id  BIGINT UNSIGNED NULL COMMENT 'owner: center',
  created_by          BIGINT UNSIGNED NOT NULL,
  price               DECIMAL(12,2) NOT NULL COMMENT 'must be < sum of its course prices (BR-PKG-01)',
  starts_on           DATE NOT NULL COMMENT 'on sale from (DEC-21)',
  ends_on             DATE NOT NULL COMMENT 'on sale until (DEC-21)',
  status              VARCHAR(16) NOT NULL DEFAULT 'active' COMMENT 'active, inactive',
  deactivated_by      BIGINT UNSIGNED NULL COMMENT 'owner or admin (DEC-09, C-§16)',
  deactivated_at      TIMESTAMP NULL,
  deactivation_reason TEXT NULL,
  created_at          TIMESTAMP NULL,
  updated_at          TIMESTAMP NULL,
  deleted_at          TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY course_packages_uuid (uuid),
  UNIQUE KEY course_packages_code (code),
  KEY course_packages_sale (status, starts_on, ends_on),
  KEY course_packages_trainer (trainer_id),
  KEY course_packages_center (training_center_id),
  CONSTRAINT course_packages_trainer FOREIGN KEY (trainer_id) REFERENCES trainers (id),
  CONSTRAINT course_packages_center FOREIGN KEY (training_center_id) REFERENCES training_centers (id),
  CONSTRAINT course_packages_created_by FOREIGN KEY (created_by) REFERENCES users (id),
  CONSTRAINT course_packages_deactivated_by FOREIGN KEY (deactivated_by) REFERENCES users (id),
  CONSTRAINT course_packages_owner CHECK ((trainer_id IS NOT NULL) <> (training_center_id IS NOT NULL)),
  CONSTRAINT course_packages_dates CHECK (ends_on >= starts_on)
) ENGINE=InnoDB;

CREATE TABLE course_package_translations (
  id                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  course_package_id  BIGINT UNSIGNED NOT NULL,
  locale             VARCHAR(10) NOT NULL,
  name               VARCHAR(255) NOT NULL,
  description        TEXT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY course_package_translations_uuid (uuid),
  UNIQUE KEY course_package_translations_unique (course_package_id, locale),
  CONSTRAINT course_package_translations_package FOREIGN KEY (course_package_id) REFERENCES course_packages (id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- course_package_items: courses in a package (≥ 2, same owner — BR-PKG-01). A bought package
-- is never edited (BR-PKG-02); the purchase keeps its own snapshot.
CREATE TABLE course_package_items (
  course_package_id  BIGINT UNSIGNED NOT NULL,
  course_id          BIGINT UNSIGNED NOT NULL,
  sort_order         SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (course_package_id, course_id),
  KEY course_package_items_course (course_id),
  CONSTRAINT course_package_items_package FOREIGN KEY (course_package_id) REFERENCES course_packages (id),
  CONSTRAINT course_package_items_course FOREIGN KEY (course_id) REFERENCES courses (id)
) ENGINE=InnoDB;

-- =====================================================================================
-- 6. PURCHASES, ENROLLMENTS AND PAYMENTS
-- One checkout = one purchase (a single course, or a package = all its courses, DEC-31).
-- Every price, discount and reward used is snapshotted (BR-ENR-05). No seat hold (DEC-29):
-- enrollments are created awaiting_payment and confirmed only when the payment succeeds,
-- re-checking capacity under the course row lock.
-- =====================================================================================

-- purchases: TRN-010, TRN-011, TRN-017, ADM-033/035; DEC-07 (each component stored apart)
CREATE TABLE purchases (
  id                      BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  code                    VARCHAR(20) NOT NULL,
  user_id                 BIGINT UNSIGNED NOT NULL COMMENT 'trainee',
  course_package_id       BIGINT UNSIGNED NULL COMMENT 'set for a package purchase',
  status                  VARCHAR(20) NOT NULL DEFAULT 'awaiting_payment' COMMENT 'awaiting_payment, paid, failed, expired',
  original_amount         DECIMAL(12,2) NOT NULL COMMENT 'sum of course prices before any reduction',
  discount_amount         DECIMAL(12,2) NOT NULL DEFAULT 0 COMMENT 'course discount (offer) or package saving',
  cashback_used_amount    DECIMAL(12,2) NOT NULL DEFAULT 0,
  points_used             INT UNSIGNED NOT NULL DEFAULT 0,
  points_discount_amount  DECIMAL(12,2) NOT NULL DEFAULT 0,
  total_amount            DECIMAL(12,2) NOT NULL COMMENT 'amount actually paid (BR-FIN-02 base)',
  snapshot                JSON NOT NULL COMMENT 'package / course data, rates and rules at purchase',
  paid_at                 TIMESTAMP NULL,
  created_at              TIMESTAMP NULL,
  updated_at              TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY purchases_uuid (uuid),
  UNIQUE KEY purchases_code (code),
  KEY purchases_user (user_id, created_at),
  KEY purchases_status (status, created_at),
  CONSTRAINT purchases_user FOREIGN KEY (user_id) REFERENCES users (id),
  CONSTRAINT purchases_package FOREIGN KEY (course_package_id) REFERENCES course_packages (id),
  CONSTRAINT purchases_total CHECK (total_amount >= 0
    AND total_amount = original_amount - discount_amount - cashback_used_amount - points_discount_amount)
) ENGINE=InnoDB;

-- enrollments: one trainee in one course (TRN-012..014, TRR-024, ADM-033/034). The amounts are
-- this course's share of the purchase, so commission and refunds work per course.
-- Cancelled = the course was cancelled (D5) or the admin removed the trainee (DEC-12); the
-- cash refund itself happens outside the system (D6) — only its status is tracked here,
-- never marked completed before the transfer is confirmed, one refund per enrollment (C-§11).
CREATE TABLE enrollments (
  id                      BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  code                    VARCHAR(20) NOT NULL,
  purchase_id             BIGINT UNSIGNED NOT NULL,
  user_id                 BIGINT UNSIGNED NOT NULL COMMENT 'trainee',
  course_id               BIGINT UNSIGNED NOT NULL,
  status                  VARCHAR(20) NOT NULL DEFAULT 'awaiting_payment'
                          COMMENT 'awaiting_payment, confirmed, completed, cancelled, payment_failed',
  course_price            DECIMAL(12,2) NOT NULL,
  discount_percentage     DECIMAL(5,2) NULL,
  discount_amount         DECIMAL(12,2) NOT NULL DEFAULT 0,
  cashback_used_amount    DECIMAL(12,2) NOT NULL DEFAULT 0,
  points_used             INT UNSIGNED NOT NULL DEFAULT 0,
  points_discount_amount  DECIMAL(12,2) NOT NULL DEFAULT 0,
  paid_amount             DECIMAL(12,2) NOT NULL COMMENT 'this course''s share of the amount actually paid',
  points_to_earn          INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'course points snapshot, granted on completion (D4)',
  snapshot                JSON NOT NULL COMMENT 'course name, owner, schedule, venue / link at purchase',
  confirmed_at            TIMESTAMP NULL,
  completed_at            TIMESTAMP NULL,
  cancelled_at            TIMESTAMP NULL,
  cancelled_by            BIGINT UNSIGNED NULL COMMENT 'admin; NULL when cancelled with the course',
  cancellation_reason     TEXT NULL,
  refund_status           VARCHAR(16) NOT NULL DEFAULT 'none' COMMENT 'none, pending, completed',
  refund_amount           DECIMAL(12,2) NULL,
  refund_reference        VARCHAR(255) NULL,
  refund_confirmed_by     BIGINT UNSIGNED NULL,
  refund_confirmed_at     TIMESTAMP NULL,
  created_at              TIMESTAMP NULL,
  updated_at              TIMESTAMP NULL,
  active_course_id        BIGINT UNSIGNED GENERATED ALWAYS AS (IF(status IN ('confirmed', 'completed'), course_id, NULL)) STORED,
  PRIMARY KEY (id),
  UNIQUE KEY enrollments_uuid (uuid),
  UNIQUE KEY enrollments_code (code),
  UNIQUE KEY enrollments_one_active (user_id, active_course_id) COMMENT 'BR-ENR-01',
  KEY enrollments_course (course_id, status),
  KEY enrollments_user (user_id, status),
  KEY enrollments_purchase (purchase_id),
  KEY enrollments_refunds (refund_status),
  CONSTRAINT enrollments_purchase FOREIGN KEY (purchase_id) REFERENCES purchases (id),
  CONSTRAINT enrollments_user FOREIGN KEY (user_id) REFERENCES users (id),
  CONSTRAINT enrollments_course FOREIGN KEY (course_id) REFERENCES courses (id),
  CONSTRAINT enrollments_cancelled_by FOREIGN KEY (cancelled_by) REFERENCES users (id),
  CONSTRAINT enrollments_refund_confirmed_by FOREIGN KEY (refund_confirmed_by) REFERENCES users (id),
  CONSTRAINT enrollments_refund CHECK (
    (refund_status = 'none' AND refund_amount IS NULL)
    OR (refund_status IN ('pending', 'completed') AND refund_amount > 0 AND refund_amount <= paid_amount)),
  CONSTRAINT enrollments_refund_confirmed CHECK (refund_status <> 'completed' OR refund_confirmed_at IS NOT NULL)
) ENGINE=InnoDB;

-- payments: gateway payments for a purchase or a trainer subscription (TRN-011, TRR-006,
-- PLT-002, ADM-035, BR-ENR-03). Vendor and methods: DEC-06. A paid payment is never edited.
CREATE TABLE payments (
  id                       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  code                     VARCHAR(20) NOT NULL,
  user_id                  BIGINT UNSIGNED NOT NULL,
  purchase_id              BIGINT UNSIGNED NULL,
  trainer_subscription_id  BIGINT UNSIGNED NULL,
  amount                   DECIMAL(12,2) NOT NULL,
  currency                 CHAR(3) NOT NULL DEFAULT 'SAR',
  status                   VARCHAR(16) NOT NULL DEFAULT 'pending' COMMENT 'pending, paid, failed',
  gateway                  VARCHAR(30) NULL,
  method                   VARCHAR(30) NULL COMMENT 'mada, visa, apple_pay, … (DEC-06)',
  gateway_reference        VARCHAR(255) NULL,
  idempotency_key          VARCHAR(100) NOT NULL,
  failure_reason           TEXT NULL,
  gateway_payload          JSON NULL,
  paid_at                  TIMESTAMP NULL,
  created_at               TIMESTAMP NULL,
  updated_at               TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY payments_uuid (uuid),
  UNIQUE KEY payments_code (code),
  UNIQUE KEY payments_idempotency (idempotency_key),
  UNIQUE KEY payments_gateway_reference (gateway, gateway_reference),
  KEY payments_status (status, created_at),
  KEY payments_purchase (purchase_id),
  KEY payments_subscription (trainer_subscription_id),
  CONSTRAINT payments_user FOREIGN KEY (user_id) REFERENCES users (id),
  CONSTRAINT payments_purchase FOREIGN KEY (purchase_id) REFERENCES purchases (id),
  CONSTRAINT payments_subscription FOREIGN KEY (trainer_subscription_id) REFERENCES trainer_subscriptions (id),
  CONSTRAINT payments_target CHECK ((purchase_id IS NOT NULL) <> (trainer_subscription_id IS NOT NULL))
) ENGINE=InnoDB;

-- enrollment_tickets: attendance ticket for confirmed in-person enrollments (TRN-024, D7).
-- Shows the latest approved course data; a cancelled course keeps the ticket, status void.
CREATE TABLE enrollment_tickets (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  enrollment_id  BIGINT UNSIGNED NOT NULL,
  code           VARCHAR(20) NOT NULL COMMENT 'Ticket ID',
  status         VARCHAR(10) NOT NULL DEFAULT 'valid' COMMENT 'valid, void',
  voided_at      TIMESTAMP NULL,
  created_at     TIMESTAMP NULL,
  updated_at     TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY enrollment_tickets_uuid (uuid),
  UNIQUE KEY enrollment_tickets_enrollment (enrollment_id),
  UNIQUE KEY enrollment_tickets_code (code),
  CONSTRAINT enrollment_tickets_enrollment FOREIGN KEY (enrollment_id) REFERENCES enrollments (id)
) ENGINE=InnoDB;

-- completion_certificates: issued by Morvia when the course completes (TRN-025, D7)
CREATE TABLE completion_certificates (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  enrollment_id  BIGINT UNSIGNED NOT NULL,
  code           VARCHAR(20) NOT NULL,
  issued_at      TIMESTAMP NOT NULL,
  snapshot       JSON NOT NULL COMMENT 'trainee name, course name, owner, completion date',
  created_at     TIMESTAMP NULL,
  updated_at     TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY completion_certificates_uuid (uuid),
  UNIQUE KEY completion_certificates_enrollment (enrollment_id),
  UNIQUE KEY completion_certificates_code (code),
  CONSTRAINT completion_certificates_enrollment FOREIGN KEY (enrollment_id) REFERENCES enrollments (id)
) ENGINE=InnoDB;

-- trainer_ratings: the trainee rates the trainer (stars + comment) from a completed course in
-- My Courses (screens, SCR-04); shown as reviews on the trainer profile. One per enrollment.
-- A center course without a responsible trainer rates the center.
CREATE TABLE trainer_ratings (
  id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  enrollment_id       BIGINT UNSIGNED NOT NULL,
  user_id             BIGINT UNSIGNED NOT NULL COMMENT 'trainee',
  course_id           BIGINT UNSIGNED NOT NULL,
  trainer_id          BIGINT UNSIGNED NULL,
  training_center_id  BIGINT UNSIGNED NULL,
  rating              TINYINT UNSIGNED NOT NULL,
  comment             TEXT NULL,
  created_at          TIMESTAMP NULL,
  updated_at          TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY trainer_ratings_uuid (uuid),
  UNIQUE KEY trainer_ratings_enrollment (enrollment_id),
  KEY trainer_ratings_trainer (trainer_id, created_at),
  KEY trainer_ratings_center (training_center_id, created_at),
  CONSTRAINT trainer_ratings_enrollment FOREIGN KEY (enrollment_id) REFERENCES enrollments (id),
  CONSTRAINT trainer_ratings_user FOREIGN KEY (user_id) REFERENCES users (id),
  CONSTRAINT trainer_ratings_course FOREIGN KEY (course_id) REFERENCES courses (id),
  CONSTRAINT trainer_ratings_trainer FOREIGN KEY (trainer_id) REFERENCES trainers (id),
  CONSTRAINT trainer_ratings_center FOREIGN KEY (training_center_id) REFERENCES training_centers (id),
  CONSTRAINT trainer_ratings_value CHECK (rating BETWEEN 1 AND 5),
  CONSTRAINT trainer_ratings_target CHECK (trainer_id IS NOT NULL OR training_center_id IS NOT NULL)
) ENGINE=InnoDB;

-- =====================================================================================
-- 7. EARNINGS AND LOYALTY
-- Each table below is the ledger of its own money or points (TD-13): rows are appended and
-- never edited except their status; balances are sums over them.
-- =====================================================================================

-- course_earnings: what each confirmed enrollment owes the course owner (TRR-025/026,
-- CTR-010, ADM-038; BR-FIN-01..05, D7, DEC-15). Independent trainer → trainer_id;
-- center course → training_center_id (BR-FIN-05). Rate snapshotted per row (BR-FIN-03);
-- base = amount actually paid (BR-FIN-02). Paid out outside the system and recorded (BR-FIN-04).
CREATE TABLE course_earnings (
  id                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  enrollment_id         BIGINT UNSIGNED NOT NULL,
  course_id             BIGINT UNSIGNED NOT NULL,
  trainer_id            BIGINT UNSIGNED NULL,
  training_center_id    BIGINT UNSIGNED NULL,
  paid_amount           DECIMAL(12,2) NOT NULL,
  commission_rate       DECIMAL(5,2) NOT NULL COMMENT '0 for a subscription-model trainer',
  platform_amount       DECIMAL(12,2) NOT NULL,
  net_amount            DECIMAL(12,2) NOT NULL,
  status                VARCHAR(16) NOT NULL DEFAULT 'pending' COMMENT 'pending, available, settled, cancelled',
  available_at          TIMESTAMP NULL,
  settled_at            TIMESTAMP NULL,
  settled_by            BIGINT UNSIGNED NULL,
  settlement_reference  VARCHAR(255) NULL,
  cancelled_at          TIMESTAMP NULL,
  created_at            TIMESTAMP NULL,
  updated_at            TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY course_earnings_uuid (uuid),
  UNIQUE KEY course_earnings_enrollment (enrollment_id),
  KEY course_earnings_trainer (trainer_id, status),
  KEY course_earnings_center (training_center_id, status),
  KEY course_earnings_course (course_id, status),
  CONSTRAINT course_earnings_enrollment FOREIGN KEY (enrollment_id) REFERENCES enrollments (id),
  CONSTRAINT course_earnings_course FOREIGN KEY (course_id) REFERENCES courses (id),
  CONSTRAINT course_earnings_trainer FOREIGN KEY (trainer_id) REFERENCES trainers (id),
  CONSTRAINT course_earnings_center FOREIGN KEY (training_center_id) REFERENCES training_centers (id),
  CONSTRAINT course_earnings_settled_by FOREIGN KEY (settled_by) REFERENCES users (id),
  CONSTRAINT course_earnings_owner CHECK ((trainer_id IS NOT NULL) <> (training_center_id IS NOT NULL)),
  CONSTRAINT course_earnings_split CHECK (platform_amount + net_amount = paid_amount)
) ENGINE=InnoDB;

-- cashback_rules: purchase threshold → cashback value (ADM-039; several tiers: DEC-24)
CREATE TABLE cashback_rules (
  id                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  threshold_amount  DECIMAL(12,2) NOT NULL,
  cashback_amount   DECIMAL(12,2) NOT NULL,
  is_active         BOOLEAN NOT NULL DEFAULT 1,
  created_by        BIGINT UNSIGNED NULL,
  created_at        TIMESTAMP NULL,
  updated_at        TIMESTAMP NULL,
  deleted_at        TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY cashback_rules_uuid (uuid),
  CONSTRAINT cashback_rules_created_by FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB;

-- cashback_transactions: the trainee's cashback ledger (TRN-019, ADM-040; BR-LOY-01/02/05, D7).
--   earned     (+) status pending → available (trigger: DEC-25) or cancelled (course cancelled)
--   used       (−) at a successful purchase      returned (+) used cashback given back (D7)
--   adjustment (±) manual by the admin, reason required
-- Available balance = SUM(amount) WHERE status = 'available' OR type IN ('used','returned','adjustment').
CREATE TABLE cashback_transactions (
  id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  user_id          BIGINT UNSIGNED NOT NULL COMMENT 'trainee',
  type             VARCHAR(16) NOT NULL COMMENT 'earned, used, returned, adjustment',
  status           VARCHAR(16) NOT NULL DEFAULT 'posted' COMMENT 'earned: pending, available, cancelled · others: posted',
  amount           DECIMAL(12,2) NOT NULL COMMENT 'signed',
  purchase_id      BIGINT UNSIGNED NULL,
  enrollment_id    BIGINT UNSIGNED NULL,
  cashback_rule_id BIGINT UNSIGNED NULL,
  rule_snapshot    JSON NULL,
  reason           TEXT NULL,
  actor_id         BIGINT UNSIGNED NULL,
  idempotency_key  VARCHAR(100) NOT NULL,
  available_at     TIMESTAMP NULL,
  created_at       TIMESTAMP NULL,
  updated_at       TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY cashback_transactions_uuid (uuid),
  UNIQUE KEY cashback_transactions_idempotency (idempotency_key),
  KEY cashback_transactions_user (user_id, status, type),
  CONSTRAINT cashback_transactions_user FOREIGN KEY (user_id) REFERENCES users (id),
  CONSTRAINT cashback_transactions_purchase FOREIGN KEY (purchase_id) REFERENCES purchases (id),
  CONSTRAINT cashback_transactions_enrollment FOREIGN KEY (enrollment_id) REFERENCES enrollments (id),
  CONSTRAINT cashback_transactions_rule FOREIGN KEY (cashback_rule_id) REFERENCES cashback_rules (id),
  CONSTRAINT cashback_transactions_actor FOREIGN KEY (actor_id) REFERENCES users (id),
  CONSTRAINT cashback_transactions_adjustment CHECK (type <> 'adjustment' OR reason IS NOT NULL)
) ENGINE=InnoDB;

-- loyalty_levels: levels by total points earned (TRN-019, ADM-041; BR-LOY-04, DEC-26)
CREATE TABLE loyalty_levels (
  id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  minimum_points   INT UNSIGNED NOT NULL,
  sort_order       SMALLINT UNSIGNED NOT NULL,
  can_use_points   BOOLEAN NOT NULL DEFAULT 0,
  discount_rate    DECIMAL(5,2) NULL,
  is_active        BOOLEAN NOT NULL DEFAULT 1,
  created_at       TIMESTAMP NULL,
  updated_at       TIMESTAMP NULL,
  deleted_at       TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY loyalty_levels_uuid (uuid),
  UNIQUE KEY loyalty_levels_order (sort_order)
) ENGINE=InnoDB;

CREATE TABLE loyalty_level_translations (
  id                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  loyalty_level_id  BIGINT UNSIGNED NOT NULL,
  locale            VARCHAR(10) NOT NULL,
  name              VARCHAR(255) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY loyalty_level_translations_uuid (uuid),
  UNIQUE KEY loyalty_level_translations_unique (loyalty_level_id, locale),
  CONSTRAINT loyalty_level_translations_level FOREIGN KEY (loyalty_level_id) REFERENCES loyalty_levels (id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- points_transactions: the trainee's points ledger (TRN-019, ADM-041; D4, D7, BR-LOY-03..05).
--   earned (+) on course completion · used (−) as a discount at purchase
--   returned (+) used points given back when the course is cancelled (D7)
--   reversed (−) earned points taken back by the admin when removing a trainee (DEC-12)
--   adjustment (±) manual, reason required
-- Level = SUM(points) of type earned (using points never lowers it, BR-LOY-04).
-- The points → SAR conversion rate is a setting (app_configs), snapshotted on use.
CREATE TABLE points_transactions (
  id                   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  user_id              BIGINT UNSIGNED NOT NULL COMMENT 'trainee',
  type                 VARCHAR(16) NOT NULL COMMENT 'earned, used, returned, reversed, adjustment',
  points               INT NOT NULL COMMENT 'signed',
  purchase_id          BIGINT UNSIGNED NULL,
  enrollment_id        BIGINT UNSIGNED NULL,
  conversion_snapshot  JSON NULL,
  reason               TEXT NULL,
  actor_id             BIGINT UNSIGNED NULL,
  idempotency_key      VARCHAR(100) NOT NULL,
  created_at           TIMESTAMP NULL,
  updated_at           TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY points_transactions_uuid (uuid),
  UNIQUE KEY points_transactions_idempotency (idempotency_key),
  KEY points_transactions_user (user_id, type),
  CONSTRAINT points_transactions_user FOREIGN KEY (user_id) REFERENCES users (id),
  CONSTRAINT points_transactions_purchase FOREIGN KEY (purchase_id) REFERENCES purchases (id),
  CONSTRAINT points_transactions_enrollment FOREIGN KEY (enrollment_id) REFERENCES enrollments (id),
  CONSTRAINT points_transactions_actor FOREIGN KEY (actor_id) REFERENCES users (id),
  CONSTRAINT points_transactions_manual CHECK (type NOT IN ('adjustment', 'reversed') OR reason IS NOT NULL)
) ENGINE=InnoDB;

-- =====================================================================================
-- 8. HALLS, BOOKINGS AND EVENTS (HPV-*, TRR-031/032, TRN-015/016/027, ADM-015..022; D8, DEC-33)
-- Halls belong only to providers (DEC-16); a booking is never linked to a course (DEC-17).
-- =====================================================================================

-- halls: HPV-003, HPV-005, BR-HALL-03/06. Media: photos.
CREATE TABLE halls (
  id                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  hall_provider_id  BIGINT UNSIGNED NOT NULL,
  city_id           BIGINT UNSIGNED NOT NULL,
  address           TEXT NOT NULL,
  map_url           VARCHAR(1000) NULL,
  capacity          INT UNSIGNED NOT NULL,
  hourly_price      DECIMAL(12,2) NOT NULL,
  status            VARCHAR(16) NOT NULL DEFAULT 'active' COMMENT 'active, inactive',
  created_at        TIMESTAMP NULL,
  updated_at        TIMESTAMP NULL,
  deleted_at        TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY halls_uuid (uuid),
  KEY halls_browse (status, city_id),
  KEY halls_provider (hall_provider_id),
  CONSTRAINT halls_provider FOREIGN KEY (hall_provider_id) REFERENCES hall_providers (id),
  CONSTRAINT halls_city FOREIGN KEY (city_id) REFERENCES cities (id),
  CONSTRAINT halls_capacity CHECK (capacity > 0)
) ENGINE=InnoDB;

CREATE TABLE hall_translations (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  hall_id      BIGINT UNSIGNED NOT NULL,
  locale       VARCHAR(10) NOT NULL,
  name         VARCHAR(255) NOT NULL,
  description  TEXT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY hall_translations_uuid (uuid),
  UNIQUE KEY hall_translations_unique (hall_id, locale),
  CONSTRAINT hall_translations_hall FOREIGN KEY (hall_id) REFERENCES halls (id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- hall_features: master list owned by the admin (ADM-016, BR-HALL-04). Media: icon.
CREATE TABLE hall_features (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  is_active   BOOLEAN NOT NULL DEFAULT 1,
  created_at  TIMESTAMP NULL,
  updated_at  TIMESTAMP NULL,
  deleted_at  TIMESTAMP NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE hall_feature_translations (
  id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  hall_feature_id  BIGINT UNSIGNED NOT NULL,
  locale           VARCHAR(10) NOT NULL,
  name             VARCHAR(255) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY hall_feature_translations_uuid (uuid),
  UNIQUE KEY hall_feature_translations_unique (hall_feature_id, locale),
  CONSTRAINT hall_feature_translations_feature FOREIGN KEY (hall_feature_id) REFERENCES hall_features (id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- hall_feature_prices: features a hall offers and the provider's price per slot (HPV-004, BR-HALL-05)
CREATE TABLE hall_feature_prices (
  hall_id          BIGINT UNSIGNED NOT NULL,
  hall_feature_id  BIGINT UNSIGNED NOT NULL,
  price_per_slot   DECIMAL(12,2) NOT NULL,
  created_at       TIMESTAMP NULL,
  updated_at       TIMESTAMP NULL,
  PRIMARY KEY (hall_id, hall_feature_id),
  CONSTRAINT hall_feature_prices_hall FOREIGN KEY (hall_id) REFERENCES halls (id),
  CONSTRAINT hall_feature_prices_feature FOREIGN KEY (hall_feature_id) REFERENCES hall_features (id)
) ENGINE=InnoDB;

-- hall_availability: periods in which a hall can be booked (HPV-006)
CREATE TABLE hall_availability (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  hall_id     BIGINT UNSIGNED NOT NULL,
  starts_at   DATETIME NOT NULL,
  ends_at     DATETIME NOT NULL,
  created_at  TIMESTAMP NULL,
  updated_at  TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY hall_availability_uuid (uuid),
  KEY hall_availability_hall (hall_id, starts_at, ends_at),
  CONSTRAINT hall_availability_hall FOREIGN KEY (hall_id) REFERENCES halls (id),
  CONSTRAINT hall_availability_times CHECK (ends_at > starts_at)
) ENGINE=InnoDB;

-- hall_bookings: a booking request with all its slots as one unit (TRR-031/032, HPV-007..011,
-- BR-HALL-07..13). Booker = a trainer or a center account (C-§12). Price fixed at request.
-- Withdrawn by the booker until Booked; a Booked booking is cancelled only by the provider,
-- on the booker's request or offline with a reason (D8).
CREATE TABLE hall_bookings (
  id                   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  code                 VARCHAR(20) NOT NULL,
  hall_id              BIGINT UNSIGNED NOT NULL,
  booker_id            BIGINT UNSIGNED NOT NULL COMMENT 'trainer or center account',
  status               VARCHAR(16) NOT NULL DEFAULT 'pending'
                       COMMENT 'pending, accepted, booked, rejected, withdrawn, cancelled, completed',
  attendees            INT UNSIGNED NOT NULL,
  hourly_price         DECIMAL(12,2) NOT NULL,
  total_hours          DECIMAL(8,2) NOT NULL,
  features_amount      DECIMAL(12,2) NOT NULL DEFAULT 0,
  total_amount         DECIMAL(12,2) NOT NULL,
  notes                TEXT NULL,
  snapshot             JSON NOT NULL COMMENT 'hall name, address and prices at request',
  rejection_reason     TEXT NULL,
  cancellation_reason  TEXT NULL,
  cancelled_by         BIGINT UNSIGNED NULL,
  accepted_at          TIMESTAMP NULL,
  booked_at            TIMESTAMP NULL,
  rejected_at          TIMESTAMP NULL,
  withdrawn_at         TIMESTAMP NULL,
  cancelled_at         TIMESTAMP NULL,
  completed_at         TIMESTAMP NULL,
  created_at           TIMESTAMP NULL,
  updated_at           TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY hall_bookings_uuid (uuid),
  UNIQUE KEY hall_bookings_code (code),
  KEY hall_bookings_hall (hall_id, status),
  KEY hall_bookings_booker (booker_id, status),
  CONSTRAINT hall_bookings_hall FOREIGN KEY (hall_id) REFERENCES halls (id),
  CONSTRAINT hall_bookings_booker FOREIGN KEY (booker_id) REFERENCES users (id),
  CONSTRAINT hall_bookings_cancelled_by FOREIGN KEY (cancelled_by) REFERENCES users (id),
  CONSTRAINT hall_bookings_total CHECK (total_amount = ROUND(hourly_price * total_hours, 2) + features_amount)
) ENGINE=InnoDB;

-- hall_booking_slots: date + start + end per slot (BR-HALL-10, BR-HALL-25). hall_id is copied
-- so the conflict check is one indexed query.
CREATE TABLE hall_booking_slots (
  id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  hall_booking_id  BIGINT UNSIGNED NOT NULL,
  hall_id          BIGINT UNSIGNED NOT NULL,
  starts_at        DATETIME NOT NULL,
  ends_at          DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY hall_booking_slots_uuid (uuid),
  KEY hall_booking_slots_booking (hall_booking_id),
  KEY hall_booking_slots_conflict (hall_id, starts_at, ends_at),
  CONSTRAINT hall_booking_slots_booking FOREIGN KEY (hall_booking_id) REFERENCES hall_bookings (id),
  CONSTRAINT hall_booking_slots_hall FOREIGN KEY (hall_id) REFERENCES halls (id),
  CONSTRAINT hall_booking_slots_times CHECK (ends_at > starts_at)
) ENGINE=InnoDB;

-- hall_booking_features: features chosen in the request, price per slot snapshotted (BR-HALL-05)
CREATE TABLE hall_booking_features (
  hall_booking_id  BIGINT UNSIGNED NOT NULL,
  hall_feature_id  BIGINT UNSIGNED NOT NULL,
  price_per_slot   DECIMAL(12,2) NOT NULL,
  name_snapshot    JSON NOT NULL COMMENT '{"ar": "...", "en": "..."}',
  PRIMARY KEY (hall_booking_id, hall_feature_id),
  CONSTRAINT hall_booking_features_booking FOREIGN KEY (hall_booking_id) REFERENCES hall_bookings (id),
  CONSTRAINT hall_booking_features_feature FOREIGN KEY (hall_feature_id) REFERENCES hall_features (id)
) ENGINE=InnoDB;

-- hall_booking_cancellation_requests: the booker asks the provider to cancel a Booked booking
-- (C-§13, D8). The app warns that paid fees are not refunded before sending.
CREATE TABLE hall_booking_cancellation_requests (
  id                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  hall_booking_id   BIGINT UNSIGNED NOT NULL,
  requested_by      BIGINT UNSIGNED NOT NULL,
  reason            TEXT NOT NULL,
  status            VARCHAR(16) NOT NULL DEFAULT 'pending' COMMENT 'pending, approved, rejected',
  decided_by        BIGINT UNSIGNED NULL COMMENT 'provider account',
  decided_at        TIMESTAMP NULL,
  decision_note     TEXT NULL,
  created_at        TIMESTAMP NULL,
  updated_at        TIMESTAMP NULL,
  open_booking_id   BIGINT UNSIGNED GENERATED ALWAYS AS (IF(status = 'pending', hall_booking_id, NULL)) STORED,
  PRIMARY KEY (id),
  UNIQUE KEY hall_booking_cancellation_requests_uuid (uuid),
  UNIQUE KEY hall_booking_cancellation_requests_one_open (open_booking_id),
  KEY hall_booking_cancellation_requests_booking (hall_booking_id),
  CONSTRAINT hbcr_booking FOREIGN KEY (hall_booking_id) REFERENCES hall_bookings (id),
  CONSTRAINT hbcr_requested_by FOREIGN KEY (requested_by) REFERENCES users (id),
  CONSTRAINT hbcr_decided_by FOREIGN KEY (decided_by) REFERENCES users (id)
) ENGINE=InnoDB;

-- hall_commissions: Morvia's commission on a booking (ADM-019; BR-ADMIN-HALL-07..11, DEC-22,
-- DEC-33). Created pending at Accepted (not income) → due at Booked (income) → settled by the
-- admin after collecting it outside; any withdrawal / cancellation → cancelled. Rate copied
-- from the global setting (DEC-22, BR-FIN-03).
CREATE TABLE hall_commissions (
  id                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  hall_booking_id       BIGINT UNSIGNED NOT NULL,
  hall_provider_id      BIGINT UNSIGNED NOT NULL,
  booking_amount        DECIMAL(12,2) NOT NULL,
  rate                  DECIMAL(5,2) NOT NULL,
  amount                DECIMAL(12,2) NOT NULL,
  status                VARCHAR(16) NOT NULL DEFAULT 'pending' COMMENT 'pending, due, settled, cancelled',
  due_at                TIMESTAMP NULL,
  settled_at            TIMESTAMP NULL,
  settled_by            BIGINT UNSIGNED NULL,
  settlement_reference  VARCHAR(255) NULL,
  cancelled_at          TIMESTAMP NULL,
  created_at            TIMESTAMP NULL,
  updated_at            TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY hall_commissions_uuid (uuid),
  UNIQUE KEY hall_commissions_booking (hall_booking_id),
  KEY hall_commissions_provider (hall_provider_id, status),
  KEY hall_commissions_status (status, due_at),
  CONSTRAINT hall_commissions_booking FOREIGN KEY (hall_booking_id) REFERENCES hall_bookings (id),
  CONSTRAINT hall_commissions_provider FOREIGN KEY (hall_provider_id) REFERENCES hall_providers (id),
  CONSTRAINT hall_commissions_settled_by FOREIGN KEY (settled_by) REFERENCES users (id)
) ENGINE=InnoDB;

-- events: provider events, external redirect link only (HPV-014, TRN-016, ADM-022, BR-EVT-01).
-- Media: image.
CREATE TABLE events (
  id                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  hall_provider_id  BIGINT UNSIGNED NOT NULL,
  hall_id           BIGINT UNSIGNED NULL,
  city_id           BIGINT UNSIGNED NOT NULL,
  starts_at         DATETIME NOT NULL,
  ends_at           DATETIME NULL,
  external_link     VARCHAR(1000) NOT NULL,
  status            VARCHAR(16) NOT NULL DEFAULT 'published' COMMENT 'published, inactive, ended',
  suspended_by      BIGINT UNSIGNED NULL COMMENT 'admin (BR-ADMIN-HALL-20)',
  created_at        TIMESTAMP NULL,
  updated_at        TIMESTAMP NULL,
  deleted_at        TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY events_uuid (uuid),
  KEY events_browse (status, starts_at),
  KEY events_provider (hall_provider_id),
  CONSTRAINT events_provider FOREIGN KEY (hall_provider_id) REFERENCES hall_providers (id),
  CONSTRAINT events_hall FOREIGN KEY (hall_id) REFERENCES halls (id),
  CONSTRAINT events_city FOREIGN KEY (city_id) REFERENCES cities (id),
  CONSTRAINT events_suspended_by FOREIGN KEY (suspended_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE event_translations (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  event_id     BIGINT UNSIGNED NOT NULL,
  locale       VARCHAR(10) NOT NULL,
  name         VARCHAR(255) NOT NULL,
  description  TEXT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY event_translations_uuid (uuid),
  UNIQUE KEY event_translations_unique (event_id, locale),
  CONSTRAINT event_translations_event FOREIGN KEY (event_id) REFERENCES events (id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =====================================================================================
-- 9. JOBS (TRN-020, ADM-042; BR-JOB-01/02)
-- =====================================================================================

-- job_ads: Media: employer logo.
CREATE TABLE job_ads (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  city_id        BIGINT UNSIGNED NULL,
  employer_name  VARCHAR(255) NOT NULL,
  external_link  VARCHAR(1000) NOT NULL COMMENT 'applying happens outside',
  status         VARCHAR(16) NOT NULL DEFAULT 'published' COMMENT 'published, inactive, ended',
  published_at   TIMESTAMP NULL,
  ends_at        TIMESTAMP NULL,
  created_by     BIGINT UNSIGNED NULL,
  created_at     TIMESTAMP NULL,
  updated_at     TIMESTAMP NULL,
  deleted_at     TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY job_ads_uuid (uuid),
  KEY job_ads_browse (status, published_at),
  CONSTRAINT job_ads_city FOREIGN KEY (city_id) REFERENCES cities (id),
  CONSTRAINT job_ads_created_by FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE job_ad_translations (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  job_ad_id    BIGINT UNSIGNED NOT NULL,
  locale       VARCHAR(10) NOT NULL,
  title        VARCHAR(255) NOT NULL,
  description  TEXT NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY job_ad_translations_uuid (uuid),
  UNIQUE KEY job_ad_translations_unique (job_ad_id, locale),
  CONSTRAINT job_ad_translations_job FOREIGN KEY (job_ad_id) REFERENCES job_ads (id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- job_ad_categories: matched with the categories of the trainee's courses for recommendations (BR-JOB-01)
CREATE TABLE job_ad_categories (
  job_ad_id    BIGINT UNSIGNED NOT NULL,
  category_id  BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (job_ad_id, category_id),
  KEY job_ad_categories_category (category_id),
  CONSTRAINT job_ad_categories_job FOREIGN KEY (job_ad_id) REFERENCES job_ads (id),
  CONSTRAINT job_ad_categories_category FOREIGN KEY (category_id) REFERENCES categories (id)
) ENGINE=InnoDB;

-- job_ad_views: a view counts when the details open (BR-JOB-02)
CREATE TABLE job_ad_views (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid CHAR(36) NOT NULL DEFAULT (UUID()),
  job_ad_id  BIGINT UNSIGNED NOT NULL,
  user_id    BIGINT UNSIGNED NOT NULL,
  viewed_at  TIMESTAMP NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY job_ad_views_uuid (uuid),
  KEY job_ad_views_job (job_ad_id, viewed_at),
  CONSTRAINT job_ad_views_job FOREIGN KEY (job_ad_id) REFERENCES job_ads (id),
  CONSTRAINT job_ad_views_user FOREIGN KEY (user_id) REFERENCES users (id)
) ENGINE=InnoDB;