/*结算用户*/
create Role settle 
    login  encrypted password '123456'   /* 'spok$!)@EN'  */
    valid until '2035-12-31 23:59:59' ;

/*结算库*/
CREATE DATABASE settledb
  WITH OWNER = settle
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       CONNECTION LIMIT = -1;


/*在模型中生成。
定义模型中所有对象的sql，需要修改，尤其是varchar的长度
要根据不同的数据库来定义模板*/

   /* 表 班级统计 definition */

 drop table if exists ClassesStat ;

 create table ClassesStat ( 
   classesId  BigInt  ,
   statDay  Timestamp  ,
   areaId  Integer  default 0,
   onlineAccount  Integer default 0 ,
   onlineHowmuch  Integer default 0 ,
   onlineYear  Integer default 0 ,
   onlineAmount  Integer default 0 ,
   onlineFee  Integer default 0 ,
   cashAccount  Integer default 0 ,
   cashHowmuch  Integer default 0 ,
   cashMonth  Integer default 0 ,
   cashAmount  Integer  default 0  ,
   cashFee  Integer  default 0  , 
   memo varchar( 1000 ) default '' ,
 primary key ( classesId , statDay ) ) ;

    
  
comment on  table ClassesStat is '班级统计' ;  
comment on  column  ClassesStat.classesId is '班级ID '; 
comment on  column  ClassesStat.statDay is '日期 '; 
comment on  column  ClassesStat.areaId is '地区 '; 
comment on  column  ClassesStat.onlineAccount is '平台支付账号数 '; 
comment on  column  ClassesStat.onlineHowmuch is '平台支付笔数 '; 
comment on  column  ClassesStat.onlineYear is '平台支付年数 '; 
comment on  column  ClassesStat.onlineAmount is '平台金额 '; 
comment on  column  ClassesStat.onlineFee is '平台出货金额 '; 
comment on  column  ClassesStat.cashAccount is '现金支付账号数 '; 
comment on  column  ClassesStat.cashHowmuch is '现金支付笔数 '; 
comment on  column  ClassesStat.cashMonth is '现金支付月数 '; 
comment on  column  ClassesStat.cashAmount is '现金金额 '; 
comment on  column  ClassesStat.cashFee is '现金支付出货金额 '; 
comment on  column  ClassesStat.memo is '备注'; 

      
   /* 表 学生个人统计 definition */

 drop table if exists StudentStat ;

 create table StudentStat ( 
   studentId  BigInt  ,
   statDay  Timestamp  ,
   areaId  Integer  ,
   onlineTime  Integer  ,
   studyTotalScore  Integer  ,
   studyCount  Integer  ,
   assignCount  Integer  ,
   examCount  Integer  ,
   goldencount  Integer  ,
   ispayed  Integer  ,
   amount  Integer  ,
   fee  Integer     , 
 primary key () ) ;

    
  
comment on  table StudentStat is '学生个人统计' ;  
comment on  column  StudentStat.studentId is '学生ID '; 
comment on  column  StudentStat.statDay is '日期 '; 
comment on  column  StudentStat.areaId is '地区ID '; 
comment on  column  StudentStat.onlineTime is '在线时间 '; 
comment on  column  StudentStat.studyTotalScore is '学习总分 '; 
comment on  column  StudentStat.studyCount is '学习总次数 '; 
comment on  column  StudentStat.assignCount is '完成作业次数 '; 
comment on  column  StudentStat.examCount is '完成考试次数 '; 
comment on  column  StudentStat.goldencount is '金牌数 '; 
comment on  column  StudentStat.ispayed is '是否支付 '; 
comment on  column  StudentStat.amount is '支付价 '; 
comment on  column  StudentStat.fee is '出货价 '; 

      
   /* 表 教师个人统计 definition */

 drop table if exists TeacherStat ;

 create table TeacherStat ( 
   teacherId  BigInt  ,
   statDay  Timestamp  ,
   areaId  Integer  ,
   assignCount  Integer  ,
   assignDegree  Integer  ,
   messageCount  Integer  ,
   examCount  Integer     , 
 primary key () ) ;

    
  
comment on  table TeacherStat is '教师个人统计' ;  
comment on  column  TeacherStat.teacherId is '教师ID '; 
comment on  column  TeacherStat.statDay is '日期 '; 
comment on  column  TeacherStat.areaId is '地区ID '; 
comment on  column  TeacherStat.assignCount is '作业布置次数 '; 
comment on  column  TeacherStat.assignDegree is '平均作业完成度 '; 
comment on  column  TeacherStat.messageCount is '发布公告次数 '; 
comment on  column  TeacherStat.examCount is '发布考试次数 ';