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

export async function GET(request: NextRequest) {
  try {
    const startTime = Date.now();
    const { searchParams } = new URL(request.url);
    const page = parseInt(searchParams.get('page')) || 1;
    const limit = parseInt(searchParams.get('limit')) || 10;
    const search = searchParams.get('search') || '';
    const author = searchParams.get('author') || '';
    const startDate = searchParams.get('start_date') || '';
    const endDate = searchParams.get('end_date') || '';
    const popular = searchParams.get('popular') || '';
    const active = searchParams.get('active') || '';
    
    // Counting total posts
    const [totalPostsCountResult] = await pool.query('SELECT COUNT(*) AS total FROM news');
    const totalPostsCount = totalPostsCountResult[0].total;

    if (totalPostsCount === 0) {
      console.log('🔍 No news found, creating sample news...');
      // Sample news creation logic if needed
    }

    // Calculate pagination
    const skip = (page - 1) * limit;

    let whereClause = 'WHERE 1=1'; // default where clause
    let queryParams: any[] = [];

    // Apply filters based on search
    if (search) {
      whereClause += ` AND (n.title LIKE ? OR n.description LIKE ? OR a.full_name LIKE ?)`;
      queryParams.push(`%${search}%`, `%${search}%`, `%${search}%`);
    }

    // Apply author filter
    if (author && author !== '') {
      whereClause += ` AND n.author_id = ?`;
      queryParams.push(author);
    }

    // Date range filters
    if (startDate || endDate) {
      if (startDate) {
        whereClause += ` AND n.created_at >= ?`;
        queryParams.push(`${startDate}T00:00:00Z`);
      }
      if (endDate) {
        whereClause += ` AND n.created_at <= ?`;
        queryParams.push(`${endDate}T23:59:59Z`);
      }
    }

    // Popular filter
    if (popular !== '') {
      whereClause += ` AND n.is_featured = ?`;
      queryParams.push(popular === 'true' ? 1 : 0);
    }

    // Active filter
    if (active !== '') {
      whereClause += ` AND n.is_active = ?`;
      queryParams.push(active === 'true' ? 1 : 0);
    }

    // Get total count after filtering
    const [totalCountResult] = await pool.query(
      `SELECT COUNT(*) AS total FROM news n 
       LEFT JOIN authors a ON n.author_id = a.id
       ${whereClause}`, 
      queryParams
    );
    const totalCount = totalCountResult[0].total;

    // Fetch paginated news with author information
    const [news] = await pool.query<RowDataPacket[]>(`
      SELECT 
        n.id, 
        n.title, 
        n.description, 
        n.image, 
        n.author_id,
        n.user_id, 
        n.is_active, 
        n.is_featured, 
        n.enable_meta_tags, 
        n.created_at, 
        n.updated_at, 
        n.seo, 
        n.slug,
        n.sm_question,
        n.sm_answer,
        n.likes,
        a.full_name AS author_name,
        a.slug AS author_slug,
        a.profile_image AS author_image,
        u.first_name AS user_name
      FROM news n
      LEFT JOIN authors a ON n.author_id = a.id
      LEFT JOIN users u ON n.user_id = u.id
      ${whereClause}
      ORDER BY n.created_at DESC
      LIMIT ? OFFSET ?`, 
      [...queryParams, limit, skip]
    );

    // Pagination metadata
    const totalPages = Math.ceil(totalCount / limit);
    const hasNextPage = page < totalPages;
    const hasPrevPage = page > 1;

    return NextResponse.json({
      success: true,
      data: news,
      pagination: {
        currentPage: page,
        totalPages,
        totalItems: totalCount,
        itemsPerPage: limit,
        hasNextPage,
        hasPrevPage,
        startItem: skip + 1,
        endItem: Math.min(skip + news.length, totalCount),
      },
      filters: {
        author,
        search,
        startDate,
        endDate,
        popular,
        active,
      }
    });

  } catch (err: any) {
    console.error("❌ GET API Error:", err);
    return NextResponse.json({ 
      success: false, 
      error: "Failed to fetch news", 
      details: err.message 
    }, { status: 500 });
  }
}

