#!/usr/bin/env python
"""
Load Transactions - Fixed Version
Fixes the project start_date constraint issue.
"""
import os
import sys
import django
import pandas as pd
from datetime import datetime
from decimal import Decimal
import uuid

# Add the project directory to Python path
sys.path.append(os.path.dirname(os.path.abspath(__file__)))

# Set Django settings
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'dubai_analytics.settings')

# Setup Django
django.setup()

from apps.analytics.models import Project, Transaction

def safe_get(row, key, default=None):
    """Safely get value from pandas row, handling NaN values."""
    try:
        value = row.get(key, default)
        if pd.isna(value) or value == '' or str(value).lower() == 'nan':
            return default
        return value
    except:
        return default

def safe_decimal(value, default=0):
    """Safely convert to Decimal."""
    try:
        if pd.isna(value) or value == '' or str(value).lower() == 'nan':
            return Decimal(str(default))
        return Decimal(str(value))
    except:
        return Decimal(str(default))

def safe_datetime(value):
    """Safely convert to datetime."""
    try:
        if pd.isna(value) or value == '' or str(value).lower() == 'nan':
            return None
        return pd.to_datetime(value)
    except:
        return None

def safe_str(value, default=''):
    """Safely convert to string."""
    try:
        if pd.isna(value) or value == '' or str(value).lower() == 'nan':
            return default
        return str(value).strip()
    except:
        return default

def load_transactions_fixed(csv_path, batch_size=10):
    """Load transactions data with fixed project creation."""
    print("💼 Loading transactions data (fixed version)...")
    try:
        df = pd.read_csv(csv_path)
        print(f"   Found {len(df)} transaction records")
        
        total_created = 0
        total_errors = 0
        
        for i in range(0, min(batch_size, len(df))):
            row = df.iloc[i]
            try:
                transaction_number = safe_str(row.get('TRANSACTION_NUMBER', ''))
                if not transaction_number:
                    total_errors += 1
                    continue
                
                # Get or create project with proper start_date
                project = None
                project_name = safe_str(row.get('PROJECT_EN', ''))
                if project_name:
                    project, created = Project.objects.get_or_create(
                        project_number=safe_str(row.get('PROJECT_NUMBER', '')),
                        defaults={
                            'project_name_en': project_name,
                            'start_date': datetime.now(),  # Provide required start_date
                            'project_status': 'ACTIVE',
                            'area_en': safe_str(row.get('AREA_EN', '')),
                            'zone_en': safe_str(row.get('ZONE_EN', '')),
                        }
                    )
                
                transaction, created = Transaction.objects.get_or_create(
                    transaction_number=transaction_number,
                    defaults={
                        'instance_date': safe_datetime(row.get('INSTANCE_DATE')) or datetime.now(),
                        'group': safe_str(row.get('GROUP_EN', 'Sale')),
                        'procedure': safe_str(row.get('PROCEDURE_EN', '')),
                        'is_offplan': safe_str(row.get('IS_OFFPLAN_EN', 'Ready')),
                        'is_freehold': safe_str(row.get('IS_FREE_HOLD_EN', 'Free Hold')),
                        'usage': safe_str(row.get('USAGE_EN', 'Residential')),
                        'area_en': safe_str(row.get('AREA_EN', '')),
                        'property_type': safe_str(row.get('PROP_TYPE_EN', 'Unit')),
                        'property_sub_type': safe_str(row.get('PROP_SB_TYPE_EN', '')),
                        'transaction_value': safe_decimal(row.get('TRANS_VALUE', 0)),
                        'procedure_area': safe_decimal(row.get('PROCEDURE_AREA', 0)),
                        'actual_area': safe_decimal(row.get('ACTUAL_AREA', 0)),
                        'rooms': safe_str(row.get('ROOMS_EN', '')),
                        'parking': int(safe_str(row.get('PARKING', 0)) or 0),
                        'nearest_metro': safe_str(row.get('NEAREST_METRO_EN', '')),
                        'nearest_mall': safe_str(row.get('NEAREST_MALL_EN', '')),
                        'nearest_landmark': safe_str(row.get('NEAREST_LANDMARK_EN', '')),
                        'total_buyer': int(safe_str(row.get('TOTAL_BUYER', 0)) or 0),
                        'total_seller': int(safe_str(row.get('TOTAL_SELLER', 0)) or 0),
                        'master_project': safe_str(row.get('MASTER_PROJECT_EN', '')),
                        'project': project,
                    }
                )
                if created:
                    total_created += 1
                    print(f"     ✅ Created transaction: {transaction_number}")
            except Exception as e:
                print(f"     ❌ Error creating transaction {safe_str(row.get('TRANSACTION_NUMBER', 'unknown'))}: {str(e)[:100]}")
                total_errors += 1
        
        print(f"   💼 Transactions Summary: {total_created} created, {total_errors} errors")
        return total_created
    except Exception as e:
        print(f"   ❌ Error loading transactions: {e}")
        return 0

def main():
    """Main function to load transactions."""
    print("=" * 60)
    print("  Load Transactions - Fixed Version")
    print("=" * 60)
    print()
    
    csv_path = "sample data/transactions.csv"
    if not os.path.exists(csv_path):
        print(f"❌ File {csv_path} not found!")
        return
    
    created = load_transactions_fixed(csv_path, batch_size=10)
    
    print(f"\n✅ Transactions loading completed!")
    print(f"📊 Total transactions created: {created}")

if __name__ == '__main__':
    main()