<?php

namespace Yuvayana\Acadlix\Common\Models;

defined('ABSPATH') || exit();

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Capsule\Manager as DB;
use Illuminate\Support\Carbon;

if (!class_exists('WpUsers')) {
  class WpUsers extends Model
  {
    protected $table;
    protected $primaryKey = 'ID';
    protected $with = [
      'user_metas',
    ];

    protected $hidden = [
      'user_pass',
      'user_activation_key' 
    ];

    protected $appends = [
      'thumbnail',
    ];

    public function __construct(array $attributes = [])
    {
      global $wpdb;
      parent::__construct($attributes);
      $this->table = "{$wpdb->base_prefix}users";
    }

    public function user_metas()
    {
      return $this->hasMany(acadlix()->model()->wpUserMeta(), 'user_id', 'ID');
    }

    public function orders()
    {
      return $this->hasMany(acadlix()->model()->order(), 'user_id', 'ID');
    }

    public function course_statistics()
    {
      return $this->hasMany(acadlix()->model()->courseStatistic(), 'user_id', 'ID');
    }

    public function getThumbnailAttribute()
    {
      $profilePhoto = $this->user_metas
        ->firstWhere('meta_key', '_acadlix_profile_photo')
        ?->meta_value;

      if (!empty($profilePhoto)) {
        return $profilePhoto;
      }

      return get_avatar_url($this->ID);
    }

    public function scopeSearch($query, $search = null)
    {
      if (empty($search)) {
        return $query;
      }

      $usersTable = $this->getTable();

      return $query->where(function ($q) use ($search, $usersTable) {
        $q->where("{$usersTable}.display_name", 'like', "%{$search}%")
          ->orWhere("{$usersTable}.user_email", 'like', "%{$search}%")
          ->orWhere("{$usersTable}.user_login", 'like', "%{$search}%");
      });
    }

    public function scopeWithCoursePurchasedCount($query)
    {
      $baseQuery = $this->getEnrolledCoursesQuery();
      $usersTable = $this->getTable();

      $courseCountSubQuery = DB::query()
        ->fromSub($baseQuery, 'purchased_courses')
        ->select(
          'purchased_courses.user_id',
          DB::raw('COUNT(DISTINCT purchased_courses.course_id) as course_purchased_count')
        )
        ->groupBy('purchased_courses.user_id');

      return $query->leftJoinSub(
        $courseCountSubQuery,
        'user_course_counts',
        function ($join) {
          $join->on('user_course_counts.user_id', '=', $this->getTable() . '.ID');
        }
      )->addSelect(
          "{$usersTable}.*",
          DB::raw('COALESCE(user_course_counts.course_purchased_count, 0) as course_purchased_count')
        );
    }

    public function scopeEnrolled($query)
    {
      $baseQuery = $this->getEnrolledCoursesQuery();

      $usersTable = $this->getTable();

      $enrolledUserIds = DB::query()
        ->fromSub($baseQuery, 'enrolled_courses')
        ->select('enrolled_courses.user_id');

      return $query->whereIn(
        "{$usersTable}.ID",
        $enrolledUserIds
      );
    }

    protected function getEnrolledCoursesQuery()
    {
      $wpNow = Carbon::parse(current_time('mysql'));
      $courseEnrollmentTable = acadlix()->model()->courseEnrollment()->getTable();
      $enrollmentSourceTable = acadlix()->model()->enrollmentSource()->getTable();

      return DB::table($courseEnrollmentTable)
        ->join(
          $enrollmentSourceTable,
          "$enrollmentSourceTable.enrollment_id",
          '=',
          "$courseEnrollmentTable.id"
        )
        ->select(
          "$courseEnrollmentTable.user_id",
          "$courseEnrollmentTable.course_id"
        )
        ->whereIn("$courseEnrollmentTable.status", [CourseEnrollment::STATUS_ACTIVE, CourseEnrollment::STATUS_COMPLETED])
        ->where(function ($query) use ($enrollmentSourceTable, $wpNow) {
          $query->whereNull("$enrollmentSourceTable.starts_at")
            ->orWhere("$enrollmentSourceTable.starts_at", '<=', $wpNow);
        })
        ->where(function ($query) use ($enrollmentSourceTable, $wpNow) {
          $query->whereNull("$enrollmentSourceTable.expires_at")
            ->orWhere("$enrollmentSourceTable.expires_at", '>=', $wpNow);
        });
      // $orderItemTable = acadlix()->model()->orderItem()->getTable();
      // $ordersTable = acadlix()->model()->order()->getTable();

      // $directCourseQuery = DB::table($orderItemTable)
      //   ->join(
      //     $ordersTable,
      //     "$ordersTable.id",
      //     '=',
      //     "$orderItemTable.order_id"
      //   )
      //   ->select(
      //     "$orderItemTable.item_id as course_id",
      //     "$ordersTable.user_id"
      //   )
      //   ->where("$orderItemTable.type", 'course')
      //   ->whereNull("$orderItemTable.subscription_id")
      //   ->where("$ordersTable.status", Order::STATUS_SUCCESS);
      // return $directCourseQuery;
    }
  }
}