import logging
import datetime
import re
from io import BytesIO
import openpyxl
from openpyxl.styles import Font, Alignment
from telegram import Update, ReplyKeyboardMarkup, KeyboardButton, ReplyKeyboardRemove
from telegram.ext import (
    Application, CommandHandler, MessageHandler, ContextTypes,
    filters, ConversationHandler, JobQueue
)
from geopy.distance import geodesic
import sqlite3
import pytz

# ====================== CONFIG ======================
BOT_TOKEN = "8532670100:AAFiS-Yg_rD-QXn48Pb76VJG2OBCbYmq7YA"

# Your school coordinates
SCHOOL_CENTER = (41.2572650, 71.5133650)
RADIUS_METERS = 200  # Adjust if needed

# Admin Telegram user IDs - CHANGE THIS!
ADMIN_IDS = [5910341036,1914634678]  # Put your own Telegram user ID here

# Timezone
TIMEZONE = pytz.timezone('Asia/Tashkent')

# States
NAME, PHONE, MAIN, ADMIN_DATE, ADMIN_PERIOD_START, ADMIN_PERIOD_END = range(6)

# ====================== DATABASE ======================
conn = sqlite3.connect("/home/Muhammadiev/schoolat1/keldi_ketti.db", check_same_thread=False)
c = conn.cursor()
c.execute('''CREATE TABLE IF NOT EXISTS users
             (
                 user_id
                 INTEGER
                 PRIMARY
                 KEY,
                 username
                 TEXT,
                 full_name
                 TEXT,
                 phone
                 TEXT
             )''')
c.execute('''CREATE TABLE IF NOT EXISTS attendance
             (
                 id
                 INTEGER
                 PRIMARY
                 KEY
                 AUTOINCREMENT,
                 user_id
                 INTEGER,
                 action
                 TEXT,
                 timestamp
                 TEXT
             )''')
conn.commit()

logging.basicConfig(format='%(asctime)s - %(name)s - %(levelname)s - %(message)s', level=logging.INFO)
logging.getLogger("httpx").setLevel(logging.WARNING)


# ====================== DYNAMIC KEYBOARDS ======================
def get_main_keyboard(user_id: int, is_admin: bool = False):
    today = datetime.date.today().strftime("%Y-%m-%d")

    c.execute("SELECT action FROM attendance WHERE user_id=? AND date(timestamp)=? ORDER BY timestamp DESC LIMIT 1",
              (user_id, today))
    row = c.fetchone()
    last_action = row[0] if row else None

    buttons = []

    if last_action == "ketdi" or last_action is None:
        buttons.append(["📍 Keldim"])
    else:
        buttons.append(["📍 Ketdim"])

    buttons.append(["⚙️ Sozlamalar"])
    if is_admin:
        buttons.append(["🔒 Admin"])

    return ReplyKeyboardMarkup(buttons, resize_keyboard=True)


def settings_keyboard():
    return ReplyKeyboardMarkup([
        ["📜 Tarix", "ℹ️ Ma'lumotlarim"],
        ["🗑️ Akkountni o'chirish"],
        ["⬅️ Orqaga"]
    ], resize_keyboard=True)


def request_phone_keyboard():
    return ReplyKeyboardMarkup([[KeyboardButton("📱 Telefon raqamni yuborish", request_contact=True)]],
                               resize_keyboard=True, one_time_keyboard=True)


def admin_keyboard():
    return ReplyKeyboardMarkup([
        ["📅 Bugun", "🗓️ Bir kun", "📆 Davr (bosh-tug)"],
        ["⬅️ Orqaga"]
    ], resize_keyboard=True)


def location_request_keyboard():
    return ReplyKeyboardMarkup([
        [KeyboardButton("📍 Jonli joylashuv yuborish", request_location=True)],
        ["⬅️ Orqaga"]
    ], resize_keyboard=True, one_time_keyboard=True)


# ====================== HELPERS ======================
def is_user_registered(user_id):
    c.execute("SELECT 1 FROM users WHERE user_id=?", (user_id,))
    return c.fetchone() is not None


def is_admin(user_id):
    return user_id in ADMIN_IDS


def in_school_zone(location):
    return geodesic(SCHOOL_CENTER, (location.latitude, location.longitude)).meters <= RADIUS_METERS


