from flask import Flask, render_template, request, jsonify, send_file, send_from_directory
import sqlite3, os, sys, csv, io, calendar, json, zipfile, tempfile, shutil
from datetime import date, datetime, timedelta
from werkzeug.utils import secure_filename
import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment

# ── Path detection (dev vs frozen PyInstaller exe) ────────────────────────────
FROZEN  = getattr(sys, 'frozen', False)
RES_DIR = sys._MEIPASS if FROZEN else os.path.dirname(os.path.abspath(__file__))
APP_DIR = os.path.dirname(sys.executable) if FROZEN else os.path.dirname(os.path.abspath(__file__))

DB_PATH  = os.path.join(APP_DIR, 'contabilita.db')
RECEIPTS = os.path.join(APP_DIR, 'allegati')
os.makedirs(RECEIPTS, exist_ok=True)

app = Flask(__name__,
    template_folder=os.path.join(RES_DIR, 'templates'),
    static_folder=os.path.join(RES_DIR, 'static'))

ALLOWED_EXT = {'png', 'jpg', 'jpeg', 'gif', 'pdf', 'webp'}


def get_db():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    conn.execute('PRAGMA foreign_keys = ON')
    return conn


def init_db():
    conn = get_db(); c = conn.cursor()

    c.execute('''CREATE TABLE IF NOT EXISTS categories (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL, type TEXT NOT NULL,
        color TEXT DEFAULT '#6366f1', icon TEXT DEFAULT '💰', budget REAL DEFAULT 0
    )''')

    c.execute('''CREATE TABLE IF NOT EXISTS transactions (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        date TEXT NOT NULL, amount REAL NOT NULL, type TEXT NOT NULL,
        category_id INTEGER, description TEXT NOT NULL, notes TEXT,
        receipt_path TEXT, recurring_id INTEGER,
        FOREIGN KEY (category_id) REFERENCES categories(id)
    )''')
    for col in ('ALTER TABLE transactions ADD COLUMN receipt_path TEXT',
                'ALTER TABLE transactions ADD COLUMN recurring_id INTEGER'):
        try: c.execute(col)
        except: pass

    c.execute('''CREATE TABLE IF NOT EXISTS recurring_transactions (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        description TEXT NOT NULL, amount REAL NOT NULL, type TEXT NOT NULL,
        category_id INTEGER, frequency TEXT NOT NULL,
        day_of_month INTEGER DEFAULT 1, next_date TEXT, active INTEGER DEFAULT 1, notes TEXT,
        FOREIGN KEY (category_id) REFERENCES categories(id)
    )''')

    c.execute('''CREATE TABLE IF NOT EXISTS goals (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL, target_amount REAL NOT NULL, current_amount REAL DEFAULT 0,
        deadline TEXT, color TEXT DEFAULT '#4f46e5', icon TEXT DEFAULT '🎯', notes TEXT
    )''')

    c.execute('''CREATE TABLE IF NOT EXISTS tags (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL UNIQUE, color TEXT DEFAULT '#6366f1'
    )''')

    c.execute('''CREATE TABLE IF NOT EXISTS transaction_tags (
        transaction_id INTEGER NOT NULL, tag_id INTEGER NOT NULL,
        PRIMARY KEY (transaction_id, tag_id),
        FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE,
        FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
    )''')

    c.execute('SELECT COUNT(*) FROM categories')
    if c.fetchone()[0] == 0:
        for row in [
            ('Stipendio','entrata','#10b981','💼',0), ('Freelance','entrata','#34d399','💻',0),
            ('Bonus','entrata','#a3e635','🎁',0), ('Affitto/Mutuo','uscita','#ef4444','🏠',0),
            ('Spesa alimentare','uscita','#f97316','🛒',0), ('Trasporti','uscita','#3b82f6','🚗',0),
            ('Bollette','uscita','#8b5cf6','💡',0), ('Svago','uscita','#ec4899','🎭',0),
            ('Salute','uscita','#14b8a6','❤️',0), ('Abbigliamento','uscita','#f59e0b','👔',0),
            ('Istruzione','uscita','#6366f1','📚',0), ('Risparmio','uscita','#10b981','🏦',0),
            ('Ristorante','uscita','#fb923c','🍽️',0), ('Altro','uscita','#64748b','📦',0),
        ]:
            c.execute('INSERT INTO categories (name,type,color,icon,budget) VALUES (?,?,?,?,?)', row)

    conn.commit(); conn.close()


# ── Helpers ──────────────────────────────────────────────────────────────────

def next_recur_date(freq, from_str, dom=1):
    dt = datetime.strptime(from_str, '%Y-%m-%d')
    if freq == 'weekly':    return (dt + timedelta(weeks=1)).strftime('%Y-%m-%d')
    if freq == 'biweekly':  return (dt + timedelta(weeks=2)).strftime('%Y-%m-%d')
    if freq == 'monthly':
        m, y = dt.month + 1, dt.year
        if m > 12: m, y = 1, y + 1
        last = calendar.monthrange(y, m)[1]
        return f'{y}-{m:02d}-{min(dom, last):02d}'
    if freq == 'yearly':
        try:    return f'{dt.year+1}-{dt.month:02d}-{dt.day:02d}'
        except: return f'{dt.year+1}-{dt.month:02d}-28'
    return from_str


