from sqlalchemy import Column, String, Numeric, Date, Integer, Boolean, Text, DateTime, ForeignKey, CheckConstraint
from sqlalchemy.sql import func
from sqlalchemy.orm import relationship
from database import Base

class Project(Base):
    __tablename__ = "projects"
    
    project_id = Column(String(20), primary_key=True, index=True)
    project_name = Column(String(255), nullable=False)
    client_name = Column(String(255), nullable=False, index=True)
    contract_value_aed = Column(Numeric(15, 2), nullable=False)
    planned_cost_aed = Column(Numeric(15, 2), nullable=False)
    project_start_date = Column(Date, nullable=False)
    project_end_date = Column(Date, nullable=False)
    project_type = Column(String(50), nullable=False, index=True)
    project_status = Column(String(50), nullable=False, index=True)
    current_phase = Column(String(100))
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())
    
    # Relationships
    timesheets = relationship("Timesheet", back_populates="project")
    milestones = relationship("Milestone", back_populates="project", cascade="all, delete-orphan")
    invoices = relationship("Invoice", back_populates="project")
    subcontractors = relationship("Subcontractor", back_populates="project", cascade="all, delete-orphan")
    staff_allocations = relationship("StaffAllocation", back_populates="project", cascade="all, delete-orphan")


class Employee(Base):
    __tablename__ = "employees"
    
    employee_id = Column(String(20), primary_key=True, index=True)
    employee_name = Column(String(255), nullable=False)
    department = Column(String(100), nullable=False, index=True)
    role = Column(String(100), nullable=False, index=True)
    hourly_rate_aed = Column(Numeric(10, 2), nullable=False)
    employment_type = Column(String(50), nullable=False)
    start_date = Column(Date, nullable=False)
    end_date = Column(Date)
    cost_category = Column(String(100), nullable=False, index=True)
    is_active = Column(Boolean, default=True, index=True)
    email = Column(String(255))
    phone = Column(String(50))
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())
    
    # Relationships
    timesheets = relationship("Timesheet", back_populates="employee")
    staff_allocations = relationship("StaffAllocation", back_populates="employee")


class Timesheet(Base):
    __tablename__ = "timesheets"
    
    record_id = Column(String(20), primary_key=True, index=True)
    date = Column(Date, nullable=False, index=True)
    employee_id = Column(String(20), ForeignKey("employees.employee_id"), nullable=False, index=True)
    project_id = Column(String(20), ForeignKey("projects.project_id"), nullable=False, index=True)
    hours_worked = Column(Numeric(5, 2), nullable=False)
    billable_hours = Column(Numeric(5, 2), nullable=False)
    work_category = Column(String(100), nullable=False)
    task_description = Column(String(500))
    is_approved = Column(Boolean, default=False, index=True)
    approved_by = Column(String(20))
    approved_at = Column(DateTime)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())
    
    # Relationships
    employee = relationship("Employee", back_populates="timesheets")
    project = relationship("Project", back_populates="timesheets")


class Milestone(Base):
    __tablename__ = "milestones"
    
    milestone_id = Column(Integer, primary_key=True, autoincrement=True)
    project_id = Column(String(20), ForeignKey("projects.project_id", ondelete="CASCADE"), nullable=False, index=True)
    milestone_name = Column(String(255), nullable=False)
    milestone_order = Column(Integer, nullable=False)
    planned_date = Column(Date, nullable=False, index=True)
    actual_date = Column(Date)
    status = Column(String(50), nullable=False, index=True)
    completion_percentage = Column(Integer, default=0)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())
    
    # Relationships
    project = relationship("Project", back_populates="milestones")
    staff_allocations = relationship("StaffAllocation", back_populates="milestone")


class Invoice(Base):
    __tablename__ = "invoices"
    
    invoice_id = Column(String(20), primary_key=True, index=True)
    project_id = Column(String(20), ForeignKey("projects.project_id"), nullable=False, index=True)
    invoice_date = Column(Date, nullable=False)
    invoice_amount_aed = Column(Numeric(15, 2), nullable=False)
    due_date = Column(Date, nullable=False, index=True)
    payment_date = Column(Date, index=True)
    payment_status = Column(String(50), nullable=False, index=True)
    days_outstanding = Column(Integer)
    milestone_reference = Column(String(255))
    paid_amount_aed = Column(Numeric(15, 2), default=0)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())
    
    # Relationships
    project = relationship("Project", back_populates="invoices")


class Subcontractor(Base):
    __tablename__ = "subcontractors"
    
    subcontractor_id = Column(Integer, primary_key=True, autoincrement=True)
    project_id = Column(String(20), ForeignKey("projects.project_id", ondelete="CASCADE"), nullable=False, index=True)
    subcontractor_name = Column(String(255), nullable=False, index=True)
    service_type = Column(String(100), nullable=False, index=True)
    contract_amount_aed = Column(Numeric(15, 2), nullable=False)
    amount_invoiced_aed = Column(Numeric(15, 2), default=0)
    payment_status = Column(String(50), nullable=False, index=True)
    work_category = Column(String(100), nullable=False)
    contract_start_date = Column(Date)
    contract_end_date = Column(Date)
    contact_person = Column(String(255))
    contact_email = Column(String(255))
    contact_phone = Column(String(50))
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())
    
    # Relationships
    project = relationship("Project", back_populates="subcontractors")


class StaffAllocation(Base):
    __tablename__ = "staff_allocation"
    
    allocation_id = Column(Integer, primary_key=True, autoincrement=True)
    project_id = Column(String(20), ForeignKey("projects.project_id", ondelete="CASCADE"), nullable=False, index=True)
    milestone_id = Column(Integer, ForeignKey("milestones.milestone_id", ondelete="SET NULL"), index=True)
    milestone_name = Column(String(255))
    employee_id = Column(String(20), ForeignKey("employees.employee_id"), nullable=False, index=True)
    employee_name = Column(String(255), nullable=False)
    role = Column(String(100), nullable=False)
    category = Column(String(100))
    hours_allocated = Column(Numeric(10, 2), nullable=False)
    hours_worked = Column(Numeric(10, 2), default=0)
    hourly_rate_aed = Column(Numeric(10, 2), nullable=False)
    skill_match_score = Column(Integer)
    availability_status = Column(String(50))
    performance_rating = Column(String(50))
    start_date = Column(Date, nullable=False, index=True)
    end_date = Column(Date)
    notes = Column(Text)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())
    
    # Relationships
    project = relationship("Project", back_populates="staff_allocations")
    milestone = relationship("Milestone", back_populates="staff_allocations")
    employee = relationship("Employee", back_populates="staff_allocations")