Spaces:
Sleeping
Sleeping
File size: 3,584 Bytes
6a7fb3c | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 | #!/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)
|