
-- ═══════════════════════════════════════════════════════════════
-- SUDANNA — Financial Institutions Integration v1
-- سودانا كـ Payment Switch — المؤسسات المالية = Source of Truth
-- ═══════════════════════════════════════════════════════════════

-- ① المؤسسات المالية الشريكة
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;

-- ② إعدادات API المشفّرة لكل مؤسسة
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;

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

-- ④ كل معاملة مؤكدة من البنك (Source of Truth)
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),
  INDEX idx_bank_txn (bank_txn_id),
  INDEX idx_status (bank_status),
  INDEX idx_institution (institution_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ⑤ تأكيدات البنك لكل معاملة
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;

-- ⑥ طلبات الإيداع النقدي
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;

-- ⑦ طلبات السحب النقدي
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;

-- ⑧ حسابات Escrow للخدمات
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;

-- ⑨ 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),
  INDEX idx_bank_txn (bank_txn_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ⑩ حسابات التسوية
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;

-- ⑪ إيداعات الاستثمار الخارجية
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;

-- ── ALTER sdpay_accounts — إضافة حقول Bank Linkage ──────────
ALTER TABLE sdpay_accounts
  ADD COLUMN IF NOT EXISTS bank_institution_id    INT UNSIGNED NULL AFTER status,
  ADD COLUMN IF NOT EXISTS bank_account_no        VARCHAR(50) NULL AFTER bank_institution_id,
  ADD COLUMN IF NOT EXISTS bank_account_name      VARCHAR(150) NULL AFTER bank_account_no,
  ADD COLUMN IF NOT EXISTS bank_account_type      ENUM('current','savings','mobile','wallet') DEFAULT 'mobile' AFTER bank_account_name,
  ADD COLUMN IF NOT EXISTS bank_account_verified  TINYINT DEFAULT 0 AFTER bank_account_type,
  ADD COLUMN IF NOT EXISTS bank_account_iban      VARCHAR(50) NULL AFTER bank_account_verified,
  ADD COLUMN IF NOT EXISTS bank_balance_mirror    DECIMAL(14,2) DEFAULT 0.00 AFTER bank_account_iban,
  ADD COLUMN IF NOT EXISTS balance_pending_confirm DECIMAL(14,2) DEFAULT 0.00 AFTER bank_balance_mirror,
  ADD COLUMN IF NOT EXISTS last_bank_sync         DATETIME NULL AFTER balance_pending_confirm,
  ADD COLUMN IF NOT EXISTS failed_pin_attempts    TINYINT DEFAULT 0 AFTER last_bank_sync,
  ADD COLUMN IF NOT EXISTS pin_locked_until       DATETIME NULL AFTER failed_pin_attempts;

-- ── ALTER invest_projects — إضافة حقول Bank ──────────────────
ALTER TABLE invest_projects
  ADD COLUMN IF NOT EXISTS project_bank_institution_id INT UNSIGNED NULL AFTER status,
  ADD COLUMN IF NOT EXISTS project_bank_account_no     VARCHAR(50) NULL AFTER project_bank_institution_id,
  ADD COLUMN IF NOT EXISTS project_bank_verified       TINYINT DEFAULT 0 AFTER project_bank_account_no;

-- ── ALTER sdpay_transactions — إضافة failed_reason ───────────
ALTER TABLE sdpay_transactions
  ADD COLUMN IF NOT EXISTS failed_reason      VARCHAR(100) NULL AFTER status,
  ADD COLUMN IF NOT EXISTS bank_txn_id        VARCHAR(100) NULL AFTER failed_reason,
  ADD COLUMN IF NOT EXISTS bank_confirmed_at  DATETIME NULL AFTER bank_txn_id,
  ADD COLUMN IF NOT EXISTS bank_response_code VARCHAR(20) NULL AFTER bank_confirmed_at,
  ADD COLUMN IF NOT EXISTS escrow_ref         VARCHAR(30) NULL AFTER bank_response_code;

-- ── Seed: المؤسسات المالية الشريكة ───────────────────────────
INSERT IGNORE INTO financial_institutions
  (code,name_ar,name_en,institution_type,swift_code,is_sandbox,supports_transfer,priority_order) VALUES
('BOK',  'بنك الخرطوم','Bank of Khartoum','bank','BKIMSDSXXX',1,1,1),
('EBS',  'شبكة المدفوعات السودانية','Electronic Banking Services','ebs','',1,1,2),
('FAISAL','بنك فيصل الإسلامي السوداني','Faisal Islamic Bank Sudan','bank','FIBSSDSXXX',1,1,3),
('OMDURMAN','بنك أمدرمان الوطني','Omdurman National Bank','bank','',1,1,4),
('SAVINGS','بنك الادخار السوداني','Sudan Savings Bank','bank','',1,1,5),
('ZAIN_CASH','زين كاش','Zain Cash','mobile_money','',1,1,6),
('MTN_MONEY','MTN موني','MTN Money','mobile_money','',1,1,7),
('SUDANI_PAY','سوداني باي','Sudani Pay','mobile_money','',1,1,8);
