"""
CRUD for file_schedules in askme360_questions.db (main DATABASE_URL).
"""
from __future__ import annotations

import os
from datetime import datetime, timedelta
from typing import Any, Dict, List, Optional

from sqlalchemy import select

from app.core.database import get_db_session
from app.models.file import FileSchedule, SchedulerStatus


def _normalize_agent_id(agent_id: Optional[str]) -> Optional[str]:
    aid = str(agent_id or "").strip()
    return aid or None


def _normalize_path(file_path: str) -> str:
    return os.path.normpath(os.path.abspath(str(file_path)))


_ACTIVE_FILE_SCHEDULE_STATUSES = frozenset({"pending", "processing"})


def _paths_related(path_a: str, path_b: str) -> bool:
    """True if paths are equal or one is an ancestor directory of the other."""
    a = _normalize_path(path_a)
    b = _normalize_path(path_b)
    if not a or not b:
        return False
    if a == b:
        return True
    sep = os.sep
    return a.startswith(b + sep) or b.startswith(a + sep)


def _row_to_dict(row: FileSchedule) -> Dict[str, Any]:
    return {
        "id": row.id,
        "file_path": row.file_path,
        "agent_id": row.agent_id,
        "job_id": row.job_id,
        "root_path": row.root_path,
        "status": row.status,
        "created_at": row.created_at,
    }


