-- ══════════════════════════════════════════════════════════════════
-- SUDANNA AI OS v23 — ACTIVATION SQL
-- Run AFTER: sudanna_v22_schema_complete.sql is fully imported
-- Run ONCE only (all INSERT IGNORE / UPDATE are safe to re-run)
-- ══════════════════════════════════════════════════════════════════

-- ── STEP 1: Activate all providers ───────────────────────────────
UPDATE users
SET provider_verified = 1
WHERE user_type IN ('doctor','lawyer','teacher','service_provider')
AND provider_verified = 0;

-- ── STEP 2: Subscription plans ────────────────────────────────────
INSERT IGNORE INTO subscription_plans
  (plan_key, name_ar, name_en, price_sdg, ai_requests_day, bookings_day, status)
VALUES
  ('free',  'مجاني',  'Free',  0,      20,  2,  'active'),
  ('plus',  'بلاس',   'Plus',  5000,   60,  5,  'active'),
  ('pro',   'برو',    'Pro',   10000,  200, 20,  'active');

-- ── STEP 3: Feature flags ──────────────────────────────────────────
INSERT IGNORE INTO feature_flags (flag_key, enabled, description)
VALUES
  ('telemedicine', 1, 'Telemedicine flow'),
  ('legal_flow',   1, 'Legal consultation flow'),
  ('business_flow',1, 'Business consulting flow'),
  ('academy',      1, 'Academy module'),
  ('marketplace',  1, 'Marketplace module'),
  ('bde',          1, 'Business document engine'),
  ('agent_api',    1, 'Developer agent API');

-- ── STEP 4: Chart of Accounts (double-entry accounting) ───────────
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');

-- ── STEP 5: Seed today's KPI row ──────────────────────────────────
INSERT IGNORE INTO kpi_daily (kpi_date) VALUES (CURDATE());

-- ── STEP 6: Verify final state ────────────────────────────────────
SELECT 'users'              AS tbl, COUNT(*)                                    AS cnt FROM users
UNION SELECT 'providers',    COUNT(*) FROM users WHERE provider_verified=1
UNION SELECT 'plans',        COUNT(*) FROM subscription_plans
UNION SELECT 'flags',        COUNT(*) FROM feature_flags
UNION SELECT 'accounts',     COUNT(*) FROM accounts
UNION SELECT 'flow_sessions',COUNT(*) FROM flow_sessions
UNION SELECT 'tasks',        COUNT(*) FROM tasks
UNION SELECT 'notifications',COUNT(*) FROM notifications
UNION SELECT 'kpi_daily',    COUNT(*) FROM kpi_daily;

-- ══ DONE ══

-- ── Auto-create billing_reserves (idempotent) ────────────────────
CREATE TABLE IF NOT EXISTS billing_reserves (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, execution_id VARCHAR(60) NOT NULL UNIQUE, user_id INT UNSIGNED NOT NULL, sector VARCHAR(50), amount DECIMAL(10,2), status ENUM('reserved','committed','rolled_back') DEFAULT 'reserved', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, committed_at DATETIME NULL, rolled_back_at DATETIME NULL, INDEX idx_user(user_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
