import logging
import os
import sqlite3
from aiogram import Router, F
from aiogram.types import (
    CallbackQuery, InlineKeyboardMarkup, InlineKeyboardButton, Message
)
from aiogram.types.input_file import FSInputFile
from aiogram.fsm.state import StatesGroup, State
from aiogram.fsm.context import FSMContext
from db import get_user_language, get_language_text, set_user_language, MENU_DB, DATA_DB

router = Router(name="catalog")
logger = logging.getLogger(__name__)

BACK_CB = "menu:button9"
CITY_CB_PREFIX = "city:"
PROD_CB_PREFIX = "prod:"
TAG_CB_PREFIX = "tag:"
DISTRICT_CB_PREFIX = "district:"
DELIVERY_CB_PREFIX = "delivery:"

class CatalogStates(StatesGroup):
    wait_city = State()

def get_button9_text(lang: str) -> str:
    label = "⬅️ Назад"
    try:
        conn = sqlite3.connect(MENU_DB)
        cur = conn.cursor()
        cur.execute("SELECT button9 FROM language WHERE name = ?", (lang,))
        row = cur.fetchone()
        conn.close()
        if row and row[0] and str(row[0]).strip():
            label = str(row[0]).strip()
    except Exception as e:
        logger.error(f"get_button9_text error for lang={lang}: {e}")
    return label

def get_button11_text(lang: str) -> str:
    label = "🚗 Доставка"
    try:
        conn = sqlite3.connect(MENU_DB)
        cur = conn.cursor()
        cur.execute("SELECT button11 FROM language WHERE name = ?", (lang,))
        row = cur.fetchone()
        conn.close()
        if row and row[0] and str(row[0]).strip():
            label = str(row[0]).strip()
    except Exception as e:
        logger.error(f"get_button11_text error for lang={lang}: {e}")
    return label

def build_back_keyboard(lang: str) -> InlineKeyboardMarkup:
    return InlineKeyboardMarkup(
        inline_keyboard=[[InlineKeyboardButton(text=get_button9_text(lang), callback_data=BACK_CB)]]
    )

def load_all_cities() -> list[tuple[int, str]]:
    """Загружает все города из базы данных"""
    try:
        conn = sqlite3.connect(DATA_DB)
        cur = conn.cursor()
        cur.execute("SELECT city_id, city_name FROM city")
        rows = cur.fetchall()
        conn.close()
        return [(int(r[0]), str(r[1]).strip()) for r in rows if r and r[0] is not None and r[1]]
    except Exception as e:
        logger.error(f"load_all_cities error: {e}")
        return []

def find_cities_by_prefix(prefix: str) -> list[tuple[int, str]]:
    """Находит города по префиксу (поиск)"""
    q = (prefix or "").strip().lower()
    if not q:
        return []
    cities = load_all_cities()
    matches = [(cid, name) for cid, name in cities if name.lower().startswith(q)]
    return sorted(matches, key=lambda x: x[1].lower())

def get_city_language(city_id: int) -> str | None:
    """Получает язык для указанного города"""
    try:
        conn = sqlite3.connect(DATA_DB)
        cur = conn.cursor()
        
        # Проверяем есть ли колонка city_language в таблице city
        cur.execute("PRAGMA table_info(city)")
        columns = [col[1] for col in cur.fetchall()]
        
        if 'city_language' in columns:
            cur.execute("SELECT city_language FROM city WHERE city_id = ?", (city_id,))
            row = cur.fetchone()
            conn.close()
            if row and row[0]:
                return str(row[0]).strip()
        
        conn.close()
        return None
        
    except Exception as e:
        logger.error(f"get_city_language error for city_id={city_id}: {e}")
        return None

def get_city_name(city_id: int) -> str | None:
    """Получает название города по ID"""
    try:
        conn = sqlite3.connect(DATA_DB)
        cur = conn.cursor()
        cur.execute("SELECT city_name FROM city WHERE city_id = ?", (city_id,))
        row = cur.fetchone()
        conn.close()
        return str(row[0]).strip() if row and row[0] else None
    except Exception as e:
        logger.error(f"get_city_name error: {e}")
        return None

