<?php
/**
 * Created by PhpStorm.
 * User: foda
 * Date: 1/18/2019
 * Time: 6:44 PM
 */

namespace App;

use Illuminate\Database\Eloquent\Model;
use App\Brands;
use App\Systems;
use App\Items;
use Illuminate\Database\Eloquent\SoftDeletes;
use Illuminate\Support\Str;

class Suppliers extends model
{
    protected $table = 'suppliers';

    public static function verifyPassword($userName, $password)
    {
        $supplierData = \DB::selectOne("SELECT id,deleted_at FROM suppliers WHERE user_name=? AND password=?", [$userName,$password]);

        if (!$supplierData)
            return 'WRONGCREDENTIALS';

        if ($supplierData->deleted_at)
            return 'INACTIVEUSER';

        $token = sha1($supplierData->id . Str::random(20));
        \DB::update("UPDATE suppliers SET token=? WHERE id=?", [$token, $supplierData->id]);
        return $token;
    }

    public static function getSupplierByUserName($userName)
    {
        return \DB::table('suppliers')
            ->select([
                '*'
            ])
            ->where('user_name', '=', $userName)
            ->first();
    }

    public static function createSupplier($request)
    {
        return \DB::table('suppliers')->insert([
            'user_name' => $request['userName'],
            'password' => $request['password'],
            'first_name' => $request['firstName'],
            'last_name' => $request['lastName'],
            'email' => $request['email'],
            'phone' => $request['phone'],
            'fax' => $request['fax'],
            'companyName' => $request['companyName'],
            'area_id' => $request['areaId'],
            'field_id' => $request['fieldId'],
            'deleted_at' => date('Y-m-d')
        ]);
    }

    public static function getAllSuppliers()
    {
        $suppliers = \DB::table('suppliers as s')
            ->select([
                's.id',
                \DB::raw('CONCAT("http://'.$_SERVER["SERVER_NAME"].'/",s.logo) AS logo'),
                's.companyName AS supplier_name',
            ])
            ->whereNull('s.deleted_at')
            ->get();

        $output = [];
        foreach ($suppliers as $supplier) {
            $supplier->badges = Brands::getBrandBadges($supplier->id);
            $output[] = $supplier;
        }
        return $output;
    }

    public static function getAllSuppliersAdmin()
    {
        $suppliers = \DB::table('suppliers as s')
            ->select([
                's.id',
                \DB::raw('CONCAT("http://'.$_SERVER["SERVER_NAME"].'/",s.logo) AS logo'),
                's.companyName AS supplier_name',
                \DB::raw('CASE WHEN s.deleted_at IS NULL THEN TRUE ELSE FALSE END AS active')
            ])
            ->get();

        $output = [];
        foreach ($suppliers as $supplier) {
            $supplier->badges = Brands::getBrandBadges($supplier->id);
            $output[] = $supplier;
        }
        return $output;
    }

    public static function getAllSubSubCategorySuppliers($subSubCategoryId)
    {
        $suppliers = \DB::table('suppliers as s')
            ->select([
                's.id',
                \DB::raw('CONCAT("http://'.$_SERVER["SERVER_NAME"].'/",s.logo) AS logo'),
                's.companyName AS supplier_name',
            ])
            ->join('supplier_system as ss', 's.id', '=', 'ss.supplier_id')
            ->join('brand_systems as bs', 'bs.id', '=', 'ss.brand_system_id')
            ->join('systems as sy', 'sy.id', '=', 'bs.system_id')
            ->whereNull('s.deleted_at')
            ->where('sy.sub_sub_category_id','=',$subSubCategoryId)
            ->groupBy('s.id')
            ->get();

        $output = [];
        foreach ($suppliers as $supplier) {
            $supplier->badges = Brands::getBrandBadges($supplier->id);
            $output[] = $supplier;
        }
        return $output;
    }

