# #region OrchestratorIntegrationTests [C:4] [TYPE Module] [SEMANTICS test, integration, orchestrator, postgres, testcontainers]
# @BRIEF Integration tests for TranslationOrchestrator with real PostgreSQL.
# @RELATION BINDS_TO -> [TranslationOrchestrator]
# @RELATION BINDS_TO -> [TranslationJob]
# @RELATION BINDS_TO -> [TranslationRun]
# @RELATION BINDS_TO -> [TranslationBatch]
# @RELATION BINDS_TO -> [TranslationRecord]
# @RELATION BINDS_TO -> [TranslationEvent]
# @TEST_CONTRACT TranslationOrchestrator ->
# {
#   invariants: [
#     "start_run creates PENDING run with event",
#     "execute_run transitions run through RUNNING -> COMPLETED/FAILED",
#     "cancel_run sets status to CANCELLED",
#     "retry_failed_batches re-executes only failed batches",
#     "only one terminal event allowed per run",
#     "RUN_STARTED must precede other run events"
#   ]
# }
# @TEST_EDGE missing_preview -> ValueError when no accepted preview and no datasource
# @TEST_EDGE draft_job_cannot_run -> ValueError on DRAFT job
# @TEST_EDGE invalid_run_status -> ValueError on non-PENDING run
# @TEST_EDGE executor_failure -> run marked FAILED with error
# @TEST_EDGE cancel_completed_run -> ValueError
# @TEST_INVARIANT terminal_event_uniqueness -> VERIFIED_BY: [test_only_one_terminal_event_per_run]
# @TEST_INVARIANT event_ordering -> VERIFIED_BY: [test_run_started_must_precede_other_events]

import sys
from datetime import UTC, datetime
from pathlib import Path

sys.path.insert(0, str(Path(__file__).parent.parent.parent / "src"))

import pytest
from sqlalchemy.orm import Session
from unittest.mock import MagicMock, patch

from src.models.translate import (
    TranslationBatch,
    TranslationEvent,
    TranslationJob,
    TranslationPreviewSession,
    TranslationRecord,
    TranslationRun,
)
from src.plugins.translate.events import TranslationEventLog
from src.plugins.translate.orchestrator import TranslationOrchestrator


