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

export async function GET(request) {
  let connection;
  
  try {
    connection = await pool.getConnection();

    const { searchParams } = new URL(request.url);
    const page = Number.parseInt(searchParams.get("page") || "1", 10) || 1;
    const limit = Number.parseInt(searchParams.get("limit") || "15", 10) || 15;
    const search = (searchParams.get("search") || "").trim();
    const status = searchParams.get("status") || "";
    const skip = (page - 1) * limit;

    // Build WHERE clause for search and filters
    let whereSql = "";
    const whereParams = [];
    
    const conditions = [];
    
    if (search) {
      conditions.push("url_path LIKE ?");
      whereParams.push(`%${search}%`);
    }
    
    if (status) {
      if (status === 'active') {
        conditions.push("is_active = 1");
      } else if (status === 'inactive') {
        conditions.push("is_active = 0");
      }
    }
    
    if (conditions.length > 0) {
      whereSql = `WHERE ${conditions.join(" AND ")}`;
    }

    // Count query
    const countQuery = `
      SELECT COUNT(*) AS totalCount 
      FROM sitemap_urls 
      ${whereSql}
    `;
    
    const [countRows] = await connection.query(countQuery, whereParams);
    const totalCount = countRows[0]?.totalCount ?? 0;

    // Data query
    const dataQuery = `
      SELECT 
        id,
        url_path,
        priority,
        last_modified,
        is_active,
        created_at,
        updated_at
      FROM sitemap_urls 
      ${whereSql}
      ORDER BY priority DESC, created_at DESC
      LIMIT ? OFFSET ?
    `;
    
    const dataParams = [...whereParams, limit, skip];
    const [urls] = await connection.query(dataQuery, dataParams);

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

    return NextResponse.json({
      success: true,
      data: urls,
      meta: {
        totalItems: totalCount,
        totalPages,
        currentPage: page,
        itemsPerPage: limit,
        hasNextPage: page < totalPages,
        hasPreviousPage: page > 1,
      },
    });
  } catch (error) {
    console.error("GET /sitemap error:", error);
    return NextResponse.json(
      { 
        success: false,
        error: "Failed to fetch sitemap URLs", 
        details: error.message 
      },
      { status: 500 }
    );
  } finally {
    if (connection) {
      connection.release();
    }
  }
}