def get_tx_tags(conn, tx_id):
    c = conn.cursor()
    c.execute('''SELECT t.id,t.name,t.color FROM tags t
                 JOIN transaction_tags tt ON tt.tag_id=t.id WHERE tt.transaction_id=?''', (tx_id,))
    return [dict(r) for r in c.fetchall()]


def set_tx_tags(conn, tx_id, tag_names):
    c = conn.cursor()
    c.execute('DELETE FROM transaction_tags WHERE transaction_id=?', (tx_id,))
    for name in tag_names:
        name = name.strip()
        if not name: continue
        c.execute('INSERT OR IGNORE INTO tags (name) VALUES (?)', (name,))
        c.execute('SELECT id FROM tags WHERE name=?', (name,))
        tag_id = c.fetchone()['id']
        c.execute('INSERT OR IGNORE INTO transaction_tags VALUES (?,?)', (tx_id, tag_id))


def pct_change(now, prev):
    if prev == 0: return None
    return round(((now - prev) / prev) * 100, 1)


# ── Pages ─────────────────────────────────────────────────────────────────────

@app.route('/')
def dashboard():        return render_template('dashboard.html')
@app.route('/impostazioni')
def impostazioni():     return render_template('impostazioni.html')
@app.route('/transactions')
def transactions(): return render_template('transactions.html')
@app.route('/categories')
def categories():   return render_template('categories.html')
@app.route('/reports')
def reports():      return render_template('reports.html')
@app.route('/recurring')
def recurring():    return render_template('recurring.html')
@app.route('/budget')
def budget():       return render_template('budget.html')
@app.route('/goals')
def goals():        return render_template('goals.html')
@app.route('/calendar')
def cal():          return render_template('calendar.html')


# ── API: Dashboard ────────────────────────────────────────────────────────────

@app.route('/api/dashboard')
def api_dashboard():
    year  = request.args.get('year',  date.today().year,  type=int)
    month = request.args.get('month', date.today().month, type=int)
    conn  = get_db(); c = conn.cursor()
    ms    = f'{year}-{month:02d}'

    c.execute('''SELECT
        COALESCE(SUM(CASE WHEN type='entrata' THEN amount ELSE 0 END),0) entrate,
        COALESCE(SUM(CASE WHEN type='uscita'  THEN amount ELSE 0 END),0) uscite
        FROM transactions WHERE date LIKE ?''', (f'{ms}%',))
    row = c.fetchone(); entrate, uscite = row['entrate'], row['uscite']

    pm, py = month - 1, year
    if pm <= 0: pm, py = 12, year - 1
    c.execute('''SELECT
        COALESCE(SUM(CASE WHEN type='entrata' THEN amount ELSE 0 END),0) entrate,
        COALESCE(SUM(CASE WHEN type='uscita'  THEN amount ELSE 0 END),0) uscite
        FROM transactions WHERE date LIKE ?''', (f'{py}-{pm:02d}%',))
    prev = c.fetchone()

    today = date.today()
    projection = None
    if year == today.year and month == today.month and uscite > 0:
        days_total = calendar.monthrange(year, month)[1]
        elapsed    = today.day
        daily_rate = uscite / elapsed
        projection = {
            'projected': round(daily_rate * days_total, 2),
            'elapsed': elapsed, 'total': days_total,
            'pct': round(elapsed / days_total * 100)
        }

    c.execute('''SELECT cat.name, cat.color, cat.icon, SUM(t.amount) total
        FROM transactions t JOIN categories cat ON t.category_id=cat.id
        WHERE t.type='uscita' AND t.date LIKE ?
        GROUP BY cat.id ORDER BY total DESC''', (f'{ms}%',))
    by_category = [dict(r) for r in c.fetchall()]

    trend = []
    for i in range(5, -1, -1):
        m, y = month - i, year
        while m <= 0: m += 12; y -= 1
        c.execute('''SELECT
            COALESCE(SUM(CASE WHEN type='entrata' THEN amount ELSE 0 END),0) e,
            COALESCE(SUM(CASE WHEN type='uscita'  THEN amount ELSE 0 END),0) u
            FROM transactions WHERE date LIKE ?''', (f'{y}-{m:02d}%',))
        r = c.fetchone()
        trend.append({'label': f'{calendar.month_abbr[m]} {y}', 'entrate': r['e'], 'uscite': r['u']})

    c.execute('''SELECT t.description, t.amount, t.date, cat.name cat_name, cat.icon, cat.color
        FROM transactions t LEFT JOIN categories cat ON t.category_id=cat.id
        WHERE t.type='uscita' AND t.date LIKE ?
        ORDER BY t.amount DESC LIMIT 5''', (f'{ms}%',))
    top_spese = [dict(r) for r in c.fetchall()]

    c.execute('''SELECT t.*, cat.name cat_name, cat.color, cat.icon
        FROM transactions t LEFT JOIN categories cat ON t.category_id=cat.id
        ORDER BY t.date DESC, t.id DESC LIMIT 10''')
    recent = [dict(r) for r in c.fetchall()]

    c.execute('''SELECT r.*, cat.name cat_name FROM recurring_transactions r
        LEFT JOIN categories cat ON r.category_id=cat.id
        WHERE r.active=1 AND r.next_date <= ?
        ORDER BY r.next_date''', (today.strftime('%Y-%m-%d'),))
    pending = [dict(r) for r in c.fetchall()]

    conn.close()
    return jsonify({
        'entrate': entrate, 'uscite': uscite, 'saldo': entrate - uscite,
        'prev_entrate': prev['entrate'], 'prev_uscite': prev['uscite'],
        'chg_entrate': pct_change(entrate, prev['entrate']),
        'chg_uscite':  pct_change(uscite,  prev['uscite']),
        'projection': projection,
        'by_category': by_category, 'monthly_trend': trend,
        'top_spese': top_spese, 'recent': recent, 'pending_recurring': pending
    })


