"""
AttendX Pro v2.0 — Backend Server
SQLite database, full REST API, serves frontend + PWA
"""
from flask import Flask, jsonify, request, send_from_directory, g
from flask_cors import CORS
import sqlite3, os, datetime, json

app  = Flask(__name__)
CORS(app, origins="*")

BASE     = os.path.dirname(os.path.abspath(__file__))
FRONTEND = os.path.join(BASE, "frontend")
DB_PATH  = os.path.join(BASE, "attendx.db")

# ── Database ──────────────────────────────────────────────────────
def get_db():
    if "db" not in g:
        g.db = sqlite3.connect(DB_PATH)
        g.db.row_factory = sqlite3.Row
        g.db.execute("PRAGMA journal_mode=WAL")
    return g.db

@app.teardown_appcontext
def close_db(e=None):
    db = g.pop("db", None)
    if db: db.close()

def init_db():
    db = sqlite3.connect(DB_PATH)
    db.executescript("""
    CREATE TABLE IF NOT EXISTS attendance (
        id          INTEGER PRIMARY KEY AUTOINCREMENT,
        emp_id      INTEGER,
        emp_name    TEXT,
        dept        TEXT,
        date        TEXT,
        time_in     TEXT,
        time_out    TEXT,
        status      TEXT DEFAULT 'PRESENT',
        source      TEXT DEFAULT 'MANUAL',
        zone        TEXT,
        device_fp   TEXT,
        late_mins   INTEGER DEFAULT 0,
        ot_hours    REAL DEFAULT 0,
        remarks     TEXT,
        saved_at    TEXT
    );
    CREATE TABLE IF NOT EXISTS leaves (
        id          INTEGER PRIMARY KEY AUTOINCREMENT,
        emp_id      INTEGER,
        emp_name    TEXT,
        dept        TEXT,
        type_id     TEXT,
        type_name   TEXT,
        from_date   TEXT,
        to_date     TEXT,
        days        REAL,
        reason      TEXT,
        status      TEXT DEFAULT 'PENDING',
        approved_by TEXT,
        applied_on  TEXT,
        remarks     TEXT
    );
    CREATE TABLE IF NOT EXISTS devices (
        id              INTEGER PRIMARY KEY AUTOINCREMENT,
        emp_id          INTEGER,
        fp              TEXT UNIQUE,
        user_agent      TEXT,
        status          TEXT DEFAULT 'PENDING',
        active          INTEGER DEFAULT 0,
        registered_at   TEXT,
        approved_at     TEXT
    );
    CREATE TABLE IF NOT EXISTS settings (
        key   TEXT PRIMARY KEY,
        value TEXT
    );
    CREATE INDEX IF NOT EXISTS idx_att_date ON attendance(date);
    CREATE INDEX IF NOT EXISTS idx_att_emp  ON attendance(emp_id);
    """)
    db.commit()
    db.close()

# ── Frontend ──────────────────────────────────────────────────────
@app.route("/")
def index():
    return send_from_directory(FRONTEND, "index.html")

@app.route("/checkin")
@app.route("/checkin.html")
def checkin_page():
    return send_from_directory(FRONTEND, "checkin.html")

@app.route("/sw.js")
def service_worker():
    return send_from_directory(FRONTEND, "sw.js",
                               mimetype="application/javascript")

@app.route("/manifest.json")
def manifest():
    return send_from_directory(FRONTEND, "manifest.json",
                               mimetype="application/manifest+json")

@app.route("/icon-<size>.png")
def icon(size):
    return send_from_directory(FRONTEND, "icon-" + size + ".png",
                               mimetype="image/png")

@app.route("/<path:path>")
def static_files(path):
    # serve real files from frontend, fall back to index.html for SPA routes
    full = os.path.join(FRONTEND, path)
    if os.path.isfile(full):
        return send_from_directory(FRONTEND, path)
    return send_from_directory(FRONTEND, "index.html")

# ── Health ────────────────────────────────────────────────────────
@app.route("/api/health")
def health():
    return jsonify({
        "status": "ok", "system": "AttendX Pro", "version": "2.0",
        "mode": "local" if "localhost" in request.host else "online",
        "time": str(datetime.datetime.now())
    })

# ── Attendance ────────────────────────────────────────────────────
@app.route("/api/attendance", methods=["GET","POST"])
def attendance():
    db = get_db()
    if request.method == "POST":
        d = request.json or {}
        db.execute("""INSERT INTO attendance
            (emp_id,emp_name,dept,date,time_in,time_out,status,source,zone,device_fp,late_mins,ot_hours,remarks,saved_at)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)""",
            (d.get("emp_id"),d.get("emp_name",""),d.get("dept",""),
             d.get("date"),d.get("time_in"),d.get("time_out"),
             d.get("status","PRESENT"),d.get("source","MANUAL"),
             d.get("zone"),d.get("device_fp"),
             d.get("late_mins",0),d.get("ot_hours",0),
             d.get("remarks",""),str(datetime.datetime.now())))
        db.commit()
        lid = db.execute("SELECT last_insert_rowid()").fetchone()[0]
        return jsonify({"status":"saved","id":lid}), 201
    month = request.args.get("month","")
    emp   = request.args.get("emp_id","")
    sql   = "SELECT * FROM attendance WHERE 1=1"
    args  = []
    if month: sql += " AND date LIKE ?"; args.append(month+"%")
    if emp:   sql += " AND emp_id=?";   args.append(int(emp))
    sql += " ORDER BY date DESC, time_in DESC LIMIT 500"
    return jsonify([dict(r) for r in db.execute(sql,args).fetchall()])

