from __future__ import annotations

import json
import sqlite3
from datetime import datetime, timezone
from pathlib import Path
from typing import Optional

from .models import Priority, Status, Todo
from .timebox import BoxState, TimeBox

SCHEMA = """
CREATE TABLE IF NOT EXISTS todos (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    repo TEXT DEFAULT '',
    status TEXT NOT NULL DEFAULT 'open',
    priority TEXT NOT NULL DEFAULT 'medium',
    assignee TEXT DEFAULT '',
    tags TEXT DEFAULT '',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    resolved_at TEXT DEFAULT NULL
);
CREATE INDEX IF NOT EXISTS idx_todos_status ON todos(status);
CREATE INDEX IF NOT EXISTS idx_todos_repo ON todos(repo);

CREATE TABLE IF NOT EXISTS time_boxes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    todo_id INTEGER REFERENCES todos(id),
    duration_seconds INTEGER NOT NULL,
    state TEXT NOT NULL DEFAULT 'open',
    accumulated_seconds REAL NOT NULL DEFAULT 0.0,
    last_started_at TEXT,
    interruptions TEXT NOT NULL DEFAULT '[]',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    completed_at TEXT,
    reason TEXT
);
CREATE INDEX IF NOT EXISTS idx_timebox_state ON time_boxes(state);
"""


