"""
Database module for Python Scheduler
Manages SQLite database with job definitions, schedules, and execution history
"""

import sqlite3
import json
import socket
from datetime import datetime
from pathlib import Path
from typing import Optional, List, Dict, Any
from contextlib import contextmanager
from loguru import logger


class SchedulerDatabase:
    """Singleton database manager for scheduler"""

    _instance = None

    def __new__(cls, remote_path: str = None, local_path: str = None, local_runtime_path: str = None):
        if cls._instance is None:
            cls._instance = super().__new__(cls)
            cls._instance._initialized = False
        return cls._instance

    def __init__(self, remote_path: str = r"C:\PYTHON\Frank\data\frank.db",
                 local_path: str = None, local_runtime_path: str = None):
        if self._initialized:
            return

        self._remote_path = remote_path
        # split_mode=True: daemon — local DB for runtime, B only for config sync + version poll
        # split_mode=False: GUI — connects directly to B (backward compat)
        self._split_mode = local_path is not None
        self.db_path = local_path if self._split_mode else remote_path
        # local_runtime_path: when set (GUI non-split mode), backup_operations queries
        # read from frank_local.db instead of frank.db (daemon writes there)
        self._local_runtime_path = local_runtime_path
        self._ensure_db_exists()
        self._initialized = True
        if self._split_mode:
            logger.info(f"Database split mode — local: {self.db_path} | remote: {self._remote_path}")
        else:
            logger.info(f"Database initialized at {self.db_path}")

    def _ensure_db_exists(self):
        """Create local schema; if split_mode, also sync config from remote B"""
        try:
            Path(self.db_path).parent.mkdir(parents=True, exist_ok=True)
        except (OSError, NotImplementedError):
            # UNC paths on Windows don't support mkdir — directory must pre-exist
            pass

        with self._get_connection() as conn:
            # Enable WAL mode for concurrent access
            conn.execute("PRAGMA journal_mode=WAL")
            conn.execute("PRAGMA foreign_keys=ON")

            # Create tables
            self._create_tables(conn)
            conn.commit()

        if self._split_mode:
            # Init config_version table on remote B (non-blocking)
            self._ensure_remote_config_version_table()
            # Sync config tables from B → local (fail-safe: uses local cache if B unreachable)
            self._sync_config_from_remote()

    @contextmanager
    def _get_runtime_connection(self):
        """Connection to frank_local.db for backup/runtime data (GUI non-split mode).
        Falls back to main db_path if local_runtime_path is not set."""
        path = self._local_runtime_path or self.db_path
        conn = sqlite3.connect(path, timeout=30.0)
        conn.row_factory = sqlite3.Row
        try:
            yield conn
        finally:
            conn.close()

    @contextmanager
    def _get_connection(self):
        """Context manager for database connections (always local in split_mode)"""
        conn = sqlite3.connect(self.db_path, timeout=30.0)
        conn.row_factory = sqlite3.Row  # Return rows as dictionaries
        try:
            yield conn
        finally:
            conn.close()

    @contextmanager
    def _get_remote_connection(self):
        """Short-timeout connection to remote B (UNC path). Raises on failure."""
        conn = sqlite3.connect(self._remote_path, timeout=5.0)
        try:
            yield conn
        finally:
            conn.close()

    def _ensure_remote_config_version_table(self):
        """Create config_version table on remote B if not exists (non-blocking)."""
        try:
            with self._get_remote_connection() as conn:
                conn.execute("""
                    CREATE TABLE IF NOT EXISTS config_version (
                        id INTEGER PRIMARY KEY CHECK (id = 1),
                        version INTEGER NOT NULL DEFAULT 1,
                        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
                    )
                """)
                conn.execute("INSERT OR IGNORE INTO config_version (id, version) VALUES (1, 1)")
                conn.commit()
        except Exception as e:
            logger.warning(f"Could not init remote config_version table: {e}")

    def get_remote_config_version(self) -> Optional[int]:
        """
        Read config_version from remote B (single lightweight query, 5s timeout).
        Returns None if B is unreachable.
        """
        try:
            with self._get_remote_connection() as conn:
                row = conn.execute("SELECT version FROM config_version WHERE id = 1").fetchone()
                return row[0] if row else 0
        except Exception:
            return None

    # Config tables synced from B → local at startup and on version change
    _CONFIG_TABLES = [
        'jobs', 'job_groups', 'job_group_members',
        'schedules', 'backup_profiles', 'scheduler_config',
    ]

    def _sync_config_from_remote(self) -> bool:
        """
        Copy config tables from remote B → local DB.
        Returns True on success, False if B unreachable (uses local cache silently).
        """
        try:
            remote_conn = sqlite3.connect(self._remote_path, timeout=5.0)
            try:
                with self._get_connection() as local:
                    for table in self._CONFIG_TABLES:
                        try:
                            cursor = remote_conn.execute(f"SELECT * FROM {table}")
                            cols = [d[0] for d in cursor.description]
                            rows = cursor.fetchall()
                            local.execute(f"DELETE FROM {table}")
                            if rows:
                                ph = ','.join(['?'] * len(cols))
                                local.executemany(
                                    f"INSERT INTO {table} ({','.join(cols)}) VALUES ({ph})",
                                    rows
                                )
                        except Exception as e:
                            logger.warning(f"Skipping table '{table}' during remote sync: {e}")
                    local.commit()
            finally:
                remote_conn.close()
            logger.info(f"Config synced from remote B ({len(self._CONFIG_TABLES)} tables)")
            return True
        except Exception as e:
            logger.warning(f"Remote sync failed: {e} — using local cache")
            return False

    def _create_tables(self, conn: sqlite3.Connection):
        """Create all database tables"""

        # Jobs table
        conn.execute("""
            CREATE TABLE IF NOT EXISTS jobs (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL UNIQUE,
                description TEXT,
                job_type TEXT NOT NULL,
                executable_path TEXT NOT NULL,
                arguments TEXT,
                working_directory TEXT,
                schedule_type TEXT NOT NULL,
                schedule_config TEXT NOT NULL,
                enabled BOOLEAN DEFAULT 1,
                timeout_seconds INTEGER DEFAULT 3600,
                retry_count INTEGER DEFAULT 0,
                retry_delay_seconds INTEGER DEFAULT 300,
                criticality TEXT DEFAULT 'normal',
                tags TEXT,
                show_console BOOLEAN DEFAULT 0,
                show_on_dashboard BOOLEAN DEFAULT 1,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                created_by TEXT DEFAULT 'user'
            )
        """)

        # Job groups table
        conn.execute("""
            CREATE TABLE IF NOT EXISTS job_groups (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL UNIQUE,
                description TEXT,
                error_handling TEXT DEFAULT 'stop_on_error',
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        """)

        # Job group members table
        conn.execute("""
            CREATE TABLE IF NOT EXISTS job_group_members (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                group_id INTEGER NOT NULL,
                job_id INTEGER NOT NULL,
                execution_order INTEGER NOT NULL,
                FOREIGN KEY (group_id) REFERENCES job_groups(id) ON DELETE CASCADE,
                FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE,
                UNIQUE(group_id, job_id),
                UNIQUE(group_id, execution_order)
            )
        """)

        # Schedules table (new architecture - separates "what" from "when")
        conn.execute("""
            CREATE TABLE IF NOT EXISTS schedules (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL UNIQUE,
                description TEXT,
                target_type TEXT NOT NULL,
                target_id INTEGER NOT NULL,
                schedule_type TEXT NOT NULL,
                schedule_config TEXT NOT NULL,
                enabled BOOLEAN DEFAULT 1,
                show_on_dashboard BOOLEAN DEFAULT 1,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                created_by TEXT DEFAULT 'user'
            )
        """)

        # Job executions table
        conn.execute("""
            CREATE TABLE IF NOT EXISTS job_executions (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                job_id INTEGER NOT NULL,
                group_id INTEGER,
                status TEXT NOT NULL,
                start_time DATETIME NOT NULL,
                end_time DATETIME,
                duration_seconds REAL,
                exit_code INTEGER,
                log_file TEXT,
                stdout_preview TEXT,
                stderr_preview TEXT,
                error_message TEXT,
                triggered_by TEXT DEFAULT 'scheduler',
                parent_execution_id INTEGER,
                FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE,
                FOREIGN KEY (group_id) REFERENCES job_groups(id) ON DELETE SET NULL
            )
        """)

        # Notifications table
        conn.execute("""
            CREATE TABLE IF NOT EXISTS notifications (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                execution_id INTEGER NOT NULL,
                notification_type TEXT NOT NULL,
                status TEXT NOT NULL,
                message TEXT NOT NULL,
                sent_at DATETIME,
                error TEXT,
                FOREIGN KEY (execution_id) REFERENCES job_executions(id) ON DELETE CASCADE
            )
        """)

        # Configuration table
        conn.execute("""
            CREATE TABLE IF NOT EXISTS scheduler_config (
                key TEXT PRIMARY KEY,
                value TEXT NOT NULL,
                description TEXT,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        """)

        # ===== BACKUP MODULE TABLES =====

        # Backup profiles table
        conn.execute("""
            CREATE TABLE IF NOT EXISTS backup_profiles (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL UNIQUE,
                description TEXT,
                backup_root TEXT NOT NULL,
                sources TEXT NOT NULL,
                exclude_patterns TEXT,
                retention_days INTEGER DEFAULT 30,
                enabled BOOLEAN DEFAULT 1,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        """)

        # Backup operations table
        conn.execute("""
            CREATE TABLE IF NOT EXISTS backup_operations (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                profile_id INTEGER NOT NULL,
                operation_type TEXT NOT NULL,
                backup_name TEXT,
                status TEXT NOT NULL,
                start_time DATETIME NOT NULL,
                end_time DATETIME,
                files_total INTEGER DEFAULT 0,
                files_linked INTEGER DEFAULT 0,
                files_copied INTEGER DEFAULT 0,
                files_in_store INTEGER DEFAULT 0,
                total_size_bytes INTEGER DEFAULT 0,
                error_message TEXT,
                log_path TEXT,
                FOREIGN KEY (profile_id) REFERENCES backup_profiles(id) ON DELETE CASCADE
            )
        """)

        # Backup restores table
        conn.execute("""
            CREATE TABLE IF NOT EXISTS backup_restores (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                profile_id INTEGER NOT NULL,
                backup_name TEXT NOT NULL,
                restore_type TEXT NOT NULL,
                source_path TEXT,
                destination_path TEXT NOT NULL,
                status TEXT NOT NULL,
                start_time DATETIME NOT NULL,
                end_time DATETIME,
                files_restored INTEGER DEFAULT 0,
                error_message TEXT,
                FOREIGN KEY (profile_id) REFERENCES backup_profiles(id)
            )
        """)

        # Sync and Version tables removed in v1.3.0 (replaced by git + robocopy)

        # Create indexes
        conn.execute("CREATE INDEX IF NOT EXISTS idx_job_executions_job_id ON job_executions(job_id)")
        conn.execute("CREATE INDEX IF NOT EXISTS idx_job_executions_start_time ON job_executions(start_time DESC)")
        conn.execute("CREATE INDEX IF NOT EXISTS idx_job_executions_status ON job_executions(status)")
        conn.execute("CREATE INDEX IF NOT EXISTS idx_job_group_members_group ON job_group_members(group_id, execution_order)")
        conn.execute("CREATE INDEX IF NOT EXISTS idx_schedules_target ON schedules(target_type, target_id)")
        conn.execute("CREATE INDEX IF NOT EXISTS idx_schedules_enabled ON schedules(enabled)")
        conn.execute("CREATE INDEX IF NOT EXISTS idx_backup_ops_profile ON backup_operations(profile_id)")
        conn.execute("CREATE INDEX IF NOT EXISTS idx_backup_ops_time ON backup_operations(start_time DESC)")
        conn.execute("CREATE INDEX IF NOT EXISTS idx_backup_restores_profile ON backup_restores(profile_id)")
        # ===== MIGRATIONS =====
        # Add hostname column to jobs if not exists (v1.3.0)
        cursor = conn.execute("PRAGMA table_info(jobs)")
        columns = [row[1] for row in cursor.fetchall()]
        if 'hostname' not in columns:
            conn.execute("ALTER TABLE jobs ADD COLUMN hostname TEXT DEFAULT ''")
            # Assign current hostname to all existing jobs (migration from single-host setup)
            current_hostname = socket.gethostname()
            conn.execute("UPDATE jobs SET hostname = ? WHERE hostname = ''", (current_hostname,))
            logger.info(f"Migrated jobs table: assigned hostname '{current_hostname}' to existing jobs")

        # Add hostname column to backup_profiles if not exists (v1.3.1)
        cursor = conn.execute("PRAGMA table_info(backup_profiles)")
        bp_columns = [row[1] for row in cursor.fetchall()]
        if 'hostname' not in bp_columns:
            conn.execute("ALTER TABLE backup_profiles ADD COLUMN hostname TEXT DEFAULT ''")
            current_hostname = socket.gethostname()
            conn.execute("UPDATE backup_profiles SET hostname = ? WHERE hostname = ''", (current_hostname,))
            logger.info(f"Migrated backup_profiles table: assigned hostname '{current_hostname}' to existing profiles")

        # Add hostname column to job_executions if not exists (v1.3.2)
        cursor = conn.execute("PRAGMA table_info(job_executions)")
        ex_columns = [row[1] for row in cursor.fetchall()]
        if 'hostname' not in ex_columns:
            conn.execute("ALTER TABLE job_executions ADD COLUMN hostname TEXT DEFAULT ''")
            # Back-fill existing executions: assign the hostname of the job that ran them
            conn.execute("""
                UPDATE job_executions SET hostname = (
                    SELECT j.hostname FROM jobs j WHERE j.id = job_executions.job_id
                ) WHERE hostname = ''
            """)
            logger.info("Migrated job_executions table: back-filled hostname from jobs")

        # Add show_console + show_on_dashboard to jobs if not exists (v1.3.3)
        cursor = conn.execute("PRAGMA table_info(jobs)")
        jobs_columns = [row[1] for row in cursor.fetchall()]
        if 'show_console' not in jobs_columns:
            conn.execute("ALTER TABLE jobs ADD COLUMN show_console BOOLEAN DEFAULT 0")
            logger.info("Migrated jobs table: added show_console column")
        if 'show_on_dashboard' not in jobs_columns:
            conn.execute("ALTER TABLE jobs ADD COLUMN show_on_dashboard BOOLEAN DEFAULT 1")
            logger.info("Migrated jobs table: added show_on_dashboard column")

        # Add show_on_dashboard to schedules if not exists (v1.3.3)
        cursor = conn.execute("PRAGMA table_info(schedules)")
        sch_columns = [row[1] for row in cursor.fetchall()]
        if 'show_on_dashboard' not in sch_columns:
            conn.execute("ALTER TABLE schedules ADD COLUMN show_on_dashboard BOOLEAN DEFAULT 1")
            logger.info("Migrated schedules table: added show_on_dashboard column")

    # ===== JOB OPERATIONS =====

    def create_job(self, name: str, job_type: str, executable_path: str,
                   schedule_type: str, schedule_config: dict, **kwargs) -> int:
        """Create a new job"""
        with self._get_connection() as conn:
            cursor = conn.execute("""
                INSERT INTO jobs (
                    name, description, job_type, executable_path, arguments,
                    working_directory, schedule_type, schedule_config, enabled,
                    timeout_seconds, retry_count, retry_delay_seconds,
                    criticality, tags, show_console, show_on_dashboard, hostname
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """, (
                name,
                kwargs.get('description'),
                job_type,
                executable_path,
                kwargs.get('arguments'),
                kwargs.get('working_directory'),
                schedule_type,
                json.dumps(schedule_config),
                kwargs.get('enabled', True),
                kwargs.get('timeout_seconds', 3600),
                kwargs.get('retry_count', 0),
                kwargs.get('retry_delay_seconds', 300),
                kwargs.get('criticality', 'normal'),
                json.dumps(kwargs.get('tags', [])),
                kwargs.get('show_console', False),
                kwargs.get('show_on_dashboard', True),
                kwargs.get('hostname', socket.gethostname())
            ))
            conn.commit()
            job_id = cursor.lastrowid
            logger.info(f"Created job '{name}' with ID {job_id}")
            return job_id

    def get_job(self, job_id: int) -> Optional[Dict[str, Any]]:
        """Get job by ID"""
        with self._get_connection() as conn:
            cursor = conn.execute("SELECT * FROM jobs WHERE id = ?", (job_id,))
            row = cursor.fetchone()
            return dict(row) if row else None

    def get_job_by_name(self, name: str) -> Optional[Dict[str, Any]]:
        """Get job by name"""
        with self._get_connection() as conn:
            cursor = conn.execute("SELECT * FROM jobs WHERE name = ?", (name,))
            row = cursor.fetchone()
            return dict(row) if row else None

    def get_all_jobs(self, enabled_only: bool = False, hostname: str = None) -> List[Dict[str, Any]]:
        """Get all jobs, optionally filtered by hostname ('' = runs on all hosts)"""
        with self._get_connection() as conn:
            conditions = []
            params = []
            if enabled_only:
                conditions.append("enabled = 1")
            if hostname is not None:
                conditions.append("(hostname = '' OR hostname = ?)")
                params.append(hostname)
            where = f"WHERE {' AND '.join(conditions)}" if conditions else ""
            cursor = conn.execute(f"SELECT * FROM jobs {where} ORDER BY name", params)
            return [dict(row) for row in cursor.fetchall()]

    def update_job(self, job_id: int, **kwargs):
        """Update job fields"""
        allowed_fields = [
            'name', 'description', 'job_type', 'executable_path', 'arguments',
            'working_directory', 'schedule_type', 'schedule_config', 'enabled',
            'timeout_seconds', 'retry_count', 'retry_delay_seconds',
            'criticality', 'tags', 'show_console', 'show_on_dashboard', 'hostname'
        ]

        updates = []
        values = []

        for field, value in kwargs.items():
            if field in allowed_fields:
                if field in ['schedule_config', 'tags'] and isinstance(value, (dict, list)):
                    value = json.dumps(value)
                updates.append(f"{field} = ?")
                values.append(value)

        if not updates:
            return

        updates.append("updated_at = CURRENT_TIMESTAMP")
        values.append(job_id)

        with self._get_connection() as conn:
            conn.execute(f"UPDATE jobs SET {', '.join(updates)} WHERE id = ?", values)
            conn.commit()
            logger.info(f"Updated job ID {job_id}")

    def delete_job(self, job_id: int):
        """Delete job and associated schedules"""
        # Delete associated schedules first
        deleted_schedules = self._delete_schedules_for_target('job', job_id)

        # Delete the job (CASCADE will handle job_group_members and job_executions)
        with self._get_connection() as conn:
            conn.execute("DELETE FROM jobs WHERE id = ?", (job_id,))
            conn.commit()
            logger.info(f"Deleted job ID {job_id} (and {deleted_schedules} schedule(s))")

    # ===== JOB EXECUTION OPERATIONS =====

    def create_execution(self, job_id: int, status: str, triggered_by: str = 'scheduler', **kwargs) -> int:
        """Create execution record"""
        with self._get_runtime_connection() as conn:
            cursor = conn.execute("""
                INSERT INTO job_executions (
                    job_id, group_id, status, start_time, triggered_by, parent_execution_id, hostname
                ) VALUES (?, ?, ?, ?, ?, ?, ?)
            """, (
                job_id,
                kwargs.get('group_id'),
                status,
                kwargs.get('start_time', datetime.now()),
                triggered_by,
                kwargs.get('parent_execution_id'),
                socket.gethostname()
            ))
            conn.commit()
            execution_id = cursor.lastrowid
            logger.debug(f"Created execution {execution_id} for job {job_id}")
            return execution_id

    def update_execution(self, execution_id: int, **kwargs):
        """Update execution record"""
        allowed_fields = [
            'status', 'start_time', 'end_time', 'duration_seconds', 'exit_code',
            'log_file', 'stdout_preview', 'stderr_preview', 'error_message'
        ]

        updates = []
        values = []

        for field, value in kwargs.items():
            if field in allowed_fields:
                updates.append(f"{field} = ?")
                values.append(value)

        if not updates:
            return

        values.append(execution_id)

        with self._get_runtime_connection() as conn:
            conn.execute(f"UPDATE job_executions SET {', '.join(updates)} WHERE id = ?", values)
            conn.commit()

    def get_execution(self, execution_id: int) -> Optional[Dict[str, Any]]:
        """Get execution by ID"""
        with self._get_runtime_connection() as conn:
            cursor = conn.execute("SELECT * FROM job_executions WHERE id = ?", (execution_id,))
            row = cursor.fetchone()
            return dict(row) if row else None

    def get_execution_status(self, execution_id: int) -> Optional[str]:
        """Get execution status"""
        with self._get_runtime_connection() as conn:
            cursor = conn.execute("SELECT status FROM job_executions WHERE id = ?", (execution_id,))
            row = cursor.fetchone()
            return row['status'] if row else None

    def get_recent_executions(self, limit: int = 10, job_id: Optional[int] = None, hostname: str = None) -> List[Dict[str, Any]]:
        """Get recent executions, optionally filtered by hostname"""
        with self._get_runtime_connection() as conn:
            if job_id:
                cursor = conn.execute("""
                    SELECT e.*, j.name as job_name, j.show_on_dashboard as show_on_dashboard_value
                    FROM job_executions e
                    JOIN jobs j ON e.job_id = j.id
                    WHERE e.job_id = ?
                    ORDER BY e.start_time DESC
                    LIMIT ?
                """, (job_id, limit))
            elif hostname:
                cursor = conn.execute("""
                    SELECT e.*, j.name as job_name, j.show_on_dashboard as show_on_dashboard_value
                    FROM job_executions e
                    JOIN jobs j ON e.job_id = j.id
                    WHERE e.hostname = ? OR e.hostname IS NULL OR e.hostname = ''
                    ORDER BY e.start_time DESC
                    LIMIT ?
                """, (hostname, limit))
            else:
                cursor = conn.execute("""
                    SELECT e.*, j.name as job_name, j.show_on_dashboard as show_on_dashboard_value
                    FROM job_executions e
                    JOIN jobs j ON e.job_id = j.id
                    ORDER BY e.start_time DESC
                    LIMIT ?
                """, (limit,))
            return [dict(row) for row in cursor.fetchall()]

    def get_active_executions(self, hostname: str = None) -> List[Dict[str, Any]]:
        """Get currently running executions, optionally filtered by hostname"""
        with self._get_runtime_connection() as conn:
            if hostname:
                cursor = conn.execute("""
                    SELECT e.*, j.name as job_name
                    FROM job_executions e
                    JOIN jobs j ON e.job_id = j.id
                    WHERE e.status IN ('queued', 'running')
                      AND (e.hostname = ? OR e.hostname IS NULL OR e.hostname = '')
                    ORDER BY e.start_time DESC
                """, (hostname,))
            else:
                cursor = conn.execute("""
                    SELECT e.*, j.name as job_name
                    FROM job_executions e
                    JOIN jobs j ON e.job_id = j.id
                    WHERE e.status IN ('queued', 'running')
                    ORDER BY e.start_time DESC
                """)
            return [dict(row) for row in cursor.fetchall()]

    # ===== JOB GROUP OPERATIONS =====

    def create_job_group(self, name: str, description: str = None, error_handling: str = 'stop_on_error') -> int:
        """Create job group"""
        with self._get_connection() as conn:
            cursor = conn.execute("""
                INSERT INTO job_groups (name, description, error_handling)
                VALUES (?, ?, ?)
            """, (name, description, error_handling))
            conn.commit()
            group_id = cursor.lastrowid
            logger.info(f"Created job group '{name}' with ID {group_id}")
            return group_id

    def add_job_to_group(self, group_id: int, job_id: int, execution_order: int):
        """Add job to group"""
        with self._get_connection() as conn:
            conn.execute("""
                INSERT INTO job_group_members (group_id, job_id, execution_order)
                VALUES (?, ?, ?)
            """, (group_id, job_id, execution_order))
            conn.commit()
            logger.info(f"Added job {job_id} to group {group_id} at position {execution_order}")

    def get_job_group(self, group_id: int) -> Optional[Dict[str, Any]]:
        """Get job group by ID"""
        with self._get_connection() as conn:
            cursor = conn.execute("SELECT * FROM job_groups WHERE id = ?", (group_id,))
            row = cursor.fetchone()
            return dict(row) if row else None

    def get_job_group_members(self, group_id: int) -> List[Dict[str, Any]]:
        """Get job group members in execution order"""
        with self._get_connection() as conn:
            cursor = conn.execute("""
                SELECT m.*, j.name as job_name, j.hostname
                FROM job_group_members m
                JOIN jobs j ON m.job_id = j.id
                WHERE m.group_id = ?
                ORDER BY m.execution_order
            """, (group_id,))
            return [dict(row) for row in cursor.fetchall()]

    def update_job_group(self, group_id: int, name: str = None, description: str = None, error_handling: str = None):
        """Update job group"""
        with self._get_connection() as conn:
            updates = []
            params = []

            if name is not None:
                updates.append("name = ?")
                params.append(name)
            if description is not None:
                updates.append("description = ?")
                params.append(description)
            if error_handling is not None:
                updates.append("error_handling = ?")
                params.append(error_handling)

            if updates:
                params.append(group_id)
                query = f"UPDATE job_groups SET {', '.join(updates)} WHERE id = ?"
                conn.execute(query, params)
                conn.commit()
                logger.info(f"Updated job group {group_id}")

    def delete_job_group(self, group_id: int):
        """Delete job group, associated schedules, and members"""
        # Delete associated schedules first
        deleted_schedules = self._delete_schedules_for_target('group', group_id)

        # Delete the group (CASCADE will handle job_group_members)
        with self._get_connection() as conn:
            conn.execute("DELETE FROM job_groups WHERE id = ?", (group_id,))
            conn.commit()
            logger.info(f"Deleted job group {group_id} (and {deleted_schedules} schedule(s))")

    def remove_job_from_group(self, group_id: int, job_id: int):
        """Remove specific job from group"""
        with self._get_connection() as conn:
            conn.execute("""
                DELETE FROM job_group_members
                WHERE group_id = ? AND job_id = ?
            """, (group_id, job_id))
            conn.commit()
            logger.info(f"Removed job {job_id} from group {group_id}")

    def clear_group_members(self, group_id: int):
        """Remove all jobs from group"""
        with self._get_connection() as conn:
            conn.execute("DELETE FROM job_group_members WHERE group_id = ?", (group_id,))
            conn.commit()
            logger.info(f"Cleared all members from group {group_id}")

    # ===== SCHEDULE OPERATIONS =====

    def create_schedule(self, name: str, target_type: str, target_id: int,
                       schedule_type: str, schedule_config: dict, **kwargs) -> int:
        """
        Create a schedule for a job or group

        Args:
            name: Schedule name
            target_type: 'job' or 'group'
            target_id: ID of job or group to execute
            schedule_type: 'cron', 'interval', 'date', 'manual'
            schedule_config: Dict with schedule configuration
            **kwargs: enabled, description, created_by

        Returns:
            Schedule ID
        """
        # Validation
        if target_type not in ['job', 'group', 'backup_profile']:
            raise ValueError(f"Invalid target_type: {target_type}")

        if target_type == 'job':
            if not self.get_job(target_id):
                raise ValueError(f"Job {target_id} not found")
        elif target_type == 'group':
            if not self.get_job_group(target_id):
                raise ValueError(f"Group {target_id} not found")
        elif target_type == 'backup_profile':
            if not self.get_backup_profile(target_id):
                raise ValueError(f"Backup profile {target_id} not found")
        elif target_type == 'sync_profile':
            if not self.get_sync_profile(target_id):
                raise ValueError(f"Sync profile {target_id} not found")

        with self._get_connection() as conn:
            cursor = conn.execute("""
                INSERT INTO schedules (
                    name, description, target_type, target_id,
                    schedule_type, schedule_config, enabled, created_by
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            """, (
                name,
                kwargs.get('description'),
                target_type,
                target_id,
                schedule_type,
                json.dumps(schedule_config),
                kwargs.get('enabled', True),
                kwargs.get('created_by', 'user')
            ))
            conn.commit()
            schedule_id = cursor.lastrowid
            logger.info(f"Created schedule '{name}' (ID {schedule_id}): {target_type} {target_id}")
            return schedule_id

    def get_all_schedules(self, enabled_only: bool = False, hostname: str = None) -> List[Dict[str, Any]]:
        """
        Get all schedules with target information

        Args:
            enabled_only: Only return enabled schedules

        Returns:
            List of schedule dicts with target_name included
        """
        with self._get_connection() as conn:
            query = """
                SELECT
                    s.*,
                    CASE s.target_type
                        WHEN 'job' THEN j.name
                        WHEN 'group' THEN g.name
                        WHEN 'backup_profile' THEN bp.name
                    END as target_name,
                    COALESCE(s.show_on_dashboard, j.show_on_dashboard, 1) as show_on_dashboard_value
                FROM schedules s
                LEFT JOIN jobs j ON s.target_type = 'job' AND s.target_id = j.id
                LEFT JOIN job_groups g ON s.target_type = 'group' AND s.target_id = g.id
                LEFT JOIN backup_profiles bp ON s.target_type = 'backup_profile' AND s.target_id = bp.id
            """

            conditions = []
            params = []

            if enabled_only:
                conditions.append("s.enabled = 1")

            if hostname:
                conditions.append(
                    "("
                    "s.target_type NOT IN ('job', 'backup_profile', 'group')"
                    " OR (s.target_type = 'job' AND (j.hostname IS NULL OR j.hostname = '' OR j.hostname = ?))"
                    " OR (s.target_type = 'backup_profile' AND (bp.hostname IS NULL OR bp.hostname = '' OR bp.hostname = ?))"
                    " OR (s.target_type = 'group' AND EXISTS ("
                    "   SELECT 1 FROM job_group_members jgm"
                    "   JOIN jobs jj ON jgm.job_id = jj.id"
                    "   WHERE jgm.group_id = s.target_id"
                    "   AND (jj.hostname = ? OR jj.hostname IS NULL OR jj.hostname = '')"
                    " ))"
                    ")"
                )
                params.extend([hostname, hostname, hostname])

            if conditions:
                query += " WHERE " + " AND ".join(conditions)

            query += " ORDER BY s.name"

            cursor = conn.execute(query, params)
            results = [dict(row) for row in cursor.fetchall()]

            # Logging diagnostico
            total = len(results)
            job_count = sum(1 for r in results if r['target_type'] == 'job')
            group_count = sum(1 for r in results if r['target_type'] == 'group')
            backup_count = sum(1 for r in results if r['target_type'] == 'backup_profile')
            null_target_count = sum(1 for r in results if r['target_name'] is None)
            enabled_count = sum(1 for r in results if r['enabled'])
            disabled_count = total - enabled_count

            logger.debug(f"get_all_schedules(enabled_only={enabled_only}): "
                        f"total={total}, jobs={job_count}, groups={group_count}, backups={backup_count}, "
                        f"enabled={enabled_count}, disabled={disabled_count}, null_targets={null_target_count}")

            if null_target_count > 0:
                null_schedules = [r for r in results if r['target_name'] is None]
                for sched in null_schedules:
                    logger.warning(f"Schedule {sched['id']} '{sched['name']}' has NULL target_name "
                                 f"(target_type={sched['target_type']}, target_id={sched['target_id']})")

            return results

    def get_schedule(self, schedule_id: int) -> Optional[Dict[str, Any]]:
        """Get schedule by ID with target information"""
        with self._get_connection() as conn:
            cursor = conn.execute("""
                SELECT
                    s.*,
                    CASE s.target_type
                        WHEN 'job' THEN j.name
                        WHEN 'group' THEN g.name
                        WHEN 'backup_profile' THEN bp.name
                    END as target_name
                FROM schedules s
                LEFT JOIN jobs j ON s.target_type = 'job' AND s.target_id = j.id
                LEFT JOIN job_groups g ON s.target_type = 'group' AND s.target_id = g.id
                LEFT JOIN backup_profiles bp ON s.target_type = 'backup_profile' AND s.target_id = bp.id
                WHERE s.id = ?
            """, (schedule_id,))
            row = cursor.fetchone()
            return dict(row) if row else None

    def validate_schedule_target(self, target_type: str, target_id: int) -> bool:
        """
        Validate that a schedule target exists

        Args:
            target_type: Type of target ('job', 'group', 'backup_profile', 'sync_profile')
            target_id: ID of the target

        Returns:
            True if target exists, False otherwise
        """
        with self._get_connection() as conn:
            if target_type == 'job':
                cursor = conn.execute("SELECT 1 FROM jobs WHERE id = ?", (target_id,))
            elif target_type == 'group':
                cursor = conn.execute("SELECT 1 FROM job_groups WHERE id = ?", (target_id,))
            elif target_type == 'backup_profile':
                cursor = conn.execute("SELECT 1 FROM backup_profiles WHERE id = ?", (target_id,))
            elif target_type == 'sync_profile':
                cursor = conn.execute("SELECT 1 FROM sync_profiles WHERE id = ?", (target_id,))
            else:
                logger.warning(f"Unknown target_type: {target_type}")
                return False

            return cursor.fetchone() is not None

    def update_schedule(self, schedule_id: int, **kwargs):
        """Update schedule fields"""
        with self._get_connection() as conn:
            updates = []
            params = []

            for field in ['name', 'description', 'target_type', 'target_id',
                          'schedule_type', 'enabled']:
                if field in kwargs:
                    updates.append(f"{field} = ?")
                    params.append(kwargs[field])

            if 'schedule_config' in kwargs:
                updates.append("schedule_config = ?")
                params.append(json.dumps(kwargs['schedule_config']))

            if updates:
                updates.append("updated_at = CURRENT_TIMESTAMP")
                params.append(schedule_id)
                query = f"UPDATE schedules SET {', '.join(updates)} WHERE id = ?"
                conn.execute(query, params)
                conn.commit()
                logger.info(f"Updated schedule {schedule_id}")

    def delete_schedule(self, schedule_id: int):
        """Delete schedule (does not delete target job/group)"""
        with self._get_connection() as conn:
            conn.execute("DELETE FROM schedules WHERE id = ?", (schedule_id,))
            conn.commit()
            logger.info(f"Deleted schedule {schedule_id}")

    def _delete_schedules_for_target(self, target_type: str, target_id: int) -> int:
        """
        Delete all schedules associated with a specific target

        Args:
            target_type: Type of target ('job', 'group', 'backup_profile', 'sync_profile')
            target_id: ID of the target entity

        Returns:
            Number of schedules deleted
        """
        with self._get_connection() as conn:
            cursor = conn.execute("""
                SELECT id, name FROM schedules
                WHERE target_type = ? AND target_id = ?
            """, (target_type, target_id))
            schedules = cursor.fetchall()

            if schedules:
                schedule_names = [s['name'] for s in schedules]

                conn.execute("""
                    DELETE FROM schedules
                    WHERE target_type = ? AND target_id = ?
                """, (target_type, target_id))

                conn.commit()
                logger.info(f"Deleted {len(schedules)} schedule(s) for {target_type} {target_id}: {', '.join(schedule_names)}")
                return len(schedules)

            return 0

    # ===== BACKUP PROFILE OPERATIONS =====

    def create_backup_profile(self, name: str, backup_root: str, sources: list, **kwargs) -> int:
        """Create a new backup profile"""
        with self._get_connection() as conn:
            cursor = conn.execute("""
                INSERT INTO backup_profiles (
                    name, description, backup_root, sources,
                    exclude_patterns, retention_days, enabled
                ) VALUES (?, ?, ?, ?, ?, ?, ?)
            """, (
                name,
                kwargs.get('description'),
                backup_root,
                json.dumps(sources),
                json.dumps(kwargs.get('exclude_patterns', [])),
                kwargs.get('retention_days', 30),
                kwargs.get('enabled', True)
            ))
            conn.commit()
            profile_id = cursor.lastrowid
            logger.info(f"Created backup profile '{name}' with ID {profile_id}")
            return profile_id

    def get_backup_profile(self, profile_id: int) -> Optional[Dict[str, Any]]:
        """Get backup profile by ID"""
        with self._get_connection() as conn:
            cursor = conn.execute("SELECT * FROM backup_profiles WHERE id = ?", (profile_id,))
            row = cursor.fetchone()
            return dict(row) if row else None

    def get_all_backup_profiles(self, enabled_only: bool = False, hostname: str = None) -> List[Dict[str, Any]]:
        """Get all backup profiles, optionally filtered by hostname"""
        with self._get_connection() as conn:
            conditions = []
            params = []
            if enabled_only:
                conditions.append("enabled = 1")
            if hostname:
                conditions.append("(hostname = ? OR hostname IS NULL OR hostname = '')")
                params.append(hostname)
            where = (" WHERE " + " AND ".join(conditions)) if conditions else ""
            cursor = conn.execute(f"SELECT * FROM backup_profiles{where} ORDER BY name", params)
            return [dict(row) for row in cursor.fetchall()]

    def update_backup_profile(self, profile_id: int, **kwargs):
        """Update backup profile fields"""
        with self._get_connection() as conn:
            updates = []
            params = []

            for field in ['name', 'description', 'backup_root', 'retention_days', 'enabled']:
                if field in kwargs:
                    updates.append(f"{field} = ?")
                    params.append(kwargs[field])

            if 'sources' in kwargs:
                updates.append("sources = ?")
                params.append(json.dumps(kwargs['sources']))

            if 'exclude_patterns' in kwargs:
                updates.append("exclude_patterns = ?")
                params.append(json.dumps(kwargs['exclude_patterns']))

            if updates:
                updates.append("updated_at = CURRENT_TIMESTAMP")
                params.append(profile_id)
                query = f"UPDATE backup_profiles SET {', '.join(updates)} WHERE id = ?"
                conn.execute(query, params)
                conn.commit()
                logger.info(f"Updated backup profile {profile_id}")

    def delete_backup_profile(self, profile_id: int):
        """Delete backup profile and associated schedules"""
        # Delete associated schedules first
        deleted_schedules = self._delete_schedules_for_target('backup_profile', profile_id)

        # Delete the profile
        with self._get_connection() as conn:
            conn.execute("DELETE FROM backup_profiles WHERE id = ?", (profile_id,))
            conn.commit()
            logger.info(f"Deleted backup profile {profile_id} (and {deleted_schedules} schedule(s))")

    # ===== SYNC PROFILE OPERATIONS =====

    def create_sync_profile(self, name: str, source_path: str, destination_path: str, **kwargs) -> int:
        """Create a new sync profile"""
        with self._get_connection() as conn:
            cursor = conn.execute("""
                INSERT INTO sync_profiles (
                    name, description, source_path, destination_path,
                    exclude_patterns, protect_excluded, enabled
                ) VALUES (?, ?, ?, ?, ?, ?, ?)
            """, (
                name,
                kwargs.get('description'),
                source_path,
                destination_path,
                kwargs.get('exclude_patterns', ''),
                kwargs.get('protect_excluded', False),
                kwargs.get('enabled', True)
            ))
            conn.commit()
            profile_id = cursor.lastrowid
            logger.info(f"Created sync profile '{name}' with ID {profile_id}")
            return profile_id

    def get_sync_profile(self, profile_id: int) -> Optional[Dict[str, Any]]:
        """Get sync profile by ID"""
        with self._get_connection() as conn:
            cursor = conn.execute("SELECT * FROM sync_profiles WHERE id = ?", (profile_id,))
            row = cursor.fetchone()
            return dict(row) if row else None

    def get_all_sync_profiles(self, enabled_only: bool = False) -> List[Dict[str, Any]]:
        """Get all sync profiles"""
        with self._get_connection() as conn:
            if enabled_only:
                cursor = conn.execute("SELECT * FROM sync_profiles WHERE enabled = 1 ORDER BY name")
            else:
                cursor = conn.execute("SELECT * FROM sync_profiles ORDER BY name")
            return [dict(row) for row in cursor.fetchall()]

    def update_sync_profile(self, profile_id: int, **kwargs):
        """Update sync profile fields"""
        with self._get_connection() as conn:
            updates = []
            params = []

            for field in ['name', 'description', 'source_path', 'destination_path',
                          'exclude_patterns', 'protect_excluded', 'enabled']:
                if field in kwargs:
                    updates.append(f"{field} = ?")
                    params.append(kwargs[field])

            if updates:
                updates.append("updated_at = CURRENT_TIMESTAMP")
                params.append(profile_id)
                query = f"UPDATE sync_profiles SET {', '.join(updates)} WHERE id = ?"
                conn.execute(query, params)
                conn.commit()
                logger.info(f"Updated sync profile {profile_id}")

    def delete_sync_profile(self, profile_id: int):
        """Delete sync profile and associated schedules"""
        # Delete associated schedules first
        deleted_schedules = self._delete_schedules_for_target('sync_profile', profile_id)

        # Delete the profile
        with self._get_connection() as conn:
            conn.execute("DELETE FROM sync_profiles WHERE id = ?", (profile_id,))
            conn.commit()
            logger.info(f"Deleted sync profile {profile_id} (and {deleted_schedules} schedule(s))")

    # ===== BACKUP OPERATION OPERATIONS =====

    def create_backup_operation(self, profile_id: int, operation_type: str,
                               status: str, start_time: datetime, **kwargs) -> int:
        """Create backup operation record"""
        with self._get_connection() as conn:
            cursor = conn.execute("""
                INSERT INTO backup_operations (
                    profile_id, operation_type, backup_name,
                    status, start_time
                ) VALUES (?, ?, ?, ?, ?)
            """, (
                profile_id,
                operation_type,
                kwargs.get('backup_name'),
                status,
                start_time
            ))
            conn.commit()
            operation_id = cursor.lastrowid
            logger.debug(f"Created backup operation {operation_id} for profile {profile_id}")
            return operation_id

    def update_backup_operation(self, operation_id: int, **kwargs):
        """Update backup operation record"""
        with self._get_connection() as conn:
            updates = []
            params = []

            for field in ['status', 'end_time', 'backup_name', 'files_total',
                         'files_linked', 'files_copied', 'files_in_store',
                         'total_size_bytes', 'error_message', 'log_path']:
                if field in kwargs:
                    updates.append(f"{field} = ?")
                    params.append(kwargs[field])

            if updates:
                params.append(operation_id)
                query = f"UPDATE backup_operations SET {', '.join(updates)} WHERE id = ?"
                conn.execute(query, params)
                conn.commit()

    def get_recent_backup_operations(self, limit: int = 20, hostname: str = None) -> List[Dict[str, Any]]:
        """Get recent backup operations, optionally filtered by hostname via profile"""
        with self._get_runtime_connection() as conn:
            if hostname:
                cursor = conn.execute("""
                    SELECT bo.* FROM backup_operations bo
                    JOIN backup_profiles bp ON bo.profile_id = bp.id
                    WHERE bp.hostname = ? OR bp.hostname IS NULL OR bp.hostname = ''
                    ORDER BY bo.start_time DESC
                    LIMIT ?
                """, (hostname, limit))
            else:
                cursor = conn.execute("""
                    SELECT * FROM backup_operations
                    ORDER BY start_time DESC
                    LIMIT ?
                """, (limit,))
            return [dict(row) for row in cursor.fetchall()]

    def get_last_backup_operation(self, profile_id: int) -> Optional[Dict[str, Any]]:
        """Get most recent backup operation for profile"""
        with self._get_runtime_connection() as conn:
            cursor = conn.execute("""
                SELECT * FROM backup_operations
                WHERE profile_id = ? AND operation_type = 'backup'
                ORDER BY start_time DESC
                LIMIT 1
            """, (profile_id,))
            row = cursor.fetchone()
            return dict(row) if row else None

    # ===== SYNC OPERATION OPERATIONS =====

    def create_sync_operation(self, profile_id: int, status: str, start_time: datetime) -> int:
        """Create sync operation record"""
        with self._get_connection() as conn:
            cursor = conn.execute("""
                INSERT INTO sync_operations (
                    profile_id, status, start_time
                ) VALUES (?, ?, ?)
            """, (profile_id, status, start_time))
            conn.commit()
            operation_id = cursor.lastrowid
            logger.debug(f"Created sync operation {operation_id} for profile {profile_id}")
            return operation_id

    def update_sync_operation(self, operation_id: int, **kwargs):
        """Update sync operation record"""
        with self._get_connection() as conn:
            updates = []
            params = []

            for field in ['status', 'end_time', 'files_scanned', 'files_copied',
                         'files_deleted', 'bytes_total', 'error_message']:
                if field in kwargs:
                    updates.append(f"{field} = ?")
                    params.append(kwargs[field])

            if updates:
                params.append(operation_id)
                query = f"UPDATE sync_operations SET {', '.join(updates)} WHERE id = ?"
                conn.execute(query, params)
                conn.commit()

    def get_recent_sync_operations(self, limit: int = 20) -> List[Dict[str, Any]]:
        """Get recent sync operations"""
        with self._get_connection() as conn:
            cursor = conn.execute("""
                SELECT * FROM sync_operations
                ORDER BY start_time DESC
                LIMIT ?
            """, (limit,))
            return [dict(row) for row in cursor.fetchall()]

    def get_last_sync_operation(self, profile_id: int) -> Optional[Dict[str, Any]]:
        """Get most recent sync operation for profile"""
        with self._get_connection() as conn:
            cursor = conn.execute("""
                SELECT * FROM sync_operations
                WHERE profile_id = ?
                ORDER BY start_time DESC
                LIMIT 1
            """, (profile_id,))
            row = cursor.fetchone()
            return dict(row) if row else None

    def has_running_sync_operation(self, profile_id: int) -> bool:
        """Check if there's a running sync operation for this profile"""
        with self._get_connection() as conn:
            cursor = conn.execute("""
                SELECT COUNT(*) as count FROM sync_operations
                WHERE profile_id = ? AND status = 'running'
            """, (profile_id,))
            row = cursor.fetchone()
            return row['count'] > 0 if row else False

    # ===== PROJECT CONFIG OPERATIONS =====

    def create_project_config(self, name: str, source_path: str, server_base_path: str, **kwargs) -> int:
        """Create a new project configuration for versioning"""
        with self._get_connection() as conn:
            cursor = conn.execute("""
                INSERT INTO project_configs (
                    name, description, source_path, server_base_path,
                    exclude_patterns, enabled
                ) VALUES (?, ?, ?, ?, ?, ?)
            """, (
                name,
                kwargs.get('description'),
                source_path,
                server_base_path,
                kwargs.get('exclude_patterns', ''),
                kwargs.get('enabled', True)
            ))
            conn.commit()
            project_id = cursor.lastrowid
            logger.info(f"Created project config '{name}' with ID {project_id}")
            return project_id

    def get_project_config(self, project_id: int) -> Optional[Dict[str, Any]]:
        """Get project configuration by ID"""
        with self._get_connection() as conn:
            cursor = conn.execute("SELECT * FROM project_configs WHERE id = ?", (project_id,))
            row = cursor.fetchone()
            return dict(row) if row else None

    def get_all_project_configs(self, enabled_only: bool = False) -> List[Dict[str, Any]]:
        """Get all project configurations"""
        with self._get_connection() as conn:
            if enabled_only:
                cursor = conn.execute("SELECT * FROM project_configs WHERE enabled = 1 ORDER BY name")
            else:
                cursor = conn.execute("SELECT * FROM project_configs ORDER BY name")
            return [dict(row) for row in cursor.fetchall()]

    def update_project_config(self, project_id: int, **kwargs):
        """Update project configuration fields"""
        with self._get_connection() as conn:
            updates = []
            params = []

            for field in ['name', 'description', 'source_path', 'server_base_path',
                          'exclude_patterns', 'enabled']:
                if field in kwargs:
                    updates.append(f"{field} = ?")
                    params.append(kwargs[field])

            if updates:
                updates.append("updated_at = CURRENT_TIMESTAMP")
                params.append(project_id)
                query = f"UPDATE project_configs SET {', '.join(updates)} WHERE id = ?"
                conn.execute(query, params)
                conn.commit()
                logger.info(f"Updated project config {project_id}")

    def delete_project_config(self, project_id: int):
        """Delete project configuration (CASCADE deletes all versions)"""
        with self._get_connection() as conn:
            conn.execute("DELETE FROM project_configs WHERE id = ?", (project_id,))
            conn.commit()
            logger.info(f"Deleted project config {project_id}")

    # ===== PROJECT VERSION OPERATIONS =====

    def create_project_version(self, project_id: int, version_name: str,
                              zip_filename: str, **kwargs) -> int:
        """Create a new project version record"""
        with self._get_connection() as conn:
            cursor = conn.execute("""
                INSERT INTO project_versions (
                    project_id, version_name, description, zip_filename,
                    size_bytes, files_count, created_by
                ) VALUES (?, ?, ?, ?, ?, ?, ?)
            """, (
                project_id,
                version_name,
                kwargs.get('description'),
                zip_filename,
                kwargs.get('size_bytes', 0),
                kwargs.get('files_count', 0),
                kwargs.get('created_by', 'user')
            ))
            conn.commit()
            version_id = cursor.lastrowid
            logger.info(f"Created version '{version_name}' for project {project_id}")
            return version_id

    def get_project_version(self, version_id: int) -> Optional[Dict[str, Any]]:
        """Get project version by ID"""
        with self._get_connection() as conn:
            cursor = conn.execute("SELECT * FROM project_versions WHERE id = ?", (version_id,))
            row = cursor.fetchone()
            return dict(row) if row else None

    def get_project_versions(self, project_id: int, limit: int = None) -> List[Dict[str, Any]]:
        """Get all versions for a project, ordered by creation date (newest first)"""
        with self._get_connection() as conn:
            if limit:
                cursor = conn.execute("""
                    SELECT * FROM project_versions
                    WHERE project_id = ?
                    ORDER BY created_at DESC
                    LIMIT ?
                """, (project_id, limit))
            else:
                cursor = conn.execute("""
                    SELECT * FROM project_versions
                    WHERE project_id = ?
                    ORDER BY created_at DESC
                """, (project_id,))
            return [dict(row) for row in cursor.fetchall()]

    def get_latest_version(self, project_id: int) -> Optional[Dict[str, Any]]:
        """Get the most recent version for a project"""
        with self._get_connection() as conn:
            cursor = conn.execute("""
                SELECT * FROM project_versions
                WHERE project_id = ?
                ORDER BY created_at DESC
                LIMIT 1
            """, (project_id,))
            row = cursor.fetchone()
            return dict(row) if row else None

    def delete_project_version(self, version_id: int):
        """Delete a project version record"""
        with self._get_connection() as conn:
            conn.execute("DELETE FROM project_versions WHERE id = ?", (version_id,))
            conn.commit()
            logger.info(f"Deleted project version {version_id}")

    # ===== BACKUP RESTORE OPERATIONS =====

    def create_backup_restore(self, profile_id: int, backup_name: str,
                             restore_type: str, destination_path: str,
                             status: str, start_time: datetime, **kwargs) -> int:
        """Create backup restore record"""
        with self._get_connection() as conn:
            cursor = conn.execute("""
                INSERT INTO backup_restores (
                    profile_id, backup_name, restore_type, source_path,
                    destination_path, status, start_time
                ) VALUES (?, ?, ?, ?, ?, ?, ?)
            """, (
                profile_id,
                backup_name,
                restore_type,
                kwargs.get('source_path'),
                destination_path,
                status,
                start_time
            ))
            conn.commit()
            return cursor.lastrowid

    def update_backup_restore(self, restore_id: int, **kwargs):
        """Update backup restore record"""
        with self._get_connection() as conn:
            updates = []
            params = []

            for field in ['status', 'end_time', 'files_restored', 'error_message']:
                if field in kwargs:
                    updates.append(f"{field} = ?")
                    params.append(kwargs[field])

            if updates:
                params.append(restore_id)
                query = f"UPDATE backup_restores SET {', '.join(updates)} WHERE id = ?"
                conn.execute(query, params)
                conn.commit()

    # ===== NOTIFICATION OPERATIONS =====

    def create_notification(self, execution_id: int, notification_type: str, status: str, message: str, **kwargs) -> int:
        """Create notification record"""
        with self._get_connection() as conn:
            cursor = conn.execute("""
                INSERT INTO notifications (execution_id, notification_type, status, message, sent_at, error)
                VALUES (?, ?, ?, ?, ?, ?)
            """, (
                execution_id,
                notification_type,
                status,
                message,
                kwargs.get('sent_at', datetime.now() if status == 'sent' else None),
                kwargs.get('error')
            ))
            conn.commit()
            return cursor.lastrowid
nDatabase = SchedulerDatabase


# Alias for unified daemon
Database = SchedulerDatabase