from sqlalchemy import Column, Integer, String, Date, Float, ForeignKey, UniqueConstraint, Index, DateTime, Text, JSON
from sqlalchemy.orm import relationship
from .database import Base


class Market(Base):
    __tablename__ = "market"
    id = Column(Integer, primary_key=True)
    name = Column(String)

    instruments = relationship("Instrument", back_populates="market")

class Sector(Base):
    __tablename__ = "sector"
    id = Column(Integer, primary_key=True)
    name = Column(String)

    instruments = relationship("Instrument", back_populates="sector")

class MarketExchnges(Base):
    __tablename__ = "market_exchanges"
    id = Column(Integer, primary_key=True)
    index_code = Column(String, unique=True, nullable=False)
    index_name = Column(String)
    echange_name = Column(String)
    market_name = Column(String)

    instruments = relationship("Instrument", back_populates="market_exchange")

class Instrument(Base):
    __tablename__ = "instruments"
    id = Column(Integer, primary_key=True)
    market_exchange_id = Column(Integer, ForeignKey("market_exchanges.id"), nullable=True)
    market_id = Column(Integer, ForeignKey("market.id"), nullable=True)
    sector_id = Column(Integer, ForeignKey("sector.id"), nullable=True)
    short_code = Column(Integer, nullable=True)
    symbol = Column(String, unique=True, nullable=False)
    ric = Column(String, unique=True, nullable=True)
    ticker = Column(String, unique=True, nullable=True)
    name = Column(String)

    # __table_args__ = (
    #     Index('unique_ric_not_null', 'ric', unique=True, postgresql_where=ric.isnot(None)),
    # )

    market_exchange = relationship("MarketExchnges", back_populates="instruments")
    market = relationship("Market", back_populates="instruments")
    sector = relationship("Sector", back_populates="instruments")

class KPI(Base):
    __tablename__ = "kpis"
    id = Column(Integer, primary_key=True)
    code = Column(String, unique=True, nullable=False)
    name = Column(String)

class KPIValue(Base):
    __tablename__ = "kpi_values"
    id = Column(Integer, primary_key=True)
    instrument_id = Column(Integer, ForeignKey("instruments.id"))
    kpi_id = Column(Integer, ForeignKey("kpis.id"))
    frequency = Column(String, nullable=False)
    date = Column(Date, nullable=False)
    value = Column(Float)
    currency = Column(String)

    __table_args__ = (UniqueConstraint('instrument_id', 'kpi_id', 'frequency', 'date', name='unique_kpi_entry'),)

class InstrumentPriceValue(Base):
    __tablename__ = "instrument_price_values"
    id = Column(Integer, primary_key=True)
    instrument_for_id = Column(Integer, ForeignKey("instruments.id"), nullable=True)
    date = Column(Date, nullable=False)
    value = Column(Float)

    __table_args__ = (UniqueConstraint('instrument_for_id', 'date', name='uq_instrument_price_entry'),)


class IndexPriceValue(Base):
    __tablename__ = "index_price_values"
    id = Column(Integer, primary_key=True)
    market_exchange_for_id = Column(Integer, ForeignKey("market_exchanges.id"), nullable=True)
    date = Column(Date, nullable=False)
    value = Column(Float)

    __table_args__ = (UniqueConstraint('market_exchange_for_id', 'date', name='uq_index_price_entry'),)

from datetime import datetime

class ApiLog(Base):
    __tablename__ = "api_logs"

    id = Column(Integer, primary_key=True, index=True)
    endpoint = Column(String(500), index=True, nullable=False)
    method = Column(String(10), nullable=False)
    ip_address = Column(String(45), nullable=False)
    user_agent = Column(String(500), nullable=True)
    timestamp = Column(DateTime, default=datetime.utcnow, index=True)

    # Store everything as JSON for maximum flexibility
    request_data = Column(JSON, nullable=True)
    response_status = Column(Integer, nullable=True)
    error_message = Column(Text, nullable=True)

    def as_dict(self):
        return {
            "id": self.id,
            "endpoint": self.endpoint,
            "method": self.method,
            "ip_address": self.ip_address,
            "user_agent": self.user_agent,
            "timestamp": self.timestamp.isoformat(),
            "request_data": self.request_data or {},
            "response_status": self.response_status,
            "error_message": self.error_message
        }