<?php

namespace App\Http\Controllers\Client;

use App\Http\Controllers\Controller;
use App\Models\Color;
use App\Models\Company;
use App\Models\Currency;
use App\Models\EmailSettings;
use App\Models\ExtraSettings;
use App\Models\Fiscal;
use App\Models\PreSettings;
use App\Models\Screen;
use App\Models\Size;
use App\Models\TimeZone;
use Illuminate\Support\Facades\Validator;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\File;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Str;
use Illuminate\Support\Facades\Schema;


class SettingsController extends Controller
{
    function __construct()
    {
        $this->middleware('permission:الاعدادات العامة للنظام');
    }

    public function screens_settings(){
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $screen_settings = Screen::where('company_id',$company_id)->first();
        return view('client.settings.screens', compact('company','screen_settings'));
    }
    public function screens_settings_post(Request $request){
        $data = $request->all();
        $company_id = Auth::user()->company_id;
        $screen_settings = Screen::where('company_id',$company_id)->first();
        $screen_settings->update($data);

        return response()->json(['message' => 'تم تحديث الإعدادات بنجاح','status'=>true]);
    }
    public function basic()
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        return view('client.settings.basic', compact('company'));
    }
    public function update_basic(Request $request)
    {
        $company = Company::FindOrFail($request->company_id);
        $company->update($request->all());
        $company->basic_settings->update($request->all());
        if ($request->hasFile('company_logo')) {
            $company_logo = $request->file('company_logo');
            $fileName = $company_logo->getClientOriginalName();
            $uploadDir = 'uploads/companies/logos/' . $company->id;
            $company_logo->move(public_path($uploadDir), $fileName);
            $company->company_logo = $uploadDir . '/' . $fileName;
            $company->save();
        }
        if ($request->hasFile('header')) {
            $header = $request->file('header');
            $fileName = $header->getClientOriginalName();
            $uploadDir = 'uploads/companies/headers/' . $company->id;
            $header->move(public_path($uploadDir), $fileName);
            $company->basic_settings->header = $uploadDir . '/' . $fileName;
            $company->basic_settings->save();
        }

        if ($request->hasFile('footer')) {
            $footer = $request->file('footer');
            $fileName = $footer->getClientOriginalName();
            $uploadDir = 'uploads/companies/footers/' . $company->id;
            $footer->move(public_path($uploadDir), $fileName);
            $company->basic_settings->footer = $uploadDir . '/' . $fileName;
            $company->basic_settings->save();
        }

        if ($request->hasFile('electronic_stamp')) {
            $electronic_stamp = $request->file('electronic_stamp');
            $fileName = $electronic_stamp->getClientOriginalName();
            $uploadDir = 'uploads/companies/electronic_stamps/' . $company->id;
            $electronic_stamp->move(public_path($uploadDir), $fileName);
            $company->basic_settings->electronic_stamp = $uploadDir . '/' . $fileName;
            $company->basic_settings->save();
        }

        return response()->json(['message' => 'تم تحديث الإعدادات بنجاح','status'=>true]);

    }
    public function billing()
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $fiscal = Fiscal::where('company_id', $company_id)->first();
        return view('client.settings.billing', compact('company','fiscal'));
    }
    public function update_billing(Request $request)
    {
        $this->validate($request, [
            'tax_number' => 'required',
            'commercial_registration_number' => 'required',
            'tax_value_added' => 'required',

        ]);
        $company = Company::FindOrFail($request->company_id);
        $company->update($request->all());

        return response()->json(['status'=>true,'message'=>'تم تحديث الإعدادات بنجاح']);

    }
    public function update_fiscal(Request $request)
    {
        $company = Company::FindOrFail($request->company_id);
        $company->fiscal->update($request->all());

        return response()->json(['status'=>true,'message'=>'تم تحديث الإعدادات بنجاح']);
    }
    public function email()
    {
        $company_id = Auth::user()->company_id;
        $email = EmailSettings::where('company_id', $company_id)->first();
        $company = Company::FindOrFail($company_id);
        return view('client.settings.email', compact('email', 'company'));
    }
    public function update_email(Request $request)
    {
        $this->validate($request, [
            'mail_user' => 'required|email',
            'mail_pass' => 'required',
            'mail_host' => 'required',
            'mail_port' => 'required',
            'sender_name' => 'required',
        ]);
        $company = Company::FindOrFail($request->company_id);
        $company->email_settings->update($request->all());

        return response()->json(['status'=>true,'message'=>'تم تحديث الإعدادات بنجاح']);
    }
    public function pre()
    {
        $company_id = Auth::user()->company_id;
        $pre = PreSettings::where('company_id', $company_id)->first();
        $company = Company::FindOrFail($company_id);
        return view('client.settings.pre', compact('pre', 'company'));
    }
    public function update_pre(Request $request)
    {
        $company = Company::FindOrFail($request->company_id);
        $company->pre_settings->update($request->all());
        return redirect()->route('client.pre.settings.edit')->with('success', 'تم تحديث بادئات الفواتير والسندات بنجاح');
    }
    public function extra()
    {
        $company_id = Auth::user()->company_id;
        $extra = ExtraSettings::where('company_id', $company_id)->first();
        $company = Company::FindOrFail($company_id);
        $timezones = TimeZone::all();
        $currencies = Currency::all();
        $sizes = Size::where('company_id', $company_id)->get();
        $colors = Color::where('company_id', $company_id)->get();
        return view('client.settings.extra', compact('extra', 'company', 'colors', 'sizes', 'timezones', 'currencies'));
    }
    public function update_extra(Request $request)
    {
        $company = Company::FindOrFail($request->company_id);
        $company->extra_settings->update($request->all());
        $company->update($request->all());

        return response()->json(['message' => 'تم تحديث الإعدادات بنجاح','status'=>true]);
    }
    public function update_extra_2(Request $request)
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        
        $sizes= Size::where('company_id',$company_id)->get();
        foreach($sizes as $size){
            $size->delete();
        }
    
        $colors= Color::where('company_id',$company_id)->get();
        foreach($colors as $color){
            $color->delete();
        }
        $sizes = $request->sizes;
        $colors = $request->colors;
        foreach ($sizes as $size) {
            Size::create([
                'company_id' => $request->company_id ,
                'size' => $size
            ]);
        }
        foreach ($colors as $color) {
            Color::create([
                'company_id' => $request->company_id ,
                'color' => $color
            ]);
        }
        return redirect()->route('client.extra.settings.edit')->with('success', 'تم تحديث البيانات الاضافية بنجاح');
    }


    public function backup()
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        return view('client.settings.backup', compact('company'));
    }

    public function get_backup()
    {

        
        try {
            $company = Auth::user()->company;
        
            if (!$company) {
                // Handle case when company is not found
            }
        
            // $tables = DB::select('SHOW TABLES');

            // get all table names where it have column name company_id

            // $tables = array_map('current', $tables);

            // $tables = array_filter($tables, function ($table) {
            //     return DB::getSchemaBuilder()->hasColumn($table, 'company_id');
            // });

            // $tables = array_diff($tables, ['roles','subscriptions','payments']);

            // return $tables;

            $tables = array(
                "branches",
                "clients",
                "safes",
                "banks",
                "units",
                "sizes",
                "colors",
                "stores",
                "categories",
                "products",
                "outer_clients",
                "suppliers",
                "employees",
                "screens",
                "email_settings",
                "fixed_expenses",
                "buy_cash",
                "cash",
                "bank_buy_cash",
                "bank_cash",
                "expenses",
                "extra_settings",
                "fiscals",
                "gifts",
                "outer_client_address",
                "outer_client_note",
                "outer_client_phone",
                "pre_settings",
                "product_unit",
                "purchase_orders",
                "purchase_orders_elements",
                "purchase_orders_extra",
                "quotations",
                "quotations_elements",
                "quotations_extra",
                "safe_bank_transfer",
                "sale_bills",
                "sale_bill_elements",
                "sale_bill_extra",
                "sale_bill_return",
                "special_currencies",
                "supplier_address",
                "supplier_note",
                "supplier_phone",
                "bank_safe_transfer",
                "banks_modifications",
                "banks_process",
                "banks_transfer",
                "basic_settings",
                "buy_bills",
                "buy_bill_elements",
                "buy_bill_extra",
                "buy_bill_return",
                "capitals",
                "client_profiles",
                "employees_cash");
              
              

            $backupScript = "";
        
            foreach ($tables as $table) {

                $tableName =  $table ;
                
                $rows = DB::table($tableName)->where('company_id', $company->id)->get();

                if ($rows->isNotEmpty()) {
                    $columns = DB::getSchemaBuilder()->getColumnListing($tableName);
        
                    foreach ($rows as $row) {
                        $backupScript .= "INSERT INTO `$tableName` VALUES(";
                        foreach ($columns as $column) {

                            $value = addslashes($row->$column);
                            // $backupScript .= "'" . $value . "',";

                            $isIndex = $this->isIndex($tableName, $column);

                            if(($value === '' || $value === NULL) && $isIndex){
                                $value = "NULL" ; 
                                $first_quotes = "";
                                $last_quotes = ",";
                            }else{
                                $first_quotes = "'";
                                $last_quotes = "',";
                            }
                            

                            $backupScript .= $first_quotes.$value.$last_quotes;

                        }
                        $backupScript = rtrim($backupScript, ',') . ");\n";
                    }
                }
            }
        
            if (!empty($backupScript)) {
                $backup_file_name = $company->company_name . '_' . date('Y-m-d') . '.sql';
                $backupFilePath = public_path("uploads/backups/".$backup_file_name );
                
                $file_name = $company->company_name . '_' . date('Y-m-d') . '.sql';
    
    
                $fileHandler = fopen($backupFilePath, 'w+');
                $number_of_lines = fwrite($fileHandler, $backupScript);
                fclose($fileHandler);

                // return response()->download($backupFilePath)->deleteFileAfterSend(true);
                 return response()->download($backupFilePath);


            }
        } catch (\Exception $e) {
            // Handle exceptions
            dd($e->getMessage());
        }
        
        
    }

    public function restore(Request $request)
    {
        try {
            $user = auth()->user();
            $company = $user->company;
    
            // Validate the request

            if (!$request->hasFile('sql_file')) {
                return redirect()->back()->with('error', 'يجب تحديد ملف النسخة الاحتياطية');
            }

            // check type of file

            $file = $request->file('sql_file');
            $extension = $file->getClientOriginalExtension();
            if ($extension != 'sql') {
                return redirect()->back()->with('error', 'يجب تحديد ملف sql');
            }

            // check size of file

            $size = $file->getSize();
            if ($size > 10000000) {
                return redirect()->back()->with('error', 'يجب أن يكون حجم الملف أقل من 10 ميجا بايت');
            }
    
            // Store the uploaded file
            $newFileName = $company->company_name . '_' . now()->format('Y-m-d_H-i-s') . '_' . Str::random(5) . '.sql';
            $filePath = $request->file('sql_file')->storeAs('uploads/restore', $newFileName);
                       
            // Read the contents of the SQL file using the Storage facade
            $sql = Storage::get($filePath);
            
            preg_match_all('/`(.*?)`/', $sql, $matches);
            $tables = $matches[1];
            $tables = array_unique($tables);

            if(empty($tables)){
                return redirect()->back()->with('error', 'هذه النسخة الاحتياطية فارغة');
            }
            $company_id = $company->id;
            $first_record = explode("\n", $sql)[0];
            $first_record = explode("VALUES", $first_record)[1];
            $first_record = explode(",", $first_record);
            $first_record = array_map(function ($value) {
                return trim($value, " '");
            }, $first_record);
            $first_record_company_id = $first_record[1];
    
    

    
            if($first_record_company_id != $company_id){
                return redirect()->back()->with('error', 'هذه النسخة الاحتياطية ليست لهذه الشركة');
            }    
            // Use Laravel's DB facade for queries within a transaction
            DB::beginTransaction();
    
            // Clear existing data for the company
            DB::statement('SET foreign_key_checks=0');

            foreach ($tables as $table) {
                $tableName = $table;
                DB::table($tableName)->where('company_id', $company->id)->delete();
            }
    
            DB::statement('SET foreign_key_checks=1');

            // Read and execute SQL file
            $contents = Storage::get($filePath);
            DB::unprepared($contents);
    
            DB::commit();

            File::delete(public_path('uploads/' . $filePath));
            
            return redirect()->route('client.backup.settings.edit')->with('success', 'تم رفع النسخة الاحتياطية بنجاح');
        } catch (\Exception $e) {
            // Handle exceptions
            DB::rollBack();
            return redirect()->back()->with('error', 'حدث خطأ أثناء رفع النسخة الاحتياطية: ' . $e->getMessage());
        }

    }


    protected function checkBackUpFile($filePath, $company_id){

        $sql = file_get_contents(storage_path('app/' . $filePath));
        preg_match_all('/`(.*?)`/', $sql, $matches);
        $tables = $matches[1];
        $tables = array_unique($tables);

        $first_record = explode("\n", $sql)[0];
        $first_record = explode("VALUES", $first_record)[1];
        $first_record = explode(",", $first_record);
        $first_record = array_map(function ($value) {
            return trim($value, " '");
        }, $first_record);
        $first_record_company_id = $first_record[1];


        if(empty($first_record_company_id)){
            return redirect()->back()->with('error', 'هذه النسخة الاحتياطية فارغة');
        }

        if($first_record_company_id != $company_id){
            return redirect()->back()->with('error', 'هذه النسخة الاحتياطية ليست لهذه الشركة');
        }

        return $tables;


    }

    protected function isIndex($table, $column)
    {
        $connection = DB::getDoctrineConnection();
        $schemaManager = $connection->getSchemaManager();
    
        $indexes = $schemaManager->listTableIndexes($table);
    
        foreach ($indexes as $index) {
            if (in_array($column, $index->getColumns())) {
                return true;
            }
        }
    
        return false;
    }


}


