"""
Scripture Oracle Service for Bible-Companion.
Semantic verse retrieval using embeddings + SQLite persistence.
Adapted from Echo's oracle.js pattern.
"""

import json
import logging
import sqlite3
from pathlib import Path
from typing import List, Dict, Optional, Tuple
import numpy as np
from sentence_transformers import SentenceTransformer

logger = logging.getLogger(__name__)


class SimpleEmbeddingIndex:
    """
    In-memory embedding index with cosine similarity search.
    Mirrors Echo's SimpleEmbeddingIndex pattern.
    """

    def __init__(self):
        self.embeddings = {}  # verse_id -> embedding vector
        self.metadata = {}    # verse_id -> metadata dict

    def add(self, verse_id: str, embedding: List[float], metadata: Dict = None):
        """Add embedding to index"""
        self.embeddings[verse_id] = np.array(embedding, dtype=np.float32)
        self.metadata[verse_id] = metadata or {}

    def search(self, query_embedding: List[float], k: int = 5) -> List[Tuple[str, float]]:
        """
        Search for similar embeddings using cosine similarity.

        Args:
            query_embedding: Query vector
            k: Number of results to return

        Returns:
            List of (verse_id, similarity_score) tuples
        """
        if not self.embeddings:
            return []

        query_vec = np.array(query_embedding, dtype=np.float32)

        # Normalize for cosine similarity
        query_norm = np.linalg.norm(query_vec)
        if query_norm == 0:
            return []
        query_vec = query_vec / query_norm

        similarities = []
        for verse_id, embedding in self.embeddings.items():
            # Normalize embedding
            emb_norm = np.linalg.norm(embedding)
            if emb_norm > 0:
                embedding = embedding / emb_norm

            # Cosine similarity
            similarity = float(np.dot(query_vec, embedding))
            similarities.append((verse_id, similarity))

        # Sort by similarity descending
        similarities.sort(key=lambda x: x[1], reverse=True)

        return similarities[:k]


