#!/usr/bin/env python3
"""Export every source Excel/CSV row into raw_import_rows.csv for lossless archival."""
from __future__ import annotations

import csv
import json
import uuid
from datetime import datetime
from pathlib import Path
from typing import Any, Dict, List

import openpyxl

ROOT = Path('/Users/bot1/Volumes/root_for_ai/AI工作区')
PROJECT_DIR = ROOT / '通用_货品台账系统_数据库表结构设计_20260612_0134'
OUT = PROJECT_DIR / 'data' / 'normalized_v1'
GUOBO = ROOT / '国博_合同台账_供应商采购补货梳理_20260610_2117'
SAMPLE = ROOT / '通用_公司产品样品库_秘色破茧云汉寻真_20260611_1035'
SIGNOFF = ROOT / '良渚_云汉寻真_3D悬浮手机贴西泠发货签收_20260611_0919'

FILES = [
    ('guobo_initial_ledger', GUOBO / 'deliverables/国博供应商采购补货台账_初版.xlsx'),
    ('guobo_downstream_purchase', GUOBO / 'deliverables/下游供应商采购表_四川极深奇乐_博航手信.xlsx'),
    ('guobo_upstream_purchase', GUOBO / 'deliverables/国博上游采购表_含渠道数量.xlsx'),
    ('guobo_replenishment_calc', GUOBO / 'deliverables/国博6-7月补货需求_vs_天猫调拨测算表.xlsx'),
    ('company_sample_library', SAMPLE / 'deliverables/公司产品样品库_秘色破茧_云汉寻真_20260611.xlsx'),
    ('yunhan_xiling_signoff', SIGNOFF / 'deliverables/云汉寻真_3D悬浮手机贴_西泠印社签收表_20260611.xlsx'),
]
CSV_FILES = list((GUOBO / 'work/extraction_tables').glob('*.csv')) + list((GUOBO / 'docs').glob('*.csv')) + list((ROOT / '通用_合同货品记录_事实台账_20260610_1700/registers').glob('*.csv'))

def clean(v: Any) -> str:
    if v is None:
        return ''
    return str(v).strip()

def row_payload(headers: List[str], vals: List[Any]) -> Dict[str, str]:
    return {headers[i] if headers[i] else f'col_{i+1}': clean(vals[i]) if i < len(vals) else '' for i in range(len(headers))}

def find_header(rows: List[tuple]) -> int:
    best_i, best_score = 0, -1
    for i, row in enumerate(rows[:8]):
        cells = [clean(c) for c in row]
        non = sum(1 for c in cells if c)
        uniq = len(set(c for c in cells if c))
        score = non + uniq
        if score > best_score:
            best_i, best_score = i, score
    return best_i

now = datetime.now().astimezone().isoformat(timespec='seconds')
# fetch import batch id from generated normalized import summary indirectly: use fixed code lookup later if empty
batch_id = ''
rows_out = []

for label, path in FILES:
    if not path.exists():
        continue
    wb = openpyxl.load_workbook(path, data_only=True, read_only=True)
    for ws in wb.worksheets:
        vals = list(ws.iter_rows(values_only=True))
        if not vals:
            continue
        hi = find_header(vals)
        headers = [clean(c) for c in vals[hi]]
        for idx, row in enumerate(vals[hi+1:], start=hi+2):
            payload = row_payload(headers, list(row))
            if not any(payload.values()):
                continue
            rows_out.append({
                'id': str(uuid.uuid4()),
                'import_batch_id': batch_id,
                'source_label': label,
                'source_file_path': str(path),
                'sheet_or_table': ws.title,
                'row_number': idx,
                'row_payload': json.dumps(payload, ensure_ascii=False),
                'created_at': now,
            })

for path in CSV_FILES:
    if not path.exists():
        continue
    with path.open(encoding='utf-8-sig', errors='replace', newline='') as f:
        reader = csv.DictReader(f)
        for idx, rec in enumerate(reader, start=2):
            if not any(clean(v) for v in rec.values()):
                continue
            rows_out.append({
                'id': str(uuid.uuid4()),
                'import_batch_id': batch_id,
                'source_label': 'csv_source',
                'source_file_path': str(path),
                'sheet_or_table': path.name,
                'row_number': idx,
                'row_payload': json.dumps({k: clean(v) for k,v in rec.items()}, ensure_ascii=False),
                'created_at': now,
            })

OUT.mkdir(parents=True, exist_ok=True)
out = OUT / 'raw_import_rows.csv'
cols = ['id','import_batch_id','source_label','source_file_path','sheet_or_table','row_number','row_payload','created_at']
with out.open('w', encoding='utf-8', newline='') as f:
    w = csv.DictWriter(f, fieldnames=cols)
    w.writeheader()
    w.writerows(rows_out)

summary = {
    'generated_at': now,
    'raw_import_rows': len(rows_out),
    'sources': sorted({r['source_file_path'] for r in rows_out}),
    'sheets': sorted({r['sheet_or_table'] for r in rows_out}),
}
(OUT / 'raw_import_rows_summary.json').write_text(json.dumps(summary, ensure_ascii=False, indent=2), encoding='utf-8')
print(json.dumps(summary, ensure_ascii=False, indent=2))