"""
naver_apt_crawler.py
────────────────────
원본 Selenium UI 방식 기반 + 개선판

개선 내용 (원본 대비):
  - 단지마다 즉시 DB 저장 (원본 방식 유지)
  - crawl_progress 테이블로 중단 후 재시작 지원
  - logging 모듈로 파일+콘솔 동시 출력
  - complex_no 컬럼 저장
"""

import re
import time
import random
import logging
import pymysql
import undetected_chromedriver as uc
from datetime import datetime
from selenium.webdriver.common.by import By
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from selenium.webdriver.common.action_chains import ActionChains
from selenium.webdriver.common.keys import Keys
from selenium.common.exceptions import TimeoutException, NoSuchElementException

# ══════════════════════════════════════════════
#  로깅
# ══════════════════════════════════════════════

logging.basicConfig(
    level=logging.INFO,
    format="%(asctime)s [%(levelname)s] %(message)s",
    handlers=[
        logging.StreamHandler(),
        logging.FileHandler("crawler.log", encoding="utf-8"),
    ],
)
log = logging.getLogger(__name__)

# ══════════════════════════════════════════════
#  설정
# ══════════════════════════════════════════════

LEVEL_SIDO = "FAS.sido"
LEVEL_GU   = "FAS.gu"
LEVEL_DONG = "FAS.dong"

DB_CONFIG = {
    "host":     "hongheemarketing.com",
    "port":     3306,
    "user":     "pjjh030551",
    "password": "ghdgml7655!1",
    "database": "pjjh030551",
    "charset":  "utf8mb4",
}

# ══════════════════════════════════════════════
#  DB
# ══════════════════════════════════════════════

def get_conn():
    return pymysql.connect(**DB_CONFIG)


def init_db():
    conn = get_conn()
    try:
        with conn.cursor() as cur:
            cur.execute("""
                CREATE TABLE IF NOT EXISTS crawl_list (
                    id             INT AUTO_INCREMENT PRIMARY KEY,
                    sido           VARCHAR(50)  NOT NULL COMMENT '시/도',
                    sigungu        VARCHAR(50)  NOT NULL COMMENT '시/군/구',
                    dong           VARCHAR(50)  NOT NULL COMMENT '읍/면/동',
                    name           VARCHAR(100) NOT NULL COMMENT '단지명',
                    type           VARCHAR(30)           COMMENT '유형',
                    households     INT                   COMMENT '세대수(숫자)',
                    households_raw VARCHAR(50)           COMMENT '세대수 원본',
                    addr_jibun     VARCHAR(200)          COMMENT '지번주소',
                    addr_road      VARCHAR(200)          COMMENT '도로명주소',
                    complex_no     VARCHAR(30)           COMMENT '네이버 단지 번호',
                    crawled_at     DATETIME DEFAULT CURRENT_TIMESTAMP,
                    updated_at     DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    deleted_at     DATETIME NULL DEFAULT NULL COMMENT '소프트 삭제일시',
                    UNIQUE KEY uq_complex (sido, sigungu, dong, name)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
            """)
            cur.execute("""
                CREATE TABLE IF NOT EXISTS crawl_logs (
                    id          INT AUTO_INCREMENT PRIMARY KEY,
                    started_at  DATETIME,
                    finished_at DATETIME,
                    total_count INT DEFAULT 0,
                    status      VARCHAR(20) DEFAULT 'running'
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
            """)
            # 스키마 변경 대응: 항상 재생성
            cur.execute("DROP TABLE IF EXISTS crawl_progress")
            cur.execute("""
                CREATE TABLE crawl_progress (
                    id         INT AUTO_INCREMENT PRIMARY KEY,
                    region_key VARCHAR(150) NOT NULL UNIQUE COMMENT '완료된 동 키',
                    saved      INT DEFAULT 0,
                    done_at    DATETIME DEFAULT CURRENT_TIMESTAMP
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
            """)
            cur.execute("""
                ALTER TABLE crawl_list
                ADD COLUMN IF NOT EXISTS deleted_at DATETIME NULL DEFAULT NULL
                COMMENT '소프트 삭제일시'
            """)
        conn.commit()
        log.info("✅ DB 준비 완료")
    finally:
        conn.close()


