<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Carbon\Carbon;
use App\Models\Website;
use DB;
use App\Helpers\EncryptionHelper;
use Symfony\Component\Process\Process;
use Symfony\Component\Process\Exception\ProcessFailedException;
use App\Services\DatabasePrivilegeManager;
use Mail;

class WebsiteUsage extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'app:website-usage';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Command description';

    /**
     * Execute the console command.
     */

    function getDatabaseSize($host, $username, $password, $database)
    {
        // Create temporary connection
        $connectionName = 'temp_conn_' . $database;

        config([
            "database.connections.$connectionName" => [
                'driver' => 'mysql',
                'host' => $host,
                'port' => '3306',
                'database' => $database,
                'username' => $username,
                'password' => $password,
                'charset' => 'utf8mb4',
                'collation' => 'utf8mb4_unicode_ci',
                'prefix' => '',
            ]
        ]);

        // Use the temporary connection
        $size = DB::connection($connectionName)->selectOne("
            SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
            FROM information_schema.tables
            WHERE table_schema = ?
        ", [$database]);

        return $size->size_mb ?? 0;
    }
    public function sendWarningEmail($website){

        if($website->user){
            $user = $website->user;
            Mail::send('emails.usage_warning_email', [
                'user' => $user,
                'website'=>$website
            ], function ($message) use ($user) {
                $message->to($user->email);
                $message->subject('Usage exceeded - ' . env('APP_NAME'));
            });

            
            Mail::send('emails.usage_warning_email_for_admin', [
                'user' => $user,
                'website'=>$website
            ], function ($message) {
                 $message->to(env('ADMIN_EMAIL'), env('ADMIN_NAME'));
                $message->subject('Usage exceeded - ' . env('APP_NAME'));
            });
        }
    }
    public function blockWebsite($website){

        if($website){

            $server_meta = json_decode($website->server_meta, true);

            $process = new Process([
                'sudo',
                '/var/bloxbycommands/toggle_site.sh',
                "down",
                $server_meta["sftp_user"],
                "/var/www/".$server_meta["folder"]."/".$server_meta["sftp_user"]
            ]);
            
            $process->setTimeout(300); // Increase for long tasks
            $process->run();

            if (!$process->isSuccessful()) {

                
                //return redirect()->back()->with([ 'errors' => [$process->getErrorOutput()]]);
                
            } 

            $website->is_website_down=1;

            $website->save();
        }
        
        
    }
    public function handle()
    {
        //
        $cutoffTime = Carbon::now()->subHours(24);

        $limit = 500;

        Website::where('status', 'completed')
        ->where(function ($q) use ($cutoffTime) {
            $q->where('usage_checked_at', '<', $cutoffTime)
            ->orWhereNull('usage_checked_at');
        })
        ->where("usage_status","completed")
        ->update(["usage_status"=>"pending"]);

        Website::where('status', 'completed')
            ->where(function ($q) use ($cutoffTime) {
                $q->where('usage_checked_at', '<', $cutoffTime)
                ->orWhereNull('usage_checked_at');
            })
            ->limit($limit)
            ->chunk(100, function ($websites) {
                foreach ($websites as $website) {
                    try{

                        if($website->usage_status=="pending"){

                            $website->usage_status="inprogress";

                            $website->save();
                        
                            if($website->server_meta){
                                
                                $server_meta = json_decode($website->server_meta, true);

                                $db_pass = EncryptionHelper::secret_decrypt($server_meta["db_pass"],env("SODIUM_KEY"));

                                $db_size =  $this->getDatabaseSize("localhost", $server_meta["db_user"],$db_pass,$server_meta["db_name"]);
                                
                                $website->usage = $db_size;

                                $userDir = "/var/www/".$server_meta["folder"]."/".$server_meta["slug_environment"]."_".$server_meta["site"];

                                $size = trim(shell_exec("du -sb " . escapeshellarg($userDir) . " | cut -f1"));

                                $mb = $size / (1024 * 1024);

                                $disk_size = $mb;

                                $website->usage = $db_size + $disk_size;

                                $website->save();

                                if($website->usage >= 550){

                                    $this->blockWebsite($website);

                                    if($website->is_insert_permission_revoked==1){

                                        $privManager = new DatabasePrivilegeManager();

                                        $privManager->grantInsert($server_meta["db_name"], $server_meta["db_user"]);

                                        $website->is_insert_permission_revoked = 0;

                                        $website->save();
                                    }
                                    if($website->is_warning_email_sent===0 ){

                                        $this->sendWarningEmail($website);

                                        $website->is_warning_email_sent = 1;

                                        $website->save();
                                    }

                                } else if($website->usage > 500){

                                    $server_meta = json_decode($website->server_meta, true);

                                    if($website->is_insert_permission_revoked==0){

                                        $privManager = new DatabasePrivilegeManager();

                                        $privManager->revokeInsert($server_meta["db_name"], $server_meta["db_user"]);

                                        $website->is_insert_permission_revoked = 1;

                                        $website->save();
                                    }
                                    if($website->is_warning_email_sent===0 ){
                                        $this->sendWarningEmail($website);

                                        $website->is_warning_email_sent = 1;

                                        $website->save();
                                    }

                                } else if($website->usage > 400){

                                    $server_meta = json_decode($website->server_meta, true);

                                    if($website->is_insert_permission_revoked==1){

                                        $privManager = new DatabasePrivilegeManager();

                                        $privManager->grantInsert($server_meta["db_name"], $server_meta["db_user"]);

                                        $website->is_insert_permission_revoked = 0;

                                        $website->save();
                                    }
                                    
                                    if($website->is_warning_email_sent===0 ){
                                        $this->sendWarningEmail($website);

                                        $website->is_warning_email_sent = 1;

                                        $website->save();
                                    }
                                } else {

                                    $server_meta = json_decode($website->server_meta, true);

                                    if($website->is_insert_permission_revoked==1){

                                        $privManager = new DatabasePrivilegeManager();

                                        $privManager->grantInsert($server_meta["db_name"], $server_meta["db_user"]);

                                        $website->is_insert_permission_revoked = 0;

                                        $website->save();
                                    }


                                    $website->is_warning_email_sent = 0;

                                }
                                $website->usage_checked_at = date("Y-m-d H:i:s");

                                $website->save();
                            

                            }
                            $website->usage_status="completed";

                            $website->usage_checked_at =date("Y-m-d H:i:s"); 

                            $website->save();
                        }
                    } catch (\Exception $e){

                        $website->usage_status="completed";

                        $website->usage_checked_at =date("Y-m-d H:i:s");

                        $website->save();

                        echo $e->getMessage();
                    }
                }
            });
    }
}