359 lines
21 KiB
SQL
359 lines
21 KiB
SQL
-- =============================================================
|
|
-- schema_transit.sql — قاعدة بيانات منصة مواصلاتي (siroTransitDb)
|
|
-- عزل كامل عن main/ride/tracking — ممنوع أي JOIN خارجي
|
|
-- الربط بالنظام الرئيسي عبر passenger_id / driver_id فقط
|
|
-- =============================================================
|
|
|
|
SET FOREIGN_KEY_CHECKS = 0;
|
|
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
|
|
SET time_zone = "+00:00";
|
|
|
|
-- -----------------------------------------------------------------
|
|
-- 1. transit_orgs — المؤسسات (جامعات، مدارس، فنادق، شركات، ناقلون)
|
|
-- -----------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `transit_orgs` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`type` ENUM('university','school','hotel','company','transporter') NOT NULL,
|
|
`country` CHAR(2) NOT NULL COMMENT 'JO | SY | EG | ...',
|
|
`city` VARCHAR(80) NOT NULL,
|
|
`name_ar` VARCHAR(200) NOT NULL,
|
|
`name_en` VARCHAR(200) NOT NULL,
|
|
`logo_url` VARCHAR(500) DEFAULT NULL,
|
|
`campus_polygon` JSON DEFAULT NULL COMMENT 'حدود الحرم — مصفوفة [{lat,lng}]',
|
|
`website` VARCHAR(300) DEFAULT NULL,
|
|
`contact_phone` VARCHAR(30) DEFAULT NULL,
|
|
`contact_email` VARCHAR(150) DEFAULT NULL,
|
|
`contract_status` ENUM('trial','active','suspended','terminated') NOT NULL DEFAULT 'trial',
|
|
`trial_ends_at` DATE DEFAULT NULL,
|
|
`notes` TEXT DEFAULT NULL,
|
|
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_country_type` (`country`, `type`),
|
|
KEY `idx_contract_status` (`contract_status`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- -----------------------------------------------------------------
|
|
-- 2. transit_org_links — ربط ناقل ↔ مؤسسة (النمط الثاني)
|
|
-- جامعة حكومية لا تملك أسطولاً → تُخدَم بناقل مسجّل
|
|
-- -----------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `transit_org_links` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`institution_id` INT UNSIGNED NOT NULL COMMENT 'org_id للمؤسسة (جامعة/مدرسة)',
|
|
`transporter_id` INT UNSIGNED NOT NULL COMMENT 'org_id للناقل (type=transporter)',
|
|
`status` ENUM('active','inactive') NOT NULL DEFAULT 'active',
|
|
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uq_link` (`institution_id`, `transporter_id`),
|
|
KEY `idx_transporter` (`transporter_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- -----------------------------------------------------------------
|
|
-- 3. transit_org_admins — مشرفو المؤسسة (لوحة تحكم الويب)
|
|
-- -----------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `transit_org_admins` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`org_id` INT UNSIGNED NOT NULL,
|
|
`name` VARCHAR(120) NOT NULL,
|
|
`phone` VARCHAR(25) NOT NULL,
|
|
`role` ENUM('owner','transport_manager','dispatcher') NOT NULL DEFAULT 'transport_manager',
|
|
`permissions` JSON DEFAULT NULL COMMENT '{"routes":true,"drivers":true,"roster":true,"broadcast":true}',
|
|
`is_active` TINYINT(1) NOT NULL DEFAULT 1,
|
|
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uq_phone_org` (`phone`, `org_id`),
|
|
KEY `idx_org` (`org_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- -----------------------------------------------------------------
|
|
-- 4. transit_sessions — جلسات تسجيل دخول مشرف الويب (OTP-based)
|
|
-- -----------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `transit_sessions` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`admin_id` INT UNSIGNED NOT NULL,
|
|
`org_id` INT UNSIGNED NOT NULL,
|
|
`token_hash` VARCHAR(64) NOT NULL COMMENT 'sha256 للـ session token',
|
|
`ip` VARCHAR(45) DEFAULT NULL,
|
|
`user_agent` VARCHAR(300) DEFAULT NULL,
|
|
`expires_at` TIMESTAMP NOT NULL,
|
|
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uq_token` (`token_hash`),
|
|
KEY `idx_admin` (`admin_id`),
|
|
KEY `idx_expires` (`expires_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- -----------------------------------------------------------------
|
|
-- 5. transit_vehicles — الباصات والفانات المملوكة للمؤسسة
|
|
-- -----------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `transit_vehicles` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`org_id` INT UNSIGNED NOT NULL,
|
|
`plate` VARCHAR(30) NOT NULL,
|
|
`make` VARCHAR(50) DEFAULT NULL,
|
|
`model` VARCHAR(50) DEFAULT NULL,
|
|
`year` SMALLINT DEFAULT NULL,
|
|
`color` VARCHAR(30) DEFAULT NULL,
|
|
`capacity` TINYINT NOT NULL DEFAULT 30,
|
|
`vehicle_type` ENUM('bus','minibus','van','other') NOT NULL DEFAULT 'bus',
|
|
`is_active` TINYINT(1) NOT NULL DEFAULT 1,
|
|
`notes` VARCHAR(300) DEFAULT NULL,
|
|
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uq_plate` (`plate`),
|
|
KEY `idx_org` (`org_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- -----------------------------------------------------------------
|
|
-- 6. transit_drivers — سائقو الباصات (يُنشئهم مشرف المؤسسة)
|
|
-- -----------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `transit_drivers` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`org_id` INT UNSIGNED NOT NULL,
|
|
`name` VARCHAR(120) NOT NULL,
|
|
`phone` VARCHAR(25) NOT NULL,
|
|
`license_number` VARCHAR(50) DEFAULT NULL,
|
|
`license_img_url` VARCHAR(500) DEFAULT NULL,
|
|
`main_driver_id` VARCHAR(100) DEFAULT NULL COMMENT 'معرّف في جدول driver الرئيسي إن كان كابتن مشاوير أيضاً',
|
|
`invite_token` VARCHAR(64) DEFAULT NULL COMMENT 'رمز الدعوة للتفعيل الأولي',
|
|
`invite_sent_at` TIMESTAMP DEFAULT NULL,
|
|
`activated_at` TIMESTAMP DEFAULT NULL,
|
|
`status` ENUM('invited','active','suspended') NOT NULL DEFAULT 'invited',
|
|
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uq_phone_org` (`phone`, `org_id`),
|
|
KEY `idx_org` (`org_id`),
|
|
KEY `idx_invite_token` (`invite_token`),
|
|
KEY `idx_main_driver` (`main_driver_id`) -- يُستعلم في transitResolveDriver + driver/me.php
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- -----------------------------------------------------------------
|
|
-- 7. transit_routes — خطوط النقل
|
|
-- -----------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `transit_routes` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`org_id` INT UNSIGNED NOT NULL,
|
|
`name_ar` VARCHAR(150) NOT NULL,
|
|
`name_en` VARCHAR(150) DEFAULT NULL,
|
|
`direction` ENUM('outbound','inbound','circular') NOT NULL DEFAULT 'outbound',
|
|
`polyline` TEXT DEFAULT NULL COMMENT 'Encoded polyline للمسار',
|
|
`distance_km` DECIMAL(7,2) DEFAULT NULL,
|
|
`duration_min` SMALLINT DEFAULT NULL,
|
|
`status` ENUM('draft','active','suspended','rejected') NOT NULL DEFAULT 'draft',
|
|
`created_by` INT UNSIGNED DEFAULT NULL COMMENT 'admin_id من أنشأه',
|
|
`approved_by` INT UNSIGNED DEFAULT NULL COMMENT 'admin_id من اعتمده (فريق سيرو)',
|
|
`approved_at` TIMESTAMP DEFAULT NULL,
|
|
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_org_status` (`org_id`, `status`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- -----------------------------------------------------------------
|
|
-- 8. transit_stops — محطات الخط مع جيوفينس
|
|
-- -----------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `transit_stops` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`route_id` INT UNSIGNED NOT NULL,
|
|
`org_id` INT UNSIGNED NOT NULL,
|
|
`sequence` TINYINT NOT NULL COMMENT 'ترتيب المحطة في الخط (1، 2، 3…)',
|
|
`name_ar` VARCHAR(120) NOT NULL,
|
|
`name_en` VARCHAR(120) DEFAULT NULL,
|
|
`latitude` DECIMAL(10,7) NOT NULL,
|
|
`longitude` DECIMAL(10,7) NOT NULL,
|
|
`geofence_radius` SMALLINT NOT NULL DEFAULT 150 COMMENT 'نصف قطر الجيوفينس بالمتر',
|
|
`eta_offset_min` SMALLINT DEFAULT NULL COMMENT 'الإزاحة الزمنية عن وقت الانطلاق بالدقائق',
|
|
`is_major` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'محطة رئيسية (تظهر بارزة للطالب)',
|
|
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_route_seq` (`route_id`, `sequence`),
|
|
KEY `idx_org` (`org_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- -----------------------------------------------------------------
|
|
-- 9. transit_schedules — جداول انطلاق الخطوط
|
|
-- -----------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `transit_schedules` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`route_id` INT UNSIGNED NOT NULL,
|
|
`org_id` INT UNSIGNED NOT NULL,
|
|
`vehicle_id` INT UNSIGNED DEFAULT NULL COMMENT 'الباص المخصص لهذا الجدول (NULL = يُحدد يومياً)',
|
|
`driver_id` INT UNSIGNED DEFAULT NULL COMMENT 'السائق المخصص (NULL = يُحدد يومياً)',
|
|
`days_mask` TINYINT NOT NULL DEFAULT 62 COMMENT 'قناع الأيام: bit0=أحد…bit6=سبت (62=الأحد–الخميس)',
|
|
`departure_time` TIME NOT NULL,
|
|
`valid_from` DATE NOT NULL,
|
|
`valid_until` DATE DEFAULT NULL,
|
|
`is_active` TINYINT(1) NOT NULL DEFAULT 1,
|
|
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_route_active` (`route_id`, `is_active`),
|
|
KEY `idx_org` (`org_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- -----------------------------------------------------------------
|
|
-- 10. transit_trips — الرحلات اليومية الفعلية
|
|
-- -----------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `transit_trips` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`schedule_id` INT UNSIGNED NOT NULL,
|
|
`route_id` INT UNSIGNED NOT NULL,
|
|
`org_id` INT UNSIGNED NOT NULL,
|
|
`driver_id` INT UNSIGNED NOT NULL,
|
|
`vehicle_id` INT UNSIGNED DEFAULT NULL COMMENT 'NULL إن كان الجدول بلا باص مخصص',
|
|
`trip_date` DATE NOT NULL,
|
|
`status` ENUM('scheduled','started','completed','cancelled','no_show') NOT NULL DEFAULT 'scheduled',
|
|
`started_at` TIMESTAMP DEFAULT NULL,
|
|
`completed_at` TIMESTAMP DEFAULT NULL,
|
|
`delay_minutes` TINYINT DEFAULT 0,
|
|
`delay_reason` VARCHAR(300) DEFAULT NULL,
|
|
`current_stop_seq` TINYINT DEFAULT NULL COMMENT 'آخر محطة جيوفينس مرّ عليها الباص',
|
|
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uq_schedule_date` (`schedule_id`, `trip_date`),
|
|
KEY `idx_route_date` (`route_id`, `trip_date`),
|
|
KEY `idx_org_date` (`org_id`, `trip_date`),
|
|
KEY `idx_driver_date` (`driver_id`, `trip_date`),
|
|
KEY `idx_status` (`status`) -- cron_approaching_alerts يستعلم WHERE status='started' كل دقيقة
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- -----------------------------------------------------------------
|
|
-- 11. transit_enrollments — عضويات الطلاب/الموظفين في المؤسسة
|
|
-- -----------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `transit_enrollments` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`org_id` INT UNSIGNED NOT NULL,
|
|
`passenger_id` VARCHAR(100) DEFAULT NULL COMMENT 'معرّف في جدول passengers الرئيسي — NULL لصفوف الكشف قبل تفعيل الطالب',
|
|
`student_id` VARCHAR(50) NOT NULL COMMENT 'الرقم الجامعي/الوظيفي (encrypted)',
|
|
`member_name` VARCHAR(120) DEFAULT NULL COMMENT 'الاسم من كشف الجامعة',
|
|
`preferred_stop_id` INT UNSIGNED DEFAULT NULL COMMENT 'محطتي المفضلة',
|
|
`preferred_route_id` INT UNSIGNED DEFAULT NULL,
|
|
`verify_method` ENUM('api','roster_phone_match','roster_manual','manual_admin') NOT NULL DEFAULT 'roster_manual',
|
|
`status` ENUM('pending','active','suspended','expired') NOT NULL DEFAULT 'pending',
|
|
`verified_at` TIMESTAMP DEFAULT NULL,
|
|
`expires_at` DATE DEFAULT NULL COMMENT 'نهاية الفصل الدراسي',
|
|
`roster_id` INT UNSIGNED DEFAULT NULL COMMENT 'الكشف الذي جاءت منه العضوية',
|
|
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uq_org_passenger` (`org_id`, `passenger_id`),
|
|
KEY `idx_org_status` (`org_id`, `status`),
|
|
KEY `idx_passenger` (`passenger_id`),
|
|
KEY `idx_preferred_route` (`preferred_route_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- -----------------------------------------------------------------
|
|
-- 12. transit_rosters — سجلات استيراد كشوف الطلاب
|
|
-- -----------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `transit_rosters` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`org_id` INT UNSIGNED NOT NULL,
|
|
`uploaded_by` INT UNSIGNED NOT NULL COMMENT 'admin_id',
|
|
`filename` VARCHAR(200) DEFAULT NULL,
|
|
`total_rows` INT DEFAULT 0,
|
|
`matched_rows` INT DEFAULT 0 COMMENT 'طلاب طابق هاتفهم حساباً موجوداً',
|
|
`new_enrollments` INT DEFAULT 0,
|
|
`expired_count` INT DEFAULT 0 COMMENT 'طلاب تخرجوا وأُوقفت عضويتهم',
|
|
`semester` VARCHAR(30) DEFAULT NULL COMMENT 'مثال: 2026-S1',
|
|
`notes` VARCHAR(300) DEFAULT NULL,
|
|
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_org` (`org_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- -----------------------------------------------------------------
|
|
-- 13. transit_guardians — أولياء الأمور (للمدارس)
|
|
-- -----------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `transit_guardians` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`enrollment_id` INT UNSIGNED NOT NULL,
|
|
`org_id` INT UNSIGNED NOT NULL,
|
|
`guardian_passenger_id` VARCHAR(100) NOT NULL COMMENT 'معرّف ولي الأمر في passengers الرئيسي',
|
|
`relation` ENUM('father','mother','guardian') NOT NULL DEFAULT 'guardian',
|
|
`notify_depart` TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'إشعار عند انطلاق الباص',
|
|
`notify_board` TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'إشعار عند صعود الطفل',
|
|
`notify_arrive` TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'إشعار عند وصول الباص للمدرسة',
|
|
`notify_return` TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'إشعار عند العودة للبيت',
|
|
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uq_enrollment_guardian` (`enrollment_id`, `guardian_passenger_id`),
|
|
KEY `idx_org` (`org_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- -----------------------------------------------------------------
|
|
-- 14. transit_broadcasts — إعلانات المشرف للمشتركين
|
|
-- -----------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `transit_broadcasts` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`org_id` INT UNSIGNED NOT NULL,
|
|
`sent_by` INT UNSIGNED NOT NULL COMMENT 'admin_id',
|
|
`target_type` ENUM('all','route','role') NOT NULL DEFAULT 'all',
|
|
`target_id` INT UNSIGNED DEFAULT NULL COMMENT 'route_id عند target_type=route',
|
|
`title_ar` VARCHAR(200) DEFAULT NULL,
|
|
`body_ar` TEXT NOT NULL,
|
|
`fcm_topic` VARCHAR(150) DEFAULT NULL COMMENT 'الموضوع المستخدم للإرسال',
|
|
`sent_at` TIMESTAMP DEFAULT NULL,
|
|
`delivery_count` INT DEFAULT NULL,
|
|
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_org` (`org_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- -----------------------------------------------------------------
|
|
-- 15. transit_boardings — إثبات الصعود والنزول (مرحلة ثانية)
|
|
-- -----------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `transit_boardings` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`trip_id` INT UNSIGNED NOT NULL,
|
|
`enrollment_id` INT UNSIGNED NOT NULL,
|
|
`stop_id` INT UNSIGNED DEFAULT NULL,
|
|
`boarded_at` TIMESTAMP DEFAULT NULL,
|
|
`alighted_at` TIMESTAMP DEFAULT NULL,
|
|
`method` ENUM('geofence','qr','driver_tap') NOT NULL DEFAULT 'geofence',
|
|
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_trip` (`trip_id`),
|
|
KEY `idx_enrollment` (`enrollment_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- -----------------------------------------------------------------
|
|
-- 16. transit_otp_log — سجل OTPs لمشرفي الويب (تنظيف دوري)
|
|
-- -----------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `transit_otp_log` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`phone` VARCHAR(25) NOT NULL,
|
|
`otp_hash` VARCHAR(64) NOT NULL COMMENT 'sha256 للكود',
|
|
`expires_at` TIMESTAMP NOT NULL,
|
|
`used` TINYINT(1) NOT NULL DEFAULT 0,
|
|
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_phone` (`phone`),
|
|
KEY `idx_expires` (`expires_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
SET FOREIGN_KEY_CHECKS = 1;
|
|
|
|
-- -----------------------------------------------------------------
|
|
-- ترحيل (Migration) لقاعدة أُنشئت قبل 2026-07-12 — نفّذ مرة واحدة:
|
|
-- ALTER TABLE transit_enrollments MODIFY passenger_id VARCHAR(100) DEFAULT NULL
|
|
-- COMMENT 'معرّف في جدول passengers الرئيسي — NULL لصفوف الكشف قبل تفعيل الطالب';
|
|
-- ALTER TABLE transit_trips MODIFY vehicle_id INT UNSIGNED DEFAULT NULL
|
|
-- COMMENT 'NULL إن كان الجدول بلا باص مخصص';
|
|
-- ALTER TABLE transit_routes MODIFY status ENUM('draft','active','suspended','rejected') NOT NULL DEFAULT 'draft';
|
|
-- (بدونها: Admin/transit/route/approve.php بإجراء reject يفشل بخطأ SQL دائماً)
|
|
-- ALTER TABLE transit_drivers ADD KEY idx_main_driver (main_driver_id);
|
|
-- (بدونها: كل استعلام transitResolveDriver / driver/me.php = مسح جدول كامل)
|
|
-- ALTER TABLE transit_trips ADD KEY idx_status (status);
|
|
-- (بدونها: cron_approaching_alerts كل دقيقة = مسح جدول transit_trips كامل)
|
|
-- -----------------------------------------------------------------
|
|
|
|
-- -----------------------------------------------------------------
|
|
-- ملاحظات الـ Redis (keys تُكتب من PHP — ليست في SQL)
|
|
-- transit:trip:{trip_id}:pos → آخر موقع GPS للباص (Hash: lat,lng,ts)
|
|
-- transit:trip:{trip_id}:status → حالة الرحلة الحية
|
|
-- transit:otp:{phone} → OTP مشرف الويب (TTL 300s)
|
|
-- transit:session:{token_hash} → admin_id+org_id (TTL 86400s)
|
|
-- transit:driver_mode:{driver_id} → وضع السائق الحالي (bus/ride), TTL session
|
|
-- FCM Topics: transit_route_{route_id} ← كل مشتركي خط
|
|
-- -----------------------------------------------------------------
|