"""
HSEQ Kennisbank Search Engine
FTS5 full-text search
"""

import sqlite3
import os
from collections import Counter

DB_PATH = os.path.join(os.path.dirname(__file__), 'hseq_kennisbank.db')


def get_db():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn


def search_documents(query, category=None, page=1, per_page=20):
    """Full-text search across documents"""
    conn = get_db()

    if not query or not query.strip():
        return {'results': [], 'total': 0, 'pages': 0, 'page': page, 'per_page': per_page}

    # Build FTS5 query - escape special chars
    safe_query = query.strip()
    # Remove characters that break FTS5
    safe_query = safe_query.replace('"', ' ').replace("'", ' ').replace('*', ' ')
    safe_query = ' '.join(safe_query.split())

    if not safe_query:
        return {'results': [], 'total': 0, 'pages': 0, 'page': page, 'per_page': per_page}

    # FTS5 match query
    fts_query = f'"{safe_query}"'

    where_clauses = []
    params = []

    if category:
        where_clauses.append("d.category = ?")
        params.append(category)

    where_sql = " AND ".join(where_clauses)

    # Count total results
    count_sql = f'''
        SELECT COUNT(DISTINCT d.id) as cnt
        FROM documents_fts f
        JOIN documents d ON d.id = f.rowid
        WHERE documents_fts MATCH ? {f"AND {where_sql}" if where_sql else ""}
    '''
    count_params = [fts_query] + params
    total = conn.execute(count_sql, count_params).fetchone()['cnt']

    # Get paginated results
    offset = (page - 1) * per_page
    search_sql = f'''
        SELECT d.id, d.filename, d.title, d.author, d.pages, d.category,
               d.file_size, d.created_date, d.indexed_date,
               snippet(documents_fts, 2, '<mark>', '</mark>', '...', 64) as snippet,
               SUBSTR(d.full_text, 1, 2000) as content,
               rank
        FROM documents_fts f
        JOIN documents d ON d.id = f.rowid
        WHERE documents_fts MATCH ? {f"AND {where_sql}" if where_sql else ""}
        ORDER BY rank
        LIMIT ? OFFSET ?
    '''
    search_params = [fts_query] + params + [per_page, offset]
    rows = conn.execute(search_sql, search_params).fetchall()

    results = []
    for row in rows:
        results.append(dict(row))

    total_pages = max(1, (total + per_page - 1) // per_page)

    conn.close()
    return {
        'results': results,
        'total': total,
        'pages': total_pages,
        'page': page,
        'per_page': per_page
    }


def get_document(doc_id):
    """Get full document by ID"""
    conn = get_db()
    doc = conn.execute('SELECT * FROM documents WHERE id = ?', (doc_id,)).fetchone()
    chunks = conn.execute(
        'SELECT * FROM chunks WHERE document_id = ? ORDER BY chunk_index', (doc_id,)
    ).fetchall()
    conn.close()

    if not doc:
        return None

    return {
        'document': dict(doc),
        'chunks': [dict(c) for c in chunks]
    }


def get_categories():
    """Get all categories with counts"""
    conn = get_db()
    rows = conn.execute('''
        SELECT category, COUNT(*) as count
        FROM documents
        GROUP BY category
        ORDER BY count DESC
    ''').fetchall()
    conn.close()
    return [dict(r) for r in rows]


def get_authors():
    """Get all authors with counts"""
    conn = get_db()
    rows = conn.execute('''
        SELECT author, COUNT(*) as count
        FROM documents
        WHERE author IS NOT NULL AND author != ''
        GROUP BY author
        ORDER BY count DESC
    ''').fetchall()
    conn.close()
    return [dict(r) for r in rows]


def get_recent_documents(limit=10):
    """Get most recently indexed documents"""
    conn = get_db()
    rows = conn.execute('''
        SELECT id, filename, title, author, pages, category, file_size,
               created_date, indexed_date
        FROM documents
        ORDER BY indexed_date DESC
        LIMIT ?
    ''', (limit,)).fetchall()
    conn.close()
    return [dict(r) for r in rows]


def get_stats():
    """Get overall statistics"""
    conn = get_db()
    row = conn.execute('''
        SELECT
            COUNT(*) as total_documents,
            COALESCE(SUM(pages), 0) as total_pages,
            COALESCE(SUM(file_size), 0) as total_size,
            COUNT(DISTINCT category) as total_categories,
            MAX(indexed_date) as last_indexed
        FROM documents
    ''').fetchone()
    conn.close()
    return dict(row)

def get_all_documents(category=None, page=1, per_page=20):
    """Get all documents with optional category filter and pagination"""
    conn = get_db()
    where_clauses = []
    params = []
    if category:
        where_clauses.append("category = ?")
        params.append(category)
    where_sql = (" WHERE " + " AND ".join(where_clauses)) if where_clauses else ""
    total = conn.execute(f'SELECT COUNT(*) as cnt FROM documents{where_sql}', params).fetchone()['cnt']
    offset = (page - 1) * per_page
    rows = conn.execute(f'''
        SELECT id, filename, title, author, pages, category, file_size,
               created_date, indexed_date,
               SUBSTR(full_text, 1, 150) as preview
        FROM documents{where_sql}
        ORDER BY indexed_date DESC
        LIMIT ? OFFSET ?
    ''', params + [per_page, offset]).fetchall()
    conn.close()
    return {
        'results': [dict(r) for r in rows],
        'total': total,
        'pages': max(1, (total + per_page - 1) // per_page),
        'page': page,
        'per_page': per_page
    }