app.py

from flask import Flask, request, jsonify, send_file
from pathlib import Path
import sqlite3
from contextlib import contextmanager
from typing import Any, Dict, List, Optional

import analysis  
import entry      
import queries   

DB_PATH = Path("retail.db")

app = Flask(__name__)


@contextmanager
def db_conn(path: Path = DB_PATH):
    conn = sqlite3.connect(str(path), detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON;")
    try:
        yield conn
    finally:
        conn.close()


def fetch_all(sql: str, params: Optional[Dict[str, Any]] = None) -> List[Dict[str, Any]]:
    params = params or {}
    with db_conn() as conn:
        cur = conn.execute(sql, params)
        rows = cur.fetchall()
    return [dict(r) for r in rows]


def fetch_one(sql: str, params: Optional[Dict[str, Any]] = None) -> Optional[Dict[str, Any]]:
    params = params or {}
    with db_conn() as conn:
        cur = conn.execute(sql, params)
        row = cur.fetchone()
    return dict(row) if row else None


def parse_int_qs(key: str) -> Optional[int]:
    v = request.args.get(key)
    if v is None or v == "":
        return None
    try:
        return int(v)
    except ValueError:
        return None


def date_qs(key: str) -> Optional[str]:
    v = request.args.get(key)
    return v if v else None


@app.route("/health", methods=["GET"])
def health():
    return jsonify({"status": "ok"}), 200


@app.route("/api/tables/customers", methods=["GET"])
def api_customers():
    return jsonify(fetch_all(queries.GET_ALL_CUSTOMERS))


@app.route("/api/tables/products", methods=["GET"])
def api_products():
    return jsonify(fetch_all(queries.GET_ALL_PRODUCTS))


@app.route("/api/tables/stores", methods=["GET"])
def api_stores():
    return jsonify(fetch_all(queries.GET_ALL_STORES))


@app.route("/api/tables/order-items", methods=["GET"])
def api_order_items():
    return jsonify(fetch_all(queries.GET_ALL_ORDER_ITEMS))


@app.route("/api/tables/orders", methods=["GET"])
def api_orders():
    """
    Returns orders (latest first). Optional filters:
      ?customer_id=<int>
      ?store_id=<int>
    """
    customer_id = parse_int_qs("customer_id")
    store_id = parse_int_qs("store_id")

    # If invalid integer passed, return 400
    raw_customer = request.args.get("customer_id")
    raw_store = request.args.get("store_id")
    if raw_customer and customer_id is None:
        return jsonify({"error": "customer_id must be integer"}), 400
    if raw_store and store_id is None:
        return jsonify({"error": "store_id must be integer"}), 400

    params = {"customer_id": customer_id, "store_id": store_id}
    return jsonify(fetch_all(queries.GET_ALL_ORDERS, params))


@app.route("/api/tables/order-items-with-product", methods=["GET"])
def api_order_items_with_product():
    return jsonify(fetch_all(queries.GET_ORDER_ITEMS_WITH_PRODUCT))


@app.route("/api/summary", methods=["GET"])
def api_summary():
    start_date = date_qs("start_date")
    end_date = date_qs("end_date")
    stats = analysis.summary_stats(DB_PATH, start_date=start_date, end_date=end_date)
    return jsonify(stats)


@app.route("/api/top-products", methods=["GET"])
def api_top_products():
    n_raw = request.args.get("n", "10")
    try:
        n = int(n_raw)
    except ValueError:
        return jsonify({"error": "n must be an integer"}), 400
    start_date = date_qs("start_date")
    end_date = date_qs("end_date")
    df = analysis.top_products_df(DB_PATH, n=n, start_date=start_date, end_date=end_date)
    return jsonify(df.to_dict(orient="records"))


@app.route("/api/sales/monthly.png", methods=["GET"])
def api_monthly_png():
    start_date = date_qs("start_date")
    end_date = date_qs("end_date")
    img = analysis.plot_monthly_sales(DB_PATH, start_date=start_date, end_date=end_date)
    return send_file(img, mimetype="image/png")


@app.route("/api/top-products.png", methods=["GET"])
def api_top_products_png():
    n_raw = request.args.get("n", "10")
    try:
        n = int(n_raw)
    except ValueError:
        n = 10
    start_date = date_qs("start_date")
    end_date = date_qs("end_date")
    img = analysis.plot_top_products(DB_PATH, n=n, start_date=start_date, end_date=end_date)
    return send_file(img, mimetype="image/png")


@app.route("/api/sales/by-region", methods=["GET"])
def api_sales_by_region():
    start_date = date_qs("start_date")
    end_date = date_qs("end_date")
    df = analysis.sales_by_region_df(DB_PATH, start_date=start_date, end_date=end_date)
    return jsonify(df.to_dict(orient="records"))


@app.route("/api/sales/by-region.png", methods=["GET"])
def api_sales_by_region_png():
    start_date = date_qs("start_date")
    end_date = date_qs("end_date")
    img = analysis.plot_sales_by_region(DB_PATH, start_date=start_date, end_date=end_date)
    return send_file(img, mimetype="image/png")


@app.route("/api/customers", methods=["POST"])
def api_add_customer():
    payload = request.get_json() or {}
    try:
        with db_conn() as conn:
            cust_id = entry.add_customer(conn,
                                         name=payload.get("name"),
                                         email=payload.get("email"),
                                         city=payload.get("city"),
                                         state=payload.get("state"),
                                         signup_date=payload.get("signup_date"))
            conn.commit()
        return jsonify({"customer_id": cust_id}), 201
    except ValueError as e:
        return jsonify({"error": str(e)}), 400
    except Exception as e:
        return jsonify({"error": "server error", "detail": str(e)}), 500


@app.route("/api/products", methods=["POST"])
def api_add_product():
    payload = request.get_json() or {}
    try:
        price = float(payload.get("price"))
    except (TypeError, ValueError):
        return jsonify({"error": "price is required and must be numeric"}), 400
    try:
        with db_conn() as conn:
            pid = entry.add_product(conn,
                                    sku=payload.get("sku"),
                                    name=payload.get("name"),
                                    category=payload.get("category"),
                                    price=price)
            conn.commit()
        return jsonify({"product_id": pid}), 201
    except ValueError as e:
        return jsonify({"error": str(e)}), 400
    except sqlite3.IntegrityError as e:
        return jsonify({"error": "integrity error (maybe SKU exists)", "detail": str(e)}), 400
    except Exception as e:
        return jsonify({"error": "server error", "detail": str(e)}), 500


@app.route("/api/stores", methods=["POST"])
def api_add_store():
    payload = request.get_json() or {}
    try:
        with db_conn() as conn:
            sid = entry.add_store(conn,
                                  name=payload.get("name"),
                                  city=payload.get("city"),
                                  state=payload.get("state"),
                                  region=payload.get("region"))
            conn.commit()
        return jsonify({"store_id": sid}), 201
    except ValueError as e:
        return jsonify({"error": str(e)}), 400
    except Exception as e:
        return jsonify({"error": "server error", "detail": str(e)}), 500


@app.route("/api/orders", methods=["POST"])
def api_create_order():
    payload = request.get_json() or {}
    try:
        customer_id = int(payload.get("customer_id"))
        store_id = int(payload.get("store_id"))
        items = payload.get("items")
    except Exception:
        return jsonify({"error": "customer_id, store_id and items are required"}), 400

    order_date = payload.get("order_date")
    status = payload.get("status", "completed")
    try:
        with db_conn() as conn:
            order_id = entry.create_order(conn,
                                          customer_id=customer_id,
                                          store_id=store_id,
                                          items=items,
                                          order_date=order_date,
                                          status=status)
            conn.commit()
        return jsonify({"order_id": order_id}), 201
    except ValueError as e:
        return jsonify({"error": str(e)}), 400
    except sqlite3.IntegrityError as e:
        return jsonify({"error": "integrity error", "detail": str(e)}), 400
    except Exception as e:
        return jsonify({"error": "server error", "detail": str(e)}), 500


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