init_db.py

#!/usr/bin/env python3
"""
Script de inicialização automática da base de dados
Executado automaticamente ao iniciar a aplicação no Hugging Face Spaces
"""

import sqlite3
import bcrypt
from pathlib import Path

def init_database():
    """Inicializa a base de dados se não existir"""
    
    BASE_DIR = Path(__file__).parent
    DB_PATH = BASE_DIR / "db" / "app.db"
    
    # Se a base de dados já existe, não fazer nada
    if DB_PATH.exists():
        print("✅ Base de dados já existe")
        return
    
    # Criar diretório
    DB_PATH.parent.mkdir(parents=True, exist_ok=True)
    
    print("=" * 60)
    print("Inicialização da Base de Dados")
    print("=" * 60)
    
    # Conectar à BD
    conn = sqlite3.connect(DB_PATH)
    conn.execute("PRAGMA foreign_keys = ON")
    cur = conn.cursor()
    
    # Criar tabela users
    print("\n[1/4] Criando tabela 'users'...")
    cur.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT UNIQUE NOT NULL,
        password_hash TEXT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
    """)
    print("✅ Tabela 'users' criada")
    
    # Criar tabela tasks
    print("\n[2/4] Criando tabela 'tasks'...")
    cur.execute("""
    CREATE TABLE IF NOT EXISTS tasks (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT NOT NULL,
        task_code TEXT UNIQUE NOT NULL,
        task_name TEXT NOT NULL,
        task_date DATE NOT NULL,
        start_time TIMESTAMP NOT NULL,
        end_time TIMESTAMP NOT NULL,
        planned_hours REAL NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (username) REFERENCES users(username) ON DELETE CASCADE
    )
    """)
    print("✅ Tabela 'tasks' criada")
    
    # Criar tabela subtasks
    print("\n[3/4] Criando tabela 'subtasks'...")
    cur.execute("""
    CREATE TABLE IF NOT EXISTS subtasks (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        task_id INTEGER NOT NULL,
        subtask_code TEXT UNIQUE NOT NULL,
        subtask_name TEXT NOT NULL,
        start_time TIMESTAMP NOT NULL,
        end_time TIMESTAMP NOT NULL,
        planned_hours REAL NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE
    )
    """)
    print("✅ Tabela 'subtasks' criada")
    
    # Criar tabela task_logs
    print("\n[4/4] Criando tabela 'task_logs'...")
    cur.execute("""
    CREATE TABLE IF NOT EXISTS task_logs (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT NOT NULL,
        task_id INTEGER,
        activity TEXT NOT NULL,
        event_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (username) REFERENCES users(username) ON DELETE CASCADE,
        FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE
    )
    """)
    print("✅ Tabela 'task_logs' criada")
    
    # Criar utilizador de teste
    print("\n[5/5] Criando utilizador de teste...")
    username = "admin"
    password = "admin123"
    pw_hash = bcrypt.hashpw(password.encode(), bcrypt.gensalt()).decode()
    
    try:
        cur.execute(
            "INSERT INTO users (username, password_hash) VALUES (?, ?)",
            (username, pw_hash)
        )
        print(f"✅ Utilizador de teste criado:")
        print(f"   Username: {username}")
        print(f"   Password: {password}")
    except sqlite3.IntegrityError:
        print(f"⚠️  Utilizador '{username}' já existe")
    
    # Commit e fechar
    conn.commit()
    conn.close()
    
    print("\n" + "=" * 60)
    print("✅ BASE DE DADOS INICIALIZADA COM SUCESSO!")
    print("=" * 60)

if __name__ == "__main__":
    init_database()