const db = require('../../database/database');
const { mergeLanguageDescriptors } = require('../../utils/languageUtils');

class I18nPreferencesRepository {
  _safeJsonArray(value) {
    if (Array.isArray(value)) return value;
    if (value == null) return [];

    try {
      const parsed = typeof value === 'string' ? JSON.parse(value) : value;
      return Array.isArray(parsed) ? parsed : [];
    } catch (_) {
      return [];
    }
  }

  _normalizeRow(row) {
    const categories = this._safeJsonArray(row?.categories_json);
    const globalKeys = this._safeJsonArray(row?.global_keys_json);
    return {
      categories,
      globalKeys,
    };
  }

  _safeBoolean(value, fallback) {
    if (value === undefined || value === null) return fallback;
    if (typeof value === 'boolean') return value;
    const normalized = String(value).trim().toLowerCase();
    if (['1', 'true', 'yes', 'on'].includes(normalized)) return true;
    if (['0', 'false', 'no', 'off'].includes(normalized)) return false;
    return fallback;
  }

  async get() {
    const [rows] = await db.query('SELECT * FROM i18n_preferences WHERE id = 1 LIMIT 1');
    if (!rows.length) {
      return { categories: [], globalKeys: [] };
    }
    return this._normalizeRow(rows[0]);
  }

  async listLanguages({ enabledOnly = true } = {}) {
    try {
      const [rows] = await db.query(
        `SELECT language_code AS languageCode,
                label,
                is_enabled AS isEnabled,
                is_custom AS isCustom
         FROM i18n_languages
         ${enabledOnly ? 'WHERE is_enabled = 1' : ''}
         ORDER BY language_code`
      );

      const languages = mergeLanguageDescriptors(rows || []);
      return enabledOnly ? languages.filter((entry) => entry.isEnabled !== false) : languages;
    } catch (_) {
      return mergeLanguageDescriptors([]);
    }
  }

  async upsert({ categories, globalKeys, updatedByUserId } = {}) {
    const current = await this.get();

    const nextCategories = categories !== undefined ? categories : current.categories;
    const nextGlobalKeys = globalKeys !== undefined ? globalKeys : current.globalKeys;

    await db.query(
      `INSERT INTO i18n_preferences (id, categories_json, global_keys_json, updated_by_user_id)
       VALUES (1, ?, ?, ?)
       ON DUPLICATE KEY UPDATE
         categories_json = VALUES(categories_json),
         global_keys_json = VALUES(global_keys_json),
         updated_by_user_id = VALUES(updated_by_user_id)`,
      [JSON.stringify(nextCategories || []), JSON.stringify(nextGlobalKeys || []), updatedByUserId || null]
    );

    return this.get();
  }

  async clear(updatedByUserId) {
    await db.query(
      `INSERT INTO i18n_preferences (id, categories_json, global_keys_json, updated_by_user_id)
       VALUES (1, ?, ?, ?)
       ON DUPLICATE KEY UPDATE
         categories_json = VALUES(categories_json),
         global_keys_json = VALUES(global_keys_json),
         updated_by_user_id = VALUES(updated_by_user_id)`,
      [JSON.stringify([]), JSON.stringify([]), updatedByUserId || null]
    );

    return this.get();
  }

  async _tableExists(conn, tableName) {
    const [rows] = await conn.query(
      `SELECT 1
       FROM INFORMATION_SCHEMA.TABLES
       WHERE TABLE_SCHEMA = DATABASE()
         AND TABLE_NAME = ?
       LIMIT 1`,
      [tableName]
    );
    return rows.length > 0;
  }

  async _columnExists(conn, tableName, columnName) {
    const [rows] = await conn.query(
      `SELECT 1
       FROM INFORMATION_SCHEMA.COLUMNS
       WHERE TABLE_SCHEMA = DATABASE()
         AND TABLE_NAME = ?
         AND COLUMN_NAME = ?
       LIMIT 1`,
      [tableName, columnName]
    );
    return rows.length > 0;
  }