# ── Punch (ZKTeco + Mobile) ───────────────────────────────────────
@app.route("/api/punch", methods=["POST"])
def punch():
    db      = get_db()
    punches = (request.json or {}).get("punches", [])
    saved = updated = 0
    now   = str(datetime.datetime.now())
    for p in punches:
        existing = db.execute(
            "SELECT id,time_in,time_out FROM attendance WHERE emp_id=? AND date=?",
            (p.get("emp_id"), p.get("date"))
        ).fetchone()
        if existing:
            new_in  = p.get("time_in")  or existing["time_in"]
            new_out = p.get("time_out") or existing["time_out"]
            if existing["time_in"]  and new_in  and new_in  > existing["time_in"]:  new_in  = existing["time_in"]
            if existing["time_out"] and new_out and new_out < existing["time_out"]: new_out = existing["time_out"]
            db.execute("UPDATE attendance SET time_in=?,time_out=?,zone=?,device_fp=?,status=? WHERE id=?",
                      (new_in,new_out,p.get("zone"),p.get("device_fp"),p.get("status","PRESENT"),existing["id"]))
            updated += 1
        else:
            db.execute("""INSERT INTO attendance
                (emp_id,emp_name,dept,date,time_in,time_out,status,source,zone,device_fp,saved_at)
                VALUES (?,?,?,?,?,?,?,?,?,?,?)""",
                (p.get("emp_id"),p.get("emp_name",""),p.get("dept",""),
                 p.get("date"),p.get("time_in"),p.get("time_out"),
                 p.get("status","PRESENT"),p.get("source","MOBILE"),
                 p.get("zone"),p.get("device_fp"),now))
            saved += 1
    db.commit()
    return jsonify({"status":"ok","saved":saved,"updated":updated})

# ── Today ─────────────────────────────────────────────────────────
@app.route("/api/today")
def today():
    db  = get_db()
    day = str(datetime.date.today())
    return jsonify([dict(r) for r in db.execute(
        "SELECT * FROM attendance WHERE date=? ORDER BY time_in",(day,)).fetchall()])

# ── Leaves ────────────────────────────────────────────────────────
@app.route("/api/leaves", methods=["GET","POST","PUT"])
def leaves():
    db = get_db()
    if request.method == "POST":
        d = request.json or {}
        db.execute("""INSERT INTO leaves
            (emp_id,emp_name,dept,type_id,type_name,from_date,to_date,days,reason,status,applied_on)
            VALUES (?,?,?,?,?,?,?,?,?,?,?)""",
            (d.get("emp_id"),d.get("emp_name",""),d.get("dept",""),
             d.get("type_id"),d.get("type_name",""),
             d.get("from_date"),d.get("to_date"),d.get("days",1),
             d.get("reason",""),"PENDING",str(datetime.date.today())))
        db.commit()
        lid = db.execute("SELECT last_insert_rowid()").fetchone()[0]
        return jsonify({"status":"submitted","id":lid}), 201
    if request.method == "PUT":
        d = request.json or {}
        db.execute("UPDATE leaves SET status=?,approved_by=?,remarks=? WHERE id=?",
                  (d.get("status"),d.get("approved_by","admin"),d.get("remarks",""),d.get("id")))
        db.commit()
        return jsonify({"status":"updated"})
    return jsonify([dict(r) for r in db.execute(
        "SELECT * FROM leaves ORDER BY applied_on DESC").fetchall()])

# ── Devices ───────────────────────────────────────────────────────
@app.route("/api/register_device", methods=["POST"])
def register_device():
    db  = get_db()
    d   = request.json or {}
    fp  = d.get("fp","")
    now = str(datetime.datetime.now())
    existing = db.execute("SELECT id,status FROM devices WHERE fp=?",(fp,)).fetchone()
    if existing:
        return jsonify({"status":"known","device_status":existing["status"]})
    status = d.get("status","PENDING")
    db.execute("INSERT INTO devices (emp_id,fp,user_agent,status,active,registered_at) VALUES (?,?,?,?,?,?)",
              (d.get("emp_id"),fp,d.get("user_agent",""),status,1 if status=="APPROVED" else 0,now))
    db.commit()
    return jsonify({"status":"registered","pending":status=="PENDING"}), 201

