"""
CREATE MATERIALIZED VIEW mlinkdw.mv_split_candidate
TABLESPACE pg_default
AS SELECT a.group_id,
    a.vender_code,
    count(*) AS cnt
   FROM mlinkdw.shopprod_group_map2 a
     JOIN mlinkdw.shopprod_group2 b ON a.group_id = b.group_id AND a.vender_code::text = b.winner_vender_code::text
  GROUP BY a.group_id, a.vender_code
 HAVING count(*) > 1
WITH DATA;
"""

import psycopg2
from psycopg2.extras import DictCursor, execute_values
from db_config import DB_INFO_PSYCOPG2

FETCH_SIZE = 2000

def refresh_mv(conn):
    with conn.cursor() as cur:
        cur.execute("REFRESH MATERIALIZED VIEW mlinkdw.mv_split_candidate;")
    conn.commit()


def split_groups(conn_read, conn_write):
    refresh_mv(conn_write)

    read_cur = conn_read.cursor(
        name="split_cursor",
        cursor_factory=DictCursor
    )

    read_cur.execute("""
        SELECT group_id, vender_code
        FROM mlinkdw.mv_split_candidate
        ORDER BY group_id
    """)

    write_cur = conn_write.cursor(cursor_factory=DictCursor)

    while True:
        rows = read_cur.fetchmany(FETCH_SIZE)
        if not rows:
            break

        # ==========
        # 배치 단위 최적화: (group_id, vender_code) 여러 건을 한 번에 처리
        # ==========
        pairs = [(r["group_id"], r["vender_code"]) for r in rows]
        # 중복 제거(순서 유지)
        seen = set()
        uniq_pairs = []
        for p in pairs:
            if p in seen:
                continue
            seen.add(p)
            uniq_pairs.append(p)
        pairs = uniq_pairs

        group_ids = list({gid for gid, _ in pairs})

        # 🔥 0️⃣ 현재 group에서 무효가 된 sub_group 정리 (배치)
        if group_ids:
            execute_values(
                write_cur,
                """
                SELECT sg.sub_group_id
                  FROM mlinkdw.shopprod_sub_group2 sg
                  JOIN mlinkdw.shopprod_group2 g ON g.group_id = sg.group_id
                  JOIN (VALUES %s) AS v(group_id) ON v.group_id = sg.group_id
                 WHERE sg.winner_vender_code != g.winner_vender_code
                   AND NOT EXISTS (
                        SELECT 1
                          FROM mlinkdw.shopprod_sub_group_map2 sm
                         WHERE sm.sub_group_id = sg.sub_group_id
                           AND sm.vender_code  = g.winner_vender_code
                   )
                """,
                [(gid,) for gid in group_ids],
            )
            obsolete_subs = [rr["sub_group_id"] for rr in write_cur.fetchall()]

            if obsolete_subs:
                execute_values(
                    write_cur,
                    """
                    DELETE FROM mlinkdw.shopprod_sub_group_map2 sm
                    USING (VALUES %s) AS v(sub_group_id)
                    WHERE sm.sub_group_id = v.sub_group_id
                    """,
                    [(sid,) for sid in obsolete_subs],
                )
                execute_values(
                    write_cur,
                    """
                    DELETE FROM mlinkdw.shopprod_sub_group2 sg
                    USING (VALUES %s) AS v(sub_group_id)
                    WHERE sg.sub_group_id = v.sub_group_id
                    """,
                    [(sid,) for sid in obsolete_subs],
                )

        # 1️⃣ sub_group 존재 여부 확인 (배치)
        execute_values(
            write_cur,
            """
            SELECT sg.group_id, sg.winner_vender_code, sg.sub_group_id
              FROM mlinkdw.shopprod_sub_group2 sg
              JOIN (VALUES %s) AS v(group_id, vender_code)
                ON sg.group_id = v.group_id
               AND sg.winner_vender_code = v.vender_code
            """,
            pairs,
        )
        sub_map = {(rr["group_id"], rr["winner_vender_code"]): rr["sub_group_id"] for rr in write_cur.fetchall()}

        missing = [p for p in pairs if p not in sub_map]

        # 신규 sub_group 생성 (winner는 임시 대표) - 배치
        if missing:
            execute_values(
                write_cur,
                """
                SELECT DISTINCT ON (m.group_id, m.vender_code)
                       m.group_id, m.vender_code, m.icode, m.price
                  FROM mlinkdw.shopprod_group_map2 m
                  JOIN (VALUES %s) AS v(group_id, vender_code)
                    ON m.group_id = v.group_id
                   AND m.vender_code = v.vender_code
                 ORDER BY m.group_id, m.vender_code, m.price NULLS LAST, m.icode
                """,
                missing,
            )
            winners = write_cur.fetchall()

            insert_rows = []
            for w in winners:
                insert_rows.append((w["group_id"], "WINNER_VENDOR_MULTI", w["icode"], w["vender_code"], w["price"]))

            if insert_rows:
                execute_values(
                    write_cur,
                    """
                    INSERT INTO mlinkdw.shopprod_sub_group2
                      (group_id, split_reason, winner_icode, winner_vender_code, winner_price, winner_updated_at)
                    VALUES %s
                    RETURNING sub_group_id, group_id, winner_vender_code
                    """,
                    insert_rows,
                    template="(%s, %s, %s, %s, %s, now())",
                )
                for ins in write_cur.fetchall():
                    sub_map[(ins["group_id"], ins["winner_vender_code"])] = ins["sub_group_id"]

        # 2️⃣ 현재 group_map 상품 (배치)
        execute_values(
            write_cur,
            """
            SELECT m.group_id, m.vender_code, m.icode, m.iname, m.price
              FROM mlinkdw.shopprod_group_map2 m
              JOIN (VALUES %s) AS v(group_id, vender_code)
                ON m.group_id = v.group_id
               AND m.vender_code = v.vender_code
            """,
            pairs,
        )
        current_rows = write_cur.fetchall()

        # 3️⃣ 기존 sub_group_map 상품 (배치)
        sub_ids = list({sub_map[p] for p in pairs if p in sub_map})
        existing_rows = []
        if sub_ids:
            execute_values(
                write_cur,
                """
                SELECT sm.sub_group_id, sm.vender_code, sm.icode
                  FROM mlinkdw.shopprod_sub_group_map2 sm
                  JOIN (VALUES %s) AS v(sub_group_id) ON v.sub_group_id = sm.sub_group_id
                """,
                [(sid,) for sid in sub_ids],
            )
            existing_rows = write_cur.fetchall()

        # (sub_group_id)별 current/existing set 구성
        current_keys_by_sub = {}
        current_detail_by_sub = {}
        for cr in current_rows:
            key_pair = (cr["group_id"], cr["vender_code"])
            sid = sub_map.get(key_pair)
            if not sid:
                continue
            k = (cr["vender_code"], cr["icode"])
            current_keys_by_sub.setdefault(sid, set()).add(k)
            current_detail_by_sub.setdefault(sid, {})[k] = cr

        existing_keys_by_sub = {}
        for er in existing_rows:
            sid = er["sub_group_id"]
            k = (er["vender_code"], er["icode"])
            existing_keys_by_sub.setdefault(sid, set()).add(k)

        inserts = []
        deletes = []
        for sid in sub_ids:
            cur_set = current_keys_by_sub.get(sid, set())
            ex_set = existing_keys_by_sub.get(sid, set())

            for k in cur_set - ex_set:
                d = current_detail_by_sub[sid][k]
                inserts.append((sid, d["vender_code"], d["icode"], d["iname"], d["price"]))

            for k in ex_set - cur_set:
                deletes.append((sid, k[0], k[1]))

        # 4️⃣ INSERT (신규) - 배치
        if inserts:
            execute_values(
                write_cur,
                """
                INSERT INTO mlinkdw.shopprod_sub_group_map2
                  (sub_group_id, vender_code, icode, iname, price)
                VALUES %s
                """,
                inserts,
            )

        # 5️⃣ DELETE (사라진 상품) - 배치
        if deletes:
            execute_values(
                write_cur,
                """
                DELETE FROM mlinkdw.shopprod_sub_group_map2 sm
                USING (VALUES %s) AS v(sub_group_id, vender_code, icode)
                WHERE sm.sub_group_id = v.sub_group_id
                  AND sm.vender_code  = v.vender_code
                  AND sm.icode        = v.icode
                """,
                deletes,
            )

        conn_write.commit()

    read_cur.close()
    write_cur.close()


if __name__ == "__main__":
    conn_read = psycopg2.connect(**DB_INFO_PSYCOPG2)
    conn_write = psycopg2.connect(**DB_INFO_PSYCOPG2)

    split_groups(conn_read, conn_write)

    conn_read.close()
    conn_write.close()