from flask import Flask, request, jsonify
from flask_cors import CORS
import sqlite3
import matplotlib
matplotlib.use('Agg')
import matplotlib.pyplot as plt
import base64
import io
import bcrypt
import os
from datetime import datetime, timedelta
import json


app = Flask(__name__)
CORS(app)

# Configuração do SQLite
DB_PATH = 'ctrlcash.db'

# Funções do Banco de Dados
def init_db():
    """Inicializa o banco de dados e cria as tabelas"""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    
    # Tabela de usuários
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            email TEXT UNIQUE NOT NULL,
            password TEXT NOT NULL,
            name TEXT NOT NULL,
            monthly_income_goal REAL DEFAULT 5000,
            monthly_expense_limit REAL DEFAULT 2500,
            currency TEXT DEFAULT 'BRL',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    
    # Tabela de categorias
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS categories (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            name TEXT NOT NULL,
            type TEXT NOT NULL,
            color TEXT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users (id),
            UNIQUE(user_id, name, type)
        )
    ''')
    
    # Tabela de transações
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS transactions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            amount REAL NOT NULL,
            description TEXT NOT NULL,
            category TEXT NOT NULL,
            type TEXT NOT NULL,
            date DATE NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users (id)
        )
    ''')
    
    conn.commit()
    conn.close()

def get_db_connection():
    """Cria uma conexão com o banco de dados"""
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row  # Para retornar dicionários
    return conn

# Categorias padrão
default_categories = [
    {"name": "Alimentação", "type": "expense", "color": "#FF6B6B"},
    {"name": "Transporte", "type": "expense", "color": "#4ECDC4"},
    {"name": "Moradia", "type": "expense", "color": "#45B7D1"},
    {"name": "Saúde", "type": "expense", "color": "#96CEB4"},
    {"name": "Educação", "type": "expense", "color": "#FFEAA7"},
    {"name": "Lazer", "type": "expense", "color": "#DDA0DD"},
    {"name": "Salário", "type": "income", "color": "#98D8C8"},
    {"name": "Investimentos", "type": "income", "color": "#F7DC6F"},
    {"name": "Freelance", "type": "income", "color": "#BB8FCE"},
    {"name": "Outros", "type": "income", "color": "#95a5a6"},
    {"name": "Outros", "type": "expense", "color": "#95a5a6"}
]

# Funções auxiliares
def hash_password(password):
    return bcrypt.hashpw(password.encode('utf-8'), bcrypt.gensalt()).decode('utf-8')

def check_password(password, hashed):
    return bcrypt.checkpw(password.encode('utf-8'), hashed.encode('utf-8'))

def init_default_categories(user_id):
    """Insere categorias padrão para um usuário"""
    conn = get_db_connection()
    cursor = conn.cursor()
    
    for cat in default_categories:
        cursor.execute('''
            INSERT OR IGNORE INTO categories (user_id, name, type, color)
            VALUES (?, ?, ?, ?)
        ''', (user_id, cat['name'], cat['type'], cat['color']))
    
    conn.commit()
    conn.close()



# ROTAS DE AUTENTICAÇÃO
@app.route('/api/auth/register', methods=['POST'])
def register():
    try:
        data = request.get_json()
        email = data.get('email', '').strip().lower()
        password = data.get('password', '')
        name = data.get('name', '').strip()
        
        if not email or not password or not name:
            return jsonify({"error": "Todos os campos são obrigatórios"}), 400
        
        if len(password) < 6:
            return jsonify({"error": "A senha deve ter pelo menos 6 caracteres"}), 400
        
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # Verificar se usuário já existe
        cursor.execute('SELECT id FROM users WHERE email = ?', (email,))
        if cursor.fetchone():
            conn.close()
            return jsonify({"error": "Email já cadastrado"}), 400
        
        # Criar usuário
        cursor.execute('''
            INSERT INTO users (email, password, name)
            VALUES (?, ?, ?)
        ''', (email, hash_password(password), name))
        
        user_id = cursor.lastrowid
        
        conn.commit()
        conn.close()
        
        # Inicializar categorias e dados mock
        init_default_categories(user_id)
        
        return jsonify({
            "message": "Usuário criado com sucesso",
            "user": {
                "id": user_id,
                "email": email,
                "name": name,
                "profile": {
                    "monthly_income_goal": 5000,
                    "monthly_expense_limit": 2500,
                    "currency": "BRL"
                }
            }
        }), 201
        
    except Exception as e:
        print(f"Erro no registro: {e}")
        return jsonify({"error": "Erro interno do servidor"}), 500

