import pandas as pd
from sqlalchemy import create_engine, text
from sqlalchemy.exc import SQLAlchemyError
import warnings
warnings.filterwarnings('ignore')

class DatabaseManager:
    def __init__(self):
        self.conn_params = {
            'host': '192.168.2.50',
            'port': 5000,
            'database': 'mbg_pipeline',
            'user': 'mbg_dev',
            'password': 'mbg_dev_2025'
        }
        self.engine = None
        self.is_connected = False
    
    def connect(self):
        """Membuat koneksi ke database"""
        try:
            DATABASE_URL = f"postgresql://{self.conn_params['user']}:{self.conn_params['password']}@{self.conn_params['host']}:{self.conn_params['port']}/{self.conn_params['database']}"
            self.engine = create_engine(DATABASE_URL)
            
            # Test connection
            with self.engine.connect() as conn:
                conn.execute(text("SELECT 1"))
            
            self.is_connected = True
            print("✅ Connected to database successfully")
            return True
            
        except Exception as e:
            print(f"❌ Database connection failed: {e}")
            self.is_connected = False
            return False
    
    def load_articles(self, start_date=None, end_date=None):
        """Load data dari database dengan filter tanggal"""
        if not self.is_connected and not self.connect():
            return pd.DataFrame({"Error": ["Database connection failed"]})
        
        try:
            query = """
            SELECT id, "source", keyword, "date", title, "content", author, thumbnail, url, hash, created_at
            FROM public.scraped_articles
            """
            
            params = {}
            conditions = []
            
            if start_date:
                conditions.append("date >= :start_date")
                params['start_date'] = start_date
            
            if end_date:
                conditions.append("date < :end_date")
                params['end_date'] = end_date
            
            if conditions:
                query += " WHERE " + " AND ".join(conditions)
            
            query += " ORDER BY date DESC;"
            
            with self.engine.connect() as conn:
                df = pd.read_sql_query(text(query), conn, params=params)
            
            print(f"✅ Loaded {len(df)} rows from database")
            return df
            
        except Exception as e:
            print(f"❌ Error loading data from database: {e}")
            return pd.DataFrame({"Error": [f"Database error: {str(e)}"]})

# Singleton instance
db_manager = DatabaseManager()