def start_log():
    conn = get_conn()
    try:
        with conn.cursor() as cur:
            cur.execute("INSERT INTO crawl_logs (started_at) VALUES (%s)", (datetime.now(),))
            conn.commit()
            return cur.lastrowid
    finally:
        conn.close()


def finish_log(log_id, total, status="done"):
    conn = get_conn()
    try:
        with conn.cursor() as cur:
            cur.execute(
                "UPDATE crawl_logs SET finished_at=%s, total_count=%s, status=%s WHERE id=%s",
                (datetime.now(), total, status, log_id),
            )
        conn.commit()
    finally:
        conn.close()


def is_done(region_key: str) -> bool:
    conn = get_conn()
    try:
        with conn.cursor() as cur:
            cur.execute("SELECT 1 FROM crawl_progress WHERE region_key = %s", (region_key,))
            return cur.fetchone() is not None
    finally:
        conn.close()


def mark_done(region_key: str, saved: int):
    conn = get_conn()
    try:
        with conn.cursor() as cur:
            cur.execute(
                "INSERT IGNORE INTO crawl_progress (region_key, saved) VALUES (%s, %s)",
                (region_key, saved),
            )
        conn.commit()
    finally:
        conn.close()


def save_complex(sido, sigungu, dong, name, apt_type, households_raw,
                 addr_jibun, addr_road, complex_no=""):
    """단지 1건 즉시 저장. 이미 있으면 UPDATE."""
    m = re.search(r"(\d+)세대", households_raw or "")
    households = int(m.group(1)) if m else None

    sql = """
    INSERT INTO crawl_list
        (sido, sigungu, dong, name, type, households, households_raw,
         addr_jibun, addr_road, complex_no)
    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
    ON DUPLICATE KEY UPDATE
        type           = VALUES(type),
        households     = VALUES(households),
        households_raw = VALUES(households_raw),
        addr_jibun     = VALUES(addr_jibun),
        addr_road      = VALUES(addr_road),
        complex_no     = VALUES(complex_no),
        deleted_at     = NULL,
        updated_at     = CURRENT_TIMESTAMP
    """
    conn = get_conn()
    try:
        with conn.cursor() as cur:
            cur.execute(sql, (
                sido, sigungu, dong, name,
                apt_type or None,
                households,
                households_raw or None,
                addr_jibun or None,
                addr_road or None,
                complex_no or None,
            ))
        conn.commit()
    finally:
        conn.close()


# ══════════════════════════════════════════════
#  Selenium 유틸 (원본 그대로)
# ══════════════════════════════════════════════

def human_delay(lo=0.8, hi=1.5):
    time.sleep(random.uniform(lo, hi))


def dismiss_alert(driver):
    try:
        WebDriverWait(driver, 1.5).until(EC.alert_is_present())
        driver.switch_to.alert.accept()
        time.sleep(0.5)
    except TimeoutException:
        pass


def get_popup(driver):
    return driver.find_element(By.CSS_SELECTOR, ".filter_popup--area")


def is_popup_open(driver):
    return get_popup(driver).get_attribute("aria-hidden") == "false"


def get_current_level(driver):
    try:
        popup = get_popup(driver)
        radios = popup.find_elements(By.CSS_SELECTOR, "li.area_item input.radio_input")
        return radios[0].get_attribute("data-nclk") if radios else None
    except Exception:
        return None


def wait_for_level(driver, expected, timeout=8):
    try:
        WebDriverWait(driver, timeout).until(lambda d: get_current_level(d) == expected)
        return True
    except TimeoutException:
        log.warning(f"    ⚠️  레벨 대기 실패: 기대={expected}, 현재={get_current_level(driver)}")
        return False


def get_current_items(driver):
    return get_popup(driver).find_elements(By.CSS_SELECTOR, "li.area_item")


