29.2-dars: To'liq loyiha: muammo va ma'lumot

mijozlar (6000)              hisobvaraqlar (~111 ming)        tolovlar (~101 ming)
  mijoz_id  PK                 mijoz_id, oy  PK                  mijoz_id, hisob_oy  PK
  yosh, hudud, daromad         balans                             tolov_oy   <- HODISA VAQTI
  limit, avto_tolov            min_tolov                          tolov_kuni
  maosh_loyihasi               (oy oxirida yaratiladi)            summa
  ochilgan_oy                                                     (keyingi oyda keladi!)
        |                            |                                  |
        +-------- mijoz_id ----------+-------- (mijoz_id, oy) ----------+
        |
tranzaksiyalar (~587 ming)
  mijoz_id, oy, kun, turi (xarid / naqd / onlayn), summa

                as-of t (t-oy oxiri)
                      |
 ...  t-2   t-1       |    t+1        t+2
------+-----+---------+-----+----------+------------> vaqt
      |     |  t-hisobvaraq yaratildi   |
      |     |         |     | t ning to'lovi (muddat: t+1 ning 25-kuni)
      |     |  t-1 ning to'lovi keldi   |     t+1 ning to'lovi
      |     |  (t-oyda)                 |     (t+2 da)
      |                                  |
  BELGILAR (X): faqat t gacha             NATIJA (y60): t va t+1 hisobvaraqlari
  - balans/limit: t gacha                 ikkalasi ham to'lanmagan
  - to'lov belgilari: muddati o'tgan      -> (t+2)-oy oxirida MA'LUM bo'ladi
    hisobvaraqlar, ya'ni t-1 gacha
  - tranzaksiyalar: t-2 dan t gacha

WITH nomzod AS (...),          -- kim baholanadi: as-of t da faol va kechikmagan
     muddati_otgan AS (...),   -- hisobvaraq + to'lov, faqat tolov_oy <= :t
     tolov_b AS (...),         -- kechikish_6, tolov_nisbati_3, tolov_kuni_3
     balans_b AS (...),        -- util, util_3, util_ozg
     tr_b AS (...)             -- naqd_soni_3, naqd_summa_3, xarid_soni_3
SELECT ... FROM nomzod JOIN ... LEFT JOIN tr_b ...   -- tranzaksiyasi yo'q -> 0

LOYIHADAGI SIZISH TURLARI:
  1. KELAJAK HODISASI   "oxirgi 6 hisobvaraq" joriy hisobvaraq to'lovini ham oladi
                        (to'lov t+1 da, u y60 ning bir qismi!)
  2. YETILMAGAN BELGI   o'quvga as-of T-1 qo'shildi, uning natijasi hali to'liq emas
                        -> y60 = 0 ko'rinadi -> bashorat tizimli pasayadi
  3. HOZIRGI HOLAT      o'zgaruvchan atributning bugungi qiymati (limit, hudud, status)
  4. NATIJADAN HOSIL    "undiruv bo'limiga o'tkazilgan" bayrog'i - kechikishning natijasi

USHLASH (hammasi avtomatik, CI da):
  a) SNAPSHOT TESTI  as-of t dagi ombor nusxasidan va to'liq ombordan hisoblangan
                     belgilar BIR XIL bo'lishi shart (halolda 0 farq)
  b) SANA AUDITI     so'rov o'qigan har manba qatorining hodisa vaqti <= as-of
  c) KUCHLI BELGI    bitta belgining o'zi AUC > 0.9 -> qo'lda tekshiruv
                     (bu vazifada eng kuchli halol belgi ~0.8)
  d) ISHLAB CHIQARISH SIMULYATSIYASI  val oylarini snapshot dan qayta hisoblab baholash

as-of:   7 ........ 14 | 15 | 16 .. 18 | 19 | 20 .. 22 | 23-24
         O'QUV (8 oy)  |BO'S|  VAL (3)  |BO'S|  TEST (3) | belgi yetilmagan
                                                         (as-of emas)
NEGA BO'SHLIQ: as-of 14 ning natijasi 16-oy oxirida ma'lum -> 16-oyda o'qitilgan
  model uchun eng oxirgi belgili oy 14. Val ning birinchi oyi 16 -> o'quv <= 14.
  Test 20 dan -> yakuniy model <= 18 da o'qitiladi 29.3-bob. 15 va 19 - bo'shliq.

NEGA TASODIFIY EMAS:
  1. ishlab chiqarish = o'tmishda o'qib, kelajakni bashorat qilish -> shuni simulyatsiya
  2. tasodifiy bo'lishda o'quvda val dan KEYINGI oylar bor (32.6% qator)
  3. drift 27.12-bob faqat vaqt bo'yicha bo'lishda ko'rinadi
  4. belgi yetilishi va bo'shliqni faqat vaqt o'qida to'g'ri qo'yish mumkin

TEST QOIDASI: test oylari (20-22) belgilari 29.3 da BIR MARTA ochiladi.
  Hatto ulushiga ham hozir qaralmaydi (1-bo'lim jadvalida "?").

# Ma'lumot kartasi: kredit karta kechikishi (v1)

## Tarkib
- Manbalar: mijozlar, hisobvaraqlar, tolovlar, tranzaksiyalar (bank ombori, kunlik eksport)
- Davr: 2024-01 - 2025-12; as-of oylar 2024-07 - 2025-10
- Tahlil birligi: (mijoz, as-of oy); qatorlar: 68532; noyob mijozlar: 5463
- Nomzodlar: faol, kamida bitta hisobvarag'i bor, oxirgi hisobvarag'i to'langan

## Belgilar va natija
- 15 belgi, hammasi as-of gacha; snapshot testi: 0 farq
- Natija y60: t va t+1 hisobvaraqlari bo'yicha minimal to'lov qilinmagan
- Natija 2 oyda yetiladi; o'quvdagi ulush 0.0209

## Yig'ish va sifat
- Sifat tekshiruvlari: kalit, NULL, diapazon, birlik, yaxlitlik, oylik hajm
- Ma'lum nuqsonlar va tuzatishlar jurnali: (versiya bo'yicha)

## Bo'lish
- O'quv 7-14, val 16-18, test 20-22; bo'shliqlar 15 va 19; test bir marta ochiladi

## Maqsad va cheklovlar
- Yaroqli: profilaktik qo'ng'iroq ro'yxati uchun kechikish xavfini baholash
- Yaroqsiz: kredit berish qarori (boshqa populyatsiya), shaxsiy baho sifatida oshkor qilish
- Hudud va yosh - sezgir bo'lishi mumkin; segmentlar bo'yicha tahlil majburiy (29.11)
- Zarar parametri faraziy (0.45 * balans)

## Egasi va yangilanish
- Egasi: DS jamoasi + kredit risk; yangilanish: oylik; versiya: xesh bilan (27.5)

import contextlib
import sqlite3

import pandas as pd

with contextlib.closing(sqlite3.connect(":memory:")) as con:
    for nom, df in bank.items():
        df.to_sql(nom, con, index=False)
    con.execute("CREATE INDEX ix_p ON tolovlar(mijoz_id, hisob_oy)")
    X = pd.read_sql(BELGI_SQL, con, params={"t": 16})        # faqat t gacha
    y = pd.read_sql(NATIJA_SQL, con, params={"t": 16})       # alohida: t dan keyin

# halol to'lov sharti: hodisa vaqti bo'yicha
# LEFT JOIN tolovlar p ON ... AND p.tolov_oy <= :t   WHERE h.oy BETWEEN :t - 6 AND :t - 1

# snapshot testi
snapshot(con, t)                                             # tolov_oy <= t, oy <= t ...
assert belgilar(con, t).equals(belgilar(con, t, p="snap_"))

# vaqt bo'yicha bo'lish
oquv, val, test = df[df.oy <= 14], df[df.oy.between(16, 18)], df[df.oy.between(20, 22)]

