from flask import Flask, render_template, request, jsonify, send_file
from flask_sqlalchemy import SQLAlchemy
import pandas as pd
from datetime import datetime
import os

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///data/tickets.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)

class Ticket(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(120), nullable=False)
    phone = db.Column(db.String(20), nullable=False)
    name = db.Column(db.String(100), nullable=False)
    ticket_count = db.Column(db.Integer, nullable=False)
    ticket_number = db.Column(db.String(50), nullable=False)
    country = db.Column(db.String(50), nullable=False)
    region = db.Column(db.String(50), nullable=False)
    timestamp = db.Column(db.DateTime, default=datetime.utcnow)

@app.route('/add_data/<email>/<phone>/<name>/<int:ticket_count>/<ticket_number>/<country>/<region>')
def add_data(email, phone, name, ticket_count, ticket_number, country, region):
    try:
        new_ticket = Ticket(
            email=email,
            phone=phone,
            name=name,
            ticket_count=ticket_count,
            ticket_number=ticket_number,
            country=country,
            region=region
        )
        db.session.add(new_ticket)
        db.session.commit()
        return jsonify({"status": "success", "message": "Ticket added successfully"})
    except Exception as e:
        return jsonify({"status": "error", "message": str(e)}), 400

@app.route('/')
def dashboard():
    tickets = Ticket.query.order_by(Ticket.timestamp.desc()).all()
    return render_template('index.html', tickets=tickets)

@app.route('/download_excel')
def download_excel():
    try:
        tickets = Ticket.query.all()
        data = {
            "Email": [t.email for t in tickets],
            "Phone": [t.phone for t in tickets],
            "Name": [t.name for t in tickets],
            "Ticket Count": [t.ticket_count for t in tickets],
            "Ticket Number": [t.ticket_number for t in tickets],
            "Country": [t.country for t in tickets],
            "Region": [t.region for t in tickets],
            "Timestamp": [t.timestamp for t in tickets]
        }
        df = pd.DataFrame(data)
        excel_path = os.path.join('data', 'tickets_export.xlsx')
        df.to_excel(excel_path, index=False)
        return send_file(excel_path, as_attachment=True)
    except Exception as e:
        return str(e), 500

if __name__ == '__main__':
    with app.app_context():
        db.create_all()
    app.run(host='0.0.0.0', port=7860, debug=True)