#!/usr/bin/env bash
# list SQLite schemas, tables, and columns (type, notnull, default, pk)
# Usage: ./database_schema_display.sh path/to/db.sqlite

set -euo pipefail

DB_PATH="${1:-}"

if [[ -z "$DB_PATH" ]]; then
  echo "Usage: $0 path/to/database.sqlite" >&2
  exit 1
fi
if [[ ! -f "$DB_PATH" ]]; then
  echo "Error: Database file '$DB_PATH' does not exist." >&2
  exit 1
fi

echo "=== Schemas (databases) ==="
# Show as-is for human info
sqlite3 "$DB_PATH" ".databases" || true
echo ""

# Get schema names via PRAGMA (stable across sqlite versions)
schemas=$(sqlite3 -noheader -separator '|' "$DB_PATH" "PRAGMA database_list;" | awk -F'|' '{print $2}')

# Fallback if empty (very old sqlite): assume main
if [[ -z "$schemas" ]]; then
  schemas="main"
fi

for schema in $schemas; do
  echo "=== Schema: $schema ==="

  # Get user tables (exclude internal sqlite_% tables)
  tables=$(sqlite3 -noheader "$DB_PATH" \
    "SELECT name
       FROM \"$schema\".sqlite_schema
      WHERE type='table' AND name NOT LIKE 'sqlite_%'
      ORDER BY name;")

  if [[ -z "$tables" ]]; then
    echo "  (no user tables)"
    echo ""
    continue
  fi

  # Iterate tables safely line-by-line
  while IFS= read -r table; do
    [[ -z "$table" ]] && continue
    echo "  -> Table: $table"
    # Columns: cid|name|type|notnull|dflt_value|pk
    sqlite3 -noheader -separator '|' "$DB_PATH" "PRAGMA \"$schema\".table_info('$table');" \
    | awk -F'|' '
      BEGIN {
        printf "     %-28s %-16s %-8s %-16s %-3s\n", "Column", "Type", "NotNull", "Default", "PK"
        printf "     %-28s %-16s %-8s %-16s %-3s\n", "------", "----", "-------", "-------", "--"
      }
      {
        dv = ($5 == "" ? "NULL" : $5)
        printf "     %-28s %-16s %-8s %-16s %-3s\n", $2, $3, $4, dv, $6
      }
    '
    echo ""
  done <<< "$tables"
done