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)