import ast
import sqlite3
import sys
import time
import pyodbc
import json
from woocommerce import API

import all_from_woo

conn = sqlite3.connect('imd.db')  # intermediary db
c = conn.cursor()

# Create table in SLQLite - we use this for syncing
c.execute('''CREATE TABLE IF NOT EXISTS products (id INTEGER NOT NULL PRIMARY KEY ON CONFLICT IGNORE, aid INTEGER,
    sifra TEXT, naziv TEXT, price REAL, qty INTEGER, snc INTEGER, grupa INTEGER)''')
# all_from_woo.initSQLite()

# Connect to WooCommerce API
wcapi = API(
    url="https://www.rasterdoo.com",
    consumer_key="ck_b3169b1723f6f39a965ed04ecfa77860fb89bbf5",
    consumer_secret="cs_a83f0217ed8d6191ab7de9df06a0c2b652f6bd57",
    wp_api=True,
    timeout=360,
    version="wc/v3"
)
# Let's check if connection with WooCommerce is OK - list a sales report
# print(wcapi.get("reports/sales?date_min=2024-01-01&date_max=2024-02-04").json())
# print("Resting for for a bit...")
# time.sleep(5)

print("Connecting to MS SQL server")
'''
cnxn = pyodbc.connect('Driver={SQL Server};'
                      'Server=SERVER-BS;'
                      'Database=abacus_raster;'
                      'Trusted_Connection=yes;')
'''
cnxn = pyodbc.connect('DRIVER={SQL Server};'
                      'SERVER=raster.mywire.org,1433;'
                      'DATABASE=abacus_raster;'
                      'UID=sa;'
                      'PWD=!Pos456!;'
                      'TDS_Version=7.3;')

cursor = cnxn.cursor()

# Prvi artikal pocetnog stanja 2019 se vodi pod  brojem "306570", za 2020 pod brojem "396498".
# Ako racunamo lager za artikle od njega dobicemo pravo stanje za 2019. Ukoliko ovo ne uradimo,
# lager se racuna od pocetka rada sa Abacusom - i vodi do gresaka.
# prvo nadjemo id_document pocetnog stanja (prvog dokumenta u godini)
# /****** Script for SelectTopNRows command from SSMS  ******/
# SELECT     id_dokument, id_vrsta, broj_dok, datum
# FROM         abacus_raster.dbo.artikal_dokument
# WHERE     (datum > CONVERT(DATETIME, '2020-12-31 00:00:00', 102))
#
# A onda nadjemo artikal u lageru - Stari kod:
# SELECT     id_artikal, multiply * kol AS qty
#                       FROM          abacus_raster.dbo.artikal_dnevnik
#                      WHERE      (multiply <> 0) AND id_dnevnik >=396498.
cursor.execute('''SELECT a.id_artikal,
    SUM (a.qty) AS stanje
    INTO tmplager
    FROM         (SELECT artikal_dnevnik.id_artikal, 
artikal_dnevnik.multiply*artikal_dnevnik.kol AS 'qty'
FROM abacus_raster.dbo.artikal_dnevnik artikal_dnevnik, abacus_raster.dbo.artikal_dokument artikal_dokument
WHERE artikal_dnevnik.id_dokument = artikal_dokument.id_dokument AND ((artikal_dokument.datum_fak>={d '2024-01-01'})) AND (artikal_dokument.id_vrsta <> 18) ) \
    AS a INNER JOIN
        abacus_raster.dbo.artikal ON a.id_artikal = abacus_raster.dbo.artikal.id_artikal
    GROUP BY a.id_artikal ''')

