import sys
sys.stdout.reconfigure(encoding='utf-8')

import sqlite3
import json
from pathlib import Path

SCRIPT_DIR = Path(__file__).parent
DATA_DIR = SCRIPT_DIR / 'data'
DB_PATH = DATA_DIR / 'appraisers.db'
PROGRESS_FILE = SCRIPT_DIR / 'query_progress.json'

# 讀取進度檔案
with open(PROGRESS_FILE, 'r', encoding='utf-8') as f:
    progress = json.load(f)

failed_names = [item['name'] for item in progress['failed']]

print(f"找到 {len(failed_names)} 個失敗的姓名：")
for name in failed_names:
    print(f"  - {name}")

# 從 staging 表中刪除這些記錄
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()

for name in failed_names:
    cursor.execute('DELETE FROM certificate_staging WHERE query_name = ?', (name,))
    print(f"✓ 已從 staging 刪除: {name}")

conn.commit()
conn.close()

# 清空 failed 清單
progress['failed'] = []
with open(PROGRESS_FILE, 'w', encoding='utf-8') as f:
    json.dump(progress, f, ensure_ascii=False, indent=2)

print(f"\n✓ 已清空進度檔案的 failed 清單")
print(f"✓ 這些姓名現在可以重新查詢")