# attendance.py

import calendar
from calendar import monthrange
from flask import Blueprint, render_template, request, redirect, url_for, flash, jsonify
from flask_login import login_required, current_user
from datetime import datetime, date, timedelta
from sqlalchemy import func, and_, or_

from extensions import db
from models import (
    Attendance, Holiday, WeekendSettings, Employee, DesktimeLog,
    Payroll, LeaveRequest
)

# =========================
# Leave type normalization
# =========================

def _normalize_leave_type(value) -> str:
    """
    Returns an UPPERCASED canonical name for a leave type.

    Handles:
    - string values: "rh", "Restricted Holiday", "optional", etc.
    - related objects: attempts .code, .name, .type, .category
    - anything else: returns ""
    """
    if value is None:
        return ""
    # Relationship object (e.g., LeaveType)
    if hasattr(value, "__dict__"):
        for attr in ("code", "name", "type", "category", "short_code", "key", "title", "label"):
            v = getattr(value, attr, None)
            if isinstance(v, str) and v.strip():
                return v.strip().upper()
        return ""
    # Plain string
    if isinstance(value, str):
        return value.strip().upper()
    return ""


def _is_rh_leave(value) -> bool:
    """True if this represents Restricted/Optional Holiday leave."""
    lt = _normalize_leave_type(value)
    if not lt:
        return False
    RH_ALIASES = {
        "RH", "R H", "RESTRICTED", "RESTRICTED HOLIDAY",
        "OPTIONAL", "OPTIONAL HOLIDAY", "RESTRICTED/OPTIONAL"
    }
    return lt in RH_ALIASES or ("RESTRICTED" in lt) or ("OPTIONAL" in lt)


# =========================
# Misc helpers
# =========================

def resolve_employee_id_for_user(user):
    # direct field
    eid = getattr(user, "employee_id", None)
    if eid:
        return eid
    # relationship
    emp_rel = getattr(user, "employee", None)
    if emp_rel and getattr(emp_rel, "id", None):
        return emp_rel.id
    # by user_id (only if your schema maps like this)
    emp = Employee.query.filter_by(employee_code=getattr(user, "id", None)).first()
    if emp:
        return emp.id
    # by email (fallback)
    if getattr(user, "email", None):
        emp = Employee.query.filter_by(email=user.email).first()
        if emp:
            return emp.id
    return None


def _norm(s):
    return (s or "").strip().lower()


def _holiday_is_optional(h) -> bool:
    """
    True if this Holiday is a Restricted/Optional holiday.
    Detection via:
      - explicit boolean flag: h.is_optional
      - OR type/category text containing RH/OPTIONAL/RESTRICTED
    """
    # explicit boolean wins
    if hasattr(h, "is_optional"):
        return bool(getattr(h, "is_optional"))

    t = (getattr(h, "type", None) or getattr(h, "category", None) or "").strip().upper()
    # common optional markers (substring match to be robust)
    return any(k in t for k in ("RH", "RESTRICTED", "OPTIONAL"))


def _holiday_is_public(h) -> bool:
    """
    True if this Holiday is a public holiday for everyone.
    Rule: if it's NOT optional, it's public (label-agnostic).
    """
    # explicit boolean wins
    if hasattr(h, "is_optional"):
        return not bool(getattr(h, "is_optional"))

    # If not explicitly optional by text, treat as public.
    return not _holiday_is_optional(h)


def saturday_mode(settings, day):
    """
    Returns: 'off' | 'half-day' | 'working'
    """
    if not settings:
        return 'working'
    week_number = (day.day - 1) // 7 + 1
    key = ["first", "second", "third", "fourth", "fifth"][week_number - 1]
    raw = getattr(settings, f"{key}_saturday", None)
    v = _norm(raw)
    if v in ('off', 'holiday', 'weekend'):
        return 'off'
    if v in ('half-day', 'halfday', 'half'):
        return 'half-day'
    return 'working'


def is_sunday_off(settings):
    return _norm(getattr(settings, "sunday", None)) in ('off', 'holiday', 'weekend')


# =========================
# Blueprint
# =========================

attendance_bp = Blueprint('attendance', __name__, url_prefix='/attendance')


# =========================
# Routes
# =========================

