-- 用户祖先信息表
CREATE TABLE `user_ancestor` (
`ancestor_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '祖先编号',
`user_id` INT(11) UNSIGNED NOT NULL COMMENT '用户编号',
`relation` VARCHAR(8) NOT NULL COMMENT '关系',
`name` VARCHAR(8) NOT NULL COMMENT '姓名',
`create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`ancestor_id`),
KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4 COMMENT='用户祖先信息表';

-- 用户祭拜祖先信息表
CREATE TABLE `prac_ancestor` (
`prac_id` BIGINT(20) UNSIGNED NOT NULL COMMENT '修行编号',
`ancestor_id` INT(11) UNSIGNED NOT NULL COMMENT '祖先编号',
`offering_types` VARCHAR(32) NOT NULL COMMENT '供品编号(#号分隔)',
`back` VARCHAR(1024) DEFAULT NULL COMMENT '回向文',
PRIMARY KEY (`prac_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COMMENT='用户祭拜祖先信息表';

-- 内容诵经经书表
CREATE TABLE `cont_scripture_read` (
`scripture_read_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '诵经经书编号',
`name` VARCHAR(32) NOT NULL COMMENT '名称',
`author` VARCHAR(32) NOT NULL COMMENT '作者',
`img_path` VARCHAR(256) DEFAULT NULL COMMENT '图片路径',
`audio_path` VARCHAR(256) DEFAULT NULL COMMENT '音频路径',
`content_path` VARCHAR(256) DEFAULT NULL COMMENT '歌词路径',
`audio_sec` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '音频秒数',
`read_count` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '诵读次数',
`create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`scripture_read_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COMMENT='内容诵经经书表';

-- 用户诵经信息表
CREATE TABLE `prac_scripture_read` (
`prac_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '修行编号',
`scripture_read_id` BIGINT(20) UNSIGNED NOT NULL COMMENT '诵经经书编号',
`audio_path` VARCHAR(256) NOT NULL COMMENT '音频路径',
`back` VARCHAR(1024) DEFAULT NULL COMMENT '回向文',
PRIMARY KEY (`prac_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COMMENT='用户诵经信息表';


-- 内容抄经经书表
CREATE TABLE `cont_scripture_write` (
`scripture_write_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '抄经经书编号',
`name` VARCHAR(64) NOT NULL COMMENT '经书名称',
`img_path` VARCHAR(256) DEFAULT NULL COMMENT '经书图片',
`content_path` VARCHAR(256) DEFAULT NULL COMMENT '经书原文路径',
`word_num` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '字数',
`write_count` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '抄写次数',
`create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`scripture_write_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COMMENT='内容抄经经书表';

-- 用户抄经信息表
CREATE TABLE `prac_write_scripture` (
`prac_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '修行编号',
`scripture_write_id` BIGINT(20) UNSIGNED NOT NULL COMMENT '抄经经书编号',
`img_path` VARCHAR(256) NOT NULL COMMENT '图片路径',
`back` VARCHAR(1024) DEFAULT NULL COMMENT '回向文',
PRIMARY KEY (`prac_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COMMENT='用户抄经信息表';

ALTER TABLE `cont_scripture` add COLUMN `scripture_write_id` BIGINT(20) UNSIGNED DEFAULT NULL COMMENT '抄经书编号' AFTER `view_count`;
ALTER TABLE `cont_scripture` add COLUMN `scripture_read_id` BIGINT(20) UNSIGNED DEFAULT NULL COMMENT '诵经书编号' AFTER `scripture_write_id`;


ALTER TABLE `user_prac_ext` add COLUMN `scripture_read_count` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '诵经次数' AFTER `repent_count`;
ALTER TABLE `user_prac_ext` add COLUMN `scripture_read_sec` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '诵经时长(秒数)' AFTER `scripture_read_count`;
ALTER TABLE `user_prac_ext` add COLUMN `scripture_write_count` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '抄经次数' AFTER `scripture_read_sec`;
ALTER TABLE `user_prac_ext` add COLUMN `scripture_write_word_num` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '抄经字数' AFTER `scripture_write_count`;
ALTER TABLE `user_prac_ext` add COLUMN `ancestor_count` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '祭拜祖先次数' AFTER `scripture_write_word_num`;

ALTER TABLE `user` add COLUMN `openid` VARCHAR(48) DEFAULT NULL COMMENT '微信openid' AFTER `phone`;
ALTER TABLE  `user`  ADD  UNIQUE (`openid` );
ALTER TABLE  `user`  ADD  INDEX (`nick_name` );


ALTER TABLE `stats_event` add COLUMN `user_id` INT(11) UNSIGNED DEFAULT 0 COMMENT '分享用户编号' AFTER `user_flag`;