-- ══════════════════════════════════════════════════════════════════
-- SUDANNA AI OS v23 — SCHEMA FIX SCRIPT
-- Run THIS on the live server to fix the broken deployment
-- Run AFTER importing sudanna_v22_schema_complete.sql
-- Safe to run multiple times (all IF NOT EXISTS / IF EXISTS)
-- ══════════════════════════════════════════════════════════════════

-- ── FIX 1: Add reference_code column if missing (root cause of invoice failure)
ALTER TABLE invoices
  MODIFY COLUMN amount_sdg DECIMAL(10,2) DEFAULT 0.00 COMMENT 'copy of amount';

-- ── FIX 2: Ensure reference_code column exists and is UNIQUE
ALTER TABLE invoices
  ADD COLUMN IF NOT EXISTS reference_code VARCHAR(60) NOT NULL DEFAULT '' COMMENT 'idempotency key';

-- Fix UNIQUE index on reference_code if missing
SET @idx_exists := (SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'invoices' AND INDEX_NAME = 'idx_ref');
SET @sql := IF(@idx_exists = 0,
  'ALTER TABLE invoices ADD UNIQUE INDEX idx_ref (reference_code)',
  'SELECT 1 -- idx_ref already exists');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ── FIX 3: Ensure expires_at column exists
ALTER TABLE invoices
  ADD COLUMN IF NOT EXISTS expires_at DATETIME NULL;

-- ── FIX 4: Ensure paid_at column exists
ALTER TABLE invoices
  ADD COLUMN IF NOT EXISTS paid_at DATETIME NULL;

-- ── FIX 5: Ensure payment_method column exists
ALTER TABLE invoices
  ADD COLUMN IF NOT EXISTS payment_method ENUM('offline','gateway') DEFAULT 'offline';

-- ── FIX 6: Ensure bank_account column exists
ALTER TABLE invoices
  ADD COLUMN IF NOT EXISTS bank_account VARCHAR(30) DEFAULT '1821425';

-- ── FIX 7: Wallet table — ensure it exists with correct schema
CREATE TABLE IF NOT EXISTS wallet (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id         INT UNSIGNED NOT NULL UNIQUE,
  balance_sdg     DECIMAL(12,2) DEFAULT 0.00,
  hold_balance    DECIMAL(12,2) DEFAULT 0.00,
  total_deposited DECIMAL(12,2) DEFAULT 0.00,
  total_withdrawn DECIMAL(12,2) DEFAULT 0.00,
  kyc_level       TINYINT DEFAULT 0,
  status          ENUM('active','frozen','suspended') DEFAULT 'active',
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── FIX 8: payment_receipts table
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,
  INDEX idx_invoice (invoice_id),
  INDEX idx_user    (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── FIX 9: accounts table — ensure Chart of Accounts exists
CREATE TABLE IF NOT EXISTS accounts (
  id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(10)  NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  type ENUM('asset','liability','equity','revenue','expense') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO accounts (code, name, type) VALUES
  ('1001', 'نقدية وبنوك (Cash & Banks)',            'asset'),
  ('1002', 'محافظ المستخدمين (User Wallets)',        'asset'),
  ('2001', 'التزامات المستخدمين (User Liability)',   'liability'),
  ('2002', 'التزامات المزودين (Provider Liability)', 'liability'),
  ('3001', 'رأس المال (Equity)',                     'equity'),
  ('4001', 'إيرادات العمولات (Commission Revenue)',  'revenue'),
  ('4002', 'إيرادات الاشتراكات (Subscription Rev)', 'revenue'),
  ('5001', 'مصروفات البنك (Bank Fees)',              'expense');

-- ── FIX 10: Ledger table — ensure exists
CREATE TABLE IF NOT EXISTS ledger (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  account_id      INT UNSIGNED    NOT NULL,
  debit           DECIMAL(12,2)   DEFAULT 0.00,
  credit          DECIMAL(12,2)   DEFAULT 0.00,
  reference_type  VARCHAR(30)     NULL,
  reference_id    INT UNSIGNED    NULL,
  description     VARCHAR(255)    NULL,
  created_at      DATETIME        DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_account (account_id),
  INDEX idx_ref (reference_type, reference_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── FIX 11: Init wallet for user_id=1 (admin) if not exists
INSERT IGNORE INTO wallet (user_id, balance_sdg, status) VALUES (1, 0.00, 'active');

-- ── FIX 12: providers table — ensure exists
CREATE TABLE IF NOT EXISTS providers (
  id                   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id              INT UNSIGNED NOT NULL UNIQUE,
  category             VARCHAR(50)  NOT NULL DEFAULT 'general',
  specialty            VARCHAR(100) NULL,
  bio                  TEXT         NULL,
  rating               DECIMAL(3,1) DEFAULT 0.0,
  total_ratings        INT          DEFAULT 0,
  price_sdg            DECIMAL(10,2) DEFAULT 0.00,
  available            TINYINT(1)   DEFAULT 1,
  verified             TINYINT(1)   DEFAULT 0,
  bank_account         VARCHAR(30)  NULL,
  created_at           DATETIME     DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── VERIFY: Show invoice columns after fix
DESCRIBE invoices;

SELECT 'FIX COMPLETE' AS status,
  (SELECT COUNT(*) FROM accounts) AS accounts_count,
  (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE()) AS total_tables;
