-- ============================================================================
-- ZENTRAX STORE (zentraxshop.in) — NON-DESTRUCTIVE PRODUCTION MIGRATION 002
-- Purpose: Adds extended Certificate Vault versioning, UDID lock/method tracking,
--          local inventory revocation/replacement tracking, dead-letter queue
--          tracking, and currency settlement snapshots without dropping data.
-- ============================================================================

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

ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `reseller_min_balance_inr` DECIMAL(12,2) NOT NULL DEFAULT 0.00;
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `reseller_balance_inr` DECIMAL(12,2) NOT NULL DEFAULT 0.00;

ALTER TABLE `local_stock_keys` ADD COLUMN IF NOT EXISTS `is_revoked` TINYINT(1) NOT NULL DEFAULT 0;
ALTER TABLE `local_stock_keys` ADD COLUMN IF NOT EXISTS `revoked_reason` VARCHAR(255) DEFAULT NULL;
ALTER TABLE `local_stock_keys` ADD COLUMN IF NOT EXISTS `replaced_by_key_id` BIGINT(20) DEFAULT NULL;
ALTER TABLE `local_stock_keys` ADD COLUMN IF NOT EXISTS `order_item_id` BIGINT(20) DEFAULT NULL;
ALTER TABLE `local_stock_keys` ADD COLUMN IF NOT EXISTS `batch_tag` VARCHAR(64) DEFAULT NULL;

ALTER TABLE `orders` ADD COLUMN IF NOT EXISTS `settlement_currency` VARCHAR(12) NOT NULL DEFAULT 'INR';
ALTER TABLE `orders` ADD COLUMN IF NOT EXISTS `display_amount_snapshot` DECIMAL(14,2) NOT NULL DEFAULT 0.00;
ALTER TABLE `orders` ADD COLUMN IF NOT EXISTS `review_note` VARCHAR(255) DEFAULT NULL;
ALTER TABLE `orders` ADD COLUMN IF NOT EXISTS `reviewed_by` INT(11) DEFAULT NULL;
ALTER TABLE `orders` ADD COLUMN IF NOT EXISTS `reviewed_at` DATETIME DEFAULT NULL;
ALTER TABLE `orders` ADD COLUMN IF NOT EXISTS `refund_reason` VARCHAR(255) DEFAULT NULL;
ALTER TABLE `orders` ADD COLUMN IF NOT EXISTS `refunded_at` DATETIME DEFAULT NULL;

ALTER TABLE `order_items` ADD COLUMN IF NOT EXISTS `udid_locked` TINYINT(1) NOT NULL DEFAULT 0;
ALTER TABLE `order_items` ADD COLUMN IF NOT EXISTS `udid_enrollment_method` VARCHAR(32) DEFAULT NULL;
ALTER TABLE `order_items` ADD COLUMN IF NOT EXISTS `esign_install_url` VARCHAR(500) DEFAULT NULL;
ALTER TABLE `order_items` ADD COLUMN IF NOT EXISTS `setup_video_url` VARCHAR(500) DEFAULT NULL;
ALTER TABLE `order_items` ADD COLUMN IF NOT EXISTS `delivery_instructions` TEXT DEFAULT NULL;
ALTER TABLE `order_items` ADD COLUMN IF NOT EXISTS `thank_you_message` TEXT DEFAULT NULL;
ALTER TABLE `order_items` ADD COLUMN IF NOT EXISTS `support_link` VARCHAR(500) DEFAULT NULL;
ALTER TABLE `order_items` ADD COLUMN IF NOT EXISTS `delivery_version` INT(11) NOT NULL DEFAULT 0;

ALTER TABLE `vault_deliveries` ADD COLUMN IF NOT EXISTS `sha256_hash` CHAR(64) DEFAULT NULL;
ALTER TABLE `vault_deliveries` ADD COLUMN IF NOT EXISTS `delivery_version` INT(11) NOT NULL DEFAULT 1;
ALTER TABLE `vault_deliveries` ADD COLUMN IF NOT EXISTS `is_superseded` TINYINT(1) NOT NULL DEFAULT 0;
ALTER TABLE `vault_deliveries` ADD COLUMN IF NOT EXISTS `superseded_at` DATETIME DEFAULT NULL;
ALTER TABLE `vault_deliveries` ADD COLUMN IF NOT EXISTS `replaced_by_id` BIGINT(20) DEFAULT NULL;
ALTER TABLE `vault_deliveries` ADD COLUMN IF NOT EXISTS `uploaded_by` INT(11) DEFAULT NULL;
ALTER TABLE `vault_deliveries` ADD COLUMN IF NOT EXISTS `notes` VARCHAR(255) DEFAULT NULL;
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;

ALTER TABLE `ticket_messages` ADD COLUMN IF NOT EXISTS `attachment_name` VARCHAR(180) DEFAULT NULL;

ALTER TABLE `reviews` ADD COLUMN IF NOT EXISTS `order_id` BIGINT(20) DEFAULT NULL;
ALTER TABLE `reviews` ADD COLUMN IF NOT EXISTS `product_id` INT(11) DEFAULT NULL;

ALTER TABLE `fulfillment_jobs` ADD COLUMN IF NOT EXISTS `max_attempts` INT(11) NOT NULL DEFAULT 5;
ALTER TABLE `fulfillment_jobs` ADD COLUMN IF NOT EXISTS `dead_lettered_at` DATETIME DEFAULT NULL;