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

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

    try {
        const { searchParams } = new URL(request.url);
        const page = parseInt(searchParams.get('page')) || 1;
        const limit = parseInt(searchParams.get('limit')) || 10;
        const offset = (page - 1) * limit;

        // Get total count
        const [countResult] = await connection.query('SELECT COUNT(*) as total FROM tbl_our_products');
        const totalItems = countResult[0].total;

        // Get paginated data
        const [products] = await connection.query(`
            SELECT 
                id,
                our_product_course_name,
                our_product_course_fee,
                city_id,
                country_id,
                university_id,
                our_product_cgpa,
                our_product_currency,
                our_product_intake_month,
                our_product_moi,
                our_product_remarks,
                our_product_other_detail,
                our_product_english_test,
                our_product_admission_test,
                our_product_admission_dead_line_start_date,
                our_product_admission_dead_line_end_date,
                our_product_scholarship_status,
                our_product_scholarship,
                course_category_id,
                interested_program_id,
                created_at,
                updated_at 
            FROM tbl_our_products 
            ORDER BY created_at DESC 
            LIMIT ? OFFSET ?
        `, [limit, offset]);

        return NextResponse.json({
            success: true,
            products,
            meta: {
                totalItems,
                totalPages: Math.ceil(totalItems / limit),
                currentPage: page,
                itemsPerPage: limit
            }
        });

    } catch (error) {
        console.error('Error fetching products:', error);
        return NextResponse.json(
            { success: false, error: 'Failed to fetch products' },
            { status: 500 }
        );
    } finally {
        connection.release();
    }
}

export async function POST(request) {
    const connection = await pool.getConnection();

    try {
        const body = await request.json();
        const {
            our_product_course_name,
            our_product_course_fee,
            city_id,
            country_id,
            university_id,
            our_product_cgpa,
            our_product_currency,
            our_product_intake_month,
            our_product_moi,
            our_product_remarks,
            our_product_other_detail,
            our_product_english_test,
            our_product_admission_test,
            our_product_admission_dead_line_start_date,
            our_product_admission_dead_line_end_date,
            our_product_scholarship_status,
            our_product_scholarship,
            course_category_id,
            interested_program_id
        } = body;

        // Validate required fields
        if (!our_product_course_name || !city_id || !country_id || !university_id || !course_category_id) {
            return NextResponse.json(
                { 
                    success: false, 
                    error: 'Course name, city, country, university, and category are required' 
                },
                { status: 400 }
            );
        }

        // Check for duplicate entry based on composite key
        const [existingProduct] = await connection.query(
            `SELECT id FROM tbl_our_products 
             WHERE our_product_course_name = ? 
             AND city_id = ? 
             AND country_id = ? 
             AND university_id = ? 
             AND course_category_id = ?`,
            [our_product_course_name, city_id, country_id, university_id, course_category_id]
        );

        if (existingProduct.length > 0) {
            return NextResponse.json(
                { 
                    success: false, 
                    error: 'Product with same course name, city, country, university, and category already exists' 
                },
                { status: 400 }
            );
        }

        // Insert new product
        const [result] = await connection.query(`
            INSERT INTO tbl_our_products (
                our_product_course_name,
                our_product_course_fee,
                city_id,
                country_id,
                university_id,
                our_product_cgpa,
                our_product_currency,
                our_product_intake_month,
                our_product_moi,
                our_product_remarks,
                our_product_other_detail,
                our_product_english_test,
                our_product_admission_test,
                our_product_admission_dead_line_start_date,
                our_product_admission_dead_line_end_date,
                our_product_scholarship_status,
                our_product_scholarship,
                course_category_id,
                interested_program_id,
                created_at,
                updated_at
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())
        `, [
            our_product_course_name,
            our_product_course_fee || null,
            city_id,
            country_id,
            university_id,
            our_product_cgpa || null,
            our_product_currency,
            our_product_intake_month,
            our_product_moi,
            our_product_remarks,
            our_product_other_detail,
            our_product_english_test,
            our_product_admission_test,
            our_product_admission_dead_line_start_date || null,
            our_product_admission_dead_line_end_date || null,
            our_product_scholarship_status,
            our_product_scholarship,
            course_category_id,
            interested_program_id || null
        ]);

        // Get the inserted product
        const [newProduct] = await connection.query(
            'SELECT * FROM tbl_our_products WHERE id = ?',
            [result.insertId]
        );

        return NextResponse.json({
            success: true,
            product: newProduct[0]
        });

    } catch (error) {
        console.error('Error creating product:', error);
        return NextResponse.json(
            { success: false, error: 'Failed to create product' },
            { status: 500 }
        );
    } finally {
        connection.release();
    }
}

