# -*- coding: utf-8 -*-
from selenium import webdriver
from selenium.webdriver.common.by import By
from selenium.webdriver.common.keys import Keys
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from webdriver_manager.chrome import ChromeDriverManager
from selenium.webdriver.chrome.service import Service
import psycopg2
import time
from selenium.common.exceptions import StaleElementReferenceException
from db_config import DB_INFO_PSYCOPG2

def shop_lgoin():
    try:
        # ===============================
        # 드라이버 실행
        # ===============================
        options = webdriver.ChromeOptions()

        options.add_argument("--headless=new")
        options.add_argument("--disable-gpu")
        options.add_argument("--no-sandbox")
        options.add_argument("--disable-dev-shm-usage")

        options.add_argument("--disable-extensions")
        options.add_argument("--disable-infobars")
        options.add_argument("--disable-notifications")

        options.add_argument("--window-size=1280,800")
        options.add_argument("--disable-blink-features=AutomationControlled")

        driver = webdriver.Chrome(
            service=Service(ChromeDriverManager().install()),
            options=options
        )

        wait = WebDriverWait(driver, 20)

        # ===============================
        # 1. 로그인 페이지 접속
        # ===============================
        driver.get("https://ownerclan.com/V2/member/loginform.php")

        wait.until(EC.presence_of_element_located((By.ID, "id"))).send_keys("kdjsystem1")
        wait.until(EC.presence_of_element_located((By.ID, "passwd"))).send_keys("!kdjsystem!!" + Keys.ENTER)

        time.sleep(2)

        # ===============================
        # 3. 공급사코드 선택
        # ===============================
        # 드롭다운 버튼 클릭
        dropdown_btn = safe_find(wait, By.XPATH, "//*[@id='top_wrap']/form/div/div[1]")
        dropdown_btn.click()

        time.sleep(2)

        # 공급사코드 항목 클릭
        supplier_code_item = safe_find(wait, By.XPATH, "/html/body/div[11]/div[3]/form/div/div[1]/div/div[2]/a[4]/span")
        supplier_code_item.click()

        return driver

    except Exception as e:
        print('[ERROR][LOGIN]', type(e), e)
        raise

def domeggook_login():

    try:
        options = webdriver.ChromeOptions()

        options.add_argument("--headless=new")
        options.add_argument("--disable-gpu")
        options.add_argument("--no-sandbox")
        options.add_argument("--disable-dev-shm-usage")

        options.add_argument("--disable-extensions")
        options.add_argument("--disable-infobars")
        options.add_argument("--disable-notifications")

        options.add_argument("--window-size=1280,800")
        options.add_argument("--disable-blink-features=AutomationControlled")

        driver = webdriver.Chrome(
            service=Service(ChromeDriverManager().install()),
            options=options
        )
        driver.set_page_load_timeout(180)
        driver.set_script_timeout(180)

        wait = WebDriverWait(driver, 20)

        driver.get("https://domeggook.com/ssl/member/mem_loginForm.php?back=aHR0cHM6Ly9kb21lbWVkYi5kb21lZ2dvb2suY29tL2luZGV4")
        #https://domeggook.com/ssl/member/mem_loginForm.php?back=aHR0cHM6Ly9kb21lbWVkYi5kb21lZ2dvb2suY29tL2luZGV4

        wait.until(EC.presence_of_element_located((By.ID, "idInput"))).send_keys("kdj01")
        wait.until(EC.presence_of_element_located((By.ID, "pwInput"))).send_keys("kdj7928hd" + Keys.ENTER)

        # time.sleep(1)
        # # 도매매 항목 클릭
        # wait.until(EC.element_to_be_clickable(
        #     (By.XPATH, "/html/body/div[5]/div[1]/div[1]/ul[1]/li[1]/a")
        # )).click()

        time.sleep(1)
        # 드롭다운 클릭
        wait.until(EC.element_to_be_clickable(
            (By.XPATH, '/html/body/header/div[3]/div[2]/div[1]/form/div[1]/div/div[1]/a')
            #/html/body/header/div[3]/div[2]/div[1]/form/div[1]/div/div[1]/a
        )).click()

        time.sleep(1)
        # 공급사코드 항목 클릭
        wait.until(EC.element_to_be_clickable(
            (By.XPATH, "/html/body/header/div[3]/div[2]/div[1]/form/div[1]/ul/li[3]/a")
            #/html/body/header/div[3]/div[2]/div[1]/form/input[5]
        )).click()

        return driver

    except Exception as e:
        print('[ERROR][LOGIN]', type(e), e)
        raise

