from sqlalchemy import Column, Integer, String, ForeignKey, DateTime, Table
from sqlalchemy.orm import relationship
from app.db.database import Base
import datetime

# Junction Tables
project_specialties = Table(
    'project_specialties',
    Base.metadata,
    Column('project_id', Integer, ForeignKey('projects.id'), primary_key=True),
    Column('specialty_id', Integer, ForeignKey('specialties.id'), primary_key=True)
)

project_contractors = Table(
    'project_contractors',
    Base.metadata,
    Column('project_id', Integer, ForeignKey('projects.id'), primary_key=True),
    Column('contractor_id', Integer, ForeignKey('contractors.id'), primary_key=True)
)

class Project(Base):
    __tablename__ = "projects"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, index=True, nullable=False)
    code = Column(String, unique=True, index=True)
    location = Column(String)
    start_date = Column(DateTime)
    end_date = Column(DateTime)
    status = Column(String, default="active")
    parent_id = Column(Integer, ForeignKey("projects.id"), nullable=True)

    # Relationships
    parent = relationship("Project", remote_side=[id], back_populates="subprojects")
    subprojects = relationship("Project", back_populates="parent")
    activities = relationship("Activity", back_populates="project")
    specialties = relationship("app.models.specialty.Specialty", secondary=project_specialties)
    contractors = relationship("app.models.contractor.Contractor", secondary=project_contractors)