#!/usr/bin/env python3
import argparse
import csv
import sqlite3
from pathlib import Path


EXPORT_HEADERS = [
    ("root_path", "source_root（来源根路径）"),
    ("spu", "spu（SPU）"),
    ("directory_name", "directory_name（目录名称）"),
    ("relative_path", "relative_path（相对路径）"),
    ("windows_path", "windows_path（Windows路径）"),
    ("sku_dir_path", "sku_dir_path（SKU目录完整路径）"),
    ("parent_path", "parent_path（上级目录）"),
    ("grandparent_path", "grandparent_path（上上级目录）"),
    ("depth", "depth（目录层级）"),
    ("file_count", "file_count（文件数）"),
    ("image_count", "image_count（图片数）"),
    ("child_dir_count", "child_dir_count（子目录数）"),
    ("has_detail_dir_sibling", "has_detail_dir_sibling（同级有详情页或images）"),
    ("has_main_dir_sibling", "has_main_dir_sibling（同级有主图）"),
    ("has_detail_and_main_siblings", "has_detail_and_main_siblings（同级同时有详情页和主图）"),
    ("sibling_detail_dir_names_json", "sibling_detail_dir_names_json（同级详情目录）"),
    ("sibling_main_dir_names_json", "sibling_main_dir_names_json（同级主图目录）"),
    ("sibling_dir_names_json", "sibling_dir_names_json（同级全部目录）"),
    ("sample_files_json", "sample_files_json（示例文件）"),
    ("created_at", "created_at（创建时间）"),
    ("scan_id", "scan_id（扫描批次ID）"),
]


def parse_args():
    parser = argparse.ArgumentParser(description="Export sku folder scan results from sqlite to CSV.")
    parser.add_argument(
        "--db-path",
        default="/root/gongxiangp2/output/history-sku-folders.db",
        help="SQLite database file path.",
    )
    parser.add_argument(
        "--output",
        default="历史SKU目录清单.csv",
        help="Output CSV path.",
    )
    parser.add_argument(
        "--scan-id",
        default="",
        help="Optional scan_id filter. Empty means export all rows.",
    )
    parser.add_argument(
        "--only-detail-main",
        action="store_true",
        help="Only export rows whose sibling folders contain both detail/images and main image folders.",
    )
    return parser.parse_args()


def main():
    args = parse_args()
    output_path = Path(args.output)
    output_path.parent.mkdir(parents=True, exist_ok=True)

    conn = sqlite3.connect(args.db_path)
    conn.row_factory = sqlite3.Row
    try:
        where_clauses = []
        params = []

        if args.scan_id:
            where_clauses.append("scan_id = ?")
            params.append(args.scan_id)

        if args.only_detail_main:
            where_clauses.append("has_detail_and_main_siblings = 1")

        where_sql = ""
        if where_clauses:
            where_sql = "WHERE " + " AND ".join(where_clauses)

        query = f"""
        SELECT
          scan_id,
          root_path,
          spu,
          directory_name,
          relative_path,
          sku_dir_path AS windows_path,
          sku_dir_path,
          parent_path,
          grandparent_path,
          depth,
          file_count,
          image_count,
          child_dir_count,
          has_detail_dir_sibling,
          has_main_dir_sibling,
          has_detail_and_main_siblings,
          sibling_detail_dir_names_json,
          sibling_main_dir_names_json,
          sibling_dir_names_json,
          sample_files_json,
          created_at
        FROM sku_folder_scan_items
        {where_sql}
        ORDER BY root_path, relative_path
        """

        rows = conn.execute(query, params).fetchall()

        with output_path.open("w", encoding="utf-8-sig", newline="") as handle:
            writer = csv.writer(handle)
            writer.writerow([header for _, header in EXPORT_HEADERS])
            for row in rows:
                writer.writerow([row[column] for column, _ in EXPORT_HEADERS])

        print(f"exported_rows={len(rows)}")
        print(f"output={output_path}")
    finally:
        conn.close()


if __name__ == "__main__":
    main()