#!/usr/bin/env python3
"""
Command Table Widget - Reusable
================================
Auto-detecting command table with CRUD operations.

Features:
- Auto-detect columns from database
- Search + filter
- Add/Edit/Delete commands
- Gray out when disconnected
- Double-click to execute
- Works for both UART and I2C

Author: Kynsight
Version: 1.0.0
"""

from PyQt6.QtWidgets import (
    QWidget, QVBoxLayout, QHBoxLayout, QTableWidget, QTableWidgetItem,
    QPushButton, QLineEdit, QLabel, QComboBox, QHeaderView,
    QAbstractItemView, QDialog, QFormLayout, QDialogButtonBox,
    QMessageBox, QTextEdit
)
from PyQt6.QtCore import Qt, pyqtSignal
from PyQt6.QtGui import QColor


class CommandDialog(QDialog):
    """Dialog for adding/editing commands."""
    
    def __init__(self, command_type, all_columns, values=None, parent=None):
        super().__init__(parent)
        
        self.command_type = command_type
        self.all_columns = all_columns
        self.fields = {}
        
        self.setWindowTitle("Add Command" if values is None else "Edit Command")
        self.setModal(True)
        
        layout = QVBoxLayout()
        self.setLayout(layout)
        
        # Form
        form_layout = QFormLayout()
        
        for col in all_columns:
            if col == 'command_id':
                continue  # Skip ID
            
            # Create appropriate widget
            if col == 'description':
                widget = QTextEdit()
                widget.setMaximumHeight(80)
                if values and col in values:
                    widget.setPlainText(str(values[col]))
            else:
                widget = QLineEdit()
                if values and col in values:
                    widget.setText(str(values[col]))
            
            self.fields[col] = widget
            form_layout.addRow(col.replace('_', ' ').title() + ":", widget)
        
        layout.addLayout(form_layout)
        
        # Buttons
        button_box = QDialogButtonBox(
            QDialogButtonBox.StandardButton.Ok | 
            QDialogButtonBox.StandardButton.Cancel
        )
        button_box.accepted.connect(self.accept)
        button_box.rejected.connect(self.reject)
        layout.addWidget(button_box)
    
    def get_values(self):
        """Get values from form."""
        values = {}
        for col, widget in self.fields.items():
            if isinstance(widget, QTextEdit):
                values[col] = widget.toPlainText()
            else:
                values[col] = widget.text()
        return values


