-- BoostWithKagujje Database Schema
-- Migration: 002_admin_tables.sql
-- Purpose: Add admin-specific tables and functionality

SET FOREIGN_KEY_CHECKS=0;

-- Admin Logs table
CREATE TABLE IF NOT EXISTS `admin_logs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `admin_id` bigint unsigned NOT NULL,
  `action` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `details` longtext COLLATE utf8mb4_unicode_ci,
  `ip_address` varchar(45),
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`admin_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  KEY `action_idx` (`action`),
  KEY `created_idx` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Audit Trail table (for sensitive operations)
CREATE TABLE IF NOT EXISTS `audit_trail` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user_id` bigint unsigned,
  `table_name` varchar(100) COLLATE utf8mb4_unicode_ci,
  `record_id` bigint unsigned,
  `action` enum('insert', 'update', 'delete') NOT NULL,
  `old_values` json,
  `new_values` json,
  `ip_address` varchar(45),
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  KEY `table_idx` (`table_name`),
  KEY `action_idx` (`action`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Service Sync Log table
CREATE TABLE IF NOT EXISTS `service_sync_logs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `sync_start` timestamp,
  `sync_end` timestamp,
  `services_added` int DEFAULT 0,
  `services_updated` int DEFAULT 0,
  `services_removed` int DEFAULT 0,
  `status` enum('pending', 'in_progress', 'completed', 'failed') DEFAULT 'pending',
  `error_message` longtext,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Commission History table
CREATE TABLE IF NOT EXISTS `commission_history` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `referral_id` bigint unsigned NOT NULL,
  `order_id` bigint unsigned,
  `commission_amount` decimal(15, 2) NOT NULL,
  `currency` varchar(3) DEFAULT 'UGX',
  `status` enum('pending', 'earned', 'withdrawn') DEFAULT 'pending',
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`referral_id`) REFERENCES `referrals`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE SET NULL,
  KEY `referral_idx` (`referral_id`),
  KEY `status_idx` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Email Queue table
CREATE TABLE IF NOT EXISTS `email_queue` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `recipient` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `subject` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `template` varchar(100),
  `data` json,
  `status` enum('pending', 'sent', 'failed') DEFAULT 'pending',
  `retry_count` int DEFAULT 0,
  `last_error` text,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `sent_at` timestamp NULL,
  KEY `status_idx` (`status`),
  KEY `recipient_idx` (`recipient`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Webhook Events table
CREATE TABLE IF NOT EXISTS `webhook_events` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `event_type` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `source` varchar(50),
  `payload` json NOT NULL,
  `delivered` boolean DEFAULT false,
  `response_code` int,
  `response_body` longtext,
  `retry_count` int DEFAULT 0,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `delivered_at` timestamp NULL,
  KEY `event_idx` (`event_type`),
  KEY `source_idx` (`source`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Rate Limiting table
CREATE TABLE IF NOT EXISTS `rate_limits` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user_id` bigint unsigned,
  `api_key` varchar(255),
  `endpoint` varchar(255) COLLATE utf8mb4_unicode_ci,
  `ip_address` varchar(45),
  `request_count` int DEFAULT 0,
  `window_reset_at` timestamp,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  KEY `user_idx` (`user_id`),
  KEY `api_key_idx` (`api_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Update settings table with additional default settings
INSERT IGNORE INTO `settings` (`setting_key`, `setting_value`, `data_type`, `description`) VALUES
('mail_driver', 'smtp', 'string', 'Email driver (smtp, sendmail)'),
('mail_host', 'smtp.mailtrap.io', 'string', 'Mail server host'),
('mail_port', '465', 'string', 'Mail server port'),
('mail_username', 'your_username', 'string', 'Mail server username'),
('mail_password', 'your_password', 'string', 'Mail server password'),
('mail_from_address', 'noreply@boost.kagujje.com', 'string', 'Default from email address'),
('mail_from_name', 'BoostWithKagujje', 'string', 'Default from name'),
('payment_min_amount', '5000', 'integer', 'Minimum payment amount in UGX'),
('payment_max_amount', '10000000', 'integer', 'Maximum payment amount in UGX'),
('service_api_timeout', '30', 'integer', 'Service API timeout in seconds'),
('auto_sync_enabled', 'true', 'boolean', 'Enable automatic service synchronization'),
('auto_sync_interval', '3600', 'integer', 'Auto-sync interval in seconds'),
('commission_payout_enabled', 'true', 'boolean', 'Enable automatic commission payouts'),
('commission_payout_min', '50000', 'integer', 'Minimum commission to trigger payout'),
('support_email', 'support@boost.kagujje.com', 'string', 'Support email address'),
('maintenance_mode', 'false', 'boolean', 'Enable maintenance mode');

SET FOREIGN_KEY_CHECKS=1;