
-- ═══════════════════════════════════════════════════════════════
-- SUDANNA — COMPLETE ACCOUNTING SYSTEM v1
-- شجرة الحسابات + اليومية + دفتر الأستاذ + تسوية
-- ═══════════════════════════════════════════════════════════════

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

-- إدخال شجرة الحسابات الكاملة
INSERT IGNORE INTO chart_of_accounts (code,parent_code,name_ar,name_en,account_type,sub_type) VALUES
-- ═══ الأصول ═══
('1000',NULL,'الأصول','Assets','asset','parent'),
('1100','1000','الأصول المتداولة','Current Assets','asset','parent'),
('1110','1100','النقدية والمعادلات','Cash & Equivalents','asset','cash'),
('1111','1110','محافظ العملاء','Customer Wallets','asset','cash'),
('1112','1110','الأموال المحتجزة','Held Funds','asset','cash'),
('1113','1110','رصيد EBS','EBS Balance','asset','cash'),
('1114','1110','حساب بنك الخرطوم','Bank KRT Account','asset','cash'),
('1120','1100','الذمم المدينة','Accounts Receivable','asset','receivable'),
('1121','1120','مستحقات التجار','Merchant Receivables','asset','receivable'),
('1122','1120','مستحقات الوكلاء','Agent Receivables','asset','receivable'),
('1130','1100','الرسوم المؤجلة','Deferred Fees','asset','deferred'),
('1200','1000','الأصول الثابتة','Fixed Assets','asset','parent'),
-- ═══ الخصوم ═══
('2000',NULL,'الخصوم','Liabilities','liability','parent'),
('2100','2000','الخصوم المتداولة','Current Liabilities','liability','parent'),
('2110','2100','أرصدة العملاء','Customer Balances','liability','deposits'),
('2111','2110','محافظ المستخدمين','User Wallet Balances','liability','deposits'),
('2112','2110','أرصدة التجار','Merchant Balances','liability','deposits'),
('2113','2110','أرصدة الوكلاء','Agent Float','liability','deposits'),
('2120','2100','المعاملات المعلقة','Pending Transactions','liability','pending'),
('2130','2100','الرسوم المستحقة','Accrued Fees','liability','accrued'),
('2131','2130','عمولات التجار المستحقة','Merchant Commissions Payable','liability','accrued'),
('2132','2130','عمولات الوكلاء المستحقة','Agent Commissions Payable','liability','accrued'),
('2133','2130','رسوم EBS المستحقة','EBS Fees Payable','liability','accrued'),
-- ═══ حقوق الملكية ═══
('3000',NULL,'حقوق الملكية','Equity','equity','parent'),
('3100','3000','رأس المال','Capital','equity','capital'),
('3200','3000','الأرباح المحتجزة','Retained Earnings','equity','retained'),
-- ═══ الإيرادات ═══
('4000',NULL,'الإيرادات','Revenue','revenue','parent'),
('4100','4000','إيرادات المعاملات','Transaction Revenue','revenue','parent'),
('4110','4100','رسوم التحويل','Transfer Fees','revenue','fees'),
('4111','4110','رسوم التحويل < 10K','Small Transfer Fees','revenue','fees'),
('4112','4110','رسوم التحويل 10K-100K','Mid Transfer Fees','revenue','fees'),
('4113','4110','رسوم التحويل > 100K','Large Transfer Fees','revenue','fees'),
('4120','4100','عمولات التجار','Merchant Commissions','revenue','commission'),
('4121','4120','عمولات basic (1.5%)','Basic Merchant Comm','revenue','commission'),
('4122','4120','عمولات silver (1.2%)','Silver Merchant Comm','revenue','commission'),
('4123','4120','عمولات gold (1.0%)','Gold Merchant Comm','revenue','commission'),
('4124','4120','عمولات platinum (0.8%)','Platinum Merchant Comm','revenue','commission'),
('4130','4100','رسوم الفواتير','Bill Payment Fees','revenue','fees'),
('4131','4130','رسوم الكهرباء','Electricity Bill Fees','revenue','fees'),
('4132','4130','رسوم المياه','Water Bill Fees','revenue','fees'),
('4133','4130','رسوم الاتصالات','Telecom Bill Fees','revenue','fees'),
('4140','4100','رسوم الخدمات الحكومية','Gov Service Fees','revenue','fees'),
('4150','4100','رسوم الاستثمار','Investment Fees','revenue','fees'),
('4151','4150','رسوم إدارة (2%)','Management Fees 2%','revenue','fees'),
('4152','4150','حصة الأرباح (5%)','Profit Share 5%','revenue','profit_share'),
('4200','4000','إيرادات الاشتراكات','Subscription Revenue','revenue','parent'),
('4210','4200','اشتراكات Plus','Plus Subscriptions','revenue','subscription'),
('4220','4200','اشتراكات Pro','Pro Subscriptions','revenue','subscription'),
('4230','4200','اشتراكات Corporate','Corporate Subscriptions','revenue','subscription'),
('4300','4000','إيرادات الخدمات','Service Revenue','revenue','parent'),
('4310','4300','عمولات الخدمات المهنية','Professional Service Comm','revenue','commission'),
('4320','4300','عمولات الحجوزات','Booking Commissions','revenue','commission'),
('4330','4300','إيرادات BDE','BDE Revenue','revenue','platform'),
-- ═══ المصروفات ═══
('5000',NULL,'المصروفات','Expenses','expense','parent'),
('5100','5000','تكاليف المعاملات','Transaction Costs','expense','parent'),
('5110','5100','رسوم EBS','EBS Fees','expense','fees'),
('5120','5100','رسوم التسوية البنكية','Bank Settlement Fees','expense','fees'),
('5200','5000','مصروفات AI','AI Costs','expense','parent'),
('5210','5200','تكلفة Anthropic API','Anthropic API Cost','expense','api'),
('5300','5000','مصروفات تشغيلية','Operational Expenses','expense','parent'),
('5310','5300','استضافة السيرفر','Server Hosting','expense','ops'),
('5320','5300','رواتب','Salaries','expense','ops');

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

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

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

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

