database/db.py

"""
CONSTABLE – SQLite database helpers.
Tables:
  employees  – id (TEXT PK), name (TEXT), registered_at (TEXT)
  attendance – id (INTEGER PK), employee_id (TEXT FK), timestamp (TEXT), date (TEXT)
"""

import sqlite3
import os
from datetime import datetime, date

DB_DIR = os.path.join(os.path.dirname(__file__))
DB_PATH = os.path.join(DB_DIR, "constable.db")


def get_connection():
    conn = sqlite3.connect(DB_PATH, check_same_thread=False)
    conn.row_factory = sqlite3.Row
    return conn


def init_db():
    """Create tables if they don't exist."""
    os.makedirs(DB_DIR, exist_ok=True)
    conn = get_connection()
    cur = conn.cursor()
    cur.executescript("""
        CREATE TABLE IF NOT EXISTS employees (
            id           TEXT PRIMARY KEY,
            name         TEXT NOT NULL,
            registered_at TEXT NOT NULL
        );

        CREATE TABLE IF NOT EXISTS attendance (
            id          INTEGER PRIMARY KEY AUTOINCREMENT,
            employee_id TEXT NOT NULL,
            timestamp   TEXT NOT NULL,
            date        TEXT NOT NULL,
            FOREIGN KEY (employee_id) REFERENCES employees(id)
        );
    """)
    conn.commit()
    conn.close()


# ---------------------------------------------------------------------------
# Employee helpers
# ---------------------------------------------------------------------------

def add_employee(employee_id: str, name: str) -> bool:
    """Insert or replace an employee record. Returns True on success."""
    conn = get_connection()
    try:
        conn.execute(
            "INSERT OR REPLACE INTO employees (id, name, registered_at) VALUES (?, ?, ?)",
            (employee_id, name, datetime.now().isoformat(timespec="seconds")),
        )
        conn.commit()
        return True
    except Exception as e:
        print(f"[DB] add_employee error: {e}")
        return False
    finally:
        conn.close()


def get_employee(employee_id: str):
    """Return employee row or None."""
    conn = get_connection()
    try:
        row = conn.execute(
            "SELECT * FROM employees WHERE id = ?", (employee_id,)
        ).fetchone()
        return dict(row) if row else None
    finally:
        conn.close()


def get_all_employees():
    conn = get_connection()
    try:
        rows = conn.execute("SELECT * FROM employees ORDER BY registered_at DESC").fetchall()
        return [dict(r) for r in rows]
    finally:
        conn.close()


def delete_employee(employee_id: str) -> bool:
    """Delete an employee and their attendance records. Returns True on success."""
    conn = get_connection()
    try:
        conn.execute("DELETE FROM attendance WHERE employee_id = ?", (employee_id,))
        conn.execute("DELETE FROM employees WHERE id = ?", (employee_id,))
        conn.commit()
        return True
    except Exception as e:
        print(f"[DB] delete_employee error: {e}")
        return False
    finally:
        conn.close()


# ---------------------------------------------------------------------------
# Attendance helpers
# ---------------------------------------------------------------------------

def mark_attendance(employee_id: str) -> dict:
    """
    Log attendance for today.
    Returns {'status': 'success'|'already_marked', 'timestamp': ...}
    """
    today = date.today().isoformat()
    conn = get_connection()
    try:
        existing = conn.execute(
            "SELECT id FROM attendance WHERE employee_id = ? AND date = ?",
            (employee_id, today),
        ).fetchone()
        if existing:
            return {"status": "already_marked"}

        ts = datetime.now().strftime("%I:%M %p")
        conn.execute(
            "INSERT INTO attendance (employee_id, timestamp, date) VALUES (?, ?, ?)",
            (employee_id, ts, today),
        )
        conn.commit()
        return {"status": "success", "timestamp": ts}
    finally:
        conn.close()


def get_today_attendance():
    today = date.today().isoformat()
    conn = get_connection()
    try:
        rows = conn.execute(
            """SELECT a.timestamp, e.id, e.name
               FROM attendance a
               JOIN employees e ON a.employee_id = e.id
               WHERE a.date = ?
               ORDER BY a.id DESC""",
            (today,),
        ).fetchall()
        return [dict(r) for r in rows]
    finally:
        conn.close()