def get_supplier_codes():
    conn = psycopg2.connect(**DB_INFO_PSYCOPG2)

    # 사전 INSERT (오너클랜 공급사 등록)
    insert_query = """
        insert into mlinkdw.supplier_info (supplier_code, dome)
        select distinct a.vender_code, 'OWN'
          from mlinkdw.shopprodinfo_ownerclan as a
         where a.status = '0'
           and not exists (
                select 1
                  from mlinkdw.supplier_info b
                 where a.vender_code = b.supplier_code
                   and b.dome = 'OWN'
           );
    """

    # 공급사 조회 (supplier_code, up_datetime 반환)
    select_query = """
            select supplier_code, company_name, phone_number, business_address, email, up_datetime
              from mlinkdw.supplier_info 
             where dome = 'OWN' 
             order by up_datetime desc;
        """

    result = []

    #and up_datetime is null
    #and product_total_count = 0 and company_name = ''
    #and supplier_code in ('2010026982')

    try:
        with conn.cursor() as cur:
            # 🔹 INSERT 먼저 실행
            cur.execute(insert_query)
            conn.commit()

            # 🔹 SELECT 실행
            cur.execute(select_query)
            rows = cur.fetchall()

            # (supplier_code, up_datetime) 튜플 그대로 반환
            result = rows

    except Exception as e:
        conn.rollback()
        raise e
    finally:
        conn.close()

    return result

def get_domeggook_supplier_codes():
    conn = psycopg2.connect(**DB_INFO_PSYCOPG2)

    # 사전 INSERT (도매꾹 공급사 등록)
    insert_query = """
        insert into mlinkdw.supplier_info (supplier_code, dome)
        select distinct a.vender_code, 'DMM'
          from mlinkdw.shopprodinfo_domeggook as a
         where a.status = '0'
           and not exists (
                select 1
                  from mlinkdw.supplier_info b
                 where a.vender_code = b.supplier_code
                   and b.dome = 'DMM'
           );
    """

    # 공급사 조회 (supplier_code, up_datetime 반환)
    select_query = """
            select supplier_code, company_name, phone_number, business_address, email, up_datetime
              from mlinkdw.supplier_info
             where dome = 'DMM' 
               order by up_datetime desc;
        """

    result = []

    #and up_datetime is null
    #and supplier_code in ('syubea1004')
    #where dome = 'DMM' and product_total_count = 0 and company_name = ''

    try:
        with conn.cursor() as cur:
            # 🔹 INSERT 먼저 실행
            cur.execute(insert_query)
            conn.commit()

            # 🔹 SELECT 실행
            cur.execute(select_query)
            rows = cur.fetchall()

            # (supplier_code, up_datetime) 튜플 그대로 반환
            result = rows

    except Exception as e:
        conn.rollback()
        raise e
    finally:
        conn.close()

    return result

def convert_vender_grade(vender_icon):
    """
    반환값:
      - 1~17 : 실제 공급사 등급
      - 'NEW'
      - 'BAD'
      - None
    """
    try:
        vender_class = vender_icon.get_attribute("class")

        for cls in vender_class.split():
            # BAD
            if "icon_venderB" in cls.lower():
                return "BAD"

            # 신규
            if "icon_vender0" in cls.lower():
                return "NEW"

            # 숫자 등급
            if cls.startswith("icon_vender"):
                num = int("".join(filter(str.isdigit, cls)))
                return 18 - num   # 🔥 핵심 변환식

    except Exception:
        pass

    return None