-- سجل خدمات المستخدم (User Service History)
CREATE TABLE IF NOT EXISTS user_services_history (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id         INT UNSIGNED NOT NULL,
  service_type    VARCHAR(50) NOT NULL,
  provider_user_id INT UNSIGNED NULL,
  booking_id      INT UNSIGNED NULL,
  service_ref     VARCHAR(50) NULL,
  payment_ref     VARCHAR(50) NULL,
  amount_sdg      DECIMAL(14,2) NULL,
  status          ENUM('requested','matched','in_progress','completed','cancelled','refunded') DEFAULT 'requested',
  rating          TINYINT NULL,
  review_text     TEXT NULL,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  completed_at    DATETIME NULL,
  INDEX idx_user (user_id),
  INDEX idx_provider (provider_user_id),
  INDEX idx_status (status),
  INDEX idx_date (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

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

-- أرباح المزودين (Provider Earnings)
CREATE TABLE IF NOT EXISTS provider_earnings (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  provider_user_id INT UNSIGNED NOT NULL,
  service_type    VARCHAR(50) NOT NULL,
  gross_sdg       DECIMAL(14,2) NOT NULL,
  platform_fee_sdg DECIMAL(14,2) NOT NULL,
  net_sdg         DECIMAL(14,2) NOT NULL,
  service_payment_id BIGINT UNSIGNED NULL,
  sdpay_txn_ref   VARCHAR(50) NULL,
  status          ENUM('pending','paid','held','refunded') DEFAULT 'pending',
  paid_at         DATETIME NULL,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_provider (provider_user_id),
  INDEX idx_status (status),
  INDEX idx_date (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
