-- SQL for Production Import: webhook_logs table
-- Created: 2025-12-26
-- Purpose: Track Razorpay webhook events for debugging UID generation issues

CREATE TABLE IF NOT EXISTS `webhook_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `event_type` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'payment.captured, payment.failed, etc.',
  `payment_id` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Razorpay payment_id',
  `order_id` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Razorpay order_id',
  `user_id` bigint(20) unsigned DEFAULT NULL COMMENT 'User ID from notes or matched',
  `status` enum('pending','processed','failed','duplicate') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `razorpay_signature` text COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Webhook signature for verification',
  `payload` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Full webhook JSON payload',
  `error_message` text COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Error if processing failed',
  `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Webhook sender IP',
  `processed_at` timestamp NULL DEFAULT NULL COMMENT 'When webhook was processed',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `webhook_logs_event_type_index` (`event_type`),
  KEY `webhook_logs_payment_id_index` (`payment_id`),
  KEY `webhook_logs_order_id_index` (`order_id`),
  KEY `webhook_logs_user_id_index` (`user_id`),
  KEY `webhook_logs_status_index` (`status`),
  KEY `webhook_logs_created_at_index` (`created_at`),
  KEY `webhook_logs_status_created_at_index` (`status`,`created_at`),
  KEY `webhook_logs_payment_id_event_type_index` (`payment_id`,`event_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add indexes for better query performance
ALTER TABLE `webhook_logs` 
  ADD INDEX `idx_event_created` (`event_type`, `created_at`),
  ADD INDEX `idx_user_status` (`user_id`, `status`);

-- Verification: Check table structure
DESCRIBE `webhook_logs`;

-- Usage Instructions:
-- 1. Backup your database before importing
-- 2. Import this SQL file: mysql -u username -p database_name < webhook_logs_production.sql
-- 3. Verify table created: SHOW TABLES LIKE 'webhook_logs';
-- 4. Grant permissions if needed: GRANT ALL ON database_name.webhook_logs TO 'app_user'@'localhost';
