-- https://www.phpmyadmin.net/
--
-- Tempo de geração: 24/09/2025 às 22:21
-- 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');

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

--
-- 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');

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

--
-- 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',
  `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`, `total_amount`, `created_at`, `approved_at`, `finalized_at`, `final_obs`) VALUES
(1, 1, 4, '2025-09-29', '', NULL, 'NORMAL', 'FINALIZADA', 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', 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', 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', 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', 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', 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', 'APROVADA', 0.00, '2025-09-24 12:52:48', '2025-09-24 09:53:35', NULL, NULL),
(8, 3, 4, '2025-09-25', 'RESINA ESTÁ EM NIVEL MINIMO', '36062626', 'URGENTE', 'APROVADA', 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', 'APROVADA', 0.00, '2025-09-24 13:38:58', '2025-09-24 11:00:31', NULL, NULL),
(10, 2, 4, '2025-09-30', '', '', 'NORMAL', 'APROVADA', 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', 0.00, '2025-09-24 19:26:39', '2025-09-24 16:30:11', NULL, NULL);

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

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

CREATE TABLE `request_items` (
  `id` int NOT NULL,
  `request_id` int NOT NULL,
  `item_desc` varchar(255) NOT 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`, `qty`, `unit`, `cost_center`, `unit_price`) VALUES
(1, 1, 'MALHA 325', 1000.000, 'KG', NULL, 0.00),
(2, 2, 'RESINA 401', 5000.000, 'KG', NULL, 0.00),
(3, 3, 'RESINA 402', 5000.000, 'KG', NULL, 0.00),
(4, 4, 'FONTE ATX 750W', 1.000, 'un', NULL, 0.00),
(5, 5, 'NOTEBOOK', 1.000, 'un', NULL, 0.00),
(6, 6, 'CASCA DE SURURU', 10000.000, 'KG', NULL, 0.00),
(7, 7, 'RESINA 403', 5000.000, 'KG', NULL, 0.00),
(8, 8, 'RESINA 401', 10000.000, 'KG', NULL, 0.00),
(9, 9, 'MEMORIA RAM 16GB', 1.000, '', NULL, 0.00),
(10, 10, 'TAMPA BALDE 18L', 500.000, 'UN', '101001', 0.00),
(11, 11, 'MEMORIA RAM 15GB', 1.000, 'UN', '101001', 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 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_items`
--
ALTER TABLE `request_items`
  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=12;

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

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

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

--
-- 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_items`
--
ALTER TABLE `request_items`
  ADD CONSTRAINT `request_items_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE;
COMMIT;

