<?php

namespace App\Http\Controllers\Admin;

use App\Exports\DownloadsExport;
use App\Exports\VisitorContactsExport;
use App\Exports\VisitorsExport;
use App\Exports\NewsletterSubscriptionsExport;
use App\Http\Controllers\Controller;
use App\Libs\Facades\Localization;
use Assessment;
use Illuminate\Pagination\Paginator;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Str;
use Maatwebsite\Excel\Facades\Excel;
use NewsletterSubscription;
use Visitor;
use VisitorContact;
use VisitorDownload;
use VisitorPageView;

class StatisticsController extends Controller
{
    public function getPageViews($visitorId = null)
    {
        $website = Localization::getWebsite();
        $visitor = null;
        if ($visitorId) {
            $visitor = Visitor::find($visitorId);
        }

        if ($website->id == 2) {
            return view('admin/pages/statistics/CF/page_views', array(
                'visitor' => $visitor
            ));
        } else {
            abort(404);
        }
    }

    public function getVisitors()
    {
        $website = Localization::getWebsite();
        if ($website->id == 2) {
            return view('admin/pages/statistics/CF/visitors');
        } else {
            return view('admin/pages/statistics/visitors');
        }
    }

    public function postListVisitors()
    {
        $website = Localization::getWebsite();
        $page = request()->input("current");
        $sort = request()->input("sort");
        $search = request()->input("searchPhrase");

        if (!empty($page)) {
            Paginator::currentPageResolver(function () use ($page) {
                return $page;
            });

            if (!empty($search)) {
                $query = Visitor::where(function ($query) use ($search) {
                    $query->orWhere('first_name', 'like', "%{$search}%")
                        ->orWhere('last_name', 'like', "%{$search}%");
                });
            } else {
                $query = Visitor::query();
            }
            $query->select(DB::raw('*, (SELECT count(*) FROM visitor_downloads WHERE visitor_id = visitors.id) as downloads'));
            $query->where('website_id', $website->id);
            if (empty($sort)) {
                $rows = $query->paginate(request()->input("rowCount"));
            } else {
                foreach ($sort as $field => $direction) {
                    if (empty($direction) || !Str::is(strtolower($direction), "desc")) {
                        $order_direction = "asc";
                    }
                    $rows = $query->orderBy($field, strtolower($direction))->paginate(request()->input("rowCount"));
                    break;
                }
            }

            $result = array(
                "total" => $rows->total(),
                "current" => $rows->currentPage(),
                "rowCount" => request()->input("rowCount"),
                "rows" => $rows->getCollection()
            );

            return response()->json($result);
        }
    }

    public function postListVisitorsCf()
    {
        $website = Localization::getWebsite();
        $page = request()->input("current");
        $sort = request()->input("sort");
        $search = request()->input("searchPhrase");

        if (!empty($page)) {
            Paginator::currentPageResolver(function () use ($page) {
                return $page;
            });
            if (!empty($search)) {
                $query = Visitor::where(function ($query) use ($search) {
                    $query->orWhere('first_name', 'like', "%{$search}%")
                        ->orWhere('last_name', 'like', "%{$search}%");
                });
            } else {
                $query = Visitor::query();
            }
            $query->select(DB::raw('*, (SELECT count(*) FROM sessions s WHERE s.visitor_id = visitors.id) as sessions_count, '
                .' (SELECT count(*) FROM visitor_page_views pv WHERE pv.visitor_id = visitors.id) as views_count, '
                .' (SELECT max(s.created_at) FROM sessions s WHERE s.visitor_id = visitors.id) as last_session '));
            $query->where('website_id', $website->id);
            if (empty($sort)) {
                $rows = $query->paginate(request()->input("rowCount"));
            } else {
                foreach ($sort as $field => $direction) {
                    if (empty($direction) || !Str::is(strtolower($direction), "desc")) {
                        $order_direction = "asc";
                    }
                    $rows = $query->orderBy($field, strtolower($direction))->paginate(request()->input("rowCount"));
                    break;
                }
            }

            $result = array(
                "total" => $rows->total(),
                "current" => $rows->currentPage(),
                "rowCount" => request()->input("rowCount"),
                "rows" => $rows->getCollection()
            );

            return response()->json($result);
        }
    }

    public function postListPageViews($visitorId = null)
    {
        $website = Localization::getWebsite();
        $page = request()->input("current");
        $sort = request()->input("sort");
        $search = request()->input("searchPhrase");

        $visitor = null;
        if ($visitorId) {
            $visitor = Visitor::find($visitorId);
        }

        if (!empty($page)) {
            Paginator::currentPageResolver(function () use ($page) {
                return $page;
            });
            if (!empty($search)) {
                $query = VisitorPageView::where(function ($query) use ($search) {
                    $query->orWhere('page', 'like', "%{$search}%")
                        ->orWhere('referer_page', 'like', "%{$search}%");
                });
            } else {
                $query = VisitorPageView::query();
            }
            $query->select(DB::raw('sessions.visit_number, visitor_page_views.*, visitors.email as visitor'));
            $query->join('visitors', 'visitors.id', '=', 'visitor_page_views.visitor_id');
            $query->join('sessions', 'sessions.id', '=', 'visitor_page_views.session_id');
            $query->where('website_id', $website->id);

            if ($visitor) {
                $query->where('visitors.id', $visitor->id);
            }

            if (empty($sort)) {
                $rows = $query->paginate(request()->input("rowCount"));
            } else {
                foreach ($sort as $field => $direction) {
                    if (empty($direction) || !Str::is(strtolower($direction), "desc")) {
                        $order_direction = "asc";
                    }
                    $rows = $query->orderBy($field, strtolower($direction))->paginate(request()->input("rowCount"));
                    break;
                }
            }

            $result = array(
                "total" => $rows->total(),
                "current" => $rows->currentPage(),
                "rowCount" => request()->input("rowCount"),
                "rows" => $rows->getCollection()
            );

            return response()->json($result);
        }
    }

    public function getNewsletterSubscriptions()
    {
        return view('admin/pages/statistics/newsletter_subscriptions');
    }

    public function postListNewsletterSubscriptions()
    {
        $website = Localization::getWebsite();
        $page = request()->input("current");
        $sort = request()->input("sort");
        $search = request()->input("searchPhrase");

        if (!empty($page)) {
            Paginator::currentPageResolver(function () use ($page) {
                return $page;
            });
            if (!empty($search)) {
                $query = NewsletterSubscription::where(function ($query) use ($search) {
                    $query->orWhere('email', 'like', "%{$search}%");
                });
            } else {
                $query = NewsletterSubscription::query();
            }
            $query->where('website_id', $website->id);
            $query->with('visitor');
            if (empty($sort)) {
                $rows = $query->paginate(request()->input("rowCount"));
            } else {
                foreach ($sort as $field => $direction) {
                    if (empty($direction) || !Str::is(strtolower($direction), "desc")) {
                        $order_direction = "asc";
                    }
                    $rows = $query->orderBy($field, strtolower($direction))->paginate(request()->input("rowCount"));
                    break;
                }
            }

            $result = array(
                "total" => $rows->total(),
                "current" => $rows->currentPage(),
                "rowCount" => request()->input("rowCount"),
                "rows" => $rows->getCollection()
            );

            return response()->json($result);
        }
    }

    public function getDownloads()
    {
        return view('admin/pages/statistics/downloads');
    }

    public function postListDownloads()
    {
        $website = Localization::getWebsite();
        $page = request()->input("current");
        $sort = request()->input("sort");
        $search = request()->input("searchPhrase");

        if (!empty($page)) {
            Paginator::currentPageResolver(function () use ($page) {
                return $page;
            });
            if (!empty($search)) {
                $query = VisitorDownload::where(function ($query) use ($search) {

                });
            } else {
                $query = VisitorDownload::query();
            }

            $query->select('visitor_downloads.id', 'visitor_downloads.created_at', 'visitors.id as visitor_id',
                'visitors.first_name', 'visitors.last_name', 'visitors.email',
                'publications_lang.title as publication');
            $query->join('visitors', 'visitors.id', '=', 'visitor_downloads.visitor_id');
            $query->join('publications_lang', 'publications_lang.publication_id', '=',
                'visitor_downloads.publication_id')
                ->where('publications_lang.language_id', 'en');

            $query->where('visitors.website_id', $website->id);

            if (empty($sort)) {
                $rows = $query->paginate(request()->input("rowCount"));
            } else {
                foreach ($sort as $field => $direction) {
                    if (empty($direction) || !Str::is(strtolower($direction), "desc")) {
                        $order_direction = "asc";
                    }
                    $rows = $query->orderBy($field, strtolower($direction))->paginate(request()->input("rowCount"));
                    break;
                }
            }

            $result = array(
                "total" => $rows->total(),
                "current" => $rows->currentPage(),
                "rowCount" => request()->input("rowCount"),
                "rows" => $rows->getCollection()
            );

            return response()->json($result);
        }
    }

    public function getExportNewsletterSubscriptions()
    {
        return Excel::download(new NewsletterSubscriptionsExport, 'newletter_subs.xlsx');
    }

    public function getExportVisitors()
    {
        return Excel::download(new VisitorsExport, 'visitors.xlsx');
    }

    public function getExportDownloads()
    {
        return Excel::download(new DownloadsExport, 'visitor_downloads.xlsx');
    }

    public function getExportPageViews($visitorId = null)
    {
        $website = Localization::getWebsite();

        $query = VisitorPageView::query();

        $query->select(DB::raw('visitor_page_views.created_at date, first_name, last_name, email, visit_number, page, referer_page, ip'));
        $query->join('visitors', 'visitors.id', '=', 'visitor_page_views.visitor_id');
        $query->join('sessions', 'sessions.id', '=', 'visitor_page_views.session_id');
        $query->where('website_id', $website->id);
        if ($visitorId) {
            $query->where('visitors.id', $visitorId);
        }

        $query->orderBy('sessions.created_at', 'desc')
            ->orderBy('visitor_page_views.id', 'asc');
        $pageViews = $query->get();

        Excel::create('Page Views', function ($excel) use ($pageViews) {
            $excel->sheet('Page Views', function ($sheet) use ($pageViews) {
                $sheet->fromArray($pageViews->toArray());
            });
        })->download('xlsx');
    }

    public function getVisitorContacts()
    {
        return view('admin/pages/statistics/visitor_contacts');
    }

    public function postListVisitorContacts()
    {
        $website = Localization::getWebsite();
        $page = request()->input("current");
        $sort = request()->input("sort");
        $search = request()->input("searchPhrase");

        if (!empty($page)) {
            Paginator::currentPageResolver(function () use ($page) {
                return $page;
            });
            if (!empty($search)) {
                $query = VisitorContact::where(function ($query) use ($search) {
                    $query->orWhere('first_name', 'like', "%{$search}%")
                        ->orWhere('last_name', 'like', "%{$search}%");
                });
            } else {
                $query = VisitorContact::query();
            }
            $query->select(DB::raw('visitor_contacts.created_at, visitors.first_name, visitors.last_name, visitors.company, visitors.country, visitors.email, visitor_contacts.message, visitor_contacts.id, contacts.name as contact, contacts.email as contact_email, visitor_contacts.page_url as page'));
            $query->join('visitors', 'visitor_contacts.visitor_id', '=', 'visitors.id');
            $query->join('contacts', 'visitor_contacts.contact_id', '=', 'contacts.id');
            if (empty($sort)) {
                $rows = $query->paginate(request()->input("rowCount"));
            } else {
                foreach ($sort as $field => $direction) {
                    if (empty($direction) || !Str::is(strtolower($direction), "desc")) {
                        $order_direction = "asc";
                    }
                    $rows = $query->orderBy($field, strtolower($direction))->paginate(request()->input("rowCount"));
                    break;
                }
            }

            $result = array(
                "total" => $rows->total(),
                "current" => $rows->currentPage(),
                "rowCount" => request()->input("rowCount"),
                "rows" => $rows->getCollection()
            );

            return response()->json($result);
        }
    }

    public function getAssessments()
    {
        return view('admin/pages/statistics/assessments');
    }

    public function postListAssessments()
    {
        $page = request()->input("current");
        $sort = request()->input("sort");
        $search = request()->input("searchPhrase");

        if (!empty($page)) {
            Paginator::currentPageResolver(function () use ($page) {
                return $page;
            });
            if (!empty($search)) {
                $query = Assessment::where(function ($query) use ($search) {
                    $query->orWhere('first_name', 'like', "%{$search}%")
                        ->orWhere('last_name', 'like', "%{$search}%")
                        ->orWhere('site_name', 'like', "%{$search}%");
                });
            } else {
                $query = Assessment::query();
            }

            if (empty($sort)) {
                $rows = $query->paginate(request()->input("rowCount"));
            } else {
                foreach ($sort as $field => $direction) {
                    if (empty($direction) || !Str::is(strtolower($direction), "desc")) {
                        $order_direction = "asc";
                    }
                    $rows = $query->orderBy($field, strtolower($direction))->paginate(request()->input("rowCount"));
                    break;
                }
            }

            $result = array(
                "total" => $rows->total(),
                "current" => $rows->currentPage(),
                "rowCount" => request()->input("rowCount"),
                "rows" => $rows->getCollection()
            );

            return response()->json($result);
        }
    }

    public function getDownloadAssessmentProfile($assessmentId = null)
    {

        $assessment = Assessment::find($assessmentId);
        if (empty($assessment) || !Storage::disk('assessment_profiles')->has($assessment->load_profile)) {
            abort(404);
        }

        return Storage::disk('assessment_profiles')->download($assessment->load_profile);
    }

    public function getExportVisitorContacts()
    {
        return Excel::download(new VisitorContactsExport, 'visitor_contacts.xlsx');
    }
}