-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Tempo de geração: 01/12/2025 às 05:07
-- Versão do servidor: 10.4.32-MariaDB
-- Versão do PHP: 8.2.12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Banco de dados: `condominio_portaria`
--

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

--
-- Estrutura para tabela `access_logs`
--

CREATE TABLE `access_logs` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `cpf` varchar(14) NOT NULL,
  `objective` enum('Visita','Serviço') NOT NULL,
  `destination` varchar(100) NOT NULL,
  `authorizer` varchar(255) NOT NULL,
  `guard_id` varchar(50) NOT NULL,
  `post_id` varchar(50) DEFAULT NULL,
  `timestamp` datetime DEFAULT current_timestamp(),
  `observations` text DEFAULT NULL,
  `exit_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Despejando dados para a tabela `access_logs`
--

INSERT INTO `access_logs` (`id`, `name`, `cpf`, `objective`, `destination`, `authorizer`, `guard_id`, `post_id`, `timestamp`, `observations`, `exit_time`) VALUES
(1, 'Emilio Iglesias', '07765303805', 'Visita', '115', 'Ulisses', 'portaria_01', NULL, '2025-11-30 17:39:37', 'degsdfgds', NULL),
(2, 'Carmen', '183', 'Visita', '115', 'Ulisses', 'portaria_01', NULL, '2025-11-30 17:54:51', NULL, NULL),
(3, 'Con', '64290778887', 'Serviço', '102', 'fulhao', 'portaria_01', NULL, '2025-11-30 18:17:52', NULL, NULL),
(4, 'Emilio Iglesias', '07765303805', 'Serviço', '43534', 'ddgd', 'portaria_01', NULL, '2025-11-30 18:18:30', NULL, NULL),
(5, 'fula', '036.609.910-82', 'Visita', '115', 'Ulisses', 'portaria_01', NULL, '2025-11-30 18:26:49', NULL, NULL),
(6, 'vssfgsdfg', '421.694.600-39', 'Serviço', '102', 'Ulisses', 'portaria_01', NULL, '2025-11-30 18:27:38', NULL, '2025-11-30 22:04:08'),
(7, 'asdfasdf', '386.538.670-90', 'Visita', '115', 'fulhao', 'portaria_01', NULL, '2025-11-30 18:41:40', NULL, '2025-11-30 22:16:44'),
(8, 'asdfasdf', '386.538.670-90', 'Visita', '102', 'ddgd', 'portaria_01', NULL, '2025-11-30 18:45:04', NULL, '2025-11-30 22:16:58'),
(9, 'kglkgll glklkglklg', '097.649.610-02', 'Visita', '115', 'Ulisses', 'portaria_01', NULL, '2025-11-30 21:27:48', 'sdghsdfg', '2025-11-30 22:03:57'),
(10, 'Consuelo', '642.907.788-87', 'Visita', '24', 'Autorizante', 'portaria_01', NULL, '2025-11-30 21:58:11', 'teste de acesso', NULL);

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

--
-- Estrutura para tabela `guards`
--

CREATE TABLE `guards` (
  `id` int(11) NOT NULL,
  `username` varchar(50) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `name` varchar(100) NOT NULL,
  `role` enum('PORTARIA','MASTER') DEFAULT 'PORTARIA',
  `status` enum('active','inactive') DEFAULT 'active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Despejando dados para a tabela `guards`
--

INSERT INTO `guards` (`id`, `username`, `password_hash`, `name`, `role`, `status`) VALUES
(1, 'admin', '$2y$10$J/NOgLJAJcLMZEjqO8bk0uJc8cB7miwUnsDssRXQTzWImlk/v8Pom', 'Admin Master', 'MASTER', 'active'),
(2, 'guarda', '$2y$10$J/NOgLJAJcLMZEjqO8bk0uJc8cB7miwUnsDssRXQTzWImlk/v8Pom', 'Guarda Diurno', 'PORTARIA', 'active'),
(3, 'p.silva', '$2y$10$W4wM2qQLnjG85RFGcqig4uZjGusEQ/Z/k/fJBI6gAJUJwfHZLlnkC', 'Paulo da Silva', 'PORTARIA', 'active');

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

--
-- Estrutura para tabela `packages`
--

CREATE TABLE `packages` (
  `id` int(11) NOT NULL,
  `unit` varchar(20) NOT NULL,
  `recipient` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `tracking_code` varchar(100) DEFAULT NULL,
  `status` enum('Pendente','Entregue') DEFAULT 'Pendente',
  `arrival_time` datetime DEFAULT current_timestamp(),
  `pickup_time` datetime DEFAULT NULL,
  `picked_up_by` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Despejando dados para a tabela `packages`
--

INSERT INTO `packages` (`id`, `unit`, `recipient`, `description`, `tracking_code`, `status`, `arrival_time`, `pickup_time`, `picked_up_by`) VALUES
(1, '115', 'Hospital do Notebook', 'Caixa Mercado Livre', '', 'Entregue', '2025-11-30 23:22:20', '2025-11-30 23:22:39', 'Morador'),
(2, '115', 'Ulisses', 'Mercado Livre', '', 'Pendente', '2025-11-30 23:50:41', NULL, NULL),
(3, '24', 'Consuelo', 'Amazon', '', 'Entregue', '2025-11-30 23:51:13', '2025-12-01 00:39:13', 'Morador'),
(4, '55', 'Gabriel', 'Caixa Mercado Livre', '', 'Pendente', '2025-12-01 00:39:04', NULL, NULL);

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

--
-- Estrutura para tabela `units`
--

CREATE TABLE `units` (
  `id` int(11) NOT NULL,
  `unit_name` varchar(50) NOT NULL,
  `details` text DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Despejando dados para a tabela `units`
--

INSERT INTO `units` (`id`, `unit_name`, `details`, `created_at`) VALUES
(1, 'Torre Única', 'Edifício', '2025-12-01 00:07:32'),
(2, 'Conj. 01', '', '2025-12-01 00:08:31');

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

--
-- Estrutura para tabela `visitors`
--

CREATE TABLE `visitors` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `cpf` varchar(14) NOT NULL,
  `type` enum('Visitante Comum','Prestador de Serviço') NOT NULL,
  `photo_url` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `observations` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Despejando dados para a tabela `visitors`
--

INSERT INTO `visitors` (`id`, `name`, `cpf`, `type`, `photo_url`, `created_at`, `observations`) VALUES
(1, 'Emilio Iglesias', '07765303805', 'Visitante Comum', '', '2025-11-30 20:39:24', NULL),
(2, 'Carmen', '183', 'Visitante Comum', '', '2025-11-30 20:54:18', NULL),
(4, 'Con', '64290778887', 'Visitante Comum', '', '2025-11-30 21:17:29', NULL),
(5, 'fula', '036.609.910-82', 'Visitante Comum', '', '2025-11-30 21:26:40', NULL),
(6, 'vssfgsdfg', '421.694.600-39', 'Visitante Comum', '', '2025-11-30 21:27:23', NULL),
(7, 'asdfasdf', '386.538.670-90', 'Visitante Comum', '', '2025-11-30 21:41:26', NULL),
(14, 'kglkgll glklkglklg', '097.649.610-02', 'Prestador de Serviço', '', '2025-12-01 00:27:36', 'teste'),
(16, 'Consuelo', '642.907.788-87', 'Visitante Comum', '', '2025-12-01 00:57:42', 'teste cadastro'),
(17, 'Ana', '601.374.160-32', 'Visitante Comum', '', '2025-12-01 02:56:30', ''),
(24, 'Novo', '442.093.310-07', 'Visitante Comum', '', '2025-12-01 03:35:15', ''),
(25, 'Felipe', '082.936.620-26', 'Prestador de Serviço', '', '2025-12-01 03:40:25', 'Claro'),
(27, 'sdfgsdf', '077.653.038-05', 'Visitante Comum', '', '2025-12-01 03:41:51', '');

--
-- Índices para tabelas despejadas
--

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

--
-- Índices de tabela `guards`
--
ALTER TABLE `guards`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `username` (`username`);

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

--
-- Índices de tabela `units`
--
ALTER TABLE `units`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unit_name` (`unit_name`);

--
-- Índices de tabela `visitors`
--
ALTER TABLE `visitors`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `cpf` (`cpf`);

--
-- AUTO_INCREMENT para tabelas despejadas
--

--
-- AUTO_INCREMENT de tabela `access_logs`
--
ALTER TABLE `access_logs`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;

--
-- AUTO_INCREMENT de tabela `guards`
--
ALTER TABLE `guards`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT de tabela `packages`
--
ALTER TABLE `packages`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT de tabela `units`
--
ALTER TABLE `units`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT de tabela `visitors`
--
ALTER TABLE `visitors`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=36;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;