def get_city_currency(city_id: int) -> tuple[str, float] | None:
    """Получает валюту города и курс"""
    try:
        conn = sqlite3.connect(DATA_DB)
        cur = conn.cursor()
        
        # Получаем валюту города
        cur.execute("SELECT valyuta FROM city WHERE city_id = ?", (city_id,))
        row = cur.fetchone()
        
        if not row or not row[0]:
            conn.close()
            return None
        
        currency = str(row[0]).strip().lower()
        
        # Получаем курс валюты из таблицы kurs
        cur.execute(f"SELECT {currency} FROM kurs LIMIT 1")
        kurs_row = cur.fetchone()
        conn.close()
        
        if kurs_row and kurs_row[0]:
            try:
                exchange_rate = float(kurs_row[0])
                return currency.upper(), exchange_rate
            except ValueError:
                return None
        
        return None
        
    except Exception as e:
        logger.error(f"get_city_currency error for city_id={city_id}: {e}")
        return None

def get_city_delivery_status(city_id: int) -> bool:
    """Проверяет доступна ли доставка для города"""
    try:
        conn = sqlite3.connect(DATA_DB)
        cur = conn.cursor()
        
        cur.execute("SELECT delivery FROM city WHERE city_id = ?", (city_id,))
        row = cur.fetchone()
        conn.close()
        
        if row and row[0]:
            delivery_status = str(row[0]).strip().lower()
            return delivery_status == 'yes'
        
        return False
        
    except Exception as e:
        logger.error(f"get_city_delivery_status error for city_id={city_id}: {e}")
        return False

def get_city_districts(city_id: int) -> list[str]:
    """Получает список районов для города"""
    try:
        conn = sqlite3.connect(DATA_DB)
        cur = conn.cursor()
        
        # Проверяем существует ли таблица city_districts
        cur.execute("""
            SELECT name FROM sqlite_master 
            WHERE type='table' AND name='city_districts'
        """)
        if not cur.fetchone():
            conn.close()
            return []
        
        cur.execute("SELECT district_name FROM city_districts WHERE city_id = ? ORDER BY district_name", (city_id,))
        rows = cur.fetchall()
        conn.close()
        
        districts = []
        for row in rows:
            if row and row[0]:
                district_name = str(row[0]).strip()
                if district_name:
                    districts.append(district_name)
        
        return districts
        
    except Exception as e:
        logger.error(f"get_city_districts error for city_id={city_id}: {e}")
        return []

def find_products_for_city(city_id: int) -> list[tuple[int, str]]:
    """
    Находит товары для указанного города
    Возвращает список кортежей: (product_id, product_name)
    """
    try:
        conn = sqlite3.connect(DATA_DB)
        cur = conn.cursor()
        
        # Получаем все товары
        cur.execute("SELECT product_id, product_name FROM products ORDER BY product_name")
        rows = cur.fetchall()
        conn.close()
        
        products = []
        for row in rows:
            if row and row[0] is not None and row[1]:
                product_id = int(row[0])
                product_name = str(row[1]).strip()
                products.append((product_id, product_name))
        
        logger.info(f"Found {len(products)} products total")
        return products
        
    except Exception as e:
        logger.error(f"find_products_for_city error: {e}")
        return []

def build_cities_keyboard(lang: str, cities: list[tuple[int, str]]) -> InlineKeyboardMarkup:
    """Строит клавиатуру с городами"""
    rows: list[list[InlineKeyboardButton]] = []
    for cid, name in cities:
        rows.append([InlineKeyboardButton(text=name, callback_data=f"{CITY_CB_PREFIX}{cid}")])
    rows.append([InlineKeyboardButton(text=get_button9_text(lang), callback_data=BACK_CB)])
    return InlineKeyboardMarkup(inline_keyboard=rows)

def build_products_keyboard(lang: str, products: list[tuple[int, str]], city_id: int) -> InlineKeyboardMarkup:
    """Строит клавиатуру с товарами"""
    rows: list[list[InlineKeyboardButton]] = []
    
    for product_id, product_name in products:
        # Обрезаем длинное название для кнопки
        button_text = product_name
        if len(button_text) > 30:
            button_text = button_text[:27] + "..."
        
        rows.append([InlineKeyboardButton(
            text=button_text, 
            callback_data=f"{PROD_CB_PREFIX}{product_id}:{city_id}"
        )])
    
    rows.append([InlineKeyboardButton(text=get_button9_text(lang), callback_data=BACK_CB)])
    return InlineKeyboardMarkup(inline_keyboard=rows)

def get_product_details(product_id: int) -> dict | None:
    """Получает детальную информацию о товаре"""
    try:
        conn = sqlite3.connect(DATA_DB)
        cur = conn.cursor()
        
        cur.execute("""
        SELECT product_id, product_name, product_description, product_image 
        FROM products WHERE product_id = ?
        """, (product_id,))
        row = cur.fetchone()
        conn.close()
        
        if not row:
            return None
        
        return {
            "product_id": int(row[0]),
            "product_name": str(row[1]).strip() if row[1] else "Без названия",
            "product_description": str(row[2]).strip() if row[2] else "",
            "product_image": str(row[3]).strip() if row[3] else ""
        }
        
    except Exception as e:
        logger.error(f"get_product_details error id={product_id}: {e}")
        return None

