-- Create discounts table
CREATE TABLE `discounts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) DEFAULT NULL,
  `category` varchar(50) DEFAULT NULL,
  `discount_percentage` decimal(5,2) NOT NULL,
  `start_date` datetime DEFAULT NULL,
  `end_date` datetime DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `discounts_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Create advertisements table
CREATE TABLE `advertisements` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` text,
  `image_url` varchar(255) DEFAULT NULL,
  `link_url` varchar(255) DEFAULT NULL,
  `start_date` datetime DEFAULT NULL,
  `end_date` datetime DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `position` varchar(50) DEFAULT 'homepage', -- homepage, product_page, etc.
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Insert sample discounts
INSERT INTO `discounts` (`product_id`, `discount_percentage`, `start_date`, `end_date`, `is_active`) VALUES
(1, 10.00, '2025-11-20 00:00:00', '2025-12-20 23:59:59', 1),
(3, 15.00, '2025-11-20 00:00:00', '2025-12-20 23:59:59', 1),
(5, 20.00, '2025-11-20 00:00:00', '2025-12-20 23:59:59', 1);

-- Insert sample advertisements
INSERT INTO `advertisements` (`title`, `description`, `image_url`, `link_url`, `start_date`, `end_date`, `is_active`, `position`) VALUES
('Holiday Sale', 'Up to 50% off on selected items!', 'assets/images/ads/holiday_sale.jpg', '/products/index.php', '2025-11-20 00:00:00', '2025-12-31 23:59:59', 1, 'homepage'),
('Free Shipping', 'Free shipping on orders over $50', 'assets/images/ads/free_shipping.jpg', '/products/index.php', '2025-11-20 00:00:00', '2025-12-31 23:59:59', 1, 'homepage'),
('New Arrivals', 'Check out our latest products', 'assets/images/ads/new_arrivals.jpg', '/products/index.php', '2025-11-20 00:00:00', '2025-12-31 23:59:59', 1, 'homepage');