-- ============================================================================
-- ZENTRAX STORE (zentraxshop.in) — NON-DESTRUCTIVE PRODUCTION MIGRATION 001
-- Target: MySQL 8.0+ / MariaDB 10.5+
-- Purpose: Adds idempotency locks, webhook event deduplication, durable
--          fulfillment outbox jobs, rate-limit buckets, and foreign keys
--          without dropping or truncating any existing production tables.
-- ============================================================================

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

-- 1. 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;

-- 2. Durable Fulfillment Outbox / Retry Queue (Protects Paid Orders During Bridge Outage)
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, -- v7_key | v6_key | local_stock | cert_udid_init
  `status` VARCHAR(24) NOT NULL DEFAULT 'pending', -- pending | processing | succeeded | failed_retryable | manual_hold
  `attempts` INT(11) NOT NULL DEFAULT 0,
  `last_error` TEXT DEFAULT NULL,
  `response_meta_json` LONGTEXT DEFAULT NULL,
  `next_retry_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;

-- 3. Endpoint Rate Limiting Buckets (Login, OTP, Coupon, Reset, Checkout)
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;

-- 4. 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;

-- 5. 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;

-- 6. Vault Deliveries Expiration & Revocation Columns (Idempotent on MySQL 8.0+ / MariaDB 10.5+)
ALTER TABLE `vault_deliveries` ADD COLUMN IF NOT EXISTS `expires_at` DATETIME DEFAULT NULL;
ALTER TABLE `vault_deliveries` ADD COLUMN IF NOT EXISTS `revoked_at` DATETIME DEFAULT NULL;