working_api.py

#!/usr/bin/env python3
"""
Working API with schema-adapted search for 83GB database
"""

from flask import Flask, request, jsonify, send_file
from flask_cors import CORS
import sqlite3
import time
import json
import os

app = Flask(__name__)
CORS(app)

DB_PATH = "/mnt/databases/SELF_HEALING_AGI.db"

def adapted_search(query, limit=10):
    """Schema-adapted search for the actual database structure"""
    try:
        start_time = time.time()

        conn = sqlite3.connect(DB_PATH, check_same_thread=False)
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()

        search_query = """
        SELECT
            s.id,
            s.question_title as title,
            s.answer_body as solution,
            s.question_tags as tags,
            s.error_pattern,
            s.fix_command,
            s.answer_score,
            s.is_accepted,
            fts.rank
        FROM solutions_fts fts
        JOIN solutions s ON s.id = fts.rowid
        WHERE solutions_fts MATCH ?
        ORDER BY rank
        LIMIT ?
        """

        cursor.execute(search_query, (query, limit))
        results = []

        for row in cursor.fetchall():
            result = {
                'id': row['id'],
                'title': row['title'] or 'No title',
                'solution': row['solution'] or 'No solution',
                'tags': row['tags'] or '',
                'error_pattern': row['error_pattern'] or '',
                'fix_command': row['fix_command'] or '',
                'answer_score': row['answer_score'] or 0,
                'is_accepted': bool(row['is_accepted']),
                'similarity': 1.0,
                'confidence': row['answer_score'] / 100.0 if row['answer_score'] else 0.5,
                'search_strategy': 'fts_adapted'
            }
            results.append(result)

        conn.close()

        search_time = (time.time() - start_time) * 1000  # Convert to ms

        return {
            'results': results,
            'query': query,
            'count': len(results),
            'search_time_ms': round(search_time, 1),
            'database_size': '83GB',
            'total_records': '18.5M+'
        }

    except Exception as e:
        return {
            'error': f'Search failed: {str(e)}',
            'results': [],
            'count': 0
        }

@app.route('/search', methods=['POST'])
def search():
    """Search endpoint"""
    try:
        data = request.get_json()
        query = data.get('query', '')
        limit = data.get('limit', 10)

        if not query:
            return jsonify({'error': 'Query required'}), 400

        results = adapted_search(query, limit)
        return jsonify(results)

    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/health', methods=['GET'])
def health():
    """Health check with live database stats"""
    try:
        conn = sqlite3.connect(DB_PATH)
        cursor = conn.cursor()

        # Get actual record count
        cursor.execute("SELECT COUNT(*) FROM solutions")
        total_records = cursor.fetchone()[0]

        # Get database size
        cursor.execute("SELECT page_count * page_size as size FROM pragma_page_count(), pragma_page_size()")
        db_size_bytes = cursor.fetchone()[0]
        db_size_gb = round(db_size_bytes / (1024**3), 1)

        conn.close()

        return jsonify({
            'status': 'healthy',
            'database': 'connected',
            'database_path': DB_PATH,
            'live_stats': {
                'total_records': f"{total_records:,}",
                'database_size': f"{db_size_gb}GB",
                'database_size_bytes': db_size_bytes
            },
            'timestamp': time.time()
        })
    except Exception as e:
        return jsonify({
            'status': 'error',
            'database': 'error',
            'error': str(e),
            'timestamp': time.time()
        })

@app.route('/', methods=['GET'])
def root():
    """Serve HTML interface or API info"""
    # Check if request wants HTML
    accept_header = request.headers.get('Accept', '')
    if 'text/html' in accept_header:
        # Serve the HTML interface
        html_path = '/var/www/talon-api/fixit_frontend.html'
        if os.path.exists(html_path):
            return send_file(html_path)
        else:
            return '''
            <html><body style="font-family: Arial; padding: 40px; background: #f5f5f5;">
            <div style="max-width: 800px; margin: 0 auto; background: white; padding: 40px; border-radius: 10px;">
            <h1 style="color: #e74c3c;">🔧 FixIt API</h1>
            <p>HTML interface not found. API endpoints available:</p>
            <ul>
                <li><strong>POST /search</strong> - Search Stack Overflow solutions</li>
                <li><strong>GET /health</strong> - Health check</li>
            </ul>
            <p>Try: <code>curl -X POST -H "Content-Type: application/json" -d '{"query":"python error","limit":5}' https://fixit.built-simple.ai/search</code></p>
            </div>
            </body></html>
            '''
    else:
        # Return JSON API info
        return jsonify({
            'name': 'FixIt - Stack Overflow Solution Search API',
            'database': '83GB SELF_HEALING_AGI.db',
            'records': '18.5M+ Stack Overflow solutions',
            'endpoints': {
                '/search': 'POST - Search solutions',
                '/health': 'GET - Health check',
                '/': 'GET - Web interface (HTML) or API info (JSON)'
            }
        })

if __name__ == '__main__':
    print("🚀 Starting Schema-Adapted Search API...")
    print(f"📊 Database: {DB_PATH}")
    app.run(host='0.0.0.0', port=5001, debug=False)