"""Keldi-ketdi Telegram bot (xodimlar davomati).

Tuzilma:
  config.py   - sozlamalar (.env)
  database.py - SQLite ulanish, sxema, zaxira nusxa
  logic.py    - sof hisob-kitob (kechikish, soat, stavka, validatsiya)
  repo.py     - bazadagi barcha amallar
  reports.py  - Excel va PDF hisobotlar
  auth.py     - Web Panel kirish tokenlari
  main.py     - shu fayl: Telegram handlerlar va rejalashtirilgan ishlar
  webapp.py   - admin Web Panel (Flask)
"""
import asyncio
import logging
import os
import sys
import tempfile
import time
from datetime import datetime, timedelta
from logging.handlers import RotatingFileHandler

from aiogram import Bot, Dispatcher, F, types
from aiogram.exceptions import TelegramForbiddenError
from aiogram.filters import Command, CommandStart
from aiogram.fsm.context import FSMContext
from aiogram.fsm.state import State, StatesGroup
from aiogram.fsm.storage.memory import MemoryStorage
from aiogram.types import (FSInputFile, InlineKeyboardButton, InlineKeyboardMarkup, KeyboardButton,
                           ReplyKeyboardMarkup, WebAppInfo)
from apscheduler.schedulers.asyncio import AsyncIOScheduler

import auth
import config
import database
import logic
import reports
import repo
import voice_report
from config import ADMIN_IDS, esc, get_now, money

# ================= LOGGING =================
logger = logging.getLogger("attendance_bot")


def setup_logging():
    fmt = logging.Formatter("%(asctime)s [%(levelname)s] %(name)s: %(message)s")
    root = logging.getLogger()
    root.setLevel(logging.INFO)
    if not any(isinstance(h, RotatingFileHandler) for h in root.handlers):
        fh = RotatingFileHandler(config.LOG_FILE, maxBytes=2_000_000, backupCount=3, encoding="utf-8")
        fh.setFormatter(fmt)
        root.addHandler(fh)
    if not any(type(h) is logging.StreamHandler for h in root.handlers):
        sh = logging.StreamHandler(sys.stdout)
        sh.setFormatter(fmt)
        root.addHandler(sh)
    # Har daqiqalik ishlar ("Running job ... executed successfully") logni to'ldirib yubormasin
    logging.getLogger("apscheduler").setLevel(logging.WARNING)


async def run_db(func, *args, **kwargs):
    """Bloklovchi SQLite chaqiruvlarini alohida thread'da bajaradi."""
    return await asyncio.to_thread(func, *args, **kwargs)


# ================= FSM =================
class AttendanceState(StatesGroup):
    waiting_for_reason = State()
    waiting_for_checkout_location = State()
    waiting_for_broadcast_text = State()


bot: Bot | None = None
dp = Dispatcher(storage=MemoryStorage())
scheduler: AsyncIOScheduler | None = None
VOICE_CFG = voice_report.VoiceConfig.from_env()

MAX_REASON_LEN = 500

# ================= KLAVIATURALAR =================
# Tugmalar 3 ustunda - telefonda skrol qilmasdan hammasi ko'rinadi.
_user_rows = [
    [KeyboardButton(text="🟢 Ishga keldim"), KeyboardButton(text="🔴 Ishdan ketdim"),
     KeyboardButton(text="☕ Tanaffusga chiqdim")],
    [KeyboardButton(text="🏢 Tanaffusdan qaytdim"), KeyboardButton(text="📊 Mening statistikam"),
     KeyboardButton(text="💰 Mening oyligim")],
]
# Admin tugmalari. Eski (uzun) nomlar ham qabul qilinadi - xodimlarda eski klaviatura qolgan bo'lsa ishlayversin.
BTN_REPORT = "📈 Excel hisobot"
BTN_BROADCAST = "📢 Xabarnoma"
REPORT_TEXTS = {BTN_REPORT, "📈 Barcha davomat hisoboti (Excel)"}
BROADCAST_TEXTS = {BTN_BROADCAST, "📢 Xabarnoma yuborish"}
user_keyboard = ReplyKeyboardMarkup(keyboard=_user_rows, resize_keyboard=True)

location_keyboard = ReplyKeyboardMarkup(
    keyboard=[[KeyboardButton(text="📍 Joylashuvni yuborish", request_location=True)]],
    resize_keyboard=True, one_time_keyboard=True,
)


BTN_ADMIN_PANEL = "🖥 Admin panel"


def get_kb_for(user_id: int):
    """Admin klaviaturasi. Oxirgi qatordagi "🖥 Admin panel" web saytga kirish
    havolasini beradi. Telegram ichidagi WebApp tugmasi hozircha yashirin
    (SHOW_WEBAPP_BUTTON=1 bo'lsa yoniga qo'shiladi)."""
    if user_id not in ADMIN_IDS:
        return user_keyboard
    rows = [list(r) for r in _user_rows] + [
        [KeyboardButton(text="👥 Xodimlar"), KeyboardButton(text=BTN_REPORT), KeyboardButton(text=BTN_BROADCAST)],
    ]
    last = [KeyboardButton(text=BTN_ADMIN_PANEL)]
    if config.SHOW_WEBAPP_BUTTON:
        url = auth.webapp_url_for(user_id)
        if url:
            last.append(KeyboardButton(text="📱 Web Panel", web_app=WebAppInfo(url=url)))
    rows.append(last)
    return ReplyKeyboardMarkup(keyboard=rows, resize_keyboard=True)


NOT_APPROVED = "⏳ Sizning arizangiz hali admin tomonidan tasdiqlanmagan."


async def is_approved(user_id: int) -> bool:
    return await run_db(repo.is_approved, user_id)


async def notify_admins(text: str, **kwargs) -> dict:
    """Barcha adminlarga yuboradi. {admin_id: message_id} qaytaradi (keyin tahrirlash uchun)."""
    sent = {}
    for admin_id in ADMIN_IDS:
        try:
            m = await bot.send_message(chat_id=admin_id, text=text, parse_mode="HTML", **kwargs)
            sent[admin_id] = getattr(m, "message_id", None)
        except Exception as e:
            logger.error(f"Adminga ({admin_id}) xabar yuborilmadi: {e}")
    return sent


async def safe_send(chat_id: int, text: str, **kwargs) -> bool:
    try:
        await bot.send_message(chat_id=chat_id, text=text, **kwargs)
        return True
    except TelegramForbiddenError:
        logger.info(f"{chat_id} botni bloklagan.")
    except Exception as e:
        logger.error(f"Xabar yuborilmadi ({chat_id}): {e}")
    return False


def _tmp_path(name: str) -> str:
    return os.path.join(tempfile.gettempdir(), f"kk_{os.getpid()}_{int(time.time()*1000)}_{name}")


async def send_file(chat_id: int, path: str, filename: str, caption: str):
    try:
        await bot.send_document(chat_id=chat_id, document=FSInputFile(path, filename=filename),
                                caption=caption, parse_mode="HTML")
        return True
    except TelegramForbiddenError:
        logger.info(f"{chat_id} botni bloklagan, fayl yuborilmadi.")
    except Exception as e:
        logger.error(f"Fayl yuborilmadi ({chat_id}): {e}")
    return False


def _remove(path):
    try:
        os.remove(path)
    except OSError:
        pass


# ================= /cancel =================
@dp.message(Command("cancel"))
async def cmd_cancel(message: types.Message, state: FSMContext):
    if await state.get_state() is None:
        await message.answer("ℹ️ Hozircha bekor qilinadigan amal yo'q.")
        return
    await state.clear()
    await message.answer("❌ Amal bekor qilindi.", reply_markup=get_kb_for(message.from_user.id))


