import { ResultSetHeader, RowDataPacket } from "mysql2";
import pool from "../../../../lib/db/db"
import { NextRequest, NextResponse } from "next/server";
import { verifyAuthentication } from "../../../utils/verifyAuthentication";

export async function GET(request: NextRequest) {
  try {
    const { searchParams } = new URL(request.url);
    const page = parseInt(searchParams.get('page')) || 1;
    const limit = parseInt(searchParams.get('limit')) || 15;
    const search = searchParams.get('search') || '';
    const featured = searchParams.get('featured') || '';
    const active = searchParams.get('active') || '';
    const startDate = searchParams.get('start_date') || '';
    const endDate = searchParams.get('end_date') || '';
    const author = searchParams.get('author') || '';

    console.log('🔍 Guides API called with params:', { page, limit, search, featured, active, startDate, endDate, author });

    let whereClauses = [];
    let queryParams = [];

    if (search) {
      whereClauses.push(`
        (g.title LIKE ? OR g.sub_title LIKE ? OR g.guide_type LIKE ? OR a.full_name LIKE ?)
      `);
      queryParams.push(`%${search}%`, `%${search}%`, `%${search}%`, `%${search}%`);
    }

    if (featured !== '') {
      whereClauses.push('g.is_featured = ?');
      queryParams.push(featured === 'true' ? 1 : 0);
    }

    if (active !== '') {
      whereClauses.push('g.is_active = ?');
      queryParams.push(active === 'true' ? 1 : 0);
    }

    if (author !== '') {
      whereClauses.push('g.author_id = ?');
      queryParams.push(author);
    }

    if (startDate) {
      whereClauses.push('g.created_at >= ?');
      queryParams.push(new Date(startDate).toISOString().slice(0, 19).replace('T', ' '));
    }
    if (endDate) {
      whereClauses.push('g.created_at <= ?');
      queryParams.push(new Date(endDate).toISOString().slice(0, 19).replace('T', ' '));
    }

    const whereClause = whereClauses.length > 0 ? 'WHERE ' + whereClauses.join(' AND ') : '';

    const countQuery = `
      SELECT COUNT(*) AS totalItems 
      FROM guides g
      LEFT JOIN authors a ON g.author_id = a.id
      ${whereClause}
    `;
    const [countResults] = await pool.query<RowDataPacket[]>(countQuery, queryParams);
    const totalItems = countResults[0].totalItems;
    const totalPages = Math.ceil(totalItems / limit);

    const offset = (page - 1) * limit;

    const guidesQuery = `
      SELECT 
        g.id, 
        g.title, 
        g.sub_title, 
        g.description, 
        g.image, 
        g.guide_type, 
        g.university_id, 
        g.subject_id, 
        g.slug, 
        g.sort_order, 
        g.is_active, 
        g.is_featured, 
        g.sm_question, 
        g.sm_answer, 
        g.review_detail, 
        g.seo, 
        g.created_at, 
        g.updated_at,
        g.author_id,
        g.user_id,
        a.full_name AS author_name,
        a.slug AS author_slug,
        a.profile_image AS author_image,
        a.bio AS author_bio,
        a.website AS author_website,
        u.first_name AS created_by_name,
        u.email AS created_by_email
      FROM guides g
      LEFT JOIN authors a ON g.author_id = a.id
      LEFT JOIN users u ON g.user_id = u.id
      ${whereClause}
      ORDER BY g.created_at DESC
      LIMIT ? OFFSET ?;
    `;
    const [guides] = await pool.query<RowDataPacket[]>(guidesQuery, [...queryParams, limit, offset]);

    return Response.json({
      success: true,
      data: guides,
      meta: {
        totalItems: totalItems,
        totalPages: totalPages,
        currentPage: page,
        limit: limit,
        hasNextPage: page < totalPages,
        hasPrevPage: page > 1
      }
    });

  } catch (error) {
    console.error('🔍 Error in guides API:', error);
    return Response.json({
      success: false,
      error: 'Failed to fetch guides',
      details: error.message
    }, { status: 500 });
  }
}

