-- ============================================================================= -- 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 -- ============================================================================= SET NAMES utf8mb4; SET time_zone = '+00:00'; 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 -- =============================================================================