openbilibili/app/interface/main/mcn/dao/mcn.sql

-- mcn签约表
drop table if exists mcn_sign;
create table mcn_sign (
  id int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  mcn_mid int(11) unsigned NOT NULL DEFAULT 0 COMMENT 'mcn的mid',
  company_name varchar(32) NOT NULL DEFAULT '' COMMENT '企业名称',
  company_license_id varchar(32) NOT NULL DEFAULT '' COMMENT '营业执照注册号',
  company_license_link varchar(255) NOT NULL DEFAULT '' COMMENT '营业执照链接',
  contract_link varchar(255) NOT NULL DEFAULT '' COMMENT '合同链接',
  contact_name varchar(16) NOT NULL DEFAULT '' COMMENT '对接人姓名',
  contact_title varchar(16) NOT NULL DEFAULT '' COMMENT '对接人职务',
  contact_idcard varchar(32) NOT NULL DEFAULT '' COMMENT '对接人身份证号',
  contact_phone varchar(16) NOT NULL DEFAULT '' COMMENT '对接人手机号',
  begin_date date NOT NULL DEFAULT '0000-00-00' COMMENT '合同开始时间',
  end_date date NOT NULL DEFAULT '0000-00-00' COMMENT '合同结束时间',
  reject_reason varchar(255) NOT NULL DEFAULT '' COMMENT '驳回理由',
	`reject_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '驳回时间',
	`pay_expire_state` tinyint(4) NOT NULL DEFAULT '1' COMMENT '付款到期状态:1:未到期 2:即将到期',
  state tinyint(4) NOT NULL DEFAULT 0 COMMENT '状态,0未申请，1待审核，2已驳回，10已签约，11冷却中，12已到期，13封禁，14清退, 15待开启，100移除',
  ctime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
  mtime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`),
  KEY `ix_mcn_mid` (`mcn_mid`),
	KEY `ix_mtime` (`mtime`),
	KEY `ix_state` (`state`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='mcn签约表';

-- mcn付款表
drop table if exists mcn_sign_pay;
CREATE TABLE mcn_sign_pay (
  id int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  mcn_mid int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'mcn mid',
  sign_id int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'mcn签约ID',
  due_date date NOT NULL DEFAULT '0000-00-00' COMMENT '付款时间',
  pay_value BIGINT(20) NOT NULL DEFAULT '0' COMMENT '金额',
  state tinyint(4) NOT NULL DEFAULT '0' COMMENT '状态，0未支付，1已支付，100删除',
  note varchar(255) NOT NULL DEFAULT '' COMMENT '备注',
  ctime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  mtime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  PRIMARY KEY (id),
  KEY ix_signid (sign_id),
  KEY ix_mcn_mid (mcn_mid),
  KEY ix_generate_date (due_date),
  KEY ix_mtime (mtime)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='付款表';


-- mcn up绑定表
drop table if exists mcn_up;
CREATE TABLE mcn_up (
  id int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  sign_id int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'mcn签约ID',
  mcn_mid int(11) unsigned NOT NULL DEFAULT 0 COMMENT 'mcn的mid',
  up_mid int(11) unsigned NOT NULL DEFAULT 0 COMMENT '绑定up的mid',
  begin_date date NOT NULL DEFAULT '0000-00-00' COMMENT '合同开始时间',
  end_date date NOT NULL DEFAULT '0000-00-00' COMMENT '合同结束时间',
  contract_link varchar(255) NOT NULL DEFAULT '' COMMENT '与up合同链接',
  up_auth_link varchar(255) NOT NULL DEFAULT '' COMMENT 'up授权协议链接',
  reject_reason varchar(255) NOT NULL DEFAULT '' COMMENT '驳回理由',
  reject_time timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '驳回时间',
  state tinyint(4) NOT NULL DEFAULT '0' COMMENT '状态，0未授权，1已拒绝，2审核中，3已驳回，10已签约，11已冻结，12已到期，13封禁，14已解约，100删除',
  state_change_time timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '状态变化时间',
  ctime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  mtime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  PRIMARY KEY (id),
  UNIQUE KEY uk_sign_id_mcn_mid_up_mid (sign_id, mcn_mid, up_mid),
  KEY ix_up_mid(up_mid),
  KEY ix_mtime (mtime)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='mcn绑定up表';


-- 数据相关表
-- 1。mcn整体数据表
-- 2。mcn下各up主数据表
-- 3。Top稿件表
-- 1。mcn整体数据表
drop table if exists mcn_data_summary;
create table mcn_data_summary (
  id int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  mcn_mid int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'mcn mid',
  sign_id int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'mcn签约ID',
  up_count int(11) unsigned NOT NULL DEFAULT '0' COMMENT '签约UP主数',
  fans_count_accumulate int(11) unsigned NOT NULL DEFAULT '0' COMMENT '累计粉丝量',
  fans_count_online int(11) unsigned NOT NULL DEFAULT '0' COMMENT '线上涨粉量',
  fans_count_real int(11) unsigned NOT NULL DEFAULT '0' COMMENT '实际涨粉量',
  fans_count_cheat_accumulate int(11) unsigned NOT NULL DEFAULT '0' COMMENT '累计作弊粉丝',
  fans_count_increase_day int(11) unsigned NOT NULL DEFAULT '0' COMMENT '当日新增粉丝数',
  play_count_accumulate int(11) unsigned NOT NULL DEFAULT '0' COMMENT '累计播放数',
  play_count_increase_day int(11) unsigned NOT NULL DEFAULT '0' COMMENT '当日新增播放数',
  archive_count_accumulate int(11) unsigned NOT NULL DEFAULT '0' COMMENT '累计投稿量',
  active_tid smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '分区,表示某个分区',
  generate_date date NOT NULL DEFAULT '0000-00-00' COMMENT '计算日',
  data_type tinyint(4) NOT NULL DEFAULT '0' COMMENT '数据类型，1按天，2按月',
  `ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_sign_id_generate_date_active_tid_data_type` (sign_id, generate_date, active_tid, data_type),
  KEY ix_mcn_mid (mcn_mid),
  KEY `ix_mtime` (`mtime`),
  KEY `ix_generate_date` (`generate_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='mcn整体数据';

drop table if exists mcn_data_up_detail;
create table mcn_data_up_detail (
  id int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  mcn_mid int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'mcn mid',
  sign_id int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'mcn签约ID',
  up_mid int(11) unsigned NOT NULL DEFAULT '0' COMMENT '签约UP主ID',
  fans_count_accumulate int(11) unsigned NOT NULL DEFAULT '0' COMMENT '累计粉丝量',
  fans_count_online int(11) unsigned NOT NULL DEFAULT '0' COMMENT '线上涨粉量',
  fans_count_real int(11) unsigned NOT NULL DEFAULT '0' COMMENT '实际涨粉量',
  fans_count_cheat_accumulate int(11) unsigned NOT NULL DEFAULT '0' COMMENT '累计作弊粉丝',
  fans_count_increase_day int(11) unsigned NOT NULL DEFAULT '0' COMMENT '当日新增粉丝数',
  play_count_accumulate int(11) unsigned NOT NULL DEFAULT '0' COMMENT '累计播放数',
  play_count_increase_day int(11) unsigned NOT NULL DEFAULT '0' COMMENT '当日新增播放数',
  archive_count_accumulate int(11) unsigned NOT NULL DEFAULT '0' COMMENT '累计投稿量',
  active_tid smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT 'Up所属分区',
  generate_date date NOT NULL DEFAULT '0000-00-00' COMMENT '计算日',
  data_type tinyint(4) NOT NULL DEFAULT '0' COMMENT '数据类型，1按天，2按月',
  `ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_sign_id_generate_date_data_type_up_mid` (sign_id, generate_date, data_type, up_mid),
  KEY ix_mcn_mid (mcn_mid),
  KEY `ix_mtime` (`mtime`),
  KEY `ix_generate_date` (`generate_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='mcn up整体数据';

-- 2。mcn下各up主数据表
drop table if exists mcn_data_up;
create table mcn_data_up (
  id int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  mcn_mid int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'mcn mid',
  sign_id int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'mcn签约ID',
  up_mid int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'up的mid',
  data_type tinyint(4) NOT NULL DEFAULT '0' COMMENT '数据类型，1累计，2昨日，3上周，4上月',
  fans_increase_accumulate int(11) unsigned NOT NULL default '0' COMMENT '粉丝数增涨量',
  archive_count int(11) unsigned NOT NULL default '0' COMMENT '投搞量',
  play_count int(11) unsigned NOT NULL default '0' COMMENT '播放量',
  fans_increase_month int(11) unsigned NOT NULL default '0' COMMENT '近一个月涨粉量',
  fans_count int(11) unsigned NOT NULL DEFAULT '0' COMMENT '粉丝总量',
  fans_count_active int(11) unsigned NOT NULL DEFAULT '0' COMMENT '活跃粉丝总量',
  generate_date date NOT NULL DEFAULT '0000-00-00' COMMENT '计算日',
  `ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_sign_id_generate_date_data_type` (sign_id, generate_date, data_type),
  KEY ix_mcn_mid (mcn_mid),
  KEY ix_up_mid (up_mid),
  KEY `ix_mtime` (`mtime`),
  KEY `ix_generate_date` (`generate_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='mcn下up数据';



-- alter table mcn_up_test add column state_change_time timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '状态变化时间';

-- 增加字段, fat 1, uat 1, prod 1
alter table mcn_data_summary add column fans_count_real_accumulate bigint(20)  NOT NULL DEFAULT '0' COMMENT '累计实际涨粉量';
alter table mcn_data_summary add column fans_count_online_accumulate bigint(20)  NOT NULL DEFAULT '0' COMMENT '累计线上涨粉量';
alter table mcn_data_summary add column archive_count_day int(11) NOT NULL DEFAULT '0' COMMENT '当日新增投稿量';

alter table mcn_data_up_detail add column fans_count_real_accumulate bigint(20) NOT NULL DEFAULT '0' COMMENT '累计实际涨粉量';
alter table mcn_data_up_detail add column fans_count_online_accumulate bigint(20) NOT NULL DEFAULT '0' COMMENT '累计线上涨粉量';
alter table mcn_data_up_detail add column archive_count_day int(11) NOT NULL DEFAULT '0' COMMENT '当日新增投稿量';

-- 修改数据字段类型，去掉unsigned， 修改播放相关的为bigint, fat 1, uat 1, prod 1
alter table mcn_data_summary modify column fans_count_accumulate int(11) NOT NULL DEFAULT '0' COMMENT '累计粉丝量';
alter table mcn_data_summary modify column fans_count_online int(11) NOT NULL DEFAULT '0' COMMENT '线上涨粉量';
alter table mcn_data_summary modify column fans_count_real int(11) NOT NULL DEFAULT '0' COMMENT '实际涨粉量';
alter table mcn_data_summary modify column fans_count_cheat_accumulate int(11) NOT NULL DEFAULT '0' COMMENT '累计作弊粉丝';
alter table mcn_data_summary modify column fans_count_increase_day int(11) NOT NULL DEFAULT '0' COMMENT '当日新增粉丝数';
alter table mcn_data_summary modify column play_count_accumulate int(11) NOT NULL DEFAULT '0' COMMENT '累计播放数';
alter table mcn_data_summary modify column play_count_increase_day int(11) NOT NULL DEFAULT '0' COMMENT '当日新增播放数';
alter table mcn_data_summary modify column archive_count_accumulate int(11) NOT NULL DEFAULT '0' COMMENT '累计投稿量';

alter table mcn_data_up_detail modify column fans_count_accumulate int(11) NOT NULL DEFAULT '0' COMMENT '累计粉丝量';
alter table mcn_data_up_detail modify column fans_count_online int(11) NOT NULL DEFAULT '0' COMMENT '线上涨粉量';
alter table mcn_data_up_detail modify column fans_count_real int(11) NOT NULL DEFAULT '0' COMMENT '实际涨粉量';
alter table mcn_data_up_detail modify column fans_count_cheat_accumulate int(11) NOT NULL DEFAULT '0' COMMENT '累计作弊粉丝';
alter table mcn_data_up_detail modify column fans_count_increase_day int(11) NOT NULL DEFAULT '0' COMMENT '当日新增粉丝数';
alter table mcn_data_up_detail modify column play_count_accumulate int(11) NOT NULL DEFAULT '0' COMMENT '累计播放数';
alter table mcn_data_up_detail modify column play_count_increase_day int(11) NOT NULL DEFAULT '0' COMMENT '当日新增播放数';
alter table mcn_data_up_detail modify column archive_count_accumulate int(11) NOT NULL DEFAULT '0' COMMENT '累计投稿量';

alter table mcn_data_up modify column fans_increase_accumulate int(11) NOT NULL default '0' COMMENT '粉丝数增涨量';
alter table mcn_data_up modify column archive_count int(11) NOT NULL default '0' COMMENT '投搞量';
alter table mcn_data_up modify column play_count bigint(20) NOT NULL default '0' COMMENT '播放量';
alter table mcn_data_up modify column fans_increase_month int(11) NOT NULL default '0' COMMENT '近一个月涨粉量';
alter table mcn_data_up modify column fans_count int(11) NOT NULL DEFAULT '0' COMMENT '粉丝总量';
alter table mcn_data_up modify column fans_count_active int(11) NOT NULL DEFAULT '0' COMMENT '活跃粉丝总量';

-- fat 1, uat 1, prod 1
alter table mcn_data_summary modify column play_count_accumulate bigint(20) NOT NULL DEFAULT '0' COMMENT '累计播放数';
alter table mcn_data_summary modify column play_count_increase_day bigint(20) NOT NULL DEFAULT '0' COMMENT '当日/月新增播放数';
alter table mcn_data_up_detail modify column play_count_accumulate bigint(20) NOT NULL DEFAULT '0' COMMENT '累计播放数';
alter table mcn_data_up_detail modify column play_count_increase_day bigint(20) NOT NULL DEFAULT '0' COMMENT '当日/月新增播放数';
alter table mcn_sign modify column reject_time datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '驳回时间';

-- fat 1, uat 1, prod 1, 增加时间上的索引
create index ix_end_date on mcn_sign (end_date);
create index ix_begin_date on mcn_sign (begin_date);
create index ix_end_date on mcn_up (end_date);
create index ix_begin_date on mcn_up (begin_date);

--------- 2期
-- fat 1, uat 1, prod 1, 增加表
alter table mcn_up add column up_type tinyint(4) not null default '0' comment '用户类型，0为站内，1为站外';
alter table mcn_up add column site_link varchar(255) not null default '' comment 'up主站外账号链接';

-- mcn_data_import_up: table
CREATE TABLE `mcn_data_import_up` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `mcn_mid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'mcn mid',
  `sign_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'mcn签约ID',
  `up_mid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'UP主 mid',
  `standard_fans_type` tinyint(4) NOT NULL DEFAULT '0' COMMENT '达标粉丝数类型, 1: 1w粉丝',
  `standard_fans_date` int(11) NOT NULL DEFAULT '0' COMMENT '达到粉丝数门槛花费的时间，秒',
  `standard_archive_count` int(11) NOT NULL DEFAULT '0' COMMENT '达标时投稿量',
  `standard_fans_count` int(11) NOT NULL DEFAULT '0' COMMENT '达标时粉丝数',
  `is_reward` int(11) NOT NULL DEFAULT '0' COMMENT '奖励情况 0:未奖励 1:已奖励',
  `ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_sign_id_mid_type` (`sign_id`,`up_mid`,`standard_fans_type`),
  KEY `ix_mtime` (`mtime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='引入账号数据表';

-- mcn_up_recommend_pool: table
CREATE TABLE `mcn_up_recommend_pool` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `up_mid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'up mid',
  `fans_count` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '粉丝量',
  `fans_count_increase_month` int(11) NOT NULL DEFAULT '0' COMMENT '本月粉丝增长量',
  `archive_count` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '总稿件数',
  `play_count_accumulate` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '累积播放量',
  `play_count_average` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '稿均播放量',
  `active_tid` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '分区,表示某个分区',
  `last_archive_time` datetime NOT NULL DEFAULT '1970-01-01 08:00:00' COMMENT '最近投稿时间',
  `state` tinyint(4) unsigned NOT NULL DEFAULT '1' COMMENT '推荐池状态: 1:未推荐 2:推荐 3:禁止推荐 100:移除',
  `source` tinyint(4) unsigned NOT NULL DEFAULT '1' COMMENT '推荐池来源: 1:自动添加(大数据)  2:手动添加',
  `generate_time` datetime NOT NULL DEFAULT '1970-01-01 08:00:00' COMMENT '大数据更新时间',
  `ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_up_mid` (`up_mid`),
  KEY `ix_state` (`state`),
  KEY `ix_active_tid` (`active_tid`),
  KEY `ix_fans_count` (`fans_count`),
  KEY `ix_play_count_accumulate` (`play_count_accumulate`),
  KEY `ix_play_count_average` (`play_count_average`),
  KEY `ix_fans_count_increase_month` (`fans_count_increase_month`),
  KEY `ix_source` (`source`),
  KEY `ix_generate_time` (`generate_time`),
  KEY `ix_mtime` (`mtime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='mcn-up主推荐池';

-- mcn_up_recommend_source: table
CREATE TABLE `mcn_up_recommend_source` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `up_mid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'up mid',
  `fans_count` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '粉丝量',
  `fans_count_increase_month` int(11) NOT NULL DEFAULT '0' COMMENT '本月粉丝增长量',
  `archive_count` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '总稿件数',
  `play_count_accumulate` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '累积播放量',
  `play_count_average` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '稿均播放量',
  `active_tid` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '分区,表示某个分区',
  `last_archive_time` datetime NOT NULL DEFAULT '1970-01-01 08:00:00' COMMENT '最近投稿时间',
  `ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  PRIMARY KEY (`id`),
  KEY `ix_up_mid` (`up_mid`),
  KEY `ix_mtime` (`mtime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='mcn-up主推荐池的来源(大数据提供)';

-- fat 1, uat 1, prod 0
-- 1。涨粉量排名
create table mcn_rank_up_fans (
  id int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  mcn_mid int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'mcn mid',
  sign_id int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'mcn签约ID',
  up_mid int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'up的mid',
  value1 int(11) NOT NULL default '0' COMMENT '数据1',
  value2 int(11) NOT NULL default '0' COMMENT '数据2',
  active_tid smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '分区,表示某个分区',
  data_type tinyint(4) NOT NULL DEFAULT '0' COMMENT '数据类型，1累计（总榜），2昨日，3上周，4上月，5活跃粉丝(累计)',
  generate_date date NOT NULL DEFAULT '0000-00-00' COMMENT '计算日',
  ctime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  mtime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_sign_id_generate_date_data_type_up_mid` (sign_id, generate_date, data_type, up_mid),
  KEY ix_mcn_mid (mcn_mid),
  KEY ix_up_mid (up_mid),
  KEY `ix_mtime` (`mtime`),
  KEY `ix_generate_date` (`generate_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='mcn下up涨粉量排名';

-- 2。Top稿件表
create table mcn_rank_archive_likes (
  id int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  mcn_mid int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'mcn mid',
  sign_id int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'mcn签约ID',
  up_mid int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'up的mid',
  archive_id bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '稿件id',
  like_count bigint(20) NOT NULL DEFAULT '0' COMMENT '日/周/月新增点赞数，根据data_type统计',
  data_type tinyint(4) NOT NULL DEFAULT '0' COMMENT '数据类型，1累计，2昨日，3上周，4上月',
  tid smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '分区ID',
  generate_date date NOT NULL DEFAULT '0000-00-00' COMMENT '计算日',
  ctime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  mtime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  PRIMARY KEY (`id`),
  KEY ix_mcn_mid (mcn_mid),
  KEY `ix_mtime` (`mtime`),
  KEY `ix_generate_date` (`generate_date`),
  UNIQUE KEY `uk_sign_id_generate_date_data_type_archive_id` (sign_id, generate_date, data_type, archive_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='mcn下top稿件表';



-- mcn_data_up_cheat: table
CREATE TABLE `mcn_data_up_cheat` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `mcn_mid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'mcn mid',
  `sign_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'mcn签约ID',
  `up_mid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'up主 mid',
  `generate_date` date NOT NULL DEFAULT '0000-00-00' COMMENT '计算日',
  `fans_count_cheat_increase_day` int(11) NOT NULL DEFAULT '0' COMMENT '新增作弊粉丝量',
  `fans_count_cheat_cleaned_accumulate` int(11) NOT NULL DEFAULT '0' COMMENT '已清除粉丝量',
  `ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_up_mid_sign_id_generate_date` (`up_mid`,`sign_id`,`generate_date`),
  KEY `ix_mcn_mid` (`mcn_mid`),
  KEY `ix_mtime` (`mtime`),
  KEY `ix_generate_date` (`generate_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='作弊筛选详情表'
;

-- fat 1, uat 1, prod 0
ALTER TABLE mcn_rank_archive_likes CHANGE archive_id avid bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '稿件id';

ALTER TABLE `bilibili_upcrm`.`mcn_data_up_cheat`
  ADD COLUMN `fans_count_cheat_accumulate` int(11) NOT NULL DEFAULT '0' COMMENT '累计作弊粉丝',
  ADD COLUMN `fans_count_accumulate` int(11) NOT NULL DEFAULT '0' COMMENT '实际粉丝量';

-- fat 1, uat 1, prod 0
ALTER TABLE mcn_up ADD COLUMN confirm_time timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'up确认时间';


--------四期-------
alter table mcn_sign add permission int(11) unsigned default '1' not null comment '权限列表-属性位';
alter table mcn_up
add permission int(11) unsigned default '1' not null comment '权限列表-属性位',
add publication_price bigint default '0' not null comment '刊例价(千分位*1000)';