# ================= START VA TASDIQLASH =================
@dp.message(CommandStart())
async def cmd_start(message: types.Message):
    uid = message.from_user.id
    full_name = message.from_user.full_name
    status = await run_db(repo.user_status, uid)

    if uid in ADMIN_IDS:
        await run_db(repo.insert_admin_user, uid, full_name)
        await message.answer(f"Assalomu alaykum, Admin <b>{esc(full_name)}</b>!",
                             reply_markup=get_kb_for(uid), parse_mode="HTML")
        return
    if status == "rejected":
        await message.answer("❌ Arizangiz admin tomonidan rad etilgan.")
        return
    if status == "archived":
        await message.answer("ℹ️ Hisobingiz arxivlangan. Savollar bo'lsa, rahbaringizga murojaat qiling.")
        return
    if status == "pending":
        await message.answer(NOT_APPROVED)
        return
    if status is None:
        await run_db(repo.insert_pending_user, uid, full_name)
        await message.answer("⏳ <b>Arizangiz adminga yuborildi.</b> Admin tasdiqlashini kuting...", parse_mode="HTML")
        kb = InlineKeyboardMarkup(inline_keyboard=[[
            InlineKeyboardButton(text="✅ Tasdiqlash", callback_data=f"approve_{uid}"),
            InlineKeyboardButton(text="❌ Rad etish", callback_data=f"reject_{uid}"),
        ]])
        await notify_admins(f"🔔 <b>Yangi foydalanuvchi:</b>\n👤 F.I.Sh: {esc(full_name)}\n🆔 ID: <code>{uid}</code>",
                            reply_markup=kb)
        return
    await message.answer(f"Assalomu alaykum, <b>{esc(full_name)}</b>!",
                         reply_markup=get_kb_for(uid), parse_mode="HTML")


@dp.callback_query(F.data.startswith("approve_"))
async def cb_approve(callback: types.CallbackQuery):
    if callback.from_user.id not in ADMIN_IDS:
        await callback.answer("Sizda ruxsat yo'q!", show_alert=True)
        return
    uid = int(callback.data.split("_")[1])
    await run_db(repo.approve_user, uid, callback.from_user.id)
    await callback.message.edit_text(f"✅ Foydalanuvchi ({uid}) tasdiqlandi!")
    await safe_send(uid, "🎉 <b>Arizangiz tasdiqlandi!</b> Endi botdan foydalanishingiz mumkin.",
                    reply_markup=user_keyboard, parse_mode="HTML")
    await callback.answer()


@dp.callback_query(F.data.startswith("reject_"))
async def cb_reject(callback: types.CallbackQuery):
    if callback.from_user.id not in ADMIN_IDS:
        await callback.answer("Sizda ruxsat yo'q!", show_alert=True)
        return
    uid = int(callback.data.split("_")[1])
    await run_db(repo.reject_user, uid, callback.from_user.id)
    await callback.message.edit_text(f"❌ Foydalanuvchi ({uid}) rad etildi.")
    await safe_send(uid, "❌ Afsuski, arizangiz admin tomonidan rad etildi.")
    await callback.answer()


# ================= KELISH =================
def _already_text(rec, kind: str) -> str:
    if kind == "in":
        if rec["checkin_source"] == "admin":
            return f"ℹ️ Admin tomonidan siz bugun soat <b>{esc(rec['check_in'])}</b> da kelgan deb belgilangansiz."
        return f"⚠️ Siz bugun soat <b>{esc(rec['check_in'])}</b> da kelgansiz!"
    src = rec["checkout_source"]
    if src == "admin":
        return f"ℹ️ Admin tomonidan siz bugun soat <b>{esc(rec['check_out'])}</b> da ketgan deb belgilangansiz."
    if src in ("auto", "auto_night"):
        return f"ℹ️ Tizim sizni bugun soat <b>{esc(rec['check_out'])}</b> da avtomatik ketdi deb belgilagan."
    return f"⚠️ Siz bugun soat <b>{esc(rec['check_out'])}</b> da ketganingiz belgilangan!"


@dp.message(F.text == "🟢 Ishga keldim")
async def ask_location_for_checkin(message: types.Message, state: FSMContext):
    if not await is_approved(message.from_user.id):
        await message.answer(NOT_APPROVED)
        return
    await state.clear()
    rec = await run_db(repo.get_attendance, message.from_user.id, config.today_str())
    if rec:
        await message.answer(_already_text(rec, "in"), parse_mode="HTML")
        return
    await message.answer("📍 Joylashuvni yuborish uchun pastdagi tugmani bosing:", reply_markup=location_keyboard)


def _location_eval(message: types.Message) -> dict:
    loc = message.location
    return logic.evaluate_location(
        loc.latitude, loc.longitude,
        accuracy=getattr(loc, "horizontal_accuracy", None),
        forwarded=getattr(message, "forward_origin", None) is not None,
        is_venue=message.venue is not None,
    )


SOURCE_NOTE = {
    "auto": "ℹ️ Ish vaqti tugagani uchun tizim avtomatik yopdi",
    "auto_night": "🌙 «Ishdan ketdim» bosilmagan - tizim kechasi avtomatik yopdi",
    "admin": "✏️ Ketish vaqtini admin kiritgan",
}


def checkin_alert_text(name: str, rec: dict, waiting_reason: bool = False) -> str:
    """Admin uchun "ishga keldi" xabari - "ishdan ketdi" xabari bilan bir xil uslubda."""
    late = rec.get("lateness") or 0
    late_txt = f"{late} daqiqa" if late else "yo'q"
    lines = ["🟢 <b>Xodim ishga keldi</b>", "",
             f"👤 Xodim: <b>{esc(name)}</b>",
             f"⏰ Kelgan vaqti: <b>{(rec.get('check_in') or '')[:5] or '—'}</b>",
             f"⏱ Kechikish: <b>{late_txt}</b>"]
    if late:
        reason = rec.get("reason") or ""
        if reason:
            lines.append(f"📝 Sababi: <i>{esc(reason)}</i>")
        else:
            lines.append("📝 Sababi: <i>kutilmoqda...</i>" if waiting_reason else "📝 Sababi: <i>yozilmagan</i>")
    if rec.get("location_flag"):
        lines.append("🕵️ Joylashuv shubhali (aniqligi yo'q) - tekshirib ko'ring")
    return "\n".join(lines)


def day_alert_text(name: str, rec: dict, extra_flag: str | None = None, date_str: str | None = None) -> str:
    """Admin uchun YAGONA ko'rinishdagi xabar: xodim ketganda bir marta yuboriladi."""
    hm = lambda t: (t or "")[:5] or "—"
    late = rec.get("lateness") or 0
    late_txt = f"{late} daqiqa" if late else "yo'q"
    lines = ["🔴 <b>Xodim ishdan ketdi</b>" + (f" ({esc(date_str)})" if date_str else ""), "",
             f"👤 Xodim: <b>{esc(name)}</b>",
             f"⏰ Kelgan vaqti: <b>{hm(rec.get('check_in'))}</b>",
             f"🚪 Ketgan vaqti: <b>{hm(rec.get('check_out'))}</b>",
             f"⏱ Kechikish: <b>{late_txt}</b>"]
    if late:
        reason = rec.get("reason") or ""
        lines.append(f"📝 Sababi: <i>{esc(reason)}</i>" if reason else "📝 Sababi: <i>yozilmagan</i>")
    if rec.get("break_minutes"):
        lines.append(f"☕ Tanaffus: <b>{rec['break_minutes']} daqiqa</b>")
    lines.append(f"🕒 Ishlagan vaqti: <b>{rec.get('work_hours') or 0} soat</b>")
    flags = [f for f in (rec.get("location_flag"), extra_flag) if f]
    if flags:
        lines.append("🕵️ Joylashuv shubhali (aniqligi yo'q) - tekshirib ko'ring")
    note = SOURCE_NOTE.get(rec.get("checkout_source"))
    if note:
        lines.append(note)
    return "\n".join(lines)


