import logging
import sqlite3
import json
from aiogram import Router, F
from aiogram.types import CallbackQuery, InlineKeyboardMarkup, InlineKeyboardButton
from aiogram.fsm.context import FSMContext
from db import get_user_language, get_language_text, get_user_balance, DATA_DB, MENU_DB

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

TAG_CB_PREFIX = "tag:"
DISTRICT_CB_PREFIX = "district:"
DELIVERY_CB_PREFIX = "delivery:"
DELIVERY_INFO_CB_PREFIX = "delivery_info:"
BACK_TO_PRODUCT_PREFIX = "back_to_product:"

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 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_delivery_options(city_id: int) -> list[tuple[str, str]]:
    """Получает опции доставки для указанного города"""
    try:
        conn = sqlite3.connect(DATA_DB)
        cur = conn.cursor()
        
        cur.execute("""
            SELECT name FROM sqlite_master 
            WHERE type='table' AND name='delivery'
        """)
        if not cur.fetchone():
            conn.close()
            return []
        
        # Получаем все записи доставки
        cur.execute("SELECT city_id, name, text FROM delivery")
        rows = cur.fetchall()
        conn.close()
        
        options = []
        for row in rows:
            if row and row[0] and row[1] and row[2]:
                delivery_city_ids = str(row[0]).strip().split(',')
                # Проверяем, подходит ли доставка для выбранного города
                if str(city_id) in delivery_city_ids:
                    name = str(row[1]).strip()
                    text = str(row[2]).strip()
                    if name and text:
                        options.append((name, text))
        
        return options
        
    except Exception as e:
        logger.error(f"get_city_delivery_options error for city_id={city_id}: {e}")
        return []

def get_city_districts(city_id: int, product_id: int, tag_index: int) -> list[str]:
    """Получает список районов для города с использованием маппинга"""
    try:
        conn = sqlite3.connect(DATA_DB)
        cur = conn.cursor()
        
        # Сначала пробуем получить районы из колонки маппинга
        district_column = f"product_districts{tag_index}"
        
        # Проверяем существует ли колонка маппинга
        cur.execute("PRAGMA table_info(products)")
        columns = [col[1] for col in cur.fetchall()]
        
        if district_column in columns:
            # Получаем маппинг районов
            cur.execute(f"SELECT {district_column} FROM products WHERE product_id = ?", (product_id,))
            row = cur.fetchone()
            
            if row and row[0]:
                try:
                    district_mapping = json.loads(row[0])
                    # Возвращаем районы для конкретного города
                    districts = district_mapping.get(str(city_id), [])
                    if districts:
                        conn.close()
                        return districts
                except json.JSONDecodeError:
                    # Если JSON невалидный, продолжаем со старым методом
                    pass
        
        # Если маппинга нет или он пустой, используем старый метод для обратной совместимости
        tag_column = f"product_tags{tag_index}"
        
        if tag_column not in columns:
            conn.close()
            return []
        
        # Получаем данные тега
        cur.execute(f"SELECT {tag_column} FROM products WHERE product_id = ?", (product_id,))
        row = cur.fetchone()
        
        if not row or not row[0]:
            conn.close()
            return []
        
        tag_data = str(row[0]).strip()
        if not tag_data:
            conn.close()
            return []
        
        # Разбираем старый формат: граммовка,цена,район1-район2-район3
        parts = tag_data.split(',')
        if len(parts) >= 3:
            # Третья часть содержит районы через дефис
            districts_str = parts[2].strip()
            if districts_str:
                all_districts = [d.strip() for d in districts_str.split('-') if d.strip()]
                
                # Фильтруем районы по принадлежности к городу (используем таблицу districts)
                filtered_districts = []
                for district in all_districts:
                    if is_district_in_city(district, city_id):
                        filtered_districts.append(district)
                
                conn.close()
                return filtered_districts
        
        conn.close()
        return []
        
    except Exception as e:
        logger.error(f"get_city_districts error for city_id={city_id}, product_id={product_id}, tag_index={tag_index}: {e}")
        return []

