import sqlite3
import openpyxl
import logging

# 로거 인스턴스 가져오기
logger = logging.getLogger('default_logger')
def read_excel_data(filename, sheet_name, start_row, end_row):
    workbook = openpyxl.load_workbook(filename, data_only=True)
    sheet = workbook[sheet_name]

    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)

    return data

def parse_data(data):
    parsed_data = []
    for item in data:
        if item:  # Check if the item is not None or empty
            cat_code = item[:10]
            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))
    return parsed_data

def insert_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, cat1, cat2, cat3, cat4)
            VALUES (?, ?, ?, ?, ?)''', item)

    conn.commit()
    conn.close()

if __name__ == "__main__":
    db_name = 'keywords.db'
    filename = 'baseXLS_Percenty.xlsx'
    sheet_name = '스스 카테고리'
    data = read_excel_data(filename, sheet_name, 2, 4500)
    parsed_data = parse_data(data)
    insert_data_to_db(db_name, parsed_data)