import pool from '../../../../../lib/db/db';
import { NextResponse } from 'next/server';
import { verifyAuthentication } from '../../../../utils/verifyAuthentication';

export async function GET(request, { params }) {
  try {
    const { id } = await params;
    const blogId = parseInt(id);

    if (isNaN(blogId)) {
      return NextResponse.json(
        { success: false, message: 'Invalid blog ID' },
        { status: 400 }
      );
    }

    // Fetch blog data with country and author information
    const [blog] = await pool.execute(
      `SELECT 
        blogs.*, 
        blog_category.id AS category_id, 
        blog_category.name AS category_name,
        countries.id AS country_id,
        countries.country AS country_name,
        countries.code AS country_code,
        authors.id AS author_id,                    -- Added author_id
        authors.full_name AS author_name,           -- Added author_name
        authors.slug AS author_slug,                -- Added author_slug
        authors.profile_image AS author_image,      -- Added author_image
        authors.bio AS author_bio,                  -- Added author_bio
        authors.website AS author_website,          -- Added author_website
        authors.social_facebook AS author_facebook, -- Added author_facebook
        authors.social_twitter AS author_twitter,   -- Added author_twitter
        authors.social_instagram AS author_instagram, -- Added author_instagram
        authors.social_linkedin AS author_linkedin, -- Added author_linkedin
        authors.status AS author_status,            -- Added author_status
        users.email AS user_email
      FROM blogs
      LEFT JOIN blog_category ON blogs.category_id = blog_category.id
      LEFT JOIN countries ON blogs.country_id = countries.id
      LEFT JOIN authors ON blogs.author_id = authors.id  -- Join with authors table
      LEFT JOIN users ON blogs.user_id = users.id
      WHERE blogs.id = ?`,
      [blogId]
    );

    if (blog.length === 0) {
      return NextResponse.json(
        { success: false, message: 'Blog not found' },
        { status: 404 }
      );
    }

    // Format the result as per the structure you want
    const blogData = {
      ...blog[0],
      enable_meta_tags: blog[0].enable_meta_tags || false,
    };

    return NextResponse.json({
      success: true,
      data: blogData,
    });

  } catch (error) {
    console.error('GET Error:', error);
    return NextResponse.json(
      { success: false, message: 'Server error' },
      { status: 500 }
    );
  }
}

