import sqlite3
import openpyxl
import logging

# 로거 인스턴스 가져오기
logger = logging.getLogger('default_logger')

def create_db(db_name):
    conn = sqlite3.connect(db_name)  # Create a connection to the SQLite database
    c = conn.cursor()  # Create a cursor object

    # If tables exist, delete them
    c.execute('''DROP TABLE IF EXISTS Keywords''')
    c.execute('''DROP TABLE IF EXISTS NaverShopping''')
    c.execute('''DROP TABLE IF EXISTS Taobao''')
    c.execute('''DROP TABLE IF EXISTS SubKeywords''')
    c.execute('''DROP TABLE IF EXISTS Matching''')
    c.execute('''DROP TABLE IF EXISTS SSCategoryCode''')

    # Create tables
    c.execute('''
    CREATE TABLE Keywords(
        id INTEGER PRIMARY KEY,
        keyword TEXT,
        base_category TEXT,
        naver_code TEXT,
        isMatch INTEGER,
        MatchingUrl TEXT,
        MatchingCat TEXT,
        delvFee INTEGER,
        packingFee INTEGER,
        plusFee INTEGER,
        date_created TEXT DEFAULT (DATE('now', 'localtime')),
        time_created TEXT DEFAULT (TIME('now', 'localtime'))
    )''')
    logger.debug("테이블 Keywords 완성")

    c.execute('''
    CREATE TABLE NaverShopping(
        id INTEGER PRIMARY KEY,
        keyword_id INTEGER, 
        keyword TEXT,
        price INTEGER,
        productTitle TEXT,
        category1Name TEXT,
        category2Name TEXT,
        category3Name TEXT,
        category4Name TEXT,
        cat_code TEXT,
        openDate TEXT,
        mallCount INTEGER,
        keepCnt INTEGER,
        overseaTp INTEGER,
        reviewCount INTEGER,
        reviewCountSum INTEGER,
        scoreInfo INTEGER,
        naverPayAdAccumulatedDisplayValue INTEGER,
        mobileLowPrice INTEGER,
        lowPrice INTEGER,
        deliveryFeeContent INTEGER,
        dlvryLowPrice INTEGER,
        imageUrl TEXT,
        imgSz TEXT,
        localImagePath TEXT,
        searchKeyword TEXT,
        mallProductUrl TEXT,
        mallPcUrl TEXT,
        mallName TEXT,
        manuTag TEXT,
        mallInfoCache TEXT,
        purchaseCnt INTEGER,
        relatedTags TEXT,
        rank INTEGER,
        chinese_keyword TEXT,
        isMatch INTEGER,
        MatchingUrl TEXT,
        MatchingCat TEXT,
        delvFee INTEGER,
        packingFee INTEGER,
        plusFee INTEGER,
        tao_imageUrl TEXT,
        tao_itemID INTEGER,
        tao_localimage TEXT,
        unique_id TEXT,
        date_created TEXT DEFAULT (DATE('now', 'localtime')),
        time_created TEXT DEFAULT (TIME('now', 'localtime')),
        FOREIGN KEY(keyword_id) REFERENCES Keywords(id)
    )''')
    logger.debug("테이블 NaverShopping 완성")

    c.execute('''
    CREATE TABLE Taobao(
        id INTEGER PRIMARY KEY,
        keyword_id INTEGER,
        taoItem_id INTEGER,
        item_name TEXT,
        imageUrl TEXT,
        price INTEGER,
        sales_volume INTEGER,
        itemUrl TEXT,
        tao_localimage TEXT,
        itemID INTEGER,
        chinese_keyword TEXT,
        isMatch INTEGER,
        MatchingUrl TEXT,
        MatchingCat TEXT,
        keywordSearchUrl TEXT,
        imageSearchUrl TEXT,
        rank INTEGER,
        date_created TEXT DEFAULT (DATE('now', 'localtime')),
        time_created TEXT DEFAULT (TIME('now', 'localtime')),
        FOREIGN KEY(keyword_id) REFERENCES Keywords(id)
    )''')
    logger.debug("테이블 Taobao 완성")

    c.execute('''
    CREATE TABLE SubKeywords(
        id INTEGER PRIMARY KEY,
        keyword_id INTEGER,
        relatedTags TEXT,
        chinese_keyword TEXT,
        isMatch INTEGER,
        MatchingUrl TEXT,
        MatchingCat TEXT,
        date_created TEXT DEFAULT (DATE('now', 'localtime')),
        time_created TEXT DEFAULT (TIME('now', 'localtime')),
        FOREIGN KEY(keyword_id) REFERENCES Keywords(id)
    )''')
    logger.debug("테이블 SubKeywords 완성")

    c.execute('''
    CREATE TABLE Matching(
        id INTEGER PRIMARY KEY,
        keyword_id INTEGER,
        naver_id INTEGER,
        taobao_id INTEGER,
        chinese_keyword TEXT,
        isMatch INTEGER,
        MatchingUrl TEXT,
        MatchingCat TEXT,
        sub_keyword_id INTEGER,
        date_created TEXT DEFAULT (DATE('now', 'localtime')),
        time_created TEXT DEFAULT (TIME('now', 'localtime')),
        FOREIGN KEY(keyword_id) REFERENCES Keywords(id),
        FOREIGN KEY(naver_id) REFERENCES NaverShopping(id),
        FOREIGN KEY(taobao_id) REFERENCES Taobao(id),
        FOREIGN KEY(sub_keyword_id) REFERENCES SubKeywords(id)
    )''')
    logger.debug("테이블 Matching 완성")
    
    c.execute('''
    CREATE TABLE SSCategoryCode(
        id INTEGER PRIMARY KEY,
        cat_code TEXT,
        naver_code TEXT,
        cat1 TEXT,
        cat2 TEXT,
        cat3 TEXT,
        cat4 TEXT
    )''')
    logger.debug("테이블 SSCategoryCode 완성")


    conn.commit()  # Commit the transaction
    conn.close()  # Close the connection
    
