-- ════════════════════════════════════════════════════════════
-- سودانا v23.1 — Pricing Infrastructure Migration
-- Run AFTER seed.sql / migration_fix.sql
-- Adds: ai_agent_runs columns, image_prices, video_prices, ai_pricing_audit
-- ════════════════════════════════════════════════════════════

-- ── 1. ai_agent_runs: إضافة tokens_used + cost_usd ──────────
ALTER TABLE ai_agent_runs
  ADD COLUMN IF NOT EXISTS tokens_used INT UNSIGNED DEFAULT 0 AFTER latency_ms,
  ADD COLUMN IF NOT EXISTS cost_usd    DECIMAL(10,8) DEFAULT 0 AFTER tokens_used,
  ADD COLUMN IF NOT EXISTS cost_sdg    INT UNSIGNED DEFAULT 0 AFTER cost_usd,
  ADD INDEX IF NOT EXISTS idx_cost (cost_sdg);

-- ── 2. ai_usage: إضافة tokens_in + tokens_out + cost_sdg ─────
ALTER TABLE ai_usage
  ADD COLUMN IF NOT EXISTS tokens_in   INT UNSIGNED DEFAULT 0 AFTER tokens_used,
  ADD COLUMN IF NOT EXISTS tokens_out  INT UNSIGNED DEFAULT 0 AFTER tokens_in,
  ADD COLUMN IF NOT EXISTS provider    VARCHAR(20)  DEFAULT NULL AFTER service,
  ADD COLUMN IF NOT EXISTS cost_sdg    DECIMAL(10,4) DEFAULT 0 AFTER cost_usd,
  ADD INDEX IF NOT EXISTS idx_provider (provider),
  ADD INDEX IF NOT EXISTS idx_cost_sdg (cost_sdg);

