"""Python 3 standard-library demo. Run: python clean_products.py products-before.csv"""
import csv
import datetime
import json
import re
import sys
import unicodedata
from collections import Counter
from pathlib import Path

HEADERS = ['商品ID', '商品名', 'カテゴリ', '単価', '在庫', '更新日']

def review(source):
    with Path(source).open(encoding='utf-8-sig', newline='') as stream:
        records = list(csv.reader(stream))
    if not records or records[0] != HEADERS:
        raise ValueError('CSVヘッダーが指定の6列と異なります')
    rows = []
    for number, record in enumerate(records[1:], 2):
        if not any(record):
            continue
        if len(record) != 6:
            raise ValueError(f'{number}行目が6列ではありません')
        cells = [unicodedata.normalize('NFKC', x).strip() for x in record]
        cells[0] = cells[0].upper()
        for index in (3, 4):
            cells[index] = re.sub(r'[¥￥,\s]', '', cells[index])
        cells[5] = cells[5].replace('/', '-')
        rows.append({'sourceRow': number, 'cells': cells, 'issues': []})
    ids = Counter(row['cells'][0] for row in rows)
    for row in rows:
        ident, name, category, price, stock, date = row['cells']
        issues = row['issues']
        if not ident:
            issues.append('商品ID未入力')
        elif ids[ident] > 1:
            issues.append('商品ID重複')
        if not name:
            issues.append('商品名未入力')
        if not category:
            issues.append('カテゴリ未入力')
        if not re.fullmatch(r'-?\d+(?:\.\d+)?', price) or float(price) < 0:
            issues.append('単価を確認')
        if not re.fullmatch(r'\d+', stock):
            issues.append('在庫を確認')
        try:
            if not re.fullmatch(r'\d{4}-\d{1,2}-\d{1,2}', date):
                raise ValueError('invalid date')
            row['cells'][5] = datetime.date(*map(int, date.split('-'))).isoformat()
        except ValueError:
            issues.append('更新日を確認')
    return {'rows': rows, 'total': len(rows), 'duplicateRows': sum('商品ID重複' in r['issues'] for r in rows), 'reviewRows': sum(bool(r['issues']) for r in rows)}

def export(result, destination):
    # Formula-like text is exported as literal data for spreadsheet review.
    safe = lambda x: "'" + str(x) if re.match(r'^[=+@-]', str(x)) else x
    with Path(destination).open('w', encoding='utf-8-sig', newline='') as stream:
        writer = csv.writer(stream)
        writer.writerow(HEADERS + ['原本行', '確認事項'])
        for row in result['rows']:
            writer.writerow([safe(x) for x in row['cells'] + [row['sourceRow'], '／'.join(row['issues']) or '確認項目なし']])

if __name__ == '__main__':
    source = Path(sys.argv[1] if len(sys.argv) > 1 else 'products-before.csv')
    result = review(source)
    export(result, source.with_name('products-reviewed.csv'))
    source.with_name('review-report.json').write_text(json.dumps(result, ensure_ascii=False, indent=2), encoding='utf-8')
    print(f"照合完了 {result['total']}行 / 要確認 {result['reviewRows']}行 / 削除なし")