-- 增加用戶戶是否已驗證
ALTER TABLE `ecs_users` ADD `is_validated` TINYINT UNSIGNED NOT NULL DEFAULT '0';

-- 將虛擬卡做為標準功能
CREATE TABLE IF NOT EXISTS `ecs_virtual_card` (
    `card_id` mediumint(8) NOT NULL auto_increment,
    `goods_id` mediumint(8) unsigned NOT NULL default '0',
    `card_sn` varchar(60) NOT NULL default '',
    `card_password` varchar(60) NOT NULL default '',
    `add_date` int(11) NOT NULL default '0',
    `end_date` int(11) NOT NULL default '0',
    `is_saled` tinyint(1) NOT NULL default '0',
    `order_sn` varchar(20) NOT NULL default '',
    `crc32` int(11) NOT NULL default '0',
    PRIMARY KEY  (`card_id`),
    KEY `goods_id` (`goods_id`),
    KEY `car_sn` (`card_sn`),
    KEY `is_saled` (`is_saled`)
    ) TYPE=MyISAM ;

-- 支付方式加入是否支持在線支付
ALTER TABLE `ecs_payment` ADD `is_online` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0';

-- 增加留言關聯的訂單ID字段
ALTER TABLE `ecs_feedback` ADD `order_id` INT( 11 ) UNSIGNED NOT NULL DEFAULT '0';

-- 紅包類型表增加字段：訂單最低金額
ALTER TABLE `ecs_bonus_type` ADD `min_goods_amount` DECIMAL( 10, 2 ) UNSIGNED NOT NULL ;

-- 解決多個管理員同時添加商品時沖突的問題
ALTER TABLE `ecs_link_goods` ADD `admin_id` TINYINT UNSIGNED NOT NULL ;
ALTER TABLE `ecs_group_goods` ADD `admin_id` TINYINT UNSIGNED NOT NULL ;
ALTER TABLE `ecs_goods_article` ADD `admin_id` TINYINT UNSIGNED NOT NULL ;
ALTER TABLE `ecs_group_goods` DROP PRIMARY KEY ,
ADD PRIMARY KEY ( `parent_id` , `goods_id` , `admin_id` );
ALTER TABLE `ecs_link_goods` DROP PRIMARY KEY ,
ADD PRIMARY KEY ( `goods_id` , `link_goods_id` , `admin_id` );
ALTER TABLE `ecs_goods_article` DROP PRIMARY KEY ,
ADD PRIMARY KEY ( `goods_id` , `article_id` , `admin_id` );

-- 會員表增加信用額度字段
ALTER TABLE `ecs_users` ADD `credit_line` DECIMAL( 10, 2 ) UNSIGNED NOT NULL ;

-- 商品表增加商品名稱樣式字段
ALTER TABLE `ecs_goods` ADD `goods_name_style` VARCHAR( 255 ) NOT NULL DEFAULT '+' AFTER `goods_name` ;

-- 訂單表增加發票類型及稅額字段
ALTER TABLE `ecs_order_info` ADD `inv_type` VARCHAR( 60 ) NOT NULL ,
ADD `tax` DECIMAL( 10, 2 ) NOT NULL ;

-- 商品表增加送多少積分字段
ALTER TABLE `ecs_goods` ADD `give_integral` INT UNSIGNED NOT NULL;

-- 商品表刪除字段
ALTER TABLE `ecs_goods` DROP `can_handsel`;
ALTER TABLE `ecs_goods` DROP `fitting_price`;
ALTER TABLE `ecs_goods` DROP `is_linked`;
ALTER TABLE `ecs_goods` DROP `is_basic`;
ALTER TABLE `ecs_goods` DROP `is_gift`;

-- 增加批發商品表
DROP TABLE IF EXISTS `ecs_wholesale`;
CREATE TABLE `ecs_wholesale` (
  `act_id` mediumint(8) unsigned NOT NULL auto_increment,
  `goods_id` mediumint(8) unsigned NOT NULL,
  `goods_name` varchar(255) NOT NULL,
  `rank_ids` varchar(255) NOT NULL,
  `prices` text NOT NULL,
  `enabled` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY  (`act_id`),
  KEY `goods_id` (`goods_id`)
) TYPE=MYISAM ;