def safe_find(wait, by, xpath, retry=3):
    for _ in range(retry):
        try:
            return wait.until(
                EC.element_to_be_clickable((by, xpath))
            )
        except StaleElementReferenceException:
            time.sleep(0.5)
    raise Exception(f"Element not clickable: {xpath}")

def SupplierInfoCrawling():

    driver = shop_lgoin()
    wait = WebDriverWait(driver, 20)

    # 공급사 코드 가저오기
    supplier_list = get_supplier_codes()
    # supplier_codes = [
    #     '2010021270',
    #     '2010028692'
    # ]

    time.sleep(2)
    processed = 0

    # ===============================
    # 5. 공급사코드 검색 반복
    # ===============================
    for supplier_code, _company_name, _phone_number, _business_address, _email, up_datetime in supplier_list:


        try:
           driver.title
        except:
            print("⚠ driver 죽음 → 재시작")
            driver.quit()
            driver = shop_lgoin()
            wait = WebDriverWait(driver, 20)

        try:
            alert = driver.switch_to.alert
            print("⚠ alert 발견:", alert.text)
            alert.accept()
        except:
            pass

        processed += 1

        if processed % 100 == 0:
            print("🔄 Driver 재시작")
            driver.quit()
            time.sleep(2)
            driver = shop_lgoin()
            wait = WebDriverWait(driver, 20)

        # ===============================
        # 4. 공급사코드 입력
        # ===============================
        search_input = safe_find(wait, By.XPATH, "/html/body/div[11]/div[3]/form/div/input[2]")
        driver.execute_script("arguments[0].value = '';", search_input)
        search_input.send_keys(supplier_code)

        time.sleep(1)

        # ===============================
        # 5. 검색 버튼 클릭
        # ===============================
        try:
            search_btn = safe_find(wait, By.XPATH, "/html/body/div[11]/div[3]/form/div/div[2]")
            search_btn.click()

            time.sleep(6)

            # ===============================
            # 총 상품 수 추출
            # ===============================
            try:
                total_text_elem = safe_find(wait, By.XPATH, "/html/body/div[13]/form/div[3]/span[2]")
                # 숫자만 추출(총 1,669개의 상품이 있습니다.)
                import re
                match = re.search(r'([\d,]+)', total_text_elem.text)
                productTotalCount = int(match.group(1).replace(',', '')) if match else 0
                if productTotalCount == 0:
                    time.sleep(6)
                    total_text_elem = safe_find(wait, By.XPATH, "/html/body/div[13]/form/div[3]/span[2]")
                    match = re.search(r'([\d,]+)', total_text_elem.text)
                    productTotalCount = int(match.group(1).replace(',', '')) if match else 0

                if productTotalCount == 0:
                    print(f"[SKIP] 상품 없음: {supplier_code}")
                    upsert_supplier_info({
                        "supplier_code": supplier_code,
                        "company_name": '상품 없음',
                        "vender_grade": '',
                        "product_total_count": 0,
                        "phone_number": '',
                        "business_address": '',
                        "email": '',
                        "dome": 'OWN',
                        "prod_name": ''
                    })
                    continue
                else:
                    print("총 상품 수:", productTotalCount)

                # ===============================
                # 공급사 등급 추출
                # ===============================
                try:
                    vender_icon = safe_find(wait, By.XPATH, "/html/body/div[13]/div[1]/ul/li[1]/div/p[1]/span[2]")
                    time.sleep(1)
                    # class 속성에서 숫자 추출
                    vender_grade = convert_vender_grade(vender_icon)

                    if vender_grade is None:
                        print("⚠ 공급사 등급 추출 실패")
                        upsert_supplier_info({
                            "supplier_code": supplier_code,
                            "company_name": '공급사 등급 추출 실패',
                            "vender_grade": '',
                            "product_total_count": productTotalCount,
                            "phone_number": '',
                            "business_address": '',
                            "email": '',
                            "dome": 'OWN',
                            "prod_name": ''
                        })
                        continue
                    else:
                        print("공급사 등급:", vender_grade)

                    # prod_name
                    prod_name = ''
                    try:
                        prod_name = wait.until(EC.presence_of_element_located(
                            (By.XPATH, "/html/body/div[13]/div[1]/ul/li[1]/div/p[3]/a")
                        )).text.strip()
                    except Exception:
                        prod_name = ''
                    print("상품명:", prod_name)

                    #if productTotalCount > 0 & (_company_name is None or _company_name == ''):
                    if productTotalCount > 0:
                        print(f"[NEW] {supplier_code} → 공급사 정보 크롤링 대상")
                        time.sleep(3)

                        # ===============================
                        # 첫 번째 상품 클릭
                        # ===============================
                        try:
                            first_product = wait.until(
                                EC.element_to_be_clickable(
                                    (By.XPATH, "/html/body/div[13]/div[1]/ul/li[1]/div/div/span/a")
                                )
                            )

                            # 새 탭 열림 대비
                            driver.execute_script("arguments[0].scrollIntoView(true);", first_product)
                            # 현재 탭 저장
                            main_window = driver.current_window_handle
                            time.sleep(0.5)
                            # 첫 상품 클릭 (이미 실행됨)
                            first_product.click()

                            time.sleep(4)

                            # 새 탭 열릴 때까지 대기
                            wait.until(lambda d: len(d.window_handles) > 1)
                            # 새 탭으로 전환
                            new_window = [w for w in driver.window_handles if w != main_window][0]
                            driver.switch_to.window(new_window)

                            # 공급사정보 탭 클릭
                            supplier_info_tab = wait.until(
                                EC.element_to_be_clickable(
                                    (By.XPATH, "/html/body/div[18]/div[4]/ul/li[3]")
                                )
                            )
                            supplier_info_tab.click()

                            time.sleep(1)

                            #상호명
                            try:
                                company_name = safe_find(wait, By.XPATH,  "/html/body/div[18]/div[4]/div[10]/div/table/tbody/tr[1]/td[1]").text.strip()
                            except Exception:
                                try:
                                    company_name = wait.until(
                                        EC.presence_of_element_located(
                                            (By.XPATH, "/html/body/div[18]/div[4]/div[5]/div[6]/div/table/tbody/tr[1]/td[1]")
                                        )
                                    ).text.strip()
                                except Exception:
                                    company_name = wait.until(
                                        EC.presence_of_element_located(
                                            (By.XPATH, "/html/body/div[18]/div[9]/div/table/tbody/tr[1]/td[1]")
                                        )
                                    ).text.strip()

                            #전화번호
                            try:
                                phone_number = safe_find(wait, By.XPATH, "/html/body/div[18]/div[4]/div[10]/div/table/tbody/tr[2]/td[2]").text.strip()
                            except Exception:
                                try:
                                    phone_number = wait.until(
                                        EC.presence_of_element_located(
                                            (By.XPATH, "/html/body/div[18]/div[4]/div[5]/div[6]/div/table/tbody/tr[2]/td[2]")
                                        )
                                    ).text.strip()
                                except Exception:
                                    phone_number = wait.until(
                                        EC.presence_of_element_located(
                                            (By.XPATH, "/html/body/div[18]/div[9]/div/table/tbody/tr[2]/td[2]")
                                        )
                                    ).text.strip()

                            #사업장 소재지
                            try:
                                business_address = safe_find(wait, By.XPATH, "/html/body/div[18]/div[4]/div[10]/div/table/tbody/tr[3]/td[2]").text.strip()
                            except Exception:
                                try:
                                    business_address = wait.until(
                                        EC.presence_of_element_located(
                                            (By.XPATH, "/html/body/div[18]/div[4]/div[5]/div[6]/div/table/tbody/tr[3]/td[2]")
                                        )
                                    ).text.strip()
                                except Exception:
                                    business_address = wait.until(
                                        EC.presence_of_element_located(
                                            (By.XPATH, "/html/body/div[18]/div[9]/div/table/tbody/tr[3]/td[2]")
                                        )
                                    ).text.strip()

                            print("상호명:", company_name)
                            print("전화번호:", phone_number)
                            print("사업장소재지:", business_address)

                        except Exception:
                            print(f"[SKIP][NOT_FOUND] supplier={supplier_code}")
                            upsert_supplier_info({
                                "supplier_code": supplier_code,
                                "company_name": '상품 없음/판매종료/삭제',
                                "vender_grade": vender_grade,
                                "product_total_count": productTotalCount,
                                "phone_number": '',
                                "business_address": '',
                                "email": '',
                                "dome": 'OWN',
                                "prod_name": prod_name
                            })

                        # 새 탭 닫기
                        driver.close()
                        # 원래 탭으로 복귀
                        driver.switch_to.window(main_window)

                    else:
                        company_name = _company_name
                        phone_number = _phone_number
                        business_address = _business_address
                        email = _email

                    data = {
                        "supplier_code": supplier_code,
                        "company_name": company_name,
                        "vender_grade": str(vender_grade) if vender_grade is not None else None,
                        "product_total_count": productTotalCount,
                        "phone_number": phone_number,
                        "business_address": business_address,
                        "email": '',
                        "dome": 'OWN',
                        "prod_name": prod_name
                    }
                    upsert_supplier_info(data)
                    print(f"DB 저장 완료: {supplier_code}")

                except Exception:
                    try:
                        print(f"[SKIP] 검색 결과 없음: {supplier_code}")
                        data = {
                            "supplier_code": supplier_code,
                            "company_name": '검색 결과 없음',
                            "vender_grade": str(vender_grade) if vender_grade is not None else None,
                            "product_total_count": productTotalCount,
                            "phone_number": '',
                            "business_address": '',
                            "email": '',
                            "dome": 'OWN',
                            "prod_name": prod_name
                        }
                        upsert_supplier_info(data)
                    except Exception:
                        print(f"DB 저장오류")

            except Exception:
                print(f"검색 오류 : {supplier_code}")

        except Exception:
            print(f"검색 오류 : {supplier_code}")

    print("✅ 공급사코드 검색 완료")
    driver.quit()

