from flask import Flask, request, jsonify, session, send_file, url_for, send_from_directory
from datetime import datetime, timedelta
from flask_cors import CORS
import hashlib
import os
from fpdf import FPDF
from sqlalchemy import create_engine, Table, MetaData
from itsdangerous import URLSafeTimedSerializer, SignatureExpired, BadTimeSignature
import mysql.connector
import secrets
from database import conectar_db
from flask_mail import Mail, Message
from werkzeug.utils import secure_filename

app = Flask(__name__)
CORS(app)
app.secret_key = "."

UPLOAD_FOLDER = 'uploads'
os.makedirs(UPLOAD_FOLDER, exist_ok=True)
app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER

def allowed_file(filename):
    return '.' in filename and filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS

ALLOWED_EXTENSIONS = {'png', 'jpg', 'jpeg', 'gif', 'csv', 'xlsx', 'xls', 'pdf'}

serializer = URLSafeTimedSerializer(app.config['SECRET_KEY'])

# -------------------- SHA512 --------------------  #

def hash_password(password):
    return hashlib.sha512(password.encode('utf-8')).hexdigest()

def verify_password(hashed_password, password):
    return hashed_password == hash_password(password)

# -------------------- Configurações do Flask-Mail --------------------  #

app.config['MAIL_SERVER'] = 'mail.purecode.pt'
app.config['MAIL_PORT'] = 465
app.config['MAIL_USE_SSL'] = True  
app.config['MAIL_USE_TLS'] = False  
app.config['MAIL_USERNAME'] = 'unicagetestes@purecode.pt'
app.config['MAIL_PASSWORD'] = 'x#3+E2v(;jkm'
app.config['MAIL_DEFAULT_SENDER'] = 'unicage@deltaunicage.pt'

mail = Mail(app)

# ---------------- Gestão de Tickets ----------------  #

@app.route('/tickets', methods=['POST'])    
def criar_ticket():
    dados = request.json
    try:
        project = dados.get('project', None)
        atributed_to = dados.get('atributed_to', None)
        nome = dados.get('nome')
        tipo = dados.get('tipo')
        data_inicio_estimada = dados.get('data_inicio_estimada')
        data_fim_estimada = dados.get('data_fim_estimada')
        estado = dados.get('estado', 'Aberto')

        if not nome or not tipo:
            return jsonify({"error": "Os campos 'nome' e 'tipo' são obrigatórios"}), 400

        conexao = conectar_db()
        cursor = conexao.cursor()

        cursor.execute("SELECT id FROM tickets WHERE nome = %s", (nome,))
        existing_ticket = cursor.fetchone()
        if existing_ticket:
            return jsonify({"error": "Já existe um ticket com este nome"}), 400

        data_atual = datetime.now().date()

        query = """
            INSERT INTO tickets 
                (project, atributed_to, nome, tipo, data_inicio_estimada, data_fim_estimada, estado, last_modification)
            VALUES 
                (%s, %s, %s, %s, %s, %s, %s, %s)
        """
        cursor.execute(
            query,
            (project, atributed_to, nome, tipo, data_inicio_estimada, data_fim_estimada, estado, data_atual)
        )
        conexao.commit()
        ticket_id = cursor.lastrowid

        cursor.close()
        conexao.close()

        return jsonify({"message": "Ticket criado com sucesso", "ticket_id": ticket_id}), 201

    except Exception as e:
        return jsonify({"error": str(e)}), 400

@app.route('/tickets/<int:ticket_id>', methods=['PUT'])
def atualizar_ticket(ticket_id):
    try:
        conexao = conectar_db()
        cursor = conexao.cursor(dictionary=True)
        dados = request.json

        nome = dados.get('nome')
        if nome:
            cursor.execute("SELECT id FROM tickets WHERE nome = %s AND id != %s", (nome, ticket_id))
            existing_ticket = cursor.fetchone()
            if existing_ticket:
                cursor.close()
                conexao.close()
                return jsonify({"error": "Já existe um ticket com este nome"}), 400

        campos_permitidos = [
            'project', 'atributed_to', 'nome', 'tipo',
            'data_inicio_estimada', 'data_fim_estimada', 'estado',
            'validacao'  
        ]

        campos = []
        valores = []

        for campo in campos_permitidos:
            if campo in dados:
                campos.append(f"{campo} = %s")
                valores.append(dados[campo])

        if not campos:
            cursor.close()
            conexao.close()
            return jsonify({"error": "Nenhum campo para atualizar"}), 400

        campos.append("last_modification = %s")
        valores.append(datetime.now())

        valores.append(ticket_id)

        update_query = f"UPDATE tickets SET {', '.join(campos)} WHERE id = %s"
        cursor.execute(update_query, valores)

        if cursor.rowcount == 0:
            conexao.rollback()
            cursor.close()
            conexao.close()
            return jsonify({"error": "Nenhum campo foi alterado ou ticket não encontrado."}), 404

        conexao.commit()
        cursor.close()
        conexao.close()
        return jsonify({"message": "Ticket atualizado com sucesso"}), 200

    except mysql.connector.Error as db_err:
        return jsonify({"error": f"Erro no banco de dados: {db_err}"}), 500
    except Exception as e:
        return jsonify({"error": str(e)}), 400

@app.route('/tickets', methods=['GET'])
def listar_tickets():
    try:
        conexao = conectar_db()
        cursor = conexao.cursor(dictionary=True)

        query = """
            SELECT 
                t.id,
                p.id AS project_id,
                p.nome AS nome_projeto,
                t.nome,
                t.tipo,
                t.estado,
                t.data_inicio_estimada,
                t.data_fim_estimada,
                t.data_inicio_real,
                t.data_fim_real,
                t.created_at,
                t.last_modification,
                t.validacao, -- ADICIONE AQUI
                u.id AS user_id,
                u.name AS user_name
            FROM tickets t
            LEFT JOIN users u ON t.atributed_to = u.id
            LEFT JOIN projects p ON t.project = p.id
        """
        cursor.execute(query)
        tickets = cursor.fetchall()

        cursor.close()
        conexao.close()

        return jsonify(tickets), 200
    except Exception as e:
        return jsonify({"error": str(e)}), 500
    
@app.route('/tickets/pending', methods=['GET'])
def listar_tickets_pending():
    try:
        conexao = conectar_db()
        cursor = conexao.cursor(dictionary=True)

        query = """
            SELECT 
                t.id,
                p.id AS project_id,
                p.nome AS nome_projeto,
                t.nome,
                t.tipo,
                t.estado,
                t.data_inicio_estimada,
                t.data_fim_estimada,
                t.data_inicio_real,
                t.data_fim_real,
                t.created_at,
                t.last_modification,
                t.validacao,
                u.id AS user_id,
                u.name AS user_name
            FROM tickets t
            LEFT JOIN users u ON t.atributed_to = u.id
            LEFT JOIN projects p ON t.project = p.id
            WHERE t.estado = 'Aberto'
        """
        cursor.execute(query)
        tickets = cursor.fetchall()

        cursor.close()
        conexao.close()

        return jsonify(tickets), 200
    except Exception as e:
        return jsonify({"error": str(e)}), 500


@app.route('/tickets/<int:ticket_id>', methods=['GET'])
def buscar_ticket(ticket_id):
    try:
        conexao = conectar_db()
        cursor = conexao.cursor(dictionary=True)

        query = """
            SELECT 
                t.id,
                p.id AS project_id,
                p.nome AS nome_projeto,
                t.nome,
                t.tipo,
                t.estado,
                t.data_inicio_estimada,
                t.data_fim_estimada,
                t.data_inicio_real,
                t.data_fim_real,
                t.created_at,
                t.last_modification,
                t.validacao, -- ADICIONE AQUI
                u.id AS user_id,
                u.name AS user_name,
                u.email AS atributed_to_email
            FROM tickets t
            LEFT JOIN users u ON t.atributed_to = u.id
            LEFT JOIN projects p ON t.project = p.id
            WHERE t.id = %s
        """
        cursor.execute(query, (ticket_id,))
        ticket = cursor.fetchone()

        cursor.close()
        conexao.close()

        if ticket:
            return jsonify(ticket), 200
        else:
            return jsonify({"error": "Ticket not found"}), 404
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@app.route('/tickets/<int:ticket_id>/estado', methods=['PATCH'])
def atualizar_estado(ticket_id):
    dados = request.json
    novo_estado = dados.get('estado')

    if not novo_estado:
        return jsonify({"error": "The field state is required"}), 400
    if novo_estado not in ['Aberto', 'Em Desenvolvimento', 'Em Validação', 'Fechado', 'Aguardando Aprovação']:
        return jsonify({"error": "Invalid state. Use Open, In Development, In Validation, Closed, or Awaiting Approval"}), 400

    try:
        conexao = conectar_db()
        cursor = conexao.cursor()

        cursor.execute("SELECT estado FROM tickets WHERE id = %s", (ticket_id,))
        resultado = cursor.fetchone()
        if not resultado:
            return jsonify({"error": f"Ticket {ticket_id} not found"}), 404

        estado_atual = resultado[0]
        query = "UPDATE tickets SET estado = %s"
        params = [novo_estado]

        if estado_atual == 'Aberto' and novo_estado == 'Em Desenvolvimento':
            query += ", data_inicio_real = %s"
            params.append(datetime.now().strftime('%Y-%m-%d'))
        elif novo_estado == 'Fechado':
            query += ", data_fim_real = %s"
            params.append(datetime.now().strftime('%Y-%m-%d'))

        query += " WHERE id = %s"
        params.append(ticket_id)

        cursor.execute(query, params)
        conexao.commit()
        cursor.close()
        conexao.close()

        return jsonify({"message": f"Ticket state {ticket_id} updated to {novo_estado}."})
    except Exception as e:
        return jsonify({"error": str(e)}), 400

