-- ============================================================ -- Kaya App - Inferred Database Schema -- Generated from API source analysis -- ============================================================ CREATE DATABASE IF NOT EXISTS kayang_kaya CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE kayang_kaya; -- ------------------------------------------------------- -- USERS -- ------------------------------------------------------- CREATE TABLE IF NOT EXISTS users ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, fullName VARCHAR(200) NOT NULL, email VARCHAR(200) DEFAULT NULL, phone VARCHAR(20) NOT NULL UNIQUE, password VARCHAR(255) NOT NULL, image_url VARCHAR(500) DEFAULT NULL, is_verified TINYINT(1) NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB; -- ------------------------------------------------------- -- OTP CODES -- ------------------------------------------------------- CREATE TABLE IF NOT EXISTS otp_codes ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, phone VARCHAR(20) NOT NULL UNIQUE, code VARCHAR(10) NOT NULL, is_used TINYINT(1) NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_phone_code (phone, code) ) ENGINE=InnoDB; -- ------------------------------------------------------- -- PACKAGES (orders) -- ------------------------------------------------------- CREATE TABLE IF NOT EXISTS packages ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, rider INT UNSIGNED DEFAULT NULL, from_location VARCHAR(500) NOT NULL, to_location VARCHAR(500) NOT NULL, package_category VARCHAR(100) DEFAULT NULL, package_description TEXT DEFAULT NULL, price DECIMAL(12,2) NOT NULL DEFAULT 0, payment_method VARCHAR(50) DEFAULT NULL, pickup_lat DECIMAL(11,8) DEFAULT NULL, pickup_lon DECIMAL(11,8) DEFAULT NULL, dropoff_lat DECIMAL(11,8) DEFAULT NULL, dropoff_lon DECIMAL(11,8) DEFAULT NULL, status VARCHAR(50) NOT NULL DEFAULT 'pending', pickup_time DATETIME DEFAULT NULL, dropoff_time DATETIME DEFAULT NULL, ride_rating TINYINT DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_user_id (user_id), INDEX idx_rider (rider), INDEX idx_status (status) ) ENGINE=InnoDB; -- ------------------------------------------------------- -- PACKAGE CONTACTS -- ------------------------------------------------------- CREATE TABLE IF NOT EXISTS package_contacts ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, package_id INT UNSIGNED NOT NULL, sender_phone VARCHAR(20) DEFAULT NULL, recipient_phone VARCHAR(20) DEFAULT NULL, INDEX idx_package_id (package_id) ) ENGINE=InnoDB; -- ------------------------------------------------------- -- DYNAMIC STOPS -- ------------------------------------------------------- CREATE TABLE IF NOT EXISTS dynamic_stops ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, package_id INT UNSIGNED NOT NULL, identifier VARCHAR(100) DEFAULT NULL, location VARCHAR(500) DEFAULT NULL, is_new TINYINT(1) NOT NULL DEFAULT 0, INDEX idx_package_id (package_id) ) ENGINE=InnoDB; -- ------------------------------------------------------- -- RIDER DOCUMENTS -- ------------------------------------------------------- CREATE TABLE IF NOT EXISTS rider_documents ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, rider_id INT UNSIGNED NOT NULL UNIQUE, nin_number VARCHAR(20) DEFAULT NULL, home_address VARCHAR(500) DEFAULT NULL, nin_certificate VARCHAR(500) DEFAULT NULL, vehicle_type VARCHAR(100) DEFAULT NULL, plate_number VARCHAR(30) DEFAULT NULL, license_image VARCHAR(500) DEFAULT NULL, rating DECIMAL(3,2) NOT NULL DEFAULT 0.00, latitude DECIMAL(11,8) DEFAULT NULL, longitude DECIMAL(11,8) DEFAULT NULL, isAvailable TINYINT(1) NOT NULL DEFAULT 0, INDEX idx_rider_id (rider_id), INDEX idx_available (isAvailable) ) ENGINE=InnoDB; -- ------------------------------------------------------- -- RIDE REQUESTS -- ------------------------------------------------------- CREATE TABLE IF NOT EXISTS ride_requests ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, passenger_id INT UNSIGNED NOT NULL UNIQUE, rider_id INT UNSIGNED DEFAULT NULL, passenger_fare DECIMAL(12,2) DEFAULT NULL, rider_fare DECIMAL(12,2) DEFAULT NULL, status VARCHAR(50) NOT NULL DEFAULT 'pending', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_passenger (passenger_id), INDEX idx_rider (rider_id) ) ENGINE=InnoDB; -- ------------------------------------------------------- -- MESSAGES (chat) -- ------------------------------------------------------- CREATE TABLE IF NOT EXISTS messages ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, package_id INT UNSIGNED NOT NULL, sender_id INT UNSIGNED NOT NULL, receiver_id INT UNSIGNED NOT NULL, content TEXT DEFAULT NULL, file_url VARCHAR(500) DEFAULT NULL, file_type VARCHAR(50) DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_package_id (package_id), INDEX idx_sender (sender_id) ) ENGINE=InnoDB; -- ------------------------------------------------------- -- NOTIFICATIONS -- ------------------------------------------------------- CREATE TABLE IF NOT EXISTS notifications ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, type VARCHAR(100) NOT NULL, event VARCHAR(100) DEFAULT NULL, status VARCHAR(50) DEFAULT NULL, title VARCHAR(200) DEFAULT NULL, description TEXT DEFAULT NULL, action_text VARCHAR(100) DEFAULT NULL, role VARCHAR(20) NOT NULL DEFAULT 'user', is_read TINYINT(1) NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_user_id (user_id), INDEX idx_role (role) ) ENGINE=InnoDB; -- ------------------------------------------------------- -- PASSENGER WALLETS -- ------------------------------------------------------- CREATE TABLE IF NOT EXISTS passenger_wallets ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL UNIQUE, balance DECIMAL(14,2) NOT NULL DEFAULT 0.00, INDEX idx_user_id (user_id) ) ENGINE=InnoDB; -- ------------------------------------------------------- -- RIDER WALLETS -- ------------------------------------------------------- CREATE TABLE IF NOT EXISTS rider_wallets ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, rider_id INT UNSIGNED NOT NULL UNIQUE, balance DECIMAL(14,2) NOT NULL DEFAULT 0.00, INDEX idx_rider_id (rider_id) ) ENGINE=InnoDB; -- ------------------------------------------------------- -- TRANSACTIONS (passenger) -- ------------------------------------------------------- CREATE TABLE IF NOT EXISTS transactions ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, type ENUM('credit','debit') NOT NULL, amount DECIMAL(14,2) NOT NULL, reference VARCHAR(200) DEFAULT NULL, reference_id VARCHAR(200) DEFAULT NULL, title VARCHAR(200) DEFAULT NULL, description TEXT DEFAULT NULL, status VARCHAR(50) NOT NULL DEFAULT 'success', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_user_id (user_id) ) ENGINE=InnoDB; -- ------------------------------------------------------- -- RIDER TRANSACTIONS -- ------------------------------------------------------- CREATE TABLE IF NOT EXISTS rider_transactions ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, rider_id INT UNSIGNED NOT NULL, type ENUM('credit','debit') NOT NULL, amount DECIMAL(14,2) NOT NULL, reference VARCHAR(200) DEFAULT NULL, reference_id VARCHAR(200) DEFAULT NULL, title VARCHAR(200) DEFAULT NULL, description TEXT DEFAULT NULL, status VARCHAR(50) NOT NULL DEFAULT 'success', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_rider_id (rider_id) ) ENGINE=InnoDB; -- ------------------------------------------------------- -- WITHDRAWAL REQUESTS -- ------------------------------------------------------- CREATE TABLE IF NOT EXISTS withdrawal_requests ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, rider_id INT UNSIGNED NOT NULL, amount DECIMAL(14,2) NOT NULL, status VARCHAR(50) NOT NULL DEFAULT 'pending', bank_code VARCHAR(20) DEFAULT NULL, account_number VARCHAR(20) DEFAULT NULL, account_name VARCHAR(200) DEFAULT NULL, transfer_ref VARCHAR(200) DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_rider_id (rider_id) ) ENGINE=InnoDB; -- ------------------------------------------------------- -- SAVED LOCATIONS -- ------------------------------------------------------- CREATE TABLE IF NOT EXISTS saved_locations ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, name VARCHAR(100) DEFAULT NULL, label VARCHAR(100) DEFAULT NULL, address VARCHAR(500) NOT NULL, type VARCHAR(50) DEFAULT NULL, lat DECIMAL(11,8) DEFAULT NULL, lng DECIMAL(11,8) DEFAULT NULL, INDEX idx_user_id (user_id) ) ENGINE=InnoDB; -- ------------------------------------------------------- -- PASSWORD RESETS -- ------------------------------------------------------- CREATE TABLE IF NOT EXISTS password_resets ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, phone VARCHAR(20) NOT NULL, otp_code VARCHAR(10) NOT NULL, expires_at DATETIME NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_phone (phone) ) ENGINE=InnoDB; -- ------------------------------------------------------- -- RIDER BANK DETAILS -- ------------------------------------------------------- CREATE TABLE IF NOT EXISTS rider_bank_details ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, rider_id INT UNSIGNED NOT NULL UNIQUE, bvn VARCHAR(20) DEFAULT NULL, bank_name VARCHAR(100) DEFAULT NULL, account_number VARCHAR(20) DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_rider_id (rider_id) ) ENGINE=InnoDB; -- ------------------------------------------------------- -- RIDER NOTIFICATIONS -- (separate from passenger notifications, used by rider/functions.php) -- ------------------------------------------------------- CREATE TABLE IF NOT EXISTS rider_notifications ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, type VARCHAR(100) NOT NULL, event VARCHAR(100) DEFAULT NULL, status VARCHAR(50) DEFAULT NULL, title VARCHAR(200) DEFAULT NULL, description TEXT DEFAULT NULL, action_text VARCHAR(100) DEFAULT NULL, is_read TINYINT(1) NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_user_id (user_id) ) ENGINE=InnoDB; -- ------------------------------------------------------- -- SCHEMA FIXES / NOTES -- ------------------------------------------------------- -- The notifications table needs BOTH schemas to be compatible: -- notify.php uses: (user_id, type, title, description, action_text, role) -- functions.php uses: (user_id, type, event, status, title, description, action_text) -- Run this ALTER to add missing columns to notifications: ALTER TABLE notifications ADD COLUMN IF NOT EXISTS event VARCHAR(100) DEFAULT NULL AFTER type, ADD COLUMN IF NOT EXISTS status VARCHAR(50) DEFAULT NULL AFTER event, ADD COLUMN IF NOT EXISTS role VARCHAR(20) NOT NULL DEFAULT 'user' AFTER action_text; -- saved_locations: code uses (user_id, name, address, type) -- Schema used (label, lat, lng) — align to what the code actually inserts: ALTER TABLE saved_locations ADD COLUMN IF NOT EXISTS name VARCHAR(100) DEFAULT NULL, ADD COLUMN IF NOT EXISTS type VARCHAR(50) DEFAULT NULL; -- packages: rider/get-orders.php searched delivery_id which doesn't exist as a column -- The fix uses CAST(id AS CHAR) instead — no schema change needed.