<?php

use Illuminate\Http\Request;
use Illuminate\Support\Facades\Route;
use Illuminate\Support\Facades\Hash;
use Illuminate\Validation\ValidationException;
use Illuminate\Support\Facades\DB;

use App\Models\User;
use App\Models\Message;
use App\Models\Blog;
use App\Models\Comment;

/*
|--------------------------------------------------------------------------
| API Routes
|--------------------------------------------------------------------------
|
| Here is where you can register API routes for your application. These
| routes are loaded by the RouteServiceProvider within a group which
| is assigned the "api" middleware group. Enjoy building your API!
|
*/

Route::middleware('auth:sanctum')->get('/user', function (Request $request) {
    return $request->user();
});

Route::post('/login', function (Request $request) {
    $request->validate([
        'email' => 'required|email',
        'password' => 'required',
    ]);

    $user = User::where('email', $request->email)->first();

    if (!$user || !Hash::check($request->password, $user->password)) {
        throw ValidationException::withMessages([
            'email' => ['The provided credentials are incorrect.'],
        ]);
    }

    return [
        'token' => $user->createToken('login')->plainTextToken,
        'userData' => json_encode($user),
    ];
});

Route::middleware('auth:sanctum')->post('/logout', function (Request $request) {
    $user = $request->user();
    $user->tokens()->delete();
    return $user;
});

Route::middleware('auth:sanctum')->post('/comment/post', function (Request $request) {
    $request->validate([
        'comment' => 'required',
        'blogId' => 'required',
    ]);

    Comment::insert([
        'writer' => $request->user()->id,
        'comment' => $request->comment,
        'blogId' => $request->blogId,
        'created_at' => now(),
        'updated_at' => now(),
    ]);

    return 'success';
});

Route::middleware('auth:sanctum')->post('/comment/delete', function (Request $request) {
    $request->validate([
        'commentID' => 'required',
    ]);

    Comment::where('id', '=', $request->commentID)->delete();

    return 'success';
});

Route::middleware('auth:sanctum')->post('/get_blog/list', function (Request $request) {
    $blogList = DB::select("SELECT 
            blogs.id, 
            title, 
            content AS excerpt, 
            tags, users.id AS userId, 
            users.name AS userFullName, 
            blogs.created_at AS blogPosted, 
            t.total AS comment
        FROM [laravel_kevin].[dbo].[blogs]
        RIGHT JOIN [dbo].[users] ON [blogs].[writer] = [users].[id]
        LEFT JOIN (
            SELECT [blogId],COUNT(*) AS total
            FROM [laravel_kevin].[dbo].[comments]
            GROUP BY [blogId]) as t ON [blogs].[id] = [t].[blogId]
        ORDER BY blogs.id DESC");

    return $blogList;
});

Route::middleware('auth:sanctum')->post('/get_blog/list/perpage', function (Request $request) {
    $request->validate([
        'offset' => 'required',
        'rows' => 'required',
    ]);
    $blogList = DB::select("SELECT 
            blogs.id, 
            title, 
            content AS excerpt, 
            tags, users.id AS userId, 
            users.name AS userFullName, 
            blogs.created_at AS blogPosted, 
            t.total AS comment
        FROM [laravel_kevin].[dbo].[blogs]
        RIGHT JOIN [dbo].[users] ON [blogs].[writer] = [users].[id]
        LEFT JOIN (
            SELECT [blogId],COUNT(*) AS total
            FROM [laravel_kevin].[dbo].[comments]
            GROUP BY [blogId]) as t ON [blogs].[id] = [t].[blogId]
        ORDER BY blogs.id DESC
        OFFSET " . $request->offset . " ROW
        FETCH NEXT " . $request->rows . " ROWS ONLY");

    return $blogList;
});

Route::middleware('auth:sanctum')->post('/get_blog/list/total', function (Request $request) {
    $blogList = DB::select("SELECT COUNT(*) AS total
        FROM [laravel_kevin].[dbo].[blogs]");

    return $blogList[0]->total;
});

Route::middleware('auth:sanctum')->post('/get_blog/detail', function (Request $request) {
    $request->validate([
        'blogId' => 'required',
    ]);
    $blog = DB::select("SELECT 
            title, 
            content, 
            tags, 
            users.id AS userId, 
            users.name AS userFullName, 
            blogs.created_at AS createdTime, 
            bookmarked, 
            t.total AS comments
        FROM [laravel_kevin].[dbo].[blogs]
        RIGHT JOIN [dbo].[users] ON [blogs].[writer] = [users].[id]
        LEFT JOIN (
            SELECT [blogId],COUNT(*) AS total
            FROM [laravel_kevin].[dbo].[comments]
            GROUP BY [blogId]) as t ON [blogs].[id] = [t].[blogId]
        WHERE blogs.id=" . $request->blogId);

    $comments = DB::select("SELECT comment AS commentText, comments.created_at AS commentedAt, users.id AS userId, users.name AS userFullName
        FROM [laravel_kevin].[dbo].[comments]
        RIGHT JOIN [dbo].[users] ON [comments].[writer] = [users].[id]
        WHERE [blogId]=" . $request->blogId);
    return ['blog' => $blog[0], 'comments' => $comments];
});

Route::middleware('auth:sanctum')->post('/get_blog/sidebar', function (Request $request) {
    $recentPosts = DB::select("SELECT TOP(4) blogs.id, title, blogs.created_at AS createdTime, t.total AS comments
        FROM [laravel_kevin].[dbo].[blogs]
        RIGHT JOIN [dbo].[users] ON [blogs].[writer] = [users].[id]
        LEFT JOIN (
            SELECT [blogId],COUNT(*) AS total
            FROM [laravel_kevin].[dbo].[comments]
            GROUP BY [blogId]) as t ON [blogs].[id] = [t].[blogId]
        ORDER BY comments DESC");

    $categories = [
        ['category' => 'Fashion', 'icon' => 'WatchIcon'],
        ['category' => 'Food', 'icon' => 'ShoppingCartIcon'],
        ['category' => 'Gaming', 'icon' => 'CommandIcon'],
        ['category' => 'Quote', 'icon' => 'HashIcon'],
        ['category' => 'Video', 'icon' => 'VideoIcon'],
    ];

    return ['recentPosts' => $recentPosts, 'categories' => $categories];
});