@app.route('/tickets/<int:ticket_id>/files', methods=['POST'])
def upload_arquivo(ticket_id):
    try:
        if 'file' not in request.files:
            return jsonify({"error": "No file sent"}), 400

        arquivo = request.files['file']
        if arquivo.filename == '':
            return jsonify({"error": "No file selected"}), 400

        caminho_arquivo = os.path.join(app.config['UPLOAD_FOLDER'], f"{ticket_id}_{arquivo.filename}")
        arquivo.save(caminho_arquivo)

        try:
            conexao = conectar_db()
            cursor = conexao.cursor()
            query = """
                INSERT INTO ticket_files (ticket_id, file_name)
                VALUES (%s, %s)
            """
            cursor.execute(query, (ticket_id, arquivo.filename))
            conexao.commit()
            cursor.close()
            conexao.close()

            return jsonify({"message": f"File '{arquivo.filename}"}), 201
        except Exception as e:
            os.remove(caminho_arquivo)
            return jsonify({"error": f"Error registering in the db: {str(e)}"}), 500
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@app.route('/tickets/<int:ticket_id>/files', methods=['GET'])
def listar_arquivos(ticket_id):
    try:
        arquivos = []
        for arquivo_nome in os.listdir(app.config['UPLOAD_FOLDER']):
            if arquivo_nome.startswith(str(ticket_id) + '_'):
                arquivos.append(arquivo_nome)
        
        if arquivos:
            return jsonify({"arquivos": arquivos}), 200
        else:
            return jsonify({"message": "No file found for this ticket"}), 404
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@app.route('/tickets/<int:ticket_id>', methods=['DELETE'])
def deletar_ticket(ticket_id):
    try:
        conexao = conectar_db()
        cursor = conexao.cursor()

        query_dependentes = "DELETE FROM tickets_views WHERE ticket_id = %s"
        cursor.execute(query_dependentes, (ticket_id,))

        query_ticket = "DELETE FROM tickets WHERE id = %s"
        cursor.execute(query_ticket, (ticket_id,))
        conexao.commit()

        rows_afetadas = cursor.rowcount 

        cursor.close()
        conexao.close()

        if rows_afetadas == 0:
            return jsonify({"error": "Ticket not found"}), 404

        return jsonify({"message": f"Ticket {ticket_id} successfully deleted."}), 200
    except Exception as e:
        return jsonify({"error": str(e)}), 500


@app.route('/tickets_views/<int:user_id>', methods=['GET'])
def listar_tickets_views(user_id):
    """
    Retorna todos os tickets que o usuário <user_id> já visualizou,
    junto com a data/hora da visualização (viewed_at).
    """
    try:
        conexao = conectar_db()
        cursor = conexao.cursor(dictionary=True)

        query = """
            SELECT 
                tv.id,
                tv.ticket_id,
                tv.user_id,
                tv.viewed_at,
                t.nome AS ticket_nome
            FROM tickets_views tv
            INNER JOIN tickets t ON tv.ticket_id = t.id
            WHERE tv.user_id = %s
        """
        cursor.execute(query, (user_id,))
        results = cursor.fetchall()

        cursor.close()
        conexao.close()

        return jsonify(results), 200
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@app.route('/tickets_views/<int:ticket_id>/mark_view', methods=['POST'])
def marcar_ticket_como_visto(ticket_id):
    """
    Recebe no body JSON:
        {
            "user_id": <valor>
        }
    e insere um registo na tabela tickets_views para indicar
    que esse usuário (user_id) visualizou o ticket (ticket_id).
    """
    try:
        dados = request.get_json()
        user_id = dados.get('user_id', None)

        if user_id is None:
            return jsonify({"error": "É necessário informar 'user_id' no corpo do JSON"}), 400

        conexao = conectar_db()
        cursor = conexao.cursor()

        sql_insert = """
            INSERT IGNORE INTO tickets_views (ticket_id, user_id)
            VALUES (%s, %s)
        """
        cursor.execute(sql_insert, (ticket_id, user_id))
        conexao.commit()

        rows_affected = cursor.rowcount

        cursor.close()
        conexao.close()

        if rows_affected == 0:
            return jsonify({"message": "Esse ticket já estava marcado como visto para esse usuário."}), 200
        else:
            return jsonify({"message": "Ticket marcado como visto com sucesso."}), 201

    except Exception as e:
        return jsonify({"error": str(e)}), 500

# -------------------- Gestão de Login e Registo --------------------  #

@app.route('/auth/change_password_request', methods=['POST'])
def change_password_request():
    dados = request.json
    email = dados.get('email')

    if not email:
        return jsonify({"error": "O campo email é obrigatório."}), 400

    try:
        conexao = conectar_db()
        cursor = conexao.cursor()
        cursor.execute("SELECT email FROM users WHERE email = %s", (email,))
        user = cursor.fetchone()
        cursor.close()
        conexao.close()

        if not user:
            return jsonify({"error": "Email não encontrado."}), 404

        token = serializer.dumps(email, salt='password-reset-salt')
        
        reset_url = f"http://37.187.49.174:3000/change-password?token={token}"

        subject = "Redefinição de senha"
        message_body = (
            f"Olá,\n\n"
            "Recebemos uma solicitação para redefinir a sua password.\n"
            f"Por favor, clique no link abaixo para definir uma nova senha:\n{reset_url}\n\n"
            "Este link é válido por 30 minutos.\n\n"
            "Atenciosamente,\nDelta Tickets"
        )

        msg = Message(subject, recipients=[email])
        msg.body = message_body
        mail.send(msg)

    except Exception as e:
        return jsonify({"error": str(e)}), 500

    return jsonify({"message": "Email de redefinição de senha enviado com sucesso."}), 200

@app.route('/auth/reset_password/<token>', methods=['POST'])
def reset_password(token):
    try:
        email = serializer.loads(token, salt='password-reset-salt', max_age=1800)
    except SignatureExpired:
        return jsonify({"error": "O link de redefinição de senha expirou."}), 400
    except BadTimeSignature:
        return jsonify({"error": "O link de redefinição de senha é inválido."}), 400

    dados = request.json
    new_password = dados.get('new_password')

    if not new_password:
        return jsonify({"error": "O campo nova senha é obrigatório."}), 400

    hashed_password = hash_password(new_password)

    try:
        conexao = conectar_db()
        cursor = conexao.cursor()
        cursor.execute("UPDATE users SET password = %s WHERE email = %s", (hashed_password, email))
        conexao.commit()
        cursor.close()
        conexao.close()

    except Exception as e:
        return jsonify({"error": str(e)}), 500

    return jsonify({"message": "Senha redefinida com sucesso."}), 200

@app.route('/auth/signup', methods=['POST'])
def signup():
    dados = request.json
    email = dados.get('email')
    password = dados.get('password')
    role = dados.get('role')
    name = dados.get('name')

    if not email or not password or role is None or not name:
        return jsonify({"error": "Os campos email, password, role e name são obrigatórios."}), 400

    hashed_password = hash_password(password)

    try:
        conexao = conectar_db()
        cursor = conexao.cursor()
        query = """
        INSERT INTO users (email, password, role, name, approved)
        VALUES (%s, %s, %s, %s, %s)
        """
        cursor.execute(query, (email, hashed_password, role, name, 0))
        conexao.commit()
        cursor.close()
        conexao.close()

        subject = "Registo está em análise!"
        message_body = (
            f"Olá {name},\n\n"
            "Recebemos a tua solicitação de registo na nossa plataforma.\n"
            "O teu pedido está a ser analisado e em breve receberás uma confirmação de aprovação.\n\n"
            "Atenciosamente,\nDelta Tickets"
        )
        msg = Message(subject, recipients=[email])
        msg.body = message_body
        mail.send(msg)

    except Exception as e:
        return jsonify({"error": str(e)}), 500

    return jsonify({"message": "Registo efetuado com sucesso"}), 201

