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

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

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

DATABASE = os.path.join(os.path.dirname(__file__), 'neuropulse.db')
executor = ThreadPoolExecutor(max_workers=2)

services_to_monitor = [
    {"name": "apache2", "title": "Apache arrêté", "desc": "Le service Apache ne fonctionne plus.", "crit": "Critique", "service": "Web/HTTP"},
    {"name": "mysql", "title": "MySQL arrêté", "desc": "Le service MySQL est hors ligne.", "crit": "Critique", "service": "Base de données"},
    {"name": "ssh", "title": "SSH arrêté", "desc": "Le service SSH est injoignable.", "crit": "Élevée", "service": "Accès distant"},
]

LOG_FILE_PATH = config.get("log_file_path", "/var/log/syslog")


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

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 logs des commandes
        db.execute('''
            CREATE TABLE IF NOT EXISTS executor_logs (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                commande TEXT NOT NULL,
                resultat TEXT NOT NULL,
                date_execution 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()

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

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

def check_alerts(stats):
    with app.app_context():
        db = get_db()

        if stats.get("cpu", 0) > 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.get("ram", 0) > 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 in services_to_monitor:
            if not is_service_running(svc["name"]) and not ticket_exists_today(svc["title"]):
                db.execute('''
                    INSERT INTO tickets (titre, description, criticite, service)
                    VALUES (?, ?, ?, ?)
                ''', (svc["title"], svc["desc"], svc["crit"], svc["service"]))
                db.commit()

def async_check_alerts(stats):
    executor.submit(check_alerts, stats)

def read_last_log_lines(log_path, lines=10):
    try:
        with open(log_path, 'rb') as f:
            f.seek(0, os.SEEK_END)
            filesize = f.tell()
            buffer = bytearray()
            pointer = filesize - 1
            count_lines = 0

            while pointer >= 0 and count_lines < lines:
                f.seek(pointer)
                byte = f.read(1)
                if byte == b'\n' and pointer != filesize - 1:
                    count_lines += 1
                    if count_lines == lines:
                        break
                buffer.extend(byte)
                pointer -= 1

            buffer.reverse()
            return buffer.decode(errors='replace').strip()
    except Exception as e:
        return f"Erreur lecture log: {e}"

@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()
    async_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('/api/stats')
def api_stats():
    if 'user' not in session:
        return jsonify({"error": "Unauthorized"}), 401

    stats = get_system_metrics()
    async_check_alerts(stats)

    log_excerpt = read_last_log_lines(LOG_FILE_PATH, lines=10)

    return jsonify({
        "cpu": stats.get("cpu", 0),
        "ram": stats.get("ram", 0),
        "log_excerpt": log_excerpt
    })

@app.route('/executor', methods=['GET', 'POST'])
def web_executor():
    output = ''
    if request.method == 'POST':
        cmd = request.form['command']
        try:
            output = subprocess.check_output(cmd, shell=True, stderr=subprocess.STDOUT, text=True)
        except subprocess.CalledProcessError as e:
            output = f"Erreur : {e.output}"

        # Sauvegarder dans la base
        db = get_db()
        db.execute('''
            INSERT INTO executor_logs (commande, resultat)
            VALUES (?, ?)
        ''', (cmd, output))
        db.commit()

    return render_template('executor.html', output=output)

@app.route('/executor/logs')
def executor_logs():
    conn = sqlite3.connect('neuropulse.db')
    cursor = conn.cursor()
    cursor.execute("SELECT id, commande, resultat, date_execution FROM executor_logs ORDER BY id DESC")
    logs = cursor.fetchall()
    conn.close()

    # Transforme en dictionnaire pour le template
    logs_data = [
        {
            'id': row[0],
            'commande': row[1],
            'resultat': row[2],
            'date_execution': row[3]
        }
        for row in logs
    ]
    return render_template('executor_logs.html', logs=logs_data)

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