    public static function getAllMainCategorySuppliers($mainCategoryId)
    {
        $suppliers = \DB::table('suppliers as s')
            ->select([
                's.id',
                \DB::raw('CONCAT("http://'.$_SERVER["SERVER_NAME"].'/",s.logo) AS logo'),
                's.companyName AS supplier_name',
            ])
            ->join('supplier_system as ss', 's.id', '=', 'ss.supplier_id')
            ->join('brand_systems as bs', 'bs.id', '=', 'ss.brand_system_id')
            ->join('systems as sy', 'sy.id', '=', 'bs.system_id')
            ->join('sub_sub_category as ssc', 'ssc.id', '=', 'sy.sub_sub_category_id')
            ->join('sub_category as sc', 'sc.id', '=', 'ssc.sub_category_id')
            ->join('main_category as mc', 'mc.id', '=', 'sc.main_category_id')
            ->where('mc.id','=',$mainCategoryId)
            ->whereNull('s.deleted_at')
            ->groupBy('s.id')
            ->get();

        $output = [];
        foreach ($suppliers as $supplier) {
            $supplier->badges = Brands::getBrandBadges($supplier->id);
            $output[] = $supplier;
        }
        return $output;
    }

    public static function getAllSubCategorySuppliers($subCategoryId)
    {
        $suppliers = \DB::table('suppliers as s')
            ->select([
                's.id',
                \DB::raw('CONCAT("http://'.$_SERVER["SERVER_NAME"].'/",s.logo) AS logo'),
                's.companyName AS supplier_name',
            ])
            ->join('supplier_system as ss', 's.id', '=', 'ss.supplier_id')
            ->join('brand_systems as bs', 'bs.id', '=', 'ss.brand_system_id')
            ->join('systems as sy', 'sy.id', '=', 'bs.system_id')
            ->join('sub_sub_category as ssc', 'ssc.id', '=', 'sy.sub_sub_category_id')
            ->join('sub_category as sc', 'sc.id', '=', 'ssc.sub_category_id')
            ->where('sc.id','=',$subCategoryId)
            ->whereNull('s.deleted_at')
            ->groupBy('s.id')
            ->get();

        $output = [];
        foreach ($suppliers as $supplier) {
            $supplier->badges = Brands::getBrandBadges($supplier->id);
            $output[] = $supplier;
        }
        return $output;
    }

    public static function getAllSystemSuppliers($systemId)
    {
        $suppliers = \DB::table('suppliers as s')
            ->select([
                's.id',
                \DB::raw('CONCAT("http://'.$_SERVER["SERVER_NAME"].'/",s.logo) AS logo'),
                's.companyName AS supplier_name',
            ])
            ->join('supplier_system as ss', 's.id', '=', 'ss.supplier_id')
            ->join('brand_systems as bs', 'bs.id', '=', 'ss.brand_system_id')
            ->join('systems as sy', 'sy.id', '=', 'bs.system_id')
            ->where('sy.id','=',$systemId)
            ->whereNull('s.deleted_at')
            ->groupBy('s.id')
            ->get();

        $output = [];
        foreach ($suppliers as $supplier) {
            $supplier->badges = Brands::getBrandBadges($supplier->id);
            $output[] = $supplier;
        }
        return $output;
    }

    public static function getSuppliersBySystemAndBrand($systemId,$brandId)
    {
        $suppliers = \DB::table('suppliers as s')
            ->select([
                's.id',
                \DB::raw('CONCAT("http://'.$_SERVER["SERVER_NAME"].'/",s.logo) AS logo'),
                's.companyName AS supplier_name',
            ])
            ->join('supplier_system as ss', 's.id', '=', 'ss.supplier_id')
            ->join('brand_systems as bs', 'bs.id', '=', 'ss.brand_system_id')
            ->where('bs.system_id','=',$systemId)
            ->where('bs.brand_id','=',$brandId)
            ->whereNull('s.deleted_at')
            ->groupBy('s.id')
            ->get();

        $output = [];
        foreach ($suppliers as $supplier) {
            $supplier->badges = Brands::getBrandBadges($supplier->id);
            $output[] = $supplier;
        }
        return $output;
    }