@attendance_bp.route('/mark', methods=['POST'])
@login_required
def mark_attendance():
    date_str = request.form['date']
    att_date = datetime.strptime(date_str, '%Y-%m-%d').date()

    employee_id = resolve_employee_id_for_user(current_user)
    if not employee_id:
        flash("❌ Unable to resolve employee.", "danger")
        return redirect(url_for('attendance.view_calendar'))

    attendance = Attendance.query.filter_by(employee_id=employee_id, date=att_date).first()
    if not attendance:
        attendance = Attendance(employee_id=employee_id, date=att_date)
        db.session.add(attendance)

    attendance.check_in_time = datetime.now().time()
    attendance.updated_on = datetime.now()

    db.session.commit()
    flash(f'Attendance updated for {att_date}!', 'success')
    return redirect(url_for('attendance.view_calendar'))


@attendance_bp.route('/calendar', methods=['GET'])
@login_required
def view_calendar():
    today = datetime.today()
    fy_start = today.year if today.month >= 4 else today.year - 1
    fy_end = fy_start + 1

    # resolve the viewing employee
    self_employee_id = resolve_employee_id_for_user(current_user)
    if not self_employee_id:
        flash("❌ Unable to resolve employee for calendar.", "danger")
        return redirect(url_for('dashboard'))

    events = get_calendar_events(self_employee_id, fy_start, fy_end)
    current_month = today.month

    is_admin = (getattr(current_user, "role", None) and
                getattr(current_user.role, "name", "").lower() == 'admin')

    employees = []
    if is_admin:
        employees = (Employee.query
                     .filter_by(status='active')
                     .order_by(Employee.full_name.asc())
                     .all())

    return render_template(
        'attendance/calendar.html',
        events=events,
        year=today.year,
        month=current_month,
        fy_start=fy_start,
        fy_end=fy_end,
        employees=employees,              # admin dropdown
        self_employee_id=self_employee_id # employee self-processing
    )


# =========================
# Calendar events
# =========================