# ── API: Transactions ─────────────────────────────────────────────────────────

@app.route('/api/transactions', methods=['GET'])
def api_get_transactions():
    year    = request.args.get('year',       type=int)
    month   = request.args.get('month',      type=int)
    type_   = request.args.get('type',       '')
    cat_id  = request.args.get('category_id',type=int)
    search  = request.args.get('search',     '')
    tag     = request.args.get('tag',        '')
    amin    = request.args.get('amount_min', type=float)
    amax    = request.args.get('amount_max', type=float)
    conn = get_db(); c = conn.cursor()
    q = '''SELECT t.*, cat.name cat_name, cat.color, cat.icon
           FROM transactions t LEFT JOIN categories cat ON t.category_id=cat.id WHERE 1=1'''
    p = []
    if year and month: q += ' AND t.date LIKE ?'; p.append(f'{year}-{month:02d}%')
    elif year:         q += ' AND t.date LIKE ?'; p.append(f'{year}%')
    if type_:          q += ' AND t.type=?';          p.append(type_)
    if cat_id:         q += ' AND t.category_id=?';   p.append(cat_id)
    if search:         q += ' AND t.description LIKE ?'; p.append(f'%{search}%')
    if amin is not None: q += ' AND t.amount>=?'; p.append(amin)
    if amax is not None: q += ' AND t.amount<=?'; p.append(amax)
    if tag:
        q += ''' AND t.id IN (SELECT tt.transaction_id FROM transaction_tags tt
                              JOIN tags tg ON tg.id=tt.tag_id WHERE tg.name=?)'''
        p.append(tag)
    q += ' ORDER BY t.date DESC, t.id DESC'
    c.execute(q, p)
    rows = []
    for r in c.fetchall():
        tx = dict(r); tx['tags'] = get_tx_tags(conn, tx['id']); rows.append(tx)
    conn.close()
    return jsonify(rows)


@app.route('/api/transactions', methods=['POST'])
def api_add_transaction():
    d = request.json; tags = d.pop('tags', [])
    conn = get_db(); c = conn.cursor()
    c.execute('INSERT INTO transactions (date,amount,type,category_id,description,notes) VALUES (?,?,?,?,?,?)',
              (d['date'], float(d['amount']), d['type'], d.get('category_id'), d['description'], d.get('notes','')))
    conn.commit(); new_id = c.lastrowid
    if tags: set_tx_tags(conn, new_id, tags); conn.commit()
    conn.close()
    return jsonify({'id': new_id, 'success': True})


@app.route('/api/transactions/<int:tid>', methods=['PUT'])
def api_update_transaction(tid):
    d = request.json; tags = d.pop('tags', [])
    conn = get_db(); c = conn.cursor()
    c.execute('UPDATE transactions SET date=?,amount=?,type=?,category_id=?,description=?,notes=? WHERE id=?',
              (d['date'], float(d['amount']), d['type'], d.get('category_id'), d['description'], d.get('notes',''), tid))
    set_tx_tags(conn, tid, tags); conn.commit(); conn.close()
    return jsonify({'success': True})


@app.route('/api/transactions/<int:tid>', methods=['DELETE'])
def api_delete_transaction(tid):
    conn = get_db(); c = conn.cursor()
    c.execute('SELECT receipt_path FROM transactions WHERE id=?', (tid,))
    row = c.fetchone()
    if row and row['receipt_path']:
        fp = os.path.join(BASE_DIR, row['receipt_path'].lstrip('/').replace('/', os.sep))
        if os.path.exists(fp): os.remove(fp)
    c.execute('DELETE FROM transactions WHERE id=?', (tid,))
    conn.commit(); conn.close()
    return jsonify({'success': True})


@app.route('/api/transactions/<int:tid>/receipt', methods=['POST'])
def api_upload_receipt(tid):
    if 'file' not in request.files: return jsonify({'error': 'No file'}), 400
    f = request.files['file']
    ext = (f.filename or '').rsplit('.', 1)[-1].lower()
    if ext not in ALLOWED_EXT: return jsonify({'error': 'Tipo non supportato'}), 400
    fname = f'{tid}_{int(datetime.now().timestamp())}.{ext}'
    f.save(os.path.join(RECEIPTS, fname))
    rel = f'/allegati/{fname}'
    conn = get_db(); c = conn.cursor()
    c.execute('SELECT receipt_path FROM transactions WHERE id=?', (tid,))
    old = c.fetchone()
    if old and old['receipt_path']:
        fp = os.path.join(RECEIPTS, os.path.basename(old['receipt_path']))
        if os.path.exists(fp): os.remove(fp)
    c.execute('UPDATE transactions SET receipt_path=? WHERE id=?', (rel, tid))
    conn.commit(); conn.close()
    return jsonify({'success': True, 'path': rel})


@app.route('/allegati/<path:filename>')
def serve_allegati(filename):
    return send_from_directory(RECEIPTS, filename)


