import { NextRequest, NextResponse } from 'next/server';
import { db, languages, translations, translationKeys } from '@/server/db/drizzle';
import { eq, and, sql } from 'drizzle-orm';

import { childLogger } from '@server/logger';
import { wrapRouteHandler } from '@server/logger/request';
const log = childLogger('route.public.translations');

async function handleGET(req: NextRequest) {
  const lang = req.nextUrl.searchParams.get('lang') || 'en';

  try {
    /*
     * raw: SELECT code FROM public.languages WHERE code = $1 AND is_active = true
     */
    const langRows = await db.select({ code: languages.code })
      .from(languages)
      .where(and(eq(languages.code, lang), eq(languages.isActive, true)));
    const resolvedLang = langRows.length > 0 ? lang : 'en';

    /*
     * raw:
     * SELECT tk.key,
     *   COALESCE(NULLIF(t_req.value, ''), t_en.value, tk.default_value) AS value
     * FROM public.translation_keys tk
     * LEFT JOIN public.translations t_req ON t_req.key_id = tk.id AND t_req.lang_code = $1
     * LEFT JOIN public.translations t_en ON t_en.key_id = tk.id AND t_en.lang_code = 'en'
     * ORDER BY tk.key
     */
    const t_req = db.$with('t_req').as(
      db.select({ keyId: translations.keyId, value: translations.value })
        .from(translations)
        .where(eq(translations.langCode, resolvedLang))
    );
    const t_en = db.$with('t_en').as(
      db.select({ keyId: translations.keyId, value: translations.value })
        .from(translations)
        .where(eq(translations.langCode, 'en'))
    );

    const rows = await db.with(t_req, t_en)
      .select({
        key: translationKeys.key,
        value: sql<string>`COALESCE(NULLIF(${t_req.value}, ''), ${t_en.value}, ${translationKeys.defaultValue})`,
      })
      .from(translationKeys)
      .leftJoin(t_req, eq(t_req.keyId, translationKeys.id))
      .leftJoin(t_en, eq(t_en.keyId, translationKeys.id))
      .orderBy(translationKeys.key);

    const result: Record<string, string> = {};
    for (const row of rows) {
      result[row.key] = row.value || '';
    }

    return NextResponse.json(result, {
      headers: {
        'Cache-Control': 'no-store',
        'X-Lang': resolvedLang,
      },
    });
  } catch (err) {
    log.error({ err }, 'translations API error');
    return NextResponse.json({ error: 'Internal server error' }, { status: 500 });
  }
}

export const GET = wrapRouteHandler((req: Request) => handleGET(req as NextRequest));