<?php

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use App\Models\Comment;
use App\Models\Common;
use App\Models\Company;
use App\Models\Complaint;
use App\Models\Complaint_Img;
use App\Models\Department;
use App\Models\Department_Working_Time;
use App\Models\Hashtag;
use App\Models\In_Charge;
use App\Models\Like;
use App\Models\Notification;
use App\Models\Onboarding_Screen;
use App\Models\Page;
use App\Models\Read_Notification;
use App\Models\Service;
use App\Models\Social_Link;
use App\Models\Story;
use App\Models\Sub_In_Charge;
use App\Models\User;
use App\Models\User_Address;
use App\Models\Vacancy;
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 {

            // Folder Name
            $company = 'public/company';
            $complaint = 'public/complaint';
            $department = 'public/department';
            $in_charge = 'public/in_charge';
            $notification = 'public/notification';
            $page = 'public/page';
            $service = 'public/service';
            $setting = 'public/setting';
            $story = 'public/story';
            $sub_in_charge = 'public/sub_in_charge';
            $user = 'public/user';

            // Name Array
            $company_name = [];
            $complaint_name = [];
            $department_name = [];
            $in_charge_name = [];
            $notification_name = [];
            $page_name = [];
            $service_name = [];
            $setting_name = [];
            $story_name = [];
            $sub_in_charge_name = [];
            $user_name = [];

            // Get Files
            $company_file = Storage::allFiles($company);
            $complaint_file = Storage::allFiles($complaint);
            $department_file = Storage::allFiles($department);
            $in_charge_file = Storage::allFiles($in_charge);
            $notification_file = Storage::allFiles($notification);
            $page_file = Storage::allFiles($page);
            $service_file = Storage::allFiles($service);
            $setting_file = Storage::allFiles($setting);
            $story_file = Storage::allFiles($story);
            $sub_in_charge_file = Storage::allFiles($sub_in_charge);
            $user_file = Storage::allFiles($user);

            // Add Name In Array
            foreach ($company_file as $file_name) {
                array_push($company_name, pathinfo($file_name)['basename']);
            }
            foreach ($complaint_file as $file_name) {
                array_push($complaint_name, pathinfo($file_name)['basename']);
            }
            foreach ($department_file as $file_name) {
                array_push($department_name, pathinfo($file_name)['basename']);
            }
            foreach ($in_charge_file as $file_name) {
                array_push($in_charge_name, pathinfo($file_name)['basename']);
            }
            foreach ($notification_file as $file_name) {
                array_push($notification_name, pathinfo($file_name)['basename']);
            }
            foreach ($page_file as $file_name) {
                array_push($page_name, pathinfo($file_name)['basename']);
            }
            foreach ($service_file as $file_name) {
                array_push($service_name, pathinfo($file_name)['basename']);
            }
            foreach ($setting_file as $file_name) {
                array_push($setting_name, pathinfo($file_name)['basename']);
            }
            foreach ($story_file as $file_name) {
                array_push($story_name, pathinfo($file_name)['basename']);
            }
            foreach ($sub_in_charge_file as $file_name) {
                array_push($sub_in_charge_name, pathinfo($file_name)['basename']);
            }
            foreach ($user_file as $file_name) {
                array_push($user_name, pathinfo($file_name)['basename']);
            }
            

            // Delete File In Folder
            foreach ($company_name as $key => $value) {

                $company_file_check = Company::select('id')->where('logo', $value)->first();
                if ($company_file_check == null) {
                    $this->common->deleteImageToFolder('company', $value);
                }
            }
            foreach ($complaint_name as $key => $value) {

                $complaint_file_check = Complaint_Img::select('id')->where('content_url', $value)->first();
                if ($complaint_file_check == null) {
                    $this->common->deleteImageToFolder('complaint', $value);
                }
            }
            foreach ($department_name as $key => $value) {

                $department_file_check = Department::select('id')->where('department_icon', $value)->first();
                if ($department_file_check == null) {
                    $this->common->deleteImageToFolder('department', $value);
                }
            }
            foreach ($in_charge_name as $key => $value) {

                $in_charge_file_check = In_Charge::select('id')->where('image', $value)->first();
                if ($in_charge_file_check == null) {
                    $this->common->deleteImageToFolder('in_charge', $value);
                }
            }
            foreach ($notification_name as $key => $value) {

                $notification_file_check = Notification::select('id')->where('image', $value)->first();
                if ($notification_file_check == null) {
                    $this->common->deleteImageToFolder('notification', $value);
                }
            }
            foreach ($page_name as $key => $value) {

                $page_file_check = Page::select('id')->where('icon', $value)->first();
                if ($page_file_check == null) {
                    $this->common->deleteImageToFolder('page', $value);
                }
            }
            foreach ($service_name as $key => $value) {

                $service_file_check = Service::select('id')->where('image', $value)->first();
                if ($service_file_check == null) {
                    $this->common->deleteImageToFolder('service', $value);
                }
            }
            foreach ($setting_name as $key => $value) {

                $social_file_check = Social_Link::select('id')->where('image', $value)->first();
                $onboarding_file_check = Onboarding_Screen::select('id')->where('image', $value)->first();

                $settingData = Setting_Data();
                $app_file_check = 'yes';
                if ($settingData['app_logo'] != $value) {
                    $app_file_check = 'no';
                }

                if ($social_file_check == null && $onboarding_file_check == null && $app_file_check == 'no') {
                    $this->common->deleteImageToFolder('setting', $value);
                }
            }
            foreach ($story_name as $key => $value) {

                $story_file_check = Story::select('id')->where('story_url', $value)->first();
                if ($story_file_check == null) {
                    $this->common->deleteImageToFolder('story', $value);
                }
            }
            foreach ($sub_in_charge_name as $key => $value) {

                $sub_in_charge_file_check = Sub_In_Charge::select('id')->where('image', $value)->first();
                if ($sub_in_charge_file_check == null) {
                    $this->common->deleteImageToFolder('sub_in_charge', $value);
                }
            }
            foreach ($user_name as $key => $value) {

                $user_file_check = User::select('id')->where('image', $value)->first();
                if ($user_file_check == null) {
                    $this->common->deleteImageToFolder('user', $value);
                }
            }

            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", [\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 {

            Comment::query()->truncate();
            Company::query()->truncate();
            Complaint::query()->truncate();
            Complaint_Img::query()->truncate();
            Department::query()->truncate();
            Department_Working_Time::query()->truncate();
            Hashtag::query()->truncate();
            In_Charge::query()->truncate();
            Like::query()->truncate();
            Notification::query()->truncate();
            Onboarding_Screen::query()->truncate();
            Read_Notification::query()->truncate();
            Service::query()->truncate();
            Social_Link::query()->truncate();
            Story::query()->truncate();
            Sub_In_Charge::query()->truncate();
            User::query()->truncate();
            User_Address::query()->truncate();
            Vacancy::query()->truncate();

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