
-- ══════════════════════════════════════════════════════════════════
-- SUDANNA v23 — SAFE MIGRATION (run after initial schema import)
-- All statements use ALTER TABLE ... ADD COLUMN IF NOT EXISTS pattern
-- Safe to run multiple times
-- ══════════════════════════════════════════════════════════════════

-- Ensure users table has FCM token column
ALTER TABLE users ADD COLUMN IF NOT EXISTS fcm_token VARCHAR(500) NULL AFTER email;

-- Ensure invoices has all required columns  
ALTER TABLE invoices ADD COLUMN IF NOT EXISTS reference_code VARCHAR(60) NULL;
ALTER TABLE invoices ADD COLUMN IF NOT EXISTS expires_at DATETIME NULL;
ALTER TABLE invoices ADD COLUMN IF NOT EXISTS paid_at DATETIME NULL;
ALTER TABLE invoices ADD COLUMN IF NOT EXISTS payment_method ENUM('offline','gateway') DEFAULT 'offline';
ALTER TABLE invoices ADD COLUMN IF NOT EXISTS bank_account VARCHAR(30) DEFAULT '1821425';
ALTER TABLE invoices ADD COLUMN IF NOT EXISTS gateway_txn_id VARCHAR(120) NULL;
ALTER TABLE invoices ADD COLUMN IF NOT EXISTS webhook_payload JSON NULL;

-- Add UNIQUE constraint on reference_code safely
-- (ignore error if already exists)
ALTER TABLE invoices ADD UNIQUE INDEX IF NOT EXISTS idx_ref_unique (reference_code);

-- Ensure wallet has all columns
ALTER TABLE wallet ADD COLUMN IF NOT EXISTS kyc_level TINYINT DEFAULT 0;
ALTER TABLE wallet ADD COLUMN IF NOT EXISTS daily_limit DECIMAL(12,2) DEFAULT 5000.00;
ALTER TABLE wallet ADD COLUMN IF NOT EXISTS monthly_limit DECIMAL(12,2) DEFAULT 50000.00;
ALTER TABLE wallet ADD COLUMN IF NOT EXISTS hold_balance DECIMAL(12,2) DEFAULT 0.00;
ALTER TABLE wallet ADD COLUMN IF NOT EXISTS total_deposited DECIMAL(12,2) DEFAULT 0.00;
ALTER TABLE wallet ADD COLUMN IF NOT EXISTS total_withdrawn DECIMAL(12,2) DEFAULT 0.00;

-- Ensure payment_proofs has file_hash
ALTER TABLE payment_proofs ADD COLUMN IF NOT EXISTS file_hash VARCHAR(64) NULL;
ALTER TABLE payment_proofs ADD COLUMN IF NOT EXISTS reviewed_by INT NULL;
ALTER TABLE payment_proofs ADD COLUMN IF NOT EXISTS reviewed_at DATETIME NULL;
ALTER TABLE payment_proofs ADD UNIQUE INDEX IF NOT EXISTS idx_proof_invoice (invoice_id);

-- Ensure payment_receipts table exists (may have been missed)
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;

-- Auto-create wallets for existing users
INSERT IGNORE INTO wallet (user_id, balance_sdg, status, created_at)
SELECT id, 0.00, 'active', NOW() FROM users;

-- Verify
SELECT 
  (SELECT COUNT(*) FROM invoices) AS invoices,
  (SELECT COUNT(*) FROM wallet)   AS wallets,
  (SELECT COUNT(*) FROM accounts) AS accounts,
  (SELECT COUNT(*) FROM payment_receipts) AS receipts;

-- ══ v23.3 PATCH — billing + notifications + execution tracking ══════
-- Safe to run multiple times (IF NOT EXISTS + MODIFY safe)

-- 1. Fix wallet_transactions.ref_type ENUM (adds billing values)
ALTER TABLE wallet_transactions
  MODIFY COLUMN ref_type
    ENUM('topup','booking','payout','transfer','refund','commission',
         'billing_reserve','billing_commit','billing_rollback')
    NOT NULL;

-- 2. Add notifications table (referenced by notifications.php)
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;

-- 3. Add execution tracking tables
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)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. Add policy_versions table
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;