@app.route('/auth/login', methods=['POST'])
def login():
    dados = request.json
    email = dados.get('email')
    password = dados.get('password')

    if not email or not password:
        return jsonify({"error": "Os campos email e password são obrigatórios."}), 400

    try:
        conexao = conectar_db()
        cursor = conexao.cursor(dictionary=True)
        query = "SELECT * FROM users WHERE email = %s"
        cursor.execute(query, (email,))
        user = cursor.fetchone()

        if user:
            if not user.get('approved'):
                cursor.close()
                conexao.close()
                return jsonify({"error": "Conta pendente de aprovação. Aguarde a liberação para acessar a plataforma."}), 403

            if verify_password(user['password'], password):
                token = secrets.token_urlsafe(32)
                created_at = datetime.now()
                expires_at = created_at + timedelta(hours=1)

                insert_query = """
                INSERT INTO session_tokens (user_id, token, created_at, expires_at)
                VALUES (%s, %s, %s, %s)
                """
                cursor.execute(insert_query, (user['id'], token, created_at, expires_at))
                conexao.commit()

                cursor.close()
                conexao.close()

                return jsonify({
                    "message": "Login successful",
                    "id": user['id'],
                    "email": user['email'],
                    "name": user['name'], 
                    "role": user.get('role'), 
                    "token": token
                }), 200
        cursor.close()
        conexao.close()
        return jsonify({"error": "Invalid credentials"}), 401
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@app.route('/auth/update_username', methods=['PUT'])
def update_username():
    """ 
    Atualiza o username do utilizador com base no token de sessão. 
    """
    data = request.json
    if not data:
        return jsonify({"error": "Falta enviar o body em JSON."}), 400
    
    token = data.get("token")
    new_username = data.get("new_username")

    if not token or not new_username:
        return jsonify({"error": "Precisamos do token e do new_username."}), 400

    try:
        conn = conectar_db()
        cursor = conn.cursor(dictionary=True)

        get_token_query = """
        SELECT * 
        FROM session_tokens 
        WHERE token = %s
          AND expires_at > NOW()
          AND revoked = 0
        """
        cursor.execute(get_token_query, (token,))
        token_info = cursor.fetchone()

        if not token_info:
            cursor.close()
            conn.close()
            return jsonify({"error": "Token inválido ou expirado. Faz login de novo."}), 401

        user_id = token_info['user_id']

        update_user_query = "UPDATE users SET name = %s WHERE id = %s"
        cursor.execute(update_user_query, (new_username, user_id))
        conn.commit()

        cursor.close()
        conn.close()

        return jsonify({
            "message": "Username atualizado com sucesso!",
            "new_username": new_username
        }), 200

    except Exception as e:
        return jsonify({"error": str(e)}), 500

@app.route('/auth/users', methods=['GET'])
def obter_usuarios():
    try:
        conexao = conectar_db()
        cursor = conexao.cursor()

        query = "SELECT id, email, role, name FROM users"
        cursor.execute(query)

        usuarios = cursor.fetchall()

        usuarios_list = []
        for usuario in usuarios:
            usuarios_list.append({
                "id": usuario[0],
                "email": usuario[1],
                "role": usuario[2],
                "name": usuario[3]
            })

        cursor.close()
        conexao.close()

        return jsonify({"usuarios": usuarios_list}), 200

    except Exception as e:
        return jsonify({"error": str(e)}), 500

@app.route('/auth/logout', methods=['POST'])
def logout():
    session.pop('user_id', None)
    return jsonify({"message": "Logout successful"}), 200

# -------------------- Gestão de Perfil --------------------  #

@app.route('/profile/<int:user_id>', methods=['GET'])
def obter_perfil(user_id):
    """
    Endpoint to obtain the user's profile data.
    """
    try:
        conexao = conectar_db()
        cursor = conexao.cursor(dictionary=True)
        query = "SELECT id, email FROM users WHERE id = %s"
        cursor.execute(query, (user_id,))
        usuario = cursor.fetchone()
        cursor.close()
        conexao.close()

        if not usuario:
            return jsonify({"error": "User not found"}), 404

        return jsonify(usuario), 200
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@app.route('/profile/<int:user_id>', methods=['PUT'])
def atualizar_perfil(user_id):
    """
    Endpoint to update the user's profile data.
    """
    dados = request.json
    email = dados.get('email')
    nova_senha = dados.get('password')

    if not email and not nova_senha:
        return jsonify({"error": "It is necessary to provide at least one field for update."}), 400

    try:
        conexao = conectar_db()
        cursor = conexao.cursor()

        campos = []
        valores = []

        if email:
            campos.append("email = %s")
            valores.append(email)

        if nova_senha:
            senha_hash = hash_password(nova_senha)
            campos.append("password = %s")
            valores.append(senha_hash)

        valores.append(user_id)
        query = f"UPDATE users SET {', '.join(campos)} WHERE id = %s"
        cursor.execute(query, valores)
        conexao.commit()
        linhas_afetadas = cursor.rowcount
        cursor.close()
        conexao.close()

        if linhas_afetadas == 0:
            return jsonify({"error": "User not found"}), 404

        return jsonify({"message": "Profile updated successfully"}), 200
    except Exception as e:
        return jsonify({"error": str(e)}), 500
    
# -------------------- Gestão de Projetos --------------------  #

@app.route('/projects', methods=['POST'])
def criar_projeto():
    dados = request.json
    nome = dados.get('nome')
    descricao = dados.get('descricao')
    estado = dados.get('estado', 'Aberto') 
    validation = dados.get('validation', 0)  
    deadline = dados.get('deadline')

    if not nome:
        return jsonify({"error": "The field 'nome' is required"}), 400

    estados_validos = ['Aberto', 'Fechado', 'Completo', 'Em Desenvolvimento']
    if estado not in estados_validos:
        return jsonify({"error": f"Invalid 'estado'. Valid options are: {', '.join(estados_validos)}"}), 400

    if not isinstance(validation, int) or validation not in [0, 1]:
        return jsonify({"error": "The field 'validation' must be 0 or 1"}), 400

    if deadline:
        try:
            datetime.strptime(deadline, '%Y-%m-%d') 
        except ValueError:
            return jsonify({"error": "The field 'deadline' must be in the format YYYY-MM-DD"}), 400

    try:
        conexao = conectar_db()
        cursor = conexao.cursor()
        query = """
            INSERT INTO projects (nome, descricao, estado, validation, deadline)
            VALUES (%s, %s, %s, %s, %s)
        """
        cursor.execute(query, (nome, descricao, estado, validation, deadline))
        conexao.commit()
        project_id = cursor.lastrowid
        cursor.close()
        conexao.close()

        return jsonify({"message": "Project created successfully!", "project_id": project_id}), 201
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@app.route('/projects/<int:project_id>', methods=['PUT'])
def atualizar_projeto(project_id):
    dados = request.json
    nome = dados.get('nome')
    descricao = dados.get('descricao')
    estado = dados.get('estado')
    validation = dados.get('validation')
    deadline = dados.get('deadline')

    if nome is None:
        return jsonify({"error": "The field 'nome' is required"}), 400

    if estado and estado not in ['Aberto', 'Fechado', 'Completo', 'Em Desenvolvimento']:
        return jsonify({"error": "Invalid 'estado'. Valid options are: Aberto, Fechado, Completo, Em Desenvolvimento"}), 400

    if validation is not None and not isinstance(validation, int):
        return jsonify({"error": "The field 'validation' must be an integer (0 or 1)"}), 400
    if validation not in [0, 1]:
        return jsonify({"error": "The field 'validation' must be 0 or 1"}), 400

    if deadline:
        try:
            datetime.strptime(deadline, '%Y-%m-%d')
        except ValueError:
            return jsonify({"error": "The field 'deadline' must be in the format YYYY-MM-DD"}), 400

    try:
        conexao = conectar_db()
        cursor = conexao.cursor()

        query = """
            UPDATE projects
            SET nome = %s,
                descricao = %s,
                estado = %s,
                validation = %s,
                deadline = %s,
                updated_at = NOW()
            WHERE id = %s
        """
        cursor.execute(query, (nome, descricao, estado, validation, deadline, project_id))

        if cursor.rowcount == 0:
            return jsonify({"error": "Nenhum campo foi alterado."}), 404

        conexao.commit()
        cursor.close()
        conexao.close()

        return jsonify({"message": "Project updated successfully!"}), 200

    except Exception as e:
        return jsonify({"error": str(e)}), 500

