import json
import sqlite3
import os
import subprocess
from flask import Flask, render_template, redirect, url_for, request, session, g
from datetime import datetime

# Import de ta fonction monitoring externe
from utils.monitoring import get_system_metrics

app = Flask(__name__)
app.secret_key = 'neuropulse_secret'

# Charger la config
with open('config.json') as f:
    config = json.load(f)

# Emplacement base de données
DATABASE = os.path.join(os.path.dirname(__file__), 'neuropulse.db')

# Connexion à la base
def get_db():
    db = getattr(g, '_database', None)
    if db is None:
        db = g._database = sqlite3.connect(DATABASE)
        db.row_factory = sqlite3.Row
    return db

# Initialisation base de données
def init_db():
    with app.app_context():
        db = get_db()
        # Table tickets
        db.execute('''
            CREATE TABLE IF NOT EXISTS tickets (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                titre TEXT NOT NULL,
                description TEXT NOT NULL,
                criticite TEXT NOT NULL,
                service TEXT NOT NULL,
                date_creation TEXT DEFAULT CURRENT_TIMESTAMP
            );
        ''')
        # Table actions
        db.execute('''
            CREATE TABLE IF NOT EXISTS actions (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                nom TEXT NOT NULL,
                description TEXT,
                commande TEXT NOT NULL,
                service_cible TEXT,
                actif INTEGER DEFAULT 1
            );
        ''')
        db.commit()

@app.teardown_appcontext
def close_connection(exception):
    db = getattr(g, '_database', None)
    if db is not None:
        db.close()

# Vérifier si ticket existe aujourd'hui (titre unique par jour)
def ticket_exists_today(titre):
    db = get_db()
    today = datetime.now().strftime('%Y-%m-%d')
    cur = db.execute("SELECT COUNT(*) FROM tickets WHERE titre = ? AND date(date_creation) = ?", (titre, today))
    return cur.fetchone()[0] > 0

# Vérifie si service actif
def is_service_running(service_name):
    try:
        status = subprocess.check_output(['systemctl', 'is-active', service_name], stderr=subprocess.DEVNULL)
        return b'active' in status
    except Exception:
        return False

# Création automatique des tickets selon alertes système
def check_alerts(stats):
    db = get_db()
    if stats["cpu"] > 80 and not ticket_exists_today("CPU élevé"):
        db.execute('''
            INSERT INTO tickets (titre, description, criticite, service)
            VALUES (?, ?, ?, ?)
        ''', ("CPU élevé", f"Utilisation CPU à {stats['cpu']}%", "Élevée", "Système"))
        db.commit()

    if stats["ram"] > 90 and not ticket_exists_today("RAM critique"):
        db.execute('''
            INSERT INTO tickets (titre, description, criticite, service)
            VALUES (?, ?, ?, ?)
        ''', ("RAM critique", f"Utilisation RAM à {stats['ram']}%", "Critique", "Système"))
        db.commit()

    for svc, label, criticite, srv in [
        ("apache2", "Apache arrêté", "Critique", "Web/HTTP"),
        ("mysql", "MySQL arrêté", "Critique", "Base de données"),
        ("ssh", "SSH arrêté", "Élevée", "Accès distant")
    ]:
        if not is_service_running(svc) and not ticket_exists_today(label):
            db.execute('''
                INSERT INTO tickets (titre, description, criticite, service)
                VALUES (?, ?, ?, ?)
            ''', (label, f"Le service {svc} ne fonctionne plus.", criticite, srv))
            db.commit()

# --- ROUTES ---

@app.route('/')
def index():
    if 'user' in session:
        return redirect(url_for('dashboard'))
    return redirect(url_for('login'))

@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        if username == 'admin' and password == 'admin':
            session['user'] = username
            return redirect(url_for('dashboard'))
        else:
            return render_template('login.html', error='Identifiants invalides')
    return render_template('login.html')

@app.route('/logout')
def logout():
    session.pop('user', None)
    return redirect(url_for('login'))

@app.route('/dashboard')
def dashboard():
    if 'user' not in session:
        return redirect(url_for('login'))

    stats = get_system_metrics()
    check_alerts(stats)

    db = get_db()
    cur = db.execute('SELECT * FROM tickets ORDER BY date_creation DESC LIMIT 5')
    tickets = cur.fetchall()

    return render_template('dashboard.html', stats=stats, tickets=tickets)

# Gestion des tickets
@app.route('/tickets')
def tickets():
    if 'user' not in session:
        return redirect(url_for('login'))

    db = get_db()
    cur = db.execute('SELECT * FROM tickets ORDER BY date_creation DESC')
    all_tickets = cur.fetchall()
    return render_template('tickets.html', tickets=all_tickets)

@app.route('/tickets/<int:ticket_id>')
def ticket_detail(ticket_id):
    if 'user' not in session:
        return redirect(url_for('login'))

    db = get_db()
    cur = db.execute('SELECT * FROM tickets WHERE id = ?', (ticket_id,))
    ticket = cur.fetchone()
    if not ticket:
        return "Ticket introuvable", 404
    return render_template('ticket_detail.html', ticket=ticket)