# ====================== HANDLERS ======================
async def start(update: Update, context: ContextTypes.DEFAULT_TYPE):
    user_id = update.effective_user.id

    if is_user_registered(user_id):
        await update.message.reply_text(
            "Xush kelibsiz! 🌟",
            reply_markup=get_main_keyboard(user_id, is_admin=is_admin(user_id))
        )
        return MAIN
    else:
        await update.message.reply_text(
            "Assalomu alaykum! Keldi-Ketti botiga xush kelibsiz. 👋\n\n"
            "Ro'yxatdan o'tish uchun ism va familiyangizni kiriting (masalan: Ahmedov Ali):",
            reply_markup=ReplyKeyboardRemove()
        )
        return NAME


async def get_name(update: Update, context: ContextTypes.DEFAULT_TYPE):
    context.user_data["full_name"] = update.message.text.strip()
    await update.message.reply_text(
        "Rahmat! Endi telefon raqamingizni yuboring:",
        reply_markup=request_phone_keyboard()
    )
    return PHONE


async def get_phone(update: Update, context: ContextTypes.DEFAULT_TYPE):
    contact = update.message.contact
    if not contact:
        await update.message.reply_text("Iltimos, tugma orqali telefon raqamni yuboring!")
        return PHONE

    user = update.effective_user
    full_name = context.user_data["full_name"]
    phone = contact.phone_number

    c.execute("INSERT OR REPLACE INTO users (user_id, username, full_name, phone) VALUES (?, ?, ?, ?)",
              (user.id, user.username, full_name, phone))
    conn.commit()

    await update.message.reply_text(
        f"Ro'yxatdan muvaffaqiyatli o'tdingiz, {full_name}! ✅\n"
        "Endi belgi qo'yishingiz mumkin.",
        reply_markup=get_main_keyboard(user.id, is_admin=is_admin(user.id))
    )
    return MAIN


async def main_menu(update: Update, context: ContextTypes.DEFAULT_TYPE):
    text = update.message.text
    user_id = update.effective_user.id

    if text in ["📍 Keldim", "📍 Ketdim"]:
        await update.message.reply_text(
            "📍 Joylashuvingizni yuboring (GPS yoqilgan bo'lishi kerak):\n\n"
            "Tugmani bosganda quyidagilardan birini tanlang:\n"
            "• 'Jonli joylashuvni ulashish' — tavsiya etiladi (1 daqiqa davomida)\n"
            "• Yoki oddiy joylashuv\n\n"
            "Orqaga qaytish uchun 'Orqaga' ni bosing.",
            reply_markup=location_request_keyboard()
        )
        context.user_data["pending_action"] = text.replace("📍 ", "")
        return MAIN

    elif text == "⚙️ Sozlamalar":
        await update.message.reply_text("Sozlamalar:", reply_markup=settings_keyboard())
        return MAIN

    elif text == "📜 Tarix":
        c.execute("SELECT action, timestamp FROM attendance WHERE user_id=? ORDER BY timestamp DESC LIMIT 20",
                  (user_id,))
        rows = c.fetchall()
        if not rows:
            msg = "Hozircha hech qanday belgi yo'q."
        else:
            msg = "So'nggi 20 ta belgi:\n\n"
            for action, ts in rows:
                emoji = "✅ Keldi" if action == "keldi" else "❌ Ketdi"
                msg += f"{emoji} {ts}\n"
        await update.message.reply_text(msg, reply_markup=settings_keyboard())
        return MAIN

    elif text == "ℹ️ Ma'lumotlarim":
        c.execute("SELECT full_name, phone FROM users WHERE user_id=?", (user_id,))
        row = c.fetchone()
        if row:
            await update.message.reply_text(
                f"Ism-familiya: {row[0]}\nTelefon: {row[1]}",
                reply_markup=settings_keyboard()
            )
        return MAIN

    elif text == "🗑️ Akkountni o'chirish":
        await update.message.reply_text(
            "Haqiqatan ham akkauntni o'chirishni xohlaysizmi?\nBu amalni ortga qaytarib bo'lmaydi!",
            reply_markup=ReplyKeyboardMarkup([["Ha, o'chir", "Yo'q, bekor qil"]], one_time_keyboard=True)
        )
        return MAIN

    elif text == "Ha, o'chir":
        c.execute("DELETE FROM users WHERE user_id=?", (user_id,))
        c.execute("DELETE FROM attendance WHERE user_id=?", (user_id,))
        conn.commit()
        await update.message.reply_text("Barcha ma'lumotlaringiz o'chirildi. Xayr!", reply_markup=ReplyKeyboardRemove())
        return ConversationHandler.END

    elif text in ["Yo'q, bekor qil", "⬅️ Orqaga"]:
        await update.message.reply_text("Bosh menyuga qaytdik.",
                                        reply_markup=get_main_keyboard(user_id, is_admin=is_admin(user_id)))
        return MAIN

    elif text == "🔒 Admin" and is_admin(user_id):
        await update.message.reply_text("Admin panel — Hisobot turi:", reply_markup=admin_keyboard())
        return ADMIN_DATE

    return MAIN