def is_district_in_city(district: str, city_id: int) -> bool:
    """Проверяет принадлежит ли район городу (для обратной совместимости)"""
    try:
        conn = sqlite3.connect(DATA_DB)
        cur = conn.cursor()
        
        # Ищем район в таблице districts
        cur.execute("SELECT * FROM districts")
        rows = cur.fetchall()
        conn.close()
        
        for row in rows:
            if not row:
                continue
                
            # Проверяем city_ids
            city_ids_str = str(row[1]) if len(row) > 1 else ''  # column index 1 - city_ids
            if city_ids_str:
                city_ids = [cid.strip() for cid in city_ids_str.split(',')]
                if str(city_id) in city_ids:
                    # Проверяем все district колонки
                    for i in range(2, 12):  # district1 to district10
                        if len(row) > i and row[i] and str(row[i]).strip() == district:
                            return True
        
        return False
        
    except Exception as e:
        logger.error(f"is_district_in_city error for district={district}, city_id={city_id}: {e}")
        return False

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()
        
        from handlers.payment_handlers import get_city_currency
        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()]
        
        for i in range(1, 51):
            tag_col = f"product_tags{i}"
            if tag_col in 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:
                        parts = [p.strip() for p in tag_data.split(',')]
                        if len(parts) >= 2:
                            tag_name = parts[0]
                            try:
                                base_price = float(parts[1])
                                final_price = base_price * exchange_rate
                                tags.append((tag_name, final_price, currency, i))
                            except ValueError:
                                continue
        
        conn.close()
        
        # СОРТИРУЕМ ТЕГИ ПО ЦЕНЕ ОТ МЕНЬШЕЙ К БОЛЬШЕЙ
        tags.sort(key=lambda x: x[1])
        
        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:
        if price.is_integer():
            price_str = f"{int(price)}"
        else:
            price_str = f"{price:.2f}"
        
        button_text = f"{tag_name} - {price_str} {currency}"
        
        rows.append([InlineKeyboardButton(
            text=button_text,
            callback_data=f"{TAG_CB_PREFIX}{product_id}:{city_id}:{tag_index}:{tag_name}"
        )])
    
    # Кнопка назад к карточке товара
    back_to_product_cb = f"{BACK_TO_PRODUCT_PREFIX}{product_id}:{city_id}"
    rows.append([InlineKeyboardButton(text=get_button9_text(lang), callback_data=back_to_product_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_product_cb = f"{BACK_TO_PRODUCT_PREFIX}{product_id}:{city_id}"
    rows.append([InlineKeyboardButton(text=get_button9_text(lang), callback_data=back_to_product_cb)])
    
    return InlineKeyboardMarkup(inline_keyboard=rows)

def build_delivery_info_keyboard(lang: str, product_id: int, city_id: int, tag_index: int, tag_name: str) -> InlineKeyboardMarkup:
    """Строит клавиатуру с опциями доставки"""
    rows: list[list[InlineKeyboardButton]] = []
    
    delivery_options = get_city_delivery_options(city_id)
    for option_name, option_text in delivery_options:
        rows.append([InlineKeyboardButton(
            text=option_name,
            callback_data=f"{DELIVERY_INFO_CB_PREFIX}{product_id}:{city_id}:{tag_index}:{tag_name}:{option_name}"
        )])
    
    # Возврат к карточке товара
    back_to_product_cb = f"{BACK_TO_PRODUCT_PREFIX}{product_id}:{city_id}"
    rows.append([InlineKeyboardButton(text=get_button9_text(lang), callback_data=back_to_product_cb)])
    
    return InlineKeyboardMarkup(inline_keyboard=rows)

@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_content = get_language_text(lang, "text7") or "Выберите район доставки:"
    
    # Получаем районы для конкретного города и граммовки
    districts = get_city_districts(city_id, product_id, tag_index)
    
    if not districts:
        # Если районы не найдены для этой граммовки и города, показываем сообщение
        error_text = "Для выбранного варианта в вашем городе районы не настроены"
        back_to_product_cb = f"{BACK_TO_PRODUCT_PREFIX}{product_id}:{city_id}"
        kb = InlineKeyboardMarkup(
            inline_keyboard=[[
                InlineKeyboardButton(text=get_button9_text(lang), callback_data=back_to_product_cb)
            ]]
        )
        await cb.message.answer(error_text, reply_markup=kb)
        await cb.answer()
        return
    
    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
    
    try:
        await cb.message.delete()
    except Exception:
        pass
    
    text33_content = get_language_text(lang, "text33") or "Выберите способ доставки:"
    
    kb = build_delivery_info_keyboard(lang, product_id, city_id, tag_index, tag_name)
    
    await cb.message.answer(text33_content, reply_markup=kb)
    await cb.answer()

@router.callback_query(F.data.startswith(DELIVERY_INFO_CB_PREFIX))
async def on_delivery_info_clicked(cb: CallbackQuery):
    """Обработчик выбора опции доставки - показывает текст доставки в сообщении"""
    user_id = cb.from_user.id
    lang = get_user_language(user_id)
    
    payload = cb.data[len(DELIVERY_INFO_CB_PREFIX):]
    try:
        pid_str, cid_str, tag_index_str, tag_name, option_name = 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
    
    try:
        await cb.message.delete()
    except Exception:
        pass
    
    delivery_options = get_city_delivery_options(city_id)
    option_text = ""
    for name, text in delivery_options:
        if name == option_name:
            option_text = text
            break
    
    if not option_text:
        option_text = "Информация о доставке не найдена"
    
    # Возврат к опциям доставки
    back_to_delivery_cb = f"{DELIVERY_CB_PREFIX}{product_id}:{city_id}:{tag_index}:{tag_name}:delivery"
    kb = InlineKeyboardMarkup(
        inline_keyboard=[[
            InlineKeyboardButton(text=get_button9_text(lang), callback_data=back_to_delivery_cb)
        ]]
    )
    
    await cb.message.answer(option_text, reply_markup=kb)
    await cb.answer()

@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
    
    try:
        await cb.message.delete()
    except Exception:
        pass
    
    text10_content = get_language_text(lang, "text10") or "Выберите способ оплаты:"
    
    # Импортируем функцию из payment_handlers
    from handlers.payment_handlers import build_payment_keyboard
    kb = build_payment_keyboard(lang, product_id, city_id, tag_index, tag_name, district, user_id)
    
    await cb.message.answer(text10_content, reply_markup=kb)
    await cb.answer()

@router.callback_query(F.data.startswith(BACK_TO_PRODUCT_PREFIX))
async def on_back_to_product_from_handlers(cb: CallbackQuery):
    """Обработчик возврата к карточке товара из product_handlers"""
    user_id = cb.from_user.id
    lang = get_user_language(user_id)
    
    payload = cb.data[len(BACK_TO_PRODUCT_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
    
    try:
        await cb.message.delete()
    except Exception:
        pass
    
    # Импортируем функции из catalog.py для отображения карточки товара
    from handlers.catalog import get_product_details, get_city_name, get_product_tags, build_product_tags_keyboard, resolve_image_path
    from aiogram.types.input_file import FSInputFile
    
    product = get_product_details(product_id)
    city_name = get_city_name(city_id)
    
    if not product:
        await cb.answer("Товар не найден", show_alert=True)
        return
    
    # Формируем описание товара (как в catalog.py)
    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_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)
    
    # Отправляем картинку если есть
    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:
                    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
                    )
                return
            except Exception as e:
                logger.error(f"Failed to send photo: {e}")
    
    await cb.message.answer(caption_text, reply_markup=kb)
    await cb.answer()