"""Ma'lumotlar bazasi bilan ishlovchi barcha (sinxron) funksiyalar.

Bot ham, Web Panel ham faqat shu funksiyalar orqali bazaga murojaat qiladi.
Pul/soat hisob-kitobi logic.py'dagi yagona funksiyalar orqali bajariladi.
"""
import calendar
import json
from datetime import datetime, timedelta

import config
import logic
from database import get_db

# ================= AUDIT JURNALI =================
def audit(actor_id, action: str, target_user_id=None, details=None, conn=None):
    """Admin (yoki tizim) harakatini jurnalga yozadi."""
    row = (config.get_now().strftime("%Y-%m-%d %H:%M:%S"), actor_id, action, target_user_id,
           json.dumps(details or {}, ensure_ascii=False))
    sql = "INSERT INTO audit_log (ts, actor_id, action, target_user_id, details) VALUES (?, ?, ?, ?, ?)"
    if conn is not None:
        conn.execute(sql, row)
    else:
        with get_db() as c:
            c.execute(sql, row)


def list_audit(target_user_id=None, limit: int = 50):
    with get_db() as conn:
        if target_user_id is None:
            rows = conn.execute(
                "SELECT ts, actor_id, action, target_user_id, details FROM audit_log ORDER BY id DESC LIMIT ?",
                (limit,)).fetchall()
        else:
            rows = conn.execute(
                "SELECT ts, actor_id, action, target_user_id, details FROM audit_log "
                "WHERE target_user_id = ? ORDER BY id DESC LIMIT ?", (target_user_id, limit)).fetchall()
    out = []
    for ts, actor, action, target, details in rows:
        try:
            d = json.loads(details or "{}")
        except ValueError:
            d = {}
        out.append({"ts": ts, "actor_id": actor, "action": action, "target_user_id": target, "details": d})
    return out


# ================= FOYDALANUVCHILAR =================
_USER_COLS = ("user_id, full_name, is_approved, monthly_salary, norm_days, work_start_time, "
              "work_end_time, auto_checkout_enabled, overtime_eligible, is_active, daily_hours, work_weekdays")


def _user_row_to_dict(r):
    if not r:
        return None
    return {
        "user_id": r[0], "name": r[1] or "", "is_approved": r[2], "salary": r[3] or 0.0,
        "norm_days": r[4] or 26, "work_start": r[5] or config.WORK_START_TIME,
        "work_end": r[6] or config.WORK_END_TIME, "auto_checkout_enabled": bool(r[7]),
        "overtime_eligible": bool(r[8]), "is_active": r[9] is None or bool(r[9]),
        "daily_hours_override": r[10],
        "daily_hours": logic.daily_norm_hours(r[5], r[6], r[10]),
        "work_weekdays": _valid_mask(r[11]),
        "regime": regime_label(_valid_mask(r[11])),
    }


def get_user(user_id: int):
    with get_db() as conn:
        r = conn.execute(f"SELECT {_USER_COLS} FROM users WHERE user_id = ?", (user_id,)).fetchone()
    return _user_row_to_dict(r)


def user_status(user_id: int) -> str | None:
    """'admin' | 'approved' | 'pending' | 'rejected' | 'archived' | None (umuman yo'q)."""
    if user_id in config.ADMIN_IDS:
        return "admin"
    u = get_user(user_id)
    if not u:
        return None
    if u["is_approved"] == -1:
        return "rejected"
    if not u["is_active"]:
        return "archived"
    return "approved" if u["is_approved"] == 1 else "pending"


def is_approved(user_id: int) -> bool:
    return user_status(user_id) in ("admin", "approved")


def display_name(user_id: int, fallback: str = "") -> str:
    u = get_user(user_id)
    return u["name"] if u and u["name"] else fallback


def insert_pending_user(user_id: int, full_name: str):
    with get_db() as conn:
        conn.execute(
            "INSERT OR IGNORE INTO users (user_id, full_name, is_approved, monthly_salary, norm_days) "
            "VALUES (?, ?, 0, 0, 26)", (user_id, full_name))


def insert_admin_user(user_id: int, full_name: str):
    with get_db() as conn:
        conn.execute(
            "INSERT OR IGNORE INTO users (user_id, full_name, is_approved, monthly_salary, norm_days) "
            "VALUES (?, ?, 1, 0, 26)", (user_id, full_name))


def approve_user(user_id: int, actor_id: int):
    with get_db() as conn:
        conn.execute("UPDATE users SET is_approved = 1, is_active = 1 WHERE user_id = ?", (user_id,))
        audit(actor_id, "approve", user_id, conn=conn)


def reject_user(user_id: int, actor_id: int):
    """Rad etilgan foydalanuvchi o'chirilmaydi - is_approved = -1 bo'lib qoladi,
    shunda u /start orqali qayta-qayta ariza yubora olmaydi."""
    with get_db() as conn:
        conn.execute("UPDATE users SET is_approved = -1 WHERE user_id = ?", (user_id,))
        audit(actor_id, "reject", user_id, conn=conn)


def upsert_user(user_id: int, name: str, actor_id: int):
    """Yangi xodim qo'shadi yoki mavjudini (arxivdagi/kutayotganini ham) faollashtiradi."""
    with get_db() as conn:
        conn.execute(
            "INSERT INTO users (user_id, full_name, is_approved, monthly_salary, norm_days, is_active) "
            "VALUES (?, ?, 1, 0.0, 26, 1) ON CONFLICT(user_id) DO UPDATE SET "
            "full_name = excluded.full_name, is_approved = 1, is_active = 1", (user_id, name))
        audit(actor_id, "add_employee", user_id, {"name": name}, conn=conn)


def rename_user(user_id: int, new_name: str, actor_id: int):
    with get_db() as conn:
        old = conn.execute("SELECT full_name FROM users WHERE user_id = ?", (user_id,)).fetchone()
        conn.execute("UPDATE users SET full_name = ? WHERE user_id = ?", (new_name, user_id))
        audit(actor_id, "rename", user_id, {"old": old[0] if old else None, "new": new_name}, conn=conn)


def set_salary(user_id: int, salary: float, actor_id: int):
    with get_db() as conn:
        old = conn.execute("SELECT monthly_salary FROM users WHERE user_id = ?", (user_id,)).fetchone()
        conn.execute("UPDATE users SET monthly_salary = ? WHERE user_id = ?", (salary, user_id))
        audit(actor_id, "salary", user_id, {"old": old[0] if old else None, "new": salary}, conn=conn)


def set_daily_hours(user_id: int, hours, actor_id: int):
    with get_db() as conn:
        conn.execute("UPDATE users SET daily_hours = ? WHERE user_id = ?", (hours, user_id))
        audit(actor_id, "daily_hours", user_id, {"new": hours}, conn=conn)


