#!/usr/bin/env python3
"""Build BJD replacement/cross-reference opportunity database from email-derived meta files."""
from __future__ import annotations

import csv
import hashlib
import json
import re
from collections import Counter, defaultdict
from datetime import datetime, timezone
from pathlib import Path
from typing import Any

VAULT = Path('/home/nac/repo/obsidian-wiki-linux/kb/bjd-vault')
META = VAULT / '_meta'
OUT_DIR = VAULT / 'projects/bjd/references'
FAST = META / 'bjd-fast-product-lookup.jsonl'
THREADS = META / 'bjd-email-internal-thread-evidence-registry-20260611.jsonl'
SNIPPETS = META / 'bjd-email-attachment-extracted-snippets-20260611.jsonl'
RUN_DATE = '20260611'

BRANDS = {
    'Molex': r'Molex|MOLEX|莫仕|莫莱克斯',
    'Hirose': r'Hirose|HRS|广濑|FH\d+',
    'IRISO': r'IRISO|意力速',
    'TE': r'TE|Tyco|TYCO|泰科|AMP',
    'Rosenberger': r'Rosenberger|罗森博格|罗森',
    'Samtec': r'Samtec|SAMTEC',
    'Harwin': r'Harwin',
    'JST': r'JST|日压',
    'Kyocera': r'Kyocera|京瓷|AVX',
    'KEL': r'\bKEL\b',
    'JAE': r'\bJAE\b',
    'I-PEX': r'I-PEX|IPEX',
    'Kostal': r'Kostal',
    'UJU': r'\bUJU\b',
    'SMK': r'\bSMK\b',
    'Aptiv': r'Aptiv|APTIV|安波福',
    'Wurth': r'Wurth|Würth|WE|伍尔特',
    'JAM': r'JAM Global|\bJAM\b',
}
BRAND_RE = {k: re.compile(v, re.I) for k, v in BRANDS.items()}
REPLACEMENT_RE = re.compile(r'替代|对标|对照|对应|可替代|匹配|cross(?:ed)?|cross[- ]?reference|replacement|alternative|equivalent|against|matching information', re.I)
NEGATIVE_RE = re.compile(r'没有可替代|没有可以对标|无替代|暂无替代|not available|no replacement|cannot replace|不建议|无法替代', re.I)
NEED_DRAWING_RE = re.compile(r'提供.*图纸|请.*图纸|need.*drawing|datasheet|规格书|footprint|land pattern|确认.*图纸', re.I)
BJD_RE = re.compile(r'\b(?:1[0-9]{5}|2[0-9]{5}|9[0-9]{5})-[0-9A-Z]{4,}(?:-[A-Z0-9]+)?\b|\bX4\d{4}X?\b|\b1410[0-9]{2}-[0-9X]{6}\b|\b2410[0-9X]{2}-[0-9X]{6}\b', re.I)
# Part numbers near brand text; intentionally broad but filters common words later.
PN_RE = re.compile(r'\b(?=[A-Z0-9-]*\d)[A-Z0-9][A-Z0-9-]{3,24}[A-Z0-9]\b', re.I)
BAD_PN = {'BJD','PDF','SMT','DIP','SMD','CONN','PIN','PINS','FPC','FFC','WTB','BTB','RFQ','SOP','PPAP','VAVE','AUTO','DATE','PART','MPN','MFR','NO','PCS','RMB','USD','FOB','HTTP','HTTPS'}


def now_iso() -> str:
    return datetime.now(timezone.utc).replace(microsecond=0).isoformat().replace('+00:00','Z')


def read_jsonl(path: Path):
    with path.open(encoding='utf-8') as f:
        for line in f:
            line=line.strip()
            if line:
                yield json.loads(line)


def clean_text(s: str) -> str:
    s = re.sub(r'<[^>]+>', ' ', s or '')
    s = re.sub(r'&nbsp;|&amp;', ' ', s)
    s = re.sub(r'\s+', ' ', s).strip()
    return s


def infer_brand(text: str) -> list[str]:
    return [brand for brand, rx in BRAND_RE.items() if rx.search(text)]


def part_numbers(text: str) -> list[str]:
    out=[]
    for m in PN_RE.finditer(text):
        pn=m.group(0).strip('.,;:()[]{}<>')
        up=pn.upper()
        if up in BAD_PN or len(pn)<5:
            continue
        if re.fullmatch(r'20\d{2}[-/]?\d{1,2}[-/]?\d{1,2}', pn):
            continue
        if re.fullmatch(r'\d{4,}', pn) and len(pn) < 6:
            continue
        out.append(pn)
    # stable unique
    seen=set(); res=[]
    for x in out:
        key=x.upper()
        if key not in seen:
            seen.add(key); res.append(x)
    return res[:20]


