import pandas as pd
from pathlib import Path
from dagster import (
    asset,
)
from dagster_duckdb import DuckDBResource


@asset(
    group_name="config",
)
def agency_list(duckdb: DuckDBResource) -> None:
    """Load agency list from CSV into DuckDB."""

    # Ensure the database directory exists
    db_path = Path(duckdb.database)
    db_path.parent.mkdir(parents=True, exist_ok=True)

    # Read the CSV (path is relative to container working directory)
    df = pd.read_csv('config/agency_list.csv')

    # Write to DuckDB
    with duckdb.get_connection() as conn:
        conn.execute("""
            CREATE OR REPLACE TABLE agency_list AS
            SELECT * FROM df
        """)