@app.route('/api/transactions/<int:tid>/receipt', methods=['DELETE'])
def api_delete_receipt(tid):
    conn = get_db(); c = conn.cursor()
    c.execute('SELECT receipt_path FROM transactions WHERE id=?', (tid,))
    row = c.fetchone()
    if row and row['receipt_path']:
        fp = os.path.join(RECEIPTS, os.path.basename(row['receipt_path']))
        if os.path.exists(fp): os.remove(fp)
    c.execute('UPDATE transactions SET receipt_path=NULL WHERE id=?', (tid,))
    conn.commit(); conn.close()
    return jsonify({'success': True})


# ── API: Categories ───────────────────────────────────────────────────────────

@app.route('/api/categories', methods=['GET'])
def api_get_categories():
    conn = get_db(); c = conn.cursor()
    c.execute('SELECT * FROM categories ORDER BY type, name')
    rows = [dict(r) for r in c.fetchall()]; conn.close()
    return jsonify(rows)

@app.route('/api/categories', methods=['POST'])
def api_add_category():
    d = request.json; conn = get_db(); c = conn.cursor()
    c.execute('INSERT INTO categories (name,type,color,icon,budget) VALUES (?,?,?,?,?)',
              (d['name'], d['type'], d.get('color','#6366f1'), d.get('icon','💰'), float(d.get('budget',0))))
    conn.commit(); new_id = c.lastrowid; conn.close()
    return jsonify({'id': new_id, 'success': True})

@app.route('/api/categories/<int:cid>', methods=['PUT'])
def api_update_category(cid):
    d = request.json; conn = get_db(); c = conn.cursor()
    c.execute('UPDATE categories SET name=?,type=?,color=?,icon=?,budget=? WHERE id=?',
              (d['name'], d['type'], d.get('color','#6366f1'), d.get('icon','💰'), float(d.get('budget',0)), cid))
    conn.commit(); conn.close()
    return jsonify({'success': True})

@app.route('/api/categories/<int:cid>', methods=['DELETE'])
def api_delete_category(cid):
    conn = get_db(); c = conn.cursor()
    c.execute('SELECT COUNT(*) n FROM transactions WHERE category_id=?', (cid,))
    n = c.fetchone()['n']
    if n: conn.close(); return jsonify({'success': False, 'error': f'Usata in {n} transazioni'}), 400
    c.execute('DELETE FROM categories WHERE id=?', (cid,))
    conn.commit(); conn.close()
    return jsonify({'success': True})


# ── API: Recurring ────────────────────────────────────────────────────────────

@app.route('/api/recurring', methods=['GET'])
def api_get_recurring():
    conn = get_db(); c = conn.cursor()
    c.execute('''SELECT r.*, cat.name cat_name, cat.color, cat.icon
        FROM recurring_transactions r LEFT JOIN categories cat ON r.category_id=cat.id
        ORDER BY r.next_date''')
    rows = [dict(r) for r in c.fetchall()]; conn.close()
    return jsonify(rows)

@app.route('/api/recurring', methods=['POST'])
def api_add_recurring():
    d = request.json; conn = get_db(); c = conn.cursor()
    start = d.get('start_date') or date.today().strftime('%Y-%m-%d')
    c.execute('''INSERT INTO recurring_transactions
        (description,amount,type,category_id,frequency,day_of_month,next_date,notes)
        VALUES (?,?,?,?,?,?,?,?)''',
        (d['description'], float(d['amount']), d['type'], d.get('category_id'),
         d['frequency'], int(d.get('day_of_month', 1)), start, d.get('notes','')))
    conn.commit(); new_id = c.lastrowid; conn.close()
    return jsonify({'id': new_id, 'success': True})

@app.route('/api/recurring/<int:rid>', methods=['PUT'])
def api_update_recurring(rid):
    d = request.json; conn = get_db(); c = conn.cursor()
    c.execute('''UPDATE recurring_transactions SET description=?,amount=?,type=?,category_id=?,
        frequency=?,day_of_month=?,next_date=?,active=?,notes=? WHERE id=?''',
        (d['description'], float(d['amount']), d['type'], d.get('category_id'),
         d['frequency'], int(d.get('day_of_month',1)), d['next_date'],
         int(d.get('active',1)), d.get('notes',''), rid))
    conn.commit(); conn.close()
    return jsonify({'success': True})

@app.route('/api/recurring/<int:rid>', methods=['DELETE'])
def api_delete_recurring(rid):
    conn = get_db(); c = conn.cursor()
    c.execute('DELETE FROM recurring_transactions WHERE id=?', (rid,))
    conn.commit(); conn.close()
    return jsonify({'success': True})

@app.route('/api/recurring/generate', methods=['POST'])
def api_generate_recurring():
    today_str = date.today().strftime('%Y-%m-%d')
    conn = get_db(); c = conn.cursor()
    c.execute('SELECT * FROM recurring_transactions WHERE active=1 AND next_date<=?', (today_str,))
    items = [dict(r) for r in c.fetchall()]
    generated = []
    for item in items:
        c.execute('''INSERT INTO transactions (date,amount,type,category_id,description,notes,recurring_id)
            VALUES (?,?,?,?,?,?,?)''',
            (item['next_date'], item['amount'], item['type'], item['category_id'],
             item['description'], item['notes'] or '', item['id']))
        new_next = next_recur_date(item['frequency'], item['next_date'], item['day_of_month'] or 1)
        c.execute('UPDATE recurring_transactions SET next_date=? WHERE id=?', (new_next, item['id']))
        generated.append({'description': item['description'], 'amount': item['amount']})
    conn.commit(); conn.close()
    return jsonify({'generated': generated, 'count': len(generated)})


