def get_base_amazon_data(schema_name):
    if schema_name == "amazon_coins":
        a = dcur.execute(
            """
            select item_sku, 'coin', quantity
            from {0}.template;
            """.format(schema_name))

    elif schema_name == "amazon_entertainment_collectibles":
        a = dcur.execute(
            """
            select item_sku, 'coin', limited_edition_quantity
            from {0}.template;
            """.format(schema_name))
    elif schema_name == "amazon_food_service_and_jan_san":

        a = dcur.execute(
            """
            select sku, "product-name", "number-of-items"
            from {0}.template;
            """.format(schema_name))

    else:
        a = dcur.execute(
            """
            select item_sku, item_name, quantity
            from {0}.template;
            """.format(schema_name))
    a = dcur.fetchall()
    return a

def update_pickingloc_info(wh, plid, old_plname, new_plname, upc):
    if old_plname != new_plname:
        a = dcur.execute(
            """
            select *
            from warehouse.picking_locations wpp
            where exists
            (select *
            from warehouse.warehouse_picking_loc
            where picking_location_id = wpp.picking_location_id
            and warehouse_id = %s)
            and picking_location_name = %s;
            """, [wh, new_plname])
        a = dcur.fetchall()
        if a:
            return True

    a = dcur.execute(
        """
        begin;
        update warehouse.picking_locations
        set picking_location_name = %(plname)s,
        upc = %(upc)s::bigint
        where picking_location_id = %(plid)s::int;
        """, {"plname": new_plname,
              "upc": upc,
              "plid": plid})

def insert_new_user(username, password, real_name,  utype, urole):
    a = dcur.execute(
        """
        select user_name
        from users.users
        where user_name = %(username)s;
        """, {"username": username})
    a = dcur.fetchall()
    if a:
        return True

    a = dcur.execute(
        """
        begin;
        insert into users.users (user_name, password, person_name,
                user_type, user_role)
        values (trim(%(user_name)s), %(password)s, 
        trim(%(person_name)s), trim(%(user_type)s), 
        trim(%(user_role)s));
        commit
        """, {"user_name": username,
              "password" :password,
              "person_name": real_name,
              "user_type": utype,
              "user_role": urole})

def generate_pallet_id(wh):
    a = dcur.execute(
        """
        begin;
        with new_pl (pallet_location_id) as
	      (insert into warehouse.pallet_locations
		    (pallet_location_name)
	       values ('staged')
	       returning pallet_location_id)
	       ,
             new_pallet (pallet_id) as
	       (insert into warehouse.pallets (pallet_id)
		values (default)
	        returning pallet_id)
	        ,
              new_wh_palletloc as
                (insert into warehouse.warehouse_pallet_loc
		     (warehouse_id, pallet_location_id)
	         select %s, pallet_location_id
	         from new_pl)
        insert into warehouse.pallet_palletloc
             (pallet_location_id, pallet_id)
        select pallet_location_id, pallet_id
        from new_pl, new_pallet
        returning pallet_id;
        """, [wh])
    a = dcur.fetchall()
    a = dcur.execute(
        """
        commit;
        """)
    return a

def bulk_load_palletlocs(f, wh):
    dcur.execute(
        """
        begin;
        create temp table pls (pallet_location_name varchar);
        """)

    ff = open(f)
    cur.copy_from(ff, "pls")

    a = dcur.execute(
        """
        with tpls (pallet_location_id) as
            (insert into warehouse.pallet_locations
                 (pallet_location_name)
             select pallet_location_name
             from pls
             where not exists
	         (select *
                  from warehouse.pallet_locations
	          join warehouse.warehouse_pallet_loc
	          using (pallet_location_id)
	          where warehouse_id = %(wh)s
	          and pallet_location_name <> pls.pallet_location_name)
              returning pallet_location_id)
        insert into warehouse.warehouse_pallet_loc
            (warehouse_id, pallet_location_id)
        select %(wh)s, pallet_location_id
        from tpls;

        drop table pls;
        commit;
        """, {"file": f,
              "wh": wh})

def add_contact(d):
    a = dcur.execute(
        """
        begin;
        with new_contact (contact_id) as
            (insert into company.contacts (contact_name,
                  contact_position, contact_phone, contact_phone2, 
                  contact_email)
             values (%(contact-name)s, %(position)s, %(phone-one)s, 
        %(phone-two)s, %(email)s)
             returning (company_contact_id))
        insert into company.company_contact (company_id, 
        company_contact_id)
        select %(cid)s, contact_id
        from new_contact
        returning company_contact_id;
        """, d)
    a = dcur.fetchall()

    a = dcur.execute(
        """
        commit;
        """)

    return a

