-- application:			youpin
-- author:				leisure.xu
-- date:				2012-09-24

-- ##########################################################################################
-- #                                      ÓŠÓÃÏà¹Ø±í                                                                                  #
-- ##########################################################################################
-- ÓÃÓÚžæËßºóÌšÄÄÐ©Àà±ðÐèÒªÅÀÈ¡µÄ

CREATE TABLE yp_spider_category(
	id			INT				PRIMARY KEY		COMMENT 'ÅÀÈ¡µÄÀà±ðµÄid',
	name		VARCHAR(50)		COMMENT 'Àà±ðÃû³Æ',
	cat_id		BIGINT			COMMENT 'ÌÔ±ŠÀà±ðid',
	status		INT				COMMENT 'ÊÇ·ñÉúÐ§'
) COMMENT = 'ÅÀÈ¡µÄÀà±ð±í';

CREATE TABLE yp_product(
	id			INT				PRIMARY KEY		COMMENT 'ÉÌÆ·id',
	num_iid		BIGINT			COMMENT 'ÉÌÆ·id(for tmall)',
	name		VARCHAR(50)		COMMENT 'ÉÌÆ·Ãû³Æ',
	pic_url		VARCHAR(255)	COMMENT 'ÉÌÆ·Ö÷ÍŒÆ¬µØÖ·',
	detail_url	VARCHAR(255)	COMMENT 'ÉÌÆ·ÏêÏžÁŽœÓ',
	nick		VARCHAR(50)		COMMENT 'ÉÌŒÒ',
	price		DOUBLE			COMMENT 'ŒÛžñ',
	num			INT				COMMENT 'ÉÌÆ·ÊýÁ¿',
	favorite	INT				COMMENT 'ÊÕ²ØÊý£¬Ã¿ÊÕ²ØÒ»ŽÎ+1£¬È¡ÏûÊÕ²Ø-1',
	is_hot		SMALLINT(2)		COMMENT 'ÊÇ·ñÈÈÃÅ',			-- ÉèÎªÈÈÃÅ
	hot_time	DATETIME		COMMENT 'ÉèÎªÈÈÃÅÊ±Œä,µ±ÇÒœöµ±is_hot=1Ê±²ÅÉúÐ§',
	status		INT				COMMENT '×ŽÌ¬',
	create_by	VARCHAR(50)		COMMENT 'ŽŽœšÕß',
	create_date	DATETIME		COMMENT 'ŽŽœšÊ±Œä',
	update_by	VARCHAR(50)		COMMENT 'žüÐÂÕß',
	update_date	DATETIME		COMMENT 'žüÐÂÊ±Œä'
) COMMENT = 'ÉÌÆ·±í';

CREATE TABLE yp_product_price(
	-- ?
) COMMENT = 'ÉÌÆ·ÕÛ¿ÛÓÅ»Ý±í';

CREATE TABLE yp_product_image(
	id			INT				PRIMARY KEY		COMMENT 'ÉÌÆ·ÍŒÆ¬id',
	product_id	INT				COMMENT 'ÉÌÆ·id',
	src			VARCHAR(255)	COMMENT 'ÉÌÆ·ÍŒÆ¬µØÖ·'
) COMMENT = 'ÉÌÆ·ÍŒÆ¬±í';

CREATE TABLE yp_category(
	id			INT				PRIMARY KEY 	COMMENT 'Àà±ðid',
	parent_id	INT				COMMENT 'žžÀàid',
	name		VARCHAR(50)		COMMENT 'Àà±ðÃû³Æ',
	status		INT				COMMENT '×ŽÌ¬'
) COMMENT = 'ÉÌÆ·ÀàÄ¿±í';

CREATE TABLE yp_tag(
	id			INT				PRIMARY KEY 	COMMENT '±êÇ©id',
	name		VARCHAR(50)		COMMENT '±êÇ©Ãû³Æ',
	input_type	SMALLINT(2)		COMMENT 'ºóÌšÊäÈë·œÊœ£º1µ¥Ñ¡ 2¶àÑ¡',
	status		INT				COMMENT '×ŽÌ¬'
) COMMENT = '±êÇ©±í';