  async deleteLanguageEntries(languageCode, updatedByUserId) {
    const conn = await db.getConnection();
    const safeLanguageCode = String(languageCode || '').trim();

    const targets = [
      // Language metadata
      { table: 'i18n_languages', possibleColumns: ['language_code', 'lang', 'code'] },
      { table: 'i18n_language_metadata', possibleColumns: ['language_code', 'lang', 'code'] },

      // Translation/custom-value stores
      { table: 'i18n_translation_overrides', possibleColumns: ['language_code', 'lang'] },
      { table: 'i18n_translations', possibleColumns: ['language_code', 'lang'] },

      // Potential language-scoped preference/link tables
      { table: 'i18n_preferences_languages', possibleColumns: ['language_code', 'lang'] },
      { table: 'i18n_preference_categories', possibleColumns: ['language_code', 'lang'] },
      { table: 'i18n_preference_global_keys', possibleColumns: ['language_code', 'lang'] },
    ];

    let deletedRows = 0;
    const touchedTables = [];

    try {
      await conn.beginTransaction();

      for (const target of targets) {
        const exists = await this._tableExists(conn, target.table);
        if (!exists) continue;

        let deleteColumn = null;
        for (const col of target.possibleColumns) {
          if (await this._columnExists(conn, target.table, col)) {
            deleteColumn = col;
            break;
          }
        }
        if (!deleteColumn) continue;

        const [result] = await conn.query(
          `DELETE FROM \`${target.table}\` WHERE \`${deleteColumn}\` = ?`,
          [safeLanguageCode]
        );
        const affected = Number(result?.affectedRows || 0);
        if (affected > 0) {
          deletedRows += affected;
          touchedTables.push(target.table);
        }
      }

      if (await this._tableExists(conn, 'i18n_preferences')) {
        await conn.query(
          `UPDATE i18n_preferences
           SET updated_by_user_id = ?,
               updated_at = CURRENT_TIMESTAMP
           WHERE id = 1`,
          [updatedByUserId || null]
        );
      }

      await conn.commit();
      return { deletedRows, touchedTables };
    } catch (error) {
      await conn.rollback();
      throw error;
    } finally {
      conn.release();
    }
  }

  async upsertLanguage(conn, language, updatedByUserId) {
    if (!language || !language.languageCode) return null;

    const languageCode = String(language.languageCode).trim().toLowerCase();
    const label = String(language.label || languageCode).trim();
    const isEnabled = this._safeBoolean(language.isEnabled, true);
    const isCustom = this._safeBoolean(language.isCustom, true);

    await conn.query(
      `INSERT INTO i18n_languages (language_code, label, is_enabled, is_custom, created_by_user_id, updated_by_user_id)
       VALUES (?, ?, ?, ?, ?, ?)
       ON DUPLICATE KEY UPDATE
         label = VALUES(label),
         is_enabled = VALUES(is_enabled),
         is_custom = VALUES(is_custom),
         updated_by_user_id = VALUES(updated_by_user_id)`,
      [languageCode, label, isEnabled ? 1 : 0, isCustom ? 1 : 0, updatedByUserId || null, updatedByUserId || null]
    );

    const [rows] = await conn.query(
      `SELECT language_code AS languageCode,
              label,
              is_enabled AS isEnabled,
              is_custom AS isCustom,
              created_at AS createdAt,
              updated_at AS updatedAt
       FROM i18n_languages
       WHERE language_code = ?
       LIMIT 1`,
      [languageCode]
    );
    return rows[0] || null;
  }

  async upsertTranslationOverrides(conn, translationEntries, updatedByUserId) {
    if (!Array.isArray(translationEntries) || !translationEntries.length) {
      return { upsertedCount: 0 };
    }

    let upsertedCount = 0;
    for (const entry of translationEntries) {
      const languageCode = String(entry.languageCode || '').trim().toLowerCase();
      const namespace = String(entry.namespace || '').trim();
      const key = String(entry.key || '').trim();
      const value = entry.value == null ? '' : String(entry.value);
      const isCustom = this._safeBoolean(entry.isCustom, true);

      if (!languageCode || !namespace || !key) continue;

      await conn.query(
        `INSERT INTO i18n_translation_overrides
           (language_code, namespace, t_key, t_value, is_custom, created_by_user_id, updated_by_user_id)
         VALUES (?, ?, ?, ?, ?, ?, ?)
         ON DUPLICATE KEY UPDATE
           t_value = VALUES(t_value),
           is_custom = VALUES(is_custom),
           updated_by_user_id = VALUES(updated_by_user_id)`,
        [
          languageCode,
          namespace,
          key,
          value,
          isCustom ? 1 : 0,
          updatedByUserId || null,
          updatedByUserId || null,
        ]
      );
      upsertedCount += 1;
    }

    return { upsertedCount };
  }