def click_label_item(driver, item):
    label = item.find_element(By.CSS_SELECTOR, "label")
    ActionChains(driver).move_to_element(label).click().perform()
    human_delay(0.8, 1.2)


def open_filter_at_sido(driver) -> bool:
    """팝업을 열고 시/도 레벨로 이동. 실패 시 False."""
    # 이미 열려있으면 닫기
    try:
        if is_popup_open(driver):
            driver.find_element(By.TAG_NAME, "body").send_keys(Keys.ESCAPE)
            human_delay(0.4, 0.6)
    except Exception:
        pass

    # ActionChains로 실제 마우스 클릭 (execute_script click은 팝업 안 열림)
    try:
        btn = driver.find_element(By.CSS_SELECTOR, "a.filter_btn_region")
        ActionChains(driver).move_to_element(btn).click().perform()
        human_delay(1.0, 1.5)
    except Exception as e:
        log.warning(f"    필터 버튼 없음: {e}")
        return False

    # 팝업 열릴 때까지 대기
    try:
        WebDriverWait(driver, 5).until(lambda d: is_popup_open(d))
    except TimeoutException:
        return False

    # 시/도 레벨이 아니면 브레드크럼 맨 앞으로 이동
    for _ in range(5):
        if get_current_level(driver) == LEVEL_SIDO:
            return True
        try:
            bcs = get_popup(driver).find_elements(By.CSS_SELECTOR, "a.area_select_item")
            if bcs:
                ActionChains(driver).move_to_element(bcs[0]).click().perform()
                human_delay(0.6, 1.0)
            else:
                break
        except Exception:
            break

    return get_current_level(driver) == LEVEL_SIDO


def navigate_to_complex_list(driver, sido_idx, sg_idx, dong_idx):
    """시/도 → 구 → 동 선택 후 단지 목록 팝업 반환. 실패 시 None."""
    # 최대 2회 시도
    for attempt in range(2):
        if not open_filter_at_sido(driver):
            human_delay(1.5, 2.0)
            continue

        sido_items = get_current_items(driver)
        if sido_idx >= len(sido_items):
            return None

        sido_name = sido_items[sido_idx].find_element(By.CSS_SELECTOR, "label").text.strip()
        click_label_item(driver, sido_items[sido_idx])
        if not wait_for_level(driver, LEVEL_GU):
            human_delay(1.5, 2.0)
            continue

        sg_items = get_current_items(driver)
        if sg_idx >= len(sg_items):
            return None
        sg_name = sg_items[sg_idx].find_element(By.CSS_SELECTOR, "label").text.strip()
        click_label_item(driver, sg_items[sg_idx])
        if not wait_for_level(driver, LEVEL_DONG):
            continue

        dong_items = get_current_items(driver)
        if dong_idx >= len(dong_items):
            return None
        dong_name = dong_items[dong_idx].find_element(By.CSS_SELECTOR, "label").text.strip()
        click_label_item(driver, dong_items[dong_idx])
        human_delay(1.0, 1.5)

        # 단지 목록 로딩 대기
        try:
            WebDriverWait(driver, 8).until(
                lambda d: is_popup_open(d) and len(
                    get_popup(d).find_elements(By.CSS_SELECTOR, "ul.area_list--complex li.complex_item")
                ) > 0
            )
            return sido_name, sg_name, dong_name
        except TimeoutException:
            return sido_name, sg_name, dong_name  # 단지 없는 동

    return None


def close_detail_panel(driver):
    """상세 패널 닫기 (여러 방법 시도)."""
    # 닫기 버튼 시도
    for sel in [
        "button.btn_close[aria-label='상세페이지 닫기']",
        "button.btn_close",
        ".detail_panel .btn_close",
    ]:
        try:
            btn = WebDriverWait(driver, 3).until(EC.element_to_be_clickable((By.CSS_SELECTOR, sel)))
            driver.execute_script("arguments[0].click()", btn)
            human_delay(0.5, 0.8)
            return True
        except Exception:
            continue
    # ESC 폴백
    try:
        driver.find_element(By.TAG_NAME, "body").send_keys(Keys.ESCAPE)
        human_delay(0.5, 0.8)
    except Exception:
        pass
    return False


