#!/usr/bin/env python3
"""
SQL Command Runner for RateRight Database
Run custom SQL queries on the SQLite database
"""

import sqlite3
import sys
from app import create_app
from app.extensions import db

def run_sql_command(sql_command):
    """Execute a SQL command on the database"""
    app = create_app()
    
    with app.app_context():
        try:
            # For SELECT queries
            if sql_command.strip().upper().startswith('SELECT'):
                result = db.session.execute(db.text(sql_command))
                rows = result.fetchall()
                
                print(f"📊 Query Results ({len(rows)} rows):")
                print("-" * 50)
                
                if rows:
                    # Print column headers if available
                    if hasattr(result, 'keys'):
                        headers = list(result.keys())
                        print(" | ".join(str(h) for h in headers))
                        print("-" * 50)
                    
                    # Print rows
                    for row in rows:
                        print(" | ".join(str(col) for col in row))
                else:
                    print("No results found.")
            
            # For INSERT, UPDATE, DELETE queries
            else:
                result = db.session.execute(db.text(sql_command))
                db.session.commit()
                print(f"✅ Command executed successfully. Rows affected: {result.rowcount}")
                
        except Exception as e:
            db.session.rollback()
            print(f"❌ Error executing SQL: {e}")

def interactive_mode():
    """Interactive SQL command mode"""
    print("🗄️  RateRight SQL Interactive Mode")
    print("=" * 40)
    print("Type SQL commands (type 'exit' to quit)")
    print("Examples:")
    print("  SELECT * FROM users LIMIT 5;")
    print("  SELECT email, role FROM users WHERE role = 'worker';")
    print("  UPDATE users SET total_points = 100 WHERE id = 1;")
    print()
    
    while True:
        try:
            command = input("SQL> ").strip()
            
            if command.lower() in ['exit', 'quit', 'q']:
                print("👋 Goodbye!")
                break
            
            if not command:
                continue
                
            run_sql_command(command)
            print()
            
        except KeyboardInterrupt:
            print("\n👋 Goodbye!")
            break
        except EOFError:
            print("\n👋 Goodbye!")
            break

if __name__ == "__main__":
    if len(sys.argv) > 1:
        # Run a single command passed as argument
        sql_command = " ".join(sys.argv[1:])
        run_sql_command(sql_command)
    else:
        # Interactive mode
        interactive_mode()