def extract_domeggook_grade(driver):
    """
    도매꾹 공급사 등급 추출
    (화면에 보이는 숫자 그대로 반환)
    """
    try:
        elems = driver.find_elements(
            By.XPATH,
            "//div[contains(@class,'main_cont_text3')]//strong"
        )

        for elem in elems:
            text = elem.text.strip()
            if text.isdigit():
                return int(text)

    except Exception:
        pass

    return None

def is_adult_cert_popup(driver):
    try:
        return len(driver.find_elements(
            By.XPATH,
            "//*[@id='lAdultCertContent' or contains(@class,'lPopupAdultCert')]"
        )) > 0
    except Exception:
        return False

def is_product_not_found(driver):
    """
    도매꾹 상품 없음/판매종료/삭제 페이지 감지
    """
    try:
        # h3 문구 기준 (가장 확실)
        elems = driver.find_elements(
            By.XPATH,
            "//*[@id='lEmpty']//h3[contains(text(),'상품을 찾을 수 없습니다')]"
        )
        return len(elems) > 0
    except Exception:
        return False

def crawl_domeggook_suppliers():
    driver = domeggook_login()
    wait = WebDriverWait(driver, 20)

    supplier_codes = get_domeggook_supplier_codes()
    # supplier_codes =[
    #     'seoul7rsoe'
    # ]

    time.sleep(1)
    processed = 0

    for supplier_code, _company_name, _phone_number, _business_address, _email, up_datetime in supplier_codes:
    # for supplier_code in supplier_codes:
        
        try:
           driver.title
        except:
            print("⚠ driver 죽음 → 재시작")
            driver.quit()
            driver = domeggook_login()
            wait = WebDriverWait(driver, 20)

        try:
            alert = driver.switch_to.alert
            print("⚠ alert 발견:", alert.text)
            alert.accept()
        except:
            pass

        processed += 1

        if processed % 200 == 0:
            print("🔄 Driver 재시작")
            driver.quit()
            time.sleep(2)
            driver = domeggook_login()
            wait = WebDriverWait(driver, 20)

        # 공급사코드 입력
        try:
            search_input = wait.until(EC.presence_of_element_located(
                (By.XPATH, "/html/body/header/div[3]/div[2]/div[1]/form/input[5]")
            ))

            driver.execute_script("arguments[0].value = '';", search_input)
            search_input.send_keys(supplier_code)


            # 검색 클릭
            driver.find_element(
                By.XPATH, "/html/body/header/div[3]/div[2]/div[1]/form/button/img"
            ).click()

            time.sleep(5)

            # 총 상품 수 영역 확인
            try:
                try:
                    total_text_elem = WebDriverWait(driver, 5).until(
                        EC.presence_of_element_located(
                            (By.XPATH, "/html/body/div[1]/div[2]/div[8]/div[2]")
                        )
                    )
                except Exception:
                    total_text_elem = ''

                total_text = total_text_elem.text.strip()
                import re
                match = re.search(r'([\d,]+)', total_text)
                product_total_count = int(match.group(1).replace(',', '')) if match else 0

                if product_total_count == 0:
                    print(f"[SKIP] 상품 없음: {supplier_code}")
                    upsert_supplier_info({
                        "supplier_code": supplier_code,
                        "company_name": '상품 없음',
                        "vender_grade": '',
                        "product_total_count": 0,
                        "phone_number": '',
                        "business_address": '',
                        "email": '',
                        "dome": 'DMM',
                        "prod_name": ''
                    })
                    continue
                else:
                    print("총 상품 수:", product_total_count)

                try:
                    products = safe_find(wait, By.XPATH, "/html/body/div[1]/div[2]/div[14]")
                except Exception:
                    products = safe_find(wait, By.XPATH, "/html/body/div[1]/div[2]/div[14]/div[4]/div[3]")

                if not products:
                    print(f"[SKIP] 상품 리스트 없음: {supplier_code}")
                    product_total_count = 0

                # 공급사 등급 (도매꾹은 단순)
                vender_grade = extract_domeggook_grade(driver)

                if vender_grade is None:
                    print("⚠ 공급사 등급 추출 실패")
                    upsert_supplier_info({
                        "supplier_code": supplier_code,
                        "company_name": '공급사 등급 추출 실패',
                        "vender_grade": '',
                        "product_total_count": product_total_count,
                        "phone_number": '',
                        "business_address": '',
                        "email": '',
                        "dome": 'DMM',
                        "prod_name": ''
                    })
                    continue
                else:
                    print("공급사 등급:", vender_grade)

                # prod_name
                prod_name = ''
                elem = wait.until(EC.presence_of_element_located(
                    (By.XPATH, "//div[contains(@class,'itemName') and contains(@class,'cur')]")
                ))
                prod_name = driver.execute_script("""
                    return arguments[0].childNodes[0].textContent.trim();
                """, elem)
                print("상품명:", prod_name)

                #if product_total_count > 0 & (_company_name is None or _company_name == ''):
                if product_total_count > 0:
                    print(f"[NEW] {supplier_code} → 공급사 정보 크롤링 대상")
                    time.sleep(3)
                    # 첫 상품 클릭
                    try:
                        main_window = driver.current_window_handle

                        try:
                            elem = wait.until(EC.presence_of_element_located(
                                (By.XPATH, "/html/body/div[1]/div[2]/div[14]/div[4]/div[3]")
                            ))

                            driver.execute_script("arguments[0].click();", elem)
                        except Exception:
                            continue

                        try:
                            wait.until(lambda d: len(d.window_handles) > 1)
                            new_window = [w for w in driver.window_handles if w != main_window][0]
                            driver.switch_to.window(new_window)
                            time.sleep(2)
                            if is_adult_cert_popup(driver):
                                print(f"[SKIP] 성인 인증 필요 상품: {supplier_code}")
                                driver.close()
                                driver.switch_to.window(main_window)

                                # DB 저장은 최소 정보만 (또는 skip)
                                upsert_supplier_info({
                                    "supplier_code": supplier_code,
                                    "company_name": '성인 인증 필요 상품',
                                    "vender_grade": vender_grade,
                                    "product_total_count": product_total_count,
                                    "phone_number": '',
                                    "business_address": '',
                                    "email": '',
                                    "dome": 'DMM',
                                    "prod_name": prod_name
                                })
                                continue  # 🔥 다음 supplier_code 로 이동

                            # 🔥 상품 없음/삭제 체크
                            if is_product_not_found(driver):
                                print(f"[SKIP][NOT_FOUND] supplier={supplier_code}")

                                driver.close()
                                driver.switch_to.window(main_window)

                                upsert_supplier_info({
                                    "supplier_code": supplier_code,
                                    "company_name": '상품 없음/판매종료/삭제',
                                    "vender_grade": vender_grade,
                                    "product_total_count": product_total_count,
                                    "phone_number": '',
                                    "business_address": '',
                                    "email": '',
                                    "dome": 'DMM',
                                    "prod_name": prod_name
                                })
                                continue

                            # 공급사 정보 탭
                            try:
                                #lPopupAdultCert
                                try:
                                    wait.until(EC.element_to_be_clickable(
                                        (By.XPATH, "/html/body/div[5]/div/div[2]/div[2]/div[2]/div[1]/ul/li[4]/a")
                                    )).click()
                                except Exception:
                                    wait.until(EC.element_to_be_clickable(
                                        (By.XPATH, "/html/body/div[5]/div/div[2]/div[2]/div[2]/div[1]/ul/li[4]/a")
                                    )).click()

                                try:
                                    company_name = wait.until(EC.presence_of_element_located(
                                        (By.XPATH, "/html/body/div[5]/div/div[2]/div[2]/div[1]/div[9]/table[1]/tbody/tr[1]/td[1]")
                                    )).text.strip()
                                except Exception:
                                    company_name = wait.until(EC.presence_of_element_located(
                                        (By.XPATH,
                                         "/html/body/div[5]/div/div[2]/div[2]/div[1]/div[9]/table[1]/tbody/tr[1]/td[1]")
                                    )).text.strip()

                                try:
                                    phone_number = wait.until(EC.presence_of_element_located(
                                        (By.XPATH, "/html/body/div[5]/div/div[2]/div[2]/div[1]/div[9]/table[1]/tbody/tr[4]/td[1]")
                                    )).text.strip()
                                except Exception:
                                    phone_number = wait.until(EC.presence_of_element_located(
                                        (By.XPATH,
                                         "/html/body/div[5]/div/div[2]/div[2]/div[1]/div[9]/table[1]/tbody/tr[2]/td[2]")
                                    )).text.strip()

                                try:
                                    business_address = wait.until(EC.presence_of_element_located(
                                        (By.XPATH, "/html/body/div[5]/div/div[2]/div[2]/div[1]/div[9]/table[1]/tbody/tr[3]/td")
                                    )).text.strip()
                                except Exception:
                                    business_address = wait.until(EC.presence_of_element_located(
                                        (By.XPATH,
                                         "/html/body/div[5]/div/div[2]/div[2]/div[1]/div[9]/table[1]/tbody/tr[3]/td")
                                    )).text.strip()

                                try:
                                    email = wait.until(EC.presence_of_element_located(
                                        (By.XPATH, "/html/body/div[5]/div/div[2]/div[2]/div[1]/div[9]/table[1]/tbody/tr[4]/td[2]")
                                    )).text.strip()
                                except Exception:
                                    email = wait.until(EC.presence_of_element_located(
                                        (By.XPATH,
                                         "/html/body/div[5]/div/div[2]/div[2]/div[1]/div[9]/table[1]/tbody/tr[4]/td[2]")
                                    )).text.strip()

                                print("상호명:", company_name)
                                print("전화번호:", phone_number)
                                print("사업장소재지:", business_address)
                                print("email:", email)

                            except Exception:
                                company_name = ''
                                phone_number = ''
                                business_address = ''
                                email = ''

                            driver.close()
                            driver.switch_to.window(main_window)

                        except Exception:
                            company_name = ''
                            phone_number = ''
                            business_address = ''
                            email = ''

                    except Exception:
                        company_name = ''
                        phone_number = ''
                        business_address = ''
                        email = ''

                else:
                    company_name = _company_name
                    phone_number = _phone_number
                    business_address = _business_address
                    email = _email

                # DB 저장 (기존 함수 재사용)
                upsert_supplier_info({
                    "supplier_code": supplier_code,
                    "company_name": company_name,
                    "vender_grade": vender_grade,
                    "product_total_count": product_total_count,
                    "phone_number": phone_number,
                    "business_address": business_address,
                    "email": email,
                    "dome": 'DMM',
                    "prod_name": prod_name
                })
                print(f"[도매꾹] 저장 완료: {supplier_code}")

            except Exception:
                print(f"[SKIP] 검색 결과 없음: {supplier_code}")
                try:
                    upsert_supplier_info({
                        "supplier_code": supplier_code,
                        "company_name": '검색 결과 없음',
                        "vender_grade": vender_grade,
                        "product_total_count": product_total_count,
                        "phone_number": '',
                        "business_address": '',
                        "email": '',
                        "dome": 'DMM',
                        "prod_name": prod_name
                    })
                except Exception:
                    print(f"DB 저장오류")

        except Exception:
            product_total_count = 0
            print(f"오류발생!! {supplier_code}")
            driver.quit()
            time.sleep(2)
            driver = domeggook_login()
            wait = WebDriverWait(driver, 20)

    driver.quit()