def get_calendar_events(employee_id, fy_start, fy_end):
    events = []
    fy_start_date = date(fy_start, 4, 1)
    fy_end_date = date(fy_end, 3, 31)

    # ---- Build approved RH dates (for this employee) ----
    approved_rh_dates = set()
    rh_leaves = (
        LeaveRequest.query
        .filter(
            LeaveRequest.employee_id == employee_id,
            LeaveRequest.status == 'final_approved',
            LeaveRequest.start_date <= fy_end_date,
            LeaveRequest.end_date >= fy_start_date,
        )
        .all()
    )
    for lr in rh_leaves:
        if not _is_rh_leave(getattr(lr, "leave_type", None)):
            continue
        d = max(lr.start_date, fy_start_date)
        d_end = min(lr.end_date, fy_end_date)
        while d <= d_end:
            approved_rh_dates.add(d)
            d += timedelta(days=1)

    # ---- Holidays (all rows; classify public vs optional) ----
    holidays = Holiday.query.filter(
        Holiday.date >= fy_start_date,
        Holiday.date <= fy_end_date
    ).all()

    public_holiday_dates = set()

    # Public holidays → ALWAYS show
    for h in holidays:
        if _holiday_is_public(h):
            public_holiday_dates.add(h.date)
            events.append({
                'title': "🎉",
                'start': h.date.isoformat(),
                'color': '#ffc107',
                'extendedProps': {'tooltip': f"Holiday: {getattr(h, 'name', 'Holiday')}"}
            })

    # Optional/RH holidays → ONLY show if employee has approved RH for that date
    for h in holidays:
        if _holiday_is_optional(h) and h.date in approved_rh_dates:
            events.append({
                'title': "🟠",
                'start': h.date.isoformat(),
                'color': '#F39C12',
                'extendedProps': {'tooltip': f"Holiday (RH Approved): {getattr(h, 'name', 'Holiday')}"}
            })

    # ---- Non-RH approved leaves (render as ✈️) ----
    leave_dates_non_rh = set()
    leaves = (LeaveRequest.query
              .filter_by(employee_id=employee_id, status='final_approved')
              .filter(LeaveRequest.start_date <= fy_end_date, LeaveRequest.end_date >= fy_start_date)
              .all())
    for leave in leaves:
        is_rh = _is_rh_leave(getattr(leave, "leave_type", None))
        d0 = max(leave.start_date, fy_start_date)
        d1 = min(leave.end_date, fy_end_date)
        d = d0
        while d <= d1:
            if not is_rh:  # RH is handled as "holiday (approved RH)" above
                leave_dates_non_rh.add(d)
                events.append({
                    'title': "✈️",
                    'start': d.isoformat(),
                    'color': '#6FA8DC',
                    'extendedProps': {'tooltip': "Approved Leave"}
                })
            d += timedelta(days=1)

    # ---- Weekend settings ----
    weekend_settings = WeekendSettings.query.order_by(WeekendSettings.updated_on.desc()).first()
    weekend_dates = set()

    def get_saturday_status(settings, day):
        if not settings:
            return None
        week_number = (day.day - 1) // 7 + 1
        slot = ["first", "second", "third", "fourth", "fifth"][week_number - 1]
        return getattr(settings, f"{slot}_saturday", None)

    d = fy_start_date
    while d <= fy_end_date:
        if d.weekday() == 5:  # Saturday
            sat_status = _norm(get_saturday_status(weekend_settings, d))
            if sat_status == 'off':
                weekend_dates.add(d)
                events.append({
                    'title': '🎓',
                    'start': d.isoformat(),
                    'color': "#AEABAB",
                    'extendedProps': {'tooltip': f"{((d.day - 1)//7)+1} Saturday Off"}
                })
            elif sat_status in ('half-day', 'halfday', 'half'):
                weekend_dates.add(d)
                events.append({
                    'title': '🌓',
                    'start': d.isoformat(),
                    'color': "#F7DC6F",
                    'extendedProps': {'tooltip': f"{((d.day - 1)//7)+1} Saturday Half Day"}
                })
        elif d.weekday() == 6 and weekend_settings and _norm(weekend_settings.sunday) == 'off':
            weekend_dates.add(d)
            events.append({
                'title': '🎓',
                'start': d.isoformat(),
                'color': "#AEABAB",
                'extendedProps': {'tooltip': 'Sunday Off'}
            })
        d += timedelta(days=1)

    # ---- Attendance (only if NOT overridden by Public holiday / Approved RH / non-RH leave / weekend) ----
    attendance_records = (Attendance.query
                          .filter_by(employee_id=employee_id)
                          .filter(Attendance.date >= fy_start_date, Attendance.date <= fy_end_date)
                          .all())

    for a in attendance_records:
        if (a.date in public_holiday_dates
                or a.date in approved_rh_dates
                or a.date in leave_dates_non_rh):
            continue
        # weekends should always reflect current settings, not past remarks
        if a.date in weekend_dates:
            continue

        checkin = a.check_in_time.strftime('%H:%M') if a.check_in_time else '--'
        checkout = a.check_out_time.strftime('%H:%M') if a.check_out_time else '--'

        desktime_log = DesktimeLog.query.filter_by(employee_id=employee_id, date=a.date).first()
        dt_checkin = desktime_log.first_activity.strftime('%H:%M') if desktime_log and desktime_log.first_activity else '--'
        dt_checkout = desktime_log.last_activity.strftime('%H:%M') if desktime_log and desktime_log.last_activity else '--'
        dt_hours = round(desktime_log.productive_hours, 2) if desktime_log and desktime_log.productive_hours else '--'

        tooltip = f"""
        Check-in: {checkin},<br>
        Check-out: {checkout},<br>
        Source: {a.source or '--'},<br>
        Desktime In: {dt_checkin},<br>
        Desktime Out: {dt_checkout},<br>
        Productive Hours: {dt_hours}
        """.strip()

        # --- Defensive fix: if DB says 'Weekend' but current settings say working, re-interpret
        status = a.remarks or ''
        if status == 'Weekend' and a.date not in weekend_dates:
            if isinstance(a.working_hours, (int, float)):
                if a.working_hours >= 9:
                    status = 'Present'
                elif a.working_hours >= 4:
                    status = 'Half Day'
                else:
                    status = 'LWP'
            else:
                status = ''  # unknown

        if status == 'Present':
            icon, color = '✅', "#a8e5b6"
        elif status == 'Half Day':
            icon, color = '🌓', "#e4c394"
        elif status == 'LWP':
            icon, color = '❌', "#d18189"
        elif status == 'Holiday':
            icon, color = '🎉', "#ffc107"
        elif status == 'Leave':
            icon, color = '✈️', "#6FA8DC"
        elif status == 'Weekend':
            icon, color = '🎓', "#AEABAB"
        else:
            icon, color = '❓', '#adb5bd'

        events.append({
            'title': icon,
            'start': a.date.isoformat(),
            'color': color,
            'extendedProps': {'tooltip': tooltip}
        })

    return events