# #region TestTranslationEventLogIntegration [C:3] [TYPE Class]
# @BRIEF Integration tests for TranslationEventLog with real PostgreSQL.
class TestTranslationEventLogIntegration:
    """Verify event log operations with real PostgreSQL."""

    # region test_log_event_creates_record [C:2] [TYPE Function]
    # @BRIEF Verify event creation persists correctly.
    def test_log_event_creates_record(self, db_session: Session):
        # Create job and run first (FK constraints)
        job = TranslationJob(
            id="job-1",
            name="Test Job",
            source_dialect="postgresql",
            target_dialect="clickhouse",
            status="ACTIVE",
            source_datasource_id="42",
            translation_column="name",
            created_by="test_user",
        )
        db_session.add(job)
        db_session.flush()

        run = TranslationRun(
            id="run-1",
            job_id="job-1",
            status="RUNNING",
            started_at=datetime.now(UTC),
            created_by="test_user",
        )
        db_session.add(run)
        db_session.commit()

        event_log = TranslationEventLog(db_session)

        event = event_log.log_event(
            job_id="job-1",
            event_type="RUN_STARTED",
            payload={"key": "value"},
            run_id="run-1",
        )

        assert event is not None
        assert event.id is not None
        assert event.job_id == "job-1"
        assert event.run_id == "run-1"
        assert event.event_type == "RUN_STARTED"
        assert event.event_data == {"key": "value"}
        assert event.created_at is not None

        # Verify persisted
        fetched = (
            db_session.query(TranslationEvent)
            .filter(TranslationEvent.id == event.id)
            .first()
        )
        assert fetched is not None
        assert fetched.event_type == "RUN_STARTED"
    # endregion test_log_event_creates_record

    # region test_invalid_event_type_raises [C:2] [TYPE Function]
    # @BRIEF Verify invalid event type raises ValueError.
    def test_invalid_event_type_raises(self, db_session: Session):
        event_log = TranslationEventLog(db_session)

        with pytest.raises(ValueError, match="Invalid event_type"):
            event_log.log_event(
                job_id="job-1",
                event_type="UNKNOWN_EVENT_TYPE",
            )
    # endregion test_invalid_event_type_raises

    # region test_only_one_terminal_event_per_run [C:2] [TYPE Function]
    # @BRIEF Verify only one terminal event allowed per run.
    def test_only_one_terminal_event_per_run(self, db_session: Session):
        # Create job and run first (FK constraints)
        job = TranslationJob(
            id="job-1",
            name="Test Job",
            source_dialect="postgresql",
            target_dialect="clickhouse",
            status="ACTIVE",
            source_datasource_id="42",
            translation_column="name",
            created_by="test_user",
        )
        db_session.add(job)
        db_session.flush()

        run = TranslationRun(
            id="run-1",
            job_id="job-1",
            status="RUNNING",
            started_at=datetime.now(UTC),
            created_by="test_user",
        )
        db_session.add(run)
        db_session.commit()

        event_log = TranslationEventLog(db_session)

        # Create first terminal event
        event_log.log_event(
            job_id="job-1",
            run_id="run-1",
            event_type="RUN_COMPLETED",
        )

        # Second terminal event should fail
        with pytest.raises(ValueError, match="already has a terminal event"):
            event_log.log_event(
                job_id="job-1",
                run_id="run-1",
                event_type="RUN_FAILED",
            )
    # endregion test_only_one_terminal_event_per_run

    # region test_run_started_must_precede_other_events [C:2] [TYPE Function]
    # @BRIEF Verify RUN_STARTED must exist before other run events.
    def test_run_started_must_precede_other_events(self, db_session: Session):
        event_log = TranslationEventLog(db_session)

        # Try to log BATCH_STARTED without RUN_STARTED
        with pytest.raises(ValueError, match="RUN_STARTED event must precede"):
            event_log.log_event(
                job_id="job-1",
                run_id="run-1",
                event_type="BATCH_STARTED",
            )
    # endregion test_run_started_must_precede_other_events

    # region test_valid_event_sequence [C:2] [TYPE Function]
    # @BRIEF Verify valid event sequence succeeds.
    def test_valid_event_sequence(self, db_session: Session):
        # Create job and run first (FK constraints)
        job = TranslationJob(
            id="job-1",
            name="Test Job",
            source_dialect="postgresql",
            target_dialect="clickhouse",
            status="ACTIVE",
            source_datasource_id="42",
            translation_column="name",
            created_by="test_user",
        )
        db_session.add(job)
        db_session.flush()

        run = TranslationRun(
            id="run-1",
            job_id="job-1",
            status="RUNNING",
            started_at=datetime.now(UTC),
            created_by="test_user",
        )
        db_session.add(run)
        db_session.commit()

        event_log = TranslationEventLog(db_session)

        # Valid sequence: RUN_STARTED -> BATCH_STARTED -> BATCH_COMPLETED -> RUN_COMPLETED
        event_log.log_event(
            job_id="job-1",
            run_id="run-1",
            event_type="RUN_STARTED",
        )
        event_log.log_event(
            job_id="job-1",
            run_id="run-1",
            event_type="BATCH_STARTED",
            payload={"batch_id": "batch-1"},
        )
        event_log.log_event(
            job_id="job-1",
            run_id="run-1",
            event_type="BATCH_COMPLETED",
            payload={"batch_id": "batch-1"},
        )
        event_log.log_event(
            job_id="job-1",
            run_id="run-1",
            event_type="RUN_COMPLETED",
        )

        # Verify all events persisted
        events = (
            db_session.query(TranslationEvent)
            .filter(TranslationEvent.run_id == "run-1")
            .order_by(TranslationEvent.created_at)
            .all()
        )
        assert len(events) == 4
        assert events[0].event_type == "RUN_STARTED"
        assert events[1].event_type == "BATCH_STARTED"
        assert events[2].event_type == "BATCH_COMPLETED"
        assert events[3].event_type == "RUN_COMPLETED"
    # endregion test_valid_event_sequence

# #endregion TestTranslationEventLogIntegration