def read_excel_category_data(db_name):
    
    filename = 'baseXLS_Percenty.xlsx'
    sheet_name = '스스 카테고리'
    start_row = 2
    end_row = 4900
    
    workbook = openpyxl.load_workbook(filename, data_only=True)
    sheet = workbook[sheet_name]
    # logger.debug(f"read_excel_category_data - B 선택된 시트 : {sheet}")
    
    # data = []
    # for row in sheet.iter_rows(min_row=start_row, max_row=end_row, values_only=True):
    #     data.append(row[0])  # assuming the data is in the first column (B)

    data = []
    # for row in sheet.iter_rows(min_row=start_row, max_row=end_row, values_only=True):
    #     category = row[0] 
    #     naver_code = row[1]  # Assuming Naver_Code is in the second column
    #     data.append((category, naver_code)) 
        
        
    for row in sheet.iter_rows(min_row=start_row, max_row=end_row, values_only=True):
    # Ensure the row has at least 2 elements (for category and naver_code)
        if len(row) >= 2:
            category, naver_code = row[0], row[1]
            data.append((category, naver_code))
        else:
            # Handle rows with insufficient data, e.g., by skipping or logging them
            logger.debug(f"Skipping row with insufficient data: {row}")

    parse_data(db_name, data)

def parse_data(db_name, data):
    parsed_data = []
    
    # for item in data:
    #     if item:  # Check if the item is not None or empty
    #         cat_code = item[:10]
    #         logger.debug(f"parse_data - cat_code : {cat_code}")
    #         categories = item[11:].split("-", maxsplit=3)
    #         while len(categories) < 4:
    #             categories.append(None)  # Fill with None if less than 4 categories
    #         parsed_data.append((cat_code, *categories))
            
    for category, naver_code in data:  # Unpack cat_code and naver_code
        if category: 
            cat_code = category[:10]
            # logger.debug(f"parse_data - cat_code : {cat_code}")
            categories = category[11:].split("-", maxsplit=3)
            while len(categories) < 4:
                categories.append(None) 
            parsed_data.append((cat_code, naver_code, *categories))
            
    insert_category_data_to_db(db_name, parsed_data)

def insert_category_data_to_db(db_name, data):
    conn = sqlite3.connect(db_name)
    c = conn.cursor()

    for item in data:
        c.execute('''
            INSERT INTO SSCategoryCode (cat_code, naver_code, cat1, cat2, cat3, cat4)
            VALUES (?, ?, ?, ?, ?, ?)''', item)

    conn.commit()
    conn.close()


# if __name__ == "__main__":
#     db_name = 'keywords.db'
#     create_db(db_name)  # Create the database and table