-- ============================================================
-- سودانا v22 — COMPLETE SCHEMA (Code-Aligned)
-- Generated from: actual PHP code analysis
-- Apply to: saiadmin_sudb999
-- ============================================================
-- ORDER MATTERS: run top to bottom
-- ============================================================

SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';

-- ══════════════════════════════════════════════════════════════
-- TIER 1: CORE PLATFORM (no dependencies)
-- ══════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS users (
  id                    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name                  VARCHAR(150) NOT NULL,
  phone                 VARCHAR(30)  UNIQUE,
  email                 VARCHAR(200) UNIQUE,
  password_hash         VARCHAR(255),
  user_type             ENUM('user','doctor','academic','service_provider','admin') DEFAULT 'user',
  -- Location
  state                 VARCHAR(100),
  city                  VARCHAR(100),
  neighborhood          VARCHAR(150),
  neighborhood_id       INT UNSIGNED NULL,
  -- Profile
  education_level       VARCHAR(50),
  age_group             VARCHAR(20),
  goals                 VARCHAR(200),
  bio                   TEXT,
  profile_image         VARCHAR(500),
  -- Learning
  learning_track        ENUM('foundation','growth','leadership') DEFAULT 'foundation',
  lessons_done          INT UNSIGNED DEFAULT 0,
  weekly_lessons_done   INT UNSIGNED DEFAULT 0,
  last_lesson_week      TINYINT UNSIGNED DEFAULT 0,
  -- Gamification
  points_learn          INT UNSIGNED DEFAULT 0,
  points_trade          INT UNSIGNED DEFAULT 0,
  points_community      INT UNSIGNED DEFAULT 0,
  points_family         INT UNSIGNED DEFAULT 0,
  rank_title            VARCHAR(100) DEFAULT 'مبتدئ',
  -- Subscription (used by Gateway + ControlEngine)
  subscription_tier     VARCHAR(20)  NOT NULL DEFAULT 'free',
  plan_key              VARCHAR(50)  NOT NULL DEFAULT 'free',
  plan_expires_at       DATETIME NULL,
  subscription_expires_at DATETIME NULL,
  -- Provider (used by StepController.fetchProviders + Gateway)
  provider_verified     TINYINT(1)  DEFAULT 0,
  provider_specialty    VARCHAR(200),
  provider_plan         VARCHAR(50)  DEFAULT 'basic',
  avg_rating            DECIMAL(3,2) DEFAULT 0.00,
  -- Notifications
  fcm_token             VARCHAR(512),
  notify_pulse          TINYINT(1)  DEFAULT 1,
  notify_family         TINYINT(1)  DEFAULT 1,
  notify_market         TINYINT(1)  DEFAULT 1,
  notify_academy        TINYINT(1)  DEFAULT 1,
  -- Flags
  has_children          TINYINT(1)  DEFAULT 0,
  onboarding_done       TINYINT(1)  DEFAULT 0,
  is_diaspora           TINYINT(1)  DEFAULT 0,
  api_key               VARCHAR(64) UNIQUE,
  -- Timestamps
  created_at            DATETIME    DEFAULT CURRENT_TIMESTAMP,
  last_active           DATETIME    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_phone    (phone),
  INDEX idx_email    (email),
  INDEX idx_type     (user_type),
  INDEX idx_state    (state, city),
  INDEX idx_provider (provider_verified)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sessions (auth)
CREATE TABLE IF NOT EXISTS sessions (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED NOT NULL,
  token       VARCHAR(128) NOT NULL UNIQUE,
  ip_address  VARCHAR(45),
  user_agent  VARCHAR(300),
  expires_at  DATETIME NOT NULL,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_token   (token),
  INDEX idx_user    (user_id),
  INDEX idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Rate limits (used by config.php check_rate_limit())
CREATE TABLE IF NOT EXISTS rate_limits (
  key_hash    VARCHAR(64)  NOT NULL PRIMARY KEY,
  count       INT UNSIGNED DEFAULT 1,
  window_end  DATETIME NOT NULL,
  INDEX idx_window (window_end)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Feature flags (used by api/booking/create.php)
CREATE TABLE IF NOT EXISTS feature_flags (
  flag_name   VARCHAR(100) PRIMARY KEY,
  is_enabled  TINYINT(1)  DEFAULT 1,
  description VARCHAR(300),
  updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Subscription plans (used by Gateway.php revenue gate)
CREATE TABLE IF NOT EXISTS subscription_plans (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  plan_key    VARCHAR(50)  NOT NULL UNIQUE,
  name        VARCHAR(100) NOT NULL,
  price_sdg   DECIMAL(10,2) DEFAULT 0,
  ai_limit    INT UNSIGNED DEFAULT 20,
  booking_limit INT UNSIGNED DEFAULT 2,
  is_active   TINYINT(1)  DEFAULT 1,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Provider plans (used by StepController.fetchProviders for listing_boost)
CREATE TABLE IF NOT EXISTS provider_plans (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  plan_key      VARCHAR(50)  NOT NULL UNIQUE,
  name          VARCHAR(100) NOT NULL,
  listing_boost TINYINT UNSIGNED DEFAULT 0,
  commission_rate DECIMAL(5,2) DEFAULT 10.00,
  price_sdg     DECIMAL(10,2) DEFAULT 0,
  is_active     TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- User subscriptions (used by ControlEngine + TaskEngine)
CREATE TABLE IF NOT EXISTS user_subscriptions (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED NOT NULL,
  plan_id     INT UNSIGNED NULL,
  plan_key    VARCHAR(50)  NOT NULL,
  status      ENUM('active','expired','cancelled','paused') DEFAULT 'active',
  started_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  expires_at  DATETIME NOT NULL,
  payment_ref VARCHAR(200),
  auto_renew  TINYINT(1) DEFAULT 0,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user   (user_id),
  INDEX idx_status (status),
  INDEX idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Notifications (used by TaskEngine, GrowthBrain, ControlEngine)
CREATE TABLE IF NOT EXISTS notifications (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id    INT UNSIGNED NOT NULL,
  type       VARCHAR(50),
  title      VARCHAR(200),
  body       TEXT,
  data       JSON,
  is_read    TINYINT(1) DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user    (user_id),
  INDEX idx_type    (type),
  INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ══════════════════════════════════════════════════════════════
-- TIER 2: BOOKING & FLOW SYSTEM
-- ══════════════════════════════════════════════════════════════

-- Provider services
CREATE TABLE IF NOT EXISTS provider_services (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  provider_id INT UNSIGNED NOT NULL,
  title       VARCHAR(200) NOT NULL,
  description TEXT,
  price_sdg   DECIMAL(10,2) DEFAULT 0,
  duration_min INT UNSIGNED DEFAULT 60,
  is_active   TINYINT(1) DEFAULT 1,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_provider (provider_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Bookings (used by StepController, TaskEngine, GrowthBrain, SupportEngines)
CREATE TABLE IF NOT EXISTS bookings (
  id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id           INT UNSIGNED NOT NULL,
  provider_id       INT UNSIGNED NOT NULL,
  service_id        INT UNSIGNED NULL,
  booking_date      DATE NOT NULL,
  booking_time      TIME NOT NULL,
  amount_sdg        DECIMAL(10,2) DEFAULT 0,
  commission_rate   DECIMAL(5,2)  DEFAULT 10.00,
  consultation_type ENUM('clinic','video','chat') DEFAULT 'clinic',
  meeting_link      VARCHAR(500) NULL,
  triage_level      ENUM('low','medium','high','emergency') NULL,
  status            ENUM('pending','confirmed','in_progress','completed','cancelled','rejected') DEFAULT 'pending',
  notes             TEXT,
  cancelled_by      ENUM('user','provider','system') NULL,
  cancelled_at      DATETIME NULL,
  rejection_reason  VARCHAR(300) NULL,
  completed_at      DATETIME NULL,
  started_at        DATETIME NULL,
  confirmed_at      DATETIME NULL,
  created_at        DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user     (user_id),
  INDEX idx_provider (provider_id),
  INDEX idx_date     (booking_date),
  INDEX idx_status   (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tele sessions (used by StepController.initTeleSession)
CREATE TABLE IF NOT EXISTS tele_sessions (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  booking_id     INT UNSIGNED NOT NULL,
  user_id        INT UNSIGNED NOT NULL,
  provider_id    INT UNSIGNED NOT NULL,
  session_type   ENUM('chat','video') DEFAULT 'chat',
  session_token  VARCHAR(128) NOT NULL UNIQUE,
  status         ENUM('waiting','active','ended') DEFAULT 'waiting',
  started_at     DATETIME NULL,
  ended_at       DATETIME NULL,
  created_at     DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_booking (booking_id),
  INDEX idx_user    (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Medical records (used by StepController.createMedicalCase)
CREATE TABLE IF NOT EXISTS medical_records (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED NOT NULL,
  record_type VARCHAR(50)  NOT NULL DEFAULT 'consultation',
  title       VARCHAR(200),
  content     JSON,
  is_private  TINYINT(1)  DEFAULT 1,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_user (user_id),
  INDEX idx_type (record_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Prescription requests (used by TaskEngine.pharmSuggest)
CREATE TABLE IF NOT EXISTS prescription_requests (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  booking_id  INT UNSIGNED NOT NULL,
  user_id     INT UNSIGNED NOT NULL,
  status      ENUM('pending','dispensed','cancelled') DEFAULT 'pending',
  notes       TEXT,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_booking (booking_id),
  INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Booking ratings
CREATE TABLE IF NOT EXISTS booking_ratings (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  booking_id  INT UNSIGNED NOT NULL UNIQUE,
  user_id     INT UNSIGNED NOT NULL,
  provider_id INT UNSIGNED NOT NULL,
  rating      TINYINT UNSIGNED NOT NULL,
  comment     TEXT,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_provider (provider_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ══════════════════════════════════════════════════════════════
-- TIER 3: FLOW ENGINE (StepController)
-- ══════════════════════════════════════════════════════════════

-- Flow sessions (used by StepController — critical)
CREATE TABLE IF NOT EXISTS flow_sessions (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  session_id    VARCHAR(36)  NOT NULL UNIQUE,
  user_id       INT UNSIGNED NULL,
  flow_id       VARCHAR(100) NOT NULL,
  flow_version  VARCHAR(20)  DEFAULT 'v1',
  current_step  VARCHAR(100) NOT NULL DEFAULT 'initial',
  context_data  JSON,
  status        ENUM('active','completed','abandoned') DEFAULT 'active',
  completed_at  DATETIME NULL,
  created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_session (session_id),
  INDEX idx_user    (user_id),
  INDEX idx_status  (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Flow step logs (used by StepController.logTransition)
CREATE TABLE IF NOT EXISTS flow_step_logs (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  session_id  VARCHAR(36)  NOT NULL,
  step_from   VARCHAR(100) NOT NULL,
  step_to     VARCHAR(100) NOT NULL,
  input_data  JSON,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_session (session_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ══════════════════════════════════════════════════════════════
-- TIER 4: QUEUE + EVENT SYSTEM
-- ══════════════════════════════════════════════════════════════

-- Tasks (used by QueueEngine — critical for Worker)
CREATE TABLE IF NOT EXISTS tasks (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  type        VARCHAR(50)  NOT NULL,
  payload     JSON         NOT NULL,
  status      ENUM('pending','processing','done','failed') DEFAULT 'pending',
  attempts    INT UNSIGNED DEFAULT 0,
  run_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  event_id    VARCHAR(64)  NULL,
  last_error  TEXT         NULL,
  failed_at   DATETIME     NULL,
  created_at  DATETIME     DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME     NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_event_id (event_id),
  INDEX idx_status  (status, run_at),
  INDEX idx_retry   (status, attempts, run_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Audit logs (used by QueueEngine.fail + GrowthBrain + hub/track)
CREATE TABLE IF NOT EXISTS audit_logs (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED NULL,
  request_id  VARCHAR(36)  NULL,
  action      VARCHAR(100) NOT NULL,
  entity_type VARCHAR(50),
  entity_id   INT UNSIGNED NULL,
  ip_address  VARCHAR(45),
  user_agent  VARCHAR(300),
  data        JSON,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user   (user_id),
  INDEX idx_action (action),
  INDEX idx_entity (entity_type, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ══════════════════════════════════════════════════════════════
-- TIER 5: AI / ANALYTICS
-- ══════════════════════════════════════════════════════════════

-- AI requests log (used by KPILogger)
CREATE TABLE IF NOT EXISTS ai_requests (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  request_uuid   VARCHAR(36)  NULL,
  user_id        INT UNSIGNED NULL,
  session_id     VARCHAR(36)  NULL,
  service        VARCHAR(50)  DEFAULT 'general',
  intent         VARCHAR(100) NULL,
  ie_confidence  DECIMAL(4,3) DEFAULT 0,
  ie_hit         TINYINT(1)   DEFAULT 0,
  cache_hit      TINYINT(1)   DEFAULT 0,
  ai_used        TINYINT(1)   DEFAULT 0,
  model_used     VARCHAR(50)  NULL,
  input_tokens   INT UNSIGNED DEFAULT 0,
  output_tokens  INT UNSIGNED DEFAULT 0,
  cost_usd       DECIMAL(10,8) DEFAULT 0,
  latency_ms     INT UNSIGNED DEFAULT 0,
  failover_used  TINYINT(1)   DEFAULT 0,
  error_occurred TINYINT(1)   DEFAULT 0,
  user_tier      VARCHAR(20)  DEFAULT 'guest',
  created_at     DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user    (user_id),
  INDEX idx_service (service),
  INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- AI cache (used by CacheLayer)
CREATE TABLE IF NOT EXISTS ai_cache (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cache_key      VARCHAR(64)  NOT NULL UNIQUE,
  semantic_key   VARCHAR(64)  NULL,
  service        VARCHAR(50)  DEFAULT 'general',
  response       LONGTEXT     NOT NULL,
  response_source VARCHAR(50) DEFAULT 'ai',
  hits           INT UNSIGNED DEFAULT 0,
  last_hit_at    DATETIME NULL,
  expires_at     DATETIME NOT NULL,
  created_at     DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_key     (cache_key),
  INDEX idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Context memory (used by ContextMemory.php)
CREATE TABLE IF NOT EXISTS context_memory (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id      INT UNSIGNED NOT NULL,
  session_hash VARCHAR(64)  NOT NULL,
  role         ENUM('user','assistant') NOT NULL,
  content      TEXT NOT NULL,
  service      VARCHAR(50) DEFAULT 'general',
  created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user_session (user_id, session_hash),
  INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- System metrics (used by KPILogger.getLoadLevel)
CREATE TABLE IF NOT EXISTS system_metrics (
  id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  metric_minute    DATETIME NOT NULL UNIQUE,
  requests_count   INT UNSIGNED DEFAULT 0,
  ai_calls_count   INT UNSIGNED DEFAULT 0,
  load_level       TINYINT UNSIGNED DEFAULT 0,
  updated_at       DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_minute (metric_minute)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- KPI daily (used by GrowthBrain + KPILogger)
CREATE TABLE IF NOT EXISTS kpi_daily (
  id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  kpi_date            DATE UNIQUE,
  total_requests      INT UNSIGNED DEFAULT 0,
  ie_hits             INT UNSIGNED DEFAULT 0,
  cache_hits          INT UNSIGNED DEFAULT 0,
  ai_calls            INT UNSIGNED DEFAULT 0,
  bookings_created    INT UNSIGNED DEFAULT 0,
  new_users           INT UNSIGNED DEFAULT 0,
  revenue_sdg         DECIMAL(12,2) DEFAULT 0,
  cost_usd            DECIMAL(10,6) DEFAULT 0,
  avg_latency_ms      INT UNSIGNED DEFAULT 0,
  load_level          TINYINT UNSIGNED DEFAULT 0,
  created_at          DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- AI decisions (used by AIOrchestrator)
CREATE TABLE IF NOT EXISTS ai_decisions (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event       VARCHAR(100) NOT NULL,
  decision    JSON         NOT NULL,
  score       FLOAT        DEFAULT 0,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_event   (event),
  INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- AI metrics (used by SupportEngines: Monitoring + ReinforcementEngine)
CREATE TABLE IF NOT EXISTS ai_metrics (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  metric      VARCHAR(100) NOT NULL,
  value       FLOAT        NOT NULL DEFAULT 0,
  context     JSON         NULL,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_metric  (metric),
  INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Data gaps (used by Gateway when providers empty)
CREATE TABLE IF NOT EXISTS data_gaps (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  gap_key     VARCHAR(200) NOT NULL UNIQUE,
  category    VARCHAR(100) NOT NULL,
  location    VARCHAR(200) NULL,
  occurrences INT UNSIGNED DEFAULT 1,
  last_seen   DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ══════════════════════════════════════════════════════════════
-- TIER 6: SUDANNA AGENT SYSTEM (AIEngine)
-- ══════════════════════════════════════════════════════════════

-- API Keys (used by AIEngine.validateKey + generateKey)
CREATE TABLE IF NOT EXISTS ai_api_keys (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id      INT UNSIGNED NOT NULL,
  key_hash     VARCHAR(64)  NOT NULL UNIQUE,
  key_prefix   VARCHAR(12)  NOT NULL,
  label        VARCHAR(100) DEFAULT 'Default Key',
  status       ENUM('active','revoked') DEFAULT 'active',
  plan         VARCHAR(20)  DEFAULT 'free',
  calls_today  INT UNSIGNED DEFAULT 0,
  calls_total  INT UNSIGNED DEFAULT 0,
  last_used_at DATETIME NULL,
  created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user   (user_id),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- AI Agents (used by AIEngine.runAgent + dashboard)
CREATE TABLE IF NOT EXISTS ai_agents (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id      INT UNSIGNED NOT NULL,
  name         VARCHAR(150) NOT NULL,
  description  TEXT,
  prompt       TEXT         NOT NULL,
  service      VARCHAR(50)  DEFAULT 'general',
  status       ENUM('active','paused','deleted') DEFAULT 'active',
  runs_total   INT UNSIGNED DEFAULT 0,
  last_run_at  DATETIME NULL,
  created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user   (user_id),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Agent runs (used by AIEngine.runAgent + dashboard/logs)
CREATE TABLE IF NOT EXISTS ai_agent_runs (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  agent_id     INT UNSIGNED NOT NULL,
  user_id      INT UNSIGNED NOT NULL,
  input        TEXT,
  output       TEXT,
  status       ENUM('running','done','failed') DEFAULT 'running',
  latency_ms   INT UNSIGNED DEFAULT 0,
  created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_agent  (agent_id),
  INDEX idx_user   (user_id),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- AI Usage (used by AIEngine.logUsage + ControlEngine + TaskEngine)
CREATE TABLE IF NOT EXISTS ai_usage (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id      INT UNSIGNED NOT NULL,
  api_key_id   INT UNSIGNED NULL,
  agent_id     INT UNSIGNED NULL,
  event_type   VARCHAR(50)  NOT NULL,
  tokens_used  INT UNSIGNED DEFAULT 0,
  cost_usd     DECIMAL(10,8) DEFAULT 0,
  service      VARCHAR(50)  DEFAULT 'general',
  created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user    (user_id),
  INDEX idx_created (created_at),
  INDEX idx_type    (event_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Agent plan limits
CREATE TABLE IF NOT EXISTS ai_plan_limits (
  plan           VARCHAR(20) PRIMARY KEY,
  api_calls_day  INT UNSIGNED DEFAULT 50,
  agents_max     INT UNSIGNED DEFAULT 2,
  runs_day       INT UNSIGNED DEFAULT 10
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ══════════════════════════════════════════════════════════════
-- TIER 7: COMMUNITY & MARKETPLACE
-- ══════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS neighborhoods (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(150) NOT NULL,
  state       VARCHAR(100),
  city        VARCHAR(100),
  is_active   TINYINT(1) DEFAULT 1,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_state (state, city)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS neighborhood_services (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED NOT NULL,
  name        VARCHAR(150) NOT NULL,
  category    VARCHAR(50),
  description TEXT,
  phone       VARCHAR(30),
  state       VARCHAR(100),
  city        VARCHAR(100),
  neighborhood_id INT UNSIGNED NULL,
  status      ENUM('active','pending','suspended') DEFAULT 'pending',
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_category (category),
  INDEX idx_status   (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS community_events (
  id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  organizer_id     INT UNSIGNED NOT NULL,
  neighborhood_id  INT UNSIGNED NULL,
  state            VARCHAR(100),
  city             VARCHAR(100),
  title            VARCHAR(300) NOT NULL,
  description      TEXT,
  category         VARCHAR(50)  DEFAULT 'social',
  event_date       DATETIME NOT NULL,
  end_date         DATETIME NULL,
  location         VARCHAR(300),
  max_participants INT UNSIGNED NULL,
  image_url        VARCHAR(500) NULL,
  is_free          TINYINT(1)  DEFAULT 1,
  status           ENUM('upcoming','active','ended','cancelled') DEFAULT 'upcoming',
  created_at       DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_status (status),
  INDEX idx_date   (event_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS event_participants (
  event_id   INT UNSIGNED NOT NULL,
  user_id    INT UNSIGNED NOT NULL,
  joined_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (event_id, user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS marketplace_items (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED NOT NULL,
  title       VARCHAR(300) NOT NULL,
  description TEXT,
  price_sdg   DECIMAL(10,2) DEFAULT 0,
  category    VARCHAR(50),
  state       VARCHAR(100),
  city        VARCHAR(100),
  images      JSON,
  status      ENUM('active','sold','removed') DEFAULT 'active',
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_category (category),
  INDEX idx_status   (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pulse_posts (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED NOT NULL,
  type        VARCHAR(50)  DEFAULT 'general',
  content     TEXT NOT NULL,
  neighborhood_id INT UNSIGNED NULL,
  reactions   INT UNSIGNED DEFAULT 0,
  status      ENUM('active','removed') DEFAULT 'active',
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user   (user_id),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS family_members (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED NOT NULL,
  name        VARCHAR(150) NOT NULL,
  relation    VARCHAR(50),
  birth_date  DATE NULL,
  blood_type  VARCHAR(5),
  allergies   TEXT,
  chronic     TEXT,
  notes       TEXT,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pharmacies (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED NOT NULL,
  name        VARCHAR(150) NOT NULL,
  phone       VARCHAR(30),
  state       VARCHAR(100),
  city        VARCHAR(100),
  address     TEXT,
  status      ENUM('active','pending','suspended') DEFAULT 'pending',
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS lessons (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title         VARCHAR(300) NOT NULL,
  content       TEXT NOT NULL,
  track         VARCHAR(50)  DEFAULT 'foundation',
  order_index   INT UNSIGNED DEFAULT 0,
  is_premium    TINYINT(1)   DEFAULT 0,
  status        ENUM('published','draft') DEFAULT 'draft',
  quiz_question TEXT,
  quiz_options  JSON,
  quiz_answer   VARCHAR(10),
  created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_track  (track),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS lesson_submissions (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id    INT UNSIGNED NOT NULL,
  lesson_id  INT UNSIGNED NOT NULL,
  score      TINYINT UNSIGNED DEFAULT 0,
  passed     TINYINT(1) DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_user_lesson (user_id, lesson_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ══════════════════════════════════════════════════════════════
-- TIER 8: ADMIN & OPERATIONS
-- ══════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS staff_members (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name          VARCHAR(150) NOT NULL,
  email         VARCHAR(200) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role          ENUM('super_admin','admin','supervisor','finance','support','content') DEFAULT 'support',
  department    VARCHAR(100),
  active        TINYINT(1) DEFAULT 1,
  last_login    DATETIME NULL,
  created_at    DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS staff_activity_log (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  staff_id    INT UNSIGNED NULL,
  action      VARCHAR(200) NOT NULL,
  target_type VARCHAR(50),
  target_id   INT UNSIGNED NULL,
  details     TEXT,
  ip_address  VARCHAR(45),
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_staff  (staff_id),
  INDEX idx_action (action)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS inquiries (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(150),
  email       VARCHAR(200),
  phone       VARCHAR(30),
  message     TEXT,
  type        VARCHAR(50) DEFAULT 'general',
  status      ENUM('new','read','replied') DEFAULT 'new',
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS provider_applications (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED NOT NULL,
  specialty   VARCHAR(200),
  credentials TEXT,
  status      ENUM('pending','approved','rejected') DEFAULT 'pending',
  reviewed_by INT UNSIGNED NULL,
  reviewed_at DATETIME NULL,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS subscription_requests (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id    INT UNSIGNED NOT NULL,
  plan_key   VARCHAR(50),
  status     ENUM('pending','approved','rejected') DEFAULT 'pending',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contracts (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED NOT NULL,
  title       VARCHAR(300),
  content     TEXT,
  status      ENUM('draft','active','expired') DEFAULT 'draft',
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS crm_notes (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED NOT NULL,
  staff_id    INT UNSIGNED NULL,
  note_type   ENUM('general','followup','complaint','resolved') DEFAULT 'general',
  content     TEXT NOT NULL,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS crm_timeline (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED NOT NULL,
  event_type  VARCHAR(50),
  summary     TEXT,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS invoices (
  id               INT UNSIGNED   AUTO_INCREMENT PRIMARY KEY,
  user_id          INT UNSIGNED   NOT NULL,
  booking_id       INT UNSIGNED   NULL,
  service          VARCHAR(50)    NOT NULL DEFAULT 'general',
  amount           DECIMAL(10,2)  NOT NULL,
  amount_sdg       DECIMAL(10,2)  DEFAULT 0.00 COMMENT 'mirror of amount — use amount field',
  reference_code   VARCHAR(60)    NOT NULL UNIQUE COMMENT 'idempotency key',
  payment_method   ENUM('offline','gateway') DEFAULT 'offline',
  gateway_provider VARCHAR(30)    NULL COMMENT 'stripe|paypal|local_gateway',
  gateway_txn_id   VARCHAR(120)   NULL UNIQUE COMMENT 'gateway transaction id — UNIQUE prevents replay',
  webhook_payload  JSON           NULL COMMENT 'raw webhook data stored for audit',
  status           ENUM('pending','proof_uploaded','under_review','paid','rejected','refunded','gateway_pending','gateway_failed') DEFAULT 'pending',
  bank_account     VARCHAR(30)    DEFAULT '1821425',
  notes            TEXT           NULL,
  created_at       DATETIME       DEFAULT CURRENT_TIMESTAMP,
  expires_at       DATETIME       NULL,
  paid_at          DATETIME       NULL,
  INDEX idx_user      (user_id),
  INDEX idx_status    (status),
  INDEX idx_ref       (reference_code),
  INDEX idx_method    (payment_method),
  INDEX idx_txn       (gateway_txn_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS partners (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(150) NOT NULL,
  track      VARCHAR(50),
  active     TINYINT(1) DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS investment_applications (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(150),
  org         VARCHAR(200),
  email       VARCHAR(200),
  tier        VARCHAR(50),
  message     TEXT,
  status      ENUM('new','reviewing','responded') DEFAULT 'new',
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ai_logs alias (admin.php references this)
CREATE TABLE IF NOT EXISTS ai_logs (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED NULL,
  service     VARCHAR(50),
  question    TEXT,
  answer      TEXT,
  source      VARCHAR(50),
  latency_ms  INT UNSIGNED DEFAULT 0,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user    (user_id),
  INDEX idx_service (service),
  INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ══════════════════════════════════════════════════════════════
-- SEED DATA (required for system boot)
-- ══════════════════════════════════════════════════════════════

INSERT IGNORE INTO feature_flags (flag_name, is_enabled, description) VALUES
  ('enable_booking',   1, 'تفعيل الحجز'),
  ('enable_ai',        1, 'تفعيل الذكاء الاصطناعي'),
  ('enable_community', 1, 'تفعيل المجتمع'),
  ('maintenance_mode', 0, 'وضع الصيانة');

INSERT IGNORE INTO subscription_plans (plan_key, name, price_sdg, ai_limit, booking_limit) VALUES
  ('free',  'مجاني',  0,     20,  2),
  ('plus',  'بلاس',   5000,  60,  5),
  ('pro',   'برو',    10000, 200, 20),
  ('paid',  'مدفوع',  5000,  100, 10);

INSERT IGNORE INTO provider_plans (plan_key, name, listing_boost, commission_rate) VALUES
  ('basic',   'أساسي',  0, 10.00),
  ('pro',     'محترف',  1, 8.00),
  ('premium', 'بريميوم',2, 6.00);

INSERT IGNORE INTO ai_plan_limits VALUES
  ('free',  50,   2,  10),
  ('plus',  500,  10, 100),
  ('pro',   5000, 50, 1000);

INSERT IGNORE INTO kpi_daily (kpi_date, total_requests, ai_calls, ie_hits, cache_hits,
  bookings_created, new_users, revenue_sdg, cost_usd, load_level)
VALUES (CURDATE(), 0, 0, 0, 0, 0, 0, 0.00, 0.0000, 0)
ON DUPLICATE KEY UPDATE load_level = load_level;

-- MySQL Event Scheduler: auto-recover stuck tasks
CREATE EVENT IF NOT EXISTS sudanna_reset_stuck_tasks
  ON SCHEDULE EVERY 10 MINUTE
  DO UPDATE tasks
     SET status='pending', updated_at=NOW()
     WHERE status='processing'
       AND updated_at < DATE_SUB(NOW(), INTERVAL 10 MINUTE)
       AND attempts < 3;

SET FOREIGN_KEY_CHECKS = 1;

-- ══════════════════════════════════════════════════════════════
-- TIER 9: MISSING TABLES (added in hardening pass)
-- ══════════════════════════════════════════════════════════════

-- Password resets (api/auth/forgot_password.php)
CREATE TABLE IF NOT EXISTS password_resets (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email      VARCHAR(200) NOT NULL,
  code       VARCHAR(10)  NOT NULL,
  expires_at DATETIME NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_email (email),
  INDEX idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Payments (api/subscription/activate.php + request.php)
CREATE TABLE IF NOT EXISTS payments (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id        INT UNSIGNED NOT NULL,
  plan_id        INT UNSIGNED NULL,
  amount_sdg     DECIMAL(10,2) DEFAULT 0,
  payment_method VARCHAR(50) DEFAULT 'manual',
  receipt_url    VARCHAR(500) NULL,
  status         ENUM('pending','verified','rejected','refunded') DEFAULT 'pending',
  verified_by    INT UNSIGNED NULL,
  verified_at    DATETIME NULL,
  notes          TEXT,
  created_at     DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user   (user_id),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- BDE documents (api/bde/generate.php + wizard.php)
CREATE TABLE IF NOT EXISTS bde_documents (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id        INT UNSIGNED NOT NULL,
  template_id    INT UNSIGNED NULL,
  doc_type       VARCHAR(50)  NOT NULL,
  title          VARCHAR(300) NOT NULL,
  collected_data JSON,
  language       VARCHAR(10)  DEFAULT 'ar',
  content        LONGTEXT,
  status         ENUM('draft','complete','exported') DEFAULT 'draft',
  created_at     DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at     DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_user   (user_id),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- BDE templates (engine/TemplateEngine.php)
CREATE TABLE IF NOT EXISTS bde_templates (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(200) NOT NULL,
  type       VARCHAR(50),
  variables  JSON,
  content    LONGTEXT,
  language   VARCHAR(10) DEFAULT 'ar',
  is_active  TINYINT(1) DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_type (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Contract templates (api/contracts/index.php)
CREATE TABLE IF NOT EXISTS contract_templates (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(200) NOT NULL,
  type       VARCHAR(50),
  variables  JSON,
  content    LONGTEXT,
  is_active  TINYINT(1) DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Certificates (api/academy/certificate.php)
CREATE TABLE IF NOT EXISTS certificates (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED NOT NULL,
  track       VARCHAR(50)  NOT NULL,
  cert_code   VARCHAR(50)  NOT NULL UNIQUE,
  issued_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_user_track (user_id, track),
  INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- User progress (api/academy/lessons.php)
CREATE TABLE IF NOT EXISTS user_progress (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id      INT UNSIGNED NOT NULL,
  lesson_id    INT UNSIGNED NOT NULL,
  completed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  score        TINYINT UNSIGNED DEFAULT 0,
  UNIQUE KEY uq_user_lesson (user_id, lesson_id),
  INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- User devices / FCM tokens (api/auth/save_fcm_token.php)
CREATE TABLE IF NOT EXISTS user_devices (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED NOT NULL,
  fcm_token   VARCHAR(512) NOT NULL UNIQUE,
  platform    VARCHAR(20)  DEFAULT 'web',
  device_name VARCHAR(150) NULL,
  last_seen   DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Provider consultations (api/consultation/start.php + message.php)
CREATE TABLE IF NOT EXISTS provider_consultations (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED NOT NULL,
  provider_id INT UNSIGNED NOT NULL,
  type        ENUM('chat','video','voice') DEFAULT 'chat',
  subject     VARCHAR(300),
  is_paid     TINYINT(1) DEFAULT 0,
  status      ENUM('pending','active','closed','cancelled') DEFAULT 'pending',
  started_at  DATETIME NULL,
  ended_at    DATETIME NULL,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user     (user_id),
  INDEX idx_provider (provider_id),
  INDEX idx_status   (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Consultation messages (api/consultation/message.php)
CREATE TABLE IF NOT EXISTS consultation_messages (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  consultation_id INT UNSIGNED NOT NULL,
  sender_id       INT UNSIGNED NOT NULL,
  sender_type     ENUM('user','provider') NOT NULL,
  message         TEXT NOT NULL,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_consultation (consultation_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Provider earnings (api/provider/earnings.php)
CREATE TABLE IF NOT EXISTS provider_earnings (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  provider_id     INT UNSIGNED NOT NULL,
  booking_id      INT UNSIGNED NULL,
  gross_sdg       DECIMAL(10,2) DEFAULT 0,
  commission_rate DECIMAL(5,2)  DEFAULT 10.00,
  commission_sdg  DECIMAL(10,2) DEFAULT 0,
  net_sdg         DECIMAL(10,2) DEFAULT 0,
  status          ENUM('pending','paid') DEFAULT 'pending',
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_provider (provider_id),
  INDEX idx_booking  (booking_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Investment projects (api/invest/projects.php)
CREATE TABLE IF NOT EXISTS investment_projects (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id      INT UNSIGNED NOT NULL,
  title        VARCHAR(300) NOT NULL,
  description  TEXT,
  sector       VARCHAR(100),
  target_sdg   DECIMAL(14,2) DEFAULT 0,
  raised_sdg   DECIMAL(14,2) DEFAULT 0,
  equity_pct   DECIMAL(5,2)  DEFAULT 0,
  status       ENUM('draft','pending','active','funded','rejected') DEFAULT 'draft',
  published_at DATETIME NULL,
  created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user   (user_id),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Investment interest (api/invest/projects.php)
CREATE TABLE IF NOT EXISTS investment_interest (
  id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  project_id       INT UNSIGNED NOT NULL,
  investor_user_id INT UNSIGNED NOT NULL,
  amount_interested DECIMAL(14,2) DEFAULT 0,
  message          TEXT,
  status           ENUM('pending','accepted','rejected') DEFAULT 'pending',
  created_at       DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_project  (project_id),
  INDEX idx_investor (investor_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Prescription responses (api/pharmacy/prescription.php)
CREATE TABLE IF NOT EXISTS prescription_responses (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  request_id  INT UNSIGNED NOT NULL,
  pharmacy_id INT UNSIGNED NOT NULL,
  available   TINYINT(1) DEFAULT 0,
  note        TEXT,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_request  (request_id),
  INDEX idx_pharmacy (pharmacy_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Service ratings (api/services/directory.php)
CREATE TABLE IF NOT EXISTS service_ratings (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  service_id INT UNSIGNED NOT NULL,
  user_id    INT UNSIGNED NOT NULL,
  rating     TINYINT UNSIGNED NOT NULL,
  comment    TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_service_user (service_id, user_id),
  INDEX idx_service (service_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Content reports (api/content/report.php)
CREATE TABLE IF NOT EXISTS content_reports (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  reporter_id  INT UNSIGNED NOT NULL,
  content_type VARCHAR(50)  NOT NULL,
  content_id   INT UNSIGNED NOT NULL,
  reason       VARCHAR(100) NOT NULL,
  details      TEXT,
  status       ENUM('pending','reviewed','dismissed') DEFAULT 'pending',
  created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_content (content_type, content_id),
  INDEX idx_status  (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Support tickets (api/support/ticket.php)
CREATE TABLE IF NOT EXISTS support_tickets (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id      INT UNSIGNED NOT NULL,
  category     VARCHAR(50) DEFAULT 'general',
  priority     ENUM('low','medium','high','urgent') DEFAULT 'medium',
  subject      VARCHAR(300) NOT NULL,
  description  TEXT,
  related_type VARCHAR(50) NULL,
  related_id   INT UNSIGNED NULL,
  status       ENUM('open','in_progress','escalated','resolved','closed') DEFAULT 'open',
  assigned_to  INT UNSIGNED NULL,
  resolved_at  DATETIME NULL,
  created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at   DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_user   (user_id),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Support ticket replies (api/support/ticket.php)
CREATE TABLE IF NOT EXISTS support_ticket_replies (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ticket_id  INT UNSIGNED NOT NULL,
  user_id    INT UNSIGNED NOT NULL,
  message    TEXT NOT NULL,
  is_staff   TINYINT(1) DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_ticket (ticket_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ═══════════════════════════════════════════════════════════════════
-- SUDANNA PAY — E-Wallet Foundation Tables
-- Added in v23.3 — بنية Sudanna Pay الأساسية
-- ═══════════════════════════════════════════════════════════════════

-- Wallet (محفظة كل مستخدم)
CREATE TABLE IF NOT EXISTS wallet (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id         INT UNSIGNED NOT NULL UNIQUE,
  balance_sdg     DECIMAL(12,2) DEFAULT 0.00,
  hold_balance    DECIMAL(12,2) DEFAULT 0.00,
  total_deposited DECIMAL(12,2) DEFAULT 0.00,
  total_withdrawn DECIMAL(12,2) DEFAULT 0.00,
  kyc_level       TINYINT DEFAULT 0 COMMENT '0=email,1=phone,2=ID',
  daily_limit     DECIMAL(12,2) DEFAULT 5000.00,
  monthly_limit   DECIMAL(12,2) DEFAULT 50000.00,
  status          ENUM('active','frozen','suspended') DEFAULT 'active',
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Wallet Transactions (كل عملية في المحفظة)
CREATE TABLE IF NOT EXISTS wallet_transactions (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  wallet_id     INT UNSIGNED NOT NULL,
  user_id       INT UNSIGNED NOT NULL,
  type          ENUM('credit','debit','hold','release','commission') NOT NULL,
  amount_sdg    DECIMAL(12,2) NOT NULL,
  balance_after DECIMAL(12,2) NOT NULL,
  ref_type      ENUM('topup','booking','payout','transfer','refund','commission') NOT NULL,
  ref_id        INT UNSIGNED NULL,
  description   VARCHAR(300),
  created_by    INT UNSIGNED NULL COMMENT 'admin or system',
  created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_wallet (wallet_id),
  INDEX idx_user   (user_id),
  INDEX idx_type   (type),
  INDEX idx_date   (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Wallet Topup Requests (طلبات شحن المحفظة)
CREATE TABLE IF NOT EXISTS wallet_topup (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED NOT NULL,
  amount_sdg  DECIMAL(12,2) NOT NULL,
  bank_ref    VARCHAR(100) NULL,
  receipt_url VARCHAR(500) NULL,
  invoice_no  VARCHAR(50) NULL,
  status      ENUM('pending','approved','rejected') DEFAULT 'pending',
  verified_by INT UNSIGNED NULL,
  verified_at DATETIME NULL,
  notes       TEXT,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user   (user_id),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sudanna Revenue (إيرادات سودانا من العمولات)
CREATE TABLE IF NOT EXISTS sudanna_revenue (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  date            DATE NOT NULL,
  source_type     ENUM('booking_commission','subscription','transfer_fee','agent_fee') NOT NULL,
  amount_sdg      DECIMAL(12,2) NOT NULL,
  commission_rate DECIMAL(5,2) DEFAULT 10.00,
  ref_id          INT UNSIGNED NULL,
  user_id         INT UNSIGNED NULL,
  provider_id     INT UNSIGNED NULL,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_date (date),
  INDEX idx_type (source_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ══ SUDANNA PAY — E-Wallet Tables ══════════════════════════════════

CREATE TABLE IF NOT EXISTS wallet_topup_requests (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id      INT UNSIGNED NOT NULL,
  amount_sdg   DECIMAL(12,2) NOT NULL,
  bank_ref     VARCHAR(100),
  receipt_url  VARCHAR(500),
  invoice_no   VARCHAR(30),
  status       ENUM('pending','approved','rejected') DEFAULT 'pending',
  verified_by  INT UNSIGNED NULL,
  verified_at  DATETIME NULL,
  notes        TEXT,
  created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user   (user_id),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payment_proofs (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  invoice_id   BIGINT UNSIGNED NOT NULL UNIQUE COMMENT 'one proof per invoice',
  image_path   VARCHAR(255)   NOT NULL UNIQUE,
  file_hash    VARCHAR(64)    DEFAULT NULL UNIQUE COMMENT 'SHA256 prevents duplicate images',
  status       ENUM('pending','approved','rejected') DEFAULT 'pending',
  reviewed_by  INT UNSIGNED   DEFAULT NULL,
  reviewed_at  DATETIME       DEFAULT NULL,
  uploaded_at  DATETIME       DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_invoice (invoice_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS accounts (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code       VARCHAR(20)  NOT NULL UNIQUE,
  name       VARCHAR(100) NOT NULL,
  type       ENUM('asset','liability','equity','revenue','expense') NOT NULL,
  is_active  TINYINT(1)   DEFAULT 1,
  created_at DATETIME     DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ledger (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  account_id     INT UNSIGNED    NOT NULL,
  debit          DECIMAL(12,2)   DEFAULT 0.00,
  credit         DECIMAL(12,2)   DEFAULT 0.00,
  reference_type VARCHAR(50)     NOT NULL,
  reference_id   BIGINT UNSIGNED NOT NULL,
  description    VARCHAR(300),
  created_by     INT UNSIGNED    DEFAULT NULL,
  created_at     DATETIME        DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_account (account_id),
  INDEX idx_ref     (reference_type, reference_id),
  INDEX idx_date    (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ══ RECEIPT TABLE (one per paid invoice — idempotency UNIQUE) ══
CREATE TABLE IF NOT EXISTS payment_receipts (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  invoice_id      BIGINT UNSIGNED NOT NULL UNIQUE COMMENT 'one receipt per invoice',
  user_id         INT UNSIGNED    NOT NULL,
  receipt_no      VARCHAR(30)     NOT NULL UNIQUE COMMENT 'RCP-YYYYMMDD-NNNNNN',
  amount          DECIMAL(10,2)   NOT NULL,
  service         VARCHAR(50)     NOT NULL,
  payment_method  ENUM('offline','gateway') DEFAULT 'offline',
  reference_code  VARCHAR(60)     NOT NULL,
  txn_id          VARCHAR(120)    NULL,
  issued_at       DATETIME        DEFAULT CURRENT_TIMESTAMP,
  emailed_at      DATETIME        NULL COMMENT 'NULL = email not sent yet',
  email_recipient VARCHAR(200)    NULL,
  ledger_balanced TINYINT(1)      DEFAULT 0,
  INDEX idx_invoice (invoice_id),
  INDEX idx_user    (user_id),
  INDEX idx_receipt_no (receipt_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ══ MISSING CRITICAL TABLES (added for full compatibility) ══════════

CREATE TABLE IF NOT EXISTS providers (
  id                   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id              INT UNSIGNED NOT NULL UNIQUE,
  category             VARCHAR(50)  NOT NULL DEFAULT 'general',
  specialty            VARCHAR(100) NULL,
  bio                  TEXT         NULL,
  rating               DECIMAL(3,1) DEFAULT 0.0,
  total_ratings        INT          DEFAULT 0,
  completed_bookings   INT          DEFAULT 0,
  price_sdg            DECIMAL(10,2) DEFAULT 0.00,
  available            TINYINT(1)   DEFAULT 1,
  verified             TINYINT(1)   DEFAULT 0,
  bank_account         VARCHAR(30)  NULL,
  bank_name            VARCHAR(100) NULL,
  created_at           DATETIME     DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user       (user_id),
  INDEX idx_category   (category),
  INDEX idx_rating     (rating)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS services (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  category    VARCHAR(50)  NOT NULL,
  name_ar     VARCHAR(150) NOT NULL,
  name_en     VARCHAR(150) NOT NULL,
  description TEXT         NULL,
  price_sdg   DECIMAL(10,2) DEFAULT 0.00,
  sla_hours   INT          DEFAULT 24,
  active      TINYINT(1)   DEFAULT 1,
  created_at  DATETIME     DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS events (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title       VARCHAR(200) NOT NULL,
  description TEXT         NULL,
  location    VARCHAR(200) NULL,
  start_date  DATETIME     NULL,
  end_date    DATETIME     NULL,
  organizer   VARCHAR(100) NULL,
  status      ENUM('active','cancelled','completed') DEFAULT 'active',
  created_at  DATETIME     DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS posts (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED NOT NULL,
  neighborhood_id INT UNSIGNED NULL,
  content     TEXT         NOT NULL,
  type        VARCHAR(30)  DEFAULT 'post',
  likes       INT          DEFAULT 0,
  status      ENUM('active','hidden','removed') DEFAULT 'active',
  created_at  DATETIME     DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS items (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED NOT NULL,
  title       VARCHAR(200) NOT NULL,
  description TEXT         NULL,
  price       DECIMAL(10,2) DEFAULT 0.00,
  category    VARCHAR(50)  DEFAULT 'general',
  status      ENUM('active','sold','removed') DEFAULT 'active',
  created_at  DATETIME     DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS invest (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED NOT NULL,
  amount_sdg  DECIMAL(12,2) NOT NULL,
  type        VARCHAR(50)  DEFAULT 'equity',
  status      ENUM('pending','active','completed') DEFAULT 'pending',
  notes       TEXT         NULL,
  created_at  DATETIME     DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS staff (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(150) NOT NULL,
  role        VARCHAR(100) NULL,
  email       VARCHAR(200) NULL,
  active      TINYINT(1)   DEFAULT 1,
  created_at  DATETIME     DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS subscriptions (
  id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id           INT UNSIGNED NOT NULL,
  plan_id           INT UNSIGNED NOT NULL,
  status            ENUM('active','cancelled','expired') DEFAULT 'active',
  starts_at         DATETIME     DEFAULT CURRENT_TIMESTAMP,
  expires_at        DATETIME     NULL,
  created_at        DATETIME     DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS lesson_subscriptions (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED NOT NULL,
  lesson_id   INT UNSIGNED NOT NULL,
  status      ENUM('active','completed','cancelled') DEFAULT 'active',
  created_at  DATETIME     DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS lesson_orders (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED NOT NULL,
  lesson_id   INT UNSIGNED NOT NULL,
  amount_sdg  DECIMAL(10,2) NOT NULL,
  status      ENUM('pending','paid','cancelled') DEFAULT 'pending',
  created_at  DATETIME     DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS booking_reviews (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  booking_id  INT UNSIGNED NOT NULL,
  user_id     INT UNSIGNED NOT NULL,
  rating      TINYINT      DEFAULT 5,
  comment     TEXT         NULL,
  created_at  DATETIME     DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ai_feedback (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED NULL,
  query       TEXT         NULL,
  response    TEXT         NULL,
  rating      TINYINT      NULL,
  created_at  DATETIME     DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS system_settings (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  setting_key VARCHAR(100) NOT NULL UNIQUE,
  value       TEXT         NULL,
  updated_at  DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Default system settings
INSERT IGNORE INTO system_settings (setting_key, value) VALUES
  ('platform_mode', 'production'),
  ('maintenance_mode', '0'),
  ('bank_integration_enabled', '0'),
  ('default_commission_rate', '0.10');

-- ══ BILLING RESERVES — atomic fund reservation before execution ══
CREATE TABLE IF NOT EXISTS billing_reserves (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  execution_id   VARCHAR(60)     NOT NULL UNIQUE COMMENT 'links to all engine logs',
  user_id        INT UNSIGNED    NOT NULL,
  sector         VARCHAR(50)     NOT NULL,
  amount         DECIMAL(10,2)   NOT NULL,
  status         ENUM('reserved','committed','rolled_back') DEFAULT 'reserved',
  created_at     DATETIME        DEFAULT CURRENT_TIMESTAMP,
  committed_at   DATETIME        NULL,
  rolled_back_at DATETIME        NULL,
  INDEX idx_user   (user_id),
  INDEX idx_status (status),
  INDEX idx_exec   (execution_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ══ NOTIFICATIONS TABLE ═══════════════════════════════════════════
CREATE TABLE IF NOT EXISTS notifications (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id    INT UNSIGNED NOT NULL,
  type       VARCHAR(60)  NOT NULL DEFAULT 'info',
  title      VARCHAR(200) NOT NULL,
  body       TEXT,
  data       JSON,
  read_at    DATETIME NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user (user_id),
  INDEX idx_unread (user_id, read_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ══ EXECUTION LOG ══════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS execution_log (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  execution_id VARCHAR(60)  NOT NULL,
  user_id      INT UNSIGNED,
  sector       VARCHAR(50),
  state        ENUM('INIT','RESERVED','RUNNING','SUCCESS','FAILED','ROLLED_BACK','INVOICE_PENDING') NOT NULL,
  detail       TEXT,
  created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_exec (execution_id),
  INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ══ POLICY VERSIONS ════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS policy_versions (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  policy_key VARCHAR(100) NOT NULL,
  version    INT UNSIGNED NOT NULL DEFAULT 1,
  value      TEXT NOT NULL,
  is_active  TINYINT(1) DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_policy_ver (policy_key, version)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
