from __future__ import annotations

import json
import sqlite3
import uuid
from datetime import datetime, timezone
from pathlib import Path
from typing import Any, Dict, List, Optional

from core.paths import runtime_root


def utc_now_iso() -> str:
    return datetime.now(timezone.utc).isoformat(timespec="milliseconds")


class TransactionStore:
    """Persistent SQLite-backed remediation transaction store."""

    def __init__(self, db_path: Optional[str | Path] = None):
        base_path = Path(db_path) if db_path is not None else runtime_root() / "remediation_transactions.db"
        self.db_path = Path(base_path)
        self.db_path.parent.mkdir(parents=True, exist_ok=True)
        self._initialize()

    def _connect(self) -> sqlite3.Connection:
        conn = sqlite3.connect(str(self.db_path))
        conn.row_factory = sqlite3.Row
        return conn

    def _initialize(self) -> None:
        conn = self._connect()
        try:
            conn.execute(
                """
                CREATE TABLE IF NOT EXISTS remediation_transactions (
                    transaction_id TEXT PRIMARY KEY,
                    finding_id TEXT,
                    requested_action TEXT NOT NULL,
                    timestamp TEXT NOT NULL,
                    before_state TEXT,
                    backup_objects TEXT,
                    actions_attempted TEXT,
                    actions_completed TEXT,
                    verification_results TEXT,
                    rollback_available INTEGER NOT NULL DEFAULT 1,
                    status TEXT NOT NULL DEFAULT 'PREPARED',
                    errors TEXT,
                    metadata TEXT
                )
                """
            )
            conn.commit()
        finally:
            conn.close()

    def create_transaction(
        self,
        finding_id: str,
        requested_action: str,
        before_state: Optional[Dict[str, Any]] = None,
        transaction_id: Optional[str] = None,
        metadata: Optional[Dict[str, Any]] = None,
    ) -> Dict[str, Any]:
        tx_id = transaction_id or f"TX-{uuid.uuid4().hex[:12].upper()}"
        record = {
            "transaction_id": tx_id,
            "finding_id": finding_id,
            "requested_action": requested_action,
            "timestamp": utc_now_iso(),
            "before_state": before_state or {},
            "backup_objects": [],
            "actions_attempted": [],
            "actions_completed": [],
            "verification_results": [],
            "rollback_available": True,
            "status": "PREPARED",
            "errors": [],
            "metadata": metadata or {},
        }

        conn = self._connect()
        try:
            conn.execute(
                """
                INSERT OR REPLACE INTO remediation_transactions (
                    transaction_id, finding_id, requested_action, timestamp,
                    before_state, backup_objects, actions_attempted,
                    actions_completed, verification_results,
                    rollback_available, status, errors, metadata
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                """,
                (
                    tx_id,
                    finding_id,
                    requested_action,
                    record["timestamp"],
                    json.dumps(record["before_state"], sort_keys=True),
                    json.dumps(record["backup_objects"], sort_keys=True),
                    json.dumps(record["actions_attempted"], sort_keys=True),
                    json.dumps(record["actions_completed"], sort_keys=True),
                    json.dumps(record["verification_results"], sort_keys=True),
                    1 if record["rollback_available"] else 0,
                    record["status"],
                    json.dumps(record["errors"], sort_keys=True),
                    json.dumps(record["metadata"], sort_keys=True),
                ),
            )
            conn.commit()
        finally:
            conn.close()

        return record

    def _decode_json_field(self, value: Optional[str], default: Any) -> Any:
        if value in (None, ""):
            return default
        try:
            return json.loads(value)
        except json.JSONDecodeError:
            return default

    def get_transaction(self, transaction_id: str) -> Dict[str, Any]:
        conn = self._connect()
        try:
            row = conn.execute(
                "SELECT * FROM remediation_transactions WHERE transaction_id = ?",
                (transaction_id,),
            ).fetchone()
        finally:
            conn.close()

        if row is None:
            raise KeyError(f"Transaction {transaction_id!r} not found")

        return {
            "transaction_id": row["transaction_id"],
            "finding_id": row["finding_id"],
            "requested_action": row["requested_action"],
            "timestamp": row["timestamp"],
            "before_state": self._decode_json_field(row["before_state"], {}),
            "backup_objects": self._decode_json_field(row["backup_objects"], []),
            "actions_attempted": self._decode_json_field(row["actions_attempted"], []),
            "actions_completed": self._decode_json_field(row["actions_completed"], []),
            "verification_results": self._decode_json_field(row["verification_results"], []),
            "rollback_available": bool(row["rollback_available"]),
            "status": row["status"],
            "errors": self._decode_json_field(row["errors"], []),
            "metadata": self._decode_json_field(row["metadata"], {}),
        }

    def update_status(self, transaction_id: str, status: str) -> Dict[str, Any]:
        tx = self.get_transaction(transaction_id)
        tx["status"] = status

        conn = self._connect()
        try:
            conn.execute(
                "UPDATE remediation_transactions SET status = ? WHERE transaction_id = ?",
                (status, transaction_id),
            )
            conn.commit()
        finally:
            conn.close()
        return tx

    def append_backup(self, transaction_id: str, backup_entry: Dict[str, Any]) -> Dict[str, Any]:
        tx = self.get_transaction(transaction_id)
        tx["backup_objects"].append(backup_entry)
        self._update_transaction_field(transaction_id, "backup_objects", tx["backup_objects"])
        return tx

    def record_action(self, transaction_id: str, action: str, stage: str = "attempted") -> Dict[str, Any]:
        tx = self.get_transaction(transaction_id)
        if stage == "completed":
            if action not in tx["actions_completed"]:
                tx["actions_completed"].append(action)
            self._update_transaction_field(transaction_id, "actions_completed", tx["actions_completed"])
        else:
            if action not in tx["actions_attempted"]:
                tx["actions_attempted"].append(action)
            self._update_transaction_field(transaction_id, "actions_attempted", tx["actions_attempted"])
        return tx

    def record_verification(self, transaction_id: str, result: str) -> Dict[str, Any]:
        tx = self.get_transaction(transaction_id)
        tx["verification_results"].append(result)
        self._update_transaction_field(transaction_id, "verification_results", tx["verification_results"])
        return tx

    def _update_transaction_field(self, transaction_id: str, field_name: str, value: Any) -> None:
        conn = self._connect()
        try:
            conn.execute(
                f"UPDATE remediation_transactions SET {field_name} = ? WHERE transaction_id = ?",
                (json.dumps(value, sort_keys=True), transaction_id),
            )
            conn.commit()
        finally:
            conn.close()

    def list_transactions(self, limit: int = 100) -> List[Dict[str, Any]]:
        conn = self._connect()
        try:
            rows = conn.execute(
                "SELECT transaction_id FROM remediation_transactions ORDER BY timestamp DESC LIMIT ?",
                (limit,),
            ).fetchall()
        finally:
            conn.close()
        return [self.get_transaction(row["transaction_id"]) for row in rows]