def bjd_numbers(text: str) -> list[str]:
    seen=set(); out=[]
    for m in BJD_RE.finditer(text):
        pn=m.group(0).strip('.,;:()[]{}<>')
        key=pn.upper()
        if key not in seen:
            seen.add(key); out.append(pn)
    return out[:12]


def context_window(text: str, max_len: int = 700) -> str:
    text=clean_text(text)
    if len(text)<=max_len:
        return text
    m=REPLACEMENT_RE.search(text)
    if not m:
        return text[:max_len]
    start=max(0,m.start()-260); end=min(len(text),m.end()+440)
    return text[start:end]


def confidence(text: str, brands: list[str], bjds: list[str], comp_pns: list[str], source_type: str) -> str:
    if NEGATIVE_RE.search(text):
        return 'negative'
    if source_type == 'fast_lookup':
        return 'high'
    if brands and bjds and comp_pns and REPLACEMENT_RE.search(text):
        return 'high'
    if bjds and REPLACEMENT_RE.search(text):
        return 'medium'
    if brands and comp_pns and REPLACEMENT_RE.search(text):
        return 'medium'
    return 'low'


def stage_label(conf: str, text: str) -> str:
    if conf == 'negative':
        return '明确无替代/不建议'
    if NEED_DRAWING_RE.search(text):
        return '需图纸/工程复核'
    if re.search(r'报价|quote|RFQ|询价', text, re.I):
        return 'RFQ/报价评估'
    if re.search(r'样品|sample|承认|PPAP|SOP', text, re.I):
        return '样品/PPAP/项目推进'
    return '替代对标线索'


def record_id(payload: dict[str, Any]) -> str:
    raw=json.dumps(payload,ensure_ascii=False,sort_keys=True)
    return hashlib.sha1(raw.encode('utf-8')).hexdigest()[:12]


def add_record(records: list[dict[str, Any]], **kw):
    text=kw.get('evidence','') + ' ' + kw.get('subject','') + ' ' + kw.get('filename','')
    brands=kw.get('competitor_brands') or infer_brand(text)
    bjds=kw.get('bjd_candidates') or bjd_numbers(text)
    pns=kw.get('competitor_pns') or []
    if not pns:
        # remove BJD pns from generic pn list
        bjd_upper={x.upper() for x in bjds}
        pns=[x for x in part_numbers(text) if x.upper() not in bjd_upper][:12]
    conf=confidence(text, brands, bjds, pns, kw.get('source_type',''))
    if not (brands or bjds or pns or REPLACEMENT_RE.search(text)):
        return
    rec={
        'source_type': kw.get('source_type'),
        'source_ref': kw.get('source_ref'),
        'date': kw.get('date'),
        'customer_domains': kw.get('customer_domains') or [],
        'customers': kw.get('customers') or [],
        'subject': kw.get('subject') or '',
        'filename': kw.get('filename') or '',
        'product_families': kw.get('product_families') or [],
        'applications': kw.get('applications') or [],
        'competitor_brands': brands,
        'competitor_pns': pns,
        'bjd_candidates': bjds,
        'status': stage_label(conf, text),
        'confidence': conf,
        'evidence': context_window(kw.get('evidence') or text),
    }
    rec['id']=record_id(rec)
    records.append(rec)


def build_from_fast(records):
    for r in read_jsonl(FAST):
        if r.get('kind')!='competitor_cross_reference':
            continue
        comp=' '.join(filter(None,[str(r.get('competitor') or ''), str(r.get('competitor_pn') or '')]))
        text=' '.join([str(r.get('model') or ''), str(r.get('description') or ''), comp, str(r.get('bjd_pn') or ''), str(r.get('notes') or '')])
        add_record(
            records,
            source_type='fast_lookup', source_ref=r.get('source_page'), date='2026-04-22',
            subject=f"{r.get('source_slug')}:{r.get('source_sheet')} row {r.get('row_number')}",
            product_families=[r.get('product_line')] if r.get('product_line') else [],
            competitor_brands=infer_brand(comp or text), competitor_pns=[r.get('competitor_pn')] if r.get('competitor_pn') else part_numbers(comp),
            bjd_candidates=[r.get('bjd_pn')] if r.get('bjd_pn') else [], evidence=text,
        )


