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


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



