#!/usr/bin/env python3
"""
Database utilities for PHP migration tracking system.
Provides common database operations and schema management.
"""

import sqlite3
import os
from datetime import datetime
from typing import List, Dict, Optional, Tuple, Any

class MigrationDB:
    """Database manager for PHP migration tracking."""
    
    def __init__(self, db_path: str = None):
        if db_path is None:
            db_path = os.path.join(os.path.dirname(__file__), 'migration.db')
        self.db_path = db_path
        self.init_database()
    
    def get_connection(self) -> sqlite3.Connection:
        """Get database connection with row factory."""
        conn = sqlite3.connect(self.db_path)
        conn.row_factory = sqlite3.Row
        return conn

    def _migrate_status_constraint(self, conn: sqlite3.Connection):
        """Migrate status CHECK constraint to include new statuses."""
        # Check if files table exists and has old constraint
        cursor = conn.execute("SELECT sql FROM sqlite_master WHERE type='table' AND name='files'")
        result = cursor.fetchone()
        if not result:
            return  # Table doesn't exist yet, will be created with new constraint

        create_sql = result['sql']
        # Check if the old constraint is present (only has old statuses)
        if "'remove'" in create_sql and "'Done'" not in create_sql:
            print("Migrating database status constraint...")

            # Create new table with updated constraint
            conn.execute('''
                CREATE TABLE files_new (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    primary_folder TEXT,
                    sub_folder TEXT,
                    file_name TEXT NOT NULL,
                    status TEXT DEFAULT 'new' CHECK(status IN ('new', 'keep', 'archive', 'remove', 'Done', 'Key Page', 'Done Key Page')),
                    php8_rewritten INTEGER DEFAULT 0 CHECK(php8_rewritten IN (0, 1)),
                    tested INTEGER DEFAULT 0 CHECK(tested IN (0, 1)),
                    links_scanned INTEGER DEFAULT 0 CHECK(links_scanned IN (0, 1)),
                    file_exists INTEGER DEFAULT 1 CHECK(file_exists IN (0, 1)),
                    checked INTEGER DEFAULT 0 CHECK(checked IN (0, 1)),
                    key_file INTEGER DEFAULT 0 CHECK(key_file IN (0, 1)),
                    isLive INTEGER DEFAULT 0 CHECK(isLive IN (0, 1)),
                    notes TEXT,
                    last_updated DATETIME DEFAULT CURRENT_TIMESTAMP,
                    UNIQUE(primary_folder, sub_folder, file_name)
                )
            ''')

            # Copy data from old table
            conn.execute('''
                INSERT INTO files_new (id, primary_folder, sub_folder, file_name, status,
                                     php8_rewritten, tested, links_scanned, file_exists, checked, key_file, isLive, notes, last_updated)
                SELECT id, primary_folder, sub_folder, file_name, status,
                       php8_rewritten, tested, links_scanned, file_exists, checked, 0, 0, notes, last_updated
                FROM files
            ''')

            # Drop old table and rename new one
            conn.execute('DROP TABLE files')
            conn.execute('ALTER TABLE files_new RENAME TO files')

            # Recreate indexes
            conn.execute('CREATE INDEX IF NOT EXISTS idx_files_status ON files(status)')
            conn.execute('CREATE INDEX IF NOT EXISTS idx_files_flags ON files(php8_rewritten, tested, links_scanned)')
            conn.execute('CREATE INDEX IF NOT EXISTS idx_links_source ON links(source_file_id)')

            print("Database migration completed successfully!")

    def init_database(self):
        """Initialize database with schema."""
        with self.get_connection() as conn:
            # Create files table (without constraint initially)
            conn.execute('''
                CREATE TABLE IF NOT EXISTS files (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    primary_folder TEXT,
                    sub_folder TEXT,
                    file_name TEXT NOT NULL,
                    status TEXT DEFAULT 'new',
                    php8_rewritten INTEGER DEFAULT 0 CHECK(php8_rewritten IN (0, 1)),
                    tested INTEGER DEFAULT 0 CHECK(tested IN (0, 1)),
                    links_scanned INTEGER DEFAULT 0 CHECK(links_scanned IN (0, 1)),
                    file_exists INTEGER DEFAULT 1 CHECK(file_exists IN (0, 1)),
                    checked INTEGER DEFAULT 0 CHECK(checked IN (0, 1)),
                    notes TEXT,
                    last_updated DATETIME DEFAULT CURRENT_TIMESTAMP,
                    UNIQUE(primary_folder, sub_folder, file_name)
                )
            ''')

            # Check if we need to migrate the status constraint
            self._migrate_status_constraint(conn)
            
            # Add file_exists column to existing databases
            try:
                conn.execute('ALTER TABLE files ADD COLUMN file_exists INTEGER DEFAULT 1 CHECK(file_exists IN (0, 1))')
            except sqlite3.OperationalError:
                # Column already exists
                pass
            
            # Add checked column to existing databases
            try:
                conn.execute('ALTER TABLE files ADD COLUMN checked INTEGER DEFAULT 0 CHECK(checked IN (0, 1))')
            except sqlite3.OperationalError:
                # Column already exists
                pass

            # Add key_file column to existing databases
            try:
                conn.execute('ALTER TABLE files ADD COLUMN key_file INTEGER DEFAULT 0 CHECK(key_file IN (0, 1))')
            except sqlite3.OperationalError:
                # Column already exists
                pass

            # Add isLive column to existing databases
            try:
                conn.execute('ALTER TABLE files ADD COLUMN isLive INTEGER DEFAULT 0 CHECK(isLive IN (0, 1))')
            except sqlite3.OperationalError:
                # Column already exists
                pass
            
            # Create links table
            conn.execute('''
                CREATE TABLE IF NOT EXISTS links (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    source_file_id INTEGER NOT NULL,
                    target_path TEXT NOT NULL,
                    created_on DATETIME DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (source_file_id) REFERENCES files(id) ON DELETE CASCADE
                )
            ''')
            
            # Create indexes for better performance
            conn.execute('CREATE INDEX IF NOT EXISTS idx_files_status ON files(status)')
            conn.execute('CREATE INDEX IF NOT EXISTS idx_files_flags ON files(php8_rewritten, tested, links_scanned)')
            conn.execute('CREATE INDEX IF NOT EXISTS idx_links_source ON links(source_file_id)')
            
            conn.commit()
    
    def add_file(self, primary_folder: str, sub_folder: str, file_name: str,
                  status: str = 'new', php8_rewritten: int = 0, tested: int = 0,
                  links_scanned: int = 0, file_exists: int = 1, checked: int = 0, key_file: int = 0, isLive: int = 0, notes: str = '') -> int:
        """Add or update a file record. Returns file ID."""
        with self.get_connection() as conn:
            try:
                cursor = conn.execute('''
                    INSERT INTO files (primary_folder, sub_folder, file_name, status,
                                      php8_rewritten, tested, links_scanned, file_exists, checked, key_file, isLive, notes, last_updated)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                ''', (primary_folder, sub_folder, file_name, status, php8_rewritten,
                      tested, links_scanned, file_exists, checked, key_file, isLive, notes, datetime.now().isoformat()))
                return cursor.lastrowid
            except sqlite3.IntegrityError:
                # File exists, update it
                conn.execute('''
                    UPDATE files
                    SET status = ?, php8_rewritten = ?, tested = ?, links_scanned = ?,
                        file_exists = ?, checked = ?, key_file = ?, isLive = ?, notes = ?, last_updated = ?
                    WHERE primary_folder = ? AND sub_folder = ? AND file_name = ?
                ''', (status, php8_rewritten, tested, links_scanned, file_exists, checked, key_file, isLive, notes,
                      datetime.now().isoformat(), primary_folder, sub_folder, file_name))
                
                # Get the file ID
                cursor = conn.execute('''
                    SELECT id FROM files
                    WHERE primary_folder = ? AND sub_folder = ? AND file_name = ?
                ''', (primary_folder, sub_folder, file_name))
                row = cursor.fetchone()
                if row is None:
                    raise Exception(f"Failed to find file after update: {primary_folder}/{sub_folder}/{file_name}")
                return row['id']
    
    def get_file_by_id(self, file_id: int) -> Optional[Dict]:
        """Get file record by ID."""
        with self.get_connection() as conn:
            cursor = conn.execute('SELECT * FROM files WHERE id = ?', (file_id,))
            row = cursor.fetchone()
            return dict(row) if row else None
    
    def get_file_by_path(self, primary_folder: str, sub_folder: str, file_name: str) -> Optional[Dict]:
        """Get file record by path components."""
        with self.get_connection() as conn:
            cursor = conn.execute('''
                SELECT * FROM files 
                WHERE primary_folder = ? AND sub_folder = ? AND file_name = ?
            ''', (primary_folder, sub_folder, file_name))
            row = cursor.fetchone()
            return dict(row) if row else None
    
    def update_file_status(self, file_id: int, status: str) -> bool:
        """Update file status."""
        with self.get_connection() as conn:
            cursor = conn.execute('''
                UPDATE files SET status = ?, last_updated = ? WHERE id = ?
            ''', (status, datetime.now().isoformat(), file_id))
            return cursor.rowcount > 0
    
    def update_file_flags(self, file_id: int, php8_rewritten: int = None,
                         tested: int = None, links_scanned: int = None,
                         file_exists: int = None, checked: int = None, key_file: int = None, isLive: int = None) -> bool:
        """Update file completion flags."""
        updates = []
        params = []
        
        if php8_rewritten is not None:
            updates.append('php8_rewritten = ?')
            params.append(php8_rewritten)
        if tested is not None:
            updates.append('tested = ?')
            params.append(tested)
        if links_scanned is not None:
            updates.append('links_scanned = ?')
            params.append(links_scanned)
        if file_exists is not None:
            updates.append('file_exists = ?')
            params.append(file_exists)
        if checked is not None:
            updates.append('checked = ?')
            params.append(checked)
        if key_file is not None:
            updates.append('key_file = ?')
            params.append(key_file)
        if isLive is not None:
            updates.append('isLive = ?')
            params.append(isLive)
        
        if not updates:
            return False
        
        updates.append('last_updated = ?')
        params.append(datetime.now().isoformat())
        params.append(file_id)
        
        with self.get_connection() as conn:
            cursor = conn.execute(f'''
                UPDATE files SET {', '.join(updates)} WHERE id = ?
            ''', params)
            return cursor.rowcount > 0
    
    def update_file_notes(self, file_id: int, notes: str) -> bool:
        """Update file notes."""
        with self.get_connection() as conn:
            cursor = conn.execute('''
                UPDATE files SET notes = ?, last_updated = ? WHERE id = ?
            ''', (notes, datetime.now().isoformat(), file_id))
            return cursor.rowcount > 0
    
    def get_files_by_status(self, status: str) -> List[Dict]:
        """Get all files with specific status."""
        with self.get_connection() as conn:
            cursor = conn.execute('SELECT * FROM files WHERE status = ? ORDER BY primary_folder, sub_folder, file_name', (status,))
            return [dict(row) for row in cursor.fetchall()]
    
    def get_files_needing_work(self) -> List[Dict]:
        """Get files that need PHP8 rewrite or testing."""
        with self.get_connection() as conn:
            cursor = conn.execute('''
                SELECT * FROM files 
                WHERE status = 'keep' AND (php8_rewritten = 0 OR tested = 0)
                ORDER BY primary_folder, sub_folder, file_name
            ''')
            return [dict(row) for row in cursor.fetchall()]
    
    def get_files_needing_links_scan(self) -> List[Dict]:
        """Get files that need link scanning."""
        with self.get_connection() as conn:
            cursor = conn.execute('''
                SELECT * FROM files 
                WHERE status = 'keep' AND links_scanned = 0
                ORDER BY primary_folder, sub_folder, file_name
            ''')
            return [dict(row) for row in cursor.fetchall()]
    
    def get_all_files(self, order_by: str = 'primary_folder, sub_folder, file_name') -> List[Dict]:
        """Get all files with optional ordering."""
        with self.get_connection() as conn:
            cursor = conn.execute(f'SELECT * FROM files ORDER BY {order_by}')
            return [dict(row) for row in cursor.fetchall()]
    
    def search_files(self, search_term: str) -> List[Dict]:
        """Search files by name or path."""
        with self.get_connection() as conn:
            cursor = conn.execute('''
                SELECT * FROM files 
                WHERE file_name LIKE ? OR primary_folder LIKE ? OR sub_folder LIKE ?
                ORDER BY primary_folder, sub_folder, file_name
            ''', (f'%{search_term}%', f'%{search_term}%', f'%{search_term}%'))
            return [dict(row) for row in cursor.fetchall()]
    
    def add_link(self, source_file_id: int, target_path: str) -> int:
        """Add a link between files."""
        with self.get_connection() as conn:
            cursor = conn.execute('''
                INSERT INTO links (source_file_id, target_path, created_on)
                VALUES (?, ?, ?)
            ''', (source_file_id, target_path, datetime.now().isoformat()))
            return cursor.lastrowid
    
    def get_links_for_file(self, file_id: int) -> List[Dict]:
        """Get all links from a file."""
        with self.get_connection() as conn:
            cursor = conn.execute('SELECT * FROM links WHERE source_file_id = ?', (file_id,))
            return [dict(row) for row in cursor.fetchall()]
    
    def clear_links_for_file(self, file_id: int) -> int:
        """Clear all links for a file. Returns number of deleted links."""
        with self.get_connection() as conn:
            cursor = conn.execute('DELETE FROM links WHERE source_file_id = ?', (file_id,))
            return cursor.rowcount
    
    def get_statistics(self) -> Dict[str, Any]:
        """Get database statistics."""
        with self.get_connection() as conn:
            stats = {}
            
            # Total files
            cursor = conn.execute('SELECT COUNT(*) as count FROM files')
            stats['total_files'] = cursor.fetchone()['count']
            
            # Files by status
            cursor = conn.execute('SELECT status, COUNT(*) as count FROM files GROUP BY status')
            stats['by_status'] = {row['status']: row['count'] for row in cursor.fetchall()}
            
            # Completion stats
            cursor = conn.execute('SELECT COUNT(*) as count FROM files WHERE php8_rewritten = 1')
            stats['php8_completed'] = cursor.fetchone()['count']
            
            cursor = conn.execute('SELECT COUNT(*) as count FROM files WHERE tested = 1')
            stats['tested_completed'] = cursor.fetchone()['count']
            
            cursor = conn.execute('SELECT COUNT(*) as count FROM files WHERE links_scanned = 1')
            stats['links_scanned'] = cursor.fetchone()['count']
            
            # Total links
            cursor = conn.execute('SELECT COUNT(*) as count FROM links')
            stats['total_links'] = cursor.fetchone()['count']
            
            return stats
    
    def get_file_path(self, file_record: Dict) -> str:
        """Get full file path from file record."""
        parts = []
        if file_record['primary_folder']:
            parts.append(file_record['primary_folder'])
        if file_record['sub_folder']:
            parts.append(file_record['sub_folder'])
        parts.append(file_record['file_name'])
        return '/'.join(parts)
    
    def get_full_filesystem_path(self, file_record: Dict, base_path: str = None) -> str:
        """Get full filesystem path for a file record."""
        if base_path is None:
            # Use absolute path resolution
            script_dir = os.path.dirname(os.path.abspath(__file__))
            base_dir = os.path.dirname(script_dir)
            base_path = os.path.join(base_dir, 'vmserver10', 'intranet')
        
        rel_path = self.get_file_path(file_record)
        return os.path.join(base_path, rel_path)
    
    def verify_file_exists(self, file_id: int, base_path: str = None) -> bool:
        """Check if a file exists on filesystem and update database."""
        file_record = self.get_file_by_id(file_id)
        if not file_record:
            return False
        
        file_path = self.get_full_filesystem_path(file_record, base_path)
        exists = os.path.exists(file_path)
        
        # Update database with current status
        self.update_file_flags(file_id, file_exists=int(exists))
        
        return exists
    
    def verify_all_files_exist(self, base_path: str = None) -> Dict[str, int]:
        """Verify all files exist and update database. Returns statistics."""
        all_files = self.get_all_files()
        stats = {'total': len(all_files), 'exists': 0, 'missing': 0}
        
        for file_record in all_files:
            file_path = self.get_full_filesystem_path(file_record, base_path)
            exists = os.path.exists(file_path)
            
            # Update database
            self.update_file_flags(file_record['id'], file_exists=int(exists))
            
            if exists:
                stats['exists'] += 1
            else:
                stats['missing'] += 1
        
        return stats
    
    def get_missing_files(self) -> List[Dict]:
        """Get all files that don't exist on filesystem."""
        with self.get_connection() as conn:
            cursor = conn.execute('''
                SELECT * FROM files
                WHERE file_exists = 0
                ORDER BY primary_folder, sub_folder, file_name
            ''')
            return [dict(row) for row in cursor.fetchall()]
    
    def get_files_by_existence(self, exists: bool = True) -> List[Dict]:
        """Get files by existence status."""
        exists_flag = 1 if exists else 0
        with self.get_connection() as conn:
            cursor = conn.execute('''
                SELECT * FROM files
                WHERE file_exists = ?
                ORDER BY primary_folder, sub_folder, file_name
            ''', (exists_flag,))
            return [dict(row) for row in cursor.fetchall()]
    
    def update_file_checked(self, file_id: int, checked: bool) -> bool:
        """Update file checked status."""
        with self.get_connection() as conn:
            cursor = conn.execute('''
                UPDATE files SET checked = ?, last_updated = ? WHERE id = ?
            ''', (int(checked), datetime.now().isoformat(), file_id))
            return cursor.rowcount > 0
    
    def get_checked_files(self) -> List[Dict]:
        """Get all checked files."""
        with self.get_connection() as conn:
            cursor = conn.execute('''
                SELECT * FROM files
                WHERE checked = 1
                ORDER BY primary_folder, sub_folder, file_name
            ''')
            return [dict(row) for row in cursor.fetchall()]


def main():
    """Test database functionality."""
    db = MigrationDB()
    print("Database initialized successfully!")
    
    # Test adding a file
    file_id = db.add_file('databases', 'agenda', 'test.php', 'new')
    print(f"Added test file with ID: {file_id}")
    
    # Test getting statistics
    stats = db.get_statistics()
    print(f"Database statistics: {stats}")


if __name__ == '__main__':
    main()