insurance/10 Tests/2026-01-16 2.py

#!/usr/bin/env python3
# -*- coding: utf-8 -*-

"""
Najde přesné datum změny stavu pojištění pomocí binary search.
Používá existující VZP B2B klient + ukládá výsledky do MySQL.

Cíl:
- minimalizovat počet dotazů na VZP
- přesně určit první den, kdy stav != '1'
"""

import sys
import time
import logging
from pathlib import Path
from datetime import date, timedelta
import pymysql

# ==========================================
# PROJECT ROOT (import fix)
# ==========================================
PROJECT_ROOT = Path(__file__).resolve().parent.parent
sys.path.insert(0, str(PROJECT_ROOT))

from knihovny.medicus_db import MedicusDB
from knihovny.vzpb2b_client import VZPB2BClient

# ==========================================
# LOGGING
# ==========================================
logging.basicConfig(
    filename="insurance_binary_search.log",
    level=logging.INFO,
    format="%(asctime)s [%(levelname)s] %(message)s",
    encoding="utf-8"
)

console = logging.getLogger("console")
console.setLevel(logging.INFO)
handler = logging.StreamHandler()
handler.setFormatter(logging.Formatter("%(message)s"))
console.addHandler(handler)

def log(msg):
    logging.info(msg)
    console.info(msg)

# ==========================================
# MYSQL
# ==========================================
mysql = pymysql.connect(
    host="192.168.1.76",
    port=3307,
    user="root",
    password="Vlado9674+",
    database="medevio",
    charset="utf8mb4",
    autocommit=True,
    cursorclass=pymysql.cursors.DictCursor
)

# ==========================================
# SAVE RESULT
# ==========================================
def save_insurance_status(mysql_conn, rc, prijmeni, jmeno, k_datu, result, xml):
    sql = """
    INSERT INTO vzp_stav_pojisteni
        (rc, prijmeni, jmeno, k_datu,
         stav, kod_pojistovny, nazev_pojistovny,
         pojisteni_kod, stav_vyrizeni, response_xml)
    VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
    """
    with mysql_conn.cursor() as cur:
        cur.execute(sql, (
            rc,
            prijmeni,
            jmeno,
            k_datu,
            result["stav"],
            result["kodPojistovny"],
            result["nazevPojistovny"],
            result["pojisteniKod"],
            result["stavVyrizeni"],
            xml
        ))

# ==========================================
# VZP CONFIG
# ==========================================
HOST = "192.168.1.4"
DB_PATH = r"z:\Medicus 3\data\MEDICUS.FDB"

PFX_PATH = PROJECT_ROOT / "certificates" / "MBcert.pfx"
PFX_PASSWORD = "Vlado7309208104++"

ENV = "prod"
ICZ = "00000000"
DIC = "00000000"

# ==========================================
# INIT
# ==========================================
db = MedicusDB(HOST, DB_PATH)
vzp = VZPB2BClient(
    ENV,
    str(PFX_PATH),
    PFX_PASSWORD,
    icz=ICZ,
    dic=DIC
)

# ==========================================
# HELPER FUNCTIONS
# ==========================================
def vzp_query(rc, prijmeni, jmeno, d):
    xml = vzp.stav_pojisteni(rc=rc, k_datu=d.isoformat())
    result = vzp.parse_stav_pojisteni(xml)
    save_insurance_status(mysql, rc, prijmeni, jmeno, d, result, xml)
    time.sleep(2)  # VZP rate limit
    return result["stav"]

def binary_search_change(rc, prijmeni, jmeno, start_date, end_date):
    """
    Najde PRVNÍ den, kdy stav != '1'
    """
    low = start_date
    high = end_date
    found = None

    while low <= high:
        mid = low + (high - low) // 2
        log(f"   🔍 {rc} probing {mid}")

        stav = vzp_query(rc, prijmeni, jmeno, mid)

        if stav == '1':
            low = mid + timedelta(days=1)
        else:
            found = mid
            high = mid - timedelta(days=1)

    return found

# ==========================================
# LOAD CANDIDATES
# ==========================================
with mysql.cursor() as cur:
    cur.execute("""
        SELECT
    x.rc,
    p.prijmeni,
    p.jmeno,
    x.last_ok
FROM (
    SELECT
        rc,
        MAX(CASE WHEN stav = '1' THEN k_datu END) AS last_ok,
        MAX(k_datu) AS last_seen
    FROM vzp_stav_pojisteni
    GROUP BY rc
) x
JOIN vzp_stav_pojisteni p
  ON p.rc = x.rc
 AND p.k_datu = x.last_seen
WHERE
    x.last_ok IS NOT NULL
  AND x.last_seen = (
        SELECT MAX(k_datu)
        FROM vzp_stav_pojisteni z
        WHERE z.rc = x.rc AND z.stav <> '1'
    );

    """)
    patients = cur.fetchall()

log(f"Loaded {len(patients)} patients for binary search\n")

# ==========================================
# MAIN LOOP
# ==========================================
today = date.today()

for p in patients:
    rc = p["rc"]
    prijmeni = p["prijmeni"]
    jmeno = p["jmeno"]
    last_ok = p["last_ok"]

    log(f"\n📌 {prijmeni} {jmeno} ({rc}) – searching change")

    change_date = binary_search_change(
        rc,
        prijmeni,
        jmeno,
        last_ok,
        today
    )

    if change_date:
        log(f"   ✅ změna nastala: {change_date}")
    else:
        log("   ⚠️ změna nenalezena")

# ==========================================
# CLEANUP
# ==========================================
db.close()
mysql.close()

log("\nDONE – binary search insurance check finished.")