CREATE TABLE yp_tag_option(
	id			INT				PRIMARY KEY		COMMENT '±êÇ©Ñ¡Ïîid',
	tag_id		INT				COMMENT '±êÇ©id',
	name		VARCHAR(50)		COMMENT 'Ñ¡ÏîÃû³Æ'
) COMMENT = '±êÇ©Ñ¡Ïî±í';

CREATE TABLE yp_product_option(
	id			INT				PRIMARY KEY		COMMENT 'id',
	product_id	INT				COMMENT '²úÆ·id',
	tag_id		INT				COMMENT '±êÇ©id,ÈßÓà×Ö¶Î',
	option_id	INT				COMMENT 'Ñ¡Ïîid',
) COMMENT = '²úÆ·Ñ¡Ïî±í';

-- ##########################################################################################
-- #                                      ÓÃ»§Ïà¹Ø±í                                                                                  #
-- ##########################################################################################
CREATE TABLE yp_user(
	id			INT				PRIMARY KEY		COMMENT 'ÓÃ»§id',
	passport_id	INT				COMMENT 'ÍšÐÐÖ€id',
	nick		VARCHAR(50)		COMMENT 'ÓÃ»§êÇ³Æ',
	face		VARCHAR(255)	COMMENT 'ÓÃ»§Í·Ïñ 80x80',
	sign 		VARCHAR(255)	COMMENT 'ÓÃ»§žöÐÔÇ©Ãû',
	status		INT				COMMENT 'ÓÃ»§×ŽÌ¬'
) COMMENT = 'ÓÃ»§±í';

CREATE TABLE yp_user_favorite(
	id			INT				PRIMARY KEY		COMMENT 'id',
	user_id		INT				COMMENT 'ÓÃ»§id',
	product_id	INT				COMMENT 'ÉÌÆ·id',
	favorite_time	DATETIME	COMMENT 'ÊÕ²ØÊ±Œä'
) COMMENT = 'ÓÃ»§ÊÕ²Ø±í';

CREATE TABLE yp_user_record(
	id			INT				PRIMARY KEY		COMMENT 'id',
	user_id		INT				COMMENT 'ÓÃ»§id',
	product_id	INT				COMMENT 'ÉÌÆ·id',
	visit_time	DATETIME		COMMENT 'ä¯ÀÀÊ±Œä'
) COMMENT = 'ÓÃ»§ä¯ÀÀŒÇÂŒ±í';

CREATE TABLE yp_user_test(
	id			INT				PRIMARY KEY		COMMENT 'id',
	-- result	?
	test_time	DATETIME		COMMENT '²âÊÔÊ±Œä'
) COMMENT = 'ÓÃ»§²âÊÔŒÇÂŒ±í';


-- ##########################################################################################
-- #                                      Ö÷ÌâÏà¹Ø±í                                                                                  #
-- ##########################################################################################
CREATE TABLE yp_theme(
	id			INT				PRIMARY KEY 	COMMENT 'Ö÷Ìâid',
	name		VARCHAR(50)		COMMENT 'Ö÷ÌâÃû³Æ',
	logo		VARCHAR(50)		COMMENT 'Ö÷Ìâ·âÃæ',
	status		INT				COMMENT 'Ö÷Ìâ×ŽÌ¬',
	intro		TEXT			COMMENT 'Ö÷ÌâÃèÊö',
	seq			INT				COMMENT 'ÅÅÐò'
) COMMENT = 'Ö÷Ìâ±í';

CREATE TABLE yp_theme_line(
	id			INT				PRIMARY KEY 	COMMENT 'ÐÐid',
	theme_id	INT				COMMENT 'Ö÷Ìâid',
	style		SMALLINT(2)		COMMENT 'ÐÐÄ£°æ 1Ò»ÐÐÒ»ÍŒ 2Ò»ÐÐ¶þÍŒ 4Ò»ÐÐËÄÍŒ 5Ò»ÐÐÎåÍŒ 7Ò»ÐÐÆßÍŒ',
	seq			SMALLINT(2)		COMMENT 'ÐÐÅÅÐò'
);

