-- Tracciatore schema (UTF8MB4, PHP 8.3, mysqli)
CREATE DATABASE IF NOT EXISTS pensam70_tracciatore CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE pensam70_tracciatore;

-- usuarios
CREATE TABLE IF NOT EXISTS usuarios (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  nome_completo VARCHAR(150) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  nivel TINYINT NOT NULL DEFAULT 1, -- 1=leitor,2=usuario,3=gestor,4=admin
  is_master TINYINT(1) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  email_confirmado TINYINT(1) NOT NULL DEFAULT 0,
  senha_hash VARCHAR(255) DEFAULT NULL,
  token_confirmacao VARCHAR(64) DEFAULT NULL,
  token_recuperacao VARCHAR(64) DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- categorias
CREATE TABLE IF NOT EXISTS categorias (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(100) NOT NULL,
  parent_id INT UNSIGNED DEFAULT NULL,
  FOREIGN KEY (parent_id) REFERENCES categorias(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- projetos
CREATE TABLE IF NOT EXISTS projetos (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(150) NOT NULL,
  tipo_projeto VARCHAR(80) DEFAULT NULL,
  descricao TEXT
) ENGINE=InnoDB;

-- tags
CREATE TABLE IF NOT EXISTS tags (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(80) NOT NULL UNIQUE
) ENGINE=InnoDB;

-- tarefas
CREATE TABLE IF NOT EXISTS tarefas (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(150) NOT NULL,
  info TEXT,
  validada TINYINT(1) NOT NULL DEFAULT 0,
  status TINYINT NOT NULL DEFAULT 0, -- 0:a iniciar; 1:em andamento; 2:concluída; 3:parcial; 4:abandonada
  usuario_inclusao INT UNSIGNED NOT NULL,
  data_hora_inclusao DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  prazo DATE DEFAULT NULL,
  data_hora_conclusao DATETIME DEFAULT NULL,
  prioridade TINYINT NOT NULL DEFAULT 2, -- 1 alta; 2 média; 3 baixa
  tempo_previsto_min INT UNSIGNED DEFAULT 0,
  tempo_realizado_min INT UNSIGNED DEFAULT 0,
  categoria_id INT UNSIGNED DEFAULT NULL,
  projeto_id INT UNSIGNED DEFAULT NULL,
  tags_csv VARCHAR(500) DEFAULT NULL,
  FOREIGN KEY (usuario_inclusao) REFERENCES usuarios(id) ON DELETE RESTRICT,
  FOREIGN KEY (categoria_id) REFERENCES categorias(id) ON DELETE SET NULL,
  FOREIGN KEY (projeto_id) REFERENCES projetos(id) ON DELETE SET NULL,
  INDEX (status), INDEX (validada), INDEX (prioridade), INDEX (prazo)
) ENGINE=InnoDB;

-- comunicacoes
CREATE TABLE IF NOT EXISTS comunicacoes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_tarefa INT UNSIGNED NOT NULL,
  id_usuario_origem INT UNSIGNED NOT NULL,
  ids_usuarios_destino VARCHAR(255) NOT NULL,
  importante TINYINT(1) NOT NULL DEFAULT 0,
  texto VARCHAR(500) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (id_tarefa) REFERENCES tarefas(id) ON DELETE CASCADE,
  FOREIGN KEY (id_usuario_origem) REFERENCES usuarios(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Usuário master
INSERT INTO usuarios (username, nome_completo, email, nivel, is_master, is_active, email_confirmado, senha_hash)
VALUES ('rodrigo', 'Rodrigo da Silva Wolfart', 'pj.rodrigo@gmail.com', 4, 1, 1, 1, NULL)
ON DUPLICATE KEY UPDATE email=email;