import os
from pathlib import Path

import mysql.connector


def load_env_file():
    env_path = Path(__file__).resolve().parent.parent / ".env"
    if not env_path.exists():
        return

    for raw_line in env_path.read_text(encoding="utf-8").splitlines():
        line = raw_line.strip()
        if not line or line.startswith("#") or "=" not in line:
            continue
        key, value = line.split("=", 1)
        os.environ.setdefault(key.strip(), value.strip().strip('"').strip("'"))


load_env_file()

DB_CONFIG = {
    "host": os.environ.get("DB_HOST", "officialsite.kr"),
    "port": int(os.environ.get("DB_PORT", "23306")),
    "user": os.environ.get("DB_USER", "admin"),
    "password": os.environ.get("DB_PASSWORD", ""),
    "database": os.environ.get("DB_NAME", "app_master"),
}

COUNT_MISSING_QUERY = """
SELECT COUNT(*)
FROM babynote_family_members fm
JOIN babynote_baby_info bi
  ON bi.user_id = fm.user_id
 AND bi.family_id = fm.family_id
WHERE NULLIF(TRIM(fm.relationship), '') IS NULL
  AND NULLIF(TRIM(bi.guardian_title), '') IS NOT NULL
"""

BACKFILL_QUERY = """
UPDATE babynote_family_members fm
JOIN babynote_baby_info bi
  ON bi.user_id = fm.user_id
 AND bi.family_id = fm.family_id
SET fm.relationship = CASE
  WHEN LOWER(REPLACE(bi.guardian_title, ' ', '')) REGEXP
       '할아버지|grandfather|grandpa|おじいちゃん|祖父|爷爷|外公|姥爷'
    THEN '할아버지입니다.'
  WHEN LOWER(REPLACE(bi.guardian_title, ' ', '')) REGEXP
       '할머니|grandmother|grandma|おばあちゃん|祖母|奶奶|外婆|姥姥'
    THEN '할머니입니다.'
  WHEN LOWER(REPLACE(bi.guardian_title, ' ', '')) REGEXP
       '아빠|아버지|father|dad|パパ|お父さん|爸爸|父亲'
    THEN '아빠입니다.'
  WHEN LOWER(REPLACE(bi.guardian_title, ' ', '')) REGEXP
       '엄마|어머니|mother|mom|mum|ママ|お母さん|妈妈|母亲'
    THEN '엄마입니다.'
  WHEN LOWER(REPLACE(bi.guardian_title, ' ', '')) REGEXP
       '시터|돌보미|sitter|caregiver|シッター|保姆'
    THEN '시터입니다.'
  ELSE LEFT(TRIM(bi.guardian_title), 50)
END
WHERE NULLIF(TRIM(fm.relationship), '') IS NULL
  AND NULLIF(TRIM(bi.guardian_title), '') IS NOT NULL
"""


def run():
    connection = None
    cursor = None
    try:
        connection = mysql.connector.connect(**DB_CONFIG)
        cursor = connection.cursor()
        cursor.execute(COUNT_MISSING_QUERY)
        before_count = int(cursor.fetchone()[0])

        cursor.execute(BACKFILL_QUERY)
        updated_count = cursor.rowcount
        connection.commit()

        cursor.execute(COUNT_MISSING_QUERY)
        remaining_count = int(cursor.fetchone()[0])
        print(
            "OK: family relationships backfilled "
            f"before={before_count} updated={updated_count} remaining={remaining_count}"
        )
    finally:
        if cursor is not None:
            cursor.close()
        if connection is not None and connection.is_connected():
            connection.close()


if __name__ == "__main__":
    run()