#!/usr/bin/env python3
# -*- coding: utf-8 -*-

"""
Phase 3: Calibre Auto-Import
Imports .azw files to Calibre library
"""

import os
import sys
import subprocess
import threading
import time


def find_all_azw_files(content_dir, exclude_asins=None):
    """Recursively find all .azw files in content directory"""
    from modules.utils import print_error
    
    azw_files = []
    exclude_asins = exclude_asins or []
    
    try:
        for root, dirs, files in os.walk(content_dir):
            for file in files:
                if file.lower().endswith('.azw'):
                    asin = file.split('_')[0] if '_' in file else file.replace('.azw', '')
                    
                    if asin in exclude_asins:
                        continue
                    
                    full_path = os.path.join(root, file)
                    azw_files.append(full_path)
        
        return azw_files
        
    except Exception as e:
        print_error(f"Error scanning directory: {e}")
        return []


def check_book_exists_in_calibre(library_path, asin):
    """
    Check if a book with the given ASIN already exists in Calibre library
    Returns: (exists: bool, book_id: str or None, title: str or None)
    """
    import sqlite3
    
    if not asin or len(asin) < 5:
        return False, None, None
    
    try:
        db_path = os.path.join(library_path, "metadata.db")
        if not os.path.exists(db_path):
            return False, None, None
        
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        # Search for books where identifiers contain the ASIN
        # Calibre stores identifiers in the identifiers table linked to books
        cursor.execute("""
            SELECT books.id, books.title 
            FROM books 
            JOIN books_identifiers_link ON books.id = books_identifiers_link.book
            JOIN identifiers ON books_identifiers_link.id = identifiers.id
            WHERE identifiers.type = 'asin' AND identifiers.val = ?
        """, (asin,))
        
        result = cursor.fetchone()
        conn.close()
        
        if result:
            return True, str(result[0]), result[1]
        
        return False, None, None
        
    except Exception:
        return False, None, None


def check_all_books_before_import(content_dir, library_path):
    """
    Check all .azw files in content directory against Calibre library
    Returns: dict mapping book_path -> (exists, book_id, title)
    """
    from modules.utils import print_step, print_ok, print_warn
    
    print_step("Checking for existing books in Calibre library...")
    
    azw_files = find_all_azw_files(content_dir)
    
    if not azw_files:
        return {}
    
    existing_books = {}
    found_count = 0
    
    for book_path in azw_files:
        book_name = os.path.basename(book_path)
        # Extract ASIN from filename (e.g., "B00N17VVZC_EBOK.azw" -> "B00N17VVZC")
        asin = book_name.split('_')[0] if '_' in book_name else os.path.splitext(book_name)[0]
        
        exists, book_id, title = check_book_exists_in_calibre(library_path, asin)
        
        if exists:
            existing_books[book_path] = (exists, book_id, title)
            found_count += 1
    
    if found_count > 0:
        print_warn(f"Found {found_count} book(s) already in Calibre library - will skip importing these")
    
    return existing_books


def get_book_title_from_calibre(library_path, book_id):
    """Get book title from Calibre library using book ID"""
    import sqlite3
    
    if not book_id:
        return None
    
    try:
        db_path = os.path.join(library_path, "metadata.db")
        if not os.path.exists(db_path):
            return None
        
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        cursor.execute("SELECT title FROM books WHERE id = ?", (book_id,))
        result = cursor.fetchone()
        conn.close()
        
        if result:
            return result[0]
        return None
        
    except Exception:
        return None


def extract_asin_from_filename(book_name):
    """Extract ASIN from book filename (e.g., 'B00N17VVZC_EBOK.azw' -> 'B00N17VVZC')"""
    # Remove extension first
    name_without_ext = os.path.splitext(book_name)[0]
    # If there's an underscore, take the part before it (ASIN)
    if '_' in name_without_ext:
        return name_without_ext.split('_')[0]
    # Otherwise the whole name might be the ASIN
    return name_without_ext