# #region TestTranslationOrchestratorIntegration [C:3] [TYPE Class]
# @BRIEF Integration tests for TranslationOrchestrator with real PostgreSQL.
class TestTranslationOrchestratorIntegration:
    """Verify orchestrator operations with real PostgreSQL."""

    # region test_start_run_creates_pending_run [C:2] [TYPE Function]
    # @BRIEF Verify start_run creates PENDING run with event.
    def test_start_run_creates_pending_run(self, db_session: Session):
        # Create job with datasource, target table, and LLM provider
        job = TranslationJob(
            id="job-123",
            name="Test Job",
            source_dialect="postgresql",
            target_dialect="clickhouse",
            status="ACTIVE",
            source_datasource_id="42",
            translation_column="name",
            target_table="translated_data",
            target_schema="public",
            provider_id="openai",
            created_by="test_user",
        )
        db_session.add(job)
        db_session.commit()

        config_manager = MagicMock()
        orch = TranslationOrchestrator(db_session, config_manager, "test_user")

        run = orch.start_run(job_id="job-123")

        assert run is not None
        assert run.id is not None
        assert run.job_id == "job-123"
        assert run.status == "PENDING"
        assert run.created_by == "test_user"
        assert run.created_at is not None
        # Note: started_at is set when execution begins, not at plan time

        # Verify event created
        events = (
            db_session.query(TranslationEvent)
            .filter(TranslationEvent.run_id == run.id)
            .all()
        )
        assert len(events) >= 1
        assert any(e.event_type == "RUN_STARTED" for e in events)
    # endregion test_start_run_creates_pending_run

    # region test_start_run_draft_job_raises [C:2] [TYPE Function]
    # @BRIEF Verify DRAFT job cannot be run.
    def test_start_run_draft_job_raises(self, db_session: Session):
        job = TranslationJob(
            id="job-draft",
            name="Draft Job",
            source_dialect="postgresql",
            target_dialect="clickhouse",
            status="DRAFT",
            created_by="test_user",
        )
        db_session.add(job)
        db_session.commit()

        config_manager = MagicMock()
        orch = TranslationOrchestrator(db_session, config_manager, "test_user")

        with pytest.raises(ValueError, match="DRAFT"):
            orch.start_run(job_id="job-draft")
    # endregion test_start_run_draft_job_raises

    # region test_start_run_missing_preview_raises [C:2] [TYPE Function]
    # @BRIEF Verify run without preview and without datasource raises.
    def test_start_run_missing_preview_raises(self, db_session: Session):
        # Job without datasource and no preview, but with target table and provider
        job = TranslationJob(
            id="job-no-preview",
            name="No Preview Job",
            source_dialect="postgresql",
            target_dialect="clickhouse",
            status="ACTIVE",
            source_datasource_id=None,  # No datasource
            environment_id=None,
            translation_column="name",
            target_table="translated_data",
            target_schema="public",
            provider_id="openai",
            created_by="test_user",
        )
        db_session.add(job)
        db_session.commit()

        config_manager = MagicMock()
        orch = TranslationOrchestrator(db_session, config_manager, "test_user")

        with pytest.raises(ValueError, match="no accepted preview"):
            orch.start_run(job_id="job-no-preview")
    # endregion test_start_run_missing_preview_raises

    # region test_start_run_with_accepted_preview_succeeds [C:2] [TYPE Function]
    # @BRIEF Verify run with accepted preview session succeeds.
    def test_start_run_with_accepted_preview_succeeds(self, db_session: Session):
        # Create job without datasource but with target table and provider
        job = TranslationJob(
            id="job-with-preview",
            name="Preview Job",
            source_dialect="postgresql",
            target_dialect="clickhouse",
            status="ACTIVE",
            source_datasource_id=None,
            translation_column="name",
            target_table="translated_data",
            target_schema="public",
            provider_id="openai",
            created_by="test_user",
        )
        db_session.add(job)
        db_session.flush()

        # Create accepted preview session
        preview = TranslationPreviewSession(
            id="preview-1",
            job_id="job-with-preview",
            status="APPLIED",
            created_at=datetime.now(UTC),
        )
        db_session.add(preview)
        db_session.commit()

        config_manager = MagicMock()
        orch = TranslationOrchestrator(db_session, config_manager, "test_user")

        run = orch.start_run(job_id="job-with-preview")

        assert run.status == "PENDING"
        assert run.job_id == "job-with-preview"
    # endregion test_start_run_with_accepted_preview_succeeds

    # region test_cancel_run_sets_cancelled_status [C:2] [TYPE Function]
    # @BRIEF Verify cancel_run sets status to CANCELLED.
    def test_cancel_run_sets_cancelled_status(self, db_session: Session):
        # Create job and run
        job = TranslationJob(
            id="job-cancel",
            name="Cancel Job",
            source_dialect="postgresql",
            target_dialect="clickhouse",
            status="ACTIVE",
            source_datasource_id="42",
            translation_column="name",
            created_by="test_user",
        )
        db_session.add(job)
        db_session.flush()

        run = TranslationRun(
            id="run-cancel",
            job_id="job-cancel",
            status="RUNNING",
            started_at=datetime.now(UTC),
            created_by="test_user",
        )
        db_session.add(run)
        db_session.commit()

        config_manager = MagicMock()
        orch = TranslationOrchestrator(db_session, config_manager, "test_user")

        result = orch.cancel_run("run-cancel")

        assert result.status == "CANCELLED"
        assert result.completed_at is not None

        # Verify event created
        events = (
            db_session.query(TranslationEvent)
            .filter(TranslationEvent.run_id == "run-cancel")
            .all()
        )
        assert any(e.event_type == "RUN_CANCELLED" for e in events)
    # endregion test_cancel_run_sets_cancelled_status

    # region test_cancel_completed_run_raises [C:2] [TYPE Function]
    # @BRIEF Verify cannot cancel a completed run.
    def test_cancel_completed_run_raises(self, db_session: Session):
        job = TranslationJob(
            id="job-completed",
            name="Completed Job",
            source_dialect="postgresql",
            target_dialect="clickhouse",
            status="ACTIVE",
            source_datasource_id="42",
            translation_column="name",
            created_by="test_user",
        )
        db_session.add(job)
        db_session.flush()

        run = TranslationRun(
            id="run-completed",
            job_id="job-completed",
            status="COMPLETED",
            started_at=datetime.now(UTC),
            completed_at=datetime.now(UTC),
            created_by="test_user",
        )
        db_session.add(run)
        db_session.commit()

        config_manager = MagicMock()
        orch = TranslationOrchestrator(db_session, config_manager, "test_user")

        with pytest.raises(ValueError, match="cancelled"):
            orch.cancel_run("run-completed")
    # endregion test_cancel_completed_run_raises

    # region test_get_run_status_returns_structured_data [C:2] [TYPE Function]
    # @BRIEF Verify get_run_status returns structured status dict.
    def test_get_run_status_returns_structured_data(self, db_session: Session):
        job = TranslationJob(
            id="job-status",
            name="Status Job",
            source_dialect="postgresql",
            target_dialect="clickhouse",
            status="ACTIVE",
            source_datasource_id="42",
            translation_column="name",
            created_by="test_user",
        )
        db_session.add(job)
        db_session.flush()

        run = TranslationRun(
            id="run-status",
            job_id="job-status",
            status="COMPLETED",
            started_at=datetime.now(UTC),
            completed_at=datetime.now(UTC),
            total_records=100,
            successful_records=95,
            failed_records=3,
            skipped_records=2,
            insert_status="success",
            superset_execution_id="query-1",
            created_by="test_user",
        )
        db_session.add(run)
        db_session.commit()

        config_manager = MagicMock()
        orch = TranslationOrchestrator(db_session, config_manager, "test_user")

        status = orch.get_run_status("run-status")

        assert status["id"] == "run-status"
        assert status["job_id"] == "job-status"
        assert status["status"] == "COMPLETED"
        assert status["total_records"] == 100
        assert status["successful_records"] == 95
        assert status["failed_records"] == 3
        assert status["skipped_records"] == 2
        assert status["insert_status"] == "success"
        assert status["superset_execution_id"] == "query-1"
    # endregion test_get_run_status_returns_structured_data

    # region test_get_run_history_returns_paginated_runs [C:2] [TYPE Function]
    # @BRIEF Verify get_run_history returns paginated runs.
    def test_get_run_history_returns_paginated_runs(self, db_session: Session):
        job = TranslationJob(
            id="job-history",
            name="History Job",
            source_dialect="postgresql",
            target_dialect="clickhouse",
            status="ACTIVE",
            source_datasource_id="42",
            translation_column="name",
            created_by="test_user",
        )
        db_session.add(job)
        db_session.flush()

        # Create multiple runs
        for i in range(5):
            run = TranslationRun(
                id=f"run-history-{i}",
                job_id="job-history",
                status="COMPLETED",
                started_at=datetime.now(UTC),
                completed_at=datetime.now(UTC),
                total_records=10 * (i + 1),
                successful_records=10 * (i + 1),
                created_by="test_user",
            )
            db_session.add(run)
        db_session.commit()

        config_manager = MagicMock()
        orch = TranslationOrchestrator(db_session, config_manager, "test_user")

        total, runs = orch.get_run_history("job-history", page=1, page_size=2)

        assert total == 5
        assert len(runs) == 2

        total, runs = orch.get_run_history("job-history", page=3, page_size=2)
        assert total == 5
        assert len(runs) == 1
    # endregion test_get_run_history_returns_paginated_runs