async def send_day_alert(uid: int, date_str: str, fallback_name: str = "", extra_flag: str | None = None,
                         show_date: bool = False):
    rec = await run_db(repo.get_attendance, uid, date_str)
    if not rec:
        return
    name = await run_db(repo.display_name, uid, fallback_name)
    await notify_admins(day_alert_text(name, rec, extra_flag, date_str if show_date else None))


@dp.message(F.location, AttendanceState.waiting_for_checkout_location)
async def process_checkout_location(message: types.Message, state: FSMContext):
    uid = message.from_user.id
    kb = get_kb_for(uid)
    if not await is_approved(uid):
        await state.clear()
        await message.answer(NOT_APPROVED)
        return
    ev = _location_eval(message)
    if not ev["ok"]:
        await state.clear()
        await message.answer(f"❌ <b>Joylashuv xatosi!</b> {esc(ev['error'])}", reply_markup=kb, parse_mode="HTML")
        return

    now = get_now()
    today = now.strftime("%Y-%m-%d")
    await state.clear()
    status, info = await run_db(repo.checkout, uid, today, now)
    if status == "no_checkin":
        # Kecha "Ketdim" bosib lokatsiya yubormay qolgan bo'lsa - bu yuborilgan
        # joylashuv aslida bugungi KELISH uchundir.
        await handle_location(message, state)
        return
    if status == "already_checked_out":
        rec = await run_db(repo.get_attendance, uid, today)
        await message.answer(_already_text(rec, "out"), reply_markup=kb, parse_mode="HTML")
        return

    s = await run_db(repo.salary_summary, uid, today[:7])
    daily = info["paid_hours"] * (s["hourly_rate"] if s else 0)
    extra = ""
    if info["break_autoclosed"]:
        extra += f"\nℹ️ Tanaffusdan qaytish belgilanmagan edi - tanaffus avtomatik yopildi (+{info['break_added']} daq.)."
    if info["clamped"]:
        extra += f"\nℹ️ Ish vaqtingiz ({info['check_out'][:5]}) tugagani uchun ketish vaqti shu vaqtga belgilandi."
    if info["paid_hours"] != info["work_hours"]:
        extra += f"\n⭐ Kech/yakshanba soatlari 2 barobar hisoblandi (to'lanadigan soat: <b>{info['paid_hours']}</b>)."
    await message.answer(
        f"🔴 <b>Ishdan ketganingiz belgilandi!</b>\n⏰ Ketish vaqti: <b>{info['check_out']}</b>\n"
        f"⏱ Ishlangan net-vaqt: <b>{info['work_hours']} soat</b>\n"
        f"💰 Bugungi ishlagan pulingiz: <b>{money(daily)} so'm</b>{extra}",
        reply_markup=kb, parse_mode="HTML")

    await send_day_alert(uid, today, message.from_user.full_name, extra_flag=ev["flag"])


@dp.message(F.location)
async def handle_location(message: types.Message, state: FSMContext):
    uid = message.from_user.id
    kb = get_kb_for(uid)
    if not await is_approved(uid):
        await message.answer(NOT_APPROVED)
        return
    ev = _location_eval(message)
    if not ev["ok"]:
        await message.answer(f"❌ <b>Joylashuv xatosi!</b> {esc(ev['error'])}", reply_markup=kb, parse_mode="HTML")
        return

    now = get_now()
    today = now.strftime("%Y-%m-%d")
    t = now.strftime("%H:%M:%S")
    lateness, _work_day = await run_db(repo.prepare_checkin, uid, today, t)
    loc = message.location
    att_id = await run_db(repo.checkin, uid, today, t, lateness, loc.latitude, loc.longitude,
                          getattr(loc, "horizontal_accuracy", None), ev["flag"])
    if att_id is None:
        rec = await run_db(repo.get_attendance, uid, today)
        await message.answer(_already_text(rec, "in"), reply_markup=kb, parse_mode="HTML")
        return

    # 1-xabar adminga: "ishga keldi" - shu zahoti (real vaqtda)
    name = await run_db(repo.display_name, uid, message.from_user.full_name)
    rec = await run_db(repo.get_attendance, uid, today)
    sent = await notify_admins(checkin_alert_text(name, rec, waiting_reason=lateness > 0))

    if lateness > 0:
        await state.set_state(AttendanceState.waiting_for_reason)
        # Sabab yozilganda yangi xabar emas, shu "ishga keldi" xabari tahrirlanadi
        await state.update_data(attendance_id=att_id, lateness=lateness, current_time=t,
                                admin_msgs={str(k): v for k, v in sent.items() if v})
        await message.answer(f"⚠️ <b>Siz {lateness} daqiqa kechikdingiz!</b>\n\n"
                             f"Iltimos, kechikish sababini matn bilan yozib qoldiring:",
                             reply_markup=types.ReplyKeyboardRemove(), parse_mode="HTML")
    else:
        await message.answer(f"✅ <b>Ishga kelganingiz belgilandi!</b>\n⏰ Vaqt: <b>{t}</b>",
                             reply_markup=kb, parse_mode="HTML")


@dp.message(AttendanceState.waiting_for_reason)
async def handle_lateness_reason(message: types.Message, state: FSMContext):
    reason = (message.text or "").strip()
    if not reason or reason.startswith("/") or len(reason) < 3 or not any(ch.isalpha() for ch in reason):
        await message.answer("✍️ Iltimos, kechikish sababini so'z bilan yozing (masalan: <i>tirbandlik</i>).",
                             parse_mode="HTML")
        return
    if len(reason) > MAX_REASON_LEN:
        reason = reason[:MAX_REASON_LEN] + "…"
    data = await state.get_data()
    await state.clear()
    uid = message.from_user.id
    if "attendance_id" not in data:
        await message.answer("ℹ️ Sabab saqlanmadi (sessiya eskirgan).", reply_markup=get_kb_for(uid))
        return
    await run_db(repo.save_lateness_reason, data["attendance_id"], reason)
    await message.answer("✅ <b>Rahmat! Sabab saqlandi.</b>", reply_markup=get_kb_for(uid), parse_mode="HTML")
    # Adminga yangi (3-) xabar yuborilmaydi - "ishga keldi" xabarining o'zi sabab bilan yangilanadi
    rec = await run_db(repo.get_attendance, uid, config.today_str())
    if rec and rec["id"] == data["attendance_id"]:
        name = await run_db(repo.display_name, uid, message.from_user.full_name)
        text = checkin_alert_text(name, rec)
        for admin_id, msg_id in (data.get("admin_msgs") or {}).items():
            try:
                await bot.edit_message_text(chat_id=int(admin_id), message_id=msg_id, text=text, parse_mode="HTML")
            except Exception as e:
                logger.warning(f"Admin xabarini yangilab bo'lmadi ({admin_id}): {e}")


