"""Bitable CLI subcommands.

Usage::

    agentkit bitable list-tables
    agentkit bitable create-table --name "Orders"
    agentkit bitable import-excel --file data.xlsx --table "Import"
    agentkit bitable query --table <id> --limit 20

CLI calls BitableService directly (KTD5 exception: CLI is an ops tool,
not a runtime call path).
"""

from __future__ import annotations

import asyncio
import os
from typing import Optional

import typer
from rich import print as rprint
from rich.table import Table

bitable_app = typer.Typer(
    name="bitable",
    help="Bitable (multi-dimensional table) management commands",
    no_args_is_help=True,
)


def _check_db_url() -> str:
    """Return DATABASE_URL or exit with a clear error."""
    db_url = os.environ.get("DATABASE_URL") or os.environ.get("AGENTKIT_DATABASE_URL")
    if not db_url:
        rprint("[red]Error: DATABASE_URL environment variable is not set.[/red]")
        rprint("[dim]Set it to your PostgreSQL connection string, e.g.:[/dim]")
        rprint("[dim]  export DATABASE_URL=postgresql+asyncpg://user@localhost/db[/dim]")
        raise typer.Exit(code=1)
    return db_url


async def _run_with_service(coro_factory):
    """Initialize DB, run coro_factory(service), then close — all in one event loop."""
    from agentkit.bitable.db import BitableDB
    from agentkit.bitable.service import BitableService

    db = BitableDB()
    try:
        await db.init()
        service = BitableService(db)
        return await coro_factory(service)
    finally:
        await db.close()


# ---------------------------------------------------------------------------
# list-tables
# ---------------------------------------------------------------------------


@bitable_app.command("list-tables")
def list_tables(
    owner: Optional[str] = typer.Option(None, "--owner", help="Filter by owner user ID"),
):
    """List all bitable tables."""
    _check_db_url()

    async def _run(service):
        tables = await service.list_tables(owner_user_id=owner)
        if not tables:
            rprint("[dim]No tables found.[/dim]")
            return
        tbl = Table(title="Bitable Tables")
        tbl.add_column("ID", style="cyan", no_wrap=True)
        tbl.add_column("Name", style="white")
        tbl.add_column("Description", style="dim")
        tbl.add_column("Created", style="green")
        for t in tables:
            tbl.add_row(
                t.id,
                t.name,
                t.description or "",
                t.created_at.strftime("%Y-%m-%d %H:%M") if t.created_at else "",
            )
        rprint(tbl)

    asyncio.run(_run_with_service(_run))


# ---------------------------------------------------------------------------
# create-table
# ---------------------------------------------------------------------------


@bitable_app.command("create-table")
def create_table(
    name: str = typer.Option(..., "--name", "-n", help="Table name"),
    description: str = typer.Option("", "--description", "-d", help="Table description"),
):
    """Create a new bitable table."""
    _check_db_url()

    async def _run(service):
        table = await service.create_table(name=name, description=description)
        rprint(f"[green]Created table:[/green] {table.name}")
        rprint(f"  ID: [cyan]{table.id}[/cyan]")
        if description:
            rprint(f"  Description: {description}")

    asyncio.run(_run_with_service(_run))


# ---------------------------------------------------------------------------
# import-excel
# ---------------------------------------------------------------------------


@bitable_app.command("import-excel")
def import_excel(
    file: str = typer.Option(..., "--file", "-f", help="Path to .xlsx file"),
    table_name: Optional[str] = typer.Option(
        None, "--table", "-t", help="Target table name (creates if not exists)"
    ),
    sheet: Optional[str] = typer.Option(None, "--sheet", "-s", help="Sheet name (default: first)"),
):
    """Import an Excel file into a bitable table.

    Creates a new table with inferred field types and inserts all rows.
    """
    _check_db_url()

    from agentkit.bitable.ingestion import parse_excel
    from agentkit.bitable.models import FieldType

    try:
        sheets = parse_excel(file)
    except FileNotFoundError:
        rprint(f"[red]Error: File not found: {file}[/red]")
        raise typer.Exit(code=1)
    except Exception as exc:
        rprint(f"[red]Error reading Excel file: {exc}[/red]")
        raise typer.Exit(code=1)

    if not sheets:
        rprint("[red]Error: No sheets found in Excel file.[/red]")
        raise typer.Exit(code=1)

    # Select sheet
    target = sheets[0]
    if sheet:
        target = next((s for s in sheets if s.name == sheet), None)
        if target is None:
            rprint(
                f"[red]Error: Sheet '{sheet}' not found. "
                f"Available: {[s.name for s in sheets]}[/red]"
            )
            raise typer.Exit(code=1)

    tbl_name = table_name or target.name

    async def _run(service):
        table = await service.create_table(name=tbl_name, description=f"Imported from {file}")
        rprint(f"[green]Created table:[/green] {table.name} (ID: {table.id})")

        # Create fields
        field_ids: list[str] = []
        for col_name, col_type in zip(target.columns, target.field_types):
            try:
                ft = FieldType(col_type)
            except ValueError:
                ft = FieldType.text
            field = await service.create_field(
                table_id=table.id,
                name=col_name,
                field_type=ft,
                config={},
            )
            field_ids.append(field.id)
            rprint(f"  Field: [cyan]{col_name}[/cyan] ({ft.value})")

        # Insert records (P2 #19: batch insert instead of per-record)
        if target.records:
            values_list: list[dict] = []
            for rec_values in target.records:
                values_dict = {}
                for idx, fid in enumerate(field_ids):
                    col_name = target.columns[idx]
                    if col_name in rec_values:
                        values_dict[fid] = rec_values[col_name]
                values_list.append(values_dict)
            created = await service.create_records_batch(table.id, values_list)
            rprint(f"[green]Imported {len(created)} records.[/green]")
        else:
            rprint("[yellow]No records found in sheet.[/yellow]")

    asyncio.run(_run_with_service(_run))


# ---------------------------------------------------------------------------
# query
# ---------------------------------------------------------------------------


@bitable_app.command("query")
def query(
    table_id: str = typer.Option(..., "--table", "-t", help="Table ID"),
    limit: int = typer.Option(20, "--limit", "-l", help="Max records to show"),
    cursor: Optional[str] = typer.Option(None, "--cursor", help="Pagination cursor"),
):
    """Query records from a bitable table."""
    _check_db_url()

    async def _run(service):
        table = await service.get_table(table_id)
        if table is None:
            rprint(f"[red]Error: Table '{table_id}' not found.[/red]")
            raise typer.Exit(code=1)

        fields = await service.list_fields(table_id)
        records, next_cursor = await service.list_records_filtered(
            table_id, cursor=cursor, limit=limit
        )

        if not records:
            rprint("[dim]No records found.[/dim]")
            return

        tbl = Table(title=f"{table.name} ({len(records)} records)")
        tbl.add_column("ID", style="cyan", no_wrap=True, width=12)
        for f in fields:
            tbl.add_column(f.name, style="white")

        for rec in records:
            row = [rec.id[:8] + "..."]
            for f in fields:
                val = rec.values.get(f.id)
                if val is None:
                    row.append("[dim]—[/dim]")
                elif isinstance(val, list):
                    row.append(f"[{len(val)} items]")
                elif isinstance(val, dict):
                    row.append("{...}")
                else:
                    row.append(str(val)[:50])
            tbl.add_row(*row)

        rprint(tbl)
        if next_cursor:
            rprint(f"[dim]Next cursor: {next_cursor}[/dim]")

    asyncio.run(_run_with_service(_run))
