import os
import sqlite3
from pathlib import Path
from config import DB_PATH, FILES_DIR  # Importer depuis config

def init_db():
    conn = sqlite3.connect(DB_PATH)
    cur = conn.cursor()

    # Table principale
    cur.execute("""
        CREATE TABLE IF NOT EXISTS documents (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            path TEXT NOT NULL UNIQUE,
            extension TEXT,
            added_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)

    # Table des métadonnées
    cur.execute("""
        CREATE TABLE IF NOT EXISTS document_metadata (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            document_path TEXT UNIQUE NOT NULL,
            author TEXT,
            tags TEXT,
            comment TEXT,
            version TEXT,
            updated_at TEXT,
            FOREIGN KEY(document_path) REFERENCES documents(path) ON DELETE CASCADE
        )
    """)

    conn.commit()
    return conn, cur

def insert_document(name, path, ext, cur):
    cur.execute("INSERT OR IGNORE INTO documents (name, path, extension) VALUES (?, ?, ?)", (name, path, ext))

def scan_and_insert_files(folder_path):
    conn, cur = init_db()
    count = 0
    for root, _, files in os.walk(folder_path):
        for filename in files:
            full_path = os.path.join(root, filename)
            rel_path = os.path.relpath(full_path, folder_path)
            ext = os.path.splitext(filename)[1].lower()
            insert_document(filename, rel_path, ext, cur)
            count += 1
    conn.commit()
    conn.close()
    print(f"[OK] {count} fichiers indexés.")

def index_files_with_progress(base_path, cur, callback=None):
    indexed = []

    # 1. Lister tous les fichiers à traiter
    all_files = []
    for root, _, files in os.walk(base_path, followlinks=True):
        for file in files:
            full_path = Path(root) / file
            all_files.append(full_path)

    total = len(all_files)

    # 2. Indexer chaque fichier avec le callback de progression
    for i, full_path in enumerate(all_files, start=1):
        rel_path = full_path.relative_to(base_path)
        ext = full_path.suffix.lower()
        name = full_path.name

        cur.execute("""
            INSERT OR IGNORE INTO documents (name, path, extension)
            VALUES (?, ?, ?)
        """, (name, str(rel_path), ext))

        indexed.append(str(rel_path))

        if callback:
            callback(i, total)

    return indexed


def index_files(base_path, cur):
    indexed = []
    for root, _, files in os.walk(base_path, followlinks=True):
        for file in files:
            full_path = Path(root) / file
            rel_path = full_path.relative_to(base_path)
            ext = full_path.suffix.lower()
            cur.execute("""
                INSERT OR IGNORE INTO documents (name, path, extension)
                VALUES (?, ?, ?)
            """, (file, str(rel_path), ext))
            indexed.append(str(rel_path))
    return indexed

def clean_removed(indexed, cur):
    cur.execute("SELECT path FROM documents")
    known_paths = {row[0] for row in cur.fetchall()}
    indexed_set = set(indexed)
    removed = known_paths - indexed_set

    for path in removed:
        cur.execute("DELETE FROM documents WHERE path = ?", (path,))

    # Nettoyage des métadonnées orphelines
    cur.execute("DELETE FROM document_metadata WHERE document_path NOT IN (SELECT path FROM documents)")

    return removed

def reindex_files_with_progress(callback=None):
    # ouverture de la base et indexation
    conn, cur = init_db()
    indexed = index_files_with_progress(FILES_DIR, cur, callback)
    clean_removed(indexed, cur)

    # purge des symlinks dont la cible n'existe plus
    from pathlib import Path
    import os
    for p in Path(FILES_DIR).rglob("*"):
        if p.is_symlink() and not p.resolve().exists():
            rel = p.relative_to(FILES_DIR).as_posix()
            p.unlink()
            cur.execute("DELETE FROM documents WHERE path = ?", (rel,))
            cur.execute("DELETE FROM document_metadata WHERE document_path = ?", (rel,))

    # on enregistre et on ferme la connexion
    conn.commit()
    conn.close()
    return len(indexed)