def set_flags(user_id: int, actor_id: int, auto_checkout=None, overtime=None):
    with get_db() as conn:
        if auto_checkout is not None:
            conn.execute("UPDATE users SET auto_checkout_enabled = ? WHERE user_id = ?", (int(bool(auto_checkout)), user_id))
            audit(actor_id, "flag_auto_checkout", user_id, {"new": bool(auto_checkout)}, conn=conn)
        if overtime is not None:
            conn.execute("UPDATE users SET overtime_eligible = ? WHERE user_id = ?", (int(bool(overtime)), user_id))
            audit(actor_id, "flag_overtime", user_id, {"new": bool(overtime)}, conn=conn)


def set_work_time(user_id: int, start: str, end: str, actor_id: int):
    """Ish vaqtini o'zgartiradi va bugungi ochiq yozuvning kechikishini qayta hisoblaydi."""
    with get_db() as conn:
        c = conn.cursor()
        old = c.execute("SELECT work_start_time, work_end_time FROM users WHERE user_id = ?", (user_id,)).fetchone()
        c.execute("UPDATE users SET work_start_time = ?, work_end_time = ? WHERE user_id = ?", (start, end, user_id))
        today = config.today_str()
        row = c.execute(
            "SELECT id, check_in_time FROM attendance WHERE user_id = ? AND date = ? AND check_out_time IS NULL",
            (user_id, today)).fetchone()
        if row and row[1]:
            late = logic.compute_lateness_minutes(today, row[1], start, is_work_day(c, user_id, today))
            c.execute("UPDATE attendance SET lateness_minutes = ? WHERE id = ?", (late, row[0]))
        audit(actor_id, "schedule", user_id,
              {"old": list(old) if old else None, "new": [start, end]}, conn=conn)


def archive_user(user_id: int, actor_id: int):
    """Xodimni o'chirish o'rniga arxivlaydi - butun tarix saqlanib qoladi."""
    with get_db() as conn:
        conn.execute("UPDATE users SET is_active = 0 WHERE user_id = ?", (user_id,))
        audit(actor_id, "archive", user_id, conn=conn)


def restore_user(user_id: int, actor_id: int):
    with get_db() as conn:
        conn.execute("UPDATE users SET is_active = 1, is_approved = 1 WHERE user_id = ?", (user_id,))
        audit(actor_id, "restore", user_id, conn=conn)


def delete_user_permanently(user_id: int, actor_id: int) -> dict | None:
    """Xodimni va UNING BUTUN TARIXINI butunlay o'chiradi (davomat, avans, bonus/jarima,
    yo'qliklar, qo'lda kalendar). Qaytarib bo'lmaydi. Jurnalga nima o'chirilgani yoziladi."""
    if user_id in config.ADMIN_IDS:
        raise ValueError("Adminni o'chirib bo'lmaydi")
    with get_db() as conn:
        u = conn.execute("SELECT full_name FROM users WHERE user_id = ?", (user_id,)).fetchone()
        if not u:
            return None
        counts = {}
        for table in ("attendance", "advances", "adjustments", "absences", "work_schedule"):
            counts[table] = conn.execute(f"DELETE FROM {table} WHERE user_id = ?", (user_id,)).rowcount
        conn.execute("DELETE FROM users WHERE user_id = ?", (user_id,))
        audit(actor_id, "delete_forever", user_id, {"name": u[0], "deleted": counts}, conn=conn)
    return {"name": u[0], **counts}


def list_users(include_archived: bool = True, include_admins: bool = False):
    with get_db() as conn:
        rows = conn.execute(f"SELECT {_USER_COLS} FROM users ORDER BY full_name COLLATE NOCASE").fetchall()
    out = []
    for r in rows:
        u = _user_row_to_dict(r)
        if not include_admins and u["user_id"] in config.ADMIN_IDS:
            continue
        if not include_archived and not u["is_active"]:
            continue
        out.append(u)
    return out


def active_employees(include_admins: bool = False):
    """Tasdiqlangan va arxivlanmagan xodimlar."""
    return [u for u in list_users(include_archived=False, include_admins=include_admins) if u["is_approved"] == 1]


# ================= ISH KUNLARI: REJIM + ISHXONA KALENDARI =================
# Ish kuni quyidagi tartibda aniqlanadi:
#   1) Admin shu xodim uchun shu oyga QO'LDA kalendar saqlagan bo'lsa - faqat o'sha kunlar.
#   2) Aks holda AVTOMATIK: ishxona kalendarida dam olish (qizil) bo'lsa - dam;
#      ko'chirilgan ish kuni (yashil) bo'lsa - ish; qolgan kunlar xodimning ish rejimiga
#      (5 kunlik / 6 kunlik / tanlangan hafta kunlari) qarab.
#   3) auto_norm_since oyidan oldingi oylar - eski qoida (yakshanbadan boshqa kunlar),
#      to'langan oylar hisobi o'zgarib ketmasligi uchun.
WEEKDAYS_UZ = ["Du", "Se", "Ch", "Pa", "Ju", "Sh", "Ya"]
REGIMES = {"1111110": "6 kunlik (Du-Sh)", "1111100": "5 kunlik (Du-Ju)", "1111111": "7 kunlik (har kuni)"}


def _valid_mask(mask) -> str:
    m = str(mask or "")
    return m if len(m) == 7 and set(m) <= {"0", "1"} and "1" in m else "1111110"


def regime_label(mask: str) -> str:
    mask = _valid_mask(mask)
    if mask in REGIMES:
        return REGIMES[mask]
    days = [WEEKDAYS_UZ[i] for i, ch in enumerate(mask) if ch == "1"]
    return f"{len(days)} kunlik ({', '.join(days)})"


def _auto_since(cursor) -> str:
    r = cursor.execute("SELECT value FROM settings WHERE key = 'auto_norm_since'").fetchone()
    return r[0] if r else "0000-00"


def _has_manual(cursor, user_id: int, month_prefix: str) -> bool:
    cursor.execute("SELECT COUNT(*) FROM work_schedule WHERE user_id = ? AND date LIKE ?",
                   (user_id, f"{month_prefix}%"))
    return cursor.fetchone()[0] > 0


def _user_mask(cursor, user_id: int) -> str:
    r = cursor.execute("SELECT work_weekdays FROM users WHERE user_id = ?", (user_id,)).fetchone()
    return _valid_mask(r[0] if r else None)


def _holiday_map(cursor, month_prefix: str) -> dict:
    """{'YYYY-MM-DD': is_off} - shu oy uchun ishxona kalendari."""
    return {d: bool(off) for d, off in cursor.execute(
        "SELECT date, is_off FROM holidays WHERE date LIKE ?", (f"{month_prefix}%",)).fetchall()}


