import uuid
from django.db import models, connection


class BaseModel(models.Model):

    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    created_at = models.DateTimeField(auto_now_add=True, null=True, blank=True)
    updated_at = models.DateTimeField(auto_now=True, null=True, blank=True)

    class Meta:
        abstract = True


class MutualFund(BaseModel):
    """
    This model will store the mutual fund data
    """

    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    fund_name = models.CharField(max_length=200, unique=True)
    isin_number = models.CharField(max_length=50, unique=True, null=True)
    security_id = models.CharField(max_length=50, unique=True)
    data = models.JSONField(null=True)
    rank = models.IntegerField(unique=True, null=True)
    crisil_rank = models.IntegerField(null=True)
    aum = models.FloatField(null=True)
    expense_ratio = models.FloatField(null=True, blank=True)
    return_m12 = models.FloatField(null=True, blank=True)
    nav = models.FloatField(null=True, blank=True)

    @staticmethod
    def execute_raw_sql_query(sql_query):
        with connection.cursor() as cursor:
            cursor.execute(sql_query)
            columns = [col[0] for col in cursor.description]
            results = [dict(zip(columns, row)) for row in cursor.fetchall()]

        return results

    @classmethod
    def execute_query(cls, query):
        try:
            return cls.execute_raw_sql_query(query)
        except Exception as e:
            print("Error: ", repr(e))
            return []

class Stock(BaseModel):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    name = models.CharField(max_length=200)
    ltp = models.CharField(max_length=50, null=True)
    percentage_change = models.CharField(max_length=50, null=True)
    price_change = models.CharField(max_length=50, null=True)
    link = models.URLField(max_length=200, null=True)
    volume = models.CharField(max_length=50, null=True)
    data = models.JSONField(null=True)
    isin_number = models.CharField(max_length=50, unique=True, null=True)
    rank = models.IntegerField(unique=True, null=True)


class MFHoldings(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    isin_number = models.CharField(max_length=20, null=True, blank=True)
    security_id = models.CharField(max_length=20, null=True, blank=True)
    sector = models.CharField(max_length=50, null=True, blank=True)
    country = models.CharField(max_length=50, null=True, blank=True)
    currency = models.CharField(max_length=100, null=True, blank=True)
    weighting = models.FloatField(null=True, blank=True)
    sector_code = models.CharField(max_length=100, null=True, blank=True)
    holding_type = models.CharField(max_length=100, null=True, blank=True)
    market_value = models.FloatField(null=True, blank=True)
    stock_rating = models.CharField(max_length=100, null=True, blank=True)
    total_assets = models.FloatField(null=True, blank=True)
    currency_name = models.CharField(max_length=150, null=True, blank=True)
    holding_name = models.CharField(max_length=100, null=True, blank=True)
    holding_type = models.CharField(max_length=100, null=True, blank=True)
    holding_type_id = models.CharField(max_length=100, null=True, blank=True)
    number_of_shares = models.FloatField(null=True, blank=True)
    one_year_return = models.FloatField(null=True, blank=True)
    mutual_fund = models.ForeignKey(MutualFund, on_delete=models.CASCADE)

    def __str__(self):
        return f"{self.ticker} - {self.securityName}"


class MFVolatility(models.Model):
    VOLATILITY_CHOICES = (
        (1, "for1Year"),
        (3, "for3Year"),
        (5, "for5Year"),
        (10, "for10Year"),
        (15, "for15Year"),
    )
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    mutual_fund = models.ForeignKey(MutualFund, on_delete=models.CASCADE)
    year = models.CharField(max_length=100, choices=VOLATILITY_CHOICES)
    alpha = models.FloatField(null=True, blank=True)
    beta = models.FloatField(null=True, blank=True)
    sharpe_ratio = models.FloatField(null=True, blank=True)
    standard_deviation = models.FloatField(null=True, blank=True)