# ================= TANAFFUS =================
@dp.message(F.text == "☕ Tanaffusga chiqdim")
async def handle_break_start(message: types.Message):
    uid = message.from_user.id
    if not await is_approved(uid):
        await message.answer(NOT_APPROVED)
        return
    now = get_now()
    status, extra = await run_db(repo.break_start, uid, now.strftime("%Y-%m-%d"), now.strftime("%H:%M:%S"))
    msgs = {
        "no_checkin": "⚠️ Avval ishga kelganingizni belgilang!",
        "already_checked_out": "⚠️ Siz bugun allaqachon ishdan ketgansiz!",
        "already_on_break": f"⚠️ Siz allaqachon soat <b>{esc(extra)}</b> da tanaffusga chiqqansiz!",
    }
    if status in msgs:
        await message.answer(msgs[status], parse_mode="HTML")
        return
    await message.answer(f"☕ <b>Tanaffus boshlandi!</b>\nSoat: <b>{now.strftime('%H:%M:%S')}</b>\n"
                         f"Yoqimli hordiq chiqaring!", parse_mode="HTML")


@dp.message(F.text == "🏢 Tanaffusdan qaytdim")
async def handle_break_end(message: types.Message):
    uid = message.from_user.id
    if not await is_approved(uid):
        await message.answer(NOT_APPROVED)
        return
    now = get_now()
    status, mins = await run_db(repo.break_end, uid, now.strftime("%Y-%m-%d"), now)
    if status == "not_on_break":
        await message.answer("⚠️ Siz tanaffusga chiqqaningizni belgilamagansiz!")
        return
    if status == "already_returned":
        await message.answer("⚠️ Siz tanaffusdan qaytganingizni belgilab bo'lgansiz!")
        return
    await message.answer(f"🏢 <b>Ishga qaytganingiz belgilandi!</b>\nTanaffus davomiyligi: <b>{mins} daqiqa</b>.",
                         parse_mode="HTML")


# ================= KETISH =================
@dp.message(F.text == "🔴 Ishdan ketdim")
async def handle_checkout_request(message: types.Message, state: FSMContext):
    uid = message.from_user.id
    if not await is_approved(uid):
        await message.answer(NOT_APPROVED)
        return
    rec = await run_db(repo.get_attendance, uid, config.today_str())
    if not rec:
        await message.answer("⚠️ Siz bugun ishga kelganingizni belgilamagansiz!")
        return
    if rec["check_out"]:
        await message.answer(_already_text(rec, "out"), parse_mode="HTML")
        return
    await state.set_state(AttendanceState.waiting_for_checkout_location)
    await message.answer("📍 Ishdan ketishni tasdiqlash uchun joylashuvni yuboring:", reply_markup=location_keyboard)


# ================= STATISTIKA, TARIX, OYLIK =================
def _prev_month(month_prefix: str) -> str:
    y, m = (int(x) for x in month_prefix.split("-"))
    return f"{y - 1}-12" if m == 1 else f"{y}-{m - 1:02d}"


def _stats_kb(month_prefix: str) -> InlineKeyboardMarkup:
    prev = _prev_month(month_prefix)
    return InlineKeyboardMarkup(inline_keyboard=[
        [InlineKeyboardButton(text="📅 Kunma-kun", callback_data=f"hist_{month_prefix}")],
        [InlineKeyboardButton(text=f"◀️ {config.month_label(prev)}", callback_data=f"stat_{prev}")],
    ])


def _stats_text(s: dict) -> str:
    extra = ""
    if round(s["paid_hours"], 2) != round(s["work_hours"], 2):
        extra = f"⭐ 2x hisobga olingan soat: <b>{s['paid_hours']:.1f} soat</b>\n"
    ab = s["absences"]
    ab_line = ""
    if ab:
        ab_line = (f"📝 Yo'qliklar: ta'til {ab.get('tatil', 0)}, kasallik {ab.get('kasal', 0)}, "
                   f"ruxsat {ab.get('otgul', 0)} kun\n")
    return (f"📊 <b>{config.month_label(s['month'])} bo'yicha statistikangiz:</b>\n\n"
            f"📅 Ishga kelgan kunlar: <b>{s['days_came']} kun</b>\n"
            f"⏱ Jami ishlangan net-soat: <b>{s['work_hours']:.1f} soat</b>\n{extra}"
            f"☕ Jami tanaffus: <b>{s['break_minutes']} daqiqa</b>\n"
            f"⚠️ Kechikish: <b>{s['late_days']} marta, {s['lateness_total']} daqiqa</b>\n{ab_line}"
            f"💵 Belgilangan oylik: <b>{money(s['salary'])} so'm</b> "
            f"(norma: {s['norm_days']} kun × {s['daily_hours']:g} soat)\n"
            f"💰 <b>Topilgan pul (soatbay): {money(s['earned'])} so'm</b>")


@dp.message(F.text == "📊 Mening statistikam")
async def cmd_user_stats(message: types.Message):
    uid = message.from_user.id
    if not await is_approved(uid):
        await message.answer(NOT_APPROVED)
        return
    month = get_now().strftime("%Y-%m")
    s = await run_db(repo.salary_summary, uid, month)
    if not s:
        await message.answer("⚠️ Ma'lumot topilmadi.")
        return
    await message.answer(_stats_text(s), parse_mode="HTML", reply_markup=_stats_kb(month))


def _valid_month(raw: str) -> str | None:
    try:
        datetime.strptime(raw + "-01", "%Y-%m-%d")
        return raw
    except ValueError:
        return None


@dp.callback_query(F.data.startswith("stat_"))
async def cb_stats_month(callback: types.CallbackQuery):
    month = _valid_month(callback.data.removeprefix("stat_"))
    if not month or not await is_approved(callback.from_user.id):
        await callback.answer()
        return
    s = await run_db(repo.salary_summary, callback.from_user.id, month)
    await callback.message.answer(_stats_text(s), parse_mode="HTML", reply_markup=_stats_kb(month))
    await callback.answer()


ABS_SHORT_UZ = {"tatil": "🏖 ta'til", "kasal": "🤒 kasallik", "otgul": "📝 ruxsat"}


def history_text(name: str, month: str, hist: list) -> str:
    lines = [f"📅 <b>{esc(name)} — {config.month_label(month)}</b>\n"]
    if not hist:
        lines.append("<i>Bu oy uchun yozuv yo'q.</i>")
    for h in hist:
        d = h["date"][8:]
        if h["absence"] and not h["check_in"]:
            lines.append(f"<code>{d}</code> {ABS_SHORT_UZ.get(h['absence'], h['absence'])}")
            continue
        late = f" ⚠️{h['lateness']}d" if h["lateness"] else ""
        out = h["check_out"] or "…"
        lines.append(f"<code>{d}</code> {h['check_in']}–{out} · {h['work_hours']:.1f} soat{late}")
    return "\n".join(lines)


@dp.callback_query(F.data.startswith("hist_"))
async def cb_history(callback: types.CallbackQuery):
    month = _valid_month(callback.data.removeprefix("hist_"))
    uid = callback.from_user.id
    if not month or not await is_approved(uid):
        await callback.answer()
        return
    hist = await run_db(repo.daily_history, uid, month)
    name = await run_db(repo.display_name, uid, callback.from_user.full_name)
    await callback.message.answer(history_text(name, month, hist), parse_mode="HTML")
    await callback.answer()


