from flask import Flask, render_template, request, jsonify
import psycopg2
from psycopg2 import sql

app = Flask(__name__)

def connect_to_db():
    return psycopg2.connect(
        dbname="glprui_jloddr",
        user="glprui_jloddr",
        password="612ef773",
        host="db.qgiscloud.com",
        port="5432",
        sslmode="prefer"
    )

@app.route('/')
def index():
    return render_template('index.html')

@app.route('/record_data', methods=['POST'])
def record_point():
    data = request.json

    try:
        conn = connect_to_db()
        cursor = conn.cursor()

        # Insert into gettinglost_tracking
        cursor.execute(
            """
            INSERT INTO public.gettinglost_tracking (Age, Gender, Transport, TimeOfDay, DayOfWeek, Description) 
            VALUES (%s, %s, %s, %s, %s, %s) RETURNING ID;
            """,
            (data['age'], data['gender'], data['transport'], data['timeOfDay'], data['dayOfWeek'], data['description'])
        )

        # Get the ID of the just inserted record
        record_id = cursor.fetchone()[0]

        # Insert into gettinglost_geom using the retrieved ID
        cursor.execute(
            """
            INSERT INTO public.gettinglost_geom (ID, PointType, geom) 
            VALUES (%s, %s, ST_SetSRID(ST_Point(%s, %s), 4326));
            """,
            (record_id, data['pointType'], data['lon'], data['lat'])
        )

        conn.commit()

        return jsonify(message="Data recorded successfully!")
    except Exception as e:
        return jsonify(error=str(e))
    finally:
        cursor.close()
        conn.close()

if __name__ == '__main__':
    app.run(debug=True)