#!/usr/bin/env python3
"""
冠字号提取脚本 - 最终版
规则：只提取J0开头的冠字号（J0+8位或J0+9位）
"""
import sys
sys.path.insert(0, '/root/coolbot-data')

import re
import psycopg2
from datetime import datetime

DB_CONFIG = {
    'host': 'pgm-bp1t1008h019ez6c.pg.rds.aliyuncs.com',
    'port': 5432,
    'database': 'coolbot_data',
    'user': 'coolbot',
    'password': 'Coolbot123'
}

FEATURE_KEYWORDS = [
    '无47', '无34', '无347', '无4', '无3', '无2',
    '标十', '标百', '标九', '标准十', '标准百',
    '首日', '生日', '金马', '银马',
    '金钩', '倒置', '满号', '圆圆',
    '豹子', '顺子', '恐龙', '天龙',
    'PMG', '爱藏', '尾号',
    '大象', '麒麟', '老虎', '狮子',
    '金马王', '天马', '龙马精神',
    '连号', '散号', '一刀', '龙凤', '熊猫',
]

def extract_codes(text):
    """只提取J0开头的冠字号：J0+8位或J0+9位"""
    if not text:
        return set()
    codes = set()
    for p in [r'J0\d{8}', r'J0\d{9}']:
        for c in re.findall(p, text):
            # J0+8位: 总长10, J0+9位: 总长11
            if len(c) in (10, 11):
                codes.add(c)
    return codes

def extract_features(text):
    if not text:
        return None
    feat = [kw for kw in FEATURE_KEYWORDS if kw in text]
    return '|'.join(feat) if feat else None

def extract_price(text):
    if not text:
        return None, '元'
    prices = []
    for p in [r'(\d+(?:\.\d+)?)\s*元', r'(\d+(?:\.\d+)?)\s*/\s*[张件组百]']:
        for x in re.findall(p, text):
            try:
                v = float(x)
                if 1 <= v < 100000:
                    prices.append(v)
            except:
                pass
    return (min(prices), '元') if prices else (None, '元')

def get_category(title):
    if not title:
        return '其他'
    for kw in ['龙', '龙钞', '小龙钞', '钞王']:
        if kw in title:
            return '龙钞'
    for kw in ['马', '马钞']:
        if kw in title:
            return '马钞'
    for kw in ['蛇', '蛇钞']:
        if kw in title:
            return '蛇钞'
    return '其他'

def main():
    print(f'[{datetime.now()}] 冠字号提取开始（仅J0冠字号）...')

    conn = psycopg2.connect(**DB_CONFIG)
    cur = conn.cursor()

    # 清空旧数据
    cur.execute('DELETE FROM collections WHERE crown_code IS NOT NULL')
    conn.commit()
    print(f'清空旧数据完成')

    BATCH = 100
    offset = 0
    total_new = 0

    while True:
        cur.execute(f'''
            SELECT id, post_id, title, content, post_type,
                   author_username, price, price_unit, url, crawled_at
            FROM yichens_posts
            ORDER BY id
            LIMIT {BATCH} OFFSET {offset}
        ''')
        posts = cur.fetchall()
        if not posts:
            break

        for (pid, post_id, title, content, post_type,
             author, price, price_unit, url, crawled_at) in posts:

            text = f'{title or ""} {content or ""}'
            codes = extract_codes(text)
            if not codes:
                offset += 1
                continue

            features = extract_features(text)
            price_val, _ = extract_price(text)
            price_val = price_val if price_val else price
            category = get_category(title)

            for code in codes:
                try:
                    cur.execute('''
                        INSERT INTO collections (
                            name, crown_code, category, post_id, post_url,
                            author, price, price_unit,
                            number_feature, post_title, post_type,
                            post_crawled_at, created_at, updated_at
                        ) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,NOW(),NOW())
                    ''', (
                        code, code, category, str(post_id), url,
                        author, price_val, price_unit or '元',
                        features, title, post_type,
                        crawled_at
                    ))
                    total_new += 1
                except Exception as e:
                    pass

        conn.commit()
        offset += len(posts)
        print(f'  已处理 {offset} 条，新增 {total_new} 条')

    print(f'\n完成！总计新增: {total_new} 条')
    conn.close()

if __name__ == '__main__':
    main()