-- https://www.phpmyadmin.net/
--
-- Waktu pembuatan: 21 Okt 2025 pada 15.17
-- Versi server: 10.4.32-MariaDB
-- Versi PHP: 8.2.12

START TRANSACTION;



--
-- Database: `labushop_db`
--

DELIMITER $$
--
-- Prosedur
--
CREATE DEFINER=`root`@`localhost` PROCEDURE `UpdateProductStock` (IN `product_id` INT, IN `quantity_sold` INT)   BEGIN
    UPDATE products 
    SET stock = stock - quantity_sold 
    WHERE id = product_id AND stock >= quantity_sold;
    
    IF ROW_COUNT() = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient stock';
    END IF;
END$$

DELIMITER ;

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

--
-- Struktur dari tabel `orders`
--

CREATE TABLE `orders` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `unit_price` decimal(10,2) NOT NULL,
  `total_price` decimal(10,2) NOT NULL,
  `status` enum('pending','processing','shipped','delivered','cancelled') DEFAULT 'pending',
  `payment_status` enum('pending','paid','failed') DEFAULT 'pending',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Trigger `orders`
--
DELIMITER $$
CREATE TRIGGER `update_stock_after_order` AFTER INSERT ON `orders` FOR EACH ROW BEGIN
    CALL UpdateProductStock(NEW.product_id, NEW.quantity);
END
$$
DELIMITER ;

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

--
-- Stand-in struktur untuk tampilan `order_summary`
-- (Lihat di bawah untuk tampilan aktual)
--
CREATE TABLE `order_summary` (
`order_id` int(11)
,`username` varchar(50)
,`email` varchar(255)
,`product_name` varchar(255)
,`quantity` int(11)
,`unit_price` decimal(10,2)
,`total_price` decimal(10,2)
,`order_status` enum('pending','processing','shipped','delivered','cancelled')
,`payment_status` enum('pending','paid','failed')
,`created_at` timestamp
);

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

--
-- Struktur dari tabel `payments`
--

CREATE TABLE `payments` (
  `id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL,
  `payment_method` enum('credit_card','debit_card','bank_transfer','e_wallet') NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `status` enum('pending','success','failed','cancelled') DEFAULT 'pending',
  `transaction_id` varchar(255) DEFAULT NULL,
  `payment_proof` varchar(500) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Struktur dari tabel `products`
--

CREATE TABLE `products` (
  `id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `price` decimal(10,2) NOT NULL,
  `discount_percentage` decimal(5,2) DEFAULT 0.00,
  `rating` decimal(3,2) DEFAULT 0.00,
  `stock` int(11) DEFAULT 0,
  `brand` varchar(100) DEFAULT NULL,
  `category` varchar(100) DEFAULT NULL,
  `thumbnail` varchar(500) DEFAULT NULL,
  `images` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`images`)),
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data untuk tabel `products`
--

