app.py

from flask import Flask, render_template, request, redirect, url_for, flash, g
import pyodbc

# Import database functions
from dbconnector import MSSQLConnectorLocal, ListDatabases

app = Flask(__name__)
app.secret_key = 'database-ai-project'  # Required for flash messages

# Global storage for the connection
app.config['DB_CONN'] = None


@app.route('/')
def home():
    return render_template('index.html')


@app.route('/connect', methods=['POST'])
def connect():
    server_address = request.form.get('ServerAddress')
    connection = request.form.get('Connection')
    print(f"Connecting to {server_address} with connection: {connection}")
    try:
        conn = MSSQLConnectorLocal(server=server_address)
        app.config['DB_CONN'] = conn

        return render_template(
            'Connection.html',
            status='success',
            db_type='mssql',
            connection=connection,
            ServerAddress=server_address
        )

    except Exception as e:
        return render_template(
            'Connection.html',
            status='failed',
            error=f"❌ Connection failed: {str(e)}",
            ServerAddress=server_address,
            db_type='mssql'
        )
    

@app.route('/local')
def Local():
    conn = app.config.get('DB_CONN')
    
    if not conn:
        flash("⚠️ No active connection. Please connect first.")
        return redirect(url_for('home'))

    try:
        databases = ListDatabases(conn)
        if not databases:
            flash("⚠️ No databases found.")
        else:
            for db in databases:
                print(f"Database: {db}")
            flash("✅ Databases fetched successfully.")

        return render_template('dashboard.html', databases=databases)

    except Exception as e:
        flash(f"❌ Failed to fetch databases: {str(e)}")
        print(f"Error: {str(e)}")
        return redirect(url_for('home'))


# @app.teardown_appcontext
# def close_db_connection(exception):
#     """Closes the database connection after each request."""
#     conn = app.config.pop('DB_CONN', None)
#     if conn:
#         conn.close()
#         print("✅ Connection closed gracefully.")


if __name__ == '__main__':
    app.run(debug=True)