/*

        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $dbHost = env('DB_HOST');
        $dbUsername = env('DB_USERNAME');
        $dbPassword = env('DB_PASSWORD');
        $dbName = env('DB_DATABASE');
        $conn = mysqli_connect($dbHost, $dbUsername, $dbPassword, $dbName);
        $conn->set_charset("utf8");
        $tables = array();
        $result = mysqli_query($conn, "SELECT * FROM `tables`");
        while ($row = mysqli_fetch_row($result)) {
            $tables[] = $row[1];
        }
        $sqlScript = "";
        foreach ($tables as $table) {
            $result = mysqli_query($conn, "SELECT * FROM `$table` WHERE `company_id` = '$company_id' ");
            if (mysqli_num_rows($result) > 0) {
                $columnCount = mysqli_num_fields($result);
                // Prepare SQLscript for dumping data for each table
                for ($i = 0; $i < $columnCount; $i++) {
                    while ($row = mysqli_fetch_row($result)) {
                        $sqlScript .= "INSERT INTO `$table` VALUES(";
                        for ($j = 0; $j < $columnCount; $j++) {
                            $row[$j] = $row[$j];

                            if (isset($row[$j])) {
                                $sqlScript .= "'" . $row[$j] . "'";
                            } else {
                                $sqlScript .= '""';
                            }
                            if ($j < ($columnCount - 1)) {
                                $sqlScript .= ',';
                            }
                        }
                        $sqlScript .= ");\n";
                    }
                }
//                $sqlScript .= "\n";
            }
        }

        if (!empty($sqlScript)) {
            // Save the SQL script to a backup file
            $backup_file_name = $company->company_name . '_' . date('Y-m-d') . '.sql';
            $backup_file_name = public_path("/uploads/backup/".$backup_file_name );
            
            $file_name = $company->company_name . '_' . date('Y-m-d') . '.sql';


            $fileHandler = fopen($backup_file_name, 'w+');
            $number_of_lines = fwrite($fileHandler, $sqlScript);
            fclose($fileHandler);
            // Download the SQL backup file to the browser
            header('Content-type: file/txt');
            // It will be called downloaded.pdf
            header("Content-Disposition: attachment; filename=" . $file_name . "");
            // The PDF source is in original.pdf
            readfile($backup_file_name);
        }
*/