-- --------------------------------------------------------
-- Host:                         localhost
-- Server version:               12.1.2-MariaDB - MariaDB Server
-- Server OS:                    Win64
-- HeidiSQL Version:             12.6.0.6765
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


-- 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
CREATE TABLE IF NOT EXISTS `users` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Auto-increment primary key for the user account',
  `username` text NOT NULL COMMENT 'User''s display name/username',
  `email` text NOT NULL COMMENT 'User’s email address used for login',
  `password` text NOT NULL COMMENT 'Hashed password for authenticating the user',
  `phone` text DEFAULT NULL COMMENT 'User’s phone number (optional)',
  `address` text DEFAULT NULL COMMENT 'User’s address for shipping and billing details (optional)',
  `full_name` text DEFAULT NULL COMMENT 'User’s full name for order details (optional)',
  `role` enum('user','administrator') NOT NULL DEFAULT 'user' COMMENT 'User permission level (administrator or user)',
  `is_active` tinyint(1) DEFAULT 1 COMMENT 'Field indicating whether the account is enabled (1/0)',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'Timestamp when the user account record was first created',
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'Timestamp when the user account record was updated last',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_latvian_ci COMMENT='Users table that stores user account information, authentification and account status data\r\n';

-- Dumping data for table webstoredb.users: ~5 rows (approximately)
INSERT IGNORE INTO `users` (`id`, `username`, `email`, `password`, `phone`, `address`, `full_name`, `role`, `is_active`, `created_at`, `updated_at`) VALUES
	(2, 'Daniels', 'daniels.topcijevs@gmail.com', '$2y$10$qAYyhWVWBVZ75cfY4r9Eh.gqNdaikqhnPyluehgm3D1EvKFS48qzu', NULL, NULL, NULL, 'administrator', 1, '2026-01-31 09:35:29', '2026-01-31 09:35:29'),
	(3, 'aaa', 'aaaa', '$2y$10$XH6Cm9FGRLT.0NGugRvWuerRREO08d592YE8VdNeZQqF/6GwK0gQO', NULL, NULL, NULL, 'user', 1, '2026-01-31 12:26:38', '2026-01-31 12:26:38'),
	(4, 'asdlasdhashd', 'ashgasdasdhasd', '$2y$10$cowe25i4wLBjJBBRq6hHGeghdh19ppqyZLlkLecsGmUEbr2AR0/iq', NULL, NULL, NULL, 'user', 1, '2026-02-01 13:02:39', '2026-02-01 13:02:39'),
	(5, 'Daniel -', 'daniel.work.and.more@gmail.com', '$2y$10$940hmS/4ieCrvMHtAhTZKui7Bg9C7/rIt0XTckdVhyLEYEXmcc692', NULL, NULL, NULL, 'user', 1, '2026-02-01 13:12:05', '2026-02-01 13:12:05'),
	(6, 'ITALIANBUFFGUY', 'italianbuffman@gmail.com', '$2y$10$dv1YwXNmD5jC.3f4peYbMOYPmA70JqIyVwDqhFL1AHONTzRouWJvW', '+371 123 1931', 'Larper Street 9-1', 'Italiano Musicano', 'user', 1, '2026-02-02 06:40:50', '2026-02-04 07:48:02'),
	(7, 'UsernameIsEpic', 'Example@gmail.com', '$2y$10$Vl.o6eSiQPdpF23k.bM/RO73JJABU2JIRhMI02FzaW5.EM/BfXw0O', NULL, NULL, NULL, 'user', 1, '2026-02-04 08:21:07', '2026-02-04 08:21:07');

-- 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);

/*!40103 SET TIME_ZONE=IFNULL(@OLD_TIME_ZONE, 'system') */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;