"""Export commodities and commodity_form_schemas tables to Excel."""

from __future__ import annotations

import os
import sqlite3
from pathlib import Path

import pandas as pd
from dotenv import load_dotenv

ROOT = Path(__file__).resolve().parents[1]
load_dotenv(ROOT / ".env")

DB_PATH = Path(os.environ.get("UNSPSC_DB_PATH", ROOT / "data" / "unspsc.db"))
OUT_DIR = ROOT / "data"
COMMODITY_XLSX = OUT_DIR / "commodity.xlsx"
FORM_SCHEMA_XLSX = OUT_DIR / "commodity form schema.xlsx"


def main() -> None:
    if not DB_PATH.exists():
        raise FileNotFoundError(f"Database not found: {DB_PATH}")

    OUT_DIR.mkdir(parents=True, exist_ok=True)
    conn = sqlite3.connect(DB_PATH)

    commodities = pd.read_sql_query("SELECT * FROM commodities ORDER BY id", conn)
    form_schemas = pd.read_sql_query(
        "SELECT * FROM commodity_form_schemas ORDER BY commodity_code", conn
    )
    conn.close()

    commodities.to_excel(COMMODITY_XLSX, index=False, sheet_name="commodities")
    form_schemas.to_excel(FORM_SCHEMA_XLSX, index=False, sheet_name="commodity_form_schemas")

    print(f"Exported {len(commodities):,} rows -> {COMMODITY_XLSX}")
    print(f"Exported {len(form_schemas):,} rows -> {FORM_SCHEMA_XLSX}")


if __name__ == "__main__":
    main()