import_tool.py

#!/usr/bin/env python3
"""
MushLog Database Import Tool
Standalone tool for importing Excel data into the MushLog database.
Supports both replace and merge modes with graceful conflict handling.
"""

import sys
import os
import sqlite3
import pandas as pd
import argparse
from datetime import datetime
from PySide6.QtWidgets import (
    QApplication, QWidget, QVBoxLayout, QHBoxLayout, QPushButton, 
    QLabel, QFileDialog, QTextEdit, QComboBox, QProgressBar,
    QMessageBox, QGroupBox, QRadioButton, QButtonGroup, QCheckBox
)
from PySide6.QtCore import Qt, QThread, Signal as pyqtSignal
from PySide6.QtGui import QFont, QIcon

from apps.catalog_data import DB_FILE, init_db
from apps.utils import app_root, ensure_dir, resource_path, logger

class ImportWorker(QThread):
    """Background worker for import operations"""
    progress = pyqtSignal(int)
    status = pyqtSignal(str)
    finished = pyqtSignal(bool, str)
    
    def __init__(self, excel_file, mode, conflict_strategy):
        super().__init__()
        self.excel_file = excel_file
        self.mode = mode
        self.conflict_strategy = conflict_strategy
        
    def _read_data_file(self):
        """Read Excel or CSV file based on file extension"""
        file_ext = os.path.splitext(self.excel_file)[1].lower()
        
        if file_ext in ['.xlsx', '.xls']:
            # Excel file
            return pd.read_excel(self.excel_file)
        elif file_ext == '.csv':
            # CSV file
            return pd.read_csv(self.excel_file)
        else:
            raise ValueError(f"Unsupported file format: {file_ext}. Please use .xlsx, .xls, or .csv files.")
        
    def run(self):
        try:
            if self.mode == "replace":
                imported_count = self._replace_mode()
            else:
                imported_count = self._merge_mode()
            self.finished.emit(True, f"Import completed successfully! {imported_count} records imported.")
        except Exception as e:
            self.finished.emit(False, f"Import failed: {str(e)}")
    
    def _replace_mode(self):
        """Replace entire database with Excel or CSV data"""
        self.status.emit("Reading data file...")
        df = self._read_data_file()
        self.progress.emit(20)
        
        self.status.emit("Validating data...")
        self._validate_data(df)
        self.progress.emit(40)
        
        self.status.emit("Backing up existing database...")
        self._backup_database()
        self.progress.emit(60)
        
        self.status.emit("Replacing database...")
        imported_count = self._import_data(df, replace=True)
        self.progress.emit(100)
        return imported_count
    
    def _merge_mode(self):
        """Merge Excel or CSV data with existing database"""
        self.status.emit("Reading data file...")
        df = self._read_data_file()
        self.progress.emit(20)
        
        self.status.emit("Validating data...")
        self._validate_data(df)
        self.progress.emit(40)
        
        self.status.emit("Checking for conflicts...")
        conflicts = self._check_conflicts(df)
        self.progress.emit(60)
        
        if not conflicts.empty and self.conflict_strategy == "ask":
            # This would need to be handled in the main thread
            pass
        
        self.status.emit("Merging data...")
        imported_count = self._import_data(df, replace=False, conflicts=conflicts)
        self.progress.emit(100)
        return imported_count
    
    def _validate_data(self, df):
        """Validate Excel data structure"""
        required_columns = ['id', 'date', 'state', 'county', 'city', 'site_name', 
                          'species', 'field_photo', 'nearby_trees', 'substrate', 
                          'habit', 'odor', 'taste', 'notes', 'inat_id', 'collected_by']
        
        missing_columns = [col for col in required_columns if col not in df.columns]
        if missing_columns:
            raise ValueError(f"Missing required columns: {missing_columns}")
        
        # Check for empty required fields
        for col in ['species', 'date', 'state', 'county', 'city']:
            if df[col].isnull().any():
                raise ValueError(f"Column '{col}' contains empty values")
    
    def _backup_database(self):
        """Create backup of existing database"""
        if os.path.exists(DB_FILE):
            timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
            backup_file = os.path.join(os.path.dirname(DB_FILE), f"backup_{timestamp}.db")
            import shutil
            shutil.copy2(DB_FILE, backup_file)
    
    def _check_conflicts(self, df):
        """Check for ID conflicts in merge mode"""
        if not os.path.exists(DB_FILE):
            return []
        
        conn = sqlite3.connect(DB_FILE)
        existing_ids = pd.read_sql_query("SELECT id FROM specimens", conn)
        conn.close()
        
        conflicts = df[df['id'].isin(existing_ids['id'])]
        return conflicts
    
    def _import_data(self, df, replace=False, conflicts=None):
        """Import data into database"""
        if replace:
            # Clear existing database
            conn = sqlite3.connect(DB_FILE)
            conn.execute("DELETE FROM specimens")
            conn.commit()
            conn.close()
        
        # Prepare data for insertion
        conn = sqlite3.connect(DB_FILE)
        imported_count = 0
        
        for index, row in df.iterrows():
            # Handle conflicts based on strategy
            if not replace and conflicts is not None and row['id'] in conflicts['id'].values:
                if self.conflict_strategy == "skip":
                    continue
                elif self.conflict_strategy == "overwrite":
                    # Delete existing record
                    conn.execute("DELETE FROM specimens WHERE id = ?", (row['id'],))
            
            # Insert or update record
            conn.execute("""
                INSERT OR REPLACE INTO specimens (
                    id, date, state, county, city, site_name,
                    species, field_photo, nearby_trees, substrate,
                    habit, odor, taste, notes, inat_id, collected_by
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """, (
                row['id'], row['date'], row['state'], row['county'], row['city'],
                row['site_name'], row['species'], row['field_photo'], row['nearby_trees'],
                row['substrate'], row['habit'], row['odor'], row['taste'],
                row['notes'], row['inat_id'], row['collected_by']
            ))
            imported_count += 1
        
        conn.commit()
        conn.close()
        return imported_count

