"""APScheduler-based background sync scheduler."""

from __future__ import annotations

import logging
from typing import Any

from apscheduler.schedulers.asyncio import AsyncIOScheduler
from apscheduler.triggers.cron import CronTrigger

from database.connection_manager import ConnectionManager
from database.local_pg import LocalPG
from knowledge.table_registry import TableRegistry
from knowledge.column_registry import ColumnRegistry
from sync.table_sync import TableSync
from notifications.event_processor import EventProcessor
from notifications.email_sender import EmailSender
from notifications.subscription_manager import SubscriptionManager

logger = logging.getLogger(__name__)


class SyncScheduler:
    """Manages scheduled background sync jobs."""

    def __init__(
        self,
        cm: ConnectionManager,
        local_pg: LocalPG,
        table_registry: TableRegistry,
        column_registry: ColumnRegistry,
        batch_size: int = 10000,
    ) -> None:
        self._cm = cm
        self._local = local_pg
        self._table_reg = table_registry
        self._col_reg = column_registry
        self._batch_size = batch_size
        self._scheduler = AsyncIOScheduler()
        self._syncer = TableSync(cm, local_pg, table_registry, column_registry, batch_size)

    async def start(self) -> None:
        """Start the scheduler and register all configured sync jobs."""
        tables = await self._table_reg.list_synced()

        for table in tables:
            schedule = table.get("sync_schedule")
            if not schedule:
                continue

            connection = await self._cm.fetch_one(
                "SELECT * FROM v3q_config.database_connections WHERE id = :id",
                {"id": str(table["connection_id"])},
            )
            if not connection:
                continue

            self._add_sync_job(
                table_id=str(table["id"]),
                connection_string=connection["connection_string"],
                db_type=connection["db_type"],
                source_schema=table["source_schema"],
                source_table=table["source_table"],
                local_table=table["local_table"],
                cron_expression=schedule,
                connection_id=str(connection["id"]),
                source_system=connection.get("source_system"),
                sync_filter=table.get("sync_filter"),
            )

        self._scheduler.start()
        logger.info(f"Sync scheduler started with {len(self._scheduler.get_jobs())} jobs")

    def _add_sync_job(
        self,
        table_id: str,
        connection_string: str,
        db_type: str,
        source_schema: str,
        source_table: str,
        local_table: str,
        cron_expression: str,
        connection_id: str | None = None,
        source_system: str | None = None,
        sync_filter: str | None = None,
    ) -> None:
        trigger = CronTrigger.from_crontab(cron_expression)

        async def _do_sync():
            try:
                logger.info(f"Scheduled sync starting: {source_schema}.{source_table}" + (f" WHERE {sync_filter}" if sync_filter else ""))
                result = await self._syncer.sync_table(
                    connection_string=connection_string,
                    db_type=db_type,
                    source_schema=source_schema,
                    source_table=source_table,
                    local_table=local_table,
                    connection_id=connection_id,
                    source_system=source_system,
                    sync_filter=sync_filter,
                )
                logger.info(f"Scheduled sync complete: {result['row_count']} rows")

                # Process notification events if any changes were detected
                if result.get("sync_events", 0) > 0:
                    try:
                        sub_mgr = SubscriptionManager(self._cm)
                        processor = EventProcessor(self._cm, sub_mgr)
                        queued = await processor.process_pending_events()
                        if queued > 0:
                            sender = EmailSender(self._cm)
                            sent = await sender.send_pending_notifications()
                            logger.info(f"Post-sync notifications: {queued} queued, {sent} sent")
                    except Exception as ne:
                        logger.warning(f"Post-sync notification processing failed: {ne}")
            except Exception as e:
                logger.error(f"Scheduled sync failed for {source_table}: {e}")

        self._scheduler.add_job(
            _do_sync,
            trigger=trigger,
            id=f"sync_{table_id}",
            replace_existing=True,
        )

    def stop(self) -> None:
        if self._scheduler.running:
            self._scheduler.shutdown(wait=False)

    def get_jobs(self) -> list[dict]:
        return [
            {
                "id": job.id,
                "name": job.name,
                "next_run": str(job.next_run_time) if job.next_run_time else None,
            }
            for job in self._scheduler.get_jobs()
        ]