# ── API: Goals ────────────────────────────────────────────────────────────────

@app.route('/api/goals', methods=['GET'])
def api_get_goals():
    conn = get_db(); c = conn.cursor()
    c.execute('SELECT * FROM goals ORDER BY deadline')
    rows = [dict(r) for r in c.fetchall()]; conn.close()
    return jsonify(rows)

@app.route('/api/goals', methods=['POST'])
def api_add_goal():
    d = request.json; conn = get_db(); c = conn.cursor()
    c.execute('INSERT INTO goals (name,target_amount,current_amount,deadline,color,icon,notes) VALUES (?,?,?,?,?,?,?)',
              (d['name'], float(d['target_amount']), float(d.get('current_amount',0)),
               d.get('deadline'), d.get('color','#4f46e5'), d.get('icon','🎯'), d.get('notes','')))
    conn.commit(); new_id = c.lastrowid; conn.close()
    return jsonify({'id': new_id, 'success': True})

@app.route('/api/goals/<int:gid>', methods=['PUT'])
def api_update_goal(gid):
    d = request.json; conn = get_db(); c = conn.cursor()
    c.execute('UPDATE goals SET name=?,target_amount=?,current_amount=?,deadline=?,color=?,icon=?,notes=? WHERE id=?',
              (d['name'], float(d['target_amount']), float(d.get('current_amount',0)),
               d.get('deadline'), d.get('color','#4f46e5'), d.get('icon','🎯'), d.get('notes',''), gid))
    conn.commit(); conn.close()
    return jsonify({'success': True})

@app.route('/api/goals/<int:gid>/contribute', methods=['POST'])
def api_goal_contribute(gid):
    amount = float(request.json.get('amount', 0))
    conn = get_db(); c = conn.cursor()
    c.execute('UPDATE goals SET current_amount=current_amount+? WHERE id=?', (amount, gid))
    conn.commit(); conn.close()
    return jsonify({'success': True})

@app.route('/api/goals/<int:gid>', methods=['DELETE'])
def api_delete_goal(gid):
    conn = get_db(); c = conn.cursor()
    c.execute('DELETE FROM goals WHERE id=?', (gid,))
    conn.commit(); conn.close()
    return jsonify({'success': True})


# ── API: Tags ─────────────────────────────────────────────────────────────────

@app.route('/api/tags', methods=['GET'])
def api_get_tags():
    conn = get_db(); c = conn.cursor()
    c.execute('''SELECT t.*, COUNT(tt.transaction_id) usage
        FROM tags t LEFT JOIN transaction_tags tt ON tt.tag_id=t.id
        GROUP BY t.id ORDER BY usage DESC, t.name''')
    rows = [dict(r) for r in c.fetchall()]; conn.close()
    return jsonify(rows)

@app.route('/api/tags', methods=['POST'])
def api_add_tag():
    d = request.json; conn = get_db(); c = conn.cursor()
    try:
        c.execute('INSERT INTO tags (name,color) VALUES (?,?)', (d['name'], d.get('color','#6366f1')))
        conn.commit(); new_id = c.lastrowid; conn.close()
        return jsonify({'id': new_id, 'success': True})
    except sqlite3.IntegrityError:
        conn.close(); return jsonify({'error': 'Tag già esistente'}), 400

@app.route('/api/tags/<int:tid>', methods=['DELETE'])
def api_delete_tag(tid):
    conn = get_db(); c = conn.cursor()
    c.execute('DELETE FROM tags WHERE id=?', (tid,))
    conn.commit(); conn.close()
    return jsonify({'success': True})


# ── API: Calendar ─────────────────────────────────────────────────────────────

@app.route('/api/calendar')
def api_calendar():
    year  = request.args.get('year',  date.today().year,  type=int)
    month = request.args.get('month', date.today().month, type=int)
    conn  = get_db(); c = conn.cursor()
    c.execute('''SELECT t.*, cat.name cat_name, cat.color, cat.icon
        FROM transactions t LEFT JOIN categories cat ON t.category_id=cat.id
        WHERE t.date LIKE ? ORDER BY t.date, t.id''', (f'{year}-{month:02d}%',))
    days = {}
    for tx in c.fetchall():
        tx = dict(tx); day = tx['date']
        if day not in days: days[day] = {'entrate': 0, 'uscite': 0, 'transactions': []}
        if tx['type'] == 'entrata': days[day]['entrate'] += tx['amount']
        else:                       days[day]['uscite']  += tx['amount']
        days[day]['transactions'].append(tx)
    fw, dim = calendar.monthrange(year, month)
    conn.close()
    return jsonify({'days': days, 'first_weekday': fw, 'days_in_month': dim, 'year': year, 'month': month})


# ── API: Budget ───────────────────────────────────────────────────────────────