  async upsertBundle({ categories, globalKeys, language, translations, updatedByUserId } = {}) {
    const conn = await db.getConnection();
    try {
      await conn.beginTransaction();

      const [prefRows] = await conn.query('SELECT * FROM i18n_preferences WHERE id = 1 LIMIT 1');
      const current = prefRows.length
        ? this._normalizeRow(prefRows[0])
        : { categories: [], globalKeys: [] };

      const nextCategories = categories !== undefined ? categories : current.categories;
      const nextGlobalKeys = globalKeys !== undefined ? globalKeys : current.globalKeys;

      await conn.query(
        `INSERT INTO i18n_preferences (id, categories_json, global_keys_json, updated_by_user_id)
         VALUES (1, ?, ?, ?)
         ON DUPLICATE KEY UPDATE
           categories_json = VALUES(categories_json),
           global_keys_json = VALUES(global_keys_json),
           updated_by_user_id = VALUES(updated_by_user_id)`,
        [JSON.stringify(nextCategories || []), JSON.stringify(nextGlobalKeys || []), updatedByUserId || null]
      );

      const languagesToEnsure = new Map();
      if (language && language.languageCode) {
        languagesToEnsure.set(String(language.languageCode).toLowerCase(), language);
      }
      for (const t of (Array.isArray(translations) ? translations : [])) {
        const code = String(t.languageCode || '').trim().toLowerCase();
        if (!code) continue;
        if (!languagesToEnsure.has(code)) {
          languagesToEnsure.set(code, {
            languageCode: code,
            label: code.toUpperCase(),
            isEnabled: true,
            isCustom: true,
          });
        }
      }

      let upsertedLanguage = null;
      for (const [, langPayload] of languagesToEnsure) {
        const row = await this.upsertLanguage(conn, langPayload, updatedByUserId);
        if (language && row && row.languageCode === String(language.languageCode).toLowerCase()) {
          upsertedLanguage = row;
        }
      }

      const translationResult = await this.upsertTranslationOverrides(conn, translations, updatedByUserId);

      const [savedRows] = await conn.query('SELECT * FROM i18n_preferences WHERE id = 1 LIMIT 1');
      const preferences = savedRows.length
        ? this._normalizeRow(savedRows[0])
        : { categories: [], globalKeys: [] };

      await conn.commit();
      return {
        preferences,
        language: upsertedLanguage,
        translationsUpserted: translationResult.upsertedCount,
      };
    } catch (error) {
      await conn.rollback();
      throw error;
    } finally {
      conn.release();
    }
  }

  async listTranslations({ languageCode, namespace } = {}) {
    const filters = [];
    const params = [];

    if (languageCode) {
      filters.push('language_code = ?');
      params.push(String(languageCode).trim().toLowerCase());
    }
    if (namespace) {
      filters.push('namespace = ?');
      params.push(String(namespace).trim());
    }

    const whereClause = filters.length ? `WHERE ${filters.join(' AND ')}` : '';
    const [rows] = await db.query(
      `SELECT language_code AS languageCode,
              namespace,
              t_key AS \`key\`,
              t_value AS value,
              is_custom AS isCustom,
              updated_at AS updatedAt
       FROM i18n_translation_overrides
       ${whereClause}
       ORDER BY language_code, namespace, t_key`,
      params
    );

    return rows || [];
  }

  async upsertTranslations({ translations, updatedByUserId } = {}) {
    const conn = await db.getConnection();
    try {
      await conn.beginTransaction();

      // Make sure every translation language exists in metadata table.
      const ensured = new Set();
      for (const t of (translations || [])) {
        const code = String(t.languageCode || '').trim().toLowerCase();
        if (!code || ensured.has(code)) continue;

        await this.upsertLanguage(
          conn,
          {
            languageCode: code,
            label: code.toUpperCase(),
            isEnabled: true,
            isCustom: true,
          },
          updatedByUserId
        );
        ensured.add(code);
      }

      const result = await this.upsertTranslationOverrides(conn, translations || [], updatedByUserId);
      await conn.commit();
      return result;
    } catch (error) {
      await conn.rollback();
      throw error;
    } finally {
      conn.release();
    }
  }

  async getScanSummary({ languageCode } = {}) {
    const code = languageCode ? String(languageCode).trim().toLowerCase() : null;

    const [languages] = await db.query(
      `SELECT language_code AS languageCode,
              label,
              is_enabled AS isEnabled,
              is_custom AS isCustom
       FROM i18n_languages
       ORDER BY language_code`
    );

    const [namespaces] = await db.query(
      `SELECT DISTINCT namespace
       FROM i18n_translation_overrides
       ${code ? 'WHERE language_code = ?' : ''}
       ORDER BY namespace`,
      code ? [code] : []
    );

    const [countsByLanguage] = await db.query(
      `SELECT language_code AS languageCode, COUNT(*) AS entryCount
       FROM i18n_translation_overrides
       ${code ? 'WHERE language_code = ?' : ''}
       GROUP BY language_code
       ORDER BY language_code`,
      code ? [code] : []
    );

    const prefs = await this.get();
    const categoryNamespaces = Array.isArray(prefs.categories)
      ? [...new Set(prefs.categories.flatMap((c) => (Array.isArray(c?.namespaces) ? c.namespaces : [])))]
      : [];

    return {
      languages: languages || [],
      namespaces: (namespaces || []).map((r) => r.namespace),
      countsByLanguage: countsByLanguage || [],
      categories: prefs.categories || [],
      globalKeys: prefs.globalKeys || [],
      categoryNamespaces,
    };
  }
}

module.exports = I18nPreferencesRepository;