def build_from_threads(records):
    for r in read_jsonl(THREADS):
        subject=clean_text(r.get('subject',''))
        ev=' '.join(clean_text(x) for x in r.get('evidence') or [])
        text=subject+' '+ev
        if not REPLACEMENT_RE.search(text) and not infer_brand(text):
            continue
        # keep signal-heavy rows only
        if not (REPLACEMENT_RE.search(text) or bjd_numbers(text) or any(b in text for b in ['Molex','Hirose','IRISO','TE','Samtec','Harwin','Rosenberger'])):
            continue
        add_record(records, source_type='email_thread', source_ref=r.get('thread_id'), date=(r.get('date_end') or '')[:10],
                   customers=r.get('customers') or [], customer_domains=r.get('domains') or [], subject=subject,
                   product_families=r.get('families') or [], applications=r.get('applications') or [], evidence=text)


def build_from_snippets(records):
    for r in read_jsonl(SNIPPETS):
        subject='; '.join(r.get('subjects') or [])
        filename=r.get('filename') or ''
        text=clean_text(' '.join([filename, subject, r.get('text_excerpt') or '']))
        if not (REPLACEMENT_RE.search(text) or re.search(r'\b(MPN|Matching Information|Crossed competitor|Alternative to)\b', text, re.I)):
            continue
        if not (infer_brand(text) or bjd_numbers(text)):
            continue
        add_record(records, source_type='email_attachment', source_ref=r.get('absolute_path') or r.get('content_sha1'), date=(r.get('date_end') or '')[:10],
                   customer_domains=r.get('top_domains') or [], subject=subject, filename=filename,
                   product_families=[r.get('class')] if r.get('class') else [], evidence=text)


def dedupe(records):
    best={}
    rank={'high':3,'medium':2,'low':1,'negative':2}
    for r in records:
        key=(tuple(sorted(x.upper() for x in r['competitor_brands'])), tuple(x.upper() for x in r['competitor_pns'][:5]), tuple(x.upper() for x in r['bjd_candidates'][:5]), r['subject'][:80])
        if key not in best or rank.get(r['confidence'],0)>rank.get(best[key]['confidence'],0):
            best[key]=r
    out=list(best.values())
    out.sort(key=lambda r: ({'high':0,'medium':1,'negative':2,'low':3}.get(r['confidence'],4), r.get('date') or '', r.get('subject') or ''))
    return out


def write_jsonl(path, records):
    with path.open('w',encoding='utf-8') as f:
        for r in records:
            f.write(json.dumps(r,ensure_ascii=False)+'\n')


def write_csv(path, records):
    fields=['id','date','source_type','customer_domains','subject','filename','product_families','applications','competitor_brands','competitor_pns','bjd_candidates','status','confidence','source_ref','evidence']
    with path.open('w',encoding='utf-8-sig',newline='') as f:
        w=csv.DictWriter(f,fieldnames=fields)
        w.writeheader()
        for r in records:
            row={k:r.get(k,'') for k in fields}
            for k in ['customer_domains','product_families','applications','competitor_brands','competitor_pns','bjd_candidates']:
                row[k]='; '.join(row[k] or [])
            w.writerow(row)


def md_table(rows):
    lines=['| 原厂/品牌 | 原厂料号 | BJD候选 | 状态 | 证据 |','|---|---|---|---|---|']
    for r in rows:
        lines.append('| ' + ' | '.join([
            esc('; '.join(r['competitor_brands']) or '未提取'),
            esc('; '.join(r['competitor_pns'][:4]) or '未提取'),
            esc('; '.join(r['bjd_candidates'][:4]) or '未提取'),
            esc(r['status']+' / '+r['confidence']),
            esc((r.get('subject') or r.get('filename') or r.get('evidence',''))[:180]),
        ]) + ' |')
    return '\n'.join(lines)


def esc(s):
    return str(s).replace('|','\\|').replace('\n',' ')