# #endregion TestTranslationOrchestratorIntegration


# #region TestTranslationBatchRecordIntegration [C:3] [TYPE Class]
# @BRIEF Integration tests for batch and record operations with real PostgreSQL.
class TestTranslationBatchRecordIntegration:
    """Verify batch and record operations with real PostgreSQL."""

    # region test_create_batch_with_records [C:2] [TYPE Function]
    # @BRIEF Verify batch creation with records persists correctly.
    def test_create_batch_with_records(self, db_session: Session):
        # Create job and run
        job = TranslationJob(
            id="job-batch",
            name="Batch Job",
            source_dialect="postgresql",
            target_dialect="clickhouse",
            status="ACTIVE",
            source_datasource_id="42",
            translation_column="name",
            created_by="test_user",
        )
        db_session.add(job)
        db_session.flush()

        run = TranslationRun(
            id="run-batch",
            job_id="job-batch",
            status="RUNNING",
            started_at=datetime.now(UTC),
            created_by="test_user",
        )
        db_session.add(run)
        db_session.flush()

        # Create batch
        batch = TranslationBatch(
            id="batch-1",
            run_id="run-batch",
            batch_index=0,
            status="PENDING",
            total_records=3,
        )
        db_session.add(batch)
        db_session.flush()

        # Create records
        for i in range(3):
            record = TranslationRecord(
                id=f"record-{i}",
                batch_id="batch-1",
                run_id="run-batch",
                source_sql=f"SELECT {i}",
                target_sql=f"INSERT INTO target VALUES ({i})",
                source_object_type="table_row",
                source_object_id=str(i),
                status="SUCCESS",
            )
            db_session.add(record)
        db_session.commit()

        # Verify batch
        fetched_batch = (
            db_session.query(TranslationBatch)
            .filter(TranslationBatch.id == "batch-1")
            .first()
        )
        assert fetched_batch is not None
        assert fetched_batch.run_id == "run-batch"
        assert fetched_batch.total_records == 3

        # Verify records
        records = (
            db_session.query(TranslationRecord)
            .filter(TranslationRecord.batch_id == "batch-1")
            .all()
        )
        assert len(records) == 3
        assert all(r.status == "SUCCESS" for r in records)
    # endregion test_create_batch_with_records

    # region test_cascade_delete_run_deletes_batches_and_records [C:2] [TYPE Function]
    # @BRIEF Verify deleting run cascades to batches and records.
    def test_cascade_delete_run_deletes_batches_and_records(self, db_session: Session):
        job = TranslationJob(
            id="job-cascade",
            name="Cascade Job",
            source_dialect="postgresql",
            target_dialect="clickhouse",
            status="ACTIVE",
            source_datasource_id="42",
            translation_column="name",
            created_by="test_user",
        )
        db_session.add(job)
        db_session.flush()

        run = TranslationRun(
            id="run-cascade",
            job_id="job-cascade",
            status="COMPLETED",
            started_at=datetime.now(UTC),
            completed_at=datetime.now(UTC),
            created_by="test_user",
        )
        db_session.add(run)
        db_session.flush()

        batch = TranslationBatch(
            id="batch-cascade",
            run_id="run-cascade",
            batch_index=0,
            status="COMPLETED",
            total_records=1,
        )
        db_session.add(batch)
        db_session.flush()

        record = TranslationRecord(
            id="record-cascade",
            batch_id="batch-cascade",
            run_id="run-cascade",
            source_sql="SELECT 1",
            status="SUCCESS",
        )
        db_session.add(record)
        db_session.commit()

        # Delete run
        db_session.delete(run)
        db_session.commit()

        # Verify cascade
        remaining_batch = (
            db_session.query(TranslationBatch)
            .filter(TranslationBatch.id == "batch-cascade")
            .first()
        )
        assert remaining_batch is None

        remaining_record = (
            db_session.query(TranslationRecord)
            .filter(TranslationRecord.id == "record-cascade")
            .first()
        )
        assert remaining_record is None
    # endregion test_cascade_delete_run_deletes_batches_and_records

# #endregion TestTranslationBatchRecordIntegration

# #endregion OrchestratorIntegrationTests