INSERT INTO `products` (`id`, `title`, `description`, `price`, `discount_percentage`, `rating`, `stock`, `brand`, `category`, `thumbnail`, `images`, `status`, `created_at`, `updated_at`) VALUES
(1, 'Essence Mascara Lash Princess', 'The Essence Mascara Lash Princess is a popular mascara known for its volumizing and lengthening effects. Achieve dramatic lashes with this long-lasting and cruelty-free formula.', 9.99, 10.48, 2.56, 99, 'Essence', 'beauty', 'https://cdn.dummyjson.com/product-images/beauty/essence-mascara-lash-princess/thumbnail.webp', '[\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/beauty\\/essence-mascara-lash-princess\\/1.webp\"]', 'active', '2025-10-19 06:12:48', '2025-10-19 06:12:48'),
(2, 'Eyeshadow Palette with Mirror', 'The Eyeshadow Palette with Mirror offers a versatile range of eyeshadow shades for creating stunning eye looks. With a built-in mirror, it\'s convenient for on-the-go makeup application.', 19.99, 18.19, 2.86, 34, 'Glamour Beauty', 'beauty', 'https://cdn.dummyjson.com/product-images/beauty/eyeshadow-palette-with-mirror/thumbnail.webp', '[\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/beauty\\/eyeshadow-palette-with-mirror\\/1.webp\"]', 'active', '2025-10-19 06:12:48', '2025-10-19 06:12:48'),
(3, 'Powder Canister', 'The Powder Canister is a finely milled setting powder designed to set makeup and control shine. With a lightweight and translucent formula, it provides a smooth and matte finish.', 14.99, 9.84, 4.64, 89, 'Velvet Touch', 'beauty', 'https://cdn.dummyjson.com/product-images/beauty/powder-canister/thumbnail.webp', '[\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/beauty\\/powder-canister\\/1.webp\"]', 'active', '2025-10-19 06:12:48', '2025-10-19 06:12:48'),
(4, 'Red Lipstick', 'The Red Lipstick is a classic and bold choice for adding a pop of color to your lips. With a creamy and pigmented formula, it provides a vibrant and long-lasting finish.', 12.99, 12.16, 4.36, 91, 'Chic Cosmetics', 'beauty', 'https://cdn.dummyjson.com/product-images/beauty/red-lipstick/thumbnail.webp', '[\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/beauty\\/red-lipstick\\/1.webp\"]', 'active', '2025-10-19 06:12:48', '2025-10-19 06:12:48'),
(5, 'Red Nail Polish', 'The Red Nail Polish offers a rich and glossy red hue for vibrant and polished nails. With a quick-drying formula, it provides a salon-quality finish at home.', 8.99, 11.44, 4.32, 79, 'Nail Couture', 'beauty', 'https://cdn.dummyjson.com/product-images/beauty/red-nail-polish/thumbnail.webp', '[\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/beauty\\/red-nail-polish\\/1.webp\"]', 'active', '2025-10-19 06:12:48', '2025-10-19 06:12:48'),
(6, 'Calvin Klein CK One', 'CK One by Calvin Klein is a classic unisex fragrance, known for its fresh and clean scent. It\'s a versatile fragrance suitable for everyday wear.', 49.99, 1.89, 4.37, 29, 'Calvin Klein', 'fragrances', 'https://cdn.dummyjson.com/product-images/fragrances/calvin-klein-ck-one/thumbnail.webp', '[\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/fragrances\\/calvin-klein-ck-one\\/1.webp\",\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/fragrances\\/calvin-klein-ck-one\\/2.webp\",\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/fragrances\\/calvin-klein-ck-one\\/3.webp\"]', 'active', '2025-10-19 06:12:48', '2025-10-19 06:12:48'),
(7, 'Chanel Coco Noir Eau De', 'Coco Noir by Chanel is an elegant and mysterious fragrance, featuring notes of grapefruit, rose, and sandalwood. Perfect for evening occasions.', 129.99, 16.51, 4.26, 58, 'Chanel', 'fragrances', 'https://cdn.dummyjson.com/product-images/fragrances/chanel-coco-noir-eau-de/thumbnail.webp', '[\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/fragrances\\/chanel-coco-noir-eau-de\\/1.webp\",\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/fragrances\\/chanel-coco-noir-eau-de\\/2.webp\",\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/fragrances\\/chanel-coco-noir-eau-de\\/3.webp\"]', 'active', '2025-10-19 06:12:48', '2025-10-19 06:12:48'),
(8, 'Dior J\'adore', 'J\'adore by Dior is a luxurious and floral fragrance, known for its blend of ylang-ylang, rose, and jasmine. It embodies femininity and sophistication.', 89.99, 14.72, 3.80, 98, 'Dior', 'fragrances', 'https://cdn.dummyjson.com/product-images/fragrances/dior-j\'adore/thumbnail.webp', '[\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/fragrances\\/dior-j\'adore\\/1.webp\",\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/fragrances\\/dior-j\'adore\\/2.webp\",\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/fragrances\\/dior-j\'adore\\/3.webp\"]', 'active', '2025-10-19 06:12:48', '2025-10-19 06:12:48'),
(9, 'Dolce Shine Eau de', 'Dolce Shine by Dolce & Gabbana is a vibrant and fruity fragrance, featuring notes of mango, jasmine, and blonde woods. It\'s a joyful and youthful scent.', 69.99, 0.62, 3.96, 4, 'Dolce & Gabbana', 'fragrances', 'https://cdn.dummyjson.com/product-images/fragrances/dolce-shine-eau-de/thumbnail.webp', '[\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/fragrances\\/dolce-shine-eau-de\\/1.webp\",\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/fragrances\\/dolce-shine-eau-de\\/2.webp\",\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/fragrances\\/dolce-shine-eau-de\\/3.webp\"]', 'active', '2025-10-19 06:12:48', '2025-10-19 06:12:48'),
(10, 'Gucci Bloom Eau de', 'Gucci Bloom by Gucci is a floral and captivating fragrance, with notes of tuberose, jasmine, and Rangoon creeper. It\'s a modern and romantic scent.', 79.99, 14.39, 2.74, 91, 'Gucci', 'fragrances', 'https://cdn.dummyjson.com/product-images/fragrances/gucci-bloom-eau-de/thumbnail.webp', '[\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/fragrances\\/gucci-bloom-eau-de\\/1.webp\",\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/fragrances\\/gucci-bloom-eau-de\\/2.webp\",\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/fragrances\\/gucci-bloom-eau-de\\/3.webp\"]', 'active', '2025-10-19 06:12:48', '2025-10-19 06:12:48'),
(11, 'Annibale Colombo Bed', 'The Annibale Colombo Bed is a luxurious and elegant bed frame, crafted with high-quality materials for a comfortable and stylish bedroom.', 1899.99, 8.57, 4.77, 88, 'Annibale Colombo', 'furniture', 'https://cdn.dummyjson.com/product-images/furniture/annibale-colombo-bed/thumbnail.webp', '[\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/furniture\\/annibale-colombo-bed\\/1.webp\",\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/furniture\\/annibale-colombo-bed\\/2.webp\",\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/furniture\\/annibale-colombo-bed\\/3.webp\"]', 'active', '2025-10-19 06:12:48', '2025-10-19 06:12:48'),
(12, 'Annibale Colombo Sofa', 'The Annibale Colombo Sofa is a sophisticated and comfortable seating option, featuring exquisite design and premium upholstery for your living room.', 2499.99, 14.40, 3.92, 60, 'Annibale Colombo', 'furniture', 'https://cdn.dummyjson.com/product-images/furniture/annibale-colombo-sofa/thumbnail.webp', '[\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/furniture\\/annibale-colombo-sofa\\/1.webp\",\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/furniture\\/annibale-colombo-sofa\\/2.webp\",\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/furniture\\/annibale-colombo-sofa\\/3.webp\"]', 'active', '2025-10-19 06:12:48', '2025-10-19 06:12:48'),
(13, 'Bedside Table African Cherry', 'The Bedside Table in African Cherry is a stylish and functional addition to your bedroom, providing convenient storage space and a touch of elegance.', 299.99, 19.09, 2.87, 64, 'Furniture Co.', 'furniture', 'https://cdn.dummyjson.com/product-images/furniture/bedside-table-african-cherry/thumbnail.webp', '[\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/furniture\\/bedside-table-african-cherry\\/1.webp\",\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/furniture\\/bedside-table-african-cherry\\/2.webp\",\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/furniture\\/bedside-table-african-cherry\\/3.webp\"]', 'active', '2025-10-19 06:12:48', '2025-10-19 06:12:48'),
(14, 'Knoll Saarinen Executive Conference Chair', 'The Knoll Saarinen Executive Conference Chair is a modern and ergonomic chair, perfect for your office or conference room with its timeless design.', 499.99, 2.01, 4.88, 26, 'Knoll', 'furniture', 'https://cdn.dummyjson.com/product-images/furniture/knoll-saarinen-executive-conference-chair/thumbnail.webp', '[\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/furniture\\/knoll-saarinen-executive-conference-chair\\/1.webp\",\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/furniture\\/knoll-saarinen-executive-conference-chair\\/2.webp\",\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/furniture\\/knoll-saarinen-executive-conference-chair\\/3.webp\"]', 'active', '2025-10-19 06:12:48', '2025-10-19 06:12:48'),
(15, 'Wooden Bathroom Sink With Mirror', 'The Wooden Bathroom Sink with Mirror is a unique and stylish addition to your bathroom, featuring a wooden sink countertop and a matching mirror.', 799.99, 8.80, 3.59, 7, 'Bath Trends', 'furniture', 'https://cdn.dummyjson.com/product-images/furniture/wooden-bathroom-sink-with-mirror/thumbnail.webp', '[\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/furniture\\/wooden-bathroom-sink-with-mirror\\/1.webp\",\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/furniture\\/wooden-bathroom-sink-with-mirror\\/2.webp\",\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/furniture\\/wooden-bathroom-sink-with-mirror\\/3.webp\"]', 'active', '2025-10-19 06:12:48', '2025-10-19 06:12:48'),
(16, 'Apple', 'Fresh and crisp apples, perfect for snacking or incorporating into various recipes.', 1.99, 12.62, 4.19, 8, NULL, 'groceries', 'https://cdn.dummyjson.com/product-images/groceries/apple/thumbnail.webp', '[\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/groceries\\/apple\\/1.webp\"]', 'active', '2025-10-19 06:12:48', '2025-10-19 06:12:48'),
(17, 'Beef Steak', 'High-quality beef steak, great for grilling or cooking to your preferred level of doneness.', 12.99, 9.61, 4.47, 86, NULL, 'groceries', 'https://cdn.dummyjson.com/product-images/groceries/beef-steak/thumbnail.webp', '[\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/groceries\\/beef-steak\\/1.webp\"]', 'active', '2025-10-19 06:12:48', '2025-10-19 06:12:48'),
(18, 'Cat Food', 'Nutritious cat food formulated to meet the dietary needs of your feline friend.', 8.99, 9.58, 3.13, 46, NULL, 'groceries', 'https://cdn.dummyjson.com/product-images/groceries/cat-food/thumbnail.webp', '[\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/groceries\\/cat-food\\/1.webp\"]', 'active', '2025-10-19 06:12:48', '2025-10-19 06:12:48'),
(19, 'Chicken Meat', 'Fresh and tender chicken meat, suitable for various culinary preparations.', 9.99, 13.70, 3.19, 97, NULL, 'groceries', 'https://cdn.dummyjson.com/product-images/groceries/chicken-meat/thumbnail.webp', '[\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/groceries\\/chicken-meat\\/1.webp\",\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/groceries\\/chicken-meat\\/2.webp\"]', 'active', '2025-10-19 06:12:48', '2025-10-19 06:12:48'),
(20, 'Cooking Oil', 'Versatile cooking oil suitable for frying, sautéing, and various culinary applications.', 4.99, 9.33, 4.80, 10, NULL, 'groceries', 'https://cdn.dummyjson.com/product-images/groceries/cooking-oil/thumbnail.webp', '[\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/groceries\\/cooking-oil\\/1.webp\"]', 'active', '2025-10-19 06:12:48', '2025-10-19 06:12:48'),
(21, 'Cucumber', 'Crisp and hydrating cucumbers, ideal for salads, snacks, or as a refreshing side.', 1.49, 0.16, 4.07, 84, NULL, 'groceries', 'https://cdn.dummyjson.com/product-images/groceries/cucumber/thumbnail.webp', '[\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/groceries\\/cucumber\\/1.webp\"]', 'active', '2025-10-19 06:12:48', '2025-10-19 06:12:48'),
(22, 'Dog Food', 'Specially formulated dog food designed to provide essential nutrients for your canine companion.', 10.99, 10.27, 4.55, 71, NULL, 'groceries', 'https://cdn.dummyjson.com/product-images/groceries/dog-food/thumbnail.webp', '[\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/groceries\\/dog-food\\/1.webp\"]', 'active', '2025-10-19 06:12:48', '2025-10-19 06:12:48'),
(23, 'Eggs', 'Fresh eggs, a versatile ingredient for baking, cooking, or breakfast.', 2.99, 11.05, 2.53, 9, NULL, 'groceries', 'https://cdn.dummyjson.com/product-images/groceries/eggs/thumbnail.webp', '[\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/groceries\\/eggs\\/1.webp\"]', 'active', '2025-10-19 06:12:48', '2025-10-19 06:12:48'),
(24, 'Fish Steak', 'Quality fish steak, suitable for grilling, baking, or pan-searing.', 14.99, 4.23, 3.78, 74, NULL, 'groceries', 'https://cdn.dummyjson.com/product-images/groceries/fish-steak/thumbnail.webp', '[\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/groceries\\/fish-steak\\/1.webp\"]', 'active', '2025-10-19 06:12:48', '2025-10-19 06:12:48'),
(25, 'Green Bell Pepper', 'Fresh and vibrant green bell pepper, perfect for adding color and flavor to your dishes.', 1.29, 0.16, 3.25, 33, NULL, 'groceries', 'https://cdn.dummyjson.com/product-images/groceries/green-bell-pepper/thumbnail.webp', '[\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/groceries\\/green-bell-pepper\\/1.webp\"]', 'active', '2025-10-19 06:12:48', '2025-10-19 06:12:48'),
(26, 'Green Chili Pepper', 'Spicy green chili pepper, ideal for adding heat to your favorite recipes.', 0.99, 1.00, 3.66, 3, NULL, 'groceries', 'https://cdn.dummyjson.com/product-images/groceries/green-chili-pepper/thumbnail.webp', '[\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/groceries\\/green-chili-pepper\\/1.webp\"]', 'active', '2025-10-19 06:12:48', '2025-10-19 06:12:48'),
(27, 'Honey Jar', 'Pure and natural honey in a convenient jar, perfect for sweetening beverages or drizzling over food.', 6.99, 14.40, 3.97, 34, NULL, 'groceries', 'https://cdn.dummyjson.com/product-images/groceries/honey-jar/thumbnail.webp', '[\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/groceries\\/honey-jar\\/1.webp\"]', 'active', '2025-10-19 06:12:48', '2025-10-19 06:12:48'),
(28, 'Ice Cream', 'Creamy and delicious ice cream, available in various flavors for a delightful treat.', 5.49, 8.69, 3.39, 27, NULL, 'groceries', 'https://cdn.dummyjson.com/product-images/groceries/ice-cream/thumbnail.webp', '[\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/groceries\\/ice-cream\\/1.webp\",\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/groceries\\/ice-cream\\/2.webp\",\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/groceries\\/ice-cream\\/3.webp\",\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/groceries\\/ice-cream\\/4.webp\"]', 'active', '2025-10-19 06:12:48', '2025-10-19 06:12:48'),
(29, 'Juice', 'Refreshing fruit juice, packed with vitamins and great for staying hydrated.', 3.99, 12.06, 3.94, 50, NULL, 'groceries', 'https://cdn.dummyjson.com/product-images/groceries/juice/thumbnail.webp', '[\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/groceries\\/juice\\/1.webp\"]', 'active', '2025-10-19 06:12:48', '2025-10-19 06:12:48'),
(30, 'Kiwi', 'Nutrient-rich kiwi, perfect for snacking or adding a tropical twist to your dishes.', 2.49, 15.22, 4.93, 99, NULL, 'groceries', 'https://cdn.dummyjson.com/product-images/groceries/kiwi/thumbnail.webp', '[\"https:\\/\\/cdn.dummyjson.com\\/product-images\\/groceries\\/kiwi\\/1.webp\"]', 'active', '2025-10-19 06:12:48', '2025-10-19 06:12:48');

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

