from flask import Flask, render_template, request, jsonify, send_file, session, redirect, url_for
import mysql.connector
from mysql.connector import Error
from datetime import datetime
import os
from docx import Document
from docx.shared import Pt, Inches
from docx.enum.text import WD_ALIGN_PARAGRAPH
import tempfile
import json
import subprocess
import bcrypt
from functools import wraps

app = Flask(__name__)
app.secret_key = 'R0mt3l3c0m!'  # IMPORTANT: Schimbă în producție!

# Configurare MySQL - MODIFICĂ ACESTE SETĂRI
MYSQL_CONFIG = {
    'host': 'localhost',
    'database': 'evidenta_deconturi',
    'user': 'root',
    'password': 'romtelecom',
    'port': 3306
}

TEMPLATES_DIR = 'templates_word'
PDF_OUTPUT_DIR = 'cereri_pdf'

os.makedirs(TEMPLATES_DIR, exist_ok=True)
os.makedirs(PDF_OUTPUT_DIR, exist_ok=True)

def get_db_connection():
    """Creează conexiune la baza de date MySQL"""
    try:
        connection = mysql.connector.connect(**MYSQL_CONFIG)
        return connection
    except Error as e:
        print(f"Eroare la conectarea la MySQL: {e}")
        return None

def login_required(f):
    """Decorator pentru a verifica autentificarea"""
    @wraps(f)
    def decorated_function(*args, **kwargs):
        if 'user_id' not in session:
            return jsonify({'error': 'Autentificare necesară'}), 401
        return f(*args, **kwargs)
    return decorated_function

