-- BoostWithKagujje Platform Database Schema
-- Import this into phpMyAdmin to create all tables
-- Version: Phase 3 Production

-- Create database
CREATE DATABASE IF NOT EXISTS `kagujje1_smm` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `kagujje1_smm`;

-- ===== USERS TABLE =====
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(50) UNIQUE NOT NULL,
  `email` VARCHAR(100) UNIQUE NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `full_name` VARCHAR(100),
  `phone` VARCHAR(20),
  `balance` DECIMAL(12, 2) DEFAULT 0,
  `primary_currency` VARCHAR(3) DEFAULT 'UGX',
  `account_type` ENUM('user', 'seller', 'admin') DEFAULT 'user',
  `status` ENUM('active', 'suspended', 'banned', 'pending') DEFAULT 'pending',
  `verification_status` ENUM('unverified', 'verified') DEFAULT 'unverified',
  `api_key` VARCHAR(255) UNIQUE,
  `telegram_user_id` VARCHAR(50) UNIQUE,
  `telegram_chat_id` VARCHAR(50),
  `referral_code` VARCHAR(20) UNIQUE,
  `referred_by` INT,
  `two_fa_enabled` BOOLEAN DEFAULT FALSE,
  `last_login` TIMESTAMP NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_username` (`username`),
  INDEX `idx_email` (`email`),
  INDEX `idx_api_key` (`api_key`),
  INDEX `idx_telegram_user_id` (`telegram_user_id`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===== SERVICES TABLE =====
CREATE TABLE IF NOT EXISTS `services` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `provider_id` INT NOT NULL DEFAULT 1,
  `name` VARCHAR(255) NOT NULL,
  `category` VARCHAR(50) NOT NULL,
  `description` LONGTEXT,
  `base_price` DECIMAL(10, 4) NOT NULL,
  `min_quantity` INT DEFAULT 1,
  `max_quantity` INT DEFAULT 10000,
  `refill_period` INT DEFAULT 0,
  `refill_allowed` BOOLEAN DEFAULT FALSE,
  `status` ENUM('active', 'inactive', 'discontinued') DEFAULT 'active',
  `provider_service_id` VARCHAR(50),
  `last_sync` TIMESTAMP NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_category` (`category`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===== ORDERS TABLE =====
CREATE TABLE IF NOT EXISTS `orders` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `service_id` INT NOT NULL,
  `order_id` VARCHAR(50) UNIQUE,
  `quantity` INT NOT NULL,
  `price` DECIMAL(12, 2) NOT NULL,
  `link` TEXT NOT NULL,
  `status` ENUM('pending', 'processing', 'in_progress', 'completed', 'partial', 'cancelled', 'refunded') DEFAULT 'pending',
  `provider_status` VARCHAR(50),
  `provider_order_id` VARCHAR(100),
  `start_date` TIMESTAMP NULL,
  `completion_date` TIMESTAMP NULL,
  `refund_reason` TEXT,
  `refunded_amount` DECIMAL(12, 2) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`service_id`) REFERENCES `services`(`id`),
  INDEX `idx_user_id` (`user_id`),
  INDEX `idx_status` (`status`),
  INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===== PAYMENTS TABLE =====