--
-- Struktur dari tabel `users`
--

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `username` varchar(50) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Struktur untuk view `order_summary`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `order_summary`  AS SELECT `o`.`id` AS `order_id`, `u`.`username` AS `username`, `u`.`email` AS `email`, `p`.`title` AS `product_name`, `o`.`quantity` AS `quantity`, `o`.`unit_price` AS `unit_price`, `o`.`total_price` AS `total_price`, `o`.`status` AS `order_status`, `o`.`payment_status` AS `payment_status`, `o`.`created_at` AS `created_at` FROM ((`orders` `o` join `users` `u` on(`o`.`user_id` = `u`.`id`)) join `products` `p` on(`o`.`product_id` = `p`.`id`)) ;

--
-- Indexes for dumped tables
--

--
-- Indeks untuk tabel `orders`
--
ALTER TABLE `orders`
  ADD PRIMARY KEY (`id`),
  ADD KEY `product_id` (`product_id`),
  ADD KEY `idx_orders_user_id` (`user_id`),
  ADD KEY `idx_orders_status` (`status`);

--
-- Indeks untuk tabel `payments`
--
ALTER TABLE `payments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_payments_order_id` (`order_id`);

--
-- Indeks untuk tabel `products`
--
ALTER TABLE `products`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_products_category` (`category`),
  ADD KEY `idx_products_status` (`status`);

--
-- Indeks untuk tabel `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `username` (`username`),
  ADD UNIQUE KEY `email` (`email`);

--
-- AUTO_INCREMENT untuk tabel yang dibuang
--

--
-- AUTO_INCREMENT untuk tabel `orders`
--
ALTER TABLE `orders`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT untuk tabel `payments`
--
ALTER TABLE `payments`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT untuk tabel `products`
--
ALTER TABLE `products`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=31;

--
-- AUTO_INCREMENT untuk tabel `users`
--
ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- Ketidakleluasaan untuk tabel pelimpahan (Dumped Tables)
--

--
-- Ketidakleluasaan untuk tabel `orders`
--
ALTER TABLE `orders`
  ADD CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `orders_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE;

--
-- Ketidakleluasaan untuk tabel `payments`
--
ALTER TABLE `payments`
  ADD CONSTRAINT `payments_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE;
COMMIT;

