<?php

use App\Models\Campaigns;
use App\Models\Countries;
use App\Models\Registrations;
use App\Models\Sources;
use App\Models\Spinthewheelwinners;
use App\Modules\Models\Registration\Registration;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Intervention\Image\Facades\Image;
use Endroid\QrCode\Color\Color;
use Endroid\QrCode\Encoding\Encoding;
use Endroid\QrCode\ErrorCorrectionLevel\ErrorCorrectionLevelLow;
use Endroid\QrCode\QrCode;
use Endroid\QrCode\Label\Label;
use Endroid\QrCode\Logo\Logo;
use Endroid\QrCode\RoundBlockSizeMode\RoundBlockSizeModeMargin;
use Endroid\QrCode\Writer\PngWriter;
use Endroid\QrCode\Writer\ValidationException;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Hash;
use Revolution\Google\Sheets\Facades\Sheets;

class LMS
{
    public function __construct()
    {
        $this->initDB();
        $this->seedPermissions();
    }
    public static function spinwheelwinners()
    {
        $winners = DB::table("spinthewheelwinners")
            // ->where('status', 1)
            ->orderBy('created_at', "desc")
            ->get();
        foreach ($winners as $winner) {
            $registration = DB::table("registrations")
                ->where('status', 1)
                ->where('registration_id', $winner->registrations_id)
                ->first();
            $winner->Winner = $registration;
        }
        // dd($winners);
        return $winners;
    }
    public static function savespinwheelwinner($registration_id, $prize)
    {
        // Fetch registration data based on registration_id
        $data = DB::table("registrations")
            ->where('status', 1)
            ->where('registration_id', $registration_id)
            ->first();
        if ($data) {
            // Create a new spin wheel winner record
            Spinthewheelwinners::create([
                'title' => $data->name,
                'registrations_id' => $data->registration_id,
                'prize' => $prize
            ]);
            // Return the updated list of winners ordered by created_on in descending order
            return Spinthewheelwinners::orderBy("created_at", "desc")->get();
        } else {
            // Handle the case when no registration data is found
            return response()->json(['error' => 'Registration not found'], 404);
        }
    }
    public static function checkRegistrationByMobile($mobile)
    {
        $registration = DB::table("registrations")
            ->where('status', 1)
            ->where('mobile', $mobile)
            ->orderBy('display_order')
            ->first();
        if (!$registration) {
            // Handle the case where the registration is not found
            return;
        }
        return $registration;
    }
    public static function getLeadQR($registration_id)
    {
        $registration = DB::table("registrations")
            ->where('status', 1)
            ->where('registration_id', $registration_id)
            ->orderBy('display_order')
            ->first();
        if (!$registration) {
            // Handle the case where the registration is not found
            return;
        }
        $Campaign = Campaigns::findOrFail($registration->campaigns_id);
        $QRURL = route("registration.view", md5($registration_id));
        $writer = new PngWriter();
        $qrCode = QrCode::create($QRURL)
            ->setEncoding(new Encoding('UTF-8'))
            ->setErrorCorrectionLevel(new ErrorCorrectionLevelLow())
            ->setSize(230)
            ->setMargin(1)
            ->setRoundBlockSizeMode(new RoundBlockSizeModeMargin())
            ->setForegroundColor(new Color(0, 0, 0))
            ->setBackgroundColor(new Color(255, 255, 255));
        $result = $writer->write($qrCode);
        $dataUri = $result->getDataUri();
        if ($Campaign->pass_layout != "") {
            $pass_layout = storage_path(str_replace("storage/", "app/public/", $Campaign->pass_layout));
            $backgroundImage = Image::make($pass_layout);
        } else {
            $backgroundImage = Image::make(public_path(env("CLIENT_PATH") . '/assets/images/pass.png'));
        }
        $qrCodeImage = Image::make($dataUri);
        $qrCodePosition = ($Campaign->qr_position != "") ? $Campaign->qr_position : "100,100";
        $qrCodePosition = explode(",", $qrCodePosition);
        $backgroundImage->insert($qrCodeImage, 'top-left', $qrCodePosition[0], $qrCodePosition[1]);
        $text = $registration->registration_id;
        $idPosition = ($Campaign->id_position != "") ? $Campaign->id_position : "100,100";
        $idPosition = explode(",", $idPosition);
        $fontPath = public_path('assets/fonts/Poppins-Regular.ttf'); // Replace with the actual font path
        $backgroundImage->text($text, $idPosition[0], $idPosition[1], function ($font) use ($fontPath) {
            $font->file($fontPath);
            $font->size(28);
            $font->color('#000000');
            $font->align('left');
            $font->valign('top');
        });
        $text = $registration->name;
        $namePosition = ($Campaign->name_position != "") ? $Campaign->name_position : "100,100";
        $namePosition = explode(",", $namePosition);
        $fontPath = public_path('assets/fonts/Poppins-Regular.ttf'); // Replace with the actual font path
        $backgroundImage->text($text, $namePosition[0], $namePosition[1], function ($font) use ($fontPath) {
            $font->file($fontPath);
            $font->size(28);
            $font->color('#000000');
            $font->align('left');
            $font->valign('top');
        });
        $text = $registration->email;
        $emailPosition = ($Campaign->email_position != "") ? $Campaign->email_position : "100,100";
        $emailPosition = explode(",", $emailPosition);
        $fontPath = public_path('assets/fonts/Poppins-Regular.ttf'); // Replace with the actual font path
        $backgroundImage->text($text, $emailPosition[0], $emailPosition[1], function ($font) use ($fontPath) {
            $font->file($fontPath);
            $font->size(24);
            $font->color('#000000');
            $font->align('left');
            $font->valign('top');
        });
        $text = $registration->mobile;
        $xCoordinateText = 960; // Set the X coordinate for the text
        $yCoordinateText = 365; // Set the Y coordinate for the text
        $fontPath = public_path('assets/fonts/Poppins-Regular.ttf'); // Replace with the actual font path
        $backgroundImage->text($text, $xCoordinateText, $yCoordinateText, function ($font) use ($fontPath) {
            $font->file($fontPath);
            $font->size(24);
            $font->color('#000000');
            $font->align('left');
            $font->valign('top');
        });
        $text = $registration->intrested_for_country;
        $xCoordinateText = 960; // Set the X coordinate for the text
        $yCoordinateText = 430; // Set the Y coordinate for the text
        $fontPath = public_path('assets/fonts/Poppins-Regular.ttf'); // Replace with the actual font path
        $backgroundImage->text($text, $xCoordinateText, $yCoordinateText, function ($font) use ($fontPath) {
            $font->file($fontPath);
            $font->size(24);
            $font->color('#000000');
            $font->align('left');
            $font->valign('top');
        });
        // Save the combined image
        $outputPath = public_path(env("CLIENT_PATH") . '/assets/images/generated/pass_' . date("ymdhis") . '_image.png');
        $backgroundImage->save($outputPath);
        // Remove the temporary QR code image
        // unlink($qrCodePath);
        // You can return the output path or perform any other desired actions
        return $outputPath;
    }
    public static function sendSMSWithCurl($destination, $message)
    {
        $userName = (SITEVARS->sms_username) ? SITEVARS->sms_username : '';
        $password = (SITEVARS->sms_password) ? SITEVARS->sms_password : '';
        $sender = (SITEVARS->sms_sender) ? SITEVARS->sms_sender : '';
        $url = (SITEVARS->sms_api) ? SITEVARS->sms_api : 'http://api.ininepal.com/api/index?';
        $encodedMessage = urlencode($message);
        $encodedDestination = urlencode($destination);
        $url_query = "username={$userName}&password={$password}&msg={$encodedMessage}&contacts={$encodedDestination}&responsetype=json";
        $url_final = $url . $url_query;

        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL, $url_final);
        curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
        curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, false);

        $response = curl_exec($ch);
        ob_clean();
        if ($response === false) {
            // Handle the error if needed (e.g., log the error)
            return false;
        }

        // Close the cURL session
        curl_close($ch);
        return true;
    }
    public static function oldsendSMSWithCurl($destination, $message)
    {
        $userName = (SITEVARS->sms_username) ? SITEVARS->sms_username : '';
        $password = (SITEVARS->sms_password) ? SITEVARS->sms_password : '';
        $sender = (SITEVARS->sms_sender) ? SITEVARS->sms_sender : '';
        $url = (SITEVARS->sms_api) ? SITEVARS->sms_api : 'http://api.ininepal.com/api/index?';
        // URL-encode the message and destination
        $encodedMessage = urlencode($message);
        $encodedDestination = urlencode($destination);
        // Construct the URL query string
        $url_query = "username={$userName}&password={$password}&message={$encodedMessage}&destination={$encodedDestination}&sender={$sender}";
        // Build the final URL
        $url_final = $url . $url_query;
        // Initialize cURL session
        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL, $url_final);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        // Execute the cURL request
        $response = curl_exec($ch);
        // Check for errors and handle the response
        if ($response === false) {
            // cURL request failed, handle the error as needed
            // You can log the error or throw an exception
            return false;
        } else {
            // Check the response for success or failure
            if ($response === '1') {
                // SMS sent successfully
                return true;
            } else {
                // SMS sending failed, handle the error as needed
                // You can log the error or throw an exception
                return false;
            }
        }
        // Close the cURL session
        curl_close($ch);
    }
    public static function SaveInGoogleSheet($data)
    {
        $Campaign = Campaigns::findorfail($data['campaigns_id']);
        // $spreadsheetId = ($Campaign->google_sheet_id != "") ? $Campaign->google_sheet_id : '1g0lR_98d_10P1rDUcWMz3b0eCW1bmDFxBpgZAxCDTF4';
        // $sheetName = ($Campaign->google_sheet_worksheet != "") ? $Campaign->google_sheet_worksheet : 'OfficeVisits';
        $spreadsheetId = ($Campaign->google_sheet_id != "") ? $Campaign->google_sheet_id : '1uwd3MiLpp_qf7kYGU3CkxO3bXrLpzrhdusNsjZueWB4';
        $sheetName = ($Campaign->google_sheet_worksheet != "") ? $Campaign->google_sheet_worksheet : 'Sheet1';
        $rows = Sheets::spreadsheet($spreadsheetId)->sheet($sheetName)->all();
        $lastRowIndex = count($rows);
        $newRowIndex = $lastRowIndex + 1;
        /* TO DUMP ALL ROWS
        $rowData = ['sn' => $lastRowIndex];
        $a=0;
        foreach ($data as $index => $value) {
            $columnName = 'col'.$a;// . ($index + 1);
            $a++;
            $rowData[$columnName] = $value;
        }
        Sheets::spreadsheet($spreadsheetId)->sheet($sheetName)->range('A' . $newRowIndex)->append([$rowData]);
        */
        Sheets::spreadsheet($spreadsheetId)->sheet($sheetName)->range('A' . $newRowIndex)->append([
            [
                'sn' => $lastRowIndex,
                'name' => $data['name'],
                'email' => $data['email'],
                'phone' => $data['mobile'],
                'qualification' => isset($data['highest_qualification']) ? $data['highest_qualification'] : "0",
                'passed_year' => '',
                'interested_for' => isset($data['preferred_destination']) ? $data['preferred_destination'] : "0",
                'user_source' => env('CLIENT_PATH'),
            ]
        ]);
    }
    //CONSULTANCY RELATED TEMP FUNCTIONS
    public static function createMenuLink($text, $URL)
    {
        $isActive = request()->fullUrl() == $URL;
        $activeClass = $isActive ? 'active' : '';
?>
        <li>
            <a class="nav-link menu-link <?php echo $activeClass; ?>" href="<?php echo $URL; ?>"><i class="ri-file-text-line "></i> <span data-key="t-landing">
                    <?php echo $text; ?>
                </span></a>
        </li>
<?php
    }
    public static function getCampaigns()
    {
        $Rows = DB::table("campaigns")->where("status", 1)->orderBy("display_order")->get();
        return $Rows;
    }
    public static function getCampaignByAlias($alias)
    {
        $Rows = DB::table("campaigns")->where("status", 1)->where("alias", $alias)->first();
        return $Rows;
    }
    public static function getActiveCampaign()
    {
        $Rows = DB::table("campaigns")->where("status", 1)->orderBy("display_order")->first();
        return $Rows;
    }
    public static function campaignsCount()
    {
        $Rows = DB::table("campaigns")->where("status", 1)->count();
        return $Rows;
    }
    public static function getLeadCategories()
    {
        $Rows = DB::table("leadcategories")->where("status", 1)->orderBy("display_order")->get();
        return $Rows;
    }
    public static function getSources()
    {
        $Rows = DB::table("sources")->where("status", 1)->orderBy("display_order")->get();
        return $Rows;
    }
    public static function getQualifications()
    {
        $Rows = DB::table("qualifications")->where("status", 1)->orderBy("display_order")->get();
        return $Rows;
    }
    public static function getPrefferedlocation()
    {
        $Rows = DB::table("registrations")->where("status", 1)->orderBy("display_order")->get();
        return $Rows->preffered_location;
    }
    public static function getSiteVars()
    {
        $siteVars = DB::table("settings")->where('status', 1)->orderby('display_order')->first();
        $siteVars->Countries = self::getCountries();
        $siteVars->Campaigns = self::getCampaigns();

        return $siteVars;
    }
    public static function qrScanCounts($id = null)
    {
        if ($id) {
            $query = DB::table("qrscans")->where('status', 1)->where("registrations_id", $id);
            return $query->count();
        } else {
            $query = DB::table("qrscans")->where('status', 1);
            return $query->count();
        }
    }
    public static function leadsCount($group = null, $id = null)
    {
        $query = DB::table("registrations")->where('status', 1);
        // Apply additional conditions using a switch-case statement
        switch ($group) {
            case 'campaign':
                if ($id !== null) {
                    $query->where('campaigns_id', $id);
                }
                break;
            case 'source':
                if ($id !== null) {
                    $query->where('sources_id', $id);
                }
                break;
            case 'country':
                if ($id !== null) {
                    $query->where('countries_id', $id);
                }
                break;
            case 'category':
                if ($id !== null) {
                    $query->where('leadcategories_id', $id);
                }
                break;
            case 'agent':
                if ($id !== null) {
                    $query->where('agents_id', $id);
                }
                break;
                // Add more cases as needed
        }
        $count = $query->count();
        return $count;
    }
    public static function studentsCount($group = null, $id = null)
    {
        $query = DB::table("students")->where('status', 1);
        // Apply additional conditions using a switch-case statement
        switch ($group) {
            case 'campaign':
                if ($id !== null) {
                    $query->where('campaigns_id', $id);
                }
                break;
            case 'source':
                if ($id !== null) {
                    $query->where('sources_id', $id);
                }
                break;
            case 'branch':
                if ($id !== null) {
                    $query->where('branches_id', $id);
                }
                break;
            case 'country':
                if ($id !== null) {
                    $query->where('countries_id', $id);
                }
                break;
            case 'category':
                if ($id !== null) {
                    $query->where('leadcategories_id', $id);
                }
                break;
            case 'agent':
                if ($id !== null) {
                    $query->where('agents_id', $id);
                }
                break;
                // Add more cases as needed
        }
        $count = $query->count();
        return $count;
    }
    public static function countriesCount()
    {
        $Rows = DB::table("countries")->where('status', 1)->count();
        return $Rows;
    }
    public static function getCountries()
    {
        $Rows = DB::table("countries")->where('status', 1)->orderby('display_order')->get();
        return $Rows;
    }
    public static function getAgents()
    {
        $Rows = DB::table("agents")->where('status', 1)->orderby('display_order')->get();
        return $Rows;
    }
    public static function getLeads($limit = null)
    {
        $Rows = DB::table("registrations")->where('status', 1)->orderByDesc('display_order');
        if ($limit) {
            $Rows = $Rows->limit($limit);
        }
        $Rows = $Rows->get();
        foreach ($Rows as $Row) {
            $Row->countries_id = ($Row->countries_id != 0) ? $Row->countries_id : 1;
            $Row->sources_id = ($Row->sources_id != 0) ? $Row->sources_id : 1;
            $Row->Source = Sources::findorfail($Row->sources_id);
            $Row->Country = Countries::findorfail($Row->countries_id);
        }
        return $Rows;
    }
    public static function getCampaignArticlesByCampaignAlias($alias)
    {
        $subquery = DB::table('campaigns')->select('campaign_id')->where('alias', $alias)->where("status", 1)->orderBy("display_order", "ASC");
        $articles = DB::table('campaignarticles')->where('parent_article', 0)->orderBy("display_order", "ASC")->whereIn('campaigns_id', $subquery)->get();
        foreach ($articles as $article) {
            $article->ChildArticles = DB::table('campaignarticles')->where('parent_article', $article->campaignarticle_id)->where("status", 1)->orderBy("display_order", "ASC")->get();
        }
        return $articles;
    }
    public static function getCountry($alias)
    {
        $Countries = DB::table("countries")->where('alias', $alias)->get();
        return $Countries[0];
    }
    public static function showForm($formID)
    {
        if (is_numeric($formID)) {
            $Form = DB::table("forms")->where('form_id', $formID)->first();
        } else {
            $Form = DB::table("forms")->where('alias', $formID)->first();
        }
        if (!$Form) {
            // Handle the case where the form with the given ID/alias doesn't exist
            return "Error: Form (ID/Alias: $formID) not found.";
        }
        $csrfToken = csrf_token();
        if (session('success')) {
            echo '<div class="alert alert-success" role="alert">';
            echo session('success');
            echo '</div>';
        }
        echo '<form class="mt-5" action="' . route("form.submit") . '" method="POST">';
        echo '<input type="hidden" name="_token" value="' . $csrfToken . '">';
        echo '<input type="hidden" name="form_id" value="' . $Form->form_id . '">';
        $form_fields = json_decode($Form->form_fields);
        foreach ($form_fields as $field) {
            $fieldAlias = strtolower($field->fieldAlias);
            $fieldName = strtolower($field->fieldName);
            $fieldType = $field->fieldType;
            $fieldDefault = $field->fieldDefault;
            $fieldCss = $field->fieldCss;
            echo '<div class="mb-3 ' . $fieldCss . '">';
            echo '<label for="' . $fieldAlias . '" class="form-label">' . ucfirst($fieldName) . '</label>';
            // Check if the "required" class is present in $fieldCss and add the required attribute
            $isRequired = strpos($fieldCss, 'required') !== false;
            if ($fieldType === 'textarea') {
                echo '<textarea class="form-control ' . ($isRequired ? 'required' : '') . '" id="' . $fieldAlias . '" name="' . $fieldAlias . '" ' . ($isRequired ? 'required' : '') . '>' . $fieldDefault . '</textarea>';
            } else {
                echo '<input type="' . $fieldType . '" class="form-control ' . ($isRequired ? 'required' : '') . '" id="' . $fieldAlias . '" name="' . $fieldAlias . '" value="' . $fieldDefault . '" ' . ($isRequired ? 'required' : '') . '>';
            }
            echo '</div>';
        }
        echo '<button type="submit" class="btn btn-primary">Submit</button>';
        echo '</form>';
    }

    public static function seedPermissions()
    {
        $modelNames = self::getModelNames();

        foreach ($modelNames as $modelName) {
            self::createPermission($modelName, 'create');
            self::createPermission($modelName, 'edit');
            self::createPermission($modelName, 'index');
            self::createPermission($modelName, 'delete');
            self::createPermission($modelName, 'store');
            self::createPermission($modelName, 'update');
        }
    }

    public static function getModelNames()
    {
        // Adjust the directory path based on your model location
        $modelsDirectory = app_path('Models');
        $files = scandir($modelsDirectory);
        $models = [];

        foreach ($files as $file) {
            if (pathinfo($file, PATHINFO_EXTENSION) == 'php') {
                $modelName = pathinfo($file, PATHINFO_FILENAME);
                $models[] = $modelName;
            }
        }

        return $models;
    }
    public static function createPermission($modelName, $command)
    {

        $AdminUser = DB::table('users')
            ->where('roles_id', 1)
            ->first();
        if (!$AdminUser) {
            // dd("hello");
            DB::table('users')->insert([
                'name' => 'Prajwal Adhikari',
                'email' => 'prajwalbro@hotmail.com',
                'username' => 'prajwalbro@hotmail.com',
                'password' => Hash::make('p@ssw0rd'),
                'roles_id' => 1,
                'created_at' => now(), // or use a specific timestamp if needed
                'createdby' => 1, // replace with the actual user ID who created it
                'updated_at' => now(),
                'updatedby' => 1, // replace with the actual user ID who updated it
                'status' => 1, // adjust as needed
            ]);
        }
        $permissionName = "{$command} {$modelName}";
        $existingPermission = DB::table('permissions')
            ->where('title', $permissionName)
            ->first();
        $adminRole = DB::table('roles')
            ->where('title', 'admin')
            ->first();
        if (!$existingPermission) {
            $LastInsertID = DB::table('permissions')->insert([
                'title' => ucfirst($command) . ' ' . $modelName,
                'alias' => strtolower("{$command}_{$modelName}"),
                'modal' => $modelName,
                'command' => $command,
                'created_at' => now(), // or use a specific timestamp if needed
                'createdby' => 1, // replace with the actual user ID who created it
                'updated_at' => now(),
                'updatedby' => 1, // replace with the actual user ID who updated it
                'status' => 1, // adjust as needed
            ]);
            DB::table('rolepermissions')->insert([
                'roles_id' => $adminRole->role_id,
                'permissions_id' => $LastInsertID,
                'created_at' => now(),
                'createdby' => 1,
                'updated_at' => now(),
                'updatedby' => 1,
                'status' => 1

            ]);
        }
    }
    private function initDB()
    {
        static $initialized = false;
        if (!$initialized) {
            DB::statement("CREATE TABLE IF NOT EXISTS `tbl_logos` (
                `logo_id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `logo` VARCHAR(255),
                `link` VARCHAR(255),
                `display_order` INT(11),
                `status` INT(11),
                `remarks` TEXT,
                `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
                `createdby` INT(11),
                `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                `updatedby` INT(11)
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
              ");
            DB::statement("CREATE TABLE IF NOT EXISTS `tbl_testimonials` (
                `testimonial_id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `video_url` VARCHAR(255),
                `review` VARCHAR(255),
                `name` VARCHAR(20) NOT NULL,
                `designation` VARCHAR(100),
                `display_order` INT(11),
                `status` INT(11),
                `remarks` TEXT,
                `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
                `createdby` INT(11),
                `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                `updatedby` INT(11)
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
              ");
        DB::statement("CREATE TABLE IF NOT EXISTS `tbl_contactus` (
            `inquiries_id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `title` VARCHAR(255),
            `email` VARCHAR(50),
            `phone` VARCHAR(20) NOT NULL,
            `country_interested` VARCHAR(100),
            `study_background` VARCHAR(50) NOT NULL,
            `terms_and_condition` VARCHAR(20) DEFAULT 0,
            `display_order` INT(11),
            `status` INT(11),
            `remarks` TEXT,
            `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
            `createdby` INT(11),
            `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            `updatedby` INT(11)
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
          ");
            DB::statement("CREATE TABLE IF NOT EXISTS `tbl_qualifications` (
                `qualification_id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `title` VARCHAR(255),
                `alias` VARCHAR(255),
                `description` TEXT,
                `display_order` INT(11),
                `status` INT(11),
                `remarks` TEXT,
                `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
                `createdby` INT(11),
                `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                `updatedby` INT(11)
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
              ");
            DB::statement("CREATE TABLE IF NOT EXISTS `tbl_leadcategories` (
                `leadcategory_id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `title` VARCHAR(255),
                `alias` VARCHAR(255),
                `description` TEXT,
                `color` VARCHAR(255),
                `display_order` INT(11),
                `status` INT(11),
                `remarks` TEXT,
                `created_at` DATETIME DEFAULT NOW(),
                `createdby` INT(11),
                `updated_at` DATETIME DEFAULT NOW(),
                `updatedby` INT(11)
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
              ");
            DB::statement("CREATE TABLE IF NOT EXISTS `tbl_institutiontypes` (
                `institutiontype_id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `title` VARCHAR(255),
                `alias` VARCHAR(255),
                `description` TEXT,
                `display_order` INT(11),
                `status` INT(11),
                `remarks` TEXT,
                `created_at` DATETIME DEFAULT NOW(),
                `createdby` INT(11),
                `updated_at` DATETIME DEFAULT NOW(),
                `updatedby` INT(11)
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
              ");
            DB::statement("CREATE TABLE IF NOT EXISTS `tbl_institutions` (
                `institution_id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `institutiontypes_id` INT(11),
                `countries_id` INT(11),
                `title` VARCHAR(255),
                `alias` VARCHAR(255),
                `description` TEXT,
                `state` VARCHAR(255),
                `city` VARCHAR(255),
                `address` TEXT,
                `contact` VARCHAR(255),
                `logo` VARCHAR(255),
                `thumb` VARCHAR(255),
                `display_order` INT(11),
                `status` INT(11),
                `remarks` TEXT,
                `created_at` DATETIME DEFAULT NOW(),
                `createdby` INT(11),
                `updated_at` DATETIME DEFAULT NOW(),
                `updatedby` INT(11)
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
              ");
            DB::statement("CREATE TABLE IF NOT EXISTS `tbl_programs` (
                `program_id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `institutions_id` INT(11),
                `title` VARCHAR(255),
                `alias` VARCHAR(255),
                `description` TEXT,
                `programlevels_id` INT(11),
                `program_level` VARCHAR(255),
                `years` VARCHAR(255),
                `psw` VARCHAR(255),
                `code` VARCHAR(255),
                `coops_id` INT(11),
                `documentsets_id` INT(11),
                `program_url` text,
                `prospects` TEXT,
                `intake` VARCHAR(255),
                `institution` VARCHAR(255),
                `city` VARCHAR(255),
                `application_open` VARCHAR(255),
                `application_deadline` VARCHAR(255),
                `fee` VARCHAR(255),
                `level` VARCHAR(255),
                `min_level` VARCHAR(255),
                `min_level_faculty` VARCHAR(255),
                `min_level_score` VARCHAR(255),
                `min_level_passout` VARCHAR(255),
                `min_level_optional_requirement` VARCHAR(255),
                `prof_test_required` INT(11),
                `prof_test_accepted` VARCHAR(255),
                `prof_test_min_score` VARCHAR(255),
                `prof_test_band_score` VARCHAR(255),
                `display_order` INT(11),
                `status` INT(11),
                `remarks` TEXT,
                `created_at` DATETIME DEFAULT NOW(),
                `createdby` INT(11),
                `updated_at` DATETIME DEFAULT NOW(),
                `updatedby` INT(11)
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
              ");
            DB::statement("CREATE TABLE IF NOT EXISTS `tbl_agents` (
                `agent_id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `title` VARCHAR(255),
                `alias` VARCHAR(255),
                `description` TEXT,
                `display_order` INT(11),
                `status` INT(11),
                `remarks` TEXT,
                `created_at` DATETIME DEFAULT NOW(),
                `createdby` INT(11),
                `updated_at` DATETIME DEFAULT NOW(),
                `updatedby` INT(11)
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
              ");
            DB::statement("CREATE TABLE IF NOT EXISTS `tbl_cities` (
                `city_id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `districts_id` INT(11),
                `title` VARCHAR(255),
                `alias` VARCHAR(255),
                `description` TEXT,
                `display_order` INT(11),
                `status` INT(11),
                `remarks` TEXT,
                `created_at` DATETIME DEFAULT NOW(),
                `createdby` INT(11),
                `updated_at` DATETIME DEFAULT NOW(),
                `updatedby` INT(11)
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
              ");
            DB::statement("CREATE TABLE IF NOT EXISTS `tbl_districts` (
                `district_id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `provinces_id` INT(11),
                `title` VARCHAR(255),
                `alias` VARCHAR(255),
                `description` TEXT,
                `display_order` INT(11),
                `status` INT(11),
                `remarks` TEXT,
                `created_at` DATETIME DEFAULT NOW(),
                `createdby` INT(11),
                `updated_at` DATETIME DEFAULT NOW(),
                `updatedby` INT(11)
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
              ");
            DB::statement("CREATE TABLE IF NOT EXISTS `tbl_provinces` (
                `province_id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `title` VARCHAR(255),
                `alias` VARCHAR(255),
                `description` TEXT,
                `display_order` INT(11),
                `status` INT(11),
                `remarks` TEXT,
                `created_at` DATETIME,
                `createdby` INT(11),
                `updated_at` DATETIME,
                `updatedby` INT(11)
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
              ");
            DB::statement("CREATE TABLE IF NOT EXISTS `tbl_countries` (
                `country_id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `title` VARCHAR(255),
                `alias` VARCHAR(255),
                `description` TEXT,
                `display_order` INT(11),
                `status` INT(11),
                `remarks` TEXT,
                `created_at` DATETIME,
                `createdby` INT(11),
                `updated_at` DATETIME,
                `updatedby` INT(11)
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
              ");
            DB::statement("CREATE TABLE IF NOT EXISTS `tbl_branches` (
                `branch_id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `title` VARCHAR(255),
                `alias` VARCHAR(255),
                `description` TEXT,
                `email` VARCHAR(255),
                `telephone` VARCHAR(255),
                `phone1` VARCHAR(255),
                `phone2` VARCHAR(255),
                `address` VARCHAR(255),
                `company_reg` VARCHAR(255),
                `is_main` INT(11) NULL Default 0,
                `display_order` INT(11),
                `status` INT(11),
                `remarks` TEXT,
                `created_at` DATETIME,
                `createdby` INT(11),
                `updated_at` DATETIME,
                `updatedby` INT(11)
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
              ");
            DB::statement("CREATE TABLE IF NOT EXISTS `tbl_campaigns` (
                `campaign_id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `title` VARCHAR(255),
                `alias` VARCHAR(255),
                `description` TEXT,
                `details` TEXT,
                `cover_photo` VARCHAR(255),
                `thumb` VARCHAR(255),
                `starts` DATE,
                `ends` DATE,
                `success_message` VARCHAR(255),
                `sms_message` VARCHAR(255),
                `coupon_codes` TEXT,
                `offered_choices` TEXT,
                `pass_layout` VARCHAR(255),
                `qr_position` VARCHAR(255),
                `id_position` VARCHAR(255),
                `name_position` VARCHAR(255),
                `email_position` VARCHAR(255),
                `notify_emails` TEXT,
                `google_sheet_id` VARCHAR(255),
                `google_sheet_worksheet` VARCHAR(255),
                `is_active` int(11),
                `og_image` VARCHAR(255),
                `seo_title` VARCHAR(255),
                `seo_keywords` VARCHAR(255),
                `seo_description` TEXT,
                `meta_tags` TEXT,
                `display_order` INT(11),
                `status` INT(11),
                `remarks` TEXT,
                `created_at` DATETIME DEFAULT NOW(),
                `createdby` INT(11),
                `updated_at` DATETIME DEFAULT NOW(),
                `updatedby` INT(11)
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
              ");
            DB::statement("CREATE TABLE IF NOT EXISTS `tbl_operation_logs` (
                `operation_id` bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `refNo` varchar(255) DEFAULT NULL,
                `user_id` int(11) DEFAULT NULL,
                `operation_start_no` bigint(20) DEFAULT NULL,
                `operation_end_no` bigint(20) DEFAULT NULL,
                `model_name` varchar(100) DEFAULT NULL,
                `model_id` int(11) DEFAULT NULL,
                `operation_name` varchar(100) DEFAULT NULL,
                `previous_values` text DEFAULT NULL,
                `new_values` longtext DEFAULT NULL,
                `created_at` timestamp NULL DEFAULT NULL,
                `updated_at` timestamp NULL DEFAULT NULL
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
              ");
            DB::statement("CREATE TABLE IF NOT EXISTS `tbl_error_logs` (
                `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `user_id` bigint(20) UNSIGNED DEFAULT NULL,
                `controller_name` varchar(255) DEFAULT NULL,
                `method_name` varchar(255) DEFAULT NULL,
                `errors` longTEXT NULL,
                `created_at` timestamp NULL DEFAULT NULL,
                `updated_at` timestamp NULL DEFAULT NULL
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
              ");
            DB::statement("CREATE TABLE IF NOT EXISTS `tbl_activity_logs` (
                `activity_id` bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `user_id` int(11) DEFAULT NULL,
                `controllerName` varchar(100) DEFAULT NULL,
                `methodName` varchar(100) DEFAULT NULL,
                `actionUrl` varchar(255) DEFAULT NULL,
                `activity` varchar(255) DEFAULT NULL,
                `created_at` timestamp NULL DEFAULT NULL,
                `updated_at` timestamp NULL DEFAULT NULL
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
              ");
            DB::statement("CREATE TABLE IF NOT EXISTS `tbl_users` (
                `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `name` varchar(255) NULL,
                `email` varchar(255) NULL,
                `username` varchar(255) NULL,
                `email_verified_at` timestamp NULL DEFAULT NULL,
                `password` varchar(255) NULL,
                `remember_token` varchar(100) DEFAULT NULL,
                `display_order` INT(11) DEFAULT 1,
                `roles_id` INT(11),
                `branches_id` INT(11),
                `agents_id` INT(11),
                `students_id` INT(11),
                `status` INT(11) DEFAULT 1,
                `created_at` timestamp NULL DEFAULT NULL,
                `createdby` INT(11),
                `updated_at` timestamp NULL DEFAULT NULL,
                `updatedby` INT(11)
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
              ");
            DB::statement("CREATE TABLE IF NOT EXISTS `tbl_settings` (
                `setting_id` int(11) NOT NULL  AUTO_INCREMENT PRIMARY KEY,
                `title` varchar(255) NULL,
                `description` TEXT NULL,
                `url1` varchar(255) NULL,
                `url2` varchar(255) NULL,
                `email` varchar(255) NULL,
                `phone` varchar(255) NULL,
                `secondary_phone` varchar(255) NULL,
                `google_map` TEXT NULL,
                `fb` varchar(255) NULL,
                `insta` varchar(255) NULL,
                `twitter` varchar(255) NULL,
                `tiktok` varchar(255) NULL,
                `primary_logo` varchar(255) NULL,
                `secondary_logo` varchar(255) NULL,
                `thumb` varchar(255) NULL,
                `icon` varchar(255) NULL,
                `og_image` varchar(255) NULL,
                `no_image` varchar(250) NULL,
                `copyright_text` varchar(250) NULL,
                `content1` TEXT NULL,
                `content2` TEXT NULL,
                `content3` TEXT NULL,
                `seo_title` varchar(255) NULL,
                `seo_description` TEXT NULL,
                `seo_keywords` TEXT NULL,
                `og_tags` TEXT NULL,
                `display_order` int(11) NULL DEFAULT 0,
                `status` int(11) NULL DEFAULT 0,
                `created_at` timestamp NULL DEFAULT NULL,
                `updated_at` timestamp NULL DEFAULT NULL,
                `createdby` int(11) DEFAULT NULL,
                `updatedby` int(11) DEFAULT NULL
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
              ");
            DB::statement("CREATE TABLE IF NOT EXISTS `tbl_articles` (
                `article_id` int(11) NOT NULL  AUTO_INCREMENT PRIMARY KEY,
                `parent_article` int(11) DEFAULT 0,
                `title` varchar(250) NULL,
                `alias` varchar(250) NULL,
                `text` TEXT NULL,
                `cover_photo` varchar(500) NOT NULL,
                `thumb` varchar(255) NULL,
                `display_order` int(11) NULL DEFAULT 0,
                `status` int(11) NULL DEFAULT 0,
                `created_at` timestamp NULL DEFAULT NULL,
                `updated_at` timestamp NULL DEFAULT NULL,
                `createdby` int(11) DEFAULT NULL,
                `updatedby` int(11) DEFAULT NULL
              ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
              ");
            DB::statement("CREATE TABLE IF NOT EXISTS `tbl_campaignarticles` (
                `campaignarticle_id` int(11) NOT NULL  AUTO_INCREMENT PRIMARY KEY,
                `campaigns_id` int(11) DEFAULT 0,
                `parent_article` int(11) DEFAULT 0,
                `title` varchar(250) NULL,
                `alias` varchar(250) NULL,
                `link` text NULL,
                `text` TEXT NULL,
                `cover_photo` varchar(500) NOT NULL,
                `thumb` varchar(255) NULL,
                `display_order` int(11) NULL DEFAULT 0,
                `status` int(11) NULL DEFAULT 0,
                `created_at` timestamp NULL DEFAULT NULL,
                `updated_at` timestamp NULL DEFAULT NULL,
                `createdby` int(11) DEFAULT NULL,
                `updatedby` int(11) DEFAULT NULL
              ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
              ");
            DB::statement("CREATE TABLE IF NOT EXISTS `tbl_preparationclasses` (
                `preparationclass_id` int(11) NOT NULL  AUTO_INCREMENT PRIMARY KEY,
                `title` varchar(255) NULL,
                `alias` varchar(255) NULL,
                `parent_preparationclass` int(11) NULL DEFAULT 0,
                `details` TEXT NULL,
                `intro` TEXT NULL,
                `image` varchar(255) NULL,
                `thumb` varchar(255) NULL,
                `cover` varchar(255) NULL,
                `remarks` TEXT NULL,
                `display_order` int(11) NULL DEFAULT 0,
                `status` int(11) NULL DEFAULT 0,
                `created_at` timestamp NULL DEFAULT NULL,
                `updated_at` timestamp NULL DEFAULT NULL,
                `createdby` int(11) DEFAULT NULL,
                `updatedby` int(11) DEFAULT NULL
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
              ");
            DB::statement("CREATE TABLE IF NOT EXISTS `tbl_services` (
                `service_id` int(11) NOT NULL  AUTO_INCREMENT PRIMARY KEY,
                `parent_service` int(11) DEFAULT 0,
                `title` varchar(250) NULL,
                `alias` varchar(250) NULL,
                `text` TEXT NULL,
                `link` varchar(500) NOT NULL,
                `cover_photo` varchar(500) NOT NULL,
                `display_order` int(11) NULL DEFAULT 0,
                `status` int(11) NULL DEFAULT 0,
                `created_at` timestamp NULL DEFAULT NULL,
                `updated_at` timestamp NULL DEFAULT NULL,
                `createdby` int(11) DEFAULT NULL,
                `updatedby` int(11) DEFAULT NULL
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
              ");
            DB::statement("CREATE TABLE IF NOT EXISTS `tbl_countries` (
                `country_id` int(11) NOT NULL  AUTO_INCREMENT PRIMARY KEY,
                `title` varchar(255) NULL,
                `alias` varchar(255) NULL,
                `text` TEXT NULL,
                `details` TEXT NULL,
                `cover_photo` varchar(255) NULL,
                `image_thumb` varchar(255) NULL,
                `display_order` int(11) NULL DEFAULT 0,
                `status` int(11) NULL DEFAULT 0,
                `created_at` timestamp NULL DEFAULT NULL,
                `updated_at` timestamp NULL DEFAULT NULL,
                `createdby` int(11) DEFAULT NULL,
                `updatedby` int(11) DEFAULT NULL
              ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
              ");
            DB::statement('
            CREATE TABLE IF NOT EXISTS `tbl_subscribers` (
                `subscriber_id` int(11) NOT NULL  AUTO_INCREMENT PRIMARY KEY,
                `title` varchar(255) NULL,
                `alias` varchar(250) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
                `email` varchar(255) NULL,
                `display_order` int(11) NULL DEFAULT 0,
                `status` int(11) NULL DEFAULT 0,
                `createdby` int(11) DEFAULT NULL,
                `updatedby` int(11) DEFAULT NULL,
                `created_at` timestamp NULL DEFAULT NULL,
                `updated_at` timestamp NULL DEFAULT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8
        ');
            DB::statement('
            CREATE TABLE IF NOT EXISTS `tbl_faqs` (
                `faq_id` int(11) NOT NULL  AUTO_INCREMENT PRIMARY KEY,
                `title` varchar(250) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
                `text` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
                `display_order` int(11) NULL DEFAULT 0,
                `status` int(11) NULL DEFAULT 0,
                `createdby` int(11) DEFAULT NULL,
                `updatedby` int(11) DEFAULT NULL,
                `created_at` timestamp NULL DEFAULT NULL,
                `updated_at` timestamp NULL DEFAULT NULL
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
        ');
            DB::statement('
            CREATE TABLE IF NOT EXISTS `tbl_successstories` (
                `successstory_id` int(11) NOT NULL  AUTO_INCREMENT PRIMARY KEY,
                `countries_id` int(11) DEFAULT NULL,
                `title` varchar(255) DEFAULT NULL,
                `alias` varchar(200) DEFAULT NULL,
                `image` varchar(255) DEFAULT NULL,
                `text` text DEFAULT NULL,
                `remarks` varchar(255) DEFAULT NULL,
                `display_order` int(11) DEFAULT NULL,
                `status` int(11) DEFAULT NULL,
                `university` varchar(255) DEFAULT NULL,
                `faculty` varchar(255) DEFAULT NULL,
                `createdby` varchar(255) DEFAULT NULL,
                `created_at` varchar(255) DEFAULT NULL,
                `updatedby` varchar(255) DEFAULT NULL,
                `updated_at` varchar(255) DEFAULT NULL
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
        ');
            DB::statement('
            CREATE TABLE IF NOT EXISTS `tbl_qrscans` (
                `qrscan_id` int(11) NOT NULL  AUTO_INCREMENT PRIMARY KEY,
                `registrations_id` int(11) DEFAULT NULL,
                `qrscan_type` varchar(255) DEFAULT NULL,
                `qrscan_location` varchar(255) DEFAULT NULL,
                `remarks` varchar(255) DEFAULT NULL,
                `display_order` int(11) DEFAULT NULL,
                `status` int(11) DEFAULT NULL,
                `createdby` varchar(255) DEFAULT NULL,
                `created_at` varchar(255) DEFAULT NULL,
                `updatedby` varchar(255) DEFAULT NULL,
                `updated_at` varchar(255) DEFAULT NULL
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
        ');
            DB::statement("CREATE TABLE IF NOT EXISTS tbl_sources (
            source_id INT(11) AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(255),
            alias VARCHAR(255),
            name VARCHAR(255),
            description TEXT,
            display_order INT(11),
            status INT(11),
            remarks TEXT,
            created_at DATETIME,
            createdby INT(11),
            updated_at DATETIME,
            updatedby INT(11)
        );
        ");
            DB::statement("CREATE TABLE IF NOT EXISTS tbl_registrations (
            registration_id INT(11) AUTO_INCREMENT PRIMARY KEY,
            campaigns_id INT(11),
            sources_id INT(11),
            countries_id INT(11),
            provinces_id INT(11),
            districts_id INT(11),
            cities_id INT(11),
            leadcategories_id INT(11),
            agents_id INT(11),
            name VARCHAR(255),
            email VARCHAR(255),
            phone VARCHAR(255),
            mobile VARCHAR(255),
            address VARCHAR(255),
            qualifications_id INT(11),
            see_year VARCHAR(255),
            see_grade VARCHAR(255),
            see_stream VARCHAR(255),
            see_school VARCHAR(255),
            plus2_year VARCHAR(255),
            plus2_grade VARCHAR(255),
            plus2_stream VARCHAR(255),
            plus2_college VARCHAR(255),
            bachelors_year VARCHAR(255),
            bachelors_grade VARCHAR(255),
            bachelors_stream VARCHAR(255),
            bachelors_college VARCHAR(255),
            highest_qualification VARCHAR(255),
            highest_grade VARCHAR(255),
            highest_year VARCHAR(255),
            highest_stream VARCHAR(255),
            highest_college VARCHAR(255),
            preparation_class VARCHAR(255),
            preparation_score VARCHAR(255),
            preparation_bandscore VARCHAR(255),
            preparation_date VARCHAR(255),
            preffered_location TEXT,
            intrested_for_country TEXT,
            intrested_course TEXT,
            user_agent TEXT,
            tags TEXT,
            coupen_code VARCHAR(255),
            display_order INT(11),
            status INT(11),
            remarks TEXT,
            created_at DATETIME,
            createdby INT(11),
            updated_at DATETIME,
            updatedby INT(11)
        );
        ");
            DB::statement("CREATE TABLE IF NOT EXISTS tbl_students (
            student_id INT(11) AUTO_INCREMENT PRIMARY KEY,
            campaigns_id INT(11) NULL,
            sources_id INT(11) NULL,
            leadcategories_id INT(11) NULL,
            agents_id INT(11) NULL,
    countries_id INT(11) NULL,
    name VARCHAR(255) NULL,
    email VARCHAR(255) NULL,
    phone VARCHAR(255) NULL,
    mobile VARCHAR(255) NULL,
    address VARCHAR(255) NULL,
    dob VARCHAR(255) NULL,
    gender VARCHAR(255) NULL,
    martial_status VARCHAR(255) NULL,
    guardian_name VARCHAR(255) NULL,
    guardian_contact VARCHAR(255) NULL,
    see_year VARCHAR(255) NULL,
    see_grade VARCHAR(255) NULL,
    see_stream VARCHAR(255) NULL,
    see_school VARCHAR(255) NULL,
    plus2_year VARCHAR(255) NULL,
    plus2_grade VARCHAR(255) NULL,
    plus2_stream VARCHAR(255) NULL,
    plus2_college VARCHAR(255) NULL,
    bachelors_year VARCHAR(255) NULL,
    bachelors_grade VARCHAR(255) NULL,
    bachelors_stream VARCHAR(255) NULL,
    bachelors_college VARCHAR(255) NULL,
    highest_qualification VARCHAR(255) NULL,
    highest_grade VARCHAR(255) NULL,
    highest_stream VARCHAR(255) NULL,
    highest_college VARCHAR(255) NULL,
    work_experience TEXT NULL,
    extra_skills text NULL,
    how_know_us text NULL,
    preparation_class VARCHAR(255) NULL,
    preparation_score VARCHAR(255) NULL,
    preparation_bandscore VARCHAR(255) NULL,
    preparation_date VARCHAR(255) NULL,
    preffered_location TEXT NULL,
    intrested_for_country TEXT NULL,
    intrested_course TEXT NULL,
    user_agent TEXT NULL,
    tags TEXT NULL,
    coupen_code VARCHAR(255) NULL,
    display_order INT(11) NULL,
    status INT(11) NULL,
    remarks TEXT NULL,
    created_at DATETIME NULL,
    createdby INT(11) NULL,
    updated_at DATETIME NULL,
    updatedby INT(11) NULL
        );
        ");
            DB::statement("CREATE TABLE IF NOT EXISTS tbl_followups (
            followup_id INT(11) AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(255),
            alias VARCHAR(255),
            registrations_id INT(11),
            parent_followup INT(11),
            followup_date DATETIME,
            followup_response VARCHAR(255),
            followup_by VARCHAR(255),
            next_schedule DATETIME,
            display_order INT(11),
            status INT(11),
            remarks TEXT,
            created_at DATETIME,
            createdby INT(11),
            updated_at DATETIME,
            updatedby INT(11)
        );
        ");
            DB::statement("CREATE TABLE IF NOT EXISTS tbl_documentsets (
            documentset_id INT(11) AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(255),
            alias VARCHAR(255),
            description TEXT,
            display_order INT(11),
            status INT(11),
            remarks TEXT,
            created_at DATETIME,
            createdby INT(11),
            updated_at DATETIME,
            updatedby INT(11)
        );");
            DB::statement("CREATE TABLE IF NOT EXISTS tbl_requireddocuments (
            requireddocument_id INT(11) AUTO_INCREMENT PRIMARY KEY,
            documentsets_id INT(11),
            title VARCHAR(255),
            alias VARCHAR(255),
            photo VARCHAR(255),
            description TEXT,
            display_order INT(11),
            status INT(11),
            remarks TEXT,
            created_at DATETIME,
            createdby INT(11),
            updated_at DATETIME,
            updatedby INT(11)
        );");
            DB::statement("CREATE TABLE IF NOT EXISTS tbl_options (
            option_id INT(11) AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(255),
            alias VARCHAR(255),
            students_id INT(11),
            programs_id INT(11),
            advice TEXT,
            description TEXT,
            display_order INT(11),
            status INT(11),
            remarks TEXT,
            created_at DATETIME,
            createdby INT(11),
            updated_at DATETIME,
            updatedby INT(11)
        );");
            DB::statement("CREATE TABLE IF NOT EXISTS tbl_spinthewheelwinners (
            spinthewheelwinner_id INT(11) AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(255),
            alias VARCHAR(255),
            registrations_id INT(11),
            prize TEXT,
            display_order INT(11),
            status INT(11),
            remarks TEXT,
            created_at DATETIME,
            createdby INT(11),
            updated_at DATETIME,
            updatedby INT(11)
        );");
            DB::statement("CREATE TABLE IF NOT EXISTS tbl_offerapplications (
            offerapplication_id INT(11) AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(255),
            alias VARCHAR(255),
            students_id INT(11),
            programs_id INT(11),
            advice TEXT,
            requireddocuments_id VARCHAR(255),
            additional_documents VARCHAR(255),
            application_date DATE,
            application_fee DOUBLE,
            applicationfee_deadline DATE,
            applicationfee_paid BOOLEAN,
            applicationfee_paymentdate DATE,
            applicationfee_paymentremarks DATE,
            offer_status VARCHAR(255),
            offer_letter VARCHAR(255),
            offerletter_date DATE,
            offered_conditions VARCHAR(255),
            description TEXT,
            display_order INT(11),
            status INT(11),
            remarks TEXT,
            created_at DATETIME,
            createdby INT(11),
            updated_at DATETIME,
            updatedby INT(11)
        );");
            DB::statement("CREATE TABLE IF NOT EXISTS tbl_admittedpplications (
            admittedpplication_id INT(11) AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(255),
            alias VARCHAR(255),
            students_id INT(11),
            offerapplications_id INT(11),
            admission_date DATE,
            admission_fee DOUBLE,
            admissionfee_deadline DATE,
            admissionfee_paid BOOLEAN,
            admissionfee_paymentdate DATE,
            admissionee_paymentremarks DATE,
            admission_status VARCHAR(255),
            admission_letter VARCHAR(255),
            admission_conditions text,
            visa_status VARCHAR(255),
            visa_letter VARCHAR(255),
            visaapplication_date DATE,
            visaissue_date DATE,
            visa_conditions VARCHAR(255),
            commencement_status VARCHAR(255),
            commencement_letter VARCHAR(255),
            commencement_date DATE,
            commencement_conditions VARCHAR(255),
            description TEXT,
            display_order INT(11),
            status INT(11),
            remarks TEXT,
            created_at DATETIME,
            createdby INT(11),
            updated_at DATETIME,
            updatedby INT(11)
        );");
            DB::statement("CREATE TABLE IF NOT EXISTS tbl_intakes (
            intake_id INT(11) AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(255),
            alias VARCHAR(255),
            description TEXT,
            display_order INT(11),
            status INT(11),
            remarks TEXT,
            created_at DATETIME,
            createdby INT(11),
            updated_at DATETIME,
            updatedby INT(11)
        );");
            DB::statement("CREATE TABLE IF NOT EXISTS tbl_programlevels (
            programlevel_id INT(11) AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(255),
            alias VARCHAR(255),
            description TEXT,
            display_order INT(11),
            status INT(11),
            remarks TEXT,
            created_at DATETIME,
            createdby INT(11),
            updated_at DATETIME,
            updatedby INT(11)
        );");
            DB::statement("CREATE TABLE IF NOT EXISTS tbl_roles (
            role_id INT(11) AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(255),
            alias VARCHAR(255),
            description TEXT,
            display_order INT(11),
            status INT(11),
            remarks TEXT,
            created_at DATETIME,
            createdby INT(11),
            updated_at DATETIME,
            updatedby INT(11)
        );");
            DB::statement("CREATE TABLE IF NOT EXISTS tbl_permissions (
            permission_id INT(11) AUTO_INCREMENT PRIMARY KEY,
           title VARCHAR(255),
           alias VARCHAR(255),
           modal VARCHAR(255),
            command VARCHAR(255),
            created_at DATETIME,
            createdby INT(11),
            updated_at DATETIME,
            updatedby INT(11),
            status INT(11)

        );");
            DB::statement("CREATE TABLE IF NOT EXISTS tbl_progressstatuses (
            status_id INT(11) AUTO_INCREMENT PRIMARY KEY,
           title VARCHAR(255),
           alias VARCHAR(255),
            display_order int(11),
            created_at DATETIME,
            createdby INT(11),
            updated_at DATETIME,
            updatedby INT(11),
            status INT(11)

        );");
            DB::statement("CREATE TABLE IF NOT EXISTS tbl_coops (
            coop_id INT(11) AUTO_INCREMENT PRIMARY KEY,
           title VARCHAR(255),
           alias VARCHAR(255),
            display_order int(11),
            created_at DATETIME,
            createdby INT(11),
            updated_at DATETIME,
            updatedby INT(11),
            status INT(11)

        );");
            DB::statement("CREATE TABLE IF NOT EXISTS tbl_campaignlocations (
            location_id INT(11) AUTO_INCREMENT PRIMARY KEY,
            campaigns_id INT(11),
           title VARCHAR(255),
           alias VARCHAR(255),
           location_address VARCHAR(255),
           event_date DATETIME,
            display_order int(11),
            created_at DATETIME,
            createdby INT(11),
            updated_at DATETIME,
            updatedby INT(11),
            status INT(11)

        );");
            DB::statement("CREATE TABLE IF NOT EXISTS tbl_rolepermissions (
                rolepermission_id INT(11) AUTO_INCREMENT PRIMARY KEY,
                roles_id INT(11),
                permissions_id INT(11),
                display_order INT(11),
                remarks VARCHAR(255),
                created_at DATETIME,
                createdby INT(11),
                updated_at DATETIME,
                updatedby INT(11),
                status INT(11)
            );");
            DB::statement("UPDATE tbl_programs set intake = '[\"1\"]' where intake='May 2024';");
            DB::statement("UPDATE tbl_programs set intake = '[\"2\"]' where intake='September 2024';");
            DB::statement("UPDATE tbl_programs set intake = '[\"2\"]' where intake='September';");
            DB::statement("UPDATE tbl_programs set intake = '[\"1\",\"2\"]' where intake='May/Sept 2024';");
            DB::statement("UPDATE tbl_programs set intake = '[\"1\",\"2\"]' where intake='May/Sept';");
            if (Schema::hasColumn('offerapplications', 'requireddocuments_id')) {
                DB::statement("ALTER TABLE tbl_offerapplications CHANGE requireddocuments_id required_documents TEXT;");
            }
            if (Schema::hasColumn('users', 'role')) {
                DB::statement("ALTER TABLE tbl_users CHANGE role roles_id TEXT;");
            }
            if (Schema::hasColumn('campaigns', 'seo_keywords')) {
                DB::statement("ALTER TABLE tbl_campaigns CHANGE seo_keywords seo_keywords TEXT;");
            }
            DB::statement("ALTER TABLE tbl_offerapplications CHANGE 	additional_documents 	additional_documents TEXT;");
            DB::statement("ALTER TABLE tbl_offerapplications CHANGE 	application_fee 	application_fee VARCHAR(255);");


            if (!(DB::table('programlevels')->first())) {
                DB::statement("INSERT INTO `tbl_programlevels` (`title`, `description`, `status`) VALUES ('PG Diploma /Certificate', 'pg_cert', '1');");
                DB::statement("INSERT INTO `tbl_programlevels` (`title`, `description`, `status`) VALUES ('Certificate', 'cert', '1');");
                DB::statement("INSERT INTO `tbl_programlevels` (`title`, `description`, `status`) VALUES ('Diploma', 'diploma', '1');");
                DB::statement("INSERT INTO `tbl_programlevels` (`title`, `description`, `status`) VALUES ('Postgraduate', 'pg', '1');");
                DB::statement("INSERT INTO `tbl_programlevels` (`title`, `description`, `status`) VALUES ('Fast Track', 'fast_track', '1');");
                DB::statement("INSERT INTO `tbl_programlevels` (`title`, `description`, `status`) VALUES ('UG+PG (Accelerated) Degree', 'ug_pg_accelerated', '1');");
                DB::statement("INSERT INTO `tbl_programlevels` (`title`, `description`, `status`) VALUES ('Advanced Diploma', 'advanced_diploma', '1');");
                DB::statement("INSERT INTO `tbl_programlevels` (`title`, `description`, `status`) VALUES ('PG Diploma / Degree', 'pg_diploma_degree', '1');");
            }
            if (!(DB::table('settings')->first())) {
                DB::statement("INSERT INTO `tbl_settings` (`title`, `description`, `status`) VALUES ('Bibhuti LMS', '', '1');");
            }
            if (!(DB::table('campaigns')->first())) {
                DB::statement("INSERT INTO `tbl_campaigns` (`title`,`alias`,`status`) VALUES ('My First Campaign','my_first_campaign', '1');");
            }
            if (!(DB::table('countries')->first())) {
                DB::statement("INSERT INTO `tbl_countries` (`title`,`alias`,`status`) VALUES ('Not Specific','not_specific', '1');");
            }
            if (!(DB::table('leadcategories')->first())) {
                DB::statement("INSERT INTO `tbl_leadcategories` (`title`,`alias`,`status`) VALUES ('Raw Lead','raw_lead','1');");
            }
            if (!(DB::table('intakes')->first())) {
                DB::statement("INSERT INTO `tbl_intakes` (`title`,`alias`,`status`) VALUES ('May 2024','may-2024','1');");
                DB::statement("INSERT INTO `tbl_intakes` (`title`,`alias`,`status`) VALUES ('Sept 2024','sept-2024','1');");
                DB::statement("INSERT INTO `tbl_intakes` (`title`,`alias`,`status`) VALUES ('March 2024','march-2024','1');");
                DB::statement("INSERT INTO `tbl_intakes` (`title`,`alias`,`status`) VALUES ('April 2024','april-2024','1');");
            }
            if (!(DB::table('coops')->first())) {
                DB::statement("INSERT INTO `tbl_coops` (`title`,`alias`,`status`) VALUES ('Not Offered','not_offered','1');");
                DB::statement("INSERT INTO `tbl_coops` (`title`,`alias`,`status`) VALUES ('Open','open','1');");
                DB::statement("INSERT INTO `tbl_coops` (`title`,`alias`,`status`) VALUES ('Waitlisted','waitlisted','1');");
                DB::statement("INSERT INTO `tbl_coops` (`title`,`alias`,`status`) VALUES ('Close','close','1');");
            }
            if (!(DB::table('sources')->first())) {
                DB::statement("INSERT INTO `tbl_sources` (`title`,`alias`,`status`) VALUES ('Direct','direct','1');");
                DB::statement("INSERT INTO `tbl_sources` (`title`,`alias`,`status`) VALUES ('Office Visits','office','1');");
                DB::statement("INSERT INTO `tbl_sources` (`title`,`alias`,`status`) VALUES ('Online Registrations','online','1');");
            }
            if (!(DB::table('roles')->first())) {
                DB::statement("INSERT INTO `tbl_roles` (`title`,`alias`,`status`) VALUES ('Admin','admin','1');");
                DB::statement("INSERT INTO `tbl_roles` (`title`,`alias`,`status`) VALUES ('Manager','manager','1');");
                DB::statement("INSERT INTO `tbl_roles` (`title`,`alias`,`status`) VALUES ('Branch','branch','1');");
                DB::statement("INSERT INTO `tbl_roles` (`title`,`alias`,`status`) VALUES ('Agent','agent','1');");
                DB::statement("INSERT INTO `tbl_roles` (`title`,`alias`,`status`) VALUES ('Student','student','1');");
            }

            if (!Schema::hasColumn('students', 'branches_id')) {
                Schema::table('students', function (Blueprint $table) {
                    // Add the new column to the table
                    $table->integer('branches_id')->nullable();
                });
            }
            if (!Schema::hasColumn('settings', 'address')) {
                Schema::table('settings', function (Blueprint $table) {
                    // Add the new column to the table
                    $table->text('address')->nullable();
                });
            }
            if (!Schema::hasColumn('programs', 'scholarship')) {
                Schema::table('programs', function (Blueprint $table) {
                    // Add the new column to the table
                    $table->string('scholarship')->nullable();
                });
            }
            if (!Schema::hasColumn('programs', 'documentsets_id')) {
                Schema::table('programs', function (Blueprint $table) {
                    // Add the new column to the table
                    $table->integer('documentsets_id')->nullable();
                });
            }
            if (!Schema::hasColumn('programs', 'program_url')) {
                Schema::table('programs', function (Blueprint $table) {
                    // Add the new column to the table
                    $table->string('program_url')->nullable();
                });
            }
            if (!Schema::hasColumn('programs', 'programlevels_id')) {
                Schema::table('programs', function (Blueprint $table) {
                    // Add the new column to the table
                    $table->string('programlevels_id')->nullable();
                });
            }
            if (!Schema::hasColumn('programs', 'code')) {
                Schema::table('programs', function (Blueprint $table) {
                    // Add the new column to the table
                    $table->string('code')->nullable();
                });
            }
            if (!Schema::hasColumn('programs', 'coops_id')) {
                Schema::table('programs', function (Blueprint $table) {
                    // Add the new column to the table
                    $table->string('coops_id')->nullable();
                });
            }
            if (!Schema::hasColumn('offerapplications', 'applicationfee_remarks')) {
                Schema::table('offerapplications', function (Blueprint $table) {
                    // Add the new column to the table
                    $table->text('applicationfee_remarks')->nullable();
                });
            }
            if (!Schema::hasColumn('users', 'branches_id')) {
                Schema::table('users', function (Blueprint $table) {
                    // Add the new column to the table
                    $table->integer('branches_id')->nullable();
                });
            }
            if (!Schema::hasColumn('users', 'agents_id')) {
                Schema::table('users', function (Blueprint $table) {
                    // Add the new column to the table
                    $table->integer('agents_id')->nullable();
                });
            }
            if (!Schema::hasColumn('users', 'students_id')) {
                Schema::table('users', function (Blueprint $table) {
                    // Add the new column to the table
                    $table->integer('students_id')->nullable();
                });
            }
            if (!Schema::hasColumn('requireddocuments', 'documentsets_id')) {
                Schema::table('requireddocuments', function (Blueprint $table) {
                    // Add the new column to the table
                    $table->integer('documentsets_id')->nullable();
                });
            }
            if (!Schema::hasColumn('requireddocuments', 'photo')) {
                Schema::table('requireddocuments', function (Blueprint $table) {
                    // Add the new column to the table
                    $table->string('photo')->nullable();
                });
            }
            if (!Schema::hasColumn('campaignarticles', 'sub_title')) {
                Schema::table('campaignarticles', function (Blueprint $table) {
                    // Add the new column to the table
                    $table->string('sub_title')->nullable()->after('title');
                });
            }

            $initialized = true;
        }
    }
}