def auto_work_days(cursor, user_id: int, month_prefix: str, mask: str | None = None) -> list[int]:
    """Rejim + ishxona kalendari bo'yicha avtomatik ish kunlari (oy kunlari raqamlari)."""
    mask = _valid_mask(mask) if mask else _user_mask(cursor, user_id)
    hol = _holiday_map(cursor, month_prefix)
    y, m = (int(x) for x in month_prefix.split("-"))
    out = []
    for d in range(1, calendar.monthrange(y, m)[1] + 1):
        ds = f"{month_prefix}-{d:02d}"
        if ds in hol:
            if not hol[ds]:
                out.append(d)
        elif mask[datetime(y, m, d).weekday()] == "1":
            out.append(d)
    return out


def is_work_day(cursor, user_id: int, date_str: str) -> bool:
    month = date_str[:7]
    if _has_manual(cursor, user_id, month):
        cursor.execute("SELECT 1 FROM work_schedule WHERE user_id = ? AND date = ?", (user_id, date_str))
        return cursor.fetchone() is not None
    if month < _auto_since(cursor):
        return datetime.strptime(date_str, "%Y-%m-%d").weekday() != 6
    r = cursor.execute("SELECT is_off FROM holidays WHERE date = ?", (date_str,)).fetchone()
    if r:
        return not r[0]
    return _user_mask(cursor, user_id)[datetime.strptime(date_str, "%Y-%m-%d").weekday()] == "1"


def is_work_day_sync(user_id: int, date_str: str) -> bool:
    with get_db() as conn:
        return is_work_day(conn.cursor(), user_id, date_str)


def norm_days_for_month(cursor, user_id: int, month_prefix: str, fallback) -> int:
    """Oylik ish kuni normasi - tizim o'zi hisoblaydi (rejim + kalendar)."""
    fallback = fallback if fallback and fallback > 0 else 26
    if _has_manual(cursor, user_id, month_prefix):
        cursor.execute("SELECT COUNT(*) FROM work_schedule WHERE user_id = ? AND date LIKE ?",
                       (user_id, f"{month_prefix}%"))
        return cursor.fetchone()[0]
    if month_prefix < _auto_since(cursor):
        return fallback
    return len(auto_work_days(cursor, user_id, month_prefix)) or fallback


def get_work_days(user_id: int, month_prefix: str):
    """Qo'lda saqlangan kunlar (bo'lmasa - bo'sh ro'yxat)."""
    with get_db() as conn:
        rows = conn.execute("SELECT date FROM work_schedule WHERE user_id = ? AND date LIKE ? ORDER BY date",
                            (user_id, f"{month_prefix}%")).fetchall()
    return [r[0] for r in rows]


def workdays_view(user_id: int, month_prefix: str) -> dict:
    """Xodim kalendari uchun: amaldagi ish kunlari, avtomatik kunlar, bayramlar."""
    y, m = (int(x) for x in month_prefix.split("-"))
    with get_db() as conn:
        c = conn.cursor()
        manual = [int(d[8:]) for d in get_work_days(user_id, month_prefix)]
        auto = auto_work_days(c, user_id, month_prefix)
        hol = {int(d[8:]): off for d, off in _holiday_map(c, month_prefix).items()}
        mask = _user_mask(c, user_id)
        legacy = month_prefix < _auto_since(c)
    if legacy and not manual:
        auto = [d for d in range(1, calendar.monthrange(y, m)[1] + 1) if datetime(y, m, d).weekday() != 6]
    return {"month": month_prefix, "days_in_month": calendar.monthrange(y, m)[1],
            "first_weekday": datetime(y, m, 1).weekday(), "manual": bool(manual),
            "marked": manual or auto, "auto": auto, "count": len(manual or auto),
            "holidays": {str(d): off for d, off in hol.items()}, "regime": regime_label(mask), "legacy": legacy}


def set_work_days(user_id: int, month_prefix: str, days, actor_id: int):
    """Shu xodim uchun shu oyni QO'LDA belgilaydi (avtomatik hisobni almashtiradi).
    Bo'sh ro'yxat - qo'lda belgilashni olib tashlab, avtomatikka qaytaradi."""
    year, mon = (int(x) for x in month_prefix.split("-"))
    dim = calendar.monthrange(year, mon)[1]
    clean = sorted({int(d) for d in days})
    if any(d < 1 or d > dim for d in clean):
        raise ValueError(f"Kun 1..{dim} oralig'ida bo'lishi kerak")
    with get_db() as conn:
        conn.execute("DELETE FROM work_schedule WHERE user_id = ? AND date LIKE ?", (user_id, f"{month_prefix}%"))
        conn.executemany("INSERT OR IGNORE INTO work_schedule (user_id, date) VALUES (?, ?)",
                         [(user_id, f"{month_prefix}-{d:02d}") for d in clean])
        audit(actor_id, "workdays" if clean else "workdays_auto", user_id,
              {"month": month_prefix, "count": len(clean)}, conn=conn)
    return len(clean)


def set_work_regime(user_id: int, mask: str, actor_id: int) -> str:
    m = str(mask or "")
    if len(m) != 7 or set(m) - {"0", "1"} or "1" not in m:
        raise ValueError("Kamida bitta ish kuni tanlanishi kerak")
    with get_db() as conn:
        old = conn.execute("SELECT work_weekdays FROM users WHERE user_id = ?", (user_id,)).fetchone()
        conn.execute("UPDATE users SET work_weekdays = ? WHERE user_id = ?", (m, user_id))
        audit(actor_id, "regime", user_id, {"old": regime_label(old[0] if old else None), "new": regime_label(m)},
              conn=conn)
    return regime_label(m)


# ---- Ishxona kalendari (bayram va dam olish kunlari) ----
# O'zbekistonda har yili sanasi o'zgarmaydigan bayramlar. Ramazon va Qurbon hayit hamda
# ko'chirilgan dam olish kunlari har yili hukumat qarori bilan belgilanadi - ularni admin qo'shadi.
FIXED_HOLIDAYS = [("01-01", "Yangi yil"), ("03-08", "Xotin-qizlar kuni"), ("03-21", "Navro'z bayrami"),
                  ("05-09", "Xotira va qadrlash kuni"), ("09-01", "Mustaqillik kuni"),
                  ("10-01", "O'qituvchi va murabbiylar kuni"), ("12-08", "Konstitutsiya kuni")]


def list_holidays(month_prefix: str):
    with get_db() as conn:
        rows = conn.execute("SELECT date, name, is_off FROM holidays WHERE date LIKE ? ORDER BY date",
                            (f"{month_prefix}%",)).fetchall()
    return [{"date": d, "name": n or "", "is_off": bool(o)} for d, n, o in rows]


def set_holiday(date_str: str, is_off: bool, name: str, actor_id: int):
    datetime.strptime(date_str, "%Y-%m-%d")
    with get_db() as conn:
        conn.execute("INSERT INTO holidays (date, name, is_off) VALUES (?, ?, ?) ON CONFLICT(date) DO UPDATE SET "
                     "name = excluded.name, is_off = excluded.is_off", (date_str, (name or "")[:80], int(bool(is_off))))
        audit(actor_id, "holiday", None, {"date": date_str, "is_off": bool(is_off), "name": name}, conn=conn)