def salary_text(s: dict) -> str:
    extra = ""
    if round(s["paid_hours"], 2) != round(s["work_hours"], 2):
        extra = f"⭐ Jumladan 2x hisobga olingan holda: <b>{s['paid_hours']:.1f} soat</b>\n"
    return (f"💰 <b>{config.month_label(s['month'])} — oylik hisob-kitob:</b>\n\n"
            f"👤 Xodim: <b>{esc(s['name'])}</b>\n💵 Belgilangan oylik: <b>{money(s['salary'])} so'm</b>\n"
            f"📅 Norma: <b>{s['norm_days']} kun × {s['daily_hours']:g} soat</b>\n"
            f"⏱ 1 soatlik ish haqi: <b>{money(s['hourly_rate'])} so'm</b>\n━━━━━━━━━━━━━━━━━━━\n"
            f"📈 Ishlangan soat: <b>{s['work_hours']:.1f} soat</b>\n{extra}"
            f"💵 Hisoblangan: <b>{money(s['earned'])} so'm</b>\n"
            f"🎁 Bonus: <b>+{money(s['bonuses'])} so'm</b>\n🔻 Jarima: <b>-{money(s['fines'])} so'm</b>\n"
            f"💸 Avans: <b>-{money(s['advances'])} so'm</b>\n━━━━━━━━━━━━━━━━━━━\n"
            f"💼 <b>Qo'lga tegadigan sof oylik: {money(s['net'])} so'm</b>")


@dp.message(F.text == "💰 Mening oyligim")
async def cmd_my_salary(message: types.Message):
    uid = message.from_user.id
    if not await is_approved(uid):
        await message.answer(NOT_APPROVED)
        return
    month = get_now().strftime("%Y-%m")
    s = await run_db(repo.salary_summary, uid, month)
    if not s:
        await message.answer("⚠️ Ma'lumot topilmadi.")
        return
    prev = _prev_month(month)
    kb = InlineKeyboardMarkup(inline_keyboard=[
        [InlineKeyboardButton(text="📄 PDF hisob varaqasi", callback_data=f"pdf_{month}")],
        [InlineKeyboardButton(text=f"📄 {config.month_label(prev)} (PDF)", callback_data=f"pdf_{prev}")],
    ])
    await message.answer(salary_text(s), parse_mode="HTML", reply_markup=kb)


async def send_payslip(user_id: int, month: str, chat_id: int | None = None) -> bool:
    path = _tmp_path(f"varaqa_{user_id}_{month}.pdf")
    try:
        await run_db(reports.generate_payslip_pdf, user_id, month, path)
        return await send_file(chat_id or user_id, path, f"Hisob_varaqasi_{month}.pdf",
                               f"📄 <b>{config.month_label(month)}</b> — ish haqi hisob varaqasi")
    except Exception as e:
        logger.error(f"PDF yaratilmadi ({user_id}, {month}): {e}")
        return False
    finally:
        _remove(path)


@dp.callback_query(F.data.startswith("pdf_"))
async def cb_pdf(callback: types.CallbackQuery):
    month = _valid_month(callback.data.removeprefix("pdf_"))
    if not month or not await is_approved(callback.from_user.id):
        await callback.answer()
        return
    await callback.answer("⏳ Tayyorlanmoqda...")
    ok = await send_payslip(callback.from_user.id, month)
    if not ok:
        await callback.message.answer("❌ PDF tayyorlashda xatolik yuz berdi.")


# ================= ADMIN: EXCEL HISOBOT =================
def _report_month_kb(year: int) -> InlineKeyboardMarkup:
    now = get_now()
    rows, row = [], []
    for m in range(1, 13):
        if year > now.year or (year == now.year and m > now.month):
            break
        label = config.UZ_MONTHS[m - 1] + (" •" if (year, m) == (now.year, now.month) else "")
        row.append(InlineKeyboardButton(text=label, callback_data=f"rep_m_{year}-{m:02d}"))
        if len(row) == 3:
            rows.append(row)
            row = []
    if row:
        rows.append(row)
    nav = [InlineKeyboardButton(text=f"◀️ {year - 1}", callback_data=f"rep_y_{year - 1}")]
    if year < now.year:
        nav.append(InlineKeyboardButton(text=f"{year + 1} ▶️", callback_data=f"rep_y_{year + 1}"))
    rows.append(nav)
    return InlineKeyboardMarkup(inline_keyboard=rows)


@dp.message(F.text.in_(REPORT_TEXTS))
async def cmd_admin_report(message: types.Message):
    if message.from_user.id not in ADMIN_IDS:
        return
    year = get_now().year
    await message.answer(f"📅 Qaysi oy hisobotini yuklab olmoqchisiz? (<b>{year}</b>)",
                         reply_markup=_report_month_kb(year), parse_mode="HTML")


@dp.callback_query(F.data.startswith("rep_y_"))
async def cb_report_year(callback: types.CallbackQuery):
    if callback.from_user.id not in ADMIN_IDS:
        await callback.answer()
        return
    try:
        year = min(int(callback.data.removeprefix("rep_y_")), get_now().year)
    except ValueError:
        await callback.answer()
        return
    await callback.message.edit_text(f"📅 Qaysi oy hisobotini yuklab olmoqchisiz? (<b>{year}</b>)",
                                     reply_markup=_report_month_kb(year), parse_mode="HTML")
    await callback.answer()


async def send_monthly_excel(chat_id: int, month: str) -> bool:
    path = _tmp_path(f"oylik_{month}.xlsx")
    try:
        await run_db(reports.generate_monthly_excel, month, path)
        return await send_file(chat_id, path, f"Oylik_Hisobot_{month}.xlsx",
                               f"📊 <b>{config.month_label(month)}</b> — oylik, bonus/jarima, avans va sof maosh")
    except Exception as e:
        logger.error(f"Oylik Excel xatosi ({month}): {e}")
        return False
    finally:
        _remove(path)


@dp.callback_query(F.data.startswith("rep_m_"))
async def cb_report_month(callback: types.CallbackQuery):
    if callback.from_user.id not in ADMIN_IDS:
        await callback.answer()
        return
    month = _valid_month(callback.data.removeprefix("rep_m_"))
    if not month:
        await callback.answer("Noto'g'ri oy", show_alert=True)
        return
    await callback.answer(f"⏳ {config.month_label(month)} hisoboti tayyorlanmoqda...")
    if not await send_monthly_excel(callback.message.chat.id, month):
        await callback.message.answer("❌ Hisobot yaratishda xatolik yuz berdi.")


# ================= ADMIN: XABARNOMA =================
async def broadcast(text_builder) -> tuple[int, int]:
    users = await run_db(repo.active_employees, True)
    ok = fail = 0
    for u in users:
        if await safe_send(u["user_id"], text_builder(u["name"]), parse_mode="HTML",
                           reply_markup=get_kb_for(u["user_id"])):
            ok += 1
        else:
            fail += 1
        await asyncio.sleep(0.05)  # Telegram limitlariga tushmaslik uchun
    return ok, fail


@dp.message(F.text.in_(BROADCAST_TEXTS))
async def broadcast_menu(message: types.Message):
    if message.from_user.id not in ADMIN_IDS:
        return
    kb = InlineKeyboardMarkup(inline_keyboard=[
        [InlineKeyboardButton(text="☀️ Standart: Kelish eslatmasi", callback_data="broadcast_checkin")],
        [InlineKeyboardButton(text="🌙 Standart: Ketish eslatmasi", callback_data="broadcast_checkout")],
        [InlineKeyboardButton(text="✍️ Maxsus xabar yozish", callback_data="broadcast_custom")],
    ])
    await message.answer("📢 <b>Xabarnoma yuborish:</b>\n\nXabar barcha faol xodimlarga yuboriladi.",
                         reply_markup=kb, parse_mode="HTML")