def wait_for_detail(driver, timeout=8):
    try:
        WebDriverWait(driver, timeout).until(
            lambda d: len(d.find_elements(By.CSS_SELECTOR, "div.detail_box--complex")) > 0
        )
        return True
    except TimeoutException:
        return False


def scrape_detail(driver):
    result = {"name": "", "type": "", "households": "", "addr_jibun": "", "addr_road": ""}

    try:
        result["name"] = driver.find_element(
            By.CSS_SELECTOR, "div.detail_contents_inner h4.heading"
        ).text.strip()
    except NoSuchElementException:
        pass

    try:
        dl = driver.find_element(By.CSS_SELECTOR, "dl.complex_feature")
        for dt, dd in zip(dl.find_elements(By.CSS_SELECTOR, "dt"),
                          dl.find_elements(By.CSS_SELECTOR, "dd")):
            if dt.text.strip() == "유형":
                result["type"] = dd.text.strip()
                break
    except NoSuchElementException:
        pass

    try:
        for row in driver.find_elements(By.CSS_SELECTOR, "div.detail_box--complex tr.info_table_item"):
            for th, td in zip(row.find_elements(By.CSS_SELECTOR, "th.table_th"),
                              row.find_elements(By.CSS_SELECTOR, "td.table_td")):
                if th.text.strip() == "세대수":
                    result["households"] = td.text.strip()
    except Exception:
        pass

    try:
        for p in driver.find_elements(By.CSS_SELECTOR, "td.table_td p.address"):
            if p.find_elements(By.CSS_SELECTOR, "i.icon_roadname"):
                result["addr_road"] = p.text.strip()
            else:
                result["addr_jibun"] = p.text.strip()
    except Exception:
        pass

    return result


# ══════════════════════════════════════════════
#  단지 수집 + 즉시 DB 저장 (원본 방식)
# ══════════════════════════════════════════════

