import os
import sqlite3
from datetime import datetime
from flask import Flask, g, render_template, request, redirect, url_for, send_from_directory, flash, session
from werkzeug.utils import secure_filename
from functools import wraps

BASE_DIR = os.path.abspath(os.path.dirname(__file__))
UPLOAD_FOLDER = os.path.join(BASE_DIR, 'static', 'uploads')
DB_PATH = os.path.join(BASE_DIR, 'instance', 'data.db')
ALLOWED_EXTENSIONS = {'png', 'jpg', 'jpeg', 'gif', 'heic'}

app = Flask(__name__)
app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER
app.config['DATABASE'] = DB_PATH
app.config['MAX_CONTENT_LENGTH'] = 16 * 1024 * 1024
app.secret_key = 'change-this-secret'

USERNAME = 'edoardo'
PASSWORD = 'ilmionuovoreport89%'

os.makedirs(app.config['UPLOAD_FOLDER'], exist_ok=True)
os.makedirs(os.path.dirname(app.config['DATABASE']), exist_ok=True)

# Database helper
def get_db():
    db = getattr(g, '_database', None)
    if db is None:
        db = g._database = sqlite3.connect(app.config['DATABASE'])
        db.row_factory = sqlite3.Row
    return db

@app.teardown_appcontext
def close_connection(exception):
    db = getattr(g, '_database', None)
    if db is not None:
        db.close()

# Auth helper
def login_required(f):
    from functools import wraps
    @wraps(f)
    def decorated_function(*args, **kwargs):
        if not session.get('logged_in'):
            return redirect(url_for('login'))
        return f(*args, **kwargs)
    return decorated_function

# Login routes
@app.route('/login', methods=['GET','POST'])
def login():
    if request.method == 'POST':
        user = request.form.get('username')
        pw = request.form.get('password')
        if user == USERNAME and pw == PASSWORD:
            session['logged_in'] = True
            return redirect(url_for('index'))
        else:
            flash('Credenziali non valide', 'error')
    return render_template('login.html')

@app.route('/logout')
def logout():
    session.clear()
    return redirect(url_for('login'))

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

# Main page with form submission
@app.route('/', methods=['GET','POST'])
@login_required
def index():
    if request.method == 'POST':
        db = get_db()
        date = request.form.get('date')
        ac_reg = request.form.get('ac_registration')
        notes = request.form.get('notes')
        created_at = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

        # Insert report
        cur = db.execute('INSERT INTO reports (date, ac_registration, notes, created_at) VALUES (?,?,?,?)',
                        (date, ac_reg, notes, created_at))
        report_id = cur.lastrowid
        db.commit()

        # Insert sectors
        for i in range(1,5):
            from_field = request.form.get(f'sector{i}_from')
            to_field = request.form.get(f'sector{i}_to')
            pax_a = int(request.form.get(f'sector{i}_pax_a', 0))
            pax_b = int(request.form.get(f'sector{i}_pax_b', 0))
            sector_revenue = float(request.form.get(f'sector{i}_revenue', 0))
            personal_revenue = float(request.form.get(f'sector{i}_personal', 0))

            db.execute('''INSERT INTO sectors (report_id, sector_index, from_field, to_field, pax_a, pax_b, sector_revenue, personal_revenue)
                          VALUES (?,?,?,?,?,?,?,?)''',
                       (report_id, i, from_field, to_field, pax_a, pax_b, sector_revenue, personal_revenue))
            db.commit()

            # Handle image upload
            file = request.files.get(f'sector{i}_image')
            if file and allowed_file(file.filename):
                filename = secure_filename(file.filename)
                sector_folder = os.path.join(app.config['UPLOAD_FOLDER'], str(report_id))
                os.makedirs(sector_folder, exist_ok=True)
                file.save(os.path.join(sector_folder, filename))
                db.execute('INSERT INTO images (report_id, sector_index, filename) VALUES (?,?,?)',
                           (report_id, i, filename))
                db.commit()

        flash('Report salvato correttamente', 'success')
        return redirect(url_for('reports'))

    return render_template('index.html')

# List reports
@app.route('/reports')
@login_required
def reports():
    db = get_db()
    rs = db.execute('SELECT id, date, ac_registration, created_at FROM reports ORDER BY date DESC, id DESC').fetchall()
    return render_template('reports.html', reports=rs)

# View report details
@app.route('/report/<int:report_id>')
@login_required
def report_view(report_id):
    db = get_db()
    report = db.execute('SELECT * FROM reports WHERE id = ?', (report_id,)).fetchone()
    sectors = db.execute('SELECT * FROM sectors WHERE report_id = ? ORDER BY sector_index', (report_id,)).fetchall()
    images = db.execute('SELECT * FROM images WHERE report_id = ?', (report_id,)).fetchall()
    total_pax_of_day = sum([s['pax_a'] for s in sectors])
    total_sector_revenue = sum([s['sector_revenue'] for s in sectors])
    total_personal_revenue = sum([s['personal_revenue'] for s in sectors])
    return render_template('report_view.html', report=report, sectors=sectors, images=images,
                           total_pax_of_day=total_pax_of_day,
                           total_sector_revenue=total_sector_revenue,
                           total_personal_revenue=total_personal_revenue)

# Serve uploaded files
@app.route('/uploads/<int:report_id>/<filename>')
def uploaded_file(report_id, filename):
    folder = os.path.join(app.config['UPLOAD_FOLDER'], str(report_id))
    return send_from_directory(folder, filename)

# Mock visual report
@app.route('/report_mock')
def report_mock():
    return render_template('report_mock.html')

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