@app.route('/tickets/new', methods=['GET', 'POST'])
def new_ticket():
    if 'user' not in session:
        return redirect(url_for('login'))

    if request.method == 'POST':
        titre = request.form['title']
        description = request.form['description']
        criticite = request.form['criticite']
        service = request.form['service']

        db = get_db()
        db.execute(
            'INSERT INTO tickets (titre, description, criticite, service) VALUES (?, ?, ?, ?)',
            (titre, description, criticite, service)
        )
        db.commit()
        return redirect(url_for('tickets'))

    return render_template('new_ticket.html')

# Gestion des actions automatisées

@app.route('/actions')
def liste_actions():
    if 'user' not in session:
        return redirect(url_for('login'))

    db = get_db()
    actions = db.execute('SELECT * FROM actions').fetchall()
    html = '''
    <h1>Actions Automatisées</h1>
    <a href="/dashboard">← Retour</a> | <a href="/actions/add">➕ Ajouter</a>
    <table border="1" cellpadding="5" cellspacing="0">
        <tr>
            <th>Nom</th><th>Description</th><th>Commande</th><th>Service</th><th>Actif</th><th>Actions</th>
        </tr>
    '''
    for a in actions:
        html += f'''
            <tr>
                <td>{a["nom"]}</td>
                <td>{a["description"]}</td>
                <td>{a["commande"]}</td>
                <td>{a["service_cible"]}</td>
                <td>{"✅" if a["actif"] else "❌"}</td>
                <td>
                    <a href="/actions/edit/{a["id"]}">✏️</a> | 
                    <a href="/actions/delete/{a["id"]}">🗑️</a> | 
                    <a href="/actions/toggle/{a["id"]}">🔁</a>
                </td>
            </tr>
        '''
    html += '</table>'
    return html

@app.route('/actions/add', methods=['GET', 'POST'])
def add_action():
    if 'user' not in session:
        return redirect(url_for('login'))

    if request.method == 'POST':
        nom = request.form['nom']
        desc = request.form['description']
        cmd = request.form['commande']
        service = request.form['service_cible']
        actif = int(request.form.get('actif', 0))

        db = get_db()
        db.execute('INSERT INTO actions (nom, description, commande, service_cible, actif) VALUES (?, ?, ?, ?, ?)',
                   (nom, desc, cmd, service, actif))
        db.commit()
        return redirect(url_for('liste_actions'))

    return '''
        <h2>Ajouter une Action</h2>
        <form method="post">
            Nom : <input name="nom"><br>
            Description : <input name="description"><br>
            Commande : <input name="commande"><br>
            Service cible : <input name="service_cible"><br>
            Actif ? <input type="checkbox" name="actif" value="1"><br>
            <input type="submit" value="Enregistrer">
        </form>
        <a href="/actions">← Retour</a>
    '''

@app.route('/actions/edit/<int:id>', methods=['GET', 'POST'])
def edit_action(id):
    if 'user' not in session:
        return redirect(url_for('login'))

    db = get_db()
    action = db.execute('SELECT * FROM actions WHERE id = ?', (id,)).fetchone()

    if request.method == 'POST':
        nom = request.form['nom']
        desc = request.form['description']
        cmd = request.form['commande']
        service = request.form['service_cible']
        actif = int(request.form.get('actif', 0))

        db.execute('''
            UPDATE actions SET nom = ?, description = ?, commande = ?, service_cible = ?, actif = ? WHERE id = ?
        ''', (nom, desc, cmd, service, actif, id))
        db.commit()
        return redirect(url_for('liste_actions'))

    if not action:
        return "Action non trouvée", 404

    checked = "checked" if action['actif'] else ""
    return f'''
        <h2>Modifier Action #{id}</h2>
        <form method="post">
            Nom : <input name="nom" value="{action['nom']}"><br>
            Description : <input name="description" value="{action['description']}"><br>
            Commande : <input name="commande" value="{action['commande']}"><br>
            Service cible : <input name="service_cible" value="{action['service_cible']}"><br>
            Actif ? <input type="checkbox" name="actif" value="1" {checked}><br>
            <input type="submit" value="Enregistrer">
        </form>
        <a href="/actions">← Retour</a>
    '''

@app.route('/actions/delete/<int:id>')
def delete_action(id):
    if 'user' not in session:
        return redirect(url_for('login'))

    db = get_db()
    db.execute('DELETE FROM actions WHERE id = ?', (id,))
    db.commit()
    return redirect(url_for('liste_actions'))

@app.route('/actions/toggle/<int:id>')
def toggle_action(id):
    if 'user' not in session:
        return redirect(url_for('login'))

    db = get_db()
    action = db.execute('SELECT actif FROM actions WHERE id = ?', (id,)).fetchone()
    if not action:
        return "Action non trouvée", 404

    new_status = 0 if action['actif'] else 1
    db.execute('UPDATE actions SET actif = ? WHERE id = ?', (new_status, id))
    db.commit()
    return redirect(url_for('liste_actions'))

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