147 lines
5.3 KiB
Python
147 lines
5.3 KiB
Python
from datetime import datetime
|
|
from sqlalchemy import (
|
|
Column,
|
|
Integer,
|
|
String,
|
|
Date,
|
|
DateTime,
|
|
ForeignKey,
|
|
UniqueConstraint,
|
|
Numeric,
|
|
Boolean, # NEW
|
|
)
|
|
from sqlalchemy.orm import declarative_base
|
|
|
|
Base = declarative_base()
|
|
|
|
|
|
class User(Base):
|
|
__tablename__ = "users"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
username = Column(String(128), unique=True, nullable=False, index=True)
|
|
password_hash = Column(String(256), nullable=False)
|
|
|
|
|
|
class Employee(Base):
|
|
__tablename__ = "employees"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
name = Column(String(256), nullable=False, index=True)
|
|
|
|
# NEW: active status + optional termination date
|
|
# Default True ensures existing/new employees are treated as active.
|
|
is_active = Column(Boolean, nullable=False, default=True)
|
|
termination_date = Column(Date, nullable=True)
|
|
|
|
|
|
class TimesheetPeriod(Base):
|
|
__tablename__ = "timesheet_periods"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
period_start = Column(Date, nullable=False, index=True)
|
|
period_end = Column(Date, nullable=False, index=True)
|
|
name = Column(String(256), nullable=True)
|
|
created_at = Column(DateTime, nullable=False, default=datetime.utcnow, index=True)
|
|
|
|
|
|
class TimeEntry(Base):
|
|
__tablename__ = "time_entries"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
employee_id = Column(Integer, ForeignKey("employees.id"), nullable=False, index=True)
|
|
timesheet_id = Column(Integer, ForeignKey("timesheet_periods.id"), nullable=False, index=True)
|
|
|
|
work_date = Column(Date, nullable=False, index=True)
|
|
clock_in = Column(DateTime, nullable=True)
|
|
clock_out = Column(DateTime, nullable=True)
|
|
|
|
pto_clock_in_backup = Column(DateTime, nullable=True)
|
|
pto_clock_out_backup = Column(DateTime, nullable=True)
|
|
|
|
total_hours = Column(Numeric(12, 2), default=0, nullable=False)
|
|
break_hours = Column(Numeric(12, 2), default=0, nullable=False)
|
|
|
|
pto_hours = Column(Numeric(12, 2), default=0, nullable=False)
|
|
pto_type = Column(String(64), nullable=True)
|
|
|
|
holiday_hours = Column(Numeric(12, 2), default=0, nullable=False)
|
|
bereavement_hours = Column(Numeric(12, 2), default=0, nullable=False)
|
|
|
|
hours_paid = Column(Numeric(12, 2), default=0, nullable=False)
|
|
|
|
|
|
class WeekAssignment(Base):
|
|
__tablename__ = "week_assignments"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
timesheet_id = Column(Integer, ForeignKey("timesheet_periods.id"), nullable=False, index=True)
|
|
|
|
period_start = Column(Date, nullable=False)
|
|
period_end = Column(Date, nullable=False)
|
|
|
|
day_date = Column(Date, nullable=False, index=True)
|
|
week_number = Column(Integer, nullable=False)
|
|
|
|
|
|
class EmployeePeriodSetting(Base):
|
|
__tablename__ = "employee_period_settings"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
employee_id = Column(Integer, ForeignKey("employees.id"), nullable=False, index=True)
|
|
timesheet_id = Column(Integer, ForeignKey("timesheet_periods.id"), nullable=False, index=True)
|
|
|
|
period_start = Column(Date, nullable=False)
|
|
period_end = Column(Date, nullable=False)
|
|
|
|
carry_over_hours = Column(Numeric(12, 2), default=0, nullable=False)
|
|
|
|
|
|
class TimesheetStatus(Base):
|
|
__tablename__ = "timesheet_status"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
timesheet_id = Column(Integer, ForeignKey("timesheet_periods.id"), nullable=False, index=True)
|
|
employee_id = Column(Integer, ForeignKey("employees.id"), nullable=False, index=True)
|
|
|
|
period_start = Column(Date, nullable=False)
|
|
period_end = Column(Date, nullable=False)
|
|
|
|
status = Column(String(32), default="pending", nullable=False)
|
|
submitted_at = Column(DateTime, default=datetime.utcnow, nullable=True)
|
|
|
|
|
|
class DuplicateReview(Base):
|
|
__tablename__ = "duplicate_reviews"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
timesheet_id = Column(Integer, ForeignKey("timesheet_periods.id"), nullable=False, index=True)
|
|
employee_id = Column(Integer, ForeignKey("employees.id"), nullable=False, index=True)
|
|
work_date = Column(Date, nullable=False, index=True)
|
|
|
|
__table_args__ = (
|
|
UniqueConstraint("timesheet_id", "employee_id", "work_date", name="uix_dup_review"),
|
|
)
|
|
|
|
|
|
# PTO tracker (per-year starting balance + per-year manual adjustments)
|
|
class PTOAccount(Base):
|
|
__tablename__ = "pto_accounts"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
employee_id = Column(Integer, ForeignKey("employees.id"), nullable=False, index=True)
|
|
year = Column(Integer, nullable=True, index=True) # per-year balance; filled/required by app logic
|
|
starting_balance = Column(Numeric(12, 2), default=0, nullable=False)
|
|
created_at = Column(DateTime, default=datetime.utcnow, nullable=False)
|
|
updated_at = Column(DateTime, default=datetime.utcnow, nullable=False)
|
|
|
|
|
|
class PTOAdjustment(Base):
|
|
__tablename__ = "pto_adjustments"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
employee_id = Column(Integer, ForeignKey("employees.id"), nullable=False, index=True)
|
|
year = Column(Integer, nullable=True, index=True) # per-year adjustment; filled/required by app logic
|
|
hours = Column(Numeric(12, 2), nullable=False) # positive or negative
|
|
note = Column(String(255), nullable=True)
|
|
created_at = Column(DateTime, default=datetime.utcnow, nullable=False) |