async def location_received(update: Update, context: ContextTypes.DEFAULT_TYPE):
    if not update.message or not update.message.location:
        return MAIN

    user_id = update.effective_user.id
    location = update.message.location
    today = datetime.date.today().strftime("%Y-%m-%d")

    message_time = update.message.date
    now = datetime.datetime.now(tz=datetime.timezone.utc)
    time_diff = (now - message_time.replace(tzinfo=datetime.timezone.utc)).total_seconds()

    if time_diff > 120:
        invalid_msg = await update.message.reply_text(
            "⚠ Joylashuv juda eski (2 daqiqadan ko'p vaqt o'tgan)!\n"
            "Iltimos, yangi jonli joylashuv yuboring.",
            reply_markup=get_main_keyboard(user_id, is_admin=is_admin(user_id))
        )
        context.job_queue.run_once(delete_message, 600, data=(update.message.chat_id, invalid_msg.message_id))
        return MAIN

    if getattr(location, "live_period", None) is None:
        invalid_msg = await update.message.reply_text(
            "❌ Statik yoki saqlangan joylashuv qabul qilinmaydi!\n\n"
            "Iltimos, quyidagi tugmani bosing va jonli (harakatlanuvchi nuqta) joylashuv yuboring:",
            reply_markup=location_request_keyboard()
        )
        context.job_queue.run_once(delete_message, 600, data=(update.message.chat_id, invalid_msg.message_id))
        return MAIN

    if not in_school_zone(location):
        distance = geodesic(SCHOOL_CENTER, (location.latitude, location.longitude)).meters
        invalid_msg = await update.message.reply_text(
            f"📍 Siz maktab hududidan {distance:.0f} metr uzoqdasiz.\n"
            "Faqat maktab atrofida (200 metr ichida) belgilash mumkin!",
            reply_markup=get_main_keyboard(user_id, is_admin=is_admin(user_id))
        )
        context.job_queue.run_once(delete_message, 600, data=(update.message.chat_id, invalid_msg.message_id))
        return MAIN

    action = context.user_data.get("pending_action")
    if not action:
        invalid_msg = await update.message.reply_text(
            "Xatolik: Amal topilmadi. Iltimos, qaytadan \"Keldim\" yoki \"Ketdim\" ni bosing.",
            reply_markup=get_main_keyboard(user_id, is_admin=is_admin(user_id))
        )
        context.job_queue.run_once(delete_message, 600, data=(update.message.chat_id, invalid_msg.message_id))
        return MAIN

    if action == "Keldim":
        c.execute("SELECT 1 FROM attendance WHERE user_id=? AND action='keldi' AND date(timestamp)=?", (user_id, today))
        if c.fetchone():
            invalid_msg = await update.message.reply_text(
                "Siz bugun allaqachon keldingiz!",
                reply_markup=get_main_keyboard(user_id, is_admin=is_admin(user_id))
            )
            context.job_queue.run_once(delete_message, 600, data=(update.message.chat_id, invalid_msg.message_id))
            return MAIN
        db_action = "keldi"
        success_msg = "Keldim"
    else:  # Ketdim
        c.execute("SELECT 1 FROM attendance WHERE user_id=? AND action='keldi' AND date(timestamp)=?", (user_id, today))
        if not c.fetchone():
            invalid_msg = await update.message.reply_text(
                "Bugun hali kelmadingiz! Avval \"Keldim\" ni bosing.",
                reply_markup=get_main_keyboard(user_id, is_admin=is_admin(user_id))
            )
            context.job_queue.run_once(delete_message, 600, data=(update.message.chat_id, invalid_msg.message_id))
            return MAIN
        c.execute("SELECT 1 FROM attendance WHERE user_id=? AND action='ketdi' AND date(timestamp)=?", (user_id, today))
        if c.fetchone():
            invalid_msg = await update.message.reply_text(
                "Siz bugun allaqachon ketdingiz!",
                reply_markup=get_main_keyboard(user_id, is_admin=is_admin(user_id))
            )
            context.job_queue.run_once(delete_message, 600, data=(update.message.chat_id, invalid_msg.message_id))
            return MAIN
        db_action = "ketdi"
        success_msg = "Ketdim"

    timestamp = datetime.datetime.now(tz=TIMEZONE).strftime("%Y-%m-%d %H:%M:%S")
    c.execute("INSERT INTO attendance (user_id, action, timestamp) VALUES (?, ?, ?)", (user_id, db_action, timestamp))
    conn.commit()

    await update.message.reply_text(
        f"✅ {success_msg} muvaffaqiyatli belgilandi!\n"
        f"Vaqt: {timestamp}",
        reply_markup=get_main_keyboard(user_id, is_admin=is_admin(user_id))
    )

    if "pending_action" in context.user_data:
        del context.user_data["pending_action"]

    return MAIN


