00_criar_bd_e_user_teste.py

#!/usr/bin/env python3
"""
Script de inicialização da base de dados IA Mining
Versão com USERNAME em vez de user_id
"""

import sqlite3
import bcrypt
from pathlib import Path

# Paths
BASE_DIR = Path(__file__).parent
DB_PATH = BASE_DIR / "db" / "app.db"
DB_PATH.parent.mkdir(parents=True, exist_ok=True)

print("=" * 60)
print("IA MINING - Inicialização da Base de Dados")
print("Versão: USERNAME (em vez de user_id)")
print("=" * 60)

# Conectar à BD
conn = sqlite3.connect(DB_PATH)
conn.execute("PRAGMA foreign_keys = ON")  # Ativar foreign keys
cur = conn.cursor()

# Criar tabela users
print("\n[1/5] 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 (com username em vez de user_id)
print("\n[2/5] Criando tabela 'tasks' (com username)...")
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 (FK: username)")

# Criar tabela subtasks
print("\n[3/5] 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 (com username)
print("\n[4/5] Criando tabela 'task_logs' (com username)...")
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)
print("\n📋 Estrutura:")
print("   - tasks.username → FK para users.username")
print("   - task_logs.username → FK para users.username")
print("   - Códigos de tarefa: TAR_{username}_{seq}")
print("\nPróximos passos:")
print("1. Execute: python app.py")
print("2. Aceda: http://localhost:7860")
print(f"3. Faça login com: {username} / {password}")
print("=" * 60)