CREATE TABLE IF NOT EXISTS `payments` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `amount` DECIMAL(12, 2) NOT NULL,
  `currency` VARCHAR(3) DEFAULT 'UGX',
  `reference` VARCHAR(100) UNIQUE,
  `provider` VARCHAR(50) DEFAULT 'marzpay',
  `provider_reference` VARCHAR(100),
  `status` ENUM('pending', 'completed', 'failed', 'cancelled') DEFAULT 'pending',
  `payment_method` VARCHAR(50),
  `tx_reference` VARCHAR(100),
  `completed_at` TIMESTAMP NULL,
  `failed_reason` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  INDEX `idx_user_id` (`user_id`),
  INDEX `idx_status` (`status`),
  INDEX `idx_reference` (`reference`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===== TRANSACTIONS TABLE =====
CREATE TABLE IF NOT EXISTS `transactions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `type` ENUM('deposit', 'withdrawal', 'order', 'refund', 'bonus', 'fee') DEFAULT 'deposit',
  `amount` DECIMAL(12, 2) NOT NULL,
  `balance_before` DECIMAL(12, 2),
  `balance_after` DECIMAL(12, 2),
  `reference` VARCHAR(100),
  `description` TEXT,
  `status` ENUM('pending', 'completed', 'failed') DEFAULT 'completed',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  INDEX `idx_user_id` (`user_id`),
  INDEX `idx_type` (`type`),
  INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===== SETTINGS TABLE =====
CREATE TABLE IF NOT EXISTS `settings` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `setting_key` VARCHAR(100) UNIQUE NOT NULL,
  `setting_value` LONGTEXT,
  `description` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default settings
INSERT IGNORE INTO `settings` (`setting_key`, `setting_value`, `description`) VALUES
('telegram_user_bot_token', '', 'Telegram user bot token'),
('telegram_admin_bot_token', '', 'Telegram admin bot token'),
('telegram_admin_chat_ids', '', 'Comma-separated admin chat IDs'),
('telegram_webhook_enabled', '1', 'Enable Telegram webhooks'),
('minimum_deposit', '5000', 'Minimum deposit amount'),
('maximum_deposit', '5000000', 'Maximum deposit amount'),
('daily_deposit_limit', '10000000', 'Daily deposit limit per user'),
('profit_markup', '0.80', '80% markup on service prices'),
('email_verification_required', '0', 'Require email verification'),
('auto_refund_fail_orders', '1', 'Auto-refund orders that fail'),
('refund_delay_hours', '24', 'Hours to wait before auto-refund'),
('min_withdrawal', '10000', 'Minimum withdrawal amount'),
('max_withdrawal', '1000000', 'Maximum withdrawal amount'),
('platform_fee_percent', '0', 'Platform fee on orders'),
('referral_bonus_percent', '0', 'Referral bonus percentage');

-- ===== TELEGRAM_LOGS TABLE =====
CREATE TABLE IF NOT EXISTS `telegram_logs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `bot_type` ENUM('user', 'admin') DEFAULT 'user',
  `chat_id` VARCHAR(50),
  `user_id` INT,
  `username` VARCHAR(50),
  `message_type` VARCHAR(50),
  `command` VARCHAR(100),
  `message_data` LONGTEXT,
  `response` LONGTEXT,
  `status` ENUM('success', 'error', 'pending') DEFAULT 'pending',
  `error_message` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_bot_type` (`bot_type`),
  INDEX `idx_user_id` (`user_id`),
  INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===== EMAIL_QUEUE TABLE =====
CREATE TABLE IF NOT EXISTS `email_queue` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `recipient` VARCHAR(100) NOT NULL,
  `subject` VARCHAR(255) NOT NULL,
  `body` LONGTEXT NOT NULL,
  `status` ENUM('pending', 'sent', 'failed') DEFAULT 'pending',
  `attempts` INT DEFAULT 0,
  `last_attempt` TIMESTAMP NULL,
  `sent_at` TIMESTAMP NULL,
  `error_message` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_status` (`status`),
  INDEX `idx_recipient` (`recipient`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===== SYNC_LOGS TABLE =====
CREATE TABLE IF NOT EXISTS `sync_logs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `provider` VARCHAR(50) DEFAULT 'mrgoviral',
  `sync_type` VARCHAR(50) DEFAULT 'services',
  `total_services` INT DEFAULT 0,
  `services_added` INT DEFAULT 0,
  `services_updated` INT DEFAULT 0,
  `services_removed` INT DEFAULT 0,
  `status` ENUM('success', 'partial', 'failed') DEFAULT 'success',
  `error_message` TEXT,
  `duration_seconds` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_provider` (`provider`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===== API_LOGS TABLE =====
CREATE TABLE IF NOT EXISTS `api_logs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT,
  `method` VARCHAR(10),
  `endpoint` VARCHAR(255),
  `status_code` INT,
  `response_time_ms` INT,
  `ip_address` VARCHAR(45),
  `user_agent` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_endpoint` (`endpoint`),
  INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===== ADMIN_LOGS TABLE =====
CREATE TABLE IF NOT EXISTS `admin_logs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `admin_id` INT,
  `action` VARCHAR(100),
  `table_name` VARCHAR(50),
  `record_id` INT,
  `old_values` LONGTEXT,
  `new_values` LONGTEXT,
  `ip_address` VARCHAR(45),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`admin_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_action` (`action`),
  INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create indexes for performance
ALTER TABLE `users` ADD INDEX `idx_created_at` (`created_at`);
ALTER TABLE `orders` ADD INDEX `idx_service_id` (`service_id`);
ALTER TABLE `payments` ADD INDEX `idx_created_at` (`created_at`);

-- Initial admin user (change password after first login)
INSERT IGNORE INTO `users` (
  `username`, `email`, `password`, `full_name`, 
  `account_type`, `status`, `verification_status`
) VALUES (
  'admin',
  'admin@kagujje.com',
  '$2y$10$xvvhw0nGl3p/lHYvgGFn5.qIrVHCBfSd2rXXvs/pN1K6IM.lfqGEi', -- password: Admin123!
  'Administrator',
  'admin',
  'active',
  'verified'
);

-- Done
COMMIT;