init_db.py

#!/usr/bin/env python3
"""
Initialize the Agent Worklog database.

Usage:
    python init_db.py [--db-path PATH]

This script creates the SQLite database and applies the schema.
Safe to run multiple times - uses IF NOT EXISTS clauses.
"""

import sqlite3
import argparse
import os
from pathlib import Path


def get_default_db_path():
    """Get the default database path (~/.agent-worklog.db)."""
    return Path.home() / ".agent-worklog.db"


def get_schema_path():
    """Get the path to the schema file."""
    return Path(__file__).parent / "schema.sql"


def init_database(db_path: Path) -> None:
    """Initialize the database with the schema."""
    schema_path = get_schema_path()
    
    if not schema_path.exists():
        raise FileNotFoundError(f"Schema file not found: {schema_path}")
    
    # Read schema
    with open(schema_path, 'r') as f:
        schema = f.read()
    
    # Connect and execute schema
    conn = sqlite3.connect(db_path)
    try:
        conn.executescript(schema)
        conn.commit()
        print(f"Database initialized successfully: {db_path}")
    finally:
        conn.close()


def verify_database(db_path: Path) -> bool:
    """Verify the database was created correctly."""
    if not db_path.exists():
        return False
    
    conn = sqlite3.connect(db_path)
    try:
        cursor = conn.cursor()
        
        # Check main table exists
        cursor.execute("""
            SELECT name FROM sqlite_master 
            WHERE type='table' AND name='worklog'
        """)
        if not cursor.fetchone():
            return False
        
        # Check indexes exist
        cursor.execute("""
            SELECT COUNT(*) FROM sqlite_master 
            WHERE type='index' AND name LIKE 'idx_worklog%'
        """)
        index_count = cursor.fetchone()[0]
        if index_count < 4:
            return False
        
        # Check views exist
        cursor.execute("""
            SELECT COUNT(*) FROM sqlite_master 
            WHERE type='view'
        """)
        view_count = cursor.fetchone()[0]
        if view_count < 2:
            return False
        
        return True
    finally:
        conn.close()


def main():
    parser = argparse.ArgumentParser(
        description="Initialize the Agent Worklog database"
    )
    parser.add_argument(
        "--db-path",
        type=Path,
        default=get_default_db_path(),
        help="Path to the database file (default: ./worklog.db)"
    )
    parser.add_argument(
        "--force",
        action="store_true",
        help="Recreate database even if it exists"
    )
    
    args = parser.parse_args()
    
    if args.db_path.exists() and not args.force:
        print(f"Database already exists: {args.db_path}")
        if verify_database(args.db_path):
            print("Database schema verified OK.")
        else:
            print("Warning: Database exists but schema may be incomplete.")
            print("Run with --force to recreate.")
        return
    
    if args.force and args.db_path.exists():
        os.remove(args.db_path)
        print(f"Removed existing database: {args.db_path}")
    
    init_database(args.db_path)
    
    if verify_database(args.db_path):
        print("Database schema verified OK.")
    else:
        print("Warning: Database may not have initialized correctly.")


if __name__ == "__main__":
    main()