db.py

import sqlite3
from datetime import datetime, UTC

DB_NAME = "team_activity.db"

def get_connection():
    conn = sqlite3.connect(DB_NAME)
    conn.row_factory = sqlite3.Row
    return conn

def init_db():
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS activities (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        employee_name TEXT NOT NULL,
        activity_date TEXT NOT NULL,
        category TEXT NOT NULL,
        task_type TEXT NOT NULL,
        task_name TEXT NOT NULL,
        duration_hour TEXT NOT NULL,
        notes TEXT,
        created_at TEXT NOT NULL
        )
    """)

    conn.commit()
    conn.close()

def add_activity(employee_name,
            activity_date,
            category,
            task_type,
            task_name,
            duration_hour,
            notes):
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute("""
        INSERT INTO activities (
            employee_name,
            activity_date,
            category,
            task_type,
            task_name,
            duration_hour,
            notes,
            created_at
        )
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    """, (employee_name,
            activity_date,
            category,
            task_type,
            task_name,
            duration_hour,
            notes,
            datetime.now(UTC).isoformat())
                   )
    conn.commit()
    conn.close()

def get_all_activities():
    conn = get_connection()
    rows = conn.execute("""
        SELECT * FROM activities
        ORDER BY activity_date DESC, id DESC
        """).fetchall()
    conn.close()
    return rows