@app.route('/api/auth/login', methods=['POST'])
def login():
    try:
        data = request.get_json()
        email = data.get('email', '').strip().lower()
        password = data.get('password', '')
        
        if not email or not password:
            return jsonify({"error": "Email e senha são obrigatórios"}), 400
        
        conn = get_db_connection()
        cursor = conn.cursor()
        
        cursor.execute('''
            SELECT id, email, password, name, monthly_income_goal, monthly_expense_limit, currency 
            FROM users WHERE email = ?
        ''', (email,))
        
        user = cursor.fetchone()
        conn.close()
        
        if not user or not check_password(password, user['password']):
            return jsonify({"error": "Credenciais inválidas"}), 401
        
        return jsonify({
            "message": "Login realizado com sucesso",
            "user": {
                "id": user['id'],
                "email": user['email'],
                "name": user['name'],
                "profile": {
                    "monthly_income_goal": user['monthly_income_goal'],
                    "monthly_expense_limit": user['monthly_expense_limit'],
                    "currency": user['currency']
                }
            }
        }), 200
        
    except Exception as e:
        print(f"Erro no login: {e}")
        return jsonify({"error": "Erro interno do servidor"}), 500

# ROTAS DE TRANSAÇÕES
@app.route('/api/transactions', methods=['GET'])
def get_transactions():
    try:
        user_id = request.args.get('user_id')
        if not user_id:
            return jsonify({"transactions": []}), 200
        
        conn = get_db_connection()
        cursor = conn.cursor()
        
        cursor.execute('''
            SELECT id, user_id, amount, description, category, type, date
            FROM transactions 
            WHERE user_id = ?
            ORDER BY date DESC
        ''', (user_id,))
        
        transactions = []
        for row in cursor.fetchall():
            transactions.append({
                "id": row['id'],
                "user_id": row['user_id'],
                "amount": row['amount'],
                "description": row['description'],
                "category": row['category'],
                "type": row['type'],
                "date": row['date']
            })
        
        conn.close()
        return jsonify({"transactions": transactions}), 200
        
    except Exception as e:
        print(f"Erro ao buscar transações: {e}")
        return jsonify({"error": "Erro ao buscar transações"}), 500

@app.route('/api/transactions', methods=['POST'])
def add_transaction():
    try:
        data = request.get_json()
        user_id = data.get('user_id')
        
        if not user_id:
            return jsonify({"error": "User ID é obrigatório"}), 400
        
        # Validar dados
        amount = float(data.get('amount', 0))
        description = data.get('description', '').strip()
        category = data.get('category', 'Outros')
        transaction_type = data.get('type', 'expense')
        date_str = data.get('date', '')
        
        if amount <= 0:
            return jsonify({"error": "Valor deve ser maior que zero"}), 400
        
        if not description:
            return jsonify({"error": "Descrição é obrigatória"}), 400
        
        # ✅ CORREÇÃO: Usar a data EXATA que veio do frontend
        # Não converter para datetime, usar como string mesmo
        if not date_str:
            date_str = datetime.now().strftime('%Y-%m-%d')
        
        # Validar formato da data (YYYY-MM-DD)
        try:
            datetime.strptime(date_str, '%Y-%m-%d')
        except ValueError:
            return jsonify({"error": "Formato de data inválido. Use YYYY-MM-DD"}), 400
        
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # ✅ CORREÇÃO: Inserir a data EXATA como string
        cursor.execute('''
            INSERT INTO transactions (user_id, amount, description, category, type, date)
            VALUES (?, ?, ?, ?, ?, ?)
        ''', (user_id, amount, description, category, transaction_type, date_str))
        
        transaction_id = cursor.lastrowid
        
        # Buscar transação criada
        cursor.execute('SELECT * FROM transactions WHERE id = ?', (transaction_id,))
        new_transaction = cursor.fetchone()
        
        conn.commit()
        conn.close()
        
        return jsonify({
            "message": "Transação adicionada com sucesso",
            "transaction": {
                "id": new_transaction['id'],
                "user_id": new_transaction['user_id'],
                "amount": new_transaction['amount'],
                "description": new_transaction['description'],
                "category": new_transaction['category'],
                "type": new_transaction['type'],
                "date": new_transaction['date']  # ✅ Já vem correto do banco
            }
        }), 201
        
    except Exception as e:
        print(f"Erro ao adicionar transação: {e}")
        return jsonify({"error": "Erro ao adicionar transação"}), 500
        
    except Exception as e:
        print(f"Erro ao adicionar transação: {e}")
        return jsonify({"error": "Erro ao adicionar transação"}), 500