export async function PUT(request) {
    const connection = await pool.getConnection();

    try {
        const { searchParams } = new URL(request.url);
        const id = searchParams.get('id');
        const body = await request.json();
        const {
            our_product_course_name,
            our_product_course_fee,
            city_id,
            country_id,
            university_id,
            our_product_cgpa,
            our_product_currency,
            our_product_intake_month,
            our_product_moi,
            our_product_remarks,
            our_product_other_detail,
            our_product_english_test,
            our_product_admission_test,
            our_product_admission_dead_line_start_date,
            our_product_admission_dead_line_end_date,
            our_product_scholarship_status,
            our_product_scholarship,
            course_category_id,
            interested_program_id
        } = body;

        if (!id) {
            return NextResponse.json(
                { success: false, error: 'Product ID is required' },
                { status: 400 }
            );
        }

        // Validate required fields
        if (!our_product_course_name || !city_id || !country_id || !university_id || !course_category_id) {
            return NextResponse.json(
                { 
                    success: false, 
                    error: 'Course name, city, country, university, and category are required' 
                },
                { status: 400 }
            );
        }

        // Check for duplicate entry based on composite key (excluding current product)
        const [existingProduct] = await connection.query(
            `SELECT id FROM tbl_our_products 
             WHERE our_product_course_name = ? 
             AND city_id = ? 
             AND country_id = ? 
             AND university_id = ? 
             AND course_category_id = ?
             AND id != ?`,
            [our_product_course_name, city_id, country_id, university_id, course_category_id, id]
        );

        if (existingProduct.length > 0) {
            return NextResponse.json(
                { 
                    success: false, 
                    error: 'Another product with same course name, city, country, university, and category already exists' 
                },
                { status: 400 }
            );
        }

        // Update product
        await connection.query(`
            UPDATE tbl_our_products 
            SET 
                our_product_course_name = ?,
                our_product_course_fee = ?,
                city_id = ?,
                country_id = ?,
                university_id = ?,
                our_product_cgpa = ?,
                our_product_currency = ?,
                our_product_intake_month = ?,
                our_product_moi = ?,
                our_product_remarks = ?,
                our_product_other_detail = ?,
                our_product_english_test = ?,
                our_product_admission_test = ?,
                our_product_admission_dead_line_start_date = ?,
                our_product_admission_dead_line_end_date = ?,
                our_product_scholarship_status = ?,
                our_product_scholarship = ?,
                course_category_id = ?,
                interested_program_id = ?,
                updated_at = NOW()
            WHERE id = ?
        `, [
            our_product_course_name,
            our_product_course_fee || null,
            city_id,
            country_id,
            university_id,
            our_product_cgpa || null,
            our_product_currency,
            our_product_intake_month,
            our_product_moi,
            our_product_remarks,
            our_product_other_detail,
            our_product_english_test,
            our_product_admission_test,
            our_product_admission_dead_line_start_date || null,
            our_product_admission_dead_line_end_date || null,
            our_product_scholarship_status,
            our_product_scholarship,
            course_category_id,
            interested_program_id || null,
            id
        ]);

        // Get updated product
        const [updatedProduct] = await connection.query(
            'SELECT * FROM tbl_our_products WHERE id = ?',
            [id]
        );

        return NextResponse.json({
            success: true,
            product: updatedProduct[0]
        });

    } catch (error) {
        console.error('Error updating product:', error);
        return NextResponse.json(
            { success: false, error: 'Failed to update product' },
            { status: 500 }
        );
    } finally {
        connection.release();
    }
}

export async function DELETE(request) {
    const connection = await pool.getConnection();

    try {
        const { searchParams } = new URL(request.url);
        const id = searchParams.get('id');

        if (!id) {
            return NextResponse.json(
                { success: false, error: 'Product ID is required' },
                { status: 400 }
            );
        }

        await connection.query('DELETE FROM tbl_our_products WHERE id = ?', [id]);

        return NextResponse.json({
            success: true,
            message: 'Product deleted successfully'
        });

    } catch (error) {
        console.error('Error deleting product:', error);
        return NextResponse.json(
            { success: false, error: 'Failed to delete product' },
            { status: 500 }
        );
    } finally {
        connection.release();
    }
}