<reponame>eric2861994/stalestockBEAT
from model.Product import Product


class ProductRepository(object):
    """
    Repository for products object.
    Please refer to database schema for more information.

    field:
    conn -- psycopg2 database connection

    Table Name: category
    Current Schema:
    id -- serial
    name -- varchar
    categoryId -- integer
    size -- string
    color -- string
    price -- integer
    NOTE current schema might be outdated.
    """
    def __init__(self, psycopg2Connection):
        self.conn = psycopg2Connection

    def insert(self, parent, name, slug):
        cur = self.conn.cursor()
        cur.execute(
            "BEGIN;\n"
            "LOCK TABLE category;\n"
            "DO $$\n"
            "DECLARE myLeft INTEGER;\n"
            "BEGIN\n"
            "SELECT lft INTO myLeft FROM category WHERE id = %s;\n"
            "UPDATE category SET rgt = rgt + 2 WHERE rgt > myLeft;\n"
            "UPDATE category SET lft = lft + 2 WHERE lft > myLeft;\n"
            "INSERT INTO category(name, slug, lft, rgt) VALUES "
            "(%s, %s, myLeft + 1, myLeft + 2);\n"
            "END $$;\n"
            "COMMIT;",
            (parent.getId(), name, slug)
        )
        self.conn.commit()

    def append(self, product):
        """
        append
        """
        cur = self.conn.cursor()
        cur.execute(
            "INSERT INTO product(name, categoryId, size, color, price) "
            "VALUES (%s, %s, %s, %s, %s);",
            (
                product.getName(),
                product.getCategoryId(),
                product.getSize(),
                product.getColor(),
                product.getPrice()
            )
        )

    def delete(self, productId):
        cur = self.conn.cursor()
        cur.execute("DELETE FROM product WHERE id=%s;", (productId,))
        self.conn.commit()

    def get(
        self,
        categoryId,
        size=None,
        color=None,
        priceStart=None,
        priceEnd=None
    ):
        """
        size=None, color=None, priceStart=None, priceEnd=None
        """
        query = "SELECT product.* FROM product "
        whereClause = ""
        args = []

        if categoryId:
            query = (
                "SELECT product.* FROM product, "
                "category as node, category as parent "
            )
            whereClause = (
                " AND parent.id = %s"
                " AND node.lft BETWEEN parent.lft AND parent.rgt"
                " AND product.categoryId = node.id"
            )
            args.append(categoryId)

        if size:
            whereClause += " AND size = %s"
            args.append(size)
        if color:
            whereClause += " AND color = %s"
            args.append(color)
        if priceStart and priceEnd:
            whereClause += " AND price BETWEEN %s AND %s"
            args += [priceStart, priceEnd]

        if args:
            whereClause = "WHERE " + whereClause[5:] + " "
            query += whereClause

        query += "ORDER BY product.categoryId;"

        cur = self.conn.cursor()
        print 'query', cur.mogrify(query, args)
        cur.execute(query, args)
        dbProducts = cur.fetchall()

        if dbProducts:
            return [apply(Product, row) for row in dbProducts]

        return []

    def getAll(
        self,
        size=None,
        color=None,
        priceStart=None,
        priceEnd=None
    ):
        cur = self.conn.cursor()
        whereClause = ""
        args = []

        if size:
            whereClause += " AND size = %s"
            args.append(size)
        if color:
            whereClause += " AND color = %s"
            args.append(color)
        if priceStart and priceEnd:
            whereClause += " AND price BETWEEN %s AND %s"
            args += [priceStart, priceEnd]

        query = "SELECT product.* FROM product "
        if args:
            whereClause = "WHERE " + whereClause[5:] + " "
            query += whereClause
        query += "ORDER BY product.categoryId;"
        cur.execute(query, args)
        dbProducts = cur.fetchall()
        products = [apply(Product, row) for row in dbProducts]

        return products