export async function POST(req) {
  let connection;

  try {
    connection = await pool.getConnection();
    const body = await req.json();
    
    // Required field validation
    if (!body.url_path || !body.url_path.trim()) {
      return NextResponse.json(
        { success: false, error: "URL path is required" },
        { status: 400 }
      );
    }

    // Validate URL path format
    if (!body.url_path.startsWith('/')) {
      return NextResponse.json(
        { success: false, error: "URL path must start with '/'" },
        { status: 400 }
      );
    }

    // Validate priority
    const priority = parseFloat(body.priority) || 0.5;
    if (priority < 0 || priority > 1) {
      return NextResponse.json(
        { success: false, error: "Priority must be between 0.0 and 1.0" },
        { status: 400 }
      );
    }

    // Validate date
    let lastModified = null;
    if (body.last_modified) {
      const date = new Date(body.last_modified);
      if (isNaN(date.getTime())) {
        return NextResponse.json(
          { success: false, error: "Invalid date format for last_modified" },
          { status: 400 }
        );
      }
      lastModified = date.toISOString().split('T')[0];
    }

    // Check if URL already exists
    const [existingUrl] = await connection.query(
      'SELECT id FROM sitemap_urls WHERE url_path = ?',
      [body.url_path.trim()]
    );

    if (existingUrl.length > 0) {
      return NextResponse.json(
        { success: false, error: "URL path already exists. Please use a different URL path." },
        { status: 400 }
      );
    }

    // Insert URL
    const [result] = await connection.query(
      `INSERT INTO sitemap_urls (
        url_path, 
        priority, 
        last_modified, 
        is_active,
        created_at, 
        updated_at
      ) VALUES (?, ?, ?, ?, ?, ?)`,
      [
        body.url_path.trim(),
        priority,
        lastModified,
        body.is_active ? 1 : 0,
        new Date(),
        new Date(),
      ]
    );

    const urlId = result.insertId;

    // Fetch the created URL
    const [rows] = await connection.query(
      `SELECT * FROM sitemap_urls WHERE id = ?`,
      [urlId]
    );

    return NextResponse.json({ 
      success: true, 
      data: rows[0],
      message: 'URL added to sitemap successfully'
    }, { status: 201 });

  } catch (error) {
    console.error("Error adding sitemap URL:", error);
    
    // Handle specific database errors
    if (error.code === 'ER_DUP_ENTRY') {
      return NextResponse.json(
        { success: false, error: "A sitemap URL with this path already exists" },
        { status: 400 }
      );
    }

    return NextResponse.json(
      { 
        success: false, 
        error: "Failed to add sitemap URL",
        details: process.env.NODE_ENV === 'development' ? error.message : undefined
      },
      { status: 500 }
    );
  } finally {
    if (connection) {
      connection.release();
    }
  }
}

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: "URL ID is required" },
        { status: 400 }
      );
    }

    // Fetch single URL
    const [urls] = await connection.query(
      `SELECT * FROM sitemap_urls WHERE id = ?`,
      [id]
    );

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

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

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: "URL ID is required" },
        { status: 400 }
      );
    }

    const body = await req.json();

    // Check if URL exists
    const [existingUrl] = await connection.query(
      'SELECT id FROM sitemap_urls WHERE id = ?',
      [id]
    );

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

    // Prepare update data
    const updateFields = {};
    const updateParams = [];

    if (body.url_path !== undefined) {
      if (!body.url_path.trim()) {
        return NextResponse.json(
          { success: false, error: "URL path is required" },
          { status: 400 }
        );
      }
      if (!body.url_path.startsWith('/')) {
        return NextResponse.json(
          { success: false, error: "URL path must start with '/'" },
          { status: 400 }
        );
      }
      updateFields.url_path = body.url_path.trim();
    }

    if (body.priority !== undefined) {
      const priority = parseFloat(body.priority);
      if (priority < 0 || priority > 1) {
        return NextResponse.json(
          { success: false, error: "Priority must be between 0.0 and 1.0" },
          { status: 400 }
        );
      }
      updateFields.priority = priority;
    }

    if (body.last_modified !== undefined) {
      let lastModified = null;
      if (body.last_modified) {
        const date = new Date(body.last_modified);
        if (isNaN(date.getTime())) {
          return NextResponse.json(
            { success: false, error: "Invalid date format for last_modified" },
            { status: 400 }
          );
        }
        lastModified = date.toISOString().split('T')[0];
      }
      updateFields.last_modified = lastModified;
    }

    if (body.is_active !== undefined) {
      updateFields.is_active = body.is_active ? 1 : 0;
    }

    // If no fields to update
    if (Object.keys(updateFields).length === 0) {
      return NextResponse.json(
        { success: false, error: "No fields to update" },
        { status: 400 }
      );
    }

    // Check for duplicate URL path (if updating url_path)
    if (updateFields.url_path) {
      const [duplicateCheck] = await connection.query(
        'SELECT id FROM sitemap_urls WHERE url_path = ? AND id != ?',
        [updateFields.url_path, id]
      );

      if (duplicateCheck.length > 0) {
        return NextResponse.json(
          { success: false, error: "URL path already exists. Please use a different URL path." },
          { status: 400 }
        );
      }
    }

    // Build update query
    const setClause = Object.keys(updateFields)
      .map(field => `${field} = ?`)
      .join(', ');
    
    updateParams.push(...Object.values(updateFields));
    updateParams.push(new Date()); // updated_at
    updateParams.push(id);

    const updateQuery = `
      UPDATE sitemap_urls 
      SET ${setClause}, updated_at = ?
      WHERE id = ?
    `;

    const [result] = await connection.query(updateQuery, updateParams);

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

    // Fetch updated URL
    const [rows] = await connection.query(
      `SELECT * FROM sitemap_urls WHERE id = ?`,
      [id]
    );

    return NextResponse.json({ 
      success: true, 
      data: rows[0],
      message: 'Sitemap URL updated successfully'
    });
  } catch (error) {
    console.error("Error updating sitemap URL:", error);
    
    if (error.code === 'ER_DUP_ENTRY') {
      return NextResponse.json(
        { success: false, error: "A sitemap URL with this path already exists" },
        { status: 400 }
      );
    }

    return NextResponse.json(
      { 
        success: false, 
        error: "Failed to update sitemap URL",
        details: error.message 
      },
      { status: 500 }
    );
  } finally {
    if (connection) {
      connection.release();
    }
  }
}

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: "URL ID is required" },
        { status: 400 }
      );
    }

    // Check if the URL exists
    const [urlExists] = await connection.query(
      'SELECT id FROM sitemap_urls WHERE id = ?',
      [id]
    );

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

    // Delete the URL
    const [result] = await connection.query(
      `DELETE FROM sitemap_urls WHERE id = ?`,
      [id]
    );

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

    return NextResponse.json({ 
      success: true, 
      message: "Sitemap URL deleted successfully" 
    });

  } catch (error) {
    console.error("Error deleting sitemap URL:", error);
    
    return NextResponse.json(
      { 
        success: false, 
        error: "Failed to delete sitemap URL",
        details: process.env.NODE_ENV === 'development' ? error.message : undefined
      },
      { status: 500 }
    );
  } finally {
    if (connection) {
      connection.release();
    }
  }
}