<?php

namespace App;

use Illuminate\Database\Eloquent\Model;
use \Carbon\Carbon;
use stdClass;

class Announcement extends Model
{
    protected $dates = ['date_from', 'date_to'];

    public static function getUserNotifications($userId, $companyId)
    {
        $user = \DB::table('users')
            ->where('id', '=', $userId)
            ->first(['group_id', 'department_id', 'id']);
        $notifications = \DB::select("SELECT id, topic, details, created_at, attachment
        FROM announcements
               WHERE ((target=?)
               OR (target=? AND LOCATE(?,target_id) > 0)
               OR (target=? AND LOCATE(?,target_id) > 0)
               OR (target=? AND LOCATE(?,target_id) > 0))
               AND company_id=?
               AND created_at >= (SELECT CONVERT(join_date,DATETIME) FROM users WHERE id=?)
        ORDER BY created_at DESC",
            [
                'all',
                'user', '"' . $userId . '"',
                'department', '"' . $user->department_id . '"',
                'group', '"' . $user->group_id . '"',
                $companyId, $userId]);
        if (!empty($notifications)) {
            /** @var stdClass $notification */
            foreach ($notifications as $notification) {
                $notification->created_at = Carbon::parse($notification->created_at)->diffForHumans();
                if ($notification->attachment == null
                    || $notification->attachment == ''
                    || !\File::exists(public_path($notification->attachment))
                )
                    $notification->attachment = null;
                else {
                    $path = \URL::asset($notification->attachment);
                    $public_path = public_path($notification->attachment);
                    $notification->attachment = new stdClass();
                    $notification->attachment->path = $path;
                    $notification->attachment->mime = \File::mimeType($public_path);
                }
            }
        }
        return $notifications;
    }

    public static function getUserNotificationsCount($userId, $companyId)
    {
        $user = \DB::table('users')
            ->where('id', '=', $userId)
            ->first(['group_id', 'department_id', 'id']);
        $count = \DB::selectOne("SELECT count(*) AS count FROM announcements
               WHERE
               ((target=?  AND LOCATE(?,seen_by) =0)
               OR (target=? AND LOCATE(?,target_id) > 0 AND LOCATE(?,seen_by) =0)
               OR (target=? AND LOCATE(?,target_id) > 0 AND LOCATE(?,seen_by) =0)
               OR (target=? AND LOCATE(?,target_id) > 0 AND LOCATE(?,seen_by) =0))
               AND company_id=?
               AND created_at >= (SELECT CONVERT(join_date,DATETIME) FROM users WHERE id=?)",
            ['all', '"' . $userId . '"',
                'department', '"' . $user->department_id . '"', '"' . $user->id . '"',
                'group', '"' . $user->group_id . '"', '"' . $user->id . '"',
                'user', '"' . $userId . '"', '"' . $user->id . '"'
                , $companyId, $userId]);
        return $count ? $count->count : 0;
    }

    public static function readUserNotification($userId, $companyId)
    {
        $user = \DB::table('users')
            ->where('id', '=', $userId)
            ->first(['group_id', 'department_id', 'id']);
        // Update All and User Notifications
        \DB::statement("UPDATE announcements SET seen_by=CONCAT(seen_by,?)
               WHERE
               ((target=? AND LOCATE(?,target_id) = 0)
               OR (target=? AND LOCATE(?,target_id) = 0))
               AND company_id=?
               AND created_at >= (SELECT CONVERT(join_date,DATETIME) FROM users WHERE id=?)",
            ['"' . $userId . '"',
                'all', '"' . $userId . '"',
                'user', '"' . $userId . '"', $companyId, $userId]);
        // Update Group
        \DB::statement("UPDATE announcements SET seen_by=CONCAT(seen_by,?)
               WHERE (target=? AND LOCATE(?,target_id) = 0)
               AND company_id=?
               AND created_at >= (SELECT CONVERT(join_date,DATETIME) FROM users WHERE id=?)",
            ['"' . $user->group_id . '"',
                'group', '"' . $user->group_id . '"', $companyId, $userId]);
        // Update Department
        \DB::statement("UPDATE announcements SET seen_by=CONCAT(seen_by,?)
               WHERE (target=? AND LOCATE(?,target_id) = 0)
               AND company_id=?
               AND created_at >= (SELECT CONVERT(join_date,DATETIME) FROM users WHERE id=?)",
            ['"' . $user->department_id. '"',
                'department', '"' . $user->department_id . '"', $companyId, $userId]);

        return "SUCCESS";
    }

    public static function insertUserNotification($topic, $details, $userId, $targetId, $companyId)
    {
        \DB::insert("INSERT INTO announcements (topic,details,user_id,target_id,company_id,date_from,date_to,target) VALUES (?,?,?,?,?,?,?,?)",
            [$topic, $details, $userId, $targetId, $companyId, Carbon::now()->toDateString(), Carbon::now()->addDays(7)->toDateString(), 'user']);
        return \DB::getPdo()->lastInsertId();
    }

    public static function insert($title, $topic, $details, $userId, $target, $targetId, $companyId, $from, $to, $attachment)
    {
        \DB::insert("INSERT INTO announcements (title,topic,details,user_id,target,target_id,company_id,date_from,date_to,attachment) VALUES (?,?,?,?,?,?,?,?,?,?)",
            [$title, $topic, $details, $userId, $target, $targetId, $companyId, $from, $to, $attachment]);
        return \DB::getPdo()->lastInsertId();
    }
}

