from sqlalchemy import sql from sanmarco models base import db from sa

from sqlalchemy import sql
from sanmarco.models.base import db
from sanmarco.models import goods
from sanmarco.models import catalog
from sanmarco.lib.mapping import get_idents, mapping_one_to_one, mapping_many_to_one
from sanmarco.models.catalog.files import FileNode
from sanmarco.models.ordering.orders import q_order_item_price
from sanmarco.queries.goods.statistics import q_products_statistic


def q_products_active():
    q = q_products_all()
    q = q.filter(goods.Product.active == True)
    return q


def q_as_complect(q, complect_id, complect_type):
    q = q.join(
        goods.Complect, sql.and_(
            goods.Complect.active==True,
            goods.Complect.link_id == goods.Product.id,
            goods.Complect.complect_type == complect_type,
            goods.Complect.from_id == complect_id
        ))
    return q


def q_as_complect_free(q, complect_id, complect_type):
    q = q.outerjoin(
        goods.Complect, sql.and_(
            goods.Complect.link_id == goods.Product.id,
            goods.Complect.complect_type == complect_type,
            goods.Complect.from_id == complect_id
        ))
    q = q.filter(goods.Complect.id == None)
    return q


def q_products_all():
    subq = q_products_statistic().subquery()
    q = db.query(
        goods.Product,
        goods.Brand,
        goods.Kind,
        goods.Country,
        catalog.Category,
        goods.Material,
        subq.c.no_images,
        subq.c.has_images,
        subq.c.no_options,
        subq.c.has_options,
        subq.c.no_prices,
        subq.c.has_prices
    )
    q = q.outerjoin(subq, subq.c.pid == goods.Product.id)
    q = q.join(goods.Brand, goods.Brand.id == goods.Product.brand_id)
    q = q.join(goods.Kind, goods.Kind.id == goods.Product.kind_id)
    q = q.join(goods.Country, goods.Country.id == goods.Product.country_id)
    q = q.join(catalog.Category, catalog.Category.id == goods.Product.category_id)
    q = q.join(goods.Material, goods.Material.id == goods.Product.material_id)
    q.subqueries = [subq]
    return q


def q_products_covers(products_list, as_ids=False):
    if as_ids:
        idents = products_list
    else:
        idents = get_idents(products_list)
    q = db.query(FileNode, goods.VariantImage.product_id)
    q = q.join(goods.VariantImage, goods.VariantImage.file_id == FileNode.id)
    q = q.filter(sql.and_(
        goods.VariantImage.product_id.in_(idents),
        goods.VariantImage.active == True
    ))
    return q


def q_products_variants(products_list, as_ids):
    subq = q_order_item_price().subquery()
    if as_ids:
        idents = products_list
    else:
        idents = get_idents(products_list)

    q = db.query(goods.Variant)
    q = q.join(subq, subq.c.vid == goods.Variant.id)
    q = q.filter(goods.Variant.product_id.in_(idents))
    q = q.order_by(sql.asc(goods.Variant.id))
    return q


def q_variants_images(variants_list, as_ids=False):
    if as_ids:
        idents = variants_list
    else:
        idents = get_idents(variants_list)

    q = db.query(FileNode, goods.VariantImage.variant_id)
    q = q.join(goods.VariantImage, goods.VariantImage.file_id == FileNode.id)
    q = q.filter(sql.and_(
        goods.VariantImage.variant_id.in_(idents)
    ))
    q.order_by(sql.desc(goods.VariantImage.ordering))
    return q


def q_variant_images(products_list, as_ids):
    if as_ids:
        idents = products_list
    else:
        idents = get_idents(products_list)

    q = db.query(FileNode, goods.VariantImage.variant_id)
    q = q.join(goods.VariantImage, goods.VariantImage.file_id == FileNode.id)
    q = q.filter(sql.and_(
        goods.VariantImage.product_id.in_(idents)
    ))
    q.order_by(sql.desc(goods.VariantImage.ordering))
    return q


def q_product_variants(product_ids):
    imq = db.query(goods.VariantImage.variant_id.label('vid'), sql.func.count(goods.VariantImage.id).label('cnt'))
    imq = imq.filter(goods.VariantImage.product_id.in_(product_ids))
    imq = imq.group_by(goods.VariantImage.variant_id)
    imq = imq.subquery()

    q = db.query(goods.Variant, goods.Colour, goods.Material)
    q = q.join(goods.Colour, goods.Colour.id == goods.Variant.colour_id)
    q = q.join(goods.Material, goods.Material.id == goods.Variant.material_id)
    if len(product_ids) > 1:
        q = q.filter(goods.Variant.product_id.in_(product_ids))
    elif len(product_ids) == 1:
        q = q.filter(goods.Variant.product_id == product_ids[0])
    q = q.join(imq, imq.c.vid == goods.Variant.id)
    q = q.filter(sql.and_(
        imq.c.cnt > 0
    ))
    return q


def q_variants_detail_process(product_id):
    q = q_product_variants([product_id])
    variants = q.all()
    variants = mapping_one_to_one(variants, ['colour', 'material'])
    ind_vars = get_idents(variants, as_hash=True)

    q = db.query(FileNode, goods.VariantImage.variant_id)
    q = q.join(goods.VariantImage, goods.VariantImage.file_id == FileNode.id)
    q = q.filter(goods.VariantImage.variant_id.in_(ind_vars.keys()))
    result = mapping_one_to_one(q.all(), ['variant_id'])
    mapping_many_to_one(ind_vars, result, 'variant_id', 'images')

    q = q_order_item_price().filter(goods.VariantPrice.product_id == product_id)
    for variant_id, price in q.all():
        if not variant_id in ind_vars: continue
        ind_vars[variant_id].price = price

    return variants