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 siloRouteId = searchParams.get("silo_route_id") || "";
    const skip = (page - 1) * limit;

    // Build WHERE clause
    let whereSql = "";
    const whereParams = [];
    const conditions = [];
    
    if (search) {
      conditions.push("(srd.simple_title LIKE ? OR srd.color_title LIKE ? OR srd.short_description LIKE ? OR srd.meta_title LIKE ?)");
      whereParams.push(`%${search}%`, `%${search}%`, `%${search}%`, `%${search}%`);
    }
    
    if (siloRouteId) {
      conditions.push("srd.silo_route_id = ?");
      whereParams.push(siloRouteId);
    }
    
    if (conditions.length > 0) {
      whereSql = `WHERE ${conditions.join(" AND ")}`;
    }

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

    // Data query with silo route info
    const dataQuery = `
      SELECT 
        srd.id,
        srd.simple_title,
        srd.color_title,
        srd.slug,
        srd.short_description,
        srd.meta_title,
        srd.meta_description,
        srd.created_at,
        srd.updated_at,
        srd.silo_route_id,
        sr.name as silo_route_name,
        sr.slug as silo_route_slug
      FROM silo_route_details srd
      LEFT JOIN silo_routes sr ON srd.silo_route_id = sr.id
      ${whereSql}
      ORDER BY srd.created_at DESC
      LIMIT ? OFFSET ?
    `;
    
    const dataParams = [...whereParams, limit, skip];
    const [siloRouteDetails] = await connection.query(dataQuery, dataParams);

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

    return NextResponse.json({
      success: true,
      data: siloRouteDetails,
      meta: {
        totalItems: totalCount,
        totalPages,
        currentPage: page,
        itemsPerPage: limit,
        hasNextPage: page < totalPages,
        hasPreviousPage: page > 1,
      },
    });
  } catch (error) {
    console.error("GET /silo-route-details error:", error);
    return NextResponse.json(
      { 
        success: false,
        error: "Failed to fetch silo route details", 
        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 }
      );
    }

    // 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_route_details 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 detail
    const [result] = await connection.query(
      `INSERT INTO silo_route_details (
        silo_route_id,
        simple_title, 
        color_title,
        slug,
        short_description,
        top_content,
        middle_content,
        bottom_content,
        meta_title,
        meta_description,
        faqs,
        created_at, 
        updated_at
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        body.silo_route_id || null,
        body.simple_title.trim(),
        body.color_title || "",
        slug,
        body.short_description || "",
        body.top_content || "",
        body.middle_content || "",
        body.bottom_content || "",
        body.meta_title || "",
        body.meta_description || "",
        JSON.stringify(faqs),
        new Date(),
        new Date(),
      ]
    );

    const routeId = result.insertId;

    // Fetch the created silo route detail with silo route info
    const [rows] = await connection.query(
      `SELECT srd.*, sr.name as silo_route_name, sr.slug as silo_route_slug
       FROM silo_route_details srd
       LEFT JOIN silo_routes sr ON srd.silo_route_id = sr.id
       WHERE srd.id = ?`,
      [routeId]
    );

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

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

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

    // Fetch single silo route detail with silo route info
    const [routes] = await connection.query(
      `SELECT srd.*, sr.name as silo_route_name, sr.slug as silo_route_slug
       FROM silo_route_details srd
       LEFT JOIN silo_routes sr ON srd.silo_route_id = sr.id
       WHERE srd.id = ?`,
      [id]
    );

    if (routes.length === 0) {
      return NextResponse.json(
        { success: false, error: "Silo route detail 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-route-details error:", error);
    return NextResponse.json(
      { 
        success: false,
        error: "Failed to fetch silo route detail", 
        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 detail 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 }
      );
    }

    // Check if slug already exists (excluding current route)
    if (body.slug && body.slug.trim()) {
      const [existingRoute] = await connection.query(
        'SELECT id FROM silo_route_details 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_route_details 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 detail
    const [result] = await connection.query(
      `UPDATE silo_route_details SET 
        silo_route_id = ?,
        simple_title = ?, 
        color_title = ?,
        slug = ?,
        short_description = ?,
        top_content = ?,
        middle_content = ?,
        bottom_content = ?,
        meta_title = ?,
        meta_description = ?,
        faqs = ?,
        updated_at = ?
       WHERE id = ?`,
      [
        body.silo_route_id || null,
        body.simple_title.trim(),
        body.color_title || "",
        slugToUse,
        body.short_description || "",
        body.top_content || "",
        body.middle_content || "",
        body.bottom_content || "",
        body.meta_title || "",
        body.meta_description || "",
        JSON.stringify(faqs),
        new Date(),
        id
      ]
    );

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

    // Fetch updated silo route detail with silo route info
    const [rows] = await connection.query(
      `SELECT srd.*, sr.name as silo_route_name, sr.slug as silo_route_slug
       FROM silo_route_details srd
       LEFT JOIN silo_routes sr ON srd.silo_route_id = sr.id
       WHERE srd.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 detail updated successfully'
    });
  } catch (error) {
    console.error("Error updating silo route detail:", error);
    
    if (error.code === 'ER_DUP_ENTRY') {
      return NextResponse.json(
        { success: false, error: "A silo route detail with this slug already exists" },
        { status: 400 }
      );
    }

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

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

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

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

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

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

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