def insert_pallet_location(wh, pl_name):
    a = dcur.execute(
        """
        select *
        from warehouse.pallet_locations
        join warehouse.warehouse_pallet_loc
        using (pallet_location_id)
        where pallet_location_name = trim(%(plname)s)
        and warehouse_id = %(wh)s;
        """, {"plname": pl_name,
              "wh": wh})
    a = dcur.fetchall()
    if a:
        return True

    a = dcur.execute(
        """
        begin;
        with new_loc (pallet_location_id) as
	    (insert into warehouse.pallet_locations
             (pallet_location_name)
	     values (%(plname)s)
	     returning pallet_location_id
	     )
        insert into warehouse.warehouse_pallet_loc
                    (warehouse_id, pallet_location_id)
        select %(wh)s, pallet_location_id
        from new_loc;
        commit;
        """, {"plname": pl_name,
              "wh": wh})

def insert_picking_location(d):
        a = dcur.execute(
            """
            select *
            from warehouse.picking_locations wpp
            where exists
            (select *
            from warehouse.warehouse_picking_loc
            where picking_location_id = wpp.picking_location_id
            and warehouse_id = %(wh)s)
            and picking_location_name = %(plname)s;
            """, d)
        a = dcur.fetchall()
        if a:
            return True


        if d["upc"] == "":
            d["upc"] = None
            
        a = dcur.execute(
            """
            with new_plid (picking_location_id) as
                (insert into warehouse.picking_locations
                     (picking_location_name, upc)
                 values(%(plname)s, %(upc)s::bigint)
                 returning picking_location_id)
            insert into warehouse.warehouse_picking_loc
                (warehouse_id, picking_location_id)
            select %(wh)s, picking_location_id
            from new_plid;
            """, d)

def insert_picking_location(d):
    a = dcur.execute(
        """
        select warehouse_id, warehouse_name, picking_location_name
        from warehouse.warehouses
        join warehouse.warehouse_picking_loc
        using(warehouse_id)
        join warehouse.picking_locations
        using(picking_location_id)
        where warehouse_id = %(wh)s
        and picking_location_name = %(picking-location)s;
        """, d)
    a = dcur.fetchall()
    if a:
        ploc = d["picking-location"]
        whname = a[0]["warehouse_name"]
        return "{0} already exists in {1} warehouse".format(ploc, whname)

    else:
        a = dcur.execute(
            """
            with new_picking_location (new_location_id) as
	    (insert into warehouse.picking_locations
	    (picking_location_name, sku, qty)
	    values (%(picking-location)s, %(sku)s, %(upc)s::int)
	    returning picking_location_id
	    )
            insert into warehouse.warehouse_picking_loc
	    (warehouse_id, picking_location_id)
            select %(wh)s, new_location_id
            from new_picking_location;
            """, d)
    return None

def update_user(original_username, username, real_name, utype,
                urole):
    if original_username != username:
        a = dcur.execute(
            """
            select user_name
            from users.users
            where user_name = %(username)s;
            """, {"username": username})
        a = dcur.fetchall()
        if a:
            return True

    if utype == "":
        utype = None

    a = dcur.execute(
        """
        begin;
        select users.update_user(%(original_username)s, 
        %(username)s, %(real_name)s, %(user_type)s, %(user_role)s);
        commit;
        """, {"original_username": original_username,
              "username": username,
              "real_name": real_name,
              "user_type": utype,
              "user_role": urole})

def select_all_images():
    a = dcur.execute(
        """
        select array_agg(sku) sku_list, image
        from product.image_gallery ig
        left join product.images im
        on (ig.image = im.main_image
        or ig.image = im.image_one
        or ig.image = im.image_two
        or ig.image = im.image_three
        or ig.image = im.image_four
        or ig.image = im.image_five
        or ig.image = im.image_six
        or ig.image = im.image_seven
        or ig.image = im.image_eight
        or ig.image = im.image_nine
        or ig.image = im.image_ten
        or ig.image = im.image_eleven
        or ig.image = im.image_twelve
        or ig.image = im.swatch_image)
        group by image
        order by image;       
        """
    )
    a = dcur.fetchall()
    return a

