from flask import Flask, request, jsonify, render_template, send_from_directory, redirect, url_for, flash, session, Response
import os
import json
import subprocess
import sys
from datetime import datetime, timedelta
import sqlite3
from werkzeug.security import generate_password_hash, check_password_hash
import jwt
import secrets
import requests

app = Flask(__name__)
app.config['SECRET_KEY'] = os.environ.get('SECRET_KEY', secrets.token_hex(32))
app.config['JWT_SECRET'] = os.environ.get('JWT_SECRET', secrets.token_hex(32))

# Database setup
def init_db():
    conn = sqlite3.connect('softedge.db')
    c = conn.cursor()
    c.execute('''CREATE TABLE IF NOT EXISTS users
                 (id INTEGER PRIMARY KEY AUTOINCREMENT,
                  email TEXT UNIQUE NOT NULL,
                  password TEXT NOT NULL,
                  name TEXT,
                  role TEXT DEFAULT 'user',
                  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)''')
    c.execute('''CREATE TABLE IF NOT EXISTS contact_submissions
                 (id INTEGER PRIMARY KEY AUTOINCREMENT,
                  name TEXT NOT NULL,
                  email TEXT NOT NULL,
                  message TEXT NOT NULL,
                  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)''')
    c.execute('''CREATE TABLE IF NOT EXISTS projects
                 (id INTEGER PRIMARY KEY AUTOINCREMENT,
                  title TEXT NOT NULL,
                  description TEXT NOT NULL,
                  status TEXT DEFAULT 'active',
                  technologies TEXT,
                  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)''')
    conn.commit()
    conn.close()

init_db()

# Helper functions
def get_db_connection():
    conn = sqlite3.connect('softedge.db')
    conn.row_factory = sqlite3.Row
    return conn

def create_token(user_id):
    payload = {
        'user_id': user_id,
        'exp': datetime.utcnow() + timedelta(hours=24)
    }
    return jwt.encode(payload, app.config['JWT_SECRET'], algorithm='HS256')

# PHP Integration
def call_php_script(script_name, method='GET', data=None):
    """Call PHP script and return response"""
    try:
        php_path = os.path.join(os.getcwd(), script_name)
        if not os.path.exists(php_path):
            return None

        # For simple GET requests, we can execute PHP directly
        if method == 'GET':
            result = subprocess.run(['php', php_path],
                                  capture_output=True, text=True, timeout=30)
            if result.returncode == 0:
                return result.stdout
        return None
    except Exception as e:
        print(f"PHP execution error: {e}")
        return None

# Routes
@app.route('/')
def home():
    # Get dynamic stats
    stats = {'projects': 0, 'contacts': 0, 'satisfaction': 4.9}
    try:
        conn = get_db_connection()
        stats['projects'] = conn.execute('SELECT COUNT(*) as count FROM projects').fetchone()['count']
        stats['contacts'] = conn.execute('SELECT COUNT(*) as count FROM contact_submissions').fetchone()['count']
        conn.close()
    except Exception as e:
        print(f"Stats error: {e}")

    return render_template('index.html', stats=stats)

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

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

@app.route('/projetos')
def projetos():
    # Get projects from database
    projects = []
    try:
        conn = get_db_connection()
        projects = conn.execute('SELECT * FROM projects ORDER BY created_at DESC').fetchall()
        conn.close()
        projects = [dict(project) for project in projects]
    except Exception as e:
        print(f"Projects error: {e}")

    return render_template('projetos.html', projects=projects)

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

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

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

@app.route('/admin')
def admin():
    # Check authentication
    if 'user_id' not in session:
        return redirect(url_for('login'))

    # Get admin stats
    stats = {'total_users': 0, 'total_contacts': 0, 'recent_users': [], 'recent_contacts': []}
    try:
        conn = get_db_connection()
        stats['total_users'] = conn.execute('SELECT COUNT(*) as count FROM users').fetchone()['count']
        stats['total_contacts'] = conn.execute('SELECT COUNT(*) as count FROM contact_submissions').fetchone()['count']

        # Recent users
        stats['recent_users'] = conn.execute(
            'SELECT name, email, created_at FROM users ORDER BY created_at DESC LIMIT 5'
        ).fetchall()

        # Recent contacts
        stats['recent_contacts'] = conn.execute(
            'SELECT name, email, message, created_at FROM contact_submissions ORDER BY created_at DESC LIMIT 5'
        ).fetchall()

        conn.close()
        stats['recent_users'] = [dict(user) for user in stats['recent_users']]
        stats['recent_contacts'] = [dict(contact) for contact in stats['recent_contacts']]
    except Exception as e:
        print(f"Admin stats error: {e}")

    return render_template('admin.html', stats=stats)

# API Routes
@app.route('/api/health')
def health():
    return jsonify({
        'status': 'ok',
        'timestamp': datetime.now().isoformat(),
        'service': 'SoftEdge Hybrid API',
        'version': '3.0.0',
        'technologies': ['Flask', 'PHP', 'Node.js', 'MySQL']
    })

@app.route('/api/stats')
def stats():
    try:
        conn = get_db_connection()
        projects_count = conn.execute('SELECT COUNT(*) as count FROM projects').fetchone()['count']
        contact_count = conn.execute('SELECT COUNT(*) as count FROM contact_submissions').fetchone()['count']
        conn.close()

        return jsonify({
            'projects': projects_count,
            'contacts': contact_count,
            'satisfaction': 4.9,
            'uptime': '99.9%'
        })
    except Exception as e:
        return jsonify({'error': 'Database error'}), 500

