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

// GET - Fetch all videos with country information
export async function GET(request) {
  let connection;

  try {
    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 status = searchParams.get('status') || '';
    const featured = searchParams.get('featured') || '';
    const country = searchParams.get('country') || '';

    connection = await pool.getConnection();

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

    // Build WHERE clause for filters
    let whereClause = '';
    const queryParams = [];
    
    const conditions = [];
    
    if (search) {
      const searchLower = `%${search.toLowerCase()}%`;
      conditions.push(`(LOWER(yv.title) LIKE ? OR LOWER(yv.description) LIKE ? OR LOWER(yv.video_id) LIKE ?)`);
      queryParams.push(searchLower, searchLower, searchLower);
    }
    
    if (status) {
      if (status === 'active') {
        conditions.push(`yv.is_active = 1`);
      } else if (status === 'inactive') {
        conditions.push(`yv.is_active = 0`);
      }
    }
    
    if (featured) {
      if (featured === 'true') {
        conditions.push(`yv.featured = 1`);
      } else if (featured === 'false') {
        conditions.push(`yv.featured = 0`);
      }
    }
    
    if (country) {
      conditions.push(`yv.country_id = ?`);
      queryParams.push(parseInt(country));
    }

    if (conditions.length > 0) {
      whereClause = `WHERE ${conditions.join(' AND ')}`;
    }

    // Get total count for pagination
    const countQuery = `SELECT COUNT(*) AS totalCount FROM youtube_videos yv ${whereClause}`;
    const [countRows] = await connection.execute(countQuery, queryParams);
    const totalCount = countRows[0].totalCount;

    // Get paginated data with country information
    const videosQuery = `
      SELECT 
        yv.*,
        c.id as country_id,
        c.country as country_name,
        c.code as country_code
      FROM youtube_videos yv
      LEFT JOIN countries c ON yv.country_id = c.id
      ${whereClause}
      ORDER BY yv.created_at DESC 
      LIMIT ? OFFSET ?
    `;
    
    const paginatedQueryParams = [...queryParams, limit, skip];
    const [videosRows] = await connection.execute(videosQuery, paginatedQueryParams);

    // Calculate pagination metadata
    const totalPages = Math.ceil(totalCount / limit);

    const response = { 
      success: true,
      data: videosRows,
      meta: {
        totalItems: totalCount,
        totalPages: totalPages,
        currentPage: page,
        itemsPerPage: limit,
        hasNextPage: page < totalPages,
        hasPreviousPage: page > 1
      }
    };

    return NextResponse.json(response);
  } catch (error) {
    console.error("Error fetching videos:", error);
    return NextResponse.json(
      { success: false, error: error.message },
      { status: 500 }
    );
  } finally {
    if (connection) {
      connection.release();
    }
  }
}

