from flask import Flask, request, jsonify
import pandas as pd
from sqlalchemy import create_engine
import os

app = Flask(__name__)

# Database connection
DATABASE_URL = os.environ.get('DATABASE_URL', 'postgresql://user:password@localhost:5432/database')
engine = create_engine(DATABASE_URL)

@app.route('/user/<int:user_id>', methods=['GET'])
def get_user_data(user_id):
    """Endpoint to fetch user data by ID"""
    try:
        # Query database for specific user
        query = f"SELECT * FROM your_table WHERE user_id = {user_id}"
        df = pd.read_sql(query, engine)
        
        if df.empty:
            return jsonify({'error': 'User not found'}), 404
        
        return jsonify(df.to_dict('records')), 200
    
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/user/<int:user_id>/csv', methods=['GET'])
def get_user_csv(user_id):
    """Endpoint to download user data as CSV"""
    try:
        query = f"SELECT * FROM your_table WHERE user_id = {user_id}"
        df = pd.read_sql(query, engine)
        
        if df.empty:
            return jsonify({'error': 'User not found'}), 404
        
        # Return as CSV
        return df.to_csv(index=False), 200, {
            'Content-Type': 'text/csv',
            'Content-Disposition': f'attachment; filename=user_{user_id}_data.csv'
        }
    
    except Exception as e:
        return jsonify({'error': str(e)}), 500

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5000)