    public static function getSupplierDetails($supplierId)
    {
        $supplier = \DB::table('suppliers as s')
            ->select([
                's.id',
                \DB::raw('CONCAT("http://'.$_SERVER["SERVER_NAME"].'/",s.logo) AS logo'),
                's.companyName AS supplier_name',
                's.address',
                's.email',
                's.phone',
                \DB::raw('CONCAT("http://'.$_SERVER["SERVER_NAME"].'/",s.pdf_link) AS pdf_link'),
                'a.area',
                'f.field',
                \DB::raw('CONCAT("http://'.$_SERVER["SERVER_NAME"].'/",s.banner) AS banner')
            ])
            ->join('areas as a', 'a.id', '=', 's.area_id')
            ->join('fields as f', 'f.id', '=', 's.field_id')
            ->where('s.id','=',$supplierId)
            ->whereNull('s.deleted_at')
            ->first();

        $supplier->badges = Brands::getBrandBadges($supplier->id);
        return $supplier;
    }

    public static function updateSupplierStatus($supplierId,$status)
    {
        return \DB::table('suppliers')
            ->where('id', '=', $supplierId)
            ->update(['deleted_at' => $status == true ? null : date('Y-m-d')]);
    }

    public static function addBrandsAndSystemsForSupplier($brandId,$systemId,$supplierId)
    {
        $brandSystemId = Items::getBrandSystemId($brandId,$systemId);

        return \DB::table('supplier_system')->insert([
            'supplier_id' => $supplierId,
            'brand_system_id' => $brandSystemId
        ]);
    }

    public static function deleteSystemsAssignedToSupplier($supplierSystemId)
    {
        return \DB::table('supplier_system')
            ->where('id', '=', $supplierSystemId)
            ->delete();
    }

    public static function updateSupplier($request,$supplierId)
    {
        $supplier = self::whereId($supplierId)->first();

        $canDelete = true;
        if(basename($supplier->logo) != basename($request['logo']))
        {
            $canDelete = Util::deleteFile($supplier->logo);
        }

        if(basename($supplier->pdf_link) != basename($request['pdfLink']))
        {
            $canDelete = Util::deleteFile($supplier->pdf_link);
        }

        if(basename($supplier->banner) != basename($request['banner']))
        {
            $canDelete = Util::deleteFile($supplier->banner);
        }

        if($canDelete) {
            return \DB::table('suppliers')
                ->where('id','=',$supplierId)
                ->update([
                    'first_name' => $request['firstName'],
                    'last_name' => $request['lastName'],
                    'companyName' => $request['companyName'],
                    'user_name' => $request['userName'],
                    'password' => $request['password'],
                    'email' => $request['email'],
                    'phone' => $request['phone'],
                    'logo' => env('supplierLogoPath') .basename($request['logo']),
                    'description' => $request['description'],
                    'address' => $request['address'],
                    'fax' => $request['fax'],
                    'area_id' => $request['areaId'],
                    'field_id' => $request['fieldId'],
                    'pdf_link' => env('supplierPDFPath') .basename($request['pdfLink']),
                    'banner' => env('supplierBannerPath') .basename($request['banner'])
                ]);
        } else {
            return false;
        }
    }

    public static function getSupplierData($supplierId)
    {
        return \DB::table('suppliers')
            ->select([
                'id',
                'first_name as firstName',
                'last_name as lastName',
                'companyName',
                'user_name as userName',
                'password',
                'email',
                'phone',
                \DB::raw('CONCAT("http://'.$_SERVER["SERVER_NAME"].'/",logo) AS logo'),
                'description',
                'address',
                'fax',
                'area_id as areaId',
                'field_id as fieldId',
                \DB::raw('CONCAT("http://'.$_SERVER["SERVER_NAME"].'/",pdf_link) AS pdfLink'),
                \DB::raw('CONCAT("http://'.$_SERVER["SERVER_NAME"].'/",banner) AS banner')
            ])
            ->where('id','=',$supplierId)
            ->first();
    }

    //<editor-fold defaultstate="collapsed" desc="table structure">

    public static function whereId($id)
    {
        return \DB::table('suppliers')
            ->select([
                '*'
            ])
            ->where('id', '=', $id);
    }

    public static function whereEmail($email)
    {
        return \DB::table('suppliers')
            ->select([
                '*'
            ])
            ->where('email', '=', $email);
    }

    public static function whereUserName($userName)
    {
        return \DB::table('suppliers')
            ->select([
                '*'
            ])
            ->where('user_name', '=', $userName);
    }

    public static function whereToken($token)
    {
        return \DB::table('suppliers')
            ->select([
                '*'
            ])
            ->where('token', '=', $token);
    }

    //</editor-fold>

}