{# --------------------------------------------------------------------------
   Template: sqlite_library.py.jinja2
   Target:   Python 3 / sqlite3
   Generates: Database creation, seed data insertion, and report queries.
   DO NOT EDIT the generated output — fix the data model or this template.
   -------------------------------------------------------------------------- #}
"""Auto-generated SQLite library database.

Generated from: {{ source_file }}
DO NOT EDIT — modify the DBML data model or Jinja2 template instead.
"""

import sqlite3


DATABASE = ":memory:"


def create_tables(conn: sqlite3.Connection) -> None:
    """Create all tables."""
    cursor = conn.cursor()
{% for table in tables %}

    cursor.execute("""
        CREATE TABLE IF NOT EXISTS {{ table.name }} (
{% for col in table.columns %}
            {{ col.name }} {{ col.sqlite_type }}
            {%- if col.is_pk %} PRIMARY KEY{% endif %}
            {%- if col.is_autoincrement %} AUTOINCREMENT{% endif %}
            {%- if col.is_not_null and not col.is_pk %} NOT NULL{% endif %}
            {%- if col.fk_table %} REFERENCES {{ col.fk_table }}({{ col.fk_column }}){% endif %}
            {%- if not loop.last %},{% endif %}

{% endfor %}
        )
    """)
{% endfor %}
    conn.commit()


def insert_seed_data(conn: sqlite3.Connection) -> None:
    """Insert sample data."""
    cursor = conn.cursor()
{% for seed in seeds %}

    # Seed: {{ seed.table }}
    cursor.executemany(
        "INSERT INTO {{ seed.table }} ({{ seed.columns | join(', ') }}) VALUES ({{ seed.columns | map('regex_replace', '.*', '?') | join(', ') }})",
        [
{% for row in seed.rows %}
            ({{ row | join(', ') }}),
{% endfor %}
        ],
    )
{% endfor %}
    conn.commit()

{% for query in queries %}

def report_{{ query.name }}(conn: sqlite3.Connection) -> None:
    """Run report: {{ query.name }}."""
    cursor = conn.cursor()
    cursor.execute("""
        {{ query.sql }}
    """)
    rows = cursor.fetchall()
    print("=== Active Loans ===")
    for title, borrower in rows:
        print(f"  {title} -> {borrower}")
    if not rows:
        print("  (no active loans)")
{% endfor %}


def main() -> None:
    """Entry point."""
    conn = sqlite3.connect(DATABASE)
    try:
        create_tables(conn)
        insert_seed_data(conn)
{% for query in queries %}
        report_{{ query.name }}(conn)
{% endfor %}
    finally:
        conn.close()


if __name__ == "__main__":
    main()