app_sql.py

from flask import Flask, request, jsonify
import sqlite3, os
from datetime import datetime

app = Flask(__name__)
DB_PATH = os.environ.get("DB_PATH", "employees.db")

def init_db():
    with sqlite3.connect(DB_PATH) as db:
        db.execute("""
            CREATE TABLE IF NOT EXISTS employees (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                level INTEGER NOT NULL,
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL
            )
        """)
        db.commit()

def row_to_dict(row):
    return {
        "id": row[0],
        "name": row[1],
        "level": row[2],
        "created_at": row[3],
        "updated_at": row[4],
    }

def now_iso():
    return datetime.utcnow().isoformat(timespec="seconds") + "Z"

@app.get("/health")
def health():
    return {"status": "ok"}

@app.post("/add_employee")
def add_employee():
    init_db()
    data = request.get_json(silent=True) or {}
    name, level = data.get("name"), data.get("level")

    if not isinstance(name, str) or not name.strip():
        return {"error": "name required"}, 400
    if not isinstance(level, int) or level == 0:
        return {"error": "level must be a non-zero integer"}, 400

    ts = now_iso()
    with sqlite3.connect(DB_PATH) as db:
        cur = db.execute(
            "INSERT INTO employees (name, level, created_at, updated_at) VALUES (?,?,?,?)",
            (name.strip(), level, ts, ts)
        )
        db.commit()
        return {"id": cur.lastrowid}, 201

@app.put("/update_employee/<int:employee_id>")
def update_employee(employee_id):
    init_db()
    data = request.get_json(silent=True) or {}
    with sqlite3.connect(DB_PATH) as db:
        row = db.execute("SELECT * FROM employees WHERE id=?", (employee_id,)).fetchone()
        if not row:
            return {"error": "id not found"}, 404
        new_name = data.get("name", row[1]) or row[1]
        new_level = data.get("level", row[2]) or row[2]
        ts = now_iso()
        db.execute("UPDATE employees SET name=?, level=?, updated_at=? WHERE id=?",
                   (new_name, new_level, ts, employee_id))
        db.commit()
        row = db.execute("SELECT * FROM employees WHERE id=?", (employee_id,)).fetchone()
        return row_to_dict(row)

@app.get("/get_all_employees")
def get_all_employees():
    init_db()
    with sqlite3.connect(DB_PATH) as db:
        rows = db.execute("SELECT * FROM employees ORDER BY id").fetchall()
        return jsonify([row_to_dict(r) for r in rows])

@app.delete("/delete_employee/<int:employee_id>")
def delete_employee(employee_id):
    init_db()
    with sqlite3.connect(DB_PATH) as db:
        row = db.execute("SELECT id FROM employees WHERE id=?", (employee_id,)).fetchone()
        if not row:
            return {"error": "id not found"}, 404
        db.execute("DELETE FROM employees WHERE id=?", (employee_id,))
        db.commit()
        return "", 204

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