export async function POST(request: NextRequest) {
  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 body = await request.json();

    const {
      title,
      slug,
      author_id,
      description,
      image,
      is_featured = false,
      is_active = true,
      enable_meta_tags = false,
      seo = '',
      sm_question = '',
      sm_answer = '',
      likes = 0,
    } = body;

    await connection.beginTransaction();

    // Validation
    const errors = [];
    if (!title?.trim()) errors.push('Title is required');
    if (!slug?.trim()) errors.push('Slug is required');

    if (errors.length > 0) {
      console.error('Validation failed:', { errors, body });
      return NextResponse.json(
        { success: false, message: 'Validation failed', errors },
        { status: 400 }
      );
    }

    // Check if author exists (if provided)
    if (author_id) {
      const [authorExists] = await connection.execute<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 }
        );
      }
    }

    // Check if slug is unique
    const [existingSlug] = await connection.execute<RowDataPacket[]>(
      'SELECT id FROM news WHERE slug = ?',
      [slug.trim()]
    );

    if (existingSlug.length > 0) {
      return NextResponse.json(
        { success: false, message: 'News with this slug already exists' },
        { status: 400 }
      );
    }

    // Prepare news data for insertion
    const newsData = {
      title: title.trim(),
      slug: slug.trim(),
      author_id: author_id || null,
      description: description?.trim() || null,
      image: image?.trim() || null,
      is_featured: Boolean(is_featured),
      is_active: Boolean(is_active),
      enable_meta_tags: Boolean(enable_meta_tags),
      user_id: Number(decoded?.id),
      seo: seo?.trim() || null,
      sm_question: sm_question?.trim() || null,
      sm_answer: sm_answer?.trim() || null,
      likes: Number(likes) || 0,
    };

    // Insert news data
    const [result] = await connection.execute<ResultSetHeader>(
      `INSERT INTO news (
        title,
        slug,
        author_id,
        description,
        image,
        is_featured,
        is_active,
        enable_meta_tags,
        user_id,
        seo,
        sm_question,
        sm_answer,
        likes
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        newsData.title,
        newsData.slug,
        newsData.author_id,
        newsData.description,
        newsData.image,
        newsData.is_featured,
        newsData.is_active,
        newsData.enable_meta_tags,
        newsData.user_id,
        newsData.seo,
        newsData.sm_question,
        newsData.sm_answer,
        newsData.likes,
      ]
    );

    const newsId = result.insertId;

    // Retrieve the inserted news data with joins including author
    const [newNews] = await connection.execute<RowDataPacket[]>(
      `SELECT 
        n.*,
        a.full_name AS author_name,
        a.slug AS author_slug,
        a.profile_image AS author_image
      FROM news n
      LEFT JOIN authors a ON n.author_id = a.id
      WHERE n.id = ?`,
      [newsId]
    );

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

    await connection.commit();

    return NextResponse.json({
      success: true,
      message: 'News created successfully',
      data: newNews[0]
    }, { status: 201 });

  } catch (error: any) {
    console.error('❌ POST /api/internal/news error:', error);
    await connection.rollback();
    return NextResponse.json(
      {
        success: false,
        message: 'Failed to create news',
        error: process.env.NODE_ENV === 'development' ? error.message : 'Internal server error'
      },
      { status: 500 }
    );
  } finally {
    connection.release();
  }
}

export async function PUT(request: NextRequest) {
  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 body = await request.json();
    const url = new URL(request.url);
    const id = url.pathname.split('/').pop();

    if (!id) {
      return NextResponse.json(
        { success: false, message: 'News ID is required' },
        { status: 400 }
      );
    }

    const {
      title,
      slug,
      author_id,
      description,
      image,
      is_featured = false,
      is_active = true,
      enable_meta_tags = false,
      seo = '',
      sm_question = '',
      sm_answer = '',
      likes = 0,
    } = body;

    await connection.beginTransaction();

    // Validation
    const errors = [];
    if (!title?.trim()) errors.push('Title is required');
    if (!slug?.trim()) errors.push('Slug is required');

    if (errors.length > 0) {
      console.error('Validation failed:', { errors, body });
      return NextResponse.json(
        { success: false, message: 'Validation failed', errors },
        { status: 400 }
      );
    }

    // Check if news exists
    const [newsExists] = await connection.execute<RowDataPacket[]>(
      'SELECT id FROM news WHERE id = ?',
      [id]
    );

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

    // Check if author exists (if provided)
    if (author_id) {
      const [authorExists] = await connection.execute<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 }
        );
      }
    }

    // Check if slug is unique (excluding current news)
    const [existingSlug] = await connection.execute<RowDataPacket[]>(
      'SELECT id FROM news WHERE slug = ? AND id != ?',
      [slug.trim(), id]
    );

    if (existingSlug.length > 0) {
      return NextResponse.json(
        { success: false, message: 'Another news with this slug already exists' },
        { status: 400 }
      );
    }

    // Prepare news data for update
    const newsData = {
      title: title.trim(),
      slug: slug.trim(),
      author_id: author_id || null,
      description: description?.trim() || null,
      image: image?.trim() || null,
      is_featured: Boolean(is_featured),
      is_active: Boolean(is_active),
      enable_meta_tags: Boolean(enable_meta_tags),
      seo: seo?.trim() || null,
      sm_question: sm_question?.trim() || null,
      sm_answer: sm_answer?.trim() || null,
      likes: Number(likes) || 0,
    };

    // Update news data
    await connection.execute<ResultSetHeader>(
      `UPDATE news SET
        title = ?,
        slug = ?,
        author_id = ?,
        description = ?,
        image = ?,
        is_featured = ?,
        is_active = ?,
        enable_meta_tags = ?,
        seo = ?,
        sm_question = ?,
        sm_answer = ?,
        likes = ?,
        updated_at = CURRENT_TIMESTAMP
      WHERE id = ?`,
      [
        newsData.title,
        newsData.slug,
        newsData.author_id,
        newsData.description,
        newsData.image,
        newsData.is_featured,
        newsData.is_active,
        newsData.enable_meta_tags,
        newsData.seo,
        newsData.sm_question,
        newsData.sm_answer,
        newsData.likes,
        id
      ]
    );

    // Retrieve the updated news data
    const [updatedNews] = await connection.execute<RowDataPacket[]>(
      `SELECT 
        n.*,
        a.full_name AS author_name,
        a.slug AS author_slug,
        a.profile_image AS author_image
      FROM news n
      LEFT JOIN authors a ON n.author_id = a.id
      WHERE n.id = ?`,
      [id]
    );

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

    await connection.commit();

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

  } catch (error: any) {
    console.error('❌ PUT /api/internal/news/[id] error:', error);
    await connection.rollback();
    return NextResponse.json(
      {
        success: false,
        message: 'Failed to update news',
        error: process.env.NODE_ENV === 'development' ? error.message : 'Internal server error'
      },
      { status: 500 }
    );
  } finally {
    connection.release();
  }
}

export async function DELETE(request: NextRequest) {
  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 url = new URL(request.url);
    const id = url.pathname.split('/').pop();

    if (!id) {
      return NextResponse.json(
        { success: false, message: 'News ID is required' },
        { status: 400 }
      );
    }

    await connection.beginTransaction();

    // Check if news exists
    const [newsExists] = await connection.execute<RowDataPacket[]>(
      'SELECT title FROM news WHERE id = ?',
      [id]
    );

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

    // Delete news
    await connection.execute(
      'DELETE FROM news WHERE id = ?',
      [id]
    );

    // Log activity
    await connection.query(
      "INSERT INTO activity_logs (user_id, action) VALUES (?, ?)", 
      [decoded.id, `News "${newsExists[0].title}" deleted by user ${decoded.name} (${decoded.email})`]
    );

    await connection.commit();

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

  } catch (error: any) {
    console.error('❌ DELETE /api/internal/news/[id] error:', error);
    await connection.rollback();
    return NextResponse.json(
      {
        success: false,
        message: 'Failed to delete news',
        error: process.env.NODE_ENV === 'development' ? error.message : 'Internal server error'
      },
      { status: 500 }
    );
  } finally {
    connection.release();
  }
}