#!/usr/bin/env python3
"""
Minimal markdown-cache CLI - use sqlite3 for queries.

This tool only adds value where Python helps:
- Importing markdown with YAML frontmatter parsing
- Listing tables (convenience wrapper)
- Showing schema (convenience wrapper)

For everything else, use sqlite3 directly:
  sqlite3 cache.db "SELECT * FROM tasks WHERE status='active'"
  sqlite3 cache.db ".schema tasks"
"""

import click
from pathlib import Path
from sqlite_utils import Database
import subprocess
import sys


@click.group()
def cli():
    """Markdown cache - import markdown files into SQLite."""
    pass


@cli.command()
@click.option('--db', required=True, help='Database file path')
@click.option('--table', default='docs', help='Table name')
@click.option('--root', required=True, type=click.Path(exists=True), help='Root directory with markdown files')
@click.option('--pattern', default='**/*.md', help='Glob pattern for files')
@click.option('--verbose', '-v', is_flag=True, help='Verbose output')
def import_files(db, table, root, pattern, verbose):
    """Import markdown files into a table (creates or updates)."""
    # Just call the import.py script
    import_script = Path(__file__).parent / 'import.py'
    args = ['python3', str(import_script), '--db', db, '--table', table, '--root', root, '--pattern', pattern]
    if verbose:
        args.append('--verbose')

    result = subprocess.run(args)
    sys.exit(result.returncode)


@cli.command()
@click.option('--db', required=True, help='Database file path')
def tables(db):
    """List all tables in the database."""
    db_path = Path(db)
    if not db_path.exists():
        click.echo(f"❌ Database not found: {db}", err=True)
        return 1

    database = Database(str(db_path))
    table_list = database.table_names()

    if not table_list:
        click.echo("No tables in database")
        return

    click.echo(f"📊 Tables in {db}:")
    for table_name in table_list:
        count = database.execute(f"SELECT COUNT(*) FROM {table_name}").fetchone()[0]
        click.echo(f"  {table_name:20} ({count} rows)")


@cli.command()
@click.option('--db', required=True, help='Database file path')
@click.option('--table', required=True, help='Table name')
def schema(db, table):
    """Show schema for a table (convenience for .schema)."""
    db_path = Path(db)
    if not db_path.exists():
        click.echo(f"❌ Database not found: {db}", err=True)
        return 1

    database = Database(str(db_path))

    if table not in database.table_names():
        click.echo(f"❌ Table '{table}' not found", err=True)
        click.echo(f"\nAvailable tables: {', '.join(database.table_names())}")
        return 1

    click.echo(f"📋 Schema for '{table}':")
    click.echo()

    columns = database[table].columns
    click.echo(f"Columns ({len(columns)}):")
    for col in columns:
        click.echo(f"  {col.name:30} {col.type}")


@cli.command()
@click.option('--db', required=True, help='Database file path')
@click.argument('query')
def sql(db, query):
    """Execute SQL query (or just use sqlite3 directly)."""
    # Just call sqlite3 - it's better than wrapping it
    result = subprocess.run(['sqlite3', db, query])
    sys.exit(result.returncode)


if __name__ == '__main__':
    cli()