def get_product_tags(product_id: int, city_id: int) -> list[tuple[str, float, str, int]]:
    """Получает теги товара с ценами в валюте города"""
    tags = []
    try:
        conn = sqlite3.connect(DATA_DB)
        cur = conn.cursor()
        
        # Получаем валюту и курс города
        currency_info = get_city_currency(city_id)
        if not currency_info:
            return tags
        
        currency, exchange_rate = currency_info
        
        # Проверяем доступные колонки тегов
        cur.execute("PRAGMA table_info(products)")
        columns = [col[1] for col in cur.fetchall()]
        
        # Ищем колонки product_tags1 до product_tags50
        tag_columns = []
        for i in range(1, 51):
            col_name = f"product_tags{i}"
            if col_name in columns:
                tag_columns.append((col_name, i))
        
        # Сортируем по номеру тега
        tag_columns.sort(key=lambda x: x[1])
        
        for tag_col, tag_index in tag_columns:
            cur.execute(f"SELECT {tag_col} FROM products WHERE product_id = ?", (product_id,))
            row = cur.fetchone()
            
            if row and row[0]:
                tag_data = str(row[0]).strip()
                if tag_data:
                    # Разбираем данные тега: "100 г,100"
                    parts = [p.strip() for p in tag_data.split(',')]
                    if len(parts) >= 2:
                        tag_name = parts[0]  # "100 г"
                        try:
                            base_price = float(parts[1])  # 100
                            # Конвертируем цену по курсу
                            final_price = base_price * exchange_rate
                            tags.append((tag_name, final_price, currency, tag_index))
                        except ValueError:
                            continue
        
        conn.close()
        return tags
        
    except Exception as e:
        logger.error(f"get_product_tags error product_id={product_id}, city_id={city_id}: {e}")
        return []

def build_product_tags_keyboard(lang: str, tags: list[tuple[str, float, str, int]], product_id: int, city_id: int) -> InlineKeyboardMarkup:
    """Строит клавиатуру с тегами товара"""
    rows: list[list[InlineKeyboardButton]] = []
    
    for tag_name, price, currency, tag_index in tags:
        # Форматируем цену: "100 г - 83 RUB"
        if price.is_integer():
            price_str = f"{int(price)}"
        else:
            price_str = f"{price:.2f}"
        
        button_text = f"{tag_name} - {price_str} {currency}"
        
        # Создаем callback_data в формате tag:product_id:city_id:tag_index:tag_name
        rows.append([InlineKeyboardButton(
            text=button_text,
            callback_data=f"{TAG_CB_PREFIX}{product_id}:{city_id}:{tag_index}:{tag_name}"
        )])
    
    # Добавляем кнопку "Назад"
    rows.append([InlineKeyboardButton(text=get_button9_text(lang), callback_data=BACK_CB)])
    
    return InlineKeyboardMarkup(inline_keyboard=rows)

def build_districts_keyboard(lang: str, districts: list[str], product_id: int, city_id: int, tag_index: int, tag_name: str) -> InlineKeyboardMarkup:
    """Строит клавиатуру с районами города"""
    rows: list[list[InlineKeyboardButton]] = []
    
    # Добавляем кнопку доставки если доступна
    delivery_available = get_city_delivery_status(city_id)
    if delivery_available:
        button11_text = get_button11_text(lang)
        rows.append([InlineKeyboardButton(
            text=button11_text,
            callback_data=f"{DELIVERY_CB_PREFIX}{product_id}:{city_id}:{tag_index}:{tag_name}:delivery"
        )])
    
    # Добавляем кнопки районов
    for district in districts:
        rows.append([InlineKeyboardButton(
            text=district,
            callback_data=f"{DISTRICT_CB_PREFIX}{product_id}:{city_id}:{tag_index}:{tag_name}:{district}"
        )])
    
    # Добавляем кнопку "Назад" к выбору тега
    back_to_tags_cb = f"{PROD_CB_PREFIX}{product_id}:{city_id}"
    rows.append([InlineKeyboardButton(text=get_button9_text(lang), callback_data=back_to_tags_cb)])
    
    return InlineKeyboardMarkup(inline_keyboard=rows)