@app.route('/projects/<int:project_id>', methods=['DELETE'])
def remover_projeto(project_id):
    try:

        conexao = conectar_db()
        cursor = conexao.cursor()

        cursor.execute("SELECT * FROM projects WHERE id = %s", (project_id,))
        projeto = cursor.fetchone()

        if not projeto:
            return jsonify({"error": "Project not found"}), 404

        cursor.execute("DELETE FROM tickets WHERE project = %s", (project_id,))
        conexao.commit()

        cursor.execute("DELETE FROM projects WHERE id = %s", (project_id,))
        conexao.commit()

        cursor.close()
        conexao.close()

        return jsonify({"message": "Project and associated tickets deleted successfully"}), 200
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@app.route('/projects', methods=['GET'])
def listar_projetos():
    try:
        conexao = conectar_db()
        cursor = conexao.cursor(dictionary=True)
        cursor.execute("SELECT * FROM projects") 
        projects = cursor.fetchall()
        cursor.close()
        conexao.close()

        return jsonify(projects), 200
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@app.route('/projects/<int:project_id>', methods=['GET'])
def detalhes_projeto(project_id):
    try:
        conexao = conectar_db()
        cursor = conexao.cursor(dictionary=True)
        cursor.execute("SELECT * FROM projects WHERE id = %s", (project_id,))
        projeto = cursor.fetchone()
        cursor.close()
        conexao.close()

        if projeto:
            return jsonify(projeto), 200
        else:
            return jsonify({"error": "Project not found"}), 404
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@app.route('/projects/<int:project_id>/tickets', methods=['GET'])
def listar_tickets_por_projeto(project_id):
    try:
        conexao = conectar_db()
        cursor = conexao.cursor(dictionary=True)

        query = """
            SELECT 
                t.id,
                p.id AS project_id,
                p.nome AS nome_projeto,
                t.nome,
                t.tipo,
                t.estado,
                t.data_inicio_estimada,
                t.data_fim_estimada,
                t.data_inicio_real,
                t.data_fim_real,
                t.created_at,
                t.last_modification,
                t.validacao,
                u.id AS user_id,
                u.name AS user_name
            FROM tickets t
            LEFT JOIN users u ON t.atributed_to = u.id
            LEFT JOIN projects p ON t.project = p.id
            WHERE t.project = %s
        """
        cursor.execute(query, (project_id,))
        tickets = cursor.fetchall()

        cursor.close()
        conexao.close()

        if not tickets:
            return jsonify({"message": "No tickets associated with this project"}), 404

        return jsonify(tickets), 200
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@app.route('/projects/<int:project_id>/tickets/<int:ticket_id>', methods=['POST'])
def associar_ticket_projeto(project_id, ticket_id):
    try:
        conexao = conectar_db()
        cursor = conexao.cursor()

        cursor.execute("SELECT * FROM projects WHERE id = %s", (project_id,))
        projeto = cursor.fetchone()

        cursor.execute("SELECT * FROM tickets WHERE id = %s", (ticket_id,))
        ticket = cursor.fetchone()

        if not projeto:
            return jsonify({"error": "Project not found"}), 404
        if not ticket:
            return jsonify({"error": "Ticket not found"}), 404

        query = "INSERT INTO project_tickets (project_id, ticket_id) VALUES (%s, %s)"
        cursor.execute(query, (project_id, ticket_id))
        conexao.commit()

        cursor.close()
        conexao.close()

        return jsonify({"message": "Ticket associated with the project successfully"}), 201
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@app.route('/projects/dashboard', methods=['GET'])
def listar_ultimos_projetos():
    """
    Retorna os últimos 3 projetos criados.
    """
    try:
        conexao = conectar_db()
        cursor = conexao.cursor(dictionary=True)

        query = """
            SELECT 
                id,
                nome,
                descricao,
                created_at,
                validation,
                deadline,
                estado
            FROM projects
            ORDER BY created_at DESC
            LIMIT 3
        """
        cursor.execute(query)
        projetos = cursor.fetchall()

        cursor.close()
        conexao.close()

        return jsonify(projetos), 200
    except Exception as e:
        return jsonify({"error": str(e)}), 500

# -------------------- Rotas de Dashboard --------------------  #

@app.route('/tickets/aberto', methods=['GET'])
def listar_tickets_periodos_geral():
    try:
        conexao = conectar_db()
        cursor = conexao.cursor(dictionary=True)

        query_30_dias = """
            SELECT COUNT(*) AS total_tickets_30_dias
            FROM tickets
            WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
        """
        cursor.execute(query_30_dias)
        resultado_30_dias = cursor.fetchone()

        query_3_meses = """
            SELECT COUNT(*) AS total_tickets_3_meses
            FROM tickets
            WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 MONTH)
        """
        cursor.execute(query_3_meses)
        resultado_3_meses = cursor.fetchone()

        query_1_ano = """
            SELECT COUNT(*) AS total_tickets_1_ano
            FROM tickets
            WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
        """
        cursor.execute(query_1_ano)
        resultado_1_ano = cursor.fetchone()

        query_total = """
            SELECT COUNT(*) AS total_tickets_lifetime
            FROM tickets
        """
        cursor.execute(query_total)
        resultado_total = cursor.fetchone()

        query_diaria = """
            SELECT DATE(created_at) AS dia, COUNT(*) AS total
            FROM tickets
            WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
            GROUP BY DATE(created_at)
            ORDER BY DATE(created_at)
        """
        cursor.execute(query_diaria)
        resultados_diarios = cursor.fetchall()

        cursor.close()
        conexao.close()

        daily_counts = {}
        for i in range(30):
            dia = (datetime.now() - timedelta(days=i)).date()
            daily_counts[str(dia)] = 0

        for row in resultados_diarios:
            dia_str = str(row["dia"])
            daily_counts[dia_str] = row["total"]

        tickets_por_dia = [
            {"dia": dia, "total": daily_counts[dia]}
            for dia in sorted(daily_counts.keys())
        ]

        response = {
            "total_tickets_30_dias": resultado_30_dias["total_tickets_30_dias"],
            "total_tickets_3_meses": resultado_3_meses["total_tickets_3_meses"],
            "total_tickets_1_ano": resultado_1_ano["total_tickets_1_ano"],
            "total_tickets_lifetime": resultado_total["total_tickets_lifetime"],
            "tickets_por_dia": tickets_por_dia
        }

        return jsonify(response), 200

    except Exception as e:
        return jsonify({"error": str(e)}), 500

@app.route('/tickets/taxa/prazo', methods=['GET'])
def taxa_prazo():
    try:
        conexao = conectar_db()
        cursor = conexao.cursor(dictionary=True)

        query_ultimos_30 = """
            SELECT
                SUM(CASE WHEN data_fim_real <= data_fim_estimada THEN 1 ELSE 0 END) AS fechados_no_prazo,
                COUNT(*) AS total_fechados
            FROM tickets
            WHERE estado = 'Fechado'
              AND data_fim_real IS NOT NULL
              AND data_fim_estimada IS NOT NULL
              AND data_fim_real >= CURDATE() - INTERVAL 30 DAY
        """
        cursor.execute(query_ultimos_30)
        result_30 = cursor.fetchone()

        fechados_no_prazo_30 = result_30['fechados_no_prazo'] or 0
        total_fechados_30 = result_30['total_fechados'] or 0

        if total_fechados_30 > 0:
            perc_ultimos_30_dias = (fechados_no_prazo_30 / total_fechados_30) * 100
        else:
            perc_ultimos_30_dias = 0

        query_total = """
            SELECT
                SUM(CASE WHEN data_fim_real <= data_fim_estimada THEN 1 ELSE 0 END) AS fechados_no_prazo,
                COUNT(*) AS total_fechados
            FROM tickets
            WHERE estado = 'Fechado'
              AND data_fim_real IS NOT NULL
              AND data_fim_estimada IS NOT NULL
        """
        cursor.execute(query_total)
        result_total = cursor.fetchone()

        fechados_no_prazo_total = result_total['fechados_no_prazo'] or 0
        total_fechados_geral = result_total['total_fechados'] or 0

        if total_fechados_geral > 0:
            perc_total = (fechados_no_prazo_total / total_fechados_geral) * 100
        else:
            perc_total = 0

        cursor.close()
        conexao.close()

        resposta = {
            "percentual_ultimos_30_dias": f"{perc_ultimos_30_dias:.2f}",
            "percentual_total": f"{perc_total:.2f}"
        }

        return jsonify(resposta), 200

    except Exception as e:
        return jsonify({"error": str(e)}), 500