class Store:
    def __init__(self, db_path: str = ".agent_todos.db") -> None:
        self.db_path = db_path
        self._ensure()

    def _ensure(self) -> None:
        first = not Path(self.db_path).exists()
        with sqlite3.connect(self.db_path) as con:
            con.executescript(SCHEMA)
            con.commit()
        if first:
            self.seed()

    def seed(self) -> None:
        now = datetime.utcnow().isoformat() + "Z"
        self.create(Todo(
            id=None,
            title="Scaffold agent-todo-tracker CLI",
            repo="stackchain/agent-todo-tracker",
            status=Status.resolved,
            priority=Priority.high,
            tags=["hackathon", "p2"],
            created_at=now,
            updated_at=now,
            resolved_at=now,
        ))
        self.create(Todo(
            id=None,
            title="Add Gitea issue sync for TODOs",
            repo="stackchain/stackchain-hackathon",
            status=Status.open,
            priority=Priority.medium,
            tags=["hackathon", "p1"],
            created_at=now,
            updated_at=now,
        ))

    def create(self, todo: Todo) -> Todo:
        now = datetime.utcnow().isoformat() + "Z"
        todo.created_at = now
        todo.updated_at = now
        with sqlite3.connect(self.db_path) as con:
            cur = con.execute(
                "INSERT INTO todos (title, repo, status, priority, assignee, tags, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
                (
                    todo.title,
                    todo.repo,
                    todo.status.value,
                    todo.priority.value,
                    todo.assignee,
                    ",".join(todo.tags),
                    todo.created_at,
                    todo.updated_at,
                ),
            )
            todo.id = cur.lastrowid
            con.commit()
        return todo

    def list(self, status: Optional[Status] = None, repo: Optional[str] = None) -> list[Todo]:
        query = "SELECT * FROM todos WHERE 1=1"
        params: list = []
        if status:
            query += " AND status = ?"
            params.append(status.value)
        if repo:
            query += " AND repo = ?"
            params.append(repo)
        query += " ORDER BY created_at DESC"
        with sqlite3.connect(self.db_path) as con:
            con.row_factory = sqlite3.Row
            rows = con.execute(query, params).fetchall()
        return [self._row_to_todo(r) for r in rows]

    def update(self, todo_id: int, **kwargs) -> Optional[Todo]:
        todo = self.get(todo_id)
        if not todo:
            return None
        allowed = {"title", "repo", "status", "priority", "assignee", "tags", "resolved_at"}
        fields = {k: v for k, v in kwargs.items() if k in allowed}
        if not fields:
            return todo
        now = datetime.utcnow().isoformat() + "Z"
        sets = ["updated_at = ?"]
        params = [now]
        for k, v in fields.items():
            if k == "tags" and isinstance(v, list):
                v = ",".join(v)
            if k == "status":
                v = v.value if isinstance(v, Status) else v
            if k == "priority":
                v = v.value if isinstance(v, Priority) else v
            sets.append(f"{k} = ?")
            params.append(v)
        if fields.get("status") == Status.resolved.value:
            sets.append("resolved_at = ?")
            params.append(now)
        params.append(todo_id)
        with sqlite3.connect(self.db_path) as con:
            con.execute(f"UPDATE todos SET {', '.join(sets)} WHERE id = ?", params)
            con.commit()
        return self.get(todo_id)

    def get(self, todo_id: int) -> Optional[Todo]:
        with sqlite3.connect(self.db_path) as con:
            con.row_factory = sqlite3.Row
            row = con.execute("SELECT * FROM todos WHERE id = ?", (todo_id,)).fetchone()
        return self._row_to_todo(row) if row else None

    def _row_to_todo(self, row: sqlite3.Row) -> Todo:
        return Todo(
            id=row["id"],
            title=row["title"],
            repo=row["repo"],
            status=Status(row["status"]),
            priority=Priority(row["priority"]),
            assignee=row["assignee"],
            tags=[t for t in row["tags"].split(",") if t],
            created_at=row["created_at"],
            updated_at=row["updated_at"],
            resolved_at=row["resolved_at"],
        )

    # ----- time_boxes -----------------------------------------------------

    def create_timebox(self, box: TimeBox) -> TimeBox:
        now = datetime.now(timezone.utc).isoformat()
        box.created_at = now
        box.updated_at = now
        with sqlite3.connect(self.db_path) as con:
            cur = con.execute(
                "INSERT INTO time_boxes "
                "(todo_id, duration_seconds, state, accumulated_seconds, "
                "last_started_at, interruptions, created_at, updated_at, "
                "completed_at, reason) "
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
                (
                    box.todo_id,
                    box.duration_seconds,
                    box.state.value,
                    box.accumulated_seconds,
                    box.last_started_at,
                    json.dumps(box.interruptions),
                    box.created_at,
                    box.updated_at,
                    box.completed_at,
                    box.reason,
                ),
            )
            box.id = cur.lastrowid
            con.commit()
        return box

    def get_timebox(self, box_id: int) -> Optional[TimeBox]:
        with sqlite3.connect(self.db_path) as con:
            con.row_factory = sqlite3.Row
            row = con.execute(
                "SELECT * FROM time_boxes WHERE id = ?", (box_id,)
            ).fetchone()
        return self._row_to_timebox(row) if row else None

    def list_timeboxes(self, active_only: bool = False) -> list[TimeBox]:
        query = "SELECT * FROM time_boxes"
        if active_only:
            states = ", ".join(f"'{s.value}'" for s in BoxState if s not in {
                BoxState.completed, BoxState.abandoned, BoxState.expired
            })
            query += f" WHERE state IN ({states})"
        query += " ORDER BY updated_at DESC"
        with sqlite3.connect(self.db_path) as con:
            con.row_factory = sqlite3.Row
            rows = con.execute(query).fetchall()
        return [self._row_to_timebox(r) for r in rows]

    def update_timebox(self, box_id: int, **kwargs) -> Optional[TimeBox]:
        box = self.get_timebox(box_id)
        if not box:
            return None
        allowed = {
            "todo_id", "duration_seconds", "state", "accumulated_seconds",
            "last_started_at", "interruptions", "completed_at", "reason",
        }
        fields = {k: v for k, v in kwargs.items() if k in allowed}
        if not fields:
            return box
        now = datetime.now(timezone.utc).isoformat()
        sets = ["updated_at = ?"]
        params: list = [now]
        for k, v in fields.items():
            if k == "interruptions" and isinstance(v, list):
                v = json.dumps(v)
            if k == "state":
                v = v.value if isinstance(v, BoxState) else v
            sets.append(f"{k} = ?")
            params.append(v)
        params.append(box_id)
        with sqlite3.connect(self.db_path) as con:
            con.execute(
                f"UPDATE time_boxes SET {', '.join(sets)} WHERE id = ?", params
            )
            con.commit()
        return self.get_timebox(box_id)

    def recover_timebox(self) -> Optional[TimeBox]:
        """Return the most-recent active box, or None if none exists.

        Use this after an interruption (crash, reboot, agent restart) so the
        user can resume exactly where they left off.
        """
        states = ", ".join(f"'{s.value}'" for s in BoxState if s not in {
            BoxState.completed, BoxState.abandoned, BoxState.expired
        })
        query = (
            f"SELECT * FROM time_boxes WHERE state IN ({states}) "
            "ORDER BY updated_at DESC LIMIT 1"
        )
        with sqlite3.connect(self.db_path) as con:
            con.row_factory = sqlite3.Row
            row = con.execute(query).fetchone()
        return self._row_to_timebox(row) if row else None

    def _row_to_timebox(self, row: sqlite3.Row) -> TimeBox:
        return TimeBox(
            id=row["id"],
            todo_id=row["todo_id"],
            duration_seconds=row["duration_seconds"],
            state=BoxState(row["state"]),
            accumulated_seconds=row["accumulated_seconds"] or 0.0,
            last_started_at=row["last_started_at"],
            interruptions=json.loads(row["interruptions"] or "[]"),
            created_at=row["created_at"],
            updated_at=row["updated_at"],
            completed_at=row["completed_at"],
            reason=row["reason"],
        )