مخطط قاعدة البيانات

نظرة عامة

تستخدم منصة شام باص قاعدة بيانات PostgreSQL عبر Supabase مع Row Level Security (RLS) لضمان عزل البيانات. يتغير عدد الجداول والكيانات مع الترحيلات، لذلك يجب اعتبار هذا المستند خريطة مفاهيمية وعملية، بينما يبقى مجلد الترحيلات هو المرجع النهائي للأسماء والبنية الدقيقة.

ملاحظات توافق العقود

تحافظ الترحيلات الحديثة على أعمدة توافقية بجانب الحقول الأساسية حتى لا تنكسر واجهات PostgREST أو تطبيقات الموبايل أثناء تطور المخطط. أمثلة مهمة:

• cities.name يزامن من name_ar حتى تعمل الاستعلامات القديمة التي تطلب cities.name.
• companies.name وحقول حالة الاشتراك/التحقق تكمل الحقول العربية/الإنجليزية المستخدمة في لوحات الإدارة والشركات.
• routes.base_price يزامن مع price_base.
• trips.price يزامن مع price_base، مع أعمدة توقيت التشغيل started_at و completed_at.
• bookings.is_checked_in, checked_in_at, checked_in_by, و checkin_method تعكس سجلات passenger_checkins.
• booking_qr_tokens.is_valid, used_at, و used_by تعكس حالة المسح.
• دوال مثل get_booking_by_code و token_blocklist موجودة كعقود حية لاختبارات الويب والموبايل.

Journey Booking aggregate

Migration 00227 separates the customer purchase from its operational segments:

journeys
  └─ journey_bookings
       ├─ booking_passengers
       ├─ bookings (one compatibility segment per journey_leg)
       │    ├─ booking_extras
       │    └─ journey_seat_assignments
       └─ journey_tickets (one Passenger × Journey Leg)

journey_booking_sessions is the opaque checkout capability. journey_resource_hold_batches groups all trip_resource_holds for one quote so exact seats and non-seat resources either reserve together or all roll back. Direct client mutation is revoked; quote, hold, release, confirmation, expiry, and ticket verification use caller-bound SECURITY DEFINER commands with RLS-protected reads.

Migration 00291 adds service-only confirmation bridges that temporarily project the API-verified Traveler UUID into the atomic database command. A malformed or expired Bearer token is rejected by the HTTP boundary and cannot silently become an anonymous checkout. Migration 00292 gives every compatibility Booking the same durable journey_booking_sessions owner projection as a native Journey, including a backfill and lifecycle trigger. A confirmed legacy Booking can therefore be reopened from My Trips without turning its public URL into an authorization credential.

Migrations 00293 and 00294 keep isolated demo discovery deterministic. New demo profiles are published before insertion, while get_demo_passenger_launch_option returns only a future Stop Call pair backed by an active vehicle and open Seat inventory on the Asia/Damascus service date. The function is executable only by service_role; public visibility remains governed by the existing exact-demo identity scope.

Migrations 00295 and 00296 repair runtime lineage and security rather than changing the Journey contract. They reassert the final 00292–00294 definitions before normalizing only three known release-candidate checksums, enable RLS and least-privilege grants on migration/auth/quality metadata, pin every legacy SECURITY DEFINER search path, and revoke stale client access to the service-only PII backfill checkpoint. pnpm audit:db-schema verifies the running database separately from the migration source inventory.

The compatibility trips.available_seats counter changes only when a Booking Segment becomes active. Temporary holds remain in trip_resource_inventory.held_quantity; confirmed seats and Extras move to reserved_quantity. The reconciliation function derives both values from canonical rows and rejects any capacity shrink below held plus reserved demand.

journey_orders groups one or two Journey Bookings so outbound and return travel share one idempotent checkout lifecycle. Confirmation is atomic across every Journey, segment, passenger, assignment, Extra, and Ticket. The database command rejects stale/foreign holds and rolls the whole order back if any leg cannot be confirmed.

Migration 00305 adds Company-controlled confirmation without turning a reservation into a usable Ticket prematurely:

company_booking_confirmation_policies
  └─ Company mode + review window + applicable traveler channels

journey_booking
  ├─ confirmation_status
  ├─ reservation_submitted_at / confirmation_deadline
  └─ journey_booking_confirmation_requests (one immutable policy snapshot per operator)

AUTO_CONFIRM remains the onboarding/backfill default. Under MANUAL_CONFIRM, checkout consumes the authoritative resource hold but moves the aggregate to PENDING_CONFIRMATION; Ticket rows use PENDING_CONFIRMATION with an empty credential. decide_company_booking_confirmation is service-role-only, verifies the authenticated Company actor and tenant, is idempotent, and approves the whole Journey/Order only after every required request closes successfully. Rejection or deadline expiry atomically cancels the affected scope and releases inventory. Direct document issuance and direct Ticket promotion are guarded by the same aggregate state.

journey_booking_boarding_authorized is the shared fulfillment predicate. It requires confirmation_status=CONFIRMED, exactly party_size × leg_count Tickets, and a current signed credential plus boarding authorization timestamp for every Ticket. journey_order_boarding_authorized requires every item to pass. Customer Web/mobile, Driver manifests, QR/document endpoints, and Company workspaces consume these projections rather than independently inferring eligibility. A confirmed connected Journey cannot be cancelled through the legacy single-segment RPC; the Company must use the aggregate-aware Trip operations/disruption workflow.

Migration 00306 closes the older direct-Booking compatibility seam. After the canonical projection trigger creates a Journey aggregate, an ordered non-callable trigger initializes the same Company policy and request graph. It also suppresses the obsolete segment-level confirmed notification when a manual decision request exists. The no-policy checkout bridges, boarding predicates, and traveler-history projection helpers are explicitly service-only; no anonymous or authenticated role can invoke them directly.

Migration 00307 prevents a never-issued Ticket from gaining a credential when operator rejection or timeout changes it from PENDING_CONFIRMATION to a terminal state. Previously issued cancellations keep their signed audit state; never-authorized cancellations remain unsigned. The validated journey_tickets_boardable_has_credential constraint independently guarantees that every boardable Ticket has a non-empty signed payload and authorization timestamp.

Migration 00263 makes a later bookings.trip_id change a canonical Journey operation rather than a disconnected compatibility update. The database locks the Journey, validates the replacement Trip's operator and endpoints, moves the Journey Leg, seat assignments, Tickets, Extras, and pending reminders, reconciles old and new inventory, updates aggregate totals, and replaces the accepted quote with a new price snapshot. It rejects active holds, finalized attendance, cross-operator moves, incompatible stops, timing conflicts, and insufficient resource capacity. A Booking Segment and its Journey Leg therefore cannot point at different Trips after a successful transaction.

Each journey_ticket is the boarding identity for exactly one Passenger and one Journey Leg. Migration 00247 records attendance in journey_ticket_checkins, whose ticket_id is unique. validate_and_checkin_journey_ticket verifies the assigned Driver, live assignment, Trip match, credential version, HMAC signature, Ticket/Booking state, offline timestamp, and optional coordinates while holding the relevant rows. Replays return the existing per-Ticket result and cannot check in another Passenger in the same Booking. Clients exchange only the versioned Ticket UUID/signature credential; names, phones, seats, and Booking references are not embedded in the QR payload.

Connected operations and tenant isolation

The operational graph uses the same Trip truth consumed by booking and settlement:

Trip
  ├─ Trip Stop Calls
  ├─ Trip Assignments → Driver / Bus
  ├─ Trip Operation Events
  ├─ Trip Disruptions
  ├─ Passenger Announcements
  ├─ Bus Locations
  ├─ Bookings / Check-ins
  └─ Partner Settlement Line

Telemetry and check-in mutations are available only through caller-bound commands. Direct driver writes to live-location storage are revoked. Migrations 00237–00242 consolidate access through narrow SECURITY DEFINER predicates that resolve the authenticated Platform role, Company membership, assigned Driver, or owning Traveler before RLS returns operational or financial records. No policy may infer authorization from client-supplied identity fields.

The exception snapshot ranks active critical alerts, disruptions, delay, and unhealthy GPS before future assignment gaps. The result reports both the complete count inside the requested window and the bounded number of rows returned, so the control-center UI cannot mistake pagination for operational truth.

