
from datetime import datetime
import uuid
from sqlalchemy import (TIMESTAMP,
    Boolean, Column, Float, Integer, String, DateTime, Numeric, Index, func,ForeignKey,Enum
)
from sqlalchemy.dialects.postgresql import UUID  # Use PostgreSQL UUID support
from src.constants import ChartTypeEnum
from src.core.database import Base
from sqlalchemy.orm import relationship
from sqlalchemy.dialects.postgresql import ARRAY



class GeneralLedgerMaster(Base):
    __tablename__ = "general_ledger_master"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4, index=True)
    general_ledger_code = Column(Numeric(50), nullable=False, unique=True)
    account_name = Column(String(255), nullable=False)
    category = Column(String(50), nullable=True)
    tags = Column(ARRAY(String), default=[]) 

    created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)
    updated_at = Column(DateTime(timezone=True), onupdate=func.now(), server_default=func.now(), nullable=False)

    def as_list(self):
        return [
            str(self.id),  # UUID converted to string for better readability
            float(self.general_ledger_code),  # Numeric to float
            self.account_name,
            self.category,
            self.tags,
            self.created_at.isoformat() if self.created_at else None,
            self.updated_at.isoformat() if self.updated_at else None,
        ]
    
    def as_dict(self):
        return {
            'id': str(self.id),
            'general_ledger_code': float(self.general_ledger_code),
            'account_name': self.account_name,
            'category': self.category,
            'tags': self.tags,
            'created_at': self.created_at.isoformat() if self.created_at else None,
            'updated_at': self.updated_at.isoformat() if self.updated_at else None,
        }



from sqlalchemy.dialects.postgresql import UUID, JSONB


class GeneralLedger(Base):
    __tablename__ = "general_ledger"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4, index=True)
    date = Column(DateTime, nullable=False)
    general_ledger_code = Column(Numeric(50), nullable=False)
    account_name = Column(String(255), nullable=False)
    category = Column(String(50), nullable=True)
    description = Column(String(500), nullable=True)
    debit = Column(Numeric(15, 2), nullable=False, default=0)
    credit = Column(Numeric(15, 2), nullable=False, default=0)
    is_active = Column(Boolean, nullable=False, default=True)
    entry_by = Column(String(255), nullable=False)
    entry_system_source = Column(String(255), nullable=False)
    created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)
    updated_at = Column(DateTime(timezone=True), onupdate=func.now(), server_default=func.now(), nullable=False)
    transaction_hash = Column(String, unique=True, index=True)

    integration_id = Column(UUID(as_uuid=True), ForeignKey("integration_history.id", ondelete="CASCADE"))
    integration_history = relationship("IntegrationHistory", back_populates="ledger_entries")

    extra_data = Column(JSONB, nullable=True)

    def __repr__(self):
        return f"<GeneralLedger(id={self.id}, date={self.date}, account_name={self.account_name})>"


    def as_list(self):
        return [
            str(self.id),
            self.date.isoformat() if self.date else None,
            float(self.general_ledger_code),
            self.account_name,
            self.category,
            self.description,
            float(self.debit),
            float(self.credit),
            self.entry_by,
            self.entry_system_source,
            self.created_at.isoformat() if self.created_at else None,
            self.updated_at.isoformat() if self.updated_at else None,
            self.extra_data if self.extra_data is not None else {},  # add extra_data at the end
        ]

    def as_dict(self):
        return {
            'id': str(self.id),
            'date': self.date.isoformat() if self.date else None,
            'general_ledger_code': float(self.general_ledger_code),
            'account_name': self.account_name,
            'category': self.category,
            'description': self.description,
            'debit': float(self.debit),
            'credit': float(self.credit),
            'entry_by': self.entry_by,
            'entry_system_source': self.entry_system_source,
            'created_at': self.created_at.isoformat() if self.created_at else None,
            'updated_at': self.updated_at.isoformat() if self.updated_at else None,
            'extra_data': self.extra_data if self.extra_data is not None else {},  # include extra_data here
        }

class IntegrationHistory(Base):
    __tablename__ = "integration_history"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    source_type = Column(String(50), nullable=False)  # e.g., "file", "api"
    source_identifier = Column(String(255), nullable=True)  # filename, API client ID, etc.
    status = Column(String(50), nullable=False)  # success, failed,
    message = Column(String(1000), nullable=True)  # optional details
    uploaded_user_id = Column(UUID(as_uuid=True))
    created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)

    ledger_entries = relationship(
    "GeneralLedger",
    back_populates="integration_history",
    cascade="all, delete-orphan"
    )

    def as_dict(self):
        return {
            "id": str(self.id),
            "source_type": self.source_type,
            "source_identifier": self.source_identifier,
            "status": self.status,
            "message": self.message,
            "uploaded_user_id": str(self.uploaded_user_id) if self.uploaded_user_id else None,
            "created_at": self.created_at.isoformat() if self.created_at else None
        }

    def as_list(self):
        return [
            str(self.id),
            self.source_type,
            self.source_identifier,
            self.status,
            self.message,
            str(self.uploaded_user_id) if self.uploaded_user_id else None,
            self.created_at.isoformat() if self.created_at else None
        ]


class MetricDefinition(Base):
    __tablename__ = "metric_definition"
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(100), nullable=False, unique=True)
    tags = Column(ARRAY(String), default=[])
    gl_codes = Column(ARRAY(Numeric(50)), default=[])
    amount_field = Column(String(10), default="debit")
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now(), server_default=func.now())


class DerivedMetric(Base):
    __tablename__ = "derived_metric"
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(100), nullable=False, unique=True)
    formula = Column(String(500), nullable=False)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now(), server_default=func.now())