def _bc_result(ok, fail, what):
    return f"✅ {what} <b>{ok}</b> ta xodimga yuborildi." + (f"\n⚠️ <b>{fail}</b> ta xodimga yetmadi." if fail else "")


@dp.callback_query(F.data.in_({"broadcast_checkin", "broadcast_checkout"}))
async def cb_broadcast_std(callback: types.CallbackQuery):
    if callback.from_user.id not in ADMIN_IDS:
        await callback.answer("Sizda ruxsat yo'q!", show_alert=True)
        return
    await callback.answer("Yuborilmoqda...")
    if callback.data == "broadcast_checkin":
        ok, fail = await broadcast(lambda n: f"Xayrli kun, <b>{esc(n)}</b>! ☀️\n"
                                             f"Ofisga kelgach, <b>🟢 Ishga keldim</b> tugmasini bosishni unutmang!")
        what = "Kelish eslatmasi"
    else:
        ok, fail = await broadcast(lambda n: f"Hurmatli <b>{esc(n)}</b>,\n"
                                             f"Ish vaqti tugagach, <b>🔴 Ishdan ketdim</b> tugmasini bosishni unutmang!")
        what = "Ketish eslatmasi"
    await callback.message.answer(_bc_result(ok, fail, what), parse_mode="HTML")


@dp.callback_query(F.data == "broadcast_custom")
async def cb_broadcast_custom(callback: types.CallbackQuery, state: FSMContext):
    if callback.from_user.id not in ADMIN_IDS:
        await callback.answer("Sizda ruxsat yo'q!", show_alert=True)
        return
    await callback.message.answer("✍️ Barcha xodimlarga yuboriladigan xabar matnini kiriting:\n(Bekor qilish: /cancel)")
    await state.set_state(AttendanceState.waiting_for_broadcast_text)
    await callback.answer()


@dp.message(AttendanceState.waiting_for_broadcast_text)
async def process_broadcast_text(message: types.Message, state: FSMContext):
    text = (message.text or "").strip()
    if not text:
        await message.answer("❌ Xabar matni bo'sh bo'lishi mumkin emas. Qaytadan kiriting:")
        return
    await state.clear()
    await message.answer("⏳ Xabar yuborilmoqda...")
    ok, fail = await broadcast(lambda _n: f"📢 <b>E'lon:</b>\n\n{esc(text)}")
    await message.answer(_bc_result(ok, fail, "Xabar"), parse_mode="HTML")


# ================= ADMIN: XODIMLAR HOLATI =================
ABS_LABEL = {k: v for k, v in config.ABSENCE_TYPES.items()}


@dp.message(F.text == "👥 Xodimlar")
async def cmd_employee_status(message: types.Message):
    if message.from_user.id not in ADMIN_IDS:
        return
    st = await run_db(repo.employee_status, config.today_str())
    lines = [f"👥 <b>Xodimlar holati</b> (hozir {get_now().strftime('%H:%M')}):\n",
             f"🟢 <b>Ishda ({len(st['ishda'])}):</b>"]
    for x in st["ishda"] or [None]:
        if not x:
            lines.append("  <i>Yo'q</i>")
            break
        late = f" (⚠️ {x['lateness']} daq. kech)" if x["lateness"] else ""
        brk = " ☕" if x["on_break"] else ""
        lines.append(f"  • {esc(x['name'])} — {esc(x['check_in'][:5])} dan{late}{brk}")
    lines.append(f"\n🔴 <b>Ketgan ({len(st['ketgan'])}):</b>")
    for x in st["ketgan"] or [None]:
        if not x:
            lines.append("  <i>Yo'q</i>")
            break
        late = f" (⚠️ {x['lateness']} daq.)" if x["lateness"] else ""
        lines.append(f"  • {esc(x['name'])} — {esc(x['check_in'][:5])} - {esc(x['check_out'][:5])}{late}")
    if st["sababli"]:
        lines.append(f"\n📝 <b>Sababli ({len(st['sababli'])}):</b>")
        for x in st["sababli"]:
            lines.append(f"  • {esc(x['name'])} — {ABS_LABEL.get(x['absence'], x['absence'])}")
    lines.append(f"\n⏳ <b>Kelmagan ({len(st['kelmagan'])}):</b>")
    for x in st["kelmagan"] or [None]:
        if not x:
            lines.append("  <i>Yo'q</i>")
            break
        lines.append(f"  • {esc(x['name'])}")
    await message.answer("\n".join(lines), parse_mode="HTML")

    if st["kelmagan"]:
        btns = [[InlineKeyboardButton(text=f"🔔 {x['name']}", callback_data=f"nudge_{x['user_id']}")]
                for x in st["kelmagan"]]
        btns.append([InlineKeyboardButton(text="🔔 Hammasiga yuborish", callback_data="nudge_all")])
        await message.answer("Kelmagan xodimga eslatma yuborish uchun tanlang:",
                             reply_markup=InlineKeyboardMarkup(inline_keyboard=btns))


NUDGE_TEXT = "🔔 Ishga kelgan bo'lsangiz, belgilashni unutmang!"


@dp.callback_query(F.data.startswith("nudge_"))
async def cb_nudge(callback: types.CallbackQuery):
    if callback.from_user.id not in ADMIN_IDS:
        await callback.answer("Sizda ruxsat yo'q!", show_alert=True)
        return
    target = callback.data.split("_", 1)[1]
    if target == "all":
        st = await run_db(repo.employee_status, config.today_str())
        sent = 0
        for x in st["kelmagan"]:
            sent += await safe_send(x["user_id"], NUDGE_TEXT)
        await callback.answer(f"{sent} ta xodimga eslatma yuborildi.", show_alert=True)
        return
    try:
        ok = await safe_send(int(target), NUDGE_TEXT)
    except ValueError:
        ok = False
    await callback.answer("Eslatma yuborildi." if ok else "Xabar yuborilmadi (xodim botni bloklagan bo'lishi mumkin).",
                          show_alert=True)


# ================= ADMIN BUYRUQLARI =================
@dp.message(Command("panel"))
async def cmd_panel(message: types.Message):
    uid = message.from_user.id
    if uid not in ADMIN_IDS:
        return
    url = auth.webapp_url_for(uid)
    if not url:
        await message.answer("⚠️ WEBAPP_URL sozlanmagan (.env).")
        return
    kb = InlineKeyboardMarkup(inline_keyboard=[[InlineKeyboardButton(text="🖥 Web Panelni ochish",
                                                                     web_app=WebAppInfo(url=url))]])
    await message.answer(f"🖥 Web Panel havolasi {config.WEBAPP_LINK_TTL_HOURS} soat amal qiladi.\n"
                         f"Kompyuterda keng ko'rinishda ishlash uchun: /login",
                         reply_markup=kb)
    await message.answer("Klaviatura yangilandi.", reply_markup=get_kb_for(uid))