@app.route('/tickets/resolucao', methods=['GET'])
def percentagem_tickets_fechados():
    try:
        conexao = conectar_db()
        cursor = conexao.cursor()

        query_percentagem_fechados = """
            SELECT 
                (COUNT(*) / (SELECT COUNT(*) FROM tickets)) * 100 AS total
            FROM tickets
            WHERE estado = 'Fechado';
        """
        
        query_30_dias = """
            SELECT 
                (COUNT(*) / (SELECT COUNT(*) FROM tickets WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY))) * 100 AS percentagem_30_dias
            FROM tickets
            WHERE estado = 'Fechado' AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY);
        """
        
        query_3_meses = """
            SELECT 
                (COUNT(*) / (SELECT COUNT(*) FROM tickets WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 MONTH))) * 100 AS percentagem_3_meses
            FROM tickets
            WHERE estado = 'Fechado' AND created_at >= DATE_SUB(NOW(), INTERVAL 3 MONTH);
        """
        
        query_1_ano = """
            SELECT 
                (COUNT(*) / (SELECT COUNT(*) FROM tickets WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR))) * 100 AS percentagem_1_ano
            FROM tickets
            WHERE estado = 'Fechado' AND created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR);
        """
        
        cursor.execute(query_percentagem_fechados)
        resultado_total = cursor.fetchone()
        percentagem_total = resultado_total[0] if resultado_total and resultado_total[0] is not None else 0
        
        cursor.execute(query_30_dias)
        resultado_30_dias = cursor.fetchone()
        percentagem_30_dias = resultado_30_dias[0] if resultado_30_dias and resultado_30_dias[0] is not None else 0
        
        cursor.execute(query_3_meses)
        resultado_3_meses = cursor.fetchone()
        percentagem_3_meses = resultado_3_meses[0] if resultado_3_meses and resultado_3_meses[0] is not None else 0
        
        cursor.execute(query_1_ano)
        resultado_1_ano = cursor.fetchone()
        percentagem_1_ano = resultado_1_ano[0] if resultado_1_ano and resultado_1_ano[0] is not None else 0

        cursor.close()
        conexao.close()

        return jsonify({
            "percentagem_fechados_total": percentagem_total,
            "percentagem_fechados_30_dias": percentagem_30_dias,
            "percentagem_fechados_3_meses": percentagem_3_meses,
            "percentagem_fechados_1_ano": percentagem_1_ano
        }), 200
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@app.route('/projects/<int:project_id>/tickets/describe', methods=['GET'])
def listar_tickets_por_projeto_describe(project_id):
    try:
        conexao = conectar_db()
        cursor = conexao.cursor(dictionary=True)

        query = """
            SELECT 
                t.id,
                t.nome,
                t.tipo,
                t.estado,
                t.data_inicio_estimada,
                t.data_fim_estimada,
                t.data_inicio_real,
                t.data_fim_real,
                t.created_at,
                t.last_modification,
                t.validacao,
                u.id AS user_id,
                u.name AS user_name
            FROM tickets t
            LEFT JOIN users u ON t.atributed_to = u.id
            WHERE t.project = %s
              AND t.estado = 'Aberto';
        """
        cursor.execute(query, (project_id,))
        tickets = cursor.fetchall()

        cursor.close()
        conexao.close()

        return jsonify(tickets), 200
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@app.route('/projects/<int:project_id>/tickets/describe/last30', methods=['GET'])
def listar_tickets_criados_ultimos_30_dias(project_id):
    try:
        conexao = conectar_db()
        cursor = conexao.cursor(dictionary=True)

        query = """
            SELECT 
                DAY(created_at) AS dia,
                COUNT(*) AS total_criados
            FROM tickets
            WHERE project = %s 
                AND created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
            GROUP BY dia
            ORDER BY dia ASC;
        """
        cursor.execute(query, (project_id,))
        resultados = cursor.fetchall()

        dias = {str(i): 0 for i in range(1, 31)}
        for resultado in resultados:
            dias[str(resultado['dia'])] = resultado['total_criados']

        cursor.close()
        conexao.close()

        return jsonify({"tickets_por_dia": dias}), 200
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@app.route('/projects/<int:project_id>/tickets/describe/validate', methods=['GET'])
def listar_tickets_nao_validados(project_id):
    try:
        conexao = conectar_db()
        cursor = conexao.cursor()

        query = """
            SELECT *
            FROM tickets
            WHERE project = %s 
                AND (validacao IS NULL OR validacao != 1);
        """
        cursor.execute(query, (project_id,))
        resultado = cursor.fetchall()

        tickets_descricao = []
        colunas = [desc[0] for desc in cursor.description]
        for linha in resultado:
            ticket = dict(zip(colunas, linha))
            tickets_descricao.append(ticket)

        cursor.close()
        conexao.close()

        return jsonify({"tickets_nao_validados": tickets_descricao}), 200
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@app.route('/tickets/total', methods=['GET'])
def listar_total_tickets():
    try:
        conexao = conectar_db()
        cursor = conexao.cursor(dictionary=True)
        
        query_30_dias = """
            SELECT COUNT(*) AS total_tickets_30_dias
            FROM tickets
            WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
        """
        cursor.execute(query_30_dias)
        resultado_30_dias = cursor.fetchone()
        
        query_3_meses = """
            SELECT COUNT(*) AS total_tickets_3_meses
            FROM tickets
            WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 MONTH)
        """
        cursor.execute(query_3_meses)
        resultado_3_meses = cursor.fetchone()

        query_1_ano = """
            SELECT COUNT(*) AS total_tickets_1_ano
            FROM tickets
            WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
        """
        cursor.execute(query_1_ano)
        resultado_1_ano = cursor.fetchone()

        query_total = """
            SELECT COUNT(*) AS total_tickets_lifetime
            FROM tickets
        """
        cursor.execute(query_total)
        resultado_total = cursor.fetchone()

        cursor.close()
        conexao.close()

        return jsonify({
            "total_tickets_30_dias": resultado_30_dias["total_tickets_30_dias"],
            "total_tickets_3_meses": resultado_3_meses["total_tickets_3_meses"],
            "total_tickets_1_ano": resultado_1_ano["total_tickets_1_ano"],
            "total_tickets_lifetime": resultado_total["total_tickets_lifetime"]
        }), 200

    except Exception as e:
        return jsonify({"error": str(e)}), 500

@app.route('/tickets/last30', methods=['GET'])
def listar_tickets_ultimos_30_dias():
    try:
        conexao = conectar_db()
        cursor = conexao.cursor(dictionary=True)

        query_total_30 = """
            SELECT COUNT(*) AS total_tickets_ultimos_30_dias
            FROM tickets
            WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
        """
        cursor.execute(query_total_30)
        resultado_total_30 = cursor.fetchone()

        query_diaria = """
            SELECT DATE(created_at) AS dia, COUNT(*) AS total
            FROM tickets
            WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
            GROUP BY DATE(created_at)
            ORDER BY DATE(created_at)
        """
        cursor.execute(query_diaria)
        resultados_diarios = cursor.fetchall()

        cursor.close()
        conexao.close()

        daily_counts = {}
        for i in range(30):
            dia = (datetime.now() - timedelta(days=i)).date()
            daily_counts[str(dia)] = 0

        for row in resultados_diarios:
            dia_str = str(row["dia"])
            daily_counts[dia_str] = row["total"]

        tickets_por_dia = [
            {"dia": dia, "total": daily_counts[dia]}
            for dia in sorted(daily_counts.keys())
        ]

        response = {
            "tickets_por_dia": tickets_por_dia,
            "total_tickets_ultimos_30_dias": resultado_total_30["total_tickets_ultimos_30_dias"]
        }

        return jsonify(response), 200

    except Exception as e:
        return jsonify({"error": str(e)}), 500

@app.route('/tickets/validacao', methods=['GET'])
def listar_tickets_em_validacao():
    try:
        conexao = conectar_db()
        cursor = conexao.cursor(dictionary=True)

        query_total = """
            SELECT COUNT(*) AS total_tickets_em_validacao
            FROM tickets
            WHERE validacao = 1
        """
        cursor.execute(query_total)
        resultado_total = cursor.fetchone()

        query_30_dias = """
            SELECT COUNT(*) AS total_tickets_em_validacao_30_dias
            FROM tickets
            WHERE validacao = 1
              AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
        """
        cursor.execute(query_30_dias)
        resultado_30_dias = cursor.fetchone()

        cursor.close()
        conexao.close()

        return jsonify({
            "total_tickets_em_validacao": resultado_total["total_tickets_em_validacao"],
            "total_tickets_em_validacao_30_dias": resultado_30_dias["total_tickets_em_validacao_30_dias"]
        }), 200

    except Exception as e:
        return jsonify({"error": str(e)}), 500