-- 5. Seed default policies
INSERT IGNORE INTO policy_versions (policy_key, version, value, is_active) VALUES
('max_daily_requests',   1, '200',   1),
('max_booking_amount',   1, '5000',  1),
('free_tier_limit',      1, '20',    1),
('plus_tier_limit',      1, '60',    1),
('pro_tier_limit',       1, '200',   1),
('risk_block_threshold', 1, '0.85',  1),
('billing_grace_secs',   1, '30',    1);

-- 6. Add missing index on billing_reserves
ALTER TABLE billing_reserves
  ADD INDEX IF NOT EXISTS idx_user_status (user_id, status);

-- 7. Update schema to add hold_balance to wallet if not there
ALTER TABLE wallet
  ADD COLUMN IF NOT EXISTS hold_balance DECIMAL(12,2) DEFAULT 0.00 AFTER balance_sdg;

-- Verify
SELECT 'migration_v23_3_complete' AS status,
  (SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name='notifications') AS has_notifications,
  (SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name='execution_log') AS has_execution_log,
  (SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name='policy_versions') AS has_policy_versions;

-- ═══════════════════════════════════════════════════════════════
-- MIGRATION: Pricing System + Corporate Solutions
-- Date: 2026-04-28 | Sudanna v23
-- ═══════════════════════════════════════════════════════════════

-- Fix 3: wallet_transactions ref_type
ALTER TABLE wallet_transactions
  MODIFY COLUMN ref_type ENUM(
    'topup','booking','payout','transfer',
    'refund','commission',
    'subscription','doc_issuance','credit_deduct','corporate'
  ) NOT NULL;

-- Fix 4: subscription_plans — add USD + override columns
ALTER TABLE subscription_plans
  ADD COLUMN IF NOT EXISTS price_usd    DECIMAL(8,2) DEFAULT 0.00,
  ADD COLUMN IF NOT EXISTS name_ar      VARCHAR(100) DEFAULT '',
  ADD COLUMN IF NOT EXISTS name_en      VARCHAR(100) DEFAULT '',
  ADD COLUMN IF NOT EXISTS ai_limit     INT UNSIGNED DEFAULT 20,
  ADD COLUMN IF NOT EXISTS booking_limit INT UNSIGNED DEFAULT 2,
  ADD COLUMN IF NOT EXISTS doc_credit_sdg INT UNSIGNED DEFAULT 0,
  ADD COLUMN IF NOT EXISTS sdg_override  TINYINT(1) DEFAULT 1
                            COMMENT '1=use price_sdg directly, 0=calculate from USD×rate';

-- New Table 1: 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;

-- New Table 2: exchange_rates (historical log)
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;

-- New Table 3: 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;

-- New Table 4: 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;

-- New Table 5: 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;

-- New Table 6: 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;

-- New Table 7: 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;