def import_single_book(book_path, library_path, use_duplicates=False, timeout_seconds=180, raw_log_path=None, idx=None, total=None):
    """Import a single book to Calibre with timeout and RAW logging"""
    from modules.utils import display_progress_timer, append_to_raw_log
    
    cmd = ['calibredb', 'add']
    
    if use_duplicates:
        cmd.append('-d')
    
    cmd.extend([
        '-1',
        book_path,
        f'--library-path={library_path}'
    ])
    
    try:
        process = subprocess.Popen(
            cmd,
            stdout=subprocess.PIPE,
            stderr=subprocess.PIPE,
            text=True,
            encoding='utf-8',
            errors='replace'
        )
        
        timer_stopped = threading.Event()
        
        book_label = os.path.splitext(os.path.basename(book_path))[0]
        counter = f"[{idx:02d}/{total:02d}] " if (idx is not None and total is not None) else ""
        timer_message = f"Importing.. {counter}{book_label}"

        timer_thread = threading.Thread(
            target=display_progress_timer,
            args=(timer_message, timeout_seconds, timer_stopped),
            daemon=True
        )
        timer_thread.start()
        
        try:
            stdout, stderr = process.communicate(timeout=timeout_seconds)
            result_returncode = process.returncode
            timer_stopped.set()
            timer_thread.join(timeout=1)
            print("\r" + " " * 80 + "\r", end='', flush=True)
        except subprocess.TimeoutExpired:
            timer_stopped.set()
            timer_thread.join(timeout=1)
            print("\r" + " " * 80 + "\r", end='', flush=True)
            process.kill()
            stdout, stderr = process.communicate()
            result_returncode = -1
        
        # Log to RAW log if enabled
        if raw_log_path:
            book_name = os.path.basename(book_path)
            append_to_raw_log(
                raw_log_path,
                cmd,
                stdout,
                stderr,
                result_returncode,
                book_info=book_name
            )
        
        if result_returncode == 0 and stdout:
            for line in stdout.split('\n'):
                if 'Added book ids:' in line:
                    ids_str = line.split('Added book ids:')[1].strip()
                    book_id = ids_str.split(',')[0].strip() if ids_str else None
                    
                    # Try to get the book title from Calibre
                    book_title = None
                    if book_id:
                        book_title = get_book_title_from_calibre(library_path, book_id)
                    
                    return True, book_id, None, False, False, book_title, False

        error_msg = stderr if stderr else "Unknown error"
        already_exists = "already exist in the database" in error_msg

        # Detect Calibre database lock errors - these mean Calibre is still open.
        # Common strings returned by calibredb when the GUI holds the DB lock:
        calibre_db_lock_strings = [
            "database is locked",
            "unable to connect to running calibre instance",
            "calibre is already running",
            "lock wait timeout",
        ]
        calibre_open = any(s in error_msg.lower() for s in calibre_db_lock_strings)

        return False, None, error_msg, False, already_exists, None, calibre_open

    except subprocess.TimeoutExpired:
        # Log timeout to RAW log if enabled
        if raw_log_path:
            book_name = os.path.basename(book_path)
            append_to_raw_log(
                raw_log_path,
                cmd,
                '',
                '',
                -1,  # Timeout exit code
                book_info=book_name
            )
        return False, None, f"Timeout after {timeout_seconds} seconds", True, False, None, False
    except Exception as e:
        return False, None, str(e), False, False, None, False


