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

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

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

# Emplacement de la 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

# Initialiser la base de données
def init_db():
    with app.app_context():
        db = get_db()
        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
            );
        ''')
        db.commit()

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

# Vérifie si un ticket du même type a déjà été créé aujourd'hui
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

# Création automatique de tickets en cas de problè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()

    if not is_service_running("apache2") and not ticket_exists_today("Apache arrêté"):
        db.execute('''
            INSERT INTO tickets (titre, description, criticite, service)
            VALUES (?, ?, ?, ?)
        ''', ("Apache arrêté", "Le service Apache ne fonctionne plus.", "Critique", "Web/HTTP"))
        db.commit()

    if not is_service_running("mysql") and not ticket_exists_today("MySQL arrêté"):
        db.execute('''
            INSERT INTO tickets (titre, description, criticite, service)
            VALUES (?, ?, ?, ?)
        ''', ("MySQL arrêté", "Le service MySQL est hors ligne.", "Critique", "Base de données"))
        db.commit()

    if not is_service_running("ssh") and not ticket_exists_today("SSH arrêté"):
        db.execute('''
            INSERT INTO tickets (titre, description, criticite, service)
            VALUES (?, ?, ?, ?)
        ''', ("SSH arrêté", "Le service SSH est injoignable.", "Élevée", "Accès distant"))
        db.commit()

# Vérifie si un service est 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

# Routes principales
@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('/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)

@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')

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

if __name__ == '__main__':
    init_db()
    app.run(host='0.0.0.0', port=5000, debug=True)