-- --------------------------------------------------------
-- Host:                         127.0.0.1
-- Server version:               8.0.30 - MySQL Community Server - GPL
-- Server OS:                    Win64
-- HeidiSQL Version:             12.1.0.6537
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


-- Dumping database structure for tracestock
CREATE DATABASE IF NOT EXISTS `tracestock` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `tracestock`;

-- Dumping structure for table tracestock.categorias_produtos
CREATE TABLE IF NOT EXISTS `categorias_produtos` (
  `id_categoria` int unsigned NOT NULL AUTO_INCREMENT,
  `nome_categoria` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `type_categoria` enum('restaurant','construct') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`id_categoria`),
  KEY `FK_categorias_produtos_server_data` (`type_categoria`),
  CONSTRAINT `FK_categorias_produtos_server_data` FOREIGN KEY (`type_categoria`) REFERENCES `server_data` (`type_empresa`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table tracestock.categorias_produtos: ~0 rows (approximately)

-- Dumping structure for table tracestock.clientes
CREATE TABLE IF NOT EXISTS `clientes` (
  `id_cliente` int unsigned NOT NULL AUTO_INCREMENT,
  `desconto_id` int unsigned NOT NULL,
  `nome` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `data_nascimento` date DEFAULT NULL,
  `email` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `nif` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `endereço` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `cod_postal` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `telefone` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `telefone_comercial` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id_cliente`) USING BTREE,
  UNIQUE KEY `nif` (`nif`),
  KEY `fk_get_desconto_id_by_table_desconto` (`desconto_id`),
  CONSTRAINT `fk_get_desconto_id_by_table_desconto` FOREIGN KEY (`desconto_id`) REFERENCES `desconto` (`id_desconto`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table tracestock.clientes: ~1 rows (approximately)

-- Dumping structure for table tracestock.desconto
CREATE TABLE IF NOT EXISTS `desconto` (
  `id_desconto` int unsigned NOT NULL AUTO_INCREMENT,
  `tipo_desconto` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `nome_desconto` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `valor` float unsigned DEFAULT NULL,
  PRIMARY KEY (`id_desconto`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table tracestock.desconto: ~1 rows (approximately)

-- Dumping structure for table tracestock.empresas
CREATE TABLE IF NOT EXISTS `empresas` (
  `id_empresa` int unsigned NOT NULL AUTO_INCREMENT,
  `id_type_empresa` int unsigned NOT NULL,
  `nome` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `razao_social` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `nif_cnpj` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `email_contato` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `telefone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `website` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `endereco_rua` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `endereco_numero` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `endereco_complemento` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `endereco_bairro` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `endereco_cidade` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `endereco_estado` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `endereco_cep` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `endereco_pais` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'Portugal',
  `active` tinyint unsigned NOT NULL DEFAULT '1',
  `criado_em` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `atualizado_em` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_empresa`) USING BTREE,
  UNIQUE KEY `nif_cnpj` (`nif_cnpj`),
  UNIQUE KEY `nome` (`nome`),
  KEY `fk_id_type_empresa` (`id_type_empresa`),
  CONSTRAINT `fk_id_type_empresa` FOREIGN KEY (`id_type_empresa`) REFERENCES `server_data` (`id_type_empresa`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table tracestock.empresas: ~3 rows (approximately)
INSERT INTO `empresas` (`id_empresa`, `id_type_empresa`, `nome`, `razao_social`, `nif_cnpj`, `email_contato`, `telefone`, `website`, `endereco_rua`, `endereco_numero`, `endereco_complemento`, `endereco_bairro`, `endereco_cidade`, `endereco_estado`, `endereco_cep`, `endereco_pais`, `active`, `criado_em`, `atualizado_em`) VALUES
	(1, 1, 'System', 'System', '000000000000', 'system@system.com', '91000000', 'www.tracestock.page.pd', 'localhost', 'localhost', 'localhost', 'localhost', 'localhost', 'localhost', 'localhost', 'localhost', 1, '2025-09-05 09:36:48', '2025-09-05 19:55:04');

-- Dumping structure for table tracestock.empresa_contactos
CREATE TABLE IF NOT EXISTS `empresa_contactos` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `telefone` varchar(100) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '0',
  `email` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table tracestock.empresa_contactos: ~0 rows (approximately)

-- Dumping structure for table tracestock.estoque_produtos
CREATE TABLE IF NOT EXISTS `estoque_produtos` (
  `id_produto_stock` int unsigned NOT NULL,
  `quantidade` int unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id_produto_stock`),
  UNIQUE KEY `id_produto_stock` (`id_produto_stock`),
  CONSTRAINT `fk_get_id_produto_by_table_produtos` FOREIGN KEY (`id_produto_stock`) REFERENCES `produtos` (`id_produto`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table tracestock.estoque_produtos: ~0 rows (approximately)

-- Dumping structure for procedure tracestock.inserir_empresa_retorno
DELIMITER //
CREATE PROCEDURE `inserir_empresa_retorno`(
    IN p_id_type_empresa INT,
    IN p_nome VARCHAR(150),
    IN p_razao_social VARCHAR(200),
    IN p_nif_cnpj VARCHAR(20),
    IN p_email_contato VARCHAR(150),
    IN p_telefone VARCHAR(20),
    IN p_website VARCHAR(150),
    IN p_endereco_rua VARCHAR(150),
    IN p_endereco_numero VARCHAR(10),
    IN p_endereco_complemento VARCHAR(50),
    IN p_endereco_bairro VARCHAR(100),
    IN p_endereco_cidade VARCHAR(100),
    IN p_endereco_estado VARCHAR(50),
    IN p_endereco_cep VARCHAR(20),
    IN p_endereco_pais VARCHAR(50),
    IN p_active TINYINT,
    OUT p_id_empresa INT
)
BEGIN
    INSERT INTO empresas (
        id_type_empresa,
        nome,
        razao_social,
        nif_cnpj,
        email_contato,
        telefone,
        website,
        endereco_rua,
        endereco_numero,
        endereco_complemento,
        endereco_bairro,
        endereco_cidade,
        endereco_estado,
        endereco_cep,
        endereco_pais,
        active
    ) VALUES (
        p_id_type_empresa,
        p_nome,
        p_razao_social,
        p_nif_cnpj,
        p_email_contato,
        p_telefone,
        p_website,
        p_endereco_rua,
        p_endereco_numero,
        p_endereco_complemento,
        p_endereco_bairro,
        p_endereco_cidade,
        p_endereco_estado,
        p_endereco_cep,
        p_endereco_pais,
        p_active
    );

    -- Retorna o id gerado
    SET p_id_empresa = LAST_INSERT_ID();
END//
DELIMITER ;

-- Dumping structure for procedure tracestock.inserir_utilizador
DELIMITER //
CREATE PROCEDURE `inserir_utilizador`(
	IN `p_id_empresa` INT,
	IN `p_telefone` VARCHAR(20),
	IN `p_foto` VARCHAR(255),
	IN `p_nome` VARCHAR(100),
	IN `p_apelido` VARCHAR(100),
	IN `p_email` VARCHAR(150),
	IN `p_pass` VARCHAR(255),
	IN `p_perms` VARCHAR(50),
	IN `p_active` TINYINT,
	OUT `p_id_user` INT
)
    DETERMINISTIC
BEGIN
    INSERT INTO utilizadores (
        id_empresa,
        telefone,
        foto,
        nome,
        apelido,
        email,
        pass,
        perms,
        active
    ) VALUES (
        p_id_empresa,
        p_telefone,
        p_foto,
        p_nome,
        p_apelido,
        p_email,
        p_pass,
        p_perms,
        p_active
    );

    -- Retorna o ID gerado
    SET p_id_user = LAST_INSERT_ID();
END//
DELIMITER ;

-- Dumping structure for table tracestock.logs_estoque
CREATE TABLE IF NOT EXISTS `logs_estoque` (
  `id_logs_estoque` int unsigned NOT NULL AUTO_INCREMENT,
  `id_user` int unsigned NOT NULL,
  `produto_id` int unsigned NOT NULL,
  `tipo_acao` enum('ENTRADA','SAIDA','AJUSTE','VENDA','COMPRA') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `quantidade` int unsigned NOT NULL,
  `quantidade_anterior` int unsigned DEFAULT NULL,
  `usuario` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `observacao` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `criado_em` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_logs_estoque`) USING BTREE,
  KEY `fk_produto_log` (`produto_id`),
  KEY `fk_get_log_stock_user_id_by_table_utilizadores` (`id_user`),
  CONSTRAINT `fk_get_log_stock_user_id_by_table_utilizadores` FOREIGN KEY (`id_user`) REFERENCES `utilizadores` (`id_user`),
  CONSTRAINT `fk_produto_log` FOREIGN KEY (`produto_id`) REFERENCES `produtos` (`id_produto`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table tracestock.logs_estoque: ~1 rows (approximately)

-- Dumping structure for table tracestock.logs_sistema
CREATE TABLE IF NOT EXISTS `logs_sistema` (
  `id_logs_stock` int unsigned NOT NULL AUTO_INCREMENT,
  `id_user_logs` int unsigned NOT NULL,
  `acao` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `descricao` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `ip` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `user_agent` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `sucesso` tinyint(1) DEFAULT '1',
  `criado_em` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_logs_stock`) USING BTREE,
  KEY `fk_get_user_id_logs_by_table_utilizadores` (`id_user_logs`),
  CONSTRAINT `fk_get_user_id_logs_by_table_utilizadores` FOREIGN KEY (`id_user_logs`) REFERENCES `utilizadores` (`id_user`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table tracestock.logs_sistema: ~0 rows (approximately)

-- Dumping structure for table tracestock.permissoes
CREATE TABLE IF NOT EXISTS `permissoes` (
  `id_perm` int unsigned NOT NULL AUTO_INCREMENT,
  `id_user` int unsigned NOT NULL,
  `dkey` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'user',
  `dvalue` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '1',
  PRIMARY KEY (`id_perm`) USING BTREE,
  KEY `fk_get_user_id_by_table_admin` (`id_user`),
  CONSTRAINT `fk_get_user_id_by_table_utilizadores` FOREIGN KEY (`id_user`) REFERENCES `utilizadores` (`id_user`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table tracestock.permissoes: ~0 rows (approximately)

-- Dumping structure for table tracestock.produtos
CREATE TABLE IF NOT EXISTS `produtos` (
  `id_produto` int unsigned NOT NULL AUTO_INCREMENT,
  `isbn` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `id_categoria_produto` int unsigned NOT NULL,
  `id_empresa` int unsigned NOT NULL DEFAULT '0',
  `nome` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `descricao` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `preco` decimal(10,2) unsigned NOT NULL,
  `criado_em` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `atualizado_em` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_produto`) USING BTREE,
  UNIQUE KEY `isbn` (`isbn`),
  KEY `fk_get_categoria_produto_id_by_table_categorias` (`id_categoria_produto`),
  KEY `fk_get_produto_empresa_id_by_table_empresas` (`id_empresa`),
  CONSTRAINT `fk_get_categoria_produto_id_by_table_categorias` FOREIGN KEY (`id_categoria_produto`) REFERENCES `categorias_produtos` (`id_categoria`),
  CONSTRAINT `fk_get_produto_empresa_id_by_table_empresas` FOREIGN KEY (`id_empresa`) REFERENCES `empresas` (`id_empresa`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table tracestock.produtos: ~2 rows (approximately)

-- Dumping structure for table tracestock.server_data
CREATE TABLE IF NOT EXISTS `server_data` (
  `id_type_empresa` int unsigned NOT NULL AUTO_INCREMENT,
  `type_empresa` enum('restaurant','construct') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`id_type_empresa`) USING BTREE,
  KEY `type_empresa` (`type_empresa`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Dumping data for table tracestock.server_data: ~2 rows (approximately)
INSERT INTO `server_data` (`id_type_empresa`, `type_empresa`) VALUES
	(1, 'restaurant'),
	(2, 'construct');

-- Dumping structure for table tracestock.utilizadores
CREATE TABLE IF NOT EXISTS `utilizadores` (
  `id_user` int unsigned NOT NULL AUTO_INCREMENT,
  `id_empresa` int unsigned NOT NULL DEFAULT '0',
  `telefone` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `foto` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `nome` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `apelido` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `email` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `pass` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `perms` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `active` int NOT NULL DEFAULT '1',
  PRIMARY KEY (`id_user`) USING BTREE,
  KEY `fk_get_empresa_id_by_table_empresas` (`id_empresa`),
  CONSTRAINT `fk_get_empresa_id_by_table_empresas` FOREIGN KEY (`id_empresa`) REFERENCES `empresas` (`id_empresa`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table tracestock.utilizadores: ~1 rows (approximately)
INSERT INTO `utilizadores` (`id_user`, `id_empresa`, `telefone`, `foto`, `nome`, `apelido`, `email`, `pass`, `perms`, `active`) VALUES
	(1, 1, '+351910000000', NULL, 'Administrador', 'de Sistema', '%2Tracestock2%', '961b92eb7db10fabbcd25d6a9f1fd650f149fb48362e010683670b5140f65222', '{}', 1);

-- Dumping structure for table tracestock.utilizadores_protegidos
CREATE TABLE IF NOT EXISTS `utilizadores_protegidos` (
  `id_user` int unsigned NOT NULL,
  PRIMARY KEY (`id_user`),
  CONSTRAINT `fk_get_utilizadores_protegidos` FOREIGN KEY (`id_user`) REFERENCES `utilizadores` (`id_user`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table tracestock.utilizadores_protegidos: ~1 rows (approximately)
INSERT INTO `utilizadores_protegidos` (`id_user`) VALUES
	(1);

-- Dumping structure for table tracestock.vendas
CREATE TABLE IF NOT EXISTS `vendas` (
  `id_venda` int unsigned NOT NULL AUTO_INCREMENT,
  `produto_id` int unsigned NOT NULL,
  `utilizador_id` int unsigned NOT NULL,
  `quantidade` int unsigned NOT NULL,
  `preco_unitario` decimal(10,2) unsigned NOT NULL,
  `desconto` decimal(10,2) unsigned DEFAULT '0.00',
  `total` decimal(12,2) DEFAULT NULL,
  `metodo_pagamento` enum('DINHEIRO','CARTAO','PIX','TRANSFERENCIA') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `status` enum('CONCLUIDA','PENDENTE','CANCELADA') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'CONCLUIDA',
  `criado_em` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_venda`) USING BTREE,
  KEY `fk_get_produto_by_table_produtos` (`produto_id`),
  KEY `fk_get_user_by_table_utilizadores` (`utilizador_id`),
  CONSTRAINT `fk_get_produto_by_table_produtos` FOREIGN KEY (`produto_id`) REFERENCES `produtos` (`id_produto`),
  CONSTRAINT `fk_get_user_by_table_utilizadores` FOREIGN KEY (`utilizador_id`) REFERENCES `utilizadores` (`id_user`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table tracestock.vendas: ~0 rows (approximately)

-- Dumping structure for view tracestock.view_all_empresas
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_all_empresas` (
	`id` INT(10) UNSIGNED NOT NULL,
	`nome_empresa` VARCHAR(150) NOT NULL COLLATE 'utf8mb4_general_ci',
	`dados_empresa` JSON NULL,
	`type_empresa` ENUM('restaurant','construct') NOT NULL COLLATE 'utf8mb4_general_ci',
	`active` VARCHAR(5) NOT NULL COLLATE 'utf8mb4_0900_ai_ci'
) ENGINE=MyISAM;

-- Dumping structure for view tracestock.view_all_users
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_all_users` (
	`id` INT(10) UNSIGNED NOT NULL,
	`nome` VARCHAR(200) NOT NULL COLLATE 'utf8mb4_general_ci',
	`apelido` VARCHAR(200) NOT NULL COLLATE 'utf8mb4_general_ci',
	`nome_empresa` VARCHAR(150) NOT NULL COLLATE 'utf8mb4_general_ci',
	`email` VARCHAR(200) NOT NULL COLLATE 'utf8mb4_general_ci',
	`telefone` VARCHAR(50) NULL COLLATE 'utf8mb4_general_ci',
	`tipo` VARCHAR(10) NOT NULL COLLATE 'utf8mb4_0900_ai_ci',
	`perms` JSON NULL,
	`active` VARCHAR(5) NOT NULL COLLATE 'utf8mb4_0900_ai_ci'
) ENGINE=MyISAM;

-- Dumping structure for view tracestock.view_clientes_desconto
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_clientes_desconto` (
	`id_cliente` INT(10) UNSIGNED NOT NULL,
	`desconto_id` INT(10) UNSIGNED NOT NULL,
	`desconto` VARCHAR(50) NULL COLLATE 'utf8mb4_general_ci'
) ENGINE=MyISAM;

-- Dumping structure for view tracestock.view_logs_estoque
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_logs_estoque` (
	`id` INT(10) UNSIGNED NOT NULL,
	`produto_nome` VARCHAR(100) NOT NULL COLLATE 'utf8mb4_general_ci',
	`nome` VARCHAR(401) NOT NULL COLLATE 'utf8mb4_general_ci',
	`quantidade_atual` INT(10) UNSIGNED NOT NULL,
	`diferenca` BIGINT(20) UNSIGNED NULL,
	`description` TEXT NULL COLLATE 'utf8mb4_0900_ai_ci'
) ENGINE=MyISAM;

-- Dumping structure for view tracestock.view_logs_system
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_logs_system` (
	`id` INT(10) UNSIGNED NOT NULL,
	`descricao` TEXT NULL COLLATE 'utf8mb4_general_ci',
	`sucesso` VARCHAR(5) NOT NULL COLLATE 'utf8mb4_0900_ai_ci',
	`nome` VARCHAR(401) NOT NULL COLLATE 'utf8mb4_general_ci'
) ENGINE=MyISAM;

-- Dumping structure for view tracestock.view_produtos_empresa
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_produtos_empresa` (
	`id` INT(10) UNSIGNED NOT NULL,
	`nome_empresa` VARCHAR(150) NOT NULL COLLATE 'utf8mb4_general_ci',
	`produtos` JSON NULL
) ENGINE=MyISAM;

-- Dumping structure for view tracestock.view_table_descontos
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_table_descontos` (
	`id` INT(10) UNSIGNED NOT NULL,
	`type` VARCHAR(50) NULL COLLATE 'utf8mb4_general_ci',
	`valor` FLOAT UNSIGNED NULL
) ENGINE=MyISAM;

-- Dumping structure for view tracestock.view_type_empresa
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_type_empresa` (
	`id` INT(10) UNSIGNED NOT NULL,
	`nome_empresa` VARCHAR(150) NOT NULL COLLATE 'utf8mb4_general_ci',
	`tipo_empresa` ENUM('restaurant','construct') NOT NULL COLLATE 'utf8mb4_general_ci',
	`active` VARCHAR(5) NOT NULL COLLATE 'utf8mb4_0900_ai_ci'
) ENGINE=MyISAM;

-- Dumping structure for view tracestock.view_user_permissions
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_user_permissions` (
	`id` INT(10) UNSIGNED NOT NULL,
	`perm` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_general_ci',
	`active` VARCHAR(5) NOT NULL COLLATE 'utf8mb4_0900_ai_ci'
) ENGINE=MyISAM;

-- Dumping structure for trigger tracestock.proteger_usuarios_delete
SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
DELIMITER //
CREATE TRIGGER `proteger_usuarios_delete` BEFORE DELETE ON `utilizadores` FOR EACH ROW BEGIN
    IF EXISTS (SELECT 1 FROM utilizadores_protegidos WHERE id_user = OLD.id_user) THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Este usuário está protegido e não pode ser deletado!';
    END IF;
END//
DELIMITER ;
SET SQL_MODE=@OLDTMP_SQL_MODE;

-- Dumping structure for trigger tracestock.proteger_usuarios_insert
SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
DELIMITER //
CREATE TRIGGER `proteger_usuarios_insert` BEFORE UPDATE ON `utilizadores` FOR EACH ROW BEGIN
    IF EXISTS (SELECT 1 FROM utilizadores_protegidos WHERE id_user = OLD.id_user) THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Este usuário está protegido e não pode ser inserido!';
    END IF;
END//
DELIMITER ;
SET SQL_MODE=@OLDTMP_SQL_MODE;

-- Dumping structure for trigger tracestock.proteger_usuarios_update
SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
DELIMITER //
CREATE TRIGGER `proteger_usuarios_update` BEFORE UPDATE ON `utilizadores` FOR EACH ROW BEGIN
    IF EXISTS (SELECT 1 FROM utilizadores_protegidos WHERE id_user = OLD.id_user) THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Este usuário está protegido e não pode ser atualizado!';
    END IF;
END//
DELIMITER ;
SET SQL_MODE=@OLDTMP_SQL_MODE;

-- Dumping structure for view tracestock.view_all_empresas
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `view_all_empresas`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `view_all_empresas` AS select `e`.`id_empresa` AS `id`,`e`.`nome` AS `nome_empresa`,json_object('morada',json_object('rua',`e`.`endereco_rua`,'numero',`e`.`endereco_numero`,'localidade',`e`.`endereco_complemento`,'freguesia',`e`.`endereco_bairro`,'concelho',`e`.`endereco_cidade`,'distrito',`e`.`endereco_estado`,'codPost',`e`.`endereco_cep`,'nacionalidade',`e`.`endereco_pais`),'identificacao',json_object('razao',`e`.`razao_social`,'nif',`e`.`nif_cnpj`),'contactos',json_object('email',`e`.`email_contato`,'telefone',`e`.`telefone`,'website',`e`.`website`)) AS `dados_empresa`,`srv`.`type_empresa` AS `type_empresa`,(case when `e`.`active` then 'true' else 'false' end) AS `active` from (`empresas` `e` join `server_data` `srv` on((`srv`.`id_type_empresa` = `e`.`id_type_empresa`))) where (`e`.`id_empresa` <> 1);

-- Dumping structure for view tracestock.view_all_users
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `view_all_users`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `view_all_users` AS select `u`.`id_user` AS `id`,(`u`.`nome` collate utf8mb4_general_ci) AS `nome`,(`u`.`apelido` collate utf8mb4_general_ci) AS `apelido`,(`e`.`nome` collate utf8mb4_general_ci) AS `nome_empresa`,(`u`.`email` collate utf8mb4_general_ci) AS `email`,(`u`.`telefone` collate utf8mb4_general_ci) AS `telefone`,'utilizador' AS `tipo`,coalesce(json_objectagg(`perms`.`dkey`,(case when (`perms`.`dvalue` = 1) then 'true' else 'false' end)),json_object()) AS `perms`,(case when (`u`.`active` = 1) then 'true' else 'false' end) AS `active` from ((`utilizadores` `u` join `empresas` `e` on((`e`.`id_empresa` = `u`.`id_empresa`))) left join `permissoes` `perms` on((`perms`.`id_user` = `u`.`id_user`))) group by `u`.`id_user`;

-- Dumping structure for view tracestock.view_clientes_desconto
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `view_clientes_desconto`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `view_clientes_desconto` AS select `c`.`id_cliente` AS `id_cliente`,`c`.`desconto_id` AS `desconto_id`,`d`.`nome_desconto` AS `desconto` from (`clientes` `c` join `desconto` `d` on((`c`.`desconto_id` = `d`.`id_desconto`)));

-- Dumping structure for view tracestock.view_logs_estoque
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `view_logs_estoque`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `view_logs_estoque` AS select `le`.`id_logs_estoque` AS `id`,`p`.`nome` AS `produto_nome`,concat(`u`.`nome`,' ',`u`.`apelido`) AS `nome`,`le`.`quantidade` AS `quantidade_atual`,(`le`.`quantidade_anterior` - `le`.`quantidade`) AS `diferenca`,`le`.`observacao` AS `description` from ((`logs_estoque` `le` join `utilizadores` `u` on((`le`.`id_user` = `u`.`id_user`))) join `produtos` `p` on((`le`.`produto_id` = `p`.`id_produto`)));

-- Dumping structure for view tracestock.view_logs_system
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `view_logs_system`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `view_logs_system` AS select `l`.`id_logs_stock` AS `id`,`l`.`descricao` AS `descricao`,(case when (`l`.`sucesso` = 1) then 'true' else 'false' end) AS `sucesso`,concat(`u`.`nome`,' ',`u`.`apelido`) AS `nome` from (`logs_sistema` `l` join `utilizadores` `u` on((`l`.`id_user_logs` = `u`.`id_user`))) order by `l`.`id_logs_stock` limit 100;

-- Dumping structure for view tracestock.view_produtos_empresa
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `view_produtos_empresa`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `view_produtos_empresa` AS select `e`.`id_empresa` AS `id`,`e`.`nome` AS `nome_empresa`,json_objectagg(`p`.`id_produto`,json_object('nome_produto',`p`.`nome`,'isbn_produto',`p`.`isbn`,'preco_produto',`p`.`preco`,'categoria_produto',`p`.`id_categoria_produto`,'quantidade_produto',`ep`.`quantidade`)) AS `produtos` from ((`empresas` `e` left join `produtos` `p` on((`p`.`id_empresa` = `e`.`id_empresa`))) left join `estoque_produtos` `ep` on((`ep`.`id_produto_stock` = `p`.`id_produto`))) group by `e`.`id_empresa`,`e`.`nome`;

-- Dumping structure for view tracestock.view_table_descontos
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `view_table_descontos`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `view_table_descontos` AS select `desconto`.`id_desconto` AS `id`,`desconto`.`tipo_desconto` AS `type`,`desconto`.`valor` AS `valor` from `desconto`;

-- Dumping structure for view tracestock.view_type_empresa
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `view_type_empresa`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `view_type_empresa` AS select `emp`.`id_empresa` AS `id`,`emp`.`nome` AS `nome_empresa`,`srv`.`type_empresa` AS `tipo_empresa`,(case when (`emp`.`active` = 1) then 'true' else 'false' end) AS `active` from (`empresas` `emp` join `server_data` `srv` on((`srv`.`id_type_empresa` = `emp`.`id_type_empresa`)));

-- Dumping structure for view tracestock.view_user_permissions
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `view_user_permissions`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `view_user_permissions` AS select `permissoes`.`id_user` AS `id`,`permissoes`.`dkey` AS `perm`,(case when (`permissoes`.`dvalue` = 1) then 'true' else 'false' end) AS `active` from `permissoes`;

/*!40103 SET TIME_ZONE=IFNULL(@OLD_TIME_ZONE, 'system') */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;
