-- BoostWithKagujje Database Schema
-- Migration: 001_initial_schema.sql
-- Created: 2024
-- Purpose: Initial database setup for SMM panel platform

SET FOREIGN_KEY_CHECKS=0;

-- Users table
CREATE TABLE IF NOT EXISTS `users` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `username` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL UNIQUE,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL UNIQUE,
  `full_name` varchar(255) COLLATE utf8mb4_unicode_ci,
  `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `phone` varchar(20),
  `country` varchar(100),
  `status` enum('active', 'inactive', 'suspended', 'banned') DEFAULT 'active',
  `account_type` enum('user', 'reseller', 'admin') DEFAULT 'user',
  `balance` decimal(15, 2) DEFAULT 0.00,
  `primary_currency` varchar(3) DEFAULT 'UGX',
  `two_factor_enabled` boolean DEFAULT false,
  `api_key` varchar(255),
  `last_login` timestamp NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `email_idx` (`email`),
  KEY `username_idx` (`username`),
  KEY `status_idx` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Services table
CREATE TABLE IF NOT EXISTS `services` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `service_id` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL UNIQUE,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `category` varchar(100),
  `base_price` decimal(10, 4) NOT NULL,
  `currency` varchar(3) DEFAULT 'UGX',
  `min_quantity` int DEFAULT 1,
  `max_quantity` int DEFAULT 10000,
  `is_refillable` boolean DEFAULT false,
  `refill_days` int,
  `status` enum('active', 'inactive') DEFAULT 'active',
  `provider` varchar(50) DEFAULT 'mrgoviral',
  `last_synced` timestamp NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `category_idx` (`category`),
  KEY `status_idx` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Orders table
CREATE TABLE IF NOT EXISTS `orders` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `order_id` varchar(50) COLLATE utf8mb4_unicode_ci UNIQUE,
  `user_id` bigint unsigned NOT NULL,
  `service_id` bigint unsigned NOT NULL,
  `external_order_id` varchar(100),
  `link` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `quantity` int NOT NULL,
  `start_count` int,
  `current_count` int,
  `remaining` int,
  `status` enum('pending', 'processing', 'completed', 'cancelled', 'refunded', 'partial') DEFAULT 'pending',
  `quantity_received` int DEFAULT 0,
  `price` decimal(10, 2) NOT NULL,
  `currency` varchar(3) DEFAULT 'UGX',
  `note` text COLLATE utf8mb4_unicode_ci,
  `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`) ON DELETE RESTRICT,
  KEY `user_idx` (`user_id`),
  KEY `status_idx` (`status`),
  KEY `created_idx` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Payments table
CREATE TABLE IF NOT EXISTS `payments` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `payment_id` varchar(100) COLLATE utf8mb4_unicode_ci UNIQUE,
  `user_id` bigint unsigned NOT NULL,
  `reference` varchar(100) COLLATE utf8mb4_unicode_ci,
  `amount` decimal(12, 2) NOT NULL,
  `currency` varchar(3) DEFAULT 'UGX',
  `gateway` varchar(50) DEFAULT 'marzpay',
  `gateway_reference` varchar(255),
  `status` enum('pending', 'completed', 'failed', 'refunded', 'expired') DEFAULT 'pending',
  `method` varchar(50),
  `description` text COLLATE utf8mb4_unicode_ci,
  `metadata` json,
  `paid_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,
  KEY `user_idx` (`user_id`),
  KEY `status_idx` (`status`),
  KEY `reference_idx` (`reference`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tickets table
CREATE TABLE IF NOT EXISTS `tickets` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `ticket_number` varchar(50) COLLATE utf8mb4_unicode_ci UNIQUE NOT NULL,
  `user_id` bigint unsigned NOT NULL,
  `subject` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `category` varchar(100),
  `priority` enum('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
  `status` enum('open', 'in_progress', 'pending_user', 'resolved', 'closed') DEFAULT 'open',
  `message` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `reply` longtext COLLATE utf8mb4_unicode_ci,
  `assigned_to` bigint unsigned,
  `closure_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,
  KEY `user_idx` (`user_id`),
  KEY `status_idx` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Referrals table
CREATE TABLE IF NOT EXISTS `referrals` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `referrer_id` bigint unsigned NOT NULL,
  `referred_id` bigint unsigned NOT NULL,
  `commission_rate` decimal(5, 2) DEFAULT 10.00,
  `commission_earned` decimal(15, 2) DEFAULT 0.00,
  `currency` varchar(3) DEFAULT 'UGX',
  `status` enum('pending', 'active', 'inactive') DEFAULT 'active',
  `referred_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`referrer_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`referred_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  KEY `referrer_idx` (`referrer_id`),
  KEY `status_idx` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Transactions table
CREATE TABLE IF NOT EXISTS `transactions` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user_id` bigint unsigned NOT NULL,
  `transaction_type` enum('deposit', 'withdrawal', 'order', 'commission', 'refund', 'charge') DEFAULT 'order',
  `reference_id` varchar(100) COLLATE utf8mb4_unicode_ci,
  `description` text COLLATE utf8mb4_unicode_ci,
  `amount` decimal(15, 2) NOT NULL,
  `currency` varchar(3) DEFAULT 'UGX',
  `previous_balance` decimal(15, 2),
  `new_balance` decimal(15, 2),
  `status` enum('pending', 'completed', 'failed', 'cancelled') DEFAULT 'completed',
  `gateway` varchar(50),
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  KEY `user_idx` (`user_id`),
  KEY `type_idx` (`transaction_type`),
  KEY `created_idx` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- API Logs table
CREATE TABLE IF NOT EXISTS `api_logs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user_id` bigint unsigned,
  `api_key` varchar(255),
  `endpoint` varchar(255) COLLATE utf8mb4_unicode_ci,
  `method` varchar(10),
  `request_data` json,
  `response_status` varchar(50),
  `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,
  KEY `api_key_idx` (`api_key`),
  KEY `created_idx` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Settings table
CREATE TABLE IF NOT EXISTS `settings` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `setting_key` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL UNIQUE,
  `setting_value` longtext COLLATE utf8mb4_unicode_ci,
  `data_type` enum('string', 'integer', 'boolean', 'json') DEFAULT 'string',
  `description` text,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `key_idx` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default settings
INSERT INTO `settings` (`setting_key`, `setting_value`, `data_type`, `description`) VALUES
('site_name', 'BoostWithKagujje', 'string', 'Site name'),
('site_url', 'https://boost.kagujje.com', 'string', 'Site URL'),
('primary_currency', 'UGX', 'string', 'Primary currency code'),
('min_deposit', '10000', 'integer', 'Minimum deposit amount'),
('max_deposits_per_day', '10', 'integer', 'Maximum deposits per user per day'),
('referral_commission', '10', 'integer', 'Referral commission percentage'),
('service_sync_interval', '3600', 'integer', 'Service synchronization interval in seconds'),
('enable_registration', 'true', 'boolean', 'Enable new user registration'),
('enable_referral_program', 'true', 'boolean', 'Enable referral program'),
('api_rate_limit', '100', 'integer', 'API rate limit per hour'),
('ticket_auto_close_days', '30', 'integer', 'Auto-close resolved tickets after days')
ON DUPLICATE KEY UPDATE `setting_value` = VALUES(`setting_value`);

SET FOREIGN_KEY_CHECKS=1;