class ImportTool(QWidget):
    def __init__(self):
        super().__init__()
        self.setWindowIcon(QIcon(resource_path("blank.ico")))
        self.excel_file = None
        self.worker = None
        self.init_ui()
        
    def init_ui(self):
        self.setWindowTitle("")
        self.setMinimumSize(600, 500)
        
        pass
        
        # Window icon already set in __init__()
        
        # Apply styling based on platform
        import platform
        if platform.system() == "Darwin":
            # macOS - use larger fonts
            self.setStyleSheet("""
            QWidget {
                background-color: #f4f6f9;
                font-family: "Verdana", "Helvetica Neue", "Arial";
                font-size: 13pt;
            }
            QGroupBox {
                font-weight: bold;
                border: 1px solid #333333;
                border-radius: 4px;
                margin-top: 10px;
                padding-top: 10px;
                background-color: #ffffff;
                font-size: 13pt;
            }
            QGroupBox::title {
                subcontrol-origin: margin;
                left: 10px;
                padding: 0 8px 0 8px;
                color: #000000;
                font-size: 13pt;
            }
            QPushButton {
                padding: 8px 16px;
                background-color: #3c8dbc;
                color: white;
                border: none;
                border-radius: 4px;
                font-weight: bold;
                font-size: 12pt;
                min-width: 100px;
                min-height: 28px;
                max-height: 30px;
            }
            QPushButton:hover {
                background-color: #367fa9;
            }
            QPushButton:disabled {
                background-color: #cccccc;
                color: #666666;
            }
            QRadioButton {
                spacing: 8px;
                font-weight: normal;
                font-size: 12pt;
            }
            QRadioButton::indicator {
                width: 16px;
                height: 16px;
            }
            QRadioButton::indicator:unchecked {
                border: 1px solid #333333;
                border-radius: 8px;
                background-color: white;
            }
            QRadioButton::indicator:checked {
                border: 1px solid #333333;
                border-radius: 8px;
                background-color: #3c8dbc;
            }
            QProgressBar {
                border: 1px solid #333333;
                border-radius: 4px;
                text-align: center;
                font-weight: bold;
                font-size: 12pt;
                min-height: 20px;
            }
            QProgressBar::chunk {
                background-color: #28a745;
                border-radius: 3px;
            }
            QTextEdit {
                border: 1px solid #333333;
                border-radius: 4px;
                background-color: #ffffff;
                padding: 8px;
                font-family: 'Courier New', 'Monaco';
                font-size: 11pt;
            }
            QLabel {
                color: #333333;
                font-size: 12pt;
            }
            QLabel#titleLabel {
                font-size: 18pt;
                font-weight: bold;
                color: #333333;
                padding: 4px 0;
            }
            """)
        else:
            # Windows/Linux - use default sizes
            self.setStyleSheet("""
            QWidget {
                background-color: #f4f6f9;
                font-family: Arial;
                font-size: 9pt;
            }
            QGroupBox {
                font-weight: bold;
                border: 1px solid #333333;
                border-radius: 4px;
                margin-top: 10px;
                padding-top: 10px;
                background-color: #ffffff;
            }
            QGroupBox::title {
                subcontrol-origin: margin;
                left: 10px;
                padding: 0 8px 0 8px;
                color: #000000;
                font-size: 10pt;
            }
            QPushButton {
                padding: 6px 12px;
                background-color: #3c8dbc;
                color: white;
                border: none;
                border-radius: 4px;
                font-weight: bold;
                font-size: 9pt;
                min-width: 100px;
                min-height: 28px;
                max-height: 30px;
            }
            QPushButton:hover {
                background-color: #367fa9;
            }
            QPushButton:disabled {
                background-color: #cccccc;
                color: #666666;
            }
            QRadioButton {
                spacing: 6px;
                font-weight: normal;
                font-size: 9pt;
            }
            QRadioButton::indicator {
                width: 12px;
                height: 12px;
            }
            QRadioButton::indicator:unchecked {
                border: 1px solid #333333;
                border-radius: 6px;
                background-color: white;
            }
            QRadioButton::indicator:checked {
                border: 1px solid #333333;
                border-radius: 6px;
                background-color: #3c8dbc;
            }
            QProgressBar {
                border: 1px solid #333333;
                border-radius: 4px;
                text-align: center;
                font-weight: bold;
                font-size: 9pt;
            }
            QProgressBar::chunk {
                background-color: #28a745;
                border-radius: 3px;
            }
            QTextEdit {
                border: 1px solid #333333;
                border-radius: 4px;
                background-color: #ffffff;
                padding: 6px;
                font-family: 'Courier New', monospace;
                font-size: 8pt;
            }
            QLabel {
                color: #333333;
                font-size: 9pt;
            }
            QLabel#titleLabel {
                font-size: 16pt;
                font-weight: bold;
                color: #333333;
                padding: 4px 0;
            }
            """)
        
        layout = QVBoxLayout(self)
        layout.setSpacing(15)
        layout.setContentsMargins(20, 20, 20, 20)
        
        # Header with visible icon and bold title (matches specimen_browser.py)
        header_layout = QHBoxLayout()
        icon_label = QLabel()
        
        # Use platform-appropriate icon
        import platform
        if platform.system() == "Darwin":
            # macOS - prefer .icns, fallback to .png
            icon_path = resource_path("import_tool.icns")
            if not os.path.exists(icon_path):
                icon_path = resource_path("import_tool.png")
        else:
            # Windows/Linux - prefer .ico, fallback to .png
            icon_path = resource_path("import_tool.ico")
            if not os.path.exists(icon_path):
                icon_path = resource_path("import_tool.png")
        
        if os.path.exists(icon_path):
            icon_label.setPixmap(QIcon(icon_path).pixmap(48, 48))
        
        title_label = QLabel("MushLog Database Import Tool")
        title_label.setObjectName("titleLabel")
        header_layout.addWidget(icon_label)
        header_layout.addWidget(title_label)
        header_layout.addStretch()
        layout.addLayout(header_layout)
        
        # File selection
        file_group = QGroupBox("Excel File Selection")
        file_layout = QVBoxLayout(file_group)
        
        file_btn_layout = QHBoxLayout()
        self.file_label = QLabel("No file selected")
        self.file_label.setStyleSheet("color: #666666; font-style: italic; padding: 8px; background-color: #f8f9fa; border: 1px solid #dee2e6; border-radius: 4px;")
        self.browse_btn = QPushButton("📁 Browse for File (Excel or csv)")
        self.browse_btn.clicked.connect(self.browse_file)
        self.browse_btn.setStyleSheet("""
            QPushButton {
                padding: 8px 16px;
                background-color: #004080;
                color: white;
                border: none;
                border-radius: 4px;
                font-weight: bold;
                font-size: 12pt;
                min-width: 120px;
                min-height: 28px;
                max-height: 30px;
            }
            QPushButton:hover {
                background-color: #0059b3;
            }
            QPushButton:disabled {
                background-color: #b3d1f2;
                color: #666666;
            }
        """)
        file_btn_layout.addWidget(self.file_label, 1)
        file_btn_layout.addWidget(self.browse_btn)
        file_layout.addLayout(file_btn_layout)
        
        layout.addWidget(file_group)
        
        # Import mode selection
        mode_group = QGroupBox("Import Mode")
        mode_layout = QVBoxLayout(mode_group)
        
        self.replace_radio = QRadioButton("Replace Database")
        self.replace_radio.setChecked(True)
        self.replace_radio.toggled.connect(self.on_mode_changed)
        self.merge_radio = QRadioButton("Merge Data")
        self.merge_radio.toggled.connect(self.on_mode_changed)
        
        mode_layout.addWidget(self.replace_radio)
        mode_layout.addSpacing(8)  # Add spacing between radio buttons
        mode_layout.addWidget(self.merge_radio)
        
        # Conflict resolution (for merge mode)
        self.conflict_group = QGroupBox("Conflict Resolution")
        conflict_layout = QVBoxLayout(self.conflict_group)
        
        self.skip_radio = QRadioButton("Skip conflicting records")
        self.skip_radio.setChecked(True)
        self.overwrite_radio = QRadioButton("Overwrite conflicting records")
        
        conflict_layout.addWidget(self.skip_radio)
        conflict_layout.addSpacing(8)  # Add spacing between radio buttons
        conflict_layout.addWidget(self.overwrite_radio)
        
        layout.addWidget(mode_group)
        layout.addWidget(self.conflict_group)
        
        # Progress and status
        self.progress_bar = QProgressBar()
        self.status_label = QLabel("Ready to import")
        self.status_label.setStyleSheet("color: #666666; font-weight: bold; padding: 8px; background-color: #e8f4fd; border: 1px solid #bee5eb; border-radius: 4px;")
        
        layout.addWidget(self.progress_bar)
        layout.addWidget(self.status_label)
        
        # Import button
        self.import_btn = QPushButton("🚀 Start Import")
        self.import_btn.clicked.connect(self.start_import)
        self.import_btn.setEnabled(False)
        self.import_btn.setStyleSheet("""
            QPushButton {
                padding: 8px 16px;
                background-color: #004080;
                color: white;
                border: none;
                border-radius: 4px;
                font-weight: bold;
                font-size: 12pt;
                min-width: 120px;
                min-height: 28px;
                max-height: 30px;
            }
            QPushButton:hover {
                background-color: #0059b3;
            }
            QPushButton:disabled {
                background-color: #b3d1f2;
                color: #666666;
            }
        """)
        layout.addWidget(self.import_btn, alignment=Qt.AlignCenter)
        
        # Log area
        log_group = QGroupBox("Import Log")
        log_layout = QVBoxLayout(log_group)
        self.log_text = QTextEdit()
        self.log_text.setMaximumHeight(150)
        log_layout.addWidget(self.log_text)
        layout.addWidget(log_group)
        
        # Initialize UI state
        self.on_mode_changed()
        
        # Ensure title is blank (double-check)
        self.setWindowTitle("")
        self.setWindowTitle(" ")  # Try single space
        # print(f"Final window title: '{self.windowTitle()}'")
        
    def browse_file(self):
        file_path, _ = QFileDialog.getOpenFileName(
            self, "Select Data File", "", "Data Files (*.xlsx *.xls *.csv);;Excel Files (*.xlsx *.xls);;CSV Files (*.csv)"
        )
        if file_path:
            self.excel_file = file_path
            self.file_label.setText(os.path.basename(file_path))
            self.import_btn.setEnabled(True)
            self.log_message(f"Selected file: {file_path}")
    
    def on_mode_changed(self):
        # Show/hide conflict resolution based on mode
        self.conflict_group.setVisible(self.merge_radio.isChecked())
    
    def start_import(self):
        if not self.excel_file:
            return
        
        # Show confirmation dialog
        mode = "replace" if self.replace_radio.isChecked() else "merge"
        conflict_strategy = "skip" if self.skip_radio.isChecked() else "overwrite"
        
        if mode == "replace":
            msg = QMessageBox(self)
            msg.setIcon(QMessageBox.Warning)
            msg.setWindowTitle("Confirm Database Replacement")
            msg.setText("⚠️  DANGER: Database Replacement")
            msg.setInformativeText(
                "This operation will:\n"
                "• DELETE ALL existing specimens in the database\n"
                "• Replace everything with data from the selected file\n"
                "• Create a backup of the current database\n\n"
                "Are you absolutely sure you want to continue?"
            )
            msg.setStandardButtons(QMessageBox.Cancel | QMessageBox.Yes)
            msg.setDefaultButton(QMessageBox.Cancel)
            
            # Set dialog icon
            import platform
            if platform.system() == "Darwin":
                icon_path = resource_path("import_tool.icns")
                if not os.path.exists(icon_path):
                    icon_path = resource_path("import_tool.png")
            else:
                icon_path = resource_path("import_tool.ico")
                if not os.path.exists(icon_path):
                    icon_path = resource_path("import_tool.png")
            if os.path.exists(icon_path):
                msg.setWindowIcon(QIcon(icon_path))
            
            if msg.exec() != QMessageBox.Yes:
                return
        else:
            msg = QMessageBox(self)
            msg.setIcon(QMessageBox.Question)
            msg.setWindowTitle("Confirm Database Merge")
            msg.setText("Database Merge")
            msg.setInformativeText(
                "This operation will:\n"
                "• Keep all existing specimens\n"
                "• Add new specimens from the selected file\n"
                "• Update existing specimens with matching IDs\n\n"
                f"Conflict strategy: {conflict_strategy}\n\n"
                "Continue with merge?"
            )
            msg.setStandardButtons(QMessageBox.Cancel | QMessageBox.Yes)
            msg.setDefaultButton(QMessageBox.Cancel)
            
            # Set dialog icon
            import platform
            if platform.system() == "Darwin":
                icon_path = resource_path("import_tool.icns")
                if not os.path.exists(icon_path):
                    icon_path = resource_path("import_tool.png")
            else:
                icon_path = resource_path("import_tool.ico")
                if not os.path.exists(icon_path):
                    icon_path = resource_path("import_tool.png")
            if os.path.exists(icon_path):
                msg.setWindowIcon(QIcon(icon_path))
            
            if msg.exec() != QMessageBox.Yes:
                return
        
        # Start import
        self.import_btn.setEnabled(False)
        self.browse_btn.setEnabled(False)
        self.progress_bar.setValue(0)
        
        self.worker = ImportWorker(self.excel_file, mode, conflict_strategy)
        self.worker.progress.connect(self.progress_bar.setValue)
        self.worker.status.connect(self.status_label.setText)
        self.worker.finished.connect(self.import_finished)
        self.worker.start()
        
        self.log_message(f"Starting {mode} import...")
    
    def import_finished(self, success, message):
        self.import_btn.setEnabled(True)
        self.browse_btn.setEnabled(True)
        
        if success:
            self.log_message("✅ " + message)
            msg = QMessageBox(self)
            msg.setIcon(QMessageBox.Information)
            msg.setWindowTitle("Import Complete")
            msg.setText("Import Complete")
            msg.setInformativeText(message)
            
            # Set dialog icon
            import platform
            if platform.system() == "Darwin":
                icon_path = resource_path("import_tool.icns")
                if not os.path.exists(icon_path):
                    icon_path = resource_path("import_tool.png")
            else:
                icon_path = resource_path("import_tool.ico")
                if not os.path.exists(icon_path):
                    icon_path = resource_path("import_tool.png")
            if os.path.exists(icon_path):
                msg.setWindowIcon(QIcon(icon_path))
            
            msg.exec()
        else:
            self.log_message("❌ " + message)
            msg = QMessageBox(self)
            msg.setIcon(QMessageBox.Critical)
            msg.setWindowTitle("Import Failed")
            msg.setText("Import Failed")
            msg.setInformativeText(message)
            
            # Set dialog icon
            import platform
            if platform.system() == "Darwin":
                icon_path = resource_path("import_tool.icns")
                if not os.path.exists(icon_path):
                    icon_path = resource_path("import_tool.png")
            else:
                icon_path = resource_path("import_tool.ico")
                if not os.path.exists(icon_path):
                    icon_path = resource_path("import_tool.png")
            if os.path.exists(icon_path):
                msg.setWindowIcon(QIcon(icon_path))
            
            msg.exec()
    
    def log_message(self, message):
        timestamp = datetime.now().strftime("%H:%M:%S")
        self.log_text.append(f"[{timestamp}] {message}")