export async function POST(request: NextRequest) {
  const connection = await pool.getConnection();

  try {
    const body = await request.json();

    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: 403
        }
      );
    }

    await connection.beginTransaction();

    const {
      type, 
      type_id, 
      title, 
      show_meta, 
      slug, 
      description, 
      is_active, 
      meta_title = "", 
      meta_description = "", 
      meta_keywords = "", 
      image,
      author_id
    } = body;

    if (!type || !title || !slug || !type_id) {
      return NextResponse.json({
        success: false,
        message: 'Required fields are missing: Title, Slug, Guide Type and University/Subject'
      }, { status: 400 });
    }

    if (author_id) {
      const [authorExists] = await connection.query<RowDataPacket[]>(
        'SELECT id FROM authors WHERE id = ? AND status = "active"',
        [author_id]
      );

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

    let university_id = null;
    let subject_id = null;

    if (type === "University") {
      university_id = parseInt(type_id);
      subject_id = null;
    } else if (type === "Subject") {
      subject_id = parseInt(type_id);
      university_id = null;
    }

    let seo = null;
    if (show_meta === "on" && (meta_title || meta_description || meta_keywords)) {
      seo = {
        meta_title: meta_title || "",
        meta_description: meta_description || "",
        meta_keywords: meta_keywords || ""
      };
    }

    const query = `
      INSERT INTO guides (
        user_id,
        author_id,
        guide_type,
        university_id,
        subject_id,
        title,
        slug,
        description,
        is_active,
        seo,
        image
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
    `;

    const values = [
      decoded.id,
      author_id || null,
      type,
      university_id,
      subject_id,
      title,
      slug,
      description || "",
      is_active ? 1 : 0,
      seo ? JSON.stringify(seo) : null,
      image || null
    ];

    const [result] = await connection.query<ResultSetHeader>(query, values);

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

    await connection.commit();

    return NextResponse.json({
      success: true,
      data: {
        id: result.insertId,
        title,
        slug,
        seo: seo,
        author_id: author_id
      },
      message: 'Guide created successfully'
    }, { status: 201 });

  } catch (error) {
    console.error('🔍 Error creating guide:', error);
    await connection.rollback();
    
    return NextResponse.json({
      success: false,
      message: 'Failed to create guide',
      details: error.message || 'Internal server error'
    }, { status: 500 });
  } finally {
    connection.release();
  }
}

export async function PUT(request: NextRequest) {
  const connection = await pool.getConnection();

  try {
    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: 403
        }
      );
    }

    await connection.beginTransaction();

    const { searchParams } = new URL(request.url);
    const id = searchParams.get('id');
    
    if (!id) {
      return NextResponse.json({
        success: false,
        error: 'Guide ID is required'
      }, { status: 400 });
    }

    const body = await request.json();
    
    if (body.author_id) {
      const [authorExists] = await connection.query<RowDataPacket[]>(
        'SELECT id FROM authors WHERE id = ? AND status = "active"',
        [body.author_id]
      );

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

    let seo = null;
    if (body.seo && body.show_meta === "on") {
      seo = {
        meta_title: body.meta_title || "",
        meta_description: body.meta_description || "",
        meta_keywords: body.meta_keywords || ""
      };
    } else if (body.show_meta === "on" && (body.meta_title || body.meta_description || body.meta_keywords)) {
      seo = {
        meta_title: body.meta_title || "",
        meta_description: body.meta_description || "",
        meta_keywords: body.meta_keywords || ""
      };
    }

    const updateData = {
      guide_type: body.guide_type,
      university_id: body.university_id ? Number(body.university_id) : null,
      subject_id: body.subject_id ? Number(body.subject_id) : null,
      title: body.title,
      image: body.image,
      slug: body.slug,
      description: body.description || "",
      is_active: body.is_active !== undefined ? (body.is_active ? 1 : 0) : 1,
      is_featured: body.is_featured !== undefined ? (body.is_featured ? 1 : 0) : 0,
      author_id: body.author_id || null,
      seo: seo ? JSON.stringify(seo) : null,
    };

    const sqlQuery = `
      UPDATE guides
      SET guide_type = ?, university_id = ?, subject_id = ?, title = ?, slug = ?, 
          description = ?, is_active = ?, is_featured = ?, author_id = ?, seo = ?, image = ?, updated_at = NOW()
      WHERE id = ?
    `;

    const [result] = await connection.query(sqlQuery, [
      updateData.guide_type,
      updateData.university_id,
      updateData.subject_id,
      updateData.title,
      updateData.slug,
      updateData.description,
      updateData.is_active,
      updateData.is_featured,
      updateData.author_id,
      updateData.seo,
      updateData.image,
      parseInt(id),
    ]);

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

    await connection.commit();

    return NextResponse.json({
      success: true,
      data: result,
      message: 'Guide updated successfully'
    });

  } catch (error) {
    console.error('🔍 Error updating guide:', error);
    await connection.rollback();
    return NextResponse.json({
      success: false,
      error: 'Failed to update guide',
      details: error.message
    }, { status: 500 });
  } finally {
    connection.release();
  }
}