class FPNAYearlyReport(Base):
    __tablename__ = "fpna_overview_report"
    # id = Column(Integer, primary_key=True, autoincrement=True)
    year = Column(Integer,primary_key= True ,  nullable=False)
    revenue_trend = Column(JSONB, nullable=True)
    expense_trend = Column(JSONB, nullable=True)
    net_profit_trend = Column(JSONB, nullable=True)
    gross_profit_trend = Column(JSONB, nullable=True)
    cashflow_trend = Column(JSONB ,nullable = True)
    head_count_trend= Column(JSONB , nullable = True)
    actual_vs_budget_trend = Column(JSONB , nullable = True)   
    # keymetrics = Column(JSONB, nullable=True)
    created_at = Column(TIMESTAMP, server_default=func.now())
    updated_at = Column(
        TIMESTAMP,
        server_default=func.now(),
        onupdate=func.now()
    )

class FPNAYearlyReport1(Base):
    __tablename__ = "fpna_overview_report1"
    year = Column(Integer,primary_key= True ,  nullable=False)
    metric_trend = Column(JSONB, nullable=True)
    created_at = Column(TIMESTAMP, server_default=func.now())
    updated_at = Column(TIMESTAMP, server_default=func.now(),onupdate=func.now())










class FinancialWidget(Base):
    __tablename__ = "Financial_widget"
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String,unique=True,nullable=False)
    category = Column(String)
    chart_Type=Column(Enum(ChartTypeEnum, type=String),default=ChartTypeEnum.BAR_CHART,nullable=False)
    enable_or_disable = Column(Boolean, default=True)
    position=Column(Integer,nullable=False)
    created_at=Column(DateTime(timezone=True),server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now(), server_default=func.now())

from sqlalchemy import Column, Integer, String, ForeignKey, DateTime, Text

from sqlalchemy.orm import relationship
from datetime import datetime


# Entity Table (General Ledger Entry)
class Entity(Base):
    __tablename__ = 'entity'

    entity_id = Column(Integer, primary_key=True)
    name = Column(String(255))
    created_at = Column(DateTime, default=datetime.utcnow)

    # Relationship to Attribute-Value pair
    values = relationship('Value', back_populates='entity')

    def __repr__(self):
        return f"<Entity(entity_id={self.entity_id}, name={self.name}, created_at={self.created_at})>"

# Attribute Table (Attribute like 'amount', 'description')
class Attribute(Base):
    __tablename__ = 'attribute'

    attribute_id = Column(Integer, primary_key=True)
    name = Column(String(255), unique=True)

    # Relationship to Value table
    values = relationship('Value', back_populates='attribute')

    def __repr__(self):
        return f"<Attribute(attribute_id={self.attribute_id}, name={self.name})>"

# Value Table (Stores the actual values for each attribute of the entity)
class Value(Base):
    __tablename__ = 'value'

    value_id = Column(Integer, primary_key=True)
    entity_id = Column(Integer, ForeignKey('entity.entity_id'))
    attribute_id = Column(Integer, ForeignKey('attribute.attribute_id'))
    value = Column(Text)

    # Relationships
    entity = relationship('Entity', back_populates='values')
    attribute = relationship('Attribute', back_populates='values')

    def __repr__(self):
        return f"<Value(entity_id={self.entity_id}, attribute_id={self.attribute_id}, value={self.value})>"


# History Table to track file uploads
class FileUploadHistory(Base):
    __tablename__ = 'file_upload_history'

    id = Column(Integer, primary_key=True)
    file_name = Column(String(255), nullable=False)
    file_path = Column(String(512), nullable=False)  # Path to where the file is stored
    file_type = Column(String(50))  # MIME type or file extension (e.g., .pdf, .csv)
    uploaded_by = Column(String(255), nullable=False)  # User who uploaded the file
    uploaded_at = Column(DateTime, default=datetime.utcnow)  # Timestamp of upload
    status = Column(String(50), default='success')  # Status of the upload (success, failed, etc.)

    def __repr__(self):
        return f"<FileUploadHistory(id={self.id}, file_name={self.file_name}, uploaded_by={self.uploaded_by}, status={self.status})>"


from sqlalchemy import Boolean, Text , Integer
from sqlalchemy import Boolean, Text

class TrackedJSONKey(Base):
    __tablename__ = "tracked_json_keys"

    id = Column(Integer, primary_key=True)
    table_name = Column(String, nullable=False)
    column_name = Column(String, nullable=False)
    key = Column(String, unique=True, nullable=False)
    created_at = Column(DateTime, server_default=func.now())
    
    # New columns you might add:
    has_null_values = Column(Boolean, default=False, nullable=False)  # Whether any rows have null or missing for this key
    data_type = Column(String, nullable=True)  # Optional: type of the JSON value (string, number, boolean, etc)
    description = Column(Text, nullable=True)  # Optional: a description of the key/purpose

# class FinanceGeneralLedger(Base):
#     __tablename__ = "finance_general_ledger_transactions"
#     id = Column(Integer, primary_key=True)
#     transaction_id = Column(Integer)
#     gl_ccount_id   = Column(String)
#     transaction_date = Column(Text)
#     amount_debit   = Column(Float)
#     amount_credit  = Column(Float)
#     currency       = Column(Text)
#     department_id  = Column(String)
#     cost_center_id = Column(String)
#     profit_center_id = Column(Integer)
#     entity_id      = Column(Integer)
#     customer_id    = Column(String)
#     vendor_id      = Column(String)
#     period         = Column(Text)