def clear_holiday(date_str: str, actor_id: int) -> bool:
    with get_db() as conn:
        cur = conn.execute("DELETE FROM holidays WHERE date = ?", (date_str,))
        if cur.rowcount:
            audit(actor_id, "holiday_clear", None, {"date": date_str}, conn=conn)
        return cur.rowcount > 0


def add_fixed_holidays(year: int, actor_id: int) -> int:
    n = 0
    with get_db() as conn:
        for md, name in FIXED_HOLIDAYS:
            cur = conn.execute("INSERT OR IGNORE INTO holidays (date, name, is_off) VALUES (?, ?, 1)",
                               (f"{year}-{md}", name))
            n += cur.rowcount
        audit(actor_id, "holidays_fixed", None, {"year": year, "added": n}, conn=conn)
    return n


def company_calendar(month_prefix: str) -> dict:
    """Ishxona kalendari sahifasi uchun: kunlar, bayramlar va rejimlar bo'yicha norma."""
    y, m = (int(x) for x in month_prefix.split("-"))
    hol = list_holidays(month_prefix)
    with get_db() as conn:
        c = conn.cursor()
        norms = {mask: len(auto_work_days(c, 0, month_prefix, mask)) for mask in REGIMES}
        legacy = month_prefix < _auto_since(c)
    return {"month": month_prefix, "days_in_month": calendar.monthrange(y, m)[1],
            "first_weekday": datetime(y, m, 1).weekday(), "holidays": hol, "legacy": legacy,
            "norms": [{"regime": REGIMES[k], "mask": k, "days": v} for k, v in norms.items()]}


# ================= YO'QLIKLAR (ta'til / kasallik / otgul) =================
def get_absence(cursor, user_id: int, date_str: str):
    cursor.execute("SELECT type, note FROM absences WHERE user_id = ? AND date = ?", (user_id, date_str))
    r = cursor.fetchone()
    return {"type": r[0], "note": r[1] or ""} if r else None


def set_absence(user_id: int, date_from: str, date_to: str, abs_type: str, note: str, actor_id: int) -> int:
    """date_from..date_to oralig'idagi kunlarni yo'qlik deb belgilaydi (to'lanmaydi)."""
    if abs_type not in config.ABSENCE_TYPES:
        raise ValueError("Noto'g'ri yo'qlik turi")
    d1 = datetime.strptime(date_from, "%Y-%m-%d")
    d2 = datetime.strptime(date_to or date_from, "%Y-%m-%d")
    if d2 < d1:
        raise ValueError("Tugash sanasi boshlanishdan oldin")
    if (d2 - d1).days > 62:
        raise ValueError("Bir martada ko'pi bilan 2 oy belgilash mumkin")
    now = config.get_now().strftime("%Y-%m-%d %H:%M:%S")
    n = 0
    with get_db() as conn:
        d = d1
        while d <= d2:
            conn.execute(
                "INSERT INTO absences (user_id, date, type, note, created_by, created_at) VALUES (?, ?, ?, ?, ?, ?) "
                "ON CONFLICT(user_id, date) DO UPDATE SET type = excluded.type, note = excluded.note, "
                "created_by = excluded.created_by, created_at = excluded.created_at",
                (user_id, d.strftime("%Y-%m-%d"), abs_type, note or "", actor_id, now))
            n += 1
            d += timedelta(days=1)
        audit(actor_id, "absence", user_id, {"from": date_from, "to": date_to, "type": abs_type, "note": note}, conn=conn)
    return n


def clear_absence(user_id: int, date_str: str, actor_id: int) -> bool:
    with get_db() as conn:
        cur = conn.execute("DELETE FROM absences WHERE user_id = ? AND date = ?", (user_id, date_str))
        if cur.rowcount:
            audit(actor_id, "absence_clear", user_id, {"date": date_str}, conn=conn)
        return cur.rowcount > 0


def list_absences(user_id: int, month_prefix: str):
    with get_db() as conn:
        rows = conn.execute("SELECT date, type, note FROM absences WHERE user_id = ? AND date LIKE ? ORDER BY date",
                            (user_id, f"{month_prefix}%")).fetchall()
    return [{"date": d, "type": t, "note": n or ""} for d, t, n in rows]


# ================= DAVOMAT =================
_ATT_COLS = ("id, check_in_time, check_out_time, break_start, break_end, break_minutes, "
             "checkin_source, checkout_source, lateness_minutes, work_hours, paid_hours, break_count, location_flag, "
             "lateness_reason")


def _att_to_dict(r):
    if not r:
        return None
    return {"id": r[0], "check_in": r[1], "check_out": r[2], "break_start": r[3], "break_end": r[4],
            "break_minutes": r[5] or 0, "checkin_source": r[6] or "self", "checkout_source": r[7] or "self",
            "lateness": r[8] or 0, "work_hours": r[9] or 0, "paid_hours": r[10] or 0,
            "break_count": r[11] or 0, "location_flag": r[12], "reason": (r[13] or "").strip()}


def get_attendance(user_id: int, date_str: str):
    with get_db() as conn:
        r = conn.execute(f"SELECT {_ATT_COLS} FROM attendance WHERE user_id = ? AND date = ?",
                         (user_id, date_str)).fetchone()
    return _att_to_dict(r)


def prepare_checkin(user_id: int, date_str: str, time_str: str):
    """(lateness, is_work_day) - kechikishni xodimning shaxsiy ish vaqti va kalendari bo'yicha hisoblaydi."""
    u = get_user(user_id)
    start = u["work_start"] if u else config.WORK_START_TIME
    work_day = is_work_day_sync(user_id, date_str)
    return logic.compute_lateness_minutes(date_str, time_str, start, work_day), work_day


def checkin(user_id: int, date_str: str, time_str: str, lateness: int,
            lat=None, lon=None, accuracy=None, flag=None):
    """Kelishni yozadi. Shu kunga yozuv allaqachon bo'lsa None qaytaradi."""
    import sqlite3
    with get_db() as conn:
        try:
            cur = conn.execute(
                "INSERT INTO attendance (user_id, date, check_in_time, lateness_minutes, checkin_lat, "
                "checkin_lon, checkin_accuracy, location_flag) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
                (user_id, date_str, time_str, lateness, lat, lon, accuracy, flag))
        except sqlite3.IntegrityError:
            return None
        return cur.lastrowid


def save_lateness_reason(attendance_id: int, reason: str):
    with get_db() as conn:
        conn.execute("UPDATE attendance SET lateness_reason = ? WHERE id = ?", (reason, attendance_id))