@app.route('/api/budget')
def api_budget():
    year  = request.args.get('year',  date.today().year,  type=int)
    month = request.args.get('month', date.today().month, type=int)
    conn  = get_db(); c = conn.cursor()
    c.execute('''SELECT cat.id,cat.name,cat.color,cat.icon,cat.budget,
        COALESCE(SUM(t.amount),0) spent
        FROM categories cat
        LEFT JOIN transactions t ON t.category_id=cat.id AND t.type='uscita' AND t.date LIKE ?
        WHERE cat.type='uscita'
        GROUP BY cat.id ORDER BY cat.budget DESC, spent DESC''', (f'{year}-{month:02d}%',))
    rows = [dict(r) for r in c.fetchall()]; conn.close()
    return jsonify(rows)


# ── API: Reports ──────────────────────────────────────────────────────────────

@app.route('/api/reports/monthly')
def api_reports_monthly():
    year = request.args.get('year', date.today().year, type=int)
    conn = get_db(); c = conn.cursor()
    monthly = []
    for m in range(1, 13):
        c.execute('''SELECT
            COALESCE(SUM(CASE WHEN type='entrata' THEN amount ELSE 0 END),0) entrate,
            COALESCE(SUM(CASE WHEN type='uscita'  THEN amount ELSE 0 END),0) uscite
            FROM transactions WHERE date LIKE ?''', (f'{year}-{m:02d}%',))
        r = c.fetchone()
        monthly.append({'month': m, 'label': calendar.month_name[m],
                        'entrate': r['entrate'], 'uscite': r['uscite'],
                        'saldo': r['entrate'] - r['uscite']})
    c.execute('''SELECT cat.name,cat.color,cat.icon,cat.budget,SUM(t.amount) total
        FROM transactions t JOIN categories cat ON t.category_id=cat.id
        WHERE t.type='uscita' AND t.date LIKE ?
        GROUP BY cat.id ORDER BY total DESC''', (f'{year}%',))
    by_cat = [dict(r) for r in c.fetchall()]
    conn.close()
    return jsonify({'monthly': monthly, 'by_category': by_cat})


@app.route('/api/reports/trends')
def api_reports_trends():
    conn = get_db(); c = conn.cursor()
    today = date.today()
    months = []
    for i in range(11, -1, -1):
        m, y = today.month - i, today.year
        while m <= 0: m += 12; y -= 1
        months.append((f'{y}-{m:02d}', f'{calendar.month_abbr[m]} {y}'))

    c.execute("SELECT id,name,color,icon FROM categories WHERE type='uscita' ORDER BY name")
    cats = [dict(r) for r in c.fetchall()]
    result = []
    for cat in cats:
        totals = []
        for ms, _ in months:
            c.execute('''SELECT COALESCE(SUM(amount),0) t FROM transactions
                WHERE type='uscita' AND category_id=? AND date LIKE ?''', (cat['id'], f'{ms}%'))
            totals.append(c.fetchone()['t'])
        if sum(totals) > 0:
            nz  = [x for x in totals if x > 0]
            avg = sum(nz) / len(nz) if nz else 0
            result.append({**cat, 'monthly_data': totals, 'average': avg, 'total': sum(totals)})

    cur_y, prev_y = today.year, today.year - 1
    def year_totals(y):
        c.execute('''SELECT
            COALESCE(SUM(CASE WHEN type='entrata' THEN amount ELSE 0 END),0) e,
            COALESCE(SUM(CASE WHEN type='uscita'  THEN amount ELSE 0 END),0) u
            FROM transactions WHERE date LIKE ?''', (f'{y}%',))
        r = c.fetchone(); return r['e'], r['u']
    cy_e, cy_u = year_totals(cur_y)
    py_e, py_u = year_totals(prev_y)

    # Per-category YoY
    c.execute("SELECT id,name,color,icon FROM categories WHERE type='uscita' ORDER BY name")
    cats2 = [dict(r) for r in c.fetchall()]
    yoy_cats = []
    for cat in cats2:
        c.execute("SELECT COALESCE(SUM(amount),0) t FROM transactions WHERE type='uscita' AND category_id=? AND date LIKE ?",
                  (cat['id'], f'{cur_y}%'))
        cy = c.fetchone()['t']
        c.execute("SELECT COALESCE(SUM(amount),0) t FROM transactions WHERE type='uscita' AND category_id=? AND date LIKE ?",
                  (cat['id'], f'{prev_y}%'))
        py = c.fetchone()['t']
        if cy > 0 or py > 0:
            yoy_cats.append({**cat, 'cur': cy, 'prev': py,
                             'diff': cy - py, 'pct': pct_change(cy, py)})

    conn.close()
    return jsonify({
        'categories': result,
        'month_labels': [x[1] for x in months],
        'yoy': {'cur_year': cur_y, 'cur_entrate': cy_e, 'cur_uscite': cy_u,
                'prev_year': prev_y, 'prev_entrate': py_e, 'prev_uscite': py_u,
                'yoy_cats': yoy_cats}
    })


@app.route('/api/years')
def api_years():
    conn = get_db(); c = conn.cursor()
    c.execute("SELECT DISTINCT substr(date,1,4) y FROM transactions ORDER BY y DESC")
    years = [r['y'] for r in c.fetchall()]; conn.close()
    if not years: years = [str(date.today().year)]
    return jsonify(years)


# ── API: Export ───────────────────────────────────────────────────────────────

