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

/*!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' */;
/*M!100616 SET @OLD_NOTE_VERBOSITY=@@NOTE_VERBOSITY, NOTE_VERBOSITY='' */;


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


/*!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 */;
/*M!100616 SET NOTE_VERBOSITY=IFNULL(@OLD_NOTE_VERBOSITY, 'basic,explain') */;
