
-- ═══════════════════════════════════════════════════════════════
-- SUDANNA PAY — Complete Schema v1
-- ═══════════════════════════════════════════════════════════════

-- حسابات 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,
  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)
) 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(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)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

-- التجار والمزودون التجاريون
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,
  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(4,3) DEFAULT 0.015,
  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)
) 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 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','failed') DEFAULT 'pending',
  bank_ref        VARCHAR(100) NULL,
  settled_at      DATETIME NULL,
  UNIQUE KEY uniq_merchant_date (merchant_id, period_date)
) 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(100) NOT NULL,
  name_en         VARCHAR(100) NULL,
  category        ENUM('electricity','water','telecom','internet','gov_fees',
                       'education','health','insurance','other') NOT NULL,
  api_url         VARCHAR(255) NULL,
  api_key_enc     TEXT NULL,
  commission_pct  DECIMAL(4,3) DEFAULT 0.005,
  fixed_fee_sdg   DECIMAL(8,2) DEFAULT 0.00,
  validation_required TINYINT(1) DEFAULT 1,
  logo_url        VARCHAR(255) NULL,
  instructions    TEXT NULL,
  is_active       TINYINT(1) DEFAULT 1,
  sort_order      TINYINT DEFAULT 0,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP
) 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,
  txn_ref         VARCHAR(32) 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,
  token_received  VARCHAR(255) NULL,
  provider_ref    VARCHAR(100) NULL,
  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        VARCHAR(100) NOT NULL,
  service_code    VARCHAR(30) NOT NULL UNIQUE,
  name_ar         VARCHAR(150) NOT NULL,
  description     TEXT NULL,
  fee_sdg         DECIMAL(10,2) NOT NULL,
  processing_days TINYINT DEFAULT 1,
  docs_required   JSON NULL,
  payment_account VARCHAR(50) NULL,
  api_endpoint    VARCHAR(255) NULL,
  is_active       TINYINT(1) DEFAULT 1,
  sort_order      TINYINT DEFAULT 0
) 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(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   DECIMAL(14,2) DEFAULT 0.00,
  daily_limit_sdg DECIMAL(14,2) DEFAULT 5000000.00,
  commission_rate DECIMAL(4,3) DEFAULT 0.005,
  total_earned_sdg DECIMAL(14,2) DEFAULT 0.00,
  txn_count       INT DEFAULT 0,
  status          ENUM('pending','active','suspended') DEFAULT 'pending',
  kyc_verified    TINYINT(1) DEFAULT 0,
  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;

-- معاملات الوكلاء
CREATE TABLE IF NOT EXISTS sdpay_agent_transactions (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  agent_id        INT UNSIGNED NOT NULL,
  txn_ref         VARCHAR(32) NOT NULL,
  type            ENUM('cashin','cashout','registration') NOT NULL,
  user_account    VARCHAR(20) NOT NULL,
  amount_sdg      DECIMAL(14,2) NOT NULL,
  commission_sdg  DECIMAL(10,2) DEFAULT 0.00,
  status          ENUM('completed','failed','reversed') DEFAULT 'completed',
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_agent (agent_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- الصناديق (ROSCAs)
CREATE TABLE IF NOT EXISTS sdpay_roscas (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name            VARCHAR(100) NOT NULL,
  creator_id      INT UNSIGNED NOT NULL,
  monthly_sdg     DECIMAL(12,2) NOT NULL,
  member_count    TINYINT NOT NULL DEFAULT 2,
  order_method    ENUM('sequential','lottery') DEFAULT 'sequential',
  start_date      DATE NOT NULL,
  current_round   TINYINT DEFAULT 0,
  total_rounds    TINYINT NOT NULL,
  status          ENUM('forming','active','completed','cancelled') DEFAULT 'forming',
  next_collection DATE NULL,
  safety_deposit_pct DECIMAL(4,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_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(1) DEFAULT 0,
  received_at     DATETIME NULL,
  joined_at       DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq (rosca_id, 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(200) NOT NULL,
  description     TEXT NOT NULL,
  model           ENUM('mudarabah','musharakah','salam','ijarah','murabahah') DEFAULT 'mudarabah',
  sector          VARCHAR(50) NULL,
  owner_user_id   INT UNSIGNED NOT NULL,
  owner_kyc_level TINYINT DEFAULT 1,
  target_sdg      DECIMAL(14,2) NOT NULL,
  collected_sdg   DECIMAL(14,2) DEFAULT 0.00,
  min_invest_sdg  DECIMAL(12,2) DEFAULT 5000.00,
  max_invest_sdg  DECIMAL(14,2) NULL,
  investor_share_pct DECIMAL(5,2) DEFAULT 60.00,
  expected_return_pct DECIMAL(5,2) NOT NULL,
  risk_level      TINYINT DEFAULT 2,
  duration_days   SMALLINT NOT NULL,
  start_date      DATE NULL,
  end_date        DATE NULL,
  safety_pool_sdg DECIMAL(12,2) DEFAULT 0.00,
  investor_count  SMALLINT DEFAULT 0,
  status          ENUM('draft','review','active','funded','running','completed','failed') DEFAULT 'draft',
  monthly_report  JSON NULL,
  guarantor_name  VARCHAR(100) NULL,
  guarantor_id_no VARCHAR(50) NULL,
  contract_template_id INT NULL,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_status (status),
  INDEX idx_sector (sector)
) 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_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(12,2) DEFAULT 0.00,
  returned_sdg    DECIMAL(14,2) DEFAULT 0.00,
  status          ENUM('active','completed','cancelled','defaulted') DEFAULT 'active',
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_project (project_id),
  INDEX idx_investor (investor_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 NOT NULL,
  report_year     SMALLINT 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,
  profit_sdg      DECIMAL(14,2) DEFAULT 0.00,
  submitted_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  approved_at     DATETIME NULL,
  UNIQUE KEY uniq_month (project_id, report_month, report_year)
) 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,
  contributed_sdg DECIMAL(12,2) NOT NULL,
  balance_sdg     DECIMAL(12,2) NOT NULL,
  used_sdg        DECIMAL(12,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_currency_rates (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  from_currency   VARCHAR(5) NOT NULL,
  to_currency     VARCHAR(5) NOT NULL DEFAULT 'SDG',
  rate            DECIMAL(14,4) NOT NULL,
  valid_date      DATE NOT NULL,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_rate_date (from_currency, to_currency, valid_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- نزاعات المعاملات
CREATE TABLE IF NOT EXISTS sdpay_disputes (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  txn_ref         VARCHAR(32) NOT NULL,
  raised_by       INT UNSIGNED NOT NULL,
  reason          ENUM('wrong_amount','wrong_recipient','service_not_delivered',
                       'duplicate','unauthorized','other') NOT NULL,
  description     TEXT NOT NULL,
  evidence_urls   JSON NULL,
  status          ENUM('open','mediation','resolved','escalated','closed') DEFAULT 'open',
  resolution      TEXT NULL,
  resolved_by     INT UNSIGNED NULL,
  resolved_at     DATETIME NULL,
  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 DEFAULT 0,
  action          ENUM('earned','spent','expired') DEFAULT 'earned',
  txn_ref         VARCHAR(32) NULL,
  expires_at      DATE NULL,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- سجل AML (امتثال غسيل الأموال)
CREATE TABLE IF NOT EXISTS sdpay_aml_reports (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  report_type     ENUM('CTR','SAR','STR') NOT NULL,
  user_id         INT UNSIGNED NULL,
  txn_ref         VARCHAR(32) NULL,
  amount_sdg      DECIMAL(14,2) NULL,
  reason          TEXT NOT NULL,
  submitted_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  submitted_to    VARCHAR(50) DEFAULT 'EBS',
  status          ENUM('draft','submitted','acknowledged') DEFAULT 'draft'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ═══════════════════════════════════════════════════════════════
-- SUDANNA PAY — Additional Tables v2
-- ═══════════════════════════════════════════════════════════════

-- التجار والمزودون
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;

-- تسوية التجار اليومية
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;

-- مزودو الفواتير
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;

-- مدفوعات الفواتير
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;

-- الخدمات الحكومية
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;

-- مشاريع الاستثمار
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)
) 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,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;

-- تقارير المشاريع
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;

-- صندوق الضمان
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;

-- الصناديق الرقمية 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;

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

-- الوكلاء
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;

-- معاملات الوكلاء
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;

-- نقاط الولاء
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;

-- تقارير AML
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;

-- معدلات تحويل العملات
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;

-- نزاعات المعاملات
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;


-- ═══════════════════════════════════════════════════════════════
-- SUDANNA PAY — Integration Tables (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;

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;

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;