class FileScheduleService:
    """Access file_schedules and scheduler_status on the main application SQLite database."""

    def get_scheduler_status(self) -> Optional[Dict[str, Any]]:
        try:
            with get_db_session() as db:
                q = select(SchedulerStatus).where(SchedulerStatus.id == 1)
                row = db.execute(q).scalars().first()
                if row:
                    # Auto-correct stale values: if active_seconds or rest_seconds
                    # are unreasonably small (< 60), they were stored incorrectly.
                    needs_fix = False
                    if row.active_seconds is None or row.active_seconds < 60:
                        row.active_seconds = 1200
                        needs_fix = True
                    if row.rest_seconds is None or row.rest_seconds < 60:
                        row.rest_seconds = 600
                        needs_fix = True
                    if needs_fix:
                        # Also update phase_started_at and next_phase_at if they are missing
                        if not row.phase_started_at:
                            row.phase_started_at = datetime.now().isoformat()
                        if not row.next_phase_at:
                            phase_dur = row.rest_seconds if (row.phase or "active").lower() == "rest" else row.active_seconds
                            started = datetime.fromisoformat(row.phase_started_at) if row.phase_started_at else datetime.now()
                            row.next_phase_at = (started + timedelta(seconds=phase_dur)).isoformat()
                        db.commit()
                        print(f"[SCHEDULER] Auto-corrected scheduler_status: active_seconds={row.active_seconds}, rest_seconds={row.rest_seconds}")
                    return {
                        "id": row.id,
                        "current_job_id": row.current_job_id,
                        "phase": row.phase,
                        "active_seconds": row.active_seconds,
                        "rest_seconds": row.rest_seconds,
                        "phase_started_at": row.phase_started_at,
                        "next_phase_at": row.next_phase_at,
                    }
                # If not exists, insert default
                now = datetime.now()
                now_iso = now.isoformat()
                next_iso = (now + timedelta(seconds=1200)).isoformat()
                default_status = SchedulerStatus(
                    id=1,
                    current_job_id=None,
                    phase="active",
                    active_seconds=1200,
                    rest_seconds=600,
                    phase_started_at=now_iso,
                    next_phase_at=next_iso,
                )
                db.add(default_status)
                db.commit()
                return {
                    "id": 1,
                    "current_job_id": None,
                    "phase": "active",
                    "active_seconds": 1200,
                    "rest_seconds": 600,
                    "phase_started_at": now_iso,
                    "next_phase_at": next_iso,
                }
        except Exception as e:
            print(f"Error get_scheduler_status: {e}")
            return None

    def update_scheduler_status(
        self,
        *,
        phase: Optional[str] = None,
        phase_started_at: Optional[str] = None,
        next_phase_at: Optional[str] = None,
        active_seconds: Optional[int] = None,
        rest_seconds: Optional[int] = None,
        current_job_id: Optional[str] = None,
        clear_job_id: bool = False,
    ) -> bool:
        try:
            with get_db_session() as db:
                q = select(SchedulerStatus).where(SchedulerStatus.id == 1)
                row = db.execute(q).scalars().first()
                if not row:
                    effective_phase = (phase if phase is not None else "active").lower()
                    effective_active = active_seconds if active_seconds is not None else 1200
                    effective_rest = rest_seconds if rest_seconds is not None else 600
                    now_iso = datetime.now().isoformat()
                    if phase_started_at is None:
                        phase_started_at = now_iso
                    if next_phase_at is None:
                        phase_dur = effective_rest if effective_phase == "rest" else effective_active
                        next_phase_at = (
                            datetime.fromisoformat(phase_started_at)
                            + timedelta(seconds=max(1, int(phase_dur)))
                        ).isoformat()
                    row = SchedulerStatus(
                        id=1,
                        phase=phase if phase is not None else "active",
                        active_seconds=effective_active,
                        rest_seconds=effective_rest,
                        phase_started_at=phase_started_at,
                        next_phase_at=next_phase_at,
                        current_job_id=current_job_id,
                    )
                    db.add(row)
                else:
                    if phase is not None:
                        row.phase = phase
                    if phase_started_at is not None:
                        row.phase_started_at = phase_started_at
                    if next_phase_at is not None:
                        row.next_phase_at = next_phase_at
                    if active_seconds is not None:
                        row.active_seconds = active_seconds
                    if rest_seconds is not None:
                        row.rest_seconds = rest_seconds
                    if current_job_id is not None:
                        row.current_job_id = current_job_id
                    if clear_job_id:
                        row.current_job_id = None
                    effective_phase = (phase if phase is not None else row.phase or "active").lower()
                    reset_phase_window = (
                        phase_started_at is None
                        and next_phase_at is None
                        and (
                            (rest_seconds is not None and effective_phase == "rest")
                            or (active_seconds is not None and effective_phase == "active")
                        )
                    )
                    if reset_phase_window:
                        now = datetime.now()
                        phase_dur = (
                            int(row.rest_seconds or 600)
                            if effective_phase == "rest"
                            else int(row.active_seconds or 1200)
                        )
                        row.phase_started_at = now.isoformat()
                        row.next_phase_at = (
                            now + timedelta(seconds=max(1, phase_dur))
                        ).isoformat()
            return True
        except Exception as e:
            print(f"Error update_scheduler_status: {e}")
            return False

    def get_file_schedule_by_job_id(self, job_id: str) -> Optional[Dict[str, Any]]:
        try:
            with get_db_session() as db:
                q = select(FileSchedule).where(FileSchedule.job_id == job_id)
                row = db.execute(q).scalars().first()
                if row:
                    return {
                        "id": row.id,
                        "file_path": row.file_path,
                        "agent_id": row.agent_id,
                        "status": row.status,
                        "job_id": row.job_id,
                        "root_path": row.root_path,
                        "created_at": row.created_at,
                    }
                return None
        except Exception as e:
            print(f"Error get_file_schedule_by_job_id: {e}")
            return None

    def cancel_file_schedule(self, job_id: str) -> Optional[str]:
        """Mark job as cancelled, return the file path if found."""
        try:
            file_path = None
            with get_db_session() as db:
                q = select(FileSchedule).where(FileSchedule.job_id == job_id)
                rows = db.execute(q).scalars().all()
                for row in rows:
                    row.status = "cancelled"
                    file_path = row.file_path
            return file_path
        except Exception as e:
            print(f"Error cancel_file_schedule: {e}")
            return None

    def upsert_file_schedule(
        self,
        *,
        file_path: str,
        agent_id: Optional[str] = None,
        schedule_enabled: bool = True,
        cycle_seconds: int = 1800,
        active_seconds: int = 1200,
        rest_seconds: int = 600,
        phase: str = "active",
        status: str = "pending",
        job_id: Optional[str] = None,
        root_path: Optional[str] = None,
    ) -> Optional[str]:
        """
        Enqueue a task in file_schedules queue.

        Returns the ``job_id`` to use (new or already-active). Returns ``None`` on error.
        Only one active (pending/processing) row per agent+path is kept.
        """
        try:
            path_norm = _normalize_path(file_path)
            aid = _normalize_agent_id(agent_id)
            root_norm = _normalize_path(root_path) if root_path else path_norm
            now = datetime.now().isoformat()
            queue_status = "pending" if schedule_enabled else "completed"
            if status in _ACTIVE_FILE_SCHEDULE_STATUSES:
                queue_status = status

            with get_db_session() as db:
                q = select(FileSchedule).where(
                    (FileSchedule.file_path == path_norm) | (FileSchedule.root_path == path_norm)
                )
                if aid is None:
                    q = q.where(FileSchedule.agent_id.is_(None))
                else:
                    q = q.where(FileSchedule.agent_id == aid)
                q = q.where(FileSchedule.status.in_(_ACTIVE_FILE_SCHEDULE_STATUSES))
                q = q.order_by(FileSchedule.id.desc())
                existing_rows = list(db.execute(q).scalars().all())
                if existing_rows:
                    keep = existing_rows[0]
                    keep_jid = str(keep.job_id or job_id or "").strip() or None
                    for extra in existing_rows[1:]:
                        extra.status = "cancelled"
                    return keep_jid

                db.add(
                    FileSchedule(
                        file_path=path_norm,
                        agent_id=aid,
                        job_id=job_id,
                        root_path=root_norm,
                        status=queue_status,
                        created_at=now,
                    )
                )
            if job_id:
                self.consolidate_active_schedules_for_path(
                    file_path=path_norm,
                    agent_id=aid or "",
                    keep_job_id=str(job_id),
                )
            return str(job_id).strip() if job_id else None
        except Exception as e:
            print(f"Error upsert_file_schedule: {e}")
            return None

    def consolidate_active_schedules_for_path(
        self,
        *,
        file_path: str,
        agent_id: str,
        keep_job_id: str,
    ) -> int:
        """Cancel duplicate active rows for the same path, keeping a single job_id."""
        try:
            path_norm = _normalize_path(file_path)
            aid = _normalize_agent_id(agent_id)
            keep = str(keep_job_id or "").strip()
            if not path_norm or not keep:
                return 0
            cancelled = 0
            with get_db_session() as db:
                q = select(FileSchedule).where(
                    (FileSchedule.file_path == path_norm) | (FileSchedule.root_path == path_norm)
                )
                if aid is None:
                    q = q.where(FileSchedule.agent_id.is_(None))
                else:
                    q = q.where(FileSchedule.agent_id == aid)
                q = q.where(FileSchedule.status.in_(_ACTIVE_FILE_SCHEDULE_STATUSES))
                rows = list(db.execute(q).scalars().all())
                for row in rows:
                    if str(row.job_id or "").strip() != keep:
                        row.status = "cancelled"
                        cancelled += 1
            return cancelled
        except Exception as e:
            print(f"Error consolidate_active_schedules_for_path: {e}")
            return 0

    def list_file_schedules_for_agent(
        self,
        *,
        agent_id: str,
        file_path: Optional[str] = None,
    ) -> List[Dict[str, Any]]:
        """All file_schedules rows for an agent, optionally filtered by file_path/root_path."""
        try:
            aid = _normalize_agent_id(agent_id)
            if not aid:
                return []
            path_filter = _normalize_path(file_path) if file_path and str(file_path).strip() else ""
            with get_db_session() as db:
                q = select(FileSchedule).where(FileSchedule.agent_id == aid)
                if path_filter:
                    q = q.where(
                        (FileSchedule.file_path == path_filter) | (FileSchedule.root_path == path_filter)
                    )
                q = q.order_by(FileSchedule.created_at.desc())
                rows = list(db.execute(q).scalars().all())
                
                results = []
                for r in rows:
                    results.append({
                        "id": r.id,
                        "file_path": r.file_path,
                        "agent_id": r.agent_id,
                        "schedule_enabled": 1 if r.status in ("pending", "processing") else 0,
                        "status": r.status,
                        "job_id": r.job_id,
                        "root_path": r.root_path,
                        "created_at": r.created_at,
                    })
                return results
        except Exception as e:
            print(f"Error list_file_schedules_for_agent: {e}")
            return []

    def get_file_schedule(
        self,
        *,
        file_path: str,
        agent_id: Optional[str] = None,
    ) -> Optional[Dict[str, Any]]:
        """Find if there is any pending or processing task for file_path / root_path."""
        try:
            path_norm = _normalize_path(file_path)
            aid = _normalize_agent_id(agent_id)
            with get_db_session() as db:
                q = select(FileSchedule).where(
                    (FileSchedule.file_path == path_norm) | (FileSchedule.root_path == path_norm)
                )
                if aid is None:
                    q = q.where(FileSchedule.agent_id.is_(None))
                else:
                    q = q.where(FileSchedule.agent_id == aid)
                
                q = q.where(FileSchedule.status.in_(_ACTIVE_FILE_SCHEDULE_STATUSES))
                q = q.order_by(
                    FileSchedule.status.desc(),
                    FileSchedule.created_at.asc(),
                )
                row = db.execute(q).scalars().first()
                if row:
                    return {
                        "id": row.id,
                        "file_path": row.file_path,
                        "agent_id": row.agent_id,
                        "schedule_enabled": 1 if row.status in ("pending", "processing") else 0,
                        "status": row.status,
                        "job_id": row.job_id,
                        "root_path": row.root_path,
                        "created_at": row.created_at,
                    }
                return None
        except Exception as e:
            print(f"Error get_file_schedule: {e}")
            return None

    def get_file_schedule_row(
        self,
        *,
        file_path: str,
        agent_id: Optional[str] = None,
    ) -> Optional[Dict[str, Any]]:
        """Latest file_schedules row for path (any status: pending through cancelled)."""
        try:
            path_norm = _normalize_path(file_path)
            aid = _normalize_agent_id(agent_id)
            with get_db_session() as db:
                q = select(FileSchedule).where(
                    (FileSchedule.file_path == path_norm) | (FileSchedule.root_path == path_norm)
                )
                if aid is None:
                    q = q.where(FileSchedule.agent_id.is_(None))
                else:
                    q = q.where(FileSchedule.agent_id == aid)
                q = q.order_by(FileSchedule.created_at.desc())
                row = db.execute(q).scalars().first()
                if row:
                    return {
                        "id": row.id,
                        "file_path": row.file_path,
                        "agent_id": row.agent_id,
                        "schedule_enabled": 1 if row.status in ("pending", "processing") else 0,
                        "status": row.status,
                        "job_id": row.job_id,
                        "root_path": row.root_path,
                        "created_at": row.created_at,
                    }
                return None
        except Exception as e:
            print(f"Error get_file_schedule_row: {e}")
            return None

    def delete_file_schedules_touching_paths(
        self,
        *,
        paths: list[str],
        agent_id: Optional[str] = None,
    ) -> int:
        """Remove queue tasks for paths that match or overlap paths."""
        paths_norm = list(
            {_normalize_path(p) for p in paths if p and str(p).strip()}
        )
        if not paths_norm:
            return 0
        try:
            aid = _normalize_agent_id(agent_id)
            deleted = 0
            with get_db_session() as db:
                q = select(FileSchedule)
                if aid is None:
                    q = q.where(FileSchedule.agent_id.is_(None))
                else:
                    q = q.where(FileSchedule.agent_id == aid)
                rows = list(db.execute(q).scalars().all())
                for row in rows:
                    sp = row.file_path or ""
                    rp = row.root_path or ""
                    if any(_paths_related(sp, p) or _paths_related(rp, p) for p in paths_norm):
                        db.delete(row)
                        deleted += 1
            return deleted
        except Exception as e:
            print(f"Error delete_file_schedules_touching_paths: {e}")
            return 0

    def cancel_all_active_schedules(
        self,
        *,
        agent_id: str,
    ) -> List[Dict[str, Any]]:
        """
        Cancel every pending/processing schedule for the agent in one transaction.
        Returns a list of dicts {job_id, file_path} for each cancelled row.
        """
        try:
            aid = _normalize_agent_id(agent_id)
            if not aid:
                return []
            cancelled = []
            with get_db_session() as db:
                q = select(FileSchedule).where(
                    (FileSchedule.agent_id == aid)
                    & (FileSchedule.status.in_(["running", "pending", "processing"]))
                )
                rows = db.execute(q).scalars().all()
                for row in rows:
                    previous_status = row.status
                    row.status = "cancelled"
                    cancelled.append(
                        {
                            "job_id": row.job_id,
                            "file_path": row.file_path,
                            "status": "cancelled",
                            "previous_status": previous_status,
                        }
                    )
            return cancelled
        except Exception as e:
            print(f"Error cancel_all_active_schedules: {e}")
            return []

    def disable_file_schedule(
        self,
        *,
        file_path: str,
        agent_id: Optional[str] = None,
        job_id: Optional[str] = None,
    ) -> bool:
        """
        Mark pending/processing queue rows as cancelled.

        When ``job_id`` is set, only that row is updated so a new re-upload on the
        same path is not affected by a stop on a previous job.
        """
        try:
            path_norm = _normalize_path(file_path)
            aid = _normalize_agent_id(agent_id)
            jid = str(job_id or "").strip() or None
            with get_db_session() as db:
                q = select(FileSchedule).where(
                    FileSchedule.status.in_(["pending", "processing", "running"])
                )
                if jid:
                    q = q.where(FileSchedule.job_id == jid)
                elif path_norm:
                    q = q.where(
                        (FileSchedule.file_path == path_norm)
                        | (FileSchedule.root_path == path_norm)
                    )
                else:
                    return False
                if aid is None:
                    q = q.where(FileSchedule.agent_id.is_(None))
                else:
                    q = q.where(FileSchedule.agent_id == aid)

                rows = db.execute(q).scalars().all()
                for row in rows:
                    row.status = "cancelled"
            return True
        except Exception as e:
            print(f"Error disable_file_schedule: {e}")
            return False

    def update_file_schedule_phase(
        self,
        *,
        file_path: str,
        agent_id: Optional[str],
        phase: str,
        override_dur_seconds: Optional[int] = None,
    ) -> bool:
        """Update global scheduler_status phase."""
        try:
            phase_norm = "rest" if str(phase).strip().lower() == "rest" else "active"
            status = self.get_scheduler_status()
            if not status:
                return False
            
            if override_dur_seconds is not None:
                dur = override_dur_seconds
            else:
                dur = int(status["rest_seconds"] if phase_norm == "rest" else status["active_seconds"])
            
            now = datetime.now()
            now_iso = now.isoformat()
            next_phase_iso = (now + timedelta(seconds=max(1, dur))).isoformat()
            
            return self.update_scheduler_status(
                phase=phase_norm,
                phase_started_at=now_iso,
                next_phase_at=next_phase_iso
            )
        except Exception as e:
            print(f"Error update_file_schedule_phase: {e}")
            return False
