-- =====================================================
-- OTP Log Table Creation Script
-- For Harvest Rewards API System
-- =====================================================

-- Drop table if exists (for clean installation)

-- Create otp_log table
CREATE TABLE `otp_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `phone_number` varchar(20) DEFAULT NULL,
  `otp_code` varchar(10) NOT NULL,
  `status` enum('pending','verified','expired','failed') NOT NULL DEFAULT 'pending',
  `sms_sent` tinyint(1) DEFAULT 0,
  `sms_message_id` varchar(100) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `expires_at` timestamp NOT NULL,
  `verified_at` timestamp NULL DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_otp_code` (`otp_code`),
  KEY `idx_status` (`status`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_expires_at` (`expires_at`),
  KEY `idx_user_otp_status` (`user_id`, `otp_code`, `status`),
  CONSTRAINT `fk_otp_log_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Sample Data (Optional - Remove in production)
-- =====================================================

-- Insert sample OTP records for testing (uncomment if needed)
-- INSERT INTO `otp_log` (`user_id`, `phone_number`, `otp_code`, `status`, `sms_sent`, `created_at`, `expires_at`) VALUES
-- (1, '+27123456789', '123456', 'pending', 1, NOW(), DATE_ADD(NOW(), INTERVAL 10 MINUTE)),
-- (1, '+27123456789', '654321', 'expired', 1, DATE_SUB(NOW(), INTERVAL 1 HOUR), DATE_SUB(NOW(), INTERVAL 50 MINUTE));

-- =====================================================
-- Indexes for Performance
-- =====================================================

-- Additional indexes for common queries
CREATE INDEX `idx_phone_number` ON `otp_log` (`phone_number`);
CREATE INDEX `idx_sms_message_id` ON `otp_log` (`sms_message_id`);

-- =====================================================
-- Cleanup Procedure (Optional)
-- =====================================================

-- Create a procedure to clean up old OTP records
DELIMITER //

CREATE PROCEDURE CleanupOldOTPs()
BEGIN
    -- Delete OTP records older than 24 hours
    DELETE FROM otp_log 
    WHERE created_at < DATE_SUB(NOW(), INTERVAL 24 HOUR);
    
    -- Get count of deleted records
    SELECT ROW_COUNT() as deleted_records;
END //

DELIMITER ;

-- =====================================================
-- Table Information
-- =====================================================

-- Show table structure
-- DESCRIBE otp_log;

-- Show indexes
-- SHOW INDEX FROM otp_log;

-- =====================================================
-- Usage Notes
-- =====================================================

/*
Table Fields Explanation:

1. id - Primary key, auto-increment
2. user_id - Foreign key to users table
3. phone_number - Phone number where OTP was sent (for logging)
4. otp_code - The actual OTP code (6 digits)
5. status - Current status of OTP:
   - 'pending': OTP generated and sent, waiting for verification
   - 'verified': OTP successfully verified
   - 'expired': OTP has expired (after 10 minutes)
   - 'failed': OTP verification failed
6. sms_sent - Boolean flag indicating if SMS was successfully sent
7. sms_message_id - Message ID from BulkSMS for tracking
8. created_at - When OTP was generated
9. expires_at - When OTP expires (created_at + 10 minutes)
10. verified_at - When OTP was successfully verified
11. ip_address - IP address of the request (for security)
12. user_agent - User agent string (for security)

Indexes:
- Primary key on id
- Foreign key on user_id
- Composite index on (user_id, otp_code, status) for fast lookups
- Individual indexes on commonly queried fields

Security Features:
- Foreign key constraint ensures data integrity
- Indexes for fast queries
- Cleanup procedure to remove old records
- IP and user agent tracking for security auditing

Performance:
- Optimized for fast OTP verification queries
- Efficient cleanup of old records
- Proper indexing for common query patterns
*/

