-- =============================================================================
-- Safar Libya — MySQL schema (Phase 2 design)
-- Source: MongoDB `safar_libya` (26 collections) + property child tables
-- Status: AWAITING APPROVAL — do not execute until confirmed
--
-- ID policy: original MongoDB string `_id` → PRIMARY KEY `id` VARCHAR(36)
-- Measured max _id length across all collections: 29 (VARCHAR(36) sufficient)
-- Charset: utf8mb4
--
-- Table count:
--   26 collection-mapped tables
--   + 3 property child tables (images, videos, blocked_dates)
--   + 1 city_aliases child table
--   = 30 CREATE TABLE statements
--
-- Nested decisions (final):
--   • activity_logs.meta / ledger_entries.meta / wallet_txns.meta → JSON
--   • app_settings.value / bookings.payment / bookings.invoice → JSON
--   • cities.aliases → city_aliases (city_id, alias) + INDEX(alias)
--   • properties.images/videos/blockedDates → child tables
--   • bookings.invoice_number / payment_status → denormalized from JSON at insert time
-- =============================================================================


CREATE DATABASE IF NOT EXISTS safar_libya
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE safar_libya;

-- -----------------------------------------------------------------------------
-- 1) users  (Mongo: users)
-- -----------------------------------------------------------------------------
CREATE TABLE users (
  id                          VARCHAR(36)  NOT NULL,
  email                       VARCHAR(255) NOT NULL,
  password_hash               VARCHAR(255) NULL,
  google_sub                  VARCHAR(255) NULL,
  full_name                   VARCHAR(255) NOT NULL,
  phone                       VARCHAR(64)  NULL,
  avatar_url                  VARCHAR(1024) NULL,
  role                        VARCHAR(32)  NOT NULL DEFAULT 'CUSTOMER',
  status                      VARCHAR(64)  NOT NULL DEFAULT 'PENDING_EMAIL_VERIFICATION',
  locale                      VARCHAR(16)  NOT NULL DEFAULT 'ar',
  email_verified_at           DATETIME(3)  NULL,
  phone_verified_at           DATETIME(3)  NULL,
  passport_url                VARCHAR(1024) NULL,
  owner_verification_status   VARCHAR(32)  NOT NULL DEFAULT 'NONE',
  trusted_owner               TINYINT(1)   NOT NULL DEFAULT 0,
  owner_verified_at           DATETIME(3)  NULL,
  deleted_at                  DATETIME(3)  NULL,
  created_at                  DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at                  DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_users_email (email),
  UNIQUE KEY uk_users_google_sub (google_sub),
  UNIQUE KEY uk_users_phone (phone),
  KEY idx_users_role_status (role, status),
  KEY idx_users_deleted_at (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 2) cities  (Mongo: cities)
-- -----------------------------------------------------------------------------
CREATE TABLE cities (
  id           VARCHAR(36)   NOT NULL,
  name_ar      VARCHAR(255)  NOT NULL,
  name_en      VARCHAR(255)  NOT NULL,
  slug         VARCHAR(255)  NOT NULL,
  country      VARCHAR(128)  NOT NULL DEFAULT 'Tunisia',
  region       VARCHAR(128)  NOT NULL DEFAULT 'Tunisia',
  is_tourist   TINYINT(1)    NOT NULL DEFAULT 1,
  blurb_en     TEXT          NULL,
  blurb_ar     TEXT          NULL,
  image_url    VARCHAR(1024) NULL,
  deleted_at   DATETIME(3)   NULL,
  created_at   DATETIME(3)   NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at   DATETIME(3)   NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_cities_name_en (name_en),
  UNIQUE KEY uk_cities_slug (slug),
  KEY idx_cities_deleted_at (deleted_at),
  KEY idx_cities_is_tourist (is_tourist)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 2b) city_aliases  (from cities.aliases[])
-- -----------------------------------------------------------------------------
CREATE TABLE city_aliases (
  city_id  VARCHAR(36)  NOT NULL,
  alias    VARCHAR(255) NOT NULL,
  PRIMARY KEY (city_id, alias),
  KEY idx_city_aliases_alias (alias),
  CONSTRAINT fk_city_aliases_city
    FOREIGN KEY (city_id) REFERENCES cities (id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 3) app_settings  (Mongo: appsettings)
-- -----------------------------------------------------------------------------
CREATE TABLE app_settings (
  id          VARCHAR(36)  NOT NULL,
  `key`       VARCHAR(128) NOT NULL,
  value       JSON         NOT NULL COMMENT 'Mongo Mixed → JSON (approved)',
  created_at  DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at  DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_app_settings_key (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 4) coupons  (Mongo: coupons)
-- -----------------------------------------------------------------------------
CREATE TABLE coupons (
  id                 VARCHAR(36)    NOT NULL,
  code               VARCHAR(64)    NOT NULL,
  type               VARCHAR(32)    NOT NULL,
  value              DECIMAL(14, 4) NOT NULL,
  max_uses           INT            NULL,
  used_count         INT            NOT NULL DEFAULT 0,
  min_nights         INT            NULL,
  min_subtotal_tnd   DECIMAL(14, 4) NULL,
  valid_from         DATETIME(3)    NULL,
  valid_to           DATETIME(3)    NULL,
  active             TINYINT(1)     NOT NULL DEFAULT 1,
  note               TEXT           NULL,
  created_at         DATETIME(3)    NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at         DATETIME(3)    NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_coupons_code (code),
  KEY idx_coupons_active (active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 5) exchange_rates  (Mongo: exchangerates)
-- -----------------------------------------------------------------------------
CREATE TABLE exchange_rates (
  id              VARCHAR(36)    NOT NULL,
  from_currency   VARCHAR(8)     NOT NULL,
  to_currency     VARCHAR(8)     NOT NULL,
  rate            DECIMAL(18, 8) NOT NULL,
  valid_from      DATETIME(3)    NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  valid_to        DATETIME(3)    NULL,
  updated_by_id   VARCHAR(36)    NULL,
  created_at      DATETIME(3)    NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  KEY idx_exchange_rates_pair_valid (from_currency, to_currency, valid_from),
  KEY idx_exchange_rates_updated_by (updated_by_id),
  CONSTRAINT fk_exchange_rates_updated_by
    FOREIGN KEY (updated_by_id) REFERENCES users (id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 6) properties  (Mongo: properties)
-- -----------------------------------------------------------------------------
CREATE TABLE properties (
  id                    VARCHAR(36)    NOT NULL,
  owner_id              VARCHAR(36)    NOT NULL,
  city_id               VARCHAR(36)    NOT NULL,
  title_ar              VARCHAR(512)   NOT NULL,
  title_en              VARCHAR(512)   NOT NULL,
  slug                  VARCHAR(512)   NOT NULL,
  description_ar        MEDIUMTEXT     NOT NULL,
  description_en        MEDIUMTEXT     NOT NULL,
  address               VARCHAR(1024)  NOT NULL,
  latitude              DECIMAL(10, 7) NULL,
  longitude             DECIMAL(10, 7) NULL,
  status                VARCHAR(32)    NOT NULL DEFAULT 'DRAFT',
  bedrooms              INT            NOT NULL,
  bathrooms             INT            NOT NULL,
  max_guests            INT            NOT NULL,
  wifi                  TINYINT(1)     NOT NULL DEFAULT 0,
  parking               TINYINT(1)     NOT NULL DEFAULT 0,
  air_conditioning      TINYINT(1)     NOT NULL DEFAULT 0,
  kitchen               TINYINT(1)     NOT NULL DEFAULT 0,
  hospital_nearby       TINYINT(1)     NOT NULL DEFAULT 0,
  university_nearby     TINYINT(1)     NOT NULL DEFAULT 0,
  pet_friendly          TINYINT(1)     NOT NULL DEFAULT 0,
  instant_booking       TINYINT(1)     NOT NULL DEFAULT 0,
  base_price_tnd        DECIMAL(14, 4) NOT NULL,
  cleaning_fee_tnd      DECIMAL(14, 4) NOT NULL DEFAULT 0,
  check_in_time         VARCHAR(8)     NOT NULL DEFAULT '15:00',
  check_out_time        VARCHAR(8)     NOT NULL DEFAULT '11:00',
  cancellation_policy   TEXT           NOT NULL,
  house_rules           TEXT           NULL,
  featured              TINYINT(1)     NOT NULL DEFAULT 0,
  deleted_at            DATETIME(3)    NULL,
  created_at            DATETIME(3)    NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at            DATETIME(3)    NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_properties_slug (slug),
  KEY idx_properties_owner (owner_id),
  KEY idx_properties_city (city_id),
  KEY idx_properties_max_guests (max_guests),
  KEY idx_properties_status_featured (status, featured),
  KEY idx_properties_deleted_at (deleted_at),
  CONSTRAINT fk_properties_owner
    FOREIGN KEY (owner_id) REFERENCES users (id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_properties_city
    FOREIGN KEY (city_id) REFERENCES cities (id)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 7) property_images  (from properties.images[])
-- -----------------------------------------------------------------------------
CREATE TABLE property_images (
  id           VARCHAR(36)   NOT NULL COMMENT 'Original embedded image _id',
  property_id  VARCHAR(36)   NOT NULL,
  url          VARCHAR(2048) NOT NULL,
  sort_order   INT           NOT NULL DEFAULT 0,
  created_at   DATETIME(3)   NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  KEY idx_property_images_property (property_id, sort_order),
  CONSTRAINT fk_property_images_property
    FOREIGN KEY (property_id) REFERENCES properties (id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 8) property_videos  (from properties.videos[])
-- -----------------------------------------------------------------------------
CREATE TABLE property_videos (
  id           VARCHAR(36)   NOT NULL COMMENT 'Original embedded video _id',
  property_id  VARCHAR(36)   NOT NULL,
  url          VARCHAR(2048) NOT NULL,
  sort_order   INT           NOT NULL DEFAULT 0,
  created_at   DATETIME(3)   NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  KEY idx_property_videos_property (property_id, sort_order),
  CONSTRAINT fk_property_videos_property
    FOREIGN KEY (property_id) REFERENCES properties (id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 9) property_blocked_dates  (from properties.blockedDates[] strings YYYY-MM-DD)
-- -----------------------------------------------------------------------------
CREATE TABLE property_blocked_dates (
  property_id   VARCHAR(36) NOT NULL,
  blocked_date  DATE        NOT NULL,
  PRIMARY KEY (property_id, blocked_date),
  KEY idx_property_blocked_dates_date (blocked_date),
  CONSTRAINT fk_property_blocked_dates_property
    FOREIGN KEY (property_id) REFERENCES properties (id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 10) bookings  (Mongo: bookings)
-- -----------------------------------------------------------------------------
CREATE TABLE bookings (
  id                          VARCHAR(36)    NOT NULL,
  property_id                 VARCHAR(36)    NOT NULL,
  customer_id                 VARCHAR(36)    NOT NULL,
  owner_id                    VARCHAR(36)    NOT NULL,
  check_in                    DATETIME(3)    NOT NULL,
  check_out                   DATETIME(3)    NOT NULL,
  guests                      INT            NOT NULL,
  nights                      INT            NOT NULL,
  status                      VARCHAR(32)    NOT NULL DEFAULT 'PENDING_PAYMENT',
  exchange_from_currency      VARCHAR(8)     NOT NULL DEFAULT 'TND',
  exchange_to_currency        VARCHAR(8)     NOT NULL DEFAULT 'LYD',
  exchange_rate_rate          DECIMAL(18, 8) NOT NULL,
  exchange_rate_locked_at     DATETIME(3)    NOT NULL,
  exchange_rate_expires_at    DATETIME(3)    NOT NULL,
  subtotal_tnd                DECIMAL(14, 4) NOT NULL,
  cleaning_fee_tnd            DECIMAL(14, 4) NOT NULL,
  platform_fee_tnd            DECIMAL(14, 4) NOT NULL,
  taxes_tnd                   DECIMAL(14, 4) NOT NULL,
  discount_tnd                DECIMAL(14, 4) NOT NULL DEFAULT 0,
  total_tnd                   DECIMAL(14, 4) NOT NULL,
  total_lyd                   DECIMAL(14, 4) NOT NULL,
  owner_payout_tnd            DECIMAL(14, 4) NOT NULL,
  coupon_code                 VARCHAR(64)    NULL,
  wallet_paid_lyd             DECIMAL(14, 4) NOT NULL DEFAULT 0,
  points_earned               INT            NOT NULL DEFAULT 0,
  points_redeemed             INT            NOT NULL DEFAULT 0,
  points_discount_tnd         DECIMAL(14, 4) NOT NULL DEFAULT 0,
  refund_lyd                  DECIMAL(14, 4) NOT NULL DEFAULT 0,
  refund_percent              DECIMAL(8, 4)  NOT NULL DEFAULT 0,
  cancelled_by                VARCHAR(16)    NULL,
  cancelled_at                DATETIME(3)    NULL,
  deleted_at                  DATETIME(3)    NULL,
  payment                     JSON           NULL COMMENT 'Mongo payment subdoc → JSON (approved)',
  invoice                     JSON           NULL COMMENT 'Mongo invoice subdoc → JSON (approved)',
  -- Denormalized from JSON for Mongo unique/index parity (kept in sync by migration/app)
  invoice_number              VARCHAR(64)    NULL,
  payment_status              VARCHAR(32)    NULL,
  created_at                  DATETIME(3)    NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at                  DATETIME(3)    NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_bookings_invoice_number (invoice_number),
  KEY idx_bookings_customer_status (customer_id, status),
  KEY idx_bookings_owner_status (owner_id, status),
  KEY idx_bookings_property_dates (property_id, check_in, check_out),
  KEY idx_bookings_deleted_at (deleted_at),
  KEY idx_bookings_payment_status (payment_status),
  CONSTRAINT fk_bookings_property
    FOREIGN KEY (property_id) REFERENCES properties (id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_bookings_customer
    FOREIGN KEY (customer_id) REFERENCES users (id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_bookings_owner
    FOREIGN KEY (owner_id) REFERENCES users (id)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 11) property_updates  (Mongo: propertyupdates)
-- -----------------------------------------------------------------------------
CREATE TABLE property_updates (
  id           VARCHAR(36)  NOT NULL,
  property_id  VARCHAR(36)  NOT NULL,
  owner_id     VARCHAR(36)  NOT NULL,
  title_ar     VARCHAR(512) NOT NULL,
  title_en     VARCHAR(512) NOT NULL,
  body_ar      MEDIUMTEXT   NOT NULL,
  body_en      MEDIUMTEXT   NOT NULL,
  deleted_at   DATETIME(3)  NULL,
  created_at   DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at   DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  KEY idx_property_updates_property_created (property_id, created_at),
  KEY idx_property_updates_owner (owner_id),
  KEY idx_property_updates_deleted_at (deleted_at),
  CONSTRAINT fk_property_updates_property
    FOREIGN KEY (property_id) REFERENCES properties (id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_property_updates_owner
    FOREIGN KEY (owner_id) REFERENCES users (id)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 12) favorites  (Mongo: favorites)
-- -----------------------------------------------------------------------------
CREATE TABLE favorites (
  id           VARCHAR(36) NOT NULL,
  user_id      VARCHAR(36) NOT NULL,
  property_id  VARCHAR(36) NOT NULL,
  created_at   DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_favorites_user_property (user_id, property_id),
  KEY idx_favorites_property (property_id),
  CONSTRAINT fk_favorites_user
    FOREIGN KEY (user_id) REFERENCES users (id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_favorites_property
    FOREIGN KEY (property_id) REFERENCES properties (id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 13) reviews  (Mongo: reviews)
-- -----------------------------------------------------------------------------
CREATE TABLE reviews (
  id              VARCHAR(36) NOT NULL,
  booking_id      VARCHAR(36) NOT NULL,
  property_id     VARCHAR(36) NOT NULL,
  author_id       VARCHAR(36) NOT NULL,
  rating          TINYINT     NOT NULL,
  comment         TEXT        NULL,
  owner_reply     TEXT        NULL,
  owner_reply_by  VARCHAR(36) NULL,
  created_at      DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at      DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_reviews_booking (booking_id),
  KEY idx_reviews_property (property_id),
  KEY idx_reviews_author (author_id),
  CONSTRAINT fk_reviews_booking
    FOREIGN KEY (booking_id) REFERENCES bookings (id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_reviews_property
    FOREIGN KEY (property_id) REFERENCES properties (id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_reviews_author
    FOREIGN KEY (author_id) REFERENCES users (id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_reviews_owner_reply_by
    FOREIGN KEY (owner_reply_by) REFERENCES users (id)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT chk_reviews_rating CHECK (rating >= 1 AND rating <= 5)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 14) refunds  (Mongo: refunds)
-- -----------------------------------------------------------------------------
CREATE TABLE refunds (
  id                         VARCHAR(36)    NOT NULL,
  booking_id                 VARCHAR(36)    NOT NULL,
  customer_id                VARCHAR(36)    NOT NULL,
  owner_id                   VARCHAR(36)    NOT NULL,
  type                       VARCHAR(32)    NOT NULL,
  amount_lyd                 DECIMAL(14, 4) NOT NULL,
  amount_tnd                 DECIMAL(14, 4) NOT NULL,
  reason_code                VARCHAR(64)    NOT NULL,
  reason_note                TEXT           NULL,
  platform_fee_clawback_tnd  DECIMAL(14, 4) NOT NULL DEFAULT 0,
  owner_payout_clawback_tnd  DECIMAL(14, 4) NOT NULL DEFAULT 0,
  created_by                 VARCHAR(36)    NULL,
  wallet_credited_lyd        DECIMAL(14, 4) NOT NULL DEFAULT 0,
  source                     VARCHAR(32)    NOT NULL DEFAULT 'ADMIN',
  created_at                 DATETIME(3)    NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at                 DATETIME(3)    NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  KEY idx_refunds_booking (booking_id),
  KEY idx_refunds_customer (customer_id),
  KEY idx_refunds_owner (owner_id),
  KEY idx_refunds_created_at (created_at),
  CONSTRAINT fk_refunds_booking
    FOREIGN KEY (booking_id) REFERENCES bookings (id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_refunds_customer
    FOREIGN KEY (customer_id) REFERENCES users (id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_refunds_owner
    FOREIGN KEY (owner_id) REFERENCES users (id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_refunds_created_by
    FOREIGN KEY (created_by) REFERENCES users (id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 15) withdrawal_requests  (Mongo: withdrawalrequests)
-- -----------------------------------------------------------------------------
CREATE TABLE withdrawal_requests (
  id                  VARCHAR(36)    NOT NULL,
  owner_id            VARCHAR(36)    NOT NULL,
  amount_tnd          DECIMAL(14, 4) NOT NULL,
  amount_lyd          DECIMAL(14, 4) NOT NULL,
  exchange_rate_rate  DECIMAL(18, 8) NOT NULL DEFAULT 0,
  method              VARCHAR(32)    NOT NULL,
  status              VARCHAR(32)    NOT NULL DEFAULT 'PENDING',
  note                TEXT           NULL,
  rejection_reason    TEXT           NULL,
  reviewed_by         VARCHAR(36)    NULL,
  reviewed_at         DATETIME(3)    NULL,
  created_at          DATETIME(3)    NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at          DATETIME(3)    NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  KEY idx_withdrawal_requests_owner (owner_id),
  KEY idx_withdrawal_requests_status_created (status, created_at),
  CONSTRAINT fk_withdrawal_requests_owner
    FOREIGN KEY (owner_id) REFERENCES users (id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_withdrawal_requests_reviewed_by
    FOREIGN KEY (reviewed_by) REFERENCES users (id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 16) ledger_entries  (Mongo: ledgerentries)
-- -----------------------------------------------------------------------------
CREATE TABLE ledger_entries (
  id             VARCHAR(36)    NOT NULL,
  type           VARCHAR(64)    NOT NULL,
  direction      VARCHAR(16)    NOT NULL,
  booking_id     VARCHAR(36)    NULL,
  refund_id      VARCHAR(36)    NULL,
  withdrawal_id  VARCHAR(36)    NULL,
  amount_lyd     DECIMAL(14, 4) NOT NULL,
  amount_tnd     DECIMAL(14, 4) NOT NULL,
  party_user_id  VARCHAR(36)    NULL,
  party_role     VARCHAR(32)    NOT NULL,
  status         VARCHAR(32)    NOT NULL DEFAULT 'POSTED',
  meta           JSON           NULL COMMENT 'Mongo Mixed → JSON (approved)',
  created_at     DATETIME(3)    NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at     DATETIME(3)    NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  -- Emulate Mongo partial unique indexes (populated by migration/app; NOT generated —
  -- MySQL rejects combining these generated cols with FKs on the same table).
  booking_payment_key VARCHAR(36) NULL,
  withdrawal_key      VARCHAR(36) NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_ledger_booking_payment (booking_payment_key),
  UNIQUE KEY uk_ledger_withdrawal (withdrawal_key),
  KEY idx_ledger_type (type),
  KEY idx_ledger_booking (booking_id),
  KEY idx_ledger_party_user (party_user_id),
  KEY idx_ledger_created_at (created_at),
  KEY idx_ledger_type_created (type, created_at),
  CONSTRAINT fk_ledger_booking
    FOREIGN KEY (booking_id) REFERENCES bookings (id)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_ledger_refund
    FOREIGN KEY (refund_id) REFERENCES refunds (id)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_ledger_withdrawal
    FOREIGN KEY (withdrawal_id) REFERENCES withdrawal_requests (id)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_ledger_party_user
    FOREIGN KEY (party_user_id) REFERENCES users (id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 17) wallets  (Mongo: wallets)
-- -----------------------------------------------------------------------------
CREATE TABLE wallets (
  id           VARCHAR(36)    NOT NULL,
  user_id      VARCHAR(36)    NOT NULL,
  balance_lyd  DECIMAL(14, 4) NOT NULL DEFAULT 0,
  created_at   DATETIME(3)    NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at   DATETIME(3)    NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_wallets_user (user_id),
  CONSTRAINT fk_wallets_user
    FOREIGN KEY (user_id) REFERENCES users (id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 18) wallet_topups  (Mongo: wallettopups — currently 0 docs)
-- -----------------------------------------------------------------------------
CREATE TABLE wallet_topups (
  id            VARCHAR(36)    NOT NULL,
  user_id       VARCHAR(36)    NOT NULL,
  amount_lyd    DECIMAL(14, 4) NOT NULL,
  bank_name     VARCHAR(255)   NOT NULL,
  reference     VARCHAR(255)   NOT NULL,
  status        VARCHAR(32)    NOT NULL DEFAULT 'PENDING',
  reviewed_by   VARCHAR(36)    NULL,
  reviewed_at   DATETIME(3)    NULL,
  review_note   TEXT           NULL,
  created_at    DATETIME(3)    NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at    DATETIME(3)    NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  KEY idx_wallet_topups_user (user_id),
  KEY idx_wallet_topups_status_created (status, created_at),
  CONSTRAINT fk_wallet_topups_user
    FOREIGN KEY (user_id) REFERENCES users (id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_wallet_topups_reviewed_by
    FOREIGN KEY (reviewed_by) REFERENCES users (id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 19) wallet_txns  (Mongo: wallettxns)
-- -----------------------------------------------------------------------------
CREATE TABLE wallet_txns (
  id             VARCHAR(36)    NOT NULL,
  wallet_id      VARCHAR(36)    NOT NULL,
  user_id        VARCHAR(36)    NOT NULL,
  type           VARCHAR(32)    NOT NULL,
  amount_lyd     DECIMAL(14, 4) NOT NULL,
  balance_after  DECIMAL(14, 4) NOT NULL,
  booking_id     VARCHAR(36)    NULL,
  top_up_id      VARCHAR(36)    NULL,
  note           TEXT           NULL,
  meta           JSON           NULL COMMENT 'Mongo Mixed → JSON (approved)',
  created_at     DATETIME(3)    NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  KEY idx_wallet_txns_wallet (wallet_id),
  KEY idx_wallet_txns_user_created (user_id, created_at),
  KEY idx_wallet_txns_booking (booking_id),
  KEY idx_wallet_txns_top_up (top_up_id),
  CONSTRAINT fk_wallet_txns_wallet
    FOREIGN KEY (wallet_id) REFERENCES wallets (id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_wallet_txns_user
    FOREIGN KEY (user_id) REFERENCES users (id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_wallet_txns_booking
    FOREIGN KEY (booking_id) REFERENCES bookings (id)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_wallet_txns_top_up
    FOREIGN KEY (top_up_id) REFERENCES wallet_topups (id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 20) loyalty_accounts  (Mongo: loyaltyaccounts)
-- -----------------------------------------------------------------------------
CREATE TABLE loyalty_accounts (
  id          VARCHAR(36) NOT NULL,
  user_id     VARCHAR(36) NOT NULL,
  points      INT         NOT NULL DEFAULT 0,
  created_at  DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at  DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_loyalty_accounts_user (user_id),
  CONSTRAINT fk_loyalty_accounts_user
    FOREIGN KEY (user_id) REFERENCES users (id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 21) loyalty_txns  (Mongo: loyaltytxns)
-- -----------------------------------------------------------------------------
CREATE TABLE loyalty_txns (
  id            VARCHAR(36) NOT NULL,
  user_id       VARCHAR(36) NOT NULL,
  type          VARCHAR(32) NOT NULL,
  delta         INT         NOT NULL,
  points_after  INT         NOT NULL,
  booking_id    VARCHAR(36) NULL,
  note          TEXT        NULL,
  created_at    DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  KEY idx_loyalty_txns_user_created (user_id, created_at),
  KEY idx_loyalty_txns_booking (booking_id),
  CONSTRAINT fk_loyalty_txns_user
    FOREIGN KEY (user_id) REFERENCES users (id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_loyalty_txns_booking
    FOREIGN KEY (booking_id) REFERENCES bookings (id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 22) coupon_redemptions  (Mongo: couponredemptions)
-- -----------------------------------------------------------------------------
CREATE TABLE coupon_redemptions (
  id            VARCHAR(36)    NOT NULL,
  coupon_id     VARCHAR(36)    NOT NULL,
  user_id       VARCHAR(36)    NOT NULL,
  booking_id    VARCHAR(36)    NOT NULL,
  discount_tnd  DECIMAL(14, 4) NOT NULL,
  code          VARCHAR(64)    NOT NULL,
  created_at    DATETIME(3)    NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_coupon_redemptions_booking (booking_id),
  KEY idx_coupon_redemptions_coupon_user (coupon_id, user_id),
  KEY idx_coupon_redemptions_user (user_id),
  CONSTRAINT fk_coupon_redemptions_coupon
    FOREIGN KEY (coupon_id) REFERENCES coupons (id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_coupon_redemptions_user
    FOREIGN KEY (user_id) REFERENCES users (id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_coupon_redemptions_booking
    FOREIGN KEY (booking_id) REFERENCES bookings (id)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 23) notifications  (Mongo: notifications)
-- -----------------------------------------------------------------------------
CREATE TABLE notifications (
  id           VARCHAR(36)   NOT NULL,
  user_id      VARCHAR(36)   NOT NULL,
  title_ar     VARCHAR(512)  NOT NULL,
  title_en     VARCHAR(512)  NOT NULL,
  message_ar   TEXT          NOT NULL,
  message_en   TEXT          NOT NULL,
  link         VARCHAR(1024) NULL,
  read_at      DATETIME(3)   NULL,
  created_at   DATETIME(3)   NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  KEY idx_notifications_user_created (user_id, created_at),
  KEY idx_notifications_user_read (user_id, read_at),
  CONSTRAINT fk_notifications_user
    FOREIGN KEY (user_id) REFERENCES users (id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 24) activity_logs  (Mongo: activitylogs)
-- -----------------------------------------------------------------------------
CREATE TABLE activity_logs (
  id           VARCHAR(36)  NOT NULL,
  actor_id     VARCHAR(36)  NOT NULL,
  actor_email  VARCHAR(255) NOT NULL,
  actor_name   VARCHAR(255) NOT NULL DEFAULT '',
  action       VARCHAR(128) NOT NULL,
  entity_type  VARCHAR(64)  NOT NULL,
  entity_id    VARCHAR(36)  NULL COMMENT 'Polymorphic — no FK',
  meta         JSON         NULL COMMENT 'Mongo Mixed → JSON (approved)',
  created_at   DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  KEY idx_activity_logs_actor (actor_id),
  KEY idx_activity_logs_action (action),
  KEY idx_activity_logs_entity_type (entity_type),
  KEY idx_activity_logs_entity_id (entity_id),
  KEY idx_activity_logs_created_at (created_at),
  CONSTRAINT fk_activity_logs_actor
    FOREIGN KEY (actor_id) REFERENCES users (id)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 25) contact_messages  (Mongo: contactmessages)
-- -----------------------------------------------------------------------------
CREATE TABLE contact_messages (
  id          VARCHAR(36)  NOT NULL,
  user_id     VARCHAR(36)  NULL,
  name        VARCHAR(255) NOT NULL,
  email       VARCHAR(255) NOT NULL,
  subject     VARCHAR(512) NOT NULL,
  message     TEXT         NOT NULL,
  read_at     DATETIME(3)  NULL,
  created_at  DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  KEY idx_contact_messages_created_at (created_at),
  KEY idx_contact_messages_user (user_id),
  CONSTRAINT fk_contact_messages_user
    FOREIGN KEY (user_id) REFERENCES users (id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 26) refresh_tokens  (Mongo: refreshtokens)
-- -----------------------------------------------------------------------------
CREATE TABLE refresh_tokens (
  id           VARCHAR(36)  NOT NULL,
  user_id      VARCHAR(36)  NOT NULL,
  token_hash   VARCHAR(255) NOT NULL,
  expires_at   DATETIME(3)  NOT NULL,
  revoked_at   DATETIME(3)  NULL,
  created_at   DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_refresh_tokens_hash (token_hash),
  KEY idx_refresh_tokens_user (user_id),
  KEY idx_refresh_tokens_expires (expires_at),
  CONSTRAINT fk_refresh_tokens_user
    FOREIGN KEY (user_id) REFERENCES users (id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 27) email_verification_tokens  (Mongo: emailverificationtokens)
-- -----------------------------------------------------------------------------
CREATE TABLE email_verification_tokens (
  id           VARCHAR(36)  NOT NULL,
  user_id      VARCHAR(36)  NOT NULL,
  token_hash   VARCHAR(255) NOT NULL,
  expires_at   DATETIME(3)  NOT NULL,
  used_at      DATETIME(3)  NULL,
  created_at   DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_email_verification_tokens_hash (token_hash),
  KEY idx_email_verification_tokens_user (user_id),
  CONSTRAINT fk_email_verification_tokens_user
    FOREIGN KEY (user_id) REFERENCES users (id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 28) password_reset_tokens  (Mongo: passwordresettokens)
-- -----------------------------------------------------------------------------
CREATE TABLE password_reset_tokens (
  id           VARCHAR(36)  NOT NULL,
  user_id      VARCHAR(36)  NOT NULL,
  token_hash   VARCHAR(255) NOT NULL,
  expires_at   DATETIME(3)  NOT NULL,
  used_at      DATETIME(3)  NULL,
  created_at   DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_password_reset_tokens_hash (token_hash),
  KEY idx_password_reset_tokens_user (user_id),
  CONSTRAINT fk_password_reset_tokens_user
    FOREIGN KEY (user_id) REFERENCES users (id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 29) otp_challenges  (Mongo: otpchallenges)
-- -----------------------------------------------------------------------------
CREATE TABLE otp_challenges (
  id           VARCHAR(36)  NOT NULL,
  user_id      VARCHAR(36)  NOT NULL,
  channel      VARCHAR(16)  NOT NULL,
  code_hash    VARCHAR(255) NOT NULL,
  expires_at   DATETIME(3)  NOT NULL,
  attempts     INT          NOT NULL DEFAULT 0,
  consumed_at  DATETIME(3)  NULL,
  created_at   DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at   DATETIME(3)  NULL COMMENT 'Present on some Mongo docs, model often omits updates',
  PRIMARY KEY (id),
  KEY idx_otp_challenges_user (user_id),
  KEY idx_otp_challenges_user_channel_consumed (user_id, channel, consumed_at),
  CONSTRAINT fk_otp_challenges_user
    FOREIGN KEY (user_id) REFERENCES users (id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- Mapping summary (Mongo collection → MySQL table)
-- =============================================================================
-- activitylogs            → activity_logs
-- appsettings             → app_settings
-- bookings                → bookings (+ payment/invoice JSON)
-- cities                  → cities
-- contactmessages         → contact_messages
-- couponredemptions       → coupon_redemptions
-- coupons                 → coupons
-- emailverificationtokens → email_verification_tokens
-- exchangerates           → exchange_rates
-- favorites               → favorites
-- ledgerentries           → ledger_entries
-- loyaltyaccounts         → loyalty_accounts
-- loyaltytxns             → loyalty_txns
-- notifications           → notifications
-- otpchallenges           → otp_challenges
-- passwordresettokens     → password_reset_tokens
-- properties              → properties
--   images[]              → property_images
--   videos[]              → property_videos
--   blockedDates[]        → property_blocked_dates
-- cities.aliases[]        → city_aliases
-- propertyupdates         → property_updates
-- refreshtokens           → refresh_tokens
-- refunds                 → refunds
-- reviews                 → reviews
-- users                   → users
-- wallets                 → wallets
-- wallettopups            → wallet_topups
-- wallettxns              → wallet_txns
-- withdrawalrequests      → withdrawal_requests
-- =============================================================================
