-- ============================================================================
-- ADVANCED SMM PANEL - DATABASE SCHEMA
-- Modern, Scalable Social Media Marketing Panel System
-- ============================================================================


CREATE TABLE `user_activity_logs` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) UNSIGNED NOT NULL,
  `action` VARCHAR(100) NOT NULL,
  `description` TEXT,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `user_agent` VARCHAR(255) DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  INDEX `idx_user_id` (`user_id`),
  INDEX `idx_action` (`action`),
  INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 2. SERVICES & CATEGORIES
-- ============================================================================

CREATE TABLE `service_categories` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `slug` VARCHAR(100) NOT NULL UNIQUE,
  `description` TEXT,
  `icon` VARCHAR(50) DEFAULT NULL,
  `sort_order` INT(11) DEFAULT 0,
  `status` ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_slug` (`slug`),
  INDEX `idx_status` (`status`),
  INDEX `idx_sort` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `services` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_id` INT(11) UNSIGNED NOT NULL,
  `provider_id` INT(11) UNSIGNED DEFAULT NULL,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT,
  `type` ENUM('standard','custom','subscription','drip-feed') NOT NULL DEFAULT 'standard',
  `price_per_1000` DECIMAL(15,4) NOT NULL,
  `min_quantity` INT(11) NOT NULL DEFAULT 100,
  `max_quantity` INT(11) NOT NULL DEFAULT 100000,
  `average_time` VARCHAR(50) DEFAULT NULL,
  `provider_service_id` VARCHAR(50) DEFAULT NULL,
  `refill_enabled` TINYINT(1) DEFAULT 0,
  `cancel_enabled` TINYINT(1) DEFAULT 0,
  `status` ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `sort_order` INT(11) DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`category_id`) REFERENCES `service_categories`(`id`) ON DELETE CASCADE,
  INDEX `idx_category` (`category_id`),
  INDEX `idx_status` (`status`),
  INDEX `idx_provider` (`provider_id`),
  INDEX `idx_sort` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 3. ORDERS & TRANSACTIONS
-- ============================================================================