@app.route('/api/transactions/<int:transaction_id>', methods=['DELETE'])
def delete_transaction(transaction_id):
    try:
        user_id = request.args.get('user_id')
        if not user_id:
            return jsonify({"error": "User ID é obrigatório"}), 400
        
        conn = get_db_connection()
        cursor = conn.cursor()
        
        cursor.execute('''
            DELETE FROM transactions 
            WHERE id = ? AND user_id = ?
        ''', (transaction_id, user_id))
        
        if cursor.rowcount == 0:
            conn.close()
            return jsonify({"error": "Transação não encontrada"}), 404
        
        conn.commit()
        conn.close()
        
        return jsonify({"message": "Transação deletada com sucesso"}), 200
        
    except Exception as e:
        print(f"Erro ao deletar transação: {e}")
        return jsonify({"error": "Erro ao deletar transação"}), 500

# ROTAS DE CATEGORIAS
@app.route('/api/categories', methods=['GET'])
def get_categories():
    try:
        user_id = request.args.get('user_id')
        
        if user_id:
            # Buscar categorias do usuário
            conn = get_db_connection()
            cursor = conn.cursor()
            
            cursor.execute('''
                SELECT id, user_id, name, type, color
                FROM categories 
                WHERE user_id = ?
            ''', (user_id,))
            
            categories = []
            for row in cursor.fetchall():
                categories.append({
                    "id": row['id'],
                    "user_id": row['user_id'],
                    "name": row['name'],
                    "type": row['type'],
                    "color": row['color']
                })
            
            conn.close()
        else:
            # Retornar categorias padrão
            categories = [{"id": i, **cat} for i, cat in enumerate(default_categories)]
        
        return jsonify({"categories": categories}), 200
        
    except Exception as e:
        print(f"Erro ao buscar categorias: {e}")
        return jsonify({"categories": default_categories}), 200

@app.route('/api/categories', methods=['POST'])
def add_category():
    try:
        data = request.get_json()
        user_id = data.get('user_id')
        name = data.get('name', '').strip()
        category_type = data.get('type')
        color = data.get('color', '#6c757d')
        
        if not user_id:
            return jsonify({"error": "User ID é obrigatório"}), 400
        
        if not name:
            return jsonify({"error": "Nome da categoria é obrigatório"}), 400
        
        if not category_type or category_type not in ['income', 'expense']:
            return jsonify({"error": "Tipo de categoria inválido"}), 400
        
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # Verificar se categoria já existe
        cursor.execute('''
            SELECT id FROM categories 
            WHERE user_id = ? AND name = ? AND type = ?
        ''', (user_id, name, category_type))
        
        if cursor.fetchone():
            conn.close()
            return jsonify({"error": "Categoria já existe"}), 400
        
        # Inserir nova categoria
        cursor.execute('''
            INSERT INTO categories (user_id, name, type, color)
            VALUES (?, ?, ?, ?)
        ''', (user_id, name, category_type, color))
        
        category_id = cursor.lastrowid
        
        conn.commit()
        conn.close()
        
        return jsonify({
            "message": "Categoria adicionada com sucesso",
            "category": {
                "id": category_id,
                "user_id": user_id,
                "name": name,
                "type": category_type,
                "color": color
            }
        }), 201
        
    except Exception as e:
        print(f"Erro ao adicionar categoria: {e}")
        return jsonify({"error": "Erro ao adicionar categoria"}), 500

# ROTAS DO DASHBOARD
@app.route('/api/dashboard/summary', methods=['GET'])
def get_dashboard_summary():
    try:
        user_id = request.args.get('user_id')
        if not user_id:
            return jsonify({
                "total_income": 0,
                "total_expenses": 0,
                "balance": 0,
                "recent_transactions": []
            }), 200
        
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # Calcular totais do mês atual
        first_day_of_month = datetime.now().replace(day=1).strftime('%Y-%m-%d')
        
        # Receitas do mês
        cursor.execute('''
            SELECT COALESCE(SUM(amount), 0) as total
            FROM transactions 
            WHERE user_id = ? AND type = 'income' AND date >= ?
        ''', (user_id, first_day_of_month))
        
        total_income = cursor.fetchone()['total'] or 0
        
        # Despesas do mês
        cursor.execute('''
            SELECT COALESCE(SUM(amount), 0) as total
            FROM transactions 
            WHERE user_id = ? AND type = 'expense' AND date >= ?
        ''', (user_id, first_day_of_month))
        
        total_expenses = cursor.fetchone()['total'] or 0
        balance = total_income - total_expenses
        
        # Últimas 5 transações
        cursor.execute('''
            SELECT id, user_id, amount, description, category, type, date
            FROM transactions 
            WHERE user_id = ?
            ORDER BY date DESC
            LIMIT 5
        ''', (user_id,))
        
        recent_transactions = []
        for row in cursor.fetchall():
            recent_transactions.append({
                "id": row['id'],
                "user_id": row['user_id'],
                "amount": row['amount'],
                "description": row['description'],
                "category": row['category'],
                "type": row['type'],
                "date": row['date']
            })
        
        conn.close()
        
        return jsonify({
            "total_income": round(total_income, 2),
            "total_expenses": round(total_expenses, 2),
            "balance": round(balance, 2),
            "recent_transactions": recent_transactions
        }), 200
        
    except Exception as e:
        print(f"Erro no dashboard: {e}")
        return jsonify({
            "total_income": 0,
            "total_expenses": 0,
            "balance": 0,
            "recent_transactions": []
        }), 200

@app.route('/api/dashboard/chart')
def get_chart():
    try:
        user_id = request.args.get('user_id')
        chart_type = request.args.get('type', 'monthly')
        
        if not user_id:
            return jsonify({"error": "User ID é obrigatório"}), 400
        
        conn = get_db_connection()
        cursor = conn.cursor()
        
        plt.figure(figsize=(10, 6))
        
        if chart_type == 'monthly':
            # Últimos 6 meses
            six_months_ago = (datetime.now() - timedelta(days=180)).strftime('%Y-%m-%d')
            
            cursor.execute('''
                SELECT 
                    strftime('%Y-%m', date) as month,
                    SUM(CASE WHEN type = 'income' THEN amount ELSE 0 END) as income,
                    SUM(CASE WHEN type = 'expense' THEN amount ELSE 0 END) as expenses
                FROM transactions 
                WHERE user_id = ? AND date >= ?
                GROUP BY strftime('%Y-%m', date)
                ORDER BY month DESC
                LIMIT 6
            ''', (user_id, six_months_ago))
            
            result = cursor.fetchall()
            
            if result:
                months = [row['month'] for row in result]
                income = [row['income'] for row in result]
                expenses = [row['expenses'] for row in result]
                
                # Reverter para ordem cronológica
                months.reverse()
                income.reverse()
                expenses.reverse()
                
                x = range(len(months))
                width = 0.35
                
                plt.bar([i - width/2 for i in x], income, width, label='Receitas', color='#28a745')
                plt.bar([i + width/2 for i in x], expenses, width, label='Despesas', color='#dc3545')
                plt.xlabel('Mês')
                plt.ylabel('Valor (R$)')
                plt.title('Receitas vs Despesas - Últimos 6 Meses')
                plt.xticks(x, months, rotation=45)
                plt.legend()
                plt.tight_layout()
            else:
                plt.text(0.5, 0.5, 'Sem dados para exibir', 
                        ha='center', va='center', transform=plt.gca().transAxes)
            
        elif chart_type == 'categories':
            # Gastos por categoria (últimos 30 dias)
            thirty_days_ago = (datetime.now() - timedelta(days=30)).strftime('%Y-%m-%d')
            
            cursor.execute('''
                SELECT category, SUM(amount) as total
                FROM transactions 
                WHERE user_id = ? AND type = 'expense' AND date >= ?
                GROUP BY category
            ''', (user_id, thirty_days_ago))
            
            result = cursor.fetchall()
            
            if result:
                categories = [row['category'] for row in result]
                amounts = [row['total'] for row in result]
                
                plt.pie(amounts, labels=categories, autopct='%1.1f%%', startangle=90)
                plt.title('Distribuição de Gastos por Categoria (Últimos 30 dias)')
            else:
                plt.text(0.5, 0.5, 'Sem dados de gastos', 
                        ha='center', va='center', transform=plt.gca().transAxes)
        
        conn.close()
        
        # Converter para base64
        buffer = io.BytesIO()
        plt.savefig(buffer, format='png', dpi=100, bbox_inches='tight')
        buffer.seek(0)
        image_base64 = base64.b64encode(buffer.getvalue()).decode()
        plt.close()
        
        return jsonify({
            "chart": f"data:image/png;base64,{image_base64}"
        }), 200
        
    except Exception as e:
        print(f"Erro ao gerar gráfico: {e}")
        return jsonify({"error": "Erro ao gerar gráfico"}), 500

