Makefile.database

# Database management commands for Kindle Automator

# Auto-detect environment file
DB_ENV_FILE := $(shell if [ -f .env ]; then echo .env; elif [ -f .env.staging ]; then echo .env.staging; elif [ -f .env.prod ]; then echo .env.prod; else echo .env; fi)

# Use dotenv run to load environment
PYTHON := uv run dotenv -f $(DB_ENV_FILE) run python

# Setup database privileges (requires POSTGRES_DOADMIN_DATABASE_URL)
.PHONY: db-setup-privileges
db-setup-privileges:
	@echo "Setting up database privileges..."
	@$(PYTHON) scripts/setup_db_privileges.py $(DB_ENV_FILE)

# Initialize database
.PHONY: db-init
db-init:
	@echo "Initializing Kindle database..."
	@$(PYTHON) scripts/db_connect.py --command init --env $(DB_ENV_FILE) --file database/init/01_create_database.sql

# Run Alembic migrations
.PHONY: db-migrate
db-migrate:
	@echo "Running database migrations..."
	@uv run dotenv -f $(DB_ENV_FILE) run python -m alembic upgrade head

# Software-based database export to JSON (works on any server)
.PHONY: db-export
db-export:
	@echo "Exporting database to JSON format..."
	@mkdir -p backups
	@$(PYTHON) scripts/db_export.py
	@echo "Export complete! Check backups/ directory for the JSON file"

