import os
from pathlib import Path
from datetime import timedelta
from collections import defaultdict

import mysql.connector


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", "dlgks~123"),
    "database": os.environ.get("DB_NAME", "app_master"),
}


SELECT_TARGETS_SQL = """
SELECT
  u.id AS user_id,
  u.email,
  u.name,
  bi.id AS baby_info_id,
  bi.baby_name,
  ip.created_at,
  ip.baby_age_months
FROM users u
JOIN babynote_insight_posts ip ON ip.user_id = u.id
JOIN babynote_baby_info bi ON bi.user_id = u.id
WHERE u.app_name = 'babynote'
  AND LOWER(COALESCE(u.email, '')) LIKE 'test_babynote_%@test.com'
  AND bi.birth_date IS NULL
  AND ip.baby_age_months IS NOT NULL
ORDER BY u.email, ip.created_at ASC
"""


UPDATE_SQL = """
UPDATE babynote_baby_info
SET birth_date = %s, updated_at = CURRENT_TIMESTAMP
WHERE id = %s
"""


def estimate_birth_date(records):
    """
    Insight posts store baby_age_months as:
      floor((created_at - birth_date) / 30 days)
    We search for the date that satisfies the most records.
    """
    exact_candidates = [
        created_at.date() - timedelta(days=int(age_months) * 30)
        for created_at, age_months in records
    ]
    min_candidate = min(exact_candidates) - timedelta(days=35)
    max_candidate = max(exact_candidates) + timedelta(days=35)

    best_date = None
    best_score = -1
    best_distance = None
    target_ordinal = sum(d.toordinal() for d in exact_candidates) / len(exact_candidates)

    current = min_candidate
    while current <= max_candidate:
        score = 0
        for created_at, age_months in records:
            day_diff = (created_at.date() - current).days
            if day_diff >= 0 and (day_diff // 30) == int(age_months):
                score += 1
        distance = abs(current.toordinal() - target_ordinal)
        if score > best_score or (score == best_score and (best_distance is None or distance < best_distance)):
            best_score = score
            best_distance = distance
            best_date = current
        current += timedelta(days=1)

    return best_date, best_score, len(records)


def load_env():
    env_path = Path(__file__).resolve().parents[1] / ".env"
    if not env_path.exists():
        return
    for raw in env_path.read_text(encoding="utf-8").splitlines():
        line = raw.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("'"))


def main():
    load_env()

    conn = mysql.connector.connect(**DB_CONFIG)
    cursor = conn.cursor(dictionary=True)

    cursor.execute(SELECT_TARGETS_SQL)
    grouped = defaultdict(list)
    meta = {}
    for row in cursor.fetchall():
        grouped[row["user_id"]].append((row["created_at"], row["baby_age_months"]))
        meta[row["user_id"]] = row

    updated_count = 0
    for user_id, records in grouped.items():
        birth_date, matched, total = estimate_birth_date(records)
        info = meta[user_id]
        cursor.execute(UPDATE_SQL, (birth_date, info["baby_info_id"]))
        updated_count += 1
        print(
            f"UPDATED {info['email']} / {info['baby_name']} -> {birth_date} "
            f"(matched {matched}/{total})"
        )

    conn.commit()
    print(f"\nDone. updated={updated_count}")

    cursor.close()
    conn.close()


if __name__ == "__main__":
    main()