def collect_all_complexes(driver, sido_idx, sg_idx, dong_idx):
    region = navigate_to_complex_list(driver, sido_idx, sg_idx, dong_idx)
    if not region:
        return 0
    sido_name, sg_name, dong_name = region
    region_key = f"{sido_name}|{sg_name}|{dong_name}"

    # 이미 완료된 동 스킵
    if is_done(region_key):
        log.info(f"    ✓ 스킵 (완료됨): {dong_name}")
        driver.find_element(By.TAG_NAME, "body").send_keys(Keys.ESCAPE)
        human_delay(0.4, 0.7)
        return 0

    popup = get_popup(driver)
    items = popup.find_elements(By.CSS_SELECTOR, "ul.area_list--complex li.complex_item")
    total = len(items)

    if total == 0:
        log.info(f"    ◎ {sido_name} > {sg_name} > {dong_name} | 단지 없음")
        driver.find_element(By.TAG_NAME, "body").send_keys(Keys.ESCAPE)
        human_delay(0.4, 0.7)
        mark_done(region_key, 0)
        return 0

    log.info(f"    ◎ {sido_name} > {sg_name} > {dong_name} | 단지 {total}개")

    # 이름/유형 미리 수집 (클릭 없이)
    preview = []
    for item in items:
        try:
            name = item.find_element(By.CSS_SELECTOR, "span.complex_title").text.strip()
            apt_type = item.find_element(By.CSS_SELECTOR, "strong.sale_type").text.strip()
        except NoSuchElementException:
            name, apt_type = "", ""
        preview.append({"name": name, "type": apt_type})

    saved = 0
    for idx in range(total):
        name_hint = preview[idx]["name"]
        type_hint = preview[idx]["type"]
        log.info(f"      [{idx+1}/{total}] {name_hint}")

        # idx=0: 팝업이 이미 열려있음
        # idx>0: 동으로 재탐색 (원본 방식)
        if idx == 0:
            popup = get_popup(driver)
            items_now = popup.find_elements(By.CSS_SELECTOR, "ul.area_list--complex li.complex_item")
        else:
            result = navigate_to_complex_list(driver, sido_idx, sg_idx, dong_idx)
            if not result:
                log.warning(f"        ❌ 재탐색 실패 — 스킵")
                continue
            popup = get_popup(driver)
            items_now = popup.find_elements(By.CSS_SELECTOR, "ul.area_list--complex li.complex_item")

        if idx >= len(items_now):
            log.warning(f"        ❌ 항목 없음")
            continue

        # 클릭
        try:
            link = items_now[idx].find_element(By.CSS_SELECTOR, "a.complex_item_inner")
            driver.execute_script("arguments[0].scrollIntoView({block:'center'});", link)
            human_delay(0.3, 0.5)
            driver.execute_script("arguments[0].click()", link)
        except Exception as e:
            log.warning(f"        ⚠️  클릭 실패: {e}")
            continue

        human_delay(1.2, 2.0)

        if not wait_for_detail(driver):
            log.warning(f"        ⚠️  상세 로드 실패 — 스킵")
            close_detail_panel(driver)
            continue

        detail = scrape_detail(driver)
        if not detail["name"]: detail["name"] = name_hint
        if not detail["type"]: detail["type"] = type_hint

        log.info(f"        [{detail['type']}] {detail['name']} | {detail['households']}")

        # ── 단지마다 즉시 DB 저장 ──
        try:
            save_complex(
                sido=sido_name, sigungu=sg_name, dong=dong_name,
                name=detail["name"], apt_type=detail["type"],
                households_raw=detail["households"],
                addr_jibun=detail["addr_jibun"],
                addr_road=detail["addr_road"],
                complex_no="",
            )
            saved += 1
            log.info(f"        💾 저장 완료")
        except Exception as e:
            log.warning(f"        ⚠️  DB 저장 실패: {e}")

        close_detail_panel(driver)
        human_delay(0.5, 1.0)

    mark_done(region_key, saved)
    log.info(f"      ✅ {saved}/{total}건 저장 완료\n")
    return saved



# ══════════════════════════════════════════════
#  소프트 삭제
# ══════════════════════════════════════════════

def soft_delete_missing(log_id: int):
    """
    이번 크롤링 시작 전에 updated_at이 갱신되지 않은 단지 → deleted_at = NOW()
    다음 크롤링에서 재발견 시 deleted_at = NULL 로 자동 복구됨
    """
    conn = get_conn()
    try:
        with conn.cursor() as cur:
            cur.execute("SELECT started_at FROM crawl_logs WHERE id = %s", (log_id,))
            row = cur.fetchone()
            if not row:
                return
            started_at = row[0]

        with conn.cursor() as cur:
            cur.execute(
                "UPDATE crawl_list SET deleted_at = NOW() "
                "WHERE deleted_at IS NULL AND updated_at < %s",
                (started_at,)
            )
            deleted = cur.rowcount
        conn.commit()

        if deleted > 0:
            log.info(f"🗑️  소프트 삭제: {deleted}건 (이번 크롤링 미발견)")
        else:
            log.info("✅ 소프트 삭제 대상 없음")
    except Exception as e:
        log.warning(f"⚠️  소프트 삭제 실패: {e}")
    finally:
        conn.close()

# ══════════════════════════════════════════════
#  전국 순회 (원본 방식)
# ══════════════════════════════════════════════