# ROTAS DE PERFIL
@app.route('/api/user/profile', methods=['GET'])
def get_profile():
    try:
        user_id = request.args.get('user_id')
        if not user_id:
            return jsonify({"error": "User ID é obrigatório"}), 400
        
        conn = get_db_connection()
        cursor = conn.cursor()
        
        cursor.execute('''
            SELECT id, email, name, monthly_income_goal, monthly_expense_limit, currency
            FROM users WHERE id = ?
        ''', (user_id,))
        
        user = cursor.fetchone()
        conn.close()
        
        if not user:
            return jsonify({"error": "Usuário não encontrado"}), 404
        
        return jsonify({
            "user": {
                "id": user['id'],
                "name": user['name'],
                "email": user['email'],
                "profile": {
                    "monthly_income_goal": user['monthly_income_goal'],
                    "monthly_expense_limit": user['monthly_expense_limit'],
                    "currency": user['currency']
                }
            }
        }), 200
        
    except Exception as e:
        print(f"Erro ao buscar perfil: {e}")
        return jsonify({"error": "Erro ao buscar perfil"}), 500

@app.route('/api/user/profile', methods=['PUT'])
def update_profile():
    try:
        data = request.get_json()
        user_id = data.get('user_id')
        name = data.get('name', '').strip()
        email = data.get('email', '').strip().lower()
        profile = data.get('profile', {})
        
        if not user_id:
            return jsonify({"error": "User ID é obrigatório"}), 400
        
        if not name:
            return jsonify({"error": "Nome é obrigatório"}), 400
        
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # Verificar se email já existe (para outro usuário)
        cursor.execute('SELECT id FROM users WHERE email = ? AND id != ?', (email, user_id))
        if cursor.fetchone():
            conn.close()
            return jsonify({"error": "Email já está em uso"}), 400
        
        # Atualizar perfil
        cursor.execute('''
            UPDATE users 
            SET name = ?, email = ?, monthly_income_goal = ?, monthly_expense_limit = ?
            WHERE id = ?
        ''', (name, email, profile.get('monthly_income_goal', 5000), profile.get('monthly_expense_limit', 2500), user_id))
        
        if cursor.rowcount == 0:
            conn.close()
            return jsonify({"error": "Nenhuma alteração realizada"}), 400
        
        conn.commit()
        conn.close()
        
        return jsonify({
            "message": "Perfil atualizado com sucesso",
            "user": {
                "id": user_id,
                "name": name,
                "email": email,
                "profile": profile
            }
        }), 200
        
    except Exception as e:
        print(f"Erro ao atualizar perfil: {e}")
        return jsonify({"error": "Erro ao atualizar perfil"}), 500

@app.route('/api/user/change-password', methods=['PUT'])
def change_password():
    try:
        data = request.get_json()
        user_id = data.get('user_id')
        current_password = data.get('current_password')
        new_password = data.get('new_password')
        
        if not user_id:
            return jsonify({"error": "User ID é obrigatório"}), 400
        
        conn = get_db_connection()
        cursor = conn.cursor()
        
        cursor.execute('SELECT password FROM users WHERE id = ?', (user_id,))
        user = cursor.fetchone()
        
        if not user:
            conn.close()
            return jsonify({"error": "Usuário não encontrado"}), 404
        
        if not check_password(current_password, user['password']):
            conn.close()
            return jsonify({"error": "Senha atual incorreta"}), 400
        
        if len(new_password) < 6:
            conn.close()
            return jsonify({"error": "A nova senha deve ter pelo menos 6 caracteres"}), 400
        
        cursor.execute('UPDATE users SET password = ? WHERE id = ?', (hash_password(new_password), user_id))
        conn.commit()
        conn.close()
        
        return jsonify({"message": "Senha alterada com sucesso"}), 200
        
    except Exception as e:
        print(f"Erro ao alterar senha: {e}")
        return jsonify({"error": "Erro ao alterar senha"}), 500

# Rota health check
@app.route('/api/health', methods=['GET'])
def health_check():
    try:
        # Testar conexão com SQLite
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute('SELECT 1')
        conn.close()
        
        return jsonify({
            "status": "OK", 
            "message": "Backend e SQLite funcionando",
            "timestamp": datetime.now().isoformat()
        }), 200
    except Exception as e:
        return jsonify({
            "status": "ERROR",
            "message": f"Erro no SQLite: {e}"
        }), 500

@app.route('/')
def index():
    return jsonify({
        "message": "CtrlCash API está funcionando!",
        "version": "1.0.0",
        "timestamp": datetime.now().isoformat()
    })

# Inicializar o banco de dados quando o app iniciar
init_db()
print("✅ Banco de dados SQLite inicializado!")

if __name__ == '__main__':
    print("🚀 Iniciando CtrlCash API com SQLite...")
    print(f"💾 Database: {DB_PATH}")
    app.run(debug=False, host='0.0.0.0', port=5000)