<?php

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

class CCMS
{

    public function __construct()
    {
        $this->initDB();
    }


    //CONSULTANCY RELATED TEMP FUNCTIONS
    public static  function getFAQs()
    {
        $Rows = DB::table("faqs")
            ->where("status", 1)
            ->orderBy("display_order")->get();
        return $Rows;
    }
    public static  function getServices()
    {
        $Rows = DB::table("services")
            ->where("status", 1)
            ->orderBy("display_order")->get();
        return $Rows;
    }
    public static  function getSliders()
    {
        $Rows = DB::table("sliders")
            ->where("status", 1)
            ->orderBy("display_order")->get();
        return $Rows;
    }
    public static  function getAbout()
    {
        $Rows = DB::table("articles")
            ->where("status", 1)
            ->where("parent_article", 0)
            ->orderBy("display_order")->get();
        return $Rows;
    }
    public static function getArticlesByParentAlias($alias)
    {
        $Rows = DB::table("articles")
            ->where("status", 1)
            ->where("parent_article", function ($query) use ($alias) {
                $query->select('article_id')
                    ->from('articles')
                    ->where('alias', $alias);
            })
            ->orderBy("display_order")
            ->get();
        return $Rows;
    }

    public static  function getContacts($index = -1)
    {
        $Rows = DB::table("contacts")->where("status", 1)->orderBy("display_order")->get();
        if ($index != -1) return $Rows[$index];
        return $Rows;
    }
    public static  function getContactByBranch($branches_id)
    {
        $Rows = DB::table("contacts")->where("status", 1)->where("branches_id", $branches_id)->orderBy("display_order")->first();
        return $Rows;
    }
    public static  function getQuickLinks()
    {
        $Rows = DB::table("quicklinks")->where("status", 1)->orderBy("display_order")->get();
        return $Rows;
    }
    public static  function getBranches()
    {
        $Rows = DB::table("branches")->where("status", 1)->orderBy("display_order")->get();
        return $Rows;
    }
    public static  function getBranchByID($branches_id)
    {
        $Row = DB::table("branches")->where("status", 1)->where("branch_id", $branches_id)->orderBy("display_order")->first();
        return $Row;
    }
    public static  function getStudyAbroad($limit = null)
    {
        $Rows = DB::table("countries")
            ->where("status", 1)
            ->orderBy("display_order");
        if ($limit !== null) {
            $Rows->limit($limit);
        }
        $Rows = $Rows->get();
        return $Rows;
    }
    public static function getTestPreparations($limit = null)
    {
        $query = DB::table("preparationclasses")
            ->where("status", 1)
            ->where("parent_preparationclass", 0)
            ->orderBy("display_order");
        if ($limit !== null) {
            $query->limit($limit);
        }
        $rows = $query->get();
        return $rows;
    }

    public static  function getResources()
    {
        $Rows = DB::table("resources")
            ->where("status", 1)
            ->where("parent_resource", 0)
            ->orderBy("display_order")->get();
        return $Rows;
    }
    public static  function getEvents()
    {
        $Rows = DB::table("events")
            ->where("status", 1)
            ->orderBy("display_order")->get();
        return $Rows;
    }

    public static  function getNews()
    {
        $Rows = DB::table("news")
            ->where("status", 1)
            ->orderBy("display_order")->get();
        return $Rows;
    }
    public static  function getTestimonials()
    {
        $Rows = DB::table("testimonials")->where("status", 1)->orderBy("display_order")->get();
        return $Rows;
    }
    public static  function getTestimonialsByBranchAlias($alias)
    {
        $Branch = DB::table("branches")->where("alias", $alias)->first();
        if ($Branch) {
            $Rows = DB::table("testimonials")
                ->where("status", 1)
                ->where("branches_id", $Branch->branch_id)
                ->orderBy("display_order")->get();
            return $Rows;
        } else {
            die("Branch not found");
        }
    }
    public static  function getVisas()
    {
        $Rows = DB::table("visas")->where("status", 1)->orderBy("display_order")->get();
        return $Rows;
    }

    public static  function getVisasByBranchAlias($alias)
    {
        $Branch = DB::table("branches")->where("alias", $alias)->first();
        if ($Branch) {
            $Rows = DB::table("visas")
                ->where("status", 1)
                ->where("branches_id", $Branch->branch_id)
                ->orderBy("display_order")->get();
            return $Rows;
        } else {
            die("Branch not found");
        }
    }
    public static  function getBlogs()
    {
        $Rows = DB::table("blogs")->where("status", 1)->orderBy("display_order")->get();
        return $Rows;
    }

