from flask_login import UserMixin from extensions import db, login_manager from sqlalchemy.orm import relationship from datetime import datetime import uuid from flask_bcrypt import Bcrypt from extensions import db from datetime import datetime bcrypt = Bcrypt() @login_manager.user_loader def load_user(user_id): return Employee.query.get(int(user_id)) # ---------------------------- # Department Master # ---------------------------- class Department(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(100), nullable=False) created_on = db.Column(db.DateTime, default=datetime.utcnow) updated_on = db.Column(db.DateTime, onupdate=datetime.utcnow) created_by = db.Column(db.String(50)) updated_by = db.Column(db.String(50)) # ---------------------------- # Designation Master # ---------------------------- class Designation(db.Model): id = db.Column(db.Integer, primary_key=True) title = db.Column(db.String(100), nullable=False) created_on = db.Column(db.DateTime, default=datetime.utcnow) updated_on = db.Column(db.DateTime, onupdate=datetime.utcnow) created_by = db.Column(db.String(50)) updated_by = db.Column(db.String(50)) # ---------------------------- # EmployeeChangeLog # ---------------------------- class EmployeeChangeLog(db.Model): __tablename__ = 'change_log' id = db.Column(db.Integer, primary_key=True) table_name = db.Column(db.String(100)) record_id = db.Column(db.Integer) field_name = db.Column(db.String(100)) old_value = db.Column(db.String(255)) new_value = db.Column(db.String(255)) changed_by = db.Column(db.Integer, db.ForeignKey('employee.id')) changed_by_employee = db.relationship('Employee', foreign_keys=[changed_by]) changed_on = db.Column(db.DateTime, default=datetime.utcnow) # ---------------------------- # Employee # ---------------------------- class Employee(db.Model, UserMixin): id = db.Column(db.Integer, primary_key=True) employee_code = db.Column(db.String(50), unique=True, nullable=False) full_name = db.Column(db.String(150), nullable=False) email = db.Column(db.String(150), unique=True, nullable=False) phone = db.Column(db.String(50), nullable=True) location = db.Column(db.String(100)) department_id = db.Column(db.Integer, db.ForeignKey('department.id')) designation_id = db.Column(db.Integer, db.ForeignKey('designation.id')) reporting_manager_id = db.Column(db.Integer, db.ForeignKey('employee.id'), nullable=True) department = db.relationship('Department', backref='employees') designation = db.relationship('Designation', backref='employees') reporting_manager = db.relationship('Employee', remote_side=[id], backref='reportees', foreign_keys='Employee.reporting_manager_id') date_of_joining = db.Column(db.Date, nullable=True) uan = db.Column(db.String(20)) qualification = db.Column(db.String(150)) institute = db.Column(db.String(150)) resume_path = db.Column(db.String(200)) id_proof_path = db.Column(db.String(200)) exit_date = db.Column(db.Date, nullable=True) created_on = db.Column(db.DateTime, default=datetime.utcnow) updated_on = db.Column(db.DateTime, onupdate=datetime.utcnow) created_by = db.Column(db.Integer, db.ForeignKey('employee.id'), nullable=True) updated_by = db.Column(db.Integer, db.ForeignKey('employee.id'), nullable=True) created_by_employee = relationship('Employee', foreign_keys=[created_by], back_populates='employees_created', remote_side=[id]) employees_created = relationship('Employee', foreign_keys='Employee.created_by', back_populates='created_by_employee') updated_by_employee = relationship('Employee', foreign_keys=[updated_by], back_populates='employees_updated', remote_side=[id]) employees_updated = relationship('Employee', foreign_keys='Employee.updated_by', back_populates='updated_by_employee') status = db.Column(db.String(20), default='active') role_id = db.Column(db.Integer, db.ForeignKey('roles.id')) role = relationship('Role', back_populates='employees') password_hash = db.Column(db.String(255)) set_password_token = db.Column(db.String(255), unique=True, nullable=True) read_broadcasts = db.relationship('BroadcastRead', backref='employee', lazy='dynamic') manager_approved_leaves = db.relationship('LeaveRequest',foreign_keys='LeaveRequest.approved_by_manager_id',back_populates='approved_by_manager') hr_approved_leaves = db.relationship('LeaveRequest',foreign_keys='LeaveRequest.approved_by_hr_id',back_populates='hr_approver') leave_requests = db.relationship('LeaveRequest', foreign_keys='LeaveRequest.employee_id', back_populates='employee') work_mode = db.Column(db.String(10), default='Hybrid') # or 'WFO', 'WFH' shift_start = db.Column(db.Time, nullable=True) shift_end = db.Column(db.Time, nullable=True) desktime_id = db.Column(db.String(50)) last_login = db.Column(db.DateTime) last_logout = db.Column(db.DateTime) last_ip = db.Column(db.String(100)) # --- New Personal Details --- gender = db.Column(db.String(10)) address = db.Column(db.Text) dob = db.Column(db.Date) father_spouse_name = db.Column(db.String(150)) marital_status = db.Column(db.String(20)) temporary_address = db.Column(db.Text) personal_number = db.Column(db.String(20)) emergency_contact_name = db.Column(db.String(150)) emergency_contact_number = db.Column(db.String(20)) aadhar_name = db.Column(db.String(150)) aadhar_number = db.Column(db.String(20)) pan_name = db.Column(db.String(150)) pan_number = db.Column(db.String(20)) last_company_pf_number = db.Column(db.String(50)) personal_email = db.Column(db.String(150)) bank_account_name = db.Column(db.String(150)) bank_account_number = db.Column(db.String(50)) ifsc_code = db.Column(db.String(20)) bank_name = db.Column(db.String(100)) # --- New Educational Details --- secondary_school_name = db.Column(db.String(150)) secondary_school_marks = db.Column(db.String(10)) secondary_school_year = db.Column(db.String(10)) higher_secondary_school_name = db.Column(db.String(150)) higher_secondary_school_marks = db.Column(db.String(10)) higher_secondary_school_year = db.Column(db.String(10)) graduation_name = db.Column(db.String(150)) graduation_marks = db.Column(db.String(10)) graduation_year = db.Column(db.String(10)) post_graduation_name = db.Column(db.String(150)) post_graduation_marks = db.Column(db.String(10)) post_graduation_year = db.Column(db.String(10)) # --- New Professional Details --- last_company_name = db.Column(db.String(150)) last_company_code = db.Column(db.String(50)) last_company_designation = db.Column(db.String(100)) last_company_join_date = db.Column(db.Date) last_company_exit_date = db.Column(db.Date) # --- New Document Upload Paths --- doc_qualification = db.Column(db.String(200)) doc_employment_proof = db.Column(db.String(200)) doc_photographs = db.Column(db.String(200)) doc_pan_card = db.Column(db.String(200)) doc_aadhar_card = db.Column(db.String(200)) doc_parents_aadhar = db.Column(db.String(200)) doc_uan_card = db.Column(db.String(200)) doc_cancelled_cheque = db.Column(db.String(200)) def set_password(self, password): self.password_hash = bcrypt.generate_password_hash(password).decode('utf-8') def check_password(self, password): return bcrypt.check_password_hash(self.password_hash, password) # ---------------------------- # MasterChangeLog # ---------------------------- class MasterChangeLog(db.Model): id = db.Column(db.Integer, primary_key=True) table_name = db.Column(db.String(50)) record_id = db.Column(db.Integer) field_name = db.Column(db.String(100)) old_value = db.Column(db.String(255)) new_value = db.Column(db.String(255)) changed_by = db.Column(db.Integer, db.ForeignKey('employee.id')) changed_on = db.Column(db.DateTime, default=datetime.utcnow) changed_by_employee = db.relationship("Employee", backref="master_change_logs", lazy=True) # ---------------------------- # Other Models # ---------------------------- class PublicLink(db.Model): id = db.Column(db.Integer, primary_key=True) token = db.Column(db.String(255), unique=True, default=lambda: str(uuid.uuid4())) description = db.Column(db.String(255)) created_on = db.Column(db.DateTime, default=datetime.utcnow) expires_on = db.Column(db.DateTime) is_used = db.Column(db.Boolean, default=False) class PendingEmployee(db.Model): id = db.Column(db.Integer, primary_key=True) # Basic Info full_name = db.Column(db.String(255)) email = db.Column(db.String(255)) phone = db.Column(db.String(50), nullable=True) # Company Assignment department_id = db.Column(db.Integer, db.ForeignKey('department.id')) department = db.relationship('Department', backref='pending_employees', lazy=True) designation_id = db.Column(db.Integer, db.ForeignKey('designation.id')) designation = db.relationship('Designation', backref='pending_employees', lazy=True) # Joining Info date_of_joining = db.Column(db.Date, nullable=True) date_of_exit = db.Column(db.Date) # Education Summary (optional) qualification = db.Column(db.String(255)) institute = db.Column(db.String(255)) # Resume and ID proof (single file each) resume_path = db.Column(db.String(255)) id_proof_path = db.Column(db.String(255)) # Additional Personal Details gender = db.Column(db.String(20)) dob = db.Column(db.Date) marital_status = db.Column(db.String(50)) father_spouse_name = db.Column(db.String(255)) personal_number = db.Column(db.String(50)) emergency_contact_name = db.Column(db.String(255)) emergency_contact_number = db.Column(db.String(50)) address = db.Column(db.Text) temporary_address = db.Column(db.Text) # ID & Bank Details aadhar_name = db.Column(db.String(150)) aadhar_number = db.Column(db.String(20)) pan_name = db.Column(db.String(150)) pan_number = db.Column(db.String(20)) uan = db.Column(db.String(50)) last_company_pf_number = db.Column(db.String(50)) personal_email = db.Column(db.String(255)) bank_account_name = db.Column(db.String(255)) bank_account_number = db.Column(db.String(50)) ifsc_code = db.Column(db.String(20)) bank_name = db.Column(db.String(100)) # Educational Details (detailed) secondary_school_name = db.Column(db.String(255)) secondary_school_marks = db.Column(db.String(20)) secondary_school_year = db.Column(db.String(10)) higher_secondary_school_name = db.Column(db.String(255)) higher_secondary_school_marks = db.Column(db.String(20)) higher_secondary_school_year = db.Column(db.String(10)) graduation_name = db.Column(db.String(255)) graduation_marks = db.Column(db.String(20)) graduation_year = db.Column(db.String(10)) post_graduation_name = db.Column(db.String(255)) post_graduation_marks = db.Column(db.String(20)) post_graduation_year = db.Column(db.String(10)) # Professional Details last_company_name = db.Column(db.String(255)) last_company_code = db.Column(db.String(100)) last_company_designation = db.Column(db.String(100)) # Document Uploads (multi-file paths stored as comma-separated strings) doc_qualification = db.Column(db.Text) doc_employment_proof = db.Column(db.Text) doc_photographs = db.Column(db.Text) doc_pan_card = db.Column(db.Text) doc_aadhar_card = db.Column(db.Text) doc_parents_aadhar = db.Column(db.Text) doc_uan_card = db.Column(db.Text) doc_cancelled_cheque = db.Column(db.Text) # Submission Timestamp submitted_on = db.Column(db.DateTime, default=datetime.utcnow) class PendingApprovalLog(db.Model): id = db.Column(db.Integer, primary_key=True) pending_employee_id = db.Column(db.Integer) pending_employee_name = db.Column(db.String(255)) approved_employee_id = db.Column(db.Integer, db.ForeignKey('employee.id'), nullable=True) approved_by = db.Column(db.Integer, db.ForeignKey('employee.id')) status = db.Column(db.String(20)) approved_on = db.Column(db.DateTime, default=datetime.utcnow) approved_employee = db.relationship('Employee', foreign_keys=[approved_employee_id]) approver = db.relationship('Employee', foreign_keys=[approved_by]) class BroadcastDepartment(db.Model): __tablename__ = 'broadcast_departments' id = db.Column(db.Integer, primary_key=True) broadcast_id = db.Column(db.Integer, db.ForeignKey('broadcast_message.id')) department_id = db.Column(db.Integer, db.ForeignKey('department.id')) class BroadcastRole(db.Model): __tablename__ = 'broadcast_roles' id = db.Column(db.Integer, primary_key=True) broadcast_id = db.Column(db.Integer, db.ForeignKey('broadcast_message.id')) role_id = db.Column(db.Integer, db.ForeignKey('roles.id')) class BroadcastMessage(db.Model): id = db.Column(db.Integer, primary_key=True) title = db.Column(db.String(255), nullable=False) content = db.Column(db.Text) attachment_path = db.Column(db.String(255)) start_date = db.Column(db.Date) end_date = db.Column(db.Date) created_by = db.Column(db.Integer, db.ForeignKey('employee.id')) created_on = db.Column(db.DateTime, default=datetime.utcnow) departments = db.relationship('Department', secondary=BroadcastDepartment.__table__, backref='broadcasts') roles = db.relationship('Role', secondary=BroadcastRole.__table__, backref='broadcasts') class BroadcastRead(db.Model): id = db.Column(db.Integer, primary_key=True) broadcast_id = db.Column(db.Integer, db.ForeignKey('broadcast_message.id')) employee_id = db.Column(db.Integer, db.ForeignKey('employee.id')) read_on = db.Column(db.DateTime, default=datetime.utcnow) class LeaveType(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(50), nullable=False) code = db.Column(db.String(10), unique=True, nullable=False) default_quota = db.Column(db.Float, default=0) allow_half_day = db.Column(db.Boolean, default=False) requires_approval = db.Column(db.Boolean, default=True) carry_forward = db.Column(db.Boolean, default=False) encashable = db.Column(db.Boolean, default=False) sandwich_applicable = db.Column(db.Boolean, default=True) probation_restricted = db.Column(db.Boolean, default=False) remarks = db.Column(db.String(255)) credit_type = db.Column(db.String(20)) # values: 'monthly', 'earned', 'manual' class LeavePolicy(db.Model): id = db.Column(db.Integer, primary_key=True) company_id = db.Column(db.Integer) # optional for multi-tenant leave_type_id = db.Column(db.Integer, db.ForeignKey('leave_type.id')) role_id = db.Column(db.Integer, db.ForeignKey('roles.id')) # optional department_id = db.Column(db.Integer, db.ForeignKey('department.id')) # optional annual_quota = db.Column(db.Float) monthly_credit = db.Column(db.Boolean, default=False) max_consecutive_days = db.Column(db.Integer, default=30) join_after_cutoff_month = db.Column(db.Integer) # e.g., 9 for September rh_consecutive_limit = db.Column(db.Integer) # e.g., max 2 RH in a row leave_type = db.relationship('LeaveType') class LeaveBalance(db.Model): id = db.Column(db.Integer, primary_key=True) employee_id = db.Column(db.Integer, db.ForeignKey('employee.id')) leave_type_id = db.Column(db.Integer, db.ForeignKey('leave_type.id')) year = db.Column(db.Integer) balance = db.Column(db.Float, default=0) employee = db.relationship('Employee') leave_type = db.relationship('LeaveType') class LeaveRequest(db.Model): id = db.Column(db.Integer, primary_key=True) employee_id = db.Column(db.Integer, db.ForeignKey('employee.id')) leave_type_id = db.Column(db.Integer, db.ForeignKey('leave_type.id')) start_date = db.Column(db.Date) end_date = db.Column(db.Date) total_days = db.Column(db.Float) half_day = db.Column(db.Boolean, default=False) reason = db.Column(db.Text) status = db.Column(db.String(20), default='pending') approver_level = db.Column(db.String(20)) applied_on = db.Column(db.DateTime, default=datetime.utcnow) medical_note_uploaded = db.Column(db.Boolean, default=False) employee = db.relationship('Employee', foreign_keys=[employee_id], back_populates='leave_requests') leave_type = db.relationship('LeaveType') approved_by_manager_id = db.Column(db.Integer, db.ForeignKey('employee.id'), nullable=True) approved_by_manager = db.relationship('Employee', foreign_keys=[approved_by_manager_id], back_populates='manager_approved_leaves') approved_by_hr_id = db.Column(db.Integer, db.ForeignKey('employee.id'), nullable=True) hr_approver = db.relationship('Employee', foreign_keys=[approved_by_hr_id], back_populates='hr_approved_leaves') approval_logs = db.relationship('LeaveApprovalLog', backref='leave_request', lazy='select') class LeaveApprovalLog(db.Model): id = db.Column(db.Integer, primary_key=True) leave_request_id = db.Column(db.Integer, db.ForeignKey('leave_request.id')) approver_id = db.Column(db.Integer, db.ForeignKey('employee.id')) approver_role = db.Column(db.String(20)) status = db.Column(db.String(20)) # approved, rejected remark = db.Column(db.Text) timestamp = db.Column(db.DateTime, default=datetime.utcnow) class Attendance(db.Model): id = db.Column(db.Integer, primary_key=True) employee_id = db.Column(db.Integer, db.ForeignKey('employee.id'), nullable=False) date = db.Column(db.Date, nullable=False) check_in_time = db.Column(db.Time, nullable=True) check_out_time = db.Column(db.Time, nullable=True) working_hours = db.Column(db.Float, default=0) # e.g., in hours status = db.Column(db.String(50), nullable=False, default='present') source = db.Column(db.String(20)) # values: 'Punch', 'Desktime' # options: 'present', 'absent', 'paid_leave', 'lop' desktime_log = db.relationship("DesktimeLog", uselist=False, primaryjoin="and_(Attendance.employee_id==foreign(DesktimeLog.employee_id), " "Attendance.date==foreign(DesktimeLog.date))", viewonly=True) remarks = db.Column(db.String(255)) note = db.Column(db.Text) # optional field for manual updates employee = db.relationship('Employee', backref='attendances') class PublicHoliday(db.Model): id = db.Column(db.Integer, primary_key=True) date = db.Column(db.Date, nullable=False) name = db.Column(db.String(150), nullable=False) location = db.Column(db.String(100), default='All') is_restricted = db.Column(db.Boolean, default=False) class LeaveSettings(db.Model): id = db.Column(db.Integer, primary_key=True) company_id = db.Column(db.Integer) leave_year_start = db.Column(db.Date) # e.g., 2024-04-01 leave_year_end = db.Column(db.Date) # e.g., 2025-03-31 sandwich_policy_enabled = db.Column(db.Boolean, default=True) rh_max_per_year = db.Column(db.Integer, default=5) from extensions import db from datetime import date, timedelta class CompOffRequest(db.Model): __tablename__ = 'comp_off_request' id = db.Column(db.Integer, primary_key=True) # Foreign keys & relations employee_id = db.Column(db.Integer, db.ForeignKey('employee.id'), nullable=False, index=True) employee = db.relationship('Employee', backref=db.backref('comp_off_requests', lazy='dynamic')) # Core fields work_date = db.Column(db.Date, nullable=False, index=True) valid_until = db.Column(db.Date, nullable=False) reason = db.Column(db.Text, nullable=False) status = db.Column(db.String(20), default='pending', nullable=False, index=True) # pending | approved | rejected __table_args__ = ( # 1 compโ€‘off request per employee per work date db.UniqueConstraint('employee_id', 'work_date', name='uq_comp_off_emp_workdate'), ) def __repr__(self): return f"" @property def is_expired(self) -> bool: return date.today() > (self.valid_until or date.min) @staticmethod def default_valid_until(work_date: date, days: int = 30) -> date: """Helper if you want a single place to define validity window.""" return work_date + timedelta(days=days) class WeekendSettings(db.Model): id = db.Column(db.Integer, primary_key=True) first_saturday = db.Column(db.String(20), default='off') # 'off', 'working', 'half-day' second_saturday = db.Column(db.String(20), default='off') third_saturday = db.Column(db.String(20), default='off') fourth_saturday = db.Column(db.String(20), default='off') fifth_saturday = db.Column(db.String(20), default='off') sunday = db.Column(db.String(20), default='off') updated_on = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) class RHConfig(db.Model): id = db.Column(db.Integer, primary_key=True) location = db.Column(db.String(100), unique=True, nullable=False) max_rh_per_year = db.Column(db.Integer, default=2) class EarningHead(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(100)) description = db.Column(db.String(255)) class DeductionHead(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(100)) description = db.Column(db.String(255)) class CTCHead(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(100), unique=True, nullable=False) description = db.Column(db.String(255)) class SalaryFormula(db.Model): id = db.Column(db.Integer, primary_key=True) component_type = db.Column(db.Enum('earning', 'deduction', 'ctc'), nullable=False) component_id = db.Column(db.Integer, nullable=False) formula = db.Column(db.Text, nullable=False) class EmployeeSalaryStructures(db.Model): __tablename__ = 'employee_salary_structures' id = db.Column(db.Integer, primary_key=True) employee_id = db.Column(db.Integer, db.ForeignKey('employee.id'), nullable=False) head_id = db.Column(db.Integer, nullable=False) # refers to EarningHead or DeductionHead type = db.Column(db.String(20), nullable=False) # 'earning' or 'deduction' amount = db.Column(db.Float, default=0.0) class PayrollRecord(db.Model): id = db.Column(db.Integer, primary_key=True) employee_id = db.Column(db.Integer, db.ForeignKey('employee.id'), nullable=False) year = db.Column(db.Integer, nullable=False) month = db.Column(db.Integer, nullable=False) gross = db.Column(db.Float, nullable=False) deductions = db.Column(db.Float, nullable=False) net_pay = db.Column(db.Float, nullable=False) ctc = db.Column(db.Float, nullable=True) generated_on = db.Column(db.DateTime, default=datetime.utcnow) employee = db.relationship('Employee', backref='payroll_records') __table_args__ = (db.UniqueConstraint('employee_id', 'year', 'month', name='unique_employee_month'),) class Payroll(db.Model): id = db.Column(db.Integer, primary_key=True) employee_id = db.Column(db.Integer, db.ForeignKey('employee.id'), nullable=False) month = db.Column(db.String(7), nullable=False) # e.g. "2025-07" gross_pay = db.Column(db.Float, nullable=False) deductions = db.Column(db.Float, nullable=False) net_pay = db.Column(db.Float, nullable=False) ctc = db.Column(db.Float, nullable=True) present_days = db.Column(db.Integer, nullable=False) approved_leaves = db.Column(db.Integer, nullable=False) holidays = db.Column(db.Integer, nullable=False) lwp_days = db.Column(db.Integer, nullable=False) weekends = db.Column(db.Integer, default=0) freeze_date = db.Column(db.DateTime, default=datetime.utcnow) employee = db.relationship('Employee', backref='payrolls') class MonthlyPayroll(db.Model): id = db.Column(db.Integer, primary_key=True) employee_id = db.Column(db.Integer, db.ForeignKey('employee.id'), nullable=False) month = db.Column(db.Integer, nullable=False) year = db.Column(db.Integer, nullable=False) gross = db.Column(db.Float, nullable=False) deductions = db.Column(db.Float, nullable=False) net_pay = db.Column(db.Float, nullable=False) ctc = db.Column(db.Float, nullable=True) frozen_on = db.Column(db.DateTime, default=datetime.utcnow) employee = db.relationship('Employee', backref='monthly_payrolls') # models.py class DesktimeLog(db.Model): __tablename__ = 'desktime_log' id = db.Column(db.Integer, primary_key=True) employee_id = db.Column(db.Integer, db.ForeignKey('employee.id'), nullable=False) date = db.Column(db.Date, nullable=False) first_activity = db.Column(db.Time) last_activity = db.Column(db.Time) productive_hours = db.Column(db.Float) raw_data = db.Column(db.Text) fetched_on = db.Column(db.DateTime, default=datetime.utcnow) class SyncLog(db.Model): id = db.Column(db.Integer, primary_key=True) started_at = db.Column(db.DateTime, nullable=False) ended_at = db.Column(db.DateTime) status = db.Column(db.String(50)) message = db.Column(db.Text) class FrozenSalaryComponent(db.Model): id = db.Column(db.Integer, primary_key=True) employee_id = db.Column(db.Integer, db.ForeignKey('employee.id'), nullable=False) month = db.Column(db.String(7), nullable=False) # Format: 'YYYY-MM' component_type = db.Column(db.String(20), nullable=False) # 'earning' or 'deduction' head_name = db.Column(db.String(100), nullable=False) amount = db.Column(db.Float, nullable=False) employee = db.relationship('Employee', backref='frozen_salary_components') class Menu(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(100), unique=True) icon = db.Column(db.String(100)) # optional: for UI icons order = db.Column(db.Integer, default=0) submenus = db.relationship('SubMenu', backref='menu', cascade='all, delete') class SubMenu(db.Model): id = db.Column(db.Integer, primary_key=True) menu_id = db.Column(db.Integer, db.ForeignKey('menu.id')) url = db.Column(db.String(255)) name = db.Column(db.String(100)) endpoint = db.Column(db.String(200)) # Flask endpoint or route name order = db.Column(db.Integer, default=0) class RoleAccess(db.Model): id = db.Column(db.Integer, primary_key=True) role = db.Column(db.String(50)) submenu_id = db.Column(db.Integer, db.ForeignKey('sub_menu.id')) class UserAccess(db.Model): id = db.Column(db.Integer, primary_key=True) user_id = db.Column(db.Integer, db.ForeignKey('employee.id')) submenu_id = db.Column(db.Integer, db.ForeignKey('sub_menu.id')) class DocumentCategory(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(100), nullable=False) description = db.Column(db.String(255)) created_at = db.Column(db.DateTime, default=datetime.utcnow) class Document(db.Model): id = db.Column(db.Integer, primary_key=True) category_id = db.Column(db.Integer, db.ForeignKey('document_category.id'), nullable=False) filename = db.Column(db.String(255), nullable=False) file_path = db.Column(db.String(255), nullable=False) uploaded_by = db.Column(db.Integer, db.ForeignKey('employee.id')) uploaded_at = db.Column(db.DateTime, default=datetime.utcnow) category = db.relationship('DocumentCategory', backref='documents') uploader = db.relationship('Employee', backref='uploaded_documents') class FeedbackQuestion(db.Model): __tablename__ = 'feedback_question' id = db.Column(db.Integer, primary_key=True) question_text = db.Column(db.String(255), nullable=False) qtype = db.Column(db.Enum('text', 'single', 'multi', name='feedback_qtype'), nullable=False, default='text') is_active = db.Column(db.Boolean, default=True) created_at = db.Column(db.DateTime, default=datetime.utcnow) updated_at = db.Column(db.DateTime, onupdate=datetime.utcnow) options = db.relationship( 'FeedbackOption', backref='question', cascade='all, delete-orphan', order_by='FeedbackOption.sort_order' ) class FeedbackOption(db.Model): __tablename__ = 'feedback_option' id = db.Column(db.Integer, primary_key=True) question_id = db.Column(db.Integer, db.ForeignKey('feedback_question.id'), nullable=False) label = db.Column(db.String(255), nullable=False) value = db.Column(db.String(255), nullable=False) sort_order = db.Column(db.Integer, default=0) class FeedbackResponse(db.Model): __tablename__ = 'feedback_response' id = db.Column(db.Integer, primary_key=True) employee_id = db.Column(db.Integer, db.ForeignKey('employee.id'), nullable=True) # NULL = anonymous submitted_by_id = db.Column(db.Integer, db.ForeignKey('employee.id'), nullable=False, index=True) # NEW month = db.Column(db.String(7), nullable=False) # YYYY-MM submitted_at = db.Column(db.DateTime, default=datetime.utcnow) is_anonymous = db.Column(db.Boolean, default=False) answers = db.relationship('FeedbackAnswer', backref='response', cascade='all, delete-orphan') employee = db.relationship('Employee', foreign_keys=[employee_id]) submitted_by = db.relationship('Employee', foreign_keys=[submitted_by_id]) class FeedbackAnswer(db.Model): __tablename__ = 'feedback_answer' id = db.Column(db.Integer, primary_key=True) response_id = db.Column(db.Integer, db.ForeignKey('feedback_response.id'), nullable=False) question_id = db.Column(db.Integer, db.ForeignKey('feedback_question.id'), nullable=False) # text answers answer_text = db.Column(db.Text, nullable=True) # single-choice answers selected_option_id = db.Column(db.Integer, db.ForeignKey('feedback_option.id'), nullable=True) question = db.relationship('FeedbackQuestion') selected_option = db.relationship('FeedbackOption') class FeedbackAnswerOption(db.Model): __tablename__ = 'feedback_answer_option' answer_id = db.Column(db.Integer, db.ForeignKey('feedback_answer.id'), primary_key=True) option_id = db.Column(db.Integer, db.ForeignKey('feedback_option.id'), primary_key=True) class Role(db.Model): __tablename__ = 'roles' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(50), unique=True, nullable=False) # Optional: You can add a description or permissions field here description = db.Column(db.String(200)) # Backref to Employee employees = db.relationship('Employee', back_populates='role') class PayrollPreviewControl(db.Model): __tablename__ = 'payroll_preview_control' id = db.Column(db.Integer, primary_key=True) month = db.Column(db.String(7), nullable=False, unique=True) # format: YYYY-MM preview_enabled = db.Column(db.Boolean, default=False) enabled_on = db.Column(db.DateTime) class Holiday(db.Model): id = db.Column(db.Integer, primary_key=True) date = db.Column(db.Date, unique=True) name = db.Column(db.String(100)) type = db.Column(db.String(20)) # national, mandatory, optional location = db.Column(db.String(100), nullable=True) class EmailLog(db.Model): __tablename__ = "email_log" id = db.Column(db.Integer, primary_key=True) to_email = db.Column(db.String(255), nullable=False) template = db.Column(db.String(100), nullable=False) subject = db.Column(db.String(255), nullable=False) status = db.Column(db.String(20), nullable=False) # 'sent' | 'failed' error = db.Column(db.Text) created_at = db.Column(db.DateTime, default=datetime.utcnow, nullable=False) # โœ… Fix these models in models.py class ExitRequest(db.Model): __tablename__ = "exit_requests" id = db.Column(db.Integer, primary_key=True) # to Employee table employee_id = db.Column(db.Integer, db.ForeignKey("employee.id"), nullable=False) # "users" -> Employee table (you log in as Employee) created_by = db.Column(db.Integer, db.ForeignKey("employee.id"), nullable=False) created_at = db.Column(db.DateTime, default=datetime.utcnow, nullable=False) resignation_date = db.Column(db.Date, nullable=False) proposed_lwd = db.Column(db.Date, nullable=False) status = db.Column(db.String(30), nullable=False, default="initiated") manager_id = db.Column(db.Integer, db.ForeignKey("employee.id"), nullable=True) manager_approved_at = db.Column(db.DateTime) manager_approved_by = db.Column(db.Integer, db.ForeignKey("employee.id")) hod_id = db.Column(db.Integer, db.ForeignKey("employee.id"), nullable=True) hod_approved_at = db.Column(db.DateTime) hod_approved_by = db.Column(db.Integer, db.ForeignKey("employee.id")) it_clearance_by = db.Column(db.Integer, db.ForeignKey("employee.id")) it_cleared_at = db.Column(db.DateTime) final_ok_by_manager_at = db.Column(db.DateTime) final_ok_by_hr_at = db.Column(db.DateTime) remarks = db.Column(db.Text) manager_accepted = db.Column(db.Boolean, default=False, nullable=False) # (optional but nice) relationships employee = db.relationship("Employee", foreign_keys=[employee_id]) created_by_emp = db.relationship("Employee", foreign_keys=[created_by]) manager = db.relationship("Employee", foreign_keys=[manager_id]) manager_approved_by_emp = db.relationship("Employee", foreign_keys=[manager_approved_by]) hod = db.relationship("Employee", foreign_keys=[hod_id]) hod_approved_by_emp = db.relationship("Employee", foreign_keys=[hod_approved_by]) it_cleared_by_emp = db.relationship("Employee", foreign_keys=[it_clearance_by]) class HandoverDoc(db.Model): __tablename__ = "handover_docs" id = db.Column(db.Integer, primary_key=True) exit_id = db.Column(db.Integer, db.ForeignKey("exit_requests.id"), nullable=False, index=True) title = db.Column(db.String(200), nullable=False) file_path = db.Column(db.String(400), nullable=False) # "users" -> Employee table uploaded_by = db.Column(db.Integer, db.ForeignKey("employee.id"), nullable=False) uploaded_at = db.Column(db.DateTime, default=datetime.utcnow, nullable=False) # (optional) relationships exit = db.relationship("ExitRequest", backref="handover_docs") uploader = db.relationship("Employee", foreign_keys=[uploaded_by]) class HandoverDocViewer(db.Model): __tablename__ = "handover_doc_viewers" id = db.Column(db.Integer, primary_key=True) doc_id = db.Column(db.Integer, db.ForeignKey("handover_docs.id"), nullable=False, index=True) # "employees" -> Employee table viewer_emp_id = db.Column(db.Integer, db.ForeignKey("employee.id"), nullable=False, index=True) can_view = db.Column(db.Boolean, default=True, nullable=False) acknowledged_at = db.Column(db.DateTime) # (optional) relationships doc = db.relationship("HandoverDoc", backref="viewers") viewer = db.relationship("Employee", foreign_keys=[viewer_emp_id]) class ExitEvent(db.Model): __tablename__ = "exit_events" id = db.Column(db.Integer, primary_key=True) exit_id = db.Column(db.Integer, db.ForeignKey("exit_requests.id"), nullable=False, index=True) at = db.Column(db.DateTime, default=datetime.utcnow, nullable=False) # "users" -> Employee table actor_id = db.Column(db.Integer, db.ForeignKey("employee.id")) action = db.Column(db.String(50), nullable=False) note = db.Column(db.Text) # (optional) relationships exit = db.relationship("ExitRequest", backref="events") actor = db.relationship("Employee", foreign_keys=[actor_id]) class Notification(db.Model): __tablename__ = 'notifications' id = db.Column(db.Integer, primary_key=True) # Tie to your User table (recommended). If you prefer Employee, switch the FK. employee_id = db.Column(db.Integer, db.ForeignKey('employee.id'), index=True, nullable=True) # None = broadcast title = db.Column(db.String(255), nullable=False) message = db.Column(db.Text, nullable=False) type = db.Column(db.String(20), default='info') # info, success, warning, danger link = db.Column(db.String(512)) # optional deeplink is_read = db.Column(db.Boolean, default=False, index=True) created_at = db.Column(db.DateTime, default=datetime.utcnow, index=True) # if you have a User model imported in this file, you can enable the relation # user = db.relationship('User', backref='notifications') # models_tr.py (or add to models.py) from extensions import db from datetime import datetime, date class TRRequest(db.Model): __tablename__ = 'tr_request' id = db.Column(db.Integer, primary_key=True) # Who & when employee_id = db.Column(db.Integer, db.ForeignKey('employee.id'), nullable=False, index=True) employee = db.relationship('Employee', backref=db.backref('tr_requests', lazy='dynamic')) month = db.Column(db.Integer, nullable=False) # 1..12 year = db.Column(db.Integer, nullable=False) # Aggregates (denormalized for quick lists + export) total_kms = db.Column(db.Float, default=0) total_amount = db.Column(db.Float, default=0) # Status: pending_manager, manager_approved, hr_approved, rejected, paid status = db.Column(db.String(32), default='pending_manager', index=True) created_on = db.Column(db.DateTime, default=datetime.utcnow) updated_on = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) __table_args__ = ( db.UniqueConstraint('employee_id', 'month', 'year', name='uq_tr_employee_month'), ) class TRItem(db.Model): __tablename__ = 'tr_item' id = db.Column(db.Integer, primary_key=True) request_id = db.Column(db.Integer, db.ForeignKey('tr_request.id'), nullable=False, index=True) request = db.relationship('TRRequest', backref=db.backref('items', cascade='all, delete-orphan', lazy='joined')) trip_date = db.Column(db.Date, nullable=False) from_loc = db.Column(db.String(120), nullable=False) to_loc = db.Column(db.String(120), nullable=False) kms_total = db.Column(db.Float, nullable=False) rate = db.Column(db.Float, nullable=False) amount = db.Column(db.Float, nullable=False) # computed kms_total * rate purpose = db.Column(db.String(200)) # ๐Ÿ‘ˆ optional: better audit mode = db.Column(db.String(50)) # ๐Ÿ‘ˆ optional: car/bike/taxi/metro notes = db.Column(db.Text) # ๐Ÿ‘ˆ optional attachment_path = db.Column(db.String(255)) # absolute or app-root relative path attachment_name = db.Column(db.String(200)) # original filename shown to user attachment_type = db.Column(db.String(100)) # guessed mimetype for preview class TRApprovalLog(db.Model): __tablename__ = 'tr_approval_log' id = db.Column(db.Integer, primary_key=True) request_id = db.Column(db.Integer, db.ForeignKey('tr_request.id'), nullable=False, index=True) actor_id = db.Column(db.Integer, db.ForeignKey('employee.id'), nullable=False) actor_role = db.Column(db.String(16), nullable=False) # 'hod' or 'hr' action = db.Column(db.String(16), nullable=False) # 'approved' | 'rejected' | 'paid' remark = db.Column(db.Text) acted_on = db.Column(db.DateTime, default=datetime.utcnow) request = db.relationship('TRRequest', backref=db.backref('approval_logs', lazy='dynamic')) actor = db.relationship('Employee') # --- NEW: Allocation batch audit models --- from datetime import datetime from extensions import db class LeaveAllocationBatch(db.Model): __tablename__ = 'leave_allocation_batch' id = db.Column(db.Integer, primary_key=True) batch_key = db.Column(db.String(64), unique=True, nullable=False) # e.g. 'FY2025_EL_monthly_credit_20250822_101530' leave_type_id = db.Column(db.Integer, db.ForeignKey('leave_type.id'), nullable=True) fy_label = db.Column(db.Integer, nullable=True) # your LeaveBalance.year (FY start year) strategy = db.Column(db.String(64), nullable=False) # 'monthly_credit' | 'chl_earned_leave' | 'annual' run_by_id = db.Column(db.Integer, db.ForeignKey('employee.id'), nullable=True) run_at = db.Column(db.DateTime, default=datetime.utcnow, nullable=False) note = db.Column(db.String(255), nullable=True) is_revoked = db.Column(db.Boolean, default=False, nullable=False) revoked_at = db.Column(db.DateTime, nullable=True) revoked_by_id = db.Column(db.Integer, db.ForeignKey('employee.id'), nullable=True) entries = db.relationship('LeaveAllocationEntry', backref='batch', cascade='all, delete-orphan') class LeaveAllocationEntry(db.Model): __tablename__ = 'leave_allocation_entry' id = db.Column(db.Integer, primary_key=True) batch_id = db.Column(db.Integer, db.ForeignKey('leave_allocation_batch.id'), nullable=False) employee_id = db.Column(db.Integer, db.ForeignKey('employee.id'), nullable=False) leave_type_id = db.Column(db.Integer, db.ForeignKey('leave_type.id'), nullable=False) fy_label = db.Column(db.Integer, nullable=False) previous_balance = db.Column(db.Float, default=0.0, nullable=False) new_balance = db.Column(db.Float, default=0.0, nullable=False) credit_delta = db.Column(db.Float, default=0.0, nullable=False) # --- DocumentType (master) --- class DocumentType(db.Model): __tablename__ = 'document_type' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(80), nullable=False, unique=True) code = db.Column(db.String(20), nullable=False, unique=True) # e.g., APPT, INCR description = db.Column(db.String(255)) # --- EmployeeDocument (per-file record) --- class EmployeeDocument(db.Model): __tablename__ = 'employee_document' id = db.Column(db.Integer, primary_key=True) employee_id = db.Column(db.Integer, db.ForeignKey('employee.id'), nullable=False, index=True) doc_type_id = db.Column(db.Integer, db.ForeignKey('document_type.id'), nullable=False, index=True) title = db.Column(db.String(160), nullable=False) file_path = db.Column(db.String(255), nullable=False) # relative path on disk (or s3 key) original_name = db.Column(db.String(160), nullable=False) mime_type = db.Column(db.String(60)) file_size = db.Column(db.Integer) issued_on = db.Column(db.Date) valid_until = db.Column(db.Date) notes = db.Column(db.Text) is_private = db.Column(db.Boolean, default=True) # if False, visible to employee; True also visible (since in profile), keep for future ACLs is_active = db.Column(db.Boolean, default=True, index=True) created_at = db.Column(db.DateTime, default=datetime.utcnow) created_by_id = db.Column(db.Integer, db.ForeignKey('employee.id')) updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) # relationships (optional) employee = db.relationship('Employee', foreign_keys=[employee_id]) doc_type = db.relationship('DocumentType') # models.py (or wherever your models live) class PayrollUnfreezeLog(db.Model): __tablename__ = "payroll_unfreeze_log" id = db.Column(db.Integer, primary_key=True) month = db.Column(db.String(7), nullable=False) # 'YYYY-MM' employee_id = db.Column(db.Integer, db.ForeignKey("employee.id"), nullable=True) # null => month-wide reason = db.Column(db.Text, nullable=True) deleted_payroll_rows = db.Column(db.Integer, default=0) deleted_frozen_components = db.Column(db.Integer, default=0) deleted_monthly_rows = db.Column(db.Integer, default=0) unfreezed_by_id = db.Column(db.Integer, db.ForeignKey("employee.id"), nullable=True) created_at = db.Column(db.DateTime, default=datetime.utcnow) class BrandingSetting(db.Model): __tablename__ = 'branding_settings' id = db.Column(db.Integer, primary_key=True, default=1) sidebar_bg = db.Column(db.String(20)) topbar_bg = db.Column(db.String(20)) accent = db.Column(db.String(20)) logo_url = db.Column(db.String(255)) updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) class Dashboard(db.Model): __tablename__ = 'dashboards' id = db.Column(db.Integer, primary_key=True) owner_user_id = db.Column(db.Integer, db.ForeignKey('employee.id'), nullable=True) # per-user role_name = db.Column(db.String(64), nullable=True) # or per-role (โ€œadminโ€, โ€œhrโ€) title = db.Column(db.String(120), nullable=False, default='My Dashboard') is_default = db.Column(db.Boolean, default=False) updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) widgets = db.relationship('DashboardWidget', backref='dashboard', cascade='all, delete-orphan') __table_args__ = ( db.Index('ix_dash_owner_role', 'owner_user_id', 'role_name'), ) class DashboardWidget(db.Model): __tablename__ = 'dashboard_widgets' id = db.Column(db.Integer, primary_key=True) dashboard_id = db.Column(db.Integer, db.ForeignKey('dashboards.id'), nullable=False) # widget identity & config widget_type = db.Column(db.String(64), nullable=False) # e.g. 'attendance_by_day', 'pending_leaves' title = db.Column(db.String(120), nullable=True) config_json = db.Column(db.JSON, nullable=True) # arbitrary settings/filters # layout meta as saved by Gridstack x = db.Column(db.Integer, default=0) y = db.Column(db.Integer, default=0) w = db.Column(db.Integer, default=6) # width (cols) h = db.Column(db.Integer, default=3) # height (rows) order_index = db.Column(db.Integer, default=0) class SystemSetting(db.Model): __tablename__ = 'system_setting' id = db.Column(db.Integer, primary_key=True) key = db.Column(db.String(100), unique=True, nullable=False) value = db.Column(db.Text, nullable=True) # optional plain text json_value = db.Column(db.Text, nullable=True) # JSON blob updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) updated_by_id = db.Column(db.Integer, db.ForeignKey('employee.id')) updated_by = db.relationship('Employee', foreign_keys=[updated_by_id]) class ProductiveHoursRule(db.Model): __tablename__ = 'productive_hours_rule' id = db.Column(db.Integer, primary_key=True) # Scope: company / department / designation / employee scope_type = db.Column(db.String(20), nullable=False) # 'company','department','designation','employee' department_id = db.Column(db.Integer, db.ForeignKey('department.id'), nullable=True) designation_id = db.Column(db.Integer, db.ForeignKey('designation.id'), nullable=True) employee_id = db.Column(db.Integer, db.ForeignKey('employee.id'), nullable=True) required_hours = db.Column(db.Float, nullable=False, default=7.0) created_at = db.Column(db.DateTime, default=datetime.utcnow) updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) department = db.relationship('Department') designation = db.relationship('Designation') employee = db.relationship('Employee') # ---------------------- ASSET MANAGEMENT ---------------------- # class AssetCategory(db.Model): __tablename__ = "asset_categories" id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(100), unique=True, nullable=False) description = db.Column(db.Text, nullable=True) is_active = db.Column(db.Boolean, default=True) created_at = db.Column(db.DateTime, default=datetime.utcnow) assets = db.relationship("Asset", backref="category", lazy=True) def __repr__(self): return f"" class AssetVendor(db.Model): __tablename__ = "asset_vendors" id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(150), nullable=False) contact_person = db.Column(db.String(120)) phone = db.Column(db.String(50)) email = db.Column(db.String(120)) address = db.Column(db.Text) is_active = db.Column(db.Boolean, default=True) created_at = db.Column(db.DateTime, default=datetime.utcnow) assets = db.relationship("Asset", backref="vendor", lazy=True) def __repr__(self): return f"" class Asset(db.Model): __tablename__ = "assets" id = db.Column(db.Integer, primary_key=True) asset_code = db.Column(db.String(50), unique=True, nullable=False) asset_tag = db.Column(db.String(50), unique=True, nullable=False) name = db.Column(db.String(150), nullable=False) category_id = db.Column(db.Integer, db.ForeignKey("asset_categories.id"), nullable=True) vendor_id = db.Column(db.Integer, db.ForeignKey("asset_vendors.id"), nullable=True) model = db.Column(db.String(150)) serial_number = db.Column(db.String(150), unique=True) purchase_date = db.Column(db.Date) purchase_cost = db.Column(db.Numeric(12, 2)) warranty_expiry = db.Column(db.Date) status = db.Column( db.String(30), default="available", doc="available / allocated / in_repair / lost / damaged / retired", ) location = db.Column(db.String(150)) remarks = db.Column(db.Text) created_at = db.Column(db.DateTime, default=datetime.utcnow) updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) documents = db.relationship("AssetDocument", backref="asset", lazy=True) allocations = db.relationship("AssetAllocation", backref="asset", lazy=True) events = db.relationship("AssetEventLog", backref="asset", lazy=True) def __repr__(self): return f"" class AssetDocument(db.Model): __tablename__ = "asset_documents" id = db.Column(db.Integer, primary_key=True) asset_id = db.Column(db.Integer, db.ForeignKey("assets.id"), nullable=False) doc_type = db.Column(db.String(100)) file_name = db.Column(db.String(255)) file_path = db.Column(db.String(255)) uploaded_at = db.Column(db.DateTime, default=datetime.utcnow) uploaded_by_id = db.Column(db.Integer, db.ForeignKey("employee.id")) uploaded_by = db.relationship("Employee", foreign_keys=[uploaded_by_id]) def __repr__(self): return f"" class AssetAllocation(db.Model): __tablename__ = "asset_allocations" id = db.Column(db.Integer, primary_key=True) asset_id = db.Column(db.Integer, db.ForeignKey("assets.id"), nullable=False) employee_id = db.Column(db.Integer, db.ForeignKey("employee.id"), nullable=False) allocated_on = db.Column(db.Date, nullable=False) expected_return_date = db.Column(db.Date) actual_return_date = db.Column(db.Date) status = db.Column( db.String(30), default="pending", doc="pending / allocated / returned / lost / damaged / cancelled", ) condition_on_issue = db.Column(db.String(200)) condition_on_return = db.Column(db.String(200)) remarks = db.Column(db.Text) manager_id = db.Column(db.Integer, db.ForeignKey("employee.id")) manager_approved_at = db.Column(db.DateTime) admin_id = db.Column(db.Integer, db.ForeignKey("employee.id")) admin_approved_at = db.Column(db.DateTime) employee_acknowledged_at = db.Column(db.DateTime) created_at = db.Column(db.DateTime, default=datetime.utcnow) employee = db.relationship("Employee", foreign_keys=[employee_id]) manager = db.relationship("Employee", foreign_keys=[manager_id], post_update=True) admin = db.relationship("Employee", foreign_keys=[admin_id], post_update=True) loss_damage = db.relationship("AssetLossDamage", backref="allocation", uselist=False) def __repr__(self): return f"" class AssetLossDamage(db.Model): __tablename__ = "asset_loss_damage" id = db.Column(db.Integer, primary_key=True) asset_id = db.Column(db.Integer, db.ForeignKey("assets.id"), nullable=False) allocation_id = db.Column(db.Integer, db.ForeignKey("asset_allocations.id"), nullable=True) employee_id = db.Column(db.Integer, db.ForeignKey("employee.id"), nullable=False) incident_date = db.Column(db.Date, nullable=False) incident_type = db.Column(db.String(20), nullable=False, doc="loss / damage") severity = db.Column(db.String(50)) description = db.Column(db.Text) estimated_amount = db.Column(db.Numeric(12, 2)) approved_amount = db.Column(db.Numeric(12, 2)) payroll_recovery_month = db.Column(db.String(7)) # "YYYY-MM" status = db.Column( db.String(30), default="pending", doc="pending / approved / rejected / recovered", ) created_at = db.Column(db.DateTime, default=datetime.utcnow) created_by_id = db.Column(db.Integer, db.ForeignKey("employee.id")) approved_by_id = db.Column(db.Integer, db.ForeignKey("employee.id")) approved_at = db.Column(db.DateTime) asset = db.relationship("Asset", foreign_keys=[asset_id]) # <-- add this created_by = db.relationship("Employee", foreign_keys=[created_by_id], post_update=True) approved_by = db.relationship("Employee", foreign_keys=[approved_by_id], post_update=True) employee = db.relationship("Employee", foreign_keys=[employee_id], post_update=True) def __repr__(self): return f"" class AssetEventLog(db.Model): __tablename__ = "asset_event_logs" id = db.Column(db.Integer, primary_key=True) asset_id = db.Column(db.Integer, db.ForeignKey("assets.id"), nullable=False) employee_id = db.Column(db.Integer, db.ForeignKey("employee.id"), nullable=True) event_type = db.Column( db.String(50), nullable=False, doc="created / allocation_created / allocated / returned / status_change / loss / damage / note", ) event_time = db.Column(db.DateTime, default=datetime.utcnow, nullable=False) message = db.Column(db.Text) created_by_id = db.Column(db.Integer, db.ForeignKey("employee.id")) employee = db.relationship("Employee", foreign_keys=[employee_id], post_update=True) created_by = db.relationship("Employee", foreign_keys=[created_by_id], post_update=True) def __repr__(self): return f"" from datetime import datetime from extensions import db class BiometricSyncRun(db.Model): __tablename__ = "biometric_sync_run" id = db.Column(db.Integer, primary_key=True) sync_date = db.Column(db.Date, index=True, nullable=False) source_type = db.Column(db.String(20), default="api") # 'api' / 'db' source_name = db.Column(db.String(50), default="default") # e.g. machine name status = db.Column(db.String(20), default="running") # running/success/error message = db.Column(db.String(255)) punches_saved = db.Column(db.Integer, default=0) started_at = db.Column(db.DateTime, default=datetime.utcnow) ended_at = db.Column(db.DateTime) # optional: who triggered it (for manual syncs) triggered_by_id = db.Column(db.Integer, db.ForeignKey("employee.id"), nullable=True) triggered_by = db.relationship("Employee", backref="biometric_sync_runs") created_at = db.Column(db.DateTime, default=datetime.utcnow) class BiometricPunch(db.Model): __tablename__ = "biometric_punch" id = db.Column(db.Integer, primary_key=True) sync_run_id = db.Column(db.Integer, db.ForeignKey("biometric_sync_run.id"), nullable=True) sync_run = db.relationship("BiometricSyncRun", backref=db.backref("punches", lazy="dynamic")) # Normalised identity keys employee_id = db.Column(db.Integer, db.ForeignKey("employee.id"), nullable=True) external_employee_code = db.Column(db.String(64), index=True, nullable=True) # Core punch fields punch_time = db.Column(db.DateTime, nullable=False, index=True) direction = db.Column(db.String(10)) # 'in' / 'out' / None device_code = db.Column(db.String(50)) source_type = db.Column(db.String(20)) # 'api', 'db', etc. source_name = db.Column(db.String(100)) # e.g. 'default', 'machine_1' # For debugging/vendor-specific fields raw_data = db.Column(db.Text) created_at = db.Column(db.DateTime, default=datetime.utcnow) employee = db.relationship( "Employee", backref=db.backref("biometric_punches", lazy="dynamic") ) class BiometricEmployeeMap(db.Model): __tablename__ = "biometric_employee_map" id = db.Column(db.Integer, primary_key=True) employee_id = db.Column(db.Integer, db.ForeignKey("employee.id"), nullable=False) external_code = db.Column(db.String(64), nullable=False) # card_number from API source_name = db.Column(db.String(100), default="default") # match run.source_name is_active = db.Column(db.Boolean, default=True) created_at = db.Column(db.DateTime, default=datetime.utcnow) employee = db.relationship( "Employee", backref=db.backref("biometric_mappings", lazy="dynamic") ) __table_args__ = ( db.UniqueConstraint("external_code", "source_name", name="uq_biometric_code_source"), )