---

from flask_sqlalchemy import SQLAlchemy
from datetime import datetime

db = SQLAlchemy()

class Employee(db.Model):
    __tablename__ = 'employees'
    id = db.Column(db.Integer, primary_key=True)
    tab_no = db.Column(db.String(20), unique=True)
    last_name = db.Column(db.String(50))
    first_name = db.Column(db.String(50))
    middle_name = db.Column(db.String(50))
    department = db.Column(db.String(100))
    position = db.Column(db.String(100))
    position_code = db.Column(db.String(20))

    @property
    def full_name(self):
        return f"{self.last_name} {self.first_name} {self.middle_name or ''}".strip()

class ServiceNote(db.Model):
    __tablename__ = 'service_notes'
    id = db.Column(db.Integer, primary_key=True)
    doc_number = db.Column(db.String(50))
    doc_date = db.Column(db.Date)
    payment_code = db.Column(db.String(10))
    base_reason = db.Column(db.Text)
    created_by = db.Column(db.String(50))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

class SupplementDetail(db.Model):
    __tablename__ = 'supplement_details'
    id = db.Column(db.Integer, primary_key=True)
    service_note_id = db.Column(db.Integer, db.ForeignKey('service_notes.id', ondelete='CASCADE'))
    employee_id = db.Column(db.Integer, db.ForeignKey('employees.id'))
    start_date = db.Column(db.Date)
    end_date = db.Column(db.Date)
    amount = db.Column(db.Numeric(10, 2))
    is_percent = db.Column(db.Boolean, default=False)
    work_content = db.Column(db.Text)
    work_type = db.Column(db.String(100))
    status = db.Column(db.String(20), default='draft')
    
    employee = db.relationship('Employee')
    service_note = db.relationship('ServiceNote', backref='supplement_details')

class CombinationDetail(db.Model):
    __tablename__ = 'combination_details'
    id = db.Column(db.Integer, primary_key=True)
    service_note_id = db.Column(db.Integer, db.ForeignKey('service_notes.id', ondelete='CASCADE'))
    employee_id = db.Column(db.Integer, db.ForeignKey('employees.id'))
    combined_position = db.Column(db.String(250))
    position_identifier = db.Column(db.String(20))
    department = db.Column(db.String(250))
    start_date = db.Column(db.Date)
    end_date = db.Column(db.Date)
    extra_pay = db.Column(db.Numeric(10, 2))
    payment_code = db.Column(db.String(10))
    reason = db.Column(db.Text)
    duties = db.Column(db.Text)
    
    employee = db.relationship('Employee')
    service_note = db.relationship('ServiceNote', backref='combination_details')

class SubstitutionDetail(db.Model):
    __tablename__ = 'substitution_details'
    id = db.Column(db.Integer, primary_key=True)
    service_note_id = db.Column(db.Integer, db.ForeignKey('service_notes.id', ondelete='CASCADE'))
    substitute_employee_id = db.Column(db.Integer, db.ForeignKey('employees.id'))
    absent_employee_id = db.Column(db.Integer, db.ForeignKey('employees.id'))
    start_date = db.Column(db.Date)
    end_date = db.Column(db.Date)
    extra_pay_percent = db.Column(db.Numeric(5, 2))
    payment_code = db.Column(db.String(10))
    duties = db.Column(db.Text)
    absence_reason = db.Column(db.String(200))
    
    substitute = db.relationship('Employee', foreign_keys=[substitute_employee_id])
    absent = db.relationship('Employee', foreign_keys=[absent_employee_id])
    service_note = db.relationship('ServiceNote', backref='substitution_details')

class AllowanceDetail(db.Model):
    __tablename__ = 'allowance_details'
    id = db.Column(db.Integer, primary_key=True)
    service_note_id = db.Column(db.Integer, db.ForeignKey('service_notes.id', ondelete='CASCADE'))
    employee_id = db.Column(db.Integer, db.ForeignKey('employees.id'))
    allowance_name = db.Column(db.String(250))
    payment_code = db.Column(db.String(10))
    unit = db.Column(db.String(10))
    amount = db.Column(db.Numeric(10, 2))
    status = db.Column(db.String(20))
    start_date = db.Column(db.Date)
    end_date = db.Column(db.Date)
    
    employee = db.relationship('Employee')
    service_note = db.relationship('ServiceNote', backref='allowance_details')