@app.route("/api/devices", methods=["GET"])
def get_devices():
    db = get_db()
    return jsonify([dict(r) for r in db.execute(
        "SELECT * FROM devices ORDER BY registered_at DESC").fetchall()])

@app.route("/api/devices/<int:did>", methods=["PUT"])
def update_device(did):
    db  = get_db()
    act = (request.json or {}).get("action","approve")
    now = str(datetime.datetime.now())
    if act == "approve":
        db.execute("UPDATE devices SET status='APPROVED',active=1,approved_at=? WHERE id=?",(now,did))
    elif act == "reject":
        db.execute("UPDATE devices SET status='REJECTED',active=0 WHERE id=?",(did,))
    elif act == "revoke":
        db.execute("UPDATE devices SET status='REVOKED',active=0 WHERE id=?",(did,))
    db.commit()
    return jsonify({"status":"updated"})

@app.route("/api/devices/check", methods=["POST"])
def check_device():
    db     = get_db()
    d      = request.json or {}
    fp     = d.get("fp","")
    emp_id = d.get("emp_id")
    device = db.execute("SELECT * FROM devices WHERE fp=?",(fp,)).fetchone()
    emps   = db.execute("SELECT * FROM devices WHERE emp_id=? AND active=1",(str(emp_id),)).fetchall()
    return jsonify({
        "known":           device is not None,
        "approved":        device["active"]==1 if device else False,
        "status":          device["status"] if device else "unknown",
        "emp_has_devices": len(emps) > 0,
        "can_checkin":     (device["active"]==1) if device else (len(emps)==0)
    })

# ── Employees for check-in page ───────────────────────────────────
@app.route("/api/employees_checkin")
def employees_checkin():
    db  = get_db()
    raw = db.execute("SELECT value FROM settings WHERE key='employees'").fetchone()
    if raw:
        try:
            emps = json.loads(json.loads(raw["value"]))
            return jsonify([{
                "id":e.get("id"),"name":e.get("name",""),
                "dept":e.get("dept",""),"shift":e.get("shift","")
            } for e in emps if e.get("status","ACTIVE")=="ACTIVE"])
        except: pass
    return jsonify([])

# ── Zones for check-in page ───────────────────────────────────────
@app.route("/api/zones", methods=["GET","POST"])
def get_zones():
    db = get_db()
    if request.method == "POST":
        zones = request.json or []
        db.execute("INSERT OR REPLACE INTO settings (key,value) VALUES (?,?)",
                  ("office_zones", json.dumps(json.dumps(zones))))
        db.commit()
        return jsonify({"status":"saved","count":len(zones)})
    raw = db.execute("SELECT value FROM settings WHERE key='office_zones'").fetchone()
    if raw:
        try:
            zones = json.loads(json.loads(raw["value"]))
            return jsonify(zones)  # return ALL zones (active filter done in frontend)
        except: pass
    # Default zone if none configured
    return jsonify([{"id":1,"name":"YDT Main Office — Karachi","lat":24.8607,"lng":67.0011,"radius":150,"active":True}])

# ── Settings ──────────────────────────────────────────────────────
@app.route("/api/settings", methods=["GET","POST"])
def settings():
    db = get_db()
    if request.method == "POST":
        for k,v in (request.json or {}).items():
            db.execute("INSERT OR REPLACE INTO settings (key,value) VALUES (?,?)",(k,json.dumps(v)))
        db.commit()
        return jsonify({"status":"saved"})
    return jsonify({r["key"]:json.loads(r["value"]) for r in
                    db.execute("SELECT * FROM settings").fetchall()})

# ── Import from localStorage ──────────────────────────────────────
@app.route("/api/import", methods=["POST"])
def import_data():
    db  = get_db()
    d   = request.json or {}
    now = str(datetime.datetime.now())
    cnt = 0
    for r in d.get("attlog",[]):
        try:
            db.execute("""INSERT OR IGNORE INTO attendance
                (emp_id,emp_name,dept,date,time_in,time_out,status,source,late_mins,ot_hours,remarks,saved_at)
                VALUES (?,?,?,?,?,?,?,?,?,?,?,?)""",
                (r.get("eid"),r.get("name",""),r.get("dept",""),r.get("date"),
                 r.get("in"),r.get("out"),r.get("status","PRESENT"),r.get("src","MANUAL"),
                 r.get("lm",0),r.get("ot",0),r.get("rmk",""),now))
            cnt += 1
        except: pass
    db.commit()
    return jsonify({"status":"imported","attendance":cnt})

# ── Run ───────────────────────────────────────────────────────────
if __name__ == "__main__":
    init_db()
    print("=" * 55)
    print("  AttendX Pro v2.0")
    print("=" * 55)
    print("  Local:    http://localhost:5000")
    print("  Check-in: http://localhost:5000/checkin")
    print("  Network:  http://YOUR-IP:5000")
    print("=" * 55)
    app.run(host="0.0.0.0", port=5000, debug=False)