-- --------------------------------------------------------
-- Server OS:                    Win64
-- HeidiSQL Version:             12.6.0.6765
-- --------------------------------------------------------



-- Dumping database structure for webstoredb
CREATE DATABASE IF NOT EXISTS `webstoredb` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */;
USE `webstoredb`;

-- Dumping structure for table webstoredb.migrations
CREATE TABLE IF NOT EXISTS `migrations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Auto increment primary key for the migration identificator.',
  `version` varchar(255) NOT NULL COMMENT 'The migration version identifier',
  `class` varchar(255) NOT NULL COMMENT 'The migration class name',
  `group` varchar(255) NOT NULL COMMENT 'The migration group name used to organize migrations',
  `namespace` varchar(255) NOT NULL COMMENT 'The PHP namespace where the migration class is located',
  `time` int(11) NOT NULL COMMENT 'Timestamp of when the migration was applied',
  `batch` int(11) unsigned NOT NULL COMMENT 'Batch number used to group migrations applied in the same run',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Migrations table that stores data about the most recently added CodeIgniter4 migrations.';

-- Dumping data for table webstoredb.migrations: ~6 rows (approximately)
INSERT IGNORE INTO `migrations` (`id`, `version`, `class`, `group`, `namespace`, `time`, `batch`) VALUES
	(1, '2026-01-08-155616', 'App\\Database\\Migrations\\CreateProducts', 'default', 'App', 1769859240, 1),
	(2, '2026-01-08-155624', 'App\\Database\\Migrations\\CreateTags', 'default', 'App', 1769859240, 1),
	(3, '2026-01-08-155628', 'App\\Database\\Migrations\\CreateUsers', 'default', 'App', 1769859240, 1),
	(4, '2026-01-08-155643', 'App\\Database\\Migrations\\CreateProductImages', 'default', 'App', 1769859241, 1),
	(5, '2026-01-08-155655', 'App\\Database\\Migrations\\CreateProductTags', 'default', 'App', 1769859241, 1),
	(6, '2026-01-08-155704', 'App\\Database\\Migrations\\CreateUsersProducts', 'default', 'App', 1769859241, 1);

-- Dumping structure for table webstoredb.products
CREATE TABLE IF NOT EXISTS `products` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Auto-increment primary key for the product',
  `name` varchar(255) NOT NULL COMMENT 'Product name shown on the website',
  `description` longtext NOT NULL COMMENT 'Full product description',
  `price` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Base product price before any discounts are applied',
  `status` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT 'Availability (1 = available / 0 = unavailable)',
  `discount_percentage` int(11) NOT NULL DEFAULT 0 COMMENT 'Discount percentage applied to the base price',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Table that stores the webstore''s list of product records and their attributes.';

-- Dumping data for table webstoredb.products: ~2 rows (approximately)
INSERT IGNORE INTO `products` (`id`, `name`, `description`, `price`, `status`, `discount_percentage`) VALUES
	(3, 'GTX 1650 4GB', 'The NVIDIA GeForce GTX 1650 is a budget-friendly, entry-level graphics card based on the Turing architecture (without RT cores) designed for 1080p gaming, offering solid performance in older or less demanding titles with features like hardware encoding for streaming (OBS), supporting modern APIs like DirectX 12, and often available in GDDR5 or faster GDDR6 variants with 4GB memory, making it a popular choice for compact builds due to its low 75W power draw. ', 150.60, 1, 12),
	(4, 'Rtx 3060', 'The NVIDIA GeForce RTX 3060 is an Ampere architecture graphics card known for delivering strong performance in 1080p and 2K gaming, featuring 12GB of GDDR6 memory, hardware-accelerated ray tracing, DLSS, and AI-powered Tensor Cores for enhanced visuals and performance, making it a capable choice for both gaming and creative applications. It offers great value, often outperforming the previous generation RTX 2070 at a lower power draw (around 170W TDP) and supporting modern features like PCIe 4.0 and DirectX 12 Ultimate. ', 320.00, 1, 5);

-- Dumping structure for table webstoredb.product_images
CREATE TABLE IF NOT EXISTS `product_images` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Auto-increment primary key for the product image record',
  `item_id` int(10) unsigned NOT NULL COMMENT 'Foreign key referencing the product that an image belongs to',
  `img` varchar(255) NOT NULL COMMENT 'Stored image name for the product image',
  `slot` tinyint(4) NOT NULL DEFAULT 1 COMMENT 'Display slot order for the image (1-6)',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_item_slot` (`item_id`,`slot`),
  CONSTRAINT `product_images_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
  CONSTRAINT `CONSTRAINT_1` CHECK (`slot` between -6 and 6)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_latvian_ci COMMENT='Product images table that stores image files linked to products and their display order (slots) ';

-- Dumping data for table webstoredb.product_images: ~6 rows (approximately)
INSERT IGNORE INTO `product_images` (`id`, `item_id`, `img`, `slot`) VALUES
	(5, 3, 'GTX1650.png', 1),
	(6, 3, '1770026244_7226693273bf3fec41ff.png', 6),
	(7, 3, '1770026244_e162b4cf49ec61188494.png', 5),
	(8, 3, '1770026244_0ac9e60040682741c843.png', 4),
	(9, 3, '1770026244_696ae5a557277f6c36cb.png', 3),
	(10, 3, '1770026359_f47b3a32f1b770b0a6cb.png', 2),
	(11, 4, 'RTX4060.png', 1),
	(12, 4, 'RTX4060_2.png', 2);

-- Dumping structure for table webstoredb.product_tags
CREATE TABLE IF NOT EXISTS `product_tags` (
  `item_id` int(10) unsigned NOT NULL COMMENT 'Foreign key referencing the product',
  `tag_id` int(10) unsigned NOT NULL COMMENT 'Foreign key referencing the tag assigned to the product',
  PRIMARY KEY (`item_id`,`tag_id`),
  KEY `tag_id` (`tag_id`),
  CONSTRAINT `product_tags_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
  CONSTRAINT `product_tags_ibfk_2` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_latvian_ci COMMENT='Junction table that links products to tags\r\n';