-- 增加發送隊列表
DROP TABLE IF EXISTS `ecs_email_sendlist`;
CREATE TABLE  `ecs_email_sendlist` (
 `id` MEDIUMINT( 8 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
 `email` VARCHAR( 100 ) NOT NULL ,
 `template_id` MEDIUMINT( 8 ) NOT NULL ,
 `email_content` TEXT NOT NULL ,
 `error` TINYINT( 1 ) NOT NULL DEFAULT  '0' ,
 `pri` TINYINT( 10 ) NOT NULL ,
 `last_send` INT( 10 ) NOT NULL
) TYPE = MYISAM ;

-- 增加電子雜志訂閱表
DROP TABLE IF EXISTS `ecs_email_list`;
CREATE TABLE `ecs_email_list` (
  `id` mediumint(8) NOT NULL auto_increment,
  `email` varchar(60) NOT NULL,
  `stat` tinyint(1) NOT NULL default '0',
  `hash` varchar(10) NOT NULL,
  PRIMARY KEY  (`id`)
) TYPE=MyISAM;
ALTER TABLE `ecs_mail_templates` ADD `type` VARCHAR( 10 ) NOT NULL ;
ALTER TABLE `ecs_mail_templates` ADD INDEX ( `type` ) ;
UPDATE  `ecs_mail_templates` SET type='template';

ALTER TABLE `ecs_mail_templates` DROP INDEX `template_code`;
ALTER TABLE `ecs_mail_templates` ADD INDEX ( `template_code` );

-- 增加自動處理的表
DROP TABLE IF EXISTS `ecs_auto_manage`;
CREATE TABLE `ecs_auto_manage` (
  `item_id` mediumint(8) NOT NULL,
  `type` varchar(10) NOT NULL,
  `starttime` int(10) NOT NULL,
  `endtime` int(10) NOT NULL,
  PRIMARY KEY  (`item_id`,`type`)
) TYPE=MyISAM;

ALTER TABLE `ecs_order_info` ADD `parent_id` mediumint(8) unsigned NOT NULL default '0';
ALTER TABLE `ecs_order_info` ADD `is_separate` tinyint(1) NOT NULL default '0';
ALTER TABLE `ecs_order_info` ADD INDEX ( `is_separate` ) ;

-- 增加分成信息紀錄
DROP TABLE IF EXISTS `ecs_affiliate_log`;
CREATE TABLE  `ecs_affiliate_log` (
 `log_id` MEDIUMINT( 8 ) NOT NULL auto_increment,
 `order_id` MEDIUMINT( 8 ) NOT NULL ,
 `time` INT( 10 ) NOT NULL ,
 `user_id` MEDIUMINT( 8 ) NOT NULL,
 `user_name` varchar(60),
 `money` DECIMAL(10,2) NOT NULL DEFAULT '0',
 `point` INT(10) NOT NULL DEFAULT '0',
 `separate_type` TINYINT(1) NOT NULL DEFAULT '0',
PRIMARY KEY ( `log_id` )
) TYPE = MYISAM ;
ALTER TABLE `ecs_affiliate_log` ADD `separate_by` tinyint(1) NOT NULL default '0';

-- 增加價格分級及過濾屬性
ALTER TABLE `ecs_category` ADD `grade` tinyint(4) NOT NULL default '0';
ALTER TABLE `ecs_category` ADD `filter_attr` smallint(6) NOT NULL default 0;

-- 增加`goods_attr_id`
ALTER TABLE `ecs_cart` ADD `goods_attr_id` mediumint(8) NOT NULL;
ALTER TABLE `ecs_cart` ADD `can_handsel` tinyint(3) unsigned NOT NULL default '0';

-- 增加 自定義導航欄 表
DROP TABLE IF EXISTS `ecs_nav`;
CREATE TABLE `ecs_nav` (
  `id` mediumint(8) NOT NULL auto_increment,
  `ctype` VARCHAR( 10 ) NULL,
  `cid` SMALLINT( 5 ) UNSIGNED NULL,
  `name` varchar(255) NOT NULL,
  `ifshow` tinyint(1) NOT NULL,
  `vieworder` tinyint(1) NOT NULL,
  `opennew` tinyint(1) NOT NULL,
  `url` varchar(255) NOT NULL,
  `type` varchar(10) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `type` (`type`),
  KEY `ifshow` (`ifshow`)
) TYPE=MyISAM;
--  ALTER TABLE `ecs_nav` ADD `ctype` VARCHAR( 10 ) NULL AFTER `id` ,
--  ADD `cid` SMALLINT( 5 ) UNSIGNED NULL AFTER `ctype` ;

-- 用戶表增加上級id
ALTER TABLE `ecs_users` ADD `parent_id` mediumint(8) unsigned NOT NULL;

-- 用戶id的類型加大
ALTER TABLE `ecs_admin_user` CHANGE `user_id` `user_id` SMALLINT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT;-- 廣告字段增加默認值ALTER TABLE `ecs_ad` CHANGE `ad_link` `ad_link` VARCHAR( 255 ) NOT NULL default '';ALTER TABLE `ecs_ad` CHANGE `start_time` `start_time` int(11 ) NOT NULL default '0';ALTER TABLE `ecs_ad` CHANGE `end_time` `end_time` int( 11 ) NOT NULL default '0';ALTER TABLE `ecs_ad` CHANGE `link_phone` `link_phone` VARCHAR( 60 ) NOT NULL default '';-- admin_message設定字段默認值ALTER TABLE `ecs_admin_message` CHANGE `sent_time` `sent_time` int(11 ) NOT NULL default '0';ALTER TABLE `ecs_admin_message` CHANGE `read_time` `read_time` int(11 ) NOT NULL default '0';-- admin_user設定字段默認值ALTER TABLE `ecs_admin_user` CHANGE `add_time` `add_time` int(11 ) NOT NULL default '0';ALTER TABLE `ecs_admin_user` CHANGE `last_login` `last_login` int(11 ) NOT NULL default '0';-- 文章鏈接設定默認值ALTER TABLE `ecs_article` CHANGE `link` `link` varchar(255 ) NOT NULL default ''; -- 文章分類名稱設定默認值ALTER TABLE `ecs_article_cat` CHANGE `cat_name` `cat_name` varchar(255 ) NOT NULL default '';-- bounds_type設定默認值ALTER TABLE `ecs_bonus_type` CHANGE `send_start_date` `send_start_date` int(11) NOT NULL default '0';ALTER TABLE `ecs_bonus_type` CHANGE `send_end_date` `send_end_date` int(11) NOT NULL default '0';ALTER TABLE `ecs_bonus_type` CHANGE `use_start_date` `use_start_date` int(11) NOT NULL default '0';ALTER TABLE `ecs_bonus_type` CHANGE `use_end_date` `use_end_date` int(11) NOT NULL default '0';ALTER TABLE `ecs_bonus_type` CHANGE `min_goods_amount` `min_goods_amount` decimal(10,2) unsigned NOT NULL default '0.00';-- booking_goods設定默認值ALTER TABLE `ecs_booking_goods` CHANGE `tel` `tel` varchar(60) NOT NULL default '';-- 購物車設定默認值ALTER TABLE `ecs_cart` CHANGE `session_id` `session_id` char(32) binary NOT NULL default '';ALTER TABLE `ecs_cart` CHANGE `is_gift` `is_gift` smallint(5) unsigned NOT NULL default '0';ALTER TABLE `ecs_cart` CHANGE `can_handsel` `can_handsel` tinyint(3) unsigned NOT NULL default '0';-- 分類表設定默認值ALTER TABLE `ecs_category` CHANGE `cat_name` `cat_name` varchar(90) NOT NULL default '';-- collect_goods設定默認值ALTER TABLE `ecs_collect_goods` CHANGE `add_time` `add_time` int(11) unsigned NOT NULL default '0';-- 商品表字段添加默認值ALTER TABLE `ecs_goods` CHANGE `promote_start_date` `promote_start_date` int(11) unsigned NOT NULL default '0';ALTER TABLE `ecs_goods` CHANGE `promote_end_date` `promote_end_date` int(11) unsigned NOT NULL default '0';ALTER TABLE `ecs_goods` CHANGE `give_integral` `give_integral` int(10) NOT NULL default '-1';ALTER TABLE `ecs_goods` DROP INDEX `promote_start`;ALTER TABLE `ecs_goods` DROP INDEX `promote_end`;ALTER TABLE `ecs_goods` ADD INDEX (`promote_start_date`) ;ALTER TABLE `ecs_goods` ADD INDEX (`promote_end_date`) ;-- 商品文章表字段添加默認值ALTER TABLE `ecs_goods_article` CHANGE `admin_id` `admin_id` tinyint(3) unsigned NOT NULL default '0';-- 配件表字段添加默認值ALTER TABLE `ecs_group_goods` CHANGE `admin_id` `admin_id` tinyint(3) unsigned NOT NULL default '0';-- link_goods表添加默認值ALTER TABLE `ecs_link_goods` CHANGE `admin_id` `admin_id` tinyint(3) unsigned NOT NULL default '0';-- 郵件模板表添加唯一索引ALTER TABLE `ecs_mail_templates` ADD UNIQUE (`template_code`);-- 訂單操作表字段添加默認值ALTER TABLE `ecs_order_action` CHANGE `log_time` `log_time` int(11) unsigned NOT NULL default '0';-- 訂單商品表添加默認值ALTER TABLE `ecs_order_goods` CHANGE `is_gift` `is_gift` smallint(5) unsigned NOT NULL default '0';-- 訂單信息表添加默認值ALTER TABLE `ecs_order_info` CHANGE `address` `address` varchar(255) NOT NULL default '';ALTER TABLE `ecs_order_info` CHANGE `zipcode` `zipcode` varchar(60) NOT NULL default '';ALTER TABLE `ecs_order_info` CHANGE `tel` `tel` varchar(60) NOT NULL default '';ALTER TABLE `ecs_order_info` CHANGE `mobile` `mobile` varchar(60) NOT NULL default '';ALTER TABLE `ecs_order_info` CHANGE `add_time` `add_time` int(10) unsigned NOT NULL default '0';ALTER TABLE `ecs_order_info` CHANGE `pay_note` `pay_note` varchar(255) NOT NULL default '';-- 支付日志表添加默認值ALTER TABLE `ecs_pay_log` CHANGE `order_amount` `order_amount` decimal(10,2) unsigned NOT NULL;-- 支付方式添加默認值ALTER TABLE `ecs_payment` CHANGE `pay_fee` `pay_fee` varchar(10) NOT NULL default '0';ALTER TABLE `ecs_payment` CHANGE `is_online` `is_online` tinyint(1) unsigned NOT NULL default '0';-- 配送表修改默認值ALTER TABLE `ecs_shipping` CHANGE `insure` `insure` varchar(10) NOT NULL default '0';-- 模板設置默認值ALTER TABLE `ecs_template` CHANGE `remarks` `remarks` varchar(30) NOT NULL default '';-- 用戶地址設置默認值ALTER TABLE `ecs_user_address` CHANGE `zipcode` `zipcode` varchar(60) NOT NULL default '';ALTER TABLE `ecs_user_address` CHANGE `tel` `tel` varchar(60) NOT NULL default '';ALTER TABLE `ecs_user_address` CHANGE `mobile` `mobile` varchar(60) NOT NULL default '';-- 用戶表設置默認值ALTER TABLE `ecs_users` CHANGE `frozen_money` `frozen_money` decimal(10,2) NOT NULL default '0.00';ALTER TABLE `ecs_users` CHANGE `last_login` `last_login` int(11) unsigned NOT NULL default '0';ALTER TABLE `ecs_users` CHANGE `parent_id` `parent_id` mediumint(9) NOT NULL default '0';ALTER TABLE `ecs_users` DROP INDEX `user_name`;ALTER TABLE `ecs_users` ADD UNIQUE (`user_name`);ALTER TABLE `ecs_users` ADD INDEX (`parent_id`);-- 投票表設置默認值ALTER TABLE `ecs_vote` CHANGE `start_time` `start_time` int(11) unsigned NOT NULL default '0';ALTER TABLE `ecs_vote` CHANGE `end_time` `end_time` int(11) unsigned NOT NULL default '0';
ALTER TABLE `ecs_admin_user` CHANGE `user_id` `user_id` SMALLINT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT;

-- 支持負id
ALTER TABLE `ecs_article_cat` CHANGE `cat_id` `cat_id` SMALLINT( 5 ) NOT NULL AUTO_INCREMENT;
ALTER TABLE `ecs_article` CHANGE `cat_id` `cat_id` SMALLINT( 5 ) NOT NULL DEFAULT '0';
-- 訂單表增加“折扣”字段
ALTER TABLE `ecs_order_info` ADD `discount` DECIMAL( 10, 2 ) UNSIGNED NOT NULL DEFAULT '0';

--Mail最后發送
ALTER TABLE `ecs_mail_templates` ADD `last_send` INT( 10 ) UNSIGNED NOT NULL DEFAULT '0' AFTER `last_modify` ;

-- 修改紅包序號類型
ALTER TABLE `ecs_user_bonus` CHANGE `bonus_sn` `bonus_sn` BIGINT( 20 ) UNSIGNED NOT NULL DEFAULT '0';