-- ----------------------------
DROP TABLE IF EXISTS `spu`;
CREATE TABLE `spu` (
  `id` varchar(60) NOT NULL COMMENT '主键',
  `name` varchar(100) DEFAULT NULL COMMENT 'SPU名',
  `intro` varchar(200) DEFAULT NULL COMMENT '简介',
  `brand_id` int(11) DEFAULT NULL COMMENT '品牌ID',
  `category_one_id` int(20) DEFAULT NULL COMMENT '一级分类',
  `category_two_id` int(10) DEFAULT NULL COMMENT '二级分类',
  `category_three_id` int(10) DEFAULT NULL COMMENT '三级分类',
  `images` varchar(1000) DEFAULT NULL COMMENT '图片列表',
  `after_sales_service` varchar(50) DEFAULT NULL COMMENT '售后服务',
  `content` longtext COMMENT '介绍',
  `attribute_list` varchar(3000) DEFAULT NULL COMMENT '规格列表',
  `is_marketable` int(1) DEFAULT '0' COMMENT '是否上架,0已下架，1已上架',
  `is_delete` int(1) DEFAULT '0' COMMENT '是否删除,0:未删除，1：已删除',
  `status` int(1) DEFAULT '0' COMMENT '审核状态，0：未审核，1：已审核，2：审核不通过',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `sku`;
CREATE TABLE `sku` (
  `id` varchar(60) NOT NULL COMMENT '商品id',
  `name` varchar(200) NOT NULL COMMENT 'SKU名称',
  `price` int(20) NOT NULL DEFAULT '1' COMMENT '价格（分）',
  `num` int(10) DEFAULT '100' COMMENT '库存数量',
  `image` varchar(200) DEFAULT NULL COMMENT '商品图片',
  `images` varchar(2000) DEFAULT NULL COMMENT '商品图片列表',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `spu_id` varchar(60) DEFAULT NULL COMMENT 'SPUID',
  `category_id` int(10) DEFAULT NULL COMMENT '类目ID',
  `category_name` varchar(200) DEFAULT NULL COMMENT '类目名称',
  `brand_id` int(11) DEFAULT NULL COMMENT '品牌id',
  `brand_name` varchar(100) DEFAULT NULL COMMENT '品牌名称',
  `sku_attribute` varchar(200) DEFAULT NULL COMMENT '规格',
  `status` int(1) DEFAULT '1' COMMENT '商品状态 1-正常，2-下架，3-删除',
  PRIMARY KEY (`id`),
  KEY `cid` (`category_id`),
  KEY `status` (`status`),
  KEY `updated` (`update_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='商品表';

DROP TABLE IF EXISTS `category_brand`;
CREATE TABLE `category_brand` (
  `category_id` int(11) NOT NULL COMMENT '分类ID',
  `brand_id` int(11) NOT NULL COMMENT '品牌ID',
  PRIMARY KEY (`brand_id`,`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `category_attr`;
CREATE TABLE `category_attr` (
  `category_id` int(11) NOT NULL,
  `attr_id` int(11) NOT NULL COMMENT '属性分类表',
  PRIMARY KEY (`category_id`,`attr_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

public interface BrandMapper extends BaseMapper<Brand> {
    /****
     * 1、根据分类ID查询品牌ID集合
     */
    @Select("SELECT brand_id FROM category_brand WHERE category_id=#{id}")
    List<Integer> queryBrandIds(Integer id);
}

public interface BrandService extends IService<Brand> {



    /***
     * 根据分类ID查询品牌集合
     */
    List<Brand> queryByCategoryId(Integer id);
}

@Service
public class BrandServiceImpl extends ServiceImpl<BrandMapper,Brand> implements BrandService {


    @Resource
    private BrandMapper brandMapper;


    /****
     * 根据分类ID查询品牌集合
     * @param id:分类ID
     * @return
     */
    @Override
    public List<Brand> queryByCategoryId(Integer id) {
        //根据分类ID查询品牌ID集合
        List<Integer> brandIds = brandMapper.queryBrandIds(id);
        //根据品牌ID集合查询品牌集合
        if(brandIds!=null && brandIds.size()>0){
            return brandMapper.selectList(new QueryWrapper<Brand>().in("id",brandIds));
        }
        return null;
    }
}

@RestController
@RequestMapping(value = "/brand")
@CrossOrigin
public class BrandController {

    @Autowired
    private BrandService brandService;



    /****
     * 根据分类ID查询品牌集合
     */
    @GetMapping(value = "/category/{pid}")
    public RespResult<List<Brand>>  categoryBrands(@PathVariable(value = "pid")Integer pid){
        List<Brand> brands = brandService.queryByCategoryId(pid);
        return RespResult.ok(brands);
    }
}

public interface SkuAttributeMapper extends BaseMapper<SkuAttribute> {

    /****
     * 1、根据分类ID查询属性ID集合
     * 2、根据属性ID集合查询属性集合
     */
    @Select("select * from sku_attribute where id IN(SELECT attr_id FROM category_attr WHERE category_id=#{id})")
    List<SkuAttribute> queryByCategoryId(Integer id);

}

public interface SkuAttributeService extends IService<SkuAttribute> {


    /***
     * 根据分类ID查询属性加集合
     */
    List<SkuAttribute> queryList(Integer id);
}

@Service
public class SkuAttributeServiceImpl extends ServiceImpl<SkuAttributeMapper,SkuAttribute> implements SkuAttributeService {

    @Autowired
    private SkuAttributeMapper skuAttributeMapper;

    /*****
     * 根据分类ID查询属性集合
     * @param id
     * @return
     */
    @Override
    public List<SkuAttribute> queryList(Integer id) {
        return skuAttributeMapper.queryByCategoryId(id);
    }
}

@RestController
@RequestMapping(value = "/skuAttribute")
@CrossOrigin
public class SkuAttributeController {

    @Autowired
    private SkuAttributeService skuAttributeService;

    /*****
     * 根据分类ID查询属性集合
     */
    @GetMapping(value = "/category/{id}")
    public RespResult<List<SkuAttributeController>> categorySkuAttributeList(@PathVariable(value = "id")Integer id){
        List<SkuAttribute> skuAttributes = skuAttributeService.queryList(id);
        return RespResult.ok(skuAttributes);
    }
}