@dp.message(Command("login"))
@dp.message(F.text.in_({BTN_ADMIN_PANEL, "🖥 Web Panel"}))
async def cmd_site_login(message: types.Message):
    """"🖥 Admin panel" tugmasi va /login: web saytga bir martalik (10 daqiqalik)
    kirish havolasi. Havolani bosganda brauzerda admin sayt ochiladi."""
    uid = message.from_user.id
    if uid not in ADMIN_IDS:
        return
    if not config.WEBAPP_URL:
        await message.answer("⚠️ Sayt manzili (WEBAPP_URL) sozlanmagan (.env).\n"
                             "Kompyuterda sinayotgan bo'lsangiz, avval sinov_webapp.bat ni ishga tushiring.")
        return
    raw = await run_db(auth.create_login_token, uid)
    url = auth.site_login_url(raw)
    kb = InlineKeyboardMarkup(inline_keyboard=[[InlineKeyboardButton(text="🌐 Admin panelni ochish", url=url)]])
    await message.answer(
        "🖥 <b>Admin panel</b>\n\nPastdagi tugmani bosing - sayt brauzerda ochiladi.\n"
        "Havola <b>10 daqiqa</b> amal qiladi va faqat <b>bir marta</b> ishlaydi. "
        "Brauzer sizni 30 kun eslab qoladi, keyingi safar to'g'ridan-to'g'ri saytga kirasiz.\n\n"
        "⚠️ Havolani hech kimga yubormang. Barcha kirishlarni bekor qilish: /panel_reset",
        parse_mode="HTML", reply_markup=kb,
        link_preview_options=types.LinkPreviewOptions(is_disabled=True))


@dp.message(Command("panel_reset"))
async def cmd_panel_reset(message: types.Message):
    uid = message.from_user.id
    if uid not in ADMIN_IDS:
        return
    await run_db(auth.revoke_all)
    await run_db(repo.audit, uid, "panel_reset", None)
    await message.answer("🔒 Barcha Web Panel havolalari va web sayt sessiyalari bekor qilindi. Yangi havola:",
                         reply_markup=get_kb_for(uid))


@dp.message(Command("backup"))
async def cmd_backup(message: types.Message):
    if message.from_user.id not in ADMIN_IDS:
        return
    path = await run_db(database.backup_db)
    await send_file(message.chat.id, path, os.path.basename(path), "💾 Bazaning joriy zaxira nusxasi")


@dp.message(Command("qongiroq"))
async def cmd_voice_report_now(message: types.Message):
    if message.from_user.id not in ADMIN_IDS:
        return
    if not VOICE_CFG.enabled:
        await message.answer("⚠️ Ovozli hisobot sozlanmagan. .env faylida VAPI_API_KEY, VAPI_ASSISTANT_ID, "
                             "VAPI_PHONE_NUMBER_ID va VOICE_REPORT_PHONE bo'lishi kerak (VOICE_REPORT.md).")
        return
    await message.answer("📞 Qo'ng'iroq qilinmoqda, telefoningizni kuting...")
    asyncio.create_task(send_voice_report())


# ================= REJALASHTIRILGAN ISHLAR =================
async def job_heartbeat():
    """Watchdog bot osilib qolmaganini shu fayl orqali tekshiradi."""
    try:
        with open(config.HEARTBEAT_FILE, "w") as f:
            f.write(str(int(time.time())))
    except OSError as e:
        logger.error(f"Heartbeat yozilmadi: {e}")


async def job_checkin_reminders():
    now = get_now()
    for uid, name, off in await run_db(repo.checkin_reminder_targets, now.strftime("%Y-%m-%d"), now.strftime("%H:%M")):
        await safe_send(uid, f"Xayrli kun, <b>{esc(name)}</b>! ☀️\nIsh vaqti boshlanishiga <b>{off} daqiqa</b> qoldi.\n"
                             f"Ofisga kelgach, <b>🟢 Ishga keldim</b> tugmasini bosishni unutmang!",
                        parse_mode="HTML", reply_markup=get_kb_for(uid))


async def job_checkout_reminders():
    now = get_now()
    for uid, _name, _off in await run_db(repo.checkout_reminder_targets, now.strftime("%Y-%m-%d"), now.strftime("%H:%M")):
        await safe_send(uid, "Ish vaqti yakunlanmoqda - ketishda «🔴 Ishdan ketdim» tugmasini bosishni unutmang.")


async def job_auto_close():
    now = get_now()
    today = now.strftime("%Y-%m-%d")
    for uid, _name in await run_db(repo.auto_close_targets, today, now.strftime("%H:%M")):
        status, info = await run_db(repo.checkout, uid, today, now)
        if status == "ok":
            await safe_send(uid, f"ℹ️ Ish vaqtingiz tugagani uchun tizim sizni bugun soat "
                                 f"<b>{esc(info['check_out'][:5])}</b> da avtomatik ketdi deb belgiladi.",
                            parse_mode="HTML")
            await send_day_alert(uid, today, _name)


async def job_night_close(up_to: str | None = None, since: str | None = None):
    """23:59 - bugun "Ketdim" bosilmagan yozuvlarni yopadi va adminga xabar beradi."""
    up_to = up_to or config.today_str()
    closed = await run_db(repo.close_open_records, up_to, since)
    if not closed:
        return
    for x in closed:
        await safe_send(x["user_id"], f"🌙 {x['date']} kuni «Ishdan ketdim» bosilmagan edi. Tizim ketish vaqtini "
                                      f"<b>{x['check_out'][:5]}</b> deb belgiladi. Noto'g'ri bo'lsa, rahbaringizga ayting.",
                        parse_mode="HTML")
        # Admin uchun - boshqa xodimlar bilan bir xil ko'rinishdagi yakuniy xabar
        await send_day_alert(x["user_id"], x["date"], x["name"], show_date=x["date"] != config.today_str())


async def job_admin_daily_summary():
    data = await run_db(repo.daily_summary, config.today_str())
    st = data["status"]
    lines = [f"📋 <b>Kunlik xulosa</b> ({config.today_str()}):\n",
             f"👥 Jami: <b>{data['total']}</b>", f"✅ Kelganlar: <b>{len(data['kelgan'])}</b>",
             f"⏳ Kelmaganlar: <b>{len(st['kelmagan'])}</b>", f"📝 Sababli: <b>{len(st['sababli'])}</b>",
             f"⚠️ Kech qolganlar: <b>{len(data['late'])}</b>"]
    if st["kelmagan"]:
        lines.append("\n⏳ <b>Kelmaganlar:</b>")
        lines += [f"  • {esc(x['name'])}" for x in st["kelmagan"]]
    if st["sababli"]:
        lines.append("\n📝 <b>Sababli:</b>")
        lines += [f"  • {esc(x['name'])} — {ABS_LABEL.get(x['absence'], '')}" for x in st["sababli"]]
    if data["late"]:
        lines.append("\n⚠️ <b>Kech qolganlar:</b>")
        lines += [f"  • {esc(x['name'])} — {x['lateness']} daqiqa" for x in data["late"]]
    text = "\n".join(lines)
    for admin_id in ADMIN_IDS:
        # reply_markup bilan - admin klaviaturasidagi Web Panel havolasi har kuni yangilanadi
        await safe_send(admin_id, text, parse_mode="HTML", reply_markup=get_kb_for(admin_id))


async def job_daily_excel():
    today = config.today_str()
    path = _tmp_path(f"kunlik_{today}.xlsx")
    try:
        await run_db(reports.generate_daily_excel, today, path)
        for admin_id in ADMIN_IDS:
            await send_file(admin_id, path, f"Kunlik_Hisobot_{today}.xlsx",
                            f"📊 <b>{today}</b> kuni uchun yakuniy davomat statistikasi.")
    except Exception as e:
        logger.error(f"Kunlik hisobot xatosi: {e}")
    finally:
        _remove(path)