def select_pallet_locations(wh):
    a = dcur.execute(
        """
        select pallet_location_id, pallet_location_name, pallet_id,
        string_agg(sku || '/' || upc || ' cases(' || case_qty || ')', ';;'),
	string_agg(sku || '(' || (case_qty * piece_qty * box_qty)::varchar || ')', ';;')
        from warehouse.warehouses
        join warehouse.warehouse_pallet_loc
        using (warehouse_id)
        join warehouse.pallet_locations
        using (pallet_location_id)
        left join warehouse.pallet_palletloc
        using (pallet_location_id)
        left join warehouse.pallet_case
        using (pallet_id)
	left join warehouse.case_box
        using (case_id)
        left join warehouse.boxes
        using (box_id)
        left join product.sku_upc
        using (upc)
        where warehouse_id = %s
        group by pallet_location_id, pallet_location_name, pallet_id
        order by pallet_location_id;
        """, [wh])
    a = dcur.fetchall()
    return a

def add_full_pallet_to_pickingloc(wh, pid):
        a = dcur.execute(
            """
            begin;
            with upc_count(upc, total) as
                (select upc, case_qty * piece_qty * box_qty total
                 from warehouse.pallet_palletloc
                 left join warehouse.pallet_case
                 using (pallet_id)
	         left join warehouse.case_box
                 using (case_id)
                 left join warehouse.boxes
                 using (box_id)
                 left join product.sku_upc
                 using (upc)
                 where pallet_id = %s::int
                 group by upc, total, pallet_id)
            update warehouse.picking_locations wpl
            set qty = qty + total
            from
            (select upc, total
            from upc_count) t1
            where wpl.upc = t1.upc
            and wpl.picking_location_id in
            (select picking_location_id
            from warehouse.warehouse_picking_loc
            where warehouse_id = %s);

            delete from warehouse.pallets
            where pallet_id = %s::int;
            commit;
            """, [pid, wh, pid])

def select_all_running_inventory():
    a = dcur.execute(
        """
        select sku, upc, coalesce(total1, 0) + coalesce(total2, 0)
from
        (select sku, upc, qty total1
        from warehouse.warehouse_picking_loc
        join warehouse.picking_locations
        using (picking_location_id)
        join product.sku_upc
        using (upc)) t1

	full join

        (select sku, upc, sum(box_qty * piece_qty * case_qty) total2
        from warehouse.warehouses
        join warehouse.warehouse_pallet_loc
        using (warehouse_id)
        join warehouse.pallet_locations
        using (pallet_location_id)
        join warehouse.pallet_palletloc
        using (pallet_location_id)
        join warehouse.pallet_case
        using (pallet_id)
        join warehouse.case_box
        using (case_id)
        join warehouse.boxes
        using (box_id)
        join product.sku_upc
        using (upc)
        group by sku, upc) t2
        using (sku, upc);
        """)
    a = dcur.fetchall()
    return a

def running_inventory(wh):
    a = dcur.execute(
        """
        select sku, upc,
        sum(coalesce(box_qty * piece_qty * case_qty, 0)
            + coalesce(qty, 0))
        from warehouse.warehouses
        join warehouse.warehouse_pallet_loc
        using (warehouse_id)
        join warehouse.pallet_locations
        using (pallet_location_id)
        join warehouse.pallet_palletloc
        using (pallet_location_id)
        join warehouse.pallet_case
        using (pallet_id)
        join warehouse.case_box
        using (case_id)
        join warehouse.boxes
        using (box_id)
        join product.sku_upc
        using (upc)
        right join warehouse.picking_locations
        using (upc)
        where warehouse_id = %s
        group by sku, upc;
        """, [wh])
    a = dcur.fetchall()
    return a

def sku_types():
    a = dcur.execute(
        """
        select sku_type
        from product.sku_types;
        """)
    a = dcur.fetchall()
    return a

def insert_original_admin(uname, password):
    a = dcur.execute(
        """
        begin;
        insert into users.users (user_name, password, user_role)
        values (%s, %s, 'original admin');
        commit;
        """, [uname, password])

def select_users():
    a = dcur.execute(
        """
        select user_name, person_name, user_type, user_role
        from users.users;
        """)
    a = dcur.fetchall()
    return a

def select_valid_usertypes():
    a = dcur.execute(
        """
        select user_type
        from users.valid_user_types;
        """)
    a = dcur.fetchall()
    return a

def sku_upcs():
    a = dcur.execute(
        """
        select image
        from product.image_gallery
        """)
    a = dcur.fetchall()
    return a