import os, sqlite3, pandas as pd, plotly.express as px, plotly.graph_objects as go, gradio as gr
from datetime import date

# CONFIG
APP_DIR = "/content/drive/MyDrive/controle_financeiro"
os.makedirs(APP_DIR, exist_ok=True)
DB_PATH, CSV_PATH = os.path.join(APP_DIR, "financas.db"), os.path.join(APP_DIR, "lancamentos.csv")
CATEGORIAS = ["Alimentação", "Educação", "Investimentos", "Lazer", "Moradia", "Outros", "Receita", "Saúde", "Transporte", "Roupas"]

# DB ENGINE
def db_query(sql, params=(), commit=False):
    with sqlite3.connect(DB_PATH, check_same_thread=False) as conn:
        if commit: return conn.execute(sql, params).rowcount
        return pd.read_sql_query(sql, conn, params=params)

def init_db():
    db_query("CREATE TABLE IF NOT EXISTS transacoes (id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT, tipo TEXT, categoria TEXT, descricao TEXT, valor REAL)", commit=True)
    db_query("CREATE TABLE IF NOT EXISTS orcamentos (mes TEXT PRIMARY KEY, valor REAL)", commit=True)
init_db()

# HELPERS
moeda = lambda v: f"R$ {float(v or 0):,.2f}".replace(",", "X").replace(".", ",").replace("X", ".")
def refresh_data(d1, d2, t, c):
    df = db_query("SELECT id AS ID, data AS Data, tipo AS Tipo, categoria AS Categoria, descricao AS Descrição, valor AS Valor FROM transacoes ORDER BY data DESC")
    if not df.empty:
        df['Data'] = pd.to_datetime(df['Data'])
        if d1: df = df[df['Data'] >= pd.to_datetime(d1)]
        if d2: df = df[df['Data'] <= pd.to_datetime(d2)]
        if t != "Todos": df = df[df['Tipo'] == t]
        if c != "Todas": df = df[df['Categoria'] == c]
    
    rec, des = df[df['Tipo']=='Receita']['Valor'].sum(), df[df['Tipo']=='Despesa']['Valor'].sum()
    html = f'<div class="cards"><div class="card receita"><span>Receitas</span><strong>{moeda(rec)}</strong></div><div class="card despesa"><span>Despesas</span><strong>{moeda(des)}</strong></div><div class="card saldo"><span>Saldo</span><strong>{moeda(rec-des)}</strong></div><div class="card registros"><span>Itens</span><strong>{len(df)}</strong></div></div>'
    
    fig1 = px.pie(df[df['Tipo']=='Despesa'], names='Categoria', values='Valor', hole=.4, title="Despesas") if not df[df['Tipo']=='Despesa'].empty else go.Figure()
    fig2 = px.bar(df.groupby(['Data', 'Tipo'])['Valor'].sum().reset_index(), x='Data', y='Valor', color='Tipo', barmode='group', title="Evolução") if not df.empty else go.Figure()
    df['Data'] = df['Data'].dt.strftime('%d/%m/%Y') if not df.empty else df['Data']
    return html, df, fig1, fig2

# CRUD ACTIONS
def manage_trans(id_t, dt, tp, cat, desc, val, mode="save"):
    try:
        if mode == "save": db_query("INSERT INTO transacoes (data, tipo, categoria, descricao, valor) VALUES (?,?,?,?,?)", (dt, tp, cat, desc, val), True)
        elif mode == "edit": db_query("UPDATE transacoes SET data=?, tipo=?, categoria=?, descricao=?, valor=? WHERE id=?", (dt, tp, cat, desc, val, id_t), True)
        elif mode == "del": db_query("DELETE FROM transacoes WHERE id=?", (id_t,), True)
        elif mode == "reset": db_query("DELETE FROM transacoes", commit=True)
        return "✅ Sucesso!", *refresh_data(None, None, "Todos", "Todas")
    except Exception as e: return f"❌ Erro: {e}", *refresh_data(None, None, "Todos", "Todas")

def get_trans(id_t):
    res = db_query("SELECT data, tipo, categoria, descricao, valor FROM transacoes WHERE id=?", (id_t,))
    return ("✅ Carregado", *res.iloc[0]) if not res.empty else ("❌ Não encontrado", "", "Despesa", "Outros", "", 0)

# INTERFACE
CSS = ".cards{display:grid;grid-template-columns:repeat(4,1fr);gap:10px}.card{padding:15px;border-radius:10px;color:white}.receita{background:#22c55e}.despesa{background:#ef4444}.saldo{background:#3b82f6}.registros{background:#8b5cf6}"
with gr.Blocks(title="Finanças") as app:
    gr.Markdown("# 💰 Controle Financeiro")
    with gr.Tab("Dashboard"):
        with gr.Row():
            f = [gr.Textbox(label="Início", value=date.today().replace(day=1)), gr.Textbox(label="Fim", value=date.today()), gr.Dropdown(["Todos", "Receita", "Despesa"], value="Todos"), gr.Dropdown(["Todas"]+CATEGORIAS, value="Todas")]
        with gr.Row():
            btn_f = gr.Button("Filtrar", variant="primary")
            btn_reset = gr.Button("🗑️ Resetar Tudo", variant="stop")
        out = [gr.HTML(), gr.Dataframe(interactive=False), gr.Plot(), gr.Plot()]
        btn_f.click(refresh_data, f, out)
        btn_reset.click(lambda: manage_trans(None,None,None,None,None,None,mode="reset"), None, [gr.Markdown(visible=False)]+out)
        gr.Button("Exportar CSV").click(lambda: db_query("SELECT * FROM transacoes").to_csv(CSV_PATH, index=False) or CSV_PATH, outputs=gr.File())

    with gr.Tab("Lançamentos"):
        msg = gr.Markdown()
        with gr.Row():
            inp = [gr.Textbox(label="Data", value=date.today()), gr.Radio(["Receita", "Despesa"], value="Despesa"), gr.Dropdown(CATEGORIAS, value="Outros"), gr.Textbox(label="Desc"), gr.Number(label="Valor")]
        gr.Button("Salvar").click(lambda *a: manage_trans(None, *a, mode="save"), inp, [msg]+out)
        
    with gr.Tab("Editar/Excluir"):
        id_e = gr.Number(label="ID")
        gr.Button("Buscar").click(get_trans, id_e, [msg]+inp)
        with gr.Row():
            gr.Button("Atualizar").click(lambda i, *a: manage_trans(i, *a, mode="edit"), [id_e]+inp, [msg]+out)
            gr.Button("Excluir", variant="stop").click(lambda i, *a: manage_trans(i, *a, mode="del"), [id_e]+inp, [msg]+out)

    app.load(refresh_data, f, out)

app.launch(share=True, theme=gr.themes.Soft(), css=CSS)