def upsert_supplier_info(data):
    # db 에 추가
    conn = psycopg2.connect(**DB_INFO_PSYCOPG2)
    sql = """
        INSERT INTO mlinkdw.supplier_info (
            supplier_code,
            company_name,
            vender_grade,
            product_total_count,
            phone_number,
            business_address,
            reg_datetime,
            up_datetime,
            email,
            dome,
            prod_name
        )
        VALUES (
            %(supplier_code)s,
            %(company_name)s,
            %(vender_grade)s,
            %(product_total_count)s,
            %(phone_number)s,
            %(business_address)s,
            to_char(now(), 'YYYY-MM-DD HH24:MI:SS'),
            to_char(now(), 'YYYY-MM-DD HH24:MI:SS'),
            %(email)s,
            %(dome)s,
            %(prod_name)s
        )
        ON CONFLICT (dome, supplier_code)
        DO UPDATE SET
            company_name        = EXCLUDED.company_name,
            vender_grade        = EXCLUDED.vender_grade,
            product_total_count = EXCLUDED.product_total_count,
            phone_number        = EXCLUDED.phone_number,
            business_address    = EXCLUDED.business_address,
            up_datetime         = to_char(now(), 'YYYY-MM-DD HH24:MI:SS'),
            email    = EXCLUDED.email,
            prod_name    = EXCLUDED.prod_name;
        """

    try:
        with conn.cursor() as cur:
            cur.execute(sql, data)
        conn.commit()
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        conn.close()

if __name__ == "__main__":
    #SupplierInfoCrawling()  # 오너클랜
    time.sleep(60)
    crawl_domeggook_suppliers()  # 도매꾹