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

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 skip = (page - 1) * limit;

    // Build WHERE clause
    let whereSql = "";
    const whereParams = [];
    
    if (search) {
      whereSql = "WHERE simple_title LIKE ? OR color_title LIKE ? OR name LIKE ? OR short_description LIKE ? OR meta_title LIKE ?";
      whereParams.push(`%${search}%`, `%${search}%`, `%${search}%`, `%${search}%`, `%${search}%`);
    }

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

    // Data query
    const dataQuery = `
      SELECT 
        id,
        simple_title,
        color_title,
        name,
        slug,
        short_description,
        meta_title,
        meta_description,
        about_title,
        about_content,
        about_image,
        about_image_alt_text,
        about_image_direction,
        created_at,
        updated_at
      FROM silo_routes 
      ${whereSql}
      ORDER BY created_at DESC
      LIMIT ? OFFSET ?
    `;
    
    const dataParams = [...whereParams, limit, skip];
    const [siloRoutes] = await connection.query(dataQuery, dataParams);

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

    return NextResponse.json({
      success: true,
      data: siloRoutes,
      meta: {
        totalItems: totalCount,
        totalPages,
        currentPage: page,
        itemsPerPage: limit,
        hasNextPage: page < totalPages,
        hasPreviousPage: page > 1,
      },
    });
  } catch (error) {
    console.error("GET /silo-routes error:", error);
    return NextResponse.json(
      { 
        success: false,
        error: "Failed to fetch silo routes", 
        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.simple_title || !body.simple_title.trim()) {
      return NextResponse.json(
        { success: false, error: "Simple title is required" },
        { status: 400 }
      );
    }
    
    if (!body.name || !body.name.trim()) {
      return NextResponse.json(
        { success: false, error: "Name is required" },
        { status: 400 }
      );
    }

    // Generate slug from simple_title if not provided
    let slug = body.slug;
    if (!slug || !slug.trim()) {
      slug = slugify(body.simple_title, {
        lower: true,
        strict: true,
        trim: true
      });
    }

    // Check if slug already exists
    const [existingRoute] = await connection.query(
      'SELECT id FROM silo_routes WHERE slug = ?',
      [slug]
    );

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

    // Prepare FAQs
    let faqs = [];
    if (body.faqs) {
      try {
        faqs = typeof body.faqs === 'string' ? JSON.parse(body.faqs) : body.faqs;
      } catch (e) {
        faqs = [];
      }
    }

    // Insert silo route
    const [result] = await connection.query(
      `INSERT INTO silo_routes (
        simple_title,
        color_title,
        name, 
        slug,
        short_description,
        top_content,
        bottom_content,
        meta_title,
        meta_description,
        faqs,
        about_title,
        about_content,
        about_image,
        about_image_alt_text,
        about_image_direction,
        created_at, 
        updated_at
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        body.simple_title.trim(),
        body.color_title || "",
        body.name.trim(),
        slug,
        body.short_description || "",
        body.top_content || "",
        body.bottom_content || "",
        body.meta_title || "",
        body.meta_description || "",
        JSON.stringify(faqs),
        body.about_title || null,
        body.about_content || null,
        body.about_image || null,
        body.about_image_alt_text || null,
        body.about_image_direction || 'left',
        new Date(),
        new Date(),
      ]
    );

    const routeId = result.insertId;

    // Fetch the created silo route
    const [rows] = await connection.query(
      `SELECT * FROM silo_routes WHERE id = ?`,
      [routeId]
    );

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

  } catch (error) {
    console.error("Error creating silo route:", error);
    
    if (error.code === 'ER_DUP_ENTRY') {
      return NextResponse.json(
        { success: false, error: "A silo route with this slug already exists" },
        { status: 400 }
      );
    }

    return NextResponse.json(
      { 
        success: false, 
        error: "Failed to create silo route",
        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: "Silo route ID is required" },
        { status: 400 }
      );
    }

    // Fetch single silo route
    const [routes] = await connection.query(
      `SELECT * FROM silo_routes WHERE id = ?`,
      [id]
    );

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

    // Parse JSON fields
    const route = routes[0];
    if (route.faqs) {
      try {
        route.faqs = typeof route.faqs === 'string' ? JSON.parse(route.faqs) : route.faqs;
      } catch (e) {
        route.faqs = [];
      }
    }

    return NextResponse.json({
      success: true,
      data: route
    });
  } catch (error) {
    console.error("PATCH /silo-routes error:", error);
    return NextResponse.json(
      { 
        success: false,
        error: "Failed to fetch silo route", 
        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: "Silo route ID is required" },
        { status: 400 }
      );
    }

    const body = await req.json();

    // Validation
    if (!body.simple_title || !body.simple_title.trim()) {
      return NextResponse.json(
        { success: false, error: "Simple title is required" },
        { status: 400 }
      );
    }
    
    if (!body.name || !body.name.trim()) {
      return NextResponse.json(
        { success: false, error: "Name is required" },
        { status: 400 }
      );
    }

    // Check if slug already exists (excluding current route)
    if (body.slug && body.slug.trim()) {
      const [existingRoute] = await connection.query(
        'SELECT id FROM silo_routes WHERE slug = ? AND id != ?',
        [body.slug.trim(), id]
      );

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

    // Get current route data to check if slug is being updated
    const [currentRoute] = await connection.query(
      'SELECT slug FROM silo_routes WHERE id = ?',
      [id]
    );

    // Determine the slug to use
    let slugToUse;
    if (body.slug && body.slug.trim()) {
      slugToUse = body.slug.trim();
    } else {
      slugToUse = currentRoute[0]?.slug;
    }

    // Prepare FAQs
    let faqs = [];
    if (body.faqs) {
      try {
        faqs = typeof body.faqs === 'string' ? JSON.parse(body.faqs) : body.faqs;
      } catch (e) {
        faqs = [];
      }
    }

    // Update silo route
    const [result] = await connection.query(
      `UPDATE silo_routes SET 
        simple_title = ?,
        color_title = ?,
        name = ?,
        slug = ?,
        short_description = ?,
        top_content = ?,
        bottom_content = ?,
        meta_title = ?,
        meta_description = ?,
        faqs = ?,
        about_title = ?,
        about_content = ?,
        about_image = ?,
        about_image_alt_text = ?,
        about_image_direction = ?,
        updated_at = ?
       WHERE id = ?`,
      [
        body.simple_title.trim(),
        body.color_title || "",
        body.name.trim(),
        slugToUse,
        body.short_description || "",
        body.top_content || "",
        body.bottom_content || "",
        body.meta_title || "",
        body.meta_description || "",
        JSON.stringify(faqs),
        body.about_title || null,
        body.about_content || null,
        body.about_image || null,
        body.about_image_alt_text || null,
        body.about_image_direction || 'left',
        new Date(),
        id
      ]
    );

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

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

    // Parse JSON fields for response
    const route = rows[0];
    if (route.faqs) {
      try {
        route.faqs = typeof route.faqs === 'string' ? JSON.parse(route.faqs) : route.faqs;
      } catch (e) {
        route.faqs = [];
      }
    }

    return NextResponse.json({ 
      success: true, 
      data: route,
      message: 'Silo route updated successfully'
    });
  } catch (error) {
    console.error("Error updating silo route:", error);
    
    if (error.code === 'ER_DUP_ENTRY') {
      return NextResponse.json(
        { success: false, error: "A silo route with this slug already exists" },
        { status: 400 }
      );
    }

    return NextResponse.json(
      { 
        success: false, 
        error: "Failed to update silo route",
        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: "Silo route ID is required" },
        { status: 400 }
      );
    }

    // Check if the silo route exists
    const [routeExists] = await connection.query(
      'SELECT id FROM silo_routes WHERE id = ?',
      [id]
    );

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

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

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

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

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