-- ── 3. image_prices ──────────────────────────────────────────
CREATE TABLE IF NOT EXISTS image_prices (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  size_key        VARCHAR(30) NOT NULL UNIQUE COMMENT '512x512, 1024x1024, 1024x1792',
  label_ar        VARCHAR(100) NOT NULL,
  base_cost_usd   DECIMAL(10,6) NOT NULL DEFAULT 0 COMMENT 'تكلفة المزود لكل صورة',
  price_free_sdg  INT UNSIGNED DEFAULT 0,
  price_plus_sdg  INT UNSIGNED DEFAULT 0,
  price_pro_sdg   INT UNSIGNED DEFAULT 0,
  provider        VARCHAR(30) DEFAULT 'gemini' COMMENT 'gemini|dalle|stable',
  is_active       TINYINT(1) DEFAULT 1,
  updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 4. video_prices ──────────────────────────────────────────
CREATE TABLE IF NOT EXISTS video_prices (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  duration_key    VARCHAR(30) NOT NULL UNIQUE COMMENT '5s, 15s, 30s, 60s',
  label_ar        VARCHAR(100) NOT NULL,
  base_cost_usd   DECIMAL(10,6) NOT NULL DEFAULT 0 COMMENT 'تكلفة المزود لكل فيديو',
  price_free_sdg  INT UNSIGNED DEFAULT 0,
  price_plus_sdg  INT UNSIGNED DEFAULT 0,
  price_pro_sdg   INT UNSIGNED DEFAULT 0,
  provider        VARCHAR(30) DEFAULT 'veo',
  is_active       TINYINT(1) DEFAULT 1,
  updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 5. ai_pricing_audit (سجل تغييرات الأسعار) ───────────────
CREATE TABLE IF NOT EXISTS ai_pricing_audit (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  entity_type   ENUM('exchange','plan','document','image','video','corporate','setting') NOT NULL,
  entity_key    VARCHAR(100) NOT NULL,
  old_value     VARCHAR(500),
  new_value     VARCHAR(500),
  changed_by    INT UNSIGNED NULL,
  reason        VARCHAR(300),
  created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_entity (entity_type, entity_key),
  INDEX idx_changed_by (changed_by),
  INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 6. Seed default image_prices ─────────────────────────────
INSERT IGNORE INTO image_prices (size_key, label_ar, base_cost_usd, price_free_sdg, price_plus_sdg, price_pro_sdg, provider) VALUES
('512x512',   'صورة صغيرة 512×512',  0.020, 1500,  1000,  500,  'gemini'),
('1024x1024', 'صورة كبيرة 1024×1024', 0.040, 3000,  2000, 1200, 'gemini'),
('1024x1792', 'صورة طويلة 1024×1792', 0.080, 5000,  3500, 2000, 'gemini');

-- ── 7. Seed default video_prices ─────────────────────────────
INSERT IGNORE INTO video_prices (duration_key, label_ar, base_cost_usd, price_free_sdg, price_plus_sdg, price_pro_sdg, provider) VALUES
('5s',  'فيديو 5 ثوانٍ',  0.50,  15000, 10000,  6000, 'veo'),
('15s', 'فيديو 15 ثانية', 1.50,  40000, 28000, 18000, 'veo'),
('30s', 'فيديو 30 ثانية', 3.00,  75000, 52000, 35000, 'veo'),
('60s', 'فيديو دقيقة',    6.00, 145000,100000, 70000, 'veo');


-- ── 5b. CRITICAL FIX: bookings table missing columns ────────────
-- Code references b.slot_time and b.service_type but these don't exist
-- in sudanna_v22_schema_complete.sql. Adding them now.
ALTER TABLE bookings
  ADD COLUMN IF NOT EXISTS slot_time     DATETIME NULL AFTER booking_time,
  ADD COLUMN IF NOT EXISTS service_type  VARCHAR(50) DEFAULT 'general' AFTER slot_time,
  ADD INDEX IF NOT EXISTS idx_slot_time (slot_time),
  ADD INDEX IF NOT EXISTS idx_service_type (service_type);

-- Backfill slot_time from existing booking_date + booking_time
UPDATE bookings
   SET slot_time = TIMESTAMP(booking_date, booking_time)
 WHERE slot_time IS NULL AND booking_date IS NOT NULL AND booking_time IS NOT NULL;


-- ── 5c. CRITICAL FIX: missing columns referenced by code ─────
-- These cause SQL errors at runtime when admin/code uses them

-- invoices missing columns (used in admin.php)
ALTER TABLE invoices
  ADD COLUMN IF NOT EXISTS invoice_no   VARCHAR(40) UNIQUE NULL AFTER id,
  ADD COLUMN IF NOT EXISTS type         VARCHAR(40) DEFAULT 'general' AFTER service,
  ADD COLUMN IF NOT EXISTS description  VARCHAR(500) NULL AFTER type,
  ADD COLUMN IF NOT EXISTS currency     VARCHAR(10) DEFAULT 'SDG' AFTER amount,
  ADD COLUMN IF NOT EXISTS payment_ref  VARCHAR(100) NULL AFTER currency,
  ADD COLUMN IF NOT EXISTS due_date     DATETIME NULL AFTER payment_ref,
  ADD INDEX IF NOT EXISTS idx_invoice_no (invoice_no),
  ADD INDEX IF NOT EXISTS idx_type (type);

-- subscription_plans missing columns (used in api/subscription/status.php)
ALTER TABLE subscription_plans
  ADD COLUMN IF NOT EXISTS ai_requests_day INT UNSIGNED DEFAULT 20,
  ADD COLUMN IF NOT EXISTS booking_per_day INT UNSIGNED DEFAULT 2;
-- Backfill from existing ai_limit / booking_limit
UPDATE subscription_plans
   SET ai_requests_day = ai_limit, booking_per_day = booking_limit
 WHERE ai_requests_day = 20 AND ai_limit > 0;

-- users missing columns
ALTER TABLE users
  ADD COLUMN IF NOT EXISTS provider_bank_name     VARCHAR(100) NULL,
  ADD COLUMN IF NOT EXISTS provider_bank_account  VARCHAR(50)  NULL,
  ADD COLUMN IF NOT EXISTS provider_specialization VARCHAR(150) NULL;

-- staff_members: add aliases for old column refs
ALTER TABLE staff_members
  ADD COLUMN IF NOT EXISTS user_id   INT UNSIGNED NULL,
  ADD COLUMN IF NOT EXISTS is_active TINYINT(1) GENERATED ALWAYS AS (active) VIRTUAL;

-- pulse_posts.is_urgent
ALTER TABLE pulse_posts
  ADD COLUMN IF NOT EXISTS is_urgent TINYINT(1) DEFAULT 0;

-- contracts: party_user_id, created_by
ALTER TABLE contracts
  ADD COLUMN IF NOT EXISTS party_user_id INT UNSIGNED NULL,
  ADD COLUMN IF NOT EXISTS created_by    INT UNSIGNED NULL;

-- subscription_requests.invoice_id
ALTER TABLE subscription_requests
  ADD COLUMN IF NOT EXISTS invoice_id INT UNSIGNED NULL;

-- lesson_submissions.status
ALTER TABLE lesson_submissions
  ADD COLUMN IF NOT EXISTS status ENUM('pending','approved','rejected') DEFAULT 'pending';

-- marketplace_items.seller_id
ALTER TABLE marketplace_items
  ADD COLUMN IF NOT EXISTS seller_id INT UNSIGNED NULL;

-- neighborhood_services missing
ALTER TABLE neighborhood_services
  ADD COLUMN IF NOT EXISTS provider_user_id INT UNSIGNED NULL,
  ADD COLUMN IF NOT EXISTS rating_avg       DECIMAL(3,2) DEFAULT 0,
  ADD COLUMN IF NOT EXISTS is_verified      TINYINT(1)   DEFAULT 0;

-- provider_consultations.updated_at
ALTER TABLE provider_consultations
  ADD COLUMN IF NOT EXISTS updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

-- pharmacies.whatsapp
ALTER TABLE pharmacies
  ADD COLUMN IF NOT EXISTS whatsapp VARCHAR(30) NULL;


-- ── 5d. Governance Layer support ─────────────────────────────
-- ip_blocks table (used by SecurityGate::checkIpBlock)
CREATE TABLE IF NOT EXISTS ip_blocks (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ip          VARCHAR(45) NOT NULL,
  reason      VARCHAR(200),
  blocked_by  INT UNSIGNED NULL,
  expires_at  DATETIME NOT NULL,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_ip_active (ip, expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- trace_id column for end-to-end request tracing
ALTER TABLE ai_usage
  ADD COLUMN IF NOT EXISTS trace_id VARCHAR(32) NULL,
  ADD INDEX IF NOT EXISTS idx_trace (trace_id);

ALTER TABLE audit_logs
  ADD COLUMN IF NOT EXISTS trace_id VARCHAR(32) NULL,
  ADD INDEX IF NOT EXISTS idx_trace (trace_id);

ALTER TABLE execution_log
  ADD COLUMN IF NOT EXISTS trace_id VARCHAR(32) NULL,
  ADD INDEX IF NOT EXISTS idx_trace (trace_id);

-- ── 8. Add platform_settings for AI limits (replace hardcoded) ─
INSERT INTO platform_settings (setting_key, value, value_type, label_ar, category, unit) VALUES
('ai_daily_limit_free', '20',  'number', 'حد AI اليومي — مجاني',  'subscription', 'requests'),
('ai_daily_limit_plus', '60',  'number', 'حد AI اليومي — Plus',   'subscription', 'requests'),
('ai_daily_limit_pro',  '200', 'number', 'حد AI اليومي — Pro',    'subscription', 'requests'),
('ai_daily_limit_corp', '500', 'number', 'حد AI اليومي — Corp',   'subscription', 'requests'),
('booking_limit_free',  '2',   'number', 'حد الحجوزات — مجاني',    'subscription', 'bookings'),
('booking_limit_plus',  '5',   'number', 'حد الحجوزات — Plus',     'subscription', 'bookings'),
('booking_limit_pro',   '20',  'number', 'حد الحجوزات — Pro',      'subscription', 'bookings'),
('booking_limit_corp',  '100', 'number', 'حد الحجوزات — Corp',     'subscription', 'bookings'),
('ai_overage_charge_sdg', '500', 'number', 'سعر السؤال الإضافي', 'subscription', 'SDG'),
('ai_warn_threshold_pct', '80',  'number', 'تنبيه عند نسبة استخدام', 'limits', '%')
ON DUPLICATE KEY UPDATE value=VALUES(value);

-- ── 9. Notification when wallet credit getting low ───────────
INSERT INTO platform_settings (setting_key, value, value_type, label_ar, category, unit) VALUES
('credit_low_threshold_pct', '20', 'number', 'تنبيه عند نفاد رصيد الوثائق', 'limits', '%'),
('credit_critical_threshold_pct', '5', 'number', 'تحذير حرج لرصيد الوثائق', 'limits', '%')
ON DUPLICATE KEY UPDATE value=VALUES(value);
