from sqlalchemy import Column, Integer, String, Text, Float, Boolean, DateTime, Date, Time, JSON, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func
from database import Base
from datetime import datetime, timezone


def now_hkt():
    """Return current time in HKT timezone."""
    return datetime.now(timezone.utc)


class User(Base):
    __tablename__ = "users"
    
    id = Column(Integer, primary_key=True, index=True)
    email = Column(String(255), unique=True, index=True, nullable=False)
    password_hash = Column(String(255), nullable=False)
    name = Column(String(255), nullable=False)
    role = Column(String(50), default="user")  # admin, user
    is_active = Column(Boolean, default=True)
    created_at = Column(DateTime, default=now_hkt)
    updated_at = Column(DateTime, default=now_hkt, onupdate=now_hkt)
    deleted_at = Column(DateTime, nullable=True)


class Attendance(Base):
    __tablename__ = "attendance"
    
    id = Column(Integer, primary_key=True, index=True)
    employee_id = Column(String(100), index=True, nullable=False)
    employee_name = Column(String(255), nullable=False)
    department = Column(String(100), index=True)
    date = Column(Date, nullable=False)
    check_in = Column(Time, nullable=True)
    check_out = Column(Time, nullable=True)
    overtime_hours = Column(Float, default=0)
    leave_type = Column(String(50), nullable=True)  # annual, sick, unpaid, other
    leave_hours = Column(Float, default=0)
    status = Column(String(50), nullable=False)  # present, absent, late, leave
    remarks = Column(Text, nullable=True)
    custom_fields = Column(JSON, default={})
    created_by = Column(Integer, ForeignKey("users.id"))
    created_at = Column(DateTime, default=now_hkt)
    updated_at = Column(DateTime, default=now_hkt, onupdate=now_hkt)
    deleted_at = Column(DateTime, nullable=True)  # Soft delete


class Accident(Base):
    __tablename__ = "accidents"
    
    id = Column(Integer, primary_key=True, index=True)
    date = Column(Date, nullable=False)
    time = Column(Time, nullable=True)
    location = Column(String(255), nullable=False)
    employee_name = Column(String(255), nullable=False)
    employee_id = Column(String(100))
    department = Column(String(100))
    description = Column(Text, nullable=False)
    severity = Column(String(50), nullable=False)  # minor, moderate, serious, fatal
    medical_report = Column(Text, nullable=True)
    action_taken = Column(Text, nullable=True)
    responsible_person = Column(String(255), nullable=True)
    custom_fields = Column(JSON, default={})
    created_by = Column(Integer, ForeignKey("users.id"))
    created_at = Column(DateTime, default=now_hkt)
    updated_at = Column(DateTime, default=now_hkt, onupdate=now_hkt)
    deleted_at = Column(DateTime, nullable=True)  # Soft delete


class AttendanceCustomField(Base):
    __tablename__ = "attendance_custom_fields"
    
    id = Column(Integer, primary_key=True, index=True)
    field_name = Column(String(100), nullable=False)
    field_type = Column(String(50), nullable=False)  # text, number, date, select
    field_key = Column(String(100), unique=True, nullable=False)
    required = Column(Boolean, default=False)
    options = Column(JSON, default=[])  # For select type
    order = Column(Integer, default=0)
    created_at = Column(DateTime, default=now_hkt)


class AccidentCustomField(Base):
    __tablename__ = "accident_custom_fields"
    
    id = Column(Integer, primary_key=True, index=True)
    field_name = Column(String(100), nullable=False)
    field_type = Column(String(50), nullable=False)  # text, number, date, select
    field_key = Column(String(100), unique=True, nullable=False)
    required = Column(Boolean, default=False)
    options = Column(JSON, default=[])  # For select type
    order = Column(Integer, default=0)
    created_at = Column(DateTime, default=now_hkt)