# -*- coding: utf-8 -*-

import os
import sys
import json
import pymysql

CURRENT_DIR = os.path.dirname(os.path.abspath(__file__))
ROOT_DIR = os.path.dirname(CURRENT_DIR)

if ROOT_DIR not in sys.path:
    sys.path.append(ROOT_DIR)

from db import get_conn


class NaverSchoolFetcher:

    def __init__(self):
        self._column_cache = {}

    # ---------------------------------------------------------
    # DB
    # ---------------------------------------------------------

    def get_columns(self, table_name):

        if table_name in self._column_cache:
            return self._column_cache[table_name]

        conn = get_conn()

        try:
            with conn.cursor(pymysql.cursors.DictCursor) as cur:
                cur.execute(f"SHOW COLUMNS FROM {table_name}")
                rows = cur.fetchall()

            columns = set(row["Field"] for row in rows)

            self._column_cache[table_name] = columns

            return columns

        finally:
            conn.close()

    # ---------------------------------------------------------
    # TARGET
    # ---------------------------------------------------------

    def load_targets(self, limit=30):

        conn = get_conn()

        try:
            sql = """
            SELECT
                article_no,
                raw_json
            FROM blog_realtor_articles
            WHERE raw_json IS NOT NULL
            ORDER BY updated_at DESC
            LIMIT %s
            """

            with conn.cursor(pymysql.cursors.DictCursor) as cur:
                cur.execute(sql, (limit,))
                rows = cur.fetchall()

            return rows

        finally:
            conn.close()

    # ---------------------------------------------------------
    # PARSE
    # ---------------------------------------------------------

    def walk_find_school_nodes(self, obj, result):

        if isinstance(obj, dict):

            keys = obj.keys()

            school_keywords = [
                "school",
                "academy",
                "edu",
                "교육",
                "학교",
                "학군",
            ]

            joined = " ".join([
                str(k).lower()
                for k in keys
            ])

            if any(k in joined for k in school_keywords):

                if (
                    "schoolName" in obj
                    or "name" in obj
                ):
                    result.append(obj)

            for value in obj.values():
                self.walk_find_school_nodes(
                    value,
                    result
                )

        elif isinstance(obj, list):

            for item in obj:
                self.walk_find_school_nodes(
                    item,
                    result
                )

    def parse_school_items(self, raw_json):

        if not raw_json:
            return []

        try:
            data = json.loads(raw_json)
        except Exception:
            return []

        found = []

        self.walk_find_school_nodes(
            data,
            found
        )

        result = []
        seen = set()

        for item in found:

            school_name = (
                item.get("schoolName")
                or item.get("name")
                or ""
            ).strip()

            if not school_name:
                continue

            school_type = (
                item.get("schoolType")
                or item.get("type")
                or ""
            ).strip()

            distance_text = (
                item.get("distance")
                or item.get("distanceText")
                or ""
            ).strip()

            address = (
                item.get("address")
                or ""
            ).strip()

            tel = (
                item.get("tel")
                or item.get("phone")
                or ""
            ).strip()

            homepage = (
                item.get("homepage")
                or item.get("site")
                or ""
            ).strip()

            latitude = (
                item.get("latitude")
                or item.get("lat")
                or None
            )

            longitude = (
                item.get("longitude")
                or item.get("lon")
                or item.get("lng")
                or None
            )

            key = f"{school_name}|{school_type}"

            if key in seen:
                continue

            seen.add(key)

            result.append({
                "school_name": school_name,
                "school_type": school_type,
                "distance_text": distance_text,
                "distance_meter": None,
                "address": address,
                "tel": tel,
                "homepage": homepage,
                "latitude": latitude,
                "longitude": longitude,
                "school_level": school_type,
                "sort_order": 0,
                "raw_json": json.dumps(
                    item,
                    ensure_ascii=False
                )
            })

        return result

    # ---------------------------------------------------------
    # SAVE
    # ---------------------------------------------------------

    def save_school_items(
        self,
        article_no,
        items
    ):

        if not items:
            print("[SCHOOL SAVE] empty")
            return

        columns = self.get_columns(
            "blog_realestate_article_schools"
        )

        conn = get_conn()

        try:
            saved = 0

            with conn.cursor() as cur:

                for idx, item in enumerate(items):

                    data = {
                        "article_no": article_no,
                        "school_name": item.get("school_name", ""),
                        "school_type": item.get("school_type", ""),
                        "distance_text": item.get("distance_text", ""),
                        "distance_meter": item.get("distance_meter"),
                        "address": item.get("address", ""),
                        "tel": item.get("tel", ""),
                        "homepage": item.get("homepage", ""),
                        "latitude": item.get("latitude"),
                        "longitude": item.get("longitude"),
                        "school_level": item.get("school_level", ""),
                        "sort_order": idx + 1,
                        "raw_json": item.get("raw_json", ""),
                    }

                    filtered = {
                        key: value
                        for key, value in data.items()
                        if key in columns
                    }

                    if not filtered:
                        continue

                    keys = list(filtered.keys())

                    placeholders = ", ".join(
                        ["%s"] * len(keys)
                    )

                    column_sql = ", ".join(keys)

                    values = [
                        filtered[key]
                        for key in keys
                    ]

                    update_sql = ", ".join([
                        f"{key}=VALUES({key})"
                        for key in keys
                        if key not in [
                            "id",
                            "created_at"
                        ]
                    ])

                    sql = f"""
                    INSERT INTO blog_realestate_article_schools
                    ({column_sql})
                    VALUES ({placeholders})
                    ON DUPLICATE KEY UPDATE
                    {update_sql}
                    """

                    cur.execute(sql, values)

                    saved += 1

            conn.commit()

            print("[SCHOOL SAVE]", saved)

        finally:
            conn.close()

    # ---------------------------------------------------------
    # RUN
    # ---------------------------------------------------------

    def run(self, limit=30):

        targets = self.load_targets(limit)

        print("=" * 80)
        print("[TARGET ARTICLES]", len(targets))
        print("=" * 80)

        for row in targets:

            article_no = row["article_no"]

            print("-" * 80)
            print("[ARTICLE]", article_no)

            items = self.parse_school_items(
                row.get("raw_json")
            )

            print("[FOUND SCHOOLS]", len(items))

            self.save_school_items(
                article_no,
                items
            )

        print("=" * 80)
        print("[DONE]")
        print("=" * 80)


def main():

    fetcher = NaverSchoolFetcher()

    fetcher.run(limit=30)


if __name__ == "__main__":
    main()