@app.route('/tickets/validation_status_report', methods=['GET'])
def validation_status_report():
    try:
        conexao = conectar_db()
        cursor = conexao.cursor(dictionary=True)

        query_totals = """
            SELECT 
                COUNT(*) AS total,
                SUM(CASE WHEN validacao = 1 THEN 1 ELSE 0 END) AS validated,
                SUM(CASE WHEN validacao = 0 OR validacao IS NULL THEN 1 ELSE 0 END) AS pending,
                SUM(CASE WHEN validacao IS NOT NULL AND validacao <> 1 AND validacao <> 0 THEN 1 ELSE 0 END) AS rejected
            FROM tickets
        """
        cursor.execute(query_totals)
        totals = cursor.fetchone()

        total_tickets = totals["total"] or 0
        validated = totals["validated"] or 0
        pending = totals["pending"] or 0
        rejected = totals["rejected"] or 0

        percent_validated = 0 if total_tickets == 0 else (validated / total_tickets) * 100
        percent_pending   = 0 if total_tickets == 0 else (pending / total_tickets) * 100
        percent_rejected  = 0 if total_tickets == 0 else (rejected / total_tickets) * 100

        query_pending_details = """
            SELECT 
                t.nome AS nome_ticket,
                u.name AS responsavel,
                t.created_at,
                p.nome AS projeto_associado
            FROM tickets t
            LEFT JOIN users u ON t.atributed_to = u.id
            LEFT JOIN projects p ON t.project = p.id
            WHERE t.validacao = 0 OR t.validacao IS NULL
        """
        cursor.execute(query_pending_details)
        pending_tickets = cursor.fetchall()

        conteudo_formatado = (
            f"Relatórios e PDFs\n"
            f"1. Estado de Validação\n\n"
            f"Total de tickets: {total_tickets}\n"
            f"Percentual de tickets pendentes: {round(percent_pending, 2)}%\n"
            f"Percentual de tickets validados: {round(percent_validated, 2)}%\n"
            f"Percentual de tickets rejeitados: {round(percent_rejected, 2)}%\n\n"
            f"Tickets pendentes:\n"
        )
        for ticket in pending_tickets:
            conteudo_formatado += (
                f"Nome do ticket: {ticket['nome_ticket']}, "
                f"Responsável: {ticket['responsavel'] or 'Não definido'}, "
                f"Data de criação: {ticket['created_at']}, "
                f"Projeto associado: {ticket['projeto_associado']}\n"
            )

        update_query = "UPDATE reports SET conteudo = %s WHERE id = 2"
        cursor.execute(update_query, (conteudo_formatado,))
        conexao.commit()

        extra_query = """
            SELECT
              ROUND(
                  (SUM(CASE WHEN (validacao IS NULL OR validacao <> 1)
                             AND DATEDIFF(CURRENT_DATE, created_at) > 30
                             THEN 1 ELSE 0 END) * 100.0)
                  / NULLIF(SUM(CASE WHEN DATEDIFF(CURRENT_DATE, created_at) > 30
                             THEN 1 ELSE 0 END), 0)
              , 2) AS percentagem_tickets_nao_validados_30_dias,
              ROUND(
                  (SUM(CASE WHEN (validacao IS NULL OR validacao <> 1)
                             AND DATEDIFF(CURRENT_DATE, created_at) > 90
                             THEN 1 ELSE 0 END) * 100.0)
                  / NULLIF(SUM(CASE WHEN DATEDIFF(CURRENT_DATE, created_at) > 90
                             THEN 1 ELSE 0 END), 0)
              , 2) AS percentagem_tickets_nao_validados_3_meses,
              ROUND(
                  (SUM(CASE WHEN (validacao IS NULL OR validacao <> 1)
                             AND DATEDIFF(CURRENT_DATE, created_at) > 365
                             THEN 1 ELSE 0 END) * 100.0)
                  / NULLIF(SUM(CASE WHEN DATEDIFF(CURRENT_DATE, created_at) > 365
                             THEN 1 ELSE 0 END), 0)
              , 2) AS percentagem_tickets_nao_validados_1_ano
            FROM tickets
        """
        cursor.execute(extra_query)
        extra_stats = cursor.fetchone()

        cursor.close()
        conexao.close()

        response = {
            "total_tickets": total_tickets,
            "percentual": {
                "pendentes": round(percent_pending, 2),
                "validados": round(percent_validated, 2),
                "rejeitados": round(percent_rejected, 2)
            },
            "tickets_pendentes": pending_tickets,
            "percentagem_tickets_nao_validados_30_dias": extra_stats["percentagem_tickets_nao_validados_30_dias"],
            "percentagem_tickets_nao_validados_3_meses": extra_stats["percentagem_tickets_nao_validados_3_meses"],
            "percentagem_tickets_nao_validados_1_ano": extra_stats["percentagem_tickets_nao_validados_1_ano"]
        }

        return jsonify(response), 200

    except Exception as e:
        return jsonify({"error": str(e)}), 500

@app.route('/tickets/performance_team', methods=['GET'])
def performance_team():
    try:
        conexao = conectar_db()
        cursor = conexao.cursor(dictionary=True)

        team_query = """
            SELECT 
                u.name AS responsavel,
                COUNT(t.id) AS total_tickets,
                SUM(CASE WHEN t.estado = 'Completo' THEN 1 ELSE 0 END) AS tickets_resolvidos,
                ROUND(AVG(
                    CASE 
                        WHEN t.estado = 'Completo' 
                             AND t.data_inicio_real IS NOT NULL 
                             AND t.data_fim_real IS NOT NULL 
                        THEN DATEDIFF(t.data_fim_real, t.data_inicio_real)
                        ELSE NULL
                    END
                ), 2) AS tempo_medio_resolucao,
                ROUND(
                    (
                        SUM(
                            CASE 
                                WHEN t.estado = 'Completo'
                                     AND t.data_fim_real IS NOT NULL 
                                     AND t.data_fim_estimada IS NOT NULL 
                                     AND t.data_fim_real <= t.data_fim_estimada 
                                THEN 1 
                                ELSE 0 
                            END
                        ) * 100.0
                    ) / NULLIF(SUM(CASE WHEN t.estado = 'Completo' THEN 1 ELSE 0 END), 0),
                    2
                ) AS taxa_entrega_prazo
            FROM tickets t
            LEFT JOIN users u ON t.atributed_to = u.id
            GROUP BY t.atributed_to, u.name
        """
        cursor.execute(team_query)
        team_performance = cursor.fetchall()

        conteudo_formatado = "Desempenho da Equipa\n\n"
        for member in team_performance:
            tempo_medio = member['tempo_medio_resolucao'] if member['tempo_medio_resolucao'] is not None else "N/A"
            taxa_entrega = member['taxa_entrega_prazo'] if member['taxa_entrega_prazo'] is not None else "N/A"
            conteudo_formatado += (
                f"Responsável: {member['responsavel']}\n"
                f"Total de tickets atribuídos: {member['total_tickets']}\n"
                f"Tickets resolvidos: {member['tickets_resolvidos']}\n"
                f"Tempo médio de resolução: {tempo_medio if tempo_medio != 'N/A' else 'N/A'} dias\n"
                f"Taxa de entrega no prazo: {taxa_entrega if taxa_entrega != 'N/A' else 'N/A'}%\n"
                "--------------------------\n"
            )

        update_query = "UPDATE reports SET conteudo = %s WHERE id = 4"
        cursor.execute(update_query, (conteudo_formatado,))
        conexao.commit()

        extra_query = """
            SELECT
              ROUND(SUM(CASE WHEN estado = 'Aberto' AND DATEDIFF(CURRENT_DATE, created_at) < 30 THEN 1 ELSE 0 END) / 30.0, 2) AS media_tickets_abertos_30_dias,
              ROUND(SUM(CASE WHEN estado = 'Aberto' AND DATEDIFF(CURRENT_DATE, created_at) < 90 THEN 1 ELSE 0 END) / 90.0, 2) AS media_tickets_abertos_3_meses,
              ROUND(SUM(CASE WHEN estado = 'Aberto' AND DATEDIFF(CURRENT_DATE, created_at) < 365 THEN 1 ELSE 0 END) / 365.0, 2) AS media_tickets_abertos_1_ano
            FROM tickets
        """
        cursor.execute(extra_query)
        extra_stats = cursor.fetchone()

        cursor.close()
        conexao.close()

        return jsonify({
            "team_performance": team_performance,
            "pdf_content": conteudo_formatado,
            "media_tickets_abertos_30_dias": extra_stats["media_tickets_abertos_30_dias"],
            "media_tickets_abertos_3_meses": extra_stats["media_tickets_abertos_3_meses"],
            "media_tickets_abertos_1_ano": extra_stats["media_tickets_abertos_1_ano"]
        }), 200

    except Exception as e:
        return jsonify({"error": str(e)}), 500