export async function PATCH(request: NextRequest) {
  try {
    const { searchParams } = new URL(request.url);
    const id = searchParams.get('id');
    
    if (!id) {
      return Response.json({
        success: false,
        error: 'Guide ID is required'
      }, { status: 400 });
    }

    const body = await request.json();

    const {is_featured} = body;
    
    const [result] = await pool.query<RowDataPacket[]>('SELECT * FROM guides WHERE id = ?', [parseInt(id)]);

    if(result.length === 0)
    {
      return NextResponse.json(
        {
          message: "Guide not found"
        },
        {
          status: 404
        }
      );
    }

    const updateGuide = await pool.query<ResultSetHeader>("UPDATE guides SET is_featured = ? WHERE id = ?", [is_featured, parseInt(id)]);

    if(updateGuide[0].affectedRows === 0)
    {
      return NextResponse.json(
        {
          message: "Failed to update guide"
        },
        {
          status: 500
        }
      );
    }

    const freshGuide = await pool.query<RowDataPacket[]>("SELECT * FROM guides WHERE id = ?", [parseInt(id)])

    return Response.json({
      success: true,
      data: freshGuide,
      message: 'Guide updated successfully'
    });

  } catch (error) {
    console.error('🔍 Error updating guide:', error);
    return Response.json({
      success: false,
      error: 'Failed to update guide',
      details: error.message
    }, { status: 500 });
  }
}

export async function DELETE(request: NextRequest) {

  const connection = await pool.getConnection();

  try {

    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: 403
        }
      );
    }

    await connection.beginTransaction();

    const { searchParams } = new URL(request.url);
    const id = searchParams.get('id');
    
    if (!id) {
      return Response.json({
        success: false,
        error: 'Guide ID is required'
      }, { status: 400 });
    }

    const [result] = await connection.query<RowDataPacket[]>('SELECT * FROM guides WHERE id = ?', [parseInt(id)]);

    if(result.length === 0)
    {
      return NextResponse.json(
        {
          message: "Guide not found"
        },
        {
          status: 404
        }
      );
    }

    await connection.query<ResultSetHeader>("DELETE FROM guides WHERE id = ?", [parseInt(id)]);

    await connection.query("INSERT INTO activity_logs (user_id, action) VALUES (?, ?)", [decoded.id, `Guide Title ${result[0].title} deleted by user ${decoded.name} and email is ${decoded.email}`])

    await connection.commit();

    return Response.json({
      success: true,
      message: 'Guide deleted successfully'
    });

  } catch (error) {
    console.error('🔍 Error deleting guide:', error);
    await connection.rollback();
    return Response.json({
      success: false,
      error: 'Failed to delete guide',
      details: error.message
    }, { status: 500 });
  } finally {
    connection.release();
  }
}