Demo reset and expiry use the private dependency-ordered teardown introduced by 00250. It rejects non-demo Companies and any cross-tenant Journey or Journey Order before enabling a transaction-local teardown flag. Immutable finance/settlement documents and Trip-history guards honor that flag only after independently verifying companies.is_demo = true; ordinary tenant history remains immutable. Migration 00303 applies the same boundary to one-use document-transfer capabilities: a verified demo document removes its events, recipient session, and grant in dependency order while the grant's Company proof is still available. Outside that transaction, document-share events remain append-only and both direct mutation and ordinary cascading deletion fail closed.

Document-share issuer resolution also returns the canonical database role. Migration 00304 keeps Company finance documents (INVOICE, PAYMENT_RECEIPT) within OWNER/ADMIN, leaves operational confirmations available to booking roles, and excludes platform SUPPORT from document issuance. The client cannot submit or override this role.

:::caution Confirmed legacy ACL gap

The historical development schema still contains broad grants on some pre-foundation tables. They are not covered by the new narrow operational predicates. A compatibility-aware caller audit and explicit grant normalization are required before those legacy ACLs can be called production-hardened; global revocation without that audit could break existing Supabase clients.

:::

مخطط العلاقات (ER Diagram)

erDiagram
    companies ||--o{ routes : "has"
    companies ||--o{ buses : "owns"
    companies ||--o{ company_users : "employs"
    companies ||--o{ drivers : "has"

    routes ||--o{ trips : "schedules"
    routes ||--o{ trip_schedules : "recurs_on"
    routes }o--|| cities : "origin"
    routes }o--|| cities : "destination"

    buses ||--o{ trips : "assigned_to"
    buses ||--o{ trip_schedules : "recurring_assignment"
    buses ||--|| bus_layouts : "has_layout"

    trips ||--o{ bookings : "has"
    trips ||--o{ seats : "has"
    trips ||--o{ trip_assignments : "assigned"
    trip_schedules ||--o{ trips : "generates"

    companies ||--o{ company_cash_shifts : "reconciles"
    company_cash_shifts ||--o{ company_cash_adjustments : "records"
    companies ||--o{ notifications : "owns"
    notifications ||--o{ notification_deliveries : "fans_out"
    notification_deliveries ||--o{ notification_delivery_attempts : "attempts"
    bookings ||--o{ financial_documents : "snapshots"
    financial_documents ||--o{ document_render_events : "renders"
    bookings ||--o{ booking_documents : "snapshots"
    booking_documents ||--o{ document_render_events : "renders"
    bookings ||--o{ document_share_grants : "shares one issued document"
    booking_documents o|--o{ document_share_grants : "shared copy"
    financial_documents o|--o{ document_share_grants : "shared copy"
    document_share_grants ||--o| document_share_sessions : "accepted once"
    document_share_grants ||--o{ document_share_events : "audits"

    passengers ||--o{ bookings : "makes"
    companies ||--|| company_loyalty_programs : "owns"
    company_loyalty_programs ||--o{ loyalty_tiers : "defines"
    company_loyalty_programs ||--o{ loyalty_rewards : "offers"
    passengers ||--o{ loyalty_accounts : "owns_per_company"
    loyalty_tiers ||--o{ loyalty_accounts : "classifies"
    loyalty_accounts ||--o{ loyalty_transactions : "records"
    loyalty_accounts ||--o{ loyalty_redemptions : "creates"
    loyalty_rewards ||--o{ loyalty_redemptions : "redeemed_as"
    bookings ||--o{ booking_qr_tokens : "has"
    bookings ||--o{ passenger_checkins : "checked_in"
    loyalty_redemptions o|--o| bookings : "applied_to"

    drivers ||--o{ trip_assignments : "drives"
    drivers ||--o{ bus_locations : "reports"

---

notifications ومراقبة التسليم

يمثل notifications حدثاً منطقياً واحداً مع tenant واللغة والـcriticality ومعرفات correlation/causation. تنشئ كل قناة خارجية صفاً مستقلاً في notification_deliveries مع recipient masked/hash وidempotency key وقفل worker وعداد retry. تسجل notification_delivery_attempts كل محاولة، بينما تجعل notification_delivery_events callbacks المزودين idempotent. لا تكتب تطبيقات الويب حالات التسليم مباشرة؛ تستخدم RPCs في الترحيل 00217 للإنشاء والclaim والتكملة والfan-out.

financial_documents وdocument_render_events

financial_documents سجل immutable لإيصال دفع أو إشعار استرداد. يحفظ رقم المستند وtenant والحجز ونوع المصدر ومعرفه وsnapshot وSHA-256 ونسخة القالب ووقت الإصدار. trigger يمنع UPDATE وDELETE، وissue_financial_document وحده يحل المصدر المصرح إلى snapshot idempotent.

document_render_events سجل append-only لكل PDF مولد، بما فيه التذاكر والتأكيدات والتقارير التي لا تحتاج financial snapshot. يحفظ نوع المستند ومفتاحه واللغة والنسخة وchecksum الملف والحجم وrenderer trace والمستخدم. لا يعيد API ملفاً مالياً إذا فشل إدراج حدث التصيير.

booking_documents ومساحة عمل الحجز

booking_documents سجل immutable لتأكيد الحجز والفاتورة وتأكيد الإلغاء. يحفظ رقم مستند متسلسلاً، نوعه، مراجعته، snapshot كامل aggregate، SHA-256، نسخة القالب، المُصدر، وعلاقة supersession بالمراجعة السابقة. تمنع triggers التحديث والحذف، وتبقى القراءة المباشرة محجوبة؛ الإصدار والقراءة يمران عبر حدود service-role بعد تحقق tenant والدور في API.

يمثل build_company_booking_snapshot الإسقاط الثابت، بينما يجمع get_company_booking_workspace الحقيقة الحية لصفحة الشركة مع المسافرين والتذاكر والمقاعد والحضور/عدم الحضور والدفعات والمستندات والاتصالات. أضيف booking_document_id إلى document_render_events لربط كل ملف بالمراجعة التي صُيّرت فعلياً.

document_share_grants, document_share_sessions, وdocument_share_events

تمثل هذه الجداول تسليم نسخة مستند واحدة بين جهازين ولا تمثل صلاحية حجز أو صعود:

• يربط document_share_grants حجزاً بمستند Booking أو Financial immutable واحد فقط. المنحة single-use، قابلة للإلغاء، وتخزن hash السر لا السر الخام.
• ينشئ القبول الوحيد صفاً في document_share_sessions مع hash جديد وجلسة حتى 30 دقيقة تنتهي كذلك بعد 15 دقيقة خمول. هوية المنحة والجلسة immutable.
• تستقبل RPCs مدد TTL ضمن حدود ثابتة وتحسب كل expires_at باستخدام clock_timestamp() داخل PostgreSQL؛ لا تعد ساعة حاوية الويب مصدراً موثوقاً لانتهاء الصلاحية.
• يسجل document_share_events أحداث CREATED/PREVIEWED/ACCEPTED/VIEWED/ DOWNLOADED/REVOKED append-only. لا يسجل التسليم إلا بعد تصيير PDF ناجح، ويضم checksum وحجم الملف وrenderer trace.

كل الجداول تستخدم RLS forced ولا تملك أدوار anon أو authenticated أو حتى service_role وصولاً مباشراً. تعمل الدوال المحددة والمقفلة فقط من service role بعد أن يحل API هوية المسافر أو السائق المعيّن أو مستخدم الشركة أو مسؤول المنصة. لا تقبل الدوال actor/company scope يختاره العميل.

---

الجداول الأساسية

companies - شركات النقل

جدول شركات النقل المسجلة على المنصة.

العمود	النوع	الوصف
id	UUID	المعرف الفريد
name_ar	TEXT	الاسم بالعربية
name_en	TEXT	الاسم بالإنجليزية
phone	TEXT	رقم الهاتف
whatsapp	TEXT	رقم الواتساب
email	TEXT	البريد الإلكتروني
logo_url	TEXT	رابط الشعار
description	TEXT	الوصف
is_verified	BOOLEAN	تم التحقق
is_active	BOOLEAN	نشط
is_suspended	BOOLEAN	موقوف
suspended_at	TIMESTAMPTZ	تاريخ الإيقاف
suspension_reason	TEXT	سبب الإيقاف
warning_count	INTEGER	عدد التحذيرات
is_demo	BOOLEAN	tenant تجريبي مؤقت
is_internal	BOOLEAN	tenant QA إنتاجي خاص
demo_expires_at	TIMESTAMPTZ	وقت انتهاء العرض
demo_slug	TEXT	معرف العرض الفريد
demo_config	JSONB	الهوية والمسارات المخصصة
created_at	TIMESTAMPTZ	تاريخ الإنشاء

عندما تكون is_demo=true يجب أن يكون demo_expires_at في المستقبل حتى تسمح طبقات API وRLS بالوصول. أما is_internal=true فيحصر الشركة بحسابات QA المعلّمة في app_metadata. يمنع قيد قاعدة البيانات جمع المجالين على شركة واحدة، وتستبعد العروض العامة كليهما. يتطلب اكتشاف Trip الموجه للمسافر أيضاً أن تكون الشركة نشطة وغير موقوفة وأن تكون profile_status=PUBLISHED؛ يحتفظ service_role فقط بمسار التشغيل الموثوق للمهام الخلفية. يصنف الترحيل 00289 بيانات QA الإنتاجية القديمة ذات التوقيع الصريح INTERNAL PRODUCTION QA ACCOUNT ويمنح النطاق الداخلي فقط لهويات تملك علاقة شركة أو سائق أو حجز موثوقة بذلك الـtenant.

---

demo_access_grants - منح الوصول للعروض

العمود	النوع	الوصف
id	UUID	معرف المنحة
company_id	UUID	tenant التجريبي
token_hash	TEXT	بصمة SHA-256 للرمز؛ لا يخزن الرمز الخام
prospect_name	TEXT	اسم جهة الاتصال الاختياري
prospect_email	TEXT	بريد جهة الاتصال الاختياري
expires_at	TIMESTAMPTZ	وقت انتهاء المنحة
revoked_at	TIMESTAMPTZ	وقت الإلغاء الفوري
last_used_at	TIMESTAMPTZ	آخر وصول ناجح
use_count	INTEGER	عدد مرات الاستخدام

demo_personas - شخصيات العرض

يربط الشخصيات الثلاث (owner, passenger, driver) بهويات auth.users وسجلات الشركة/المسافر/السائق. يستخدمه خادم bootstrap فقط ولا يقرأه العميل مباشرة.

demo_outbox - صندوق الرسائل المحاكى

يسجل البريد وSMS وواتساب والتنبيهات التي كان سيولدها العرض دون إرسالها إلى مزود خارجي. يظهر آخر محتوى للعميل داخل مركز العرض.

---

cities - المدن السورية

جدول المدن السورية (21 مدينة محافظة).

العمود	النوع	الوصف
id	UUID	المعرف الفريد
name_ar	TEXT	الاسم بالعربية
name_en	TEXT	الاسم بالإنجليزية
governorate	TEXT	المحافظة
latitude	DOUBLE PRECISION	خط العرض
longitude	DOUBLE PRECISION	خط الطول
is_active	BOOLEAN	نشط

---

routes - المسارات

جدول المسارات بين المدن.

العمود	النوع	الوصف
id	UUID	المعرف الفريد
company_id	UUID	معرف الشركة
origin_city_id	UUID	مدينة المغادرة
dest_city_id	UUID	مدينة الوصول
distance_km	INTEGER	المسافة بالكيلومتر
duration_minutes	INTEGER	المدة بالدقائق
price_base	INTEGER	السعر الأساسي للمسار
is_active	BOOLEAN	نشط

القيود: فريد (company_id, origin_city_id, dest_city_id)

تقرأ لوحة الشركة المسارات عبر get_dashboard_routes_overview(company_id, is_active, route_id). تجمع الدالة trips_count التاريخي وscheduled_trips_count القادم داخل PostgreSQL بدلاً من تحميل معرفات كل الرحلات إلى خادم Next.js. تتحقق الدالة من عضوية auth.uid() النشطة ودور OWNER أو ADMIN أو DISPATCHER في الشركة المطلوبة، ولا تمنح التنفيذ لـ anon. يدعم الاستعلام الفهرس المركب trips(company_id, route_id).

---

buses - الباصات

جدول أسطول الباصات.

العمود	النوع	الوصف
id	UUID	المعرف الفريد
company_id	UUID	معرف الشركة
plate_number	TEXT	رقم اللوحة
capacity	INTEGER	السعة
bus_type	bus_type	النوع (STANDARD, VIP, SLEEPER)
amenities	TEXT[]	المرافق
is_active	BOOLEAN	نشط

تقرأ لوحة الشركة الحالة التشغيلية عبر get_dashboard_fleet_operations(company_id, bus_id). تعيد الدالة صفاً واحداً فقط لكل باص لديه رحلة بحالة BOARDING أو DEPARTED، وتختار أحدث مغادرة عند وجود بيانات تاريخية متعارضة. يمنع ذلك استنتاج حالة الأسطول من أول 1,000 رحلة يعيدها PostgREST، ويبقي حجم الاستجابة متناسباً مع عدد الباصات لا مع تاريخ الرحلات. الدالة SECURITY DEFINER لكنها تتحقق صراحة من صلاحية tenant ومن عضوية OWNER أو ADMIN أو DISPATCHER النشطة، ولا تمنح التنفيذ لـ anon.

---

trips - الرحلات

جدول الرحلات المجدولة.

العمود	النوع	الوصف
id	UUID	المعرف الفريد
company_id	UUID	معرف الشركة، يزامن من المسار
route_id	UUID	معرف المسار
bus_id	UUID	معرف الباص
pickup_stop_id	UUID	نقطة الصعود المملوكة/العامة المختارة
pickup_name_ar/en	TEXT	اسم نقطة الصعود المثبت على الرحلة
pickup_address_ar/en	TEXT	عنوان نقطة الصعود المثبت
pickup_latitude	NUMERIC	خط العرض المثبت
pickup_longitude	NUMERIC	خط الطول المثبت
pickup_directions_ar/en	TEXT	إرشادات الوصول المثبتة
pickup_location_confirmed	BOOLEAN	أكد المشغل النقطة لهذه الرحلة
departure_time	TIMESTAMPTZ	وقت المغادرة
arrival_time	TIMESTAMPTZ	وقت الوصول
price_base	INTEGER	السعر الأساسي (ل.س)
price_vip	INTEGER	سعر VIP
available_seats	INTEGER	المقاعد المتاحة
total_seats	INTEGER	إجمالي المقاعد
status	trip_status	الحالة
schedule_id	UUID	السلسلة المتكررة، إن وجدت
boarding_started_at	TIMESTAMPTZ	بدء الصعود الفعلي
actual_departure_time	TIMESTAMPTZ	الانطلاق الفعلي
actual_arrival_time	TIMESTAMPTZ	الوصول الفعلي
delay_minutes	INTEGER	مدة التأخير
delay_reason	TEXT	سبب التأخير
cancellation_reason	TEXT	سبب الإلغاء
closed_at	TIMESTAMPTZ	إغلاق السجل التشغيلي
status_changed_by	UUID	مستخدم الشركة المنفذ

حالات الرحلة:

• SCHEDULED - مجدولة
• BOARDING - الصعود جاري
• DEPARTED - غادرت
• ARRIVED - وصلت
• CANCELLED - ملغاة
• DELAYED - متأخرة
• IN_PROGRESS - قيمة توافق لرحلة قيد التنفيذ
• IN_TRANSIT - قيمة توافق لرحلة على الطريق
• COMPLETED - مغلقة تشغيلياً

تقرأ لوحة الشركة القائمة عبر get_dashboard_trips_page(company_id, page, per_page, status, date, route_id, search_route_ids, search_bus_ids). تعيد الدالة الصفوف والعدد الإجمالي وعدادات الحالات في استدعاء واحد، بحد أقصى 100 صف للصفحة، وتتحقق من عضوية auth.uid() النشطة في نفس الشركة قبل أي قراءة. عند تمرير date تستخدم حدود يوم دمشق وترتيباً زمنياً تصاعدياً؛ ومن دونه تعيد السجل الكامل من الأحدث إلى الأقدم. تشمل النتيجة السائق والتأخير والأوقات الفعلية.

منذ migration 00251 تحوّل الدالة تاريخ يوم دمشق إلى حدّي UTC ثم تقارن departure_time مباشرةً من دون cast على العمود. تُرشّح الرحلات وتحسب العدادات وتختار الصفحة أولاً، ثم تربط المسار والمدن والباص والسائق لصفوف الصفحة فقط. تدعم ذلك الفهارس idx_trips_company_departure_id و idx_trips_company_status_departure_id حتى لا تمسح صفحة تشغيل صغيرة سجل الشركة كاملاً أو تنفذ joins لعشرات آلاف الرحلات قبل LIMIT.

بعد إنشاء الرحلة يشغّل PostgreSQL initialize_trip_seats() لإنشاء صفوف المقاعد القديمة المتوافقة. تنفذ الدالة إدراجاً واحداً عبر generate_series بدلاً من حلقة لكل مقعد، وهي SECURITY DEFINER مع search_path ثابت حتى لا تعيد سياسات RLS وتقاطعات tenant لكل مقعد ولا تتجاوز مهلة طلب المستخدم الموثق.

يمثل trips.driver_id هوية السائق التشغيلية في company_users.id، بينما يمثل trip_assignments.driver_id ملف السائق في drivers.id الذي يستهلكه تطبيق السائق. تربط drivers.company_user_id الهويتين. يشغّل sync_trip_driver_assignment() بعد إنشاء الرحلة أو تغيير السائق أو الباص، ويحدّث تكليف تطبيق السائق ذرياً. يمنع تغيير السائق أو الباص بعد بدء التشغيل، وتحجب الجاهزية الانطلاق إذا كان الملف أو التكليف غير مرتبط أو غير متطابق.

يملك trigger validate_trip_pickup_location() نقطة الصعود: يثبت أنها في مدينة منشأ المسار وأن النقطة عامة أو مملوكة للشركة، وينسخ الاسم والعنوان والإحداثيات والإرشادات إلى صف الرحلة. يمنع تغييرها بعد وجود حجز. ينشئ sync_trip_stop_calls_from_route() أول trip_stop_call من نقطة الصعود الدقيقة، وتحمل trip_schedules.pickup_stop_id النقطة إلى الرحلات المتكررة. تدعم فهارس الشركة/النقطة/المغادرة استعلامات التشغيل من دون N+1.

تعرض public_trips لقنوات العملاء كامل snapshot الرحلة مع استبعاد شركات الديمو. وبما أن PostgreSQL يثبت أعمدة SELECT * عند إنشاء الـ view، يعيد migration 00286 بناء هذا الإسقاط بعد إضافة حقول نقطة الصعود؛ يمنع contract قاعدة البيانات رجوع حالة تظهر فيها النقطة في trips وتختفي من البحث أو تفاصيل الرحلة العامة.

trip_schedules - سلاسل الرحلات

يحفظ جدول trip_schedules تعريف السلسلة بعد أن تنشئ فعلياً موعداً صالحاً واحداً على الأقل. يتضمن الشركة والمسار والباص والسائق الاختياري ونطاق التواريخ وأيام الأسبوع والوقت والمدة والأسعار والملاحظات وهوية المنشئ. يرتبط كل صف منشأ في trips.schedule_id بالسلسلة، مع قيد فريد على (schedule_id, departure_time).

تنفذ الدالة plan_company_trip_series(...) المعاينة والإنشاء. تتحقق من دور OWNER أو ADMIN أو DISPATCHER، ومن ملكية المسار والباص والسائق، وصلاحية مخطط المقاعد، ومن نطاق لا يتجاوز 180 يوماً. تفحص تداخلات الباص والسائق وصيانة الباص ورخصة السائق. عند commit=true تستخدم أقفالاً advisory للباص والسائق وتعيد الفحص داخل المعاملة قبل الإدراج، لذلك لا تعتمد الكتابة على معاينة قديمة.

trip_operation_events وتشغيل الرحلة

يحفظ trip_operation_events تغيير الحالة والحضور وعدم الحضور والتراجع، مع الشركة والرحلة والحجز الاختياري والحالة السابقة والجديدة والسبب والبيانات الإضافية ومنفذ الإجراء والوقت. الجدول للقراءة فقط أمام أدوار الشركة المسموحة؛ كل كتابة تتم من دوال التشغيل الموثقة.

تعيد get_company_trip_dispatch_v2(company_id, trip_id) لقطة الغرفة: الرحلة ونقطة الصعود المثبتة والباص والسائق والكشف والعدادات والعوائق والإجراءات والأحداث. تفحص get_trip_dispatch_blockers(...) مخطط المقاعد والصيانة والسائق والرخصة والحجوزات غير المحسومة.

تنفذ transition_company_trip(...) دورة SCHEDULED/DELAYED → BOARDING → DEPARTED → ARRIVED → COMPLETED تحت قفل صف، وتسمح بالإلغاء قبل الانطلاق فقط مع سبب. تزامن الدالة trip_assignments والأوقات وتكتب حدثاً واحداً. عند الإلغاء يلغي trigger الحجوزات النشطة ويضيف إشعارات requires_refund_review=true من دون تزوير حقيقة الدفع أو تنفيذ استرداد ضمني.

تستخدم اللقطة هوية الحضور الأساسية نفسها التي يستخدمها تطبيق السائق: تذكرة Journey Ticket واحدة لكل مسافر ومقطع رحلة. ترجع manifest_item_id و manifest_item_type=JOURNEY_TICKET لكل مسافر، مع fallback مؤقت من نوع BOOKING للحجوزات القديمة التي لم تنشئ تذاكر Journey بعد.

تنفذ set_company_trip_manifest_attendance(...) الحضور وعدم الحضور والتراجع أثناء BOARDING فقط. عند NO_SHOW تنشئ سجلاً مستقلاً للمسافر مع السبب وهوية الموظف؛ وعند RESTORE تعكس ذلك السجل بدلاً من حذفه. تقفل الدالة التذكرة والحجز وتزامن الحضور والحالة الإجمالية والتكليف وسجل التشغيل في معاملة واحدة. لا يؤدي غياب مسافر واحد في حجز جماعي إلى تغيير بقية المسافرين. تستخدم دوال SECURITY DEFINER تحققاً صريحاً من auth.uid() والشركة والدور، ولا يملك anon حق التنفيذ.

تنفذ confirm_company_booking(company_id, booking_id) تأكيد الحجز المعلق تحت قفل الحجز والرحلة وبعد التحقق من عضوية الشركة وحالة الرحلة وموعدها. يمنع trigger الحالة انتقالات التأكيد والحضور والإكمال المباشرة؛ وتفتحها أعلام معاملة محلية داخل دوال SECURITY DEFINER الموثقة فقط، مع استثناء service_role للأحداث النظامية الموثوقة مثل webhooks الدفع.

لقطة تحليلات الشركة

تقرأ صفحة التحليلات الدالة get_dashboard_analytics_snapshot(company_id). تجمع الدالة الإيرادات والحجوزات والرحلات والإشغال وسلاسل 30 يوماً و12 شهراً وأداء المسارات وتكرار السفر في استدعاء JSON واحد. تعتمد على trips.company_id وbookings.company_id المفهرسين بدلاً من بناء مرشحات trip_id=in.(...) قد تتجاوز حد سطر الطلب في Kong أو حد 1,000 صف في PostgREST. تعمل الدالة بصلاحية SECURITY DEFINER مع search_path ثابت، لكنها ترفض أي مستخدم ليس عضواً نشطاً بدور OWNER أو ADMIN في الشركة المطلوبة، ولا تمنح التنفيذ لـ anon.

يقرأ تبويب التحليلات المتقدمة الدالة get_dashboard_advanced_analytics(company_id). تجمع الدالة الإلغاءات لآخر 30 يوماً، نشاط العملاء والاحتفاظ، تاريخ 60 يوماً للتوقع، ترتيب المسارات للفترة الحالية والسابقة، ووقت الحجز المسبق. يحسب إشغال المسار من سعة الرحلات الفريدة ومقاعد الحجوزات المؤكدة أو المسجلة أو المكتملة، وليس من تكرار سعة الباص لكل حجز. تخضع الدالة لحراس العضوية والدور والصلاحيات نفسها ولا يمكن لـ anon تنفيذها.

---

passengers - الركاب

جدول بيانات الركاب.

العمود	النوع	الوصف
id	UUID	المعرف الفريد
auth_user_id	UUID	معرف مستخدم Supabase
phone	TEXT	رقم الهاتف (فريد)
name	TEXT	الاسم
email	TEXT	البريد الإلكتروني
email_verified	BOOLEAN	تم التحقق من البريد
email_verified_at	TIMESTAMPTZ	وقت التحقق من البريد
whatsapp	TEXT	رقم الواتساب
reminder_enabled	BOOLEAN	تفعيل تذكيرات الرحلات
reminder_channel	TEXT	قناة التذكير: whatsapp أو sms أو both
reminder_day_before	BOOLEAN	إرسال تذكير قبل الرحلة بيوم
reminder_hour_before	BOOLEAN	إرسال تذكير قبل الرحلة بساعة
last_booking	TIMESTAMPTZ	آخر حجز
demo_company_id	UUID	tenant العرض عند كون المسافر تجريبياً

---

bookings - الحجوزات

جدول حجوزات التذاكر.

العمود	النوع	الوصف
id	UUID	المعرف الفريد
company_id	UUID	الشركة المنسوخة من الرحلة لعزل الاستعلام
trip_id	UUID	معرف الرحلة
passenger_id	UUID	معرف الراكب
seat_number	TEXT	أسماء المقاعد القانونية مفصولة بفواصل
seat_numbers	INTEGER[]	إسقاط توافق للمقاعد الرقمية فقط
seat_type	seat_type	نوع المقعد
seats_count	INTEGER	عدد المقاعد
price	INTEGER	السعر القديم المتوافق
total_price	INTEGER	إجمالي سعر الحجز (ل.س)
subtotal_price	NUMERIC(12,2)	السعر قبل خصم الولاء
discount_amount	NUMERIC(12,2)	قيمة خصم الولاء المطبقة
loyalty_redemption_id	UUID	قسيمة الولاء المستخدمة، إن وجدت
passenger_name	TEXT	اسم منسوخ للبحث والتوافق
passenger_phone	TEXT	هاتف منسوخ للبحث والتوافق
status	booking_status	الحالة
booking_code	TEXT	كود الحجز (SB-XXXXXX)
whatsapp_sent	BOOLEAN	تم إرسال الواتساب
notes	TEXT	ملاحظات
confirmed_at	TIMESTAMPTZ	تاريخ التأكيد
cancelled_at	TIMESTAMPTZ	تاريخ الإلغاء

يمكن أن يحتوي seat_number قيماً مثل 1D. يبقى seat_numbers INTEGER[] للتوافق مع الحجوزات الرقمية القديمة فقط، ويكون NULL عند وجود أسماء حرفية. تتحقق عقود الحجز من الاسم مقابل bus_layouts.layout_document وتكتب الإسقاطين داخل المعاملة نفسها.

يحفظ الحجز الذي استخدم مكافأة السعر قبل الخصم وقيمة الخصم والسعر النهائي. يفرض القيد أن subtotal_price = total_price + discount_amount، ويمنع الفهرس الجزئي ربط قسيمة ولاء بأكثر من حجز. في الذهاب والعودة يرتبط سجل القسيمة بحجز الذهاب وتوزع قيمة الخصم على السجلين مع الاحتفاظ بتفصيل كل سعر.

حالات الحجز:

• PENDING - قيد الانتظار
• CONFIRMED - مؤكد
• CANCELLED - ملغي
• COMPLETED - مكتمل
• NO_SHOW - لم يحضر

تقرأ قائمة لوحة الشركة عبر get_dashboard_bookings_page(company_id, page, per_page, status, search, trip_date, trip_id, created_from, created_to). تتحقق الدالة من auth.uid() وعضوية الشركة النشطة والدور التشغيلي، وتعيد صفوف الصفحة والعدد الإجمالي وعدادات الحالات والإيرادات من نطاق مادي واحد. يفرض مسار القائمة حداً أقصى 100 صف، بينما تسمح الدالة بـ 5,000 صف فقط لمسار التصدير المحمي. تستخدم الفهارس (company_id, created_at DESC, id DESC) و (company_id, status, created_at DESC, id DESC) لاستقرار الترتيب والصفحات.

يعتمد تتبع الراكب العام على get_public_trip_tracking(booking_code) بدلاً من منح anon قراءة الجداول. يعيد العقد JSON محدوداً بالرحلة والمدن والشركة وحالة التكليف وآخر 100 موضع ضمن نافذة التشغيل. كود الحجز هو capability، لذلك لا يدخل في السجلات ولا تعيد الدالة أي PII أو بيانات دفع أو مقعد أو ملاحظات أو معرّفات السائق.

---

جداول الولاء والمكافآت

company_loyalty_programs

صف واحد لكل شركة يحدد سياسة الكسب والاستبدال والإحالة والانتهاء والشروط. يبدأ متوقفاً افتراضياً. تملك شركة النقل الاقتصاد التجاري للبرنامج؛ لا يملك Admin المنصة حق تعديل الأرصدة أو المكافآت.

loyalty_tiers وloyalty_accounts

يعرّف loyalty_tiers مستويات كل برنامج وأسماءها العربية والإنجليزية وحد النقاط والمزايا ومعامل الكسب. يربط loyalty_accounts حساباً واحداً بكل زوج (company_id, passenger_id) ويحفظ رصيداً مستقلاً لكل شركة. يمنع قيد فريد إنشاء حسابين للمسافر نفسه داخل الشركة، ولا توجد عملية نقل بين الشركات.

loyalty_transactions

سجل append-only لكل شركة وحساب ولكل كسب أو استبدال أو انتهاء أو تعديل. يحفظ مقدار النقاط موجباً أو سالباً، وbalance_after، ومرجع الحجز أو الرحلة، والوصف باللغتين، ووقت انتهاء النقاط المكتسبة عند وجوده. يمنع idempotency_key تكرار مكافأة الحدث نفسه.

loyalty_rewards

كتالوج مكافآت الشركة. يحتوي نوع المكافأة والنقاط المطلوبة ونسبة أو قيمة الخصم والحد الأدنى للحجز والمسارات والأيام المؤهلة وحدود الاستبدال ونافذة الصلاحية. يدعم المخطط أنواعاً إضافية، لكن تدفق حجز العميل يطبق discount وfree_trip فقط.

loyalty_redemptions

يمثل القسيمة الناتجة من إنفاق النقاط. يحفظ رمز RWD-… الفريد والحالة والانتهاء والحجز المستخدم وقيمة الخصم، إضافة إلى reward_snapshot حتى لا يؤدي تعديل المكافأة لاحقاً إلى تغيير شروط قسيمة سبق للعميل شراؤها.

تنفذ redeem_company_loyalty_reward الخصم وإنشاء الحركة والقسيمة وتحديث عداد المكافأة مع أقفال صفوف تمنع الإنفاق المزدوج وتتحقق من تطابق الشركة. تتحقق preview_customer_loyalty_redemption من الأهلية ومن أن شركة القسيمة تشغل الرحلة من دون الاستهلاك. تنفذ apply_company_loyalty_referral إنشاء الإحالة ومكافأة الترحيب في معاملة واحدة وتمنع الإحالة الذاتية والتكرار والتداخل بين الشركات. تنفذ دالتا create_customer_booking_atomic وcreate_customer_round_trip_booking_atomic حجز المقاعد واستهلاك القسيمة مرة واحدة داخل المعاملة نفسها. كل هذه الدوال الخادمة مقيدة بدور service_role.

تحتفظ الصفوف التي أنشأها النظام المركزي القديم بعلامة is_legacy_platform=true لأغراض التاريخ فقط. لا تُسند قسراً إلى شركة ولا تظهر كرصيد شركة لأن ملكيتها التجارية لا يمكن استنتاجها بأمان.

---

جداول المقاعد والتخطيط

seats - مقاعد الرحلات

سجلات المقاعد الفردية لكل رحلة.

العمود	النوع	الوصف
id	UUID	المعرف الفريد
trip_id	UUID	معرف الرحلة
booking_id	UUID	معرف الحجز
passenger_id	UUID	معرف الراكب
seat_number	INTEGER	رقم المقعد
status	TEXT	الحالة (AVAILABLE, BOOKED, LOCKED, RESERVED)
seat_type	TEXT	النوع (STANDARD, VIP, WINDOW, AISLE)
price_modifier	DECIMAL	معامل السعر

---

bus_layouts - تخطيطات الباصات

تكوين شبكة تخطيط الباص. للميزات الجديدة يعتبر layout_document هو مصدر الحقيقة الوحيد القابل للكتابة، بينما تبقى الأعمدة الشبكية القديمة وbus_layout_positions إسقاطات توافقية مشتقة منه لدعم الشاشات والبيانات القديمة.

العمود	النوع	الوصف
id	UUID	المعرف الفريد
bus_id	UUID	معرف الباص
layout_document	JSONB	التخطيط القانوني بإحداثيات x/y/width/height/rotation/deck
layout_schema_version	INTEGER	إصدار مخطط JSON
status	TEXT	draft / active / archived
validation_status	TEXT	unvalidated / valid / invalid
validation_errors	JSONB	أخطاء التحقق عند الحفظ
parent_layout_id	UUID	الإصدار السابق عند التفرع/الأرشفة
activated_at	TIMESTAMPTZ	وقت تفعيل التخطيط
archived_at	TIMESTAMPTZ	وقت أرشفة التخطيط
grid_rows	INTEGER	إسقاط توافق من layout_document
grid_cols	INTEGER	إسقاط توافق من layout_document
aisle_after_col	INTEGER[]	إسقاط توافق للممرات القديمة
driver_position	JSONB	إسقاط توافق لموقع السائق
door_positions	JSONB[]	إسقاط توافق لمواقع الأبواب
toilet_position	JSONB	إسقاط توافق لموقع دورة المياه
total_seats	INTEGER	إجمالي المقاعد المحسوب من التخطيط
version	INTEGER	رقم الإصدار
is_active	BOOLEAN	نشط

الحفظ يتم عبر RPC save_bus_layout_document(p_bus_id, p_layout_document, p_save_mode) حتى يتم حفظ التخطيط، تحديث bus.capacity، وتجديد bus_layout_positions في معاملة واحدة. لا تستخدم واجهات جديدة إدخالاً مباشراً إلى bus_layout_positions.

---

bus_layout_positions - مواقع المقاعد

مواقع المقاعد الفردية داخل التخطيط. هذا الجدول إسقاط توافق مشتق من layout_document وليس مصدر كتابة للتخطيطات الجديدة.

العمود	النوع	الوصف
id	UUID	المعرف الفريد
layout_id	UUID	معرف التخطيط
seat_id	TEXT	المعرف الثابت للمقعد داخل layout_document
seat_number	VARCHAR(10)	رقم المقعد
row_index	INTEGER	فهرس الصف
col_index	INTEGER	فهرس العمود
seat_type_id	UUID	نوع المقعد
row_label	VARCHAR(5)	تسمية الصف (A, B, ...)
is_window	BOOLEAN	مقعد نافذة
is_aisle	BOOLEAN	مقعد ممر
rotation	INTEGER	الدوران (0, 90, 180, 270)

---

seat_type_definitions - تعريفات أنواع المقاعد

أنواع المقاعد المخصصة لكل شركة.

العمود	النوع	الوصف
id	UUID	المعرف الفريد
company_id	UUID	معرف الشركة
code	VARCHAR(50)	الرمز (vip, economy, wheelchair)
name_ar	VARCHAR(100)	الاسم بالعربية
name_en	VARCHAR(100)	الاسم بالإنجليزية
color	VARCHAR(7)	اللون (HEX)
icon	VARCHAR(50)	الأيقونة
price_modifier	DECIMAL	معامل السعر (1.0 = الأساسي)
is_accessible	BOOLEAN	متاح لذوي الاحتياجات
priority_boarding	BOOLEAN	صعود أولوي
extra_legroom	BOOLEAN	مساحة إضافية للأرجل

---

bus_templates - قوالب الباصات

قوالب تخطيط مسبقة للإعداد السريع.

العمود	النوع	الوصف
id	UUID	المعرف الفريد
name_ar	VARCHAR(100)	الاسم بالعربية
name_en	VARCHAR(100)	الاسم بالإنجليزية
capacity	INTEGER	السعة
grid_rows	INTEGER	عدد الصفوف
grid_cols	INTEGER	عدد الأعمدة
has_toilet	BOOLEAN	يحتوي دورة مياه
seat_positions	JSONB	مواقع المقاعد

القوالب المتوفرة:

1. باص عادي 40 مقعد (10×4)
2. باص 50 مقعد مع دورة مياه (13×4)
3. باص VIP 30 مقعد (10×3)
4. ميني باص 20 مقعد (5×4)
5. باص كبير 55 مقعد مع دورة مياه (14×4)

---

جداول المستخدمين

admin_users - مشرفو المنصة

العمود	النوع	الوصف
id	UUID	المعرف الفريد
auth_user_id	UUID	معرف auth.users
email	TEXT	البريد الإلكتروني
name	TEXT	الاسم
role	admin_role	الدور (SUPER_ADMIN, ADMIN, SUPPORT)
is_active	BOOLEAN	نشط
last_login_at	TIMESTAMPTZ	آخر تسجيل دخول
totp_enabled	BOOLEAN	هل المصادقة الثنائية مفعلة
totp_secret	TEXT	غلاف AES-256-GCM إصداري totp:v1 لسر TOTP
backup_codes	TEXT[]	رموز الاسترداد المخزنة

---

company_users - مستخدمو الشركات

العمود	النوع	الوصف
id	UUID	المعرف الفريد
auth_user_id	UUID	معرف auth.users
company_id	UUID	معرف الشركة
email	TEXT	البريد الإلكتروني
name	TEXT	الاسم
role	company_role	الدور
is_active	BOOLEAN	نشط

أدوار الشركة:

• OWNER - مالك
• ADMIN - مدير
• DISPATCHER - منسق
• STAFF - موظف

---

verified_second_factor_sessions - جلسات العامل الثاني

قائمة سماح خادمة فقط تربط جلسة Supabase التي اجتازت TOTP بهوية المشرف أو السائق. لا تتاح مباشرةً لدوري anon أو authenticated.

العمود	النوع	الوصف
subject_type	TEXT	admin أو driver
subject_id	UUID	معرف المشرف أو السائق
auth_user_id	UUID	هوية Supabase التي اجتازت التحقق
session_id	UUID	معرف جلسة JWT المحددة
verified_at	TIMESTAMPTZ	وقت نجاح العامل الثاني
last_seen_at	TIMESTAMPTZ	آخر استخدام محمي
expires_at	TIMESTAMPTZ	انتهاء الثقة حتى لو بقيت جلسة Auth صالحة
revoked_at	TIMESTAMPTZ	وقت الإلغاء المبكر
client_ip	INET	عنوان العميل عند التحقق
user_agent	TEXT	وكيل المستخدم عند التحقق

---

drivers - السائقون

العمود	النوع	الوصف
id	UUID	المعرف الفريد
company_user_id	UUID	هوية السائق التشغيلية في company_users
company_id	UUID	معرف الشركة
license_number	TEXT	رقم الرخصة
license_expiry	DATE	تاريخ انتهاء الرخصة
phone	TEXT	رقم الهاتف
fcm_token	TEXT	رمز Firebase
current_trip_id	UUID	الرحلة الحالية
last_latitude	DECIMAL	آخر خط عرض
last_longitude	DECIMAL	آخر خط طول
total_trips	INTEGER	إجمالي الرحلات
on_time_percentage	DECIMAL	نسبة الالتزام بالمواعيد

---

جداول تتبع السائق

trip_assignments - تعيينات الرحلات

العمود	النوع	الوصف
id	UUID	المعرف الفريد
trip_id	UUID	معرف الرحلة
driver_id	UUID	معرف ملف السائق في drivers
bus_id	UUID	معرف الباص
status	trip_assignment_status	الحالة
started_at	TIMESTAMPTZ	وقت البدء
completed_at	TIMESTAMPTZ	وقت الانتهاء
total_checked_in	INTEGER	إجمالي الصعود
total_no_shows	INTEGER	إجمالي الغياب
total_collected	DECIMAL	المبلغ المحصل

يوجد تكليف واحد لكل رحلة. ينشأ ويُزامن من trips.driver_id عبر drivers.company_user_id؛ لذلك لا تكتب واجهات لوحة الشركة معرف ملف drivers مباشرة في trips.driver_id.

لا يملك السائق UPDATE مباشراً على التكليف. تسجل mark_driver_assignment_en_route(...) استعداده للطريق قبل دخول الرحلة غرفة التشغيل، ثم تستخدم انتقالات الرحلة الموثقة التسلسل الأمامي فقط: BOARDING → DEPARTED → ARRIVED → COMPLETED. يتحقق كل انتقال من هوية السائق ومن تكليفه الفعلي ويزامن الرحلة والتكليف والأوقات وسجل التشغيل في معاملة واحدة؛ ولا يستطيع دور السائق تأخير الرحلة أو إلغاءها أو إرجاعها إلى حالة سابقة.

---

bus_locations - مواقع الباصات

العمود	النوع	الوصف
id	UUID	المعرف الفريد
trip_id	UUID	معرف الرحلة
driver_id	UUID	معرف السائق
latitude	DECIMAL	خط العرض
longitude	DECIMAL	خط الطول
speed	DECIMAL	السرعة
heading	DECIMAL	الاتجاه
recorded_at	TIMESTAMPTZ	وقت التسجيل
is_offline_record	BOOLEAN	سجل غير متصل

---

passenger_checkins - تسجيل صعود الركاب

العمود	النوع	الوصف
id	UUID	المعرف الفريد
booking_id	UUID	معرف الحجز
trip_id	UUID	معرف الرحلة
driver_id	UUID	معرف السائق
checked_in_at	TIMESTAMPTZ	وقت الصعود
check_in_method	checkin_method	طريقة التسجيل
is_no_show	BOOLEAN	لم يحضر

طرق التسجيل:

• QR_SCAN - مسح QR
• MANUAL - يدوي
• AUTO - تلقائي

---

booking_qr_tokens - رموز QR للحجوزات

العمود	النوع	الوصف
id	UUID	المعرف الفريد
booking_id	UUID	معرف الحجز
trip_id	UUID	معرف الرحلة
token	TEXT	الرمز المميز
qr_data	JSONB	بيانات QR
expires_at	TIMESTAMPTZ	تاريخ الانتهاء
scanned_at	TIMESTAMPTZ	وقت المسح
scanned_by	UUID	مسح بواسطة

توفر الدالة get_trip_qr_tokens(trip_id, driver_id) نسخة HMAC مختصرة للتخزين عند السائق. التنفيذ مقصور على دور authenticated، ويربط معرف السائق بـ auth.uid() قبل قراءة أي اسم أو هاتف لراكب. يبقى bookings.seat_number نصياً للتوافق، وتعيد الدالة إسقاطاً عددياً آمناً لتطبيق السائق.

تحفظ trip_messages رسائل الرحلة. يقرأ السائق ويرسل فقط لرحلة مكلف بها بعد حل هويته عبر is_own_driver، ويقرأ الراكب فقط محادثة رحلة يملك لها حجزاً. تسمح امتيازات المتصفح بـ SELECT، INSERT، وتعديل عمود is_read فقط؛ لا ينطبق RLS على TRUNCATE لذلك هذا الامتياز مسحوب صراحةً من anon وauthenticated.

يحفظ trip_payments التحصيل الذي ينفذه السائق. يفرض الفهرس الجزئي الفريد uq_trip_payments_booking_id سجلاً واحداً لكل booking_id. الكتابة من دور authenticated متاحة فقط عبر collect_trip_payment(...): تربط الدالة p_driver_id بـ auth.uid()، وتقفل التكليف والحجز، وتتحقق من الرحلة والمبلغ المتبقي وحالة التشغيل، ثم تحدّث trip_payments وbookings.is_paid/payment_status/paid_amount/paid_at/payment_collected وtrip_assignments.total_collected ذرياً. إعادة الطلب idempotent، بينما الجدول نفسه للقراءة فقط أمام المستخدم الموثق ومحجوب بالكامل عن anon. بقيت الدالة القديمة increment_assignment_collected لدور service_role فقط.

تعيد الدالة get_dashboard_payment_ledger(...) إسقاطاً موحداً لعمليات payment_transactions وتحصيل trip_payments وحقيقة الحجز النقدية، مع إحصاءات وpagination وفلاتر الخادم. يطبع trigger normalize_booking_payment_truth() حقول payment_status, is_paid, payment_collected, paid_amount, وpaid_at حتى لا تكتب قنوات التحصيل حقائق متناقضة.

تنفذ دوال verify_company_payment, refund_company_payment, cancel_company_payment, وupdate_company_payment_notes قرارات الشركة تحت قفل العملية والحجز مع تحقق صريح من عضوية OWNER أو ADMIN. الاسترداد كامل فقط، والإلغاء مقصور على الحالات غير المكتملة، وملاحظات العملية النهائية غير قابلة للتعديل. تبقى الدالة التاريخية apply_provider_payment_webhook محجوبة خلف service_role ولا يستدعيها أي endpoint نشط. يمنع الترحيل 00278 جلسات anon وauthenticated من كتابة مزود غير CASH إلى سجلات العميل أو السائق، كما يقيد إعدادات كل شركة على النقد فقط إلى أن يعتمد مزود رسمي.

سُحبت امتيازات INSERT, UPDATE, وDELETE على payment_transactions وعلى bookings من anon وauthenticated. لذلك لا تكفي سياسة RLS لفتح كتابة مباشرة؛ كل تغيير حجز أو قرار مالي يمر عبر دالة المجال المناسبة التي تملك قفلها وتحققها وسجلها في المعاملة نفسها.

company_cash_shifts وcompany_cash_adjustments

يحفظ company_cash_shifts دورة صندوق الشركة من رصيد البداية إلى الإغلاق، بإجمالي التحصيل والحركات والمبلغ المتوقع والمعلن والفارق وهوية الفاتح والمغلق. يسمح فهرس جزئي بصندوق مفتوح واحد فقط لكل شركة. يحفظ company_cash_adjustments كل مصروف أو استرداد أو تصحيح أو حركة أخرى مع مبلغ غير صفري وملاحظة وهوية المنفذ. القراءة ودوال open, adjust, وclose مقصورة على مالك الشركة أو مديرها النشط؛ وتستخدم عملية الإغلاق قفل صف وتحسب التحصيل من وقت فتح الوردية حتى لحظة الإغلاق.

---

جداول الدعم الفني

support_tickets - تذاكر الدعم

العمود	النوع	الوصف
id	UUID	المعرف الفريد
ticket_number	TEXT	رقم التذكرة
passenger_id	UUID	معرف الراكب
company_id	UUID	معرف الشركة
booking_id	UUID	معرف الحجز
type	ticket_type	النوع
subject	TEXT	الموضوع
description	TEXT	الوصف
status	ticket_status	الحالة
priority	ticket_priority	الأولوية
assigned_to	UUID	مسند إلى
resolved_at	TIMESTAMPTZ	تاريخ الحل

أنواع التذاكر:

• COMPLAINT - شكوى
• REFUND_REQUEST - طلب استرداد
• BOOKING_ISSUE - مشكلة حجز
• GENERAL_INQUIRY - استفسار عام
• SUGGESTION - اقتراح

أولويات التذاكر:

• LOW - منخفضة
• MEDIUM - متوسطة
• HIGH - عالية
• URGENT - عاجلة

---

ticket_messages - رسائل التذاكر

العمود	النوع	الوصف
id	UUID	المعرف الفريد
ticket_id	UUID	معرف التذكرة
sender_type	message_sender_type	نوع المرسل
sender_id	UUID	معرف المرسل
sender_name	TEXT	اسم المرسل
content	TEXT	المحتوى
is_internal	BOOLEAN	رسالة داخلية
attachments	TEXT[]	المرفقات

---

جداول الإدارة

admin_permissions - صلاحيات المشرفين

العمود	النوع	الوصف
id	UUID	المعرف الفريد
code	TEXT	الرمز
name_ar	TEXT	الاسم بالعربية
name_en	TEXT	الاسم بالإنجليزية
category	TEXT	الفئة

---

admin_audit_log - سجل المراجعة

العمود	النوع	الوصف
id	UUID	المعرف الفريد
admin_user_id	UUID	معرف المشرف
admin_name	TEXT	اسم المشرف
admin_email	TEXT	بريد المشرف
action	TEXT	الإجراء
resource_type	TEXT	نوع المورد
resource_id	UUID	معرف المورد
details	JSONB	التفاصيل
ip_address	INET	عنوان IP
created_at	TIMESTAMPTZ	التاريخ

---

audit_log - سجل التدقيق العام

سجل ملحق فقط (append-only) للأحداث غير الإدارية، مثل نشاط الركاب والسائقين والشركات وأحداث النظام. لا يسمح بالوصول المباشر من anon أو authenticated؛ تتم الكتابة والقراءة من خدمات الخادم باستخدام service_role.

العمود	النوع	الوصف
id	UUID	المعرف الفريد
actor_id	TEXT	معرف الجهة المنفذة
actor_type	TEXT	راكب/شركة/مشرف/سائق/نظام
actor_email	TEXT	البريد عند توفره
company_id	UUID	نطاق الشركة عند توفره
action	TEXT	الإجراء
resource_type	TEXT	نوع المورد
resource_id	TEXT	معرف المورد
details	JSONB	تفاصيل منظمة
ip_address	TEXT	عنوان IP الموثق أو وسم المصدر القديم
user_agent	TEXT	وكيل المستخدم
created_at	TIMESTAMPTZ	وقت التسجيل

تحتفظ أعمدة التوافق table_name, record_id, old_data, new_data, وuser_id بعقد مزامنة تسجيل الحضور دون اتصال حتى تُرحّل الدالة القديمة إلى العقد الموحد.

---

waitlist - قائمة انتظار الشركات

العمود	النوع	الوصف
id	UUID	المعرف الفريد
email	TEXT	بريد الشركة أو الشخص المهتم
company_name	TEXT	اسم الشركة الاختياري
phone	TEXT	رقم الهاتف الاختياري
source	TEXT	مصدر التسجيل
status	TEXT	pending/contacted/converted/rejected
notes	TEXT	ملاحظات داخلية
created_at	TIMESTAMPTZ	وقت التسجيل
contacted_at	TIMESTAMPTZ	وقت التواصل
contacted_by	UUID	المشرف الذي تواصل مع الطلب

---

جداول الإشعارات

notifications - إشعارات الحجوزات

العمود	النوع	الوصف
id	UUID	المعرف الفريد
booking_id	UUID	معرف الحجز
type	notification_type	النوع
channel	notification_channel	القناة (SMS, WHATSAPP)
status	notification_status	الحالة
sent_at	TIMESTAMPTZ	وقت الإرسال
error_message	TEXT	رسالة الخطأ

---

driver_notifications - إشعارات السائقين

العمود	النوع	الوصف
id	UUID	المعرف الفريد
driver_id	UUID	معرف السائق
title	TEXT	العنوان
body	TEXT	المحتوى
type	driver_notification_type	النوع
data	JSONB	البيانات
read_at	TIMESTAMPTZ	وقت القراءة

---

push_subscriptions - اشتراكات الإشعارات

العمود	النوع	الوصف
id	UUID	المعرف الفريد
passenger_id	UUID	مالك راكب، عند اشتراك العميل
company_user_id	UUID	مالك شركة؛ تستخدمه أيضاً اشتراكات السائق المرتبط
admin_user_id	UUID	مالك مشرف المنصة
device_id	VARCHAR	معرف جهاز اختياري
device_type	VARCHAR	web أو ios أو android
device_name	VARCHAR	وصف الجهاز بحد 255 محرفاً
push_token	TEXT	هدف التسليم الفريد والمعتمد للـ upsert
endpoint	TEXT	عنوان Web Push عند استخدام VAPID
p256dh_key	TEXT	مفتاح تشفير Web Push
auth_key	TEXT	سر مصادقة Web Push
fcm_token	TEXT	رمز FCM الاختياري
ntfy_topic	VARCHAR	موضوع ntfy لتطبيقي Flutter
is_active	BOOLEAN	حالة الاشتراك
last_used_at	TIMESTAMPTZ	آخر تسجيل أو استخدام
expires_at	TIMESTAMPTZ	انتهاء اختياري

يفرض المخطط وجود مالك واحد فقط من أعمدة المالك الثلاثة، ويفرض تفرد push_token. لا تستخدم الأعمدة التاريخية user_id, user_type, p256dh, auth, أو device_info. عند تسجيل السائق تُحل هويته الموثقة أولاً ثم يُحفظ الاشتراك تحت drivers.company_user_id. موضوع ntfy مرتبط بهوية الجلسة (shambus-customer-{authUserId} أو shambus-driver-{driverId}) ولا يقبل موضوعاً اختيارياً من العميل.

---

جداول التقييم والإحصائيات

trip_reviews - مراجعات العملاء المنشورة

يربط الجدول كل مراجعة بحجز وراكب ورحلة وشركة، ويسمح بمراجعة واحدة لكل حجز. يقبل تقييماً عاماً من 1 إلى 5 وتقييمات اختيارية للراحة والالتزام والنظافة والسائق، مع تعليق وحالة إشراف PUBLISHED, HIDDEN, أو FLAGGED.

لا ينشئ العميل مراجعة إلا لحجز يملكه بعد اكتمال الرحلة، ولا يستطيع بعد ذلك تغيير معرف الحجز أو الراكب أو الرحلة أو الشركة أو حالة الإشراف. يتحقق مسار التعديل مجدداً من ملكية الحجز واكتمال الرحلة، ويقرأ موظف الشركة مراجعات شركته فقط عندما يكون حسابه نشطاً. يعيد الترحيل 00216 تثبيت هذه السياسات وtrigger ثبات الهوية قبل تطبيع checksum التاريخي المعروف للترحيل 00203.

trip_feedback - تقييمات الرحلات

العمود	النوع	الوصف
id	UUID	المعرف الفريد
booking_id	UUID	معرف الحجز
trip_id	UUID	معرف الرحلة
passenger_id	UUID	معرف الراكب
overall_rating	INTEGER	التقييم العام (1-5)
driver_rating	INTEGER	تقييم السائق
bus_condition_rating	INTEGER	تقييم حالة الباص
punctuality_rating	INTEGER	تقييم الالتزام بالمواعيد
comment	TEXT	التعليق
is_anonymous	BOOLEAN	مجهول الهوية

---

company_monthly_stats - إحصائيات الشركات الشهرية

العمود	النوع	الوصف
id	UUID	المعرف الفريد
company_id	UUID	معرف الشركة
year	INTEGER	السنة
month	INTEGER	الشهر
trips_count	INTEGER	عدد الرحلات
bookings_count	INTEGER	عدد الحجوزات
revenue	INTEGER	الإيرادات
avg_rating	DECIMAL	متوسط التقييم

---

أنواع البيانات المخصصة (Enums)

-- أنواع الباصات
CREATE TYPE bus_type AS ENUM ('STANDARD', 'VIP', 'SLEEPER');

-- حالات الرحلات
CREATE TYPE trip_status AS ENUM ('SCHEDULED', 'BOARDING', 'DEPARTED',
                                   'ARRIVED', 'CANCELLED', 'DELAYED');

-- حالات الحجوزات
CREATE TYPE booking_status AS ENUM ('PENDING', 'CONFIRMED', 'CANCELLED',
                                     'COMPLETED', 'NO_SHOW');

-- أدوار المشرفين
CREATE TYPE admin_role AS ENUM ('SUPER_ADMIN', 'ADMIN', 'SUPPORT');

-- أدوار الشركة
CREATE TYPE company_role AS ENUM ('OWNER', 'ADMIN', 'DISPATCHER', 'STAFF');

-- أنواع الإشعارات
CREATE TYPE notification_type AS ENUM ('BOOKING_CONFIRMED', 'BOOKING_CANCELLED',
                                        'TRIP_REMINDER', 'TRIP_DEPARTED',
                                        'TRIP_CANCELLED');

-- قنوات الإشعارات
CREATE TYPE notification_channel AS ENUM ('WHATSAPP', 'SMS');

-- أنواع تذاكر الدعم
CREATE TYPE ticket_type AS ENUM ('COMPLAINT', 'REFUND_REQUEST',
                                  'BOOKING_ISSUE', 'GENERAL_INQUIRY',
                                  'SUGGESTION');

-- حالات التعيينات
CREATE TYPE trip_assignment_status AS ENUM ('ASSIGNED', 'EN_ROUTE',
                                             'BOARDING', 'IN_PROGRESS',
                                             'COMPLETED', 'CANCELLED');

---

الفهارس الأساسية

-- فهارس الأداء
CREATE INDEX idx_trips_departure ON trips(departure_time);
CREATE INDEX idx_trips_status ON trips(status);
CREATE INDEX idx_bookings_status ON bookings(status);
CREATE INDEX idx_bookings_code ON bookings(booking_code);
CREATE INDEX idx_seats_trip_status ON seats(trip_id, status);
CREATE INDEX idx_bus_locations_trip ON bus_locations(trip_id);
CREATE INDEX idx_passengers_phone ON passengers(phone);

---

ملاحظات مهمة

1. جميع المعرفات UUID: نستخدم UUID بدلاً من التسلسل الرقمي للأمان وقابلية التوزيع.

2. الطوابع الزمنية: جميع التواريخ بتوقيت UTC (TIMESTAMPTZ).

3. الأسعار بالليرة السورية: الأسعار مخزنة كـ INTEGER بدون فواصل عشرية.

4. Soft Delete: لا نحذف البيانات نهائياً، نستخدم is_active = false.

5. التدقيق: جميع العمليات الحساسة مسجلة في admin_audit_log.