def main():
    parser = argparse.ArgumentParser(description="MushLog Database Import Tool")
    parser.add_argument("--file", type=str, help="Excel file to import")
    parser.add_argument("--mode", choices=["replace", "merge"], default="merge", 
                       help="Import mode (default: merge)")
    parser.add_argument("--conflict", choices=["skip", "overwrite"], default="skip",
                       help="Conflict resolution strategy (default: skip)")
    args = parser.parse_args()
    
    # Set application icon for macOS dock BEFORE creating QApplication
    import platform
    if platform.system() == "Darwin":
        # Try import tool icon first, fallback to mushroom icon (which works)
        icon_path = resource_path("import_tool.icns")
        if not os.path.exists(icon_path):
            icon_path = resource_path("import_tool.png")
        if not os.path.exists(icon_path):
            icon_path = resource_path("mushroom_icon.icns")  # Fallback to working icon
        # Set environment variable for macOS dock icon
        os.environ['QT_MAC_WANTS_ICON'] = '1'
    
    app = QApplication(sys.argv)
    app.setApplicationName(" ")
    app.setOrganizationName(" ")
    
    # Set application icon for macOS dock
    if platform.system() == "Darwin":
        app.setWindowIcon(QIcon(icon_path))
        # Also try setting it as a property
        app.setProperty("macIcon", icon_path)
    
    # Initialize database if it doesn't exist
    init_db()
    logger.info(f"[ImportTool] Using database: {DB_FILE}")
    window = ImportTool()
    
    # Set window icon for macOS dock (alternative approach)
    import platform
    if platform.system() == "Darwin":
        # Try ICNS first, fallback to PNG
        icon_path = resource_path("import_tool.icns")
        if not os.path.exists(icon_path):
            icon_path = resource_path("import_tool.png")
        window.setWindowIcon(QIcon(icon_path))
        # Force icon update for macOS
        window.repaint()
    
    # Pre-select file if provided
    if args.file:
        window.excel_file = args.file
        window.file_label.setText(os.path.basename(args.file))
        window.import_btn.setEnabled(True)
    
    # Pre-select mode if provided
    if args.mode == "replace":
        window.replace_radio.setChecked(True)
    else:
        window.merge_radio.setChecked(True)
    
    # Pre-select conflict strategy if provided
    if args.conflict == "overwrite":
        window.overwrite_radio.setChecked(True)
    else:
        window.skip_radio.setChecked(True)
    
    window.show()
    sys.exit(app.exec())

if __name__ == "__main__":
    main()