-- ── fraud_flags table ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS fraud_flags (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id      INT UNSIGNED NULL,
  ip_hash      VARCHAR(64)  NOT NULL,
  flag_type    ENUM('multiple_accounts','payment_fraud','abuse',
                    'spam','suspicious_activity') NOT NULL,
  details      JSON NULL,
  severity     ENUM('low','medium','high','critical') DEFAULT 'medium',
  status       ENUM('active','reviewed','cleared') DEFAULT 'active',
  reviewed_by  INT UNSIGNED NULL,
  created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user   (user_id),
  INDEX idx_status (status),
  INDEX idx_type   (flag_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── user_suggestions table ────────────────────────────────────────
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')
                    NOT NULL 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      INT 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,
  -- Auto-actions
  auto_ticket_id    INT UNSIGNED NULL COMMENT 'إذا bug → تذكرة دعم',
  -- 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_votes    (votes_count),
  INDEX idx_user     (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── suggestion_votes table ────────────────────────────────────────
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;

-- ── neighborhood_zones (18 ولاية سودانية) ─────────────────────────
CREATE TABLE IF NOT EXISTS neighborhood_zones (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  state_name VARCHAR(100) NOT NULL,
  state_ar   VARCHAR(100) NOT NULL,
  state_code VARCHAR(10)  NOT NULL UNIQUE,
  is_active  TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ═══════════════════════════════════════════════════════════════
-- MIGRATION ADD: Suggestions + Fraud + Missing Tables
-- ═══════════════════════════════════════════════════════════════

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

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

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

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

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

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


-- ═══════════════════════════════════════════════════════════════
-- Personal Assistant + ScoringAgent Tables
-- ═══════════════════════════════════════════════════════════════

-- جلسات المحادثة الشخصية
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;

-- تقييم المستخدمين المحتملين كمزودين
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;

-- بيانات الكسب لعرضها للمزودين المحتملين
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;

-- إحصاءات السوق اليومية
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;

-- ═══════════════════════════════════════════════════════════════
-- SUDANNA PAY — Complete Schema Migration
-- ═══════════════════════════════════════════════════════════════

-- ── حسابات Sudanna Pay ────────────────────────────────────────
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 COMMENT 'SD-YYYY-NNNNNN',
  balance_sdg     DECIMAL(16,2) DEFAULT 0.00,
  hold_sdg        DECIMAL(16,2) DEFAULT 0.00,
  kyc_level       TINYINT UNSIGNED DEFAULT 1 COMMENT '1=phone,2=id,3=full',
  daily_limit_sdg DECIMAL(16,2) DEFAULT 50000.00,
  monthly_spent   DECIMAL(16,2) DEFAULT 0.00,
  status          ENUM('active','suspended','frozen','closed') DEFAULT 'active',
  last_txn_at     DATETIME NULL,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user (user_id),
  INDEX idx_account (account_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── معاملات Sudanna Pay ───────────────────────────────────────
CREATE TABLE IF NOT EXISTS sdpay_transactions (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  txn_ref         VARCHAR(36) NOT NULL UNIQUE COMMENT 'UUID',
  idempotency_key VARCHAR(64) NULL UNIQUE,
  type            ENUM('transfer','qr_pay','cashin','cashout','bill',
                       'gov','invest','gift','zakat','tip','rosca',
                       'refund','fee','adjustment') NOT NULL,
  from_account_id INT UNSIGNED NULL,
  to_account_id   INT UNSIGNED NULL,
  amount_sdg      DECIMAL(16,2) NOT NULL,
  fee_sdg         DECIMAL(16,2) DEFAULT 0.00,
  net_sdg         DECIMAL(16,2) NOT NULL,
  channel         ENUM('app','agent','ussd','api','auto') DEFAULT 'app',
  status          ENUM('pending','completed','failed','reversed','disputed') DEFAULT 'pending',
  reversal_deadline DATETIME NULL,
  narration       VARCHAR(255) NULL,
  merchant_id     INT UNSIGNED NULL,
  agent_id        INT UNSIGNED NULL,
  reference_meta  JSON NULL,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  completed_at    DATETIME NULL,
  INDEX idx_from (from_account_id, created_at),
  INDEX idx_to   (to_account_id, created_at),
  INDEX idx_ref  (txn_ref),
  INDEX idx_date (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

-- ── PIN المعاملات المالية ──────────────────────────────────────
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;

-- ── 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,
  type         ENUM('static','dynamic') DEFAULT 'dynamic',
  amount_sdg   DECIMAL(16,2) NULL COMMENT 'NULL = مبلغ مفتوح',
  merchant_id  INT UNSIGNED NULL,
  scan_count   INT UNSIGNED DEFAULT 0,
  status       ENUM('active','used','expired') DEFAULT 'active',
  expires_at   DATETIME NULL,
  used_at      DATETIME NULL,
  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;

-- ── التجار ────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS sdpay_merchants (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id         INT UNSIGNED NOT NULL UNIQUE,
  merchant_code   VARCHAR(20) NOT NULL UNIQUE,
  business_name   VARCHAR(200) NOT NULL,
  business_type   ENUM('pharmacy','supermarket','restaurant','clinic',
                       'school','transport','utility','gov','craft',
                       'beauty','tech','other') NOT NULL,
  license_no      VARCHAR(100) NULL,
  tax_id          VARCHAR(50) NULL,
  governorate     VARCHAR(50) NULL,
  city            VARCHAR(50) NULL,
  address         TEXT NULL,
  gps_lat         DECIMAL(10,8) NULL,
  gps_lng         DECIMAL(11,8) NULL,
  qr_static_token VARCHAR(64) NULL,
  settlement_account VARCHAR(50) NULL COMMENT 'IBAN or wallet account_no',
  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  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_type (business_type),
  INDEX idx_gov  (governorate, city),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── QR ثابت للتجار ───────────────────────────────────────────
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;

-- ── تسوية التجار ─────────────────────────────────────────────
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 UNSIGNED DEFAULT 0,
  gross_sdg     DECIMAL(16,2) DEFAULT 0.00,
  commission_sdg DECIMAL(16,2) DEFAULT 0.00,
  net_sdg       DECIMAL(16,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 uniq_merchant_date (merchant_id, period_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── الوكلاء ───────────────────────────────────────────────────
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(200) NOT NULL,
  governorate     VARCHAR(50) NOT NULL,
  city            VARCHAR(50) NOT NULL,
  address         TEXT NULL,
  gps_lat         DECIMAL(10,8) NULL,
  gps_lng         DECIMAL(11,8) NULL,
  float_balance   DECIMAL(16,2) DEFAULT 0.00 COMMENT 'رصيد العمل',
  daily_limit_sdg DECIMAL(16,2) DEFAULT 100000.00,
  commission_rate DECIMAL(5,4) DEFAULT 0.0050,
  total_earned    DECIMAL(16,2) DEFAULT 0.00,
  kyc_verified    TINYINT(1) DEFAULT 0,
  status          ENUM('pending','active','suspended') DEFAULT 'pending',
  approved_at     DATETIME NULL,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_gov (governorate, city),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── معاملات الوكلاء ───────────────────────────────────────────
CREATE TABLE IF NOT EXISTS sdpay_agent_transactions (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  agent_id    INT UNSIGNED NOT NULL,
  txn_id      BIGINT UNSIGNED NOT NULL,
  type        ENUM('cashin','cashout','registration') NOT NULL,
  amount_sdg  DECIMAL(16,2) NOT NULL,
  commission  DECIMAL(16,2) DEFAULT 0.00,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_agent (agent_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── مزودو الفواتير ────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS sdpay_bill_providers (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code            VARCHAR(30) NOT NULL UNIQUE,
  name_ar         VARCHAR(200) NOT NULL,
  name_en         VARCHAR(200) NULL,
  category        ENUM('electricity','water','telecom','internet',
                       'tv','insurance','education','health','other') NOT NULL,
  logo_url        VARCHAR(500) NULL,
  api_endpoint    VARCHAR(500) NULL,
  api_key_enc     TEXT NULL COMMENT 'encrypted',
  validation_field VARCHAR(50) DEFAULT 'account_no',
  commission_pct  DECIMAL(5,4) DEFAULT 0.0050,
  fixed_fee_sdg   DECIMAL(10,2) DEFAULT 0.00,
  min_amount      DECIMAL(10,2) DEFAULT 100.00,
  max_amount      DECIMAL(12,2) DEFAULT 1000000.00,
  is_active       TINYINT(1) DEFAULT 1,
  sort_order      SMALLINT UNSIGNED DEFAULT 100
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── مدفوعات الفواتير ──────────────────────────────────────────
CREATE TABLE IF NOT EXISTS sdpay_bill_payments (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id         INT UNSIGNED NOT NULL,
  provider_id     INT UNSIGNED NOT NULL,
  account_ref     VARCHAR(100) NOT NULL COMMENT 'رقم العداد/الحساب',
  amount_sdg      DECIMAL(12,2) NOT NULL,
  fee_sdg         DECIMAL(10,2) DEFAULT 0.00,
  txn_id          BIGINT UNSIGNED NULL,
  token_received  VARCHAR(200) NULL COMMENT 'رمز الشحن للكهرباء',
  status          ENUM('pending','completed','failed') DEFAULT 'pending',
  paid_at         DATETIME NULL,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user (user_id),
  INDEX idx_provider (provider_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── الخدمات الحكومية ──────────────────────────────────────────
CREATE TABLE IF NOT EXISTS sdpay_gov_services (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ministry_code   VARCHAR(30) NOT NULL,
  ministry_name   VARCHAR(200) NOT NULL,
  service_code    VARCHAR(50) NOT NULL UNIQUE,
  service_name_ar VARCHAR(300) NOT NULL,
  fee_sdg         DECIMAL(12,2) NOT NULL,
  fee_type        ENUM('fixed','percentage','tiered') DEFAULT 'fixed',
  processing_days TINYINT UNSIGNED DEFAULT 1,
  docs_required   JSON NULL,
  payment_account VARCHAR(100) NULL,
  is_active       TINYINT(1) DEFAULT 1,
  sort_order      SMALLINT UNSIGNED DEFAULT 100
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── مدفوعات حكومية ────────────────────────────────────────────
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;

-- ── مشاريع الاستثمار ──────────────────────────────────────────
CREATE TABLE IF NOT EXISTS invest_projects (
  id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title             VARCHAR(300) NOT NULL,
  description       TEXT NOT NULL,
  model             ENUM('mudarabah','musharakah','salam','ijarah','murabaha') DEFAULT 'mudarabah',
  owner_user_id     INT UNSIGNED NOT NULL,
  owner_kyc_level   TINYINT UNSIGNED DEFAULT 1,
  guarantor_user_id INT UNSIGNED NULL,
  target_sdg        DECIMAL(16,2) NOT NULL,
  collected_sdg     DECIMAL(16,2) DEFAULT 0.00,
  min_invest_sdg    DECIMAL(12,2) DEFAULT 5000.00,
  max_invest_sdg    DECIMAL(16,2) NULL,
  profit_share_pct  DECIMAL(5,2) DEFAULT 60.00 COMMENT 'نسبة المستثمرين',
  expected_return   DECIMAL(5,2) NULL COMMENT 'العائد السنوي المتوقع %',
  risk_level        TINYINT UNSIGNED DEFAULT 2 COMMENT '1=low,2=med,3=high',
  duration_days     SMALLINT UNSIGNED NOT NULL,
  start_date        DATE NULL,
  end_date          DATE NULL,
  safety_pool_pct   DECIMAL(4,2) DEFAULT 1.00,
  safety_pool_sdg   DECIMAL(14,2) DEFAULT 0.00,
  sector            VARCHAR(50) NULL,
  governorate       VARCHAR(50) NULL,
  documents         JSON NULL,
  status            ENUM('draft','pending_review','active','funded',
                         'running','completed','cancelled') DEFAULT 'draft',
  investors_count   INT UNSIGNED DEFAULT 0,
  reviewed_by       INT UNSIGNED NULL,
  reviewed_at       DATETIME NULL,
  created_at        DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_status (status),
  INDEX idx_sector (sector),
  INDEX idx_owner  (owner_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── حصص المستثمرين ───────────────────────────────────────────
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,5) 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;

-- ── تقارير المشاريع ───────────────────────────────────────────
CREATE TABLE IF NOT EXISTS invest_reports (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  project_id  INT UNSIGNED NOT NULL,
  report_month TINYINT UNSIGNED NOT NULL,
  report_year SMALLINT UNSIGNED NOT NULL,
  narrative   TEXT NOT NULL,
  photos      JSON NULL,
  revenue_sdg DECIMAL(14,2) DEFAULT 0.00,
  expense_sdg DECIMAL(14,2) DEFAULT 0.00,
  submitted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  approved_at DATETIME NULL,
  INDEX idx_project (project_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── صندوق الضمان ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS invest_safety_pool (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  project_id  INT UNSIGNED NOT NULL,
  amount_sdg  DECIMAL(14,2) NOT NULL,
  type        ENUM('contribution','payout') NOT NULL,
  reason      VARCHAR(200) NULL,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── الصناديق (ROSCAs) ────────────────────────────────────────
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;

-- ── أعضاء الصندوق ────────────────────────────────────────────
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;

-- ── دورات الصندوق ────────────────────────────────────────────
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;

-- ── الهدايا ───────────────────────────────────────────────────
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;

-- ── الزكاة ────────────────────────────────────────────────────
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;

-- ── حسابات الأسرة ────────────────────────────────────────────
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;

-- ── سجل الديون ───────────────────────────────────────────────
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;

-- ── مدفوعات مجدولة ───────────────────────────────────────────
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;

-- ── أسعار العملات ────────────────────────────────────────────
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;

-- ── موبايل موني ──────────────────────────────────────────────
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;

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

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

-- ── نقاط الولاء ──────────────────────────────────────────────
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      INT NOT NULL COMMENT 'سالب = استخدام',
  txn_ref     VARCHAR(36) NULL,
  reason      VARCHAR(200) NULL,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── النزاعات ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS sdpay_disputes (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  txn_id      BIGINT UNSIGNED NOT NULL,
  raised_by   INT UNSIGNED NOT NULL,
  reason      ENUM('wrong_amount','not_received','unauthorized',
                   'duplicate','other') NOT NULL,
  description TEXT NULL,
  evidence    JSON NULL,
  status      ENUM('open','reviewing','resolved','escalated') DEFAULT 'open',
  resolution  TEXT NULL,
  resolved_at DATETIME NULL,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