async def job_weekly_summary():
    now = get_now()
    start, end = (now - timedelta(days=6)).strftime("%Y-%m-%d"), now.strftime("%Y-%m-%d")
    rows = await run_db(repo.weekly_summary, start, end)
    lines = [f"📅 <b>Haftalik xulosa</b> ({start} — {end}):\n"]
    if not rows:
        lines.append("<i>Xodimlar topilmadi.</i>")
    for r in sorted(rows, key=lambda r: -r["lateness"]):
        lines.append(f"👤 <b>{esc(r['name'])}</b>: {r['days']} kun, {r['hours']:.1f} soat, "
                     f"{r['late_days']} marta kech ({r['lateness']} daq.)")
    await notify_admins("\n".join(lines))


async def job_daily_backup():
    try:
        path = await run_db(database.backup_db)
        removed = await run_db(database.prune_backups)
        logger.info(f"Zaxira nusxa olindi: {path} (eski {removed} ta o'chirildi)")
    except Exception as e:
        logger.error(f"Zaxira nusxa olinmadi: {e}")
        await notify_admins(f"⚠️ Kunlik zaxira nusxa olinmadi: <code>{esc(e)}</code>")


async def job_weekly_backup_send():
    try:
        path = await run_db(database.backup_db, _tmp_path("attendance_backup.db"))
    except Exception as e:
        logger.error(f"Haftalik zaxira xatosi: {e}")
        return
    try:
        stamp = get_now().strftime("%Y-%m-%d_%H%M")
        for admin_id in ADMIN_IDS:
            await send_file(admin_id, path, f"attendance_{stamp}.db", f"💾 <b>Haftalik zaxira nusxa:</b> {stamp}")
    finally:
        _remove(path)


async def job_monthly_report():
    """Har oyning 1-sanasi 00:30 - O'TGAN oy uchun to'liq hisobot (21:00 dan keyingi soatlar ham kiradi)."""
    month = _prev_month(get_now().strftime("%Y-%m"))
    for admin_id in ADMIN_IDS:
        await send_monthly_excel(admin_id, month)


async def job_monthly_payslips():
    """Har oyning 1-sanasi - har bir xodimga o'tgan oy bo'yicha PDF hisob varaqasi."""
    month = _prev_month(get_now().strftime("%Y-%m"))
    for u in await run_db(repo.active_employees):
        await send_payslip(u["user_id"], month)
        await asyncio.sleep(0.2)


async def send_voice_report(kind: str | None = None):
    if not VOICE_CFG.enabled:
        return
    now = get_now()
    today = now.strftime("%Y-%m-%d")
    kind = kind or voice_report.kind_for(now.strftime("%H:%M"))
    data = await run_db(voice_report.collect_day_data, today)
    report = voice_report.build_report(data, kind)
    variables = {"hisobot": report["hisobot"], "tafsilot": report["tafsilot"],
                 "qism": "ertalabki" if kind == "ertalab" else "kechki", "sana": today}

    async def _on_fail(reason):
        await notify_admins(f"📞 <b>Ovozli hisobot yetkazilmadi</b> ({esc(reason)}).\n"
                            f"Matn ko'rinishida ({today}):\n\n{esc(report['matn'])}")

    await asyncio.gather(*[voice_report.call_with_retry(VOICE_CFG, n, variables, on_fail=_on_fail)
                           for n in VOICE_CFG.to_numbers])


# ================= XATOLAR VA ISHGA TUSHISH =================
_last_error_alert = 0.0


@dp.errors()
async def on_error(event: types.ErrorEvent):
    global _last_error_alert
    logger.exception(f"Handler xatosi: {event.exception}", exc_info=event.exception)
    if time.time() - _last_error_alert > 600:  # 10 daqiqada ko'pi bilan bitta xabar
        _last_error_alert = time.time()
        await notify_admins(f"⚠️ Botda xatolik: <code>{esc(type(event.exception).__name__)}: "
                            f"{esc(str(event.exception)[:300])}</code>")
    return True


async def on_startup():
    logger.info(f"Bot ishga tushdi. Adminlar: {len(ADMIN_IDS)}")
    await job_heartbeat()
    # Bot o'chiq paytida (oxirgi 3 kun ichida) ochiq qolgan kunlarni yopamiz.
    # Eskiroq yozuvlarga tegilmaydi - o'tgan oylar hisobi o'zgarib ketmasligi uchun.
    yesterday = (get_now() - timedelta(days=1)).strftime("%Y-%m-%d")
    since = (get_now() - timedelta(days=3)).strftime("%Y-%m-%d")
    await job_night_close(yesterday, since)
    last = float(await run_db(database.get_setting, "last_startup_notice", "0"))
    if time.time() - last > 600:
        await run_db(database.set_setting, "last_startup_notice", str(time.time()))
        await notify_admins(f"♻️ Bot (qayta) ishga tushdi: {get_now().strftime('%Y-%m-%d %H:%M')}")


def build_scheduler() -> AsyncIOScheduler:
    s = AsyncIOScheduler(timezone=config.TZ_NAME, event_loop=asyncio.get_running_loop())
    every_min = dict(trigger="cron", minute="*", misfire_grace_time=30, coalesce=True)
    s.add_job(job_heartbeat, **every_min)
    # Eslatmalar har kuni tekshiriladi - ish kuni ekanligini kalendar hal qiladi
    s.add_job(job_checkin_reminders, **every_min)
    s.add_job(job_checkout_reminders, **every_min)
    s.add_job(job_auto_close, **every_min)
    s.add_job(job_admin_daily_summary, trigger="cron", day_of_week="mon-sat", hour=18, minute=5)
    s.add_job(job_daily_excel, trigger="cron", hour=21, minute=0)
    s.add_job(job_night_close, trigger="cron", hour=23, minute=59)
    s.add_job(job_daily_backup, trigger="cron", hour=23, minute=50)
    s.add_job(job_weekly_summary, trigger="cron", day_of_week="sun", hour=20, minute=0)
    s.add_job(job_weekly_backup_send, trigger="cron", day_of_week="sun", hour=23, minute=55)
    s.add_job(job_monthly_report, trigger="cron", day=1, hour=0, minute=30)
    s.add_job(job_monthly_payslips, trigger="cron", day=1, hour=0, minute=40)
    if VOICE_CFG.enabled:
        for hhmm in VOICE_CFG.times:
            h, m = (int(x) for x in hhmm.split(":"))
            s.add_job(send_voice_report, trigger="cron", day_of_week=VOICE_CFG.days, hour=h, minute=m,
                      kwargs={"kind": voice_report.kind_for(hhmm)})
        logger.info(f"Ovozli hisobot yoqildi: {VOICE_CFG.times} ({VOICE_CFG.days})")
    return s


async def main():
    global bot, scheduler
    setup_logging()
    config.require_bot_config()
    pidfile = os.getenv("KK_PIDFILE")  # lokal sinov: eski nusxani to'xtatish uchun (sinov_bot.bat)
    if pidfile:
        with open(pidfile, "w") as f:
            f.write(str(os.getpid()))
    database.init_db()
    bot = Bot(token=config.BOT_TOKEN)
    scheduler = build_scheduler()
    scheduler.start()
    await on_startup()
    try:
        await dp.start_polling(bot, handle_signals=False)
    finally:
        await bot.session.close()


if __name__ == "__main__":
    try:
        asyncio.run(main())
    except (KeyboardInterrupt, SystemExit):
        logger.info("Bot to'xtatildi.")
    except config.ConfigError as e:
        sys.exit(f"XATOLIK: {e}")