@app.route('/projects/project_performance_report', methods=['GET'])
def project_performance_report():
    try:
        conexao = conectar_db()
        cursor = conexao.cursor(dictionary=True)

        query = """
            SELECT
                p.nome AS nome_projeto,
                COUNT(t.id) AS total_tickets,
                COALESCE(SUM(CASE WHEN t.estado = 'Completo' THEN 1 ELSE 0 END), 0) AS tickets_concluidos,
                (COUNT(t.id) - COALESCE(SUM(CASE WHEN t.estado = 'Completo' THEN 1 ELSE 0 END), 0)) AS tickets_pendentes,
                ROUND(
                    IF(COUNT(t.id)=0, 0, (COALESCE(SUM(CASE WHEN t.estado = 'Completo' THEN 1 ELSE 0 END), 0) / COUNT(t.id)) * 100),
                    2
                ) AS percentual_conclusao,
                ROUND(AVG(
                    CASE 
                        WHEN t.estado = 'Completo' 
                             AND t.data_inicio_real IS NOT NULL 
                             AND t.data_fim_real IS NOT NULL 
                        THEN DATEDIFF(t.data_fim_real, t.data_inicio_real)
                        ELSE NULL
                    END
                ), 2) AS tempo_medio_resolucao
            FROM projects p
            LEFT JOIN tickets t ON p.id = t.project
            GROUP BY p.id, p.nome
        """
        cursor.execute(query)
        projects = cursor.fetchall()

        conteudo_formatado = "Desempenho do Projeto\n\n"
        for project in projects:
            conteudo_formatado += f"Projeto: {project['nome_projeto']}\n"
            conteudo_formatado += f"Total de tickets: {project['total_tickets']}\n"
            conteudo_formatado += f"Tickets concluídos: {project['tickets_concluidos']}\n"
            conteudo_formatado += f"Tickets pendentes: {project['tickets_pendentes']}\n"
            conteudo_formatado += f"Percentual de conclusão: {project['percentual_conclusao']}%\n"
            tempo_medio = project['tempo_medio_resolucao'] if project['tempo_medio_resolucao'] is not None else "N/A"
            conteudo_formatado += f"Tempo médio de resolução: {tempo_medio} dias\n"
            conteudo_formatado += "-------------------------\n"

        update_query = "UPDATE reports SET conteudo = %s WHERE id = 3"
        cursor.execute(update_query, (conteudo_formatado,))
        conexao.commit()

        extra_query = """
            SELECT
              COALESCE(ROUND(
                  (SUM(CASE WHEN estado IN ('Completo','Fechado')
                             AND DATEDIFF(CURRENT_DATE, created_at) > 30 THEN 1 ELSE 0 END) * 100.0)
                  / NULLIF(SUM(CASE WHEN DATEDIFF(CURRENT_DATE, created_at) > 30 THEN 1 ELSE 0 END), 0)
              , 2), 0) AS percentagem_projetos_completos_30_dias,
              COALESCE(ROUND(
                  (SUM(CASE WHEN estado IN ('Completo','Fechado')
                             AND DATEDIFF(CURRENT_DATE, created_at) > 90 THEN 1 ELSE 0 END) * 100.0)
                  / NULLIF(SUM(CASE WHEN DATEDIFF(CURRENT_DATE, created_at) > 90 THEN 1 ELSE 0 END), 0)
              , 2), 0) AS percentagem_projetos_completos_3_meses,
              COALESCE(ROUND(
                  (SUM(CASE WHEN estado IN ('Completo','Fechado')
                             AND DATEDIFF(CURRENT_DATE, created_at) > 365 THEN 1 ELSE 0 END) * 100.0)
                  / NULLIF(SUM(CASE WHEN DATEDIFF(CURRENT_DATE, created_at) > 365 THEN 1 ELSE 0 END), 0)
              , 2), 0) AS percentagem_projetos_completos_1_ano
            FROM tickets
            WHERE project IS NOT NULL
        """
        cursor.execute(extra_query)
        extra_stats = cursor.fetchone()

        cursor.close()
        conexao.close()

        return jsonify({
            "project_performance": projects,
            "pdf_content": conteudo_formatado,
            "percentagem_projetos_completos_30_dias": extra_stats["percentagem_projetos_completos_30_dias"],
            "percentagem_projetos_completos_3_meses": extra_stats["percentagem_projetos_completos_3_meses"],
            "percentagem_projetos_completos_1_ano": extra_stats["percentagem_projetos_completos_1_ano"]
        }), 200

    except Exception as e:
        return jsonify({"error": str(e)}), 500

@app.route('/projects/<int:project_id>/metricas', methods=['GET'])
def listar_metricas_project(project_id):
    """
    Retorna três métricas para o projeto especificado:
    - total_tickets_abertos: quantidade de tickets com estado 'Aberto'
    - total_tickets_validacao_diferente: quantidade de tickets cujo campo validacao não é igual a 1 
      (incluindo os nulos)
    - total_tickets_ultimos_30_dias: quantidade de tickets criados nos últimos 30 dias
    """
    try:
        conexao = conectar_db()
        cursor = conexao.cursor(dictionary=True)
        
        query = """
            SELECT
                (SELECT COUNT(*) 
                 FROM tickets 
                 WHERE project = %s AND estado = 'Aberto') AS total_tickets_abertos,
                 
                (SELECT COUNT(*) 
                 FROM tickets 
                 WHERE project = %s AND (validacao <> 1 OR validacao IS NULL)) AS total_tickets_validacao_diferente,
                 
                (SELECT COUNT(*) 
                 FROM tickets 
                 WHERE project = %s AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)) AS total_tickets_ultimos_30_dias
        """
        cursor.execute(query, (project_id, project_id, project_id))
        resultado = cursor.fetchone()

        cursor.close()
        conexao.close()

        return jsonify({
            "project_id": project_id,
            "total_tickets_abertos": resultado["total_tickets_abertos"],
            "total_tickets_validacao_diferente": resultado["total_tickets_validacao_diferente"],
            "total_tickets_ultimos_30_dias": resultado["total_tickets_ultimos_30_dias"]
        }), 200

    except Exception as e:
        return jsonify({"error": str(e)}), 500

# -------------------- Gestão de Reports --------------------  #

@app.route('/reports', methods=['POST'])
def criar_relatorio():
    dados = request.json
    nome = dados.get('nome')
    conteudo = dados.get('conteudo')
    data_criacao = datetime.now()

    if not nome or not conteudo:
        return jsonify({"error": "The fields name and content are required."}), 400

    try:
        conexao = conectar_db()
        cursor = conexao.cursor()
        query = """
            INSERT INTO reports (nome, conteudo, data_criacao, validado)
            VALUES (%s, %s, %s, %s)
        """
        cursor.execute(query, (nome, conteudo, data_criacao, False))
        conexao.commit()
        relatorio_id = cursor.lastrowid
        cursor.close()
        conexao.close()

        return jsonify({"message": "Report created successfully!", "relatorio_id": relatorio_id}), 201
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@app.route('/reports', methods=['GET'])
def listar_relatorios():
    try:
        conexao = conectar_db()
        cursor = conexao.cursor(dictionary=True)
        cursor.execute("SELECT * FROM reports")
        relatorios = cursor.fetchall()
        cursor.close()
        conexao.close()

        return jsonify(relatorios), 200
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@app.route('/reports/<int:report_id>/validate', methods=['PUT'])
def validar_relatorio(report_id):
    try:
        conexao = conectar_db()
        cursor = conexao.cursor()
        query = """
            UPDATE reports
            SET validado = %s
            WHERE id = %s
        """
        cursor.execute(query, (True, report_id))
        conexao.commit()

        if cursor.rowcount == 0:
            return jsonify({"error": "Report not found"}), 404

        cursor.close()
        conexao.close()

        return jsonify({"message": f"Report {report_id} validated successfully!"}), 200
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@app.route('/reports/download/<int:report_id>', methods=['GET'])
def download_relatorio(report_id):
    try:
        conexao = conectar_db()
        cursor = conexao.cursor(dictionary=True)
        cursor.execute("SELECT * FROM reports WHERE id = %s", (report_id,))
        relatorio = cursor.fetchone()
        cursor.close()
        conexao.close()

        if not relatorio:
            return jsonify({"error": "Report not found"}), 404

        pdf = FPDF()
        pdf.add_page()
        pdf.set_font('Arial', 'B', 16)
        pdf.cell(40, 10, relatorio['nome'])
        pdf.ln(10)
        pdf.set_font('Arial', '', 12)
        pdf.multi_cell(0, 10, relatorio['conteudo'])

        caminho_arquivo = os.path.join(UPLOAD_FOLDER, f"relatorio_{report_id}.pdf")
        pdf.output(caminho_arquivo)

        return send_file(caminho_arquivo, as_attachment=True)
    except Exception as e:
        return jsonify({"error": str(e)}), 500

