data_manager.py

"""
Database management module for LG NPS RAG System
Handles CSV import, SQLite operations, and data validation
"""

import sqlite3
import pandas as pd
import json
from pathlib import Path
from typing import Dict, Any, Optional, List
from sqlalchemy import create_engine, text
from loguru import logger
from config import Config


class DatabaseManager:
    """Database management class for NPS scorecard data"""
    
    def __init__(self):
        self.db_path = Config.DATABASE_PATH
        self.csv_path = Config.CSV_FILE_PATH
        self.engine = create_engine(Config.get_database_url())
        
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    def execute_query(self, query: str, params: Optional[Dict] = None) -> pd.DataFrame:
        """Execute SQL query and return results"""
        try:
            with self.engine.connect() as conn:
                result = pd.read_sql(text(query), conn, params=params or {})
            return result
            
        except Exception as e:
            # Raise to allow upstream agents/UI to surface real errors instead of
            # looking like a successful-but-empty result
            logger.error(f"Query execution failed: {e}")
            raise
    
    
    
    
    
    


    def get_all_table_names(self) -> List[str]:
        """Get all table names in the database"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
                tables = [row[0] for row in cursor.fetchall()]
                return tables
        except Exception as e:
            logger.error(f"Error getting table names: {e}")
            return []


# Utility function for easy database initialization



if __name__ == "__main__":
    # Test database initialization
    db_manager = DatabaseManager()
    tables = db_manager.get_all_table_names()
    print(f"Tables in database: {tables}")
    
    # result = initialize_database()
    # print(f"Database initialization result: {result}")