@app.route('/api/contact', methods=['POST'])
def contact():
    data = request.get_json()

    if not data or not all(k in data for k in ['name', 'email', 'message']):
        return jsonify({'error': 'Missing required fields'}), 400

    try:
        conn = get_db_connection()
        conn.execute('INSERT INTO contact_submissions (name, email, message) VALUES (?, ?, ?)',
                     (data['name'], data['email'], data['message']))
        conn.commit()
        conn.close()

        return jsonify({'message': 'Message sent successfully', 'status': 'success'}), 200
    except Exception as e:
        return jsonify({'error': 'Database error'}), 500

@app.route('/api/auth/login', methods=['POST'])
def api_login():
    data = request.get_json()

    if not data or not all(k in data for k in ['email', 'password']):
        return jsonify({'error': 'Missing credentials'}), 400

    try:
        conn = get_db_connection()
        user = conn.execute('SELECT * FROM users WHERE email = ?', (data['email'],)).fetchone()
        conn.close()

        if user and check_password_hash(user['password'], data['password']):
            session['user_id'] = user['id']
            session['user_email'] = user['email']
            session['user_name'] = user['name']
            session['user_role'] = user['role']
            token = create_token(user['id'])

            return jsonify({
                'message': 'Login successful',
                'token': token,
                'user': {
                    'id': user['id'],
                    'email': user['email'],
                    'name': user['name'],
                    'role': user['role']
                }
            }), 200
        else:
            return jsonify({'error': 'Invalid credentials'}), 401
    except Exception as e:
        return jsonify({'error': 'Database error'}), 500

@app.route('/api/auth/register', methods=['POST'])
def api_register():
    data = request.get_json()

    if not data or not all(k in data for k in ['email', 'password', 'name']):
        return jsonify({'error': 'Missing required fields'}), 400

    try:
        hashed_password = generate_password_hash(data['password'])

        conn = get_db_connection()
        conn.execute('INSERT INTO users (email, password, name) VALUES (?, ?, ?)',
                     (data['email'], hashed_password, data['name']))
        conn.commit()
        user_id = conn.execute('SELECT last_insert_rowid()').fetchone()[0]
        conn.close()

        token = create_token(user_id)
        return jsonify({
            'message': 'Registration successful',
            'token': token,
            'user': {
                'id': user_id,
                'email': data['email'],
                'name': data['name']
            }
        }), 201
    except sqlite3.IntegrityError:
        return jsonify({'error': 'Email already exists'}), 409
    except Exception as e:
        return jsonify({'error': 'Database error'}), 500

@app.route('/api/auth/logout')
def logout():
    session.clear()
    return jsonify({'message': 'Logged out successfully'})

# PHP Integration Routes
@app.route('/php/<path:script>')
def php_route(script):
    """Route to execute PHP scripts"""
    result = call_php_script(f'{script}.php')
    if result:
        return Response(result, mimetype='text/html')
    return jsonify({'error': 'PHP script not found'}), 404

# Static files
@app.route('/assets/<path:filename>')
def assets(filename):
    return send_from_directory('assets', filename)

@app.route('/css/<path:filename>')
def css_files(filename):
    return send_from_directory('css', filename)

@app.route('/js/<path:filename>')
def js_files(filename):
    return send_from_directory('js', filename)

# React integration
@app.route('/react-app')
def react_app():
    return render_template('react-app.html')

if __name__ == '__main__':
    # Seed initial data
    conn = get_db_connection()
    if not conn.execute('SELECT COUNT(*) FROM projects').fetchone()[0]:
        projects = [
            ('AKIRA IA', 'Assistente virtual angolano com processamento de linguagem natural', 'Concluído', 'Python, TensorFlow, FastAPI'),
            ('ERP Gestão Total', 'Sistema completo de gestão empresarial', 'Concluído', 'Laravel, Vue.js, MySQL'),
            ('E-commerce ShopFast', 'Plataforma de vendas online de alta performance', 'Concluído', 'Next.js, Stripe, Prisma'),
            ('Sistema de Gestão Escolar', 'Plataforma completa para gestão educacional', 'Em desenvolvimento', 'React, Node.js, PostgreSQL'),
            ('App Mobile Delivery', 'Aplicativo de delivery com geolocalização', 'Em desenvolvimento', 'Flutter, Firebase, Google Maps')
        ]
        conn.executemany('INSERT INTO projects (title, description, status, technologies) VALUES (?, ?, ?, ?)', projects)
        conn.commit()

    # Create admin user
    admin_email = os.environ.get('ADMIN_EMAIL', 'admin@softedge.com')
    admin_password = os.environ.get('ADMIN_PASSWORD', 'Admin@123456')
    admin_name = os.environ.get('ADMIN_NAME', 'Isaac Quarenta')

    if not conn.execute('SELECT id FROM users WHERE email = ?', (admin_email,)).fetchone():
        hashed_password = generate_password_hash(admin_password)
        conn.execute('INSERT INTO users (email, password, name, role) VALUES (?, ?, ?, ?)',
                     (admin_email, hashed_password, admin_name, 'super_admin'))
        conn.commit()

    conn.close()

    print("🚀 SoftEdge Corporation - Hybrid Architecture Started!")
    print("Technologies: Flask (Python) + PHP + Node.js + MySQL")
    print("Server running on http://localhost:7860")

    app.run(debug=True, host='0.0.0.0', port=int(os.environ.get('PORT', 7860)))