# Software-based database import from JSON (works on any server)
.PHONY: db-import
db-import:
	@if [ -z "$(FILE)" ]; then \
		echo "Usage: make db-import FILE=backups/kindle_db_export_YYYYMMDD_HHMMSS.json"; \
		echo ""; \
		echo "Available backup files:"; \
		ls -la backups/*.json 2>/dev/null || echo "  No backup files found"; \
		exit 1; \
	fi
	@echo "Importing database from $(FILE)..."
	@$(PYTHON) scripts/db_import.py $(FILE)

# Full database setup: privileges (if needed), init, migrate, import
.PHONY: db-setup
db-setup:
	@if [ -n "$$(grep -E '^POSTGRES_DOADMIN_DATABASE_URL=' $(DB_ENV_FILE) 2>/dev/null)" ]; then \
		echo "Admin URL found, setting up privileges first..."; \
		$(MAKE) db-setup-privileges DB_ENV_FILE=$(DB_ENV_FILE); \
	fi
	@$(MAKE) db-init DB_ENV_FILE=$(DB_ENV_FILE)
	@$(MAKE) db-migrate DB_ENV_FILE=$(DB_ENV_FILE)
	@$(MAKE) db-import DB_ENV_FILE=$(DB_ENV_FILE)
	@echo "Database setup complete!"

# Connect to database with psql
.PHONY: db-connect
db-connect:
	@echo "Connecting to kindle_db..."
	@$(PYTHON) scripts/db_connect.py --command interactive --env $(DB_ENV_FILE)

# Show database status
.PHONY: db-status db-stats
db-status db-stats:
	@echo "Checking database status..."
	@$(PYTHON) scripts/db_connect.py --command query --env $(DB_ENV_FILE) $(if $(DEBUG),--debug) --args "\
		SELECT 'Users:' as table_name, COUNT(*) as count FROM users \
		UNION ALL \
		SELECT 'Emulator Settings:', COUNT(*) FROM emulator_settings \
		UNION ALL \
		SELECT 'Device Identifiers:', COUNT(*) FROM device_identifiers \
		UNION ALL \
		SELECT 'Library Settings:', COUNT(*) FROM library_settings \
		UNION ALL \
		SELECT 'Reading Settings:', COUNT(*) FROM reading_settings \
		UNION ALL \
		SELECT 'User Preferences:', COUNT(*) FROM user_preferences \
		UNION ALL \
		SELECT 'Book Sessions:', COUNT(*) FROM book_sessions \
		UNION ALL \
		SELECT 'VNC Instances:', COUNT(*) FROM vnc_instances \
		UNION ALL \
		SELECT 'Staff Tokens:', COUNT(*) FROM staff_tokens \
		UNION ALL \
		SELECT 'Emulator Shutdown Failures:', COUNT(*) FROM emulator_shutdown_failures;"

# Backup database
.PHONY: db-backup
db-backup:
	@echo "Backing up kindle database..."
	@mkdir -p backups
	@$(PYTHON) scripts/db_connect.py --command dump --env $(DB_ENV_FILE) --args "backups/kindle_db_$(shell date +%Y%m%d_%H%M%S).sql"
	@echo "Backup saved to backups/kindle_db_$(shell date +%Y%m%d_%H%M%S).sql"

# Restore database from SQL backup (local file method - deprecated)
.PHONY: db-restore-old
db-restore-old:
	@if [ -z "$(FILE)" ]; then \
		echo "Usage: make db-restore-old FILE=backups/kindle_db_YYYYMMDD_HHMMSS.sql"; \
		exit 1; \
	fi
	@echo "Restoring database from $(FILE)..."
	@$(PYTHON) scripts/db_connect.py --command query --env $(DB_ENV_FILE) --file $(FILE)

# PostgreSQL dump via Ansible (runs on database server)
.PHONY: pg-dump
pg-dump:
	@echo "Creating PostgreSQL dump on database server..."
	@cd ansible && ansible-playbook db_operations.yml --tags dump
	@echo "Backup downloaded to backups/ directory"

# PostgreSQL restore via Ansible (runs on database server)
.PHONY: pg-restore
pg-restore:
	@if [ -z "$(FILE)" ]; then \
		echo "Usage: make pg-restore FILE=backups/kindle_db_YYYYMMDD_HHMMSS.sql.gz"; \
		echo ""; \
		echo "Available backup files:"; \
		ls -la backups/*.sql.gz 2>/dev/null || echo "  No backup files found"; \
		exit 1; \
	fi
	@echo "Restoring PostgreSQL database from $(FILE)..."
	@cd ansible && ansible-playbook db_operations.yml --tags restore -e "restore_file=../$(FILE)"

# List available backups
.PHONY: db-backups
db-backups:
	@echo "=== Available Database Backups ==="
	@echo ""
	@echo "JSON exports (software-based):"
	@ls -lah backups/*.json 2>/dev/null || echo "  No JSON backups found"
	@echo ""
	@echo "PostgreSQL dumps:"
	@ls -lah backups/*.sql.gz 2>/dev/null || echo "  No PostgreSQL dumps found"


# Show recent user activity
.PHONY: db-activity
db-activity:
	@echo "Recent user activity..."
	@$(PYTHON) scripts/db_connect.py --command query --env $(DB_ENV_FILE) --args "\
		SELECT email, avd_name, last_used, auth_date \
		FROM users \
		ORDER BY last_used DESC NULLS LAST \
		LIMIT 10;"

# Show recent database data (3 most recent rows per table)
.PHONY: db-data
db-data:
	@echo "=== Recent Database Data (3 most recent rows per table) ==="
	@echo ""
	@echo "USERS (by last_used/updated_at):"
	@$(PYTHON) scripts/db_connect.py --command query --env $(DB_ENV_FILE) --args "SELECT * FROM users ORDER BY COALESCE(last_used, updated_at, created_at) DESC NULLS LAST LIMIT 3;"
	@echo ""
	@echo "EMULATOR SETTINGS (by user's last_used):"
	@$(PYTHON) scripts/db_connect.py --command query --env $(DB_ENV_FILE) --args "SELECT es.* FROM emulator_settings es JOIN users u ON es.user_id = u.id ORDER BY COALESCE(u.last_used, u.updated_at, u.created_at) DESC NULLS LAST LIMIT 3;"
	@echo ""
	@echo "DEVICE IDENTIFIERS (by user's last_used):"
	@$(PYTHON) scripts/db_connect.py --command query --env $(DB_ENV_FILE) --args "SELECT di.* FROM device_identifiers di JOIN users u ON di.user_id = u.id ORDER BY COALESCE(u.last_used, u.updated_at, u.created_at) DESC NULLS LAST LIMIT 3;"
	@echo ""
	@echo "LIBRARY SETTINGS (by last_series_group_check or user's last_used):"
	@$(PYTHON) scripts/db_connect.py --command query --env $(DB_ENV_FILE) --args "SELECT ls.* FROM library_settings ls JOIN users u ON ls.user_id = u.id ORDER BY COALESCE(ls.last_series_group_check, u.last_used, u.updated_at, u.created_at) DESC NULLS LAST LIMIT 3;"
	@echo ""
	@echo "READING SETTINGS (by user's last_used):"
	@$(PYTHON) scripts/db_connect.py --command query --env $(DB_ENV_FILE) --args "SELECT rs.* FROM reading_settings rs JOIN users u ON rs.user_id = u.id ORDER BY COALESCE(u.last_used, u.updated_at, u.created_at) DESC NULLS LAST LIMIT 3;"
	@echo ""
	@echo "USER PREFERENCES (by user's last_used):"
	@$(PYTHON) scripts/db_connect.py --command query --env $(DB_ENV_FILE) --args "SELECT up.* FROM user_preferences up JOIN users u ON up.user_id = u.id ORDER BY COALESCE(u.last_used, u.updated_at, u.created_at) DESC NULLS LAST LIMIT 3;"
	@echo ""
	@echo "BOOK SESSIONS (by last_accessed/created_at):"
	@$(PYTHON) scripts/db_connect.py --command query --env $(DB_ENV_FILE) --args "SELECT * FROM book_sessions ORDER BY COALESCE(last_accessed, created_at) DESC NULLS LAST LIMIT 3;"
	@echo ""
	@echo "VNC INSTANCES (by updated_at/created_at):"
	@$(PYTHON) scripts/db_connect.py --command query --env $(DB_ENV_FILE) --args "SELECT * FROM vnc_instances ORDER BY COALESCE(updated_at, created_at) DESC NULLS LAST LIMIT 3;"
	@echo ""
	@echo "STAFF TOKENS (by last_used/created_at):"
	@$(PYTHON) scripts/db_connect.py --command query --env $(DB_ENV_FILE) --args "SELECT id, token, created_at, last_used, revoked, revoked_at FROM staff_tokens ORDER BY COALESCE(last_used, created_at) DESC NULLS LAST LIMIT 3;"
	@echo ""
	@echo "EMULATOR SHUTDOWN FAILURES (by created_at):"
	@$(PYTHON) scripts/db_connect.py --command query --env $(DB_ENV_FILE) --args "SELECT * FROM emulator_shutdown_failures ORDER BY created_at DESC LIMIT 3;"
	@echo ""
	@echo "REQUEST LOGS (by datetime):"
	@$(PYTHON) scripts/db_connect.py --command query --env $(DB_ENV_FILE) --args "SELECT id, datetime, elapsed_time, method, path, status_code, user_email, SUBSTRING(response_preview, 1, 50) AS response_preview FROM request_logs ORDER BY datetime DESC LIMIT 5;"

# Request log viewing commands
.PHONY: db-requests
db-requests:
	@echo "===== Recent Requests (last hour) ====="
	@$(PYTHON) scripts/db_connect.py --command query --env $(DB_ENV_FILE) --args \
		"SELECT datetime, elapsed_time, method, path, status_code, user_email, \
		        user_agent_identifier as device, \
		        SUBSTRING(params, 1, 100) as request_params, \
		        SUBSTRING(response_preview, 1, 100) as response \
		 FROM request_logs \
		 WHERE datetime > NOW() - INTERVAL '1 hour' \
		 ORDER BY datetime DESC;"

.PHONY: db-slow-requests
db-slow-requests:
	@echo "===== Slow Requests (>1s) ====="
	@$(PYTHON) scripts/db_connect.py --command query --env $(DB_ENV_FILE) --args \
		"SELECT datetime, elapsed_time, method, path, status_code, user_email, \
		        user_agent_identifier as device \
		 FROM request_logs \
		 WHERE elapsed_time > 1.0 \
		 ORDER BY elapsed_time DESC \
		 LIMIT 20;"

.PHONY: db-requests-full
db-requests-full:
	@echo "===== Recent Requests with Full User Agent ====="
	@$(PYTHON) scripts/db_connect.py --command query --env $(DB_ENV_FILE) --args \
		"SELECT datetime, method, path, status_code, user_agent_identifier as device, \
		        user_agent \
		 FROM request_logs \
		 WHERE datetime > NOW() - INTERVAL '1 hour' \
		 ORDER BY datetime DESC \
		 LIMIT 20;"