def resolve_image_path(raw_path: str) -> tuple[bool, str | None]:
    """Определяет путь к изображению (локальный или URL)"""
    if not raw_path:
        return False, None
    
    p = raw_path.strip()
    
    # Проверяем URL
    if p.lower().startswith(("http://", "https://")):
        return True, p
    
    # Проверяем локальные пути
    candidates = [
        p,
        f"./{p}",
        f"../{p}",
        f"upload/{p}",
        f"./upload/{p}",
        f"../upload/{p}"
    ]
    
    for candidate in candidates:
        norm_path = os.path.normpath(candidate)
        if os.path.exists(norm_path) and os.path.isfile(norm_path):
            return False, norm_path
    
    return False, None

@router.callback_query(F.data == "menu:button1")
async def open_catalog(cb: CallbackQuery, state: FSMContext):
    """Обработчик открытия каталога - запрашивает город"""
    user_id = cb.from_user.id
    lang = get_user_language(user_id)
    text2 = get_language_text(lang, "text2") or "Введите название города"
    
    try:
        await cb.message.delete()
    except Exception:
        pass
    
    prompt_msg = await cb.message.answer(text2, reply_markup=build_back_keyboard(lang))
    await state.set_state(CatalogStates.wait_city)
    await state.update_data(prompt_msg_id=prompt_msg.message_id)
    await cb.answer()

@router.message(CatalogStates.wait_city)
async def handle_city_input(message: Message, state: FSMContext):
    """Обработчик ввода города пользователем"""
    user_id = message.from_user.id
    lang = get_user_language(user_id)
    user_text = (message.text or "").strip()
    
    try:
        await message.delete()
    except Exception:
        pass

    try:
        data = await state.get_data()
        prompt_id = data.get("prompt_msg_id")
        if prompt_id:
            await message.bot.delete_message(chat_id=message.chat.id, message_id=prompt_id)
    except Exception:
        pass

    matches = find_cities_by_prefix(user_text)
    if matches:
        text4 = get_language_text(lang, "text4") or "Найдены города"
        kb = build_cities_keyboard(lang, matches)
        await message.answer(text4, reply_markup=kb)
        await state.clear()
        return
    
    text3 = get_language_text(lang, "text3") or "Город не найден. Попробуйте снова."
    await message.answer(text3, reply_markup=build_back_keyboard(lang))

@router.callback_query(F.data.startswith(CITY_CB_PREFIX))
async def on_city_clicked(cb: CallbackQuery):
    """Обработчик выбора города - показывает товары для этого города и меняет язык"""
    user_id = cb.from_user.id
    current_lang = get_user_language(user_id)
    
    try:
        city_id = int(cb.data[len(CITY_CB_PREFIX):])
    except ValueError:
        await cb.answer("Ошибка выбора города", show_alert=True)
        return
    
    # Получаем язык для выбранного города
    city_language = get_city_language(city_id)
    
    # Если у города есть свой язык, сохраняем его для пользователя
    if city_language and city_language != current_lang:
        success = set_user_language(user_id, city_language)
        if success:
            logger.info(f"Changed language for user {user_id} to {city_language} based on city {city_id}")
        else:
            logger.error(f"Failed to change language for user {user_id}")
    
    # Теперь используем актуальный язык (новый или старый)
    lang = city_language or current_lang or "Русский"
    
    try:
        await cb.message.delete()
    except Exception:
        pass

    # Получаем товары
    products = find_products_for_city(city_id)
    
    if products:
        city_name = get_city_name(city_id) or "неизвестный город"
        text5 = get_language_text(lang, "text5") or f"Товары в городе {city_name}:"
        
        await cb.message.answer(text5, reply_markup=build_products_keyboard(lang, products, city_id))
    else:
        text6 = get_language_text(lang, "text6") or "Товары не найдены"
        await cb.message.answer(text6, reply_markup=build_back_keyboard(lang))
    
    await cb.answer()

