from flask import Flask, request
import snowflake.connector

conn = snowflake.connector.connect(
    user="REDACTED",
    password="REDACTED",
    account="REDACTED",
    warehouse="COMPUTE_WH",
    database="SNOWFLAKE_SAMPLE_DATA",
    schema="TPCDS_SF100TCL"
)

app = Flask(__name__)

@app.route("/shipping", methods=["GET"])
def hello():
    shipping_id = request.args.get("id", "")
    query = f"SELECT sm_ship_mode_id, sm_type, sm_carrier FROM ship_mode WHERE sm_ship_mode_id='{shipping_id}'"

    try:
        cursor = conn.cursor()
        cursor.execute(query)
        data = cursor.fetchall()
        cursor.close()
    except:
        data = []

    # blind
    return {'status': len(data) > 0}
    
    # union
    return {
        "count": len(data),
        "results": [{"id": x[0], "type": x[1], "carrier": x[2]} for x in data]
    }

if __name__ == "__main__":
    app.run(host="127.0.0.1", port=5000)