# -------------------- Comentarios Tickets --------------------  #

@app.route('/uploads/<path:filename>')
def uploaded_file(filename):
    return send_from_directory(app.config['UPLOAD_FOLDER'], filename)

@app.route('/comments/<int:ticket_id>', methods=['POST'])
def adicionar_comentario(ticket_id):
    try:
        conexao = conectar_db()
        cursor = conexao.cursor()

        cursor.execute("SELECT id FROM tickets WHERE id = %s", (ticket_id,))
        ticket_existe = cursor.fetchone()

        if not ticket_existe:
            cursor.close()
            conexao.close()
            return jsonify({"error": f"O Ticket ID {ticket_id} não existe"}), 404

        user_id = request.form.get('user_id')
        comment = request.form.get('comment')

        if not (user_id and comment):
            cursor.close()
            conexao.close()
            return jsonify({"error": "Os campos 'user_id' e 'comment' são obrigatórios"}), 400

        file_paths = []
        if 'files' in request.files:
            files = request.files.getlist('files')
            for file in files:
                if file and allowed_file(file.filename):
                    filename = secure_filename(file.filename)
                    file_path = os.path.join(app.config['UPLOAD_FOLDER'], filename)
                    file.save(file_path)
                    file_paths.append(file_path)

        attachments_str = ','.join(file_paths) if file_paths else ''

        cursor.execute("""
            INSERT INTO tickets_comments (ticket_id, user_id, comment, attachments)
            VALUES (%s, %s, %s, %s)
        """, (ticket_id, user_id, comment, attachments_str))
        conexao.commit()

        cursor.close()
        conexao.close()

        return jsonify({
            "message": "Comentário adicionado com sucesso",
            "files_uploaded": file_paths
        }), 201
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@app.route('/comments/<int:comment_id>', methods=['DELETE'])
def remover_comentario(comment_id):
    try:
        conexao = conectar_db()
        cursor = conexao.cursor()
        cursor.execute("DELETE FROM tickets_comments WHERE id = %s", (comment_id,))
        conexao.commit()
        cursor.close()
        conexao.close()

        return jsonify({"message": "Comment deleted successfully"}), 200
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@app.route('/comments/<int:ticket_id>', methods=['GET'])
def listar_comentarios_por_ticket(ticket_id):
    try:
        conexao = conectar_db()
        cursor = conexao.cursor(dictionary=True)
        cursor.execute("""
            SELECT tc.id, tc.comment, tc.created_at, tc.attachments, u.name AS user_name, u.email AS user_email
            FROM tickets_comments tc
            INNER JOIN users u ON tc.user_id = u.id
            WHERE tc.ticket_id = %s
            ORDER BY tc.created_at DESC
        """, (ticket_id,))
        comments = cursor.fetchall()
        cursor.close()
        conexao.close()

        if not comments:
            return jsonify({"message": "No comments found for this ticket"}), 404

        for comment in comments:
            if comment['attachments']:
                comment['attachments'] = comment['attachments'].split(',')
            else:
                comment['attachments'] = []

        return jsonify(comments), 200
    except Exception as e:
        return jsonify({"error": str(e)}), 500

# -------------------- Search --------------------  #

@app.route('/search/<string:termo>', methods=['GET'])
def pesquisar(termo):
    """
    Pesquisa por um termo nos títulos dos projetos e tickets.
    Retorna os projetos e tickets que contêm o termo no campo 'nome'.
    """
    try:
        termo = termo.strip()
        
        if not termo:
            return jsonify({"error": "Parâmetro de pesquisa é necessário."}), 400

        conexao = conectar_db()
        cursor = conexao.cursor(dictionary=True)

        query_projetos = """
            SELECT 
                id,
                nome,
                descricao,
                created_at,
                validation,
                deadline,
                estado
            FROM projects
            WHERE nome LIKE %s
            ORDER BY created_at DESC
        """
        like_termo = f"%{termo}%"
        cursor.execute(query_projetos, (like_termo,))
        projetos = cursor.fetchall()

        query_tickets = """
            SELECT 
                t.id,
                t.project,
                p.nome AS nome_projeto,
                t.atributed_to,
                u.name AS nome_usuario,
                t.nome,
                t.tipo,
                t.estado,
                t.data_inicio_estimada,
                t.data_fim_estimada,
                t.data_inicio_real,
                t.data_fim_real,
                t.created_at,
                t.last_modification
            FROM tickets t
            LEFT JOIN projects p ON t.project = p.id
            LEFT JOIN users u ON t.atributed_to = u.id
            WHERE t.nome LIKE %s
            ORDER BY t.created_at DESC
        """
        cursor.execute(query_tickets, (like_termo,))
        tickets = cursor.fetchall()

        cursor.close()
        conexao.close()

        return jsonify({
            "projetos": projetos,
            "tickets": tickets
        }), 200

    except Exception as e:
        return jsonify({"error": str(e)}), 500

# -------------------- Fav Tickets --------------------  #

@app.route('/projects/fav', methods=['POST'])
def add_favorite():
    try:
        data = request.get_json()
        user_id = data.get('user_id')
        project_id = data.get('project_id')

        if not user_id or not project_id:
            return jsonify({"error": "user_id e project_id são obrigatórios"}), 400

        conexao = conectar_db()
        cursor = conexao.cursor(dictionary=True)

        check_query = """
            SELECT id FROM projects_fav
            WHERE user_id = %s AND project_id = %s
        """
        cursor.execute(check_query, (user_id, project_id))
        existing = cursor.fetchone()
        if existing:
            cursor.close()
            conexao.close()
            return jsonify({"message": "Este projeto já está favoritado pelo usuário."}), 200

        insert_query = """
            INSERT INTO projects_fav (user_id, project_id)
            VALUES (%s, %s)
        """
        cursor.execute(insert_query, (user_id, project_id))
        conexao.commit()

        cursor.close()
        conexao.close()

        return jsonify({"message": "Favorito adicionado com sucesso."}), 201

    except Exception as e:
        return jsonify({"error": str(e)}), 500

@app.route('/projects/fav', methods=['DELETE'])
def remove_favorite():
    try:
        data = request.get_json()
        user_id = data.get('user_id')
        project_id = data.get('project_id')

        if not user_id or not project_id:
            return jsonify({"error": "user_id e project_id são obrigatórios"}), 400

        conexao = conectar_db()
        cursor = conexao.cursor(dictionary=True)

        delete_query = """
            DELETE FROM projects_fav
            WHERE user_id = %s AND project_id = %s
        """
        cursor.execute(delete_query, (user_id, project_id))
        conexao.commit()

        if cursor.rowcount == 0:
            msg = "Nenhum favorito removido. Verifique se já existia esse favorito."
        else:
            msg = "Favorito removido com sucesso."

        cursor.close()
        conexao.close()

        return jsonify({"message": msg}), 200

    except Exception as e:
        return jsonify({"error": str(e)}), 500

@app.route('/projects/fav/<int:user_id>', methods=['GET'])
def get_favorites(user_id):
    try:
        conexao = conectar_db()
        cursor = conexao.cursor(dictionary=True)

        query = """
            SELECT
                pf.id AS favorite_id,
                p.id AS project_id,
                p.nome AS project_name,
                p.descricao AS project_description,
                p.created_at AS project_created_at,
                p.deadline AS project_deadline,
                p.estado AS project_estado,
                pf.created_at AS favorited_at
            FROM projects_fav pf
            JOIN projects p ON p.id = pf.project_id
            WHERE pf.user_id = %s
        """
        cursor.execute(query, (user_id,))
        favorites = cursor.fetchall()

        cursor.close()
        conexao.close()

        return jsonify(favorites), 200
    except Exception as e:
        return jsonify({"error": str(e)}), 500

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