-- ═══════════════════════════════════════════════════════════════════
-- SUDANNA AI OS v23 — Master Schema (Single Source of Truth)
-- Generated: 2026-04-29
-- 178 tables consolidated from 14 SQL files
-- All ALTER TABLE merged into CREATE TABLE — zero duplicates
-- Safe to re-run: all tables use IF NOT EXISTS
-- ═══════════════════════════════════════════════════════════════════

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


-- ── CORE: USERS, WALLET, SYSTEM ─────────────────────────────────

-- audit_logs
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)
,
  INDEX idx_trace (trace_id),
  INDEX idx_trace (trace_id),
  trace_id VARCHAR(32) NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- feature_flags
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;

-- notifications
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),
  INDEX idx_type (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- platform_settings
CREATE TABLE IF NOT EXISTS platform_settings (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  setting_key  VARCHAR(100) NOT NULL UNIQUE,
  value        VARCHAR(500) NOT NULL,
  value_type   ENUM('number','text','boolean','json','percent') DEFAULT 'number',
  label_ar     VARCHAR(200) NOT NULL,
  category     ENUM('exchange','subscription','document','corporate',
                    'commission','limits','system') NOT NULL,
  unit         VARCHAR(20) DEFAULT 'SDG',
  is_editable  TINYINT(1) DEFAULT 1,
  updated_by   INT UNSIGNED NULL,
  updated_at   DATETIME DEFAULT CURRENT_TIMESTAMP
               ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_cat (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- rate_limits
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;

-- users
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)
,
  IF NOT EXISTS fcm_token VARCHAR(500) NULL AFTER email,
  provider_bank_name VARCHAR(100) NULL,
  provider_bank_account VARCHAR(50)  NULL,
  provider_specialization VARCHAR(150) NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 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,
  status          ENUM('active','frozen','suspended') DEFAULT 'active',
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user (user_id)
,
  IF NOT EXISTS kyc_level TINYINT DEFAULT 0,
  daily_limit DECIMAL(12,
  monthly_limit DECIMAL(12) 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)
,
  INDEX idx_reservation (reservation_id),,
  payment_ref          VARCHAR(100) NULL,
  UNIQUE INDEX idx_topup_idem (user_id, payment_ref, ref_type)
  INDEX idx_reservation (reservation_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ── AUTHENTICATION ──────────────────────────────────────────────

-- kyc_verifications
CREATE TABLE IF NOT EXISTS kyc_verifications (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED NOT NULL,
  level       TINYINT UNSIGNED NOT NULL COMMENT '1=phone,2=id,3=full',
  id_type     ENUM('national_id','passport','driving') NULL,
  id_number   VARCHAR(50) NULL,
  id_doc_url  VARCHAR(500) NULL,
  selfie_url  VARCHAR(500) NULL,
  verified_by INT UNSIGNED NULL,
  notes       TEXT NULL,
  status      ENUM('pending','approved','rejected') DEFAULT 'pending',
  verified_at DATETIME NULL,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- password_resets
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;


-- ── BILLING: INVOICES, SUBSCRIPTIONS ────────────────────────────

-- billing_reserves
CREATE TABLE IF NOT EXISTS billing_reserves (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, execution_id VARCHAR(60) NOT NULL UNIQUE, user_id INT UNSIGNED NOT NULL, sector VARCHAR(50), amount DECIMAL(10,2), 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_user_status (user_id, status),
  INDEX idx_user_status (user_id, status)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 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),
  INDEX idx_state  (state),
  INDEX idx_date   (created_at)
,
  INDEX idx_trace (trace_id),
  INDEX idx_trace (trace_id),
  trace_id VARCHAR(32) NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- invoices
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)
,
  IF NOT EXISTS reference_code VARCHAR(60) NOT NULL DEFAULT '' COMMENT 'idempotency key',
  UNIQUE INDEX idx_ref_unique (reference_code),
  INDEX idx_type (type),
  UNIQUE INDEX idx_ref_unique (reference_code),
  INDEX idx_invoice_no (invoice_no),
  ADD INDEX idx_type (type),
  invoice_no VARCHAR(40) UNIQUE NULL AFTER id,
  type VARCHAR(40) DEFAULT 'general' AFTER service,
  description VARCHAR(500) NULL AFTER type,
  currency VARCHAR(10) DEFAULT 'SDG' AFTER amount,
  payment_ref VARCHAR(100) NULL AFTER currency,
  due_date DATETIME NULL AFTER payment_ref) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- payment_receipts
CREATE TABLE IF NOT EXISTS payment_receipts (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  invoice_id      BIGINT UNSIGNED NOT NULL UNIQUE,
  user_id         INT UNSIGNED    NOT NULL,
  receipt_no      VARCHAR(30)     NOT NULL UNIQUE,
  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,
  email_recipient VARCHAR(200)    NULL,
  ledger_balanced TINYINT(1)      DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- subscription_credits
CREATE TABLE IF NOT EXISTS subscription_credits (
  id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id          INT UNSIGNED NOT NULL,
  plan_key         VARCHAR(50) NOT NULL,
  monthly_credit   INT UNSIGNED DEFAULT 0 COMMENT 'SDG credit loaded monthly',
  used_sdg         INT UNSIGNED DEFAULT 0,
  remaining_sdg    INT UNSIGNED DEFAULT 0,
  period_month     TINYINT UNSIGNED NOT NULL,
  period_year      SMALLINT UNSIGNED NOT NULL,
  expires_at       DATETIME NOT NULL,
  created_at       DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_user_period (user_id, period_month, period_year),
  INDEX idx_user (user_id),
  INDEX idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- subscription_plans
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
,
  IF NOT EXISTS booking_per_day INT UNSIGNED DEFAULT 2,
  ai_requests_day INT UNSIGNED DEFAULT 20,
  booking_per_day INT UNSIGNED DEFAULT 2,
  price_usd DECIMAL(8,
  name_ar VARCHAR(100) DEFAULT '',
  name_en VARCHAR(100) DEFAULT '',
  doc_credit_sdg INT UNSIGNED DEFAULT 0,
  sdg_override TINYINT(1) DEFAULT 1
                            COMMENT '1=use price_sdg directly) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- subscription_requests
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)
,
  IF NOT EXISTS invoice_id INT UNSIGNED NULL,
  invoice_id INT UNSIGNED NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ── AI: USAGE, RESERVATIONS, ESTIMATION ─────────────────────────

-- ai_agent_runs
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('pending','running','done','failed') DEFAULT 'pending',
  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)
,
  INDEX idx_cost (cost_sdg),
  INDEX idx_cost (cost_sdg),
  tokens_used INT UNSIGNED DEFAULT 0 AFTER latency_ms,
  cost_usd DECIMAL(10,
  cost_sdg INT UNSIGNED DEFAULT 0 AFTER cost_usd) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ai_agents
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;

-- ai_api_keys
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_decisions
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
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;

-- ai_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 3,
  runs_day       INT UNSIGNED DEFAULT 20
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ai_usage
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)
,
  INDEX idx_cost_sdg (cost_sdg),
  INDEX idx_provider (provider),
  ADD INDEX idx_cost_sdg (cost_sdg),
  INDEX idx_trace (trace_id),
  INDEX idx_reservation (reservation_id),
  tokens_in INT UNSIGNED DEFAULT 0 AFTER tokens_used,
  tokens_out INT UNSIGNED DEFAULT 0 AFTER tokens_in,
  provider VARCHAR(20)  DEFAULT NULL AFTER service,
  cost_sdg DECIMAL(10,
  trace_id VARCHAR(32) NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- estimation_multipliers
CREATE TABLE IF NOT EXISTS estimation_multipliers (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  task_type    VARCHAR(50) NOT NULL COMMENT 'e.g. document:feasibility, image:1024x1024',
  multiplier   DECIMAL(5,3) NOT NULL DEFAULT 1.000 COMMENT '1.0 = no correction',
  sample_count INT UNSIGNED DEFAULT 0 COMMENT 'How many samples used to compute this',
  avg_accuracy DECIMAL(6,2) NULL,
  updated_at   DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uk_type (task_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- task_estimation_log
CREATE TABLE IF NOT EXISTS task_estimation_log (
  id                   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id              INT UNSIGNED NOT NULL,
  reservation_id       VARCHAR(40) NULL COMMENT 'NULL if never confirmed',
  trace_id             VARCHAR(32) NULL,

  -- Input
  prompt_hash          CHAR(32) NOT NULL COMMENT 'MD5 of original prompt',
  prompt_length        SMALLINT UNSIGNED DEFAULT 0,
  user_plan            VARCHAR(20) DEFAULT 'free',

  -- Estimation metadata
  estimation_method    ENUM('rules','ai','hybrid') NOT NULL DEFAULT 'rules',
  confidence_score     DECIMAL(4,3) NOT NULL DEFAULT 0.000,
  estimation_ms        SMALLINT UNSIGNED DEFAULT 0 COMMENT 'How long estimation took',
  estimation_cost_usd  DECIMAL(10,8) DEFAULT 0 COMMENT 'Cost of AI call for estimation (paid by platform)',

  -- Plans (JSON)
  estimated_plan       JSON NOT NULL,
  actual_plan          JSON NULL COMMENT 'Filled after execution completes',

  -- Accuracy (filled post-execution by ReservationEngine::complete)
  estimated_sdg        INT UNSIGNED NOT NULL,
  actual_sdg           INT UNSIGNED NULL,
  accuracy_pct         DECIMAL(6,2) NULL COMMENT '100 = perfect, <100 = under, >100 = over',

  -- Outcome
  was_approved         TINYINT(1) DEFAULT 0,
  was_executed         TINYINT(1) DEFAULT 0,

  created_at           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  completed_at         DATETIME NULL,

  INDEX idx_user (user_id),
  INDEX idx_reservation (reservation_id),
  INDEX idx_method_conf (estimation_method, confidence_score),
  INDEX idx_accuracy (accuracy_pct),
  INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- task_reservations
CREATE TABLE IF NOT EXISTS task_reservations (
  id                   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  reservation_id       VARCHAR(40) NOT NULL UNIQUE COMMENT 'RES_{uniqid}',
  user_id              INT UNSIGNED NOT NULL,
  trace_id             VARCHAR(32) NULL COMMENT 'Kernel request trace',

  -- Estimated plan (frozen at estimation time)
  estimated_plan       JSON NOT NULL COMMENT 'Full task list with price_snapshot',
  price_snapshot       JSON NOT NULL COMMENT 'Prices locked at estimation time',

  -- Financials
  reserved_sdg         INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Pessimistic hold amount',
  actual_sdg           INT UNSIGNED NULL DEFAULT NULL COMMENT 'Real cost after execution',
  refunded_sdg         INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Returned to user',

  -- Lifecycle
  status               ENUM('pending','active','executing','completed','cancelled','expired')
                       NOT NULL DEFAULT 'pending',
  confirmation_token   VARCHAR(64) UNIQUE NULL COMMENT 'Single-use, 30min TTL',

  -- Execution tracking
  tasks_total          TINYINT UNSIGNED DEFAULT 0,
  tasks_completed      TINYINT UNSIGNED DEFAULT 0,
  tasks_failed         TINYINT UNSIGNED DEFAULT 0,
  execution_log        JSON NULL COMMENT 'Per-task result log',

  -- Timestamps
  reserved_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at           DATETIME NOT NULL COMMENT 'Auto-cancel if not activated',
  activated_at         DATETIME NULL,
  completed_at         DATETIME NULL,
  cancelled_at         DATETIME NULL,
  cancel_reason        VARCHAR(200) NULL,

  INDEX idx_user_status (user_id, status),
  INDEX idx_expires (expires_at, status),
  INDEX idx_token (confirmation_token),
  INDEX idx_trace (trace_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ── BOOKING + CONSULTATIONS ─────────────────────────────────────

-- booking_reviews
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;

-- bookings
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)
,
  INDEX idx_service_type (service_type),
  INDEX idx_slot_time (slot_time),
  ADD INDEX idx_service_type (service_type),
  slot_time DATETIME NULL AFTER booking_time,
  service_type VARCHAR(50) DEFAULT 'general' AFTER slot_time) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- consultation_messages
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_consultations
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)
,
  IF NOT EXISTS updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- provider_earnings
CREATE TABLE IF NOT EXISTS provider_earnings (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  provider_user_id INT UNSIGNED NOT NULL,
  service_type    VARCHAR(50) NOT NULL,
  gross_sdg       DECIMAL(14,2) NOT NULL,
  platform_fee_sdg DECIMAL(14,2) NOT NULL,
  net_sdg         DECIMAL(14,2) NOT NULL,
  service_payment_id BIGINT UNSIGNED NULL,
  sdpay_txn_ref   VARCHAR(50) NULL,
  status          ENUM('pending','paid','held','refunded') DEFAULT 'pending',
  paid_at         DATETIME NULL,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_provider (provider_user_id),
  INDEX idx_status (status),
  INDEX idx_date (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 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;


-- ── BDE: BUSINESS DOCUMENT ENGINE ───────────────────────────────

-- bde_documents
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
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;


-- ── CORPORATE PROFILES + CONTRACTS ──────────────────────────────

-- contracts
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)
,
  IF NOT EXISTS created_by    INT UNSIGNED NULL,
  party_user_id INT UNSIGNED NULL,
  created_by INT UNSIGNED NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ── MARKETPLACE + NEIGHBORHOOD ──────────────────────────────────

-- marketplace_items
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)
,
  IF NOT EXISTS seller_id INT UNSIGNED NULL,
  seller_id INT UNSIGNED NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- neighborhood_services
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)
,
  IF NOT EXISTS is_verified      TINYINT(1)   DEFAULT 0,
  provider_user_id INT UNSIGNED NULL,
  rating_avg DECIMAL(3,
  is_verified TINYINT(1)   DEFAULT 0) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- pulse_posts
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)
,
  IF NOT EXISTS is_urgent TINYINT(1) DEFAULT 0,
  is_urgent TINYINT(1) DEFAULT 0) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- suggestion_votes
CREATE TABLE IF NOT EXISTS suggestion_votes (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  suggestion_id INT UNSIGNED NOT NULL,
  user_id       INT UNSIGNED NULL,
  session_id    VARCHAR(100) NULL,
  created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_user_vote (suggestion_id, user_id),
  INDEX idx_sug (suggestion_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- user_suggestions
CREATE TABLE IF NOT EXISTS user_suggestions (
  id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ref_no           VARCHAR(20) NOT NULL UNIQUE,
  user_id          INT UNSIGNED NULL,
  session_id       VARCHAR(100) NULL,
  type             ENUM('improvement','bug','solution','new_service','positive') DEFAULT 'improvement',
  service          VARCHAR(50) NULL,
  title            VARCHAR(300) NOT NULL,
  description      TEXT NOT NULL,
  expected_benefit TEXT NULL,
  conversation_log JSON NULL,
  -- Agent Analysis
  priority_score   DECIMAL(4,2) NULL,
  priority_label   ENUM('urgent','high','medium','low','duplicate','out_of_scope') NULL,
  ai_summary       TEXT NULL,
  ai_recommendation VARCHAR(100) NULL,
  affected_estimate INT UNSIGNED NULL,
  effort_level     ENUM('low','medium','high') NULL,
  effort_hours     SMALLINT UNSIGNED NULL,
  revenue_impact   ENUM('none','low','medium','high') NULL,
  team_responsible VARCHAR(50) NULL,
  risk_level       ENUM('low','medium','high') NULL,
  roadmap_quarter  VARCHAR(10) NULL,
  similar_ids      JSON NULL,
  category_tag     VARCHAR(100) NULL,
  -- Admin Action
  status           ENUM('pending','under_review','accepted','rejected','implemented','duplicate') DEFAULT 'pending',
  admin_notes      TEXT NULL,
  admin_reply      TEXT NULL,
  votes_count      INT UNSIGNED DEFAULT 0,
  linked_ticket_id INT UNSIGNED NULL,
  -- Meta
  user_plan        VARCHAR(20) NULL,
  ip_hash          VARCHAR(64) NULL,
  created_at       DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at       DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_status (status),
  INDEX idx_type (type),
  INDEX idx_priority (priority_label),
  INDEX idx_service (service),
  INDEX idx_date (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ── FINANCE: INVEST + ROSCA ─────────────────────────────────────

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

-- invest_projects
CREATE TABLE IF NOT EXISTS invest_projects (
  id                    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  owner_user_id         INT UNSIGNED NOT NULL,
  title                 VARCHAR(200) NOT NULL,
  description           TEXT NULL,
  model                 ENUM('mudarabah','musharakah','salam','ijarah','murabahah') DEFAULT 'mudarabah',
  risk_level            TINYINT DEFAULT 2,
  target_sdg            DECIMAL(16,2) NOT NULL,
  collected_sdg         DECIMAL(16,2) DEFAULT 0.00,
  min_invest_sdg        DECIMAL(14,2) DEFAULT 5000.00,
  max_invest_sdg        DECIMAL(14,2) NULL,
  expected_return_pct   DECIMAL(5,2) DEFAULT 15.00,
  investor_share_pct    DECIMAL(5,2) DEFAULT 60.00,
  duration_days         INT DEFAULT 90,
  start_date            DATE NULL,
  end_date              DATE NULL,
  status                ENUM('draft','active','funded','running','completed','cancelled') DEFAULT 'draft',
  safety_pool_sdg       DECIMAL(14,2) DEFAULT 0.00,
  monthly_report        JSON NULL,
  kyc_level_required    TINYINT DEFAULT 2,
  created_at            DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_owner (owner_user_id),
  INDEX idx_status (status),
  INDEX idx_model (model)
,
  IF NOT EXISTS project_bank_verified       TINYINT DEFAULT 0 AFTER project_bank_account_no) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- invest_reports
CREATE TABLE IF NOT EXISTS invest_reports (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  project_id    INT UNSIGNED NOT NULL,
  report_month  DATE NOT NULL,
  narrative     TEXT NULL,
  photos        JSON NULL,
  revenue_sdg   DECIMAL(14,2) DEFAULT 0.00,
  expense_sdg   DECIMAL(14,2) DEFAULT 0.00,
  profit_sdg    DECIMAL(14,2) DEFAULT 0.00,
  submitted_at  DATETIME NULL,
  approved_at   DATETIME NULL,
  created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_proj_month (project_id, report_month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- invest_safety_pool
CREATE TABLE IF NOT EXISTS invest_safety_pool (
  id                   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  project_id           INT UNSIGNED NOT NULL,
  contributor_user_id  INT UNSIGNED NOT NULL,
  amount_sdg           DECIMAL(14,2) NOT NULL,
  released             TINYINT DEFAULT 0,
  released_at          DATETIME NULL,
  created_at           DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_project (project_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- invest_shares
CREATE TABLE IF NOT EXISTS invest_shares (
  id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  project_id          INT UNSIGNED NOT NULL,
  investor_user_id    INT UNSIGNED NOT NULL,
  amount_sdg          DECIMAL(14,2) NOT NULL,
  share_pct           DECIMAL(8,4) NOT NULL,
  contract_ref        VARCHAR(50) NOT NULL UNIQUE,
  contract_signed_at  DATETIME NULL,
  profit_sdg          DECIMAL(14,2) DEFAULT 0.00,
  status              ENUM('active','completed','cancelled') DEFAULT 'active',
  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;

-- ledger
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(30)     NULL,
  reference_id    INT UNSIGNED    NULL,
  description     VARCHAR(255)    NULL,
  created_at      DATETIME        DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_account (account_id),
  INDEX idx_ref (reference_type, reference_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- rosca_members
CREATE TABLE IF NOT EXISTS rosca_members (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  group_id    INT UNSIGNED NOT NULL,
  user_id     INT UNSIGNED NOT NULL,
  position    TINYINT UNSIGNED NULL COMMENT 'ترتيب الاستلام',
  has_received TINYINT(1) DEFAULT 0,
  total_paid  DECIMAL(14,2) DEFAULT 0.00,
  status      ENUM('active','left','suspended') DEFAULT 'active',
  joined_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_group_user (group_id, user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ── SUDANNAPAY: AGENTS, MERCHANTS, TRANSACTIONS ─────────────────

-- sdpay_accounts
CREATE TABLE IF NOT EXISTS sdpay_accounts (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id         INT UNSIGNED NOT NULL UNIQUE,
  account_no      VARCHAR(20) NOT NULL UNIQUE,
  pin_hash        VARCHAR(255) NOT NULL DEFAULT '',
  balance_sdg     DECIMAL(14,2) DEFAULT 0.00,
  hold_sdg        DECIMAL(14,2) DEFAULT 0.00,
  daily_limit_sdg DECIMAL(14,2) DEFAULT 500000.00,
  monthly_vol_sdg DECIMAL(16,2) DEFAULT 0.00,
  kyc_level       TINYINT DEFAULT 1,
  status          ENUM('active','suspended','frozen','dormant') DEFAULT 'active',
  last_txn_at     DATETIME NULL,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user (user_id),
  INDEX idx_account_no (account_no)
,
  IF NOT EXISTS pin_locked_until       DATETIME NULL AFTER failed_pin_attempts) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- sdpay_agent_transactions
CREATE TABLE IF NOT EXISTS sdpay_agent_transactions (
  id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  agent_id         INT UNSIGNED NOT NULL,
  type             ENUM('cashin','cashout','registration') NOT NULL,
  customer_account VARCHAR(25) NULL,
  amount_sdg       DECIMAL(14,2) NOT NULL,
  commission_sdg   DECIMAL(14,2) DEFAULT 0.00,
  txn_ref          VARCHAR(50) NOT NULL UNIQUE,
  status           ENUM('pending','completed','failed') DEFAULT 'pending',
  created_at       DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_agent (agent_id),
  INDEX idx_date (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- sdpay_agents
CREATE TABLE IF NOT EXISTS sdpay_agents (
  id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id           INT UNSIGNED NOT NULL UNIQUE,
  agent_code        VARCHAR(20) NOT NULL UNIQUE,
  business_name     VARCHAR(150) NOT NULL,
  governorate       VARCHAR(50) NULL,
  city              VARCHAR(50) NULL,
  gps_lat           DECIMAL(10,7) NULL,
  gps_lng           DECIMAL(10,7) NULL,
  float_balance_sdg DECIMAL(14,2) DEFAULT 0.00,
  daily_limit_sdg   DECIMAL(14,2) DEFAULT 5000000.00,
  commission_rate   DECIMAL(5,4) DEFAULT 0.0050,
  status            ENUM('pending','active','suspended') DEFAULT 'pending',
  approved_at       DATETIME NULL,
  created_at        DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_city (city),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- sdpay_aml_reports
CREATE TABLE IF NOT EXISTS sdpay_aml_reports (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  account_no   VARCHAR(25) NOT NULL,
  report_type  ENUM('CTR','SAR','STR') DEFAULT 'CTR',
  amount_sdg   DECIMAL(16,2) NULL,
  details      JSON NULL,
  status       ENUM('new','reviewed','filed','closed') DEFAULT 'new',
  created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_account (account_no),
  INDEX idx_type (report_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- sdpay_bill_payments
CREATE TABLE IF NOT EXISTS sdpay_bill_payments (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id         INT UNSIGNED NOT NULL,
  provider_id     INT UNSIGNED NOT NULL,
  account_ref     VARCHAR(100) NOT NULL,
  amount_sdg      DECIMAL(14,2) NOT NULL,
  fee_sdg         DECIMAL(14,2) DEFAULT 0.00,
  txn_ref         VARCHAR(50) NOT NULL UNIQUE,
  reference_code  VARCHAR(50) NULL,
  token_received  VARCHAR(100) NULL,
  status          ENUM('pending','completed','failed','refunded') DEFAULT 'pending',
  paid_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;

-- sdpay_bill_providers
CREATE TABLE IF NOT EXISTS sdpay_bill_providers (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code            VARCHAR(30) NOT NULL UNIQUE,
  name_ar         VARCHAR(100) NOT NULL,
  name_en         VARCHAR(100) NULL,
  category        ENUM('electricity','water','telecom','gov_fees','health',
                       'insurance','education','transport','other') NOT NULL,
  api_url         VARCHAR(255) NULL,
  api_key_enc     TEXT NULL,
  commission_pct  DECIMAL(5,4) DEFAULT 0.0000,
  fixed_fee_sdg   DECIMAL(10,2) DEFAULT 0.00,
  validation_required TINYINT DEFAULT 0,
  offline_capable TINYINT DEFAULT 0,
  logo_url        VARCHAR(255) NULL,
  is_active       TINYINT DEFAULT 1,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_category (category),
  INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- sdpay_currency_rates
CREATE TABLE IF NOT EXISTS sdpay_currency_rates (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  from_currency   VARCHAR(5) NOT NULL,
  to_currency     VARCHAR(5) DEFAULT 'SDG',
  rate            DECIMAL(16,4) NOT NULL,
  source          VARCHAR(50) DEFAULT 'manual',
  valid_date      DATE NOT NULL,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_pair_date (from_currency, to_currency, valid_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- sdpay_disputes
CREATE TABLE IF NOT EXISTS sdpay_disputes (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  txn_id        BIGINT UNSIGNED NOT NULL,
  raised_by     INT UNSIGNED NOT NULL,
  reason        VARCHAR(100) NOT NULL,
  description   TEXT NULL,
  evidence_urls JSON NULL,
  status        ENUM('open','mediation','resolved','escalated','closed') DEFAULT 'open',
  resolution    TEXT NULL,
  resolved_at   DATETIME NULL,
  created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_txn (txn_id),
  INDEX idx_user (raised_by),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- sdpay_family_accounts
CREATE TABLE IF NOT EXISTS sdpay_family_accounts (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  owner_user_id   INT UNSIGNED NOT NULL,
  member_user_id  INT UNSIGNED NOT NULL,
  monthly_sdg     DECIMAL(12,2) DEFAULT 0.00 COMMENT 'مصروف شهري ثابت',
  spending_limit  DECIMAL(12,2) NULL,
  auto_send_day   TINYINT UNSIGNED DEFAULT 1 COMMENT 'يوم التحويل',
  can_view_details TINYINT(1) DEFAULT 1,
  status          ENUM('active','paused','removed') DEFAULT 'active',
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_owner_member (owner_user_id, member_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- sdpay_gifts
CREATE TABLE IF NOT EXISTS sdpay_gifts (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  from_user_id INT UNSIGNED NOT NULL,
  to_user_id   INT UNSIGNED NOT NULL,
  amount_sdg   DECIMAL(12,2) NOT NULL,
  occasion     ENUM('eid','wedding','birth','graduation','ramadan','other') DEFAULT 'other',
  message      VARCHAR(500) NULL,
  txn_id       BIGINT UNSIGNED NULL,
  status       ENUM('pending','delivered','returned') DEFAULT 'pending',
  created_at   DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- sdpay_gov_payments
CREATE TABLE IF NOT EXISTS sdpay_gov_payments (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id      INT UNSIGNED NOT NULL,
  service_id   INT UNSIGNED NOT NULL,
  amount_sdg   DECIMAL(12,2) NOT NULL,
  txn_id       BIGINT UNSIGNED NULL,
  reference_no VARCHAR(100) NULL COMMENT 'رقم مرجعي من الجهة',
  applicant_id VARCHAR(50) NULL COMMENT 'رقم هوية/جواز',
  status       ENUM('pending','completed','failed') DEFAULT 'pending',
  paid_at      DATETIME NULL,
  created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- sdpay_gov_services
CREATE TABLE IF NOT EXISTS sdpay_gov_services (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ministry        VARCHAR(100) NOT NULL,
  service_name_ar VARCHAR(150) NOT NULL,
  service_code    VARCHAR(50) NOT NULL UNIQUE,
  description     TEXT NULL,
  fee_sdg         DECIMAL(14,2) DEFAULT 0.00,
  processing_days INT DEFAULT 1,
  docs_required   JSON NULL,
  payment_account VARCHAR(100) NULL,
  is_active       TINYINT DEFAULT 1,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_ministry (ministry),
  INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- sdpay_idempotency
CREATE TABLE IF NOT EXISTS sdpay_idempotency (
  idem_key   VARCHAR(64) PRIMARY KEY,
  txn_ref    VARCHAR(36) NOT NULL,
  response   JSON NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME DEFAULT (CURRENT_TIMESTAMP + INTERVAL 24 HOUR)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- sdpay_iou
CREATE TABLE IF NOT EXISTS sdpay_iou (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  lender_id    INT UNSIGNED NOT NULL,
  borrower_id  INT UNSIGNED NOT NULL,
  amount_sdg   DECIMAL(12,2) NOT NULL,
  paid_sdg     DECIMAL(12,2) DEFAULT 0.00,
  due_date     DATE NULL,
  notes        VARCHAR(500) NULL,
  status       ENUM('active','partially_paid','paid','disputed') DEFAULT 'active',
  created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_lender   (lender_id),
  INDEX idx_borrower (borrower_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- sdpay_loyalty_points
CREATE TABLE IF NOT EXISTS sdpay_loyalty_points (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id         INT UNSIGNED NOT NULL,
  merchant_id     INT UNSIGNED NULL,
  points_earned   INT DEFAULT 0,
  points_spent    INT DEFAULT 0,
  txn_ref         VARCHAR(50) NULL,
  expires_at      DATETIME NULL,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- sdpay_merchant_qr_codes
CREATE TABLE IF NOT EXISTS sdpay_merchant_qr_codes (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  merchant_id INT UNSIGNED NOT NULL,
  qr_token    VARCHAR(64) NOT NULL UNIQUE,
  qr_image_url VARCHAR(500) NULL,
  scan_count  INT UNSIGNED DEFAULT 0,
  total_received DECIMAL(16,2) DEFAULT 0.00,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- sdpay_merchant_settlements
CREATE TABLE IF NOT EXISTS sdpay_merchant_settlements (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  merchant_id     INT UNSIGNED NOT NULL,
  period_date     DATE NOT NULL,
  txn_count       INT DEFAULT 0,
  gross_sdg       DECIMAL(14,2) DEFAULT 0.00,
  commission_sdg  DECIMAL(14,2) DEFAULT 0.00,
  net_sdg         DECIMAL(14,2) DEFAULT 0.00,
  status          ENUM('pending','processed','paid') DEFAULT 'pending',
  bank_ref        VARCHAR(100) NULL,
  settled_at      DATETIME NULL,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_merchant_date (merchant_id, period_date),
  INDEX idx_date (period_date),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- sdpay_merchants
CREATE TABLE IF NOT EXISTS sdpay_merchants (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id         INT UNSIGNED NOT NULL,
  merchant_code   VARCHAR(20) NOT NULL UNIQUE,
  business_name   VARCHAR(150) NOT NULL,
  business_type   ENUM('pharmacy','supermarket','clinic','hospital','restaurant',
                       'cafe','school','university','workshop','transport',
                       'real_estate','electronics','clothing','other') NOT NULL DEFAULT 'other',
  license_no      VARCHAR(100) NULL,
  tax_id          VARCHAR(50) NULL,
  address         TEXT NULL,
  governorate     VARCHAR(50) NULL,
  city            VARCHAR(50) NULL,
  gps_lat         DECIMAL(10,7) NULL,
  gps_lng         DECIMAL(10,7) NULL,
  qr_static_token VARCHAR(64) NULL,
  qr_image_url    VARCHAR(255) NULL,
  settlement_account VARCHAR(30) NULL,
  settlement_freq ENUM('daily','weekly','monthly') DEFAULT 'daily',
  commission_rate DECIMAL(5,4) DEFAULT 0.0150,
  tier            ENUM('basic','silver','gold','platinum') DEFAULT 'basic',
  monthly_volume_sdg DECIMAL(16,2) DEFAULT 0.00,
  status          ENUM('pending','active','suspended') DEFAULT 'pending',
  approved_at     DATETIME NULL,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user (user_id),
  INDEX idx_type (business_type),
  INDEX idx_city (city),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- sdpay_partner_transactions
CREATE TABLE IF NOT EXISTS sdpay_partner_transactions (
  id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  partner_id       INT UNSIGNED NOT NULL,
  payment_id       VARCHAR(30) NOT NULL UNIQUE,
  session_token    VARCHAR(64) NOT NULL UNIQUE,
  partner_reference VARCHAR(100) NOT NULL,
  amount_sdg       DECIMAL(14,2) NOT NULL,
  description      VARCHAR(200) NULL,
  integration_type ENUM('hosted','inline','api') DEFAULT 'hosted',
  callback_url     VARCHAR(255) NULL,
  customer_phone   VARCHAR(20) NULL,
  txn_ref          VARCHAR(50) NULL,
  status           ENUM('pending','completed','failed','expired','refunded') DEFAULT 'pending',
  completed_at     DATETIME NULL,
  expires_at       DATETIME NOT NULL,
  created_at       DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_partner (partner_id),
  INDEX idx_status (status),
  INDEX idx_reference (partner_id, partner_reference)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- sdpay_partners
CREATE TABLE IF NOT EXISTS sdpay_partners (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id         INT UNSIGNED NOT NULL,
  partner_id      VARCHAR(20) NOT NULL UNIQUE,
  partner_name    VARCHAR(150) NOT NULL,
  partner_type    ENUM('bank','provider','institution','merchant','fintech','gov') NOT NULL,
  website_url     VARCHAR(255) NULL,
  api_key         VARCHAR(60) NOT NULL UNIQUE,
  api_secret_hash VARCHAR(64) NOT NULL,
  webhook_url     VARCHAR(255) NULL,
  status          ENUM('pending','active','suspended') DEFAULT 'pending',
  approved_at     DATETIME NULL,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user (user_id),
  INDEX idx_api_key (api_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- sdpay_pins
CREATE TABLE IF NOT EXISTS sdpay_pins (
  user_id        INT UNSIGNED PRIMARY KEY,
  pin_hash       VARCHAR(255) NOT NULL,
  failed_attempts TINYINT UNSIGNED DEFAULT 0,
  locked_until   DATETIME NULL,
  last_changed   DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at     DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- sdpay_qr_codes
CREATE TABLE IF NOT EXISTS sdpay_qr_codes (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  qr_token        VARCHAR(64) NOT NULL UNIQUE,
  account_id      INT UNSIGNED NOT NULL,
  qr_type         ENUM('static','dynamic','merchant') DEFAULT 'dynamic',
  amount_sdg      DECIMAL(14,2) NULL,
  merchant_name   VARCHAR(100) NULL,
  expires_at      DATETIME NULL,
  used_at         DATETIME NULL,
  scan_count      INT DEFAULT 0,
  status          ENUM('active','used','expired','cancelled') DEFAULT 'active',
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_token (qr_token),
  INDEX idx_account (account_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- sdpay_rosca_members
CREATE TABLE IF NOT EXISTS sdpay_rosca_members (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  rosca_id    INT UNSIGNED NOT NULL,
  user_id     INT UNSIGNED NOT NULL,
  turn_order  TINYINT NOT NULL,
  has_received TINYINT DEFAULT 0,
  status      ENUM('active','inactive','removed') DEFAULT 'active',
  joined_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_member (rosca_id, user_id),
  INDEX idx_rosca (rosca_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- sdpay_roscas
CREATE TABLE IF NOT EXISTS sdpay_roscas (
  id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  creator_user_id     INT UNSIGNED NOT NULL,
  name                VARCHAR(100) NOT NULL,
  description         TEXT NULL,
  monthly_amount_sdg  DECIMAL(14,2) NOT NULL,
  max_members         TINYINT DEFAULT 10,
  order_type          ENUM('sequential','lottery') DEFAULT 'sequential',
  collection_day      TINYINT DEFAULT 1,
  current_round       TINYINT DEFAULT 0,
  status              ENUM('open','active','completed','cancelled') DEFAULT 'open',
  created_at          DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_creator (creator_user_id),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- sdpay_scheduled_payments
CREATE TABLE IF NOT EXISTS sdpay_scheduled_payments (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id         INT UNSIGNED NOT NULL,
  type            ENUM('transfer','bill','family','rosca') NOT NULL,
  target_ref      VARCHAR(100) NOT NULL,
  amount_sdg      DECIMAL(12,2) NOT NULL,
  frequency       ENUM('once','daily','weekly','monthly') DEFAULT 'monthly',
  next_run_date   DATE NOT NULL,
  last_run_date   DATE NULL,
  run_count       SMALLINT UNSIGNED DEFAULT 0,
  max_runs        SMALLINT UNSIGNED NULL,
  status          ENUM('active','paused','completed','failed') DEFAULT 'active',
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user_run (user_id, next_run_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- sdpay_transactions
CREATE TABLE IF NOT EXISTS sdpay_transactions (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  txn_ref         VARCHAR(32) NOT NULL UNIQUE,
  idempotency_key VARCHAR(64) NULL,
  type            ENUM('transfer','qr_pay','cashin','cashout','bill','invest',
                       'rosca','zakat','gift','merchant','refund','fee') NOT NULL,
  from_account    VARCHAR(20) NULL,
  to_account      VARCHAR(20) NULL,
  amount_sdg      DECIMAL(14,2) NOT NULL,
  fee_sdg         DECIMAL(14,2) DEFAULT 0.00,
  net_sdg         DECIMAL(14,2) NOT NULL,
  status          ENUM('pending','processing','completed','failed','reversed','disputed') DEFAULT 'pending',
  channel         ENUM('app','agent','ussd','api','system') DEFAULT 'app',
  agent_id        INT UNSIGNED NULL,
  merchant_id     INT UNSIGNED NULL,
  narration       VARCHAR(255) NULL,
  metadata        JSON NULL,
  reversal_of     BIGINT UNSIGNED NULL,
  can_reverse_until DATETIME NULL,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  completed_at    DATETIME NULL,
  INDEX idx_from (from_account, created_at),
  INDEX idx_to (to_account, created_at),
  INDEX idx_status (status),
  INDEX idx_idempotency (idempotency_key),
  INDEX idx_created (created_at)
,
  IF NOT EXISTS escrow_ref         VARCHAR(30) NULL AFTER bank_response_code) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- sdpay_webhook_logs
CREATE TABLE IF NOT EXISTS sdpay_webhook_logs (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  partner_id  INT UNSIGNED NOT NULL,
  payment_id  VARCHAR(30) NOT NULL,
  event       VARCHAR(50) NOT NULL,
  status      ENUM('sent','failed','retrying') DEFAULT 'sent',
  response    TEXT NULL,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_partner (partner_id),
  INDEX idx_payment (payment_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- sdpay_zakat
CREATE TABLE IF NOT EXISTS sdpay_zakat (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id         INT UNSIGNED NOT NULL,
  savings_sdg     DECIMAL(16,2) NOT NULL,
  nisab_sdg       DECIMAL(12,2) NOT NULL,
  zakat_due_sdg   DECIMAL(12,2) NOT NULL,
  zakat_paid_sdg  DECIMAL(12,2) DEFAULT 0.00,
  beneficiary     ENUM('hayat','direct','project','gov') DEFAULT 'hayat',
  txn_id          BIGINT UNSIGNED NULL,
  hijri_year      SMALLINT UNSIGNED NULL,
  status          ENUM('calculated','paid') DEFAULT 'calculated',
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ── HEALTH RECORDS ──────────────────────────────────────────────

-- pharmacies
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)
,
  IF NOT EXISTS whatsapp VARCHAR(30) NULL,
  whatsapp VARCHAR(30) NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ── EDUCATION + ACADEMY ─────────────────────────────────────────

-- lesson_submissions
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)
,
  IF NOT EXISTS status ENUM('pending','approved','rejected') DEFAULT 'pending',
  status ENUM('pending') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ── STAFF + AVAILABILITY ────────────────────────────────────────

-- staff_members
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
,
  IF NOT EXISTS is_active TINYINT(1) GENERATED ALWAYS AS (active) VIRTUAL,
  user_id INT UNSIGNED NULL,
  is_active TINYINT(1) GENERATED ALWAYS AS (active) VIRTUAL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ── MISCELLANEOUS ───────────────────────────────────────────────

-- accounting_journal
CREATE TABLE IF NOT EXISTS accounting_journal (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  journal_ref     VARCHAR(30) NOT NULL UNIQUE,
  journal_date    DATE NOT NULL,
  description     VARCHAR(255) NOT NULL,
  txn_ref         VARCHAR(50) NULL,
  user_id         INT UNSIGNED NULL,
  txn_type        ENUM('transfer','bill','gov','invest','rosca','zakat','subscription','service_payment','merchant_commission','agent_commission','refund','settlement','adjustment') NOT NULL,
  total_debit     DECIMAL(18,4) NOT NULL,
  total_credit    DECIMAL(18,4) NOT NULL,
  status          ENUM('draft','posted','void') DEFAULT 'posted',
  posted_by       INT UNSIGNED NULL,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_date (journal_date),
  INDEX idx_txn (txn_ref),
  INDEX idx_type (txn_type),
  user_id        INT UNSIGNED NULL,
  specialization VARCHAR(150) NULL,
  is_active      TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- accounting_journal_lines
CREATE TABLE IF NOT EXISTS accounting_journal_lines (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  journal_id      BIGINT UNSIGNED NOT NULL,
  account_code    VARCHAR(20) NOT NULL,
  description     VARCHAR(200) NULL,
  debit_sdg       DECIMAL(18,4) DEFAULT 0.0000,
  credit_sdg      DECIMAL(18,4) DEFAULT 0.0000,
  entity_type     VARCHAR(50) NULL,
  entity_id       INT UNSIGNED NULL,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_journal (journal_id),
  INDEX idx_account (account_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- accounting_ledger
CREATE TABLE IF NOT EXISTS accounting_ledger (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  account_code    VARCHAR(20) NOT NULL,
  journal_id      BIGINT UNSIGNED NOT NULL,
  txn_date        DATE NOT NULL,
  description     VARCHAR(255) NOT NULL,
  debit_sdg       DECIMAL(18,4) DEFAULT 0.0000,
  credit_sdg      DECIMAL(18,4) DEFAULT 0.0000,
  balance_sdg     DECIMAL(18,4) NOT NULL,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_account_date (account_code, txn_date),
  INDEX idx_journal (journal_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- achievement_badges
CREATE TABLE IF NOT EXISTS achievement_badges (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  badge_key   VARCHAR(50) NOT NULL UNIQUE,
  name_ar     VARCHAR(100) NOT NULL,
  name_en     VARCHAR(100) NOT NULL,
  icon        VARCHAR(10) NOT NULL,
  description VARCHAR(300) NULL,
  points      SMALLINT UNSIGNED DEFAULT 0,
  is_active   TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ai_cache
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;

-- ai_feedback
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;

-- ai_logs
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;

-- ai_pricing_audit
CREATE TABLE IF NOT EXISTS ai_pricing_audit (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  entity_type   ENUM('exchange','plan','document','image','video','corporate','setting') NOT NULL,
  entity_key    VARCHAR(100) NOT NULL,
  old_value     VARCHAR(500),
  new_value     VARCHAR(500),
  changed_by    INT UNSIGNED NULL,
  reason        VARCHAR(300),
  created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_entity (entity_type, entity_key),
  INDEX idx_changed_by (changed_by),
  INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ai_requests
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;

-- aml_reports
CREATE TABLE IF NOT EXISTS aml_reports (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  report_type     ENUM('CTR','SAR','monthly') NOT NULL,
  period_start    DATE NOT NULL,
  period_end      DATE NOT NULL,
  total_txns      INT UNSIGNED DEFAULT 0,
  total_amount_sdg DECIMAL(20,2) DEFAULT 0.00,
  suspicious_count INT UNSIGNED DEFAULT 0,
  report_data     JSON NULL,
  submitted       TINYINT(1) DEFAULT 0,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- bank_accounts
CREATE TABLE IF NOT EXISTS bank_accounts (
  id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id             INT UNSIGNED NOT NULL,
  institution_id      INT UNSIGNED NOT NULL,
  account_no          VARCHAR(50) NOT NULL,
  account_name        VARCHAR(150) NOT NULL,
  account_type        ENUM('current','savings','mobile','wallet') DEFAULT 'current',
  iban                VARCHAR(50) NULL,
  currency            VARCHAR(5) DEFAULT 'SDG',
  is_primary          TINYINT DEFAULT 0,
  is_verified         TINYINT DEFAULT 0,
  verified_at         DATETIME NULL,
  verification_method ENUM('micro_deposit','otp','manual','api') NULL,
  is_active           TINYINT DEFAULT 1,
  created_at          DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_user_inst_acc (user_id, institution_id, account_no),
  INDEX idx_user (user_id),
  INDEX idx_institution (institution_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- bank_confirmations
CREATE TABLE IF NOT EXISTS bank_confirmations (
  id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  sdpay_txn_ref     VARCHAR(50) NOT NULL,
  bank_txn_id       VARCHAR(100) NULL,
  institution_id    INT UNSIGNED NOT NULL,
  confirmation_type ENUM('deposit','transfer','withdrawal','escrow','payout','reversal') NOT NULL,
  amount_sdg        DECIMAL(16,4) NOT NULL,
  status            ENUM('pending','confirmed','rejected','timeout') DEFAULT 'pending',
  attempt_count     TINYINT DEFAULT 1,
  confirmed_at      DATETIME NULL,
  error_code        VARCHAR(50) NULL,
  error_message     VARCHAR(500) NULL,
  retry_after       DATETIME NULL,
  created_at        DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_ref (sdpay_txn_ref),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- bank_transactions
CREATE TABLE IF NOT EXISTS bank_transactions (
  id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  bank_txn_id         VARCHAR(100) NOT NULL UNIQUE,
  institution_id      INT UNSIGNED NOT NULL,
  sdpay_txn_ref       VARCHAR(50) NULL,
  txn_type            ENUM('transfer','deposit','withdrawal','hold','release','payout','reversal') NOT NULL,
  from_bank_account   VARCHAR(50) NULL,
  to_bank_account     VARCHAR(50) NULL,
  amount_sdg          DECIMAL(16,4) NOT NULL,
  fee_sdg             DECIMAL(16,4) DEFAULT 0.0000,
  currency            VARCHAR(5) DEFAULT 'SDG',
  bank_status         ENUM('pending','processing','completed','failed','reversed','on_hold') DEFAULT 'pending',
  bank_response_code  VARCHAR(20) NULL,
  bank_response_msg   VARCHAR(500) NULL,
  initiated_at        DATETIME NULL,
  confirmed_at        DATETIME NULL,
  settled_at          DATETIME NULL,
  raw_request         JSON NULL,
  raw_response        JSON NULL,
  created_at          DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_sdpay (sdpay_txn_ref),
  UNIQUE INDEX idx_bank_txn (bank_txn_id) COMMENT 'DB-enforced idempotency — one row per bank_txn_id',
  INDEX idx_status (bank_status),
  INDEX idx_institution (institution_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- bank_webhooks
CREATE TABLE IF NOT EXISTS bank_webhooks (
  id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  institution_id    INT UNSIGNED NOT NULL,
  event_type        VARCHAR(100) NOT NULL,
  bank_txn_id       VARCHAR(100) NULL,
  sdpay_ref         VARCHAR(50) NULL,
  payload           JSON NOT NULL,
  signature         VARCHAR(255) NULL,
  is_verified       TINYINT DEFAULT 0,
  is_processed      TINYINT DEFAULT 0,
  process_result    JSON NULL,
  processed_at      DATETIME NULL,
  received_at       DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_institution (institution_id),
  INDEX idx_processed (is_processed),
  UNIQUE INDEX idx_bank_txn (bank_txn_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;

-- cash_deposit_requests
CREATE TABLE IF NOT EXISTS cash_deposit_requests (
  id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  request_ref       VARCHAR(30) NOT NULL UNIQUE,
  user_id           INT UNSIGNED NOT NULL,
  agent_id          INT UNSIGNED NULL,
  institution_id    INT UNSIGNED NOT NULL,
  amount_sdg        DECIMAL(14,2) NOT NULL,
  bank_receipt_no   VARCHAR(100) NULL,
  bank_branch       VARCHAR(100) NULL,
  depositor_name    VARCHAR(150) NULL,
  bank_txn_id       VARCHAR(100) NULL,
  status            ENUM('pending','submitted','confirmed','rejected','expired') DEFAULT 'pending',
  submitted_at      DATETIME NULL,
  confirmed_at      DATETIME NULL,
  rejection_reason  VARCHAR(255) NULL,
  expires_at        DATETIME NULL,
  created_at        DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user (user_id),
  INDEX idx_agent (agent_id),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- certificates
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;

-- chart_of_accounts
CREATE TABLE IF NOT EXISTS chart_of_accounts (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code            VARCHAR(20) NOT NULL UNIQUE,
  parent_code     VARCHAR(20) NULL,
  name_ar         VARCHAR(100) NOT NULL,
  name_en         VARCHAR(100) NULL,
  account_type    ENUM('asset','liability','equity','revenue','expense') NOT NULL,
  sub_type        VARCHAR(50) NULL,
  currency        VARCHAR(5) DEFAULT 'SDG',
  is_system       TINYINT DEFAULT 1,
  balance         DECIMAL(18,4) DEFAULT 0.0000,
  is_active       TINYINT DEFAULT 1,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_type (account_type),
  INDEX idx_parent (parent_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- community_events
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;

-- content_reports
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;

-- context_memory
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;

-- contract_templates
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;

-- conversation_sessions
CREATE TABLE IF NOT EXISTS conversation_sessions (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id       INT UNSIGNED NOT NULL,
  session_key   VARCHAR(64) NOT NULL UNIQUE,
  messages      JSON NOT NULL DEFAULT (JSON_ARRAY()),
  domain        VARCHAR(50) DEFAULT 'general',
  started_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  last_msg_at   DATETIME DEFAULT CURRENT_TIMESTAMP
                ON UPDATE CURRENT_TIMESTAMP,
  msg_count     SMALLINT UNSIGNED DEFAULT 0,
  INDEX idx_user_session (user_id, last_msg_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- corporate_inquiries
CREATE TABLE IF NOT EXISTS corporate_inquiries (
  id                   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ref_no               VARCHAR(20) NOT NULL UNIQUE COMMENT 'CSP-XXXXX',
  -- Phase 1: Organization
  org_name             VARCHAR(200) NOT NULL,
  org_type             ENUM('private','ngo','educational','group','other') DEFAULT 'private',
  org_sector           VARCHAR(100) NULL,
  employees_count      VARCHAR(20) NULL,
  expected_users       VARCHAR(20) NULL,
  operating_states     JSON NULL,
  years_active         VARCHAR(20) NULL,
  uses_ai              TINYINT(1) DEFAULT 0,
  -- Phase 2: Challenges
  challenges_text      TEXT NULL,
  current_systems      JSON NULL,
  pain_points          JSON NULL,
  priority_challenge   VARCHAR(300) NULL,
  -- Phase 3: Services
  services_selected    JSON NULL,
  -- Phase 4: Technical + Commercial
  tech_requirements    JSON NULL,
  sla_level            ENUM('standard','professional','enterprise') DEFAULT 'standard',
  implementation_timeline VARCHAR(50) NULL,
  budget_min_usd       INT UNSIGNED NULL,
  budget_max_usd       INT UNSIGNED NULL,
  payment_preference   VARCHAR(50) NULL,
  decision_maker       VARCHAR(50) NULL,
  -- Phase 5: Contact
  contact_name         VARCHAR(150) NOT NULL,
  contact_title        VARCHAR(100) NULL,
  contact_email        VARCHAR(200) NOT NULL,
  contact_phone        VARCHAR(30) NULL,
  contact_whatsapp     VARCHAR(30) NULL,
  preferred_contact    VARCHAR(50) NULL,
  best_time            VARCHAR(100) NULL,
  website              VARCHAR(200) NULL,
  -- System
  phase_completed      TINYINT UNSIGNED DEFAULT 0,
  status               ENUM('draft','submitted','analyzing','proposal_ready',
                            'contact_attempted','negotiating','won','lost') DEFAULT 'draft',
  estimated_cost_sdg   INT UNSIGNED NULL,
  estimated_roi_pct    INT UNSIGNED NULL,
  admin_notes          TEXT NULL,
  assigned_to          INT UNSIGNED NULL,
  submitted_at         DATETIME NULL,
  created_at           DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at           DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_status (status),
  INDEX idx_ref (ref_no),
  INDEX idx_date (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- corporate_proposals
CREATE TABLE IF NOT EXISTS corporate_proposals (
  id                      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  inquiry_id              INT UNSIGNED NOT NULL,
  ref_no                  VARCHAR(20) NOT NULL UNIQUE,
  executive_summary_ar    TEXT NULL,
  executive_summary_en    TEXT NULL,
  solution_architecture   JSON NULL,
  services_breakdown      JSON NULL,
  monthly_cost_usd        DECIMAL(10,2) NULL,
  monthly_cost_sdg        INT UNSIGNED NULL,
  setup_cost_usd          DECIMAL(10,2) DEFAULT 0,
  year1_total_sdg         INT UNSIGNED NULL,
  roi_percent             INT UNSIGNED NULL,
  breakeven_months        TINYINT UNSIGNED NULL,
  implementation_plan     JSON NULL,
  sla_terms               JSON NULL,
  full_proposal_html      LONGTEXT NULL,
  ai_model_used           VARCHAR(50) NULL,
  generation_ms           INT UNSIGNED NULL,
  status                  ENUM('draft','sent','accepted','rejected') DEFAULT 'draft',
  admin_final_cost_sdg    INT UNSIGNED NULL COMMENT 'Admin can override the AI estimate',
  admin_notes             TEXT NULL,
  sent_at                 DATETIME NULL,
  created_at              DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_inquiry (inquiry_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- corporate_services_catalog
CREATE TABLE IF NOT EXISTS corporate_services_catalog (
  id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  service_key       VARCHAR(50) NOT NULL UNIQUE,
  name_ar           VARCHAR(150) NOT NULL,
  name_en           VARCHAR(150) NOT NULL,
  category          ENUM('medical','legal','bde','training','supply','api','white_label','sla') NOT NULL,
  base_price_usd    DECIMAL(8,2) NOT NULL,
  price_per_user_usd DECIMAL(6,2) DEFAULT 0,
  setup_fee_usd     DECIMAL(8,2) DEFAULT 0,
  description_ar    VARCHAR(300) NULL,
  is_active         TINYINT(1) DEFAULT 1,
  sort_order        TINYINT UNSIGNED DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- crm_notes
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;

-- crm_timeline
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;

-- currency_rates
CREATE TABLE IF NOT EXISTS currency_rates (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  from_currency CHAR(3) NOT NULL,
  to_currency   CHAR(3) NOT NULL DEFAULT 'SDG',
  rate          DECIMAL(14,4) NOT NULL,
  valid_date    DATE NOT NULL,
  source        VARCHAR(50) DEFAULT 'admin',
  created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_pair_date (from_currency, to_currency, valid_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- data_gaps
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;

-- document_prices
CREATE TABLE IF NOT EXISTS document_prices (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  doc_type        VARCHAR(50) NOT NULL UNIQUE,
  label_ar        VARCHAR(100) NOT NULL,
  price_free_sdg  INT UNSIGNED DEFAULT 0,
  price_plus_sdg  INT UNSIGNED DEFAULT 0,
  price_pro_sdg   INT UNSIGNED DEFAULT 0,
  description_ar  VARCHAR(300) NULL,
  is_active       TINYINT(1) DEFAULT 1,
  updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP
                  ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- escrow_accounts
CREATE TABLE IF NOT EXISTS escrow_accounts (
  id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  escrow_ref        VARCHAR(30) NOT NULL UNIQUE,
  payer_user_id     INT UNSIGNED NOT NULL,
  payee_user_id     INT UNSIGNED NOT NULL,
  service_type      VARCHAR(50) NOT NULL,
  booking_id        INT UNSIGNED NULL,
  amount_sdg        DECIMAL(14,2) NOT NULL,
  platform_fee_sdg  DECIMAL(14,2) DEFAULT 0.00,
  payee_net_sdg     DECIMAL(14,2) NOT NULL,
  institution_id    INT UNSIGNED NULL,
  bank_hold_ref     VARCHAR(100) NULL,
  status            ENUM('holding','released','refunded','disputed') DEFAULT 'holding',
  held_at           DATETIME DEFAULT CURRENT_TIMESTAMP,
  release_condition VARCHAR(255) NULL,
  auto_release_at   DATETIME NULL,
  released_at       DATETIME NULL,
  released_by       ENUM('user','auto','admin','dispute') NULL,
  bank_txn_id       VARCHAR(100) NULL,
  created_at        DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_payer (payer_user_id),
  INDEX idx_payee (payee_user_id),
  INDEX idx_status (status),
  INDEX idx_booking (booking_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- event_participants
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;

-- events
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;

-- exchange_rates
CREATE TABLE IF NOT EXISTS exchange_rates (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  rate_usd_sdg   DECIMAL(10,2) NOT NULL,
  previous_rate  DECIMAL(10,2) NULL,
  change_pct     DECIMAL(6,2)  NULL,
  source         ENUM('manual','auto','api') DEFAULT 'manual',
  note           VARCHAR(300) NULL,
  set_by         INT UNSIGNED NULL,
  is_active      TINYINT(1) DEFAULT 0,
  created_at     DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_active (is_active),
  INDEX idx_date (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- external_investment_deposits
CREATE TABLE IF NOT EXISTS external_investment_deposits (
  id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  deposit_ref       VARCHAR(30) NOT NULL UNIQUE,
  project_id        INT UNSIGNED NOT NULL,
  institution_id    INT UNSIGNED NOT NULL,
  bank_txn_id       VARCHAR(100) NULL,
  amount_sdg        DECIMAL(16,4) NOT NULL,
  deposit_type      ENUM('profit_distribution','capital_return','partial_return') NOT NULL,
  period_label      VARCHAR(50) NULL,
  bank_receipt_no   VARCHAR(100) NULL,
  status            ENUM('pending','confirmed','distributed','failed') DEFAULT 'pending',
  confirmed_at      DATETIME NULL,
  distributed_at    DATETIME NULL,
  created_at        DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_project (project_id),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- family_members
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;

-- financial_institutions
CREATE TABLE IF NOT EXISTS financial_institutions (
  id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code              VARCHAR(20) NOT NULL UNIQUE,
  name_ar           VARCHAR(150) NOT NULL,
  name_en           VARCHAR(150) NULL,
  institution_type  ENUM('bank','microfinance','mobile_money','ebs','other') DEFAULT 'bank',
  swift_code        VARCHAR(20) NULL,
  api_url           VARCHAR(255) NULL,
  api_version       VARCHAR(20) DEFAULT 'v1',
  webhook_url       VARCHAR(255) NULL,
  sandbox_api_url   VARCHAR(255) NULL,
  is_sandbox        TINYINT DEFAULT 1,
  supports_transfer TINYINT DEFAULT 1,
  supports_escrow   TINYINT DEFAULT 0,
  supports_hold     TINYINT DEFAULT 0,
  min_transfer_sdg  DECIMAL(14,2) DEFAULT 10.00,
  max_transfer_sdg  DECIMAL(14,2) DEFAULT 10000000.00,
  transfer_fee_pct  DECIMAL(6,4) DEFAULT 0.0000,
  settlement_time_hours INT DEFAULT 24,
  logo_url          VARCHAR(255) NULL,
  contact_email     VARCHAR(150) NULL,
  is_active         TINYINT DEFAULT 1,
  priority_order    INT DEFAULT 99,
  created_at        DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_code (code),
  INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- flow_sessions
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
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;

-- fraud_flags
CREATE TABLE IF NOT EXISTS fraud_flags (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED NULL,
  ip_hash     VARCHAR(64) NULL,
  flag_type   VARCHAR(50) NOT NULL,
  details     JSON NULL,
  severity    ENUM('low','medium','high','critical') DEFAULT 'medium',
  reviewed    TINYINT(1) DEFAULT 0,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user (user_id),
  INDEX idx_type (flag_type),
  INDEX idx_reviewed (reviewed)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- image_prices
CREATE TABLE IF NOT EXISTS image_prices (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  size_key        VARCHAR(30) NOT NULL UNIQUE COMMENT '512x512, 1024x1024, 1024x1792',
  label_ar        VARCHAR(100) NOT NULL,
  base_cost_usd   DECIMAL(10,6) NOT NULL DEFAULT 0 COMMENT 'تكلفة المزود لكل صورة',
  price_free_sdg  INT UNSIGNED DEFAULT 0,
  price_plus_sdg  INT UNSIGNED DEFAULT 0,
  price_pro_sdg   INT UNSIGNED DEFAULT 0,
  provider        VARCHAR(30) DEFAULT 'gemini' COMMENT 'gemini|dalle|stable',
  is_active       TINYINT(1) DEFAULT 1,
  updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- inquiries
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;

-- institution_api_configs
CREATE TABLE IF NOT EXISTS institution_api_configs (
  id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  institution_id      INT UNSIGNED NOT NULL,
  env                 ENUM('sandbox','production') DEFAULT 'sandbox',
  api_key_enc         TEXT NOT NULL,
  api_secret_enc      TEXT NULL,
  merchant_id         VARCHAR(100) NULL,
  terminal_id         VARCHAR(100) NULL,
  extra_params        JSON NULL,
  is_active           TINYINT DEFAULT 1,
  last_tested_at      DATETIME NULL,
  test_result         ENUM('ok','fail','pending') DEFAULT 'pending',
  created_at          DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at          DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uk_inst_env (institution_id, env)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- invest
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;

-- investment_applications
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;

-- investment_interest
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;

-- investment_projects
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;

-- ip_blocks
CREATE TABLE IF NOT EXISTS ip_blocks (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ip          VARCHAR(45) NOT NULL,
  reason      VARCHAR(200),
  blocked_by  INT UNSIGNED NULL,
  expires_at  DATETIME NOT NULL,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_ip_active (ip, expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- items
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;

-- kpi_daily
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;

-- kv_cache
CREATE TABLE IF NOT EXISTS kv_cache (
  cache_key  VARCHAR(200) NOT NULL PRIMARY KEY,
  value      MEDIUMTEXT NOT NULL,
  expires_at DATETIME NOT NULL,
  INDEX idx_exp (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- lesson_orders
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;

-- lesson_subscriptions
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;

-- lessons
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;

-- market_intelligence
CREATE TABLE IF NOT EXISTS market_intelligence (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  category    VARCHAR(50) NOT NULL,
  city        VARCHAR(50) DEFAULT 'الخرطوم',
  metric_key  VARCHAR(100) NOT NULL,
  metric_val  DECIMAL(12,2) NOT NULL,
  recorded_at DATE NOT NULL,
  INDEX idx_cat_date (category, recorded_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- medical_records
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;

-- mobile_money_txns
CREATE TABLE IF NOT EXISTS mobile_money_txns (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id         INT UNSIGNED NOT NULL,
  provider        ENUM('zain','mtn','eceap','other') NOT NULL,
  type            ENUM('cashin','cashout') NOT NULL,
  amount_sdg      DECIMAL(14,2) NOT NULL,
  phone_no        VARCHAR(20) NOT NULL,
  external_ref    VARCHAR(100) NULL,
  sdpay_txn_id    BIGINT UNSIGNED NULL,
  status          ENUM('pending','completed','failed') DEFAULT 'pending',
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- neighborhood_zones
CREATE TABLE IF NOT EXISTS neighborhood_zones (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  state_name VARCHAR(100) NOT NULL,
  city_name  VARCHAR(100) NULL,
  zone_code  VARCHAR(20) NULL,
  is_active  TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- neighborhoods
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;

-- partners
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;

-- payment_proofs
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)
,
  IF NOT EXISTS file_hash VARCHAR(64) NULL,
  UNIQUE INDEX idx_proof_invoice (invoice_id),
  UNIQUE INDEX idx_proof_invoice (invoice_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- payments
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;

-- platform_revenue
CREATE TABLE IF NOT EXISTS platform_revenue (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  revenue_date    DATE NOT NULL,
  revenue_type    ENUM('transfer_fee','merchant_commission','bill_fee','gov_fee','invest_fee','subscription','service_commission','partner_fee') NOT NULL,
  gross_sdg       DECIMAL(16,4) DEFAULT 0.0000,
  tax_sdg         DECIMAL(16,4) DEFAULT 0.0000,
  net_sdg         DECIMAL(16,4) DEFAULT 0.0000,
  txn_count       INT DEFAULT 0,
  journal_ref     VARCHAR(30) NULL,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_date_type (revenue_date, revenue_type),
  INDEX idx_date (revenue_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 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_by   INT UNSIGNED,
  created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_policy_ver (policy_key, version),
  INDEX idx_key (policy_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- posts
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;

-- prescription_requests
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;

-- prescription_responses
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;

-- provider_applications
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;

-- provider_earnings_display
CREATE TABLE IF NOT EXISTS provider_earnings_display (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  domain          VARCHAR(50) NOT NULL,
  city            VARCHAR(50) DEFAULT 'الخرطوم',
  avg_monthly_sdg INT UNSIGNED DEFAULT 0,
  min_monthly_sdg INT UNSIGNED DEFAULT 0,
  max_monthly_sdg INT UNSIGNED DEFAULT 0,
  active_providers SMALLINT UNSIGNED DEFAULT 0,
  demand_level    ENUM('low','medium','high','critical') DEFAULT 'medium',
  weekly_requests INT UNSIGNED DEFAULT 0,
  updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP
                  ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_domain_city (domain, city)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- provider_plans
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;

-- provider_potential
CREATE TABLE IF NOT EXISTS provider_potential (
  id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id           INT UNSIGNED NOT NULL UNIQUE,
  detected_domain   VARCHAR(50) NULL,
  confidence_score  DECIMAL(4,2) DEFAULT 0.00,
  user_profile      ENUM('young','professional','senior','woman') NULL,
  signals           JSON NULL,
  stage             TINYINT UNSIGNED DEFAULT 0,
  last_nudge_at     DATETIME NULL,
  nudge_response    ENUM('positive','negative','ignored') NULL,
  converted         TINYINT(1) DEFAULT 0,
  created_at        DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at        DATETIME DEFAULT CURRENT_TIMESTAMP
                    ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_score (confidence_score),
  INDEX idx_stage (stage),
  INDEX idx_domain (detected_domain)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- providers
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,
  price_sdg            DECIMAL(10,2) DEFAULT 0.00,
  available            TINYINT(1)   DEFAULT 1,
  verified             TINYINT(1)   DEFAULT 0,
  bank_account         VARCHAR(30)  NULL,
  created_at           DATETIME     DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- reconciliation_batches
CREATE TABLE IF NOT EXISTS reconciliation_batches (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  batch_ref       VARCHAR(30) NOT NULL UNIQUE,
  batch_date      DATE NOT NULL,
  batch_type      ENUM('daily_merchant','daily_agent','daily_platform','daily_invest','monthly_summary') NOT NULL,
  entity_id       INT UNSIGNED NULL,
  entity_type     VARCHAR(30) NULL,
  opening_balance DECIMAL(16,4) DEFAULT 0.0000,
  total_credits   DECIMAL(16,4) DEFAULT 0.0000,
  total_debits    DECIMAL(16,4) DEFAULT 0.0000,
  closing_balance DECIMAL(16,4) DEFAULT 0.0000,
  txn_count       INT DEFAULT 0,
  status          ENUM('pending','processing','completed','discrepancy') DEFAULT 'pending',
  discrepancy_sdg DECIMAL(16,4) DEFAULT 0.0000,
  notes           TEXT NULL,
  processed_at    DATETIME NULL,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_date (batch_date),
  INDEX idx_type (batch_type),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- rosca_cycles
CREATE TABLE IF NOT EXISTS rosca_cycles (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  group_id     INT UNSIGNED NOT NULL,
  cycle_no     TINYINT UNSIGNED NOT NULL,
  recipient_id INT UNSIGNED NOT NULL COMMENT 'المستفيد هذه الدورة',
  amount_sdg   DECIMAL(14,2) NOT NULL,
  due_date     DATE NOT NULL,
  paid_date    DATE NULL,
  status       ENUM('pending','collecting','paid','defaulted') DEFAULT 'pending',
  created_at   DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- rosca_groups
CREATE TABLE IF NOT EXISTS rosca_groups (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name            VARCHAR(200) NOT NULL,
  created_by      INT UNSIGNED NOT NULL,
  contribution_sdg DECIMAL(12,2) NOT NULL COMMENT 'مبلغ الاشتراك الشهري',
  member_count    TINYINT UNSIGNED NOT NULL,
  cycle_count     TINYINT UNSIGNED DEFAULT 0,
  start_date      DATE NULL,
  draw_type       ENUM('random','sequence','need_based') DEFAULT 'random',
  status          ENUM('forming','active','completed','cancelled') DEFAULT 'forming',
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- service_payments
CREATE TABLE IF NOT EXISTS service_payments (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  txn_ref         VARCHAR(50) NOT NULL UNIQUE,
  user_id         INT UNSIGNED NOT NULL,
  provider_user_id INT UNSIGNED NOT NULL,
  booking_id      INT UNSIGNED NULL,
  service_type    ENUM('medical','pharmacy','legal','consultation','education','agriculture','craft','mental','other') NOT NULL,
  service_description VARCHAR(200) NULL,
  gross_sdg       DECIMAL(14,2) NOT NULL,
  platform_fee_sdg DECIMAL(14,2) DEFAULT 0.00,
  provider_net_sdg DECIMAL(14,2) NOT NULL,
  fee_pct         DECIMAL(5,3) DEFAULT 0.100,
  sdpay_txn_ref   VARCHAR(50) NULL,
  status          ENUM('pending','processing','completed','refunded','disputed') DEFAULT 'pending',
  paid_at         DATETIME NULL,
  journal_ref     VARCHAR(30) NULL,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user (user_id),
  INDEX idx_provider (provider_user_id),
  INDEX idx_booking (booking_id),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- service_ratings
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;

-- services
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;

-- sessions
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;

-- settlement_accounts
CREATE TABLE IF NOT EXISTS settlement_accounts (
  id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  institution_id    INT UNSIGNED NOT NULL,
  account_no        VARCHAR(50) NOT NULL,
  account_name      VARCHAR(150) NOT NULL,
  account_type      ENUM('nostro','vostro','settlement','escrow_pool') DEFAULT 'settlement',
  currency          VARCHAR(5) DEFAULT 'SDG',
  balance_sdg       DECIMAL(18,4) DEFAULT 0.0000,
  last_settled_at   DATETIME NULL,
  is_active         TINYINT DEFAULT 1,
  created_at        DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_institution (institution_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- staff
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;

-- staff_activity_log
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;

-- subscriptions
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;

-- 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;

-- support_ticket_replies
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;

-- support_tickets
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;

-- system_metrics
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;

-- system_settings
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;

-- tasks
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;

-- tele_sessions
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;

-- user_devices
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;

-- user_progress
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_services_history
CREATE TABLE IF NOT EXISTS user_services_history (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id         INT UNSIGNED NOT NULL,
  service_type    VARCHAR(50) NOT NULL,
  provider_user_id INT UNSIGNED NULL,
  booking_id      INT UNSIGNED NULL,
  service_ref     VARCHAR(50) NULL,
  payment_ref     VARCHAR(50) NULL,
  amount_sdg      DECIMAL(14,2) NULL,
  status          ENUM('requested','matched','in_progress','completed','cancelled','refunded') DEFAULT 'requested',
  rating          TINYINT NULL,
  review_text     TEXT NULL,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  completed_at    DATETIME NULL,
  INDEX idx_user (user_id),
  INDEX idx_provider (provider_user_id),
  INDEX idx_status (status),
  INDEX idx_date (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- user_subscriptions
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;

-- video_prices
CREATE TABLE IF NOT EXISTS video_prices (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  duration_key    VARCHAR(30) NOT NULL UNIQUE COMMENT '5s, 15s, 30s, 60s',
  label_ar        VARCHAR(100) NOT NULL,
  base_cost_usd   DECIMAL(10,6) NOT NULL DEFAULT 0 COMMENT 'تكلفة المزود لكل فيديو',
  price_free_sdg  INT UNSIGNED DEFAULT 0,
  price_plus_sdg  INT UNSIGNED DEFAULT 0,
  price_pro_sdg   INT UNSIGNED DEFAULT 0,
  provider        VARCHAR(30) DEFAULT 'veo',
  is_active       TINYINT(1) DEFAULT 1,
  updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- wallet_topup
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;

-- wallet_topup_requests
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;

-- withdrawal_requests
CREATE TABLE IF NOT EXISTS withdrawal_requests (
  id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  request_ref       VARCHAR(30) NOT NULL UNIQUE,
  user_id           INT UNSIGNED NOT NULL,
  agent_id          INT UNSIGNED NULL,
  bank_account_id   INT UNSIGNED NULL,
  institution_id    INT UNSIGNED NOT NULL,
  amount_sdg        DECIMAL(14,2) NOT NULL,
  fee_sdg           DECIMAL(14,2) DEFAULT 0.00,
  net_sdg           DECIMAL(14,2) NOT NULL,
  withdrawal_type   ENUM('agent_cash','bank_transfer','atm') DEFAULT 'agent_cash',
  bank_txn_id       VARCHAR(100) NULL,
  status            ENUM('pending','processing','approved','disbursed','failed','cancelled') DEFAULT 'pending',
  approved_at       DATETIME NULL,
  disbursed_at      DATETIME NULL,
  failure_reason    VARCHAR(255) NULL,
  pin_verified_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;


SET FOREIGN_KEY_CHECKS = 1;
