import sqlite3
import datetime
from typing import List, Dict, Any, Optional
from config import DB_PATH 

def get_connection():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn
    
def init_db():
    """Veritabanı tablolarını oluşturur."""
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS papers (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        paper_id TEXT UNIQUE NOT NULL,
        title TEXT NOT NULL,
        authors TEXT,
        summary TEXT,
        url TEXT,
        source TEXT DEFAULT 'arxiv',
        citation_count INTEGER DEFAULT 0,
        published_year TEXT,
        status TEXT DEFAULT 'unread',
        notes TEXT DEFAULT '',
        tags TEXT DEFAULT '',
        saved_at TEXT
    );
    """)
    conn.commit()
    conn.close()

def save_paper(
    paper_id: str,
    title: str,
    authors: str = "",
    summary: str = "",
    url: str = "",
    source: str = "arxiv",
    citation_count: int = 0,
    published_year: str = "",
    tags: str = "genel"
) -> Dict[str, Any]:
    """Makaleyi veritabanına kaydeder (varsa günceller)."""
    init_db()
    conn = get_connection()
    cursor = conn.cursor()
    now = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    
    try:
        cursor.execute("""
        INSERT INTO papers (paper_id, title, authors, summary, url, source, citation_count, published_year, tags, saved_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ON CONFLICT(paper_id) DO UPDATE SET
            title=excluded.title,
            authors=excluded.authors,
            summary=excluded.summary,
            url=excluded.url,
            citation_count=excluded.citation_count,
            tags=excluded.tags
        """, (paper_id, title, authors, summary, url, source, citation_count, published_year, tags, now))
        conn.commit()
        return {
            "status": "success",
            "message": f"'{title}' başarıyla kütüphaneye kaydedildi.",
            "paper_id": paper_id
        }
    except Exception as e:
        return {
            "status": "error",
            "message": f"Veritabanı kaydı sırasında hata oluştu: {str(e)}"
        }
    finally:
        conn.close()

def get_saved_papers(status_filter: Optional[str] = None, tag_filter: Optional[str] = None, query: Optional[str] = None) -> List[Dict[str, Any]]:
    """Kayıtlı makaleleri getirir."""
    init_db()
    conn = get_connection()
    cursor = conn.cursor()
    
    sql = "SELECT * FROM papers WHERE 1 = 1"
    params = []
    
    if status_filter and str(status_filter).lower().strip() not in ["", "hepsi", "all", "none"]:
        sql += " AND status = ?"
        params.append(str(status_filter).lower().strip())
        
    if tag_filter and str(tag_filter).strip():
        sql += " AND tags LIKE ?"
        params.append(f"%{str(tag_filter).strip()}%")
        
    if query and str(query).strip():
        q_str = str(query).strip()
        sql += " AND (title LIKE ? OR summary LIKE ? OR authors LIKE ?)"
        params.append(f"%{q_str}%")
        params.append(f"%{q_str}%")
        params.append(f"%{q_str}%")
        
    sql += " ORDER BY id DESC"
    
    cursor.execute(sql, params)
    rows = cursor.fetchall()
    conn.close()
    
    results = []
    for r in rows:
        results.append(dict(r))
    return results

def update_paper_status_or_note(paper_id: str, status: Optional[str] = None, notes: Optional[str] = None, tags: Optional[str] = None) -> Dict[str, Any]:
    """Makalenin okuma durumunu, notunu veya etiketini günceller."""
    init_db()
    conn = get_connection()
    cursor = conn.cursor()
    
    paper = None
    target_query = str(paper_id or "").strip()
    
    if target_query:
        cursor.execute("SELECT * FROM papers WHERE paper_id = ? OR id = ?", (target_query, target_query))
        paper = cursor.fetchone()
        
        if not paper:
            cursor.execute("SELECT * FROM papers WHERE title LIKE ? OR paper_id LIKE ?", (f"%{target_query}%", f"%{target_query}%"))
            paper = cursor.fetchone()
            
    if not paper:
        cursor.execute("SELECT * FROM papers ORDER BY id DESC LIMIT 1")
        paper = cursor.fetchone()
        
    if not paper:
        conn.close()
        return {"status": "error", "message": "Kütüphanenizde güncellenecek herhangi bir makale bulunamadı."}
        
    updates = []
    params = []
    
    if status:
        updates.append("status = ?")
        params.append(status.lower())
    if notes is not None:
        updates.append("notes = ?")
        params.append(notes)
    if tags:
        updates.append("tags = ?")
        params.append(tags)
        
    if not updates:
        conn.close()
        return {"status": "warning", "message": "Güncellenecek alan belirtilmedi."}
        
    params.append(paper["id"])
    sql = f"UPDATE papers SET {', '.join(updates)} WHERE id = ?"
    
    cursor.execute(sql, params)
    conn.commit()
    conn.close()
    
    return {
        "status": "success",
        "message": f"Makale ('{paper['title']}') okuma durumu '{status or paper['status']}' olarak güncellendi.",
        "updated_paper_id": paper["paper_id"]
    }

def delete_paper(paper_id: str) -> Dict[str, Any]:
    """Makaleyi veritabanından siler."""
    init_db()
    conn = get_connection()
    cursor = conn.cursor()
    
    paper = None
    target_query = str(paper_id or "").strip()
    
    if target_query:
        cursor.execute("SELECT * FROM papers WHERE paper_id = ? OR id = ?", (target_query, target_query))
        paper = cursor.fetchone()
        
        if not paper:
            cursor.execute("SELECT * FROM papers WHERE title LIKE ? OR paper_id LIKE ?", (f"%{target_query}%", f"%{target_query}%"))
            paper = cursor.fetchone()
            
    if not paper:
        cursor.execute("SELECT * FROM papers ORDER BY id DESC LIMIT 1")
        paper = cursor.fetchone()
        
    if not paper:
        conn.close()
        return {"status": "error", "message": "Kütüphanede silinecek makale bulunamadı."}

    cursor.execute("DELETE FROM papers WHERE paper_id = ?", (paper["paper_id"],))
    deleted_count = cursor.rowcount
    conn.commit()
    conn.close()
    return {"status": "success", "message": f"'{paper['title']}' başlıklı makale veritabanından silindi."}

def clear_library() -> Dict[str, Any]:
    """Kütüphanedeki tüm makaleleri veritabanından siler."""
    init_db()
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute("DELETE FROM papers")
    deleted_count = cursor.rowcount
    conn.commit()
    conn.close()
    return {"status": "success", "message": f"Kütüphane temizlendi. Toplam {deleted_count} makale silindi."}