import re
from datetime import datetime
from typing import Optional, List

from fastapi import HTTPException, UploadFile
from sqlalchemy import select, update, or_
from sqlalchemy.ext.asyncio import AsyncSession

from src.category.models import Category
from src.manufacturer.models import Manufacturer
from src.product.models import Product, ProductCategory, ProductStatus, ProductAttribute, ProductImage
from src.product.schemas import ProductOut, ProductDetailResponse, StoreOut, ImageOut, ManufacturerOut, AttributeOut, \
    CategoryBrief, ProductCreate, ProductUpdate, ProductAttributeCreate, ProductAttributeUpdate
from src.store.models import ProductStore, Store
from src.utils.image_manager import handle_image_upload

def translit(text: str) -> str:
    map_ = {
        'а': 'a', 'б': 'b', 'в': 'v', 'г': 'h', 'ґ': 'g', 'д': 'd', 'е': 'e', 'є': 'ie', 'ж': 'zh', 'з': 'z',
        'и': 'y', 'і': 'i', 'ї': 'i', 'й': 'i', 'к': 'k', 'л': 'l', 'м': 'm', 'н': 'n', 'о': 'o', 'п': 'p',
        'р': 'r', 'с': 's', 'т': 't', 'у': 'u', 'ф': 'f', 'х': 'kh', 'ц': 'ts', 'ч': 'ch', 'ш': 'sh', 'щ': 'shch',
        'ь': '', 'ю': 'iu', 'я': 'ia', 'ʼ': '',
        'ё': 'yo', 'ъ': '', 'ы': 'y', 'э': 'e',
        'А': 'A', 'Б': 'B', 'В': 'V', 'Г': 'H', 'Ґ': 'G', 'Д': 'D', 'Е': 'E', 'Є': 'Ye', 'Ж': 'Zh', 'З': 'Z',
        'И': 'Y', 'І': 'I', 'Ї': 'I', 'Й': 'Y', 'К': 'K', 'Л': 'L', 'М': 'M', 'Н': 'N', 'О': 'O', 'П': 'P',
        'Р': 'R', 'С': 'S', 'Т': 'T', 'У': 'U', 'Ф': 'F', 'Х': 'Kh', 'Ц': 'Ts', 'Ч': 'Ch', 'Ш': 'Sh', 'Щ': 'Shch',
        'Ь': '', 'Ю': 'Yu', 'Я': 'Ya', 'Ё': 'Yo', 'Ъ': '', 'Ы': 'Y', 'Э': 'E',
        ' ': '-', '_': '-', ',': '', '.': '', '/': '-', ':': '', ';': '', '?': '', '!': '', '#': '', '(': '',
        ')': '', '"': '', '\'': '', '«': '', '»': '', '&': 'and', '%': 'percent', '@': 'amp', '=': 'equal', '+': 'plus',
        '–': '-',
        '—': '-', '№': '', '’': '',
    }
    text = ''.join(map_.get(c, c) for c in text)
    text = text.lower()
    text = re.sub(r'[^a-z0-9\-]', '', text)
    text = re.sub(r'-+', '-', text)
    return text.strip('-')


class SeoService:
    @staticmethod
    async def generate_seo_products(db: AsyncSession):
        stmt = select(Product).where(or_(
            Product.seo_keyword.is_(None),
            Product.seo_keyword == ''
        ))
        result = await db.execute(stmt)
        rows = result.scalars().all()  # scalars() возвращает только объекты Product

        products = []
        for product in rows:
            product_id = product.product_id
            name = product.name
            seo_keyword = translit(name)

            seo_keyword = "p-" + f'{product_id}' + seo_keyword
            stmt = (
                update(Product)
                .where(Product.product_id == product_id)
                .values(seo_keyword=seo_keyword)
            )
            await db.execute(stmt)
            await db.commit()
            products.append({
                'product_id'    : product_id,
                'name'          : name,
                'seo_keyword'   : seo_keyword
            })

        return products


    @staticmethod
    async def generate_seo_product(product_id: int, db: AsyncSession):
        stmt = select(Product).where(Product.product_id == product_id)
        result = await db.execute(stmt)
        rows = result.scalars().all()  # scalars() возвращает только объекты Product

        products = []
        for product in rows:
            product_id = product.product_id
            name = product.name
            seo_keyword = translit(name)

            seo_keyword = "p-" + f'{product_id}' + seo_keyword
            stmt = (
                update(Product)
                .where(Product.product_id == product_id)
                .values(seo_keyword=seo_keyword)
            )
            await db.execute(stmt)
            await db.commit()
            products.append({
                'product_id'    : product_id,
                'name'          : name,
                'seo_keyword'   : seo_keyword
            })

        return products

    @staticmethod
    async def generate_seo_category(db: AsyncSession):
        stmt = select(Category).where(Category.seo_keyword.is_(None))
        result = await db.execute(stmt)
        rows = result.scalars().all()  # scalars() возвращает только объекты Product

        categories = []
        for category in rows:
            category_id = category.category_id
            name = category.name
            seo_keyword = translit(name)

            seo_keyword = "c-" + f'{category_id}' + seo_keyword
            stmt = (
                update(Category)
                .where(Category.category_id == category_id)
                .values(seo_keyword=seo_keyword)
            )
            await db.execute(stmt)
            await db.commit()
            categories.append({
                'category_id'   : category_id,
                'name'          : name,
                'seo_keyword'   : seo_keyword
            })

        return categories

    @staticmethod
    async def generate_seo_store(db: AsyncSession):
        stmt = select(Store).where(Store.seo_keyword.is_(None))
        result = await db.execute(stmt)
        rows = result.scalars().all()

        stores = []
        for store in rows:
            store_id = store.store_id
            name = store.name
            seo_keyword = translit(name)

            seo_keyword = f"s-{store_id}{seo_keyword}"


            stmt = (
                update(Store)
                .where(Store.store_id == store_id)
                .values(seo_keyword=seo_keyword)
            )
            await db.execute(stmt)

            stores.append({
                "store_id": store_id,
                "name": name,
                "seo_keyword": seo_keyword
            })


        await db.commit()

        return stores

    @staticmethod
    async def generate_seo_manufacturer(db: AsyncSession):
        stmt = select(Manufacturer).where(
            or_(
                Manufacturer.seo_keyword.is_(None),
                Manufacturer.seo_keyword == ""
            )
        )
        result = await db.execute(stmt)
        rows = result.scalars().all()

        manufacturers = []
        for manufacturer in rows:
            manufacturer_id = manufacturer.manufacturer_id
            name = manufacturer.name
            seo_keyword = translit(name)

            seo_keyword = f"m-{manufacturer_id}{seo_keyword}"


            stmt = (
                update(Manufacturer)
                .where(Manufacturer.manufacturer_id == manufacturer_id)
                .values(seo_keyword=seo_keyword)
            )
            await db.execute(stmt)

            manufacturers.append({
                "manufacturer": manufacturer_id,
                "name": name,
                "seo_keyword": seo_keyword
            })


        await db.commit()

        return manufacturers