<?php
namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Storage;
use Carbon\Carbon;

class BackupDatabase extends Command
{
    protected $signature = 'backup:database';
    protected $description = 'Backup the database and download it';

    /*public function __construct(){
        parent::__construct();
    }
    public function handle(){
        $database = env('DB_DATABASE');
        $username = env('DB_USERNAME');
        $password = env('DB_PASSWORD');
        $host = env('DB_HOST');
        $backupPath = storage_path('app/backups/' . $database . '_' . date('Y_m_d_His') . '.sql');

        // Ensure the backup directory exists
        if (!file_exists(dirname($backupPath))) {
            //mkdir(dirname($backupPath), 0755, true);
            mkdir(dirname($backupPath), 0777, true);
        }

        $command = "mysqldump --user={$username} --password={$password} --host={$host} {$database} > {$backupPath}";

        $this->info("Running command: {$command}");
        
        $returnVar = null;
        $output = null;

        exec($command, $output, $returnVar);

        if ($returnVar !== 0) {
            $this->error('Backup failed');
            $this->error('Return code: ' . $returnVar);
            $this->error('Output: ' . implode("\n", $output));
            return;
        }

        $this->info('Backup successful: ' . $backupPath);
    }
    */
    public function handle(){
        $this->info('Starting database backup...');

        $database = env('DB_DATABASE');
        $username = env('DB_USERNAME');
        $password = env('DB_PASSWORD');
        $host = env('DB_HOST');
        $backupPath = storage_path('app/backups');
        $fileName = 'backup_soft_db'.'_'.Carbon::now()->format('Y_m_d_H_i_s') . '.sql';
    
        if (!is_dir($backupPath)) {
            mkdir($backupPath, 0755, true);
        }

        // Create the database backup file
        $backupFile = fopen($backupPath . '/' . $fileName, 'w');

        $tables = DB::select('SHOW TABLES');
        foreach ($tables as $table) {
            $tableArray = get_object_vars($table);
            $tableName = $tableArray[key($tableArray)];

            // Retrieve the create table statement
            $createTableStmt = DB::select('SHOW CREATE TABLE ' . $tableName);
            if (!empty($createTableStmt)) {
                $createTableStmtArray = get_object_vars($createTableStmt[0]);

                // Find the correct key for the create table statement
                $createTableKey = null;
                foreach ($createTableStmtArray as $key => $value) {
                    if (stripos($key, 'Create Table') !== false) {
                        $createTableKey = $key;
                        break;
                    }
                }

                if ($createTableKey) {
                    fwrite($backupFile, $createTableStmtArray[$createTableKey] . ";\n\n");

                    $rows = DB::table($tableName)->get();
                    foreach ($rows as $row) {
                        $rowArray = get_object_vars($row);
                        $rowValues = array_map(function ($value) {
                            return "'" . addslashes($value) . "'";
                        }, array_values($rowArray));

                        fwrite($backupFile, 'INSERT INTO ' . $tableName . ' VALUES(' . implode(',', $rowValues) . ");\n");
                    }
                    fwrite($backupFile, "\n\n");
                } else {
                    $this->error('Could not find create table statement for table: ' . $tableName);
                }
            } else {
                $this->error('No create table statement returned for table: ' . $tableName);
            }
        }

        fclose($backupFile);

        $this->info('Database backup completed successfully.');
    }
}
