-- ============================================
-- AmeasyShop Categories Database Schema
-- ============================================

-- Create categories table
CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `slug` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `icon` varchar(50) DEFAULT NULL,
  `color` varchar(20) DEFAULT NULL,
  `sort_order` int(11) DEFAULT 0,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`),
  KEY `parent_id` (`parent_id`),
  KEY `status` (`status`),
  KEY `sort_order` (`sort_order`),
  CONSTRAINT `fk_categories_parent` FOREIGN KEY (`parent_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================

-- Insert default categories
INSERT INTO `categories` (`name`, `slug`, `description`, `parent_id`, `icon`, `color`, `sort_order`, `status`) VALUES
('Electronics', 'electronics', 'Electronic devices and accessories', NULL, '💻', '#3B82F6', 1, 'active'),
('Clothing', 'clothing', 'Fashion and apparel items', NULL, '👕', '#10B981', 2, 'active'),
('Food & Beverage', 'food-beverage', 'Food items and drinks', NULL, '🍕', '#F59E0B', 3, 'active'),
('Home & Kitchen', 'home-kitchen', 'Home appliances and kitchen items', NULL, '🏠', '#8B5CF6', 4, 'active'),
('Sports & Outdoors', 'sports-outdoors', 'Sports equipment and outdoor gear', NULL, '⚽', '#EF4444', 5, 'active'),
('Toys & Games', 'toys-games', 'Toys, games and entertainment', NULL, '🎮', '#EC4899', 6, 'active'),
('Books & Media', 'books-media', 'Books, magazines and media content', NULL, '📚', '#6366F1', 7, 'active'),
('Beauty & Personal Care', 'beauty-personal-care', 'Beauty products and personal care items', NULL, '💄', '#F97316', 8, 'active'),
('Other', 'other', 'Miscellaneous items', NULL, '📦', '#6B7280', 9, 'active');

-- Insert subcategories (examples)
INSERT INTO `categories` (`name`, `slug`, `description`, `parent_id`, `icon`, `color`, `sort_order`, `status`) VALUES
('Mobile Phones', 'mobile-phones', 'Smartphones and mobile devices', 1, '📱', '#3B82F6', 1, 'active'),
('Computers', 'computers', 'Laptops, desktops and accessories', 1, '💻', '#3B82F6', 2, 'active'),
('Audio', 'audio', 'Headphones, speakers and audio equipment', 1, '🎧', '#3B82F6', 3, 'active'),
('Mens Clothing', 'mens-clothing', 'Clothing for men', 2, '👔', '#10B981', 1, 'active'),
('Womens Clothing', 'womens-clothing', 'Clothing for women', 2, '👗', '#10B981', 2, 'active'),
('Kids Clothing', 'kids-clothing', 'Clothing for children', 2, '👶', '#10B981', 3, 'active');

-- ============================================
-- END OF SQL SCHEMA
-- ============================================
