-- Flash Sale Table Creation
-- Run this SQL in phpMyAdmin or MySQL command line

USE mobilestoredb;

CREATE TABLE IF NOT EXISTS `tblflashsale` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `ProductId` int(11) NOT NULL,
  `DiscountPercent` int(11) NOT NULL,
  `StartDate` datetime NOT NULL,
  `EndDate` datetime NOT NULL,
  `IsActive` tinyint(1) DEFAULT 1,
  `CreatedDate` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `ProductId` (`ProductId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Add foreign key constraint if tblproducts exists
-- ALTER TABLE `tblflashsale` ADD CONSTRAINT `fk_flashsale_product` FOREIGN KEY (`ProductId`) REFERENCES `tblproducts`(`ID`) ON DELETE CASCADE;

-- Insert sample flash sale data
INSERT INTO `tblflashsale` (`ProductId`, `DiscountPercent`, `StartDate`, `EndDate`, `IsActive`) VALUES
(1, 50, '2024-01-15 10:00:00', '2024-01-15 18:00:00', 1),
(2, 30, '2024-01-15 12:00:00', '2024-01-15 20:00:00', 1),
(3, 70, '2024-01-15 14:00:00', '2024-01-15 22:00:00', 1);