@app.route('/api/export/csv')
def api_export_csv():
    year = request.args.get('year', type=int); month = request.args.get('month', type=int)
    conn = get_db(); c = conn.cursor()
    q = '''SELECT t.date,t.description,t.amount,t.type,cat.name category,t.notes
           FROM transactions t LEFT JOIN categories cat ON t.category_id=cat.id WHERE 1=1'''
    p = []
    if year and month: q += ' AND t.date LIKE ?'; p.append(f'{year}-{month:02d}%')
    elif year:         q += ' AND t.date LIKE ?'; p.append(f'{year}%')
    q += ' ORDER BY t.date ASC'
    c.execute(q, p); rows = c.fetchall(); conn.close()
    si = io.StringIO(); wr = csv.writer(si)
    wr.writerow(['Data','Descrizione','Importo','Tipo','Categoria','Note'])
    for r in rows:
        wr.writerow([r['date'], r['description'], f"{r['amount']:.2f}",
                     r['type'], r['category'] or '', r['notes'] or ''])
    out = io.BytesIO(si.getvalue().encode('utf-8-sig'))
    return send_file(out, mimetype='text/csv', as_attachment=True,
                     download_name=f"contabilita_{year or 'tutti'}.csv")


@app.route('/api/export/excel')
def api_export_excel():
    year = request.args.get('year', type=int); month = request.args.get('month', type=int)
    conn = get_db(); c = conn.cursor()
    q = '''SELECT t.date,t.description,t.amount,t.type,cat.name category,t.notes
           FROM transactions t LEFT JOIN categories cat ON t.category_id=cat.id WHERE 1=1'''
    p = []
    if year and month: q += ' AND t.date LIKE ?'; p.append(f'{year}-{month:02d}%')
    elif year:         q += ' AND t.date LIKE ?'; p.append(f'{year}%')
    q += ' ORDER BY t.date ASC'
    c.execute(q, p); rows = c.fetchall(); conn.close()

    wb = openpyxl.Workbook(); ws = wb.active; ws.title = 'Transazioni'
    hdrs = ['Data','Descrizione','Importo (€)','Tipo','Categoria','Note']
    for col, h in enumerate(hdrs, 1):
        cell = ws.cell(1, col, h)
        cell.font = Font(bold=True, color='FFFFFF')
        cell.fill = PatternFill('solid', fgColor='4F46E5')
        cell.alignment = Alignment(horizontal='center')
    for ri, row in enumerate(rows, 2):
        ws.cell(ri,1,row['date']); ws.cell(ri,2,row['description'])
        ws.cell(ri,3,row['amount']); ws.cell(ri,4,row['type'])
        ws.cell(ri,5,row['category'] or ''); ws.cell(ri,6,row['notes'] or '')
        clr = 'D1FAE5' if row['type'] == 'entrata' else 'FEE2E2'
        for col in range(1, 7): ws.cell(ri, col).fill = PatternFill('solid', fgColor=clr)
    for d, w in zip('ABCDEF', [12, 30, 14, 10, 20, 25]):
        ws.column_dimensions[d].width = w
    out = io.BytesIO(); wb.save(out); out.seek(0)
    return send_file(out, mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
                     as_attachment=True, download_name=f"contabilita_{year or 'tutti'}.xlsx")


# ── API: Import ───────────────────────────────────────────────────────────────

@app.route('/api/import/csv', methods=['POST'])
def api_import_csv():
    if 'file' not in request.files: return jsonify({'error': 'No file'}), 400
    content = request.files['file'].read().decode('utf-8-sig')
    reader  = csv.DictReader(io.StringIO(content))

    def norm(row): return {k.strip().lower(): (v or '').strip() for k, v in row.items()}
    def gcol(row, *keys):
        for k in keys:
            if k in row and row[k]: return row[k]
        return ''

    conn = get_db(); c = conn.cursor()
    c.execute('SELECT id, name FROM categories')
    cat_map = {r['name'].lower(): r['id'] for r in c.fetchall()}
    imported = 0; errors = []

    for i, raw in enumerate(reader, 1):
        row = norm(raw)
        try:
            raw_date = gcol(row, 'data', 'date', 'datum')
            desc     = gcol(row, 'descrizione', 'description', 'memo')
            amt_str  = gcol(row, 'importo', 'amount').replace(',', '.')
            type_    = gcol(row, 'tipo', 'type').lower()
            cat_name = gcol(row, 'categoria', 'category')
            notes    = gcol(row, 'note', 'notes')
            if not raw_date or not desc or not amt_str:
                errors.append(f'Riga {i}: dati mancanti'); continue
            parsed_date = None
            for fmt in ('%d/%m/%Y','%Y-%m-%d','%d-%m-%Y','%m/%d/%Y'):
                try: parsed_date = datetime.strptime(raw_date, fmt).strftime('%Y-%m-%d'); break
                except: pass
            if not parsed_date: errors.append(f'Riga {i}: data non riconosciuta'); continue
            amt = float(amt_str)
            if type_ not in ('entrata','uscita'): type_ = 'entrata' if amt >= 0 else 'uscita'
            amt = abs(amt)
            if amt <= 0: errors.append(f'Riga {i}: importo non valido'); continue
            cat_id = cat_map.get(cat_name.lower()) if cat_name else None
            c.execute('INSERT INTO transactions (date,amount,type,category_id,description,notes) VALUES (?,?,?,?,?,?)',
                      (parsed_date, amt, type_, cat_id, desc, notes))
            imported += 1
        except Exception as e:
            errors.append(f'Riga {i}: {e}')

    conn.commit(); conn.close()
    return jsonify({'success': True, 'imported': imported, 'errors': errors})


