export_csv.py

"""
AlertHub Feedback Data Exporter
===============================

Exports complete processed feedback data to CSV format.

To use:
    python export_csv.py
"""

import csv
import os
import psycopg2
import json
from datetime import datetime
from psycopg2.extras import RealDictCursor
from dotenv import load_dotenv

# Load environment variables
load_dotenv()

# PostgreSQL credentials
PG_HOST = os.getenv("PG_HOST")
PG_PORT = os.getenv("PG_PORT")
PG_DB = os.getenv("PG_DB")
PG_USER = os.getenv("PG_USER")
PG_PASSWORD = os.getenv("PG_PASSWORD")

def export_feedback_data():
    """Export all processed feedback data to a single CSV file"""
    try:
        # Create exports directory if it doesn't exist
        exports_dir = 'exports'
        os.makedirs(exports_dir, exist_ok=True)
        
        # Use fixed filename, will be overwritten each time
        csv_path = os.path.join(exports_dir, "feedback_data.csv")
        
        # Connect to PostgreSQL
        conn = psycopg2.connect(
            host=PG_HOST,
            port=PG_PORT,
            database=PG_DB,
            user=PG_USER,
            password=PG_PASSWORD
        )
        
        with conn.cursor(cursor_factory=RealDictCursor) as cursor:
            # Query to get all processed feedback data
            query = """
            SELECT 
                submission_id,
                agree_to_followup,
                followup_email,
                feedback_text as original_feedback,
                detected_language,
                language_name,
                translated_text,
                sentiment,
                primary_category,
                pillar,
                confidence,
                subcategories,
                urgency,
                urgency_score,
                complexity,
                complexity_score,
                keywords,
                text_length,
                word_count
            FROM feedback_submissions
            ORDER BY submission_id
            """
            
            cursor.execute(query)
            rows = cursor.fetchall()
            
            if not rows:
                print("No feedback data found to export")
                return None
            
            # Convert rows to dictionaries and process JSON fields
            processed_rows = []
            for row in rows:
                row_dict = dict(row)
                
                # Parse subcategories JSON if exists
                if row_dict['subcategories']:
                    try:
                        subcategories_list = json.loads(row_dict['subcategories'])
                        row_dict['subcategories'] = '; '.join(subcategories_list) if subcategories_list else ''
                    except:
                        row_dict['subcategories'] = str(row_dict['subcategories'])
                else:
                    row_dict['subcategories'] = ''
                
                # Convert boolean fields 
                row_dict['agree_to_followup'] = 'Yes' if row_dict['agree_to_followup'] else 'No'
                
                # Clean up None values
                for key, value in row_dict.items():
                    if value is None:
                        row_dict[key] = ''
                
                processed_rows.append(row_dict)
            
            # Define the column order and headers 
            fieldnames = [
                'submission_id',
                'agree_to_followup',
                'followup_email',
                'original_feedback',
                'detected_language',
                'language_name',
                'translated_text',
                'sentiment',
                'primary_category',
                'pillar',
                'confidence',
                'subcategories',
                'urgency',
                'urgency_score',
                'complexity',
                'complexity_score',
                'keywords',
                'text_length',
                'word_count'
            ]
            
            # Write to CSV
            with open(csv_path, 'w', newline='', encoding='utf-8') as csvfile:
                writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
                writer.writeheader()
                writer.writerows(processed_rows)
            
            print(f"Successfully exported {len(processed_rows)} feedback records")
            print(f"File location: {os.path.abspath(csv_path)}")
            
            return csv_path
            
    except Exception as e:
        print(f"Error exporting data: {e}")
        return None
    finally:
        if 'conn' in locals():
            conn.close()

def main():
    """Main export function"""
    print("AlertHub Feedback Data Exporter")
    print("=" * 50)
    
    csv_file = export_feedback_data()
    
    if csv_file:
        print(f"\nExport completed successfully!")
        print(f"CSV file: {os.path.basename(csv_file)}")
        
        # Show file size
        file_size = os.path.getsize(csv_file)
        file_size_mb = round(file_size / (1024 * 1024), 2)
        print(f"File size: {file_size_mb} MB")
    else:
        print("\nExport failed!")

if __name__ == "__main__":
    main()