from datetime import datetime
from uuid import uuid4
from sqlalchemy import (
    Column, Index, String, Float, Integer, DateTime, ForeignKey
)
from sqlalchemy.dialects.postgresql import UUID, JSONB
from sqlalchemy.orm import relationship
from src.core.database import Base  # Make sure Base is defined properly (e.g., declarative_base())

# --------------------------
# CRM Integration History
# --------------------------

class CRMIntegrationHistory(Base):
    __tablename__ = "crm_integration_history"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid4)
    source_type = Column(String(50), nullable=False)  # e.g., "file", "api"
    source_identifier = Column(String(255), nullable=True)  # e.g., filename
    status = Column(String(50), nullable=False)  # "success", "failed", etc.
    message = Column(String(1000), nullable=True)  # optional error message
    uploaded_user_id = Column(UUID(as_uuid=True), nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow, nullable=False)

    crm_entries = relationship(
        "CRM",
        back_populates="integration",
        cascade="all, delete-orphan",
        passive_deletes=True
    )

    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
        ]


# --------------------------
# HR Integration History
# --------------------------

class HRIntegrationHistory(Base):
    __tablename__ = "hr_integration_history"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid4)
    source_type = Column(String(50), nullable=False)
    source_identifier = Column(String(255), nullable=True)
    status = Column(String(50), nullable=False)
    message = Column(String(1000), nullable=True)
    uploaded_user_id = Column(UUID(as_uuid=True), nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow, nullable=False)

    hr_entries = relationship(
        "Employee",
        back_populates="integration",
        cascade="all, delete-orphan",
        passive_deletes=True
    )

    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
        ]


# --------------------------
# CRM Table
# --------------------------

class CRM(Base):
    __tablename__ = "crm"

    opportunity_id = Column(String, primary_key=True, index=True)
    integration_id = Column(UUID(as_uuid=True), ForeignKey("crm_integration_history.id", ondelete="CASCADE"))

    customer_name = Column(String, nullable=False)
    contact_person = Column(String, nullable=False)
    email = Column(String, nullable=False)
    product = Column(String, nullable=False)
    deal_amount = Column(Float, nullable=False)
    stage = Column(String, nullable=False)
    probability = Column(Integer, nullable=False)
    expected_close_date = Column(DateTime, nullable=False)
    region = Column(String, nullable=False)
    extra_data = Column(JSONB)
    user_id = Column(String, nullable=False)
    row_hash = Column(String(32), index=True, nullable=True)
    integration = relationship("CRMIntegrationHistory", back_populates="crm_entries")
    created_at = Column(DateTime, default=datetime.utcnow, nullable=False)

    __table_args__ = (
        Index("idx_crm_integration_id", "integration_id"),
    )

# --------------------------
# Employee Table
# --------------------------

class Employee(Base):
    __tablename__ = "employees"

    employee_id = Column(String, primary_key=True)
    integration_id = Column(UUID(as_uuid=True), ForeignKey("hr_integration_history.id", ondelete="CASCADE"))

    name = Column(String, nullable=False)
    email = Column(String, nullable=False)
    job_title = Column(String, nullable=False)
    department = Column(String, nullable=False)
    start_date = Column(DateTime, nullable=False)
    termination_date = Column(DateTime, nullable=True)
    base_salary = Column(Float, nullable=False)
    bonus = Column(Float, nullable=True)
    status = Column(String, nullable=False)
    location = Column(String, nullable=True)
    extra_data = Column(JSONB)
    user_id = Column(String, nullable=False)
    row_hash = Column(String(32), index=True, nullable=True)
    integration = relationship("HRIntegrationHistory", back_populates="hr_entries")
    __table_args__ = (
        Index("idx_employee_integration_id", "integration_id"),
    )