async def delete_message(context: ContextTypes.DEFAULT_TYPE):
    chat_id, message_id = context.job.data
    await context.bot.delete_message(chat_id=chat_id, message_id=message_id)


async def admin_date(update: Update, context: ContextTypes.DEFAULT_TYPE):
    text = update.message.text.strip()
    user_id = update.effective_user.id

    if text == "⬅️ Orqaga":
        await update.message.reply_text("Bosh menyu", reply_markup=get_main_keyboard(user_id, is_admin=True))
        return MAIN

    if text == "📅 Bugun":
        context.user_data["report_start"] = context.user_data["report_end"] = datetime.date.today().strftime("%Y-%m-%d")
        await generate_and_send_report(update, context)
        return MAIN

    if text == "🗓️ Bir kun":
        await update.message.reply_text("Kunni kiriting (YYYY-MM-DD formatida):")
        return ADMIN_DATE

    if text == "📆 Davr (bosh-tug)":
        await update.message.reply_text("Boshlanish kunini kiriting (YYYY-MM-DD):")
        return ADMIN_PERIOD_START

    if re.match(r'^\d{4}-\d{2}-\d{2}$', text):
        context.user_data["report_start"] = context.user_data["report_end"] = text
        await generate_and_send_report(update, context)
        return MAIN

    await update.message.reply_text("Noto'g'ri format. Qayta urining.")
    return ADMIN_DATE


async def admin_period_start(update: Update, context: ContextTypes.DEFAULT_TYPE):
    text = update.message.text.strip()
    if re.match(r'^\d{4}-\d{2}-\d{2}$', text):
        context.user_data["report_start"] = text
        await update.message.reply_text("Tugash kunini kiriting (YYYY-MM-DD):")
        return ADMIN_PERIOD_END
    else:
        await update.message.reply_text("Noto'g'ri format! YYYY-MM-DD shaklida kiriting.")
        return ADMIN_PERIOD_START


async def admin_period_end(update: Update, context: ContextTypes.DEFAULT_TYPE):
    text = update.message.text.strip()
    if re.match(r'^\d{4}-\d{2}-\d{2}$', text):
        context.user_data["report_end"] = text
        await generate_and_send_report(update, context)
        return MAIN
    else:
        await update.message.reply_text("Noto'g'ri format! Qayta kiriting.")
        return ADMIN_PERIOD_END