def crawl_all(driver):
    log_id = start_log()
    grand_total = 0

    try:
        if not open_filter_at_sido(driver):
            log.error("❌ 시/도 목록 열기 실패")
            return

        sido_count = len(get_current_items(driver))
        driver.find_element(By.TAG_NAME, "body").send_keys(Keys.ESCAPE)
        human_delay(0.5, 1.0)
        log.info(f"시/도 {sido_count}개 확인\n")

        for sido_idx in range(sido_count):
            # 시/군/구 수 파악
            for attempt in range(2):
                if not open_filter_at_sido(driver):
                    human_delay(2.0, 3.0)
                    continue
                sido_items = get_current_items(driver)
                sido_name = sido_items[sido_idx].find_element(By.CSS_SELECTOR, "label").text.strip()
                click_label_item(driver, sido_items[sido_idx])
                if wait_for_level(driver, LEVEL_GU):
                    break
                log.warning(f"  ⚠️  {sido_name} 구 레벨 실패, 재시도...")
                human_delay(2.0, 3.0)
            else:
                log.warning(f"  ⚠️  sido_idx={sido_idx} 스킵")
                continue

            sg_count = len(get_current_items(driver))
            driver.find_element(By.TAG_NAME, "body").send_keys(Keys.ESCAPE)
            human_delay(0.5, 1.0)
            log.info(f"\n▶ {sido_name} | 시/군/구 {sg_count}개")

            for sg_idx in range(sg_count):
                # 읍/면/동 수 파악
                if not open_filter_at_sido(driver): continue
                sido_items = get_current_items(driver)
                click_label_item(driver, sido_items[sido_idx])
                if not wait_for_level(driver, LEVEL_GU): continue

                sg_items = get_current_items(driver)
                sg_name = sg_items[sg_idx].find_element(By.CSS_SELECTOR, "label").text.strip()
                click_label_item(driver, sg_items[sg_idx])
                if not wait_for_level(driver, LEVEL_DONG): continue

                dong_count = len(get_current_items(driver))
                driver.find_element(By.TAG_NAME, "body").send_keys(Keys.ESCAPE)
                human_delay(0.5, 1.0)
                log.info(f"  ▷ {sg_name} | 읍/면/동 {dong_count}개")

                for dong_idx in range(dong_count):
                    saved = collect_all_complexes(driver, sido_idx, sg_idx, dong_idx)
                    grand_total += saved
                    human_delay(1.0, 2.0)

        finish_log(log_id, grand_total, "done")
        log.info(f"\n🎉 전국 완료! 총 {grand_total}건 저장")
        soft_delete_missing(log_id)

    except KeyboardInterrupt:
        finish_log(log_id, grand_total, "interrupted")
        log.info(f"\n⏸ 중단. 저장: {grand_total}건")
    except Exception:
        import traceback
        traceback.print_exc()
        finish_log(log_id, grand_total, "error")


# ══════════════════════════════════════════════
#  메인
# ══════════════════════════════════════════════

def main():
    print("🏠 네이버 부동산 단지 크롤러\n")
    init_db()

    options = uc.ChromeOptions()
    options.add_argument("--window-size=400,300")   # 작은 창
    driver = uc.Chrome(options=options, version_main=147)

    # 화면 오른쪽 하단 구석으로 이동 (렌더링 유지, 방해 최소화)
    driver.set_window_position(1500, 800)



    try:
        driver.get("https://new.land.naver.com/complexes")
        human_delay(5.0, 6.0)  # 페이지 완전 로딩 대기
        dismiss_alert(driver)
        # 필터 버튼이 실제로 클릭 가능할 때까지 추가 대기
        try:
            WebDriverWait(driver, 15).until(
                EC.element_to_be_clickable((By.CSS_SELECTOR, "a.filter_btn_region"))
            )
        except Exception:
            pass
        log.info("✅ 접속 완료\n")


        # ── 테스트: 서울시(0) → 강남구(0) → 개포동(0) ──────────
        # saved = collect_all_complexes(driver, sido_idx=0, sg_idx=0, dong_idx=0)
        # log.info(f"\n테스트 완료: {saved}건 DB 저장")
        # input("확인 후 Enter...")
        # ─────────────────────────────────────────────────────────

        crawl_all(driver)
        input("\n▶ Enter → 종료")

    except Exception:
        import traceback
        traceback.print_exc()
        input("Enter → 종료")
    finally:
        driver.quit()
        log.info("👋 종료")


if __name__ == "__main__":
    main()