def import_all_azw_files(content_dir, library_path, use_duplicates=False, exclude_asins=None, working_dir=None, books_to_import=None, config=None, raw_log_path=None):
    """Import all *.azw files one at a time with RAW logging
    
    Args:
        content_dir: Kindle content directory
        library_path: Calibre library path
        use_duplicates: Whether to allow duplicates
        exclude_asins: List of ASINs to exclude
        working_dir: Working directory
        books_to_import: Pre-filtered list of book paths to import
        config: Configuration dict (optional)
        raw_log_path: Path to RAW debug log file (optional)
    
    Returns:
        dict with import results
    """
    from modules.utils import print_step, print_ok, print_warn, print_error, append_to_failed_books
    
    print_step("Importing ebooks to Calibre...")
    print("--------------------------------------------------")
    print()
    
    # Use provided books_to_import list OR scan for files
    if books_to_import is not None:
        azw_files = books_to_import
    else:
        print_step("Scanning for .azw files...")
        azw_files = find_all_azw_files(content_dir, exclude_asins=exclude_asins)
    
    if not azw_files:
        print_warn("No .azw files found")
        print()
        return {
            'total': 0,
            'success': 0,
            'failed': 0,
            'book_ids': [],
            'failed_books': [],
            'skipped_books': []
        }
    
    print_ok(f"Found {len(azw_files)} .azw file(s) to import")
    print()
    
    results = {
        'total': len(azw_files),
        'success': 0,
        'failed': 0,
        'skipped': 0,
        'book_ids': [],
        'failed_books': [],
        'skipped_books': []
    }
    
    print_step(f"Importing books (timeout: 3 minutes per book)...")
    print()
    
    total = len(azw_files)

    for idx, book_path in enumerate(azw_files, 1):
        book_name = os.path.basename(book_path)
        asin = extract_asin_from_filename(book_name)

        success, book_id, error_msg, timed_out, already_exists, book_title, calibre_open = import_single_book(
            book_path,
            library_path,
            use_duplicates,
            180,
            raw_log_path=raw_log_path,
            idx=idx,
            total=total
        )

        counter = f"[{idx:02d}/{total:02d}]"

        # ----------------------------------------------------------------
        # DB-lock early abort: calibredb reported the library is locked.
        # Calibre must still be open. Stop the loop immediately and give
        # the user the same W/K/C/Q prompt so they can close it and
        # re-run, or force-continue knowing remaining books will fail.
        # ----------------------------------------------------------------
        if calibre_open:
            from modules.utils import print_colored, wait_for_calibre_to_close
            print()
            print_colored("=" * 70, "red")
            print_colored("[ERROR] CALIBRE DATABASE IS LOCKED - Import aborted!", "red")
            print_colored("=" * 70, "red")
            print()
            print(f"  calibredb reported a database lock on book [{asin}].")
            print("  This means Calibre is still open and holding the library database.")
            print()
            remaining = total - idx + 1
            print(f"  Imported so far : {results['success']} book(s)")
            print(f"  Remaining       : {remaining} book(s) (not imported)")
            print()
            print("  Close Calibre and re-run the script to import the remaining books.")
            print()
            # Record this book as failed too
            results['failed'] += 1
            results['failed_books'].append((book_name, "Calibre database locked"))
            if working_dir:
                append_to_failed_books(working_dir, asin, asin)
            # Break out of the import loop - no point continuing
            break

        if success and book_id:
            if book_title:
                print_ok(f"  {counter} Imported: {book_title} (ID: {book_id})")
            else:
                print_ok(f"  {counter} Imported (ID: {book_id}) [{asin}]")

            results['success'] += 1
            results['book_ids'].append(book_id)
        elif timed_out:
            print_error(f"  {counter} TIMEOUT [{asin}]")
            results['failed'] += 1
            failed_title = book_title if book_title else asin
            results['failed_books'].append((book_name, "Timeout"))
            if working_dir:
                append_to_failed_books(working_dir, failed_title, asin)
        elif already_exists:
            detected_title = book_title
            if not detected_title:
                exists, book_id_lookup, title_lookup = check_book_exists_in_calibre(library_path, asin)
                detected_title = title_lookup if title_lookup else asin
            print(f"  {counter} [SKIPPED] {detected_title} - already in Calibre library")
            results['skipped'] += 1
            results['skipped_books'].append((asin, detected_title))
        else:
            print_error(f"  {counter} FAILED [{asin}]")
            results['failed'] += 1
            failed_title = book_title if book_title else asin
            results['failed_books'].append((book_name, error_msg))
            if working_dir:
                append_to_failed_books(working_dir, failed_title, asin)

    print()

    return results


def cleanup_kfx_zip_books(library_path):
    """Query and optionally remove KFX-ZIP format books"""
    import sqlite3
    from modules.utils import print_step, print_ok, print_error, print_warn
    
    db_path = os.path.join(library_path, "metadata.db")
    kfx_zip_books = []
    
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        cursor.execute("SELECT book, name FROM data WHERE format = 'KFX-ZIP'")
        rows = cursor.fetchall()
        
        for row in rows:
            book_id = str(row[0])
            book_name = row[1]
            kfx_zip_books.append((book_id, book_name))
        
        conn.close()
        
    except Exception as e:
        print_error(f"Failed to query database: {e}")
        return 0, True
    
    if not kfx_zip_books:
        return 0, False
    
    print()
    print_warn(f"Found {len(kfx_zip_books)} book(s) with KFX-ZIP format in database")
    print()
    
    print("Options:")
    print("  [C] Continue - Remove these KFX-ZIP books and import (recommended)")
    print("  [S] Skip - Keep existing books")
    print()
    
    while True:
        choice = input("Your choice (C/S) [C]: ").strip().upper()
        if choice == '':
            choice = 'C'
        
        if choice == 'C':
            print()
            book_ids = [book_id for book_id, _ in kfx_zip_books]
            id_list = ','.join(book_ids)
            
            print_step(f"Removing {len(book_ids)} KFX-ZIP book(s)...")
            
            cmd = ['calibredb', 'remove', id_list, '--permanent', f'--library-path={library_path}']
            
            try:
                result = subprocess.run(cmd, capture_output=True, text=True, timeout=60)
                
                if result.returncode == 0:
                    print_ok(f"Successfully removed {len(book_ids)} book(s)")
                    print()
                    return len(book_ids), False
                else:
                    print_error(f"Failed to remove books: {result.stderr}")
                    print()
                    return 0, True
                    
            except Exception as e:
                print_error(f"Failed to remove books: {e}")
                print()
                return 0, True
                
        elif choice == 'S':
            print()
            print_warn("Skipping KFX-ZIP cleanup")
            print()
            return 0, True
        else:
            print_error("Invalid choice. Please enter C or S.")