cursor.execute('''SELECT     abacus_raster.dbo.artikal_cijenik.id_artikal, abacus_raster.dbo.artikal_cijenik.cijena, 
                    abacus_raster.dbo.artikal.naziv, abacus_raster.dbo.artikal.sifra, 
                    tmplager.stanje, abacus_raster.dbo.artikal.id_grupa, tmplager.id_artikal AS tid
FROM         abacus_raster.dbo.artikal_cijenik INNER JOIN
                      abacus_raster.dbo.artikal INNER JOIN
                      tmplager ON abacus_raster.dbo.artikal.id_artikal = tmplager.id_artikal ON 
                      abacus_raster.dbo.artikal_cijenik.id_artikal = abacus_raster.dbo.artikal.id_artikal
WHERE     (abacus_raster.dbo.artikal_cijenik.id_mjesto = 2) AND abacus_raster.dbo.artikal.id_grupa <> 11''')
# Save products from Abacus in SQLite table
row = cursor.fetchone()
print("Abacus row in works:"), row  #
snc = 0
for row in cursor:
    while row is not None:
        # print("Abacus row entered for loop & it is not None")
        # input("Press Enter to continue...") #
        aid = row[0]
        aprice = float(row[1])
        aname = row[2]
        asifra = str(row[3])
        aqty = int(row[4])
        agroup = row[5]
        # print("MS Sql group value:", agroup)
        # This dictionary matches abacus product category id to equivalent id in woocommerce
        groupdict = {
            1: 21472, 2: 21473, 4: 21474, 5: 21475, 10: 21476, 11: 21477, 13: 21477, 15: 21477, 19: 21478, 24: 21479,
            30: 21480, 32: 21481, 33: 21482, 37: 21483, 38: 21484, 44: 21485, 45: 21486, 47: 21477, 48: 21477
        }
        # print("Coverted to Woocommerce group it becomes wgroup:", groupdict[agroup])
        if agroup in groupdict:
            wgroup = groupdict[agroup]
            # print("Group in abacusu is from dictionary:", agroup, " - while woocommerce now holds:", wgroup)  #
        else:
            wgroup = 3557
            # print("Group ", agroup, "does not exist in dictionary - we put it in woocommerce group -other-:", wgroup)  #

        # Test id Woo product exists in SQLite table
        q = (asifra,)
        c.execute('SELECT * FROM products WHERE sifra=?', q)
        # print("Abacus data to be saved in SQLite:", aid, aprice, aname, asifra, aqty, wgroup)
        # input("Press Enter to continue...") #
        # We test SqlLite rows so that we update the table only with product that changed
        trow = c.fetchone()
        if trow is not None:
            tnaziv = trow[3]
            tprice = trow[4]
            tqty = trow[5]
            tgroup = trow[7]
            # print("There is an SQLite trow we need to check for an update: "), tnaziv, tprice, tqty, tgroup
            if aprice != tprice:
                snc = 1
                print(aname, "has a bad price")
            elif aqty != tqty:
                snc = 1
                print(aname, "has a bad qty")
            elif aname != tnaziv:
                snc = 1
                print(aname, "has bad naziv: ", tnaziv)
            elif wgroup != tgroup:
                snc = 1
                print(wgroup, "has a bad tgroup: ", tgroup)
            else:
                snc = 0
            if snc == 1:
                print(asifra, aname, snc)
                c.execute('''UPDATE products SET aid = ?, price = ?, qty =?, naziv = ?, 
                            snc = ?, grupa = ? WHERE sifra = ?''',
                          (aid, aprice, aqty, aname, snc, wgroup, asifra))
                conn.commit()
                c.execute('SELECT * FROM products WHERE sifra=?', q)
                trow = c.fetchone()
                # print(trow)
                # print("=======")
                # input("Press Enter to continue...")
        else:
            print("If sqlite trow does not exist (is ", trow, "), then first create one in Woo", aid, asifra, aname, aprice, aqty, wgroup)
            # input("Press Enter to continue...") #
            aprice = str(aprice)
            data = {
                "sku": asifra,
                "name": aname,
                "regular_price": aprice,
                "description": "",
                "managing_stock": "true",
                "in_stock": "true",
                "status": "publish",
                "stock_quantity": aqty,
                "categories": [
                    {
                        "id": wgroup
                    }
                ],
            }
            w = wcapi.post("products", data).json()
            # w = wcapi.get("products/categories").json()

            class ListStream:
                def __init__(self):
                    self.data = []

                def write(self, s):
                    self.data.append(s)


            sys.stdout = x = ListStream()
            print(w)
            sys.stdout = sys.__stdout__
            wresponse = (x.data[0])
            print("Full woo response: ", wresponse)
            # print("wresponse:", wresponse[38:53])
            if wresponse[38:53] == "duplicirani SKU":
                # if wresponse[26:40] == "duplicated SKU":
                d = ast.literal_eval(wresponse)
                errd = d.get('data')
                print(errd)
                rogueone = errd.get('resource_id')
                print("We have a duplicated product in Woo: ", rogueone)
                c.execute('INSERT INTO products VALUES (?,?,?,?,?,?,?,?)',
                          (rogueone, aid, asifra, aname, aprice, aqty, 1, wgroup))  # if no product it will create
                print("Inserting rogueone in SQLlite data:", rogueone, aid, asifra, aname, aprice, aqty, 1, wgroup)
                conn.commit()
                # rgpath = "\"product_batch/" + str(rogueone) + "\""
                # print rgpath
                # rg = wcapi.get(rogueone).json()
                # print("The rogueone:", rg)
            else:
                print("Since trow was NONE, we had to create it on woo, get the id (woo), then save it (SQLite):",
                      type(w), w)
                wid = w.get('id')
                wsku = w.get('sku')
                wname = w.get('name')
                wprice = w.get('price')
                wqty = w.get('qty')
                if wname is None:
                    print("Why problems? ...:", wid, aid, wsku, wname, wprice, wqty)
                else:
                    c.execute('INSERT INTO products VALUES (?,?,?,?,?,?,?,"")',
                              (wid, aid, wsku, wname, wprice, wqty, 1))  # if no product it will create
                    conn.commit()
        row = cursor.fetchone()
        # print("Resting for for a bit...")
        # time.sleep(5)
conn.close()
print("Wait before sending")
# time.sleep(10)

# Update Woo with our SQLite table
conn = sqlite3.connect('imd.db')  # intermediary db
c = conn.cursor()
c.execute('''SELECT * FROM products WHERE snc = 1 ORDER BY naziv''')
grupa = 0

# Make a product_list
product_list = list()
trow = c.fetchone()
while trow is not None:
    # count += 1
    # print("Selected for update:", trow)
    tid = trow[0]
    tnaziv = trow[3]
    tprice = str(trow[4])
    tqty = trow[5]
    tgroup = trow[7]
    # product = [tid, tqty, tnaziv, tprice, tgroup]
    product = {'id': tid, 'manage_stock': 'true', 'stock_quantity': tqty, 'name': tnaziv, 'status': 'publish', 'regular_price': tprice, 'categories': [{'id': tgroup}]}
    # print(product)
    product_list.append(product)
    trow = c.fetchone()
print(product_list)
# Make batches of products and send them to WooCommerce
l = len(product_list)
print("Lenght: ", l)
batch_size = 100

for i in range(0, l, batch_size):
    one_product_batch = product_list[i:i + batch_size]
    data = {'update': one_product_batch}
    # print(one_product_batch)
    print("_________________________BATCH", i, "_________________________")
    response = wcapi.post("products/batch", data).json()
    print(response)

c.execute('UPDATE products SET snc = 0')
conn.commit()

# Drop the temporary table

print("Preparing to drop the tmptable")
cursor.execute('''drop table tmplager''')
print("Dropped the tmptable")
cursor.close()

del cursor
conn.close()  # close sqlite connection
print("All done!")