def break_start(user_id: int, date_str: str, time_str: str):
    """Tanaffus boshlash. Kun davomida bir necha marta tanaffus qilish mumkin -
    oldingi tanaffusdan qaytilgan bo'lsa, yangisi boshlanadi."""
    with get_db() as conn:
        r = conn.execute(
            "SELECT id, break_start, break_end, check_out_time FROM attendance WHERE user_id = ? AND date = ?",
            (user_id, date_str)).fetchone()
        if not r:
            return "no_checkin", None
        if r[3] is not None:
            return "already_checked_out", None
        if r[1] is not None and r[2] is None:
            return "already_on_break", r[1]
        conn.execute("UPDATE attendance SET break_start = ?, break_end = NULL WHERE id = ?", (time_str, r[0]))
        return "ok", None


def break_end(user_id: int, date_str: str, now_dt: datetime):
    time_str = now_dt.strftime("%H:%M:%S")
    with get_db() as conn:
        r = conn.execute(
            "SELECT id, break_start, break_end, break_minutes, break_count FROM attendance WHERE user_id = ? AND date = ?",
            (user_id, date_str)).fetchone()
        if not r or not r[1]:
            return "not_on_break", None
        if r[2] is not None:
            return "already_returned", None
        start_dt = logic.to_dt(date_str, r[1]).replace(tzinfo=now_dt.tzinfo)
        mins = max(0, int((now_dt - start_dt).total_seconds() // 60))
        conn.execute("UPDATE attendance SET break_end = ?, break_minutes = ?, break_count = ? WHERE id = ?",
                     (time_str, (r[3] or 0) + mins, (r[4] or 0) + 1, r[0]))
        return "ok", mins


def checkout(user_id: int, date_str: str, now_dt: datetime, force_time: str | None = None):
    """Ketishni yozadi. Ochiq tanaffusni yopadi. Avto-ketish yoqilgan xodim ish
    tugash vaqtidan kech bossa, ketish vaqti ish tugash vaqtiga tenglanadi.

    force_time - tungi avto-yopish uchun aniq vaqt ("HH:MM:SS").
    Qaytadi: (status, dict)"""
    with get_db() as conn:
        c = conn.cursor()
        r = c.execute(
            "SELECT id, check_in_time, break_minutes, break_start, break_end, check_out_time, break_count "
            "FROM attendance WHERE user_id = ? AND date = ?", (user_id, date_str)).fetchone()
        if not r:
            return "no_checkin", None
        rec_id, check_in, brk_min, b_start, b_end, existing_out, b_count = r
        if existing_out:
            return "already_checked_out", {"check_out": existing_out}
        brk_min = brk_min or 0
        u = c.execute("SELECT work_end_time, auto_checkout_enabled, overtime_eligible FROM users WHERE user_id = ?",
                      (user_id,)).fetchone()
        work_end = (u[0] if u and u[0] else config.WORK_END_TIME)
        auto = bool(u[1]) if u else False
        overtime = bool(u[2]) if u else False

        tz = now_dt.tzinfo
        if force_time:
            eff = logic.to_dt(date_str, force_time).replace(tzinfo=tz)
            clamped, source = False, "auto_night"
        else:
            end_dt = logic.to_dt(date_str, work_end).replace(tzinfo=tz)
            clamped = auto and now_dt > end_dt
            eff = end_dt if clamped else now_dt
            source = "auto" if clamped else "self"
        ci_dt = logic.to_dt(date_str, check_in).replace(tzinfo=tz)
        if eff < ci_dt:
            eff = ci_dt
        eff_str = eff.strftime("%H:%M:%S")

        added = 0
        autoclosed_break = False
        if b_start and not b_end:
            bs = logic.to_dt(date_str, b_start).replace(tzinfo=tz)
            be = max(bs, min(now_dt, eff))
            added = int((be - bs).total_seconds() // 60)
            brk_min += added
            autoclosed_break = True
            c.execute("UPDATE attendance SET break_end = ?, break_minutes = ?, break_count = ? WHERE id = ?",
                      (be.strftime("%H:%M:%S"), brk_min, (b_count or 0) + 1, rec_id))

        work, paid = logic.compute_work_and_paid_hours(date_str, check_in, eff_str, brk_min, overtime)
        c.execute("UPDATE attendance SET check_out_time = ?, work_hours = ?, paid_hours = ?, checkout_source = ? "
                  "WHERE id = ?", (eff_str, work, paid, source, rec_id))
    return "ok", {"work_hours": work, "paid_hours": paid, "break_autoclosed": autoclosed_break,
                  "break_added": added, "clamped": clamped, "check_out": eff_str, "check_in": check_in}


def admin_get_attendance(user_id: int, date_str: str):
    rec = get_attendance(user_id, date_str)
    with get_db() as conn:
        absence = get_absence(conn.cursor(), user_id, date_str)
    if rec:
        rec = dict(rec, check_in=(rec["check_in"] or "")[:5], check_out=(rec["check_out"] or "")[:5])
    return rec, absence


def admin_set_checkin(user_id: int, date_str: str, hhmm: str, actor_id: int):
    """Admin kelish vaqtini kiritadi. (ok, xato_matni)"""
    err = logic.validate_admin_date(date_str, config.today_str())
    if err:
        return False, err
    ci = f"{hhmm}:00"
    with get_db() as conn:
        c = conn.cursor()
        u = c.execute("SELECT work_start_time, overtime_eligible FROM users WHERE user_id = ?", (user_id,)).fetchone()
        if not u:
            return False, "Xodim topilmadi"
        start = u[0] or config.WORK_START_TIME
        late = logic.compute_lateness_minutes(date_str, ci, start, is_work_day(c, user_id, date_str))
        ex = c.execute("SELECT id, check_in_time, check_out_time, break_minutes FROM attendance "
                       "WHERE user_id = ? AND date = ?", (user_id, date_str)).fetchone()
        if ex:
            rec_id, old_in, co, brk = ex
            if co:
                err = logic.validate_shift(hhmm, co)
                if err:
                    return False, err
                work, paid = logic.compute_work_and_paid_hours(date_str, ci, co, brk or 0, bool(u[1]))
                c.execute("UPDATE attendance SET check_in_time = ?, lateness_minutes = ?, work_hours = ?, "
                          "paid_hours = ?, checkin_source = 'admin' WHERE id = ?", (ci, late, work, paid, rec_id))
            else:
                c.execute("UPDATE attendance SET check_in_time = ?, lateness_minutes = ?, checkin_source = 'admin' "
                          "WHERE id = ?", (ci, late, rec_id))
        else:
            old_in = None
            c.execute("INSERT INTO attendance (user_id, date, check_in_time, lateness_minutes, checkin_source) "
                      "VALUES (?, ?, ?, ?, 'admin')", (user_id, date_str, ci, late))
        audit(actor_id, "set_checkin", user_id, {"date": date_str, "old": old_in, "new": ci}, conn=conn)
    return True, None


def admin_set_checkout(user_id: int, date_str: str, hhmm: str, actor_id: int):
    err = logic.validate_admin_date(date_str, config.today_str())
    if err:
        return False, err
    co = f"{hhmm}:00"
    with get_db() as conn:
        c = conn.cursor()
        ex = c.execute("SELECT id, check_in_time, break_minutes, check_out_time FROM attendance "
                       "WHERE user_id = ? AND date = ?", (user_id, date_str)).fetchone()
        if not ex or not ex[1]:
            return False, "Avval shu kun uchun kelish vaqtini kiriting"
        rec_id, ci, brk, old_out = ex
        err = logic.validate_shift(ci, hhmm)
        if err:
            return False, err
        if (brk or 0) >= logic.minutes_between(ci[:5], hhmm):
            return False, "Tanaffus vaqti ish vaqtidan uzun bo'lib qoladi"
        ot = c.execute("SELECT overtime_eligible FROM users WHERE user_id = ?", (user_id,)).fetchone()
        work, paid = logic.compute_work_and_paid_hours(date_str, ci, co, brk or 0, bool(ot and ot[0]))
        c.execute("UPDATE attendance SET check_out_time = ?, work_hours = ?, paid_hours = ?, checkout_source = 'admin' "
                  "WHERE id = ?", (co, work, paid, rec_id))
        audit(actor_id, "set_checkout", user_id, {"date": date_str, "old": old_out, "new": co}, conn=conn)
    return True, None


def admin_delete_attendance(user_id: int, date_str: str, actor_id: int) -> bool:
    with get_db() as conn:
        ex = conn.execute("SELECT check_in_time, check_out_time FROM attendance WHERE user_id = ? AND date = ?",
                          (user_id, date_str)).fetchone()
        if not ex:
            return False
        conn.execute("DELETE FROM attendance WHERE user_id = ? AND date = ?", (user_id, date_str))
        audit(actor_id, "delete_attendance", user_id, {"date": date_str, "old": list(ex)}, conn=conn)
    return True


def find_open_records(before_or_on: str, since: str = "0000-00-00"):
    """Ketish belgilanmagan (ochiq) yozuvlar - [since, before_or_on] oralig'ida."""
    with get_db() as conn:
        rows = conn.execute(
            "SELECT a.user_id, a.date, a.check_in_time, u.work_end_time, u.full_name FROM attendance a "
            "JOIN users u ON u.user_id = a.user_id "
            "WHERE a.check_out_time IS NULL AND a.check_in_time IS NOT NULL AND a.date BETWEEN ? AND ? "
            "ORDER BY a.date", (since, before_or_on)).fetchall()
    return rows


def close_open_records(before_or_on: str, since: str | None = None):
    """Ochiq qolgan kunlarni yopadi: kelish ish tugash vaqtidan oldin bo'lsa -
    ish tugash vaqtida, aks holda kelish vaqtida (0 soat). Admin keyin Web
    Panel'da to'g'rilashi mumkin. Yopilganlar ro'yxatini qaytaradi.
    since berilmasa - faqat before_or_on kunining o'zi (eski oylarga tegilmaydi)."""
    closed = []
    for user_id, date_str, ci, work_end, name in find_open_records(before_or_on, since or before_or_on):
        end = (work_end or config.WORK_END_TIME) + ":00"
        close_at = end if logic.to_dt(date_str, ci) < logic.to_dt(date_str, end) else ci
        status, info = checkout(user_id, date_str, config.get_now(), force_time=close_at)
        if status == "ok":
            audit(None, "auto_close", user_id, {"date": date_str, "time": close_at})
            closed.append({"user_id": user_id, "name": name, "date": date_str,
                           "check_in": ci, "check_out": info["check_out"], "work_hours": info["work_hours"]})
    return closed


# ================= ESLATMALAR / AVTO-KETISH =================
def _reminder_targets(date_str: str, now_hm: str, field: str, need_open: bool):
    out = []
    with get_db() as conn:
        c = conn.cursor()
        for u in active_employees(include_admins=True):
            if not is_work_day(c, u["user_id"], date_str) or get_absence(c, u["user_id"], date_str):
                continue
            base = datetime.strptime(u[field], "%H:%M")
            for off in (15, 10, 5):
                if (base - timedelta(minutes=off)).strftime("%H:%M") == now_hm:
                    if need_open:
                        hit = c.execute("SELECT 1 FROM attendance WHERE user_id = ? AND date = ? AND "
                                        "check_out_time IS NULL", (u["user_id"], date_str)).fetchone()
                    else:
                        hit = not c.execute("SELECT 1 FROM attendance WHERE user_id = ? AND date = ?",
                                            (u["user_id"], date_str)).fetchone()
                    if hit:
                        out.append((u["user_id"], u["name"], off))
                    break
    return out


def checkin_reminder_targets(date_str: str, now_hm: str):
    return _reminder_targets(date_str, now_hm, "work_start", need_open=False)


def checkout_reminder_targets(date_str: str, now_hm: str):
    return _reminder_targets(date_str, now_hm, "work_end", need_open=True)


def auto_close_targets(date_str: str, now_hm: str):
    out = []
    with get_db() as conn:
        c = conn.cursor()
        for u in active_employees(include_admins=True):
            if not u["auto_checkout_enabled"] or u["work_end"] != now_hm:
                continue
            if c.execute("SELECT 1 FROM attendance WHERE user_id = ? AND date = ? AND check_out_time IS NULL",
                         (u["user_id"], date_str)).fetchone():
                out.append((u["user_id"], u["name"]))
    return out


# ================= HOLAT (BUGUN) =================
def employee_status(date_str: str):
    """{'ishda': [...], 'ketgan': [...], 'kelmagan': [...], 'sababli': [...]} - adminlarsiz."""
    res = {"ishda": [], "ketgan": [], "kelmagan": [], "sababli": []}
    with get_db() as conn:
        c = conn.cursor()
        for u in active_employees():
            uid = u["user_id"]
            r = c.execute("SELECT check_in_time, check_out_time, lateness_minutes, lateness_reason, "
                          "break_start, break_end, location_flag, work_hours FROM attendance "
                          "WHERE user_id = ? AND date = ?", (uid, date_str)).fetchone()
            base = {"user_id": uid, "name": u["name"]}
            if not r:
                ab = get_absence(c, uid, date_str)
                if ab:
                    res["sababli"].append(dict(base, absence=ab["type"], note=ab["note"]))
                elif is_work_day(c, uid, date_str):
                    res["kelmagan"].append(base)
                continue
            item = dict(base, check_in=r[0], check_out=r[1], lateness=r[2] or 0, reason=r[3] or "",
                        on_break=bool(r[4] and not r[5]), location_flag=r[6], work_hours=r[7] or 0)
            res["ketgan" if r[1] else "ishda"].append(item)
    return res


def daily_summary(date_str: str) -> dict:
    st = employee_status(date_str)
    kelgan = st["ishda"] + st["ketgan"]
    late = sorted([x for x in kelgan if x["lateness"] > 0], key=lambda x: -x["lateness"])
    return {"status": st, "kelgan": kelgan, "late": late,
            "total": len(kelgan) + len(st["kelmagan"]) + len(st["sababli"])}


def weekly_summary(start: str, end: str):
    out = []
    with get_db() as conn:
        for u in active_employees():
            r = conn.execute(
                "SELECT COUNT(id), COALESCE(SUM(lateness_minutes),0), COALESCE(SUM(work_hours),0), "
                "SUM(CASE WHEN lateness_minutes > 0 THEN 1 ELSE 0 END) FROM attendance "
                "WHERE user_id = ? AND date BETWEEN ? AND ?", (u["user_id"], start, end)).fetchone()
            out.append({"name": u["name"], "days": r[0], "lateness": r[1], "hours": r[2], "late_days": r[3] or 0})
    return out


# ================= AVANS, JARIMA/BONUS =================
def add_advance(user_id: int, amount: float, date_str: str, actor_id: int, note: str = "") -> int:
    with get_db() as conn:
        cur = conn.execute("INSERT INTO advances (user_id, amount, date, note, created_by) VALUES (?, ?, ?, ?, ?)",
                           (user_id, amount, date_str, note, actor_id))
        audit(actor_id, "advance_add", user_id, {"id": cur.lastrowid, "amount": amount, "date": date_str}, conn=conn)
        return cur.lastrowid


def list_advances(user_id: int, month_prefix: str):
    with get_db() as conn:
        rows = conn.execute("SELECT id, date, amount, note FROM advances WHERE user_id = ? AND date LIKE ? "
                            "ORDER BY date, id", (user_id, f"{month_prefix}%")).fetchall()
    return [{"id": i, "date": d, "amount": a, "note": n or ""} for i, d, a, n in rows]


def update_advance(adv_id: int, amount: float, actor_id: int):
    with get_db() as conn:
        r = conn.execute("SELECT user_id, amount FROM advances WHERE id = ?", (adv_id,)).fetchone()
        if not r:
            return None
        conn.execute("UPDATE advances SET amount = ? WHERE id = ?", (amount, adv_id))
        audit(actor_id, "advance_edit", r[0], {"id": adv_id, "old": r[1], "new": amount}, conn=conn)
        return r[0]


def delete_advance(adv_id: int, actor_id: int):
    with get_db() as conn:
        r = conn.execute("SELECT user_id, amount, date FROM advances WHERE id = ?", (adv_id,)).fetchone()
        if not r:
            return None
        conn.execute("DELETE FROM advances WHERE id = ?", (adv_id,))
        audit(actor_id, "advance_delete", r[0], {"id": adv_id, "amount": r[1], "date": r[2]}, conn=conn)
        return r[0]


def add_adjustment(user_id: int, amount: float, note: str, date_str: str, actor_id: int) -> int:
    """amount > 0 - bonus, amount < 0 - jarima."""
    with get_db() as conn:
        cur = conn.execute("INSERT INTO adjustments (user_id, date, amount, note, created_by, created_at) "
                           "VALUES (?, ?, ?, ?, ?, ?)",
                           (user_id, date_str, amount, note, actor_id, config.get_now().strftime("%Y-%m-%d %H:%M:%S")))
        audit(actor_id, "bonus" if amount > 0 else "fine", user_id,
              {"id": cur.lastrowid, "amount": amount, "note": note}, conn=conn)
        return cur.lastrowid


def list_adjustments(user_id: int, month_prefix: str):
    with get_db() as conn:
        rows = conn.execute("SELECT id, date, amount, note FROM adjustments WHERE user_id = ? AND date LIKE ? "
                            "ORDER BY date, id", (user_id, f"{month_prefix}%")).fetchall()
    return [{"id": i, "date": d, "amount": a, "note": n or ""} for i, d, a, n in rows]


def delete_adjustment(adj_id: int, actor_id: int):
    with get_db() as conn:
        r = conn.execute("SELECT user_id, amount, note FROM adjustments WHERE id = ?", (adj_id,)).fetchone()
        if not r:
            return None
        conn.execute("DELETE FROM adjustments WHERE id = ?", (adj_id,))
        audit(actor_id, "adjustment_delete", r[0], {"id": adj_id, "amount": r[1], "note": r[2]}, conn=conn)
        return r[0]


# ================= OYLIK HISOB =================
def salary_summary(user_id: int, month_prefix: str):
    """Bitta xodimning oy bo'yicha to'liq hisob-kitobi."""
    u = get_user(user_id)
    if not u:
        return None
    with get_db() as conn:
        c = conn.cursor()
        norm = norm_days_for_month(c, user_id, month_prefix, u["norm_days"])
        a = c.execute(
            "SELECT COUNT(id), COALESCE(SUM(work_hours),0), COALESCE(SUM(paid_hours),0), "
            "COALESCE(SUM(lateness_minutes),0), SUM(CASE WHEN lateness_minutes > 0 THEN 1 ELSE 0 END), "
            "COALESCE(SUM(break_minutes),0) FROM attendance WHERE user_id = ? AND date LIKE ?",
            (user_id, f"{month_prefix}%")).fetchone()
        adv = c.execute("SELECT COALESCE(SUM(amount),0) FROM advances WHERE user_id = ? AND date LIKE ?",
                        (user_id, f"{month_prefix}%")).fetchone()[0]
        bon = c.execute("SELECT COALESCE(SUM(amount),0) FROM adjustments WHERE user_id = ? AND date LIKE ? "
                        "AND amount > 0", (user_id, f"{month_prefix}%")).fetchone()[0]
        fin = c.execute("SELECT COALESCE(SUM(-amount),0) FROM adjustments WHERE user_id = ? AND date LIKE ? "
                        "AND amount < 0", (user_id, f"{month_prefix}%")).fetchone()[0]
        abs_rows = c.execute("SELECT type, COUNT(*) FROM absences WHERE user_id = ? AND date LIKE ? GROUP BY type",
                             (user_id, f"{month_prefix}%")).fetchall()
    rate = logic.hourly_rate(u["salary"], norm, u["daily_hours"])
    earned = (a[2] or 0) * rate
    return {
        "user_id": user_id, "name": u["name"], "month": month_prefix, "salary": u["salary"],
        "norm_days": norm, "daily_hours": u["daily_hours"], "hourly_rate": rate,
        "days_came": a[0], "work_hours": a[1] or 0, "paid_hours": a[2] or 0,
        "lateness_total": a[3] or 0, "late_days": a[4] or 0, "break_minutes": a[5] or 0,
        "earned": earned, "advances": adv or 0, "bonuses": bon or 0, "fines": fin or 0,
        "net": earned + (bon or 0) - (fin or 0) - (adv or 0),
        "absences": {t: n for t, n in abs_rows}, "is_active": u["is_active"],
    }


def month_summary_all(month_prefix: str):
    """Faol xodimlar + shu oyda davomati bo'lgan arxivlangan xodimlar."""
    with get_db() as conn:
        had = {r[0] for r in conn.execute("SELECT DISTINCT user_id FROM attendance WHERE date LIKE ?",
                                          (f"{month_prefix}%",)).fetchall()}
    users = [u for u in list_users(include_archived=True)
             if u["is_approved"] == 1 and (u["is_active"] or u["user_id"] in had)]
    return [salary_summary(u["user_id"], month_prefix) for u in users]


def daily_history(user_id: int, month_prefix: str):
    """Oy bo'yicha kunma-kun tarix (davomat + yo'qliklar)."""
    with get_db() as conn:
        att = conn.execute(
            "SELECT date, check_in_time, check_out_time, work_hours, paid_hours, lateness_minutes, break_minutes "
            "FROM attendance WHERE user_id = ? AND date LIKE ? ORDER BY date", (user_id, f"{month_prefix}%")).fetchall()
        abs_ = conn.execute("SELECT date, type FROM absences WHERE user_id = ? AND date LIKE ?",
                            (user_id, f"{month_prefix}%")).fetchall()
    days = {}
    for d, ci, co, wh, ph, lt, bm in att:
        days[d] = {"date": d, "check_in": (ci or "")[:5], "check_out": (co or "")[:5], "work_hours": wh or 0,
                   "paid_hours": ph or 0, "lateness": lt or 0, "break_minutes": bm or 0, "absence": None}
    for d, t in abs_:
        days.setdefault(d, {"date": d, "check_in": "", "check_out": "", "work_hours": 0, "paid_hours": 0,
                            "lateness": 0, "break_minutes": 0, "absence": None})["absence"] = t
    return [days[k] for k in sorted(days)]


def daily_rows(date_str: str):
    """Kunlik Excel uchun: (kelganlar ro'yxati, kelmaganlar, sababli)."""
    st = employee_status(date_str)
    rows = []
    month = date_str[:7]
    for item in st["ishda"] + st["ketgan"]:
        s = salary_summary(item["user_id"], month)
        with get_db() as conn:
            r = conn.execute("SELECT break_minutes, paid_hours FROM attendance WHERE user_id = ? AND date = ?",
                             (item["user_id"], date_str)).fetchone()
        paid = (r[1] or item["work_hours"]) if r else 0
        rows.append(dict(item, break_minutes=(r[0] if r else 0) or 0, paid_hours=paid,
                         earned=paid * (s["hourly_rate"] if s else 0)))
    rows.sort(key=lambda x: x["name"].lower())
    return rows, st["kelmagan"], st["sababli"]


# ================= WEB SAYT: JADVAL VA GRAFIKLAR =================
def _month_users(month_prefix: str):
    with get_db() as conn:
        had = {r[0] for r in conn.execute("SELECT DISTINCT user_id FROM attendance WHERE date LIKE ?",
                                          (f"{month_prefix}%",)).fetchall()}
    return [u for u in list_users(include_archived=True)
            if u["is_approved"] == 1 and (u["is_active"] or u["user_id"] in had)]


def month_grid(month_prefix: str):
    """Barcha xodimlar × oy kunlari jadvali (web sayt uchun)."""
    year, mon = (int(x) for x in month_prefix.split("-"))
    dim = calendar.monthrange(year, mon)[1]
    today = config.today_str()
    rows = []
    with get_db() as conn:
        c = conn.cursor()
        for u in _month_users(month_prefix):
            uid = u["user_id"]
            att = {r[0]: r for r in c.execute(
                "SELECT date, check_in_time, check_out_time, lateness_minutes, work_hours, location_flag "
                "FROM attendance WHERE user_id = ? AND date LIKE ?", (uid, f"{month_prefix}%")).fetchall()}
            absn = dict(c.execute("SELECT date, type FROM absences WHERE user_id = ? AND date LIKE ?",
                                  (uid, f"{month_prefix}%")).fetchall())
            days, tot_h, tot_late, came, missed = {}, 0.0, 0, 0, 0
            for d in range(1, dim + 1):
                ds = f"{month_prefix}-{d:02d}"
                cell = {"work": is_work_day(c, uid, ds), "future": ds > today}
                r = att.get(ds)
                if r:
                    cell.update({"in": (r[1] or "")[:5], "out": (r[2] or "")[:5], "late": r[3] or 0,
                                 "hours": r[4] or 0, "flag": bool(r[5])})
                    tot_h += r[4] or 0
                    tot_late += r[3] or 0
                    came += 1
                elif ds in absn:
                    cell["abs"] = absn[ds]
                elif cell["work"] and not cell["future"] and ds != today:
                    cell["missed"] = True
                    missed += 1
                days[d] = cell
            rows.append({"user_id": uid, "name": u["name"], "is_active": u["is_active"], "days": days,
                         "came": came, "hours": round(tot_h, 1), "late": tot_late, "missed": missed})
    sundays = [d for d in range(1, dim + 1) if datetime(year, mon, d).weekday() == 6]
    return {"month": month_prefix, "days_in_month": dim, "sundays": sundays, "today": today, "rows": rows}


def _shift_month(month_prefix: str, delta: int) -> str:
    y, m = (int(x) for x in month_prefix.split("-"))
    m += delta
    while m < 1:
        m += 12
        y -= 1
    while m > 12:
        m -= 12
        y += 1
    return f"{y}-{m:02d}"


def month_charts(month_prefix: str, trend_months: int = 6):
    """Grafiklar uchun: kunlik davomat, xodimlar kesimi va oylar dinamikasi."""
    grid = month_grid(month_prefix)
    daily = []
    for d in range(1, grid["days_in_month"] + 1):
        came = late = late_min = missed = 0
        for r in grid["rows"]:
            cell = r["days"][d]
            if cell.get("in"):
                came += 1
                if cell.get("late"):
                    late += 1
                    late_min += cell["late"]
            if cell.get("missed"):
                missed += 1
        daily.append({"day": d, "came": came, "late": late, "late_minutes": late_min, "missed": missed,
                      "future": grid["rows"][0]["days"][d]["future"] if grid["rows"] else False})
    people = [{"name": s["name"], "hours": round(s["work_hours"], 1), "late": s["lateness_total"],
               "late_days": s["late_days"], "net": round(s["net"])} for s in month_summary_all(month_prefix)]
    trend = []
    for i in range(trend_months - 1, -1, -1):
        m = _shift_month(month_prefix, -i)
        rows = month_summary_all(m)
        trend.append({"month": m, "net": round(sum(r["net"] for r in rows)),
                      "earned": round(sum(r["earned"] for r in rows)),
                      "hours": round(sum(r["work_hours"] for r in rows), 1),
                      "late": sum(r["lateness_total"] for r in rows)})
    return {"month": month_prefix, "daily": daily, "people": people, "trend": trend}
