<?php

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use App\Models\Audio_Quiz_Leaderboard;
use App\Models\Audio_Quiz_Question;
use App\Models\Avatar;
use App\Models\Badge;
use App\Models\Battles_Question;
use App\Models\Category;
use App\Models\Classification;
use App\Models\Coin_Share_List;
use App\Models\Common;
use App\Models\Contest;
use App\Models\Contest_Leaderboard;
use App\Models\Contest_Question;
use App\Models\Contest_Transaction;
use App\Models\Daily_Quiz_Leaderboard;
use App\Models\Daily_Quiz_Question;
use App\Models\Exam;
use App\Models\Exam_Leaderboard;
use App\Models\Exam_Question;
use App\Models\Fear_Factor_Quiz_Leaderboard;
use App\Models\Fear_Factor_Quiz_Question;
use App\Models\Group_Battles;
use App\Models\Group_Battles_Leaderboard;
use App\Models\Level;
use App\Models\Normal_Quiz_Leaderboard;
use App\Models\Normal_Quiz_Question;
use App\Models\Notification;
use App\Models\Onboarding_Screen;
use App\Models\One_vs_One_Battles;
use App\Models\Package;
use App\Models\Page;
use App\Models\Pratice_Quiz_Leaderboard;
use App\Models\Pratice_Quiz_Question;
use App\Models\Product;
use App\Models\Product_Transaction;
use App\Models\Read_Notification;
use App\Models\Refer_Earn;
use App\Models\Reward_Transaction;
use App\Models\Skill;
use App\Models\Social_Link;
use App\Models\Transaction;
use App\Models\True_False_Quiz_Leaderboard;
use App\Models\True_False_Quiz_Question;
use App\Models\Ultimate_Challenge;
use App\Models\Ultimate_Challenge_Leaderboard;
use App\Models\Ultimate_Challenge_Question;
use App\Models\User;
use App\Models\User_Badge_List;
use App\Models\Video_Quiz_Leaderboard;
use App\Models\Video_Quiz_Question;
use App\Models\Withdrawal_Request;
use Illuminate\Support\Facades\DB;
use Exception;
use Illuminate\Support\Facades\Artisan;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Facades\File;

class SystemSettingController extends Controller
{
    public $common;
    public function __construct()
    {
        $this->common = new Common;
    }