export async function PUT(request, { params }) {
  const connection = await pool.getConnection();

  const token = request.cookies.get("university-token")?.value || null;
    
  if (!token) {
    return NextResponse.json(
      {
        message: "Please Login to continue"
      },
      {
        status: 400
      }
    );
  }

  const decoded = await verifyAuthentication(token)

  if (decoded instanceof NextResponse) {
    return NextResponse.json(
      {
        message: "You are not authorized to do this action"
      },
      {
        status: 400
      }
    );
  }
  
  try {
    const { id } = await params;
    const blogId = parseInt(id);

    if (isNaN(blogId)) {
      return NextResponse.json(
        { success: false, message: 'Invalid blog ID' },
        { status: 400 }
      );
    }

    const body = await request.json();
  
    // Check if this is just a meta tag update
    const isMetaTagOnlyUpdate = Object.keys(body).length === 1 && 
                               body.enable_meta_tags !== undefined;

    // Sanitize the body to replace undefined with null
    const updateData = isMetaTagOnlyUpdate 
      ? { is_featured: body.enable_meta_tags === true ? 1 : 0 }
      : {
          title: body.title ?? null,
          slug: body.slug ?? null,
          short_description: body.short_description ?? null,
          description: body.description ?? null,
          image: body.image ?? null,
          image_ext: body.image_ext ?? null,
          is_active: body.is_active ?? null,
          is_featured: body.is_featured ?? null,
          sm_question: body.sm_question ?? null,
          sm_answer: body.sm_answer ?? null,
          review_detail: body.review_detail ?? null,
          enable_meta_tags: body.enable_meta_tags ?? null,
          custom_post_type: body.custom_post_type ?? null,
          post_attributes: body.post_attributes ?? null,
          sort_order: body.sort_order ?? null,
          seo: body.seo ?? null,
          category_id: body.category_id ? Number(body.category_id) : null,
          country_id: body.country_id ? Number(body.country_id) : null,
          author_id: body.author_id ? Number(body.author_id) : null // Added author_id
        };

    // Check if author exists (if provided in update)
    if (updateData.author_id) {
      const [authorExists] = await connection.execute(
        'SELECT id FROM authors WHERE id = ? AND status = "active"',
        [updateData.author_id]
      );

      if (authorExists.length === 0) {
        return NextResponse.json(
          { success: false, message: 'Invalid or inactive author' },
          { status: 400 }
        );
      }
    }

    // Update query logic
    const setClauses = Object.keys(updateData)
      .map(key => `${key} = ?`)
      .join(', ');

    const values = Object.values(updateData);

    // Ensure the blog ID is added to the values array for the WHERE clause
    values.push(blogId);

    // Execute the update query
    const [result] = await connection.execute(
      `UPDATE blogs 
       SET ${setClauses} 
       WHERE id = ?`,
      values
    );

    if (result.affectedRows === 0) {
      return NextResponse.json(
        { success: false, message: 'Blog not found' },
        { status: 404 }
      );
    }

    // Retrieve the updated blog along with related category, country, author, and user data
    const [updatedBlog] = await pool.execute(
      `SELECT 
        blogs.*, 
        blog_category.id AS category_id, 
        blog_category.name AS category_name,
        countries.id AS country_id,
        countries.country AS country_name,
        countries.code AS country_code,
        authors.id AS author_id,
        authors.full_name AS author_name,
        authors.slug AS author_slug,
        authors.profile_image AS author_image,
        authors.bio AS author_bio,
        authors.website AS author_website,
        users.email AS user_email
      FROM blogs
      LEFT JOIN blog_category ON blogs.category_id = blog_category.id
      LEFT JOIN countries ON blogs.country_id = countries.id
      LEFT JOIN authors ON blogs.author_id = authors.id
      LEFT JOIN users ON blogs.user_id = users.id
      WHERE blogs.id = ?`,
      [blogId]
    );

    if (updatedBlog.length === 0) {
      return NextResponse.json(
        { success: false, message: 'Blog not found' },
        { status: 404 }
      );
    }

    await connection.query(
      "INSERT INTO activity_logs (user_id, action) VALUES (?, ?)", 
      [decoded.id, `Blog "${updatedBlog[0].title}" updated by user ${decoded.name} (${decoded.email})`]
    );

    await connection.commit();

    return NextResponse.json({
      success: true,
      message: 'Blog updated successfully',
      data: updatedBlog[0],
    });

  } catch (error) {
    console.error('PUT Error:', error);
    await connection.rollback();
    return NextResponse.json(
      { 
        success: false, 
        message: 'Server error',
        error: process.env.NODE_ENV === 'development' ? error.message : undefined
      },
      { status: 500 }
    );
  } finally {
    connection.release();
  }
}

export async function DELETE(request, { params }) {
  const token = request.cookies.get("university-token")?.value || null;
  const connection = await pool.getConnection();
    
  if (!token) {
    return NextResponse.json(
      {
        message: "Please Login to continue"
      },
      {
        status: 400
      }
    );
  }

  const decoded = await verifyAuthentication(token)

  if (decoded instanceof NextResponse) {
    return NextResponse.json(
      {
        message: "You are not authorized to do this action"
      },
      {
        status: 400
      }
    );
  }

  try {
    const { id } = await params;
    const blogId = parseInt(id);

    if (isNaN(blogId)) {
      return NextResponse.json(
        { success: false, message: 'Invalid blog ID' },
        { status: 400 }
      );
    }

    // Check if the blog exists first (optional but recommended)
    const [blogExists] = await connection.execute(
      'SELECT * FROM blogs WHERE id = ?',
      [blogId]
    );

    if (blogExists.length === 0) {
      return NextResponse.json(
        { success: false, message: 'Blog not found' },
        { status: 404 }
      );
    }

    // Delete the blog from the database
    const [deleteResult] = await connection.execute(
      'DELETE FROM blogs WHERE id = ?',
      [blogId]
    );

    // If no rows were deleted, the blog doesn't exist
    if (deleteResult.affectedRows === 0) {
      return NextResponse.json(
        { success: false, message: 'Blog not found' },
        { status: 404 }
      );
    }

    const blog = blogExists[0];

    await connection.query(
      "INSERT INTO activity_logs (user_id, action) VALUES (?, ?)", 
      [decoded.id, `Blog "${blog.title}" deleted by user ${decoded.name} (${decoded.email})`]
    );

    await connection.commit();

    return NextResponse.json({
      success: true,
      message: 'Blog deleted successfully'
    });
  } catch (error) {
    console.error('DELETE Error:', error);
    await connection.rollback();
    return NextResponse.json(
      { success: false, message: 'Server error' },
      { status: 500 }
    );
  } finally {
    connection.release();
  }
}