# #region SQLInsertService [C:4] [TYPE Class] [SEMANTICS sql, insert, superset, translate]
# @BRIEF Generate INSERT SQL from translation records and submit to Superset SQL Lab.
# @PRE Job has target table configured. Run has successful records.
# @POST SQL is generated and submitted to Superset; returns execution result.
# @SIDE_EFFECT Superset API call; event log entries.
# @RELATION DEPENDS_ON -> [SQLGenerator]
# @RELATION DEPENDS_ON -> [SupersetSqlLabExecutor]
# @RELATION DEPENDS_ON -> [TranslationEventLog]
# @RELATION DEPENDS_ON -> [orchestrator_sql_rows]
# @RATIONALE Row/column building extracted to orchestrator_sql_rows module for INV_7 compliance.

from typing import Any

from sqlalchemy.orm import Session, joinedload

from ...core.config_manager import ConfigManager
from ...core.logger import belief_scope, logger
from ...models.translate import TranslationJob, TranslationRecord, TranslationRun
from .events import TranslationEventLog
from .orchestrator_sql_rows import build_columns, build_context_keys, build_rows
from .sql_generator import SQLGenerator
from .superset_executor import SupersetSqlLabExecutor


class SQLInsertService:
    """Generate INSERT SQL from successful records and submit to Superset."""

    def __init__(self, db: Session, config_manager: ConfigManager, event_log: TranslationEventLog):
        self.db = db
        self.config_manager = config_manager
        self.event_log = event_log

    # region generate_and_insert_sql [TYPE Function]
    # @PURPOSE: Generate INSERT SQL and submit to Superset SQL Lab.
    def generate_and_insert_sql(
        self,
        job: TranslationJob,
        run: TranslationRun,
    ) -> dict[str, Any]:
        with belief_scope("SQLInsertService.generate_and_insert_sql"):
            records = (
                self.db.query(TranslationRecord)
                .options(joinedload(TranslationRecord.languages))
                .filter(
                    TranslationRecord.run_id == run.id,
                    TranslationRecord.status == "SUCCESS",
                    TranslationRecord.target_sql.isnot(None),
                )
                .all()
            )

            if not records:
                logger.reason("No successful records to insert", {"run_id": run.id})
                return {"status": "skipped", "reason": "no_records", "query_id": None}

            effective_target = job.target_column or job.translation_column
            primary_language = (job.target_languages or ["en"])[0]

            columns = build_columns(job, effective_target)
            context_keys = build_context_keys(job, effective_target)
            rows_for_sql = build_rows(records, job, effective_target, primary_language, context_keys)

            if not columns:
                columns = [effective_target or "translated_text"]
                rows_for_sql = [{columns[0]: rec.target_sql or ""} for rec in records]

            dialect = self._resolve_dialect(job)
            try:
                sql, row_count = SQLGenerator.generate(
                    dialect=dialect,
                    target_schema=job.target_schema,
                    target_table=job.target_table or "translated_data",
                    columns=columns,
                    rows=rows_for_sql,
                    key_columns=job.target_key_cols,
                    upsert_strategy=job.upsert_strategy or "MERGE",
                )
            except ValueError as e:
                logger.explore("SQL generation failed", {"error": str(e)})
                return {"status": "failed", "error_message": str(e), "query_id": None}

            self.event_log.log_event(
                job_id=job.id, run_id=run.id,
                event_type="INSERT_PHASE_STARTED",
                payload={"sql_length": len(sql), "row_count": row_count, "dialect": dialect},
            )

            try:
                env_id = job.environment_id or job.source_dialect or ""
                executor = SupersetSqlLabExecutor(self.config_manager, env_id)
                executor.resolve_database_id(target_database_id=job.target_database_id)
                result = executor.execute_and_poll(sql=sql, max_polls=30, poll_interval_seconds=2.0)
            except Exception as e:
                logger.explore("Superset SQL submission failed", {"run_id": run.id, "error": str(e)})
                result = {"status": "failed", "error_message": str(e), "query_id": None}

            self.event_log.log_event(
                job_id=job.id, run_id=run.id,
                event_type="INSERT_PHASE_COMPLETED",
                payload=result,
            )
            return result
    # endregion generate_and_insert_sql

    # region _resolve_dialect [TYPE Function]
    def _resolve_dialect(self, job: TranslationJob) -> str:
        try:
            env_id = job.environment_id or job.source_dialect or ""
            executor = SupersetSqlLabExecutor(self.config_manager, env_id)
            executor.resolve_database_id(target_database_id=job.target_database_id)
            real_backend = executor.get_database_backend()
        except Exception as e:
            logger.explore("Failed to resolve database backend, falling back to job dialect", {"error": str(e)})
            real_backend = None
        return real_backend or job.database_dialect or job.target_dialect or "postgresql"
    # endregion _resolve_dialect

# #endregion SQLInsertService
