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)) employment_type = db.Column(db.String(20), default="payroll") payroll_remarks = db.Column(db.String(255)) last_appraisal_effective_month = db.Column(db.Date, nullable=True) @property def is_payroll(self) -> bool: return (self.employment_type or "payroll").lower() == "payroll" @property def is_stipend(self) -> bool: return (self.employment_type or "").lower() == "stipend" 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' broadcast_id = db.Column(db.Integer, db.ForeignKey('broadcast_message.id'), primary_key=True) department_id = db.Column(db.Integer, db.ForeignKey('department.id'), primary_key=True) class BroadcastRole(db.Model): __tablename__ = 'broadcast_roles' broadcast_id = db.Column(db.Integer, db.ForeignKey('broadcast_message.id'), primary_key=True) role_id = db.Column(db.Integer, db.ForeignKey('roles.id'), primary_key=True) 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 max_leaves_per_month = db.Column(db.Float) # e.g., max 2 days of this leave type in a month 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) half_day_session = db.Column(db.String(2), nullable=True) 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') informees = db.relationship( 'LeaveInformee', back_populates='leave_request', lazy='select', cascade='all, delete-orphan' ) 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(50)) # 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') class ReimbursementType(db.Model): __tablename__ = "reimbursement_type" id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(80), unique=True, nullable=False) is_active = db.Column(db.Boolean, default=True) # Conditional rules flags requires_km_fields = db.Column(db.Boolean, default=False) # kms + rate required requires_route_fields = db.Column(db.Boolean, default=False) # from/to required (locations) created_on = db.Column(db.DateTime, default=datetime.utcnow) updated_on = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) 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')) # ✅ NEW: reimbursement type + range reimb_type_id = db.Column(db.Integer, db.ForeignKey("reimbursement_type.id"), nullable=True, index=True) reimb_type = db.relationship("ReimbursementType") from_date = db.Column(db.Date, nullable=True, index=True) to_date = db.Column(db.Date, nullable=True, index=True) # Keep month/year for filtering + exports (derived from from_date) month = db.Column(db.Integer, nullable=True) # 1..12 year = db.Column(db.Integer, nullable=True) # Aggregates 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) # ❌ REMOVE old unique constraint (employee_id, month, year) 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')) # ✅ Item date is optional now (range is on request header) trip_date = db.Column(db.Date, nullable=True) # ✅ Optional for non-travel reimbursements from_loc = db.Column(db.String(120), nullable=True) to_loc = db.Column(db.String(120), nullable=True) kms_total = db.Column(db.Float, nullable=True) rate = db.Column(db.Float, nullable=True) # ✅ Always required amount = db.Column(db.Float, nullable=False) # ✅ Generic fields mode = db.Column(db.String(60), nullable=False) # Mode of transport (required as per spec) notes = db.Column(db.String(250)) # Remarks purpose = db.Column(db.String(200)) # keep (optional, unused in new UI but harmless) # ✅ Attachment is required by process (enforced in route) attachment_path = db.Column(db.String(255)) attachment_name = db.Column(db.String(200)) attachment_type = db.Column(db.String(100)) 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 AssetSubCategory(db.Model): __tablename__ = "asset_subcategories" # ✅ change this to match your DB (recommended) id = db.Column(db.Integer, primary_key=True) category_id = db.Column(db.Integer, db.ForeignKey("asset_categories.id"), nullable=False, index=True) name = db.Column(db.String(150), nullable=False) description = db.Column(db.Text, nullable=True) created_at = db.Column(db.DateTime, default=datetime.utcnow, nullable=False) category = db.relationship("AssetCategory", backref=db.backref("subcategories", lazy="dynamic")) __table_args__ = ( db.UniqueConstraint("category_id", "name", name="uq_asset_subcat_category_name"), ) 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) subcategory_id = db.Column(db.Integer, db.ForeignKey("asset_subcategories.id"), nullable=True, index=True) subcategory = db.relationship("AssetSubCategory", foreign_keys=[subcategory_id]) 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="in_stock",doc="in_stock / allocated / under_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) quantity_total = db.Column(db.Integer, nullable=False, default=1) quantity_available = db.Column(db.Integer, nullable=False, default=1) image_path = db.Column(db.String(255), nullable=True) image_name = db.Column(db.String(255), nullable=True) def __init__(self, **kwargs): super().__init__(**kwargs) if self.quantity_total is None: self.quantity_total = 1 if self.quantity_available is None: self.quantity_available = self.quantity_total 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) quantity = db.Column(db.Integer, nullable=False, default=1) 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) employee_id = db.Column(db.Integer, db.ForeignKey("employee.id"), nullable=True) external_employee_code = db.Column(db.String(50)) punch_time = db.Column(db.DateTime, nullable=False) direction = db.Column(db.String(10)) device_code = db.Column(db.String(50)) source_type = db.Column(db.String(20)) source_name = db.Column(db.String(50)) raw_data = db.Column(db.Text) latitude = db.Column(db.Float) longitude = db.Column(db.Float) location_note = db.Column(db.String(255)) photo_path = db.Column(db.String(255)) photo_taken_at = db.Column(db.DateTime, nullable=True) photo_is_fresh = db.Column(db.Boolean, nullable=True) # True/False/None approval_status = db.Column(db.String(20), default="pending") approved_by_id = db.Column(db.Integer, db.ForeignKey("employee.id")) approved_on = db.Column(db.DateTime) rejection_reason = db.Column(db.String(255)) # FIXED RELATIONSHIPS employee = db.relationship( "Employee", foreign_keys=[employee_id], backref=db.backref("biometric_punches", lazy="dynamic") ) approved_by = db.relationship( "Employee", foreign_keys=[approved_by_id], backref="approved_self_punches" ) 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"), ) class SalaryHeadAlias(db.Model): __tablename__ = "salary_head_aliases" id = db.Column(db.Integer, primary_key=True) logical_key = db.Column(db.String(50), nullable=False) # e.g. 'basic', 'hra', 'pf' head_type = db.Column(db.String(20), nullable=False) # 'earning', 'deduction', 'ctc' head_name = db.Column(db.String(255), nullable=False) # must match EarningHead/DeductionHead.name __table_args__ = ( db.UniqueConstraint("logical_key", name="uq_salary_head_alias_logical"), ) class LeaveInformee(db.Model): __tablename__ = "leave_informee" id = db.Column(db.Integer, primary_key=True) leave_request_id = db.Column( db.Integer, db.ForeignKey("leave_request.id"), nullable=False ) employee_id = db.Column( db.Integer, db.ForeignKey("employee.id"), nullable=False ) leave_request = db.relationship( 'LeaveRequest', back_populates='informees' ) # Relationship to Employee (name is your choice, just not 'leave_request') employee = db.relationship( 'Employee', backref='informed_leave_links' ) # --------------------------------------------------------- # Recruitment models # --------------------------------------------------------- from datetime import datetime from extensions import db # models.py (add after existing imports / models) from extensions import db from datetime import datetime # ------------------------- # Recruitment templates # ------------------------- class RecruitmentTemplate(db.Model): __tablename__ = "recruitment_templates" id = db.Column(db.Integer, primary_key=True) designation_id = db.Column(db.Integer, db.ForeignKey("designation.id"), unique=True, nullable=False) min_education = db.Column(db.String(255)) min_experience_years = db.Column(db.Float) age_bracket = db.Column(db.String(100)) salary_bracket = db.Column(db.String(100)) accountability = db.Column(db.Text) job_description = db.Column(db.Text) extra_json = db.Column(db.JSON) created_on = db.Column(db.DateTime, default=datetime.utcnow) updated_on = db.Column( db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow ) designation = db.relationship("Designation", backref="recruitment_template") kras = db.relationship("TemplateKRA", backref="template", cascade="all, delete-orphan") kpis = db.relationship("TemplateKPI", backref="template", cascade="all, delete-orphan") skills = db.relationship("TemplateSkill", backref="template", cascade="all, delete-orphan") class TemplateKRA(db.Model): __tablename__ = "template_kras" id = db.Column(db.Integer, primary_key=True) template_id = db.Column(db.Integer, db.ForeignKey("recruitment_templates.id"), nullable=False) name = db.Column(db.String(255), nullable=False) description = db.Column(db.Text) weightage = db.Column(db.Float) created_on = db.Column(db.DateTime, default=datetime.utcnow) kpis = db.relationship("TemplateKPI", backref="kra") class TemplateKPI(db.Model): __tablename__ = "template_kpis" id = db.Column(db.Integer, primary_key=True) template_id = db.Column(db.Integer, db.ForeignKey("recruitment_templates.id"), nullable=False) kra_id = db.Column(db.Integer, db.ForeignKey("template_kras.id")) name = db.Column(db.String(255), nullable=False) target = db.Column(db.String(255)) weightage = db.Column(db.Float) created_on = db.Column(db.DateTime, default=datetime.utcnow) class TemplateSkill(db.Model): __tablename__ = "template_skills" id = db.Column(db.Integer, primary_key=True) template_id = db.Column(db.Integer, db.ForeignKey("recruitment_templates.id"), nullable=False) name = db.Column(db.String(255), nullable=False) basic_requirement = db.Column(db.Text) created_on = db.Column(db.DateTime, default=datetime.utcnow) questions = db.relationship("TemplateSkillQuestion", backref="skill", cascade="all, delete-orphan") class TemplateSkillQuestion(db.Model): __tablename__ = "template_skill_questions" id = db.Column(db.Integer, primary_key=True) skill_id = db.Column(db.Integer, db.ForeignKey("template_skills.id"), nullable=False) question_text = db.Column(db.Text, nullable=False) created_on = db.Column(db.DateTime, default=datetime.utcnow) # ------------------------- # Requisition & approvals # ------------------------- class Requisition(db.Model): __tablename__ = "requisitions" id = db.Column(db.Integer, primary_key=True) code = db.Column(db.String(50), unique=True) raised_by_id = db.Column(db.Integer, db.ForeignKey("employee.id"), nullable=False) manager_id = db.Column(db.Integer, db.ForeignKey("employee.id")) hr_owner_id = db.Column(db.Integer, db.ForeignKey("employee.id")) designation_id = db.Column(db.Integer, db.ForeignKey("designation.id"), nullable=False) department_id = db.Column(db.Integer, db.ForeignKey("department.id")) positions = db.Column(db.Integer, default=1) location = db.Column(db.String(100)) work_mode = db.Column(db.String(50)) employment_type = db.Column(db.String(50)) expected_doj = db.Column(db.Date) budget_ctc_min = db.Column(db.Float) budget_ctc_max = db.Column(db.Float) justification = db.Column(db.Text) status = db.Column(db.String(30), default="draft") # draft/pending_manager/pending_hr/approved/rejected snapshot_json = db.Column(db.JSON) created_on = db.Column(db.DateTime, default=datetime.utcnow) updated_on = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) raised_by = db.relationship("Employee", foreign_keys=[raised_by_id]) manager = db.relationship("Employee", foreign_keys=[manager_id]) hr_owner = db.relationship("Employee", foreign_keys=[hr_owner_id]) designation = db.relationship("Designation") department = db.relationship("Department") approvals = db.relationship("RequisitionApproval", backref="requisition", cascade="all, delete-orphan") stages = db.relationship("RequisitionStage", backref="requisition", cascade="all, delete-orphan") applications = db.relationship("Application", backref="requisition", cascade="all, delete-orphan") # ✅ HOLD SUPPORT hold_prev_status = db.Column(db.String(30), nullable=True) # where it was before hold hold_reason = db.Column(db.Text, nullable=True) hold_on = db.Column(db.DateTime, nullable=True) hold_by_id = db.Column(db.Integer, db.ForeignKey("employee.id"), nullable=True) hold_by = db.relationship("Employee", foreign_keys=[hold_by_id]) class RequisitionApproval(db.Model): __tablename__ = "requisition_approvals" id = db.Column(db.Integer, primary_key=True) requisition_id = db.Column(db.Integer, db.ForeignKey("requisitions.id"), nullable=False) approver_id = db.Column(db.Integer, db.ForeignKey("employee.id"), nullable=False) level = db.Column(db.String(20)) # 'manager' / 'hr' status = db.Column(db.String(20), default="pending") # pending/approved/rejected remarks = db.Column(db.Text) decided_on = db.Column(db.DateTime) approver = db.relationship("Employee") # ------------------------- # Candidates & applications # ------------------------- class Candidate(db.Model): __tablename__ = "candidates" id = db.Column(db.Integer, primary_key=True) full_name = db.Column(db.String(255), nullable=False) email = db.Column(db.String(255)) phone = db.Column(db.String(50)) total_experience_years = db.Column(db.Float) current_company = db.Column(db.String(255)) current_ctc = db.Column(db.Float) expected_ctc = db.Column(db.Float) location = db.Column(db.String(255)) resume_path = db.Column(db.String(255)) source = db.Column(db.String(50)) # public / hr_upload / referral / agency created_on = db.Column(db.DateTime, default=datetime.utcnow) updated_on = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) applications = db.relationship("Application", backref="candidate", cascade="all, delete-orphan") class RequisitionStage(db.Model): __tablename__ = "requisition_stages" id = db.Column(db.Integer, primary_key=True) requisition_id = db.Column(db.Integer, db.ForeignKey("requisitions.id"), nullable=False) name = db.Column(db.String(100), nullable=False) # e.g. HR Round, Tech Round order = db.Column(db.Integer, nullable=False) is_final = db.Column(db.Boolean, default=False) created_on = db.Column(db.DateTime, default=datetime.utcnow) class Application(db.Model): __tablename__ = "applications" id = db.Column(db.Integer, primary_key=True) requisition_id = db.Column(db.Integer, db.ForeignKey("requisitions.id"), nullable=False) candidate_id = db.Column(db.Integer, db.ForeignKey("candidates.id"), nullable=False) applied_on = db.Column(db.DateTime, default=datetime.utcnow) status = db.Column(db.String(30), default="applied") # applied/in_process/hired/rejected current_stage_id = db.Column(db.Integer, db.ForeignKey("requisition_stages.id")) final_recommendation = db.Column(db.String(20)) # hire/reject/hold final_remarks = db.Column(db.Text) current_stage = db.relationship("RequisitionStage") interviews = db.relationship("Interview", backref="application", cascade="all, delete-orphan") # ------------------------- # Interviews & feedback # ------------------------- class Interview(db.Model): __tablename__ = "interviews" id = db.Column(db.Integer, primary_key=True) application_id = db.Column(db.Integer, db.ForeignKey("applications.id"), nullable=False) stage_id = db.Column(db.Integer, db.ForeignKey("requisition_stages.id"), nullable=False) interviewer_id = db.Column(db.Integer, db.ForeignKey("employee.id"), nullable=False) scheduled_at = db.Column(db.DateTime) mode = db.Column(db.String(50)) # Online / In Person / Phone location = db.Column(db.String(255)) # or meeting link status = db.Column(db.String(20), default="scheduled") # scheduled/completed/cancelled created_on = db.Column(db.DateTime, default=datetime.utcnow) updated_on = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) interviewer = db.relationship("Employee") stage = db.relationship("RequisitionStage") feedbacks = db.relationship("InterviewFeedback", backref="interview", cascade="all, delete-orphan") class InterviewFeedback(db.Model): __tablename__ = "interview_feedbacks" id = db.Column(db.Integer, primary_key=True) interview_id = db.Column(db.Integer, db.ForeignKey("interviews.id"), nullable=False) interviewer_id = db.Column(db.Integer, db.ForeignKey("employee.id"), nullable=False) overall_rating = db.Column(db.Integer) # 1-10 recommendation = db.Column(db.String(20)) # hire/reject/hold comments = db.Column(db.Text) created_on = db.Column(db.DateTime, default=datetime.utcnow) interviewer = db.relationship("Employee") items = db.relationship("InterviewFeedbackItem", backref="feedback", cascade="all, delete-orphan") class InterviewFeedbackItem(db.Model): __tablename__ = "interview_feedback_items" id = db.Column(db.Integer, primary_key=True) feedback_id = db.Column(db.Integer, db.ForeignKey("interview_feedbacks.id"), nullable=False) question_text = db.Column(db.Text, nullable=False) skill_name = db.Column(db.String(255)) rating = db.Column(db.Integer) comment = db.Column(db.Text) from extensions import db from datetime import date from sqlalchemy import Enum as SAEnum class Loan(db.Model): __tablename__ = "loan" id = db.Column(db.Integer, primary_key=True) # Who the loan belongs to employee_id = db.Column(db.Integer, db.ForeignKey("employee.id"), nullable=False) employee = db.relationship( "Employee", foreign_keys=[employee_id], primaryjoin="Loan.employee_id == Employee.id", backref="loans" ) loan_type = db.Column(db.String(50), default="loan") requested_amount = db.Column(db.Numeric(12, 2), nullable=False) sanctioned_amount = db.Column(db.Numeric(12, 2)) requested_tenure_months = db.Column(db.Integer, nullable=False) tenure_months = db.Column(db.Integer) emi_amount = db.Column(db.Numeric(12, 2)) start_month = db.Column(db.Date) status = db.Column(db.String(30), nullable=False, default="requested") requested_on = db.Column(db.Date, default=date.today, nullable=False) approved_on = db.Column(db.Date) closed_on = db.Column(db.Date) repayment_mode = db.Column(db.String(30), nullable=False, default="salary") security_cheque_required = db.Column(db.Boolean, default=False) security_cheque_received = db.Column(db.Boolean, default=False) security_cheque_no = db.Column(db.String(50)) security_cheque_bank = db.Column(db.String(100)) security_cheque_date = db.Column(db.Date) security_cheque_remarks = db.Column(db.String(255)) # created by created_by_id = db.Column(db.Integer, db.ForeignKey("employee.id")) created_by = db.relationship( "Employee", foreign_keys=[created_by_id], primaryjoin="Loan.created_by_id == Employee.id", backref="loans_created" ) # approved by approved_by_id = db.Column(db.Integer, db.ForeignKey("employee.id")) approved_by = db.relationship( "Employee", foreign_keys=[approved_by_id], primaryjoin="Loan.approved_by_id == Employee.id", backref="loans_approved" ) remarks = db.Column(db.Text) def is_active(self): return self.status in ("approved", "active") def outstanding_amount(self): total_dr = sum(t.amount for t in self.transactions if t.direction == "DR") total_cr = sum(t.amount for t in self.transactions if t.direction == "CR") return float(total_dr - total_cr) @property def total_paid(self): """Total CR (repayments) for display.""" return float(sum(t.amount for t in self.transactions if t.direction == "CR")) @property def outstanding(self): """Alias for templates.""" return self.outstanding_amount() class LoanEMISchedule(db.Model): __tablename__ = "loan_emi_schedule" id = db.Column(db.Integer, primary_key=True) loan_id = db.Column(db.Integer, db.ForeignKey("loan.id"), nullable=False) loan = db.relationship("Loan", backref="emi_schedule") # store first day of month as the due date due_month = db.Column(db.Date, nullable=False) amount = db.Column(db.Numeric(12, 2), nullable=False) status = db.Column( db.String(30), default="pending", # 'pending', 'paid', 'skipped_approved', 'skipped_rejected' nullable=False, ) paid_on = db.Column(db.Date, nullable=True) paid_amount = db.Column(db.Numeric(12, 2), nullable=True) payroll_id = db.Column(db.Integer, db.ForeignKey("payroll.id"), nullable=True) payroll = db.relationship("Payroll", backref="loan_emis") remarks = db.Column(db.String(255)) class LoanTransaction(db.Model): __tablename__ = "loan_transaction" id = db.Column(db.Integer, primary_key=True) loan_id = db.Column(db.Integer, db.ForeignKey("loan.id"), nullable=False) loan = db.relationship("Loan", backref="transactions") txn_date = db.Column(db.Date, nullable=False, default=date.today) # 'disbursement', 'emi', 'extra_payment', 'adjustment', 'ff_adjustment', 'skip' txn_type = db.Column(db.String(50), nullable=False) # DR = company gave money to employee (principal) # CR = employee repaying direction = db.Column(db.String(2), nullable=False) # 'DR' or 'CR' amount = db.Column(db.Numeric(12, 2), nullable=False) reference = db.Column(db.String(100)) # e.g., payroll month, transaction id, cheque no, etc. source = db.Column(db.String(50)) # 'payroll', 'manual', 'ff', 'self_payment' created_by_id = db.Column(db.Integer, db.ForeignKey("employee.id")) created_by = db.relationship("Employee", foreign_keys=[created_by_id]) remarks = db.Column(db.Text) class LoanSkipRequest(db.Model): __tablename__ = "loan_skip_request" id = db.Column(db.Integer, primary_key=True) loan_id = db.Column(db.Integer, db.ForeignKey("loan.id"), nullable=False) loan = db.relationship("Loan", backref="skip_requests") emi_schedule_id = db.Column(db.Integer, db.ForeignKey("loan_emi_schedule.id")) emi = db.relationship("LoanEMISchedule") request_month = db.Column(db.Date, nullable=False) # first day of month reason = db.Column(db.Text) attachment_path = db.Column(db.String(255)) # if you allow upload status = db.Column( db.String(30), default="pending", # pending, approved, rejected nullable=False, ) requested_on = db.Column(db.Date, default=date.today) approved_on = db.Column(db.Date) approved_by_id = db.Column(db.Integer, db.ForeignKey("employee.id")) approved_by = db.relationship("Employee", foreign_keys=[approved_by_id]) admin_remarks = db.Column(db.Text) class LoanExtraPaymentRequest(db.Model): __tablename__ = "loan_extra_payment_request" id = db.Column(db.Integer, primary_key=True) loan_id = db.Column(db.Integer, db.ForeignKey("loan.id"), nullable=False) loan = db.relationship("Loan", backref="extra_payment_requests") amount = db.Column(db.Numeric(12, 2), nullable=False) payment_mode = db.Column(db.String(50)) # Cheque / UPI / Online / Other payment_ref = db.Column(db.String(100)) attachment_path = db.Column(db.String(255)) txn_date = db.Column(db.Date, default=date.today) status = db.Column( db.String(30), default="pending", nullable=False ) # pending, approved, rejected requested_on = db.Column(db.Date, default=date.today) approved_on = db.Column(db.Date) approved_by_id = db.Column(db.Integer, db.ForeignKey("employee.id")) approved_by = db.relationship("Employee", foreign_keys=[approved_by_id]) admin_remarks = db.Column(db.Text) # --- Loan config models --------------------------------- class LoanType(db.Model): __tablename__ = "loan_type" id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(50), unique=True, nullable=False) # internal name label = db.Column(db.String(100), nullable=False) # what user sees description = db.Column(db.String(255)) # optional help text max_amount = db.Column(db.Numeric(12, 2)) # optional max_tenure_months = db.Column(db.Integer) # optional is_active = db.Column(db.Boolean, default=True) display_order = db.Column(db.Integer, default=1) class LoanSetting(db.Model): __tablename__ = "loan_setting" id = db.Column(db.Integer, primary_key=True) # free-form HTML or markdown for the “Loan Policy Snapshot” box policy_html = db.Column(db.Text) # optional global rules you already had hard-coded min_months_service = db.Column(db.Integer, default=12) min_amount = db.Column(db.Numeric(12, 2), default=5000) max_amount = db.Column(db.Numeric(12, 2), default=200000) class AssetLog(db.Model): __tablename__ = "asset_logs" id = db.Column(db.Integer, primary_key=True) asset_id = db.Column(db.Integer, db.ForeignKey("assets.id"), nullable=False) # action codes: new_asset, inventory, allocated, reallocated, returned, # under_repair, retired, lost, damaged, etc. action = db.Column(db.String(50), nullable=False) from_employee_id = db.Column(db.Integer, db.ForeignKey("employee.id"), nullable=True) to_employee_id = db.Column(db.Integer, db.ForeignKey("employee.id"), nullable=True) notes = db.Column(db.Text, nullable=True) created_at = db.Column(db.DateTime, default=datetime.utcnow, nullable=False) created_by_id = db.Column(db.Integer, db.ForeignKey("employee.id"), nullable=True) asset = relationship("Asset", backref="logs") from_employee = relationship("Employee", foreign_keys=[from_employee_id]) to_employee = relationship("Employee", foreign_keys=[to_employee_id]) created_by = relationship("Employee", foreign_keys=[created_by_id]) class BirthdayEmailLog(db.Model): __tablename__ = "birthday_email_log" id = db.Column(db.Integer, primary_key=True) employee_id = db.Column(db.Integer, db.ForeignKey("employee.id"), nullable=True, index=True) year = db.Column(db.Integer, nullable=False, index=True) # The employee’s actual birthday date for that year (used for “2 days before” check) target_date = db.Column(db.Date, nullable=False, index=True) sent_at = db.Column(db.DateTime, default=datetime.utcnow, nullable=False, index=True) recipients = db.Column(db.Text, nullable=False) # store comma-separated emails subject = db.Column(db.String(255), nullable=True) status = db.Column(db.String(50), nullable=False, default="sent") # sent/failed/skipped error = db.Column(db.Text, nullable=True) employee = db.relationship("Employee", backref="birthday_email_logs") from datetime import date, datetime from sqlalchemy import UniqueConstraint, Index from extensions import db # --------------------------------------------------------------------- # Helpers # --------------------------------------------------------------------- def fy_for(d: date) -> int: """Return FY start year. FY 2025 means Apr-2025..Mar-2026.""" if d.month >= 4: return d.year return d.year - 1 # --------------------------------------------------------------------- # Masters # --------------------------------------------------------------------- class AppraisalQuestion(db.Model): __tablename__ = "appraisal_question" id = db.Column(db.Integer, primary_key=True) # who answers this question target = db.Column(db.String(20), nullable=False) # 'employee' or 'manager' question_text = db.Column(db.Text, nullable=False) weight = db.Column(db.Float, nullable=False, default=1.0) is_active = db.Column(db.Boolean, nullable=False, default=True) sort_order = db.Column(db.Integer, nullable=False, default=0) created_at = db.Column(db.DateTime, default=datetime.utcnow, nullable=False) class AppraisalScoreRule(db.Model): """ Score% -> recommended increment%. Example: 90-100 => 18 """ __tablename__ = "appraisal_score_rule" id = db.Column(db.Integer, primary_key=True) min_score_pct = db.Column(db.Float, nullable=False) # inclusive max_score_pct = db.Column(db.Float, nullable=False) # inclusive recommended_increment_pct = db.Column(db.Float, nullable=False) is_active = db.Column(db.Boolean, nullable=False, default=True) created_at = db.Column(db.DateTime, default=datetime.utcnow, nullable=False) # --------------------------------------------------------------------- # Budgets (annualized impact) # --------------------------------------------------------------------- class DeptAppraisalBudget(db.Model): __tablename__ = "dept_appraisal_budget" id = db.Column(db.Integer, primary_key=True) fy_start_year = db.Column(db.Integer, nullable=False) location = db.Column(db.String(100), nullable=False) # NEW department_id = db.Column(db.Integer, db.ForeignKey("department.id"), nullable=False) budget_amount = db.Column(db.Float, nullable=False, default=0.0) created_by_id = db.Column(db.Integer, db.ForeignKey("employee.id"), nullable=True) created_at = db.Column(db.DateTime, default=datetime.utcnow, nullable=False) __table_args__ = ( UniqueConstraint("fy_start_year", "location", "department_id", name="uq_budget_fy_loc_dept"), Index("ix_budget_fy", "fy_start_year"), Index("ix_budget_loc", "location"), ) # --------------------------------------------------------------------- # Appraisal transaction # --------------------------------------------------------------------- class Appraisal(db.Model): __tablename__ = "appraisal" id = db.Column(db.Integer, primary_key=True) employee_id = db.Column(db.Integer, db.ForeignKey("employee.id"), nullable=False) manager_id = db.Column(db.Integer, db.ForeignKey("employee.id"), nullable=True) department_id = db.Column(db.Integer, db.ForeignKey("department.id"), nullable=True) process_type = db.Column(db.String(5), nullable=False) # 'P1','P2','P3' # snapshots current_gross = db.Column(db.Float, nullable=False, default=0.0) # effective month logic: store as first day of month effective_month = db.Column(db.Date, nullable=False) # statuses status = db.Column(db.String(30), nullable=False, default="initiated") # initiated # awaiting_employee # awaiting_manager # scored # hr_finalized # applied # cancelled # scoring employee_score_pct = db.Column(db.Float, nullable=True) manager_score_pct = db.Column(db.Float, nullable=True) total_score_pct = db.Column(db.Float, nullable=True) recommended_increment_pct = db.Column(db.Float, nullable=True) # HR final decision final_increment_pct = db.Column(db.Float, nullable=True) final_new_gross = db.Column(db.Float, nullable=True) # Budget accounting annualized_impact = db.Column(db.Float, nullable=True) # (new - old)*12 # timestamps initiated_at = db.Column(db.DateTime, default=datetime.utcnow, nullable=False) initiated_by_id = db.Column(db.Integer, db.ForeignKey("employee.id"), nullable=True) employee_submitted_at = db.Column(db.DateTime, nullable=True) manager_submitted_at = db.Column(db.DateTime, nullable=True) hr_finalized_at = db.Column(db.DateTime, nullable=True) hr_finalized_by_id = db.Column(db.Integer, db.ForeignKey("employee.id"), nullable=True) applied_at = db.Column(db.DateTime, nullable=True) applied_by_id = db.Column(db.Integer, db.ForeignKey("employee.id"), nullable=True) remarks = db.Column(db.Text, nullable=True) employee = db.relationship("Employee", foreign_keys=[employee_id]) manager = db.relationship("Employee", foreign_keys=[manager_id]) department = db.relationship("Department", foreign_keys=[department_id]) location = db.Column(db.String(100), nullable=True) __table_args__ = ( Index("ix_appraisal_employee", "employee_id"), Index("ix_appraisal_effective_month", "effective_month"), Index("ix_appraisal_status", "status"), ) class AppraisalResponse(db.Model): """ One response per appraisal per responder (employee/manager). Answers stored in child table. """ __tablename__ = "appraisal_response" id = db.Column(db.Integer, primary_key=True) appraisal_id = db.Column(db.Integer, db.ForeignKey("appraisal.id"), nullable=False) responder_type = db.Column(db.String(20), nullable=False) # 'employee' or 'manager' responder_id = db.Column(db.Integer, db.ForeignKey("employee.id"), nullable=True) submitted_at = db.Column(db.DateTime, nullable=True) __table_args__ = ( UniqueConstraint("appraisal_id", "responder_type", name="uq_response_appraisal_responder_type"), Index("ix_response_appraisal", "appraisal_id"), ) class AppraisalAnswer(db.Model): __tablename__ = "appraisal_answer" id = db.Column(db.Integer, primary_key=True) response_id = db.Column(db.Integer, db.ForeignKey("appraisal_response.id"), nullable=False) question_id = db.Column(db.Integer, db.ForeignKey("appraisal_question.id"), nullable=False) rating = db.Column(db.Integer, nullable=False) # 1..5 only comment = db.Column(db.Text, nullable=True) __table_args__ = ( UniqueConstraint("response_id", "question_id", name="uq_answer_response_question"), Index("ix_answer_response", "response_id"), ) class AppraisalAuditLog(db.Model): __tablename__ = "appraisal_audit_log" id = db.Column(db.Integer, primary_key=True) appraisal_id = db.Column(db.Integer, db.ForeignKey("appraisal.id"), nullable=True) action = db.Column(db.String(50), nullable=False) old_data = db.Column(db.JSON, nullable=True) new_data = db.Column(db.JSON, nullable=True) created_by_id = db.Column(db.Integer, db.ForeignKey("employee.id"), nullable=True) created_at = db.Column(db.DateTime, default=datetime.utcnow, nullable=False) class GlobalAppraisalBudget(db.Model): __tablename__ = "global_appraisal_budget" id = db.Column(db.Integer, primary_key=True) fy_start_year = db.Column(db.Integer, nullable=False) # e.g. 2025 budget_amount = db.Column(db.Float, nullable=False, default=0.0) created_by_id = db.Column(db.Integer, db.ForeignKey("employee.id"), nullable=True) created_at = db.Column(db.DateTime, default=datetime.utcnow, nullable=False) __table_args__ = ( UniqueConstraint("fy_start_year", name="uq_budget_fy_global"), Index("ix_global_budget_fy", "fy_start_year"), ) class LocationAppraisalBudget(db.Model): __tablename__ = "location_appraisal_budget" id = db.Column(db.Integer, primary_key=True) fy_start_year = db.Column(db.Integer, nullable=False) location = db.Column(db.String(100), nullable=False) # store Employee.location text budget_amount = db.Column(db.Float, nullable=False, default=0.0) created_by_id = db.Column(db.Integer, db.ForeignKey("employee.id"), nullable=True) created_at = db.Column(db.DateTime, default=datetime.utcnow, nullable=False) __table_args__ = ( UniqueConstraint("fy_start_year", "location", name="uq_budget_fy_location"), Index("ix_loc_budget_fy", "fy_start_year"), Index("ix_loc_budget_location", "location"), ) class DeviceToken(db.Model): __tablename__ = "device_tokens" id = db.Column(db.Integer, primary_key=True) employee_id = db.Column(db.Integer, db.ForeignKey("employee.id"), index=True, nullable=False) token = db.Column(db.String(255), unique=True, index=True, nullable=False) platform = db.Column(db.String(20), default="unknown") # ios | android is_active = db.Column(db.Boolean, default=True, index=True) created_at = db.Column(db.DateTime, default=datetime.utcnow, nullable=False) last_seen_at = db.Column(db.DateTime, default=datetime.utcnow, nullable=False) class AssistantConversation(db.Model): __tablename__ = "assistant_conversations" id = db.Column(db.Integer, primary_key=True) user_id = db.Column(db.Integer, db.ForeignKey("employee.id"), nullable=False, index=True) title = db.Column(db.String(200), nullable=True) is_active = db.Column(db.Boolean, default=True, nullable=False) created_at = db.Column(db.DateTime, default=datetime.utcnow, nullable=False) updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False) messages = db.relationship( "AssistantMessage", backref="conversation", lazy="dynamic", cascade="all, delete-orphan" ) __table_args__ = ( db.Index("ix_assistant_conv_user_active_updated", "user_id", "is_active", "updated_at"), ) class AssistantMessage(db.Model): __tablename__ = "assistant_messages" id = db.Column(db.Integer, primary_key=True) conversation_id = db.Column(db.Integer, db.ForeignKey("assistant_conversations.id"), nullable=False, index=True) role = db.Column(db.String(20), nullable=False) # 'user' | 'assistant' | 'tool' content = db.Column(db.Text, nullable=False) created_at = db.Column(db.DateTime, default=datetime.utcnow, nullable=False) __table_args__ = ( db.Index("ix_assistant_msg_conv_created", "conversation_id", "created_at"), ) # models.py from datetime import datetime from extensions import db class EmployeeInstituteAccess(db.Model): __tablename__ = "employee_institute_access" id = db.Column(db.Integer, primary_key=True) viewer_employee_id = db.Column(db.Integer, db.ForeignKey("employee.id"), nullable=False, index=True) institute = db.Column(db.String(150), nullable=False, index=True) created_at = db.Column(db.DateTime, default=datetime.utcnow, nullable=False) viewer = db.relationship( "Employee", foreign_keys=[viewer_employee_id], backref=db.backref("institute_access", lazy="dynamic", cascade="all, delete-orphan") ) __table_args__ = ( db.UniqueConstraint("viewer_employee_id", "institute", name="uq_viewer_institute"), ) # ---------------------------- # Incentive Module (Generic) # ---------------------------- class IncentivePolicy(db.Model): __tablename__ = "incentive_policy" id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(200), nullable=False) description = db.Column(db.Text) period_type = db.Column(db.Enum("monthly", "quarterly", "custom"), default="monthly", nullable=False) start_date = db.Column(db.Date, nullable=True) end_date = db.Column(db.Date, nullable=True) # Scope/eligibility rules (frontend configurable) # Example: # { "include_all": true } # { "include_all": false, "filters": { "locations": ["Cochin"], "departments": [1,2] } } eligibility_json = db.Column(db.JSON, nullable=False, default=dict) # Payment mode governance allow_cash = db.Column(db.Boolean, default=False) allow_bank = db.Column(db.Boolean, default=True) status = db.Column(db.Enum("draft", "active", "closed"), default="draft", nullable=False) created_by = db.Column(db.Integer, db.ForeignKey("employee.id"), nullable=True) created_on = db.Column(db.DateTime, default=datetime.utcnow) targets = db.relationship("IncentiveTarget", backref="policy", cascade="all, delete-orphan") class IncentiveTarget(db.Model): __tablename__ = "incentive_target" id = db.Column(db.Integer, primary_key=True) policy_id = db.Column(db.Integer, db.ForeignKey("incentive_policy.id"), nullable=False) # Who is targeted (works for location/institute/department/group/individual) target_scope = db.Column(db.Enum("location", "institute", "department", "group", "individual"), nullable=False) # Reference for the scope (kept generic) # - location/institute: string # - department: department_id as string/int # - group: group code/name # - individual: employee_id scope_ref = db.Column(db.String(200), nullable=True) # What metric is measured (generic) # Example: "shipments", "revenue", "sales", "tickets_closed", etc. metric_key = db.Column(db.String(100), nullable=False) # Where it comes from (manual/system/api) – you can expand later metric_source = db.Column(db.Enum("manual", "system", "api"), default="manual", nullable=False) # Target value + units target_value = db.Column(db.Float, nullable=False, default=0.0) unit = db.Column(db.String(50), nullable=True) # "count", "₹", "%", etc. # Optional min-eligibility rules for this target # Example: { "min_days_present": 20 } eligibility_json = db.Column(db.JSON, nullable=False, default=dict) schemes = db.relationship("IncentiveScheme", backref="target", cascade="all, delete-orphan") created_on = db.Column(db.DateTime, default=datetime.utcnow) class IncentiveScheme(db.Model): __tablename__ = "incentive_scheme" id = db.Column(db.Integer, primary_key=True) target_id = db.Column(db.Integer, db.ForeignKey("incentive_target.id"), nullable=False) reward_type = db.Column(db.Enum("fixed_total_amount", "variable_amount", "slab_based"), nullable=False) # Total pool amount (for pool-based distribution) total_amount = db.Column(db.Float, nullable=True) currency = db.Column(db.String(10), default="INR") # Scheme config (for slabs, per-x bonus etc.) # Example slab config: # { "slabs": [{"from":0,"to":100,"amount":1000}, ...], "increment_per_x": 50, "increment_amount": 1000 } scheme_json = db.Column(db.JSON, nullable=False, default=dict) distribution_rules = db.relationship("IncentiveDistributionRule", backref="scheme", cascade="all, delete-orphan") created_on = db.Column(db.DateTime, default=datetime.utcnow) class IncentiveDistributionRule(db.Model): __tablename__ = "incentive_distribution_rule" id = db.Column(db.Integer, primary_key=True) scheme_id = db.Column(db.Integer, db.ForeignKey("incentive_scheme.id"), nullable=False) distribution_type = db.Column( db.Enum( "equal", "fixed_amount", "fixed_per_employee", "percentage", "slab", "workload_based", "custom_formula", ), nullable=False, ) description = db.Column(db.String(255), nullable=True) # Only allow slab for individual targets (enforced in service logic) is_individual_only = db.Column(db.Boolean, default=False) requires_approval = db.Column(db.Boolean, default=True) # Config JSON: # equal: {} # fixed_amount: { "amount": 50000 } (pool) # fixed_per_employee: { "amount": 5000 } # percentage: { "percentage": 10 } # slab: { "slabs":[...], "increment_per_x":50,"increment_amount":1000 } # workload_based: { "weights": [{"department_id":1,"weight":0.3}, ...] } # custom_formula: { "formula":"(achievement/target)*total_amount*weight", "defaults":{"weight":1} } config_json = db.Column(db.JSON, nullable=False, default=dict) created_on = db.Column(db.DateTime, default=datetime.utcnow) class IncentiveCalculationBatch(db.Model): """ A "run" of calculations for a policy + period. Freeze-like behavior: once approved/locked, numbers should not change. """ __tablename__ = "incentive_calc_batch" id = db.Column(db.Integer, primary_key=True) policy_id = db.Column(db.Integer, db.ForeignKey("incentive_policy.id"), nullable=False) period_key = db.Column(db.String(20), nullable=False) # "YYYY-MM" for monthly, or "YYYY-Q1", etc. status = db.Column(db.Enum("draft", "approved", "paid"), default="draft", nullable=False) # Snapshot inputs (optional but helpful for audit) inputs_json = db.Column(db.JSON, nullable=False, default=dict) created_by = db.Column(db.Integer, db.ForeignKey("employee.id"), nullable=True) created_on = db.Column(db.DateTime, default=datetime.utcnow) lines = db.relationship("IncentiveCalculationLine", backref="batch", cascade="all, delete-orphan") class IncentiveCalculationLine(db.Model): __tablename__ = "incentive_calc_line" id = db.Column(db.Integer, primary_key=True) batch_id = db.Column(db.Integer, db.ForeignKey("incentive_calc_batch.id"), nullable=False) employee_id = db.Column(db.Integer, db.ForeignKey("employee.id"), nullable=False) employee = db.relationship("Employee", foreign_keys=[employee_id]) target_id = db.Column(db.Integer, db.ForeignKey("incentive_target.id"), nullable=True) scheme_id = db.Column(db.Integer, db.ForeignKey("incentive_scheme.id"), nullable=True) rule_id = db.Column(db.Integer, db.ForeignKey("incentive_distribution_rule.id"), nullable=True) achieved_value = db.Column(db.Float, default=0.0) target_value = db.Column(db.Float, default=0.0) calculated_amount = db.Column(db.Float, default=0.0) # Store what actually happened (audit) formula_used = db.Column(db.Text, nullable=True) calc_breakup_json = db.Column(db.JSON, nullable=False, default=dict) status = db.Column(db.Enum("draft", "approved", "paid"), default="draft", nullable=False) created_on = db.Column(db.DateTime, default=datetime.utcnow) class IncentivePayment(db.Model): __tablename__ = "incentive_payment" id = db.Column(db.Integer, primary_key=True) calc_line_id = db.Column(db.Integer, db.ForeignKey("incentive_calc_line.id"), nullable=False) calc_line = db.relationship("IncentiveCalculationLine", foreign_keys=[calc_line_id]) payment_mode = db.Column(db.Enum("cash", "bank"), nullable=False) paid_on = db.Column(db.Date, nullable=True) reference = db.Column(db.String(100), nullable=True) # txn ref / voucher nolas notes = db.Column(db.String(255), nullable=True) created_on = db.Column(db.DateTime, default=datetime.utcnow) # ================= models.py (additions) ================= from extensions import db from datetime import datetime class BonusPolicy(db.Model): __tablename__ = 'bonus_policies' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(255)) bonus_type = db.Column(db.String(50)) # statutory/customary calculation_mode = db.Column(db.String(50)) # slab_wise/fixed/manual prorate_by_worked_days = db.Column(db.Boolean, default=False) min_worked_days = db.Column(db.Integer, default=0) is_active = db.Column(db.Boolean, default=True) created_on = db.Column(db.DateTime, default=datetime.utcnow) class BonusTenureSlab(db.Model): __tablename__ = 'bonus_tenure_slabs' id = db.Column(db.Integer, primary_key=True) policy_id = db.Column(db.Integer, db.ForeignKey('bonus_policies.id')) label = db.Column(db.String(50)) from_years = db.Column(db.Integer) to_years = db.Column(db.Integer, nullable=True) amount = db.Column(db.Float) class BonusRun(db.Model): __tablename__ = 'bonus_runs' id = db.Column(db.Integer, primary_key=True) policy_id = db.Column(db.Integer, db.ForeignKey('bonus_policies.id')) fy_label = db.Column(db.String(10)) fy_start_date = db.Column(db.Date) fy_end_date = db.Column(db.Date) payout_month = db.Column(db.String(7)) # YYYY-MM status = db.Column(db.String(20), default='draft') created_on = db.Column(db.DateTime, default=datetime.utcnow) class BonusEntry(db.Model): __tablename__ = 'bonus_entries' id = db.Column(db.Integer, primary_key=True) bonus_run_id = db.Column(db.Integer, db.ForeignKey('bonus_runs.id')) employee_id = db.Column(db.Integer) employee_name = db.Column(db.String(255)) doj = db.Column(db.Date) worked_days = db.Column(db.Float) tenure_years = db.Column(db.Integer) slab_label = db.Column(db.String(50)) base_amount = db.Column(db.Float) final_amount = db.Column(db.Float) override_amount = db.Column(db.Float, nullable=True) is_manual_override = db.Column(db.Boolean, default=False) remarks = db.Column(db.String(255), nullable=True) is_excluded = db.Column(db.Boolean, default=False) class BonusPayrollPosting(db.Model): __tablename__ = 'bonus_payroll_postings' id = db.Column(db.Integer, primary_key=True) bonus_entry_id = db.Column(db.Integer, db.ForeignKey('bonus_entries.id')) employee_id = db.Column(db.Integer) payroll_month = db.Column(db.String(7)) # YYYY-MM component_name = db.Column(db.String(100)) posted_amount = db.Column(db.Float) posted_on = db.Column(db.DateTime, default=datetime.utcnow) class GratuitySetting(db.Model): __tablename__ = 'gratuity_settings' id = db.Column(db.Integer, primary_key=True) is_enabled = db.Column(db.Boolean, default=False) is_covered_under_act = db.Column(db.Boolean, default=True) min_service_years = db.Column(db.Integer, default=5) round_up_after_months = db.Column(db.Integer, default=6) max_limit = db.Column(db.Float, default=2000000) waive_service_for_death_disability = db.Column(db.Boolean, default=True) created_on = db.Column(db.DateTime, default=datetime.utcnow) updated_on = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) formula = db.Column(db.String(255), default='(last_drawn_salary * 15 * eligible_service_years) / 26') class GratuityComponent(db.Model): __tablename__ = 'gratuity_components' id = db.Column(db.Integer, primary_key=True) setting_id = db.Column(db.Integer, db.ForeignKey('gratuity_settings.id'), nullable=False) head_id = db.Column(db.Integer, db.ForeignKey('earning_head.id'), nullable=False) head_name = db.Column(db.String(100), nullable=False) setting = db.relationship('GratuitySetting', backref='components') earning_head = db.relationship('EarningHead') class GratuityCalculation(db.Model): __tablename__ = 'gratuity_calculations' id = db.Column(db.Integer, primary_key=True) employee_id = db.Column(db.Integer, db.ForeignKey('employee.id'), nullable=False) employee_code = db.Column(db.String(50)) employee_name = db.Column(db.String(150)) date_of_joining = db.Column(db.Date) exit_date = db.Column(db.Date) exit_reason = db.Column(db.String(100)) last_drawn_salary = db.Column(db.Float, default=0) service_years = db.Column(db.Integer, default=0) service_months = db.Column(db.Integer, default=0) eligible_service_years = db.Column(db.Integer, default=0) is_eligible = db.Column(db.Boolean, default=False) eligibility_reason = db.Column(db.String(255)) calculated_amount = db.Column(db.Float, default=0) capped_amount = db.Column(db.Float, default=0) status = db.Column(db.String(20), default='draft') # draft / approved / paid / cancelled remarks = db.Column(db.String(255)) created_by = db.Column(db.Integer, db.ForeignKey('employee.id'), nullable=True) created_on = db.Column(db.DateTime, default=datetime.utcnow) updated_on = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) employee = db.relationship('Employee', foreign_keys=[employee_id], backref='gratuity_calculations') creator = db.relationship('Employee', foreign_keys=[created_by]) class FullFinalSettlement(db.Model): __tablename__ = 'full_final_settlements' id = db.Column(db.Integer, primary_key=True) exit_id = db.Column(db.Integer, db.ForeignKey('exit_requests.id'), nullable=False, unique=True) employee_id = db.Column(db.Integer, db.ForeignKey('employee.id'), nullable=False) salary_amount = db.Column(db.Float, default=0) deductions_amount = db.Column(db.Float, default=0) penalty_recovery = db.Column(db.Float, default=0) loan_advance_recovery = db.Column(db.Float, default=0) asset_recovery = db.Column(db.Float, default=0) handover_recovery = db.Column(db.Float, default=0) bonus_amount = db.Column(db.Float, default=0) gratuity_amount = db.Column(db.Float, default=0) other_benefits = db.Column(db.Float, default=0) net_payable = db.Column(db.Float, default=0) status = db.Column(db.String(20), default='draft') remarks = db.Column(db.String(255)) created_by = db.Column(db.Integer, db.ForeignKey('employee.id')) created_on = db.Column(db.DateTime, default=datetime.utcnow) updated_on = db.Column(db.DateTime, onupdate=datetime.utcnow) exit_request = db.relationship('ExitRequest', backref='fnf') employee = db.relationship('Employee', foreign_keys=[employee_id]) class GratuityDisbursement(db.Model): __tablename__ = 'gratuity_disbursements' id = db.Column(db.Integer, primary_key=True) gratuity_calc_id = db.Column(db.Integer, db.ForeignKey('gratuity_calculations.id'), nullable=False, unique=True) exit_id = db.Column(db.Integer, db.ForeignKey('exit_requests.id')) employee_id = db.Column(db.Integer, db.ForeignKey('employee.id')) amount = db.Column(db.Float, default=0) payment_status = db.Column(db.String(20), default='pending') payment_date = db.Column(db.Date) payment_reference = db.Column(db.String(100)) remarks = db.Column(db.String(255)) created_on = db.Column(db.DateTime, default=datetime.utcnow) updated_on = db.Column(db.DateTime, onupdate=datetime.utcnow) class SalarySlipTemplate(db.Model): __tablename__ = "salary_slip_templates" id = db.Column(db.Integer, primary_key=True) template_name = db.Column(db.String(150), nullable=False) scope_type = db.Column(db.String(30), default="global") # global / location / institute / location_institute location = db.Column(db.String(100), nullable=True) institute = db.Column(db.String(150), nullable=True) is_active = db.Column(db.Boolean, default=True) show_company_section = db.Column(db.Boolean, default=True) show_employee_section = db.Column(db.Boolean, default=True) show_attendance_section = db.Column(db.Boolean, default=True) show_ctc_section = db.Column(db.Boolean, default=False) show_footer = db.Column(db.Boolean, default=True) show_amount_in_words = db.Column(db.Boolean, default=False) company_name = db.Column(db.String(200), nullable=True) company_address = db.Column(db.Text, nullable=True) company_contact = db.Column(db.String(200), nullable=True) footer_text = db.Column(db.Text, nullable=True) logo_path = db.Column(db.String(255), nullable=True) created_on = db.Column(db.DateTime, default=datetime.utcnow) updated_on = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) class SalarySlipFieldConfig(db.Model): __tablename__ = "salary_slip_field_configs" id = db.Column(db.Integer, primary_key=True) template_id = db.Column(db.Integer, db.ForeignKey("salary_slip_templates.id"), nullable=False) section_name = db.Column(db.String(30), nullable=False) # employee / company / attendance field_key = db.Column(db.String(100), nullable=False) display_label = db.Column(db.String(150), nullable=True) is_visible = db.Column(db.Boolean, default=True) display_order = db.Column(db.Integer, default=0) template = db.relationship("SalarySlipTemplate", backref="field_configs") class SalarySlipComponentConfig(db.Model): __tablename__ = "salary_slip_component_configs" id = db.Column(db.Integer, primary_key=True) template_id = db.Column(db.Integer, db.ForeignKey("salary_slip_templates.id"), nullable=False) component_type = db.Column(db.String(20), nullable=False) # earning / deduction / ctc head_name = db.Column(db.String(150), nullable=False) display_label = db.Column(db.String(150), nullable=True) is_visible = db.Column(db.Boolean, default=True) display_order = db.Column(db.Integer, default=0) template = db.relationship("SalarySlipTemplate", backref="component_configs") # ---------------------------- # Demo Setup (Demo Companies & Accounts) # ---------------------------- from datetime import datetime # if not already imported at top class DemoCompany(db.Model): __tablename__ = 'demo_company' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(255), nullable=False) website = db.Column(db.String(255)) contact_person = db.Column(db.String(255)) contact_email = db.Column(db.String(255)) contact_phone = db.Column(db.String(50)) notes = db.Column(db.Text) is_active = db.Column(db.Boolean, default=True) created_at = db.Column(db.DateTime, default=datetime.utcnow) expires_on = db.Column(db.Date) demo_accounts = db.relationship( 'DemoAccount', backref='company', lazy=True, cascade='all, delete-orphan' ) class DemoAccount(db.Model): __tablename__ = 'demo_account' id = db.Column(db.Integer, primary_key=True) company_id = db.Column( db.Integer, db.ForeignKey('demo_company.id'), nullable=False ) # Display label in UI (e.g. "HR Admin", "Manager", "Employee") display_name = db.Column(db.String(255), nullable=False) # Optional mapping to your Role master role_id = db.Column(db.Integer, db.ForeignKey('roles.id'), nullable=True) role = db.relationship('Role') # 🔹 NEW: link to real Employee created for this demo login employee_id = db.Column(db.Integer, db.ForeignKey('employee.id'), nullable=True) employee = db.relationship('Employee') # Where to login login_url = db.Column(db.String(255)) # Credentials to share with prospects (for demo ONLY) login_username = db.Column(db.String(255)) login_password = db.Column(db.String(255)) # plain text only for demo convenience notes = db.Column(db.Text) is_active = db.Column(db.Boolean, default=True) created_at = db.Column(db.DateTime, default=datetime.utcnow) expires_on = db.Column(db.Date) class DemoSignup(db.Model): __tablename__ = 'demo_signup' id = db.Column(db.Integer, primary_key=True) company_name = db.Column(db.String(255), nullable=False) contact_name = db.Column(db.String(255), nullable=False) work_email = db.Column(db.String(255), nullable=False) phone = db.Column(db.String(50)) website = db.Column(db.String(255)) employee_count = db.Column(db.String(50)) message = db.Column(db.Text) status = db.Column(db.String(20), default='pending', nullable=False) # 'pending', 'approved', 'rejected' created_at = db.Column(db.DateTime, default=datetime.utcnow) approved_at = db.Column(db.DateTime) approved_by_id = db.Column(db.Integer, db.ForeignKey('employee.id')) approved_by = db.relationship('Employee', backref='approved_demo_signups') demo_company_id = db.Column(db.Integer, db.ForeignKey('demo_company.id')) demo_company = db.relationship('DemoCompany', backref='signups')