CREATE TABLE yp_theme_line_image(
	id			INT				PRIMARY KEY		COMMENT 'Ö÷ÌâÍŒÆ¬id',
	product_id	INT				COMMENT 'ÉÌÆ·id',
	-- src			VARCHAR(50)		COMMENT 'ÍŒÆ¬µØÖ·', Ö±œÓ¶ÁÈ¡²úÆ·µÄ·âÃæ
	-- link		VARCHAR(50)		COMMENT 'ÍŒÆ¬ÁŽœÓ', Ö±œÓ¶ÁÈ¡²úÆ·µÄÁŽœÓ
	seq			INT				COMMENT 'ÅÅÐò'
) COMMENT = 'Ö÷ÌâÍŒÆ¬±í';

-- ##########################################################################################
-- #                                      ÆÀÂÛÏà¹Ø±í                                                                                  #
-- ##########################################################################################
CREATE TABLE yp_comment(
	id			INT				PRIMARY KEY		COMMENT 'ÆÀÂÛid',
	passport_id	INT				COMMENT 'ÓÃ»§id',
	product_id	INT				COMMNET '²úÆ·id',
	content		TEXT			COMMENT 'ÆÀÂÛÄÚÈÝ',
	status		SMALLINT(2)		COMMENT 'ÉóºË×ŽÌ¬'
) COMMENT = 'ÆÀÂÛ±í';

-- ##########################################################################################
-- #                                      ÏµÍ³Ïà¹Ø±í                                                                                  #
-- ##########################################################################################
CREATE TABLE sec_acl (
		resource_id 	VARCHAR(50) ,
		role_id 		INT(10) UNSIGNED 		NOT NULL ,
		user_id 		INT(10) UNSIGNED 		NOT NULL ,
		application 	VARCHAR(50) ,
		account_id 		INT(10) UNSIGNED 		NOT NULL,
    	PRIMARY KEY (resource_id, role_id, user_id, application)
 ) COMMENT='ÓÃ»§';

CREATE TABLE sec_function (
		id 				INT(10) UNSIGNED 		NOT NULL ,
		`name` 			VARCHAR(50) ,
		rank 			SMALLINT(5) UNSIGNED ,
		application 	VARCHAR(50) ,
		display_order 	SMALLINT(5) UNSIGNED ,
		PRIMARY KEY (id)
) COMMENT='¹ŠÄÜ';
ALTER TABLE `sec_function` ADD COLUMN `url` VARCHAR(255) NULL AFTER `display_order`;

CREATE TABLE sec_role (
		id 				INT(10) UNSIGNED 		NOT NULL 	AUTO_INCREMENT COMMENT 'id',
		`name` 			VARCHAR(50) ,
		description 	VARCHAR(125) ,
		rank 			SMALLINT(5) UNSIGNED ,
		application 	VARCHAR(50) ,
		display_order 	SMALLINT(5) UNSIGNED ,
		PRIMARY KEY (id)
 ) COMMENT='œÇÉ«';

CREATE TABLE sec_role_function (
	role_id 			INT(10) UNSIGNED 		NOT NULL ,
	function_id 		INT(10) UNSIGNED ,
	application 		VARCHAR(50) ,
	PRIMARY KEY (role_id, function_id, application)
 ) COMMENT='œÇÉ«¹ŠÄÜ';
 
-- ÈÕÖŸ±í
CREATE TABLE ge_log (
	id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id',
	function_type VARCHAR(50) DEFAULT NULL COMMENT '¹ŠÄÜÀàÐÍ',
	operate_type VARCHAR(50) DEFAULT NULL COMMENT '²Ù×÷ÀàÐÍ',
	result VARCHAR(20) DEFAULT NULL COMMENT 'œá¹û',
	ip VARCHAR(50) DEFAULT NULL COMMENT 'ip',
	create_by VARCHAR(30) DEFAULT NULL COMMENT 'ŽŽœšÈË',
	create_date TIMESTAMP COMMENT 'ŽŽœšÊ±Œä',
	content TEXT COMMENT 'ÄÚÈÝ',
	PRIMARY KEY (id)
) COMMENT='ÈÕÖŸ';