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

export const GET = withErrorHandler(
  withAuth(async (req: AuthedRequest) => {
    if (req.session.role !== 'superadmin' && req.session.role !== 'support') {
      throw new ForbiddenError();
    }

    /*
     * raw:
     * SELECT id, code, name, native_name, flag_emoji, is_rtl, is_active, is_default, display_order,
     *   (SELECT COUNT(*) FROM public.translations t WHERE t.lang_code = l.code AND t.value <> '') AS translation_count
     * FROM public.languages l ORDER BY display_order ASC, name ASC
     */
    const [langRows, [totalKeysResult]] = await Promise.all([
      db.select({
        id: languages.id,
        code: languages.code,
        name: languages.name,
        native_name: languages.nativeName,
        flag_emoji: languages.flagEmoji,
        is_rtl: languages.isRtl,
        is_active: languages.isActive,
        is_default: languages.isDefault,
        display_order: languages.displayOrder,
        translation_count: sql<number>`(SELECT COUNT(*) FROM public.translations t WHERE t.lang_code = ${languages.code} AND t.value <> '')`,
      }).from(languages).orderBy(asc(languages.displayOrder), asc(languages.name)),
      db.select({ total: count() }).from(translationKeys),
    ]);

    const totalKeys = Number(totalKeysResult.total);
    return NextResponse.json({ languages: langRows, totalKeys });
  })
);

export const POST = withErrorHandler(
  withAuth(async (req: AuthedRequest) => {
    if (req.session.role !== 'superadmin' && req.session.role !== 'support') {
      throw new ForbiddenError();
    }
    let body: {
      code: string;
      name: string;
      native_name: string;
      flag_emoji?: string;
      is_rtl?: boolean;
      copy_from?: string;
    };
    try {
      body = await req.json() as typeof body;
    } catch {
      throw new ValidationError('Request body must be valid JSON');
    }
    const { code, name, native_name, flag_emoji = '', is_rtl = false, copy_from = 'en' } = body;

    if (!code?.trim()) throw new ValidationError('Language code is required');
    if (!name?.trim()) throw new ValidationError('Language name is required');
    if (!native_name?.trim()) throw new ValidationError('Native name is required');
    if (!/^[a-z]{2,8}(-[a-z0-9]{2,8})?$/.test(code.trim())) {
      throw new ValidationError('Language code must be 2-8 lowercase letters/digits, optionally with a hyphen subtag (e.g. "en", "fr", "zh-hans")');
    }

    const newLang = await db.transaction(async (tx) => {
      /*
       * raw: SELECT 1 FROM public.languages WHERE code = $1
       */
      const existing = await tx.select({ code: languages.code })
        .from(languages)
        .where(eq(languages.code, code.trim()))
        .limit(1);
      if (existing.length > 0) {
        throw new ValidationError(`Language code "${code}" already exists`);
      }

      /*
       * raw:
       * INSERT INTO public.languages (code, name, native_name, flag_emoji, is_rtl, is_active, is_default, display_order)
       * VALUES ($1, $2, $3, $4, $5, true, false,
       *   (SELECT COALESCE(MAX(display_order), -1) + 1 FROM public.languages))
       * RETURNING *
       */
      const [inserted] = await tx.insert(languages).values({
        code: code.trim(),
        name: name.trim(),
        nativeName: native_name.trim(),
        flagEmoji: flag_emoji.trim(),
        isRtl: is_rtl,
        isActive: true,
        isDefault: false,
        displayOrder: sql<number>`(SELECT COALESCE(MAX(display_order), -1) + 1 FROM public.languages)`,
      }).returning();

      /*
       * raw:
       * INSERT INTO public.translations (lang_code, key_id, value)
       * SELECT $1, tk.id, COALESCE(NULLIF(t_src.value, ''), NULLIF(t_en.value, ''), '')
       * FROM public.translation_keys tk
       * LEFT JOIN public.translations t_src ON t_src.key_id = tk.id AND t_src.lang_code = $2
       * LEFT JOIN public.translations t_en ON t_en.key_id = tk.id AND t_en.lang_code = 'en'
       * ON CONFLICT (lang_code, key_id) DO NOTHING
       */
      await tx.execute(sql`
        INSERT INTO public.translations (lang_code, key_id, value)
        SELECT ${inserted.code}, tk.id,
          COALESCE(
            NULLIF(t_src.value, ''),
            NULLIF(t_en.value, ''),
            ''
          )
        FROM public.translation_keys tk
        LEFT JOIN public.translations t_src
          ON t_src.key_id = tk.id AND t_src.lang_code = ${copy_from || 'en'}
        LEFT JOIN public.translations t_en
          ON t_en.key_id = tk.id AND t_en.lang_code = 'en'
        ON CONFLICT (lang_code, key_id) DO NOTHING
      `);

      return inserted;
    });

    return NextResponse.json({
      language: {
        ...newLang,
        native_name: newLang.nativeName,
        flag_emoji: newLang.flagEmoji,
        is_rtl: newLang.isRtl,
        is_active: newLang.isActive,
        is_default: newLang.isDefault,
        display_order: newLang.displayOrder,
        created_at: newLang.createdAt,
        updated_at: newLang.updatedAt,
      },
    }, { status: 201 });
  })
);