-- https://www.phpmyadmin.net/
--
-- Tempo de geração: 25/09/2025 às 17:02
-- Versão do servidor: 8.0.42
-- Versão do PHP: 8.2.12

START TRANSACTION;



--
-- Banco de dados: `compras_solicitacoes`
--

-- --------------------------------------------------------

--
-- Estrutura para tabela `approvals`
--

CREATE TABLE `approvals` (
  `id` int NOT NULL,
  `request_id` int NOT NULL,
  `approver_id` int NOT NULL,
  `decision` enum('APROVAR','REPROVAR') NOT NULL,
  `comments` varchar(255) DEFAULT NULL,
  `decided_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Despejando dados para a tabela `approvals`
--

INSERT INTO `approvals` (`id`, `request_id`, `approver_id`, `decision`, `comments`, `decided_at`) VALUES
(1, 2, 1, 'APROVAR', '', '2025-09-24 11:36:34'),
(2, 3, 1, 'APROVAR', '', '2025-09-24 11:38:03'),
(3, 4, 1, 'APROVAR', '', '2025-09-24 12:23:40'),
(4, 1, 1, 'APROVAR', '', '2025-09-24 12:25:34'),
(5, 5, 1, 'APROVAR', '', '2025-09-24 12:33:53'),
(6, 6, 1, 'APROVAR', '', '2025-09-24 12:35:45'),
(7, 7, 1, 'APROVAR', '', '2025-09-24 12:53:35'),
(8, 8, 2, 'APROVAR', '', '2025-09-24 13:29:12'),
(9, 9, 1, 'APROVAR', '', '2025-09-24 14:00:31'),
(10, 10, 2, 'APROVAR', '', '2025-09-24 19:18:14'),
(11, 11, 5, 'APROVAR', '', '2025-09-24 19:30:11'),
(12, 13, 1, 'APROVAR', '', '2025-09-25 10:49:34'),
(13, 14, 1, 'APROVAR', '', '2025-09-25 11:11:13'),
(14, 16, 2, 'APROVAR', '', '2025-09-25 12:47:32'),
(15, 17, 1, 'APROVAR', '', '2025-09-25 13:34:10'),
(16, 15, 1, 'APROVAR', '', '2025-09-25 13:41:45'),
(17, 18, 1, 'APROVAR', '', '2025-09-25 14:16:21');

-- --------------------------------------------------------

--
-- Estrutura para tabela `departments`
--

CREATE TABLE `departments` (
  `id` int NOT NULL,
  `name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Despejando dados para a tabela `departments`
--

INSERT INTO `departments` (`id`, `name`) VALUES
(1, 'Comercial'),
(2, 'Compras'),
(3, 'T.I'),
(4, 'Industrial'),
(5, 'RH'),
(6, 'DETEC'),
(7, 'EXPEDIÇÃO');

-- --------------------------------------------------------

--
-- Estrutura para tabela `requests`
--

CREATE TABLE `requests` (
  `id` int NOT NULL,
  `requester_id` int NOT NULL,
  `department_id` int DEFAULT NULL,
  `needed_by` date DEFAULT NULL,
  `justification` text,
  `cost_center` varchar(60) DEFAULT NULL,
  `priority` enum('NORMAL','URGENTE','EMERGENCIAL') NOT NULL DEFAULT 'NORMAL',
  `status` enum('ENVIADA','APROVADA','REPROVADA','FINALIZADA','CANCELADA') NOT NULL DEFAULT 'ENVIADA',
  `stage` enum('COTACAO','COMPRA','ENTREGA') NOT NULL DEFAULT 'COTACAO',
  `stage_entered_cotacao` datetime DEFAULT NULL,
  `stage_entered_compra` datetime DEFAULT NULL,
  `stage_entered_entrega` datetime DEFAULT NULL,
  `total_amount` decimal(12,2) NOT NULL DEFAULT '0.00',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `approved_at` datetime DEFAULT NULL,
  `finalized_at` datetime DEFAULT NULL,
  `final_obs` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Despejando dados para a tabela `requests`
--

INSERT INTO `requests` (`id`, `requester_id`, `department_id`, `needed_by`, `justification`, `cost_center`, `priority`, `status`, `stage`, `stage_entered_cotacao`, `stage_entered_compra`, `stage_entered_entrega`, `total_amount`, `created_at`, `approved_at`, `finalized_at`, `final_obs`) VALUES
(1, 1, 4, '2025-09-29', '', NULL, 'NORMAL', 'FINALIZADA', 'COTACAO', NULL, NULL, NULL, 0.00, '2025-09-24 10:53:46', '2025-09-24 09:25:34', '2025-09-24 09:25:48', ''),
(2, 1, 1, '2025-09-26', 'RES', NULL, 'NORMAL', 'FINALIZADA', 'COTACAO', NULL, NULL, NULL, 0.00, '2025-09-24 11:36:18', '2025-09-24 08:36:34', '2025-09-24 09:22:56', ''),
(3, 1, 4, '2025-09-30', 'PRODUCAO 3512151', NULL, 'NORMAL', 'FINALIZADA', 'COTACAO', NULL, NULL, NULL, 0.00, '2025-09-24 11:37:25', '2025-09-24 08:38:03', '2025-09-24 09:23:23', ''),
(4, 1, 3, '2025-09-24', 'FONTE DO SERVIDOR QUEIMOU', '35122002', 'URGENTE', 'FINALIZADA', 'COTACAO', NULL, NULL, NULL, 0.00, '2025-09-24 12:20:04', '2025-09-24 09:23:40', '2025-09-24 09:23:56', ''),
(5, 1, 1, '2025-09-30', 'NOTEBOOK PARA VENDEDOR DE ARACAJU', '101001', 'URGENTE', 'FINALIZADA', 'COTACAO', NULL, NULL, NULL, 0.00, '2025-09-24 12:33:31', '2025-09-24 09:33:53', '2025-09-24 09:34:21', ''),
(6, 1, 4, '2025-09-30', 'CASCA DE SURURU', '1025525', 'NORMAL', 'FINALIZADA', 'COTACAO', NULL, NULL, NULL, 0.00, '2025-09-24 12:35:39', '2025-09-24 09:35:45', '2025-09-24 09:35:59', ''),
(7, 1, 4, '2025-09-25', '', '10122502', 'URGENTE', 'FINALIZADA', 'ENTREGA', NULL, NULL, NULL, 0.00, '2025-09-24 12:52:48', '2025-09-24 09:53:35', '2025-09-25 09:22:29', ''),
(8, 3, 4, '2025-09-25', 'RESINA ESTÁ EM NIVEL MINIMO', '36062626', 'URGENTE', 'APROVADA', 'ENTREGA', NULL, '2025-09-25 11:48:09', '2025-09-25 10:07:05', 0.00, '2025-09-24 13:28:07', '2025-09-24 10:29:12', NULL, NULL),
(9, 3, 3, '2025-09-24', 'fonte', '1001001', 'URGENTE', 'FINALIZADA', 'ENTREGA', '2025-09-25 11:08:56', '2025-09-25 11:08:58', '2025-09-25 11:10:40', 0.00, '2025-09-24 13:38:58', '2025-09-24 11:00:31', '2025-09-25 11:12:28', ''),
(10, 2, 4, '2025-09-30', '', '', 'NORMAL', 'APROVADA', 'ENTREGA', NULL, NULL, NULL, 0.00, '2025-09-24 19:17:35', '2025-09-24 16:18:14', NULL, NULL),
(11, 3, 3, '2025-09-25', 'MEMORIA RAM', '', 'URGENTE', 'APROVADA', 'COMPRA', NULL, '2025-09-25 09:55:53', '2025-09-25 09:55:58', 0.00, '2025-09-24 19:26:39', '2025-09-24 16:30:11', NULL, NULL),
(13, 1, 4, '2025-09-25', 'dasadasd', '', 'NORMAL', 'APROVADA', 'COMPRA', NULL, '2025-09-25 11:09:07', NULL, 0.00, '2025-09-25 10:46:23', '2025-09-25 07:49:34', NULL, NULL),
(14, 3, 1, '2025-09-26', 'quadro', '', 'NORMAL', 'FINALIZADA', 'COTACAO', NULL, NULL, NULL, 0.00, '2025-09-25 11:09:35', '2025-09-25 08:11:13', '2025-09-25 08:26:55', ''),
(15, 3, 3, '2025-09-25', 'HD SSD 500GB', '', 'NORMAL', 'APROVADA', 'COMPRA', '2025-09-25 10:41:45', '2025-09-25 11:09:40', NULL, 0.00, '2025-09-25 11:14:28', '2025-09-25 10:41:45', NULL, NULL),
(16, 2, 4, '2025-09-30', 'TAMPA PARA BALDES CASA LUSSO', '', 'NORMAL', 'FINALIZADA', 'COTACAO', NULL, NULL, NULL, 0.00, '2025-09-25 12:47:15', '2025-09-25 09:47:32', '2025-09-25 10:12:10', ''),
(17, 3, 4, '2025-09-30', '', '', 'NORMAL', 'APROVADA', 'COTACAO', '2025-09-25 10:34:10', NULL, NULL, 0.00, '2025-09-25 13:34:00', '2025-09-25 10:34:10', NULL, NULL),
(18, 3, 5, '2025-09-30', 'FARDAMENTO PARA INDUSTRIA', '', 'URGENTE', 'FINALIZADA', 'ENTREGA', '2025-09-25 11:16:21', '2025-09-25 11:19:24', '2025-09-25 11:19:53', 0.00, '2025-09-25 14:14:27', '2025-09-25 11:16:21', '2025-09-25 11:20:17', 'fOI COMPRADIASDUHASDUHASDUHUHSAD');

-- --------------------------------------------------------

--
-- Estrutura para tabela `request_attachments`
--

CREATE TABLE `request_attachments` (
  `id` int NOT NULL,
  `request_id` int NOT NULL,
  `original_name` varchar(255) NOT NULL,
  `stored_name` varchar(255) NOT NULL,
  `mime_type` varchar(120) DEFAULT NULL,
  `size_bytes` bigint DEFAULT NULL,
  `uploaded_by` int NOT NULL,
  `uploaded_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Despejando dados para a tabela `request_attachments`
--

INSERT INTO `request_attachments` (`id`, `request_id`, `original_name`, `stored_name`, `mime_type`, `size_bytes`, `uploaded_by`, `uploaded_at`) VALUES
(1, 9, 'PEDIDO 335320.pdf', '0c2326f048fc03e0.pdf', 'application/pdf', 1081719, 1, '2025-09-25 11:09:27'),
(2, 9, 'PEDIDO 335320 (1).pdf', '2e150f04c70bb9a1.pdf', 'application/pdf', 1081719, 1, '2025-09-25 11:09:58'),
(3, 18, 'PEDIDO 335320 (1).pdf', '59d5ae5903d0e0ff.pdf', 'application/pdf', 1081719, 1, '2025-09-25 11:19:13');

-- --------------------------------------------------------

--
-- Estrutura para tabela `request_items`
--

CREATE TABLE `request_items` (
  `id` int NOT NULL,
  `request_id` int NOT NULL,
  `item_desc` varchar(255) NOT NULL,
  `product_code` varchar(60) DEFAULT NULL,
  `qty` decimal(12,3) NOT NULL DEFAULT '1.000',
  `unit` varchar(20) DEFAULT NULL,
  `cost_center` varchar(60) DEFAULT NULL,
  `unit_price` decimal(12,2) NOT NULL DEFAULT '0.00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Despejando dados para a tabela `request_items`
--

INSERT INTO `request_items` (`id`, `request_id`, `item_desc`, `product_code`, `qty`, `unit`, `cost_center`, `unit_price`) VALUES
(1, 1, 'MALHA 325', NULL, 1000.000, 'KG', NULL, 0.00),
(2, 2, 'RESINA 401', NULL, 5000.000, 'KG', NULL, 0.00),
(3, 3, 'RESINA 402', NULL, 5000.000, 'KG', NULL, 0.00),
(4, 4, 'FONTE ATX 750W', NULL, 1.000, 'un', NULL, 0.00),
(5, 5, 'NOTEBOOK', NULL, 1.000, 'un', NULL, 0.00),
(6, 6, 'CASCA DE SURURU', NULL, 10000.000, 'KG', NULL, 0.00),
(7, 7, 'RESINA 403', NULL, 5000.000, 'KG', NULL, 0.00),
(8, 8, 'RESINA 401', NULL, 10000.000, 'KG', NULL, 0.00),
(9, 9, 'MEMORIA RAM 16GB', NULL, 1.000, '', NULL, 0.00),
(10, 10, 'TAMPA BALDE 18L', NULL, 500.000, 'UN', '101001', 0.00),
(11, 11, 'MEMORIA RAM 15GB', NULL, 1.000, 'UN', '101001', 0.00),
(13, 13, 'MALHA 325', NULL, 1000.000, 'UN', '102003', 0.00),
(14, 14, 'QUADRO BRANCO', NULL, 1.000, 'UN', '102001', 0.00),
(16, 16, 'TAMPA BALDE', '5301.0001A0A.4', 1.000, 'UN', '53001', 0.00),
(17, 17, 'BALDE NACOR', '8301.0001A0A.4', 1000.000, 'UN', '', 0.00),
(18, 15, 'HD SSD 500GB', '', 1.000, 'UN', '103001', 0.00),
(19, 15, 'MEMORIA RAM', '', 1.000, 'UN', '103001', 0.00),
(20, 18, 'BATA', '', 100.000, 'UN', '1001001', 0.00),
(21, 18, 'MASCARA', '', 200.000, 'UN', '1001002', 0.00);

--
-- Acionadores `request_items`
--
DELIMITER $$
CREATE TRIGGER `trg_items_ad` AFTER DELETE ON `request_items` FOR EACH ROW BEGIN
  UPDATE requests r
    JOIN (SELECT COALESCE(SUM(qty*unit_price),0) t FROM request_items WHERE request_id = OLD.request_id) x
      ON 1=1
  SET r.total_amount = x.t
  WHERE r.id = OLD.request_id;
END
$$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER `trg_items_ai` AFTER INSERT ON `request_items` FOR EACH ROW BEGIN
  UPDATE requests r
    JOIN (SELECT COALESCE(SUM(qty*unit_price),0) t FROM request_items WHERE request_id = NEW.request_id) x
      ON 1=1
  SET r.total_amount = x.t
  WHERE r.id = NEW.request_id;
END
$$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER `trg_items_au` AFTER UPDATE ON `request_items` FOR EACH ROW BEGIN
  UPDATE requests r
    JOIN (SELECT COALESCE(SUM(qty*unit_price),0) t FROM request_items WHERE request_id = NEW.request_id) x
      ON 1=1
  SET r.total_amount = x.t
  WHERE r.id = NEW.request_id;
END
$$
DELIMITER ;

-- --------------------------------------------------------

--
-- Estrutura para tabela `request_stage_history`
--

CREATE TABLE `request_stage_history` (
  `id` int NOT NULL,
  `request_id` int NOT NULL,
  `old_stage` enum('COTACAO','COMPRA','ENTREGA') DEFAULT NULL,
  `new_stage` enum('COTACAO','COMPRA','ENTREGA') NOT NULL,
  `changed_by` int NOT NULL,
  `changed_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Despejando dados para a tabela `request_stage_history`
--

INSERT INTO `request_stage_history` (`id`, `request_id`, `old_stage`, `new_stage`, `changed_by`, `changed_at`) VALUES
(1, 11, 'COTACAO', 'COMPRA', 2, '2025-09-25 09:55:53'),
(2, 11, 'COMPRA', 'ENTREGA', 2, '2025-09-25 09:55:58'),
(3, 8, 'COMPRA', 'ENTREGA', 2, '2025-09-25 10:07:05'),
(4, 17, NULL, 'COTACAO', 1, '2025-09-25 10:34:10'),
(5, 15, NULL, 'COTACAO', 1, '2025-09-25 10:41:45'),
(6, 9, 'COMPRA', 'COTACAO', 1, '2025-09-25 11:08:56'),
(7, 9, 'COTACAO', 'COMPRA', 1, '2025-09-25 11:08:58'),
(8, 13, 'COTACAO', 'COMPRA', 1, '2025-09-25 11:09:07'),
(9, 9, 'COMPRA', 'COTACAO', 1, '2025-09-25 11:09:18'),
(10, 9, 'COTACAO', 'COMPRA', 1, '2025-09-25 11:09:30'),
(11, 15, 'COTACAO', 'COMPRA', 1, '2025-09-25 11:09:40'),
(12, 8, 'ENTREGA', 'ENTREGA', 1, '2025-09-25 11:10:06'),
(13, 11, 'ENTREGA', 'COMPRA', 1, '2025-09-25 11:10:10'),
(14, 9, 'COMPRA', 'ENTREGA', 1, '2025-09-25 11:10:40'),
(15, 18, NULL, 'COTACAO', 1, '2025-09-25 11:16:21'),
(16, 18, 'COTACAO', 'COMPRA', 1, '2025-09-25 11:19:24'),
(17, 18, 'COMPRA', 'ENTREGA', 1, '2025-09-25 11:19:53'),
(18, 8, 'ENTREGA', 'COMPRA', 1, '2025-09-25 11:48:09'),
(19, 8, 'COMPRA', 'ENTREGA', 1, '2025-09-25 11:48:10');

-- --------------------------------------------------------

--
-- Estrutura stand-in para view `v_request_totals`
-- (Veja abaixo para a visão atual)
--
CREATE TABLE `v_request_totals` (
`calc_total` decimal(46,5)
,`request_id` int
);

-- --------------------------------------------------------

--
-- Estrutura para view `v_request_totals`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_request_totals`  AS SELECT `r`.`id` AS `request_id`, coalesce(sum((`ri`.`qty` * `ri`.`unit_price`)),0) AS `calc_total` FROM (`requests` `r` left join `request_items` `ri` on((`ri`.`request_id` = `r`.`id`))) GROUP BY `r`.`id` ;

--
-- Índices para tabelas despejadas
--

--
-- Índices de tabela `approvals`
--
ALTER TABLE `approvals`
  ADD PRIMARY KEY (`id`),
  ADD KEY `request_id` (`request_id`),
  ADD KEY `approver_id` (`approver_id`);

--
-- Índices de tabela `departments`
--
ALTER TABLE `departments`
  ADD PRIMARY KEY (`id`);

--
-- Índices de tabela `requests`
--
ALTER TABLE `requests`
  ADD PRIMARY KEY (`id`),
  ADD KEY `requester_id` (`requester_id`),
  ADD KEY `department_id` (`department_id`);

--
-- Índices de tabela `request_attachments`
--
ALTER TABLE `request_attachments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `request_id` (`request_id`);

--
-- Índices de tabela `request_items`
--
ALTER TABLE `request_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `request_id` (`request_id`);

--
-- Índices de tabela `request_stage_history`
--
ALTER TABLE `request_stage_history`
  ADD PRIMARY KEY (`id`),
  ADD KEY `request_id` (`request_id`);

--
-- AUTO_INCREMENT para tabelas despejadas
--

--
-- AUTO_INCREMENT de tabela `approvals`
--
ALTER TABLE `approvals`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=18;

--
-- AUTO_INCREMENT de tabela `departments`
--
ALTER TABLE `departments`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

--
-- AUTO_INCREMENT de tabela `requests`
--
ALTER TABLE `requests`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=19;

--
-- AUTO_INCREMENT de tabela `request_attachments`
--
ALTER TABLE `request_attachments`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT de tabela `request_items`
--
ALTER TABLE `request_items`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=22;

--
-- AUTO_INCREMENT de tabela `request_stage_history`
--
ALTER TABLE `request_stage_history`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=20;

--
-- Restrições para tabelas despejadas
--

--
-- Restrições para tabelas `approvals`
--
ALTER TABLE `approvals`
  ADD CONSTRAINT `approvals_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`),
  ADD CONSTRAINT `approvals_ibfk_2` FOREIGN KEY (`approver_id`) REFERENCES `users` (`id`);

--
-- Restrições para tabelas `requests`
--
ALTER TABLE `requests`
  ADD CONSTRAINT `requests_ibfk_1` FOREIGN KEY (`requester_id`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `requests_ibfk_2` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`);

--
-- Restrições para tabelas `request_attachments`
--
ALTER TABLE `request_attachments`
  ADD CONSTRAINT `request_attachments_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE;

--
-- Restrições para tabelas `request_items`
--
ALTER TABLE `request_items`
  ADD CONSTRAINT `request_items_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE;

--
-- Restrições para tabelas `request_stage_history`
--
ALTER TABLE `request_stage_history`
  ADD CONSTRAINT `fk_rsh_req` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE;
COMMIT;

