<?php

namespace App\Http\Controllers\API\v1;

use App\Http\Controllers\Controller;
use Illuminate\Database\Eloquent\ModelNotFoundException;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Throwable;

class NotificationController extends Controller
{
    public function index(Request $request)
    {
        $user = Auth::user();

        // DB::enableQueryLog();

        $notificationsQuery = DB::table('notifications')
            ->join('users', 'notifications.notifiable_id', '=', 'users.id');

        // if ($user->employee_id != 1) {
        //     $notificationsQuery->where('notifications.notifiable_id', '=', $user->id);
        // }

        $notifications = $notificationsQuery->select('notifications.*', 'users.name')
            ->paginate(10); // Here 10 is the number of items per page, you can adjust it

        // Return paginated data as JSON
        return response()->json($notifications);
    }

    public function show(Request $request, $employee_id)
    {

        $notifications = DB::table('notifications')
        ->join('users', 'notifications.notifiable_id' ,'users.id')
        ->where('users.employee_id', '=', $employee_id)
        ->limit(5)
        ->get();

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

    public function getNotificationForBell($employee_id)
    {
        $userId = Auth::id();

        // Fetch notifications with left join on profile pictures
        $notifications = DB::table('notifications')
            ->leftJoin('employee_profile_pictures', function ($join) {
                $join->on('employee_profile_pictures.employee_id', '=', DB::raw('JSON_UNQUOTE(JSON_EXTRACT(notifications.data, "$.created_by"))'));
            })
            ->select(
                'notifications.id',
                'notifications.type',
                'notifications.notifiable_id',
                'notifications.data',
                'notifications.created_at',
                'notifications.read_at',
                DB::raw('COALESCE(employee_profile_pictures.attachment, NULL) as attachment')
            )
            ->whereExists(function ($query) use ($employee_id) {
                $query->select(DB::raw(1))
                    ->from('users')
                    ->whereColumn('users.id', 'notifications.notifiable_id')
                    ->where('users.employee_id', $employee_id);
            })
            ->where('notifications.notifiable_id', '=', $userId)
            ->orderByDesc('notifications.created_at')
            ->limit(7)
            ->get()
            ->map(function ($notification) {
                if ($notification->attachment) {
                    $notification->attachment = asset('storage/employee-profile/' . $notification->attachment);
                }
                return $notification;
            });

        // Count unread notifications efficiently
        $unreadCount = DB::table('notifications')
            ->where('notifiable_id', '=', $userId)
            ->whereNull('read_at')
            ->whereExists(function ($query) use ($employee_id) {
                $query->select(DB::raw(1))
                    ->from('users')
                    ->whereColumn('users.id', 'notifications.notifiable_id')
                    ->where('users.employee_id', $employee_id);
            })
            ->count();

        return response()->json([
            'notifications' => $notifications,
            'unread_count' => $unreadCount
        ]);
    }


    public function update(string $id)
    {
        DB::connection()->beginTransaction();

        try {
            // Retrieve the first matching notification or throw an exception if not found
            $notification = DB::table('notifications')->where('id', '=', $id)->first();

            if (!$notification) {
                throw new ModelNotFoundException("Notification not found.");
            }

            // Update the read_at field
            DB::table('notifications')->where('id', '=', $id)->update(['read_at' => now()]);

            DB::connection()->commit();

            return response()->json([
                'status'  => true,
                'message' => 'Saved successfully!',
                'data'    => $notification
            ], 201);

        } catch (Throwable $e) {
            DB::connection()->rollback();
            return response()->json([
                'status'  => false,
                'message' => 'Unable to process request. Please try again.',
                'data'    => $e->getMessage()
            ]);
        }
    }
}