# ── API: Stats / Backup / Restore ────────────────────────────────────────────

@app.route('/api/stats')
def api_stats():
    conn = get_db(); c = conn.cursor()
    counts = {}
    for t in ['transactions', 'categories', 'tags', 'goals', 'recurring_transactions']:
        c.execute(f'SELECT COUNT(*) FROM {t}')
        counts[t] = c.fetchone()[0]
    conn.close()

    db_size = os.path.getsize(DB_PATH) if os.path.exists(DB_PATH) else 0
    all_size, all_count = 0, 0
    if os.path.exists(RECEIPTS):
        for fn in os.listdir(RECEIPTS):
            fp = os.path.join(RECEIPTS, fn)
            if os.path.isfile(fp):
                all_size += os.path.getsize(fp)
                all_count += 1
    return jsonify({**counts, 'db_size': db_size,
                    'allegati_count': all_count, 'allegati_size': all_size})


def _build_backup_zip(dest_path):
    """Write backup ZIP to dest_path (a filesystem path or BytesIO)."""
    with zipfile.ZipFile(dest_path, 'w', zipfile.ZIP_DEFLATED) as zf:
        if os.path.exists(DB_PATH):
            zf.write(DB_PATH, 'contabilita.db')
        if os.path.exists(RECEIPTS):
            for fn in os.listdir(RECEIPTS):
                fp = os.path.join(RECEIPTS, fn)
                if os.path.isfile(fp):
                    zf.write(fp, f'allegati/{fn}')


@app.route('/api/backup')
def api_backup():
    buf = io.BytesIO()
    _build_backup_zip(buf)
    buf.seek(0)
    ts = datetime.now().strftime('%Y%m%d_%H%M%S')
    return send_file(buf, mimetype='application/zip', as_attachment=True,
                     download_name=f'ContabilitaFamily_backup_{ts}.zip')


@app.route('/api/restore', methods=['POST'])
def api_restore():
    if 'file' not in request.files:
        return jsonify({'error': 'Nessun file caricato'}), 400
    f = request.files['file']
    if not f.filename.lower().endswith('.zip'):
        return jsonify({'error': 'Il file deve essere un .zip'}), 400

    buf = io.BytesIO(f.read())
    try:
        with zipfile.ZipFile(buf, 'r') as zf:
            if 'contabilita.db' not in zf.namelist():
                return jsonify({'error': 'ZIP non valido: database non trovato'}), 400

            with tempfile.TemporaryDirectory() as tmp:
                zf.extractall(tmp)
                tmp_db = os.path.join(tmp, 'contabilita.db')

                # Valida che sia un DB ContabilitaFamily
                tc = sqlite3.connect(tmp_db)
                n_tx  = tc.execute('SELECT COUNT(*) FROM transactions').fetchone()[0]
                n_cat = tc.execute('SELECT COUNT(*) FROM categories').fetchone()[0]
                tc.close()

                # Sostituisci database
                shutil.copy2(tmp_db, DB_PATH)

                # Sostituisci allegati
                tmp_all = os.path.join(tmp, 'allegati')
                if os.path.exists(tmp_all):
                    for fn in os.listdir(RECEIPTS):
                        try: os.remove(os.path.join(RECEIPTS, fn))
                        except: pass
                    for fn in os.listdir(tmp_all):
                        shutil.copy2(os.path.join(tmp_all, fn),
                                     os.path.join(RECEIPTS, fn))

        return jsonify({'success': True, 'transactions': n_tx, 'categories': n_cat})
    except sqlite3.OperationalError:
        return jsonify({'error': 'Il database nel backup non e\' valido'}), 400
    except Exception as e:
        return jsonify({'error': str(e)}), 500


class WebViewApi:
    """Methods exposed to JS as window.pywebview.api.*"""

    def save_backup(self, default_name):
        try:
            import webview
            result = webview.windows[0].create_file_dialog(
                webview.SAVE_DIALOG,
                save_filename=default_name,
                file_types=('ZIP archive (*.zip)', 'All files (*.*)')
            )
            if not result:
                return {'cancelled': True}
            dest = result if isinstance(result, str) else result[0]
            if not dest.lower().endswith('.zip'):
                dest += '.zip'
            _build_backup_zip(dest)
            return {'success': True, 'path': dest}
        except Exception as e:
            return {'error': str(e)}


def _find_free_port(start=5050):
    import socket
    for p in range(start, start + 20):
        with socket.socket(socket.AF_INET, socket.SOCK_STREAM) as s:
            if s.connect_ex(('127.0.0.1', p)) != 0:
                return p
    return start


if __name__ == '__main__':
    import threading, time
    init_db()

    PORT = _find_free_port()

    def _run_flask():
        app.run(host='127.0.0.1', port=PORT, debug=False, use_reloader=False, threaded=True)

    threading.Thread(target=_run_flask, daemon=True).start()
    time.sleep(1.0)

    try:
        import webview
        window = webview.create_window(
            'ContabilitàFamily',
            url=f'http://127.0.0.1:{PORT}',
            width=1280, height=800,
            min_size=(960, 640),
            js_api=WebViewApi(),
        )
        webview.start(debug=False)
    except Exception:
        # Fallback: apri nel browser
        import webbrowser
        webbrowser.open(f'http://127.0.0.1:{PORT}')
        threading.Event().wait()   # tieni vivo il processo
