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

START TRANSACTION;



--
-- Database: `fit3047_onboarding`
--

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

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

--
-- Table structure for table `contact_messages`
--

CREATE TABLE `contact_messages` (
  `id` int(11) NOT NULL,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone_number` varchar(20) DEFAULT NULL,
  `message` text NOT NULL,
  `reply_status` tinyint(1) NOT NULL DEFAULT 0,
  `submission_date_time` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `contact_messages`
--

INSERT INTO `contact_messages` (`id`, `first_name`, `last_name`, `email`, `phone_number`, `message`, `reply_status`, `submission_date_time`) VALUES
(1, 'John', 'Doe', 'john.doe@example.com', '+61412345678', 'I would like to inquire about your latest gym equipment.', 0, '2025-03-21 10:00:00'),
(2, 'Alice', 'Smith', 'alice.smith@example.com', '+61487654321', 'Do you offer bulk discounts for corporate purchases?', 0, '2025-03-21 11:00:00'),
(3, 'Bob', 'Brown', 'bob.brown@example.com', NULL, 'Can I schedule a repair service for my treadmill?', 1, '2025-03-21 12:00:00'),
(4, 'David', 'Lee', 'david.lee@example.com', '+61415975346', 'What is the warranty period for your products?', 0, '2025-03-21 13:00:00'),
(5, 'Emma', 'Wilson', 'emma.wilson@example.com', '+61426849305', 'Can you provide a price list for your accessories?', 1, '2025-03-21 14:00:00'),
(6, 'Liam', 'Anderson', 'liam.anderson@example.com', NULL, 'I am interested in your installation services. Please call me.', 1, '2025-03-21 15:00:00'),
(7, 'Sophia', 'Taylor', 'sophia.taylor@example.com', '+61455567890', 'Do you have any financing options for gym owners?', 0, '2025-03-21 16:00:00'),
(8, 'James', 'Martinez', 'james.martinez@example.com', NULL, 'I would like to book a consultation with your team.', 1, '2025-03-21 17:00:00'),
(9, 'Olivia', 'Harris', 'olivia.harris@example.com', '+61488899977', 'Can you customize gym equipment for branding purposes?', 0, '2025-03-21 18:00:00'),
(10, 'Michael', 'Clark', 'michael.clark@example.com', NULL, 'I need assistance with assembling my recently purchased gym set.', 1, '2025-03-21 19:00:00'),
(11, 'Testing', 'Mika', 'mika@gmail.com', '', 'Hi I want to check ', 0, '2025-03-21 20:00:00');

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

--
-- Table structure for table `faq_categories`
--

CREATE TABLE `faq_categories` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `created` datetime NOT NULL DEFAULT current_timestamp(),
  `modified` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 
-- Dumping data for table `faq_categories`
--

INSERT INTO `faq_categories` (`id`, `name`, `created`, `modified`) VALUES
(1, 'General', '2025-03-21 10:00:00', NULL),
(2, 'Shipping & Delivery', '2025-03-21 11:00:00', NULL),
(3, 'Payment & Returns', '2025-03-21 12:00:00', NULL),
(4, 'Installation & Repair', '2025-03-21 14:00:00', NULL),
(5, 'Warranty & Support', '2025-03-21 15:00:00', NULL),
(6, 'Product Information', '2025-03-21 16:00:00', NULL);


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

--
-- Table structure for table `faqs`
--

CREATE TABLE `faqs` (
  `id` int(11) NOT NULL,
  `question` varchar(255) NOT NULL,
  `answer` text NOT NULL,
  `faq_category_id` int(11) DEFAULT NULL,
  `created` datetime NOT NULL DEFAULT current_timestamp(),
  `modified` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `faqs`
--

INSERT INTO `faqs` (`id`, `question`, `answer`, `faq_category_id`, `created`) VALUES
-- General
(1, 'Do I need an account to place an order?', 'No, you can checkout as a guest. However, creating an account allows you to track your orders and save your details for faster checkout next time.', 1, NOW()),

-- Shipping & Delivery
(2, 'How long does delivery take?', 'Standard delivery within Australia usually takes between 3 to 7 business days depending on your location.', 2, NOW()),
(3, 'Do you offer express shipping?', 'Yes, we offer express shipping options at checkout for eligible items and locations.', 2, NOW()),

-- Payment & Returns
(4, 'What payment methods do you accept?', 'We accept all major credit and debit cards, PayPal, and Afterpay.', 3, NOW()),
(5, 'Can I return an item if I change my mind?', 'Yes, you may return items within 14 days of delivery, provided they are unused and in their original packaging. Restocking fees may apply.', 3, NOW()),

-- Installation & Repair
(6, 'Do you provide installation services?', 'Yes, we offer professional installation for most gym equipment at an additional cost. You can book this during checkout.', 4, NOW()),
(7, 'How do I request equipment repair?', 'Please fill out the repair form on our website or contact our customer support team to schedule a service.', 4, NOW()),

-- Warranty & Support
(8, 'Is there a warranty on your equipment?', 'Yes, all our gym equipment comes with a minimum 12-month manufacturer warranty. Some items may have extended warranties.', 5, NOW()),
(9, 'Who do I contact for a warranty claim?', 'You can contact our support team at support@powerproshop.com with your order details and product issue.', 5, NOW()),

-- Product Information
(10, 'Are your accessories suitable for commercial gyms?', 'Yes, our accessories are designed for both home and commercial use, offering high durability and performance.', 6, NOW());

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


--
-- Table structure for table `product_categories`
--
CREATE TABLE `product_categories` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `created` datetime NOT NULL DEFAULT current_timestamp(),
  `modified` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `product_categories`
--
INSERT INTO `product_categories` (`id`, `name`, `created`, `modified`) VALUES
(1, 'Cardio Equipment', '2025-03-21 10:00:00', NULL),
(2, 'Strength Equipment', '2025-03-21 11:00:00', NULL),
(3, 'Accessories', '2025-03-21 12:00:00', NULL),
(4, 'Clothing', '2025-03-21 14:00:00', NULL);

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

CREATE TABLE `products` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `stock_quantity` int(11) NOT NULL DEFAULT 0,
  `is_published` TINYINT(1) NOT NULL DEFAULT 0,
  `is_featured` TINYINT(1) NOT NULL DEFAULT 0,
  `created` datetime NOT NULL DEFAULT current_timestamp(),
  `modified` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

INSERT INTO `products` (`id`, `name`, `description`, `price`, `stock_quantity`, `created`) VALUES
(1, 'Treadmill', 'High-performance treadmill with multiple speed settings.', 999.99, 10, NOW()),
(2, 'Dumbbell Set', 'Adjustable dumbbell set for versatile strength training.', 199.99, 20, NOW()),
(3, 'Yoga Mat', 'Non-slip yoga mat for all types of workouts.', 29.99, 50, NOW()),
(4, 'StairMaster', 'Cardio machine designed to simulate stair climbing for intense workouts.', 1499.99, 5, NOW()),
(5, 'Smith Machine', 'Sturdy Smith Machine with adjustable barbell for guided weightlifting.', 1799.99, 3, NOW());

--
-- Table structure for table `product_product_categories`
--

CREATE TABLE `product_product_categories` (
  `id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `product_category_id` int(11) NOT NULL,
  `created` datetime NOT NULL DEFAULT current_timestamp(),
  `modified` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 
-- Dumping data for table `product_product_categories`
--

INSERT INTO `product_product_categories` (`id`, `product_id`, `product_category_id`, `created`) VALUES
(1, 1, 1, NOW()),
(2, 1, 2, NOW()),      -- Treadmill in Cardio & Strength
(3, 2, 2, NOW()),
(4, 2, 3, NOW()),      -- Dumbbell Set in Strength & Accessories
(5, 3, 3, NOW()),      -- Yoga Mat in Accessories
(6, 4, 1, NOW()),      -- StairMaster in Cardio
(7, 5, 2, NOW());      -- Smith Machine in Strength

--
-- Table structure for table `product_images`
--
CREATE TABLE `product_images` (
  `id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `image_url` varchar(255) NOT NULL,
  `is_primary` TINYINT(1) NOT NULL DEFAULT 0,
  `created` datetime NOT NULL DEFAULT current_timestamp(),
  `modified` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


--
-- Indexes for dumped tables
--

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

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

--
-- Indexes for table `faqs`
--
ALTER TABLE `faqs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `FK_FAQS_FAQ_CATEGORIES` (`faq_category_id`);


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

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

--
-- Indexes for table `product_product_categories`
--
ALTER TABLE `product_product_categories`
  ADD PRIMARY KEY (`id`),
  ADD KEY `FK_PRODUCT_CATEGORIES_PRODUCTS` (`product_id`),
  ADD KEY `FK_PRODUCT_CATEGORIES_CATEGORIES` (`product_category_id`);

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

--
-- AUTO_INCREMENT for dumped tables
--

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

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

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

--
-- AUTO_INCREMENT for table `product_categories`
--
ALTER TABLE `product_categories`
  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 for table `product_product_categories`
--
ALTER TABLE `product_product_categories`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

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

--
-- Constraints for dumped tables
--

-- 
-- Constraints for table `faqs`
--
ALTER TABLE `faqs`
  ADD CONSTRAINT `FK_FAQS_FAQ_CATEGORIES` FOREIGN KEY (`faq_category_id`) REFERENCES `faq_categories` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- Constraints for table `product_product_categories`
--
ALTER TABLE `product_product_categories`
  ADD CONSTRAINT `FK_PRODUCT_PRODUCT_CATEGORIES_PRODUCTS` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `FK_PRODUCT_PRODUCT_CATEGORIES_PRODUCT_CATEGORIES` FOREIGN KEY (`product_category_id`) REFERENCES `product_categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `product_images`
--
ALTER TABLE `product_images`
  ADD CONSTRAINT `FK_PRODUCT_IMAGES_PRODUCTS` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

COMMIT;