async def generate_and_send_report(update: Update, context: ContextTypes.DEFAULT_TYPE):
    start_str = context.user_data["report_start"]
    end_str = context.user_data["report_end"]

    start_date = datetime.datetime.strptime(start_str, "%Y-%m-%d").date()
    end_date = datetime.datetime.strptime(end_str, "%Y-%m-%d").date()

    all_files = []

    current_date = start_date
    while current_date <= end_date:
        if current_date.weekday() >= 5:
            current_date += datetime.timedelta(days=1)
            continue

        day_str = current_date.strftime("%Y-%m-%d")

        query = """
        SELECT u.full_name, u.username,
               MIN(CASE WHEN a.action = 'keldi' THEN a.timestamp END) AS keldi_time,
               MAX(CASE WHEN a.action = 'ketdi' THEN a.timestamp END) AS ketdi_time
        FROM users u
        LEFT JOIN attendance a ON a.user_id = u.user_id AND date(a.timestamp) = ?
        GROUP BY u.user_id
        HAVING keldi_time IS NOT NULL OR ketdi_time IS NOT NULL
        ORDER BY u.full_name
        """
        c.execute(query, (day_str,))
        rows = c.fetchall()

        if not rows:
            current_date += datetime.timedelta(days=1)
            continue

        wb = openpyxl.Workbook()
        ws = wb.active
        ws.title = "Keldi-Ketti"
        ws.append(["№", "F.I.O", "Keldi", "Ketti", "Ish vaqti (soat)", "Username"])

        for cell in ws[1]:
            cell.font = Font(bold=True)
            cell.alignment = Alignment(horizontal="center")

        for i, (name, username, keldi_str, ketdi_str) in enumerate(rows, 1):
            keldi = keldi_str or "-"
            ketdi = ketdi_str or "-"

            work_hours = "-"
            if keldi_str and ketdi_str:
                try:
                    keldi_dt = datetime.datetime.strptime(keldi_str, "%Y-%m-%d %H:%M:%S")
                    ketdi_dt = datetime.datetime.strptime(ketdi_str, "%Y-%m-%d %H:%M:%S")
                    duration = ketdi_dt - keldi_dt
                    hours = duration.total_seconds() / 3600
                    work_hours = f"{hours:.2f}"
                except:
                    work_hours = "Xato"

            ws.append([i, name or "-", keldi, ketdi, work_hours, username or "-"])

        for col in ws.columns:
            max_length = max(len(str(cell.value or "")) for cell in col)
            ws.column_dimensions[col[0].column_letter].width = max_length + 3

        bio = BytesIO()
        wb.save(bio)
        bio.seek(0)

        filename = f"keldi_ketti_{day_str}.xlsx"
        all_files.append((bio, filename, day_str))

        current_date += datetime.timedelta(days=1)

    if not all_files:
        await update.message.reply_text(f"{start_str} — {end_str} oralig'ida ma'lumot yo'q.")
        await update.message.reply_text("Bosh menyu",
                                        reply_markup=get_main_keyboard(update.effective_user.id, is_admin=True))
        return

    await update.message.reply_text(f"{start_str} — {end_str} oralig'idagi hisobotlar:")

    for bio, filename, day_str in all_files:
        await update.message.reply_document(
            document=bio,
            filename=filename,
            caption=f"{day_str} kunlik hisobot"
        )
        bio.close()

    await update.message.reply_text("Hisobotlar yuborildi!",
                                    reply_markup=get_main_keyboard(update.effective_user.id, is_admin=True))


def main():
    app = Application.builder().token(BOT_TOKEN).build()

    conv_handler = ConversationHandler(
        entry_points=[CommandHandler("start", start)],
        states={
            NAME: [MessageHandler(filters.TEXT & ~filters.COMMAND, get_name)],
            PHONE: [MessageHandler(filters.CONTACT, get_phone)],
            MAIN: [
                MessageHandler(filters.TEXT & ~filters.COMMAND, main_menu),
                MessageHandler(filters.LOCATION, location_received),
            ],
            ADMIN_DATE: [MessageHandler(filters.TEXT & ~filters.COMMAND, admin_date)],
            ADMIN_PERIOD_START: [MessageHandler(filters.TEXT & ~filters.COMMAND, admin_period_start)],
            ADMIN_PERIOD_END: [MessageHandler(filters.TEXT & ~filters.COMMAND, admin_period_end)],
        },
        fallbacks=[CommandHandler("cancel", lambda update, context: ConversationHandler.END)],
    )

    app.add_handler(conv_handler)

    print("Keldi-Ketti bot ishga tushdi! Anti-cheat himoyasi faol.")
    app.run_polling()


if __name__ == '__main__':
    main()