def run_phase_3(content_dir, calibre_path, working_dir, extraction_stats, config=None):
    """
    Run Phase 3: Calibre Auto-Import
    
    Args:
        content_dir: Kindle content directory path
        calibre_path: Calibre library path
        working_dir: Working directory
        extraction_stats: Statistics from Phase 1
        config: Configuration dict (optional)
    
    Returns:
        dict with keys:
            - success: bool
            - imported_count: int
            - book_ids: list
            - results: dict with import results
    """
    from modules.utils import print_step, print_ok, print_warn, print_error, is_calibre_running
    
    # Get kfx_zip_mode from config if available
    kfx_zip_mode = 'delete_kfx_zip'  # Default
    if config and isinstance(config, dict):
        calibre_import_cfg = config.get('calibre_import', {})
        if isinstance(calibre_import_cfg, dict):
            kfx_zip_mode = calibre_import_cfg.get('kfx_zip_mode', 'delete_kfx_zip')
    
    # Safety-net: Calibre running check is handled in key_finder.py before Phase 3
    # is ever called. If Calibre is somehow still running here, bail out immediately
    # rather than waiting - this prevents any risk of database corruption.
    if is_calibre_running():
        print_warn("Calibre is still running - Phase 3 import aborted to protect database.")
        print_warn("Close Calibre and re-run the script to import your books.")
        print()
        return {
            'success': False,
            'imported_count': 0,
            'book_ids': [],
            'results': {
                'total': 0,
                'success': 0,
                'failed': 0,
                'skipped': 0,
                'book_ids': [],
                'failed_books': [],
                'skipped_books': []
            }
        }
    
    # Cleanup KFX-ZIP books based on config setting
    print_step("[PHASE 3a] Checking for existing KFX-ZIP books...")
    if kfx_zip_mode == 'delete_kfx_zip':
        # Automatically remove KFX-ZIP books without prompting
        cleanup_kfx_zip_books_auto(calibre_path, print)
    elif kfx_zip_mode == 'keep':
        print("Keeping KFX-ZIP files as configured")
    else:
        # Default: cleanup
        cleanup_kfx_zip_books_auto(calibre_path, print)
    
    # NEW: Check for existing books in Calibre BEFORE importing
    # This prevents duplicates by skipping books that are already in the library
    existing_books = check_all_books_before_import(content_dir, calibre_path)
    
    # Build list of book paths to skip (already in Calibre)
    skip_book_paths = list(existing_books.keys())
    
    # Show existing books that will be skipped
    if skip_book_paths:
        print()
        print_warn("The following books are already in Calibre and will be skipped:")
        for book_path in skip_book_paths:
            exists, book_id, title = existing_books[book_path]
            print(f"  - {title} (ID: {book_id})")
        print()
    
    # Import books (pass existing book paths to skip)
    exclude_asins = []
    if extraction_stats.get('failed_books'):
        exclude_asins.extend([asin for asin, _, _ in extraction_stats['failed_books']])
    if extraction_stats.get('skipped_books'):
        exclude_asins.extend([asin for asin, _, _ in extraction_stats['skipped_books']])
    
    # Filter out books that already exist in Calibre
    azw_files = find_all_azw_files(content_dir, exclude_asins=exclude_asins)
    books_to_import = [f for f in azw_files if f not in skip_book_paths]
    
    if not books_to_import:
        print_warn("No new books to import - all books are already in Calibre")
        print()
        return {
            'success': True,
            'imported_count': 0,
            'book_ids': [],
            'results': {
                'total': len(azw_files),
                'success': 0,
                'failed': 0,
                'skipped': len(skip_book_paths),
                'book_ids': [],
                'failed_books': [],
                'skipped_books': [(os.path.basename(p), existing_books[p][2]) for p in skip_book_paths]
            }
        }
    
    # Initialize RAW log path if enabled
    from modules.utils import get_raw_log_path, cleanup_old_raw_logs, write_raw_log_header

    enable_raw_logs = config.get('enable_raw_logs', False) if config else False
    raw_log_path = None
    if enable_raw_logs:
        raw_log_path = get_raw_log_path(working_dir, 'import')
        write_raw_log_header(raw_log_path)   # reads Kindle version + mode from config
        import_logs_dir = os.path.join(working_dir, "Logs", "02_Import")
        cleanup_old_raw_logs(import_logs_dir, keep_count=10)
    
    # Import only the books that aren't already in Calibre
    import_results = import_all_azw_files(
        content_dir, 
        calibre_path, 
        use_duplicates=False,  # Don't use duplicates since we pre-check
        exclude_asins=exclude_asins,
        working_dir=working_dir,
        books_to_import=books_to_import,  # Pass pre-filtered list
        config=config,
        raw_log_path=raw_log_path  # Pass RAW log path
    )
    
    imported_count = import_results['success']
    book_ids = import_results['book_ids']
    
    # Display import results
    print("--------------------------------------------------")
    print_step("Import Results:")
    print()
    
    if import_results['success'] > 0:
        print_ok(f"Successfully imported: {import_results['success']} ebook(s)")
        print(f"      Book IDs: {', '.join(book_ids)}")
    else:
        print_warn("No books were imported")
    
    # Show books that were already in Calibre (pre-check skipped)
    if skip_book_paths:
        print_warn(f"Skipped: {len(skip_book_paths)} book(s) already in Calibre library")
    
    if import_results.get('skipped', 0) > 0:
        print_warn(f"Skipped: {import_results['skipped']} book(s) already exist in database")
    
    if import_results.get('failed', 0) > 0:
        print_error(f"Failed: {import_results['failed']} book(s)")
    
    print()
    
    return {
        'success': imported_count > 0 or len(skip_book_paths) > 0,
        'imported_count': imported_count,
        'book_ids': book_ids,
        'results': import_results
    }