    public function index()
    {
        try {

            $params['data'] = [];
            return view('admin.system_setting.index', $params);
        } catch (Exception $e) {
            return response()->json(['status' => 400, 'errors' => $e->getMessage()]);
        }
    }
    public function ClearData()
    {
        try {

            // Avatar
            $avatar_file = Storage::allFiles('public/avatar');
            foreach ($avatar_file as $file) {

                $file_name = pathinfo($file, PATHINFO_BASENAME);
                if (!Avatar::where('image', $file_name)->exists()) {
                    $this->common->deleteImageToFolder('avatar', $file_name);
                }
            }

            // Badge
            $badge_file = Storage::allFiles('public/badge');
            foreach ($badge_file as $file) {

                $file_name = pathinfo($file, PATHINFO_BASENAME);
                if (!Badge::where('image', $file_name)->exists()) {
                    $this->common->deleteImageToFolder('badge', $file_name);
                }
            }

            // Category
            $category_file = Storage::allFiles('public/category');
            foreach ($category_file as $file) {

                $file_name = pathinfo($file, PATHINFO_BASENAME);
                if (!Category::where('image', $file_name)->exists()) {
                    $this->common->deleteImageToFolder('category', $file_name);
                }
            }

            // Classification
            $classification_file = Storage::allFiles('public/classification');
            foreach ($classification_file as $file) {

                $file_name = pathinfo($file, PATHINFO_BASENAME);
                if (!Classification::where('image', $file_name)->exists()) {
                    $this->common->deleteImageToFolder('classification', $file_name);
                }
            }

            // Contest
            $contest_file = Storage::allFiles('public/contest');
            foreach ($contest_file as $file) {

                $file_name = pathinfo($file, PATHINFO_BASENAME);
                if (!Contest::where('image', $file_name)->exists() && !Contest_Question::where('image', $file_name)->exists()) {
                    $this->common->deleteImageToFolder('contest', $file_name);
                }
            }

            // Database
            $local_database_file = Storage::allFiles('public/database');
            $database_name = array_map(fn($file) => pathinfo($file, PATHINFO_BASENAME), $local_database_file);
            foreach ($database_name as $value) {
                $this->common->deleteImageToFolder('database', $value, 1);
            }

            // Exam
            $exam_file = Storage::allFiles('public/exam');
            foreach ($exam_file as $file) {

                $file_name = pathinfo($file, PATHINFO_BASENAME);
                if (!Exam::where('image', $file_name)->exists() && !Exam_Question::where('image', $file_name)->exists()) {
                    $this->common->deleteImageToFolder('exam', $file_name);
                }
            }

            // Level
            $level_file = Storage::allFiles('public/level');
            foreach ($level_file as $file) {

                $file_name = pathinfo($file, PATHINFO_BASENAME);
                if (!Level::where('image', $file_name)->exists()) {
                    $this->common->deleteImageToFolder('level', $file_name);
                }
            }

            // Notification
            $notification_file = Storage::allFiles('public/notification');
            foreach ($notification_file as $file) {

                $file_name = pathinfo($file, PATHINFO_BASENAME);
                if (!Notification::where('image', $file_name)->exists()) {
                    $this->common->deleteImageToFolder('notification', $file_name);
                }
            }

            // Package
            $package_file = Storage::allFiles('public/package');
            foreach ($package_file as $file) {

                $file_name = pathinfo($file, PATHINFO_BASENAME);
                if (!Package::where('image', $file_name)->exists()) {
                    $this->common->deleteImageToFolder('package', $file_name);
                }
            }

            // Product
            $product_file = Storage::allFiles('public/product');
            foreach ($product_file as $file) {

                $file_name = pathinfo($file, PATHINFO_BASENAME);
                if (!Product::where('image', $file_name)->exists()) {
                    $this->common->deleteImageToFolder('product', $file_name);
                }
            }

            // Question
            $question_file = Storage::allFiles('public/question');
            foreach ($question_file as $file) {

                $file_name = pathinfo($file, PATHINFO_BASENAME);

                $practice = Pratice_Quiz_Question::where('image', $file_name)->exists();
                $normal = Normal_Quiz_Question::where('image', $file_name)->exists();
                $battle = Battles_Question::where('image', $file_name)->exists();
                $daily = Daily_Quiz_Question::where('image', $file_name)->exists();
                $fear_fector = Fear_Factor_Quiz_Question::where('image', $file_name)->exists();
                $true_false = True_False_Quiz_Question::where('image', $file_name)->exists();
                $audio = Audio_Quiz_Question::where(function ($q) use ($file_name) {
                    $q->where('image', $file_name)->orWhere('audio', $file_name);
                })->exists();
                $video = Video_Quiz_Question::where(function ($q) use ($file_name) {
                    $q->where('image', $file_name)->orWhere('video', $file_name);
                })->exists();

                if (!$practice && !$normal && !$battle && !$daily && !$fear_fector && !$true_false && !$audio && !$video) {
                    $this->common->deleteImageToFolder('question', $file_name);
                }
            }

            // Setting
            $setting_file = Storage::allFiles('public/setting');
            foreach ($setting_file as $file) {

                $file_name = pathinfo($file, PATHINFO_BASENAME);

                $setting = Setting_Data();
                $page = Page::where('icon', $file_name)->exists();
                $social_link = Social_Link::where('image', $file_name)->exists();
                $onboarding_screen = Onboarding_Screen::where('image', $file_name)->exists();

                if (!$page && !$social_link && !$onboarding_screen && $setting['app_logo'] != $file_name && $setting['panel_login_page_bg_image'] != $file_name && $setting['panel_login_page_image'] != $file_name && $setting['company_logo'] != $file_name) {
                    $this->common->deleteImageToFolder('setting', $file_name);
                }
            }

            // Skill
            $skill_file = Storage::allFiles('public/skill');
            foreach ($skill_file as $file) {

                $file_name = pathinfo($file, PATHINFO_BASENAME);
                if (!Skill::where('image', $file_name)->exists()) {
                    $this->common->deleteImageToFolder('skill', $file_name);
                }
            }

            // Ultimate Challenge
            $ultimate_challenge_file = Storage::allFiles('public/ultimate_challenge');
            foreach ($ultimate_challenge_file as $file) {

                $file_name = pathinfo($file, PATHINFO_BASENAME);
                if (!Ultimate_Challenge::where('image', $file_name)->exists() && !Ultimate_Challenge_Question::where('image', $file_name)->exists()) {
                    $this->common->deleteImageToFolder('ultimate_challenge', $file_name);
                }
            }

            // User
            $user_file = Storage::allFiles('public/user');
            foreach ($user_file as $file) {

                $file_name = pathinfo($file, PATHINFO_BASENAME);

                $user = User::where(function ($q) use ($file_name) {
                    $q->where('image', $file_name)->orWhere('qr_code', $file_name);
                })->exists();

                if (!$user) {
                    $this->common->deleteImageToFolder('user', $file_name);
                }
            }

            return response()->json(['status' => 200, 'success' => __('label.data_clear_successfully')]);
        } catch (Exception $e) {
            return response()->json(['status' => 400, 'errors' => $e->getMessage()]);
        }
    }
    public function DownloadSqlFile()
    {
        try {

            Artisan::call('config:clear');

            $storageAt = storage_path() . "/app/public/database";
            if (!file_exists($storageAt)) {
                File::makeDirectory($storageAt, 0755, true, true);
            }

            $mysqlHostName = env('DB_HOST');
            $mysqlUserName = env('DB_USERNAME');
            $mysqlPassword = env('DB_PASSWORD');
            $DbName = env('DB_DATABASE');

            // get all table name
            $result = DB::select("SHOW TABLES");
            $prep = "Tables_in_$DbName";

            foreach ($result as $res) {
                $tables[] =  $res->$prep;
            }

            $connect = new \PDO("mysql:host=$mysqlHostName;dbname=$DbName;charset=utf8", "$mysqlUserName", "$mysqlPassword", array(\PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));
            $statement = $connect->prepare("SHOW TABLES");
            $statement->execute();
            $result = $statement->fetchAll();

            $output = '';
            foreach ($tables as $table) {

                $show_table_query = "SHOW CREATE TABLE " . $table . "";
                $statement = $connect->prepare($show_table_query);
                $statement->execute();
                $show_table_result = $statement->fetchAll();

                foreach ($show_table_result as $show_table_row) {
                    $output .= "\n\n" . $show_table_row["Create Table"] . ";\n\n";
                }
                $select_query = "SELECT * FROM " . $table . "";
                $statement = $connect->prepare($select_query);
                $statement->execute();
                $total_row = $statement->rowCount();

                for ($count = 0; $count < $total_row; $count++) {
                    $single_result = $statement->fetch(\PDO::FETCH_ASSOC);
                    $table_column_array = array_keys($single_result);
                    $table_value_array = array_values($single_result);
                    $output .= "\nINSERT INTO $table (";
                    $output .= "`" . implode("`, `", $table_column_array) . "`) VALUES (";
                    $output .= "'" . implode("', '", $table_value_array) . "');\n";
                }
            }

            $file_name = App_Name() . '_db_' . date('d_m_Y') . '.sql';
            $file_handle = fopen(storage_path() . '/app/public/database/' . $file_name, 'w+');
            fwrite($file_handle, $output);
            fclose($file_handle);
            header('Content-Description: File Transfer');
            header('Content-Type: application/octet-stream');
            header('Content-Disposition: attachment; filename=' . basename($file_name));
            header('Content-Transfer-Encoding: binary');
            header('Expires: 0');
            header('Cache-Control: must-revalidate');
            header('Pragma: public');
            header('Content-Length: ' . filesize(storage_path() . '/app/public/database/' . $file_name));
            ob_clean();
            flush();
            readfile(storage_path() . '/app/public/database/' . $file_name);
        } catch (Exception $e) {
            return response()->json(['status' => 400, 'errors' => $e->getMessage()]);
        }
    }
    public function CleanDatabase()
    {
        try {

            Audio_Quiz_Leaderboard::query()->truncate();
            Audio_Quiz_Question::query()->truncate();
            Avatar::query()->truncate();
            Battles_Question::query()->truncate();
            Category::query()->truncate();
            Classification::query()->truncate();
            Coin_Share_List::query()->truncate();
            Contest::query()->truncate();
            Contest_Leaderboard::query()->truncate();
            Contest_Question::query()->truncate();
            Contest_Transaction::query()->truncate();
            Daily_Quiz_Leaderboard::query()->truncate();
            Daily_Quiz_Question::query()->truncate();
            Exam::query()->truncate();
            Exam_Leaderboard::query()->truncate();
            Exam_Question::query()->truncate();
            Fear_Factor_Quiz_Leaderboard::query()->truncate();
            Fear_Factor_Quiz_Question::query()->truncate();
            Group_Battles::query()->truncate();
            Group_Battles_Leaderboard::query()->truncate();
            Level::query()->truncate();
            Normal_Quiz_Leaderboard::query()->truncate();
            Normal_Quiz_Question::query()->truncate();
            Notification::query()->truncate();
            Onboarding_Screen::query()->truncate();
            One_vs_One_Battles::query()->truncate();
            Package::query()->truncate();
            Pratice_Quiz_Leaderboard::query()->truncate();
            Pratice_Quiz_Question::query()->truncate();
            Product::query()->truncate();
            Product_Transaction::query()->truncate();
            Read_Notification::query()->truncate();
            Refer_Earn::query()->truncate();
            Reward_Transaction::query()->truncate();
            Skill::query()->truncate();
            Social_Link::query()->truncate();
            Transaction::query()->truncate();
            True_False_Quiz_Leaderboard::query()->truncate();
            True_False_Quiz_Question::query()->truncate();
            Ultimate_Challenge::query()->truncate();
            Ultimate_Challenge_Leaderboard::query()->truncate();
            Ultimate_Challenge_Question::query()->truncate();
            User::query()->truncate();
            User_Badge_List::query()->truncate();
            Video_Quiz_Leaderboard::query()->truncate();
            Video_Quiz_Question::query()->truncate();
            Withdrawal_Request::query()->truncate();

            return response()->json(['status' => 200, 'success' => __('label.data_clean_successfully')]);
        } catch (Exception $e) {
            return response()->json(['status' => 400, 'errors' => $e->getMessage()]);
        }
    }
}
