from sqlalchemy import Column, Integer, String, Date, Float, ForeignKey, UniqueConstraint, Index, DateTime, Text, JSON
from sqlalchemy.orm import relationship
from app.db.database import Base
from sqlalchemy import Column, Integer, String, Boolean
from sqlalchemy.ext.declarative import declarative_base

# Base = declarative_base()

class AdminUser(Base):
    __tablename__ = "admin_users"

    id = Column(Integer, primary_key=True)
    username = Column(String(50), unique=True)
    password = Column(String(255))
    is_active = Column(Boolean, default=True)
    is_superuser = Column(Boolean, default=False)

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
        }