--建表
--管理人员表 admin
--drop table WPADMIN cascade constraints;

/*==============================================================*/
/* Table: ADMIN                                                 */
/*==============================================================*/
create table WPADMIN 
(
   ADMIN_LOGINNAME      varchar2(10)         not null,
   ADMIN_PASSWORD       varchar2(10)         not null,
   ADMIN_NAME           varchar(4)           not null,
   constraint PK_ADMIN primary key (ADMIN_LOGINNAME)
);

comment on table WPADMIN is
‘管理员表‘;

comment on column WPADMIN.ADMIN_LOGINNAME is
‘管理员用户名‘;

comment on column WPADMIN.ADMIN_PASSWORD is
‘管理员密码‘;

comment on column WPADMIN.ADMIN_NAME is
‘管理员姓名‘;

--用户基本信息表  user
--drop table USERS cascade constraints;

/*==============================================================*/
/* Table: USERS                                                 */
/*==============================================================*/
create table USERS 
(
   USER_LOGINNAME       varchar2(10)         not null,
   USER_PASSWORD        varchar2(10)         not null,
   USER_NAME            varchar2(4)          not null,
   USER_ADRESS          varchar2(20)         not null,
   USER_PHONE           number(11)           not null,
   constraint PK_USERS primary key (USER_LOGINNAME)
);

comment on table USERS is
‘用户表‘;

comment on column USERS.USER_LOGINNAME is
‘用户用户名‘;

comment on column USERS.USER_PASSWORD is
‘用户密码‘;

comment on column USERS.USER_NAME is
‘用户姓名‘;

comment on column USERS.USER_ADRESS is
‘用户住址‘;

comment on column USERS.USER_PHONE is
‘用户联系方式‘;

--水表 water
--drop table WATER cascade constraints;

/*==============================================================*/
/* Table: WATER                                                 */
/*==============================================================*/
create table WATER 
(
   WATER_ID             varchar2(10)         not null,
   USERID               varchar2(10)         not null,
   WATER_TIME           date                 not null,
   WATER_COUNT          number(4,2)          not null,
   WATER_BEFORECOUNT    number(4,2)          not null,
   WATER_USE            number(4,2)          not null,
   WATER_PRICE          number(3,2)          not null,
   STATUS               number(1)            not null,
   constraint PK_WATER primary key (WATER_ID)
);

comment on table WATER is
‘水费表‘;

comment on column WATER.WATER_ID is
‘水费单id‘;

comment on column WATER.USERID is
‘用户id(用户用户名)‘;

comment on column WATER.WATER_TIME is
‘水费时间‘;

comment on column WATER.WATER_COUNT is
‘当月水表数‘;

comment on column WATER.WATER_BEFORECOUNT is
‘上月水表数‘;

comment on column WATER.WATER_USE is
‘当月用水量=当月水表数-上月水表数
water_use=water_count-water_beforecount‘;

comment on column WATER.WATER_PRICE is
‘当月水费‘;

comment on column WATER.STATUS is
‘缴费状态:0:未缴费  1:已缴费
‘;

--电表 power
--drop table POWER cascade constraints;

/*==============================================================*/
/* Table: POWER                                                 */
/*==============================================================*/
create table POWER 
(
   POWER_ID             varchar2(10)         not null,
   USERID               varchar2(10)         not null,
   POWER_TIME           date                 not null,
   POWER_COUNT          number(4,2)          not null,
   POWER_BEFORECOUNT    number(4,2)          not null,
   POWER_USE            number(4,2)          not null,
   POWER_PRICE          number(3,2)          not null,
   STATUS               number(1)            not null,
   constraint PK_POWER primary key (POWER_ID)
);

comment on table POWER is
‘电费表‘;

comment on column POWER.POWER_ID is
‘电费单id‘;

comment on column POWER.USERID is
‘用户id(用户用户名)‘;

comment on column POWER.POWER_TIME is
‘电费时间‘;

comment on column POWER.POWER_COUNT is
‘当月电表数‘;

comment on column POWER.POWER_BEFORECOUNT is
‘上月电表数‘;

comment on column POWER.POWER_USE is
‘当月用电量=当月电表数-上月电表数
power_use=power_count-power_beforecount‘;

comment on column POWER.POWER_PRICE is
‘当月电费‘;

comment on column POWER.STATUS is
‘缴费状态:0:未缴费  1:已缴费
‘;


--价格表 money
--drop table PRICE cascade constraints;

/*==============================================================*/
/* Table: PRICE                                                 */
/*==============================================================*/
create table PRICE 
(
   PRICEID              varchar2(10)         not null,
   "DATE"               date                 not null,
   WATER_PRICE          number(3,2)          not null,
   POWER_PRICE          number(3,2)          not null,
   ISACTIVE             number(1)            not null,
   constraint PK_PRICE primary key (PRICEID)
);

comment on table PRICE is
‘价格表‘;

comment on column PRICE.PRICEID is
‘价格id‘;

comment on column PRICE."DATE" is
‘当前价格时间‘;

comment on column PRICE.WATER_PRICE is
‘水费单价‘;

comment on column PRICE.POWER_PRICE is
‘电费单价‘;

comment on column PRICE.ISACTIVE is
‘价格状态:0:当前价格失效
                 1:当前价格有效‘;