-- ============================================================================
-- Zuma — Database Schema
-- Target: MySQL 5.7+ / MariaDB 10.3+ (InnoDB, utf8mb4)
--
-- HOW TO USE
-- ----------
-- This file is a FALLBACK for environments where you cannot run
-- `php artisan migrate --seed` via SSH (e.g. very restricted cPanel hosts).
--
-- RECOMMENDED PATH: use SSH + the deploy.sh script + `php artisan migrate
-- --seed`, which builds this same schema AND populates it with demo data
-- (955 users, 100 restaurants, 200 products, etc.) via the seeders.
--
-- IF YOU USE THIS FILE INSTEAD:
--   1. Create an empty database + user in cPanel (MySQL Databases).
--   2. Import this file via phpMyAdmin ("Import" tab) or:
--        mysql -u DBUSER -p DBNAME < mrbismarck_schema.sql
--   3. This creates the SCHEMA ONLY (no demo data). To get demo data too,
--      you still need PHP CLI access to run: php artisan db:seed
--   4. Set DB_* values in your .env to match the database you created.
-- ============================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';
SET time_zone = '+03:00';

-- ============================================================================
-- LARAVEL CORE TABLES
-- ============================================================================

CREATE TABLE `migrations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `migration` varchar(255) NOT NULL,
  `batch` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `password_reset_tokens` (
  `email` varchar(255) NOT NULL,
  `token` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `sessions` (
  `id` varchar(255) NOT NULL,
  `user_id` bigint unsigned DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text,
  `payload` longtext NOT NULL,
  `last_activity` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `sessions_user_id_index` (`user_id`),
  KEY `sessions_last_activity_index` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `cache` (
  `key` varchar(255) NOT NULL,
  `value` mediumtext NOT NULL,
  `expiration` int NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `cache_locks` (
  `key` varchar(255) NOT NULL,
  `owner` varchar(255) NOT NULL,
  `expiration` int NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `jobs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `queue` varchar(255) NOT NULL,
  `payload` longtext NOT NULL,
  `attempts` tinyint unsigned NOT NULL,
  `reserved_at` int unsigned DEFAULT NULL,
  `available_at` int unsigned NOT NULL,
  `created_at` int unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `jobs_queue_index` (`queue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `job_batches` (
  `id` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `total_jobs` int NOT NULL,
  `pending_jobs` int NOT NULL,
  `failed_jobs` int NOT NULL,
  `failed_job_ids` longtext NOT NULL,
  `options` mediumtext,
  `cancelled_at` int DEFAULT NULL,
  `created_at` int NOT NULL,
  `finished_at` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `failed_jobs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(255) NOT NULL,
  `connection` text NOT NULL,
  `queue` text NOT NULL,
  `payload` longtext NOT NULL,
  `exception` longtext NOT NULL,
  `failed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- USERS
-- ============================================================================

CREATE TABLE `users` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `phone_verified_at` timestamp NULL DEFAULT NULL,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `country_code` varchar(3) NOT NULL DEFAULT 'KE',
  `currency` varchar(10) NOT NULL DEFAULT 'KES',
  `locale` varchar(10) NOT NULL DEFAULT 'en',
  `avatar` varchar(255) DEFAULT NULL,
  `referral_code` varchar(20) DEFAULT NULL,
  `referred_by` bigint unsigned DEFAULT NULL,
  `status` enum('active','suspended','pending_kyc','deleted') NOT NULL DEFAULT 'active',
  `kyc_status` enum('not_submitted','pending','approved','rejected') NOT NULL DEFAULT 'not_submitted',
  `kyc_documents` json DEFAULT NULL,
  `fcm_tokens` json DEFAULT NULL,
  `wallet_balance` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `escrow_balance` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `credit_score` decimal(8,2) NOT NULL DEFAULT '0.00',
  `google_id` varchar(255) DEFAULT NULL,
  `facebook_id` varchar(255) DEFAULT NULL,
  `remember_token` varchar(100) DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`),
  UNIQUE KEY `users_phone_unique` (`phone`),
  UNIQUE KEY `users_referral_code_unique` (`referral_code`),
  UNIQUE KEY `users_google_id_unique` (`google_id`),
  UNIQUE KEY `users_facebook_id_unique` (`facebook_id`),
  KEY `users_country_code_status_index` (`country_code`,`status`),
  KEY `users_referral_code_index` (`referral_code`),
  KEY `users_credit_score_index` (`credit_score`),
  KEY `users_referred_by_foreign` (`referred_by`),
  CONSTRAINT `users_referred_by_foreign` FOREIGN KEY (`referred_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `personal_access_tokens` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `tokenable_type` varchar(255) NOT NULL,
  `tokenable_id` bigint unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `token` varchar(64) NOT NULL,
  `abilities` text,
  `last_used_at` timestamp NULL DEFAULT NULL,
  `expires_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `personal_access_tokens_token_unique` (`token`),
  KEY `personal_access_tokens_tokenable_type_tokenable_id_index` (`tokenable_type`,`tokenable_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- SPATIE PERMISSION (roles & permissions)
-- ============================================================================

CREATE TABLE `permissions` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `guard_name` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `permissions_name_guard_name_unique` (`name`,`guard_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `roles` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `guard_name` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `roles_name_guard_name_unique` (`name`,`guard_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `model_has_permissions` (
  `permission_id` bigint unsigned NOT NULL,
  `model_type` varchar(255) NOT NULL,
  `model_id` bigint unsigned NOT NULL,
  PRIMARY KEY (`permission_id`,`model_id`,`model_type`),
  KEY `model_has_permissions_model_id_model_type_index` (`model_id`,`model_type`),
  CONSTRAINT `model_has_permissions_permission_id_foreign` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `model_has_roles` (
  `role_id` bigint unsigned NOT NULL,
  `model_type` varchar(255) NOT NULL,
  `model_id` bigint unsigned NOT NULL,
  PRIMARY KEY (`role_id`,`model_id`,`model_type`),
  KEY `model_has_roles_model_id_model_type_index` (`model_id`,`model_type`),
  CONSTRAINT `model_has_roles_role_id_foreign` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `role_has_permissions` (
  `permission_id` bigint unsigned NOT NULL,
  `role_id` bigint unsigned NOT NULL,
  PRIMARY KEY (`permission_id`,`role_id`),
  KEY `role_has_permissions_role_id_foreign` (`role_id`),
  CONSTRAINT `role_has_permissions_permission_id_foreign` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `role_has_permissions_role_id_foreign` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- SPATIE ACTIVITY LOG
-- ============================================================================

CREATE TABLE `activity_log` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `log_name` varchar(255) DEFAULT NULL,
  `description` text NOT NULL,
  `subject_type` varchar(255) DEFAULT NULL,
  `subject_id` bigint unsigned DEFAULT NULL,
  `event` varchar(255) DEFAULT NULL,
  `causer_type` varchar(255) DEFAULT NULL,
  `causer_id` bigint unsigned DEFAULT NULL,
  `properties` json DEFAULT NULL,
  `batch_uuid` varchar(36) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `activity_log_log_name_index` (`log_name`),
  KEY `activity_log_subject_type_subject_id_index` (`subject_type`,`subject_id`),
  KEY `activity_log_causer_type_causer_id_index` (`causer_type`,`causer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- RIDE HAILING
-- ============================================================================

CREATE TABLE `drivers` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `license_number` varchar(50) NOT NULL,
  `license_expiry` date NOT NULL,
  `vehicle_make` varchar(80) NOT NULL,
  `vehicle_model` varchar(80) NOT NULL,
  `vehicle_year` year NOT NULL,
  `plate_number` varchar(20) NOT NULL,
  `vehicle_color` varchar(40) NOT NULL,
  `vehicle_type` enum('sedan','hatchback','suv','van','boda','tuk_tuk','ev') NOT NULL DEFAULT 'sedan',
  `insurance_number` varchar(80) DEFAULT NULL,
  `insurance_expiry` date DEFAULT NULL,
  `status` enum('offline','online','on_ride','suspended') NOT NULL DEFAULT 'offline',
  `current_lat` decimal(10,8) DEFAULT NULL,
  `current_lng` decimal(11,8) DEFAULT NULL,
  `location_updated_at` timestamp NULL DEFAULT NULL,
  `rating` decimal(3,2) NOT NULL DEFAULT '5.00',
  `total_rides` int unsigned NOT NULL DEFAULT '0',
  `total_earnings` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `is_corporate` tinyint(1) NOT NULL DEFAULT '0',
  `is_women_preferred` tinyint(1) NOT NULL DEFAULT '0',
  `country_code` varchar(3) NOT NULL DEFAULT 'KE',
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `drivers_license_number_unique` (`license_number`),
  UNIQUE KEY `drivers_plate_number_unique` (`plate_number`),
  KEY `drivers_status_country_code_index` (`status`,`country_code`),
  KEY `drivers_current_lat_current_lng_index` (`current_lat`,`current_lng`),
  CONSTRAINT `drivers_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `rides` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `ride_ref` varchar(30) NOT NULL,
  `passenger_id` bigint unsigned NOT NULL,
  `driver_id` bigint unsigned DEFAULT NULL,
  `country_code` varchar(3) NOT NULL DEFAULT 'KE',
  `ride_type` enum('standard','premium','shared','corporate','school','emergency','women_only') NOT NULL DEFAULT 'standard',
  `status` enum('pending','driver_assigned','driver_arrived','in_progress','completed','cancelled') NOT NULL DEFAULT 'pending',
  `pickup_address` varchar(255) NOT NULL,
  `pickup_lat` decimal(10,8) NOT NULL,
  `pickup_lng` decimal(11,8) NOT NULL,
  `destination_address` varchar(255) NOT NULL,
  `destination_lat` decimal(10,8) NOT NULL,
  `destination_lng` decimal(11,8) NOT NULL,
  `stops` json DEFAULT NULL,
  `estimated_fare` decimal(18,4) NOT NULL,
  `actual_fare` decimal(18,4) DEFAULT NULL,
  `surge_multiplier` decimal(4,2) NOT NULL DEFAULT '1.00',
  `distance_km` decimal(8,2) DEFAULT NULL,
  `duration_seconds` int unsigned DEFAULT NULL,
  `platform_commission` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `driver_earnings` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `payment_method` enum('wallet','mpesa','airtel','mtn','cash') NOT NULL DEFAULT 'wallet',
  `payment_ref` varchar(60) DEFAULT NULL,
  `is_paid` tinyint(1) NOT NULL DEFAULT '0',
  `route_polyline` text,
  `passenger_rating` tinyint unsigned DEFAULT NULL,
  `driver_rating` tinyint unsigned DEFAULT NULL,
  `passenger_review` text,
  `driver_review` text,
  `driver_assigned_at` timestamp NULL DEFAULT NULL,
  `driver_arrived_at` timestamp NULL DEFAULT NULL,
  `started_at` timestamp NULL DEFAULT NULL,
  `completed_at` timestamp NULL DEFAULT NULL,
  `cancelled_at` timestamp NULL DEFAULT NULL,
  `cancel_reason` varchar(255) DEFAULT NULL,
  `is_scheduled` tinyint(1) NOT NULL DEFAULT '0',
  `scheduled_at` timestamp NULL DEFAULT NULL,
  `emergency_triggered` tinyint(1) NOT NULL DEFAULT '0',
  `emergency_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `rides_ride_ref_unique` (`ride_ref`),
  KEY `rides_passenger_id_status_index` (`passenger_id`,`status`),
  KEY `rides_driver_id_status_index` (`driver_id`,`status`),
  KEY `rides_country_code_status_created_at_index` (`country_code`,`status`,`created_at`),
  CONSTRAINT `rides_passenger_id_foreign` FOREIGN KEY (`passenger_id`) REFERENCES `users` (`id`),
  CONSTRAINT `rides_driver_id_foreign` FOREIGN KEY (`driver_id`) REFERENCES `drivers` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `ride_locations` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `ride_id` bigint unsigned NOT NULL,
  `lat` decimal(10,8) NOT NULL,
  `lng` decimal(11,8) NOT NULL,
  `speed_kmh` decimal(6,2) DEFAULT NULL,
  `recorded_at` timestamp NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ride_locations_ride_id_recorded_at_index` (`ride_id`,`recorded_at`),
  CONSTRAINT `ride_locations_ride_id_foreign` FOREIGN KEY (`ride_id`) REFERENCES `rides` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- FOOD DELIVERY
-- ============================================================================

CREATE TABLE `restaurants` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `owner_id` bigint unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `description` text,
  `logo` varchar(255) DEFAULT NULL,
  `images` json DEFAULT NULL,
  `phone` varchar(20) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `address` varchar(255) NOT NULL,
  `city` varchar(255) NOT NULL,
  `country_code` varchar(3) NOT NULL DEFAULT 'KE',
  `lat` decimal(10,8) NOT NULL,
  `lng` decimal(11,8) NOT NULL,
  `cuisines` json DEFAULT NULL,
  `opening_hours` json DEFAULT NULL,
  `delivery_fee` decimal(10,4) NOT NULL DEFAULT '0.0000',
  `min_order` decimal(10,4) NOT NULL DEFAULT '0.0000',
  `delivery_time_min` smallint unsigned NOT NULL DEFAULT '30',
  `delivery_time_max` smallint unsigned NOT NULL DEFAULT '45',
  `rating` decimal(3,2) NOT NULL DEFAULT '0.00',
  `total_orders` int unsigned NOT NULL DEFAULT '0',
  `total_reviews` int unsigned NOT NULL DEFAULT '0',
  `is_open` tinyint(1) NOT NULL DEFAULT '1',
  `is_featured` tinyint(1) NOT NULL DEFAULT '0',
  `is_approved` tinyint(1) NOT NULL DEFAULT '0',
  `status` enum('active','inactive','suspended') NOT NULL DEFAULT 'active',
  `commission_rate` decimal(5,2) NOT NULL DEFAULT '25.00',
  -- Extra columns used by RestaurantSeeder/Controllers
  `opening_time` time DEFAULT NULL,
  `closing_time` time DEFAULT NULL,
  `avg_prep_time_min` smallint unsigned DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `restaurants_slug_unique` (`slug`),
  KEY `restaurants_country_code_is_open_status_index` (`country_code`,`is_open`,`status`),
  KEY `restaurants_lat_lng_index` (`lat`,`lng`),
  CONSTRAINT `restaurants_owner_id_foreign` FOREIGN KEY (`owner_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `food_categories` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `restaurant_id` bigint unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `image` varchar(255) DEFAULT NULL,
  `sort_order` smallint unsigned NOT NULL DEFAULT '0',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `food_categories_restaurant_id_index` (`restaurant_id`),
  CONSTRAINT `food_categories_restaurant_id_foreign` FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `menu_items` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `restaurant_id` bigint unsigned NOT NULL,
  `food_category_id` bigint unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text,
  `image` varchar(255) DEFAULT NULL,
  `price` decimal(12,4) NOT NULL,
  `discount_price` decimal(12,4) DEFAULT NULL,
  `addons` json DEFAULT NULL,
  `allergens` json DEFAULT NULL,
  `is_vegetarian` tinyint(1) NOT NULL DEFAULT '0',
  `is_available` tinyint(1) NOT NULL DEFAULT '1',
  `is_featured` tinyint(1) NOT NULL DEFAULT '0',
  `rating` decimal(3,2) NOT NULL DEFAULT '0.00',
  `order_count` int unsigned NOT NULL DEFAULT '0',
  -- Extra columns used by RestaurantSeeder
  `currency` varchar(10) DEFAULT 'KES',
  `prep_time_min` smallint unsigned DEFAULT NULL,
  `calories` smallint unsigned DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `menu_items_restaurant_id_is_available_index` (`restaurant_id`,`is_available`),
  KEY `menu_items_food_category_id_foreign` (`food_category_id`),
  CONSTRAINT `menu_items_restaurant_id_foreign` FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `menu_items_food_category_id_foreign` FOREIGN KEY (`food_category_id`) REFERENCES `food_categories` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `food_orders` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `order_ref` varchar(30) NOT NULL,
  `customer_id` bigint unsigned NOT NULL,
  `restaurant_id` bigint unsigned NOT NULL,
  `driver_id` bigint unsigned DEFAULT NULL,
  `country_code` varchar(3) NOT NULL DEFAULT 'KE',
  `status` enum('pending','confirmed','preparing','ready_for_pickup','driver_assigned','picked_up','delivered','cancelled','refunded') NOT NULL DEFAULT 'pending',
  `items` json NOT NULL,
  `delivery_address` json NOT NULL,
  `subtotal` decimal(12,4) NOT NULL,
  `delivery_fee` decimal(10,4) NOT NULL DEFAULT '0.0000',
  `discount` decimal(10,4) NOT NULL DEFAULT '0.0000',
  `coupon_code` varchar(30) DEFAULT NULL,
  `total` decimal(12,4) NOT NULL,
  `platform_commission` decimal(10,4) NOT NULL DEFAULT '0.0000',
  `restaurant_earnings` decimal(10,4) NOT NULL DEFAULT '0.0000',
  `driver_earnings` decimal(10,4) NOT NULL DEFAULT '0.0000',
  `payment_method` enum('wallet','mpesa','airtel','mtn','cash') NOT NULL DEFAULT 'wallet',
  `payment_ref` varchar(60) DEFAULT NULL,
  `is_paid` tinyint(1) NOT NULL DEFAULT '0',
  `special_instructions` text,
  `is_group_order` tinyint(1) NOT NULL DEFAULT '0',
  `estimated_delivery_min` smallint unsigned DEFAULT NULL,
  `customer_rating` decimal(2,1) DEFAULT NULL,
  `driver_rating` decimal(2,1) DEFAULT NULL,
  `customer_review` text,
  `confirmed_at` timestamp NULL DEFAULT NULL,
  `ready_at` timestamp NULL DEFAULT NULL,
  `picked_up_at` timestamp NULL DEFAULT NULL,
  `delivered_at` timestamp NULL DEFAULT NULL,
  `cancelled_at` timestamp NULL DEFAULT NULL,
  `cancel_reason` varchar(255) DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `food_orders_order_ref_unique` (`order_ref`),
  KEY `food_orders_customer_id_status_index` (`customer_id`,`status`),
  KEY `food_orders_restaurant_id_status_index` (`restaurant_id`,`status`),
  KEY `food_orders_driver_id_status_index` (`driver_id`,`status`),
  KEY `food_orders_country_code_created_at_index` (`country_code`,`created_at`),
  CONSTRAINT `food_orders_customer_id_foreign` FOREIGN KEY (`customer_id`) REFERENCES `users` (`id`),
  CONSTRAINT `food_orders_restaurant_id_foreign` FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`),
  CONSTRAINT `food_orders_driver_id_foreign` FOREIGN KEY (`driver_id`) REFERENCES `drivers` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- (continued in part 2)
-- ============================================================================
-- E-COMMERCE
-- ============================================================================

CREATE TABLE `product_categories` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` bigint unsigned DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `image` varchar(255) DEFAULT NULL,
  `icon` varchar(255) DEFAULT NULL,
  `description` text,
  `sort_order` smallint unsigned NOT NULL DEFAULT '0',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_featured` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `product_categories_slug_unique` (`slug`),
  KEY `product_categories_parent_id_is_active_index` (`parent_id`,`is_active`),
  CONSTRAINT `product_categories_parent_id_foreign` FOREIGN KEY (`parent_id`) REFERENCES `product_categories` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `brands` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `logo` varchar(255) DEFAULT NULL,
  `country_of_origin` varchar(60) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `brands_slug_unique` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `vendors` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `business_name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `logo` varchar(255) DEFAULT NULL,
  `description` text,
  `phone` varchar(20) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `country_code` varchar(3) NOT NULL DEFAULT 'KE',
  `registration_number` varchar(80) DEFAULT NULL,
  `tax_pin` varchar(40) DEFAULT NULL,
  `rating` decimal(3,2) NOT NULL DEFAULT '0.00',
  `commission_rate` decimal(5,2) NOT NULL DEFAULT '8.00',
  `wallet_balance` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `status` enum('active','inactive','suspended','pending_approval') NOT NULL DEFAULT 'pending_approval',
  `is_verified` tinyint(1) NOT NULL DEFAULT '0',
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `vendors_slug_unique` (`slug`),
  KEY `vendors_country_code_status_index` (`country_code`,`status`),
  CONSTRAINT `vendors_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `products` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `vendor_id` bigint unsigned NOT NULL,
  `product_category_id` bigint unsigned NOT NULL,
  `brand_id` bigint unsigned DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `short_description` text,
  `description` longtext,
  `sku` varchar(60) DEFAULT NULL,
  `barcode` varchar(60) DEFAULT NULL,
  `price` decimal(12,4) NOT NULL,
  `compare_price` decimal(12,4) DEFAULT NULL,
  `cost_price` decimal(12,4) DEFAULT NULL,
  `stock_qty` int unsigned NOT NULL DEFAULT '0',
  `low_stock_alert` int unsigned NOT NULL DEFAULT '5',
  `track_inventory` tinyint(1) NOT NULL DEFAULT '1',
  `weight_kg` decimal(6,3) DEFAULT NULL,
  `dimensions` json DEFAULT NULL,
  `images` json DEFAULT NULL,
  `tags` json DEFAULT NULL,
  `specifications` json DEFAULT NULL,
  `variants` json DEFAULT NULL,
  `rating` decimal(3,2) NOT NULL DEFAULT '0.00',
  `review_count` int unsigned NOT NULL DEFAULT '0',
  `sales_count` int unsigned NOT NULL DEFAULT '0',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_featured` tinyint(1) NOT NULL DEFAULT '0',
  `is_digital` tinyint(1) NOT NULL DEFAULT '0',
  `allow_dropship` tinyint(1) NOT NULL DEFAULT '0',
  `country_code` varchar(3) NOT NULL DEFAULT 'KE',
  `affiliate_commission` decimal(5,2) DEFAULT NULL,
  -- Extra: currency used by ProductSeeder/controllers
  `currency` varchar(10) DEFAULT 'KES',
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `products_slug_unique` (`slug`),
  UNIQUE KEY `products_sku_unique` (`sku`),
  KEY `products_product_category_id_is_active_index` (`product_category_id`,`is_active`),
  KEY `products_vendor_id_is_active_index` (`vendor_id`,`is_active`),
  KEY `products_country_code_is_active_index` (`country_code`,`is_active`),
  KEY `products_brand_id_foreign` (`brand_id`),
  FULLTEXT KEY `products_name_short_description_fulltext` (`name`,`short_description`),
  CONSTRAINT `products_vendor_id_foreign` FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`id`) ON DELETE CASCADE,
  CONSTRAINT `products_product_category_id_foreign` FOREIGN KEY (`product_category_id`) REFERENCES `product_categories` (`id`),
  CONSTRAINT `products_brand_id_foreign` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `carts` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `items` json NOT NULL,
  `subtotal` decimal(12,4) NOT NULL DEFAULT '0.0000',
  `coupon_code` varchar(30) DEFAULT NULL,
  `discount` decimal(10,4) NOT NULL DEFAULT '0.0000',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `carts_user_id_unique` (`user_id`),
  CONSTRAINT `carts_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `coupons` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(30) NOT NULL,
  `name` varchar(255) NOT NULL,
  `type` enum('percentage','fixed') NOT NULL DEFAULT 'percentage',
  `value` decimal(10,4) NOT NULL,
  `min_order` decimal(10,4) NOT NULL DEFAULT '0.0000',
  `max_discount` decimal(10,4) DEFAULT NULL,
  `usage_limit` int unsigned DEFAULT NULL,
  `used_count` int unsigned NOT NULL DEFAULT '0',
  `per_user_limit` int unsigned NOT NULL DEFAULT '1',
  `starts_at` date DEFAULT NULL,
  `expires_at` date DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `country_code` varchar(3) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `coupons_code_unique` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `orders` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `order_ref` varchar(30) NOT NULL,
  `customer_id` bigint unsigned NOT NULL,
  `country_code` varchar(3) NOT NULL DEFAULT 'KE',
  `status` enum('pending','confirmed','processing','shipped','delivered','cancelled','refunded','return_requested') NOT NULL DEFAULT 'pending',
  `items` json NOT NULL,
  `shipping_address` json NOT NULL,
  `subtotal` decimal(12,4) NOT NULL,
  `shipping_fee` decimal(10,4) NOT NULL DEFAULT '0.0000',
  `discount` decimal(10,4) NOT NULL DEFAULT '0.0000',
  `coupon_code` varchar(30) DEFAULT NULL,
  `tax` decimal(10,4) NOT NULL DEFAULT '0.0000',
  `total` decimal(12,4) NOT NULL,
  `platform_commission` decimal(10,4) NOT NULL DEFAULT '0.0000',
  `payment_method` enum('wallet','mpesa','airtel','mtn','card','cash') NOT NULL DEFAULT 'wallet',
  `payment_ref` varchar(60) DEFAULT NULL,
  `is_paid` tinyint(1) NOT NULL DEFAULT '0',
  `tracking_number` varchar(40) DEFAULT NULL,
  `confirmed_at` timestamp NULL DEFAULT NULL,
  `shipped_at` timestamp NULL DEFAULT NULL,
  `delivered_at` timestamp NULL DEFAULT NULL,
  `cancelled_at` timestamp NULL DEFAULT NULL,
  `cancel_reason` varchar(255) DEFAULT NULL,
  `notes` text,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `orders_order_ref_unique` (`order_ref`),
  KEY `orders_customer_id_status_index` (`customer_id`,`status`),
  KEY `orders_country_code_created_at_index` (`country_code`,`created_at`),
  CONSTRAINT `orders_customer_id_foreign` FOREIGN KEY (`customer_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `product_reviews` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `product_id` bigint unsigned NOT NULL,
  `user_id` bigint unsigned NOT NULL,
  `order_id` bigint unsigned DEFAULT NULL,
  `rating` tinyint unsigned NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  `body` text,
  `images` json DEFAULT NULL,
  `is_verified_purchase` tinyint(1) NOT NULL DEFAULT '0',
  `is_approved` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `product_reviews_product_id_user_id_unique` (`product_id`,`user_id`),
  KEY `product_reviews_user_id_foreign` (`user_id`),
  KEY `product_reviews_order_id_foreign` (`order_id`),
  CONSTRAINT `product_reviews_product_id_foreign` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
  CONSTRAINT `product_reviews_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `product_reviews_order_id_foreign` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- WALLET / PAYMENTS
-- ============================================================================

CREATE TABLE `wallets` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `currency` varchar(10) NOT NULL DEFAULT 'KES',
  `country_code` varchar(3) NOT NULL DEFAULT 'KE',
  `balance` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `escrow_balance` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `pending_balance` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `total_credited` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `total_debited` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `is_frozen` tinyint(1) NOT NULL DEFAULT '0',
  `qr_code` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `wallets_user_id_currency_unique` (`user_id`,`currency`),
  CONSTRAINT `wallets_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `transactions` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `txn_ref` varchar(60) NOT NULL,
  `wallet_id` bigint unsigned NOT NULL,
  `user_id` bigint unsigned NOT NULL,
  `type` enum(
    'topup','withdrawal','transfer_in','transfer_out',
    'ride_payment','ride_earning','food_payment','food_earning',
    'order_payment','order_earning','loan_disbursement','loan_repayment',
    'freelance_payment','freelance_earning','rent_payment',
    'affiliate_commission','refund','platform_fee','escrow_hold','escrow_release'
  ) NOT NULL,
  `direction` enum('credit','debit') NOT NULL,
  `amount` decimal(18,4) NOT NULL,
  `fee` decimal(10,4) NOT NULL DEFAULT '0.0000',
  `balance_before` decimal(18,4) NOT NULL,
  `balance_after` decimal(18,4) NOT NULL,
  `currency` varchar(10) NOT NULL DEFAULT 'KES',
  `country_code` varchar(3) NOT NULL DEFAULT 'KE',
  `status` enum('pending','processing','completed','failed','reversed') NOT NULL DEFAULT 'pending',
  `payment_channel` enum('wallet','mpesa','airtel_money','mtn_momo','tigo_pesa','halopesa','card','bank','cash','internal') NOT NULL DEFAULT 'wallet',
  `external_ref` varchar(80) DEFAULT NULL,
  `narration` varchar(255) DEFAULT NULL,
  `metadata` json DEFAULT NULL,
  `related_id` bigint unsigned DEFAULT NULL,
  `related_type` varchar(60) DEFAULT NULL,
  `completed_at` timestamp NULL DEFAULT NULL,
  `failed_at` timestamp NULL DEFAULT NULL,
  `failure_reason` varchar(255) DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `transactions_txn_ref_unique` (`txn_ref`),
  KEY `transactions_user_id_type_created_at_index` (`user_id`,`type`,`created_at`),
  KEY `transactions_status_payment_channel_index` (`status`,`payment_channel`),
  KEY `transactions_country_code_created_at_index` (`country_code`,`created_at`),
  KEY `transactions_external_ref_index` (`external_ref`),
  KEY `transactions_wallet_id_foreign` (`wallet_id`),
  CONSTRAINT `transactions_wallet_id_foreign` FOREIGN KEY (`wallet_id`) REFERENCES `wallets` (`id`),
  CONSTRAINT `transactions_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `payment_attempts` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `transaction_id` bigint unsigned DEFAULT NULL,
  `user_id` bigint unsigned NOT NULL,
  `gateway` enum('mpesa_ke','mpesa_tz','mtn_momo','airtel','tigo','halopesa','stripe') NOT NULL,
  `amount` decimal(18,4) NOT NULL,
  `currency` varchar(10) NOT NULL DEFAULT 'KES',
  `phone` varchar(25) DEFAULT NULL,
  `gateway_ref` varchar(80) DEFAULT NULL,
  `checkout_request_id` varchar(80) DEFAULT NULL,
  `status` enum('initiated','pending','success','failed','timeout') NOT NULL DEFAULT 'initiated',
  `gateway_request` json DEFAULT NULL,
  `gateway_response` json DEFAULT NULL,
  `failure_code` varchar(20) DEFAULT NULL,
  `failure_message` varchar(255) DEFAULT NULL,
  `responded_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `payment_attempts_user_id_status_index` (`user_id`,`status`),
  KEY `payment_attempts_checkout_request_id_index` (`checkout_request_id`),
  KEY `payment_attempts_gateway_ref_index` (`gateway_ref`),
  KEY `payment_attempts_transaction_id_foreign` (`transaction_id`),
  CONSTRAINT `payment_attempts_transaction_id_foreign` FOREIGN KEY (`transaction_id`) REFERENCES `transactions` (`id`) ON DELETE SET NULL,
  CONSTRAINT `payment_attempts_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `escrow_accounts` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `escrow_ref` varchar(40) NOT NULL,
  `payer_id` bigint unsigned NOT NULL,
  `payee_id` bigint unsigned NOT NULL,
  `amount` decimal(18,4) NOT NULL,
  `currency` varchar(10) NOT NULL DEFAULT 'KES',
  `type` enum('freelance','ecommerce','real_estate','other') NOT NULL DEFAULT 'other',
  `status` enum('held','released','refunded','disputed') NOT NULL DEFAULT 'held',
  `related_id` bigint unsigned DEFAULT NULL,
  `related_type` varchar(60) DEFAULT NULL,
  `auto_release_at` timestamp NULL DEFAULT NULL,
  `released_at` timestamp NULL DEFAULT NULL,
  `release_reason` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `escrow_accounts_escrow_ref_unique` (`escrow_ref`),
  KEY `escrow_accounts_payer_id_status_index` (`payer_id`,`status`),
  KEY `escrow_accounts_payee_id_status_index` (`payee_id`,`status`),
  CONSTRAINT `escrow_accounts_payer_id_foreign` FOREIGN KEY (`payer_id`) REFERENCES `users` (`id`),
  CONSTRAINT `escrow_accounts_payee_id_foreign` FOREIGN KEY (`payee_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- (continued in part 3)
-- ============================================================================
-- JOBS & FREELANCE
-- ============================================================================

CREATE TABLE `jobs_board` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `employer_id` bigint unsigned NOT NULL,
  `title` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `company_name` varchar(255) DEFAULT NULL,
  `company_logo` varchar(255) DEFAULT NULL,
  `category` varchar(255) NOT NULL,
  `type` enum('full_time','part_time','contract','internship','remote') NOT NULL DEFAULT 'full_time',
  -- Extra: job_type alias used by JobSeeder/controllers
  `job_type` enum('full_time','part_time','contract','internship','remote') DEFAULT 'full_time',
  `location` varchar(255) NOT NULL,
  `country_code` varchar(3) NOT NULL DEFAULT 'KE',
  `is_remote` tinyint(1) NOT NULL DEFAULT '0',
  `salary_min` decimal(12,4) DEFAULT NULL,
  `salary_max` decimal(12,4) DEFAULT NULL,
  `salary_currency` varchar(10) NOT NULL DEFAULT 'KES',
  -- Extra: currency alias used by JobSeeder
  `currency` varchar(10) DEFAULT 'KES',
  `salary_period` enum('hourly','daily','monthly','annual') NOT NULL DEFAULT 'monthly',
  `description` longtext NOT NULL,
  `requirements` json DEFAULT NULL,
  `benefits` json DEFAULT NULL,
  `skills` json DEFAULT NULL,
  `experience_level` varchar(40) DEFAULT NULL,
  `deadline` date DEFAULT NULL,
  -- Extra: application_deadline alias used by JobSeeder
  `application_deadline` date DEFAULT NULL,
  `applicant_count` int unsigned NOT NULL DEFAULT '0',
  `views_count` int unsigned NOT NULL DEFAULT '0',
  `is_featured` tinyint(1) NOT NULL DEFAULT '0',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `status` enum('active','closed','draft') DEFAULT 'active',
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `jobs_board_slug_unique` (`slug`),
  KEY `jobs_board_country_code_is_active_index` (`country_code`,`is_active`),
  KEY `jobs_board_employer_id_is_active_index` (`employer_id`,`is_active`),
  FULLTEXT KEY `jobs_board_title_description_fulltext` (`title`,`description`),
  CONSTRAINT `jobs_board_employer_id_foreign` FOREIGN KEY (`employer_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `job_applications` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `job_id` bigint unsigned NOT NULL,
  `applicant_id` bigint unsigned NOT NULL,
  `cover_letter` text,
  `cv_url` varchar(255) DEFAULT NULL,
  `portfolio_links` json DEFAULT NULL,
  `status` enum('applied','shortlisted','interview','hired','rejected') NOT NULL DEFAULT 'applied',
  `expected_salary` decimal(12,4) DEFAULT NULL,
  `interview_at` timestamp NULL DEFAULT NULL,
  `employer_notes` text,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `job_applications_job_id_applicant_id_unique` (`job_id`,`applicant_id`),
  KEY `job_applications_applicant_id_foreign` (`applicant_id`),
  CONSTRAINT `job_applications_job_id_foreign` FOREIGN KEY (`job_id`) REFERENCES `jobs_board` (`id`) ON DELETE CASCADE,
  CONSTRAINT `job_applications_applicant_id_foreign` FOREIGN KEY (`applicant_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `freelance_gigs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `freelancer_id` bigint unsigned NOT NULL,
  `title` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `category` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `skills` json DEFAULT NULL,
  `budget_min` decimal(12,4) NOT NULL DEFAULT '0.0000',
  `budget_max` decimal(12,4) NOT NULL DEFAULT '0.0000',
  `budget_currency` varchar(10) NOT NULL DEFAULT 'KES',
  `budget_type` enum('fixed','hourly') NOT NULL DEFAULT 'fixed',
  -- Extra: price/currency/tags used directly by JobSeeder/FreelanceController
  `price` decimal(12,4) DEFAULT NULL,
  `currency` varchar(10) DEFAULT 'KES',
  `tags` json DEFAULT NULL,
  `delivery_days` smallint unsigned NOT NULL DEFAULT '7',
  `revisions` smallint unsigned DEFAULT NULL,
  `packages` json DEFAULT NULL,
  `images` json DEFAULT NULL,
  `rating` decimal(3,2) NOT NULL DEFAULT '0.00',
  `order_count` int unsigned NOT NULL DEFAULT '0',
  `orders_count` int unsigned NOT NULL DEFAULT '0',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_featured` tinyint(1) NOT NULL DEFAULT '0',
  `country_code` varchar(3) NOT NULL DEFAULT 'KE',
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `freelance_gigs_slug_unique` (`slug`),
  KEY `freelance_gigs_country_code_is_active_index` (`country_code`,`is_active`),
  FULLTEXT KEY `freelance_gigs_title_description_fulltext` (`title`,`description`),
  CONSTRAINT `freelance_gigs_freelancer_id_foreign` FOREIGN KEY (`freelancer_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `freelance_contracts` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `contract_ref` varchar(30) NOT NULL,
  `gig_id` bigint unsigned NOT NULL,
  `client_id` bigint unsigned NOT NULL,
  `freelancer_id` bigint unsigned NOT NULL,
  `title` varchar(255) NOT NULL,
  `amount` decimal(12,4) NOT NULL,
  `currency` varchar(10) NOT NULL DEFAULT 'KES',
  `status` enum('pending','active','delivered','approved','disputed','cancelled','completed','in_progress') NOT NULL DEFAULT 'pending',
  `deadline` date NOT NULL,
  `requirements` text,
  `deliverables` json DEFAULT NULL,
  `escrow_ref` varchar(40) DEFAULT NULL,
  `platform_fee` decimal(10,4) NOT NULL DEFAULT '0.0000',
  `freelancer_earnings` decimal(10,4) NOT NULL DEFAULT '0.0000',
  `started_at` timestamp NULL DEFAULT NULL,
  `delivered_at` timestamp NULL DEFAULT NULL,
  `completed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `freelance_contracts_contract_ref_unique` (`contract_ref`),
  KEY `freelance_contracts_gig_id_foreign` (`gig_id`),
  KEY `freelance_contracts_client_id_foreign` (`client_id`),
  KEY `freelance_contracts_freelancer_id_foreign` (`freelancer_id`),
  CONSTRAINT `freelance_contracts_gig_id_foreign` FOREIGN KEY (`gig_id`) REFERENCES `freelance_gigs` (`id`),
  CONSTRAINT `freelance_contracts_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `users` (`id`),
  CONSTRAINT `freelance_contracts_freelancer_id_foreign` FOREIGN KEY (`freelancer_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- CREDIT & LENDING
-- ============================================================================

CREATE TABLE `credit_scores` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `score` decimal(6,2) NOT NULL DEFAULT '0.00',
  `grade` enum('poor','fair','good','very_good','excellent') NOT NULL DEFAULT 'fair',
  `transaction_score` decimal(6,2) NOT NULL DEFAULT '0.00',
  `repayment_score` decimal(6,2) NOT NULL DEFAULT '0.00',
  `activity_score` decimal(6,2) NOT NULL DEFAULT '0.00',
  `behavioral_score` decimal(6,2) NOT NULL DEFAULT '0.00',
  `max_loan_amount` decimal(12,4) NOT NULL DEFAULT '0.0000',
  `max_bnpl_amount` decimal(12,4) NOT NULL DEFAULT '0.0000',
  `interest_rate` decimal(5,2) NOT NULL DEFAULT '15.00',
  `factors` json DEFAULT NULL,
  `last_computed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `credit_scores_user_id_unique` (`user_id`),
  CONSTRAINT `credit_scores_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `loans` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `loan_ref` varchar(30) NOT NULL,
  `user_id` bigint unsigned NOT NULL,
  `type` enum('micro_loan','personal','bnpl','emergency') NOT NULL DEFAULT 'micro_loan',
  `principal` decimal(12,4) NOT NULL,
  `interest_rate` decimal(5,2) NOT NULL,
  `tenure_days` smallint unsigned NOT NULL,
  `total_repayable` decimal(12,4) NOT NULL,
  `amount_repaid` decimal(12,4) NOT NULL DEFAULT '0.0000',
  `outstanding` decimal(12,4) NOT NULL,
  `currency` varchar(10) NOT NULL DEFAULT 'KES',
  `status` enum('pending','approved','disbursed','active','defaulted','completed','rejected') NOT NULL DEFAULT 'pending',
  `repayment_method` enum('mpesa','airtel','mtn','wallet') NOT NULL DEFAULT 'wallet',
  `repayment_phone` varchar(25) DEFAULT NULL,
  `related_order_id` bigint unsigned DEFAULT NULL,
  `due_date` date DEFAULT NULL,
  `disbursed_at` timestamp NULL DEFAULT NULL,
  `completed_at` timestamp NULL DEFAULT NULL,
  `rejection_reason` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `loans_loan_ref_unique` (`loan_ref`),
  KEY `loans_user_id_status_index` (`user_id`,`status`),
  KEY `loans_related_order_id_foreign` (`related_order_id`),
  CONSTRAINT `loans_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  CONSTRAINT `loans_related_order_id_foreign` FOREIGN KEY (`related_order_id`) REFERENCES `orders` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `loan_repayments` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `loan_id` bigint unsigned NOT NULL,
  `user_id` bigint unsigned NOT NULL,
  `amount` decimal(12,4) NOT NULL,
  `payment_ref` varchar(60) DEFAULT NULL,
  `channel` enum('mpesa','airtel','mtn','wallet') NOT NULL DEFAULT 'wallet',
  `status` enum('pending','confirmed','failed') NOT NULL DEFAULT 'pending',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `loan_repayments_user_id_foreign` (`user_id`),
  KEY `loan_repayments_loan_id_foreign` (`loan_id`),
  CONSTRAINT `loan_repayments_loan_id_foreign` FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`) ON DELETE CASCADE,
  CONSTRAINT `loan_repayments_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- REAL ESTATE
-- ============================================================================

CREATE TABLE `properties` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `owner_id` bigint unsigned NOT NULL,
  `title` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `description` text,
  `listing_type` enum('rent','sale','lease') NOT NULL DEFAULT 'rent',
  `property_type` enum('apartment','house','villa','land','office','warehouse','shop','studio','townhouse','commercial') NOT NULL DEFAULT 'apartment',
  `price` decimal(14,4) NOT NULL,
  `price_currency` varchar(10) NOT NULL DEFAULT 'KES',
  `price_period` enum('per_month','per_year','once') NOT NULL DEFAULT 'per_month',
  `address` varchar(255) NOT NULL,
  `city` varchar(255) NOT NULL,
  `neighborhood` varchar(255) DEFAULT NULL,
  `country_code` varchar(3) NOT NULL DEFAULT 'KE',
  `lat` decimal(10,8) DEFAULT NULL,
  `lng` decimal(11,8) DEFAULT NULL,
  `bedrooms` smallint unsigned DEFAULT NULL,
  `bathrooms` smallint unsigned DEFAULT NULL,
  `size_sqm` decimal(8,2) DEFAULT NULL,
  `amenities` json DEFAULT NULL,
  `images` json DEFAULT NULL,
  `is_furnished` tinyint(1) NOT NULL DEFAULT '0',
  `is_featured` tinyint(1) NOT NULL DEFAULT '0',
  `is_available` tinyint(1) NOT NULL DEFAULT '1',
  `is_verified` tinyint(1) NOT NULL DEFAULT '0',
  `status` enum('active','rented','sold','inactive') NOT NULL DEFAULT 'active',
  `agent_commission` decimal(5,2) DEFAULT NULL,
  `views_count` int unsigned NOT NULL DEFAULT '0',
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `properties_slug_unique` (`slug`),
  KEY `properties_country_code_listing_type_is_available_index` (`country_code`,`listing_type`,`is_available`),
  KEY `properties_lat_lng_index` (`lat`,`lng`),
  FULLTEXT KEY `properties_title_description_address_fulltext` (`title`,`description`,`address`),
  CONSTRAINT `properties_owner_id_foreign` FOREIGN KEY (`owner_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- COURIER
-- ============================================================================

CREATE TABLE `shipments` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `tracking_no` varchar(30) NOT NULL,
  `sender_id` bigint unsigned NOT NULL,
  `courier_id` bigint unsigned DEFAULT NULL,
  `sender_address` json NOT NULL,
  `recipient_address` json NOT NULL,
  `recipient_name` varchar(255) NOT NULL,
  `recipient_phone` varchar(20) NOT NULL,
  `weight_kg` decimal(6,3) NOT NULL,
  `dimensions` json DEFAULT NULL,
  `service_type` enum('standard','express','same_day','cross_border') NOT NULL DEFAULT 'standard',
  `declared_value` decimal(12,4) DEFAULT NULL,
  `shipping_fee` decimal(10,4) NOT NULL,
  `currency` varchar(10) NOT NULL DEFAULT 'KES',
  `status` enum('pending','picked_up','in_transit','out_for_delivery','delivered','failed','returned') NOT NULL DEFAULT 'pending',
  `tracking_events` json DEFAULT NULL,
  `country_code` varchar(3) NOT NULL DEFAULT 'KE',
  `is_cross_border` tinyint(1) NOT NULL DEFAULT '0',
  `picked_up_at` timestamp NULL DEFAULT NULL,
  `delivered_at` timestamp NULL DEFAULT NULL,
  `estimated_delivery_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `shipments_tracking_no_unique` (`tracking_no`),
  KEY `shipments_sender_id_status_index` (`sender_id`,`status`),
  KEY `shipments_courier_id_foreign` (`courier_id`),
  CONSTRAINT `shipments_sender_id_foreign` FOREIGN KEY (`sender_id`) REFERENCES `users` (`id`),
  CONSTRAINT `shipments_courier_id_foreign` FOREIGN KEY (`courier_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- AFFILIATE PROGRAM
-- ============================================================================

CREATE TABLE `affiliates` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `affiliate_code` varchar(20) NOT NULL,
  `tier` enum('bronze','silver','gold','platinum') NOT NULL DEFAULT 'bronze',
  `commission_rate` decimal(5,2) NOT NULL DEFAULT '5.00',
  `total_earnings` decimal(12,4) NOT NULL DEFAULT '0.0000',
  `pending_payout` decimal(12,4) NOT NULL DEFAULT '0.0000',
  `total_paid_out` decimal(12,4) NOT NULL DEFAULT '0.0000',
  `total_referrals` int unsigned NOT NULL DEFAULT '0',
  `successful_conversions` int unsigned NOT NULL DEFAULT '0',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `affiliates_affiliate_code_unique` (`affiliate_code`),
  CONSTRAINT `affiliates_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `affiliate_commissions` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `affiliate_id` bigint unsigned NOT NULL,
  `referred_user_id` bigint unsigned NOT NULL,
  `order_amount` decimal(12,4) NOT NULL,
  `commission_amount` decimal(10,4) NOT NULL,
  `type` enum('signup','first_ride','first_order','recurring') NOT NULL DEFAULT 'signup',
  `status` enum('pending','approved','paid','rejected') NOT NULL DEFAULT 'pending',
  `related_ref` varchar(60) DEFAULT NULL,
  `paid_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `affiliate_commissions_referred_user_id_foreign` (`referred_user_id`),
  KEY `affiliate_commissions_affiliate_id_foreign` (`affiliate_id`),
  CONSTRAINT `affiliate_commissions_affiliate_id_foreign` FOREIGN KEY (`affiliate_id`) REFERENCES `affiliates` (`id`) ON DELETE CASCADE,
  CONSTRAINT `affiliate_commissions_referred_user_id_foreign` FOREIGN KEY (`referred_user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- (continued in part 4)
-- ============================================================================
-- TRAVEL
-- ============================================================================

CREATE TABLE `hotels` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `owner_id` bigint unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `description` text,
  `address` varchar(255) NOT NULL,
  `city` varchar(255) NOT NULL,
  `country_code` varchar(3) NOT NULL DEFAULT 'KE',
  `lat` decimal(10,8) DEFAULT NULL,
  `lng` decimal(11,8) DEFAULT NULL,
  `star_rating` tinyint unsigned NOT NULL DEFAULT '3',
  `rating` decimal(3,2) NOT NULL DEFAULT '0.00',
  `amenities` json DEFAULT NULL,
  `images` json DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_featured` tinyint(1) NOT NULL DEFAULT '0',
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `hotels_slug_unique` (`slug`),
  KEY `hotels_country_code_is_active_index` (`country_code`,`is_active`),
  CONSTRAINT `hotels_owner_id_foreign` FOREIGN KEY (`owner_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `hotel_rooms` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint unsigned NOT NULL,
  `room_type` varchar(255) NOT NULL,
  `price_per_night` decimal(12,4) NOT NULL,
  `currency` varchar(10) NOT NULL DEFAULT 'KES',
  `max_occupancy` tinyint unsigned NOT NULL DEFAULT '2',
  `total_rooms` int unsigned NOT NULL DEFAULT '1',
  `amenities` json DEFAULT NULL,
  `images` json DEFAULT NULL,
  `is_available` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `hotel_rooms_hotel_id_foreign` (`hotel_id`),
  CONSTRAINT `hotel_rooms_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `hotel_bookings` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `booking_ref` varchar(30) NOT NULL,
  `hotel_id` bigint unsigned NOT NULL,
  `hotel_room_id` bigint unsigned NOT NULL,
  `user_id` bigint unsigned NOT NULL,
  `check_in` date NOT NULL,
  `check_out` date NOT NULL,
  `guests` tinyint unsigned NOT NULL DEFAULT '1',
  `total_amount` decimal(12,4) NOT NULL,
  `currency` varchar(10) NOT NULL DEFAULT 'KES',
  `status` enum('pending','confirmed','checked_in','checked_out','cancelled') NOT NULL DEFAULT 'pending',
  `is_paid` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `hotel_bookings_booking_ref_unique` (`booking_ref`),
  KEY `hotel_bookings_hotel_id_foreign` (`hotel_id`),
  KEY `hotel_bookings_hotel_room_id_foreign` (`hotel_room_id`),
  KEY `hotel_bookings_user_id_foreign` (`user_id`),
  CONSTRAINT `hotel_bookings_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`),
  CONSTRAINT `hotel_bookings_hotel_room_id_foreign` FOREIGN KEY (`hotel_room_id`) REFERENCES `hotel_rooms` (`id`),
  CONSTRAINT `hotel_bookings_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `tours` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `operator_id` bigint unsigned NOT NULL,
  `title` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `description` text,
  `destination` varchar(255) NOT NULL,
  `country_code` varchar(3) NOT NULL DEFAULT 'KE',
  `duration_days` smallint unsigned NOT NULL DEFAULT '1',
  `price_per_person` decimal(12,4) NOT NULL,
  `currency` varchar(10) NOT NULL DEFAULT 'KES',
  `itinerary` json DEFAULT NULL,
  `images` json DEFAULT NULL,
  `inclusions` json DEFAULT NULL,
  `max_group_size` smallint unsigned NOT NULL DEFAULT '10',
  `rating` decimal(3,2) NOT NULL DEFAULT '0.00',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tours_slug_unique` (`slug`),
  CONSTRAINT `tours_operator_id_foreign` FOREIGN KEY (`operator_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `tour_bookings` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `booking_ref` varchar(30) NOT NULL,
  `tour_id` bigint unsigned NOT NULL,
  `user_id` bigint unsigned NOT NULL,
  `tour_date` date NOT NULL,
  `participants` smallint unsigned NOT NULL DEFAULT '1',
  `total_amount` decimal(12,4) NOT NULL,
  `currency` varchar(10) NOT NULL DEFAULT 'KES',
  `status` enum('pending','confirmed','completed','cancelled') NOT NULL DEFAULT 'pending',
  `is_paid` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tour_bookings_booking_ref_unique` (`booking_ref`),
  KEY `tour_bookings_tour_id_foreign` (`tour_id`),
  KEY `tour_bookings_user_id_foreign` (`user_id`),
  CONSTRAINT `tour_bookings_tour_id_foreign` FOREIGN KEY (`tour_id`) REFERENCES `tours` (`id`),
  CONSTRAINT `tour_bookings_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- SOCIAL HUB
-- ============================================================================

CREATE TABLE `social_accounts` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `platform` enum('facebook','instagram','twitter','tiktok','linkedin','whatsapp') NOT NULL,
  `platform_user_id` varchar(255) NOT NULL,
  `username` varchar(255) DEFAULT NULL,
  `access_token` text,
  `refresh_token` text,
  `token_expires_at` timestamp NULL DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `social_accounts_user_id_platform_unique` (`user_id`,`platform`),
  CONSTRAINT `social_accounts_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `social_posts` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `platforms` json NOT NULL,
  `content` text NOT NULL,
  `media_urls` json DEFAULT NULL,
  `status` enum('draft','scheduled','published','failed') NOT NULL DEFAULT 'draft',
  `scheduled_at` timestamp NULL DEFAULT NULL,
  `published_at` timestamp NULL DEFAULT NULL,
  `platform_post_ids` json DEFAULT NULL,
  `analytics` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `social_posts_user_id_status_index` (`user_id`,`status`),
  CONSTRAINT `social_posts_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- FINANCE / INVOICES
-- ============================================================================

CREATE TABLE `invoices` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `invoice_no` varchar(30) NOT NULL,
  `user_id` bigint unsigned NOT NULL,
  `client_name` varchar(255) NOT NULL,
  `client_email` varchar(255) DEFAULT NULL,
  `client_phone` varchar(255) DEFAULT NULL,
  `line_items` json NOT NULL,
  `subtotal` decimal(12,4) NOT NULL,
  `tax_rate` decimal(5,2) NOT NULL DEFAULT '0.00',
  `tax_amount` decimal(10,4) NOT NULL DEFAULT '0.0000',
  `total` decimal(12,4) NOT NULL,
  `currency` varchar(10) NOT NULL DEFAULT 'KES',
  `status` enum('draft','sent','paid','overdue','cancelled') NOT NULL DEFAULT 'draft',
  `due_date` date DEFAULT NULL,
  `paid_at` timestamp NULL DEFAULT NULL,
  `notes` text,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `invoices_invoice_no_unique` (`invoice_no`),
  KEY `invoices_user_id_status_index` (`user_id`,`status`),
  CONSTRAINT `invoices_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `partner_applications` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `full_name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(25) NOT NULL,
  `country_code` varchar(3) NOT NULL,
  `partner_type` enum('driver','restaurant','vendor','property_owner','agent','employer','freelancer','affiliate','courier','hotel_tour_operator') NOT NULL,
  `business_name` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `services` json DEFAULT NULL,
  `message` text,
  `status` enum('new','contacted','approved','rejected') NOT NULL DEFAULT 'new',
  `admin_notes` text,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `partner_applications_status_country_code_index` (`status`,`country_code`),
  KEY `partner_applications_partner_type_index` (`partner_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `platform_settings` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `key` varchar(255) NOT NULL,
  `value` text,
  `type` varchar(20) NOT NULL DEFAULT 'string',
  `group` varchar(60) NOT NULL DEFAULT 'general',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `platform_settings_key_unique` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- SEED: ROLES & PERMISSIONS (Spatie Permission)
-- Mirrors database/seeders/RolesOnlySeeder.php and DatabaseSeeder.php
-- ============================================================================

INSERT INTO `roles` (`name`,`guard_name`,`created_at`,`updated_at`) VALUES
('super_admin','web',NOW(),NOW()),
('country_director','web',NOW(),NOW()),
('business_manager','web',NOW(),NOW()),
('finance_manager','web',NOW(),NOW()),
('customer','web',NOW(),NOW()),
('driver','web',NOW(),NOW()),
('vendor','web',NOW(),NOW()),
('merchant','web',NOW(),NOW()),
('restaurant','web',NOW(),NOW()),
('property_owner','web',NOW(),NOW()),
('agent','web',NOW(),NOW()),
('employer','web',NOW(),NOW()),
('freelancer','web',NOW(),NOW());

INSERT INTO `permissions` (`name`,`guard_name`,`created_at`,`updated_at`) VALUES
('rides.view','web',NOW(),NOW()),
('rides.create','web',NOW(),NOW()),
('rides.manage','web',NOW(),NOW()),
('rides.cancel','web',NOW(),NOW()),
('food.view','web',NOW(),NOW()),
('food.order','web',NOW(),NOW()),
('food.manage_restaurant','web',NOW(),NOW()),
('ecommerce.view','web',NOW(),NOW()),
('ecommerce.order','web',NOW(),NOW()),
('ecommerce.manage_products','web',NOW(),NOW()),
('jobs.view','web',NOW(),NOW()),
('jobs.apply','web',NOW(),NOW()),
('jobs.post','web',NOW(),NOW()),
('jobs.manage','web',NOW(),NOW()),
('freelance.view','web',NOW(),NOW()),
('freelance.bid','web',NOW(),NOW()),
('freelance.manage_gigs','web',NOW(),NOW()),
('wallet.view','web',NOW(),NOW()),
('wallet.transact','web',NOW(),NOW()),
('wallet.withdraw','web',NOW(),NOW()),
('property.view','web',NOW(),NOW()),
('property.book','web',NOW(),NOW()),
('property.manage','web',NOW(),NOW()),
('courier.view','web',NOW(),NOW()),
('courier.create','web',NOW(),NOW()),
('credit.view','web',NOW(),NOW()),
('credit.apply_loan','web',NOW(),NOW()),
('affiliate.view','web',NOW(),NOW()),
('affiliate.manage','web',NOW(),NOW()),
('admin.dashboard','web',NOW(),NOW()),
('admin.users.manage','web',NOW(),NOW()),
('admin.reports.view','web',NOW(),NOW()),
('admin.finance.manage','web',NOW(),NOW()),
('admin.country.manage','web',NOW(),NOW());

-- super_admin gets ALL permissions
INSERT INTO `role_has_permissions` (`permission_id`, `role_id`)
SELECT p.id, r.id FROM permissions p, roles r WHERE r.name = 'super_admin';

-- customer
INSERT INTO `role_has_permissions` (`permission_id`, `role_id`)
SELECT p.id, r.id FROM permissions p, roles r
WHERE r.name = 'customer' AND p.name IN (
  'rides.view','rides.create','rides.cancel','food.view','food.order',
  'ecommerce.view','ecommerce.order','jobs.view','jobs.apply',
  'freelance.view','freelance.bid','wallet.view','wallet.transact','wallet.withdraw',
  'property.view','property.book','courier.view','courier.create',
  'credit.view','credit.apply_loan','affiliate.view'
);

-- driver
INSERT INTO `role_has_permissions` (`permission_id`, `role_id`)
SELECT p.id, r.id FROM permissions p, roles r
WHERE r.name = 'driver' AND p.name IN ('rides.view','rides.manage','wallet.view','wallet.transact','wallet.withdraw');

-- vendor & merchant
INSERT INTO `role_has_permissions` (`permission_id`, `role_id`)
SELECT p.id, r.id FROM permissions p, roles r
WHERE r.name IN ('vendor','merchant') AND p.name IN ('ecommerce.view','ecommerce.manage_products','wallet.view','wallet.transact','wallet.withdraw');

-- restaurant
INSERT INTO `role_has_permissions` (`permission_id`, `role_id`)
SELECT p.id, r.id FROM permissions p, roles r
WHERE r.name = 'restaurant' AND p.name IN ('food.view','food.manage_restaurant','wallet.view','wallet.transact','wallet.withdraw');

-- property_owner & agent
INSERT INTO `role_has_permissions` (`permission_id`, `role_id`)
SELECT p.id, r.id FROM permissions p, roles r
WHERE r.name IN ('property_owner','agent') AND p.name IN ('property.view','property.manage','wallet.view','wallet.transact','wallet.withdraw');

-- employer
INSERT INTO `role_has_permissions` (`permission_id`, `role_id`)
SELECT p.id, r.id FROM permissions p, roles r
WHERE r.name = 'employer' AND p.name IN ('jobs.view','jobs.post','jobs.manage','wallet.view','wallet.transact');

-- freelancer
INSERT INTO `role_has_permissions` (`permission_id`, `role_id`)
SELECT p.id, r.id FROM permissions p, roles r
WHERE r.name = 'freelancer' AND p.name IN ('freelance.view','freelance.manage_gigs','wallet.view','wallet.transact','wallet.withdraw');

-- country_director / business_manager / finance_manager — admin permissions
INSERT INTO `role_has_permissions` (`permission_id`, `role_id`)
SELECT p.id, r.id FROM permissions p, roles r
WHERE r.name = 'country_director' AND p.name IN ('admin.dashboard','admin.users.manage','admin.reports.view','admin.finance.manage');

INSERT INTO `role_has_permissions` (`permission_id`, `role_id`)
SELECT p.id, r.id FROM permissions p, roles r
WHERE r.name = 'business_manager' AND p.name IN ('admin.dashboard','admin.reports.view');

INSERT INTO `role_has_permissions` (`permission_id`, `role_id`)
SELECT p.id, r.id FROM permissions p, roles r
WHERE r.name = 'finance_manager' AND p.name IN ('admin.dashboard','admin.finance.manage','admin.reports.view');

-- ============================================================================
-- SEED: Mark all 7 app migrations as already run (batch 1)
-- so `php artisan migrate` won't try to re-create these tables.
-- NOTE: If you ALSO plan to run `php artisan migrate` afterwards (e.g. to
-- let Sanctum/Spatie/Telescope register their own migration rows), that's
-- fine — those packages' tables already exist above and Laravel will just
-- skip files it doesn't recognize as pending. If in doubt, skip this INSERT
-- and instead run `php artisan migrate:status` then `php artisan migrate`
-- — Laravel will only run files not already listed here.
-- ============================================================================

INSERT INTO `migrations` (`migration`, `batch`) VALUES
('2023_12_31_000000_create_permission_tables', 1),
('2024_01_01_000000_create_framework_tables', 1),
('2024_01_01_000001_create_users_table', 1),
('2024_01_01_000002_create_rides_tables', 1),
('2024_01_01_000003_create_food_tables', 1),
('2024_01_01_000004_create_ecommerce_tables', 1),
('2024_01_01_000005_create_wallet_tables', 1),
('2024_01_01_000006_create_business_tables', 1),
('2024_01_01_000007_create_hotels_travel_social_tables', 1),
('2024_01_01_000008_create_partner_applications_table', 1),
('2024_01_01_000009_create_activity_log_table', 1),
('2024_01_01_000010_create_personal_access_tokens_table', 1),
('2024_01_01_000011_create_platform_settings_table', 1);

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================================
-- END OF SCHEMA
--
-- NEXT STEPS:
--   1. Set your .env DB_* credentials to match this database.
--   2. (Optional, needs SSH/PHP CLI) Run `php artisan db:seed` to populate
--      demo data: 955 users, 100 restaurants/menus, 200 products, 200 jobs +
--      100 gigs, 200 properties, 100 hotels + 50 tours, plus rides.
--   3. Login as: admin@zuma.app / Password@123 (super admin)
-- ============================================================================