    public static function getGalleries()
    {
        $Rows = DB::table('galleries')
            ->where('status', 1)->get();
        foreach ($Rows as $R) {
            $R->Photos = DB::table("photos")->where("galleries_id", $R->gallery_id)->where("status", 1)->orderBy("display_order")->get();
        }
        return $Rows;
    }
    public static function getCustomFields($start = 0, $limit = null)
    {
        $query = DB::table("customfields")
            ->where("status", 1)
            ->orderBy("display_order");

        if ($limit !== null) {
            $query->offset($start)->limit($limit);
        }

        $rows = $query->get();

        return $rows;
    }


    public static  function getTopReasonsForStudyDestination($alias, $limit = null)
    {
        $reasons_alias = 'top-reasons-to-' . $alias;
        $Rows = DB::table("countryarticles")
            ->where("status", 1)
            ->where("countries_id", function ($query) use ($alias) {
                $query->select("country_id")
                    ->from("countries")
                    ->where("alias", $alias);
            })
            ->where("parent_article", function ($query) use ($reasons_alias) {
                $query->select("article_id")
                    ->from("countryarticles")
                    ->where("alias", $reasons_alias);
            })
            ->orderBy("display_order")
            ->when($limit, function ($query) use ($limit) {
                return $query->limit($limit);
            })
            ->get();

        return $Rows;
    }
    public static  function getRepresentingInstitutionsByCountry($alias, $limit = null)
    {
        $Rows = DB::table("institutions")->where("status", 1)
            ->where("countries_id", function ($query) use ($alias) {
                $query->select("country_id")
                    ->from("countries")
                    ->where("alias", $alias);
            })
            ->orderBy("display_order")
            ->when($limit, function ($query) use ($limit) {
                return $query->limit($limit);
            })
            ->get();
        return $Rows;
    }
    //END 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 getslider()
    {
        $Gallery = DB::table('sliders')->where('status', 1)->orderBy('display_order')->get();
        return $Gallery;
    }


    public static function getarticle($alias = '')
    {
        $t = "select * from tbl_articles where status=1";

        if ($alias != '') {
            $t .= " and alias='$alias'";
            // echo $t;die;
            $Aboutus = DB::select($t)[0];
            $q = "select * from tbl_articles where parent_article=$Aboutus->article_id";
            $Aboutus->children = DB::select($q);
        } else {

            $Aboutus = DB::select($t);
        }
        //dd($Aboutus);die;
        return $Aboutus;
    }


    static public  function showMenu($menulocation_alias)
    {
        $MenuItems = CCMS::getMenuItems($menulocation_alias);

    ?>
        <ul class="navbar-nav d-flex align-items-center">
            <?php foreach ($MenuItems as $menuItem) : ?>
                <?php $menuItem->alias = str_replace("-", "_", $menuItem->alias); ?>
                <li class="nav-item <?php if (!empty($menuItem->children)) : ?> dropdown  <?php endif; ?>" id="myDropdown">
                    <a class="nav-link <?php if (!empty($menuItem->children)) : ?> dropdown-toggle  <?php endif; ?>" <?php if (!empty($menuItem->children)) : ?> data-bs-toggle="dropdown" <?php endif; ?> aria-current="page" href="<?php if ($menuItem->type != "") : ?>
          <?php echo route($menuItem->alias); ?><?php else : ?><?php echo site_url(ltrim($menuItem->ref, '/')); ?><?php endif; ?>" target="<?php echo strtolower($menuItem->target) ?>">

                        <?php echo $menuItem->title; ?>
                    </a>
                    <?php if (!empty($menuItem->children)) : ?>
                        <ul class="dropdown-menu">
                            <?php foreach ($menuItem->children as $menu) : ?>
                                <li> <a class="dropdown-item" href="<?php echo route($menuItem->alias . '.' . $menu->alias) ?>" target="<?php echo strtolower($menuItem->target); ?>"> <?php echo $menu->title; ?> </a></li>
                            <?php endforeach; ?>
                        </ul>
                    <?php endif; ?>
                </li>
            <?php endforeach; ?>
        </ul>
<?php
    }


