import { NextResponse } from 'next/server';
import db from '@/lib/db';

// GET all products or filter by category with pagination
export async function GET(request: Request) {
  const { searchParams } = new URL(request.url);
  const categorySlug = searchParams.get('category');
  const isAffiliate = searchParams.get('affiliate');
  const page = parseInt(searchParams.get('page') || '1');
  const limit = parseInt(searchParams.get('limit') || '25');
  const publishedOnly = searchParams.get('published') === 'true';

  try {
    let countSql = `
      SELECT COUNT(*) as total 
      FROM Product p 
      JOIN Category c ON p.categoryId = c.id
      WHERE 1=1
    `;
    let sql = `
      SELECT p.*, c.name as categoryName, c.slug as categorySlug 
      FROM Product p 
      JOIN Category c ON p.categoryId = c.id
      WHERE 1=1
    `;
    const params: any[] = [];

    if (categorySlug) {
      sql += ` AND c.slug = ?`;
      countSql += ` AND c.slug = ?`;
      params.push(categorySlug);
    }

    if (isAffiliate !== null) {
      sql += ` AND p.isAffiliate = ?`;
      countSql += ` AND p.isAffiliate = ?`;
      params.push(isAffiliate === 'true' ? 1 : 0);
    }

    if (publishedOnly) {
      sql += ` AND p.publishStatus = 'Published'`;
      countSql += ` AND p.publishStatus = 'Published'`;
    }

    // Get total count
    const countResult = await db.query(countSql, params);
    const total = Array.isArray(countResult) ? (countResult[0]?.total || 0) : 0;

    // Add pagination
    const offset = (page - 1) * limit;
    sql += ` ORDER BY p.createdAt DESC LIMIT ? OFFSET ?`;
    const products = await db.query(sql, [...params, limit, offset]);

    return NextResponse.json({
      products,
      pagination: {
        page,
        limit,
        total,
        totalPages: Math.ceil(total / limit)
      }
    });
  } catch (error: any) {
    return NextResponse.json({ error: error.message }, { status: 500 });
  }
}

// Generate a UUID v4 compatible ID
function generateId(): string {
  return 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g, (c) => {
    const r = (Math.random() * 16) | 0;
    const v = c === 'x' ? r : (r & 0x3) | 0x8;
    return v.toString(16);
  });
}

// POST new product
export async function POST(request: Request) {
  try {
    const body = await request.json();
    const {
      name, slug, description, price, image, brand, stock, categoryId, isAffiliate, affiliateLink, benefits, badge,
      productDescription, publishStatus, commission, revenue, cookieLife, faqs, keywords
    } = body;

    const id = generateId();
    const now = new Date().toISOString();
    const faqsJson = faqs ? JSON.stringify(faqs) : null;

    // Ensure slug is unique by appending a suffix if it already exists
    let finalSlug = slug;
    let slugExists = true;
    let attempt = 0;
    while (slugExists) {
      const existing = await db.query('SELECT id FROM Product WHERE slug = ?', [finalSlug]);
      const rows = Array.isArray(existing) ? existing : [];
      if (rows.length === 0) {
        slugExists = false;
      } else {
        attempt++;
        finalSlug = slug + '-' + Date.now() + '-' + attempt;
      }
    }

    const sql = `
      INSERT INTO Product (id, name, slug, description, price, image, brand, stock, categoryId, isAffiliate, affiliateLink, benefits, badge, productDescription, publishStatus, commission, revenue, cookieLife, faqs, keywords, createdAt, updatedAt)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `;

    await db.execute(sql, [
      id, name, finalSlug, description, parseFloat(price), image, brand, parseInt(stock) || 0, categoryId, isAffiliate ? 1 : 0, affiliateLink, benefits || '', badge || '',
      productDescription || '', publishStatus || 'Draft', parseFloat(commission) || 10, parseFloat(revenue) || 0, parseInt(cookieLife) || 30, faqsJson, keywords || '', now, now
    ]);

    return NextResponse.json({ success: true, id }, { status: 201 });
  } catch (error: any) {
    return NextResponse.json({ error: error.message }, { status: 500 });
  }
}

// PUT (Update) product
export async function PUT(request: Request) {
  try {
    const body = await request.json();
    const {
      id, name, slug, description, price, image, brand, stock, categoryId, isAffiliate, affiliateLink, benefits, badge,
      productDescription, publishStatus, commission, revenue, cookieLife, faqs, keywords
    } = body;

    const now = new Date().toISOString();
    const faqsJson = faqs ? JSON.stringify(faqs) : null;

    // Fix Bug #3: Check slug uniqueness — allow if slug belongs to the same product
    let finalSlug = slug;
    if (slug) {
      const existing = await db.query('SELECT id FROM Product WHERE slug = ? AND id != ?', [slug, id]);
      const rows = Array.isArray(existing) ? existing : [];
      if (rows.length > 0) {
        // Slug belongs to another product — append a suffix
        finalSlug = slug + '-' + Date.now();
      }
    }

    const sql = `
      UPDATE Product 
      SET name = ?, slug = ?, description = ?, price = ?, image = ?, brand = ?, stock = ?, categoryId = ?, isAffiliate = ?, affiliateLink = ?, benefits = ?, badge = ?, productDescription = ?, publishStatus = ?, commission = ?, revenue = ?, cookieLife = ?, faqs = ?, keywords = ?, updatedAt = ?
      WHERE id = ?
    `;

    await db.execute(sql, [
      name, finalSlug, description, parseFloat(price), image, brand, parseInt(stock) || 0, categoryId, isAffiliate ? 1 : 0, affiliateLink, benefits || '', badge || '',
      productDescription || '', publishStatus || 'Draft', parseFloat(commission) || 10, parseFloat(revenue) || 0, parseInt(cookieLife) || 30, faqsJson, keywords || '', now, id
    ]);

    return NextResponse.json({ success: true, id });
  } catch (error: any) {
    return NextResponse.json({ error: error.message }, { status: 500 });
  }
}

// DELETE product
export async function DELETE(request: Request) {
  try {
    const { searchParams } = new URL(request.url);
    const id = searchParams.get('id');

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

    await db.execute('DELETE FROM Product WHERE id = ?', [id]);
    return NextResponse.json({ success: true });
  } catch (error: any) {
    return NextResponse.json({ error: error.message }, { status: 500 });
  }
}