"""
   @Author      : CT
   @Description : 按站点增量追平用户筛选模式选品结果，写入 Doris selection.user_selection_pattern
                  - 传入 site_name，只处理该站点的 active filter
                  - 纯增量追平：水位线落后于该站点最新月表时补齐缺失月份，不做刷新判断
                    （us 站点利润率/Keepa 字段每日刷新型 filter，最新月差异化刷新见 dws_flow_asin_refresh.py 第5步）
                  - 支持断点续算：latest_computed_month 作为水位线，跳过已算月份
                  - Doris AGGREGATE KEY(filter_id, asin) + MIN(date_info) 自动保留首次入选月份
                  - 每月执行一次即可（只有新月表出现时才会产生缺口）
执行示例: python dwt_user_selection_pattern.py us
"""
import os
import sys
from datetime import datetime

sys.path.append(os.path.dirname(sys.path[0]))
from utils.DorisHelper import DorisHelper
from utils.db_util import DBUtil, DbTypes
import pymysql
import pandas as pd

# ===== 配置 =====
MYSQL_FILTER_TABLE = 'flow_increment_filter_sql'  # MySQL 筛选模式日志表
DORIS_RESULT_DB    = 'selection'
DORIS_RESULT_TABLE = 'user_selection_pattern'
SUPPORTED_SITES    = ('us', 'uk', 'de')


# ===== 连接工厂 =====

def _get_doris_conn():
    info = DorisHelper.get_connection_info('selection')
    return pymysql.connect(
        host=info['ip'],
        port=info['jdbc_port'],
        user=info['user'],
        password=info['pwd'],
        database=DORIS_RESULT_DB,
        charset='utf8mb4',
        autocommit=True,
    )


def _get_mysql_engine():
    """flow_increment_filter_sql 是跨站点共享的控制库表(带 site 字段区分)，固定连 us 实例"""
    return DBUtil.get_db_engine(db_type=DbTypes.mysql.name, site_name='us')


# ===== 工具函数 =====

def _next_month(month_str):
    """'2026-05' → '2026-06'"""
    y, m = int(month_str[:4]), int(month_str[5:])
    m += 1
    if m > 12:
        y, m = y + 1, 1
    return f'{y}-{m:02d}'


def _get_available_months(doris_cur, site):
    """返回指定站点已存在的月表对应月份列表，升序，排除 _test 表"""
    prefix = f'{site}_flow_asin_month_'
    doris_cur.execute("""
        SELECT TABLE_NAME FROM information_schema.TABLES
        WHERE TABLE_SCHEMA = %s
          AND TABLE_NAME LIKE %s
          AND TABLE_NAME NOT LIKE '%%\_test'
        ORDER BY TABLE_NAME
    """, (DORIS_RESULT_DB, f'{prefix}%'))
    months = []
    for (table_name,) in doris_cur.fetchall():
        suffix = table_name[len(prefix):]    # '2026_05'
        month  = suffix.replace('_', '-')   # '2026-05'
        if len(month) == 7:
            months.append(month)
    return months


def _update_mysql_log(mysql_conn, filter_id, latest_month, status, msg):
    now = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    if latest_month is not None:
        # 正常情况：同步更新水位线和状态
        sql    = f"""UPDATE `{MYSQL_FILTER_TABLE}`
                     SET latest_computed_month = %s,
                         last_run_status       = %s,
                         last_run_at           = %s,
                         last_run_msg          = %s
                     WHERE filter_id = %s"""
        params = (latest_month, status, now, msg[:500], filter_id)
    else:
        # latest_month 为 None：仅记录失败状态，不更新水位线
        sql    = f"""UPDATE `{MYSQL_FILTER_TABLE}`
                     SET last_run_status = %s,
                         last_run_at     = %s,
                         last_run_msg    = %s
                     WHERE filter_id = %s"""
        params = (status, now, msg[:500], filter_id)
    cur = mysql_conn.cursor()
    cur.execute(sql, params)
    mysql_conn.commit()
    cur.close()


# ===== 核心计算 =====

def _backfill_one_filter(row, site_name, available, doris_cur, mysql_conn):
    """水位线落后于最新月表时，纯新增 INSERT 追平缺失月份（不做刷新判断）"""
    filter_id   = row['filter_id']
    base_month  = row['base_month']
    where_sql   = row['where_sql']
    latest_done = row['latest_computed_month']  # None 表示从未计算过

    latest_available = available[-1]
    start_month = _next_month(latest_done) if latest_done else base_month
    all_months  = [m for m in available if start_month <= m <= latest_available]
    if not all_months:
        print(f"    [SKIP] 无需计算（latest_computed={latest_done}，已追平至最新月 {latest_available}）")
        return

    print(f"    [新增] 计算范围：{all_months[0]} ~ {all_months[-1]}，共 {len(all_months)} 个月")
    last_ok_month = None
    for month in all_months:
        table = f'{site_name}_flow_asin_month_{month.replace("-", "_")}'
        sql = f"""
            INSERT INTO `{DORIS_RESULT_DB}`.`{DORIS_RESULT_TABLE}`
                (filter_id, asin, date_info)
            SELECT
                {filter_id}  AS filter_id,
                asin,
                '{month}'    AS date_info
            FROM `{DORIS_RESULT_DB}`.`{table}`
            WHERE {where_sql}
        """
        try:
            doris_cur.execute(sql)
            print(f"      [OK] {month}")
            last_ok_month = month
        except Exception as e:
            err = str(e)[:200]
            print(f"      [FAIL] {month}  错误：{err}")
            _update_mysql_log(mysql_conn, filter_id, last_ok_month, 'failed', err)
            return

    _update_mysql_log(
        mysql_conn, filter_id, last_ok_month, 'success',
        f'完成：新增 {len(all_months)} 个月（{all_months[0]} ~ {all_months[-1]}）'
    )


# ===== 入口 =====

def main():
    assert len(sys.argv) == 2 and sys.argv[1] in SUPPORTED_SITES, \
        f"用法: python dwt_user_selection_pattern.py <site_name>，site_name 需为 {SUPPORTED_SITES} 之一"
    site_name = sys.argv[1]

    doris_conn = _get_doris_conn()
    engine     = _get_mysql_engine()
    mysql_conn = engine.raw_connection()
    try:
        doris_cur = doris_conn.cursor()

        available = _get_available_months(doris_cur, site_name)
        if not available:
            print(f"[SKIP] 站点 {site_name} 无可用月表")
            return
        print(f"[站点 {site_name}] 可用月份：{available[0]} ~ {available[-1]}（共 {len(available)} 个）")

        df = pd.read_sql(
            f"""SELECT filter_id, base_month, where_sql, latest_computed_month
                FROM `{MYSQL_FILTER_TABLE}`
                WHERE status = 'active' AND site = '{site_name}'
                ORDER BY filter_id""",
            engine
        )
        total = len(df)
        print(f"\n共 {total} 条 active 筛选模式（站点={site_name}），开始串行计算...\n")

        for i, row in df.iterrows():
            print(f"[{i + 1}/{total}] filter_id={row['filter_id']}")
            try:
                _backfill_one_filter(row, site_name, available, doris_cur, mysql_conn)
            except Exception as e:
                print(f"    [ERROR] 未预期异常：{e}")

        print("\n全部完成")
    finally:
        doris_conn.close()
        mysql_conn.close()


if __name__ == '__main__':
    main()
