seed_data.py

#!/usr/bin/env python3
"""
Seed script for mutual funds database.
Inserts sample data into the finances_fund table.
"""

import asyncio
import asyncpg
import uuid
from datetime import date, datetime
from decimal import Decimal
import os
from dotenv import load_dotenv

load_dotenv()

# Database connection settings
POSTGRES_HOST = os.getenv("POSTGRES_HOST", "localhost")
POSTGRES_PORT = int(os.getenv("POSTGRES_PORT", 5432))
POSTGRES_DATABASE = os.getenv("POSTGRES_DATABASE", "mutual_funds")
POSTGRES_USER = os.getenv("POSTGRES_USER")
POSTGRES_PASSWORD = os.getenv("POSTGRES_PASSWORD")

async def seed_data():
    """Insert sample data into finances_fund table."""
    
    # Connect to database
    conn = await asyncpg.connect(
        user=POSTGRES_USER,
        password=POSTGRES_PASSWORD,
        database=POSTGRES_DATABASE,
        host=POSTGRES_HOST,
        port=POSTGRES_PORT
    )
    
    try:
        # Check if table exists
        table_exists = await conn.fetchval("""
            SELECT EXISTS (
                SELECT FROM information_schema.tables 
                WHERE table_schema = 'public' 
                AND table_name = 'finances_fund'
            );
        """)
        
        if not table_exists:
            print("❌ finances_fund table does not exist. Please create it first.")
            return
        
        # Clear existing data (if any)
        await conn.execute("DELETE FROM finances_fund")
        
        # Sample data
        sample_funds = [
            {
                'fund_id': str(uuid.uuid4()),
                'name': 'ICICI Prudential Bluechip Fund',
                'full_name': 'ICICI Prudential Bluechip Fund - Direct Plan - Growth',
                'amc_name': 'ICICI Prudential Mutual Fund',
                'amc_short_name': 'ICICI Prudential',
                'asset_class': 'Equity',
                'sub_asset_class': 'Large Cap',
                'sebi_category_name': 'Large Cap Fund',
                'nav': Decimal('89.45'),
                'nav_date': date(2024, 1, 10),
                'aum': Decimal('45000000000'),  # 45,000 crores
                'return_1year': Decimal('0.18'),  # 18%
                'return_3year': Decimal('0.15'),  # 15%
                'return_5year': Decimal('0.12'),  # 12%
                'return_since_launch': Decimal('0.14'),
                'fund_expense_ratio': Decimal('0.52'),
                'risk_level': 'High',
                'fund_recommendation_rating': 4,
                'min_initial_investment': 5000,
                'is_sip_allowed': True,
                'is_active_status': True,
                'is_direct': True
            },
            {
                'fund_id': str(uuid.uuid4()),
                'name': 'SBI Small Cap Fund',
                'full_name': 'SBI Small Cap Fund - Direct Plan - Growth',
                'amc_name': 'SBI Mutual Fund',
                'amc_short_name': 'SBI',
                'asset_class': 'Equity',
                'sub_asset_class': 'Small Cap',
                'sebi_category_name': 'Small Cap Fund',
                'nav': Decimal('145.67'),
                'nav_date': date(2024, 1, 10),
                'aum': Decimal('12000000000'),  # 12,000 crores
                'return_1year': Decimal('0.28'),  # 28%
                'return_3year': Decimal('0.22'),  # 22%
                'return_5year': Decimal('0.19'),  # 19%
                'return_since_launch': Decimal('0.21'),
                'fund_expense_ratio': Decimal('0.65'),
                'risk_level': 'Very High',
                'fund_recommendation_rating': 5,
                'min_initial_investment': 5000,
                'is_sip_allowed': True,
                'is_active_status': True,
                'is_direct': True
            },
            {
                'fund_id': str(uuid.uuid4()),
                'name': 'HDFC Corporate Bond Fund',
                'full_name': 'HDFC Corporate Bond Fund - Direct Plan - Growth',
                'amc_name': 'HDFC Mutual Fund',
                'amc_short_name': 'HDFC',
                'asset_class': 'Debt',
                'sub_asset_class': 'Corporate Bond',
                'sebi_category_name': 'Corporate Bond Fund',
                'nav': Decimal('22.89'),
                'nav_date': date(2024, 1, 10),
                'aum': Decimal('8500000000'),  # 8,500 crores
                'return_1year': Decimal('0.06'),  # 6%
                'return_3year': Decimal('0.065'),  # 6.5%
                'return_5year': Decimal('0.07'),  # 7%
                'return_since_launch': Decimal('0.068'),
                'fund_expense_ratio': Decimal('0.35'),
                'risk_level': 'Low',
                'fund_recommendation_rating': 3,
                'min_initial_investment': 5000,
                'is_sip_allowed': True,
                'is_active_status': True,
                'is_direct': True
            },
            {
                'fund_id': str(uuid.uuid4()),
                'name': 'Axis Midcap Fund',
                'full_name': 'Axis Midcap Fund - Direct Plan - Growth',
                'amc_name': 'Axis Mutual Fund',
                'amc_short_name': 'Axis',
                'asset_class': 'Equity',
                'sub_asset_class': 'Mid Cap',
                'sebi_category_name': 'Mid Cap Fund',
                'nav': Decimal('67.23'),
                'nav_date': date(2024, 1, 10),
                'aum': Decimal('18000000000'),  # 18,000 crores
                'return_1year': Decimal('0.25'),  # 25%
                'return_3year': Decimal('0.18'),  # 18%
                'return_5year': Decimal('0.16'),  # 16%
                'return_since_launch': Decimal('0.17'),
                'fund_expense_ratio': Decimal('0.58'),
                'risk_level': 'High',
                'fund_recommendation_rating': 5,
                'min_initial_investment': 5000,
                'is_sip_allowed': True,
                'is_active_status': True,
                'is_direct': True
            },
            {
                'fund_id': str(uuid.uuid4()),
                'name': 'Mirae Asset Large Cap Fund',
                'full_name': 'Mirae Asset Large Cap Fund - Direct Plan - Growth',
                'amc_name': 'Mirae Asset Mutual Fund',
                'amc_short_name': 'Mirae Asset',
                'asset_class': 'Equity',
                'sub_asset_class': 'Large Cap',
                'sebi_category_name': 'Large Cap Fund',
                'nav': Decimal('112.34'),
                'nav_date': date(2024, 1, 10),
                'aum': Decimal('25000000000'),  # 25,000 crores
                'return_1year': Decimal('0.16'),  # 16%
                'return_3year': Decimal('0.14'),  # 14%
                'return_5year': Decimal('0.13'),  # 13%
                'return_since_launch': Decimal('0.135'),
                'fund_expense_ratio': Decimal('0.45'),
                'risk_level': 'High',
                'fund_recommendation_rating': 4,
                'min_initial_investment': 1000,
                'is_sip_allowed': True,
                'is_active_status': True,
                'is_direct': True
            }
        ]
        
        # Insert sample data using only the columns that exist
        for fund in sample_funds:
            try:
                await conn.execute("""
                    INSERT INTO finances_fund (
                        fund_id, name, full_name, amc_name, amc_short_name, 
                        asset_class, sub_asset_class, sebi_category_name,
                        nav, nav_date, aum, return_1year, return_3year, return_5year,
                        return_since_launch, fund_expense_ratio, risk_level,
                        fund_recommendation_rating, min_initial_investment,
                        is_sip_allowed, is_active_status, is_direct
                    ) VALUES (
                        $1, $2, $3, $4, $5, $6, $7, $8, $9, $10,
                        $11, $12, $13, $14, $15, $16, $17, $18, $19,
                        $20, $21, $22
                    )
                """, 
                fund['fund_id'], fund['name'], fund['full_name'], fund['amc_name'], fund['amc_short_name'],
                fund['asset_class'], fund['sub_asset_class'], fund['sebi_category_name'],
                fund['nav'], fund['nav_date'], fund['aum'], fund['return_1year'], fund['return_3year'], fund['return_5year'],
                fund['return_since_launch'], fund['fund_expense_ratio'], fund['risk_level'],
                fund['fund_recommendation_rating'], fund['min_initial_investment'],
                fund['is_sip_allowed'], fund['is_active_status'], fund['is_direct']
                )
                print(f"✅ Inserted: {fund['name']}")
            except Exception as e:
                print(f"❌ Failed to insert {fund['name']}: {e}")
        
        print(f"✅ Successfully inserted {len(sample_funds)} sample funds")
        
    except Exception as e:
        print(f"❌ Error: {e}")
        
    finally:
        await conn.close()

if __name__ == "__main__":
    asyncio.run(seed_data())