-- ============================================================================
-- ZENTRAX STORE (zentraxshop.in) — COMPLETE PRODUCTION MYSQL SCHEMA (34 TABLES)
-- Zero Data Loss Idempotent DDL: Safe CREATE TABLE IF NOT EXISTS, Indexes,
-- Unique Constraints, Relationships & Non-Destructive Default Seeds
-- ============================================================================

SET NAMES utf8mb4;
SET time_zone = '+00:00';

-- 1. System Settings, Branding, SEO, SMTP, Payment Gateways, Migration Registry & UI Text CMS
CREATE TABLE IF NOT EXISTS `system_settings` (
  `setting_key` VARCHAR(100) NOT NULL PRIMARY KEY,
  `setting_val` LONGTEXT DEFAULT NULL,
  `category` VARCHAR(50) NOT NULL DEFAULT 'general',
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Multi-Currency Manager (USDT Default, INR, PKR, BDT Visible + Global Currencies Hidden)
CREATE TABLE IF NOT EXISTS `currencies` (
  `code` VARCHAR(12) NOT NULL PRIMARY KEY,
  `name` VARCHAR(64) NOT NULL,
  `symbol` VARCHAR(16) NOT NULL,
  `symbol_position` VARCHAR(10) NOT NULL DEFAULT 'left',
  `rate_per_usd` DECIMAL(14,4) NOT NULL DEFAULT 1.0000,
  `decimals` TINYINT(1) NOT NULL DEFAULT 2,
  `is_default` TINYINT(1) NOT NULL DEFAULT 0,
  `is_visible` TINYINT(1) NOT NULL DEFAULT 0,
  `sort_order` INT(11) NOT NULL DEFAULT 10,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Users (Customers, Resellers & Master Owner)
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `email` VARCHAR(190) NOT NULL UNIQUE,
  `username` VARCHAR(80) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `role` VARCHAR(24) NOT NULL DEFAULT 'customer', -- customer | reseller | owner
  `status` VARCHAR(24) NOT NULL DEFAULT 'active', -- active | suspended | banned
  `ban_reason` VARCHAR(255) DEFAULT NULL,
  `totp_enabled` TINYINT(1) NOT NULL DEFAULT 0,
  `totp_secret` VARCHAR(64) DEFAULT NULL,
  `totp_recovery_codes` TEXT DEFAULT NULL,
  `is_reseller` TINYINT(1) NOT NULL DEFAULT 0,
  `reseller_expires_at` DATETIME DEFAULT NULL,
  `reseller_global_discount_pct` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  `reseller_cert_discount_enabled` TINYINT(1) NOT NULL DEFAULT 0,
  `reseller_cert_discount_pct` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  `reseller_min_balance_inr` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `reseller_balance_inr` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `telegram_handle` VARCHAR(100) DEFAULT NULL,
  `whatsapp_number` VARCHAR(40) DEFAULT NULL,
  `last_login_ip` VARCHAR(64) DEFAULT NULL,
  `last_login_at` DATETIME DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_users_role` (`role`, `is_reseller`),
  INDEX `idx_users_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Active Device Sessions (Allows Users & Owner to View & 1-Click Remote Logout Devices)
CREATE TABLE IF NOT EXISTS `user_sessions` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT(11) NOT NULL,
  `session_token_hash` CHAR(64) NOT NULL UNIQUE,
  `device_label` VARCHAR(140) NOT NULL DEFAULT 'Web Browser',
  `os_name` VARCHAR(64) NOT NULL DEFAULT 'Unknown OS',
  `browser_name` VARCHAR(64) NOT NULL DEFAULT 'Browser',
  `ip_address` VARCHAR(64) NOT NULL DEFAULT '',
  `is_revoked` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_active_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `expires_at` DATETIME NOT NULL,
  INDEX `idx_us_user` (`user_id`, `is_revoked`),
  INDEX `idx_us_exp` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Email Verification & Password Reset OTPs
CREATE TABLE IF NOT EXISTS `email_otps` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `email` VARCHAR(190) NOT NULL,
  `purpose` VARCHAR(32) NOT NULL, -- register | reset_password | change_password
  `otp_hash` VARCHAR(255) NOT NULL,
  `attempts` INT(11) NOT NULL DEFAULT 0,
  `is_used` TINYINT(1) NOT NULL DEFAULT 0,
  `ip_address` VARCHAR(64) DEFAULT NULL,
  `expires_at` DATETIME NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_otp_lookup` (`email`, `purpose`, `is_used`, `expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. Connected External Product Servers (`zentraxmod.in`, `zentrax.in`, etc.)
CREATE TABLE IF NOT EXISTS `api_providers` (
  `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `server_type` VARCHAR(40) NOT NULL, -- zentrax_v7_universal_ios | zentrax_v6_modules
  `base_url` VARCHAR(255) NOT NULL,
  `bridge_url` VARCHAR(255) NOT NULL,
  `bridge_secret` VARCHAR(255) NOT NULL,
  `api_key` VARCHAR(255) DEFAULT NULL,
  `status` VARCHAR(24) NOT NULL DEFAULT 'active',
  `last_sync_at` DATETIME DEFAULT NULL,
  `last_health_json` LONGTEXT DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. Store Products (`zentraxmod.in` Universal, `zentrax.in` Modules, GBox/ESign Certificates)
CREATE TABLE IF NOT EXISTS `products` (
  `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `slug` VARCHAR(120) NOT NULL UNIQUE,
  `name` VARCHAR(140) NOT NULL,
  `Compliance_safe_name` VARCHAR(140) NOT NULL DEFAULT 'iOS Companion Utility',
  `subtitle` VARCHAR(255) DEFAULT NULL,
  `description` TEXT DEFAULT NULL,
  `compliance_safe_description` TEXT DEFAULT NULL,
  `category` VARCHAR(60) NOT NULL DEFAULT 'iOS Suite',
  `platform_badge` VARCHAR(40) NOT NULL DEFAULT 'iOS only',
  `delivery_type` VARCHAR(32) NOT NULL DEFAULT 'zentraxmod_v7', -- zentraxmod_v7 | zentrax_in_v6 | certificate_udid | local_stock
  `provider_id` INT(11) DEFAULT NULL,
  `module_config_mode` VARCHAR(24) NOT NULL DEFAULT 'ALL', -- ALL (includes future modules) | CUSTOM
  `selected_modules_csv` TEXT DEFAULT NULL, -- e.g. DRAGHEADSHOT,AIMMENU,MAGICBULLET
  `live_status` VARCHAR(32) NOT NULL DEFAULT 'safe', -- safe | updating | testing | detected | risk | unavailable
  `status_note` VARCHAR(255) DEFAULT NULL,
  `auto_pause_on_update` TINYINT(1) NOT NULL DEFAULT 1,
  `is_visible` TINYINT(1) NOT NULL DEFAULT 1,
  `featured_badge` VARCHAR(40) DEFAULT NULL,
  `banner_url` VARCHAR(255) DEFAULT NULL,
  `demo_video_url` VARCHAR(255) DEFAULT NULL,
  `seo_title` VARCHAR(180) DEFAULT NULL,
  `seo_description` VARCHAR(320) DEFAULT NULL,
  `seo_keywords` VARCHAR(255) DEFAULT NULL,
  `sort_order` INT(11) NOT NULL DEFAULT 1,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_prod_vis_sort` (`is_visible`, `sort_order`),
  INDEX `idx_prod_cat` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. Product Pricing Plans (Day / Week / Month / Yearly)
CREATE TABLE IF NOT EXISTS `product_plans` (
  `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `product_id` INT(11) NOT NULL,
  `plan_label` VARCHAR(80) NOT NULL,
  `duration_val` INT(11) NOT NULL DEFAULT 30,
  `duration_unit` VARCHAR(20) NOT NULL DEFAULT 'days', -- hours | days | lifetime
  `access_subtitle` VARCHAR(120) NOT NULL DEFAULT 'One-time payment, instant access',
  `price_inr` DECIMAL(12,2) NOT NULL DEFAULT 950.00,
  `reseller_price_inr` DECIMAL(12,2) NOT NULL DEFAULT 650.00,
  `remote_plan_id` INT(11) DEFAULT NULL,
  `device_limit` INT(11) NOT NULL DEFAULT 1,
  `max_resets` INT(11) NOT NULL DEFAULT 3,
  `warranty_badge` VARCHAR(80) DEFAULT NULL,
  `highlight_badge` VARCHAR(40) DEFAULT NULL,
  `status` VARCHAR(20) NOT NULL DEFAULT 'active',
  `sort_order` INT(11) NOT NULL DEFAULT 1,
  INDEX `idx_pp_prod` (`product_id`, `status`, `sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9. Product Line-by-Line Features
CREATE TABLE IF NOT EXISTS `product_features` (
  `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `product_id` INT(11) NOT NULL,
  `group_title` VARCHAR(100) DEFAULT 'Core Capabilities',
  `feature_text` VARCHAR(200) NOT NULL,
  `is_pill_tag` TINYINT(1) NOT NULL DEFAULT 0,
  `sort_order` INT(11) NOT NULL DEFAULT 1,
  INDEX `idx_pf_prod` (`product_id`, `sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 10. Product Post-Purchase Download & Setup Video Links
CREATE TABLE IF NOT EXISTS `product_links` (
  `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `product_id` INT(11) NOT NULL,
  `button_label` VARCHAR(100) NOT NULL,
  `target_url` VARCHAR(500) NOT NULL,
  `link_type` VARCHAR(32) NOT NULL DEFAULT 'download', -- download | video | guide | support
  `sort_order` INT(11) NOT NULL DEFAULT 1,
  INDEX `idx_pl_prod` (`product_id`, `sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 11. Custom Requirement Fields for Certificate / Manual Products
CREATE TABLE IF NOT EXISTS `product_custom_fields` (
  `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `product_id` INT(11) NOT NULL,
  `field_key` VARCHAR(60) NOT NULL,
  `field_label` VARCHAR(120) NOT NULL,
  `field_placeholder` VARCHAR(180) DEFAULT '',
  `field_type` VARCHAR(32) NOT NULL DEFAULT 'text',
  `is_required` TINYINT(1) NOT NULL DEFAULT 1,
  `helper_url` VARCHAR(255) DEFAULT NULL,
  `sort_order` INT(11) NOT NULL DEFAULT 1,
  INDEX `idx_pcf_prod` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 12. Local Stock License Keys (Fallback or Standalone Stock Products)
CREATE TABLE IF NOT EXISTS `local_stock_keys` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `product_id` INT(11) NOT NULL,
  `plan_id` INT(11) NOT NULL,
  `key_string` VARCHAR(190) NOT NULL UNIQUE,
  `is_sold` TINYINT(1) NOT NULL DEFAULT 0,
  `is_revoked` TINYINT(1) NOT NULL DEFAULT 0,
  `revoked_reason` VARCHAR(255) DEFAULT NULL,
  `replaced_by_key_id` BIGINT(20) DEFAULT NULL,
  `sold_to_user_id` INT(11) DEFAULT NULL,
  `order_id` BIGINT(20) DEFAULT NULL,
  `order_item_id` BIGINT(20) DEFAULT NULL,
  `batch_tag` VARCHAR(64) DEFAULT NULL,
  `sold_at` DATETIME DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_lsk_avail` (`product_id`, `plan_id`, `is_sold`, `is_revoked`),
  INDEX `idx_lsk_order` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 13. Per-User Per-Product Reseller Custom Overrides
CREATE TABLE IF NOT EXISTS `reseller_product_overrides` (
  `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT(11) NOT NULL,
  `product_id` INT(11) NOT NULL,
  `plan_id` INT(11) DEFAULT NULL,
  `discount_pct` DECIMAL(5,2) DEFAULT NULL,
  `flat_price_inr` DECIMAL(12,2) DEFAULT NULL,
  `is_allowed` TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY `uniq_res_prod_plan` (`user_id`, `product_id`, `plan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 14. Coupons
CREATE TABLE IF NOT EXISTS `coupons` (
  `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `code` VARCHAR(50) NOT NULL UNIQUE,
  `discount_type` VARCHAR(16) NOT NULL DEFAULT 'percentage', -- percentage | flat_inr
  `discount_value` DECIMAL(12,2) NOT NULL DEFAULT 10.00,
  `min_order_inr` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `max_discount_inr` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `allowed_product_ids` VARCHAR(255) NOT NULL DEFAULT 'ALL',
  `max_total_uses` INT(11) NOT NULL DEFAULT 0,
  `max_uses_per_user` INT(11) NOT NULL DEFAULT 1,
  `used_count` INT(11) NOT NULL DEFAULT 0,
  `first_order_only` TINYINT(1) NOT NULL DEFAULT 0,
  `allow_for_resellers` TINYINT(1) NOT NULL DEFAULT 0,
  `total_revenue_inr` DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  `status` VARCHAR(20) NOT NULL DEFAULT 'active',
  `starts_at` DATETIME DEFAULT NULL,
  `expires_at` DATETIME DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 15. Coupon Usages
CREATE TABLE IF NOT EXISTS `coupon_usages` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `coupon_id` INT(11) NOT NULL,
  `user_id` INT(11) NOT NULL,
  `order_id` BIGINT(20) NOT NULL,
  `discount_inr` DECIMAL(12,2) NOT NULL,
  `used_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_cu_lookup` (`coupon_id`, `user_id`),
  UNIQUE KEY `uniq_coupon_order` (`coupon_id`, `order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 16. Orders (Atomic Fail-Safe Financial & Fulfillment State Machine)
CREATE TABLE IF NOT EXISTS `orders` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `order_number` VARCHAR(40) NOT NULL UNIQUE,
  `user_id` INT(11) NOT NULL,
  `user_email` VARCHAR(190) NOT NULL,
  `display_currency` VARCHAR(12) NOT NULL DEFAULT 'USDT',
  `settlement_currency` VARCHAR(12) NOT NULL DEFAULT 'INR',
  `exchange_rate_snapshot` DECIMAL(14,4) NOT NULL DEFAULT 86.5000,
  `display_amount_snapshot` DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  `subtotal_inr` DECIMAL(12,2) NOT NULL,
  `discount_inr` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `total_payable_inr` DECIMAL(12,2) NOT NULL,
  `total_payable_usdt` DECIMAL(12,2) NOT NULL,
  `coupon_code` VARCHAR(50) DEFAULT NULL,
  `payment_gateway` VARCHAR(24) NOT NULL DEFAULT 'razorpay', -- razorpay | binance
  `payment_status` VARCHAR(24) NOT NULL DEFAULT 'pending', -- pending | paid | manual_review | failed | refunded | cancelled
  `fulfillment_status` VARCHAR(32) NOT NULL DEFAULT 'awaiting_payment',
  -- awaiting_payment | paid_awaiting_udid | paid_queued_api | processing_manual | partial_delivered | completed | recalled
  `rzp_order_id` VARCHAR(80) DEFAULT NULL,
  `rzp_payment_id` VARCHAR(80) DEFAULT NULL,
  `rzp_signature` VARCHAR(255) DEFAULT NULL,
  `bank_rrn` VARCHAR(80) DEFAULT NULL,
  `binance_txid` VARCHAR(140) DEFAULT NULL UNIQUE,
  `binance_screenshot_path` VARCHAR(255) DEFAULT NULL,
  `review_note` VARCHAR(255) DEFAULT NULL,
  `reviewed_by` INT(11) DEFAULT NULL,
  `reviewed_at` DATETIME DEFAULT NULL,
  `refund_reason` VARCHAR(255) DEFAULT NULL,
  `refunded_at` DATETIME DEFAULT NULL,
  `owner_fulfill_token` VARCHAR(80) DEFAULT NULL,
  `ip_address` VARCHAR(64) DEFAULT NULL,
  `paid_at` DATETIME DEFAULT NULL,
  `completed_at` DATETIME DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_ord_user` (`user_id`, `created_at`),
  INDEX `idx_ord_rzp` (`rzp_order_id`),
  INDEX `idx_ord_status` (`payment_status`, `fulfillment_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 17. Order Items (Per-Item Fulfillment & Certificate Metadata Isolation)
CREATE TABLE IF NOT EXISTS `order_items` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `order_id` BIGINT(20) NOT NULL,
  `product_id` INT(11) NOT NULL,
  `plan_id` INT(11) NOT NULL,
  `product_name` VARCHAR(140) NOT NULL,
  `plan_label` VARCHAR(80) NOT NULL,
  `delivery_type` VARCHAR(32) NOT NULL,
  `qty` INT(11) NOT NULL DEFAULT 1,
  `unit_price_inr` DECIMAL(12,2) NOT NULL,
  `line_total_inr` DECIMAL(12,2) NOT NULL,
  `delivered_key` VARCHAR(500) DEFAULT NULL,
  `remote_server_type` VARCHAR(40) DEFAULT NULL,
  `remote_provider_id` INT(11) DEFAULT NULL,
  `delivery_payload_json` LONGTEXT DEFAULT NULL,
  `udid_verified` TINYINT(1) NOT NULL DEFAULT 0,
  `udid_locked` TINYINT(1) NOT NULL DEFAULT 0,
  `udid_enrollment_method` VARCHAR(32) DEFAULT NULL, -- ota_mobileconfig | manual_double_entry | owner_corrected
  `udid_value` VARCHAR(100) DEFAULT NULL,
  `device_product_code` VARCHAR(60) DEFAULT NULL,
  `device_model_name` VARCHAR(120) DEFAULT NULL,
  `ios_version` VARCHAR(40) DEFAULT NULL,
  `esign_install_url` VARCHAR(500) DEFAULT NULL,
  `setup_video_url` VARCHAR(500) DEFAULT NULL,
  `delivery_instructions` TEXT DEFAULT NULL,
  `thank_you_message` TEXT DEFAULT NULL,
  `support_link` VARCHAR(500) DEFAULT NULL,
  `delivery_version` INT(11) NOT NULL DEFAULT 0,
  `customer_whatsapp` VARCHAR(50) DEFAULT NULL,
  `customer_telegram` VARCHAR(80) DEFAULT NULL,
  `customer_extra_json` TEXT DEFAULT NULL,
  `item_status` VARCHAR(32) NOT NULL DEFAULT 'pending', -- pending | awaiting_udid | processing | delivered | failed_retryable | recalled
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_oi_order` (`order_id`),
  INDEX `idx_oi_key` (`delivered_key`(190))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 18. Signed Apple OTA `.mobileconfig` UDID Enrollment Tokens
CREATE TABLE IF NOT EXISTS `udid_enrollments` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `enrollment_token` CHAR(48) NOT NULL UNIQUE,
  `user_id` INT(11) NOT NULL,
  `order_id` BIGINT(20) NOT NULL,
  `order_item_id` BIGINT(20) NOT NULL,
  `udid` VARCHAR(100) DEFAULT NULL,
  `product_code` VARCHAR(60) DEFAULT NULL,
  `model_name` VARCHAR(120) DEFAULT NULL,
  `ios_version` VARCHAR(40) DEFAULT NULL,
  `serial_number` VARCHAR(60) DEFAULT NULL,
  `is_verified` TINYINT(1) NOT NULL DEFAULT 0,
  `is_locked` TINYINT(1) NOT NULL DEFAULT 1,
  `verified_at` DATETIME DEFAULT NULL,
  `expires_at` DATETIME NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_ue_order` (`order_id`, `order_item_id`),
  INDEX `idx_ue_token_exp` (`enrollment_token`, `is_verified`, `expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 19. Private Certificate & Digital Vault Files (With Full Version & Replacement History)
CREATE TABLE IF NOT EXISTS `vault_deliveries` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `download_token` CHAR(48) NOT NULL UNIQUE,
  `order_id` BIGINT(20) NOT NULL,
  `order_item_id` BIGINT(20) NOT NULL,
  `user_id` INT(11) NOT NULL,
  `original_filename` VARCHAR(200) NOT NULL,
  `stored_filename` VARCHAR(200) NOT NULL,
  `file_type` VARCHAR(40) NOT NULL DEFAULT 'certificate', -- p12 | mobileprovision | zip | extra
  `file_size_bytes` BIGINT(20) NOT NULL DEFAULT 0,
  `sha256_hash` CHAR(64) DEFAULT NULL,
  `delivery_version` INT(11) NOT NULL DEFAULT 1,
  `is_superseded` TINYINT(1) NOT NULL DEFAULT 0,
  `superseded_at` DATETIME DEFAULT NULL,
  `replaced_by_id` BIGINT(20) DEFAULT NULL,
  `uploaded_by` INT(11) DEFAULT NULL,
  `notes` VARCHAR(255) DEFAULT NULL,
  `download_count` INT(11) NOT NULL DEFAULT 0,
  `is_deleted` TINYINT(1) NOT NULL DEFAULT 0,
  `expires_at` DATETIME DEFAULT NULL,
  `revoked_at` DATETIME DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_vd_order` (`order_id`, `order_item_id`, `is_deleted`, `is_superseded`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 20. Support Tickets (Registered Users Only, IDOR-Protected)
CREATE TABLE IF NOT EXISTS `support_tickets` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `ticket_number` VARCHAR(32) NOT NULL UNIQUE,
  `user_id` INT(11) NOT NULL,
  `order_id` BIGINT(20) DEFAULT NULL,
  `category` VARCHAR(60) NOT NULL DEFAULT 'Order & Delivery',
  `subject` VARCHAR(200) NOT NULL,
  `priority` VARCHAR(20) NOT NULL DEFAULT 'normal', -- low | normal | high | urgent
  `status` VARCHAR(24) NOT NULL DEFAULT 'open', -- open | answered | customer_reply | reopened | closed
  `last_reply_by` VARCHAR(20) NOT NULL DEFAULT 'customer',
  `last_reply_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_st_user` (`user_id`, `status`),
  INDEX `idx_st_priority` (`status`, `priority`, `last_reply_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 21. Support Ticket Messages & Authorized Attachments
CREATE TABLE IF NOT EXISTS `ticket_messages` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `ticket_id` BIGINT(20) NOT NULL,
  `sender_role` VARCHAR(20) NOT NULL, -- customer | owner
  `sender_id` INT(11) NOT NULL,
  `message` TEXT NOT NULL,
  `attachment_path` VARCHAR(255) DEFAULT NULL,
  `attachment_name` VARCHAR(180) DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_tm_ticket` (`ticket_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 22. Announcements & Targeted Popups
CREATE TABLE IF NOT EXISTS `announcements` (
  `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(160) NOT NULL,
  `message` TEXT NOT NULL,
  `badge_label` VARCHAR(40) NOT NULL DEFAULT 'UPDATE',
  `display_mode` VARCHAR(24) NOT NULL DEFAULT 'top_banner',
  `target_audience` VARCHAR(24) NOT NULL DEFAULT 'all',
  `button_text` VARCHAR(80) DEFAULT NULL,
  `button_url` VARCHAR(255) DEFAULT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `expires_at` DATETIME DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 23. Editable Navigation Menus
CREATE TABLE IF NOT EXISTS `navigation_menus` (
  `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `location` VARCHAR(24) NOT NULL DEFAULT 'header', -- header | user_menu | footer
  `label` VARCHAR(80) NOT NULL,
  `target_route` VARCHAR(255) NOT NULL,
  `visibility` VARCHAR(24) NOT NULL DEFAULT 'all',
  `is_external` TINYINT(1) NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order` INT(11) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 24. Custom & Legal CMS Pages
CREATE TABLE IF NOT EXISTS `custom_pages` (
  `slug` VARCHAR(80) NOT NULL PRIMARY KEY,
  `title` VARCHAR(160) NOT NULL,
  `content_html` LONGTEXT NOT NULL,
  `seo_title` VARCHAR(180) DEFAULT NULL,
  `seo_description` VARCHAR(320) DEFAULT NULL,
  `seo_keywords` VARCHAR(255) DEFAULT NULL,
  `is_published` TINYINT(1) NOT NULL DEFAULT 1,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 25. Storefront FAQs
CREATE TABLE IF NOT EXISTS `faqs` (
  `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `question` VARCHAR(255) NOT NULL,
  `answer` TEXT NOT NULL,
  `category` VARCHAR(60) NOT NULL DEFAULT 'General',
  `is_visible` TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order` INT(11) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 26. Verified Buyer Reviews
CREATE TABLE IF NOT EXISTS `reviews` (
  `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT(11) DEFAULT NULL,
  `order_id` BIGINT(20) DEFAULT NULL,
  `product_id` INT(11) DEFAULT NULL,
  `author_name` VARCHAR(80) NOT NULL,
  `product_name` VARCHAR(120) NOT NULL,
  `rating` TINYINT(1) NOT NULL DEFAULT 5,
  `comment` TEXT NOT NULL,
  `is_verified_purchase` TINYINT(1) NOT NULL DEFAULT 1,
  `is_approved` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_rev_prod` (`product_id`, `is_approved`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 27. Anti-Replay API Nonces
CREATE TABLE IF NOT EXISTS `api_nonces` (
  `nonce_key` VARCHAR(96) NOT NULL PRIMARY KEY,
  `expires_at` DATETIME NOT NULL,
  INDEX `idx_an_exp` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 28. Visitor Traffic Analytics
CREATE TABLE IF NOT EXISTS `visitor_analytics` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `visit_date` DATE NOT NULL,
  `visitor_hash` CHAR(64) NOT NULL,
  `ip_address` VARCHAR(64) NOT NULL,
  `page_path` VARCHAR(160) NOT NULL DEFAULT '/',
  `user_agent` VARCHAR(255) DEFAULT NULL,
  `last_seen_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uniq_daily_visitor` (`visit_date`, `visitor_hash`),
  INDEX `idx_va_seen` (`last_seen_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 29. Append-Only Security & Administrative Audit Logs
CREATE TABLE IF NOT EXISTS `security_audit_logs` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `actor_role` VARCHAR(24) NOT NULL DEFAULT 'system',
  `actor_id` INT(11) DEFAULT NULL,
  `actor_email` VARCHAR(190) DEFAULT NULL,
  `event_type` VARCHAR(64) NOT NULL,
  `severity` VARCHAR(16) NOT NULL DEFAULT 'info', -- info | warning | critical
  `ip_address` VARCHAR(64) DEFAULT NULL,
  `details` TEXT DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_sal_type` (`event_type`, `created_at`),
  INDEX `idx_sal_sev` (`severity`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 30. Webhook & Payment Event Deduplication Table
CREATE TABLE IF NOT EXISTS `payment_events` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `provider` VARCHAR(32) NOT NULL, -- razorpay | binance
  `event_id` VARCHAR(128) NOT NULL,
  `order_id` BIGINT(20) DEFAULT NULL,
  `payment_ref` VARCHAR(128) DEFAULT NULL,
  `event_type` VARCHAR(64) NOT NULL,
  `amount_inr` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `currency` VARCHAR(12) NOT NULL DEFAULT 'INR',
  `status_recorded` VARCHAR(32) NOT NULL DEFAULT 'processed',
  `payload_hash` CHAR(64) NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uniq_provider_event` (`provider`, `event_id`),
  INDEX `idx_pe_order` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 31. Durable Fulfillment Outbox / Retry Queue
CREATE TABLE IF NOT EXISTS `fulfillment_jobs` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `order_id` BIGINT(20) NOT NULL,
  `order_item_id` BIGINT(20) NOT NULL,
  `idempotency_key` VARCHAR(96) NOT NULL UNIQUE,
  `job_type` VARCHAR(32) NOT NULL,
  `status` VARCHAR(24) NOT NULL DEFAULT 'pending', -- pending | processing | succeeded | failed_retryable | dead_letter
  `attempts` INT(11) NOT NULL DEFAULT 0,
  `max_attempts` INT(11) NOT NULL DEFAULT 5,
  `last_error` TEXT DEFAULT NULL,
  `response_meta_json` LONGTEXT DEFAULT NULL,
  `next_retry_at` DATETIME DEFAULT NULL,
  `dead_lettered_at` DATETIME DEFAULT NULL,
  `completed_at` DATETIME DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_fj_status_retry` (`status`, `next_retry_at`),
  INDEX `idx_fj_order` (`order_id`, `order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 32. Endpoint Rate Limiting Buckets
CREATE TABLE IF NOT EXISTS `rate_limits` (
  `bucket_key` VARCHAR(140) NOT NULL PRIMARY KEY,
  `hits` INT(11) NOT NULL DEFAULT 1,
  `window_expires_at` DATETIME NOT NULL,
  INDEX `idx_rl_exp` (`window_expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 33. Order Events Audit Trail (Immutable Per-Order State Transitions)
CREATE TABLE IF NOT EXISTS `order_events` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `order_id` BIGINT(20) NOT NULL,
  `actor_role` VARCHAR(24) NOT NULL DEFAULT 'system',
  `actor_id` INT(11) DEFAULT NULL,
  `from_payment_status` VARCHAR(24) DEFAULT NULL,
  `to_payment_status` VARCHAR(24) DEFAULT NULL,
  `from_fulfillment_status` VARCHAR(32) DEFAULT NULL,
  `to_fulfillment_status` VARCHAR(32) DEFAULT NULL,
  `note` VARCHAR(255) NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_oe_order` (`order_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 34. Reseller Onboarding Applications Table
CREATE TABLE IF NOT EXISTS `reseller_applications` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT(11) NOT NULL,
  `agency_name` VARCHAR(140) NOT NULL,
  `contact_handle` VARCHAR(140) NOT NULL,
  `estimated_volume` VARCHAR(80) NOT NULL,
  `notes` TEXT DEFAULT NULL,
  `status` VARCHAR(24) NOT NULL DEFAULT 'pending', -- pending | approved | rejected
  `reviewed_by` INT(11) DEFAULT NULL,
  `review_note` VARCHAR(255) DEFAULT NULL,
  `reviewed_at` DATETIME DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_ra_user` (`user_id`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;