-- ============================================================
--  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.
