# #region orchestrator_sql_rows [C:3] [TYPE Module] [SEMANTICS sql, insert, row, column, builders]
# @BRIEF Column and row building utilities for SQL insertion in translate plugin.
# @RELATION DEPENDS_ON -> [TranslationRecord]
# @RELATION DEPENDS_ON -> [TranslationJob]

import json

from ...models.translate import TranslationJob, TranslationRecord
from .sql_generator import _normalize_timestamp_value


# #region build_columns [C:2] [TYPE Function] [SEMANTICS sql, columns, build]
# @BRIEF Build list of target columns for SQL INSERT from job configuration.
def build_columns(job: TranslationJob, effective_target: str | None) -> list[str]:
    """Build column list for SQL INSERT from job config."""
    columns: list[str] = []
    if job.target_key_cols:
        columns.extend(job.target_key_cols)
    if effective_target:
        columns.append(effective_target)
    if job.target_language_column:
        columns.append(job.target_language_column)
    if job.target_source_column:
        columns.append(job.target_source_column)
    if job.target_source_language_column:
        columns.append(job.target_source_language_column)
    columns.append("context")
    columns.append("is_original")
    seen: set[str] = set()
    deduped: list[str] = []
    for c in columns:
        if c and c not in seen:
            deduped.append(c)
            seen.add(c)
    return deduped
# #endregion build_columns


# #region build_context_keys [C:1] [TYPE Function] [SEMANTICS context, keys]
# @BRIEF Build list of context keys for SQL row data.
def build_context_keys(job: TranslationJob, effective_target: str | None) -> list[str]:
    """Build context key list for SQL row data."""
    context_keys = list(job.context_columns or [])
    if job.translation_column and job.translation_column != effective_target and job.translation_column not in context_keys:
        context_keys.append(job.translation_column)
    return context_keys
# #endregion build_context_keys


# #region build_rows [C:2] [TYPE Function] [SEMANTICS sql, rows, build]
# @BRIEF Build row data for SQL INSERT with per-language expansion.
# @SIDE_EFFECT  Reads translation record language entries.
def build_rows(
    records: list[TranslationRecord],
    job: TranslationJob,
    effective_target: str | None,
    primary_language: str,
    context_keys: list[str],
) -> list[dict[str, object]]:
    """Build row data for SQL INSERT with per-language expansion."""
    rows_for_sql: list[dict[str, object]] = []
    for rec in records:
        source_data = rec.source_data or {}
        detected_src_lang = "und"
        if rec.languages and len(rec.languages) > 0:
            detected_src_lang = rec.languages[0].source_language_detected or "und"

        context_data = {key: str(source_data.get(key, "")) for key in context_keys}
        base_row: dict[str, object] = {}
        if job.target_key_cols:
            for k in job.target_key_cols:
                raw = source_data.get(k)
                if raw is not None:
                    normalized = _normalize_timestamp_value(raw)
                    base_row[k] = normalized if normalized else raw
                else:
                    base_row[k] = None
        if job.target_source_column:
            base_row[job.target_source_column] = rec.source_sql or ""
        if job.target_source_language_column:
            base_row[job.target_source_language_column] = detected_src_lang
        base_row["context"] = json.dumps(context_data, ensure_ascii=False)

        original_row = dict(base_row)
        if effective_target:
            original_row[effective_target] = rec.source_sql or ""
        if job.target_language_column:
            original_row[job.target_language_column] = detected_src_lang
        original_row["is_original"] = 1
        rows_for_sql.append(original_row)

        if rec.languages and len(rec.languages) > 0:
            for lang in rec.languages:
                if lang.language_code == detected_src_lang:
                    continue
                trans_row = dict(base_row)
                trans_value = lang.final_value or lang.translated_value or ""
                if effective_target:
                    trans_row[effective_target] = trans_value
                if job.target_language_column:
                    trans_row[job.target_language_column] = lang.language_code
                trans_row["is_original"] = 0
                rows_for_sql.append(trans_row)
        else:
            fallback_row = dict(base_row)
            if effective_target:
                fallback_row[effective_target] = rec.target_sql or ""
            if job.target_language_column:
                fallback_row[job.target_language_column] = primary_language
            fallback_row["is_original"] = 0
            rows_for_sql.append(fallback_row)

    return rows_for_sql
# #endregion build_rows
# #endregion orchestrator_sql_rows