-- Dumping data for table webstoredb.product_tags: ~0 rows (approximately)

-- Dumping structure for table webstoredb.tags
CREATE TABLE IF NOT EXISTS `tags` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Auto-increment primary key for the tag',
  `name` varchar(50) NOT NULL COMMENT 'Tag label used for product categorization/tagging',
  PRIMARY KEY (`id`),
  UNIQUE KEY `NAME` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_latvian_ci COMMENT='Tags table that stores tag names used to categorize available product\r\n';

-- Dumping data for table webstoredb.tags: ~3 rows (approximately)
INSERT IGNORE INTO `tags` (`id`, `name`) VALUES
	(2, 'Electronics'),
	(3, 'Novelty'),
	(1, 'Sportsware');

-- Dumping structure for table webstoredb.users_products
CREATE TABLE IF NOT EXISTS `users_products` (
  `person_id` bigint(20) unsigned NOT NULL COMMENT 'Foreign key referencing the user who owns the product',
  `product_id` int(10) unsigned NOT NULL COMMENT 'Foreign key referencing the product linked to the user',
  `quantity` int(10) unsigned NOT NULL COMMENT 'Quantity of a product associated with the given entry',
  PRIMARY KEY (`person_id`,`product_id`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `users_products_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `users_products_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_latvian_ci COMMENT='Junction table that stores which products belong to a user together with the quantity of a specified item (for the cart)';

-- Dumping data for table webstoredb.users_products: ~1 rows (approximately)
INSERT IGNORE INTO `users_products` (`person_id`, `product_id`, `quantity`) VALUES
	(6, 3, 33);

-- Dumping structure for table webstoredb.user_wishlist
CREATE TABLE IF NOT EXISTS `user_wishlist` (
  `person_id` bigint(20) unsigned NOT NULL COMMENT 'Foreign key referencing the user who favorited the product',
  `product_id` int(10) unsigned NOT NULL COMMENT 'Foreign key referencing the product favorited by the user',
  PRIMARY KEY (`person_id`,`product_id`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `1` FOREIGN KEY (`person_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Junction table that stores which products were favorited by a specific user\r\n\r\nForeign key referencing the product favorited by the user';

-- Dumping data for table webstoredb.user_wishlist: ~0 rows (approximately)
INSERT IGNORE INTO `user_wishlist` (`person_id`, `product_id`) VALUES
	(6, 3);

