System Configuration

CREATE TABLE IF NOT EXISTS users (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    student_id    TEXT    NOT NULL UNIQUE,
    name          TEXT    NOT NULL,
    email         TEXT    NOT NULL UNIQUE COLLATE NOCASE,
    password_hash TEXT    NOT NULL,
    active        INTEGER NOT NULL DEFAULT 1,
    created_at    DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS books (
    isbn    TEXT PRIMARY KEY,
    title   TEXT NOT NULL,
    author  TEXT NOT NULL,
    cover   TEXT NOT NULL DEFAULT "",
    status  TEXT NOT NULL DEFAULT "Available",
    shelf   TEXT NOT NULL DEFAULT ""
);

CREATE TABLE IF NOT EXISTS loans (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id       INTEGER NOT NULL REFERENCES users(id),
    isbn          TEXT    NOT NULL REFERENCES books(isbn),
    checked_out   DATETIME DEFAULT CURRENT_TIMESTAMP,
    due_date      DATETIME NOT NULL,
    returned      INTEGER  NOT NULL DEFAULT 0,
    returned_date DATETIME
);

DB_PATH = Path(__file__).parent / "cs_library.db"

# Option 1: Environment variable
import os
DB_PATH = Path(os.getenv('DB_PATH', '/var/lib/cs-library/cs_library.db'))

# Option 2: Absolute path
DB_PATH = Path('/opt/cs-library-kiosk/data/cs_library.db')

# Test DNS resolution
nslookup openlibrary.org

# Test HTTPS connectivity
curl -I https://openlibrary.org/api/books?bibkeys=ISBN:9780140328721&format=json&jscmd=data

# Test from Python
python3 -c "import httpx; print(httpx.get('https://openlibrary.org').status_code)"

# Stop the service
sudo systemctl stop cs-library-kiosk.service

# Backup current database (just in case)
cp /opt/cs-library-kiosk/cs_library.db /tmp/cs_library_current.db

# Find backup to restore
ls -lh /var/backups/cs-library-kiosk/

# Decompress and restore
gunzip -c /var/backups/cs-library-kiosk/cs_library_20260304_020000.db.gz > /opt/cs-library-kiosk/cs_library.db

# Set proper permissions
chown pi:pi /opt/cs-library-kiosk/cs_library.db
chmod 600 /opt/cs-library-kiosk/cs_library.db

# Restart service
sudo systemctl start cs-library-kiosk.service

# Enable/disable live API lookups
USE_LIVE_API = True

USE_LIVE_API = False

# In .env file
USE_LIVE_API=False

due = datetime.now() + timedelta(days=14)

# In .env
LOAN_PERIOD_DAYS=21

import os
LOAN_DAYS = int(os.getenv('LOAN_PERIOD_DAYS', '14'))
due = datetime.now() + timedelta(days=LOAN_DAYS)

def _connect() -> sqlite3.Connection:
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON")
    
    # Performance optimizations
    conn.execute("PRAGMA journal_mode = WAL")  # Write-Ahead Logging
    conn.execute("PRAGMA synchronous = NORMAL")  # Faster writes
    conn.execute("PRAGMA cache_size = 10000")  # Increase cache
    
    return conn

ui.run(
    host='0.0.0.0',
    port=8080,
    title="CS Library Kiosk",
    favicon='favicon1.ico',
    dark=True,
    reload=False,  # Disable auto-reload in production
    show=False  # Don't auto-open browser
)

import logging
from logging.handlers import RotatingFileHandler
import os

# Configure logging
log_level = os.getenv('LOG_LEVEL', 'INFO')
log_file = os.getenv('LOG_FILE', '/var/log/cs-library-kiosk/app.log')

# Create log directory if needed
os.makedirs(os.path.dirname(log_file), exist_ok=True)

# Configure logger
logging.basicConfig(
    level=getattr(logging, log_level),
    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s',
    handlers=[
        RotatingFileHandler(log_file, maxBytes=10485760, backupCount=5),
        logging.StreamHandler()
    ]
)

logger = logging.getLogger(__name__)

sudo vim /etc/logrotate.d/cs-library-kiosk

/var/log/cs-library-kiosk/*.log {
    daily
    rotate 14
    compress
    delaycompress
    notifempty
    create 0640 pi pi
    sharedscripts
    postrotate
        systemctl reload cs-library-kiosk.service > /dev/null 2>&1 || true
    endscript
}

sudo vim /usr/local/bin/health-check.sh

#!/bin/bash

# Check if service is running
if ! systemctl is-active --quiet cs-library-kiosk.service; then
    echo "ALERT: CS Library Kiosk service is not running" | \
      logger -t cs-library-health
    # Attempt restart
    systemctl restart cs-library-kiosk.service
fi

# Check database accessibility
if ! sqlite3 /opt/cs-library-kiosk/cs_library.db "SELECT 1" > /dev/null 2>&1; then
    echo "ALERT: Database is not accessible" | \
      logger -t cs-library-health
fi

# Check disk space
USAGE=$(df -h / | awk 'NR==2 {print $5}' | sed 's/%//')
if [ $USAGE -gt 90 ]; then
    echo "ALERT: Disk usage is at ${USAGE}%" | \
      logger -t cs-library-health
fi

# Check temperature
TEMP=$(vcgencmd measure_temp | sed 's/temp=//' | sed 's/.C//')
if (( $(echo "$TEMP > 80" | bc -l) )); then
    echo "ALERT: CPU temperature is ${TEMP}°C" | \
      logger -t cs-library-health
fi

sudo chmod +x /usr/local/bin/health-check.sh

# Run every 5 minutes
*/5 * * * * /usr/local/bin/health-check.sh

# Set restrictive permissions on database
chmod 600 /opt/cs-library-kiosk/cs_library.db
chown pi:pi /opt/cs-library-kiosk/cs_library.db

# Prevent accidental deletion
sudo chattr +i /opt/cs-library-kiosk/cs_library.db
# (Use 'sudo chattr -i' to unlock when needed)

# Check for lingering processes
lsof /opt/cs-library-kiosk/cs_library.db

# If using WAL mode, check for wal files
ls -la /opt/cs-library-kiosk/cs_library.db*

# Clear WAL checkpoint
sqlite3 /opt/cs-library-kiosk/cs_library.db "PRAGMA wal_checkpoint(FULL);"

# Check logs
sudo journalctl -u cs-library-kiosk.service -n 50

# Verify file paths in service file
sudo systemctl cat cs-library-kiosk.service

# Test manually
cd /opt/cs-library-kiosk
source venv/bin/activate
python3 main.py

# Test API connectivity
curl -m 10 https://openlibrary.org/api/books?bibkeys=ISBN:9780140328721&format=json

# Adjust timeout in database.py:146
async with httpx.AsyncClient(timeout=15.0) as client: