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 short_description LIKE ? OR meta_title LIKE ?";
      whereParams.push(`%${search}%`, `%${search}%`, `%${search}%`, `%${search}%`);
    }

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

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

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

    return NextResponse.json({
      success: true,
      data: staticPages,
      meta: {
        totalItems: totalCount,
        totalPages,
        currentPage: page,
        itemsPerPage: limit,
        hasNextPage: page < totalPages,
        hasPreviousPage: page > 1,
      },
    });
  } catch (error) {
    console.error("GET /static-pages error:", error);
    return NextResponse.json(
      { 
        success: false,
        error: "Failed to fetch static pages", 
        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 [existingPage] = await connection.query(
      'SELECT id FROM static_pages WHERE slug = ?',
      [slug]
    );

    if (existingPage.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 static page
    const [result] = await connection.query(
      `INSERT INTO static_pages (
        simple_title,
        color_title, 
        slug,
        short_description,
        top_content,
        middle_content,
        bottom_content,
        meta_title,
        meta_description,
        faqs,
        created_at, 
        updated_at
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        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 pageId = result.insertId;

    // Fetch the created static page
    const [rows] = await connection.query(
      `SELECT * FROM static_pages WHERE id = ?`,
      [pageId]
    );

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

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

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

    // Fetch single static page
    const [pages] = await connection.query(
      `SELECT * FROM static_pages WHERE id = ?`,
      [id]
    );

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

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

    return NextResponse.json({
      success: true,
      data: page
    });
  } catch (error) {
    console.error("PATCH /static-pages error:", error);
    return NextResponse.json(
      { 
        success: false,
        error: "Failed to fetch static page", 
        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: "Static page 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 page)
    if (body.slug && body.slug.trim()) {
      const [existingPage] = await connection.query(
        'SELECT id FROM static_pages WHERE slug = ? AND id != ?',
        [body.slug.trim(), id]
      );

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

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

    // Determine the slug to use
    let slugToUse;
    if (body.slug && body.slug.trim()) {
      slugToUse = body.slug.trim();
    } else {
      slugToUse = currentPage[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 static page
    const [result] = await connection.query(
      `UPDATE static_pages SET 
        simple_title = ?,
        color_title = ?,
        slug = ?,
        short_description = ?,
        top_content = ?,
        middle_content = ?,
        bottom_content = ?,
        meta_title = ?,
        meta_description = ?,
        faqs = ?,
        updated_at = ?
       WHERE id = ?`,
      [
        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: "Static page not found" },
        { status: 404 }
      );
    }

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

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

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

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

    // Check if the static page exists
    const [pageExists] = await connection.query(
      'SELECT id FROM static_pages WHERE id = ?',
      [id]
    );

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

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

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

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

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