-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `zurarazak`
--

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

--
-- Table structure for table `admin`
--

CREATE TABLE `admin` (
  `id` int(11) NOT NULL,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `role` enum('admin','customer') NOT NULL,
  `email` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `admin`
--

INSERT INTO `admin` (`id`, `username`, `password`, `role`, `email`) VALUES
(1, 'admin01', '@Admin01', 'admin', 'admin@gmail.com');

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

--
-- Table structure for table `categories`
--

CREATE TABLE `categories` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `categories`
--

INSERT INTO `categories` (`id`, `name`, `created_at`) VALUES
(1, 'Kuih', '2025-05-16 18:10:33'),
(2, 'Kuih Raya', '2025-05-16 18:24:53'),
(3, 'Frozen Food', '2025-05-16 19:49:10'),
(4, 'Kek', '2025-05-16 20:03:17');

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

--
-- Table structure for table `customers`
--

CREATE TABLE `customers` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `image_grids` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`image_grids`)),
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `blocked_until` datetime DEFAULT NULL,
  `verification_token` varchar(64) DEFAULT NULL,
  `token_expiry` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `customers`
--

INSERT INTO `customers` (`id`, `user_id`, `image_grids`, `created_at`, `blocked_until`, `verification_token`, `token_expiry`) VALUES
(19, 34, '[{\"image_name\":\"basikal.jpg\",\"grid_id\":11},{\"image_name\":\"kucing2.jpg\",\"grid_id\":6},{\"image_name\":\"rumah.jpg\",\"grid_id\":14}]', '2025-05-16 03:43:15', NULL, NULL, NULL),
(21, 36, '[{\"image_name\":\"basikal.jpg\",\"grid_id\":6},{\"image_name\":\"kucing2.jpg\",\"grid_id\":6},{\"image_name\":\"rumah.jpg\",\"grid_id\":14}]', '2025-05-16 03:58:44', '2025-05-16 12:22:33', NULL, NULL),
(22, 37, '[{\"image_name\":\"basikal.jpg\",\"grid_id\":10},{\"image_name\":\"kucing2.jpg\",\"grid_id\":6},{\"image_name\":\"rumah.jpg\",\"grid_id\":14}]', '2025-05-16 04:14:26', NULL, NULL, NULL),
(23, 38, '[{\"image_name\":\"basikal.jpg\",\"grid_id\":10},{\"image_name\":\"kucing2.jpg\",\"grid_id\":6},{\"image_name\":\"rumah.jpg\",\"grid_id\":14}]', '2025-05-17 03:55:27', NULL, NULL, NULL);

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

--
-- Table structure for table `login_attempts`
--

CREATE TABLE `login_attempts` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `attempt_time` datetime DEFAULT current_timestamp(),
  `attempt_type` enum('password','image') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `orders`
--