    public static function getMenuItems($menulocation_alias, $parentId = 0)
    {
        $menulocations_id = DB::table('menulocations')
            ->where('alias', $menulocation_alias)
            ->value('menulocation_id');
        $menuItems = DB::table('menuitems')
            ->where('menulocations_id', $menulocations_id)
            ->where('parent_menu', $parentId)
            ->orderBy('display_order')
            ->get();
        $result = [];
        foreach ($menuItems as $menuItem) {
            $children = self::getMenuItems($menulocation_alias, $menuItem->menu_id);
            $menuItem->children = $children;
            $result[] = $menuItem;
        }
        return $result;
    }


    public static function getSiteVars()
    {
        $siteVars = DB::table("settings")->where('status', 1)->orderby('display_order')->first();
        $siteVars->Destinations = self::getStudyAbroad();
        $siteVars->Services = self::getServices();
        $siteVars->Classes = self::getTestPreparations();
        $siteVars->Abouts = self::getAbout();
        return $siteVars;
    }

    public static function getCountries()
    {
        $Countries = DB::table("countries")->where('status', 1)->orderby('display_order')->get();
        return $Countries;
    }

    public static function getCountry($alias)
    {
        $Countries = DB::table("countries")->where('alias', $alias)->get();
        return $Countries[0];
    }
    public static function getDemandCountries()
    {
        $demandCountries1 = DB::table('jobdemands')->where('status', 1)->orderby('display_order')->pluck('countries_id');
        $demandCountries2 = DB::table('paperdemands')->where('status', 1)->orderby('display_order')->pluck('countries_id');
        $demandCountries = $demandCountries1->concat($demandCountries2);
        //  pre($demandCountries2,true);
        $demandCountries = DB::table("countries")->where('status', 1)->orderby('display_order')->whereIn('country_id', $demandCountries)->get();
        return $demandCountries;
    }
    public static function getCertificates()
    {
        $Certificates = DB::table("certificates")->where('status', 1)->orderby('display_order')->get();
        return $Certificates;
    }
    public static function getCertificate($alias)
    {
        $Certificate = DB::table("certificates")->where('alias', $alias)->get()[0];
        return $Certificate;
    }
    public static function getTeams()
    {
        $Teams = DB::table("teams")->where('status', 1)->orderby('display_order')->get();
        return $Teams;
    }
    public static function getTeamsByBranch($alias)
    {
        $Branch = DB::table("branches")->where("alias", $alias)->first();
        if ($Branch) {
            $Teams = DB::table("teams")
                ->where('status', 1)
                ->where('branches_id', $Branch->branch_id)
                ->orderby('display_order')->get();
            return $Teams;
        } else {
            die("Branch Not Found");
        }
    }
    public static function getTeam($alias)
    {
        $Team = DB::table("teams")->where('alias', $alias)->get()[0];
        return $Team;
    }
    public static function getPaperDemands()
    {
        $PaperDemands = DB::table("paperdemands")->where('status', 1)->orderby('display_order')->get();
        return $PaperDemands;
    }
    public static function getPaperDemand($alias)
    {
        $PaperDemand = DB::table("paperdemands")->where('alias', $alias)->get()[0];
        return $PaperDemand;
    }
    public static function getJobCategories()
    {
        $JobCategories = DB::table("job_categories")->where('status', 1)->orderby('display_order')->get();
        return $JobCategories;
    }
    public static function getJobCategory($alias)
    {
        $JobCategory = DB::table("job_categories")->where('alias', $alias)->get()[0];
        return $JobCategory;
    }
    public static function getCompanies()
    {
        $Companies = DB::table("companies")->where('status', 1)->orderby('display_order')->get();
        return $Companies;
    }
    public static function getCompany($alias)
    {
        $Company = DB::table("companies")->where('alias', $alias)->get()[0];
        return $Company;
    }
    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>';
    }


    private function initDB()
    {

        static $initialized = false;
        if (!$initialized) {
            DB::statement('
            CREATE TABLE IF NOT EXISTS `tbl_subscribers` (
                `subscriber_id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `title` varchar(255) NOT NULL,
                `alias` varchar(250) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
                `email` varchar(255) NOT NULL,
                `display_order` int(11) NOT NULL,
                `status` int(11) NOT NULL,
                `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) NOT NULL,
                `status` int(11) NOT NULL,
                `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;

        ');
            $initialized = true;
        }
    }
}