// POST - Create new video
export async function POST(req) {
  let connection;

  try {
    connection = await pool.getConnection();
    const body = await req.json();
    
    console.log("📥 Received video data:", body);
    
    // Validation
    if (!body.title) {
      return NextResponse.json(
        { success: false, error: "Video title is required" },
        { status: 400 }
      );
    }

    if (!body.video_id) {
      return NextResponse.json(
        { success: false, error: "YouTube video ID is required" },
        { status: 400 }
      );
    }

    if (!body.url) {
      return NextResponse.json(
        { success: false, error: "YouTube URL is required" },
        { status: 400 }
      );
    }

    if (!body.published_at) {
      return NextResponse.json(
        { success: false, error: "Published date is required" },
        { status: 400 }
      );
    }

    // Check if video_id already exists
    const [existingVideo] = await connection.execute(
      'SELECT id FROM youtube_videos WHERE video_id = ?',
      [body.video_id]
    );

    if (existingVideo.length > 0) {
      return NextResponse.json(
        { success: false, error: "YouTube video ID already exists" },
        { status: 400 }
      );
    }

    // Validate country_id if provided
    if (body.country_id) {
      const [existingCountry] = await connection.execute(
        'SELECT id FROM countries WHERE id = ?',
        [body.country_id]
      );

      if (existingCountry.length === 0) {
        return NextResponse.json(
          { success: false, error: "Invalid country selected" },
          { status: 400 }
        );
      }
    }

    const [result] = await connection.execute(
      `INSERT INTO youtube_videos (
        title, description, thumbnail, video_id, url, 
        published_at, view_count, duration, is_active, featured, country_id
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        body.title,
        body.description || null,
        body.thumbnail || null,
        body.video_id,
        body.url,
        body.published_at,
        body.view_count || '0',
        body.duration || null,
        body.is_active ? 1 : 0,
        body.featured ? 1 : 0,
        body.country_id || null
      ]
    );

    const insertId = result.insertId;
    
    // Fetch the created video with country information
    const [rows] = await connection.execute(
      `SELECT 
        yv.*,
        c.id as country_id,
        c.country as country_name,
        c.code as country_code
       FROM youtube_videos yv
       LEFT JOIN countries c ON yv.country_id = c.id
       WHERE yv.id = ?`,
      [insertId]
    );

    return NextResponse.json({ 
      success: true, 
      data: rows[0],
      message: 'Video created successfully'
    }, { status: 201 });
  } catch (error) {
    console.error("Error creating video:", error);
    return NextResponse.json(
      { success: false, error: error.message },
      { status: 500 }
    );
  } finally {
    if (connection) {
      connection.release();
    }
  }
}

// PUT - Update video
export async function PUT(req) {
  let connection;

  try {
    connection = await pool.getConnection();
    const { searchParams } = new URL(req.url);
    const id = searchParams.get('id');
    
    if (!id) {
      return NextResponse.json(
        { success: false, error: "Video ID is required" },
        { status: 400 }
      );
    }

    const body = await req.json();
    
    // Check if video exists
    const [existingVideo] = await connection.execute(
      'SELECT id FROM youtube_videos WHERE id = ?',
      [id]
    );

    if (existingVideo.length === 0) {
      return NextResponse.json(
        { success: false, error: "Video not found" },
        { status: 404 }
      );
    }

    // Validate country_id if provided
    if (body.country_id) {
      const [existingCountry] = await connection.execute(
        'SELECT id FROM countries WHERE id = ?',
        [body.country_id]
      );

      if (existingCountry.length === 0) {
        return NextResponse.json(
          { success: false, error: "Invalid country selected" },
          { status: 400 }
        );
      }
    }

    const [result] = await connection.execute(
      `UPDATE youtube_videos SET 
        title = ?, description = ?, thumbnail = ?, video_id = ?, url = ?, 
        published_at = ?, view_count = ?, duration = ?, is_active = ?, featured = ?, country_id = ?
      WHERE id = ?`,
      [
        body.title,
        body.description || null,
        body.thumbnail || null,
        body.video_id,
        body.url,
        body.published_at,
        body.view_count || '0',
        body.duration || null,
        body.is_active ? 1 : 0,
        body.featured ? 1 : 0,
        body.country_id || null,
        id
      ]
    );

    if (result.affectedRows === 0) {
      return NextResponse.json(
        { success: false, error: "Video not found" },
        { status: 404 }
      );
    }

    // Fetch the updated video with country information
    const [rows] = await connection.execute(
      `SELECT 
        yv.*,
        c.id as country_id,
        c.country as country_name,
        c.code as country_code
       FROM youtube_videos yv
       LEFT JOIN countries c ON yv.country_id = c.id
       WHERE yv.id = ?`,
      [id]
    );

    return NextResponse.json({ 
      success: true, 
      data: rows[0],
      message: 'Video updated successfully'
    });
  } catch (error) {
    console.error("Error updating video:", error);
    return NextResponse.json(
      { success: false, error: error.message },
      { status: 500 }
    );
  } finally {
    if (connection) {
      connection.release();
    }
  }
}

// DELETE - Remove video
export async function DELETE(req) {
  let connection;

  try {
    connection = await pool.getConnection();
    const { searchParams } = new URL(req.url);
    const id = searchParams.get('id');
    
    if (!id) {
      return NextResponse.json(
        { success: false, error: "Video ID is required" },
        { status: 400 }
      );
    }

    const [result] = await connection.execute(
      'DELETE FROM youtube_videos WHERE id = ?',
      [id]
    );

    if (result.affectedRows === 0) {
      return NextResponse.json(
        { success: false, error: "Video not found" },
        { status: 404 }
      );
    }

    return NextResponse.json({ 
      success: true, 
      message: 'Video deleted successfully'
    });
  } catch (error) {
    console.error("Error deleting video:", error);
    return NextResponse.json(
      { success: false, error: error.message },
      { status: 500 }
    );
  } finally {
    if (connection) {
      connection.release();
    }
  }
}

// PATCH - Get single video
export async function PATCH(req) {
  let connection;
  
  try {
    connection = await pool.getConnection();
    const { searchParams } = new URL(req.url);
    const id = searchParams.get('id');
    
    if (!id) {
      return NextResponse.json(
        { success: false, error: "Video ID is required" },
        { status: 400 }
      );
    }

    const [videos] = await connection.execute(
      'SELECT * FROM youtube_videos WHERE id = ?',
      [id]
    );

    if (videos.length === 0) {
      return NextResponse.json(
        { success: false, error: "Video not found" },
        { status: 404 }
      );
    }

    return NextResponse.json({
      success: true,
      data: videos[0]
    });
  } catch (error) {
    console.error("PATCH /youtube-videos error:", error);
    return NextResponse.json(
      { 
        success: false,
        error: "Failed to fetch video", 
        details: error.message 
      },
      { status: 500 }
    );
  } finally {
    if (connection) {
      connection.release();
    }
  }
}