CREATE TABLE `orders` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) UNSIGNED NOT NULL,
  `service_id` INT(11) UNSIGNED NOT NULL,
  `order_type` ENUM('standard','drip-feed','subscription') NOT NULL DEFAULT 'standard',
  `link` TEXT NOT NULL,
  `quantity` INT(11) NOT NULL,
  `charge` DECIMAL(15,4) NOT NULL,
  `start_count` INT(11) DEFAULT 0,
  `remains` INT(11) DEFAULT 0,
  `status` ENUM('pending','processing','in_progress','completed','partial','canceled','refunded') NOT NULL DEFAULT 'pending',
  `provider_order_id` VARCHAR(100) DEFAULT NULL,
  `provider_response` TEXT,
  `auto_topup` TINYINT(1) DEFAULT 0,
  `topup_transaction_id` INT(11) UNSIGNED DEFAULT NULL,
  `notes` TEXT,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`service_id`) REFERENCES `services`(`id`) ON DELETE RESTRICT,
  INDEX `idx_user` (`user_id`),
  INDEX `idx_service` (`service_id`),
  INDEX `idx_status` (`status`),
  INDEX `idx_created` (`created_at`),
  INDEX `idx_provider_order` (`provider_order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `drip_feed_orders` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_id` INT(11) UNSIGNED NOT NULL,
  `runs` INT(11) NOT NULL,
  `interval` INT(11) NOT NULL COMMENT 'Minutes between runs',
  `runs_completed` INT(11) DEFAULT 0,
  `last_run` DATETIME DEFAULT NULL,
  `next_run` DATETIME DEFAULT NULL,
  `status` ENUM('active','paused','completed') NOT NULL DEFAULT 'active',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE,
  INDEX `idx_next_run` (`next_run`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `subscription_orders` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_id` INT(11) UNSIGNED NOT NULL,
  `username` VARCHAR(100) NOT NULL,
  `posts` INT(11) NOT NULL,
  `delay` INT(11) NOT NULL COMMENT 'Minutes',
  `status` ENUM('active','paused','completed','expired') NOT NULL DEFAULT 'active',
  `expiry_date` DATE DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE,
  INDEX `idx_username` (`username`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 4. PAYMENT SYSTEM
-- ============================================================================

CREATE TABLE `payment_methods` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(50) NOT NULL UNIQUE,
  `name` VARCHAR(100) NOT NULL,
  `description` TEXT,
  `logo` VARCHAR(255) DEFAULT NULL,
  `min_amount` DECIMAL(15,2) NOT NULL DEFAULT 1.00,
  `max_amount` DECIMAL(15,2) NOT NULL DEFAULT 10000.00,
  `fee_fixed` DECIMAL(15,2) DEFAULT 0.00,
  `fee_percentage` DECIMAL(5,2) DEFAULT 0.00,
  `currencies` JSON DEFAULT NULL,
  `config` JSON DEFAULT NULL COMMENT 'API keys and settings',
  `status` ENUM('active','inactive','maintenance') NOT NULL DEFAULT 'active',
  `sort_order` INT(11) DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_code` (`code`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `transactions` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) UNSIGNED NOT NULL,
  `type` ENUM('deposit','order','refund','transfer_in','transfer_out','bonus','deduction') NOT NULL,
  `method` VARCHAR(50) DEFAULT NULL,
  `amount` DECIMAL(15,4) NOT NULL,
  `fee` DECIMAL(15,4) DEFAULT 0.0000,
  `net_amount` DECIMAL(15,4) NOT NULL,
  `balance_before` DECIMAL(15,4) NOT NULL,
  `balance_after` DECIMAL(15,4) NOT NULL,
  `reference` VARCHAR(100) UNIQUE,
  `payment_reference` VARCHAR(255) DEFAULT NULL,
  `status` ENUM('pending','processing','completed','failed','canceled','refunded') NOT NULL DEFAULT 'pending',
  `metadata` JSON DEFAULT NULL,
  `notes` TEXT,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  INDEX `idx_user` (`user_id`),
  INDEX `idx_reference` (`reference`),
  INDEX `idx_payment_ref` (`payment_reference`),
  INDEX `idx_status` (`status`),
  INDEX `idx_type` (`type`),
  INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `payment_logs` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `transaction_id` INT(11) UNSIGNED DEFAULT NULL,
  `user_id` INT(11) UNSIGNED DEFAULT NULL,
  `payment_method` VARCHAR(50) NOT NULL,
  `log_type` ENUM('info','success','warning','error','debug') NOT NULL DEFAULT 'info',
  `message` TEXT NOT NULL,
  `request_data` JSON DEFAULT NULL,
  `response_data` JSON DEFAULT NULL,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`transaction_id`) REFERENCES `transactions`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_transaction` (`transaction_id`),
  INDEX `idx_user` (`user_id`),
  INDEX `idx_method` (`payment_method`),
  INDEX `idx_type` (`log_type`),
  INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 7. TICKETS & SUPPORT
-- ============================================================================

CREATE TABLE `tickets` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) UNSIGNED NOT NULL,
  `subject` VARCHAR(255) NOT NULL,
  `category` ENUM('billing','technical','orders','account','other') NOT NULL DEFAULT 'other',
  `priority` ENUM('low','medium','high','urgent') NOT NULL DEFAULT 'medium',
  `status` ENUM('open','pending','answered','closed') NOT NULL DEFAULT 'open',
  `last_reply_by` ENUM('user','admin') DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  INDEX `idx_user` (`user_id`),
  INDEX `idx_status` (`status`),
  INDEX `idx_priority` (`priority`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `ticket_messages` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ticket_id` INT(11) UNSIGNED NOT NULL,
  `user_id` INT(11) UNSIGNED DEFAULT NULL,
  `is_admin` TINYINT(1) NOT NULL DEFAULT 0,
  `message` TEXT NOT NULL,
  `attachments` JSON DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`ticket_id`) REFERENCES `tickets`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_ticket` (`ticket_id`),
  INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 8. REFERRALS & EARNINGS
-- ============================================================================

CREATE TABLE `referrals` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `referrer_id` INT(11) UNSIGNED NOT NULL,
  `referred_id` INT(11) UNSIGNED NOT NULL,
  `commission_rate` DECIMAL(5,2) NOT NULL DEFAULT 5.00,
  `total_earned` DECIMAL(15,4) NOT NULL DEFAULT 0.0000,
  `status` ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`referrer_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`referred_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  UNIQUE KEY `unique_referral` (`referrer_id`, `referred_id`),
  INDEX `idx_referrer` (`referrer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `referral_earnings` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `referral_id` INT(11) UNSIGNED NOT NULL,
  `order_id` INT(11) UNSIGNED NOT NULL,
  `amount` DECIMAL(15,4) NOT NULL,
  `commission_rate` DECIMAL(5,2) NOT NULL,
  `commission_amount` DECIMAL(15,4) NOT NULL,
  `status` ENUM('pending','paid') NOT NULL DEFAULT 'pending',
  `paid_at` DATETIME DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`referral_id`) REFERENCES `referrals`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE,
  INDEX `idx_referral` (`referral_id`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 9. COUPONS & PROMOTIONS
-- ============================================================================

CREATE TABLE `coupons` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(50) NOT NULL UNIQUE,
  `type` ENUM('fixed','percentage') NOT NULL DEFAULT 'fixed',
  `value` DECIMAL(15,4) NOT NULL,
  `min_amount` DECIMAL(15,2) DEFAULT 0.00,
  `max_uses` INT(11) DEFAULT NULL,
  `uses_count` INT(11) NOT NULL DEFAULT 0,
  `valid_from` DATETIME DEFAULT NULL,
  `valid_until` DATETIME DEFAULT NULL,
  `status` ENUM('active','inactive','expired') NOT NULL DEFAULT 'active',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_code` (`code`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `coupon_usage` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `coupon_id` INT(11) UNSIGNED NOT NULL,
  `user_id` INT(11) UNSIGNED NOT NULL,
  `transaction_id` INT(11) UNSIGNED DEFAULT NULL,
  `discount_amount` DECIMAL(15,4) NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`coupon_id`) REFERENCES `coupons`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`transaction_id`) REFERENCES `transactions`(`id`) ON DELETE SET NULL,
  INDEX `idx_coupon` (`coupon_id`),
  INDEX `idx_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 10. NOTIFICATIONS
-- ============================================================================

CREATE TABLE `notifications` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) UNSIGNED NOT NULL,
  `type` VARCHAR(50) NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `message` TEXT NOT NULL,
  `link` VARCHAR(255) DEFAULT NULL,
  `is_read` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  INDEX `idx_user` (`user_id`),
  INDEX `idx_read` (`is_read`),
  INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- INITIAL DATA