inventar: kalit + hodisa vaqti + "hozirgi holat" ustunlari
belgilar: as-of gacha (to'lov: tolov_oy <= t, hisobvaraq <= t-1); natija - alohida
sizish: snapshot testi + sana auditi + kuchli belgi signali + ishlab chiqarish simulyatsiyasi
sifat: oldingi eksportga nisbatan; karantin; partiya darajasida tuzatish
bo'lish: vaqt bo'yicha, bo'shliq = belgi yetilishi; test bir marta

"""Manbalarni sqlite ga yig'ish, yaxlitlik tekshiruvi va SQL bilan as-of belgilar."""

import contextlib
import pathlib
import sqlite3
import tempfile

import numpy as np
import pandas as pd

HUDUDLAR = ["Toshkent", "Samarqand", "Farg'ona", "Andijon", "Buxoro", "Qashqadaryo"]
OYLAR = 24                               # 1 = 2024-01, ..., 24 = 2025-12
BELGILAR = ["yosh", "daromad", "limit", "avto_tolov", "maosh_loyihasi", "staj",
            "util", "util_3", "util_ozg", "kechikish_6", "tolov_nisbati_3",
            "tolov_kuni_3", "naqd_soni_3", "naqd_summa_3", "xarid_soni_3"]
K, SAMARA, NARX = 200, 0.30, 0.02        # oyiga qo'ng'iroq, oldini olish, mln so'm


def sigmoid(z):
    return 1 / (1 + np.exp(-z))


def oy_nomi(m):
    return f"{2024 + (m - 1) // 12}-{(m - 1) % 12 + 1:02d}"


def yarat_bank(seed=29, n=6000):
    """Kredit karta portfeli: mijozlar, hisobvaraqlar, tolovlar, tranzaksiyalar."""
    rng = np.random.default_rng(seed)
    ids = np.arange(1, n + 1)
    yosh = rng.integers(21, 66, n)
    maosh = rng.random(n) < 0.45
    daromad = np.round(np.exp(rng.normal(np.log(6.0), 0.45, n)), 2)   # mln so'm
    ochilgan = np.where(rng.random(n) < 0.7, rng.integers(-40, 1, n),
                        rng.integers(1, 20, n))
    xavf = (rng.normal(0, 1, n) - 0.5 * maosh - 0.8 * np.log(daromad / 6)
            + 0.015 * (35 - yosh))                     # yashirin: moliyaviy xavf
    unut = rng.normal(0, 1, n) + 0.03 * (35 - yosh)     # yashirin: unutuvchanlik
    avto = (rng.random(n) < sigmoid(-0.2 - 0.9 * unut)).astype(int)
    limit = np.round(daromad * rng.uniform(1.5, 3.5, n), 1)
    hudud = rng.choice(HUDUDLAR, n, p=[0.34, 0.15, 0.14, 0.13, 0.12, 0.12])
    mj = pd.DataFrame({"mijoz_id": ids, "yosh": yosh, "hudud": hudud,
                       "daromad": daromad, "limit": limit, "avto_tolov": avto,
                       "maosh_loyihasi": maosh.astype(int), "ochilgan_oy": ochilgan})
    s, oldingi, ketma = rng.normal(0, 1, n), np.zeros(n, int), np.zeros(n, int)
    hv, tl, tr = [], [], []
    for m in range(1, OYLAR + 1):
        faol = (ochilgan <= m) & (ketma < 3)            # 3 ketma-ket kechikish - defolt
        s = 0.75 * s + 0.66 * rng.normal(0, 1, n)
        d = 0.8 * xavf + 0.7 * s + 0.15 * (m % 12 == 1) + 0.08 * max(m - 18, 0)
        util = np.clip(sigmoid(-0.4 + 0.55 * d + rng.normal(0, 0.5, n)), 0.01, 1)
        balans = np.round(util * limit, 3)
        min_t = np.round(np.maximum(0.05 * balans, 0.05), 3)
        for turi, lam in [("xarid", np.exp(1.2 + 0.15 * np.log(daromad / 6) - 0.1 * d)),
                          ("naqd", np.exp(-1.6 + 0.6 * d)),
                          ("onlayn", np.exp(0.6 - 0.02 * (yosh - 35)))]:
            i = np.repeat(np.arange(n), rng.poisson(lam) * faol)
            summa = (rng.uniform(0.2, 1.5, i.size) * (1 + 0.3 * d[i].clip(0))
                     if turi == "naqd" else np.exp(rng.normal(np.log(0.25), 0.8, i.size)))
            tr.append(pd.DataFrame({"mijoz_id": ids[i], "oy": m,
                                    "kun": rng.integers(1, 29, i.size), "turi": turi,
                                    "summa": np.round(summa, 3)}))
        q = rng.random(n) < sigmoid(-3.7 + 1.3 * d + 1.2 * (util > 0.85)
                                    + 3.8 * (oldingi == 2))          # qiyinchilik
        u = ~q & (rng.random(n) < sigmoid(-3.4 + 1.1 * unut - 1.8 * avto)
                  * (oldingi != 1))                                  # unutish
        miss = (q | u) & faol
        ulush = sigmoid(0.3 - 1.2 * d + rng.normal(0, 0.5, n))
        summa = np.where(miss, min_t * rng.uniform(0, 0.6, n),
                         min_t + (balans - min_t).clip(0) * ulush)
        tolaydi = faol & (~miss | (rng.random(n) < 0.4))
        kun = np.clip(np.round(6 + 14 * sigmoid(unut + 0.5 * d)
                               + rng.normal(0, 3, n)), 1, 25)
        hv.append(pd.DataFrame({"mijoz_id": ids[faol], "oy": m,
                                "balans": balans[faol], "min_tolov": min_t[faol]}))
        if m < OYLAR:                    # m-oy hisobvarag'i (m+1)-oyda to'lanadi
            tl.append(pd.DataFrame({"mijoz_id": ids[tolaydi], "hisob_oy": m,
                                    "tolov_oy": m + 1,
                                    "tolov_kuni": kun[tolaydi].astype(int),
                                    "summa": np.round(summa[tolaydi], 3)}))
        ketma = np.where(faol, np.where(miss, ketma + 1, 0), ketma)
        oldingi = np.where(miss, np.where(q, 2, 1), 0)
    return {"mijozlar": mj, "hisobvaraqlar": pd.concat(hv, ignore_index=True),
            "tolovlar": pd.concat(tl, ignore_index=True),
            "tranzaksiyalar": pd.concat(tr, ignore_index=True)}


def orta(a):
    """Qator bo'yicha NaN siz o'rtacha (bo'sh qatorda NaN, ogohlantirishsiz)."""
    k = (~np.isnan(a)).sum(axis=1)
    return np.where(k > 0, np.nansum(a, axis=1) / np.maximum(k, 1), np.nan)


def belgilar_jadvali(bank, oylar=range(7, 23)):
    """Har as-of oy t (oy oxiri): faqat t gacha ma'lum belgilar + t, t+1 natijasi."""
    mj = bank["mijozlar"]
    ids, kol = mj["mijoz_id"].to_numpy(), np.arange(1, OYLAR + 1)

    def mat(df, qiymat, ustun="oy"):
        return (df.pivot(index="mijoz_id", columns=ustun, values=qiymat)
                .reindex(index=ids, columns=kol).to_numpy(dtype=float))

    hv, tl = bank["hisobvaraqlar"], bank["tolovlar"]
    bal, mint = mat(hv, "balans"), mat(hv, "min_tolov")
    tol, kun = mat(tl, "summa", "hisob_oy"), mat(tl, "tolov_kuni", "hisob_oy")
    bor = ~np.isnan(bal)
    miss = bor & ~(np.nan_to_num(tol) >= mint - 1e-9)    # minimal to'lov qilinmagan
    g = (bank["tranzaksiyalar"].groupby(["turi", "mijoz_id", "oy"])["summa"]
         .agg(["size", "sum"]))

    def tr_mat(turi, ust):
        return (g.loc[turi, ust].unstack("oy").reindex(index=ids, columns=kol)
                .fillna(0).to_numpy())

    naqd_n, naqd_s = tr_mat("naqd", "size"), tr_mat("naqd", "sum")
    xarid_n = tr_mat("xarid", "size")
    util = bal / mj["limit"].to_numpy()[:, None]
    och = mj["ochilgan_oy"].to_numpy()
    qismlar = []
    for t in oylar:
        c = t - 1                                        # t-oy ustuni (0 dan)
        ok = (och <= t - 1) & bor[:, c] & bor[:, c - 1] & ~miss[:, c - 1]
        u3 = util[:, c - 3]
        f = mj.drop(columns="ochilgan_oy").assign(
            oy=t, staj=t - och, util=util[:, c], util_3=orta(util[:, c - 2:c + 1]),
            util_ozg=util[:, c] - np.where(np.isnan(u3), util[:, c], u3),
            kechikish_6=miss[:, max(c - 6, 0):c].sum(axis=1),
            tolov_nisbati_3=orta(np.nan_to_num(tol[:, c - 3:c]) / bal[:, c - 3:c]),
            tolov_kuni_3=orta(kun[:, c - 3:c]),
            naqd_soni_3=naqd_n[:, c - 2:c + 1].sum(axis=1),
            naqd_summa_3=naqd_s[:, c - 2:c + 1].sum(axis=1),
            xarid_soni_3=xarid_n[:, c - 2:c + 1].sum(axis=1),
            balans=bal[:, c], y30=(miss[:, c] | miss[:, c + 1]).astype(int),
            y60=(miss[:, c] & miss[:, c + 1]).astype(int))
        qismlar.append(f[ok])
    df = pd.concat(qismlar, ignore_index=True)
    df["tolov_kuni_3"] = df["tolov_kuni_3"].fillna(25.0)
    df["tolov_nisbati_3"] = df["tolov_nisbati_3"].fillna(0.0)
    df["zarar"] = 0.45 * df["balans"]              # DPD60 bo'lsa kutilgan zarar
    df["qiymat"] = SAMARA * df["zarar"] * df["y60"] - NARX   # qo'ng'iroq qiymati
    return df

BELGI_SQL = """
WITH nomzod AS (            -- as-of t: faol, kamida bitta hisobvaraq, oxirgisi to'langan
    SELECT m.mijoz_id, m.yosh, m.daromad, m."limit" AS lim, m.avto_tolov,
           m.maosh_loyihasi, :t - m.ochilgan_oy AS staj
    FROM mijozlar m
    JOIN hisobvaraqlar h0 ON h0.mijoz_id = m.mijoz_id AND h0.oy = :t
    JOIN hisobvaraqlar h1 ON h1.mijoz_id = m.mijoz_id AND h1.oy = :t - 1
    LEFT JOIN tolovlar p1 ON p1.mijoz_id = m.mijoz_id AND p1.hisob_oy = :t - 1
                         AND p1.tolov_oy <= :t
    WHERE m.ochilgan_oy <= :t - 1
      AND COALESCE(p1.summa, 0) >= h1.min_tolov - 1e-9
),
muddati_otgan AS (          -- to'lov muddati as-of gacha o'tgan hisobvaraqlar
    SELECT h.mijoz_id, h.oy, h.balans, COALESCE(p.summa, 0) AS tolangan,
           p.tolov_kuni,
           CASE WHEN COALESCE(p.summa, 0) < h.min_tolov - 1e-9 THEN 1 ELSE 0 END
               AS kechikdi
    FROM hisobvaraqlar h
    LEFT JOIN tolovlar p ON p.mijoz_id = h.mijoz_id AND p.hisob_oy = h.oy
                        AND p.tolov_oy <= :t
    WHERE h.oy BETWEEN :t - 6 AND :t - 1
),
tolov_b AS (
    SELECT mijoz_id, SUM(kechikdi) AS kechikish_6,
           AVG(CASE WHEN oy >= :t - 3 THEN tolangan / balans END) AS tolov_nisbati_3,
           AVG(CASE WHEN oy >= :t - 3 THEN tolov_kuni END) AS tolov_kuni_3
    FROM muddati_otgan GROUP BY mijoz_id
),
balans_b AS (
    SELECT mijoz_id,
           MAX(CASE WHEN oy = :t THEN balans END) AS balans,
           MAX(CASE WHEN oy = :t - 3 THEN balans END) AS balans_3oy_oldin,
           AVG(CASE WHEN oy >= :t - 2 THEN balans END) AS ort_balans_3
    FROM hisobvaraqlar WHERE oy BETWEEN :t - 3 AND :t GROUP BY mijoz_id
),
tr_b AS (
    SELECT mijoz_id, SUM(turi = 'naqd') AS naqd_soni_3,
           SUM(CASE WHEN turi = 'naqd' THEN summa ELSE 0 END) AS naqd_summa_3,
           SUM(turi = 'xarid') AS xarid_soni_3
    FROM tranzaksiyalar WHERE oy BETWEEN :t - 2 AND :t GROUP BY mijoz_id
)
SELECT n.mijoz_id, :t AS oy, n.yosh, n.daromad, n.lim AS "limit", n.avto_tolov,
       n.maosh_loyihasi, n.staj,
       b.balans / n.lim AS util,
       b.ort_balans_3 / n.lim AS util_3,
       (b.balans - COALESCE(b.balans_3oy_oldin, b.balans)) / n.lim AS util_ozg,
       t.kechikish_6,
       COALESCE(t.tolov_nisbati_3, 0) AS tolov_nisbati_3,
       COALESCE(t.tolov_kuni_3, 25) AS tolov_kuni_3,
       COALESCE(r.naqd_soni_3, 0) AS naqd_soni_3,
       COALESCE(r.naqd_summa_3, 0) AS naqd_summa_3,
       COALESCE(r.xarid_soni_3, 0) AS xarid_soni_3,
       b.balans
FROM nomzod n
JOIN balans_b b USING (mijoz_id)
JOIN tolov_b t USING (mijoz_id)
LEFT JOIN tr_b r USING (mijoz_id)
ORDER BY n.mijoz_id
"""

NATIJA_SQL = """
SELECT h.mijoz_id,
       MAX(CASE WHEN COALESCE(p.summa, 0) < h.min_tolov - 1e-9 THEN 1 ELSE 0 END) AS y30,
       MIN(CASE WHEN COALESCE(p.summa, 0) < h.min_tolov - 1e-9 THEN 1 ELSE 0 END) AS y60
FROM hisobvaraqlar h
LEFT JOIN tolovlar p ON p.mijoz_id = h.mijoz_id AND p.hisob_oy = h.oy
WHERE h.oy IN (:t, :t + 1)
GROUP BY h.mijoz_id HAVING COUNT(*) = 2
"""


def yukla(con, bank):
    for nom, df in bank.items():
        df.to_sql(nom, con, index=False)
    con.executescript("""
        CREATE UNIQUE INDEX ix_m ON mijozlar(mijoz_id);
        CREATE UNIQUE INDEX ix_h ON hisobvaraqlar(mijoz_id, oy);
        CREATE UNIQUE INDEX ix_p ON tolovlar(mijoz_id, hisob_oy);
        CREATE INDEX ix_t ON tranzaksiyalar(oy, mijoz_id);
    """)


def main() -> None:
    bank = yarat_bank()
    with tempfile.TemporaryDirectory() as papka:
        yol = pathlib.Path(papka) / "bank.db"
        with contextlib.closing(sqlite3.connect(yol)) as con:
            yukla(con, bank)
            print("=== 1. Ombor: jadvallar va hajm ===")
            for nom in ["mijozlar", "hisobvaraqlar", "tolovlar", "tranzaksiyalar"]:
                n = con.execute(f"SELECT COUNT(*) FROM {nom}").fetchone()[0]
                print(f"  {nom:<15} {n:>8} qator")
            print(f"  fayl: {yol.name} (vaqtinchalik papkada), indekslar: 4")

            print("\n=== 2. Yaxlitlik tekshiruvlari (SQL) ===")
            tekshiruvlar = {
                "takror hisobvaraq (mijoz, oy)":
                    "SELECT COUNT(*) FROM (SELECT mijoz_id, oy FROM hisobvaraqlar "
                    "GROUP BY 1, 2 HAVING COUNT(*) > 1)",
                "yetim to'lov (hisobvarag'i yo'q)":
                    "SELECT COUNT(*) FROM tolovlar p LEFT JOIN hisobvaraqlar h "
                    "ON h.mijoz_id = p.mijoz_id AND h.oy = p.hisob_oy "
                    "WHERE h.mijoz_id IS NULL",
                "yetim tranzaksiya (mijozi yo'q)":
                    "SELECT COUNT(*) FROM tranzaksiyalar t LEFT JOIN mijozlar m "
                    "USING (mijoz_id) WHERE m.mijoz_id IS NULL",
                "to'lov hisobvaraqdan oldin":
                    "SELECT COUNT(*) FROM tolovlar WHERE tolov_oy <= hisob_oy",
                "ochilishdan oldingi hisobvaraq":
                    "SELECT COUNT(*) FROM hisobvaraqlar h JOIN mijozlar m "
                    "USING (mijoz_id) WHERE h.oy < m.ochilgan_oy",
            }
            for nom, sql in tekshiruvlar.items():
                n = con.execute(sql).fetchone()[0]
                print(f"  {nom:<34} {n:>5}  {'OK' if n == 0 else 'XATO'}")

            print("\n=== 3. SQL bilan belgilar: as-of t = 16 (2025-04) ===")
            t = 16
            sql_df = pd.read_sql(BELGI_SQL, con, params={"t": t})
            nat = pd.read_sql(NATIJA_SQL, con, params={"t": t})
            sql_df = sql_df.merge(nat, on="mijoz_id", how="left")
            print(f"  nomzodlar: {len(sql_df)}, y30 {sql_df.y30.mean():.3f}, "
                  f"y60 {sql_df.y60.mean():.3f}")
            print(sql_df[["mijoz_id", "util", "kechikish_6", "tolov_nisbati_3",
                          "naqd_soni_3", "y60"]].head(4).round(3).to_string(index=False))

        print("\n=== 4. SQL va pandas (belgilar_jadvali) bir xilmi? ===")
        pd_df = belgilar_jadvali(bank, [t]).sort_values("mijoz_id").reset_index(drop=True)
        print(f"  qatorlar: SQL {len(sql_df)}, pandas {len(pd_df)}, mijozlar bir xil: "
              f"{sql_df.mijoz_id.tolist() == pd_df.mijoz_id.tolist()}")
        farqlar = {b: float(np.abs(sql_df[b].to_numpy(float) - pd_df[b].to_numpy(float)).max())
                   for b in BELGILAR + ["balans", "y30", "y60"]}
        eng = max(farqlar, key=farqlar.get)
        print(f"  eng katta farq: {eng} = {farqlar[eng]:.2e}")
        print(f"  hamma belgi mos (< 1e-9): {all(v < 1e-9 for v in farqlar.values())}")


if __name__ == "__main__":
    main()

=== 1. Ombor: jadvallar va hajm ===
  mijozlar            6000 qator
  hisobvaraqlar     111058 qator
  tolovlar          100780 qator
  tranzaksiyalar    586885 qator
  fayl: bank.db (vaqtinchalik papkada), indekslar: 4

=== 2. Yaxlitlik tekshiruvlari (SQL) ===
  takror hisobvaraq (mijoz, oy)          0  OK
  yetim to'lov (hisobvarag'i yo'q)       0  OK
  yetim tranzaksiya (mijozi yo'q)        0  OK
  to'lov hisobvaraqdan oldin             0  OK
  ochilishdan oldingi hisobvaraq         0  OK

=== 3. SQL bilan belgilar: as-of t = 16 (2025-04) ===
  nomzodlar: 4362, y30 0.105, y60 0.021
 mijoz_id  util  kechikish_6  tolov_nisbati_3  naqd_soni_3  y60
        3 0.459            1            0.915            0    0
        4 0.370            0            0.562            2    0
        6 0.594            1            0.688            0    0
        8 0.160            1            0.875            1    0

=== 4. SQL va pandas (belgilar_jadvali) bir xilmi? ===
  qatorlar: SQL 4362, pandas 4362, mijozlar bir xil: True
  eng katta farq: naqd_summa_3 = 8.88e-16
  hamma belgi mos (< 1e-9): True

"""Vaqt nuqtasi (as-of) va sizish: "oxirgi 6 hisobvaraq" xatosini o'lchash va ushlash."""

import contextlib
import sqlite3

import numpy as np
import pandas as pd
from sklearn.linear_model import LogisticRegression
from sklearn.metrics import roc_auc_score
from sklearn.pipeline import make_pipeline
from sklearn.preprocessing import StandardScaler

HUDUDLAR = ["Toshkent", "Samarqand", "Farg'ona", "Andijon", "Buxoro", "Qashqadaryo"]
OYLAR = 24                               # 1 = 2024-01, ..., 24 = 2025-12
BELGILAR = ["yosh", "daromad", "limit", "avto_tolov", "maosh_loyihasi", "staj",
            "util", "util_3", "util_ozg", "kechikish_6", "tolov_nisbati_3",
            "tolov_kuni_3", "naqd_soni_3", "naqd_summa_3", "xarid_soni_3"]
K, SAMARA, NARX = 200, 0.30, 0.02        # oyiga qo'ng'iroq, oldini olish, mln so'm


def sigmoid(z):
    return 1 / (1 + np.exp(-z))


def oy_nomi(m):
    return f"{2024 + (m - 1) // 12}-{(m - 1) % 12 + 1:02d}"


def yarat_bank(seed=29, n=6000):
    """Kredit karta portfeli: mijozlar, hisobvaraqlar, tolovlar, tranzaksiyalar."""
    rng = np.random.default_rng(seed)
    ids = np.arange(1, n + 1)
    yosh = rng.integers(21, 66, n)
    maosh = rng.random(n) < 0.45
    daromad = np.round(np.exp(rng.normal(np.log(6.0), 0.45, n)), 2)   # mln so'm
    ochilgan = np.where(rng.random(n) < 0.7, rng.integers(-40, 1, n),
                        rng.integers(1, 20, n))
    xavf = (rng.normal(0, 1, n) - 0.5 * maosh - 0.8 * np.log(daromad / 6)
            + 0.015 * (35 - yosh))                     # yashirin: moliyaviy xavf
    unut = rng.normal(0, 1, n) + 0.03 * (35 - yosh)     # yashirin: unutuvchanlik
    avto = (rng.random(n) < sigmoid(-0.2 - 0.9 * unut)).astype(int)
    limit = np.round(daromad * rng.uniform(1.5, 3.5, n), 1)
    hudud = rng.choice(HUDUDLAR, n, p=[0.34, 0.15, 0.14, 0.13, 0.12, 0.12])
    mj = pd.DataFrame({"mijoz_id": ids, "yosh": yosh, "hudud": hudud,
                       "daromad": daromad, "limit": limit, "avto_tolov": avto,
                       "maosh_loyihasi": maosh.astype(int), "ochilgan_oy": ochilgan})
    s, oldingi, ketma = rng.normal(0, 1, n), np.zeros(n, int), np.zeros(n, int)
    hv, tl, tr = [], [], []
    for m in range(1, OYLAR + 1):
        faol = (ochilgan <= m) & (ketma < 3)            # 3 ketma-ket kechikish - defolt
        s = 0.75 * s + 0.66 * rng.normal(0, 1, n)
        d = 0.8 * xavf + 0.7 * s + 0.15 * (m % 12 == 1) + 0.08 * max(m - 18, 0)
        util = np.clip(sigmoid(-0.4 + 0.55 * d + rng.normal(0, 0.5, n)), 0.01, 1)
        balans = np.round(util * limit, 3)
        min_t = np.round(np.maximum(0.05 * balans, 0.05), 3)
        for turi, lam in [("xarid", np.exp(1.2 + 0.15 * np.log(daromad / 6) - 0.1 * d)),
                          ("naqd", np.exp(-1.6 + 0.6 * d)),
                          ("onlayn", np.exp(0.6 - 0.02 * (yosh - 35)))]:
            i = np.repeat(np.arange(n), rng.poisson(lam) * faol)
            summa = (rng.uniform(0.2, 1.5, i.size) * (1 + 0.3 * d[i].clip(0))
                     if turi == "naqd" else np.exp(rng.normal(np.log(0.25), 0.8, i.size)))
            tr.append(pd.DataFrame({"mijoz_id": ids[i], "oy": m,
                                    "kun": rng.integers(1, 29, i.size), "turi": turi,
                                    "summa": np.round(summa, 3)}))
        q = rng.random(n) < sigmoid(-3.7 + 1.3 * d + 1.2 * (util > 0.85)
                                    + 3.8 * (oldingi == 2))          # qiyinchilik
        u = ~q & (rng.random(n) < sigmoid(-3.4 + 1.1 * unut - 1.8 * avto)
                  * (oldingi != 1))                                  # unutish
        miss = (q | u) & faol
        ulush = sigmoid(0.3 - 1.2 * d + rng.normal(0, 0.5, n))
        summa = np.where(miss, min_t * rng.uniform(0, 0.6, n),
                         min_t + (balans - min_t).clip(0) * ulush)
        tolaydi = faol & (~miss | (rng.random(n) < 0.4))
        kun = np.clip(np.round(6 + 14 * sigmoid(unut + 0.5 * d)
                               + rng.normal(0, 3, n)), 1, 25)
        hv.append(pd.DataFrame({"mijoz_id": ids[faol], "oy": m,
                                "balans": balans[faol], "min_tolov": min_t[faol]}))
        if m < OYLAR:                    # m-oy hisobvarag'i (m+1)-oyda to'lanadi
            tl.append(pd.DataFrame({"mijoz_id": ids[tolaydi], "hisob_oy": m,
                                    "tolov_oy": m + 1,
                                    "tolov_kuni": kun[tolaydi].astype(int),
                                    "summa": np.round(summa[tolaydi], 3)}))
        ketma = np.where(faol, np.where(miss, ketma + 1, 0), ketma)
        oldingi = np.where(miss, np.where(q, 2, 1), 0)
    return {"mijozlar": mj, "hisobvaraqlar": pd.concat(hv, ignore_index=True),
            "tolovlar": pd.concat(tl, ignore_index=True),
            "tranzaksiyalar": pd.concat(tr, ignore_index=True)}


def orta(a):
    """Qator bo'yicha NaN siz o'rtacha (bo'sh qatorda NaN, ogohlantirishsiz)."""
    k = (~np.isnan(a)).sum(axis=1)
    return np.where(k > 0, np.nansum(a, axis=1) / np.maximum(k, 1), np.nan)


def belgilar_jadvali(bank, oylar=range(7, 23)):
    """Har as-of oy t (oy oxiri): faqat t gacha ma'lum belgilar + t, t+1 natijasi."""
    mj = bank["mijozlar"]
    ids, kol = mj["mijoz_id"].to_numpy(), np.arange(1, OYLAR + 1)

    def mat(df, qiymat, ustun="oy"):
        return (df.pivot(index="mijoz_id", columns=ustun, values=qiymat)
                .reindex(index=ids, columns=kol).to_numpy(dtype=float))

    hv, tl = bank["hisobvaraqlar"], bank["tolovlar"]
    bal, mint = mat(hv, "balans"), mat(hv, "min_tolov")
    tol, kun = mat(tl, "summa", "hisob_oy"), mat(tl, "tolov_kuni", "hisob_oy")
    bor = ~np.isnan(bal)
    miss = bor & ~(np.nan_to_num(tol) >= mint - 1e-9)    # minimal to'lov qilinmagan
    g = (bank["tranzaksiyalar"].groupby(["turi", "mijoz_id", "oy"])["summa"]
         .agg(["size", "sum"]))

    def tr_mat(turi, ust):
        return (g.loc[turi, ust].unstack("oy").reindex(index=ids, columns=kol)
                .fillna(0).to_numpy())

    naqd_n, naqd_s = tr_mat("naqd", "size"), tr_mat("naqd", "sum")
    xarid_n = tr_mat("xarid", "size")
    util = bal / mj["limit"].to_numpy()[:, None]
    och = mj["ochilgan_oy"].to_numpy()
    qismlar = []
    for t in oylar:
        c = t - 1                                        # t-oy ustuni (0 dan)
        ok = (och <= t - 1) & bor[:, c] & bor[:, c - 1] & ~miss[:, c - 1]
        u3 = util[:, c - 3]
        f = mj.drop(columns="ochilgan_oy").assign(
            oy=t, staj=t - och, util=util[:, c], util_3=orta(util[:, c - 2:c + 1]),
            util_ozg=util[:, c] - np.where(np.isnan(u3), util[:, c], u3),
            kechikish_6=miss[:, max(c - 6, 0):c].sum(axis=1),
            tolov_nisbati_3=orta(np.nan_to_num(tol[:, c - 3:c]) / bal[:, c - 3:c]),
            tolov_kuni_3=orta(kun[:, c - 3:c]),
            naqd_soni_3=naqd_n[:, c - 2:c + 1].sum(axis=1),
            naqd_summa_3=naqd_s[:, c - 2:c + 1].sum(axis=1),
            xarid_soni_3=xarid_n[:, c - 2:c + 1].sum(axis=1),
            balans=bal[:, c], y30=(miss[:, c] | miss[:, c + 1]).astype(int),
            y60=(miss[:, c] & miss[:, c + 1]).astype(int))
        qismlar.append(f[ok])
    df = pd.concat(qismlar, ignore_index=True)
    df["tolov_kuni_3"] = df["tolov_kuni_3"].fillna(25.0)
    df["tolov_nisbati_3"] = df["tolov_nisbati_3"].fillna(0.0)
    df["zarar"] = 0.45 * df["balans"]              # DPD60 bo'lsa kutilgan zarar
    df["qiymat"] = SAMARA * df["zarar"] * df["y60"] - NARX   # qo'ng'iroq qiymati
    return df

# {p} - jadval prefiksi (snapshot uchun), {oyna} va {tolov_sharti} - halol/sizuvchi farqi
SHABLON = """
WITH nomzod AS (
    SELECT m.mijoz_id, m.yosh, m.daromad, m."limit" AS lim, m.avto_tolov,
           m.maosh_loyihasi, :t - m.ochilgan_oy AS staj
    FROM {p}mijozlar m
    JOIN {p}hisobvaraqlar h0 ON h0.mijoz_id = m.mijoz_id AND h0.oy = :t
    JOIN {p}hisobvaraqlar h1 ON h1.mijoz_id = m.mijoz_id AND h1.oy = :t - 1
    LEFT JOIN {p}tolovlar p1 ON p1.mijoz_id = m.mijoz_id AND p1.hisob_oy = :t - 1
                            AND p1.tolov_oy <= :t
    WHERE m.ochilgan_oy <= :t - 1 AND COALESCE(p1.summa, 0) >= h1.min_tolov - 1e-9
),
hisob AS (
    SELECT h.mijoz_id, h.oy, h.balans, COALESCE(p.summa, 0) AS tolangan, p.tolov_kuni,
           CASE WHEN COALESCE(p.summa, 0) < h.min_tolov - 1e-9 THEN 1 ELSE 0 END
               AS kechikdi
    FROM {p}hisobvaraqlar h
    LEFT JOIN {p}tolovlar p ON p.mijoz_id = h.mijoz_id AND p.hisob_oy = h.oy {tolov_sharti}
    WHERE h.oy BETWEEN {oyna}
),
tolov_b AS (
    SELECT mijoz_id, SUM(kechikdi) AS kechikish_6,
           AVG(CASE WHEN oy >= {oxirgi3} THEN tolangan / balans END) AS tolov_nisbati_3,
           AVG(CASE WHEN oy >= {oxirgi3} THEN tolov_kuni END) AS tolov_kuni_3
    FROM hisob GROUP BY mijoz_id
),
balans_b AS (
    SELECT mijoz_id, MAX(CASE WHEN oy = :t THEN balans END) AS balans,
           MAX(CASE WHEN oy = :t - 3 THEN balans END) AS b3,
           AVG(CASE WHEN oy >= :t - 2 THEN balans END) AS ort_b
    FROM {p}hisobvaraqlar WHERE oy BETWEEN :t - 3 AND :t GROUP BY mijoz_id
),
tr_b AS (
    SELECT mijoz_id, SUM(turi = 'naqd') AS naqd_soni_3,
           SUM(CASE WHEN turi = 'naqd' THEN summa ELSE 0 END) AS naqd_summa_3,
           SUM(turi = 'xarid') AS xarid_soni_3
    FROM {p}tranzaksiyalar WHERE oy BETWEEN :t - 2 AND :t GROUP BY mijoz_id
)
SELECT n.mijoz_id, :t AS oy, n.yosh, n.daromad, n.lim AS "limit", n.avto_tolov,
       n.maosh_loyihasi, n.staj, b.balans / n.lim AS util, b.ort_b / n.lim AS util_3,
       (b.balans - COALESCE(b.b3, b.balans)) / n.lim AS util_ozg, t.kechikish_6,
       COALESCE(t.tolov_nisbati_3, 0) AS tolov_nisbati_3,
       COALESCE(t.tolov_kuni_3, 25) AS tolov_kuni_3,
       COALESCE(r.naqd_soni_3, 0) AS naqd_soni_3,
       COALESCE(r.naqd_summa_3, 0) AS naqd_summa_3,
       COALESCE(r.xarid_soni_3, 0) AS xarid_soni_3, b.balans
FROM nomzod n JOIN balans_b b USING (mijoz_id) JOIN tolov_b t USING (mijoz_id)
LEFT JOIN tr_b r USING (mijoz_id)
ORDER BY n.mijoz_id
"""
VARIANT = {
    # to'lov muddati as-of gacha o'tgan hisobvaraqlar va faqat as-of gacha kelgan to'lovlar
    "halol": dict(oyna=":t - 6 AND :t - 1", oxirgi3=":t - 3",
                  tolov_sharti="AND p.tolov_oy <= :t"),
    # "oxirgi 6 hisobvaraq" - joriy (t) hisobvaraq ham, uning kelajakdagi to'lovi ham
    "sizuvchi": dict(oyna=":t - 5 AND :t", oxirgi3=":t - 2", tolov_sharti=""),
}
NATIJA_SQL = """
SELECT h.mijoz_id,
       MIN(CASE WHEN COALESCE(p.summa, 0) < h.min_tolov - 1e-9 THEN 1 ELSE 0 END) AS y60
FROM hisobvaraqlar h
LEFT JOIN tolovlar p ON p.mijoz_id = h.mijoz_id AND p.hisob_oy = h.oy
WHERE h.oy IN (:t, :t + 1) GROUP BY h.mijoz_id HAVING COUNT(*) = 2
"""


def belgilar(con, variant, t, p=""):
    sql = SHABLON.format(p=p, **VARIANT[variant])
    return pd.read_sql(sql, con, params={"t": t})


def snapshot(con, t):
    """t-oy oxiridagi ombor holati: faqat shu paytgacha yozilgan qatorlar."""
    con.executescript(f"""
        DROP TABLE IF EXISTS snap_mijozlar; DROP TABLE IF EXISTS snap_hisobvaraqlar;
        DROP TABLE IF EXISTS snap_tolovlar; DROP TABLE IF EXISTS snap_tranzaksiyalar;
        CREATE TEMP TABLE snap_mijozlar AS SELECT * FROM mijozlar WHERE ochilgan_oy <= {t};
        CREATE TEMP TABLE snap_hisobvaraqlar AS SELECT * FROM hisobvaraqlar WHERE oy <= {t};
        CREATE TEMP TABLE snap_tolovlar AS SELECT * FROM tolovlar WHERE tolov_oy <= {t};
        CREATE TEMP TABLE snap_tranzaksiyalar AS SELECT * FROM tranzaksiyalar WHERE oy <= {t};
        CREATE INDEX temp.sx_h ON snap_hisobvaraqlar(mijoz_id, oy);
        CREATE INDEX temp.sx_p ON snap_tolovlar(mijoz_id, hisob_oy);
        CREATE INDEX temp.sx_t ON snap_tranzaksiyalar(oy, mijoz_id);
    """)


def tejam(df, ball):
    df = df.assign(_b=ball, q=SAMARA * 0.45 * df["balans"] * df["y60"] - NARX)
    return df.groupby("oy").apply(lambda g: g.nlargest(K, "_b")["q"].sum(),
                                  include_groups=False).mean()


def main() -> None:
    bank = yarat_bank()
    with contextlib.closing(sqlite3.connect(":memory:")) as con:
        for nom, df in bank.items():
            df.to_sql(nom, con, index=False)
        con.executescript("""
            CREATE INDEX ix_h ON hisobvaraqlar(mijoz_id, oy);
            CREATE INDEX ix_p ON tolovlar(mijoz_id, hisob_oy);
            CREATE INDEX ix_t ON tranzaksiyalar(oy, mijoz_id);""")
        jad = {}
        for v in VARIANT:
            qism = []
            for t in list(range(7, 15)) + [16, 17, 18]:
                f = belgilar(con, v, t)
                qism.append(f.merge(pd.read_sql(NATIJA_SQL, con, params={"t": t}),
                                    on="mijoz_id"))
            jad[v] = pd.concat(qism, ignore_index=True)

        print("=== 1. Bitta belgining AUC si (y60, as-of 7-14) ===")
        print(f"  {'belgi':<16} {'halol':>7} {'sizuvchi':>9}")
        yakka = {}
        for b in ["kechikish_6", "tolov_nisbati_3", "tolov_kuni_3", "util"]:
            r = []
            for v in VARIANT:
                d = jad[v][jad[v].oy <= 14]
                a = roc_auc_score(d.y60, d[b])
                r.append(max(a, 1 - a))              # yo'nalishdan qat'i nazar
            yakka[b] = r
            print(f"  {b:<16} {r[0]:>7.4f} {r[1]:>9.4f}")

        print("\n=== 2. Hisobotdagi natija: o'quv 7-14, val 16-18 ===")
        modellar, hisobot = {}, {}
        for v in VARIANT:
            tr, va = jad[v][jad[v].oy <= 14], jad[v][jad[v].oy >= 16]
            m = make_pipeline(StandardScaler(), LogisticRegression(max_iter=2000))
            modellar[v] = m.fit(tr[BELGILAR], tr.y60)
            p = m.predict_proba(va[BELGILAR])[:, 1]
            hisobot[v] = (roc_auc_score(va.y60, p), tejam(va, p * va.balans))
            print(f"  {v:<9} AUC {hisobot[v][0]:.4f}, tejam {hisobot[v][1]:6.2f} mln so'm/oy")

        print("\n=== 3. Ishlab chiqarish: belgilar oy oxiridagi ombordan (snapshot) ===")
        prod = {v: [] for v in VARIANT}
        for t in [16, 17, 18]:
            snapshot(con, t)
            nat = pd.read_sql(NATIJA_SQL, con, params={"t": t})
            for v in VARIANT:
                prod[v].append(belgilar(con, v, t, p="snap_").merge(nat, on="mijoz_id"))
        haqiqiy = {}
        for v in VARIANT:
            d = pd.concat(prod[v], ignore_index=True)
            p = modellar[v].predict_proba(d[BELGILAR])[:, 1]
            haqiqiy[v] = (roc_auc_score(d.y60, p), tejam(d, p * d.balans))
            print(f"  {v:<9} AUC {haqiqiy[v][0]:.4f} (hisobotda {hisobot[v][0]:.4f}), "
                  f"tejam {haqiqiy[v][1]:6.2f} (hisobotda {hisobot[v][1]:.2f})")

        print("\n=== 4. Sizishni ushlash: uchta avtomatik tekshiruv (t = 16) ===")
        snapshot(con, 16)
        for v in VARIANT:
            toliq = belgilar(con, v, 16).set_index("mijoz_id")
            snap = belgilar(con, v, 16, p="snap_").set_index("mijoz_id")
            farq = ~np.isclose(toliq[BELGILAR], snap.loc[toliq.index, BELGILAR])
            ustunlar = [b for b, f in zip(BELGILAR, farq.any(axis=0)) if f] or "yo'q"
            print(f"  [snapshot testi] {v:<9} farqli kataklar {farq.sum():>5}, "
                  f"ustunlar: {ustunlar}")
        kelajak = con.execute("""
            SELECT COUNT(*) FROM hisobvaraqlar h JOIN tolovlar p
              ON p.mijoz_id = h.mijoz_id AND p.hisob_oy = h.oy
            WHERE h.oy BETWEEN 16 - 5 AND 16 AND p.tolov_oy > 16""").fetchone()[0]
        print(f"  [sana auditi] sizuvchi so'rov as-of dan keyingi {kelajak} ta to'lovni "
              f"o'qiydi (halolda 0 - shart bilan)")
        for b, (h, s) in yakka.items():
            if s > 0.9:
                print(f"  [shubhali kuchli belgi] {b}: bitta o'zi AUC {s:.3f} > 0.9")

    print("\n=== 5. Xulosa (natijadan hisoblangan) ===")
    d_auc = hisobot["sizuvchi"][0] - haqiqiy["sizuvchi"][0]
    print(f"  sizuvchi model: hisobotdagi AUC ishlab chiqarishda {d_auc:.3f} ga tushdi")
    if haqiqiy["sizuvchi"][1] < haqiqiy["halol"][1]:
        print(f"  ishlab chiqarishda halol model {haqiqiy['halol'][1] - haqiqiy['sizuvchi'][1]:.1f}"
              f" mln so'm/oy ko'p tejaydi")
    if abs(hisobot["halol"][0] - haqiqiy["halol"][0]) < 1e-9:
        print("  halol belgilar: hisobot va ishlab chiqarish aynan bir xil")


if __name__ == "__main__":
    main()

=== 1. Bitta belgining AUC si (y60, as-of 7-14) ===
  belgi              halol  sizuvchi
  kechikish_6       0.5282    0.9138
  tolov_nisbati_3   0.7967    0.9291
  tolov_kuni_3      0.6517    0.6593
  util              0.7844    0.7844

=== 2. Hisobotdagi natija: o'quv 7-14, val 16-18 ===
  halol     AUC 0.8482, tejam  28.85 mln so'm/oy
  sizuvchi  AUC 0.9692, tejam  60.07 mln so'm/oy

=== 3. Ishlab chiqarish: belgilar oy oxiridagi ombordan (snapshot) ===
  halol     AUC 0.8482 (hisobotda 0.8482), tejam  28.85 (hisobotda 28.85)
  sizuvchi  AUC 0.7991 (hisobotda 0.9692), tejam  22.56 (hisobotda 60.07)

=== 4. Sizishni ushlash: uchta avtomatik tekshiruv (t = 16) ===
  [snapshot testi] halol     farqli kataklar     0, ustunlar: yo'q
  [snapshot testi] sizuvchi  farqli kataklar 12286, ustunlar: ['kechikish_6', 'tolov_nisbati_3', 'tolov_kuni_3']
  [sana auditi] sizuvchi so'rov as-of dan keyingi 4575 ta to'lovni o'qiydi (halolda 0 - shart bilan)
  [shubhali kuchli belgi] kechikish_6: bitta o'zi AUC 0.914 > 0.9
  [shubhali kuchli belgi] tolov_nisbati_3: bitta o'zi AUC 0.929 > 0.9

=== 5. Xulosa (natijadan hisoblangan) ===
  sizuvchi model: hisobotdagi AUC ishlab chiqarishda 0.170 ga tushdi
  ishlab chiqarishda halol model 6.3 mln so'm/oy ko'p tejaydi
  halol belgilar: hisobot va ishlab chiqarish aynan bir xil

"""Sifat tekshiruvlari (xom eksportdagi nuqsonlar) va qarorga yo'naltirilgan EDA."""

import contextlib
import sqlite3

import numpy as np
import pandas as pd

HUDUDLAR = ["Toshkent", "Samarqand", "Farg'ona", "Andijon", "Buxoro", "Qashqadaryo"]
OYLAR = 24                               # 1 = 2024-01, ..., 24 = 2025-12
BELGILAR = ["yosh", "daromad", "limit", "avto_tolov", "maosh_loyihasi", "staj",
            "util", "util_3", "util_ozg", "kechikish_6", "tolov_nisbati_3",
            "tolov_kuni_3", "naqd_soni_3", "naqd_summa_3", "xarid_soni_3"]
K, SAMARA, NARX = 200, 0.30, 0.02        # oyiga qo'ng'iroq, oldini olish, mln so'm


def sigmoid(z):
    return 1 / (1 + np.exp(-z))


def oy_nomi(m):
    return f"{2024 + (m - 1) // 12}-{(m - 1) % 12 + 1:02d}"


def yarat_bank(seed=29, n=6000):
    """Kredit karta portfeli: mijozlar, hisobvaraqlar, tolovlar, tranzaksiyalar."""
    rng = np.random.default_rng(seed)
    ids = np.arange(1, n + 1)
    yosh = rng.integers(21, 66, n)
    maosh = rng.random(n) < 0.45
    daromad = np.round(np.exp(rng.normal(np.log(6.0), 0.45, n)), 2)   # mln so'm
    ochilgan = np.where(rng.random(n) < 0.7, rng.integers(-40, 1, n),
                        rng.integers(1, 20, n))
    xavf = (rng.normal(0, 1, n) - 0.5 * maosh - 0.8 * np.log(daromad / 6)
            + 0.015 * (35 - yosh))                     # yashirin: moliyaviy xavf
    unut = rng.normal(0, 1, n) + 0.03 * (35 - yosh)     # yashirin: unutuvchanlik
    avto = (rng.random(n) < sigmoid(-0.2 - 0.9 * unut)).astype(int)
    limit = np.round(daromad * rng.uniform(1.5, 3.5, n), 1)
    hudud = rng.choice(HUDUDLAR, n, p=[0.34, 0.15, 0.14, 0.13, 0.12, 0.12])
    mj = pd.DataFrame({"mijoz_id": ids, "yosh": yosh, "hudud": hudud,
                       "daromad": daromad, "limit": limit, "avto_tolov": avto,
                       "maosh_loyihasi": maosh.astype(int), "ochilgan_oy": ochilgan})
    s, oldingi, ketma = rng.normal(0, 1, n), np.zeros(n, int), np.zeros(n, int)
    hv, tl, tr = [], [], []
    for m in range(1, OYLAR + 1):
        faol = (ochilgan <= m) & (ketma < 3)            # 3 ketma-ket kechikish - defolt
        s = 0.75 * s + 0.66 * rng.normal(0, 1, n)
        d = 0.8 * xavf + 0.7 * s + 0.15 * (m % 12 == 1) + 0.08 * max(m - 18, 0)
        util = np.clip(sigmoid(-0.4 + 0.55 * d + rng.normal(0, 0.5, n)), 0.01, 1)
        balans = np.round(util * limit, 3)
        min_t = np.round(np.maximum(0.05 * balans, 0.05), 3)
        for turi, lam in [("xarid", np.exp(1.2 + 0.15 * np.log(daromad / 6) - 0.1 * d)),
                          ("naqd", np.exp(-1.6 + 0.6 * d)),
                          ("onlayn", np.exp(0.6 - 0.02 * (yosh - 35)))]:
            i = np.repeat(np.arange(n), rng.poisson(lam) * faol)
            summa = (rng.uniform(0.2, 1.5, i.size) * (1 + 0.3 * d[i].clip(0))
                     if turi == "naqd" else np.exp(rng.normal(np.log(0.25), 0.8, i.size)))
            tr.append(pd.DataFrame({"mijoz_id": ids[i], "oy": m,
                                    "kun": rng.integers(1, 29, i.size), "turi": turi,
                                    "summa": np.round(summa, 3)}))
        q = rng.random(n) < sigmoid(-3.7 + 1.3 * d + 1.2 * (util > 0.85)
                                    + 3.8 * (oldingi == 2))          # qiyinchilik
        u = ~q & (rng.random(n) < sigmoid(-3.4 + 1.1 * unut - 1.8 * avto)
                  * (oldingi != 1))                                  # unutish
        miss = (q | u) & faol
        ulush = sigmoid(0.3 - 1.2 * d + rng.normal(0, 0.5, n))
        summa = np.where(miss, min_t * rng.uniform(0, 0.6, n),
                         min_t + (balans - min_t).clip(0) * ulush)
        tolaydi = faol & (~miss | (rng.random(n) < 0.4))
        kun = np.clip(np.round(6 + 14 * sigmoid(unut + 0.5 * d)
                               + rng.normal(0, 3, n)), 1, 25)
        hv.append(pd.DataFrame({"mijoz_id": ids[faol], "oy": m,
                                "balans": balans[faol], "min_tolov": min_t[faol]}))
        if m < OYLAR:                    # m-oy hisobvarag'i (m+1)-oyda to'lanadi
            tl.append(pd.DataFrame({"mijoz_id": ids[tolaydi], "hisob_oy": m,
                                    "tolov_oy": m + 1,
                                    "tolov_kuni": kun[tolaydi].astype(int),
                                    "summa": np.round(summa[tolaydi], 3)}))
        ketma = np.where(faol, np.where(miss, ketma + 1, 0), ketma)
        oldingi = np.where(miss, np.where(q, 2, 1), 0)
    return {"mijozlar": mj, "hisobvaraqlar": pd.concat(hv, ignore_index=True),
            "tolovlar": pd.concat(tl, ignore_index=True),
            "tranzaksiyalar": pd.concat(tr, ignore_index=True)}


def orta(a):
    """Qator bo'yicha NaN siz o'rtacha (bo'sh qatorda NaN, ogohlantirishsiz)."""
    k = (~np.isnan(a)).sum(axis=1)
    return np.where(k > 0, np.nansum(a, axis=1) / np.maximum(k, 1), np.nan)


def belgilar_jadvali(bank, oylar=range(7, 23)):
    """Har as-of oy t (oy oxiri): faqat t gacha ma'lum belgilar + t, t+1 natijasi."""
    mj = bank["mijozlar"]
    ids, kol = mj["mijoz_id"].to_numpy(), np.arange(1, OYLAR + 1)

    def mat(df, qiymat, ustun="oy"):
        return (df.pivot(index="mijoz_id", columns=ustun, values=qiymat)
                .reindex(index=ids, columns=kol).to_numpy(dtype=float))

    hv, tl = bank["hisobvaraqlar"], bank["tolovlar"]
    bal, mint = mat(hv, "balans"), mat(hv, "min_tolov")
    tol, kun = mat(tl, "summa", "hisob_oy"), mat(tl, "tolov_kuni", "hisob_oy")
    bor = ~np.isnan(bal)
    miss = bor & ~(np.nan_to_num(tol) >= mint - 1e-9)    # minimal to'lov qilinmagan
    g = (bank["tranzaksiyalar"].groupby(["turi", "mijoz_id", "oy"])["summa"]
         .agg(["size", "sum"]))

    def tr_mat(turi, ust):
        return (g.loc[turi, ust].unstack("oy").reindex(index=ids, columns=kol)
                .fillna(0).to_numpy())

    naqd_n, naqd_s = tr_mat("naqd", "size"), tr_mat("naqd", "sum")
    xarid_n = tr_mat("xarid", "size")
    util = bal / mj["limit"].to_numpy()[:, None]
    och = mj["ochilgan_oy"].to_numpy()
    qismlar = []
    for t in oylar:
        c = t - 1                                        # t-oy ustuni (0 dan)
        ok = (och <= t - 1) & bor[:, c] & bor[:, c - 1] & ~miss[:, c - 1]
        u3 = util[:, c - 3]
        f = mj.drop(columns="ochilgan_oy").assign(
            oy=t, staj=t - och, util=util[:, c], util_3=orta(util[:, c - 2:c + 1]),
            util_ozg=util[:, c] - np.where(np.isnan(u3), util[:, c], u3),
            kechikish_6=miss[:, max(c - 6, 0):c].sum(axis=1),
            tolov_nisbati_3=orta(np.nan_to_num(tol[:, c - 3:c]) / bal[:, c - 3:c]),
            tolov_kuni_3=orta(kun[:, c - 3:c]),
            naqd_soni_3=naqd_n[:, c - 2:c + 1].sum(axis=1),
            naqd_summa_3=naqd_s[:, c - 2:c + 1].sum(axis=1),
            xarid_soni_3=xarid_n[:, c - 2:c + 1].sum(axis=1),
            balans=bal[:, c], y30=(miss[:, c] | miss[:, c + 1]).astype(int),
            y60=(miss[:, c] & miss[:, c + 1]).astype(int))
        qismlar.append(f[ok])
    df = pd.concat(qismlar, ignore_index=True)
    df["tolov_kuni_3"] = df["tolov_kuni_3"].fillna(25.0)
    df["tolov_nisbati_3"] = df["tolov_nisbati_3"].fillna(0.0)
    df["zarar"] = 0.45 * df["balans"]              # DPD60 bo'lsa kutilgan zarar
    df["qiymat"] = SAMARA * df["zarar"] * df["y60"] - NARX   # qo'ng'iroq qiymati
    return df


def xom_eksport(bank, seed=7):
    """Bank eksportida uchraydigan nuqsonlar: takroriy partiya, birlik, bo'sh qiymat."""
    rng = np.random.default_rng(seed)
    xom = {k: v.copy() for k, v in bank.items()}
    tr = xom["tranzaksiyalar"]
    xom["tranzaksiyalar"] = pd.concat([tr, tr[tr.oy == 15]], ignore_index=True)
    tl = xom["tolovlar"]
    i = rng.choice(len(tl), 25, replace=False)
    tl.loc[i, "summa"] = tl.loc[i, "summa"] * 1_000_000          # so'mda yozilgan
    yetim = tl.iloc[:15].assign(mijoz_id=np.arange(9001, 9016))
    xom["tolovlar"] = pd.concat([tl, yetim], ignore_index=True)
    mj = xom["mijozlar"]
    mj.loc[rng.choice(len(mj), 12, replace=False), "limit"] = 0.0
    mj["yosh"] = mj["yosh"].astype(float)
    mj.loc[rng.choice(len(mj), 40, replace=False), "yosh"] = np.nan
    return xom


TEKSHIRUVLAR = {
    "mijoz_id takrori": "SELECT COUNT(*) - COUNT(DISTINCT mijoz_id) FROM mijozlar",
    "yosh bo'sh (NULL)": "SELECT COUNT(*) FROM mijozlar WHERE yosh IS NULL",
    "limit <= 0": 'SELECT COUNT(*) FROM mijozlar WHERE "limit" <= 0',
    "yetim to'lov": "SELECT COUNT(*) FROM tolovlar p LEFT JOIN mijozlar m "
                    "USING (mijoz_id) WHERE m.mijoz_id IS NULL",
    "to'lov > 10 * balans": "SELECT COUNT(*) FROM tolovlar p JOIN hisobvaraqlar h "
                            "ON h.mijoz_id = p.mijoz_id AND h.oy = p.hisob_oy "
                            "WHERE p.summa > 10 * h.balans",
    "to'liq takror tranzaksiya": "SELECT COALESCE(SUM(n - 1), 0) FROM (SELECT COUNT(*) AS n "
                                 "FROM tranzaksiyalar GROUP BY mijoz_id, oy, kun, turi, "
                                 "summa HAVING n > 1)",
}


def tekshir(bank):
    with contextlib.closing(sqlite3.connect(":memory:")) as con:
        for nom, df in bank.items():
            df.to_sql(nom, con, index=False)
        natija = {nom: con.execute(sql).fetchone()[0] for nom, sql in TEKSHIRUVLAR.items()}
        hajm = pd.read_sql("SELECT oy, COUNT(*) AS n FROM tranzaksiyalar GROUP BY oy", con)
    med = hajm.n.median()
    mad = (hajm.n - med).abs().median()
    z = 0.6745 * (hajm.n - med) / mad                     # robust z (8.6)
    natija["oylik hajm |z| > 5"] = int((z.abs() > 5).sum())
    return natija, hajm.oy[z.abs() > 5].tolist()


def main() -> None:
    bank = yarat_bank()
    xom = xom_eksport(bank)
    print("=== 1. Sifat tekshiruvlari: oldingi (toza) eksport va yangi eksport ===")
    n_toza, _ = tekshir(bank)
    n_xom, oylar = tekshir(xom)
    print(f"  {'tekshiruv':<28} {'oldingi':>8} {'yangi':>7}  holat")
    for nom, n in n_xom.items():
        print(f"  {nom:<28} {n_toza[nom]:>8} {n:>7}  {'OK' if n <= n_toza[nom] else 'XATO'}")
    print(f"  hajmi g'ayrioddiy oylar: {[oy_nomi(m) for m in oylar]}")

    print("\n=== 2. Nuqsonlar belgilarga qanday o'tadi? (as-of 15-17) ===")
    toza = belgilar_jadvali(bank, [15, 16, 17]).set_index(["mijoz_id", "oy"])
    with np.errstate(divide="ignore", invalid="ignore"):
        buz = belgilar_jadvali(xom, [15, 16, 17]).set_index(["mijoz_id", "oy"])
    umumiy = toza.index.intersection(buz.index)
    print(f"  qatorlar: toza {len(toza)}, xom {len(buz)}")
    for b in ["naqd_soni_3", "xarid_soni_3", "tolov_nisbati_3", "util", "yosh"]:
        a, c = toza.loc[umumiy, b].to_numpy(float), buz.loc[umumiy, b].to_numpy(float)
        farq = ~np.isclose(a, c)
        print(f"  {b:<16} buzilgan {farq.sum():>6} qator ({farq.mean():6.2%}), "
              f"o'rtacha: toza {np.nanmean(a):.3f}, xom {np.nanmean(c[np.isfinite(c)]):.3f}")

    print("\n=== 3. Tuzatish va qayta tekshiruv ===")
    tuz = {k: v.copy() for k, v in xom.items()}
    t = tuz["tranzaksiyalar"]                  # 2025-03 partiyasi ikki marta yuklangan:
    ustun = list(t.columns)                    # har takror guruhning yarmi qoladi
    n = t.assign(_i=1).groupby(ustun)["_i"].transform("size")
    tuz["tranzaksiyalar"] = t[(t.oy != 15) | (t.groupby(ustun).cumcount() < n // 2)]
    tl = tuz["tolovlar"].merge(tuz["hisobvaraqlar"], left_on=["mijoz_id", "hisob_oy"],
                               right_on=["mijoz_id", "oy"], how="left")
    birlik = (tl["summa"] > 10 * tl["balans"]).to_numpy()
    tuz["tolovlar"].loc[birlik, "summa"] = np.round(tuz["tolovlar"].loc[birlik, "summa"] / 1e6, 3)
    karantin = tuz["tolovlar"][~tuz["tolovlar"].mijoz_id.isin(tuz["mijozlar"].mijoz_id)]
    tuz["tolovlar"] = tuz["tolovlar"].drop(karantin.index)
    nol_limit = tuz["mijozlar"].mijoz_id[tuz["mijozlar"]["limit"] <= 0]
    for k in ["mijozlar", "hisobvaraqlar", "tolovlar", "tranzaksiyalar"]:
        tuz[k] = tuz[k][~tuz[k].mijoz_id.isin(nol_limit)]
    print(f"  2025-03 partiyasi bitta nusxaga qaytarildi, {birlik.sum()} to'lov birligi tuzatildi, "
          f"{len(karantin)} yetim karantinga, {len(nol_limit)} mijoz (limit 0) chiqarildi")
    n_tuz, _ = tekshir(tuz)
    qoldi = {k: v for k, v in n_tuz.items() if v > n_toza[k]}
    print(f"  qolgan muammolar: {qoldi} (yosh - modelda imputatsiya + bayroq)")
    t2 = belgilar_jadvali(tuz, [15, 16, 17]).set_index(["mijoz_id", "oy"])
    u = t2.index.intersection(toza.index)
    ustunlar = [b for b in BELGILAR if b != "yosh"]
    mos = np.allclose(t2.loc[u, ustunlar].to_numpy(float), toza.loc[u, ustunlar].to_numpy(float))
    print(f"  tuzatilgan belgilar toza manba bilan mos (yoshdan tashqari): {mos}")

    print("\n=== 4. Qarorga yo'naltirilgan EDA (toza, as-of 7-18; test oylari yopiq) ===")
    df = belgilar_jadvali(bank, range(7, 19))
    oylik = df.groupby("oy")["y60"].mean()
    print(f"  y60 oylar bo'yicha: min {oylik.min():.4f}, max {oylik.max():.4f}, "
          f"o'rtacha {oylik.mean():.4f}")
    df["faqat_y30"] = ((df.y30 == 1) & (df.y60 == 0)).astype(int)
    bins = pd.cut(df.util, [0, 0.2, 0.4, 0.6, 0.85, 1.0], include_lowest=True)
    t = df.groupby(bins, observed=True).agg(n=("y60", "size"), y60=("y60", "mean"))
    print("  util oralig'i bo'yicha y60:")
    for oraliq, n_, y_ in zip(t.index.astype(str), t.n, t.y60):
        print(f"    {oraliq:<14} n={n_:>6}  y60 {y_:.4f}")
    k6 = df.assign(k=df.kechikish_6.clip(upper=2)).groupby("k")[["y60", "faqat_y30"]].mean()
    print("  kechikish_6 (0, 1, 2+) bo'yicha: y60 "
          f"{np.round(k6.y60.to_numpy(), 4).tolist()}, faqat y30 "
          f"{np.round(k6.faqat_y30.to_numpy(), 4).tolist()}")
    yangi = df.staj < 6
    print(f"  yangi mijozlar (staj < 6 oy): ulush {yangi.mean():.3f}, "
          f"y60 {df.y60[yangi].mean():.4f} va eskilar {df.y60[~yangi].mean():.4f}")
    h = df.groupby("hudud")["y60"].mean()
    print(f"  hudud bo'yicha y60: {h.min():.4f} ({h.idxmin()}) dan "
          f"{h.max():.4f} ({h.idxmax()}) gacha")

    print("\n=== 5. Xulosa: EDA dan chiqqan qarorlar ===")
    sakrash = t.y60.iloc[-1] / t.y60.iloc[-2]
    if sakrash > 2 and t.n.iloc[-1] < 100:
        print(f"  util > 0.85 da y60 {sakrash:.1f} barobar yuqori, lekin atigi "
              f"{t.n.iloc[-1]} qator -> alohida belgi shart emas; util monoton va kuchli")
    elif sakrash > 2:
        print(f"  util > 0.85 da y60 {sakrash:.1f} barobar sakraydi -> chiziqsiz model sinaladi")
    if k6.faqat_y30.iloc[-1] > k6.y60.iloc[-1]:
        print("  ko'p kechikkanlar orasida 'o'zi to'laydiganlar' DPD60 dan ko'p -> "
              "kechikish_6 yolg'iz qoida sifatida yaroqsiz")
    if yangi.mean() < 0.1:
        print(f"  yangi mijozlar kam ({yangi.mean():.1%}) -> segment xatosi alohida "
              f"tekshiriladi 29.3-bob")


if __name__ == "__main__":
    main()

=== 1. Sifat tekshiruvlari: oldingi (toza) eksport va yangi eksport ===
  tekshiruv                     oldingi   yangi  holat
  mijoz_id takrori                    0       0  OK
  yosh bo'sh (NULL)                   0      40  XATO
  limit <= 0                          0      12  XATO
  yetim to'lov                        0      15  XATO
  to'lov > 10 * balans                0      25  XATO
  to'liq takror tranzaksiya          52   25487  XATO
  oylik hajm |z| > 5                  0       1  XATO
  hajmi g'ayrioddiy oylar: ['2025-03']

=== 2. Nuqsonlar belgilarga qanday o'tadi? (as-of 15-17) ===
  qatorlar: toza 13095, xom 13096
  naqd_soni_3      buzilgan   2012 qator (15.36%), o'rtacha: toza 0.525, xom 0.697
  xarid_soni_3     buzilgan  12594 qator (96.17%), o'rtacha: toza 10.620, xom 14.190
  tolov_nisbati_3  buzilgan      6 qator ( 0.05%), o'rtacha: toza 0.664, xom 85.000
  util             buzilgan     24 qator ( 0.18%), o'rtacha: toza 0.352, xom 0.352
  yosh             buzilgan     86 qator ( 0.66%), o'rtacha: toza 43.951, xom 43.994

=== 3. Tuzatish va qayta tekshiruv ===
  2025-03 partiyasi bitta nusxaga qaytarildi, 25 to'lov birligi tuzatildi, 15 yetim karantinga, 12 mijoz (limit 0) chiqarildi
  qolgan muammolar: {"yosh bo'sh (NULL)": 39} (yosh - modelda imputatsiya + bayroq)
  tuzatilgan belgilar toza manba bilan mos (yoshdan tashqari): True

=== 4. Qarorga yo'naltirilgan EDA (toza, as-of 7-18; test oylari yopiq) ===
  y60 oylar bo'yicha: min 0.0152, max 0.0259, o'rtacha 0.0195
  util oralig'i bo'yicha y60:
    (-0.001, 0.2]  n=  8776  y60 0.0013
    (0.2, 0.4]     n= 23232  y60 0.0083
    (0.4, 0.6]     n= 14990  y60 0.0306
    (0.6, 0.85]    n=  3874  y60 0.0826
    (0.85, 1.0]    n=    22  y60 0.4545
  kechikish_6 (0, 1, 2+) bo'yicha: y60 [0.0184, 0.0229, 0.0317], faqat y30 [0.0719, 0.1583, 0.1988]
  yangi mijozlar (staj < 6 oy): ulush 0.100, y60 0.0223 va eskilar 0.0192
  hudud bo'yicha y60: 0.0176 (Buxoro) dan 0.0226 (Qashqadaryo) gacha

=== 5. Xulosa: EDA dan chiqqan qarorlar ===
  util > 0.85 da y60 5.5 barobar yuqori, lekin atigi 22 qator -> alohida belgi shart emas; util monoton va kuchli
  ko'p kechikkanlar orasida 'o'zi to'laydiganlar' DPD60 dan ko'p -> kechikish_6 yolg'iz qoida sifatida yaroqsiz
  yangi mijozlar kam (10.0%) -> segment xatosi alohida tekshiriladi (29.3)

"""Vaqt bo'yicha bo'lish: bo'shliq, orqaga sinov, yetilmagan belgi va ma'lumot kartasi."""

import numpy as np
import pandas as pd
from sklearn.linear_model import LogisticRegression
from sklearn.metrics import roc_auc_score
from sklearn.model_selection import GroupShuffleSplit, train_test_split
from sklearn.pipeline import make_pipeline
from sklearn.preprocessing import StandardScaler

HUDUDLAR = ["Toshkent", "Samarqand", "Farg'ona", "Andijon", "Buxoro", "Qashqadaryo"]
OYLAR = 24                               # 1 = 2024-01, ..., 24 = 2025-12
BELGILAR = ["yosh", "daromad", "limit", "avto_tolov", "maosh_loyihasi", "staj",
            "util", "util_3", "util_ozg", "kechikish_6", "tolov_nisbati_3",
            "tolov_kuni_3", "naqd_soni_3", "naqd_summa_3", "xarid_soni_3"]
K, SAMARA, NARX = 200, 0.30, 0.02        # oyiga qo'ng'iroq, oldini olish, mln so'm


def sigmoid(z):
    return 1 / (1 + np.exp(-z))


def oy_nomi(m):
    return f"{2024 + (m - 1) // 12}-{(m - 1) % 12 + 1:02d}"


def yarat_bank(seed=29, n=6000):
    """Kredit karta portfeli: mijozlar, hisobvaraqlar, tolovlar, tranzaksiyalar."""
    rng = np.random.default_rng(seed)
    ids = np.arange(1, n + 1)
    yosh = rng.integers(21, 66, n)
    maosh = rng.random(n) < 0.45
    daromad = np.round(np.exp(rng.normal(np.log(6.0), 0.45, n)), 2)   # mln so'm
    ochilgan = np.where(rng.random(n) < 0.7, rng.integers(-40, 1, n),
                        rng.integers(1, 20, n))
    xavf = (rng.normal(0, 1, n) - 0.5 * maosh - 0.8 * np.log(daromad / 6)
            + 0.015 * (35 - yosh))                     # yashirin: moliyaviy xavf
    unut = rng.normal(0, 1, n) + 0.03 * (35 - yosh)     # yashirin: unutuvchanlik
    avto = (rng.random(n) < sigmoid(-0.2 - 0.9 * unut)).astype(int)
    limit = np.round(daromad * rng.uniform(1.5, 3.5, n), 1)
    hudud = rng.choice(HUDUDLAR, n, p=[0.34, 0.15, 0.14, 0.13, 0.12, 0.12])
    mj = pd.DataFrame({"mijoz_id": ids, "yosh": yosh, "hudud": hudud,
                       "daromad": daromad, "limit": limit, "avto_tolov": avto,
                       "maosh_loyihasi": maosh.astype(int), "ochilgan_oy": ochilgan})
    s, oldingi, ketma = rng.normal(0, 1, n), np.zeros(n, int), np.zeros(n, int)
    hv, tl, tr = [], [], []
    for m in range(1, OYLAR + 1):
        faol = (ochilgan <= m) & (ketma < 3)            # 3 ketma-ket kechikish - defolt
        s = 0.75 * s + 0.66 * rng.normal(0, 1, n)
        d = 0.8 * xavf + 0.7 * s + 0.15 * (m % 12 == 1) + 0.08 * max(m - 18, 0)
        util = np.clip(sigmoid(-0.4 + 0.55 * d + rng.normal(0, 0.5, n)), 0.01, 1)
        balans = np.round(util * limit, 3)
        min_t = np.round(np.maximum(0.05 * balans, 0.05), 3)
        for turi, lam in [("xarid", np.exp(1.2 + 0.15 * np.log(daromad / 6) - 0.1 * d)),
                          ("naqd", np.exp(-1.6 + 0.6 * d)),
                          ("onlayn", np.exp(0.6 - 0.02 * (yosh - 35)))]:
            i = np.repeat(np.arange(n), rng.poisson(lam) * faol)
            summa = (rng.uniform(0.2, 1.5, i.size) * (1 + 0.3 * d[i].clip(0))
                     if turi == "naqd" else np.exp(rng.normal(np.log(0.25), 0.8, i.size)))
            tr.append(pd.DataFrame({"mijoz_id": ids[i], "oy": m,
                                    "kun": rng.integers(1, 29, i.size), "turi": turi,
                                    "summa": np.round(summa, 3)}))
        q = rng.random(n) < sigmoid(-3.7 + 1.3 * d + 1.2 * (util > 0.85)
                                    + 3.8 * (oldingi == 2))          # qiyinchilik
        u = ~q & (rng.random(n) < sigmoid(-3.4 + 1.1 * unut - 1.8 * avto)
                  * (oldingi != 1))                                  # unutish
        miss = (q | u) & faol
        ulush = sigmoid(0.3 - 1.2 * d + rng.normal(0, 0.5, n))
        summa = np.where(miss, min_t * rng.uniform(0, 0.6, n),
                         min_t + (balans - min_t).clip(0) * ulush)
        tolaydi = faol & (~miss | (rng.random(n) < 0.4))
        kun = np.clip(np.round(6 + 14 * sigmoid(unut + 0.5 * d)
                               + rng.normal(0, 3, n)), 1, 25)
        hv.append(pd.DataFrame({"mijoz_id": ids[faol], "oy": m,
                                "balans": balans[faol], "min_tolov": min_t[faol]}))
        if m < OYLAR:                    # m-oy hisobvarag'i (m+1)-oyda to'lanadi
            tl.append(pd.DataFrame({"mijoz_id": ids[tolaydi], "hisob_oy": m,
                                    "tolov_oy": m + 1,
                                    "tolov_kuni": kun[tolaydi].astype(int),
                                    "summa": np.round(summa[tolaydi], 3)}))
        ketma = np.where(faol, np.where(miss, ketma + 1, 0), ketma)
        oldingi = np.where(miss, np.where(q, 2, 1), 0)
    return {"mijozlar": mj, "hisobvaraqlar": pd.concat(hv, ignore_index=True),
            "tolovlar": pd.concat(tl, ignore_index=True),
            "tranzaksiyalar": pd.concat(tr, ignore_index=True)}


def orta(a):
    """Qator bo'yicha NaN siz o'rtacha (bo'sh qatorda NaN, ogohlantirishsiz)."""
    k = (~np.isnan(a)).sum(axis=1)
    return np.where(k > 0, np.nansum(a, axis=1) / np.maximum(k, 1), np.nan)


def belgilar_jadvali(bank, oylar=range(7, 23)):
    """Har as-of oy t (oy oxiri): faqat t gacha ma'lum belgilar + t, t+1 natijasi."""
    mj = bank["mijozlar"]
    ids, kol = mj["mijoz_id"].to_numpy(), np.arange(1, OYLAR + 1)

    def mat(df, qiymat, ustun="oy"):
        return (df.pivot(index="mijoz_id", columns=ustun, values=qiymat)
                .reindex(index=ids, columns=kol).to_numpy(dtype=float))

    hv, tl = bank["hisobvaraqlar"], bank["tolovlar"]
    bal, mint = mat(hv, "balans"), mat(hv, "min_tolov")
    tol, kun = mat(tl, "summa", "hisob_oy"), mat(tl, "tolov_kuni", "hisob_oy")
    bor = ~np.isnan(bal)
    miss = bor & ~(np.nan_to_num(tol) >= mint - 1e-9)    # minimal to'lov qilinmagan
    g = (bank["tranzaksiyalar"].groupby(["turi", "mijoz_id", "oy"])["summa"]
         .agg(["size", "sum"]))

    def tr_mat(turi, ust):
        return (g.loc[turi, ust].unstack("oy").reindex(index=ids, columns=kol)
                .fillna(0).to_numpy())

    naqd_n, naqd_s = tr_mat("naqd", "size"), tr_mat("naqd", "sum")
    xarid_n = tr_mat("xarid", "size")
    util = bal / mj["limit"].to_numpy()[:, None]
    och = mj["ochilgan_oy"].to_numpy()
    qismlar = []
    for t in oylar:
        c = t - 1                                        # t-oy ustuni (0 dan)
        ok = (och <= t - 1) & bor[:, c] & bor[:, c - 1] & ~miss[:, c - 1]
        u3 = util[:, c - 3]
        f = mj.drop(columns="ochilgan_oy").assign(
            oy=t, staj=t - och, util=util[:, c], util_3=orta(util[:, c - 2:c + 1]),
            util_ozg=util[:, c] - np.where(np.isnan(u3), util[:, c], u3),
            kechikish_6=miss[:, max(c - 6, 0):c].sum(axis=1),
            tolov_nisbati_3=orta(np.nan_to_num(tol[:, c - 3:c]) / bal[:, c - 3:c]),
            tolov_kuni_3=orta(kun[:, c - 3:c]),
            naqd_soni_3=naqd_n[:, c - 2:c + 1].sum(axis=1),
            naqd_summa_3=naqd_s[:, c - 2:c + 1].sum(axis=1),
            xarid_soni_3=xarid_n[:, c - 2:c + 1].sum(axis=1),
            balans=bal[:, c], y30=(miss[:, c] | miss[:, c + 1]).astype(int),
            y60=(miss[:, c] & miss[:, c + 1]).astype(int))
        qismlar.append(f[ok])
    df = pd.concat(qismlar, ignore_index=True)
    df["tolov_kuni_3"] = df["tolov_kuni_3"].fillna(25.0)
    df["tolov_nisbati_3"] = df["tolov_nisbati_3"].fillna(0.0)
    df["zarar"] = 0.45 * df["balans"]              # DPD60 bo'lsa kutilgan zarar
    df["qiymat"] = SAMARA * df["zarar"] * df["y60"] - NARX   # qo'ng'iroq qiymati
    return df

BOLISH = {"o'quv": range(7, 15), "bo'shliq 1": [15], "val": range(16, 19),
          "bo'shliq 2": [19], "test": range(20, 23)}


def baholash(tr, va, ulush=K / 4300):
    """AUC, eng xavfli ulush ichida aniqlik va kalibrlash (o'rtacha p / haqiqiy ulush)."""
    m = make_pipeline(StandardScaler(), LogisticRegression(max_iter=2000))
    p = m.fit(tr[BELGILAR], tr["y60"]).predict_proba(va[BELGILAR])[:, 1]
    top = np.argsort(-p, kind="stable")[: int(ulush * len(va))]
    return (roc_auc_score(va["y60"], p), va["y60"].to_numpy()[top].mean(),
            p.mean() / va["y60"].mean())


def main() -> None:
    df = belgilar_jadvali(yarat_bank())
    print("=== 1. Bo'lish rejasi (as-of oylar; test belgilariga qaralmaydi) ===")
    print(f"  {'qism':<11} {'oylar':<18} {'qator':>6} {'y60':>5} {'ulush':>7}  belgi ma'lum")
    for nom, oylar in BOLISH.items():
        d = df[df.oy.isin(oylar)]
        oraliq = f"{oy_nomi(min(oylar))} - {oy_nomi(max(oylar))}"
        malum = oy_nomi(max(oylar) + 2)
        if nom == "test":
            print(f"  {nom:<11} {oraliq:<18} {len(d):>6} {'?':>5} {'?':>7}  {malum}")
        else:
            print(f"  {nom:<11} {oraliq:<18} {len(d):>6} {d.y60.sum():>5} "
                  f"{d.y60.mean():>7.4f}  {malum}")

    print("\n=== 2. Orqaga sinov: faqat 7-14 ma'lumoti bilan kelajakni baholash ===")
    tarix = df[df.oy <= 14].reset_index(drop=True)
    haqiqat = baholash(tarix, df[df.oy.between(16, 18)])
    sxemalar = {}
    a, b = train_test_split(tarix, test_size=0.2, random_state=0)
    sxemalar["tasodifiy qatorlar"] = baholash(a, b)
    i, j = next(GroupShuffleSplit(1, test_size=0.2, random_state=0)
                .split(tarix, groups=tarix.mijoz_id))
    sxemalar["mijoz bo'yicha"] = baholash(tarix.iloc[i], tarix.iloc[j])
    sxemalar["vaqt: 7-11 -> 13-14"] = baholash(tarix[tarix.oy <= 11], tarix[tarix.oy >= 13])
    print(f"  {'sxema':<22} {'AUC':>7} {'top aniqlik':>12} {'p/haqiqiy':>10}")
    for nom, (auc, prec, kal) in sxemalar.items():
        print(f"  {nom:<22} {auc:>7.4f} {prec:>12.4f} {kal:>10.3f}")
    print(f"  {'HAQIQAT: 7-14 -> 16-18':<22} {haqiqat[0]:>7.4f} {haqiqat[1]:>12.4f} "
          f"{haqiqat[2]:>10.3f}")
    xato = {nom: abs(v[0] - haqiqat[0]) for nom, v in sxemalar.items()}
    print(f"  AUC xatosi: " + ", ".join(f"{k} {v:.4f}" for k, v in xato.items()))
    oq_oylar = np.sort(a.oy.to_numpy())
    keyin = len(oq_oylar) - np.searchsorted(oq_oylar, b.oy.to_numpy() + 1, side="right")
    print(f"  tasodifiy bo'lishda val qatori uchun o'quvdagi 'kelajak' qatorlar "
          f"(belgisi undan keyin yetilgan): o'rtacha {keyin.mean() / len(oq_oylar):.1%}")

    print("\n=== 3. Yetilmagan belgi tuzog'i: T-oyda o'qitishga as-of T-1 ham qo'shilsa ===")
    print("  (as-of T-1 ning t+1 hisobvarag'i hali ma'lum emas -> ombordan y60 = 0 ko'rinadi)")
    natija = {"halol": [], "yetilmagan": []}
    for T in range(12, 19):
        va = df[df.oy == T]
        halol = df[df.oy <= T - 2]
        yet = df[df.oy <= T - 1].copy()
        yet.loc[yet.oy == T - 1, "y60"] = 0
        for nom, tr in [("halol", halol), ("yetilmagan", yet)]:
            natija[nom].append(baholash(tr, va))
    for nom, r in natija.items():
        r = np.array(r)
        kal = r[:, 2]
        print(f"  {nom:<11} AUC o'rtacha {r[:, 0].mean():.4f}, p/haqiqiy o'rtacha "
              f"{kal.mean():.3f} (SE {kal.std(ddof=1) / np.sqrt(len(kal)):.3f})")
    nisbat = np.array(natija["yetilmagan"])[:, 2] / np.array(natija["halol"])[:, 2]
    print(f"  yetilmagan / halol bashorat darajasi: har oyda {nisbat.min():.3f} - "
          f"{nisbat.max():.3f} (7/7 oyda past: {bool((nisbat < 1).all())})")

    print("\n=== 4. Ma'lumot kartasi (datasheet) - raqamlar natijadan ===")
    oq = df[df.oy.isin(BOLISH["o'quv"])]
    karta = [
        "## Ma'lumot kartasi: kredit karta kechikishi (v1)",
        f"- Davr: {oy_nomi(1)} - {oy_nomi(OYLAR)}; as-of oylar: "
        f"{oy_nomi(df.oy.min())} - {oy_nomi(df.oy.max())}",
        f"- Tahlil birligi: (mijoz, as-of oy); qatorlar: {len(df)}; "
        f"mijozlar: {df.mijoz_id.nunique()}",
        f"- Belgilar: {len(BELGILAR)} ta; hammasi as-of gacha (snapshot testi: 0 farq)",
        f"- Natija: y60, o'quvdagi ulush {oq.y60.mean():.4f}; yetilish 2 oy",
        "- Bo'lish: o'quv 7-14, val 16-18, test 20-22; bo'shliqlar 15 va 19",
        f"- Yangi mijozlar (staj < 6): {(df.staj < 6).mean():.1%}",
        "- Cheklovlar: sintetik; faraziy zarar (0.45 * balans); test oylari ochilmagan",
    ]
    for q in karta:
        print("  " + q)

    print("\n=== 5. Xulosa (natijadan hisoblangan) ===")
    for i, metrika in enumerate(["AUC", "top aniqlik", "p/haqiqiy"]):
        eng = min(sxemalar, key=lambda k: abs(sxemalar[k][i] - haqiqat[i]))
        print(f"  {metrika:<12} bo'yicha kelajakka eng yaqin: {eng}")
    if (nisbat < 1).all():
        print(f"  yetilmagan belgi bashoratni tizimli pasaytiradi (~{1 - nisbat.mean():.0%})")


if __name__ == "__main__":
    main()

=== 1. Bo'lish rejasi (as-of oylar; test belgilariga qaralmaydi) ===
  qism        oylar               qator   y60   ulush  belgi ma'lum
  o'quv       2024-07 - 2025-02   33358   697  0.0209  2025-04
  bo'shliq 1  2025-03 - 2025-03    4333    66  0.0152  2025-05
  val         2025-04 - 2025-06   13203   230  0.0174  2025-08
  bo'shliq 2  2025-07 - 2025-07    4475   102  0.0228  2025-09
  test        2025-08 - 2025-10   13163     ?       ?  2025-12

=== 2. Orqaga sinov: faqat 7-14 ma'lumoti bilan kelajakni baholash ===
  sxema                      AUC  top aniqlik  p/haqiqiy
  tasodifiy qatorlar      0.8177       0.1484      0.928
  mijoz bo'yicha          0.8306       0.1210      1.037
  vaqt: 7-11 -> 13-14     0.8372       0.1447      0.857
  HAQIQAT: 7-14 -> 16-18  0.8482       0.1156      1.088
  AUC xatosi: tasodifiy qatorlar 0.0305, mijoz bo'yicha 0.0176, vaqt: 7-11 -> 13-14 0.0110
  tasodifiy bo'lishda val qatori uchun o'quvdagi 'kelajak' qatorlar (belgisi undan keyin yetilgan): o'rtacha 32.6%

=== 3. Yetilmagan belgi tuzog'i: T-oyda o'qitishga as-of T-1 ham qo'shilsa ===
  (as-of T-1 ning t+1 hisobvarag'i hali ma'lum emas -> ombordan y60 = 0 ko'rinadi)
  halol       AUC o'rtacha 0.8380, p/haqiqiy o'rtacha 1.026 (SE 0.082)
  yetilmagan  AUC o'rtacha 0.8381, p/haqiqiy o'rtacha 0.893 (SE 0.080)
  yetilmagan / halol bashorat darajasi: har oyda 0.803 - 0.908 (7/7 oyda past: True)

=== 4. Ma'lumot kartasi (datasheet) - raqamlar natijadan ===
  ## Ma'lumot kartasi: kredit karta kechikishi (v1)
  - Davr: 2024-01 - 2025-12; as-of oylar: 2024-07 - 2025-10
  - Tahlil birligi: (mijoz, as-of oy); qatorlar: 68532; mijozlar: 5463
  - Belgilar: 15 ta; hammasi as-of gacha (snapshot testi: 0 farq)
  - Natija: y60, o'quvdagi ulush 0.0209; yetilish 2 oy
  - Bo'lish: o'quv 7-14, val 16-18, test 20-22; bo'shliqlar 15 va 19
  - Yangi mijozlar (staj < 6): 9.4%
  - Cheklovlar: sintetik; faraziy zarar (0.45 * balans); test oylari ochilmagan

=== 5. Xulosa (natijadan hisoblangan) ===
  AUC          bo'yicha kelajakka eng yaqin: vaqt: 7-11 -> 13-14
  top aniqlik  bo'yicha kelajakka eng yaqin: mijoz bo'yicha
  p/haqiqiy    bo'yicha kelajakka eng yaqin: mijoz bo'yicha
  yetilmagan belgi bashoratni tizimli pasaytiradi (~13%)

"WHERE h.oy BETWEEN :t - 5 AND :t"                                      # ⚠️
"WHERE h.oy BETWEEN :t - 6 AND :t - 1 ... AND p.tolov_oy <= :t"         # ✅

df = pd.read_sql("SELECT ..., kechikdi_keyin AS y FROM ...", con)       # ⚠️
X = pd.read_sql(BELGI_SQL, con, params=p); y = pd.read_sql(NATIJA_SQL, con, params=p)  # ✅

"FROM nomzod n JOIN tr_b r USING (mijoz_id)"                            # ⚠️ tranzaksiyasizlar yo'qoladi
"FROM nomzod n LEFT JOIN tr_b r USING (mijoz_id)" + "COALESCE(r.naqd_soni_3, 0)"  # ✅

tr = tr.drop_duplicates()                                               # ⚠️ 52 tabiiy takror ham ketadi
tr = tr[(tr.oy != 15) | (tr.groupby(ustun).cumcount() < n // 2)]       # ✅ faqat nuqsonli partiya

model = fit(belgilar(con, t))                                           # ⚠️
assert np.allclose(belgilar(con, t), belgilar(con, t, p="snap_"))       # ✅ CI da

tr, va = train_test_split(df, test_size=0.2)                            # ⚠️
tr, va = df[df.oy <= 14], df[df.oy.between(16, 18)]                     # ✅ bo'shliq bilan

oquv = df[df.oy <= joriy - 1]                                           # ⚠️ y60 hali 0 ko'rinadi
oquv = df[df.oy <= joriy - 2]                                           # ✅ gorizont = 2 oy

1.  # t-oy hisobvarag'ining to'lovi qaysi oyda keladi?
2.  # "oxirgi 6 hisobvaraq" sizuvchi oynada kechikish_6 ning AUC si qayoqqa?
3.  # sizuvchi model snapshot dan hisoblangan belgilarda - qoidadan yaxshimi?
4.  # halol so'rovda snapshot testi nechta farq topadi?
5.  # toza manbada to'liq takror tranzaksiyalar soni - nolmi?
6.  # birlik xatosi 6 qatorga tegdi - o'rtacha tolov_nisbati_3 ga ta'siri?
7.  # generatorda hudud ta'siri yo'q - hududlar orasidagi y60 farqi nima?
8.  # as-of 14 natijasi qachon ma'lum? val nega 16 dan boshlanadi?
9.  # yetilmagan as-of T-1 ni qo'shish AUC ga va kalibrlashga ta'siri?
10. # tasodifiy bo'lish bu ma'lumotda AUC ni oshirib ko'rsatdimi?

1.  sql = f"SELECT ... WHERE oy <= {t}"          # t - foydalanuvchidan

2.  "LEFT JOIN tolovlar p ON p.mijoz_id = h.mijoz_id AND p.hisob_oy = h.oy "
    "WHERE h.oy BETWEEN :t - 5 AND :t"

3.  "SELECT n.*, r.naqd_soni_3 FROM nomzod n JOIN tr_b r USING (mijoz_id)"

4.  xom = xom[xom.mijoz_id.isin(mijozlar.mijoz_id)]      # yetimlarni tashlaymiz

5.  eda = df.groupby("oy").y60.mean()                    # df - 7-22 hammasi