@router.callback_query(F.data.startswith(PROD_CB_PREFIX))
async def on_product_clicked(cb: CallbackQuery):
    """Обработчик выбора товара - показывает детальную информацию с картинкой и тегами"""
    user_id = cb.from_user.id
    lang = get_user_language(user_id)
    
    payload = cb.data[len(PROD_CB_PREFIX):]
    try:
        pid_str, cid_str = payload.split(":", 1)
        product_id = int(pid_str)
        city_id = int(cid_str)
    except Exception:
        await cb.answer("Ошибка выбора товара", show_alert=True)
        return
    
    # Получаем детальную информацию о товаре
    product = get_product_details(product_id)
    city_name = get_city_name(city_id)
    
    if not product:
        await cb.answer("Товар не найден", show_alert=True)
        return
    
    # Формируем описание товара
    city_line = f"🏙️ Город: {city_name or 'Не указан'}"
    name_line = f"📦 Товар: {product.get('product_name', 'Без названия')}"
    
    description = product.get('product_description', '')
    desc_line = f"📝 {description}" if description else ""
    
    # Добавляем text6 под описанием
    text6_content = get_language_text(lang, "text6") or ""
    text6_line = f"\n{text6_content}" if text6_content else ""
    
    # Собираем все части сообщения
    message_parts = [city_line, name_line]
    if desc_line:
        message_parts.append(desc_line)
    if text6_line:
        message_parts.append(text6_line)
    
    caption_text = "\n".join(message_parts)
    
    # Получаем теги товара с ценами
    tags = get_product_tags(product_id, city_id)
    
    # Строим клавиатуру с тегами
    kb = build_product_tags_keyboard(lang, tags, product_id, city_id)
    
    try:
        await cb.message.delete()
    except Exception:
        pass
    
    # Проверяем и выводим картинку если есть
    image_path = product.get('product_image', '')
    if image_path:
        is_url, resolved_path = resolve_image_path(image_path)
        
        if resolved_path:
            try:
                if is_url:
                    # Для URL
                    await cb.message.answer_photo(
                        photo=resolved_path, 
                        caption=caption_text, 
                        reply_markup=kb
                    )
                else:
                    # Для локальных файлов
                    await cb.message.answer_photo(
                        photo=FSInputFile(resolved_path), 
                        caption=caption_text, 
                        reply_markup=kb
                    )
                logger.info(f"Successfully sent photo for product {product_id}: {resolved_path}")
            except Exception as e:
                logger.error(f"Failed to send photo for product {product_id}: {e}")
                # Если не удалось отправить фото, отправляем текст
                await cb.message.answer(caption_text, reply_markup=kb)
        else:
            # Если путь не найден, отправляем текст
            await cb.message.answer(caption_text, reply_markup=kb)
    else:
        # Если нет картинки, отправляем текст
        await cb.message.answer(caption_text, reply_markup=kb)
    
    await cb.answer()

@router.callback_query(F.data.startswith(TAG_CB_PREFIX))
async def on_tag_clicked(cb: CallbackQuery):
    """Обработчик выбора тега товара - показывает районы"""
    user_id = cb.from_user.id
    lang = get_user_language(user_id)
    
    payload = cb.data[len(TAG_CB_PREFIX):]
    try:
        pid_str, cid_str, tag_index_str, tag_name = payload.split(":", 3)
        product_id = int(pid_str)
        city_id = int(cid_str)
        tag_index = int(tag_index_str)
    except Exception:
        await cb.answer("Ошибка выбора варианта", show_alert=True)
        return
    
    try:
        await cb.message.delete()
    except Exception:
        pass
    
    # Получаем текст text7
    text7_content = get_language_text(lang, "text7") or "Выберите район доставки:"
    
    # Получаем районы города
    districts = get_city_districts(city_id)
    
    # Строим клавиатуру с районами
    kb = build_districts_keyboard(lang, districts, product_id, city_id, tag_index, tag_name)
    
    await cb.message.answer(text7_content, reply_markup=kb)
    await cb.answer()

@router.callback_query(F.data.startswith(DELIVERY_CB_PREFIX))
async def on_delivery_clicked(cb: CallbackQuery):
    """Обработчик выбора доставки"""
    user_id = cb.from_user.id
    lang = get_user_language(user_id)
    
    payload = cb.data[len(DELIVERY_CB_PREFIX):]
    try:
        pid_str, cid_str, tag_index_str, tag_name, delivery_type = payload.split(":", 4)
        product_id = int(pid_str)
        city_id = int(cid_str)
        tag_index = int(tag_index_str)
    except Exception:
        await cb.answer("Ошибка выбора доставки", show_alert=True)
        return
    
    # TODO: Здесь будет логика обработки выбора доставки
    await cb.answer(f"Выбрана доставка: {delivery_type}", show_alert=True)

@router.callback_query(F.data.startswith(DISTRICT_CB_PREFIX))
async def on_district_clicked(cb: CallbackQuery):
    """Обработчик выбора района"""
    user_id = cb.from_user.id
    lang = get_user_language(user_id)
    
    payload = cb.data[len(DISTRICT_CB_PREFIX):]
    try:
        pid_str, cid_str, tag_index_str, tag_name, district = payload.split(":", 4)
        product_id = int(pid_str)
        city_id = int(cid_str)
        tag_index = int(tag_index_str)
    except Exception:
        await cb.answer("Ошибка выбора района", show_alert=True)
        return
    
    # TODO: Здесь будет логика обработки выбора района
    await cb.answer(f"Выбран район: {district}", show_alert=True)