<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Facades\Storage;

class CleanDatabaseForProduction extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'db:clean-for-production 
                            {--keep-users : Keep all users (default: keeps only admin/staff)}
                            {--keep-addresses : Keep customer addresses}
                            {--keep-coupons : Keep coupons and redemptions}
                            {--keep-images : Keep product images and media files}
                            {--force : Force execution without confirmation}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Clean database by removing products, orders, and related test data for production deployment';

    /**
     * Execute the console command.
     */
    public function handle()
    {
        if (!$this->option('force')) {
            $this->warn('⚠️  WARNING: This will delete all products, orders, carts, wishlists, and related data!');
            if (!$this->option('keep-images')) {
                $this->warn('⚠️  This will also DELETE all product images and media files from storage!');
            }
            $this->warn('⚠️  This action cannot be undone!');
            
            if (!$this->confirm('Are you sure you want to proceed?', false)) {
                $this->info('Operation cancelled.');
                return 0;
            }
        }

        $this->info('🧹 Starting database cleanup...');
        $this->newLine();

        // Disable foreign key checks temporarily
        DB::statement('SET FOREIGN_KEY_CHECKS=0;');

        try {
            // 1. Delete Orders and Related Data
            $this->info('📦 Cleaning orders and order items...');
            $this->cleanOrders();
            
            // 2. Delete Products and Related Data
            $this->info('🛍️  Cleaning products and related data...');
            if (!$this->option('keep-images')) {
                $this->info('🖼️  Deleting product images and media files...');
                $this->deleteProductImages();
            }
            $this->cleanProducts();
            
            // 3. Delete Carts and Cart Items
            $this->info('🛒 Cleaning carts and cart items...');
            $this->cleanCarts();
            
            // 4. Delete Wishlists
            $this->info('❤️  Cleaning wishlists...');
            $this->cleanWishlists();
            
            // 5. Delete Product Reviews
            $this->info('⭐ Cleaning product reviews...');
            $this->cleanReviews();
            
            // 6. Delete Stock Movements
            $this->info('📊 Cleaning stock movements...');
            $this->cleanStockMovements();
            
            // 7. Delete Stock Reservations
            $this->info('🔒 Cleaning stock reservations...');
            $this->cleanStockReservations();
            
            // 8. Delete OTP Codes (old ones)
            $this->info('🔐 Cleaning old OTP codes...');
            $this->cleanOtpCodes();
            
            // 9. Delete Coupon Redemptions (if not keeping)
            if (!$this->option('keep-coupons')) {
                $this->info('🎫 Cleaning coupon redemptions...');
                $this->cleanCouponRedemptions();
            }
            
            // 10. Delete Customer Users (if not keeping)
            if (!$this->option('keep-users')) {
                $this->info('👥 Cleaning customer users...');
                $this->cleanCustomerUsers();
            }
            
            // 11. Delete Customer Addresses (if not keeping)
            if (!$this->option('keep-addresses')) {
                $this->info('📍 Cleaning customer addresses...');
                $this->cleanAddresses();
            }
            
            // 12. Clear Cache
            $this->info('🗑️  Clearing application cache...');
            $this->call('cache:clear');
            $this->call('config:clear');
            $this->call('view:clear');
            
        } catch (\Exception $e) {
            $this->error('❌ Error during cleanup: ' . $e->getMessage());
            DB::statement('SET FOREIGN_KEY_CHECKS=1;');
            return 1;
        }

        // Re-enable foreign key checks
        DB::statement('SET FOREIGN_KEY_CHECKS=1;');

        $this->newLine();
        $this->info('✅ Database cleanup completed successfully!');
        $this->info('📝 Summary:');
        $this->line('   - All orders and order items deleted');
        $this->line('   - All products and variants deleted');
        $this->line('   - All carts and cart items deleted');
        $this->line('   - All wishlists deleted');
        $this->line('   - All product reviews deleted');
        $this->line('   - All stock movements deleted');
        $this->line('   - Cache cleared');
        
        if (!$this->option('keep-images')) {
            $this->line('   - Product images and media files deleted');
        }
        
        if (!$this->option('keep-users')) {
            $this->line('   - Customer users deleted (admin/staff kept)');
        }
        
        if (!$this->option('keep-addresses')) {
            $this->line('   - Customer addresses deleted');
        }
        
        if (!$this->option('keep-coupons')) {
            $this->line('   - Coupon redemptions deleted');
        }

        return 0;
    }

    /**
     * Clean orders and order items
     */
    protected function cleanOrders()
    {
        // Delete order items first (foreign key constraint)
        if (Schema::hasTable('order_items')) {
            DB::table('order_items')->truncate();
        }
        
        // Delete shipments if table exists
        if (Schema::hasTable('shipments')) {
            DB::table('shipments')->truncate();
        }
        
        // Delete orders
        if (Schema::hasTable('orders')) {
            DB::table('orders')->truncate();
        }
    }

    /**
     * Delete product images and media files from storage
     */
    protected function deleteProductImages()
    {
        try {
            $disk = Storage::disk('public');
            $deletedCount = 0;
            
            // Get all product images from database before deletion
            $productImages = [];
            $mediaFiles = [];
            
            if (Schema::hasTable('products')) {
                $productImages = DB::table('products')
                    ->whereNotNull('image_path')
                    ->pluck('image_path')
                    ->toArray();
            }
            
            if (Schema::hasTable('product_media')) {
                $mediaFiles = DB::table('product_media')
                    ->whereNotNull('file_path')
                    ->pluck('file_path')
                    ->toArray();
            }
            
            // Delete product main images
            foreach ($productImages as $imagePath) {
                if ($imagePath && $disk->exists($imagePath)) {
                    $disk->delete($imagePath);
                    $deletedCount++;
                }
            }
            
            // Delete product media files (additional images and videos)
            foreach ($mediaFiles as $filePath) {
                if ($filePath && $disk->exists($filePath)) {
                    $disk->delete($filePath);
                    $deletedCount++;
                }
            }
            
            // Also delete any remaining files in products directory (cleanup orphaned files)
            if ($disk->exists('products')) {
                // Delete all files in products directory (excluding .gitkeep if exists)
                $allFiles = $disk->allFiles('products');
                foreach ($allFiles as $file) {
                    if (basename($file) !== '.gitkeep') {
                        $disk->delete($file);
                        $deletedCount++;
                    }
                }
                
                // Delete empty media subdirectories
                if ($disk->exists('products/media')) {
                    $mediaDirs = $disk->directories('products/media');
                    foreach ($mediaDirs as $dir) {
                        if (empty($disk->files($dir))) {
                            $disk->deleteDirectory($dir);
                        }
                    }
                }
            }
            
            $this->line("   ✓ Deleted {$deletedCount} product image/media files from storage");
        } catch (\Exception $e) {
            $this->warn('   ⚠ Could not delete some image files: ' . $e->getMessage());
        }
    }

    /**
     * Clean products and related data
     */
    protected function cleanProducts()
    {
        // Delete product variant attribute values
        if (Schema::hasTable('product_variant_attribute_values')) {
            DB::table('product_variant_attribute_values')->truncate();
        }
        
        // Delete variant stock movements
        if (Schema::hasTable('variant_stock_movements')) {
            DB::table('variant_stock_movements')->truncate();
        }
        
        // Delete product variants
        if (Schema::hasTable('product_variants')) {
            DB::table('product_variants')->truncate();
        }
        
        // Delete product media
        if (Schema::hasTable('product_media')) {
            DB::table('product_media')->truncate();
        }
        
        // Delete product stock movements
        if (Schema::hasTable('product_stock_movements')) {
            DB::table('product_stock_movements')->truncate();
        }
        
        // Delete products (this will cascade to variants if foreign keys are set)
        if (Schema::hasTable('products')) {
            DB::table('products')->truncate();
        }
    }

    /**
     * Clean carts and cart items
     */
    protected function cleanCarts()
    {
        // Delete cart items first
        if (Schema::hasTable('cart_items')) {
            DB::table('cart_items')->truncate();
        }
        
        // Delete carts
        if (Schema::hasTable('carts')) {
            DB::table('carts')->truncate();
        }
    }

    /**
     * Clean wishlists
     */
    protected function cleanWishlists()
    {
        if (Schema::hasTable('wishlists')) {
            DB::table('wishlists')->truncate();
        }
    }

    /**
     * Clean product reviews
     */
    protected function cleanReviews()
    {
        if (Schema::hasTable('product_reviews')) {
            DB::table('product_reviews')->truncate();
        }
        
        // Reset product rating fields
        if (Schema::hasTable('products')) {
            DB::table('products')->update([
                'avg_rating' => 0,
                'rating_count' => 0,
            ]);
        }
    }

    /**
     * Clean stock movements
     */
    protected function cleanStockMovements()
    {
        if (Schema::hasTable('product_stock_movements')) {
            DB::table('product_stock_movements')->truncate();
        }
        
        if (Schema::hasTable('variant_stock_movements')) {
            DB::table('variant_stock_movements')->truncate();
        }
    }

    /**
     * Clean stock reservations
     */
    protected function cleanStockReservations()
    {
        if (Schema::hasTable('stock_reservations')) {
            DB::table('stock_reservations')->truncate();
        }
    }

    /**
     * Clean OTP codes (older than 1 hour)
     */
    protected function cleanOtpCodes()
    {
        if (Schema::hasTable('otp_codes')) {
            DB::table('otp_codes')
                ->where('created_at', '<', now()->subHour())
                ->delete();
        }
    }

    /**
     * Clean coupon redemptions
     */
    protected function cleanCouponRedemptions()
    {
        if (Schema::hasTable('coupon_redemptions')) {
            DB::table('coupon_redemptions')->truncate();
        }
    }

    /**
     * Clean customer users (keep admin and staff)
     */
    protected function cleanCustomerUsers()
    {
        if (Schema::hasTable('users')) {
            // Delete users with customer type, but keep admin and staff
            DB::table('users')
                ->where('user_type', 'customer')
                ->orWhereNull('user_type')
                ->delete();
        }
    }

    /**
     * Clean customer addresses
     */
    protected function cleanAddresses()
    {
        if (Schema::hasTable('addresses')) {
            DB::table('addresses')->truncate();
        }
    }
}