CREATE TABLE `orders` (
  `id` int(11) NOT NULL,
  `customer_id` int(11) DEFAULT NULL,
  `customer_name` varchar(255) NOT NULL,
  `phone_number` varchar(20) NOT NULL,
  `address` text NOT NULL,
  `pickup_date` datetime NOT NULL,
  `total_price` decimal(10,2) NOT NULL,
  `payment_status` enum('pending','paid') NOT NULL DEFAULT 'pending',
  `status` enum('pending','dispatch','completed') NOT NULL DEFAULT 'pending',
  `order_date` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `order_items`
--

CREATE TABLE `order_items` (
  `id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `price` decimal(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `products`
--

CREATE TABLE `products` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `category` varchar(100) NOT NULL,
  `quantity` int(11) NOT NULL,
  `description` text NOT NULL,
  `image` blob DEFAULT NULL,
  `date_added` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `products`
--

INSERT INTO `products` (`id`, `name`, `price`, `category`, `quantity`, `description`, `image`, `date_added`) VALUES
(20, 'Karipap Pusing Sardin', 6.00, 'Frozen Food', 25, '1 Kilogram', 0x4b61726970617020507573696e672046726f7a656e2e6a7067, '2025-05-16 20:18:06'),
(21, 'Kek Coklat Moist', 17.00, 'Kek', 10, 'Boleh Pre-Order. Order Harini esok dapat. Saiz kek 9 inci.', 0x363832373965333561383233382e6a7067, '2025-05-16 20:21:09'),
(23, 'Maruku Spicy', 18.00, 'Kuih', 20, 'Balang besar', 0x363832376137303864333436362e6a7067, '2025-05-16 20:58:48'),
(24, 'Pulut Panggang Frozen', 6.00, 'Frozen Food', 10, 'Boleh Pre-Order. 1 bekas 10 pcs.', 0x50756c75742050616e6767616e672046726f7a656e2e6a7067, '2025-05-16 21:00:42');

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

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `username` varchar(50) NOT NULL,
  `name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `contact` varchar(20) DEFAULT NULL,
  `password` varchar(255) NOT NULL,
  `role` enum('admin','customer') DEFAULT 'customer',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `otp` int(6) DEFAULT NULL,
  `otp_created_at` datetime DEFAULT NULL,
  `otp_expiry` datetime DEFAULT NULL,
  `activation_token` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 0,
  `address` text DEFAULT NULL,
  `profile_picture` varchar(255) DEFAULT NULL,
  `reset_token` varchar(255) DEFAULT NULL,
  `reset_token_expires` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `username`, `name`, `email`, `contact`, `password`, `role`, `created_at`, `otp`, `otp_created_at`, `otp_expiry`, `activation_token`, `is_active`, `address`, `profile_picture`, `reset_token`, `reset_token_expires`) VALUES
(13, 'admin01', 'admin01', 'ai220196@student.uthm.edu.my', '0123897578', '$2y$10$zqTzxVNZQbBJXU8KfE1cMe/RpiZWqm45UrUr0xr5U3ylaBUvtbSAa', 'admin', '2025-01-12 14:55:25', NULL, '2025-04-18 14:08:41', NULL, NULL, 1, NULL, NULL, NULL, NULL),
(34, 'SarahAl', 'Sarah Alyana', 'athirafarhanaaa@gmail.com', '0198987867', '$2y$10$UzFI9JbRJXLnzA3.SpwEz.ZEzQpNE18XynNQh9oGpCmzHOueCw.FO', 'customer', '2025-05-16 03:43:08', NULL, NULL, NULL, NULL, 1, NULL, NULL, NULL, NULL),
(36, 'aina123', 'Aina', 'nurathirahh71@gmail.com', '0187882134', '$2y$10$GrYqPrOgnRLes15UUAEGMe570wivxOLPxyU5gfn7b2oLleFCThBn2', 'customer', '2025-05-16 03:58:36', NULL, NULL, NULL, NULL, 1, NULL, NULL, NULL, NULL),
(37, 'aisyah12', 'aisyah', 'nurathirah.16@basis.edu.my', '0169097876', '$2y$10$A3tZn4Tp77A0ZVBXaYR4v.A6vMM5U3AnLoRA6Gnrzfo1kXkpucYY2', 'customer', '2025-05-16 04:13:39', NULL, NULL, NULL, '48c9e4e7c696d058d07d5943aa2c96f3874ff45f8b100ecb8815d463f6739942', 0, NULL, NULL, NULL, NULL),
(38, 'Syaz01', 'Syazleen', 'hanierex0715@gmail.com', '0176762134', '$2y$10$67LUdRVMQCYSgvYXoYy2SeLcQ4cGmCqRi8WSL/1WmdQ88iMXthm5i', 'customer', '2025-05-17 03:51:53', NULL, '2025-05-17 13:08:42', NULL, NULL, 1, NULL, NULL, 'a447db838164665e17f64c465f0ff3c487acff16a428ed08195f4f745abd7081', '2025-05-17 14:26:44');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `admin`
--
ALTER TABLE `admin`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `username` (`username`);

--
-- Indexes for table `categories`
--
ALTER TABLE `categories`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `name` (`name`);

--
-- Indexes for table `customers`
--
ALTER TABLE `customers`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_customer_user` (`user_id`);

--
-- Indexes for table `login_attempts`
--
ALTER TABLE `login_attempts`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `orders`
--
ALTER TABLE `orders`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_orders_customer` (`customer_id`);

--
-- Indexes for table `order_items`
--
ALTER TABLE `order_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `order_id` (`order_id`),
  ADD KEY `product_id` (`product_id`);

--
-- Indexes for table `products`
--
ALTER TABLE `products`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `username` (`username`),
  ADD UNIQUE KEY `email` (`email`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `admin`
--
ALTER TABLE `admin`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `categories`
--
ALTER TABLE `categories`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `customers`
--
ALTER TABLE `customers`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=24;

--
-- AUTO_INCREMENT for table `login_attempts`
--
ALTER TABLE `login_attempts`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=28;

--
-- AUTO_INCREMENT for table `orders`
--
ALTER TABLE `orders`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `order_items`
--
ALTER TABLE `order_items`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `products`
--
ALTER TABLE `products`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=25;

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=39;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `customers`
--
ALTER TABLE `customers`
  ADD CONSTRAINT `fk_customer_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `login_attempts`
--
ALTER TABLE `login_attempts`
  ADD CONSTRAINT `login_attempts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `orders`
--
ALTER TABLE `orders`
  ADD CONSTRAINT `fk_orders_customer` FOREIGN KEY (`customer_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;
COMMIT;

