-- =====================================================
-- WakaTraffic SMM Platform - MySQL Database Schema
-- For XAMPP/phpMyAdmin
-- =====================================================

-- Create Database
CREATE DATABASE IF NOT EXISTS wakatraffic CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE wakatraffic;

-- =====================================================
-- USERS TABLE
-- =====================================================
CREATE TABLE `users` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(255) NOT NULL UNIQUE,
  `email` VARCHAR(255) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `first_name` VARCHAR(100) DEFAULT NULL,
  `last_name` VARCHAR(100) DEFAULT NULL,
  `mobile_number` VARCHAR(20) DEFAULT NULL,
  `country_code` VARCHAR(5) DEFAULT '+234',
  `address` TEXT DEFAULT NULL,
  `city` VARCHAR(100) DEFAULT NULL,
  `state` VARCHAR(100) DEFAULT NULL,
  `zip_postal` VARCHAR(20) DEFAULT NULL,
  `country` VARCHAR(100) DEFAULT 'Nigeria',
  `balance` DECIMAL(10,2) DEFAULT 0.00,
  `profile_photo` VARCHAR(255) DEFAULT NULL,
  `email_verified` TINYINT(1) DEFAULT 0,
  `mobile_verified` TINYINT(1) DEFAULT 0,
  `two_fa_enabled` TINYINT(1) DEFAULT 0,
  `two_fa_secret` VARCHAR(255) DEFAULT NULL,
  `role` ENUM('user', 'admin') DEFAULT 'user',
  `status` ENUM('active', 'banned', 'suspended') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `last_login_at` TIMESTAMP NULL DEFAULT NULL,
  `last_login_ip` VARCHAR(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_email` (`email`),
  INDEX `idx_username` (`username`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- CATEGORIES TABLE
-- =====================================================
CREATE TABLE `categories` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `icon_class` VARCHAR(100) DEFAULT NULL,
  `description` TEXT DEFAULT NULL,
  `status` ENUM('active', 'inactive') DEFAULT 'active',
  `display_order` INT(11) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_status` (`status`),
  INDEX `idx_display_order` (`display_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- API PROVIDERS TABLE
-- =====================================================
CREATE TABLE `api_providers` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `api_url` TEXT NOT NULL,
  `api_key` VARCHAR(255) NOT NULL,
  `status` ENUM('active', 'inactive') DEFAULT 'active',
  `balance` DECIMAL(10,2) DEFAULT 0.00,
  `success_rate` DECIMAL(5,2) DEFAULT 0.00,
  `avg_completion_minutes` INT(11) DEFAULT 0,
  `last_sync_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- SERVICES TABLE
-- =====================================================
CREATE TABLE `services` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `category_id` INT(11) NOT NULL,
  `api_provider_id` INT(11) DEFAULT NULL,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `price` DECIMAL(10,2) NOT NULL,
  `min_quantity` INT(11) DEFAULT 10,
  `max_quantity` INT(11) DEFAULT 100000,
  `api_service_id` VARCHAR(100) DEFAULT NULL,
  `status` ENUM('active', 'inactive') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`api_provider_id`) REFERENCES `api_providers`(`id`) ON DELETE SET NULL,
  INDEX `idx_category` (`category_id`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- BRAND PACKAGES TABLE
-- =====================================================
CREATE TABLE `brand_packages` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `price` DECIMAL(10,2) NOT NULL,
  `original_price` DECIMAL(10,2) DEFAULT NULL,
  `featured_image` VARCHAR(255) DEFAULT NULL,
  `validity_days` INT(11) DEFAULT 30,
  `status` ENUM('active', 'inactive') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- PACKAGE SERVICES (Junction Table)
-- =====================================================
CREATE TABLE `package_services` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `package_id` INT(11) NOT NULL,
  `service_id` INT(11) NOT NULL,
  `quantity` INT(11) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`package_id`) REFERENCES `brand_packages`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`service_id`) REFERENCES `services`(`id`) ON DELETE CASCADE,
  INDEX `idx_package` (`package_id`),
  INDEX `idx_service` (`service_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- ORDERS TABLE
-- =====================================================
CREATE TABLE `orders` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `service_id` INT(11) NOT NULL,
  `api_provider_id` INT(11) DEFAULT NULL,
  `link_url` TEXT NOT NULL,
  `quantity` INT(11) NOT NULL,
  `price` DECIMAL(10,2) NOT NULL,
  `start_counter` INT(11) DEFAULT 0,
  `remains` INT(11) DEFAULT NULL,
  `api_order_id` VARCHAR(100) DEFAULT NULL,
  `status` ENUM('pending', 'processing', 'complete', 'cancelled', 'refunded', 'partial') DEFAULT 'pending',
  `status_description` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `completed_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`service_id`) REFERENCES `services`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`api_provider_id`) REFERENCES `api_providers`(`id`) ON DELETE SET NULL,
  INDEX `idx_user` (`user_id`),
  INDEX `idx_status` (`status`),
  INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- PACKAGE ORDERS TABLE