def write_page(records, jsonl, csvp):
    ts=now_iso()
    by_conf=Counter(r['confidence'] for r in records)
    by_brand=Counter(b for r in records for b in r['competitor_brands'])
    by_family=Counter(f for r in records for f in r['product_families'])
    high=[r for r in records if r['confidence']=='high'][:40]
    negative=[r for r in records if r['confidence']=='negative'][:25]
    samtec=[r for r in records if any(b in {'Samtec','Harwin'} for b in r['competitor_brands'])][:25]
    molex=[r for r in records if 'Molex' in r['competitor_brands']][:35]
    page=OUT_DIR/f'bjd-email-replacement-cross-reference-database-{RUN_DATE}.md'
    lines=[
        '---',
        'title: BJD 邮件替代对标机会数据库',
        'category: reference',
        'project: bjd',
        'tags: [bjd, emails, replacement, cross-reference, rfq, visibility/internal]',
        'visibility: internal',
        'business_domains: [sales, engineering, products]',
        'data_classification: technical-confidential',
        f'updated: {ts}',
        '---','',
        '# BJD 邮件替代对标机会数据库','',
        '本页把跟单邮件线程、附件摘录和既有 fast lookup 中的替代/对标信息归并成结构化检索入口。它用于销售工程初筛，不等同于最终 drop-in 承诺；正式推荐仍需工程按图纸、land pattern、pitch、高度、pin 数、接点方向、包装和验证状态复核。','',
        '## 数据规模','',
        f'- 结构化记录：{len(records)} 条。',
        f'- 置信度分布：' + '；'.join(f'{k}={v}' for k,v in by_conf.most_common()),
        f'- 主要品牌：' + '；'.join(f'{k}={v}' for k,v in by_brand.most_common(12)),
        f'- 主要产品/来源族：' + '；'.join(f'{k}={v}' for k,v in by_family.most_common(10)),
        '',
        '## 使用口径','',
        '- `high`：有直接对照表、明确邮件语句，或同时出现原厂料号与 BJD 候选，可作为优先候选。',
        '- `medium`：有替代/RFQ语境和原厂品牌/料号，但 BJD 候选不完整，需要二次查图纸或产品族。',
        '- `negative`：邮件明确说没有可替代、不能对标或需要客户提供图纸；这类记录很重要，避免销售误推。',
        '- `low`：只有弱线索，适合放入待验证池。','',
        '## 高置信直接对照样例','', md_table(high), '',
        '## Molex 相关线索','', md_table(molex), '',
        '## Samtec / Harwin 相关线索','', md_table(samtec), '',
        '## 明确无替代或需谨慎的线索','', md_table(negative), '',
        '## 数据文件','',
        f'- JSONL：`_meta/{jsonl.name}`',
        f'- CSV：`_meta/{csvp.name}`',
        '- 主要来源：`_meta/bjd-email-internal-thread-evidence-registry-20260611.jsonl`、`_meta/bjd-email-attachment-extracted-snippets-20260611.jsonl`、`_meta/bjd-fast-product-lookup.jsonl`。',
    ]
    page.write_text('\n'.join(lines)+'\n',encoding='utf-8')
    update_meta(ts, page, jsonl, csvp, len(records))
    return page


def update_meta(ts, page, jsonl, csvp, n):
    rel=page.relative_to(VAULT).with_suffix('').as_posix()
    idx=VAULT/'index.md'
    if idx.exists():
        text=idx.read_text(encoding='utf-8')
        text=re.sub(r'最后更新：[^*]+', f'最后更新：{ts}', text)
        entry=f'- [[{rel}]] - 从邮件线程、附件摘录和 fast lookup 归并的替代对标机会数据库。 ( #bjd #replacement #emails #cross-reference )'
        if rel not in text:
            marker='## 综合分析\n'
            text=text.replace(marker, marker+entry+'\n', 1) if marker in text else text+'\n## 综合分析\n'+entry+'\n'
        idx.write_text(text,encoding='utf-8')
    with (VAULT/'log.md').open('a',encoding='utf-8') as f:
        f.write(f'\n- [{ts}] DATA_INGEST source="email-derived replacement signals" project="bjd" mode="replacement-cross-reference-database" records={n} page="{page.relative_to(VAULT).as_posix()}" jsonl="{jsonl.relative_to(VAULT).as_posix()}" csv="{csvp.relative_to(VAULT).as_posix()}" bot_visible=false\n')
    mp=VAULT/'.manifest.json'
    data={}
    if mp.exists():
        try: data=json.loads(mp.read_text(encoding='utf-8'))
        except Exception: data={}
    data.setdefault('sources',{})['derived:email-replacement-cross-reference-20260611']={
        'ingested_at':ts,'source_type':'derived_email_replacement_database','doc_type':'replacement_cross_reference','project':'bjd','records':n,
        'pages_created':[page.relative_to(VAULT).as_posix()],'data_paths':[jsonl.relative_to(VAULT).as_posix(), csvp.relative_to(VAULT).as_posix()], 'bot_visible':False
    }
    mp.write_text(json.dumps(data,ensure_ascii=False,indent=2)+'\n',encoding='utf-8')


def main():
    records=[]
    build_from_fast(records)
    build_from_threads(records)
    build_from_snippets(records)
    records=dedupe(records)
    jsonl=META/f'bjd-email-replacement-cross-reference-db-{RUN_DATE}.jsonl'
    csvp=META/f'bjd-email-replacement-cross-reference-db-{RUN_DATE}.csv'
    write_jsonl(jsonl,records)
    write_csv(csvp,records)
    page=write_page(records,jsonl,csvp)
    print(json.dumps({'records':len(records),'jsonl':str(jsonl),'csv':str(csvp),'page':str(page)},ensure_ascii=False))

if __name__=='__main__':
    main()