def cleanup_kfx_zip_books_auto(library_path, log_func=None):
    """Automatically remove KFX-ZIP format books without prompting"""
    import sqlite3
    from modules.utils import print_step, print_ok, print_error
    
    db_path = os.path.join(library_path, "metadata.db")
    kfx_zip_books = []
    
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        cursor.execute("SELECT book, name FROM data WHERE format = 'KFX-ZIP'")
        rows = cursor.fetchall()
        
        for row in rows:
            book_id = str(row[0])
            book_name = row[1]
            kfx_zip_books.append((book_id, book_name))
        
        conn.close()
        
    except Exception as e:
        if log_func:
            log_func(f"Failed to query database: {e}")
        else:
            print_error(f"Failed to query database: {e}")
        return
    
    if not kfx_zip_books:
        if log_func:
            log_func("No KFX-ZIP books found in database")
        return
    
    if log_func:
        log_func(f"Found {len(kfx_zip_books)} book(s) with KFX-ZIP format - removing automatically")
    
    book_ids = [book_id for book_id, _ in kfx_zip_books]
    id_list = ','.join(book_ids)
    
    cmd = ['calibredb', 'remove', id_list, '--permanent', f'--library-path={library_path}']
    
    try:
        result = subprocess.run(cmd, capture_output=True, text=True, timeout=60)
        
        if result.returncode == 0:
            if log_func:
                log_func(f"Successfully removed {len(book_ids)} KFX-ZIP book(s)")
            else:
                print_ok(f"Successfully removed {len(book_ids)} KFX-ZIP book(s)")
        else:
            error_msg = result.stderr if result.stderr else "Unknown error"
            if log_func:
                log_func(f"Failed to remove books: {error_msg}")
            else:
                print_error(f"Failed to remove books: {error_msg}")
                
    except Exception as e:
        if log_func:
            log_func(f"Failed to remove books: {e}")
        else:
            print_error(f"Failed to remove books: {e}")