-- ============================================================================

-- Insert payment methods
INSERT INTO `payment_methods` (`code`, `name`, `description`, `min_amount`, `max_amount`, `currencies`, `status`) VALUES
('korapay', 'Korapay', 'Accept payments via Korapay (Nigeria)', 100.00, 1000000.00, '["NGN","USD"]', 'active'),
('flutterwave', 'Flutterwave', 'Accept payments across Africa', 1.00, 100000.00, '["NGN","KES","GHS","USD"]', 'active'),
('paystack', 'Paystack', 'Modern payment gateway for Africa', 100.00, 1000000.00, '["NGN","GHS","ZAR","USD"]', 'active'),
('expresspay', 'ExpressPay', 'Ghana payment gateway', 1.00, 50000.00, '["GHS","USD"]', 'active'),
('cryptomus', 'Cryptomus', 'Cryptocurrency payments', 5.00, 100000.00, '["USD","EUR","BTC","ETH","USDT"]', 'active'),
('heleket', 'Heleket', 'Ethiopian payment gateway', 10.00, 100000.00, '["ETB"]', 'active'),
('pesapal', 'PesaPal', 'East Africa payment solution', 10.00, 1000000.00, '["KES","UGX","TZS","USD"]', 'active');

-- ============================================================================
-- SAMPLE CATEGORIES & SERVICES
-- ============================================================================

INSERT INTO `service_categories` (`name`, `slug`, `description`, `icon`, `sort_order`) VALUES
('Instagram', 'instagram', 'Instagram services', 'instagram', 1),
('Facebook', 'facebook', 'Facebook services', 'facebook', 2),
('Twitter', 'twitter', 'Twitter services', 'twitter', 3),
('YouTube', 'youtube', 'YouTube services', 'youtube', 4),
('TikTok', 'tiktok', 'TikTok services', 'tiktok', 5);

-- ============================================================================
-- END OF SCHEMA
-- ============================================================================
