CREATE DATABASE IF NOT EXISTS cofihst_web
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE cofihst_web;

CREATE TABLE IF NOT EXISTS utilizadores (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(150) NOT NULL,
    username VARCHAR(80) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    cargo ENUM('CEO', 'Admin', 'Gestor', 'Medico') NOT NULL,
    ativo TINYINT(1) NOT NULL DEFAULT 1,
    criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS empresas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(180) NOT NULL UNIQUE,
    nif VARCHAR(30),
    telefone VARCHAR(50),
    email VARCHAR(150),
    morada TEXT,
    ativo TINYINT(1) NOT NULL DEFAULT 1,
    criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS utentes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    numero_utente VARCHAR(50),
    nome VARCHAR(180) NOT NULL,
    data_nascimento DATE,
    sexo VARCHAR(30),
    numero_identificacao VARCHAR(80),
    nif VARCHAR(30),
    telefone VARCHAR(50),
    email VARCHAR(150),
    morada TEXT,
    codigo_postal VARCHAR(30),
    localidade VARCHAR(120),
    empresa_id INT,
    departamento VARCHAR(120),
    funcao VARCHAR(120),
    data_admissao DATE,
    tipo_exame VARCHAR(120),
    data_ultimo_exame DATE,
    data_proximo_exame DATE,
    observacoes TEXT,
    ativo TINYINT(1) NOT NULL DEFAULT 1,
    criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (empresa_id) REFERENCES empresas(id)
);

CREATE TABLE IF NOT EXISTS marcacoes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    utente_id INT NOT NULL,
    medico_id INT,
    data_marcacao DATE NOT NULL,
    hora_marcacao TIME NOT NULL,
    tipo_exame VARCHAR(120),
    estado ENUM('Marcado', 'Em atendimento', 'Concluido', 'Cancelado', 'Faltou') NOT NULL DEFAULT 'Marcado',
    observacoes TEXT,
    criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (utente_id) REFERENCES utentes(id),
    FOREIGN KEY (medico_id) REFERENCES utilizadores(id)
);

CREATE TABLE IF NOT EXISTS fornecedores (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(180) NOT NULL,
    email VARCHAR(180),
    telefone VARCHAR(80),
    observacoes TEXT,
    ativo TINYINT(1) NOT NULL DEFAULT 1,
    criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS consumiveis (
    id INT AUTO_INCREMENT PRIMARY KEY,
    codigo VARCHAR(80),
    categoria VARCHAR(180),
    nome_produto VARCHAR(180) NOT NULL,
    unidade VARCHAR(80) NOT NULL DEFAULT 'unidade',
    quantidade_stock INT NOT NULL DEFAULT 0,
    stock_minimo INT NOT NULL DEFAULT 0,
    preco_unitario DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    tipo_embalagem VARCHAR(80) DEFAULT 'unidade',
    unidades_por_embalagem INT NOT NULL DEFAULT 1,
    fornecedor_id INT,
    ativo TINYINT(1) NOT NULL DEFAULT 1,
    criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (fornecedor_id) REFERENCES fornecedores(id)
);

CREATE TABLE IF NOT EXISTS consumiveis_usados_consulta (
    id INT AUTO_INCREMENT PRIMARY KEY,
    marcacao_id INT,
    utente_id INT NOT NULL,
    medico_id INT,
    consumivel_id INT NOT NULL,
    quantidade_usada INT NOT NULL,
    unidade VARCHAR(80),
    preco_unitario DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    data_registo DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (marcacao_id) REFERENCES marcacoes(id),
    FOREIGN KEY (utente_id) REFERENCES utentes(id),
    FOREIGN KEY (medico_id) REFERENCES utilizadores(id),
    FOREIGN KEY (consumivel_id) REFERENCES consumiveis(id)
);

CREATE TABLE IF NOT EXISTS movimentos_stock (
    id INT AUTO_INCREMENT PRIMARY KEY,
    consumivel_id INT NOT NULL,
    tipo_movimento VARCHAR(80) NOT NULL,
    quantidade INT NOT NULL,
    stock_antes INT NOT NULL,
    stock_depois INT NOT NULL,
    utilizador_id INT,
    origem VARCHAR(120),
    data_movimento DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (consumivel_id) REFERENCES consumiveis(id),
    FOREIGN KEY (utilizador_id) REFERENCES utilizadores(id)
);

CREATE TABLE IF NOT EXISTS historico_acoes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    utilizador_id INT,
    utilizador_nome VARCHAR(150),
    cargo VARCHAR(80),
    acao VARCHAR(180) NOT NULL,
    modulo VARCHAR(120),
    entidade VARCHAR(180),
    id_entidade INT,
    valor_antigo TEXT,
    valor_novo TEXT,
    data_acao DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (utilizador_id) REFERENCES utilizadores(id)
);

CREATE TABLE IF NOT EXISTS notificacoes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    titulo VARCHAR(180) NOT NULL,
    mensagem TEXT NOT NULL,
    tipo VARCHAR(80),
    cargo_destino VARCHAR(80),
    lida TINYINT(1) NOT NULL DEFAULT 0,
    criada_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);