import { NextResponse } from 'next/server';
import { withErrorHandler } from '@server/middleware/withErrorHandler';
import { withAuth, AuthedRequest } from '@server/middleware/withAuth';
import { ForbiddenError, NotFoundError, ValidationError } from '@server/errors';
import { db, languages, translations, translationKeys } from '@server/db/drizzle';
import { eq, and, or, ilike, sql, asc, count } from 'drizzle-orm';

export const GET = withErrorHandler(
  withAuth(async (req: AuthedRequest, context: { params: Promise<Record<string, string>> }) => {
    if (req.session.role !== 'superadmin' && req.session.role !== 'support') {
      throw new ForbiddenError();
    }
    const { langCode } = await context.params;
    const { searchParams } = new URL(req.url);
    const category = searchParams.get('category') || '';
    const search = searchParams.get('search') || '';
    const rawPage = parseInt(searchParams.get('page') || '1', 10);
    const rawLimit = parseInt(searchParams.get('limit') || '50', 10);
    const page = Math.max(1, Number.isFinite(rawPage) ? rawPage : 1);
    const limit = Math.min(200, Math.max(10, Number.isFinite(rawLimit) ? rawLimit : 50));
    const offset = (page - 1) * limit;

    /*
     * raw: SELECT * FROM public.languages WHERE code = $1
     */
    const langRows = await db.select().from(languages).where(eq(languages.code, langCode));
    if (langRows.length === 0) throw new NotFoundError('Language not found');
    const language = langRows[0];

    const dynamicConditions = [];
    if (category) {
      dynamicConditions.push(eq(translationKeys.category, category));
    }
    if (search) {
      const searchPat = `%${search}%`;
      dynamicConditions.push(
        or(
          ilike(translationKeys.key, searchPat),
          ilike(sql<string>`COALESCE(${translationKeys.description}, '')`, searchPat),
          ilike(sql<string>`COALESCE(${translations.value}, ${translationKeys.defaultValue})`, searchPat),
        )
      );
    }
    const whereClause = dynamicConditions.length > 0 ? and(...dynamicConditions) : undefined;

    /*
     * raw:
     * SELECT COUNT(*) FROM public.translation_keys tk
     * LEFT JOIN public.translations t ON t.key_id = tk.id AND t.lang_code = $1
     * WHERE 1=1 [dynamic conditions]
     */
    const [{ total }] = await db
      .select({ total: count() })
      .from(translationKeys)
      .leftJoin(translations, and(eq(translations.keyId, translationKeys.id), eq(translations.langCode, langCode)))
      .where(whereClause);

    /*
     * raw:
     * SELECT tk.id, tk.key, tk.category, tk.description, tk.default_value,
     *        COALESCE(t.value, '') AS value
     * FROM public.translation_keys tk
     * LEFT JOIN public.translations t ON t.key_id = tk.id AND t.lang_code = $1
     * WHERE 1=1 [conditions] ORDER BY tk.category ASC, tk.key ASC LIMIT $n OFFSET $n
     */
    const rows = await db
      .select({
        id: translationKeys.id,
        key: translationKeys.key,
        category: translationKeys.category,
        description: translationKeys.description,
        default_value: translationKeys.defaultValue,
        value: sql<string>`COALESCE(${translations.value}, '')`,
      })
      .from(translationKeys)
      .leftJoin(translations, and(eq(translations.keyId, translationKeys.id), eq(translations.langCode, langCode)))
      .where(whereClause)
      .orderBy(asc(translationKeys.category), asc(translationKeys.key))
      .limit(limit)
      .offset(offset);

    /*
     * raw: SELECT DISTINCT category FROM public.translation_keys ORDER BY category
     */
    const categoryRows = await db
      .selectDistinct({ category: translationKeys.category })
      .from(translationKeys)
      .orderBy(asc(translationKeys.category));

    return NextResponse.json({
      language: {
        ...language,
        native_name: language.nativeName,
        flag_emoji: language.flagEmoji,
        is_rtl: language.isRtl,
        is_active: language.isActive,
        is_default: language.isDefault,
        display_order: language.displayOrder,
      },
      keys: rows,
      categories: categoryRows.map(r => r.category),
      pagination: { page, limit, total: Number(total), pages: Math.max(1, Math.ceil(Number(total) / limit)) },
    });
  })
);

export const PUT = withErrorHandler(
  withAuth(async (req: AuthedRequest, context: { params: Promise<Record<string, string>> }) => {
    if (req.session.role !== 'superadmin' && req.session.role !== 'support') {
      throw new ForbiddenError();
    }
    const { langCode } = await context.params;
    let body: { translations: Record<string, string> };
    try {
      body = await req.json() as typeof body;
    } catch {
      throw new ValidationError('Request body must be valid JSON');
    }

    const { translations: translationMap } = body;
    if (!translationMap || typeof translationMap !== 'object') {
      return NextResponse.json({ error: 'translations must be an object' }, { status: 400 });
    }

    let updated = 0;
    await db.transaction(async (tx) => {
      /*
       * raw: SELECT code FROM public.languages WHERE code = $1
       */
      const langRows = await tx.select({ code: languages.code })
        .from(languages)
        .where(eq(languages.code, langCode));
      if (langRows.length === 0) throw new NotFoundError('Language not found');

      for (const [key, value] of Object.entries(translationMap)) {
        /*
         * raw:
         * INSERT INTO public.translations (lang_code, key_id, value, updated_at)
         * SELECT $1, tk.id, $2, now() FROM public.translation_keys tk WHERE tk.key = $3
         * ON CONFLICT (lang_code, key_id) DO UPDATE SET value = $2, updated_at = now()
         */
        const result = await tx.execute(sql`
          INSERT INTO public.translations (lang_code, key_id, value, updated_at)
          SELECT ${langCode}, tk.id, ${value ?? ''}, now()
          FROM public.translation_keys tk WHERE tk.key = ${key}
          ON CONFLICT (lang_code, key_id) DO UPDATE SET value = ${value ?? ''}, updated_at = now()
        `);
        updated += (result as { rowCount?: number }).rowCount ?? 0;
      }
    });

    return NextResponse.json({ ok: true, updated });
  })
);