import os
import django
import pandas as pd
from datetime import datetime

# Setup Django environment
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'simplify.settings')
django.setup()

from user.models import UserAnswers

# Fetch all records excluding those with null 'option' and 'answer'
records = UserAnswers.objects.exclude(option__isnull=True, answer__isnull=True)

# Get the field names, excluding 'next_question'
fields = [field.name for field in UserAnswers._meta.fields if field.name != 'next']

# Create a list of dictionaries to convert to DataFrame
data = []
for record in records:
    record_data = {}
    for field in fields:
        value = getattr(record, field)
        # Convert timezone-aware datetimes to naive datetimes
        if isinstance(value, datetime) and value.tzinfo is not None:
            value = value.astimezone(None).replace(tzinfo=None)
        record_data[field] = value
    data.append(record_data)

# Convert to DataFrame
df = pd.DataFrame(data, columns=fields)

# Save to Excel file
output_file = 'UserAnswers.xlsx'
df.to_excel(output_file, index=False)

print(f"Data has been written to {output_file}")