-- =====================================================
CREATE TABLE `package_orders` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `package_id` INT(11) NOT NULL,
  `price` DECIMAL(10,2) NOT NULL,
  `status` ENUM('pending', 'complete', 'cancelled', 'refunded') DEFAULT 'pending',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`package_id`) REFERENCES `brand_packages`(`id`) ON DELETE CASCADE,
  INDEX `idx_user` (`user_id`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TRANSACTIONS TABLE
-- =====================================================
CREATE TABLE `transactions` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `type` ENUM('deposit', 'order', 'refund', 'bonus', 'withdrawal') NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL,
  `charge` DECIMAL(10,2) DEFAULT 0.00,
  `post_balance` DECIMAL(10,2) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `reference_id` VARCHAR(100) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  INDEX `idx_user` (`user_id`),
  INDEX `idx_type` (`type`),
  INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- DEPOSITS TABLE
-- =====================================================
CREATE TABLE `deposits` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `payment_gateway` VARCHAR(50) NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL,
  `charge` DECIMAL(10,2) DEFAULT 0.00,
  `final_amount` DECIMAL(10,2) NOT NULL,
  `payment_reference` VARCHAR(100) DEFAULT NULL,
  `paystack_reference` VARCHAR(100) DEFAULT NULL,
  `proof_document` VARCHAR(255) DEFAULT NULL,
  `status` ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
  `admin_note` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `approved_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  INDEX `idx_user` (`user_id`),
  INDEX `idx_status` (`status`),
  INDEX `idx_paystack_ref` (`paystack_reference`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- SUPPORT TICKETS TABLE
-- =====================================================
CREATE TABLE `support_tickets` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `assigned_admin_id` INT(11) DEFAULT NULL,
  `subject` VARCHAR(255) NOT NULL,
  `priority` ENUM('low', 'medium', 'high') DEFAULT 'medium',
  `category` VARCHAR(100) DEFAULT NULL,
  `status` ENUM('open', 'pending', 'answered', 'closed') DEFAULT 'open',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `last_reply_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`assigned_admin_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_user` (`user_id`),
  INDEX `idx_status` (`status`),
  INDEX `idx_priority` (`priority`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TICKET MESSAGES TABLE
-- =====================================================
CREATE TABLE `ticket_messages` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `ticket_id` INT(11) NOT NULL,
  `user_id` INT(11) DEFAULT NULL,
  `is_admin` TINYINT(1) DEFAULT 0,
  `message` TEXT NOT NULL,
  `attachments` JSON DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`ticket_id`) REFERENCES `support_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;

-- =====================================================
-- NOTIFICATIONS TABLE
-- =====================================================
CREATE TABLE `notifications` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) DEFAULT NULL,
  `type` ENUM('email', 'sms', 'push') NOT NULL,
  `subject` VARCHAR(255) DEFAULT NULL,
  `message` TEXT NOT NULL,
  `status` ENUM('sent', 'failed', 'pending') DEFAULT 'pending',
  `sent_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  INDEX `idx_user` (`user_id`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- LOGIN HISTORY TABLE
-- =====================================================
CREATE TABLE `login_history` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `ip_address` VARCHAR(45) NOT NULL,
  `user_agent` TEXT DEFAULT NULL,
  `browser` VARCHAR(100) DEFAULT NULL,
  `os` VARCHAR(100) DEFAULT NULL,
  `device` VARCHAR(100) DEFAULT NULL,
  `country` VARCHAR(100) DEFAULT NULL,
  `city` VARCHAR(100) DEFAULT NULL,
  `login_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  INDEX `idx_user` (`user_id`),
  INDEX `idx_login_at` (`login_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- EMAIL SUBSCRIBERS TABLE
-- =====================================================
CREATE TABLE `email_subscribers` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(255) NOT NULL UNIQUE,
  `status` ENUM('active', 'unsubscribed') DEFAULT 'active',
  `subscribed_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `unsubscribed_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_email` (`email`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- BLOG POSTS TABLE
-- =====================================================
CREATE TABLE `blog_posts` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) NOT NULL,
  `slug` VARCHAR(255) NOT NULL UNIQUE,
  `featured_image` VARCHAR(255) DEFAULT NULL,
  `content` LONGTEXT NOT NULL,
  `meta_title` VARCHAR(255) DEFAULT NULL,
  `meta_description` TEXT DEFAULT NULL,
  `meta_keywords` TEXT DEFAULT NULL,
  `status` ENUM('published', 'draft') DEFAULT 'draft',
  `published_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `idx_slug` (`slug`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- PAGES TABLE
-- =====================================================
CREATE TABLE `pages` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) NOT NULL,
  `slug` VARCHAR(255) NOT NULL UNIQUE,
  `content` LONGTEXT NOT NULL,
  `meta_title` VARCHAR(255) DEFAULT NULL,
  `meta_description` TEXT DEFAULT NULL,
  `meta_keywords` TEXT DEFAULT NULL,
  `status` ENUM('active', 'inactive') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `idx_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- FRONTEND SECTIONS TABLE
-- =====================================================
CREATE TABLE `frontend_sections` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `section_key` VARCHAR(100) NOT NULL UNIQUE,
  `content` JSON NOT NULL,
  `status` ENUM('active', 'inactive') DEFAULT 'active',
  `display_order` INT(11) DEFAULT 0,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `idx_section_key` (`section_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- SITE SETTINGS TABLE
-- =====================================================
CREATE TABLE `site_settings` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `setting_key` VARCHAR(100) NOT NULL UNIQUE,
  `setting_value` TEXT DEFAULT NULL,
  `setting_type` ENUM('string', 'number', 'boolean', 'json') DEFAULT 'string',
  `setting_group` VARCHAR(50) DEFAULT 'general',
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `idx_setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- CRON LOGS TABLE
-- =====================================================
CREATE TABLE `cron_logs` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `execution_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `duration_seconds` INT(11) DEFAULT 0,
  `status` ENUM('success', 'failed') DEFAULT 'success',
  `actions_performed` JSON DEFAULT NULL,
  `errors` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_execution_time` (`execution_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- INSERT DEFAULT DATA
-- =====================================================

-- Insert default admin user
-- Password: admin123 (hashed with bcrypt)
INSERT INTO `users` (`username`, `email`, `password_hash`, `first_name`, `last_name`, `role`, `balance`, `status`) 
VALUES ('admin', 'admin@wakatraffic.ng', '$2b$10$YourHashedPasswordHere', 'Admin', 'User', 'admin', 0.00, 'active');

-- Insert sample categories
INSERT INTO `categories` (`name`, `icon_class`, `description`, `status`, `display_order`) VALUES
('Instagram', 'instagram', 'Instagram Services', 'active', 1),
('Facebook', 'facebook', 'Facebook Services', 'active', 2),
('Twitter', 'twitter', 'Twitter (X) Services', 'active', 3),
('YouTube', 'youtube', 'YouTube Services', 'active', 4),
('TikTok', 'camera', 'TikTok Services', 'active', 5),
('Spotify', 'music', 'Spotify Services', 'active', 6);

-- Insert sample API provider (Owlet)
INSERT INTO `api_providers` (`name`, `api_url`, `api_key`, `status`) VALUES
('Owlet Panel', 'https://owletpanel.com/api/v2', 'your-api-key-here', 'active');

-- Insert sample services
INSERT INTO `services` (`category_id`, `api_provider_id`, `name`, `description`, `price`, `min_quantity`, `max_quantity`, `status`) VALUES
(1, 1, 'Instagram Followers', 'Real Nigerian Instagram Followers', 400.45, 20, 100000, 'active'),
(1, 1, 'Instagram Likes', 'Instagram Post Likes', 200.00, 50, 500000, 'active'),
(2, 1, 'Facebook Page Likes', 'Real Facebook Page Likes', 300.00, 100, 100000, 'active'),
(3, 1, 'Twitter Followers', 'Twitter/X Followers', 350.00, 10, 150000, 'active'),
(4, 1, 'YouTube Views', 'YouTube Video Views', 250.00, 100, 1000000, 'active'),
(4, 1, 'YouTube Subscribers', 'YouTube Channel Subscribers', 450.00, 10, 50000, 'active'),
(5, 1, 'TikTok Followers', 'TikTok Followers Fast', 400.00, 10, 150000, 'active'),
(5, 1, 'TikTok Likes', 'TikTok Video Likes', 210.00, 10, 1000000, 'active'),
(6, 1, 'Spotify Plays', 'Spotify Track Plays', 300.00, 100, 100000, 'active');

-- Insert default site settings
INSERT INTO `site_settings` (`setting_key`, `setting_value`, `setting_type`, `setting_group`) VALUES
('site_name', 'WakaTraffic', 'string', 'general'),
('site_title', 'WakaTraffic - Boost Your Social Media', 'string', 'general'),
('site_email', 'support@wakatraffic.ng', 'string', 'general'),
('site_phone', '9876897689698', 'string', 'general'),
('currency', 'NGN', 'string', 'general'),
('currency_symbol', '₦', 'string', 'general'),
('registration_enabled', '1', 'boolean', 'general'),
('email_verification_required', '0', 'boolean', 'general');

-- =====================================================
-- VIEWS FOR REPORTING
-- =====================================================

-- User statistics view
CREATE OR REPLACE VIEW `v_user_stats` AS
SELECT 
    u.id,
    u.email,
    u.first_name,
    u.last_name,
    u.balance,
    u.status,
    COUNT(DISTINCT o.id) as total_orders,
    COUNT(DISTINCT d.id) as total_deposits,
    SUM(CASE WHEN d.status = 'approved' THEN d.amount ELSE 0 END) as total_deposited,
    COUNT(DISTINCT t.id) as total_transactions
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
LEFT JOIN deposits d ON u.id = d.user_id
LEFT JOIN transactions t ON u.id = t.user_id
GROUP BY u.id;

-- Order statistics view
CREATE OR REPLACE VIEW `v_order_stats` AS
SELECT 
    DATE(created_at) as order_date,
    COUNT(*) as total_orders,
    SUM(CASE WHEN status = 'complete' THEN 1 ELSE 0 END) as completed_orders,
    SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending_orders,
    SUM(CASE WHEN status = 'processing' THEN 1 ELSE 0 END) as processing_orders,
    SUM(price) as total_revenue
FROM orders
GROUP BY DATE(created_at)
ORDER BY order_date DESC;

-- =====================================================
-- STORED PROCEDURES
-- =====================================================

DELIMITER //

-- Procedure to create order
CREATE PROCEDURE `sp_create_order`(
    IN p_user_id INT,
    IN p_service_id INT,
    IN p_link_url TEXT,
    IN p_quantity INT,
    OUT p_order_id INT,
    OUT p_error_message VARCHAR(255)
)
BEGIN
    DECLARE v_service_price DECIMAL(10,2);
    DECLARE v_total_price DECIMAL(10,2);
    DECLARE v_user_balance DECIMAL(10,2);
    DECLARE v_new_balance DECIMAL(10,2);
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_error_message = 'An error occurred while creating the order';
        SET p_order_id = NULL;
    END;
    
    START TRANSACTION;
    
    -- Get service price
    SELECT price INTO v_service_price FROM services WHERE id = p_service_id;
    
    IF v_service_price IS NULL THEN
        SET p_error_message = 'Service not found';
        SET p_order_id = NULL;
        ROLLBACK;
    ELSE
        -- Calculate total price
        SET v_total_price = v_service_price * p_quantity;
        
        -- Get user balance
        SELECT balance INTO v_user_balance FROM users WHERE id = p_user_id FOR UPDATE;
        
        -- Check if user has sufficient balance
        IF v_user_balance < v_total_price THEN
            SET p_error_message = 'Insufficient balance';
            SET p_order_id = NULL;
            ROLLBACK;
        ELSE
            -- Calculate new balance
            SET v_new_balance = v_user_balance - v_total_price;
            
            -- Create order
            INSERT INTO orders (user_id, service_id, link_url, quantity, price, status, remains)
            VALUES (p_user_id, p_service_id, p_link_url, p_quantity, v_total_price, 'pending', p_quantity);
            
            SET p_order_id = LAST_INSERT_ID();
            
            -- Update user balance
            UPDATE users SET balance = v_new_balance WHERE id = p_user_id;
            
            -- Create transaction record
            INSERT INTO transactions (user_id, type, amount, post_balance, description, reference_id)
            VALUES (p_user_id, 'order', -v_total_price, v_new_balance, CONCAT('Order #', p_order_id), p_order_id);
            
            SET p_error_message = NULL;
            COMMIT;
        END IF;
    END IF;
END//

-- Procedure to approve deposit
CREATE PROCEDURE `sp_approve_deposit`(
    IN p_deposit_id INT,
    IN p_admin_note TEXT,
    OUT p_error_message VARCHAR(255)
)
BEGIN
    DECLARE v_user_id INT;
    DECLARE v_amount DECIMAL(10,2);
    DECLARE v_current_balance DECIMAL(10,2);
    DECLARE v_new_balance DECIMAL(10,2);
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_error_message = 'An error occurred while approving deposit';
    END;
    
    START TRANSACTION;
    
    -- Get deposit details
    SELECT user_id, amount INTO v_user_id, v_amount 
    FROM deposits 
    WHERE id = p_deposit_id AND status = 'pending';
    
    IF v_user_id IS NULL THEN
        SET p_error_message = 'Deposit not found or already processed';
        ROLLBACK;
    ELSE
        -- Get current balance
        SELECT balance INTO v_current_balance FROM users WHERE id = v_user_id FOR UPDATE;
        
        -- Calculate new balance
        SET v_new_balance = v_current_balance + v_amount;
        
        -- Update deposit status
        UPDATE deposits 
        SET status = 'approved', 
            admin_note = p_admin_note, 
            approved_at = NOW() 
        WHERE id = p_deposit_id;
        
        -- Update user balance
        UPDATE users SET balance = v_new_balance WHERE id = v_user_id;
        
        -- Create transaction record
        INSERT INTO transactions (user_id, type, amount, post_balance, description, reference_id)
        VALUES (v_user_id, 'deposit', v_amount, v_new_balance, 'Deposit approved', p_deposit_id);
        
        SET p_error_message = NULL;
        COMMIT;
    END IF;
END//

DELIMITER ;

-- =====================================================
-- TRIGGERS
-- =====================================================

DELIMITER //

-- Trigger to update user balance after approved deposit
CREATE TRIGGER `tr_after_deposit_approved`
AFTER UPDATE ON `deposits`
FOR EACH ROW
BEGIN
    IF NEW.status = 'approved' AND OLD.status != 'approved' THEN
        -- Log to cron_logs for auditing
        INSERT INTO cron_logs (execution_time, duration_seconds, status, actions_performed)
        VALUES (NOW(), 0, 'success', JSON_OBJECT('action', 'deposit_approved', 'deposit_id', NEW.id, 'user_id', NEW.user_id, 'amount', NEW.amount));
    END IF;
END//

DELIMITER ;

-- =====================================================
-- GRANTS (Optional - for separate database user)
-- =====================================================

-- CREATE USER 'wakatraffic_user'@'localhost' IDENTIFIED BY 'your_secure_password';
-- GRANT SELECT, INSERT, UPDATE, DELETE ON wakatraffic.* TO 'wakatraffic_user'@'localhost';
-- FLUSH PRIVILEGES;

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