def init_db():
    """Inițializează tabelul pentru cereri de concediu"""
    connection = get_db_connection()
    if not connection:
        print("Nu s-a putut conecta la baza de date!")
        return False
    
    try:
        cursor = connection.cursor()
        
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS cereri_concediu (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user_id VARCHAR(255),
                name VARCHAR(100) NOT NULL,
                data_start DATE NOT NULL,
                data_sfarsit DATE NOT NULL,
                nr_zile INT NOT NULL,
                inlocuitor_id VARCHAR(255),
                inlocuitor_name VARCHAR(100) NOT NULL,
                data_completare VARCHAR(20) NOT NULL,
                semnat_solicitant TINYINT DEFAULT 0,
                semnatura_solicitant LONGTEXT,
                data_semnare_solicitant DATETIME,
                semnat_inlocuitor TINYINT DEFAULT 0,
                semnatura_inlocuitor LONGTEXT,
                data_semnare_inlocuitor DATETIME,
                status VARCHAR(20) DEFAULT 'pending',
                pdf_path VARCHAR(500),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (user_id) REFERENCES employees(id) ON DELETE SET NULL,
                FOREIGN KEY (inlocuitor_id) REFERENCES employees(id) ON DELETE SET NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ''')
        
        connection.commit()
        print("✅ Tabelul cereri_concediu creat/verificat cu succes!")
        return True
        
    except Error as e:
        print(f"❌ Eroare la crearea tabelului: {e}")
        return False
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()

def create_word_template():
    """Creează template-ul Word pentru cererea de concediu"""
    doc = Document()
    
    section = doc.sections[0]
    section.top_margin = Inches(1)
    section.bottom_margin = Inches(1)
    section.left_margin = Inches(1)
    section.right_margin = Inches(1)
    
    title = doc.add_paragraph()
    title_run = title.add_run('CERERE DE CONCEDIU DE ODIHNĂ')
    title_run.bold = True
    title_run.font.size = Pt(16)
    title.alignment = WD_ALIGN_PARAGRAPH.CENTER
    
    doc.add_paragraph()
    doc.add_paragraph(f'Subsemnatul/Subsemnata: {{name}}')
    doc.add_paragraph()
    doc.add_paragraph(f'Solicit aprobarea concediului de odihnă pentru perioada:')
    doc.add_paragraph(f'De la data: {{data_start}}')
    doc.add_paragraph(f'Până la data: {{data_sfarsit}}')
    doc.add_paragraph(f'Număr zile: {{nr_zile}}')
    doc.add_paragraph()
    doc.add_paragraph(f'Pe perioada absenței mele, atribuțiile îmi vor fi preluate de:')
    doc.add_paragraph(f'{{inlocuitor_name}}')
    doc.add_paragraph()
    doc.add_paragraph()
    
    sig_table = doc.add_table(rows=3, cols=2)
    sig_table.style = 'Table Grid'
    
    sig_table.cell(0, 0).text = 'Data:'
    sig_table.cell(0, 1).text = 'Data:'
    sig_table.cell(1, 0).text = '{{data_completare}}'
    sig_table.cell(1, 1).text = '{{data_completare}}'
    sig_table.cell(2, 0).text = 'Semnătura solicitant:\n\n_________________'
    sig_table.cell(2, 1).text = 'Semnătura înlocuitor:\n\n_________________'
    
    template_path = os.path.join(TEMPLATES_DIR, 'cerere_concediu_template.docx')
    doc.save(template_path)
    return template_path

def generate_word_from_template(data):
    """Generează document Word completat din template"""
    template_path = os.path.join(TEMPLATES_DIR, 'cerere_concediu_template.docx')
    
    if not os.path.exists(template_path):
        create_word_template()
    
    doc = Document(template_path)
    
    replacements = {
        '{name}': data['name'],
        '{data_start}': data['data_start'],
        '{data_sfarsit}': data['data_sfarsit'],
        '{nr_zile}': str(data['nr_zile']),
        '{inlocuitor_name}': data['inlocuitor_name'],
        '{data_completare}': data['data_completare']
    }
    
    for paragraph in doc.paragraphs:
        for key, value in replacements.items():
            if key in paragraph.text:
                paragraph.text = paragraph.text.replace(key, value)
    
    for table in doc.tables:
        for row in table.rows:
            for cell in row.cells:
                for key, value in replacements.items():
                    if key in cell.text:
                        cell.text = cell.text.replace(key, value)
    
    return doc

def convert_word_to_pdf(doc, output_path):
    """Convertește document Word în PDF"""
    with tempfile.NamedTemporaryFile(suffix='.docx', delete=False) as tmp_word:
        doc.save(tmp_word.name)
        word_path = tmp_word.name
    
    try:
        result = subprocess.run(['which', 'libreoffice'], 
                              capture_output=True, text=True)
        
        if result.returncode == 0:
            pdf_dir = os.path.dirname(output_path)
            subprocess.run([
                'libreoffice', '--headless', '--convert-to', 'pdf',
                '--outdir', pdf_dir, word_path
            ], capture_output=True)
            
            base_name = os.path.basename(word_path).replace('.docx', '.pdf')
            generated_pdf = os.path.join(pdf_dir, base_name)
            
            if os.path.exists(generated_pdf):
                os.rename(generated_pdf, output_path)
                return output_path
        
        docx_path = output_path.replace('.pdf', '.docx')
        doc.save(docx_path)
        return docx_path
        
    except Exception as e:
        print(f"Eroare la conversie: {e}")
        docx_path = output_path.replace('.pdf', '.docx')
        doc.save(docx_path)
        return docx_path
    finally:
        if os.path.exists(word_path):
            os.remove(word_path)

@app.route('/')
def index():
    """Pagina principală - redirect la login sau dashboard"""
    if 'user_id' not in session:
        return redirect(url_for('login_page'))
    return render_template('index_mysql.html')

@app.route('/login')
def login_page():
    """Pagina de login"""
    return render_template('login.html')

@app.route('/api/login', methods=['POST'])
def login():
    """Autentificare utilizator"""
    connection = get_db_connection()
    if not connection:
        return jsonify({'error': 'Eroare la conectarea la baza de date'}), 500
    
    try:
        data = request.json
        username = data.get('username')
        password = data.get('password')
        
        if not username or not password:
            return jsonify({'error': 'Username și password sunt obligatorii'}), 400
        
        cursor = connection.cursor(dictionary=True)
        cursor.execute('SELECT id, name, username, password, role FROM employees WHERE username = %s', 
                      (username,))
        user = cursor.fetchone()
        
        if not user:
            return jsonify({'error': 'Username sau parolă incorectă'}), 401
        
        # Verificare parolă (IMPORTANT: În producție folosește bcrypt/hash!)
        # Verificare parola cu bcrypt
        try:
            password_hash = user['password']
            # Daca e string, converte?te la bytes
            if isinstance(password_hash, str):
                password_hash = password_hash.encode('utf-8')
            
            if not bcrypt.checkpw(password.encode('utf-8'), password_hash):
                return jsonify({'error': 'Username sau parola incorecta'}), 401
        except Exception as e:
            print(f"Eroare verificare parola: {e}")
            return jsonify({'error': 'Username sau parola incorecta'}), 401
        
        # Setează sesiunea
        session['user_id'] = user['id']
        session['username'] = user['username']
        session['name'] = user['name']
        session['role'] = user['role']
        
        return jsonify({
            'message': 'Autentificare reușită',
            'user': {
                'id': user['id'],
                'name': user['name'],
                'username': user['username'],
                'role': user['role']
            }
        }), 200
        
    except Error as e:
        return jsonify({'error': str(e)}), 500
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()

@app.route('/api/logout', methods=['POST'])
def logout():
    """Logout utilizator"""
    session.clear()
    return jsonify({'message': 'Logout reușit'}), 200

@app.route('/api/current-user', methods=['GET'])
@login_required
def current_user():
    """Returnează utilizatorul curent autentificat"""
    return jsonify({
        'id': session.get('user_id'),
        'name': session.get('name'),
        'username': session.get('username'),
        'role': session.get('role')
    }), 200

@app.route('/api/users', methods=['GET'])
@login_required
def get_users():
    """Obține lista de utilizatori din employees"""
    connection = get_db_connection()
    if not connection:
        return jsonify({'error': 'Eroare la conectarea la baza de date'}), 500
    
    try:
        cursor = connection.cursor(dictionary=True)
        cursor.execute('SELECT id, name FROM employees ORDER BY name')
        users = cursor.fetchall()
        return jsonify(users), 200
    except Error as e:
        return jsonify({'error': str(e)}), 500
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()

@app.route('/api/cereri', methods=['POST'])
@login_required
def create_cerere():
    """Creează o nouă cerere de concediu"""
    connection = get_db_connection()
    if not connection:
        return jsonify({'error': 'Eroare la conectarea la baza de date'}), 500
    
    try:
        data = request.json
        
        required_fields = ['user_id', 'data_start', 'data_sfarsit', 'inlocuitor_id']
        for field in required_fields:
            if not data.get(field):
                return jsonify({'error': f'Câmpul {field} este obligatoriu'}), 400
        
        cursor = connection.cursor(dictionary=True)
        
        cursor.execute('SELECT name FROM employees WHERE id = %s', (data['user_id'],))
        user = cursor.fetchone()
        if not user:
            return jsonify({'error': 'Utilizator invalid'}), 400
        
        cursor.execute('SELECT name FROM employees WHERE id = %s', (data['inlocuitor_id'],))
        inlocuitor = cursor.fetchone()
        if not inlocuitor:
            return jsonify({'error': 'Înlocuitor invalid'}), 400
        
        start_date = datetime.strptime(data['data_start'], '%Y-%m-%d')
        end_date = datetime.strptime(data['data_sfarsit'], '%Y-%m-%d')
        nr_zile = (end_date - start_date).days + 1
        
        data_completare = datetime.now().strftime('%d.%m.%Y')
        
        cursor.execute('''
            INSERT INTO cereri_concediu 
            (user_id, name, data_start, data_sfarsit, nr_zile, 
             inlocuitor_id, inlocuitor_name, data_completare)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
        ''', (
            data['user_id'], user['name'],
            data['data_start'], data['data_sfarsit'], nr_zile,
            data['inlocuitor_id'], inlocuitor['name'],
            data_completare
        ))
        
        cerere_id = cursor.lastrowid
        connection.commit()
        
        word_data = {
            'name': user['name'],
            'data_start': start_date.strftime('%d.%m.%Y'),
            'data_sfarsit': end_date.strftime('%d.%m.%Y'),
            'nr_zile': nr_zile,
            'inlocuitor_name': inlocuitor['name'],
            'data_completare': data_completare
        }
        
        doc = generate_word_from_template(word_data)
        
        pdf_filename = f"{user['name'].replace(' ', '-')}-{datetime.now().strftime('%Y%m%d')}.pdf"
        pdf_path = os.path.join(PDF_OUTPUT_DIR, pdf_filename)
        
        final_path = convert_word_to_pdf(doc, pdf_path)
        
        cursor.execute('UPDATE cereri_concediu SET pdf_path = %s WHERE id = %s', 
                     (final_path, cerere_id))
        connection.commit()
        
        return jsonify({
            'message': 'Cerere creată cu succes!',
            'id': cerere_id,
            'pdf_path': final_path
        }), 201
        
    except Error as e:
        return jsonify({'error': str(e)}), 500
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()

@app.route('/api/cereri', methods=['GET'])
@login_required
def get_cereri():
    """Obține toate cererile de concediu"""
    connection = get_db_connection()
    if not connection:
        return jsonify({'error': 'Eroare la conectarea la baza de date'}), 500
    
    try:
        cursor = connection.cursor(dictionary=True)
        cursor.execute('''
            SELECT c.*, 
                   e1.name as user_name,
                   e2.name as inlocuitor_name_full
            FROM cereri_concediu c
            LEFT JOIN employees e1 ON c.user_id = e1.id
            LEFT JOIN employees e2 ON c.inlocuitor_id = e2.id
            ORDER BY c.created_at DESC
        ''')
        cereri = cursor.fetchall()
        
        for cerere in cereri:
            if cerere.get('data_start'):
                cerere['data_start'] = cerere['data_start'].strftime('%Y-%m-%d')
            if cerere.get('data_sfarsit'):
                cerere['data_sfarsit'] = cerere['data_sfarsit'].strftime('%Y-%m-%d')
            if cerere.get('created_at'):
                cerere['created_at'] = cerere['created_at'].strftime('%Y-%m-%d %H:%M:%S')
            if cerere.get('data_semnare_solicitant'):
                cerere['data_semnare_solicitant'] = cerere['data_semnare_solicitant'].strftime('%Y-%m-%d %H:%M:%S')
            if cerere.get('data_semnare_inlocuitor'):
                cerere['data_semnare_inlocuitor'] = cerere['data_semnare_inlocuitor'].strftime('%Y-%m-%d %H:%M:%S')
        
        return jsonify(cereri), 200
    except Error as e:
        return jsonify({'error': str(e)}), 500
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()

@app.route('/api/cereri/<int:cerere_id>', methods=['GET'])
@login_required
def get_cerere(cerere_id):
    """Obține o cerere specifică"""
    connection = get_db_connection()
    if not connection:
        return jsonify({'error': 'Eroare la conectarea la baza de date'}), 500
    
    try:
        cursor = connection.cursor(dictionary=True)
        cursor.execute('SELECT * FROM cereri_concediu WHERE id = %s', (cerere_id,))
        cerere = cursor.fetchone()
        
        if cerere:
            if cerere.get('data_start'):
                cerere['data_start'] = cerere['data_start'].strftime('%Y-%m-%d')
            if cerere.get('data_sfarsit'):
                cerere['data_sfarsit'] = cerere['data_sfarsit'].strftime('%Y-%m-%d')
            if cerere.get('created_at'):
                cerere['created_at'] = cerere['created_at'].strftime('%Y-%m-%d %H:%M:%S')
            
            return jsonify(cerere), 200
        else:
            return jsonify({'error': 'Cerere negăsită'}), 404
    except Error as e:
        return jsonify({'error': str(e)}), 500
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()

@app.route('/api/cereri/<int:cerere_id>/pdf', methods=['GET'])
@login_required
def download_pdf(cerere_id):
    """Descarcă PDF-ul unei cereri"""
    connection = get_db_connection()
    if not connection:
        return jsonify({'error': 'Eroare la conectarea la baza de date'}), 500
    
    try:
        cursor = connection.cursor()
        cursor.execute('SELECT pdf_path, name FROM cereri_concediu WHERE id = %s', 
                     (cerere_id,))
        result = cursor.fetchone()
        
        if result and result[0] and os.path.exists(result[0]):
            return send_file(result[0], as_attachment=True, 
                           download_name=os.path.basename(result[0]))
        else:
            return jsonify({'error': 'PDF negăsit'}), 404
    except Error as e:
        return jsonify({'error': str(e)}), 500
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()

@app.route('/api/cereri/<int:cerere_id>/semneaza', methods=['POST'])
@login_required
def sign_cerere(cerere_id):
    """Semnează o cerere de concediu"""
    connection = get_db_connection()
    if not connection:
        return jsonify({'error': 'Eroare la conectarea la baza de date'}), 500
    
    try:
        data = request.json
        tip_semnatar = data.get('tip')
        semnatura = data.get('semnatura')
        
        if not tip_semnatar or not semnatura:
            return jsonify({'error': 'Date incomplete'}), 400
        
        cursor = connection.cursor(dictionary=True)
        
        cursor.execute('SELECT * FROM cereri_concediu WHERE id = %s', (cerere_id,))
        cerere = cursor.fetchone()
        
        if not cerere:
            return jsonify({'error': 'Cerere negăsită'}), 404
        
        if tip_semnatar == 'solicitant':
            cursor.execute('''
                UPDATE cereri_concediu 
                SET semnat_solicitant = 1, 
                    semnatura_solicitant = %s,
                    data_semnare_solicitant = NOW()
                WHERE id = %s
            ''', (semnatura, cerere_id))
        elif tip_semnatar == 'inlocuitor':
            cursor.execute('''
                UPDATE cereri_concediu 
                SET semnat_inlocuitor = 1,
                    semnatura_inlocuitor = %s,
                    data_semnare_inlocuitor = NOW()
                WHERE id = %s
            ''', (semnatura, cerere_id))
        else:
            return jsonify({'error': 'Tip semnatar invalid'}), 400
        
        connection.commit()
        
        cursor.execute('SELECT semnat_solicitant, semnat_inlocuitor FROM cereri_concediu WHERE id = %s', 
                      (cerere_id,))
        result = cursor.fetchone()
        
        if result['semnat_solicitant'] and result['semnat_inlocuitor']:
            cursor.execute('UPDATE cereri_concediu SET status = "approved" WHERE id = %s', 
                          (cerere_id,))
            connection.commit()
        
        return jsonify({
            'message': 'Semnătură salvată cu succes!',
            'semnat_solicitant': bool(result['semnat_solicitant']),
            'semnat_inlocuitor': bool(result['semnat_inlocuitor'])
        }), 200
        
    except Error as e:
        return jsonify({'error': str(e)}), 500
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()

@app.route('/api/calendar', methods=['GET'])
@login_required
def get_calendar_events():
    """Obține evenimente pentru calendar"""
    connection = get_db_connection()
    if not connection:
        return jsonify({'error': 'Eroare la conectarea la baza de date'}), 500
    
    try:
        cursor = connection.cursor(dictionary=True)
        cursor.execute('SELECT * FROM cereri_concediu')
        cereri = cursor.fetchall()
        
        events = []
        for cerere in cereri:
            events.append({
                'id': cerere['id'],
                'title': cerere['name'],
                'start': cerere['data_start'].strftime('%Y-%m-%d'),
                'end': cerere['data_sfarsit'].strftime('%Y-%m-%d'),
                'color': '#3788d8',
                'extendedProps': {
                    'inlocuitor': cerere['inlocuitor_name'],
                    'nr_zile': cerere['nr_zile'],
                    'status': cerere['status']
                }
            })
        
        return jsonify(events), 200
    except Error as e:
        return jsonify({'error': str(e)}), 500
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()

@app.route('/api/test-db', methods=['GET'])
def test_db():
    """Test conexiune la baza de date"""
    connection = get_db_connection()
    if not connection:
        return jsonify({
            'status': 'error',
            'message': 'Nu se poate conecta la baza de date MySQL',
            'config': {k: v if k != 'password' else '***' for k, v in MYSQL_CONFIG.items()}
        }), 500
    
    try:
        cursor = connection.cursor()
        cursor.execute('SELECT VERSION()')
        version = cursor.fetchone()
        
        cursor.execute('SELECT COUNT(*) FROM employees')
        user_count = cursor.fetchone()
        
        cursor.execute('SHOW TABLES LIKE "cereri_concediu"')
        table_exists = cursor.fetchone()
        
        return jsonify({
            'status': 'success',
            'message': 'Conexiune la baza de date reușită!',
            'mysql_version': version[0] if version else 'unknown',
            'users_count': user_count[0] if user_count else 0,
            'cereri_table_exists': bool(table_exists),
            'database': MYSQL_CONFIG['database']
        }), 200
    except Error as e:
        return jsonify({
            'status': 'error',
            'message': str(e)
        }), 500
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()

if __name__ == '__main__':
    print("="*60)
    print("  Sistem Cereri de Concediu - Cu Autentificare")
    print("="*60)
    print()
    print("Testare conexiune MySQL...")
    
    if init_db():
        print()
        create_word_template()
        print()
        print("="*60)
        print("✅ Server pornit cu succes!")
        print("📍 Accesează aplicația la: http://localhost:5000")
        print("🔐 Autentificare obligatorie (username/password din employees)")
        print("="*60)
        print()
        app.run(debug=False, host='0.0.0.0', port=5000)
    else:
        print()
        print("="*60)
        print("❌ Eroare la inițializarea bazei de date!")
        print("Verifică configurarea MySQL în app.py")
        print("="*60)