database_connection.py

import requests
import pandas as pd
from typing import Dict, List, Optional
import json

class DatabaseConnection:
    def __init__(self, base_url: str = "https://database-1-xzm0.onrender.com"):
        self.base_url = base_url
        self.session = requests.Session()
    
    def get_student_feedback_counts(self) -> pd.DataFrame:
        """Fetch student feedback data from the database"""
        try:
            url = f"{self.base_url}/student_feedback_counts"
            response = self.session.get(url, timeout=10)  # Add timeout
            response.raise_for_status()
            
            data = response.json()
            if isinstance(data, list):
                return pd.DataFrame(data)
            elif isinstance(data, dict) and 'feedback_counts' in data:
                # Handle nested structure
                feedback_data = data['feedback_counts']
                if isinstance(feedback_data, list):
                    return pd.DataFrame(feedback_data)
                else:
                    return pd.DataFrame([feedback_data])
            else:
                return pd.DataFrame([data])
        except requests.exceptions.Timeout:
            print("⚠️ Database request timed out")
            return pd.DataFrame()
        except requests.exceptions.ConnectionError:
            print("⚠️ Could not connect to database")
            return pd.DataFrame()
        except Exception as e:
            print(f"Error fetching data: {e}")
            return pd.DataFrame()
    
    def add_feedback(self, course: str, stanine: int, gwa: float, strand: str, 
                    rating: str, hobbies: str) -> bool:
        """Add new feedback to the database"""
        try:
            url = f"{self.base_url}/student_feedback_counts"
            data = {
                'course': course,
                'stanine': stanine,
                'gwa': gwa,
                'strand': strand,
                'rating': rating,
                'hobbies': hobbies,
                'count': 1
            }
            
            print(f"Attempting to add feedback: {course}, rating: {rating}")
            response = self.session.post(url, json=data, timeout=10)
            response.raise_for_status()
            
            result = response.json()
            print(f"✅ Feedback added successfully: {result}")
            return True
            
        except requests.exceptions.Timeout:
            print("⚠️ Database request timed out")
            return False
        except requests.exceptions.ConnectionError:
            print("⚠️ Could not connect to database")
            return False
        except Exception as e:
            print(f"❌ Error adding feedback: {e}")
            return False
    
    def update_feedback_count(self, feedback_id: int, count: int) -> bool:
        """Update the count for existing feedback"""
        try:
            url = f"{self.base_url}/student_feedback_counts/{feedback_id}"
            data = {"count": count}
            response = self.session.put(url, json=data, timeout=10)
            response.raise_for_status()
            return True
        except requests.exceptions.Timeout:
            print("⚠️ Database request timed out")
            return False
        except requests.exceptions.ConnectionError:
            print("⚠️ Could not connect to database")
            return False
        except Exception as e:
            print(f"Error updating feedback count: {e}")
            return False
    
    def get_available_courses(self) -> List[str]:
        """Fetch available courses from the database"""
        try:
            url = f"{self.base_url}/courses"
            response = self.session.get(url, timeout=10)
            response.raise_for_status()
            
            data = response.json()
            if isinstance(data, list):
                # Extract course names from the data
                courses = []
                for item in data:
                    if isinstance(item, dict) and 'name' in item:
                        courses.append(item['name'])
                    elif isinstance(item, str):
                        courses.append(item)
                return courses
            else:
                return []
        except requests.exceptions.Timeout:
            print("⚠️ Database request timed out")
            return []
        except requests.exceptions.ConnectionError:
            print("⚠️ Could not connect to database")
            return []
        except Exception as e:
            print(f"Error fetching courses: {e}")
            return []