@attendance_bp.route('/process-monthly', methods=['POST'], endpoint='process_monthly_attendance')
@login_required
def process_monthly_attendance():
    selected_month_str = request.form.get('selected_month')
    if not selected_month_str:
        flash("❌ No month selected", "danger")
        return redirect(url_for('attendance.view_calendar'))

    is_admin = (getattr(current_user, "role", None) and
                getattr(current_user.role, "name", "").lower() == 'admin')
    employee_id = request.form.get('employee_id', type=int) if is_admin else None
    if not employee_id:
        employee_id = resolve_employee_id_for_user(current_user)

    if not employee_id:
        flash("❌ No employee selected", "danger")
        return redirect(url_for('attendance.view_calendar'))

    emp = Employee.query.get(employee_id)
    if not emp or emp.status != 'active':
        flash("❌ Employee not found or inactive", "danger")
        return redirect(url_for('attendance.view_calendar'))

    # Range clamp
    year, month = map(int, selected_month_str.split('-'))
    start_date = date(year, month, 1)
    today = date.today()
    month_end = date(year, month, calendar.monthrange(year, month)[1])
    end_date = min(month_end, today) if (year, month) == (today.year, today.month) else month_end

    if emp.date_of_joining and emp.date_of_joining > end_date:
        flash("ℹ️ No days to process (joins after selected range).", "info")
        return redirect(url_for('attendance.view_calendar'))

    emp_start_date = max(start_date, emp.date_of_joining or start_date)

    weekend = WeekendSettings.query.order_by(WeekendSettings.updated_on.desc()).first()

    def get_saturday_status(settings, day):
        if not settings:
            return None
        week_number = (day.day - 1) // 7 + 1
        slot = ["first", "second", "third", "fourth", "fifth"][week_number - 1]
        return getattr(settings, f"{slot}_saturday", None)

    # Approved leaves (ALL types)
    approved_leaves_all = LeaveRequest.query.filter_by(
        employee_id=emp.id, status='final_approved'
    ).filter(
        LeaveRequest.start_date <= end_date,
        LeaveRequest.end_date >= emp_start_date
    ).all()

    # Split RH vs non-RH
    approved_rh_dates = set()
    approved_leave_dates_non_rh = set()
    for lr in approved_leaves_all:
        d0 = max(lr.start_date, emp_start_date)
        d1 = min(lr.end_date, end_date)
        cur = d0
        if _is_rh_leave(getattr(lr, "leave_type", None)):
            while cur <= d1:
                approved_rh_dates.add(cur)
                cur += timedelta(days=1)
        else:
            while cur <= d1:
                approved_leave_dates_non_rh.add(cur)
                cur += timedelta(days=1)

    # Existing attendance rows in range
    attendance_map = {
        att.date: att
        for att in Attendance.query.filter_by(employee_id=emp.id)
        .filter(and_(Attendance.date >= emp_start_date, Attendance.date <= end_date)).all()
    }

    cur_date = emp_start_date
    while cur_date <= end_date:
        # Weekend flags
        is_weekend = False
        if weekend:
            if cur_date.weekday() == 5 and _norm(get_saturday_status(weekend, cur_date)) == 'off':
                is_weekend = True
            if cur_date.weekday() == 6 and _norm(getattr(weekend, "sunday", None)) == 'off':
                is_weekend = True

        # Holiday row (to classify public vs optional)
        hrow = Holiday.query.filter_by(date=cur_date).first()
        is_public_holiday = bool(hrow and _holiday_is_public(hrow))
        is_optional_holiday = bool(hrow and _holiday_is_optional(hrow))

        record = attendance_map.get(cur_date)
        if not record:
            record = Attendance(employee_id=emp.id, date=cur_date)
            db.session.add(record)
            attendance_map[cur_date] = record

        if is_weekend:
            record.working_hours, record.remarks, record.source = 0, 'Weekend', 'System'

        elif is_public_holiday:
            record.working_hours, record.remarks, record.source = 0, 'Holiday', 'System'

        elif is_optional_holiday:
            # Only a holiday if RH approved for this date
            if cur_date in approved_rh_dates:
                record.working_hours, record.remarks, record.source = 0, 'Holiday', 'System'
            else:
                # Unclaimed RH → fall through to leave/attendance evaluation
                if cur_date in approved_leave_dates_non_rh:
                    record.working_hours, record.remarks, record.source = 0, 'Leave', 'System'
                else:
                    # Punch first, then Desktime, else LWP
                    if record.check_in_time and record.check_out_time:
                        dur = (datetime.combine(cur_date, record.check_out_time)
                               - datetime.combine(cur_date, record.check_in_time)).total_seconds() / 3600
                        record.working_hours = round(dur, 2)
                        record.source = 'Punch'
                        record.remarks = 'Present' if dur >= 9 else ('Half Day' if dur >= 4 else 'LWP')
                    elif emp.desktime_id:
                        try:
                            from cli.attendance_cli import fetch_and_store_desktime
                            hours = fetch_and_store_desktime(emp, cur_date) or 0
                            record.working_hours = round(hours, 2)
                            record.source = 'Desktime'
                            record.remarks = 'Present' if hours >= 7 else ('Half Day' if hours >= 4 else 'LWP')
                        except Exception:
                            record.working_hours, record.remarks, record.source = 0, 'LWP', 'System'
                    else:
                        record.working_hours, record.remarks, record.source = 0, 'LWP', 'System'

        elif cur_date in approved_leave_dates_non_rh:
            record.working_hours, record.remarks, record.source = 0, 'Leave', 'System'

        else:
            # Normal logic (non-holiday / non-weekend / not on leave)
            if record.check_in_time and record.check_out_time:
                dur = (datetime.combine(cur_date, record.check_out_time)
                       - datetime.combine(cur_date, record.check_in_time)).total_seconds() / 3600
                record.working_hours = round(dur, 2)
                record.source = 'Punch'
                record.remarks = 'Present' if dur >= 9 else ('Half Day' if dur >= 4 else 'LWP')
            elif emp.desktime_id:
                try:
                    from cli.attendance_cli import fetch_and_store_desktime
                    hours = fetch_and_store_desktime(emp, cur_date) or 0
                    record.working_hours = round(hours, 2)
                    record.source = 'Desktime'
                    record.remarks = 'Present' if hours >= 7 else ('Half Day' if hours >= 4 else 'LWP')
                except Exception:
                    record.working_hours, record.remarks, record.source = 0, 'LWP', 'System'
            else:
                record.working_hours, record.remarks, record.source = 0, 'LWP', 'System'

        cur_date += timedelta(days=1)

    db.session.commit()
    flash(f"✅ Attendance processed for {emp.full_name} from {emp_start_date} to {end_date}.", "success")
    return redirect(url_for('attendance.view_calendar'))


