89 lines
4.0 KiB
SQL
89 lines
4.0 KiB
SQL
CREATE TABLE IF NOT EXISTS `users` (
|
|
`id` int NOT NULL AUTO_INCREMENT,
|
|
`name` varchar(150) NOT NULL,
|
|
`email` varchar(150) NOT NULL,
|
|
`password` varchar(255) NOT NULL,
|
|
`role` enum('admin','editor') DEFAULT 'editor',
|
|
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `email` (`email`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `categories` (
|
|
`id` int NOT NULL AUTO_INCREMENT,
|
|
`name` varchar(150) NOT NULL,
|
|
`slug` varchar(150) NOT NULL,
|
|
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `slug` (`slug`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `products` (
|
|
`id` int NOT NULL AUTO_INCREMENT,
|
|
`category_id` int NOT NULL,
|
|
`name` varchar(255) NOT NULL,
|
|
`slug` varchar(255) NOT NULL,
|
|
`description` text,
|
|
`price` decimal(10,2) NOT NULL,
|
|
`image` varchar(255) DEFAULT NULL,
|
|
`stock` int DEFAULT '0',
|
|
`active` tinyint(1) DEFAULT '1',
|
|
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `slug` (`slug`),
|
|
KEY `category_id` (`category_id`),
|
|
CONSTRAINT `products_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `orders` (
|
|
`id` int NOT NULL AUTO_INCREMENT,
|
|
`customer_name` varchar(255) DEFAULT NULL,
|
|
`customer_email` varchar(255) DEFAULT NULL,
|
|
`total` decimal(10,2) DEFAULT NULL,
|
|
`status` enum('pending','paid','sent','cancelled') DEFAULT 'pending',
|
|
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `order_items` (
|
|
`id` int NOT NULL AUTO_INCREMENT,
|
|
`order_id` int NOT NULL,
|
|
`product_id` int NOT NULL,
|
|
`quantity` int NOT NULL,
|
|
`price` decimal(10,2) NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `order_id` (`order_id`),
|
|
KEY `product_id` (`product_id`),
|
|
CONSTRAINT `order_items_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
|
|
CONSTRAINT `order_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `messages` (
|
|
`id` int NOT NULL AUTO_INCREMENT,
|
|
`name` varchar(255) DEFAULT NULL,
|
|
`email` varchar(255) DEFAULT NULL,
|
|
`subject` varchar(255) DEFAULT NULL,
|
|
`message` text,
|
|
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
-- SEED DATA --
|
|
INSERT INTO `categories` (`name`, `slug`) VALUES
|
|
('Café da Manhã', 'cafe-da-manha'),
|
|
('Lanche', 'lanche'),
|
|
('Caixa Box', 'caixa-box');
|
|
|
|
INSERT INTO `products` (`category_id`, `name`, `slug`, `description`, `price`, `stock`, `active`) VALUES
|
|
(1, 'Cesta de Café da Manhã Clássica', 'cesta-cafe-da-manha-classica', 'Uma cesta completa com pães, frutas, frios e muito mais.', 120.00, 10, 1),
|
|
(1, 'Cesta de Café da Manhã Premium', 'cesta-cafe-da-manha-premium', 'Uma experiência de café da manhã inesquecível com itens selecionados.', 180.00, 5, 1),
|
|
(2, 'Kit Lanche da Tarde', 'kit-lanche-da-tarde', 'Uma seleção de salgados e doces para uma pausa deliciosa.', 80.00, 15, 1),
|
|
(2, 'Caixa de Brigadeiros Gourmet', 'caixa-brigadeiros-gourmet', '20 brigadeiros de sabores variados.', 50.00, 20, 1),
|
|
(3, 'Box Happy Hour', 'box-happy-hour', 'Cervejas artesanais, amendoins e outros petiscos.', 150.00, 8, 1),
|
|
(3, 'Box Presente Romântico', 'box-presente-romantico', 'Vinho, taças, chocolates e um toque de romance.', 220.00, 4, 1);
|
|
|
|
-- Admin user: admin@exemplo.com / ChangeMe123!
|
|
-- The password hash is a placeholder and needs to be generated with password_hash()
|
|
INSERT INTO `users` (`name`, `email`, `password`, `role`) VALUES
|
|
('Admin', 'admin@exemplo.com', '$2y$10$3s.G5s1JSs5s/S.d5s.d5u/zY8A.L9zJ.X5s.d5s.d5u/zY8A.L9zJ.', 'admin');
|