class CommandTableWidget(QWidget):
    """
    Reusable command table with auto-detection.
    
    Signals:
        command_selected: (command_id, command_data)
        command_double_clicked: (command_id, command_data)
    """
    
    command_selected = pyqtSignal(int, dict)  # command_id, all column data
    command_double_clicked = pyqtSignal(int, dict)
    
    def __init__(self, db_connection, command_type='uart', visible_columns=None, parent=None):
        """
        Args:
            db_connection: SQLite connection
            command_type: 'uart' or 'i2c'
            visible_columns: List of columns to display (None = all)
        """
        super().__init__(parent)
        
        self.conn = db_connection
        self.command_type = command_type
        self.table_name = f"{command_type}_commands"
        
        self.all_commands = []
        self.all_columns = []  # All columns from DB
        self.visible_columns = visible_columns  # Columns to show
        self.filter_column = None  # For category/operation filter
        
        self._init_ui()
        self._detect_columns()
        self._load_commands()
    
    def _init_ui(self):
        """Initialize UI."""
        layout = QVBoxLayout()
        self.setLayout(layout)
        
        # Search and filter
        filter_layout = QHBoxLayout()
        
        self.txt_search = QLineEdit()
        self.txt_search.setPlaceholderText("Search commands...")
        self.txt_search.textChanged.connect(self._filter_table)
        filter_layout.addWidget(self.txt_search, 2)
        
        self.combo_filter = QComboBox()
        self.combo_filter.currentTextChanged.connect(self._filter_table)
        filter_layout.addWidget(self.combo_filter, 1)
        
        layout.addLayout(filter_layout)
        
        # Table
        self.table = QTableWidget()
        self.table.setSelectionBehavior(QAbstractItemView.SelectionBehavior.SelectRows)
        self.table.setSelectionMode(QAbstractItemView.SelectionMode.SingleSelection)
        self.table.setAlternatingRowColors(True)
        self.table.verticalHeader().setVisible(False)
        self.table.setEditTriggers(QAbstractItemView.EditTrigger.NoEditTriggers)
        
        self.table.itemSelectionChanged.connect(self._on_selection_changed)
        self.table.cellDoubleClicked.connect(self._on_double_click)
        
        layout.addWidget(self.table)
        
        # CRUD buttons
        btn_layout = QHBoxLayout()
        
        self.btn_add = QPushButton("+ Add")
        self.btn_add.clicked.connect(self._add_command)
        btn_layout.addWidget(self.btn_add)
        
        self.btn_edit = QPushButton("✎ Edit")
        self.btn_edit.setEnabled(False)
        self.btn_edit.clicked.connect(self._edit_command)
        btn_layout.addWidget(self.btn_edit)
        
        self.btn_delete = QPushButton("🗑 Delete")
        self.btn_delete.setEnabled(False)
        self.btn_delete.clicked.connect(self._delete_command)
        btn_layout.addWidget(self.btn_delete)
        
        btn_layout.addStretch()
        
        self.lbl_count = QLabel()
        btn_layout.addWidget(self.lbl_count)
        
        layout.addLayout(btn_layout)
    
    def _detect_columns(self):
        """Auto-detect columns from database."""
        cursor = self.conn.execute(f"PRAGMA table_info({self.table_name})")
        self.all_columns = [row[1] for row in cursor.fetchall()]
        
        # If no visible_columns specified, show all
        if self.visible_columns is None:
            self.visible_columns = self.all_columns
        
        # Setup table columns (only visible ones)
        self.table.setColumnCount(len(self.visible_columns))
        self.table.setHorizontalHeaderLabels([
            col.replace('_', ' ').title() for col in self.visible_columns
        ])
        
        # Make ALL columns user-resizable (Interactive mode)
        header = self.table.horizontalHeader()
        for i in range(len(self.visible_columns)):
            header.setSectionResizeMode(i, QHeaderView.ResizeMode.Interactive)
        
        # Detect filter column (category for UART, operation for I2C)
        if 'category' in self.all_columns:
            self.filter_column = 'category'
            self.combo_filter.addItem("All Categories")
        elif 'operation' in self.all_columns:
            self.filter_column = 'operation'
            self.combo_filter.addItem("All Operations")
        else:
            self.combo_filter.setVisible(False)
    
    def _load_commands(self):
        """Load commands from database."""
        cursor = self.conn.execute(f"""
            SELECT * FROM {self.table_name}
            WHERE is_active = 1
            ORDER BY {self.all_columns[1]}
        """)
        
        self.all_commands = []
        for row in cursor.fetchall():
            cmd_dict = {self.all_columns[i]: row[i] for i in range(len(self.all_columns))}
            self.all_commands.append(cmd_dict)
        
        # Populate filter dropdown
        if self.filter_column:
            values = sorted(set(cmd[self.filter_column] for cmd in self.all_commands 
                              if cmd[self.filter_column]))
            self.combo_filter.clear()
            self.combo_filter.addItem(f"All {self.filter_column.title()}s")
            self.combo_filter.addItems(values)
        
        self._display_commands(self.all_commands)
    
    def _display_commands(self, commands):
        """Display commands in table (only visible columns)."""
        self.table.setRowCount(len(commands))
        
        for row_idx, cmd in enumerate(commands):
            for col_idx, col_name in enumerate(self.visible_columns):
                value = cmd.get(col_name, '')
                
                item = QTableWidgetItem(str(value) if value is not None else '')
                
                # Center-align ID
                if col_name == 'command_id':
                    item.setTextAlignment(Qt.AlignmentFlag.AlignCenter)
                
                self.table.setItem(row_idx, col_idx, item)
                
                # Color by category/operation (dark theme compatible)
                color = self._get_row_color(cmd)
                if color:
                    item.setBackground(color)
        
        self.lbl_count.setText(f"Count: {len(commands)}")
    
    def _get_row_color(self, cmd):
        """Get row color based on category/operation (dark theme compatible)."""
        if self.command_type == 'uart':
            colors = {
                "Door": QColor(70, 130, 180),      # Steel blue
                "Action": QColor(255, 165, 0),     # Orange
                "Error": QColor(220, 20, 60),      # Crimson
                "Status": QColor(60, 179, 113),    # Medium sea green
                "Motor": QColor(218, 165, 32),     # Goldenrod
                "Sensor": QColor(147, 112, 219),   # Medium purple
                "Power": QColor(138, 43, 226),     # Blue violet
                "Info": QColor(100, 149, 237),     # Cornflower blue
            }
            return colors.get(cmd.get('category'))
        else:  # i2c
            operation = cmd.get('operation')
            if operation == 'read':
                return QColor(60, 179, 113)  # Medium sea green
            elif operation == 'write':
                return QColor(255, 140, 0)   # Dark orange
        return None
    
    def _filter_table(self):
        """Filter table by search and dropdown."""
        search_text = self.txt_search.text().lower()
        filter_value = self.combo_filter.currentText()
        
        # Filter commands
        filtered = []
        for cmd in self.all_commands:
            # Filter by dropdown
            if self.filter_column and not filter_value.startswith("All"):
                if cmd.get(self.filter_column) != filter_value:
                    continue
            
            # Filter by search text
            if search_text:
                found = False
                for col_name, value in cmd.items():
                    if search_text in str(value).lower():
                        found = True
                        break
                if not found:
                    continue
            
            filtered.append(cmd)
        
        self._display_commands(filtered)
    
    def _on_selection_changed(self):
        """Handle selection change."""
        selected = self.table.selectedItems()
        has_selection = bool(selected)
        
        # Only auto-enable edit/delete if they're not externally disabled
        # (i.e., if Add button is enabled, we're in edit mode)
        if self.btn_add.isEnabled():
            self.btn_edit.setEnabled(has_selection)
            self.btn_delete.setEnabled(has_selection)
        
        if has_selection:
            row = selected[0].row()
            
            # Get command_id from visible columns
            cmd_id_col_idx = self.visible_columns.index('command_id') if 'command_id' in self.visible_columns else 0
            command_id = int(self.table.item(row, cmd_id_col_idx).text())
            
            # Get ALL column data (from all_commands, not just visible)
            cmd_data = next((cmd for cmd in self.all_commands 
                           if cmd['command_id'] == command_id), {})
            
            # Emit signal
            self.command_selected.emit(command_id, cmd_data)
    
    def _on_double_click(self, row, col):
        """Handle double-click."""
        # Get command_id from visible columns
        cmd_id_col_idx = self.visible_columns.index('command_id') if 'command_id' in self.visible_columns else 0
        command_id = int(self.table.item(row, cmd_id_col_idx).text())
        
        # Get ALL column data
        cmd_data = next((cmd for cmd in self.all_commands 
                       if cmd['command_id'] == command_id), {})
        
        # Emit signal
        self.command_double_clicked.emit(command_id, cmd_data)
    
    def _add_command(self):
        """Add new command."""
        dialog = CommandDialog(self.command_type, self.all_columns, parent=self)
        
        if dialog.exec() == QDialog.DialogCode.Accepted:
            values = dialog.get_values()
            
            # Build INSERT query
            cols = [col for col in self.all_columns if col != 'command_id']
            placeholders = ', '.join(['?'] * len(cols))
            col_names = ', '.join(cols)
            
            try:
                self.conn.execute(f"""
                    INSERT INTO {self.table_name} ({col_names})
                    VALUES ({placeholders})
                """, [values.get(col, '') for col in cols])
                
                self.conn.commit()
                self._load_commands()
                
                QMessageBox.information(self, "Success", "Command added successfully!")
                
            except Exception as e:
                QMessageBox.critical(self, "Error", f"Failed to add command:\n{e}")
    
    def _edit_command(self):
        """Edit selected command."""
        selected = self.table.selectedItems()
        if not selected:
            return
        
        row = selected[0].row()
        
        # Get command_id from visible columns
        cmd_id_col_idx = self.visible_columns.index('command_id') if 'command_id' in self.visible_columns else 0
        command_id = int(self.table.item(row, cmd_id_col_idx).text())
        
        # Get current values (all columns)
        current_values = next((cmd for cmd in self.all_commands 
                              if cmd['command_id'] == command_id), {})
        
        dialog = CommandDialog(self.command_type, self.all_columns, 
                              current_values, parent=self)
        
        if dialog.exec() == QDialog.DialogCode.Accepted:
            values = dialog.get_values()
            
            # Build UPDATE query
            set_clause = ', '.join([f"{col} = ?" for col in values.keys()])
            
            try:
                self.conn.execute(f"""
                    UPDATE {self.table_name}
                    SET {set_clause}
                    WHERE command_id = ?
                """, list(values.values()) + [command_id])
                
                self.conn.commit()
                self._load_commands()
                
                QMessageBox.information(self, "Success", "Command updated successfully!")
                
            except Exception as e:
                QMessageBox.critical(self, "Error", f"Failed to update command:\n{e}")
    
    def _delete_command(self):
        """Delete selected command."""
        selected = self.table.selectedItems()
        if not selected:
            return
        
        row = selected[0].row()
        
        # Get command_id and name
        cmd_id_col_idx = self.visible_columns.index('command_id') if 'command_id' in self.visible_columns else 0
        cmd_name_col_idx = self.visible_columns.index('command_name') if 'command_name' in self.visible_columns else 1
        
        command_id = int(self.table.item(row, cmd_id_col_idx).text())
        name = self.table.item(row, cmd_name_col_idx).text()
        
        reply = QMessageBox.question(
            self,
            "Delete Command",
            f"Delete command '{name}'?\n\nThis will mark it as inactive.",
            QMessageBox.StandardButton.Yes | QMessageBox.StandardButton.No
        )
        
        if reply == QMessageBox.StandardButton.Yes:
            try:
                # Soft delete (mark as inactive)
                self.conn.execute(f"""
                    UPDATE {self.table_name}
                    SET is_active = 0
                    WHERE command_id = ?
                """, (command_id,))
                
                self.conn.commit()
                self._load_commands()
                
                QMessageBox.information(self, "Success", "Command deleted successfully!")
                
            except Exception as e:
                QMessageBox.critical(self, "Error", f"Failed to delete command:\n{e}")
    
    def set_enabled_state(self, enabled: bool):
        """Enable/disable table (gray out when disconnected)."""
        self.table.setEnabled(enabled)
        self.txt_search.setEnabled(enabled)
        self.combo_filter.setEnabled(enabled)
        # Keep CRUD buttons always enabled
    
    def get_selected_command_id(self):
        """Get selected command ID."""
        selected = self.table.selectedItems()
        if selected:
            return int(self.table.item(selected[0].row(), 0).text())
        return None
    
    def refresh(self):
        """Reload commands from database."""
        self._load_commands()


if __name__ == "__main__":
    import sys
    from PyQt6.QtWidgets import QApplication
    import sqlite3
    
    app = QApplication(sys.argv)
    
    conn = sqlite3.connect("./database/ehinge.db")
    
    widget = CommandTableWidget(conn, 'uart')
    widget.setWindowTitle("Command Table - UART")
    widget.resize(900, 600)
    widget.show()
    
    sys.exit(app.exec())
