-- ===================================================================== -- JOSHEN — Home beauty & care services platform -- Database schema · MySQL 8.0.16+ (also runs on MariaDB 10.5+, see notes) -- Source of truth: BRD v1.3 (11/8/2026) + prototype joshen-app-baher.vercel.app -- -- Decisions confirmed with the client where BRD and design differ: -- D1 Packages are prepaid multi-session bundles with a validity period, -- reviewed by Joshen before they go live. -- D2 Group booking: one order, one provider, one place/time, several -- people each with their own services, one delivery fee. -- D3 Instant booking: customer pays, then the provider has an accept -- window (default 20 s, setting). Reject/timeout => automatic refund. -- D4 An establishment is a single provider account (no staff accounts). -- Design additions adopted (superset of the BRD): service duration, notes to -- specialist, customer city/birth year/photo, notification preferences, -- rating tags, provider radius + districts, default transport preference, -- document expiry dates, coupons, saved cards / Apple Pay / Tabby, several -- Featured plans + VAT, cancellation fees as admin-editable rules. -- TD-173 (owner request 2026-11-08): admin dashboard alerts + web push -- (`admin` app/audience, `web` devices) and admin general notifications -- (notification_broadcasts*). -- -- Conventions -- * BIGINT UNSIGNED AUTO_INCREMENT ids; human codes (JSH-48219, TRP-9921, -- SUP-1000, INV-100000) are UNIQUE columns generated by the application. -- * Public ids (TD-19, TD-20): every table has `id BIGINT UNSIGNED -- AUTO_INCREMENT PRIMARY KEY`. Every table that clients create, read, -- update, or delete also has `uuid CHAR(36) NOT NULL UNIQUE`; the -- integer `id` is the primary key and the target of foreign keys (fast -- joins) and never leaves the server, clients only see the uuid in -- routes, request bodies, headers, and the `id` key of every response. -- The app generates the uuid (UUIDv7). NO uuid on `*_translations` -- (reached through their parent) and on pure link tables (permission_role, -- permission_user, service_occasions, provider_districts, driver_districts, -- package_occasions, review_tag_links): they are only synced through -- their parents. Tables written without a model of their own -- (device_apps, notification_preferences, platform_settings) carry -- DEFAULT (UUID()) so any insert path gets a uuid (MySQL 8.0.13+). -- Tables that were keyed by user_id / a composite key (customers, -- providers, drivers, wallets, device_apps, notification_preferences, the -- link tables, provider_categories, platform_settings) keep that key as a -- UNIQUE key; foreign keys that point at providers(user_id), -- customers(user_id), and drivers(user_id) still reference that unique key. -- * Money DECIMAL(12,2) SAR. Times DATETIME(3) stored in UTC. -- * utf8mb4 everywhere (Arabic + emoji), collation utf8mb4_unicode_ci as -- in the Laravel connection. Booleans are TINYINT(1). -- * §1–§3 are base tables copied from the Laravel migrations (TD-13); -- §4 onward is the Joshen domain. -- * Files (TD-14): every uploaded or generated file is a row in the base -- media table (modules/Media), linked by model_type + model_id + -- collection. Domain tables have no *_url / file columns; each table -- lists its collections in a "Media:" comment, and the model declares -- them in $media_keys (HasMedia). Flow: POST upload -> {hash} -> send -- the hash under the collection key on create/update -> attached. -- Unattached uploads are purged after media.orphan_ttl_hours. -- * "Only one active X" rules use a generated column + UNIQUE index -- (MySQL has no partial indexes; UNIQUE ignores NULLs). -- * CHECK constraints need MySQL 8.0.16+ (MariaDB 10.2+). -- * A column used inside a generated key cannot also have ON DELETE CASCADE -- (MySQL rule), so vehicles.driver_id and trip_offers.trip_id don't cascade. -- * Translations (TD-12): admin-managed display text (names, labels, titles, -- descriptions) lives in a {entity}_translations table, one row per locale -- ('ar', 'en'), UNIQUE ({entity}_id, locale), ON DELETE CASCADE. The 'ar' -- row is required and 'en' optional (application rule); reads fall back -- to 'ar'. Name uniqueness scoped to a parent column (e.g. a district name -- per city) is checked by the application, because MySQL cannot index -- across two tables. Text written by users (package names, bios, chat, -- addresses) and checkout snapshots stay single-language on the row. -- MariaDB note: JSON columns become LONGTEXT + JSON check. -- ===================================================================== -- CREATE DATABASE joshen CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; (config/database.php) -- USE joshen; SET NAMES utf8mb4; SET time_zone = '+00:00'; -- ===================================================================== -- BASE TABLES (§1–§3, TD-08 / TD-13) -- Owned by the Laravel base migrations and used as they are — never alter -- them for Joshen. The DDL below is a copy of what those migrations create -- (mysqldump of database/migrations/0001_*, 2025_05_05 devices, 2025_10_13 -- geo, 2026_07_26 audit_logs, 2026_10_20 drop shipping zones, -- modules/Verification, modules/Media). If a base migration -- changes, regenerate these blocks instead of editing them by hand. -- Joshen data the base tables lack lives in schema-owned tables -- (device_apps, notification_preferences, the profile tables in §5–§7). -- ===================================================================== -- ===================================================================== -- 1. GEOGRAPHY (BRD: إدارة مناطق الخدمة) -- BRD city = governorates, BRD district = regions (country SA). -- Domain FK columns keep the names city_id / district_id. -- No shipping zones (TD-15): delivery is priced by delivery_fee_rules (BR-11). -- ===================================================================== CREATE TABLE countries ( id bigint unsigned NOT NULL AUTO_INCREMENT, uuid char(36) NOT NULL, phone_code varchar(5) DEFAULT NULL, phone_starting_number varchar(255) DEFAULT NULL, phone_length int DEFAULT NULL, short_name varchar(255) DEFAULT NULL, continent enum('africa','europe','asia','south_america','north_america','australia') NOT NULL DEFAULT 'asia', is_active tinyint(1) NOT NULL DEFAULT '1', created_at timestamp NULL DEFAULT NULL, updated_at timestamp NULL DEFAULT NULL, UNIQUE KEY countries_uuid_unique (uuid), PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE country_translations ( id bigint unsigned NOT NULL AUTO_INCREMENT, country_id bigint unsigned NOT NULL, name varchar(255) NOT NULL, slug varchar(255) DEFAULT NULL, currency varchar(255) DEFAULT NULL, nationality varchar(255) DEFAULT NULL, locale varchar(255) NOT NULL, PRIMARY KEY (id), UNIQUE KEY country_translations_country_id_locale_unique (country_id,locale), KEY country_translations_locale_index (locale), CONSTRAINT country_translations_country_id_foreign FOREIGN KEY (country_id) REFERENCES countries (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE governorates ( id bigint unsigned NOT NULL AUTO_INCREMENT, uuid char(36) NOT NULL, country_id bigint unsigned NOT NULL, lat double DEFAULT NULL, lng double DEFAULT NULL, is_active tinyint(1) NOT NULL DEFAULT '1', sort_order int NOT NULL DEFAULT '0', created_at timestamp NULL DEFAULT NULL, updated_at timestamp NULL DEFAULT NULL, UNIQUE KEY governorates_uuid_unique (uuid), PRIMARY KEY (id), KEY governorates_country_id_is_active_index (country_id,is_active), CONSTRAINT governorates_country_id_foreign FOREIGN KEY (country_id) REFERENCES countries (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE governorate_translations ( id bigint unsigned NOT NULL AUTO_INCREMENT, governorate_id bigint unsigned NOT NULL, name varchar(255) NOT NULL, slug varchar(255) DEFAULT NULL, locale varchar(255) NOT NULL, PRIMARY KEY (id), UNIQUE KEY governorate_translations_governorate_id_locale_unique (governorate_id,locale), KEY governorate_translations_locale_index (locale), CONSTRAINT governorate_translations_governorate_id_foreign FOREIGN KEY (governorate_id) REFERENCES governorates (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE regions ( id bigint unsigned NOT NULL AUTO_INCREMENT, uuid char(36) NOT NULL, governorate_id bigint unsigned NOT NULL, lat double DEFAULT NULL, lng double DEFAULT NULL, is_active tinyint(1) NOT NULL DEFAULT '1', sort_order int NOT NULL DEFAULT '0', created_at timestamp NULL DEFAULT NULL, updated_at timestamp NULL DEFAULT NULL, UNIQUE KEY regions_uuid_unique (uuid), PRIMARY KEY (id), KEY regions_governorate_id_is_active_index (governorate_id,is_active), CONSTRAINT regions_governorate_id_foreign FOREIGN KEY (governorate_id) REFERENCES governorates (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE region_translations ( id bigint unsigned NOT NULL AUTO_INCREMENT, region_id bigint unsigned NOT NULL, name varchar(255) NOT NULL, slug varchar(255) DEFAULT NULL, locale varchar(255) NOT NULL, PRIMARY KEY (id), UNIQUE KEY region_translations_region_id_locale_unique (region_id,locale), KEY region_translations_locale_index (locale), CONSTRAINT region_translations_region_id_foreign FOREIGN KEY (region_id) REFERENCES regions (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Delivery fee per city (BRD: تحديد رسوم التوصيل أو قواعد احتسابها لكل نطاق) CREATE TABLE delivery_fee_rules ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', city_id BIGINT UNSIGNED NULL COMMENT 'governorates.id; NULL = default rule', base_fee DECIMAL(12,2) NOT NULL DEFAULT 0, per_km_fee DECIMAL(12,2) NOT NULL DEFAULT 0, min_fee DECIMAL(12,2) NOT NULL DEFAULT 0, max_fee DECIMAL(12,2), avg_speed_kmh DECIMAL(5,1) COMMENT 'average road speed for the live ETA of a transport trip; NULL = the default rule, then 30 (TD-183)', is_active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), CONSTRAINT fk_dfr_city FOREIGN KEY (city_id) REFERENCES governorates(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ===================================================================== -- 2. IDENTITY & AUTH -- One person = one users row; customer / provider / driver profiles are -- 1:1 tables keyed by user_id, so one person can use several apps. -- users.user_type only tells admins (super_admin / supervisor) from app -- users; app access comes from the profile row + token app claim (TD-01). -- Phone = phone_code + phone, unique per pair (application rule). -- Status = is_active / is_ban / deleted_at. Avatar = Media module. -- OTP = verifications (Verification module). Sessions = JWT, no table. -- Registration consent flags live on users (TD-17): guarantees_female_gender, -- accept_policies, accept_terms (BR-01). The profile tables keep the -- per-app acceptance timestamps. -- roles is listed here because users.role_id needs it (RBAC in §3). -- ===================================================================== CREATE TABLE roles ( id bigint unsigned NOT NULL AUTO_INCREMENT, uuid char(36) NOT NULL, parent_id bigint unsigned DEFAULT NULL, level smallint unsigned NOT NULL DEFAULT '0', slug varchar(255) DEFAULT NULL, is_active tinyint(1) NOT NULL DEFAULT '1', created_at timestamp NULL DEFAULT NULL, updated_at timestamp NULL DEFAULT NULL, UNIQUE KEY roles_uuid_unique (uuid), PRIMARY KEY (id), KEY roles_parent_id_index (parent_id), KEY roles_level_index (level), CONSTRAINT roles_parent_id_foreign FOREIGN KEY (parent_id) REFERENCES roles (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE role_translations ( id bigint unsigned NOT NULL AUTO_INCREMENT, role_id bigint unsigned NOT NULL, locale varchar(255) NOT NULL, name varchar(255) NOT NULL, PRIMARY KEY (id), UNIQUE KEY role_translations_role_id_locale_unique (role_id,locale), KEY role_translations_locale_index (locale), CONSTRAINT role_translations_role_id_foreign FOREIGN KEY (role_id) REFERENCES roles (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE users ( id bigint unsigned NOT NULL AUTO_INCREMENT, uuid char(36) NOT NULL, role_id bigint unsigned DEFAULT NULL, full_name varchar(255) NOT NULL, email varchar(255) DEFAULT NULL, password varchar(255) DEFAULT NULL, phone_code varchar(255) DEFAULT NULL, phone varchar(255) DEFAULT NULL, guarantees_female_gender tinyint(1) NOT NULL DEFAULT '0', accept_policies tinyint(1) NOT NULL DEFAULT '0', accept_terms tinyint(1) NOT NULL DEFAULT '0', birth_date date DEFAULT NULL, is_active tinyint(1) NOT NULL DEFAULT '0', is_admin_active_user tinyint(1) NOT NULL DEFAULT '1', is_ban tinyint(1) DEFAULT '0', ban_reason text, user_type varchar(255) DEFAULT NULL, referral_code varchar(255) DEFAULT NULL, remember_token varchar(100) DEFAULT NULL, lat varchar(255) DEFAULT NULL, lng varchar(255) DEFAULT NULL, locale varchar(255) DEFAULT 'ar', allow_notifications tinyint(1) NOT NULL DEFAULT '1', last_login_at timestamp NULL DEFAULT NULL, email_verified_at timestamp NULL DEFAULT NULL, phone_verified_at timestamp NULL DEFAULT NULL, last_password_change_at timestamp NULL DEFAULT NULL, created_at timestamp NULL DEFAULT NULL, updated_at timestamp NULL DEFAULT NULL, deleted_at timestamp NULL DEFAULT NULL, UNIQUE KEY users_uuid_unique (uuid), PRIMARY KEY (id), UNIQUE KEY users_email_unique (email), KEY users_role_id_foreign (role_id), CONSTRAINT users_role_id_foreign FOREIGN KEY (role_id) REFERENCES roles (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE devices ( id bigint unsigned NOT NULL AUTO_INCREMENT, uuid char(36) NOT NULL, user_id bigint unsigned NOT NULL, token text NOT NULL, type enum('ios','android','huawei','web') NOT NULL COMMENT 'web = admin dashboard browser (TD-173)', created_at timestamp NULL DEFAULT NULL, updated_at timestamp NULL DEFAULT NULL, UNIQUE KEY devices_uuid_unique (uuid), PRIMARY KEY (id), KEY devices_user_id_foreign (user_id), CONSTRAINT devices_user_id_foreign FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE verifications ( id bigint unsigned NOT NULL AUTO_INCREMENT, uuid char(36) NOT NULL, token char(36) NOT NULL, verifiable_type varchar(255) DEFAULT NULL, verifiable_id bigint unsigned DEFAULT NULL, type enum('email','phone') NOT NULL, phone_code varchar(255) DEFAULT NULL, target varchar(255) NOT NULL, code varchar(255) NOT NULL, expires_at timestamp NOT NULL, verified_at timestamp NULL DEFAULT NULL, attempts tinyint unsigned NOT NULL DEFAULT '0', created_at timestamp NULL DEFAULT NULL, updated_at timestamp NULL DEFAULT NULL, UNIQUE KEY verifications_uuid_unique (uuid), PRIMARY KEY (id), KEY verifications_verifiable_type_verifiable_id_index (verifiable_type,verifiable_id), KEY verifications_token_type_target_index (token,type,target) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Which app a push token belongs to (base devices has no app column) CREATE TABLE device_apps ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE DEFAULT (UUID()) COMMENT 'public id; the only id clients see (TD-19)', device_id BIGINT UNSIGNED NOT NULL UNIQUE, app ENUM('customer','provider','driver','admin') NOT NULL COMMENT 'admin = dashboard web push (TD-173)', app_version VARCHAR(20), last_seen_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), KEY ix_device_apps_app (app), CONSTRAINT fk_dapp_device FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Design: تفضيلات الإشعارات CREATE TABLE notification_preferences ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE DEFAULT (UUID()) COMMENT 'public id; the only id clients see (TD-19)', user_id BIGINT UNSIGNED NOT NULL, app ENUM('customer','provider','driver') NOT NULL, category ENUM('orders','payments','chat','offers','account') NOT NULL, push_enabled TINYINT(1) NOT NULL DEFAULT 1, sms_enabled TINYINT(1) NOT NULL DEFAULT 1, UNIQUE KEY ux_notification_preferences (user_id, app, category), CONSTRAINT fk_np_user FOREIGN KEY (user_id) REFERENCES users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ===================================================================== -- 3. ADMIN PANEL USERS & PERMISSIONS (BRD: إدارة المشرفين والصلاحيات) -- Admins are users rows with user_type super_admin / supervisor. Every -- admin FK in the domain (approved_by, reviewed_by, processed_by, -- created_by, updated_by, moderated_by, verified_by, assigned_admin_id, -- admin_id) references users(id). -- Permissions are route names (rbac.auto); permissions.resource groups -- them into the 21 dashboard sections (customers, providers, … support). -- Admin activity log = audit_logs (written by the base pipeline). -- ===================================================================== CREATE TABLE permissions ( id bigint unsigned NOT NULL AUTO_INCREMENT, uuid char(36) NOT NULL, name varchar(255) DEFAULT NULL, route_name varchar(255) DEFAULT NULL, resource varchar(50) DEFAULT NULL, action varchar(50) DEFAULT NULL, created_at timestamp NULL DEFAULT NULL, updated_at timestamp NULL DEFAULT NULL, UNIQUE KEY permissions_uuid_unique (uuid), PRIMARY KEY (id), KEY permissions_resource_action_index (resource,action) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE permission_translations ( id bigint unsigned NOT NULL AUTO_INCREMENT, permission_id bigint unsigned NOT NULL, locale varchar(255) NOT NULL, title varchar(255) NOT NULL, PRIMARY KEY (id), UNIQUE KEY permission_translations_permission_id_locale_unique (permission_id,locale), KEY permission_translations_locale_index (locale), CONSTRAINT permission_translations_permission_id_foreign FOREIGN KEY (permission_id) REFERENCES permissions (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE permission_role ( id bigint unsigned NOT NULL AUTO_INCREMENT, role_id bigint unsigned NOT NULL, permission_id bigint unsigned NOT NULL, PRIMARY KEY (id), UNIQUE KEY permission_role_role_id_permission_id_unique (role_id,permission_id), KEY permission_role_permission_id_foreign (permission_id), CONSTRAINT permission_role_permission_id_foreign FOREIGN KEY (permission_id) REFERENCES permissions (id) ON DELETE CASCADE, CONSTRAINT permission_role_role_id_foreign FOREIGN KEY (role_id) REFERENCES roles (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE permission_user ( id bigint unsigned NOT NULL AUTO_INCREMENT, user_id bigint unsigned NOT NULL, permission_id bigint unsigned NOT NULL, PRIMARY KEY (id), UNIQUE KEY permission_user_user_id_permission_id_unique (user_id,permission_id), KEY permission_user_permission_id_foreign (permission_id), CONSTRAINT permission_user_permission_id_foreign FOREIGN KEY (permission_id) REFERENCES permissions (id) ON DELETE CASCADE, CONSTRAINT permission_user_user_id_foreign FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE audit_logs ( id bigint unsigned NOT NULL AUTO_INCREMENT, uuid char(36) NOT NULL, auditable_type varchar(255) NOT NULL, auditable_id bigint unsigned NOT NULL, action enum('created','updated','deleted','admin_action') NOT NULL, old_values json DEFAULT NULL, new_values json DEFAULT NULL, actor_id bigint unsigned DEFAULT NULL, ip_address varchar(45) DEFAULT NULL, user_agent text, correlation_id char(36) DEFAULT NULL, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY audit_logs_uuid_unique (uuid), PRIMARY KEY (id), KEY idx_auditable (auditable_type,auditable_id), KEY idx_actor (actor_id), KEY idx_created (created_at), KEY idx_action (action), CONSTRAINT audit_logs_actor_id_foreign FOREIGN KEY (actor_id) REFERENCES users (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ===================================================================== -- 3a. MEDIA (base: modules/Media — files for every model, TD-14) -- model_type = Eloquent morph class, model_id = the row's key -- (user_id for customers / providers / drivers). sort_order orders -- multiple collections. URL = disk + path; variants in meta. -- ===================================================================== CREATE TABLE media ( id bigint unsigned NOT NULL AUTO_INCREMENT, uuid char(36) NOT NULL, hash varchar(255) DEFAULT NULL, disk varchar(255) NOT NULL DEFAULT 'public', path varchar(255) NOT NULL, original_name varchar(255) DEFAULT NULL, mime_type varchar(255) DEFAULT NULL, size bigint unsigned DEFAULT NULL, is_temp tinyint(1) NOT NULL DEFAULT '1', model_type varchar(255) DEFAULT NULL, model_id bigint unsigned DEFAULT NULL, collection varchar(255) DEFAULT NULL, sort_order int unsigned NOT NULL DEFAULT '0', meta json DEFAULT NULL, deleted_at timestamp NULL DEFAULT NULL, created_at timestamp NULL DEFAULT NULL, updated_at timestamp NULL DEFAULT NULL, UNIQUE KEY media_uuid_unique (uuid), PRIMARY KEY (id), KEY media_model_type_model_id_index (model_type,model_id), KEY media_collection_index (collection), KEY media_is_temp_index (is_temp), KEY media_hash_index (hash) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ===================================================================== -- 4. CATALOG — categories (main services), service tree (service → subservice → addon), ranges -- ===================================================================== -- Media: image (single) CREATE TABLE categories ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', slug VARCHAR(255) NULL COMMENT 'optional globally unique category slug (TD-41)', icon_key VARCHAR(40) COMMENT 'e.g. lipstick, scissors', sort_order INT NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, 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), UNIQUE KEY ux_categories_slug (slug) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE category_translations ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, category_id BIGINT UNSIGNED NOT NULL, locale VARCHAR(10) NOT NULL COMMENT 'ar (required) / en', name VARCHAR(100) NOT NULL, description TEXT, UNIQUE KEY ux_category_tr (category_id, locale), UNIQUE KEY ux_category_tr_name (locale, name), CONSTRAINT fk_category_tr_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Service tree (TD-42): category -> bookable service -> option group -> choice. -- Type 1 is directly under a category, with positive duration and admin price range. -- Type 2 is an unpriced option group; its own children_selection/children_required -- govern the choices below it independently of other groups on the same service. -- Type 3 is a provider-priced choice; min=max=0 represents a free choice. -- Providers own actual prices for types 1/3; admins own definitions and ranges. -- Empty/inactive groups have no active choices to select (existing BR-05 convention). -- Media: image (single, uploaded hash via HasMedia; TD-37) CREATE TABLE services ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', category_id BIGINT UNSIGNED NOT NULL, parent_id BIGINT UNSIGNED COMMENT 'NULL for type 1; else the node one level up (trigger)', type TINYINT NOT NULL DEFAULT 1 COMMENT '1=>bookable service, 2=>option group, 3=>choice', children_selection ENUM('single','multiple') NOT NULL DEFAULT 'single' COMMENT 'how children are picked (BR-05)', children_required TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'type 2: customer must pick a choice; ignored without active choices', default_duration_min INT NOT NULL DEFAULT 0 COMMENT 'type 1: duration (> 0); type 2: 0; type 3: extra minutes', reference_price DECIMAL(12,2) COMMENT 'السعر المرجعي shown to providers', min_price DECIMAL(12,2) NOT NULL DEFAULT 0 COMMENT 'admin price range; type 2: 0; type 3 may be free', max_price DECIMAL(12,2) NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, sort_order 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), KEY ix_services_parent (parent_id), KEY ix_services_category (category_id), CONSTRAINT fk_services_category FOREIGN KEY (category_id) REFERENCES categories(id), CONSTRAINT fk_services_parent FOREIGN KEY (parent_id) REFERENCES services(id), CONSTRAINT ck_services_type CHECK (type IN (1,2,3)), CONSTRAINT ck_services_parent CHECK ((type = 1 AND parent_id IS NULL) OR (type > 1 AND parent_id IS NOT NULL)), CONSTRAINT ck_services_duration CHECK (default_duration_min >= 0 AND (type <> 1 OR default_duration_min > 0) AND (type <> 2 OR default_duration_min = 0)), CONSTRAINT ck_services_range CHECK (min_price >= 0 AND max_price >= min_price AND (type <> 1 OR min_price > 0) AND (type <> 2 OR (min_price = 0 AND max_price = 0))), CONSTRAINT ck_services_reference CHECK (reference_price IS NULL OR (reference_price >= min_price AND reference_price <= max_price)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Name unique per parent (per category for type 1) + locale: checked by the application CREATE TABLE service_translations ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, service_id BIGINT UNSIGNED NOT NULL, locale VARCHAR(10) NOT NULL COMMENT 'ar (required) / en', name VARCHAR(150) NOT NULL, description TEXT, UNIQUE KEY ux_service_tr (service_id, locale), CONSTRAINT fk_service_tr_service FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Occasions used by packages and the Joshen finder (عروس / سهرة / عناية شخصية) CREATE TABLE occasions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', sort_order INT NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE occasion_translations ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, occasion_id BIGINT UNSIGNED NOT NULL, locale VARCHAR(10) NOT NULL COMMENT 'ar (required) / en', name VARCHAR(100) NOT NULL, UNIQUE KEY ux_occasion_tr (occasion_id, locale), UNIQUE KEY ux_occasion_tr_name (locale, name), CONSTRAINT fk_occasion_tr_occasion FOREIGN KEY (occasion_id) REFERENCES occasions(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE service_occasions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, service_id BIGINT UNSIGNED NOT NULL, occasion_id BIGINT UNSIGNED NOT NULL, CONSTRAINT fk_so_service FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE, CONSTRAINT fk_so_occasion FOREIGN KEY (occasion_id) REFERENCES occasions(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ===================================================================== -- 5. CUSTOMERS -- ===================================================================== -- Profile photo = users media "image" (base User model) CREATE TABLE customers ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', user_id BIGINT UNSIGNED NOT NULL UNIQUE, city_id BIGINT UNSIGNED COMMENT 'governorates.id', birth_year SMALLINT COMMENT 'design: optional', onboarding_seen TINYINT(1) NOT NULL DEFAULT 0, female_declared_at DATETIME(3) COMMENT 'إقرار بأنني أنثى (BR-01)', terms_accepted_at DATETIME(3), privacy_accepted_at DATETIME(3), rating_avg DECIMAL(3,2) COMMENT 'ratings received from providers', rating_count INT NOT NULL DEFAULT 0, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), CONSTRAINT fk_customers_user FOREIGN KEY (user_id) REFERENCES users(id), CONSTRAINT fk_customers_city FOREIGN KEY (city_id) REFERENCES governorates(id), CONSTRAINT ck_customers_birth CHECK (birth_year IS NULL OR birth_year BETWEEN 1920 AND 2100) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE customer_addresses ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', customer_id BIGINT UNSIGNED NOT NULL, label VARCHAR(60) NOT NULL COMMENT 'المنزل / العمل / custom', label_type ENUM('home','work','other') NOT NULL DEFAULT 'other', city_id BIGINT UNSIGNED NOT NULL, district_id BIGINT UNSIGNED, address_line VARCHAR(255) NOT NULL COMMENT 'شارع الأمير سلطان — فيلا ١٢', details VARCHAR(255) COMMENT 'الدور الأول', lat DECIMAL(9,6), lng DECIMAL(9,6), is_default TINYINT(1) 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), deleted_at DATETIME(3), default_key BIGINT UNSIGNED AS (IF(is_default = 1 AND deleted_at IS NULL, customer_id, NULL)) STORED COMMENT 'one default address per customer', UNIQUE KEY ux_customer_default_address (default_key), KEY ix_caddr_customer (customer_id), CONSTRAINT fk_caddr_customer FOREIGN KEY (customer_id) REFERENCES customers(user_id), CONSTRAINT fk_caddr_city FOREIGN KEY (city_id) REFERENCES governorates(id), CONSTRAINT fk_caddr_district FOREIGN KEY (district_id) REFERENCES regions(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ===================================================================== -- 6. PROVIDERS (مقدّمات الخدمة) — individual or establishment, one account (D4) -- ===================================================================== -- Media: portfolio (multiple, صور الأعمال; order = media.sort_order) -- Profile photo = users media "image" (base User model) CREATE TABLE providers ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', user_id BIGINT UNSIGNED NOT NULL UNIQUE, provider_type ENUM('individual','establishment') NOT NULL, display_name VARCHAR(150) NOT NULL COMMENT 'person name or trade name', professional_title VARCHAR(150) COMMENT 'أخصائية عناية بالبشرة', bio TEXT, national_id VARCHAR(20) COMMENT 'individuals: national ID / iqama', commercial_register_no VARCHAR(20) COMMENT 'establishments: CR', vat_number VARCHAR(20) COMMENT 'optional', authorized_person_name VARCHAR(150) COMMENT 'المفوّضة بالتوقيع / responsible person', authorized_person_phone VARCHAR(20), years_experience SMALLINT, experience_note VARCHAR(255) COMMENT '4 سنوات — مركز لمسة الجمال', specialists_count SMALLINT COMMENT 'establishments: info only (D4)', city_id BIGINT UNSIGNED, base_lat DECIMAL(9,6), base_lng DECIMAL(9,6), service_radius_km SMALLINT NOT NULL DEFAULT 10, account_status ENUM('incomplete','pending_review','needs_changes','approved','rejected','suspended') NOT NULL DEFAULT 'incomplete', status_reason VARCHAR(500), female_declared_at DATETIME(3) COMMENT 'إقرار بأنني أنثى (BR-01)', terms_accepted_at DATETIME(3), privacy_accepted_at DATETIME(3), submitted_at DATETIME(3), approved_at DATETIME(3), approved_by BIGINT UNSIGNED, is_accepting_bookings TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'مفعّلة / متوقفة مؤقتاً', instant_enabled TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'الحجز الفوري', instant_terms_ack_at DATETIME(3) COMMENT 'confirmation pop-up accepted', default_transport_mode ENUM('ask','joshen_driver','self') NOT NULL DEFAULT 'ask', rating_avg DECIMAL(3,2), rating_count INT NOT NULL DEFAULT 0, completed_orders_count INT NOT NULL DEFAULT 0, commitment_rate DECIMAL(5,2) COMMENT 'الالتزام %', 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), KEY ix_providers_search (city_id, account_status, is_accepting_bookings, instant_enabled), CONSTRAINT fk_providers_user FOREIGN KEY (user_id) REFERENCES users(id), CONSTRAINT fk_providers_city FOREIGN KEY (city_id) REFERENCES governorates(id), CONSTRAINT fk_providers_admin FOREIGN KEY (approved_by) REFERENCES users(id), CONSTRAINT ck_providers_radius CHECK (service_radius_km > 0), CONSTRAINT ck_providers_cr CHECK (provider_type = 'individual' OR commercial_register_no IS NOT NULL OR account_status = 'incomplete') ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE provider_districts ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, provider_id BIGINT UNSIGNED NOT NULL, district_id BIGINT UNSIGNED NOT NULL, UNIQUE KEY ux_provider_districts (provider_id, district_id), CONSTRAINT fk_pd_provider FOREIGN KEY (provider_id) REFERENCES providers(user_id) ON DELETE CASCADE, CONSTRAINT fk_pd_district FOREIGN KEY (district_id) REFERENCES regions(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Main categories she works in. Adding one after approval needs admin approval (BRD). CREATE TABLE provider_categories ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE DEFAULT (UUID()) COMMENT 'public id; the only id clients see (TD-19)', provider_id BIGINT UNSIGNED NOT NULL, category_id BIGINT UNSIGNED NOT NULL, status ENUM('requested','approved','rejected') NOT NULL DEFAULT 'requested', is_enabled TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'provider toggle (التصنيفات المفعّلة)', reviewed_by BIGINT UNSIGNED, reviewed_at DATETIME(3), created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), UNIQUE KEY ux_provider_categories (provider_id, category_id), CONSTRAINT fk_pc_provider FOREIGN KEY (provider_id) REFERENCES providers(user_id) ON DELETE CASCADE, CONSTRAINT fk_pc_category FOREIGN KEY (category_id) REFERENCES categories(id), CONSTRAINT fk_pc_admin FOREIGN KEY (reviewed_by) REFERENCES users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Subservices AND addons she offers + her price (must sit inside services.min/max_price — trigger). -- An order's service total = the subservice row's price + the chosen addon rows' prices. CREATE TABLE provider_services ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', provider_id BIGINT UNSIGNED NOT NULL, service_id BIGINT UNSIGNED NOT NULL, price DECIMAL(12,2) NOT NULL, duration_min INT COMMENT 'override of default duration', is_active TINYINT(1) NOT NULL DEFAULT 1, 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), UNIQUE KEY ux_ps_provider_service (provider_id, service_id), CONSTRAINT fk_ps_provider FOREIGN KEY (provider_id) REFERENCES providers(user_id) ON DELETE CASCADE, CONSTRAINT fk_ps_service FOREIGN KEY (service_id) REFERENCES services(id), CONSTRAINT ck_ps_price CHECK (price >= 0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Weekly working hours (أوقات العمل); more than one slot per day allowed CREATE TABLE provider_working_hours ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', provider_id BIGINT UNSIGNED NOT NULL, weekday TINYINT NOT NULL COMMENT '0 = Sunday … 6 = Saturday', start_time TIME NOT NULL, end_time TIME NOT NULL, KEY ix_pwh_provider (provider_id, weekday), CONSTRAINT fk_pwh_provider FOREIGN KEY (provider_id) REFERENCES providers(user_id) ON DELETE CASCADE, CONSTRAINT ck_pwh_weekday CHECK (weekday BETWEEN 0 AND 6), CONSTRAINT ck_pwh_times CHECK (end_time > start_time) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Closing a specific day or slot (BRD: إغلاق يوم معين أو فترة معينة) CREATE TABLE provider_time_off ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', provider_id BIGINT UNSIGNED NOT NULL, starts_at DATETIME(3) NOT NULL, ends_at DATETIME(3) NOT NULL, reason VARCHAR(255), CONSTRAINT fk_pto_provider FOREIGN KEY (provider_id) REFERENCES providers(user_id) ON DELETE CASCADE, CONSTRAINT ck_pto_times CHECK (ends_at > starts_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ===================================================================== -- 7. DRIVERS (المندوبات) -- ===================================================================== -- Profile photo = users media "image"; licence / registration = user_documents CREATE TABLE drivers ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', user_id BIGINT UNSIGNED NOT NULL UNIQUE, national_id VARCHAR(20), birth_date DATE, nationality VARCHAR(60), city_id BIGINT UNSIGNED, account_status ENUM('incomplete','pending_review','needs_changes','approved','rejected','suspended') NOT NULL DEFAULT 'incomplete', status_reason VARCHAR(500), female_declared_at DATETIME(3) COMMENT 'إقرار بأنني أنثى (BR-01)', terms_accepted_at DATETIME(3), privacy_accepted_at DATETIME(3), submitted_at DATETIME(3), approved_at DATETIME(3), approved_by BIGINT UNSIGNED, is_online TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'متصل / غير متصل', current_lat DECIMAL(9,6), current_lng DECIMAL(9,6), location_updated_at DATETIME(3), rating_avg DECIMAL(3,2), rating_count INT NOT NULL DEFAULT 0, completed_trips_count 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), KEY ix_drivers_dispatch (city_id, account_status, is_online), CONSTRAINT fk_drivers_user FOREIGN KEY (user_id) REFERENCES users(id), CONSTRAINT fk_drivers_city FOREIGN KEY (city_id) REFERENCES governorates(id), CONSTRAINT fk_drivers_admin FOREIGN KEY (approved_by) REFERENCES users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE driver_districts ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, driver_id BIGINT UNSIGNED NOT NULL, district_id BIGINT UNSIGNED NOT NULL, UNIQUE KEY ux_driver_districts (driver_id, district_id), CONSTRAINT fk_dd_driver FOREIGN KEY (driver_id) REFERENCES drivers(user_id) ON DELETE CASCADE, CONSTRAINT fk_dd_district FOREIGN KEY (district_id) REFERENCES regions(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE vehicles ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', driver_id BIGINT UNSIGNED NOT NULL, vehicle_type ENUM('car','motorbike') NOT NULL, make_model VARCHAR(100) NOT NULL COMMENT 'تويوتا يارس / هوندا PCX', model_year SMALLINT, color VARCHAR(40), plate_number VARCHAR(20) NOT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), active_key BIGINT UNSIGNED AS (IF(is_active = 1, driver_id, NULL)) STORED, UNIQUE KEY ux_vehicle_active (active_key), KEY ix_vehicles_driver (driver_id), CONSTRAINT fk_vehicles_driver FOREIGN KEY (driver_id) REFERENCES drivers(user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ===================================================================== -- 8. DOCUMENTS, PAYOUT ACCOUNTS, CHANGE REQUESTS, REGISTRATION REVIEW -- (shared by providers and drivers) -- ===================================================================== -- Media: file (single, required: image or PDF). A replacement upload is a new row. CREATE TABLE user_documents ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', user_id BIGINT UNSIGNED NOT NULL, owner_role ENUM('provider','driver') NOT NULL, doc_type ENUM('national_id','professional_certificate','health_certificate','security_check', 'commercial_register','business_license','staff_health_certificates', 'driving_license','vehicle_registration','other') NOT NULL, doc_number VARCHAR(50), issuer VARCHAR(100) COMMENT 'وزارة الصحة / أبشر …', issued_at DATE, expires_at DATE COMMENT 'design: قاربت الانتهاء', status ENUM('pending','verified','rejected','expired','replaced') NOT NULL DEFAULT 'pending', review_note VARCHAR(500), reviewed_by BIGINT UNSIGNED, reviewed_at DATETIME(3), created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), KEY ix_docs_user (user_id, owner_role, doc_type), KEY ix_docs_expiry (status, expires_at), CONSTRAINT fk_docs_user FOREIGN KEY (user_id) REFERENCES users(id), CONSTRAINT fk_docs_admin FOREIGN KEY (reviewed_by) REFERENCES users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE payout_accounts ( -- الحساب البنكي (view-only in app; edits via request) id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', user_id BIGINT UNSIGNED NOT NULL, owner_role ENUM('provider','driver') NOT NULL, holder_name VARCHAR(150) NOT NULL, bank_name VARCHAR(100) NOT NULL, iban CHAR(24) NOT NULL, status ENUM('pending','verified','rejected','inactive') NOT NULL DEFAULT 'pending', is_primary TINYINT(1) NOT NULL DEFAULT 1, verified_by BIGINT UNSIGNED, verified_at DATETIME(3), created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), primary_key_ VARCHAR(40) AS (IF(is_primary = 1 AND status <> 'inactive', CONCAT(user_id, ':', owner_role), NULL)) STORED, UNIQUE KEY ux_payout_primary (primary_key_), KEY ix_payout_user (user_id), CONSTRAINT fk_pa_user FOREIGN KEY (user_id) REFERENCES users(id), CONSTRAINT fk_pa_admin FOREIGN KEY (verified_by) REFERENCES users(id), CONSTRAINT ck_pa_iban CHECK (iban REGEXP '^SA[0-9A-Z]{22}$') ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Edits that need admin approval (city, area, ID/CR, responsible person, bank…). -- Old data stays effective until approved (BRD). -- Media: files (multiple) — new documents / photos submitted with the request; -- payload references them by media id. On approval they move to the target row. CREATE TABLE profile_change_requests ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', user_id BIGINT UNSIGNED NOT NULL, owner_role ENUM('provider','driver') NOT NULL, change_type ENUM('profile','work_area','identity','bank_account','vehicle','document','new_category') NOT NULL, scope VARCHAR(40) COMMENT 'document type of a document request (TD-184)', payload JSON NOT NULL COMMENT '{"field":{"old":..,"new":..}}', status ENUM('pending','approved','rejected','cancelled') NOT NULL DEFAULT 'pending', decision_note VARCHAR(500), idempotency_key VARCHAR(128), request_hash CHAR(64), reviewed_by BIGINT UNSIGNED, reviewed_at DATETIME(3), created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), UNIQUE KEY ux_pcr_idempotency (user_id, owner_role, idempotency_key), KEY ix_pcr_pending (status, created_at), CONSTRAINT fk_pcr_user FOREIGN KEY (user_id) REFERENCES users(id), CONSTRAINT fk_pcr_admin FOREIGN KEY (reviewed_by) REFERENCES users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Registration review history (submitted / approved / rejected / needs changes …) CREATE TABLE account_review_events ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', user_id BIGINT UNSIGNED NOT NULL, owner_role ENUM('provider','driver') NOT NULL, action ENUM('submitted','approved','rejected','needs_changes','suspended','reactivated') NOT NULL, note VARCHAR(500), admin_id BIGINT UNSIGNED, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), from_status ENUM('incomplete','pending_review','needs_changes','approved','rejected','suspended') NULL, to_status ENUM('incomplete','pending_review','needs_changes','approved','rejected','suspended') NULL, KEY ix_are_user (user_id, owner_role), CONSTRAINT fk_are_user FOREIGN KEY (user_id) REFERENCES users(id), CONSTRAINT fk_are_admin FOREIGN KEY (admin_id) REFERENCES users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ===================================================================== -- 9. FEATURED SUBSCRIPTION (باقة التمييز) — several plans (design) -- ===================================================================== CREATE TABLE featured_plans ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', billing_period ENUM('monthly','yearly') NOT NULL, duration_days INT NOT NULL, price DECIMAL(12,2) NOT NULL COMMENT 'excl. VAT', vat_rate DECIMAL(5,2) NOT NULL DEFAULT 15.00, priority_level SMALLINT NOT NULL DEFAULT 1 COMMENT 'ranking weight among subscribers', grants_verified_badge TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'موثّقة', grants_featured_badge TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'مميّزة', features_packages_on_home TINYINT(1) NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, sort_order 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), CONSTRAINT ck_fplans_duration CHECK (duration_days > 0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE featured_plan_translations ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, featured_plan_id BIGINT UNSIGNED NOT NULL, locale VARCHAR(10) NOT NULL COMMENT 'ar (required) / en', name VARCHAR(100) NOT NULL COMMENT 'تمييز فضي / ذهبي / ذهبي سنوي', description TEXT, features JSON NOT NULL COMMENT 'bullet list shown in app', UNIQUE KEY ux_fplan_tr (featured_plan_id, locale), UNIQUE KEY ux_fplan_tr_name (locale, name), CONSTRAINT fk_fplan_tr_plan FOREIGN KEY (featured_plan_id) REFERENCES featured_plans(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE provider_subscriptions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', provider_id BIGINT UNSIGNED NOT NULL, plan_id BIGINT UNSIGNED NOT NULL, status ENUM('pending_payment','payment_failed','active','expired','cancelled','suspended') NOT NULL DEFAULT 'pending_payment', price DECIMAL(12,2) NOT NULL COMMENT 'snapshot', vat_amount DECIMAL(12,2) NOT NULL, total_amount DECIMAL(12,2) NOT NULL, auto_renew TINYINT(1) NOT NULL DEFAULT 1, starts_at DATETIME(3), ends_at DATETIME(3), cancelled_at DATETIME(3), renewal_of_id BIGINT UNSIGNED, 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), active_key BIGINT UNSIGNED AS (IF(status = 'active', provider_id, NULL)) STORED, UNIQUE KEY ux_one_active_subscription (active_key), KEY ix_psub_provider (provider_id), CONSTRAINT fk_psub_provider FOREIGN KEY (provider_id) REFERENCES providers(user_id), CONSTRAINT fk_psub_plan FOREIGN KEY (plan_id) REFERENCES featured_plans(id), CONSTRAINT fk_psub_renewal FOREIGN KEY (renewal_of_id) REFERENCES provider_subscriptions(id), CONSTRAINT ck_psub_dates CHECK (status NOT IN ('active','expired') OR (starts_at IS NOT NULL AND ends_at > starts_at)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ===================================================================== -- 10. PACKAGES (D1: prepaid, multi-session, validity, admin review) -- ===================================================================== -- Media: image (single, card cover) CREATE TABLE packages ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', provider_id BIGINT UNSIGNED NOT NULL, category_id BIGINT UNSIGNED NOT NULL COMMENT 'main category shown on card', name VARCHAR(150) NOT NULL, description TEXT, price DECIMAL(12,2) NOT NULL, compare_at_price DECIMAL(12,2) COMMENT 'sum of her service prices (كان ١٥٠٠)', validity_days SMALLINT NOT NULL, status ENUM('draft','pending_review','approved','rejected','archived') NOT NULL DEFAULT 'draft', is_visible TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'provider toggle: معروضة للعميلات الآن', hidden_by_admin TINYINT(1) NOT NULL DEFAULT 0, review_note VARCHAR(500), reviewed_by BIGINT UNSIGNED, reviewed_at DATETIME(3), submitted_at DATETIME(3), 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), KEY ix_packages_live (status, is_visible, hidden_by_admin, category_id), KEY ix_packages_provider (provider_id), CONSTRAINT fk_packages_provider FOREIGN KEY (provider_id) REFERENCES providers(user_id), CONSTRAINT fk_packages_category FOREIGN KEY (category_id) REFERENCES categories(id), CONSTRAINT fk_packages_admin FOREIGN KEY (reviewed_by) REFERENCES users(id), CONSTRAINT ck_packages_price CHECK (price > 0), CONSTRAINT ck_packages_validity CHECK (validity_days IN (30,45,60,90)), CONSTRAINT ck_packages_compare CHECK (compare_at_price IS NULL OR compare_at_price >= price) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE package_occasions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, package_id BIGINT UNSIGNED NOT NULL, occasion_id BIGINT UNSIGNED NOT NULL, UNIQUE KEY ux_package_occasions (package_id, occasion_id), CONSTRAINT fk_po_package FOREIGN KEY (package_id) REFERENCES packages(id) ON DELETE CASCADE, CONSTRAINT fk_po_occasion FOREIGN KEY (occasion_id) REFERENCES occasions(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Contents: "٤ جلسات عناية بالبشرة (٧٥ د × ٤)". Service must be active for the provider (app rule). CREATE TABLE package_items ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', package_id BIGINT UNSIGNED NOT NULL, service_id BIGINT UNSIGNED NOT NULL, sessions SMALLINT NOT NULL DEFAULT 1, duration_min INT NOT NULL, UNIQUE KEY ux_pi_package_service (package_id, service_id), CONSTRAINT fk_pi_package FOREIGN KEY (package_id) REFERENCES packages(id) ON DELETE CASCADE, CONSTRAINT fk_pi_service FOREIGN KEY (service_id) REFERENCES services(id), CONSTRAINT ck_pi_sessions CHECK (sessions > 0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- A customer's purchased package (sessions balance until expiry) CREATE TABLE customer_packages ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', customer_id BIGINT UNSIGNED NOT NULL, package_id BIGINT UNSIGNED NOT NULL, provider_id BIGINT UNSIGNED NOT NULL, purchase_order_id BIGINT UNSIGNED COMMENT 'FK added after orders', price_paid DECIMAL(12,2) NOT NULL, validity_days SMALLINT COMMENT 'purchase snapshot; null for legacy records', status ENUM('pending_payment','active','consumed','expired','cancelled','refunded') NOT NULL DEFAULT 'pending_payment', activated_at DATETIME(3), expires_at DATETIME(3) COMMENT 'activated_at + validity_days', created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), KEY ix_cpkg_customer (customer_id, status), CONSTRAINT fk_cpkg_customer FOREIGN KEY (customer_id) REFERENCES customers(user_id), CONSTRAINT fk_cpkg_package FOREIGN KEY (package_id) REFERENCES packages(id), CONSTRAINT fk_cpkg_provider FOREIGN KEY (provider_id) REFERENCES providers(user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE customer_package_items ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', customer_package_id BIGINT UNSIGNED NOT NULL, service_id BIGINT UNSIGNED NOT NULL, sessions_total SMALLINT NOT NULL, sessions_used SMALLINT NOT NULL DEFAULT 0, session_value DECIMAL(12,2) NOT NULL COMMENT 'share of price_paid per session; basis for earnings', name_snapshot VARCHAR(150) COMMENT 'purchase snapshot; null for legacy records', duration_min INT COMMENT 'purchase snapshot; null for legacy records', allocated_value DECIMAL(12,2) COMMENT 'item share of purchase price; null for legacy records', CONSTRAINT fk_cpi_cpkg FOREIGN KEY (customer_package_id) REFERENCES customer_packages(id) ON DELETE CASCADE, CONSTRAINT fk_cpi_service FOREIGN KEY (service_id) REFERENCES services(id), CONSTRAINT ck_cpi_used CHECK (sessions_used BETWEEN 0 AND sessions_total) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ===================================================================== -- 11. COUPONS (design: كوبون الخصم) -- ===================================================================== CREATE TABLE coupons ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', code VARCHAR(40) NOT NULL COMMENT 'case-insensitive via collation', description VARCHAR(255), discount_type ENUM('percent','fixed') NOT NULL, discount_value DECIMAL(12,2) NOT NULL, max_discount DECIMAL(12,2), min_order_amount DECIMAL(12,2) NOT NULL DEFAULT 0, applies_to ENUM('all','category','service','provider','package') NOT NULL DEFAULT 'all', applies_to_id BIGINT UNSIGNED, funded_by ENUM('platform','provider') NOT NULL DEFAULT 'platform', usage_limit INT, per_customer_limit INT NOT NULL DEFAULT 1, starts_at DATETIME(3), ends_at DATETIME(3), is_active TINYINT(1) NOT NULL DEFAULT 1, created_by BIGINT UNSIGNED, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), UNIQUE KEY ux_coupons_code (code), CONSTRAINT fk_coupons_admin FOREIGN KEY (created_by) REFERENCES users(id), CONSTRAINT ck_coupons_value CHECK (discount_value > 0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ===================================================================== -- 12. ORDERS (one visit at one address/time by one provider) -- Status machine: -- scheduled: pending_provider -> awaiting_payment -> confirmed -> … -- instant : awaiting_payment -> pending_provider (20 s) -> confirmed -> … -- … -> preparing -> on_the_way -> arrived -> in_progress -> completed -- side exits: rejected, expired (no answer / unpaid), cancelled -- "Execution stage" (no free customer cancel) starts at on_the_way. -- Customer labels: confirmed=حجز مؤكد, preparing=جاري تجهيز الطلب, -- on_the_way=مقدمة الخدمة في الطريق, arrived=تم الوصول, -- in_progress=الخدمة قيد التنفيذ, completed=مكتمل, cancelled=ملغي -- ===================================================================== CREATE TABLE cancellation_rules ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', cancelled_by ENUM('customer','provider') NOT NULL, booking_type ENUM('instant','scheduled','any') NOT NULL, stage ENUM('before_window','within_window','after_departure','after_arrival') NOT NULL, window_minutes INT COMMENT 'scheduled: minutes before scheduled_at; instant: minutes after confirmed_at; NULL means no timed tier (TD-105)', is_allowed TINYINT(1) NOT NULL DEFAULT 1, fee_percent_of_service DECIMAL(5,2) NOT NULL DEFAULT 0 COMMENT 'kept from the refund', charge_delivery_fee TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'delivery fee not refunded', provider_share_percent DECIMAL(5,2) NOT NULL DEFAULT 100 COMMENT 'share of the fee paid to provider', affects_provider_ranking TINYINT(1) NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, sort_order INT NOT NULL DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Optional: a rule may have no description in any locale CREATE TABLE cancellation_rule_translations ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, cancellation_rule_id BIGINT UNSIGNED NOT NULL, locale VARCHAR(10) NOT NULL COMMENT 'ar / en', description VARCHAR(500) NOT NULL, UNIQUE KEY ux_crule_tr (cancellation_rule_id, locale), CONSTRAINT fk_crule_tr_rule FOREIGN KEY (cancellation_rule_id) REFERENCES cancellation_rules(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE finder_searches ( -- Joshen finder (guided search) log id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', customer_id BIGINT UNSIGNED COMMENT 'NULL for guests', guest_device_id VARCHAR(100), category_id BIGINT UNSIGNED COMMENT 'مكياج / شعر / أظافر', wants_package TINYINT(1) NOT NULL DEFAULT 0 COMMENT '"باقة"', occasion_id BIGINT UNSIGNED, address_id BIGINT UNSIGNED, district_id BIGINT UNSIGNED, desired_date DATE, desired_time TIME, budget_min DECIMAL(12,2), budget_max DECIMAL(12,2), results_count INT, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), CONSTRAINT fk_fs_customer FOREIGN KEY (customer_id) REFERENCES customers(user_id), CONSTRAINT fk_fs_category FOREIGN KEY (category_id) REFERENCES categories(id), CONSTRAINT fk_fs_occasion FOREIGN KEY (occasion_id) REFERENCES occasions(id), CONSTRAINT fk_fs_address FOREIGN KEY (address_id) REFERENCES customer_addresses(id), CONSTRAINT fk_fs_district FOREIGN KEY (district_id) REFERENCES regions(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE orders ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', code VARCHAR(20) NOT NULL COMMENT 'JSH-48219, generated by the app', customer_id BIGINT UNSIGNED NOT NULL, provider_id BIGINT UNSIGNED NOT NULL, booking_type ENUM('instant','scheduled') NOT NULL, order_kind ENUM('service','package_purchase','package_session') NOT NULL DEFAULT 'service', is_group TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'D2', source ENUM('browse','search','joshen_finder','package','banner','reorder') NOT NULL DEFAULT 'browse', finder_search_id BIGINT UNSIGNED, status ENUM('pending_provider','awaiting_payment','confirmed','preparing','on_the_way', 'arrived','in_progress','completed','rejected','expired','cancelled') NOT NULL, -- where & when (address snapshot) address_id BIGINT UNSIGNED, city_id BIGINT UNSIGNED, district_id BIGINT UNSIGNED, address_text VARCHAR(500), lat DECIMAL(9,6), lng DECIMAL(9,6), provider_lat DECIMAL(9,6) COMMENT 'provider self-travel position (TD-183)', provider_lng DECIMAL(9,6), provider_location_updated_at DATETIME(3), scheduled_at DATETIME(3) COMMENT 'scheduled bookings', eta_minutes SMALLINT COMMENT 'instant: الوصول خلال ٤٥ د', estimated_duration_min INT, notes_to_provider VARCHAR(1000) COMMENT 'ملاحظات للأخصائية', -- money snapshot at checkout services_amount DECIMAL(12,2) NOT NULL DEFAULT 0 COMMENT 'sub-services incl. options', package_amount DECIMAL(12,2) NOT NULL DEFAULT 0 COMMENT 'package bought in this checkout', delivery_fee DECIMAL(12,2) NOT NULL DEFAULT 0 COMMENT 'one fee per order (incl. group)', discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0, coupon_id BIGINT UNSIGNED, vat_amount DECIMAL(12,2) NOT NULL DEFAULT 0, total_amount DECIMAL(12,2) NOT NULL DEFAULT 0, provider_commission_rate DECIMAL(5,2) NOT NULL COMMENT 'e.g. 20.00 (snapshot)', payment_status ENUM('unpaid','pending','paid','failed','refunded','partially_refunded') NOT NULL DEFAULT 'unpaid', -- provider acceptance (scheduled always; instant per D3) provider_response_due_at DATETIME(3), provider_responded_at DATETIME(3), rejection_reason VARCHAR(500), payment_due_at DATETIME(3) COMMENT 'scheduled: time to pay after acceptance', -- transport (تحديد طريقة الوصول) transport_mode ENUM('self','joshen_driver'), transport_chosen_at DATETIME(3), -- lifecycle confirmed_at DATETIME(3), departed_at DATETIME(3) COMMENT 'execution stage starts', arrived_at DATETIME(3), started_at DATETIME(3), completed_at DATETIME(3), cancelled_at DATETIME(3), cancelled_by ENUM('customer','provider','admin','system'), cancellation_reason VARCHAR(500), cancellation_rule_id BIGINT UNSIGNED, cancellation_fee DECIMAL(12,2) NOT NULL DEFAULT 0, delay_alerted_at DATETIME(3) COMMENT 'إشعار التأخير shown', 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), UNIQUE KEY ux_orders_code (code), KEY ix_orders_customer (customer_id, created_at), KEY ix_orders_provider (provider_id, status, scheduled_at), KEY ix_orders_status (status, created_at), KEY ix_orders_pending_response (status, provider_response_due_at), CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(user_id), CONSTRAINT fk_orders_provider FOREIGN KEY (provider_id) REFERENCES providers(user_id), CONSTRAINT fk_orders_finder FOREIGN KEY (finder_search_id) REFERENCES finder_searches(id), CONSTRAINT fk_orders_address FOREIGN KEY (address_id) REFERENCES customer_addresses(id), CONSTRAINT fk_orders_city FOREIGN KEY (city_id) REFERENCES governorates(id), CONSTRAINT fk_orders_district FOREIGN KEY (district_id) REFERENCES regions(id), CONSTRAINT fk_orders_coupon FOREIGN KEY (coupon_id) REFERENCES coupons(id), CONSTRAINT fk_orders_cancel_rule FOREIGN KEY (cancellation_rule_id) REFERENCES cancellation_rules(id), CONSTRAINT ck_orders_visit_location CHECK (order_kind = 'package_purchase' OR (city_id IS NOT NULL AND address_text IS NOT NULL)), CONSTRAINT ck_orders_schedule CHECK (booking_type = 'instant' OR scheduled_at IS NOT NULL OR order_kind = 'package_purchase'), CONSTRAINT ck_orders_total CHECK (total_amount = services_amount + package_amount + delivery_fee + vat_amount - discount_amount), CONSTRAINT ck_orders_cancelled CHECK (status <> 'cancelled' OR cancelled_at IS NOT NULL) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ALTER TABLE customer_packages ADD CONSTRAINT fk_cpkg_order FOREIGN KEY (purchase_order_id) REFERENCES orders(id); -- People in an order. Normal order: one row (the customer). Group order: several (الأشخاص والخدمات). CREATE TABLE order_people ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', order_id BIGINT UNSIGNED NOT NULL, label VARCHAR(100) NOT NULL COMMENT '"أنا" / "الشخص ٢" / a name', is_customer TINYINT(1) NOT NULL DEFAULT 0, sort_order SMALLINT NOT NULL DEFAULT 1, KEY ix_order_people_order (order_id), CONSTRAINT fk_op_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE order_items ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', order_id BIGINT UNSIGNED NOT NULL, order_person_id BIGINT UNSIGNED, item_type ENUM('service','package_purchase','package_session') NOT NULL, service_id BIGINT UNSIGNED, provider_service_id BIGINT UNSIGNED, package_id BIGINT UNSIGNED, customer_package_item_id BIGINT UNSIGNED, name_snapshot VARCHAR(150) NOT NULL COMMENT 'ar name at checkout (TD-12)', duration_min INT, unit_price DECIMAL(12,2) NOT NULL DEFAULT 0 COMMENT '0 for redeemed package sessions', options_price DECIMAL(12,2) NOT NULL DEFAULT 0, quantity SMALLINT NOT NULL DEFAULT 1, line_total DECIMAL(12,2) NOT NULL, KEY ix_order_items_order (order_id), CONSTRAINT fk_oi_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE, CONSTRAINT fk_oi_person FOREIGN KEY (order_person_id) REFERENCES order_people(id) ON DELETE CASCADE, CONSTRAINT fk_oi_service FOREIGN KEY (service_id) REFERENCES services(id), CONSTRAINT fk_oi_ps FOREIGN KEY (provider_service_id) REFERENCES provider_services(id), CONSTRAINT fk_oi_package FOREIGN KEY (package_id) REFERENCES packages(id), CONSTRAINT fk_oi_cpi FOREIGN KEY (customer_package_item_id) REFERENCES customer_package_items(id), CONSTRAINT ck_oi_qty CHECK (quantity > 0), CONSTRAINT ck_oi_total CHECK (line_total = (unit_price + options_price) * quantity), CONSTRAINT ck_oi_refs CHECK ( (item_type = 'service' AND service_id IS NOT NULL AND provider_service_id IS NOT NULL) OR (item_type = 'package_purchase' AND package_id IS NOT NULL) OR (item_type = 'package_session' AND customer_package_item_id IS NOT NULL AND service_id IS NOT NULL)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Addons picked with an item (TD-17). order_items.options_price = SUM(price) of these rows; -- each price is the provider's provider_services.price copied at checkout. CREATE TABLE order_item_options ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', order_item_id BIGINT UNSIGNED NOT NULL, service_id BIGINT UNSIGNED NOT NULL COMMENT 'the addon (type 3)', provider_service_id BIGINT UNSIGNED NOT NULL COMMENT 'her price row for the addon', name_snapshot VARCHAR(150) NOT NULL COMMENT 'ar snapshot رموش كثيفة (TD-12)', price DECIMAL(12,2) NOT NULL DEFAULT 0, KEY ix_oio_item (order_item_id), CONSTRAINT fk_oio_item FOREIGN KEY (order_item_id) REFERENCES order_items(id) ON DELETE CASCADE, CONSTRAINT fk_oio_service FOREIGN KEY (service_id) REFERENCES services(id), CONSTRAINT fk_oio_ps FOREIGN KEY (provider_service_id) REFERENCES provider_services(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE order_status_history ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', order_id BIGINT UNSIGNED NOT NULL, from_status VARCHAR(30), to_status VARCHAR(30) NOT NULL, actor_type ENUM('customer','provider','driver','admin','system') NOT NULL, actor_id BIGINT UNSIGNED, note VARCHAR(500), created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), KEY ix_osh_order (order_id, created_at), CONSTRAINT fk_osh_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Replay protection for order creation (TD-95). Internal bookkeeping: no uuid, never addressable (TD-22). -- One row per (user, endpoint scope, Idempotency-Key); the same key with a different body is refused. CREATE TABLE idempotency_keys ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, scope VARCHAR(40) NOT NULL COMMENT 'endpoint family, e.g. orders.store', idempotency_key VARCHAR(100) NOT NULL, request_hash CHAR(64) NOT NULL COMMENT 'sha256 of the canonical request body', order_id BIGINT UNSIGNED, refund_id BIGINT UNSIGNED COMMENT 'admin refund created under this idempotency key (TD-156)', created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), UNIQUE KEY ux_idem_user_scope_key (user_id, scope, idempotency_key), KEY ix_idem_order (order_id), KEY ix_idem_refund (refund_id), KEY ix_idem_created (created_at), CONSTRAINT fk_idem_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, CONSTRAINT fk_idem_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE coupon_redemptions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', coupon_id BIGINT UNSIGNED NOT NULL, customer_id BIGINT UNSIGNED NOT NULL, order_id BIGINT UNSIGNED NOT NULL, discount_amount DECIMAL(12,2) NOT NULL, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), UNIQUE KEY ux_cr_order (order_id), KEY ix_cr_coupon_customer (coupon_id, customer_id), CONSTRAINT fk_cr_coupon FOREIGN KEY (coupon_id) REFERENCES coupons(id), CONSTRAINT fk_cr_customer FOREIGN KEY (customer_id) REFERENCES customers(user_id), CONSTRAINT fk_cr_order FOREIGN KEY (order_id) REFERENCES orders(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ===================================================================== -- 13. DELIVERY TRIPS (طلب مندوبة) — only when provider picks a Joshen driver -- ===================================================================== CREATE TABLE trips ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', code VARCHAR(20) NOT NULL COMMENT 'TRP-9921, generated by the app', order_id BIGINT UNSIGNED NOT NULL, driver_id BIGINT UNSIGNED COMMENT 'set when a driver accepts', vehicle_id BIGINT UNSIGNED, status ENUM('searching','accepted','to_provider','at_provider','picked_up', 'to_customer','arrived','completed','no_driver','cancelled') NOT NULL DEFAULT 'searching', pickup_address VARCHAR(500) NOT NULL COMMENT 'provider location', pickup_lat DECIMAL(9,6), pickup_lng DECIMAL(9,6), dropoff_address VARCHAR(500) NOT NULL COMMENT 'customer address', dropoff_lat DECIMAL(9,6), dropoff_lng DECIMAL(9,6), distance_km DECIMAL(6,2), est_duration_min SMALLINT, delivery_fee DECIMAL(12,2) NOT NULL COMMENT '= orders.delivery_fee', search_started_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), search_expires_at DATETIME(3) NOT NULL COMMENT 'setting: driver search duration', accepted_at DATETIME(3), started_at DATETIME(3) COMMENT 'driver heads to provider = execution stage', at_provider_at DATETIME(3), picked_up_at DATETIME(3), arrived_at DATETIME(3), arrival_confirmed_by ENUM('driver','provider','customer'), completed_at DATETIME(3), cancelled_at DATETIME(3), cancel_reason VARCHAR(500), 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), live_order_key BIGINT UNSIGNED AS (IF(status IN ('no_driver','cancelled'), NULL, order_id)) STORED COMMENT 'one live trip per order; retries allowed', UNIQUE KEY ux_trips_code (code), UNIQUE KEY ux_trip_live_per_order (live_order_key), KEY ix_trips_order (order_id), KEY ix_trips_driver (driver_id, created_at), CONSTRAINT fk_trips_order FOREIGN KEY (order_id) REFERENCES orders(id), CONSTRAINT fk_trips_driver FOREIGN KEY (driver_id) REFERENCES drivers(user_id), CONSTRAINT fk_trips_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id), CONSTRAINT ck_trips_driver CHECK (status IN ('searching','no_driver','cancelled') OR driver_id IS NOT NULL) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Broadcast to nearby online drivers; first accept wins (BRD) CREATE TABLE trip_offers ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', trip_id BIGINT UNSIGNED NOT NULL, driver_id BIGINT UNSIGNED NOT NULL, offered_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), expires_at DATETIME(3) NOT NULL, response ENUM('pending','accepted','rejected','expired','withdrawn') NOT NULL DEFAULT 'pending', responded_at DATETIME(3), accepted_key BIGINT UNSIGNED AS (IF(response = 'accepted', trip_id, NULL)) STORED, UNIQUE KEY ux_trip_offer (trip_id, driver_id), UNIQUE KEY ux_trip_one_accept (accepted_key), CONSTRAINT fk_to_trip FOREIGN KEY (trip_id) REFERENCES trips(id), CONSTRAINT fk_to_driver FOREIGN KEY (driver_id) REFERENCES drivers(user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE trip_status_history ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', trip_id BIGINT UNSIGNED NOT NULL, from_status VARCHAR(30), to_status VARCHAR(30) NOT NULL, actor_type ENUM('driver','provider','customer','admin','system') NOT NULL, actor_id BIGINT UNSIGNED, lat DECIMAL(9,6), lng DECIMAL(9,6), created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), KEY ix_tsh_trip (trip_id, created_at), CONSTRAINT fk_tsh_trip FOREIGN KEY (trip_id) REFERENCES trips(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ===================================================================== -- 14. PAYMENTS, INVOICES, REFUNDS, WALLET -- ===================================================================== CREATE TABLE saved_cards ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', user_id BIGINT UNSIGNED NOT NULL, gateway VARCHAR(30) NOT NULL COMMENT 'moyasar / hyperpay / tap …', gateway_token VARCHAR(255) NOT NULL COMMENT 'never store the card number', brand ENUM('mada','visa','mastercard','amex') NOT NULL, last4 CHAR(4) NOT NULL, exp_month TINYINT, exp_year SMALLINT, is_default TINYINT(1) NOT NULL DEFAULT 0, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), deleted_at DATETIME(3), UNIQUE KEY ux_cards_token (gateway, gateway_token), KEY ix_cards_user (user_id), CONSTRAINT fk_cards_user FOREIGN KEY (user_id) REFERENCES users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- One payment attempt: for an order (incl. package purchase) or a Featured subscription CREATE TABLE payments ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', payer_user_id BIGINT UNSIGNED NOT NULL, purpose ENUM('order','featured_subscription') NOT NULL, order_id BIGINT UNSIGNED, subscription_id BIGINT UNSIGNED, method ENUM('card','mada','apple_pay','tabby','wallet','wallet_plus_gateway','earnings_deduction') NOT NULL, saved_card_id BIGINT UNSIGNED, amount DECIMAL(12,2) NOT NULL, wallet_amount DECIMAL(12,2) NOT NULL DEFAULT 0, gateway_amount DECIMAL(12,2) NOT NULL DEFAULT 0, gateway VARCHAR(30), gateway_reference VARCHAR(100), gateway_fee DECIMAL(12,2) COMMENT 'BRD: رسوم بوابة الدفع إن كانت مطبقة', status ENUM('initiated','authorized','paid','failed','voided','refunded','partially_refunded') NOT NULL DEFAULT 'initiated', failure_reason VARCHAR(500), paid_at DATETIME(3), 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), UNIQUE KEY ux_payments_gateway_ref (gateway, gateway_reference), KEY ix_payments_order (order_id), KEY ix_payments_status (status, created_at), CONSTRAINT fk_payments_user FOREIGN KEY (payer_user_id) REFERENCES users(id), CONSTRAINT fk_payments_order FOREIGN KEY (order_id) REFERENCES orders(id), CONSTRAINT fk_payments_sub FOREIGN KEY (subscription_id) REFERENCES provider_subscriptions(id), CONSTRAINT fk_payments_card FOREIGN KEY (saved_card_id) REFERENCES saved_cards(id), CONSTRAINT ck_payments_amount CHECK (amount >= 0), CONSTRAINT ck_payments_purpose CHECK ((purpose = 'order' AND order_id IS NOT NULL AND subscription_id IS NULL) OR (purpose = 'featured_subscription' AND subscription_id IS NOT NULL AND order_id IS NULL)), CONSTRAINT ck_payments_split CHECK (amount = wallet_amount + gateway_amount OR method = 'earnings_deduction') ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Media: pdf (single, generated by the app after payment) CREATE TABLE invoices ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', invoice_no VARCHAR(30) NOT NULL COMMENT 'INV-100000, generated by the app', payment_id BIGINT UNSIGNED NOT NULL, billed_user_id BIGINT UNSIGNED NOT NULL, order_id BIGINT UNSIGNED, subscription_id BIGINT UNSIGNED, subtotal DECIMAL(12,2) NOT NULL, delivery_fee DECIMAL(12,2) NOT NULL DEFAULT 0, discount DECIMAL(12,2) NOT NULL DEFAULT 0, vat_amount DECIMAL(12,2) NOT NULL DEFAULT 0, total DECIMAL(12,2) NOT NULL, zatca_uuid CHAR(36) COMMENT 'e-invoicing (Fatoora) if required', qr_payload TEXT, issued_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), UNIQUE KEY ux_invoices_no (invoice_no), UNIQUE KEY ux_invoices_payment (payment_id), CONSTRAINT fk_inv_payment FOREIGN KEY (payment_id) REFERENCES payments(id), CONSTRAINT fk_inv_user FOREIGN KEY (billed_user_id) REFERENCES users(id), CONSTRAINT fk_inv_order FOREIGN KEY (order_id) REFERENCES orders(id), CONSTRAINT fk_inv_sub FOREIGN KEY (subscription_id) REFERENCES provider_subscriptions(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE refunds ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', order_id BIGINT UNSIGNED NOT NULL, payment_id BIGINT UNSIGNED, amount DECIMAL(12,2) NOT NULL, destination ENUM('wallet','original_method') NOT NULL DEFAULT 'wallet', reason ENUM('customer_cancel','provider_cancel','provider_rejected','provider_timeout', 'admin','package_refund','other') NOT NULL, status ENUM('pending','completed','failed') NOT NULL DEFAULT 'pending', processed_by BIGINT UNSIGNED, processed_at DATETIME(3), created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), KEY ix_refunds_order (order_id), CONSTRAINT fk_refunds_order FOREIGN KEY (order_id) REFERENCES orders(id), CONSTRAINT fk_refunds_payment FOREIGN KEY (payment_id) REFERENCES payments(id), CONSTRAINT fk_refunds_admin FOREIGN KEY (processed_by) REFERENCES users(id), CONSTRAINT ck_refunds_amount CHECK (amount > 0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ALTER TABLE idempotency_keys ADD CONSTRAINT fk_idem_refund FOREIGN KEY (refund_id) REFERENCES refunds(id) ON DELETE CASCADE; -- Wallet: refunds only, spent on new orders (design: لا يمكن شحنها) CREATE TABLE wallets ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', user_id BIGINT UNSIGNED NOT NULL UNIQUE, balance DECIMAL(12,2) NOT NULL DEFAULT 0, updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), CONSTRAINT fk_wallets_customer FOREIGN KEY (user_id) REFERENCES customers(user_id), CONSTRAINT ck_wallets_balance CHECK (balance >= 0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE wallet_transactions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', wallet_user_id BIGINT UNSIGNED NOT NULL, txn_type ENUM('refund_credit','order_debit','order_debit_reversal','admin_adjustment') NOT NULL, amount DECIMAL(12,2) NOT NULL COMMENT '+ credit / - debit', balance_after DECIMAL(12,2) NOT NULL DEFAULT 0 COMMENT 'set by trigger', order_id BIGINT UNSIGNED, refund_id BIGINT UNSIGNED, payment_id BIGINT UNSIGNED, admin_id BIGINT UNSIGNED, note VARCHAR(500), created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), KEY ix_wtx_wallet (wallet_user_id, created_at), CONSTRAINT fk_wtx_wallet FOREIGN KEY (wallet_user_id) REFERENCES wallets(user_id), CONSTRAINT fk_wtx_order FOREIGN KEY (order_id) REFERENCES orders(id), CONSTRAINT fk_wtx_refund FOREIGN KEY (refund_id) REFERENCES refunds(id), CONSTRAINT fk_wtx_payment FOREIGN KEY (payment_id) REFERENCES payments(id), CONSTRAINT fk_wtx_admin FOREIGN KEY (admin_id) REFERENCES users(id), CONSTRAINT ck_wtx_amount CHECK (amount <> 0), CONSTRAINT ck_wtx_balance CHECK (balance_after >= 0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ===================================================================== -- 15. COMMISSIONS, EARNINGS, PAYOUTS -- ===================================================================== CREATE TABLE commission_rules ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', party ENUM('provider','driver') NOT NULL, applies_to ENUM('service','package','delivery_fee') NOT NULL, calc_type ENUM('percent','fixed') NOT NULL, value DECIMAL(12,2) NOT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, effective_from DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), created_by BIGINT UNSIGNED, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), CONSTRAINT fk_comm_admin FOREIGN KEY (created_by) REFERENCES users(id), CONSTRAINT ck_comm_value CHECK (value >= 0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Transfers are recorded by admin per payout cycle (BRD: no automatic transfer) CREATE TABLE payouts ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', beneficiary_id BIGINT UNSIGNED NOT NULL, beneficiary_role ENUM('provider','driver') NOT NULL, payout_account_id BIGINT UNSIGNED NOT NULL, period_start DATE, period_end DATE, amount DECIMAL(12,2) NOT NULL, status ENUM('requested','scheduled','processing','paid','failed','cancelled') NOT NULL DEFAULT 'scheduled', requested_at DATETIME(3) COMMENT 'design: تحويل الأرباح للحساب البنكي button', bank_reference VARCHAR(100), paid_at DATETIME(3), processed_by BIGINT UNSIGNED, note VARCHAR(500), created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), KEY ix_payouts_benef (beneficiary_id, status), CONSTRAINT fk_payouts_user FOREIGN KEY (beneficiary_id) REFERENCES users(id), CONSTRAINT fk_payouts_account FOREIGN KEY (payout_account_id) REFERENCES payout_accounts(id), CONSTRAINT fk_payouts_admin FOREIGN KEY (processed_by) REFERENCES users(id), CONSTRAINT ck_payouts_amount CHECK (amount > 0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Earnings ledger: one row per amount owed to a provider/driver. -- provider / order completed : gross = service value (or package session value), -- commission on gross only, + delivery fee if she went herself -- driver / trip completed : gross = delivery fee, minus driver commission if enabled -- also: cancellation fee share, Featured deduction (negative), adjustments CREATE TABLE earnings ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', beneficiary_id BIGINT UNSIGNED NOT NULL, beneficiary_role ENUM('provider','driver') NOT NULL, source_type ENUM('order','package_session','trip','cancellation_fee','subscription_deduction','adjustment') NOT NULL, order_id BIGINT UNSIGNED, trip_id BIGINT UNSIGNED, subscription_id BIGINT UNSIGNED, gross_amount DECIMAL(12,2) NOT NULL, commission_rate DECIMAL(5,2) NOT NULL DEFAULT 0, commission_amount DECIMAL(12,2) NOT NULL DEFAULT 0, delivery_fee_amount DECIMAL(12,2) NOT NULL DEFAULT 0 COMMENT 'provider self-delivery only', net_amount DECIMAL(12,2) NOT NULL, status ENUM('pending','available','in_payout','paid','reversed') NOT NULL DEFAULT 'pending', available_at DATETIME(3), payout_id BIGINT UNSIGNED, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), order_earning_key BIGINT UNSIGNED AS (IF(source_type IN ('order','package_session') AND status <> 'reversed', order_id, NULL)) STORED, trip_earning_key BIGINT UNSIGNED AS (IF(source_type = 'trip' AND status <> 'reversed', trip_id, NULL)) STORED, UNIQUE KEY ux_earning_order (order_earning_key), UNIQUE KEY ux_earning_trip (trip_earning_key), KEY ix_earnings_benef (beneficiary_id, beneficiary_role, status), CONSTRAINT fk_earn_user FOREIGN KEY (beneficiary_id) REFERENCES users(id), CONSTRAINT fk_earn_order FOREIGN KEY (order_id) REFERENCES orders(id), CONSTRAINT fk_earn_trip FOREIGN KEY (trip_id) REFERENCES trips(id), CONSTRAINT fk_earn_sub FOREIGN KEY (subscription_id) REFERENCES provider_subscriptions(id), CONSTRAINT fk_earn_payout FOREIGN KEY (payout_id) REFERENCES payouts(id), CONSTRAINT ck_earn_net CHECK (net_amount = gross_amount - commission_amount + delivery_fee_amount) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ===================================================================== -- 16. REVIEWS (two-way + provider→driver), with tags -- ===================================================================== CREATE TABLE review_tags ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', direction ENUM('customer_to_provider','provider_to_customer','provider_to_driver') NOT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, sort_order INT NOT NULL DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Label unique per direction + locale: checked by the application CREATE TABLE review_tag_translations ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, review_tag_id BIGINT UNSIGNED NOT NULL, locale VARCHAR(10) NOT NULL COMMENT 'ar (required) / en', label VARCHAR(100) NOT NULL COMMENT 'الالتزام بالوقت / نظافة الأدوات / وضوح العنوان …', UNIQUE KEY ux_rtag_tr (review_tag_id, locale), CONSTRAINT fk_rtag_tr_tag FOREIGN KEY (review_tag_id) REFERENCES review_tags(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE reviews ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', order_id BIGINT UNSIGNED NOT NULL, trip_id BIGINT UNSIGNED, direction ENUM('customer_to_provider','provider_to_customer','provider_to_driver') NOT NULL, reviewer_id BIGINT UNSIGNED NOT NULL, reviewee_id BIGINT UNSIGNED NOT NULL, stars TINYINT NOT NULL, comment VARCHAR(1000) COMMENT 'public (subject to moderation)', private_note VARCHAR(1000) COMMENT 'ملاحظات تُعرض لفريق جوشن فقط', is_visible TINYINT(1) NOT NULL DEFAULT 1, moderated_by BIGINT UNSIGNED, moderated_at DATETIME(3), created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), deleted_at DATETIME(3), UNIQUE KEY ux_reviews_order_dir (order_id, direction), KEY ix_reviews_reviewee (reviewee_id, direction, created_at), CONSTRAINT fk_reviews_order FOREIGN KEY (order_id) REFERENCES orders(id), CONSTRAINT fk_reviews_trip FOREIGN KEY (trip_id) REFERENCES trips(id), CONSTRAINT fk_reviews_reviewer FOREIGN KEY (reviewer_id) REFERENCES users(id), CONSTRAINT fk_reviews_reviewee FOREIGN KEY (reviewee_id) REFERENCES users(id), CONSTRAINT fk_reviews_admin FOREIGN KEY (moderated_by) REFERENCES users(id), CONSTRAINT ck_reviews_stars CHECK (stars BETWEEN 1 AND 5) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE review_tag_links ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, review_id BIGINT UNSIGNED NOT NULL, tag_id BIGINT UNSIGNED NOT NULL, UNIQUE KEY ux_review_tag_links (review_id, tag_id), CONSTRAINT fk_rtl_review FOREIGN KEY (review_id) REFERENCES reviews(id) ON DELETE CASCADE, CONSTRAINT fk_rtl_tag FOREIGN KEY (tag_id) REFERENCES review_tags(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ===================================================================== -- 17. ORDER CHAT (opens after confirmation, closes after completion) -- ===================================================================== CREATE TABLE conversations ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', order_id BIGINT UNSIGNED NOT NULL, status ENUM('open','closed') NOT NULL DEFAULT 'open', opened_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), closed_at DATETIME(3), UNIQUE KEY ux_conversations_order (order_id), CONSTRAINT fk_conv_order FOREIGN KEY (order_id) REFERENCES orders(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Media: attachment (single, required when message_type = image) CREATE TABLE messages ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', conversation_id BIGINT UNSIGNED NOT NULL, sender_id BIGINT UNSIGNED COMMENT 'NULL for system messages', sender_role ENUM('customer','provider','system') NOT NULL, message_type ENUM('text','image','location','system') NOT NULL DEFAULT 'text', body TEXT, read_at DATETIME(3), created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), KEY ix_messages_conv (conversation_id, created_at), CONSTRAINT fk_msg_conv FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE, CONSTRAINT fk_msg_sender FOREIGN KEY (sender_id) REFERENCES users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ===================================================================== -- 18. NOTIFICATIONS -- ===================================================================== CREATE TABLE notification_templates ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', event_key VARCHAR(80) NOT NULL COMMENT 'order.confirmed, trip.no_driver, featured.expiring …', audience ENUM('customer','provider','driver','admin') NOT NULL COMMENT 'admin = dashboard alerts (TD-173)', send_in_app TINYINT(1) NOT NULL DEFAULT 1, send_push TINYINT(1) NOT NULL DEFAULT 1, send_sms TINYINT(1) NOT NULL DEFAULT 0, send_whatsapp TINYINT(1) NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, updated_by BIGINT UNSIGNED, updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), UNIQUE KEY ux_ntpl_event (event_key, audience), CONSTRAINT fk_ntpl_admin FOREIGN KEY (updated_by) REFERENCES users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Rendered in the recipient's locale (falling back to ar) into notifications.title/body CREATE TABLE notification_template_translations ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, notification_template_id BIGINT UNSIGNED NOT NULL, locale VARCHAR(10) NOT NULL COMMENT 'ar (required) / en', title VARCHAR(200) NOT NULL, body TEXT NOT NULL COMMENT 'with {{placeholders}}', UNIQUE KEY ux_ntpl_tr (notification_template_id, locale), CONSTRAINT fk_ntpl_tr_template FOREIGN KEY (notification_template_id) REFERENCES notification_templates(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE notifications ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', user_id BIGINT UNSIGNED NOT NULL, app ENUM('customer','provider','driver','admin') NOT NULL COMMENT 'admin = dashboard inbox (TD-173)', template_id BIGINT UNSIGNED, event_key VARCHAR(80) NOT NULL, title VARCHAR(200) NOT NULL, body TEXT NOT NULL, data JSON COMMENT '{"order_id":..,"trip_id":..,"deeplink":..}', order_id BIGINT UNSIGNED, read_at DATETIME(3), created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), KEY ix_notif_user (user_id, app, created_at), KEY ix_notif_unread (user_id, app, read_at), CONSTRAINT fk_notif_user FOREIGN KEY (user_id) REFERENCES users(id), CONSTRAINT fk_notif_tpl FOREIGN KEY (template_id) REFERENCES notification_templates(id), CONSTRAINT fk_notif_order FOREIGN KEY (order_id) REFERENCES orders(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE notification_deliveries ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', notification_id BIGINT UNSIGNED NOT NULL, channel ENUM('in_app','push','sms','whatsapp') NOT NULL, status ENUM('queued','sent','delivered','failed') NOT NULL DEFAULT 'queued', provider_ref VARCHAR(100), error VARCHAR(500), sent_at DATETIME(3), CONSTRAINT fk_nd_notif FOREIGN KEY (notification_id) REFERENCES notifications(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Admin general notifications to customers / providers / drivers (ADMIN-020, TD-173). -- One row per send; recipients fixed when the admin sends, one row per user and app. CREATE TABLE notification_broadcasts ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', created_by BIGINT UNSIGNED NOT NULL COMMENT 'admin users.id', target ENUM('all','cities','users') NOT NULL, apps JSON NOT NULL COMMENT '["customer","provider","driver"] subset', city_ids JSON COMMENT 'governorates.id list when target = cities', status ENUM('queued','sending','sent','failed') NOT NULL DEFAULT 'queued', recipients_count INT UNSIGNED NOT NULL DEFAULT 0, sent_count INT UNSIGNED NOT NULL DEFAULT 0, failure_reason VARCHAR(500), sent_at DATETIME(3) COMMENT 'every recipient notified', 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), KEY ix_nbc_status (status, created_at), CONSTRAINT fk_nbc_admin FOREIGN KEY (created_by) REFERENCES users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE notification_broadcast_translations ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, notification_broadcast_id BIGINT UNSIGNED NOT NULL, locale VARCHAR(10) NOT NULL COMMENT 'ar (required) / en', title VARCHAR(200) NOT NULL, body TEXT NOT NULL, UNIQUE KEY ux_nbc_tr (notification_broadcast_id, locale), CONSTRAINT fk_nbc_tr_broadcast FOREIGN KEY (notification_broadcast_id) REFERENCES notification_broadcasts(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE notification_broadcast_recipients ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, notification_broadcast_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL, app ENUM('customer','provider','driver') NOT NULL, notification_id BIGINT UNSIGNED COMMENT 'inbox row written for this recipient', notified_at DATETIME(3), UNIQUE KEY ux_nbc_rcpt (notification_broadcast_id, user_id, app), KEY ix_nbc_rcpt_pending (notification_broadcast_id, notified_at), CONSTRAINT fk_nbc_rcpt_broadcast FOREIGN KEY (notification_broadcast_id) REFERENCES notification_broadcasts(id) ON DELETE CASCADE, CONSTRAINT fk_nbc_rcpt_user FOREIGN KEY (user_id) REFERENCES users(id), CONSTRAINT fk_nbc_rcpt_notif FOREIGN KEY (notification_id) REFERENCES notifications(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ===================================================================== -- 19. SUPPORT -- ===================================================================== CREATE TABLE support_reasons ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', audience ENUM('customer','provider','driver') NOT NULL, is_urgent TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'الإبلاغ عن حادثة أو مخالفة — عاجل', is_active TINYINT(1) NOT NULL DEFAULT 1, sort_order INT NOT NULL DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Label unique per audience + locale: checked by the application CREATE TABLE support_reason_translations ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, support_reason_id BIGINT UNSIGNED NOT NULL, locale VARCHAR(10) NOT NULL COMMENT 'ar (required) / en', label VARCHAR(150) NOT NULL, UNIQUE KEY ux_sreason_tr (support_reason_id, locale), CONSTRAINT fk_sreason_tr_reason FOREIGN KEY (support_reason_id) REFERENCES support_reasons(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Media: attachments (multiple, files sent when opening the ticket) CREATE TABLE support_tickets ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', code VARCHAR(20) NOT NULL COMMENT 'SUP-1000, generated by the app', user_id BIGINT UNSIGNED NOT NULL, app ENUM('customer','provider','driver') NOT NULL, reason_id BIGINT UNSIGNED NOT NULL, body TEXT NOT NULL, order_id BIGINT UNSIGNED, trip_id BIGINT UNSIGNED, change_request_id BIGINT UNSIGNED COMMENT 'طلب تحديث مستند عبر الدعم', priority ENUM('normal','urgent') NOT NULL DEFAULT 'normal', status ENUM('under_review','replied','closed') NOT NULL DEFAULT 'under_review', assigned_admin_id BIGINT UNSIGNED, 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), closed_at DATETIME(3), UNIQUE KEY ux_tickets_code (code), KEY ix_tickets_status (status, priority, created_at), CONSTRAINT fk_tickets_user FOREIGN KEY (user_id) REFERENCES users(id), CONSTRAINT fk_tickets_reason FOREIGN KEY (reason_id) REFERENCES support_reasons(id), CONSTRAINT fk_tickets_order FOREIGN KEY (order_id) REFERENCES orders(id), CONSTRAINT fk_tickets_trip FOREIGN KEY (trip_id) REFERENCES trips(id), CONSTRAINT fk_tickets_pcr FOREIGN KEY (change_request_id) REFERENCES profile_change_requests(id), CONSTRAINT fk_tickets_admin FOREIGN KEY (assigned_admin_id) REFERENCES users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Media: attachments (multiple, files sent with a reply) CREATE TABLE support_messages ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', ticket_id BIGINT UNSIGNED NOT NULL, sender_type ENUM('user','admin') NOT NULL, sender_id BIGINT UNSIGNED NOT NULL COMMENT 'users.id (app user or admin, per sender_type)', body TEXT NOT NULL, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), KEY ix_sm_ticket (ticket_id, created_at), CONSTRAINT fk_sm_ticket FOREIGN KEY (ticket_id) REFERENCES support_tickets(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE faqs ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', audience ENUM('customer','provider','driver') NOT NULL, sort_order INT NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE faq_translations ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, faq_id BIGINT UNSIGNED NOT NULL, locale VARCHAR(10) NOT NULL COMMENT 'ar (required) / en', question VARCHAR(300) NOT NULL, answer TEXT NOT NULL, UNIQUE KEY ux_faq_tr (faq_id, locale), CONSTRAINT fk_faq_tr_faq FOREIGN KEY (faq_id) REFERENCES faqs(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ===================================================================== -- 20. CONTENT: banners (sliders), onboarding, static pages -- ===================================================================== -- BRD "banner" (ADMIN-023, BR-30) = base App\Models\Slider / sliders (TD-15). -- Target shape: the base sliders migration (title, subtitle, link, -- sort_order, is_active, timestamps) grows into it in the banners feature — -- title / subtitle move to slider_translations, link keeps its name, the -- targeting and display-date columns are added. -- Media: image (single, required) — already on the Slider model. CREATE TABLE sliders ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', target_app ENUM('customer','provider','driver') NOT NULL, placement ENUM('home','services','packages','joshen_finder','account') NOT NULL, link_type ENUM('none','category','service','package','provider','screen','url') NOT NULL DEFAULT 'none', link VARCHAR(255) COMMENT 'id, route or URL, per link_type', sort_order INT NOT NULL DEFAULT 0, starts_at DATETIME(3), ends_at DATETIME(3), is_active TINYINT(1) NOT NULL DEFAULT 1, created_by BIGINT UNSIGNED, 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), KEY ix_sliders_live (target_app, placement, is_active, sort_order), CONSTRAINT fk_sliders_admin FOREIGN KEY (created_by) REFERENCES users(id), CONSTRAINT ck_sliders_dates CHECK (ends_at IS NULL OR starts_at IS NULL OR ends_at > starts_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE slider_translations ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, slider_id BIGINT UNSIGNED NOT NULL, locale VARCHAR(10) NOT NULL COMMENT 'ar (required) / en', title VARCHAR(200) NOT NULL, subtitle VARCHAR(300), badge_text VARCHAR(60) COMMENT 'عرض الأسبوع', UNIQUE KEY ux_slider_tr (slider_id, locale), CONSTRAINT fk_slider_tr_slider FOREIGN KEY (slider_id) REFERENCES sliders(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Media: image (single) CREATE TABLE onboarding_slides ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', app ENUM('customer','provider','driver') NOT NULL, sort_order INT NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE onboarding_slide_translations ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, onboarding_slide_id BIGINT UNSIGNED NOT NULL, locale VARCHAR(10) NOT NULL COMMENT 'ar (required) / en', title VARCHAR(200) NOT NULL, body VARCHAR(500) NOT NULL, UNIQUE KEY ux_oslide_tr (onboarding_slide_id, locale), CONSTRAINT fk_oslide_tr_slide FOREIGN KEY (onboarding_slide_id) REFERENCES onboarding_slides(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE static_pages ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE COMMENT 'public id; the only id clients see (TD-19)', slug ENUM('terms','privacy','cancellation_policy','about','female_declaration') NOT NULL, audience ENUM('all','customer','provider','driver') NOT NULL DEFAULT 'all', version INT NOT NULL DEFAULT 1 COMMENT 'bump when any locale changes', updated_by BIGINT UNSIGNED, updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), UNIQUE KEY ux_static_pages (slug, audience), CONSTRAINT fk_sp_admin FOREIGN KEY (updated_by) REFERENCES users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE static_page_translations ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, static_page_id BIGINT UNSIGNED NOT NULL, locale VARCHAR(10) NOT NULL COMMENT 'ar (required) / en', title VARCHAR(200) NOT NULL, body MEDIUMTEXT NOT NULL, UNIQUE KEY ux_spage_tr (static_page_id, locale), CONSTRAINT fk_spage_tr_page FOREIGN KEY (static_page_id) REFERENCES static_pages(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ===================================================================== -- 21. PLATFORM SETTINGS (key/value; edited from admin) -- ===================================================================== CREATE TABLE platform_settings ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE DEFAULT (UUID()) COMMENT 'public id; the only id clients see (TD-19)', setting_key VARCHAR(100) NOT NULL UNIQUE, setting_value JSON NOT NULL, description VARCHAR(500), updated_by BIGINT UNSIGNED, updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), CONSTRAINT fk_ps_admin FOREIGN KEY (updated_by) REFERENCES users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ===================================================================== -- 22. TRIGGERS (business rules the database enforces) -- Set @actor_type / @actor_id in the session before updating an order -- so the status history records who did it. -- ===================================================================== DELIMITER $$ -- Provider prices must sit inside the admin range (BRD: لا يمكن التسعير خارج الرينجات) CREATE TRIGGER trg_ps_price_ins BEFORE INSERT ON provider_services FOR EACH ROW BEGIN DECLARE lo DECIMAL(12,2); DECLARE hi DECIMAL(12,2); DECLARE node_type TINYINT; SELECT min_price, max_price, type INTO lo, hi, node_type FROM services WHERE id = NEW.service_id; IF node_type = 2 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Option groups cannot have provider prices'; END IF; IF NEW.price < lo OR NEW.price > hi THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Price is outside the allowed range for this service'; END IF; END$$ CREATE TRIGGER trg_ps_price_upd BEFORE UPDATE ON provider_services FOR EACH ROW BEGIN DECLARE lo DECIMAL(12,2); DECLARE hi DECIMAL(12,2); DECLARE node_type TINYINT; SELECT min_price, max_price, type INTO lo, hi, node_type FROM services WHERE id = NEW.service_id; IF node_type = 2 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Option groups cannot have provider prices'; END IF; IF NEW.price < lo OR NEW.price > hi THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Price is outside the allowed range for this service'; END IF; END$$ -- Tree shape (TD-17): the parent is exactly one type up and in the same category CREATE TRIGGER trg_services_parent_ins BEFORE INSERT ON services FOR EACH ROW BEGIN DECLARE p_type TINYINT; DECLARE p_cat BIGINT UNSIGNED; IF NEW.parent_id IS NOT NULL THEN SELECT type, category_id INTO p_type, p_cat FROM services WHERE id = NEW.parent_id; IF p_type <> NEW.type - 1 OR p_cat <> NEW.category_id THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Parent must be one level up in the same category'; END IF; END IF; END$$ CREATE TRIGGER trg_services_parent_upd BEFORE UPDATE ON services FOR EACH ROW BEGIN DECLARE p_type TINYINT; DECLARE p_cat BIGINT UNSIGNED; IF NEW.parent_id IS NOT NULL THEN SELECT type, category_id INTO p_type, p_cat FROM services WHERE id = NEW.parent_id; IF p_type <> NEW.type - 1 OR p_cat <> NEW.category_id THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Parent must be one level up in the same category'; END IF; END IF; END$$ -- Order status history CREATE TRIGGER trg_orders_status_ins AFTER INSERT ON orders FOR EACH ROW BEGIN INSERT INTO order_status_history (order_id, from_status, to_status, actor_type, actor_id) VALUES (NEW.id, NULL, NEW.status, COALESCE(@actor_type, 'system'), @actor_id); END$$ CREATE TRIGGER trg_orders_status_upd AFTER UPDATE ON orders FOR EACH ROW BEGIN IF NOT (NEW.status <=> OLD.status) THEN INSERT INTO order_status_history (order_id, from_status, to_status, actor_type, actor_id) VALUES (NEW.id, OLD.status, NEW.status, COALESCE(@actor_type, 'system'), @actor_id); END IF; END$$ -- Wallet balance follows the ledger; a debit larger than the balance fails CREATE TRIGGER trg_wallet_txn BEFORE INSERT ON wallet_transactions FOR EACH ROW BEGIN DECLARE bal DECIMAL(12,2); SELECT balance INTO bal FROM wallets WHERE user_id = NEW.wallet_user_id FOR UPDATE; IF bal IS NULL THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Wallet not found'; END IF; IF bal + NEW.amount < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient wallet balance'; END IF; UPDATE wallets SET balance = bal + NEW.amount WHERE user_id = NEW.wallet_user_id; SET NEW.balance_after = bal + NEW.amount; END$$ -- Only completed orders can be reviewed (BRD) CREATE TRIGGER trg_review_completed BEFORE INSERT ON reviews FOR EACH ROW BEGIN IF (SELECT status FROM orders WHERE id = NEW.order_id) <> 'completed' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Only completed orders can be reviewed'; END IF; END$$ DELIMITER ; -- ===================================================================== -- 23. VIEWS -- ===================================================================== -- Providers currently featured (ranking boost only if account approved + subscription active) CREATE VIEW v_provider_featured AS SELECT p.user_id AS provider_id, s.id AS subscription_id, fp.priority_level, fp.grants_verified_badge, fp.grants_featured_badge, s.ends_at FROM providers p JOIN provider_subscriptions s ON s.provider_id = p.user_id AND s.status = 'active' AND s.ends_at > UTC_TIMESTAMP(3) JOIN featured_plans fp ON fp.id = s.plan_id WHERE p.account_status = 'approved'; -- Bookable offers (TD-42): active type-1 service/category, approved provider/category, -- and active provider prices for the base service and every active choice in active groups. CREATE VIEW v_bookable_provider_services AS SELECT ps.id AS provider_service_id, ps.provider_id, ps.service_id, s.category_id, ps.price, COALESCE(ps.duration_min, s.default_duration_min) AS duration_min FROM provider_services ps JOIN services s ON s.id = ps.service_id AND s.type = 1 AND s.is_active = 1 JOIN categories c ON c.id = s.category_id AND c.is_active = 1 JOIN providers p ON p.user_id = ps.provider_id AND p.account_status = 'approved' JOIN provider_categories pc ON pc.provider_id = ps.provider_id AND pc.category_id = s.category_id AND pc.status = 'approved' AND pc.is_enabled = 1 WHERE ps.is_active = 1 AND NOT EXISTS ( SELECT 1 FROM services g JOIN services a ON a.parent_id = g.id AND a.type = 3 AND a.is_active = 1 WHERE g.parent_id = s.id AND g.type = 2 AND g.is_active = 1 AND NOT EXISTS (SELECT 1 FROM provider_services aps WHERE aps.provider_id = ps.provider_id AND aps.service_id = a.id AND aps.is_active = 1)); -- Selectable choices inherit the active option group and same-provider bookable service. CREATE VIEW v_selectable_provider_addons AS SELECT aps.id AS provider_service_id, aps.provider_id, a.id AS service_id, g.id AS option_group_id, g.parent_id AS bookable_service_id, a.category_id, aps.price, COALESCE(aps.duration_min, a.default_duration_min) AS duration_min FROM provider_services aps JOIN services a ON a.id = aps.service_id AND a.type = 3 AND a.is_active = 1 JOIN services g ON g.id = a.parent_id AND g.type = 2 AND g.is_active = 1 JOIN v_bookable_provider_services b ON b.provider_id = aps.provider_id AND b.service_id = g.parent_id WHERE aps.is_active = 1; -- Packages customers can see CREATE VIEW v_live_packages AS SELECT pk.* FROM packages pk JOIN providers p ON p.user_id = pk.provider_id AND p.account_status = 'approved' WHERE pk.status = 'approved' AND pk.is_visible = 1 AND pk.hidden_by_admin = 0;