@attendance_bp.route('/weekend-settings', methods=['GET', 'POST'])
@login_required
def weekend_settings():
    settings = WeekendSettings.query.order_by(WeekendSettings.updated_on.desc()).first()

    if not settings:
        settings = WeekendSettings()
        db.session.add(settings)
        db.session.commit()

    if request.method == 'POST':
        settings.first_saturday = request.form.get('first_saturday')
        settings.second_saturday = request.form.get('second_saturday')
        settings.third_saturday = request.form.get('third_saturday')
        settings.fourth_saturday = request.form.get('fourth_saturday')
        settings.fifth_saturday = request.form.get('fifth_saturday')
        settings.sunday = request.form.get('sunday')
        settings.updated_on = datetime.utcnow()
        db.session.commit()
        flash('✅ Weekend settings updated!', 'success')
        return redirect(url_for('attendance.weekend_settings'))

    return render_template('attendance/weekend_settings.html', settings=settings)


@attendance_bp.route('/apply-month-calendar', methods=['POST'])
@login_required
def apply_month_calendar():
    """
    Body JSON: {"month": "YYYY-MM"}
    Applies Holidays, Weekends (off), and Half-day Saturdays to ATTENDANCE
    for all employees whose employment overlaps the month.

    RH/Optional holidays are applied ONLY for employees who have an approved RH
    on that date; otherwise the day is not stamped as 'Holiday'.
    """
    role_name = _norm(getattr(getattr(current_user, 'role', None), 'name', ''))
    if not (getattr(current_user, "is_admin", False) or role_name in ('admin', 'hr admin', 'hr manager')):
        return jsonify({"error": "Forbidden"}), 403

    payload = request.get_json(silent=True) or {}
    month_str = payload.get("month")
    if not month_str:
        return jsonify({"error": "Missing 'month' (YYYY-MM)"}), 400

    try:
        year, month = map(int, month_str.split('-'))
    except Exception:
        return jsonify({"error": "Invalid 'month' format, expected YYYY-MM"}), 400

    start_date = date(year, month, 1)
    end_date   = date(year, month, calendar.monthrange(year, month)[1])

    weekend = WeekendSettings.query.order_by(WeekendSettings.updated_on.desc()).first()

    # Load Holiday rows for classification
    holiday_map = {
        h.date: h for h in Holiday.query
        .filter(Holiday.date >= start_date, Holiday.date <= end_date)
        .all()
    }

    # Employees whose employment overlaps the month
    emp_rows = (
        db.session.query(Employee.id, Employee.date_of_joining, Employee.exit_date)
        .filter(
            func.coalesce(Employee.date_of_joining, start_date) <= end_date,
            or_(Employee.exit_date == None, Employee.exit_date >= start_date)
        )
        .all()
    )
    employees = [{"id": eid, "start": doj or start_date, "end": exd or end_date}
                 for (eid, doj, exd) in emp_rows]
    if not employees:
        return jsonify({"ok": True, "month": month_str, "employees": 0, "rows_created": 0, "rows_updated": 0}), 200

    # Preload existing rows in the month across all those employees
    existing = {
        (att.employee_id, att.date): att
        for att in Attendance.query
            .filter(Attendance.date >= start_date, Attendance.date <= end_date)
            .filter(Attendance.employee_id.in_([e["id"] for e in employees]))
            .all()
    }

    # Build approved RH dates per employee (for the month)
    from collections import defaultdict
    emp_rh_dates = defaultdict(set)
    lr_rows = (LeaveRequest.query
        .filter(LeaveRequest.status=='final_approved',
                LeaveRequest.start_date <= end_date,
                LeaveRequest.end_date >= start_date)
        .all())
    for lr in lr_rows:
        if not _is_rh_leave(getattr(lr, "leave_type", None)):
            continue
        d0 = max(lr.start_date, start_date)
        d1 = min(lr.end_date,   end_date)
        cur = d0
        while cur <= d1:
            emp_rh_dates[lr.employee_id].add(cur)
            cur += timedelta(days=1)

    updated = 0
    created = 0

    d = start_date
    while d <= end_date:
        hrow = holiday_map.get(d)
        is_public = bool(hrow and _holiday_is_public(hrow))
        is_optional = bool(hrow and _holiday_is_optional(hrow))

        # Weekend flags (per date, reused across employees)
        wknd_flag = False
        halfday_flag = False
        if weekend:
            if d.weekday() == 5:  # Saturday
                mode = saturday_mode(weekend, d)  # 'off' | 'half-day' | 'working'
                wknd_flag   = (mode == 'off')
                halfday_flag = (mode == 'half-day')
            elif d.weekday() == 6 and is_sunday_off(weekend):
                wknd_flag = True

        for e in employees:
            eid = e["id"]
            # Skip outside employment window
            if d < e["start"] or d > e["end"]:
                continue

            att = existing.get((eid, d))
            if not att:
                att = Attendance(employee_id=eid, date=d)
                db.session.add(att)
                existing[(eid, d)] = att
                created += 1

            current = _norm(att.remarks)
            if current == 'present':
                continue  # don't downgrade present

            # --- Holidays ---
            if hrow:
                if is_public:
                    if current in {'', None, 'holiday', 'weekend', 'half day', 'lwp', 'absent', 'unknown'}:
                        att.remarks = 'Holiday'
                        att.source = 'System'
                        att.working_hours = 0
                        updated += 1
                    continue
                else:
                    # Optional (RH): only if employee has approved RH on this date
                    if d in emp_rh_dates.get(eid, set()):
                        if current in {'', None, 'holiday', 'weekend', 'half day', 'lwp', 'absent', 'unknown'}:
                            att.remarks = 'Holiday'
                            att.source = 'System'
                            att.working_hours = 0
                            updated += 1
                        continue
                    # else: unclaimed RH → fall through (not stamped as Holiday)

            # --- Weekend / Half-day weekend ---
            if wknd_flag:
                if current in {'', None, 'weekend', 'half day', 'lwp', 'absent', 'unknown'}:
                    att.remarks = 'Weekend'
                    att.source = 'System'
                    att.working_hours = 0
                    updated += 1
                continue

            if halfday_flag:
                if current in {'', None, 'half day', 'lwp', 'absent', 'unknown'}:
                    att.remarks = 'Half Day'
                    att.source = att.source or 'System'
                    updated += 1
                continue

            # --- Cleanup: if it's NOT weekend/holiday now, but row still says Weekend/Half Day, clear it ---
            if not hrow and not wknd_flag and not halfday_flag:
                if current in {'weekend', 'half day'}:
                    att.remarks = ''
                    att.source = att.source or 'System'
                    # don't touch working_hours; monthly processing will set it
                    updated += 1

        d += timedelta(days=1)

    db.session.commit()
    return jsonify({
        "ok": True,
        "month": month_str,
        "employees": len(employees),
        "rows_created": created,
        "rows_updated": updated
    }), 200