-- =====================================================
-- 2026企业CMS - 产品属性筛选功能数据库结构
-- =====================================================

USE `cms2026`;

-- =====================================================
-- 1. 产品属性表 (product_attributes)
-- =====================================================
CREATE TABLE IF NOT EXISTS `product_attributes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL COMMENT '属性名称',
  `slug` varchar(100) NOT NULL COMMENT 'URL参数名',
  `type` enum('text','number','select','multiselect','checkbox','radio','price_range') DEFAULT 'select' COMMENT '属性类型',
  `options` text DEFAULT NULL COMMENT '选项（JSON格式）',
  `sort_order` int(11) DEFAULT 0 COMMENT '排序',
  `status` tinyint(1) DEFAULT 1 COMMENT '状态:1启用,0禁用',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='产品属性表';

-- 插入默认属性
INSERT INTO `product_attributes` (`name`, `slug`, `type`, `options`, `sort_order`) VALUES
('品牌', 'brand', 'select', '["品牌A","品牌B","品牌C","品牌D"]', 1),
('价格区间', 'price', 'price_range', '[{"min":0,"max":100},{"min":100,"max":500},{"min":500,"max":1000},{"min":1000,"max":5000}]', 2),
('颜色', 'color', 'multiselect', '["红色","蓝色","黑色","白色","金色"]', 3),
('尺寸', 'size', 'multiselect', '["S","M","L","XL","XXL"]', 4),
('材质', 'material', 'select', '["棉","涤纶","尼龙","皮革","金属"]', 5);

-- =====================================================
-- 2. 产品属性值表 (product_attribute_values)
-- =====================================================
CREATE TABLE IF NOT EXISTS `product_attribute_values` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL COMMENT '产品ID',
  `attribute_id` int(11) NOT NULL COMMENT '属性ID',
  `value` text NOT NULL COMMENT '属性值',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `product_id` (`product_id`),
  KEY `attribute_id` (`attribute_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='产品属性值表';

-- =====================================================
-- 3. 产品品牌表 (product_brands) - 可选
-- =====================================================
CREATE TABLE IF NOT EXISTS `product_brands` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL COMMENT '品牌名称',
  `logo` varchar(255) DEFAULT NULL COMMENT '品牌LOGO',
  `description` text DEFAULT NULL COMMENT '品牌描述',
  `sort_order` int(11) DEFAULT 0 COMMENT '排序',
  `status` tinyint(1) DEFAULT 1,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='产品品牌表';

-- =====================================================
-- 修改产品表，添加价格字段（如果还没有）
-- =====================================================

-- 检查products表是否有price字段
-- 如果没有，则添加
ALTER TABLE `products` 
ADD COLUMN IF NOT EXISTS `price` decimal(10,2) DEFAULT 0.00 COMMENT '价格' AFTER `content`,
ADD COLUMN IF NOT EXISTS `original_price` decimal(10,2) DEFAULT 0.00 COMMENT '原价' AFTER `price`,
ADD COLUMN IF NOT EXISTS `brand` varchar(100) DEFAULT NULL COMMENT '品牌' AFTER `original_price`;

-- =====================================================
-- 示例数据：产品属性值
-- =====================================================

-- 假设产品ID为1，添加一些属性值
-- INSERT INTO `product_attribute_values` (`product_id`, `attribute_id`, `value`) VALUES
-- (1, 1, '品牌A'),
-- (1, 3, '红色'),
-- (1, 3, '蓝色'),
-- (1, 4, 'M'),
-- (1, 4, 'L'),
-- (1, 5, '棉');