class ScriptureOracle:
    """
    Scripture Oracle for Bible-Companion.
    Manages verse retrieval, doctrinal facts, and semantic search.
    """

    def __init__(self, data_dir: str = 'data', db_name: str = 'oracle.db'):
        """
        Initialize Scripture Oracle.

        Args:
            data_dir: Directory for storing data
            db_name: SQLite database filename
        """
        self.data_dir = Path(data_dir)
        self.db_path = self.data_dir / db_name
        self.embedding_model = None
        self.embedding_index = SimpleEmbeddingIndex()
        self.disallowed_translations = {"ESV"}

        self._ensure_data_dir()
        self._init_db()
        self._load_embedding_model()
        self._load_index_from_db()

    def _ensure_data_dir(self):
        """Create data directory if it doesn't exist"""
        self.data_dir.mkdir(parents=True, exist_ok=True)
        logger.info(f"📁 Data directory: {self.data_dir.absolute()}")

    def _init_db(self):
        """Initialize SQLite database with required tables"""
        conn = sqlite3.connect(str(self.db_path))
        cursor = conn.cursor()

        # Verses table: stores Scripture passages
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS verses (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                reference TEXT NOT NULL UNIQUE,
                chapter INTEGER NOT NULL,
                verse_start INTEGER NOT NULL,
                verse_end INTEGER,
                translation TEXT NOT NULL,
                text TEXT NOT NULL,
                embedding_vector BLOB,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')

        # Oracle facts table: doctrinal facts, cross-references
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS oracle_facts (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                text TEXT NOT NULL,
                type TEXT NOT NULL,
                confidence REAL DEFAULT 0.8,
                embedding_vector BLOB,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')

        # Doctrinal cross-references: OT -> NT mappings
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS doctrinal_xrefs (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                ot_reference TEXT NOT NULL,
                nt_reference TEXT NOT NULL,
                explanation TEXT,
                doctrine_type TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')

        conn.commit()
        conn.close()
        logger.info(f"✅ Database initialized: {self.db_path}")

    def _load_embedding_model(self):
        """Load all-MiniLM-L6-v2 embedding model"""
        try:
            self.embedding_model = SentenceTransformer('all-MiniLM-L6-v2')
            logger.info("✅ Embedding model loaded: all-MiniLM-L6-v2 (22MB)")
        except Exception as e:
            logger.error(f"❌ Failed to load embedding model: {e}")
            raise

    def _load_index_from_db(self):
        """Load existing embeddings from database into memory index"""
        conn = sqlite3.connect(str(self.db_path))
        cursor = conn.cursor()

        try:
            cursor.execute('SELECT id, reference, translation, embedding_vector FROM verses WHERE embedding_vector IS NOT NULL')
            verses = cursor.fetchall()

            for verse_id, reference, translation, embedding_blob in verses:
                if not self._is_translation_allowed(translation):
                    continue
                embedding = np.frombuffer(embedding_blob, dtype=np.float32).tolist()
                self.embedding_index.add(
                    f"verse_{verse_id}",
                    embedding,
                    {"reference": reference, "translation": translation, "type": "verse"}
                )

            logger.info(f"📑 Loaded {len(verses)} verse embeddings into index")
        except Exception as e:
            logger.warning(f"⚠️ No embeddings in database yet: {e}")
        finally:
            conn.close()

    def add_verse(
        self,
        reference: str,
        chapter: int,
        verse_start: int,
        verse_end: Optional[int],
        translation: str,
        text: str
    ) -> int:
        """
        Add a Scripture verse to the Oracle.

        Args:
            reference: Scripture reference (e.g., "John 3:16")
            chapter: Chapter number
            verse_start: Starting verse number
            verse_end: Ending verse number (if range)
            translation: Translation name (KJV, NKJV, NIV, etc.)
            text: Verse text

        Returns:
            Verse ID
        """
        if not self._is_translation_allowed(translation):
            logger.info(f"Skipping disallowed translation verse load: {reference} ({translation})")
            return -1

        # Generate embedding
        embedding = self.embedding_model.encode(text)
        embedding_blob = embedding.astype(np.float32).tobytes()

        conn = sqlite3.connect(str(self.db_path))
        cursor = conn.cursor()

        try:
            cursor.execute('''
                INSERT INTO verses (reference, chapter, verse_start, verse_end, translation, text, embedding_vector)
                VALUES (?, ?, ?, ?, ?, ?, ?)
            ''', (reference, chapter, verse_start, verse_end, translation, text, embedding_blob))

            verse_id = cursor.lastrowid
            conn.commit()

            # Add to in-memory index
            self.embedding_index.add(
                f"verse_{verse_id}",
                embedding.tolist(),
                {"reference": reference, "translation": translation, "type": "verse"}
            )

            return verse_id
        except sqlite3.IntegrityError:
            logger.warning(f"⚠️ Verse already exists: {reference} ({translation})")
            return -1
        finally:
            conn.close()

    def add_doctrinal_fact(self, text: str, fact_type: str = "theology", confidence: float = 0.8) -> int:
        """
        Add a doctrinal fact to the Oracle.

        Args:
            text: Doctrinal statement
            fact_type: Type of fact (theology, hermeneutics, comparative, etc.)
            confidence: Confidence score (0.0-1.0)

        Returns:
            Fact ID
        """
        embedding = self.embedding_model.encode(text)
        embedding_blob = embedding.astype(np.float32).tobytes()

        conn = sqlite3.connect(str(self.db_path))
        cursor = conn.cursor()

        try:
            cursor.execute('''
                INSERT INTO oracle_facts (text, type, confidence, embedding_vector)
                VALUES (?, ?, ?, ?)
            ''', (text, fact_type, confidence, embedding_blob))

            fact_id = cursor.lastrowid
            conn.commit()

            logger.info(f"✅ Added doctrinal fact: {text[:60]}...")
            return fact_id
        finally:
            conn.close()

    def retrieve_verses(self, query: str, k: int = 5, excluded_translations: Optional[set[str]] = None) -> List[Dict]:
        """
        Retrieve relevant Scripture passages using semantic search.

        Args:
            query: Search query
            k: Number of results

        Returns:
            List of verse dictionaries
        """
        query_embedding = self.embedding_model.encode(query)
        results = self.embedding_index.search(query_embedding.tolist(), k=k)

        verses = []
        for verse_id, similarity in results:
            # Extract database ID from verse_id
            db_id = verse_id.replace('verse_', '')
            verse = self._get_verse_by_id(int(db_id), excluded_translations=excluded_translations)
            if verse:
                verse['similarity'] = round(similarity, 3)
                verses.append(verse)

        logger.info(f"🔍 Found {len(verses)} relevant verses (query: '{query[:40]}...')")
        return verses

    def retrieve_apologetics_context(
        self,
        query: str,
        k: int = 5,
        min_confidence: float = 0.75
    ) -> List[Dict]:
        """
        Retrieve doctrinal/comparative/historical facts most relevant to query.

        Args:
            query: User query
            k: Number of results to return
            min_confidence: Minimum fact confidence threshold

        Returns:
            List of fact dictionaries with similarity scores
        """
        if not query:
            return []

        query_embedding = self.embedding_model.encode(query)
        query_vec = np.array(query_embedding, dtype=np.float32)
        query_norm = np.linalg.norm(query_vec)
        if query_norm == 0:
            return []
        query_vec = query_vec / query_norm

        conn = sqlite3.connect(str(self.db_path))
        cursor = conn.cursor()

        try:
            cursor.execute('''
                SELECT id, text, type, confidence, embedding_vector
                FROM oracle_facts
                WHERE embedding_vector IS NOT NULL
                  AND confidence >= ?
            ''', (min_confidence,))

            scored = []
            for fact_id, text, fact_type, confidence, embedding_blob in cursor.fetchall():
                try:
                    emb = np.frombuffer(embedding_blob, dtype=np.float32)
                    emb_norm = np.linalg.norm(emb)
                    if emb_norm == 0:
                        continue
                    similarity = float(np.dot(query_vec, emb / emb_norm))
                    scored.append({
                        'id': fact_id,
                        'text': text,
                        'type': fact_type,
                        'confidence': float(confidence),
                        'similarity': round(similarity, 3)
                    })
                except Exception:
                    continue

            scored.sort(key=lambda x: x['similarity'], reverse=True)
            results = scored[:k]
            logger.info(f"🧭 Found {len(results)} apologetics facts (query: '{query[:40]}...')")
            return results
        finally:
            conn.close()

    def retrieve_verses_by_reference(self, reference: str, excluded_translations: Optional[set[str]] = None) -> List[Dict]:
        """
        Retrieve a specific Scripture passage by reference (e.g., "John 3:16").
        Returns all translations of that passage.

        Args:
            reference: Scripture reference

        Returns:
            List of verse dictionaries (all translations)
        """
        conn = sqlite3.connect(str(self.db_path))
        cursor = conn.cursor()

        try:
            cursor.execute('''
                SELECT id, reference, translation, text, chapter, verse_start, verse_end
                FROM verses
                WHERE reference = ?
                ORDER BY translation
            ''', (reference,))

            rows = cursor.fetchall()
            verses = []
            excluded = {t.upper() for t in (excluded_translations or set())}
            excluded.update(self.disallowed_translations)
            for row in rows:
                translation = (row[2] or "").upper()
                if translation in excluded:
                    continue
                verses.append({
                    'id': row[0],
                    'reference': row[1],
                    'translation': row[2],
                    'text': row[3],
                    'chapter': row[4],
                    'verse_start': row[5],
                    'verse_end': row[6]
                })

            return verses
        finally:
            conn.close()

    def get_doctrinal_context(self, reference: str) -> Optional[str]:
        """
        Get doctrinal commentary for a Scripture reference.

        Args:
            reference: Scripture reference

        Returns:
            Doctrinal explanation or None
        """
        conn = sqlite3.connect(str(self.db_path))
        cursor = conn.cursor()

        try:
            cursor.execute('''
                SELECT explanation FROM doctrinal_xrefs
                WHERE ot_reference = ? OR nt_reference = ?
                LIMIT 1
            ''', (reference, reference))

            row = cursor.fetchone()
            return row[0] if row else None
        finally:
            conn.close()

    def _get_verse_by_id(self, verse_id: int, excluded_translations: Optional[set[str]] = None) -> Optional[Dict]:
        """Retrieve verse by database ID"""
        conn = sqlite3.connect(str(self.db_path))
        cursor = conn.cursor()

        try:
            cursor.execute('''
                SELECT id, reference, translation, text, chapter, verse_start, verse_end
                FROM verses
                WHERE id = ?
            ''', (verse_id,))

            row = cursor.fetchone()
            if row:
                translation = (row[2] or "").upper()
                excluded = {t.upper() for t in (excluded_translations or set())}
                excluded.update(self.disallowed_translations)
                if translation in excluded:
                    return None
                return {
                    'id': row[0],
                    'reference': row[1],
                    'translation': row[2],
                    'text': row[3],
                    'chapter': row[4],
                    'verse_start': row[5],
                    'verse_end': row[6]
                }
            return None
        finally:
            conn.close()

    def _is_translation_allowed(self, translation: str) -> bool:
        normalized = (translation or "").upper()
        return bool(normalized) and normalized not in self.disallowed_translations