CREATE TABLE IF NOT EXISTS APPLICATION
(
    ID              VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    CONTEXT_PATH    VARCHAR(20)  NOT NULL,
    NAME            VARCHAR(50)  NOT NULL,
    TYPE            VARCHAR(10)  NOT NULL,
    VALUE           VARCHAR(255) NOT NULL,
    PATH            VARCHAR(255) NOT NULL,
    INSERT_DATETIME TIMESTAMP(3),
    UPDATE_DATETIME TIMESTAMP(3)
);
COMMENT ON TABLE APPLICATION IS '服务资源';
COMMENT ON COLUMN APPLICATION.CONTEXT_PATH IS '服务名称';
COMMENT ON COLUMN APPLICATION.NAME IS '名称';
COMMENT ON COLUMN APPLICATION.TYPE IS '类型';
COMMENT ON COLUMN APPLICATION.VALUE IS '路径';
COMMENT ON COLUMN APPLICATION.PATH IS '服务名称+路径';
COMMENT ON COLUMN APPLICATION.INSERT_DATETIME IS '创建时间';
COMMENT ON COLUMN APPLICATION.UPDATE_DATETIME IS '更新时间';

CREATE TABLE IF NOT EXISTS CUSTOMER
(
    ID               VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    NAME             VARCHAR(50),
    ID_CARD          CHAR(18),
    ID_CARD_FACE     TEXT,
    ID_CARD_NATIONAL TEXT,
    FACE             TEXT,
    PHONE            CHAR(11),
    SESSION_KEY      VARCHAR(50),
    APPID            VARCHAR(50),
    OPENID           VARCHAR(50),
    ALIPAY_APPID     VARCHAR(50),
    ALIPAY_OPENID    VARCHAR(50),
    USERNAME         VARCHAR(50)
        CONSTRAINT UK_CUSTOMER_USERNAME UNIQUE,
    PASSWORD         VARCHAR(255),
    INSERT_DATETIME  TIMESTAMP(0)            DEFAULT now(),
    UPDATE_DATETIME  TIMESTAMP(0)            DEFAULT now()
);
COMMENT ON TABLE CUSTOMER IS '营业员';
COMMENT ON COLUMN CUSTOMER.NAME IS '姓名';
COMMENT ON COLUMN CUSTOMER.ID_CARD IS '身份证';
COMMENT ON COLUMN CUSTOMER.ID_CARD_FACE IS '身份证A面';
COMMENT ON COLUMN CUSTOMER.ID_CARD_NATIONAL IS '身份证B面';
COMMENT ON COLUMN CUSTOMER.FACE IS '人脸';
COMMENT ON COLUMN CUSTOMER.PHONE IS '手机号';
COMMENT ON COLUMN CUSTOMER.SESSION_KEY IS '小程序SESSION_KEY';
COMMENT ON COLUMN CUSTOMER.APPID IS '微信支付应用ID';
COMMENT ON COLUMN CUSTOMER.OPENID IS '用户在微信支付APPID下的唯一标识';
COMMENT ON COLUMN CUSTOMER.ALIPAY_APPID IS '支付宝应用ID';
COMMENT ON COLUMN CUSTOMER.ALIPAY_OPENID IS '支付宝用户在APPID下的唯一标识';
COMMENT ON COLUMN CUSTOMER.USERNAME IS '登录账户';
COMMENT ON COLUMN CUSTOMER.PASSWORD IS '密码';
COMMENT ON COLUMN CUSTOMER.INSERT_DATETIME IS '创建时间';
COMMENT ON COLUMN CUSTOMER.UPDATE_DATETIME IS '更新时间';

CREATE TABLE IF NOT EXISTS MERCHANT
(
    ID              VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    CATEGORY        VARCHAR(20)  NOT NULL,
    SHORTNAME       VARCHAR(20)  NOT NULL,
    PASSWORD        VARCHAR(255) NOT NULL,
    TRADEMARK       TEXT,
    OPEN_TIME       TIME         NOT NULL,
    CLOSE_TIME      TIME         NOT NULL,
    DESCRIPTION     VARCHAR(255),
    PICTURES        TEXT[],
    VIDEOS          TEXT[],
    STATUS          BOOLEAN                 DEFAULT FALSE,
    CUSTOMER_ID     VARCHAR(36)  NOT NULL,
    USERNAME        VARCHAR(20)  NOT NULL,
    PHONE           VARCHAR(11)  NOT NULL,
    LOCATION        POINT        NOT NULL,
    POSTCODE        CHAR(6)      NOT NULL,
    ADDRESS_VALUE   TEXT[]       NOT NULL,
    ADDRESS_CODE    TEXT[]       NOT NULL,
    ADDRESS_DETAIL  VARCHAR(50)  NOT NULL,
    ADDRESS_NAME    VARCHAR(50)  NOT NULL,
    SUB_MCH_ID      VARCHAR(50),
    SELLER_ID       VARCHAR(50),
    INSERT_DATETIME TIMESTAMP(0)            DEFAULT now(),
    UPDATE_DATETIME TIMESTAMP(0)            DEFAULT now()
);
COMMENT ON TABLE MERCHANT IS '主体';
COMMENT ON COLUMN MERCHANT.CATEGORY IS '分类';
COMMENT ON COLUMN MERCHANT.ADDRESS_DETAIL IS '详细地址';
COMMENT ON COLUMN MERCHANT.ADDRESS_NAME IS '地址名称';
COMMENT ON COLUMN MERCHANT.CLOSE_TIME IS '关闭时间';
COMMENT ON COLUMN MERCHANT.CUSTOMER_ID IS '营业员ID';
COMMENT ON COLUMN MERCHANT.OPEN_TIME IS '开门时间';
COMMENT ON COLUMN MERCHANT.PHONE IS '联系方式';
COMMENT ON COLUMN MERCHANT.LOCATION IS '坐标';
COMMENT ON COLUMN MERCHANT.SHORTNAME IS '简称';
COMMENT ON COLUMN MERCHANT.PASSWORD IS '主体密码';
COMMENT ON COLUMN MERCHANT.TRADEMARK IS 'LOGO';
COMMENT ON COLUMN MERCHANT.USERNAME IS '联系人';
COMMENT ON COLUMN MERCHANT.INSERT_DATETIME IS '创建时间';
COMMENT ON COLUMN MERCHANT.UPDATE_DATETIME IS '更新时间';
COMMENT ON COLUMN MERCHANT.POSTCODE IS '邮编';
COMMENT ON COLUMN MERCHANT.ADDRESS_VALUE IS '省市区';
COMMENT ON COLUMN MERCHANT.ADDRESS_CODE IS '省市区编号';
COMMENT ON COLUMN MERCHANT.STATUS IS '是否营业';
COMMENT ON COLUMN MERCHANT.DESCRIPTION IS '主体介绍';
COMMENT ON COLUMN MERCHANT.PICTURES IS '图集';
COMMENT ON COLUMN MERCHANT.SUB_MCH_ID IS '特约主体编号';
COMMENT ON COLUMN MERCHANT.SELLER_ID IS '卖家支付宝用户ID';
COMMENT ON COLUMN MERCHANT.VIDEOS IS '视频ID';

CREATE TABLE IF NOT EXISTS MERCHANT_CUSTOMER
(
    ID          VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    CUSTOMER_ID VARCHAR(36) NOT NULL
        CONSTRAINT MERCHANT_CUSTOMER_CUSTOMER_ID_FK REFERENCES CUSTOMER ON DELETE CASCADE,
    MERCHANT_ID VARCHAR(36) NOT NULL
);
COMMENT ON TABLE MERCHANT_CUSTOMER IS '营业员与主体关联';
COMMENT ON COLUMN MERCHANT_CUSTOMER.CUSTOMER_ID IS '营业员ID';
COMMENT ON COLUMN MERCHANT_CUSTOMER.MERCHANT_ID IS '主体ID';
CREATE UNIQUE INDEX IF NOT EXISTS MERCHANT_CUSTOMER_VID_CID_UINDEX ON MERCHANT_CUSTOMER (MERCHANT_ID, CUSTOMER_ID);

CREATE TABLE IF NOT EXISTS MERCHANT_CUSTOMER_APPLICATION
(
    ID             VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    CUSTOMER_ID    VARCHAR(36) NOT NULL
        CONSTRAINT MERCHANT_CUSTOMER_APPLICATION_CUSTOMER_ID_FK REFERENCES CUSTOMER ON DELETE CASCADE,
    APPLICATION_ID VARCHAR(36) NOT NULL
        CONSTRAINT MERCHANT_CUSTOMER_APPLICATION_APPLICATION_ID_FK REFERENCES APPLICATION ON DELETE CASCADE,
    MERCHANT_ID    VARCHAR(36) NOT NULL
);
COMMENT ON TABLE MERCHANT_CUSTOMER_APPLICATION IS '主体营业员与服务资源关联';
COMMENT ON COLUMN MERCHANT_CUSTOMER_APPLICATION.CUSTOMER_ID IS '营业员ID';
COMMENT ON COLUMN MERCHANT_CUSTOMER_APPLICATION.APPLICATION_ID IS '服务资源ID';
COMMENT ON COLUMN MERCHANT_CUSTOMER_APPLICATION.MERCHANT_ID IS '主体ID';

CREATE TABLE IF NOT EXISTS HOUSE
(
    ID            VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    MERCHANT_ID   VARCHAR(36)    NOT NULL,
    FLOOR_NUMBER  VARCHAR(50),
    UNIT          VARCHAR(50),
    ROOM_NUMBER   VARCHAR(50),
    TYPE          VARCHAR(50)    NOT NULL,
    AREA          NUMERIC(20, 2) NOT NULL,
    PEOPLE_NUMBER SMALLINT       NOT NULL DEFAULT 0,
    PARKING_COUNT SMALLINT       NOT NULL DEFAULT 0,
    REMARKS       VARCHAR(255)
);
COMMENT ON TABLE HOUSE IS '房屋';
COMMENT ON COLUMN HOUSE.MERCHANT_ID IS '主体ID';
COMMENT ON COLUMN HOUSE.FLOOR_NUMBER IS '楼号';
COMMENT ON COLUMN HOUSE.UNIT IS '单元';
COMMENT ON COLUMN HOUSE.ROOM_NUMBER IS '房号';
COMMENT ON COLUMN HOUSE.TYPE IS '类型:住宅、商铺、公寓、办公室';
COMMENT ON COLUMN HOUSE.AREA IS '房屋面积：平方米';
COMMENT ON COLUMN HOUSE.PEOPLE_NUMBER IS '人数';
COMMENT ON COLUMN HOUSE.PARKING_COUNT IS '车位数量';
COMMENT ON COLUMN HOUSE.REMARKS IS '备注';
CREATE UNIQUE INDEX IF NOT EXISTS HOUSE_MERCHANT_ID_FLOOR_NUMBER_UNIT_ROOM_NUMBER_UINDEX
    ON HOUSE (MERCHANT_ID, FLOOR_NUMBER, UNIT, ROOM_NUMBER);

CREATE TABLE IF NOT EXISTS HOUSE_RENTING
(
    ID                   VARCHAR(36) PRIMARY KEY,
    MERCHANT_ID          VARCHAR(36)    NOT NULL,
    LOCATION             POINT          NOT NULL,
    CUSTOMER_ID          VARCHAR(36),
    FLOOR_NUMBER         VARCHAR(50)    NOT NULL,
    UNIT                 VARCHAR(50)    NOT NULL,
    ROOM_NUMBER          VARCHAR(50)    NOT NULL,
    MODE                 VARCHAR(50)    NOT NULL,
    TYPE                 VARCHAR(128)   NOT NULL,
    PRICE                NUMERIC(20, 2) NOT NULL,
    NAME                 VARCHAR(128)   NOT NULL,
    PHONE                VARCHAR(11)    NOT NULL,
    TITLE                VARCHAR(128)   NOT NULL,
    DESCRIPTION          VARCHAR(255)   NOT NULL,
    DESCRIPTION_PICTURES TEXT[]         NOT NULL,
    DESIRED_POSITION     POINT,
    DESIRED_NAME         VARCHAR(255),
    DESIRED_ADDRESS      VARCHAR(255),
    insert_datetime      timestamp(0)   not null default now()
);
COMMENT ON TABLE HOUSE_RENTING IS '房屋租售';
COMMENT ON COLUMN HOUSE_RENTING.MERCHANT_ID IS '主体ID';
COMMENT ON COLUMN HOUSE_RENTING.LOCATION IS '位置';
COMMENT ON COLUMN HOUSE_RENTING.CUSTOMER_ID IS '账号ID';
COMMENT ON COLUMN HOUSE_RENTING.FLOOR_NUMBER IS '楼号';
COMMENT ON COLUMN HOUSE_RENTING.UNIT IS '单元';
COMMENT ON COLUMN HOUSE_RENTING.ROOM_NUMBER IS '房号';
COMMENT ON COLUMN HOUSE_RENTING.MODE IS '方式:整租、合租、公寓、出售';
COMMENT ON COLUMN HOUSE_RENTING.TYPE IS '户型';
COMMENT ON COLUMN HOUSE_RENTING.PRICE IS '价格';
COMMENT ON COLUMN HOUSE_RENTING.NAME IS '姓名';
COMMENT ON COLUMN HOUSE_RENTING.PHONE IS '电话';
COMMENT ON COLUMN HOUSE_RENTING.TITLE IS '标题';
COMMENT ON COLUMN HOUSE_RENTING.DESCRIPTION IS '描述';
COMMENT ON COLUMN HOUSE_RENTING.DESIRED_POSITION IS '期望坐标';
COMMENT ON COLUMN HOUSE_RENTING.DESIRED_NAME IS '期望地名';
COMMENT ON COLUMN HOUSE_RENTING.DESIRED_ADDRESS IS '期望地址';
COMMENT ON COLUMN HOUSE_RENTING.DESCRIPTION_PICTURES IS '图片';


CREATE TABLE IF NOT EXISTS HOUSE_CUSTOMER
(
    ID          VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    RELATION    VARCHAR(50) NOT NULL,
    HOUSE_ID    VARCHAR(36) NOT NULL
        CONSTRAINT HOUSE_CUSTOMER_HOUSE_ID_FK REFERENCES HOUSE,
    CUSTOMER_ID VARCHAR(36) NOT NULL
        CONSTRAINT HOUSE_CUSTOMER_CUSTOMER_ID_FK REFERENCES CUSTOMER (ID) ON DELETE CASCADE,
    MERCHANT_ID VARCHAR(36) NOT NULL
);

COMMENT ON TABLE HOUSE_CUSTOMER IS '营业员与房屋关联';
COMMENT ON COLUMN HOUSE_CUSTOMER.RELATION IS '房屋与用户之间的关系：房东、租户';
COMMENT ON COLUMN HOUSE_CUSTOMER.HOUSE_ID IS '房屋ID';
COMMENT ON COLUMN HOUSE_CUSTOMER.CUSTOMER_ID IS '用户ID';
COMMENT ON COLUMN HOUSE_CUSTOMER.MERCHANT_ID IS '主体ID';
CREATE UNIQUE INDEX IF NOT EXISTS HOUSE_CUSTOMER_HID_CID_UINDEX ON HOUSE_CUSTOMER (HOUSE_ID, CUSTOMER_ID);

CREATE TABLE IF NOT EXISTS PARKING
(
    ID          VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    MERCHANT_ID VARCHAR(36)  NOT NULL,
    HOUSE_ID    VARCHAR(36)
        CONSTRAINT PARKING_HOUSE_ID_FK REFERENCES HOUSE,
    REGION      VARCHAR(50)  NOT NULL,
    CODE        VARCHAR(50)  NOT NULL,
    CAR_CODE    VARCHAR(50)  NULL,
    STATE       VARCHAR(50)  not null,
    STATUS      VARCHAR(50)  not null,
    REMARKS     VARCHAR(255) NULL
);
COMMENT ON TABLE PARKING IS '车位';
COMMENT ON COLUMN PARKING.MERCHANT_ID IS '主体ID';
COMMENT ON COLUMN PARKING.HOUSE_ID IS '房屋ID';
COMMENT ON COLUMN PARKING.REGION IS '区域';
COMMENT ON COLUMN PARKING.CODE IS '编号';
COMMENT ON COLUMN PARKING.CAR_CODE IS '车牌号';
COMMENT ON COLUMN PARKING.STATE IS '占用状态：有车、无车';
COMMENT ON COLUMN PARKING.STATUS IS '使用状态：出售、出租、闲置';
COMMENT ON COLUMN PARKING.REMARKS IS '备注';



CREATE TABLE IF NOT EXISTS DOOR
(
    ID              VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    MERCHANT_ID     VARCHAR(36),
    IMEI            VARCHAR(50)  NOT NULL
        CONSTRAINT UK_DOOR_IMEI UNIQUE,
    TITLE           VARCHAR(50)  NOT NULL,
    IP              VARCHAR(39)  NOT NULL,
    REMARKS         VARCHAR(255),
    STATUS          VARCHAR(50)  NOT NULL,
    UPDATE_DATETIME TIMESTAMP(0) not null   default now()
);
COMMENT ON TABLE DOOR IS '门口';
COMMENT ON COLUMN DOOR.MERCHANT_ID IS '主体ID';
COMMENT ON COLUMN DOOR.IMEI IS '设备编码';
COMMENT ON COLUMN DOOR.TITLE IS '标题';
COMMENT ON COLUMN DOOR.IP IS 'IP';
COMMENT ON COLUMN DOOR.REMARKS IS '备注';
COMMENT ON COLUMN DOOR.STATUS IS '状态';
COMMENT ON COLUMN DOOR.UPDATE_DATETIME IS '更新时间';

CREATE TABLE IF NOT EXISTS DOOR_CUSTOMER
(
    ID          VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    DOOR_ID     VARCHAR(36) NOT NULL
        CONSTRAINT DOOR_CUSTOMER_DOOR_ID_FK
            REFERENCES DOOR
            ON DELETE CASCADE,
    CUSTOMER_ID VARCHAR(36) NOT NULL
        CONSTRAINT DOOR_CUSTOMER_CUSTOMER_ID_FK
            REFERENCES CUSTOMER (ID)
            ON DELETE CASCADE,
    MERCHANT_ID VARCHAR(36) NOT NULL
);
COMMENT ON TABLE DOOR_CUSTOMER IS '门口与用户关联';
COMMENT ON COLUMN DOOR_CUSTOMER.DOOR_ID IS '门口ID';
COMMENT ON COLUMN DOOR_CUSTOMER.CUSTOMER_ID IS '用户ID';
COMMENT ON COLUMN DOOR_CUSTOMER.MERCHANT_ID IS '主体ID';

CREATE TABLE IF NOT EXISTS ORDER_HOUSE
(
    ID              VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    MERCHANT_ID     VARCHAR(36)    NOT NULL,
    HOUSE_ID        VARCHAR(36)    NOT NULL
        CONSTRAINT ORDER_HOUSE_HOUSE_ID_FK REFERENCES HOUSE,
    FLOOR_NUMBER    VARCHAR(50),
    UNIT            VARCHAR(50),
    ROOM_NUMBER     VARCHAR(50),

    AREA            NUMERIC(20, 2) NOT NULL,
    RUBBISH_PRICE   NUMERIC(20, 2) NOT NULL,
    PROPERTY_TOTAL  NUMERIC(20, 2) NOT NULL,

    PARKING_COUNT   SMALLINT       NOT NULL,
    PARKING_TOTAL   NUMERIC(20, 2) NOT NULL,

    TOTAL           NUMERIC(20, 2) NOT NULL,
    TYPE            VARCHAR(50)    NOT NULL,
    REMARKS         VARCHAR(255),
    CODE            VARCHAR(32),
    START_DATE      DATE           NOT NULL,
    END_DATE        DATE           NOT NULL,
    MONTH_COUNT     BIGINT         NOT NULL,
    STATUS          VARCHAR(50)    NOT NULL,
    PAY_DATETIME    TIMESTAMP(0),
    NOTICE_DATETIME TIMESTAMP(0),
    UPDATE_DATETIME TIMESTAMP(0)   not null default now()
);
COMMENT ON TABLE ORDER_HOUSE IS '物业费';
COMMENT ON COLUMN ORDER_HOUSE.MERCHANT_ID IS '主体ID';
COMMENT ON COLUMN ORDER_HOUSE.FLOOR_NUMBER IS '楼号';
COMMENT ON COLUMN ORDER_HOUSE.UNIT IS '单元';
COMMENT ON COLUMN ORDER_HOUSE.ROOM_NUMBER IS '房号';

COMMENT ON COLUMN ORDER_HOUSE.AREA IS '面积';
COMMENT ON COLUMN ORDER_HOUSE.RUBBISH_PRICE IS '垃圾费:元/月';

COMMENT ON COLUMN ORDER_HOUSE.PARKING_COUNT IS '车位数量';
COMMENT ON COLUMN ORDER_HOUSE.PARKING_TOTAL IS '车位管理费';

COMMENT ON COLUMN ORDER_HOUSE.TOTAL IS '合计:元';
COMMENT ON COLUMN ORDER_HOUSE.TYPE IS '类型:住宅、商铺、公寓、办公室';
COMMENT ON COLUMN ORDER_HOUSE.REMARKS IS '备注';
COMMENT ON COLUMN ORDER_HOUSE.CODE IS '编号';
COMMENT ON COLUMN ORDER_HOUSE.START_DATE IS '开始日期';
COMMENT ON COLUMN ORDER_HOUSE.END_DATE IS '结束日期';
COMMENT ON COLUMN ORDER_HOUSE.MONTH_COUNT IS '月数';
COMMENT ON COLUMN ORDER_HOUSE.STATUS IS '状态:待支付、已支付';
COMMENT ON COLUMN ORDER_HOUSE.PAY_DATETIME IS '支付时间';
COMMENT ON COLUMN ORDER_HOUSE.NOTICE_DATETIME IS '通知时间';
COMMENT ON COLUMN ORDER_HOUSE.UPDATE_DATETIME IS '更新日期';
CREATE UNIQUE INDEX IF NOT EXISTS ORDER_HOUSE_HID_SD_ED_UINDEX ON ORDER_HOUSE (HOUSE_ID, START_DATE, END_DATE);


CREATE TABLE IF NOT EXISTS ORDER_WATER
(
    ID              VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    MERCHANT_ID     VARCHAR(36)    NOT NULL,
    HOUSE_ID        VARCHAR(36)    NOT NULL
        CONSTRAINT ORDER_WATER_HOUSE_ID_FK REFERENCES HOUSE,
    FLOOR_NUMBER    VARCHAR(50),
    UNIT            VARCHAR(50),
    ROOM_NUMBER     VARCHAR(50),

    WATER_INITIAL   NUMERIC(20, 5) NOT NULL,
    WATER_CURRENT   NUMERIC(20, 5) NOT NULL,
    WATER_COUNT     NUMERIC(20, 5) NOT NULL,
    WATER_LOSS      NUMERIC(20, 5) NOT NULL,
    WATER_RATIO     NUMERIC(20, 5) NOT NULL,
    WATER_TOTAL     NUMERIC(20, 2) NOT NULL,

    TYPE            VARCHAR(50)    NOT NULL,
    REMARKS         VARCHAR(255),
    CODE            VARCHAR(32),
    START_DATE      DATE           NOT NULL,
    END_DATE        DATE           NOT NULL,
    STATUS          VARCHAR(50)    NOT NULL,
    PAY_DATETIME    TIMESTAMP(0),
    NOTICE_DATETIME TIMESTAMP(0),
    UPDATE_DATETIME TIMESTAMP(0)   not null default now()
);
COMMENT ON TABLE ORDER_WATER IS '水费';
COMMENT ON COLUMN ORDER_WATER.MERCHANT_ID IS '主体ID';
COMMENT ON COLUMN ORDER_WATER.FLOOR_NUMBER IS '楼号';
COMMENT ON COLUMN ORDER_WATER.UNIT IS '单元';
COMMENT ON COLUMN ORDER_WATER.ROOM_NUMBER IS '房号';

COMMENT ON COLUMN ORDER_WATER.WATER_INITIAL IS '水表初始值';
COMMENT ON COLUMN ORDER_WATER.WATER_CURRENT IS '水表当前值';
COMMENT ON COLUMN ORDER_WATER.WATER_COUNT IS '用水量';
COMMENT ON COLUMN ORDER_WATER.WATER_LOSS IS '损耗';
COMMENT ON COLUMN ORDER_WATER.WATER_TOTAL IS '水费';

COMMENT ON COLUMN ORDER_WATER.TYPE IS '类型:住宅、商铺、公寓、办公室';
COMMENT ON COLUMN ORDER_WATER.REMARKS IS '备注';
COMMENT ON COLUMN ORDER_WATER.CODE IS '编号';
COMMENT ON COLUMN ORDER_WATER.START_DATE IS '开始日期';
COMMENT ON COLUMN ORDER_WATER.END_DATE IS '结束日期';
COMMENT ON COLUMN ORDER_WATER.STATUS IS '状态:待支付、已支付';
COMMENT ON COLUMN ORDER_WATER.PAY_DATETIME IS '支付时间';
COMMENT ON COLUMN ORDER_WATER.NOTICE_DATETIME IS '通知时间';
COMMENT ON COLUMN ORDER_WATER.UPDATE_DATETIME IS '更新日期';
CREATE UNIQUE INDEX IF NOT EXISTS ORDER_WATER_HID_SD_ED_UINDEX ON ORDER_WATER (HOUSE_ID, START_DATE, END_DATE);


CREATE TABLE IF NOT EXISTS ORDER_HEAT
(
    ID              VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    MERCHANT_ID     VARCHAR(36)    NOT NULL,
    HOUSE_ID        VARCHAR(36)    NOT NULL
        CONSTRAINT ORDER_HEAT_HOUSE_ID_FK REFERENCES HOUSE,
    FLOOR_NUMBER    VARCHAR(50),
    UNIT            VARCHAR(50),
    ROOM_NUMBER     VARCHAR(50),

    AREA            NUMERIC(20, 2) NOT NULL,
    HEAT_TOTAL      NUMERIC(20, 2) NOT NULL,

    TYPE            VARCHAR(50)    NOT NULL,
    REMARKS         VARCHAR(255),
    CODE            VARCHAR(32),
    START_DATE      DATE           NOT NULL,
    END_DATE        DATE           NOT NULL,
    STATUS          VARCHAR(50)    NOT NULL,
    PAY_DATETIME    TIMESTAMP(0),
    NOTICE_DATETIME TIMESTAMP(0),
    UPDATE_DATETIME TIMESTAMP(0)   not null default now()
);
COMMENT ON TABLE ORDER_HEAT IS '采暖费';
COMMENT ON COLUMN ORDER_HEAT.MERCHANT_ID IS '主体ID';
COMMENT ON COLUMN ORDER_HEAT.FLOOR_NUMBER IS '楼号';
COMMENT ON COLUMN ORDER_HEAT.UNIT IS '单元';
COMMENT ON COLUMN ORDER_HEAT.ROOM_NUMBER IS '房号';

COMMENT ON COLUMN ORDER_HEAT.AREA IS '面积';
COMMENT ON COLUMN ORDER_HEAT.HEAT_TOTAL IS '采暖费';

COMMENT ON COLUMN ORDER_HEAT.TYPE IS '类型:住宅、商铺、公寓、办公室';
COMMENT ON COLUMN ORDER_HEAT.REMARKS IS '备注';
COMMENT ON COLUMN ORDER_HEAT.CODE IS '编号';
COMMENT ON COLUMN ORDER_HEAT.START_DATE IS '开始日期';
COMMENT ON COLUMN ORDER_HEAT.END_DATE IS '结束日期';
COMMENT ON COLUMN ORDER_HEAT.STATUS IS '状态:待支付、已支付';
COMMENT ON COLUMN ORDER_HEAT.PAY_DATETIME IS '支付时间';
COMMENT ON COLUMN ORDER_HEAT.NOTICE_DATETIME IS '通知时间';
COMMENT ON COLUMN ORDER_HEAT.UPDATE_DATETIME IS '更新日期';
CREATE UNIQUE INDEX IF NOT EXISTS ORDER_HEAT_HID_SD_ED_UINDEX ON ORDER_HEAT (HOUSE_ID, START_DATE, END_DATE);



CREATE TABLE IF NOT EXISTS HOUSE_IDENTITY
(
    ID               VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    CUSTOMER_ID      VARCHAR(36)  NOT NULL,
    NAME             VARCHAR(50)  NOT NULL,
    ID_CARD          CHAR(18)     NOT NULL,
    ID_CARD_FACE     TEXT,
    ID_CARD_NATIONAL TEXT,
    FACE             TEXT,
    PHONE            CHAR(11)     NOT NULL,

    HOUSE_ID         VARCHAR(36)  NOT NULL
        CONSTRAINT IDENTITY_HOUSE_ID_FK REFERENCES HOUSE,
    FLOOR_NUMBER     VARCHAR(50),
    UNIT             VARCHAR(50),
    ROOM_NUMBER      VARCHAR(50),
    TYPE             VARCHAR(50)  not null,

    RELATION         VARCHAR(50)  NOT NULL,
    STATUS           VARCHAR(50)  NOT NULL,

    INSERT_DATETIME  TIMESTAMP(0) not null   default now(),
    UPDATE_DATETIME  TIMESTAMP(0) not null   default now(),

    MERCHANT_ID      VARCHAR(36)  NOT NULL
);
COMMENT ON TABLE HOUSE_IDENTITY IS '实名认证';
COMMENT ON COLUMN HOUSE_IDENTITY.CUSTOMER_ID IS '用户ID';
COMMENT ON COLUMN HOUSE_IDENTITY.NAME IS '姓名';
COMMENT ON COLUMN HOUSE_IDENTITY.ID_CARD IS '身份证';
COMMENT ON COLUMN HOUSE_IDENTITY.ID_CARD_FACE IS '身份证-人脸';
COMMENT ON COLUMN HOUSE_IDENTITY.ID_CARD_NATIONAL IS '身份证-国徽';
COMMENT ON COLUMN HOUSE_IDENTITY.FACE IS '人脸';
COMMENT ON COLUMN HOUSE_IDENTITY.PHONE IS '手机号';
COMMENT ON COLUMN HOUSE_IDENTITY.HOUSE_ID IS '房屋ID';
COMMENT ON COLUMN HOUSE_IDENTITY.FLOOR_NUMBER IS '楼号';
COMMENT ON COLUMN HOUSE_IDENTITY.UNIT IS '单元';
COMMENT ON COLUMN HOUSE_IDENTITY.ROOM_NUMBER IS '房号';
COMMENT ON COLUMN HOUSE_IDENTITY.TYPE IS '类型:住宅、商铺、公寓、办公室';
COMMENT ON COLUMN HOUSE_IDENTITY.RELATION IS '房屋与用户之间的关系：房东、租户';
COMMENT ON COLUMN HOUSE_IDENTITY.STATUS IS '状态';
COMMENT ON COLUMN HOUSE_IDENTITY.INSERT_DATETIME IS '创建时间';
COMMENT ON COLUMN HOUSE_IDENTITY.UPDATE_DATETIME IS '更新时间';
COMMENT ON COLUMN HOUSE_IDENTITY.MERCHANT_ID IS '主体ID';

CREATE TABLE IF NOT EXISTS VOTE
(
    ID              VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    MERCHANT_ID     VARCHAR(36)  NOT NULL,
    TITLE           VARCHAR(255),
    DESCRIPTION     VARCHAR(255),
    PICTURES        TEXT[],
    VIDEOS          TEXT[],
    OPTIONS         TEXT[],
    RESULT          VARCHAR(255) not null   default '',
    NOTICE_DATETIME timestamp(0),
    INSERT_DATETIME TIMESTAMP(0) not null   default now(),
    UPDATE_DATETIME TIMESTAMP(0) not null   default now()
);
COMMENT ON TABLE VOTE IS '投票';
COMMENT ON COLUMN VOTE.MERCHANT_ID IS '主体ID';
COMMENT ON COLUMN VOTE.TITLE IS '标题';
COMMENT ON COLUMN VOTE.DESCRIPTION IS '描述';
COMMENT ON COLUMN VOTE.PICTURES IS '图集';
COMMENT ON COLUMN VOTE.VIDEOS IS '视频ID';
COMMENT ON COLUMN VOTE.OPTIONS IS '选项';
COMMENT ON COLUMN VOTE.RESULT IS '结果';
COMMENT ON COLUMN VOTE.NOTICE_DATETIME IS '通知时间';
COMMENT ON COLUMN VOTE.INSERT_DATETIME IS '创建时间';
COMMENT ON COLUMN VOTE.UPDATE_DATETIME IS '更新时间';


CREATE TABLE IF NOT EXISTS VOTER
(
    ID              VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    VOTE_ID         VARCHAR(36)  NOT NULL
        CONSTRAINT VOTER_VOTE_ID_FK
            REFERENCES VOTE
            ON DELETE CASCADE,
    HOUSE_ID        VARCHAR(36)  NOT NULL
        CONSTRAINT VOTER_HOUSE_ID_FK REFERENCES HOUSE,
    MERCHANT_ID     VARCHAR(36)  NOT NULL,
    CUSTOMER_ID     VARCHAR(36)  NOT NULL
        CONSTRAINT VOTER_CUSTOMER_ID_FK
            REFERENCES CUSTOMER
            ON DELETE CASCADE,
    OPTION          VARCHAR(255),
    REMARK          VARCHAR(255),
    INSERT_DATETIME TIMESTAMP(0) not null   default now()
);
COMMENT ON TABLE VOTER IS '投票者';
COMMENT ON COLUMN VOTER.VOTE_ID IS '投票ID';
COMMENT ON COLUMN VOTER.HOUSE_ID IS '房屋ID';
COMMENT ON COLUMN VOTER.MERCHANT_ID IS '主体ID';
COMMENT ON COLUMN VOTER.CUSTOMER_ID IS '用户ID';
COMMENT ON COLUMN VOTER.OPTION IS '选项';
COMMENT ON COLUMN VOTER.REMARK IS '备注';
COMMENT ON COLUMN VOTER.INSERT_DATETIME IS '创建时间';
CREATE UNIQUE INDEX IF NOT EXISTS VOTER_VID_HID_UINDEX
    ON VOTER (VOTE_ID, HOUSE_ID);

CREATE TABLE IF NOT EXISTS PARK_REGION
(
    ID              VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    NAME            VARCHAR(255),
    POINT_PATH      PATH                                  NOT NULL,
    FORMULA         TEXT,
    PRICE           NUMERIC(20, 2)                        NOT NULL,
    FREE_TIME       int                                   not null,
    MERCHANT_ID     VARCHAR(36)                           NOT NULL,
    INSERT_DATETIME TIMESTAMP(0)            DEFAULT now() NOT NULL,
    UPDATE_DATETIME TIMESTAMP(0)            DEFAULT now() NOT NULL
);
COMMENT ON TABLE PARK_REGION IS '停车区域';
COMMENT ON COLUMN PARK_REGION.ID IS '主键';
COMMENT ON COLUMN PARK_REGION.NAME IS '名称';
COMMENT ON COLUMN PARK_REGION.POINT_PATH IS '路线图';
COMMENT ON COLUMN PARK_REGION.FORMULA IS '计算公式';
COMMENT ON COLUMN PARK_REGION.PRICE IS '单价，单位：元';
COMMENT ON COLUMN PARK_REGION.FREE_TIME IS '减免时长，单位：分钟';
COMMENT ON COLUMN PARK_REGION.MERCHANT_ID IS '主体ID';
COMMENT ON COLUMN PARK_REGION.INSERT_DATETIME IS '创建时间';
COMMENT ON COLUMN PARK_REGION.UPDATE_DATETIME IS '更新日期';

CREATE TABLE IF NOT EXISTS ACCESS
(
    ID              VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    MERCHANT_ID     VARCHAR(36),
    PARK_REGION_ID  VARCHAR(36)
        CONSTRAINT ACCESS_PARK_REGION_ID_FK REFERENCES PARK_REGION,
    IMEI            VARCHAR(50)  NOT NULL
        CONSTRAINT UK_ACCESS_IMEI UNIQUE,
    TITLE           VARCHAR(50)  NOT NULL,
    IP              VARCHAR(39)  NOT NULL,
    TYPE            VARCHAR(128)  NOT NULL,
    REMARKS         VARCHAR(255),
    STATUS          VARCHAR(50)  NOT NULL,
    UPDATE_DATETIME TIMESTAMP(0) not null   default now()
);
COMMENT ON TABLE ACCESS IS '道闸';
COMMENT ON COLUMN ACCESS.MERCHANT_ID IS '主体ID';
COMMENT ON COLUMN ACCESS.PARK_REGION_ID IS '区域ID';
COMMENT ON COLUMN ACCESS.IMEI IS '设备编码';
COMMENT ON COLUMN ACCESS.TITLE IS '标题';
COMMENT ON COLUMN ACCESS.IP IS 'IP';
COMMENT ON COLUMN ACCESS.TYPE IS '类型';
COMMENT ON COLUMN ACCESS.REMARKS IS '备注';
COMMENT ON COLUMN ACCESS.STATUS IS '状态';
COMMENT ON COLUMN ACCESS.UPDATE_DATETIME IS '更新时间';

CREATE TABLE IF NOT EXISTS ORDER_PARK
(
    ID                 VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    CAR_NUMBER         VARCHAR(255),
    CAR_NUMBER_PICTURE VARCHAR(255),
    IN_DATETIME        TIMESTAMP(0)   NOT NULL,
    IN_PICTURE         VARCHAR(255),
    OUT_DATETIME       TIMESTAMP(0),
    OUT_PICTURE        VARCHAR(255),
    MONEY_MEET         NUMERIC(20, 2) NOT NULL,
    MONEY_REDUCTION    NUMERIC(20, 2) NOT NULL,
    MONEY_PAYMENT      NUMERIC(20, 2) NOT NULL,
    PARK_STATUS        VARCHAR(50)    NOT NULL,
    IN_DEVICE_NUMBER   VARCHAR(255),
    OUT_DEVICE_NUMBER  VARCHAR(255),
    MERCHANT_ID        VARCHAR(36)    NOT NULL,
    PARK_REGION_ID     VARCHAR(36)    NOT NULL
        CONSTRAINT ORDER_PARK_PARK_REGION_ID_FK REFERENCES PARK_REGION,
    REMARKS            VARCHAR(255),
    NOTICE_DATETIME    TIMESTAMP(0),
    UPDATE_DATETIME    TIMESTAMP(0)
);
COMMENT ON TABLE ORDER_PARK IS '停车费';
COMMENT ON COLUMN ORDER_PARK.ID IS '主键';
COMMENT ON COLUMN ORDER_PARK.CAR_NUMBER IS '车牌号';
COMMENT ON COLUMN ORDER_PARK.CAR_NUMBER_PICTURE IS '车牌图片';
COMMENT ON COLUMN ORDER_PARK.IN_DATETIME IS '驶入时间';
COMMENT ON COLUMN ORDER_PARK.IN_PICTURE IS '驶入图片';
COMMENT ON COLUMN ORDER_PARK.OUT_DATETIME IS '驶离时间';
COMMENT ON COLUMN ORDER_PARK.OUT_PICTURE IS '驶离图片';
COMMENT ON COLUMN ORDER_PARK.MONEY_MEET IS '应缴金额';
COMMENT ON COLUMN ORDER_PARK.MONEY_REDUCTION IS '减免金额';
COMMENT ON COLUMN ORDER_PARK.MONEY_PAYMENT IS '实缴金额';
COMMENT ON COLUMN ORDER_PARK.PARK_STATUS IS '停车状态';
COMMENT ON COLUMN ORDER_PARK.IN_DEVICE_NUMBER IS '驶入设备编号';
COMMENT ON COLUMN ORDER_PARK.OUT_DEVICE_NUMBER IS '驶离设备编号';
COMMENT ON COLUMN ORDER_PARK.MERCHANT_ID IS '主体ID';
COMMENT ON COLUMN ORDER_PARK.PARK_REGION_ID IS '区域ID';
COMMENT ON COLUMN ORDER_PARK.REMARKS IS '备注';
COMMENT ON COLUMN ORDER_PARK.NOTICE_DATETIME IS '通知时间';
COMMENT ON COLUMN ORDER_PARK.UPDATE_DATETIME IS '更新日期';

CREATE TABLE IF NOT EXISTS PAY_ORDER
(
    ID                 VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    MERCHANT_ID        VARCHAR(36)    NOT NULL,
    SUB_MCH_ID         VARCHAR(36)    NOT NULL,
    MERCHANT_SHORTNAME VARCHAR(255),
    CUSTOMER_ID        VARCHAR(36)    NOT NULL,
    ORDER_IDS          TEXT[],
    DETAILS            JSON,
    REFUNDS            JSON,
    DESCRIPTION        VARCHAR(255)   NOT NULL,
    TOTAL              NUMERIC(20, 2) NOT NULL,
    REMARKS            VARCHAR(255),
    STATUS             VARCHAR(50)    NOT NULL,
    PAY_DATETIME       TIMESTAMP(0),
    PREPAY_ID          VARCHAR(255),
    OUT_TRADE_NO       VARCHAR(255)   not null unique,
    INSERT_DATETIME    TIMESTAMP(0)   not null default now(),
    UPDATE_DATETIME    TIMESTAMP(0)   not null default now()
);
COMMENT ON TABLE PAY_ORDER IS '支付记录';
COMMENT ON COLUMN PAY_ORDER.MERCHANT_ID IS '主体ID';
COMMENT ON COLUMN PAY_ORDER.SUB_MCH_ID IS '商户号';
COMMENT ON COLUMN PAY_ORDER.MERCHANT_SHORTNAME IS '主体简称';
COMMENT ON COLUMN PAY_ORDER.CUSTOMER_ID IS '用户ID';
COMMENT ON COLUMN PAY_ORDER.ORDER_IDS IS '详情ID';
COMMENT ON COLUMN PAY_ORDER.DETAILS IS '详情';
COMMENT ON COLUMN PAY_ORDER.REFUNDS IS '退款记录';
COMMENT ON COLUMN PAY_ORDER.DESCRIPTION IS '描述';
COMMENT ON COLUMN PAY_ORDER.TOTAL IS '支付金额';
COMMENT ON COLUMN PAY_ORDER.REMARKS IS '备注';
COMMENT ON COLUMN PAY_ORDER.STATUS IS '支付状态';
COMMENT ON COLUMN PAY_ORDER.PAY_DATETIME IS '支付时间';
COMMENT ON COLUMN PAY_ORDER.PREPAY_ID IS '预支付编号';
COMMENT ON COLUMN PAY_ORDER.OUT_TRADE_NO IS '订单编号';
COMMENT ON COLUMN PAY_ORDER.INSERT_DATETIME IS '创建时间';
COMMENT ON COLUMN PAY_ORDER.UPDATE_DATETIME IS '更新日期';

CREATE TABLE IF NOT EXISTS SEARCH_CAR_NUMBER
(
    ID          VARCHAR(36) PRIMARY KEY,
    CAR_NUMBERS TEXT[]
);
COMMENT ON TABLE SEARCH_CAR_NUMBER IS '车牌搜索记录';
COMMENT ON COLUMN SEARCH_CAR_NUMBER.ID IS '用户ID';
COMMENT ON COLUMN SEARCH_CAR_NUMBER.CAR_NUMBERS IS '车牌号';

CREATE TABLE IF NOT EXISTS PARKING_IDENTITY
(
    ID                 VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    CUSTOMER_ID        VARCHAR(36)  NOT NULL,
    NAME               VARCHAR(50)  NOT NULL,
    ID_CARD            CHAR(18)     NOT NULL,
    PHONE              CHAR(11)     NOT NULL,
    car_number         CHAR(11)     NOT NULL,
    start_datetime     TIMESTAMP(0) not null,
    end_datetime       TIMESTAMP(0) not null,
    reason             VARCHAR(255),
    reply              VARCHAR(255),
    STATUS             VARCHAR(10)  NOT NULL,
    MERCHANT_ID        VARCHAR(36)  NOT NULL,
    MERCHANT_SHORTNAME VARCHAR(36)  NOT NULL
);
COMMENT ON TABLE PARKING_IDENTITY IS '临时停车申请';
COMMENT ON COLUMN PARKING_IDENTITY.CUSTOMER_ID IS '用户ID';
COMMENT ON COLUMN PARKING_IDENTITY.NAME IS '姓名';
COMMENT ON COLUMN PARKING_IDENTITY.ID_CARD IS '身份证';
COMMENT ON COLUMN PARKING_IDENTITY.PHONE IS '手机号';
COMMENT ON COLUMN PARKING_IDENTITY.car_number IS '车牌号';
COMMENT ON COLUMN PARKING_IDENTITY.start_datetime IS '开始时间';
COMMENT ON COLUMN PARKING_IDENTITY.end_datetime IS '截至时间';
COMMENT ON COLUMN PARKING_IDENTITY.reason IS '原因';
COMMENT ON COLUMN PARKING_IDENTITY.reply IS '回复';
COMMENT ON COLUMN PARKING_IDENTITY.STATUS IS '状态(待处理、已同意、已驳回、已结束)';
COMMENT ON COLUMN PARKING_IDENTITY.MERCHANT_ID IS '主体ID';
COMMENT ON COLUMN PARKING_IDENTITY.MERCHANT_SHORTNAME IS '主体简称';

CREATE TABLE IF NOT EXISTS ADVICE
(
    ID                   VARCHAR(36) PRIMARY KEY DEFAULT GEN_RANDOM_UUID()::TEXT,
    MERCHANT_ID          VARCHAR(36)  NOT NULL,
    CUSTOMER_ID          VARCHAR(36)  NOT NULL,
    NAME                 VARCHAR(50)  NOT NULL,
    PHONE                CHAR(11)     NOT NULL,
    TITLE                VARCHAR(255),
    DESCRIPTION          VARCHAR(255),
    description_pictures TEXT[],
    REMARK               VARCHAR(255),
    STATUS               VARCHAR(50)  NOT NULL,
    INSERT_DATETIME      TIMESTAMP(0) NOT NULL,
    UPDATE_DATETIME      TIMESTAMP(0) NOT NULL
);
COMMENT ON TABLE ADVICE IS '意见箱';
COMMENT ON COLUMN ADVICE.ID IS '主键';
COMMENT ON COLUMN ADVICE.MERCHANT_ID IS '主体ID';
COMMENT ON COLUMN ADVICE.CUSTOMER_ID IS '用户ID';
COMMENT ON COLUMN ADVICE.NAME IS '姓名';
COMMENT ON COLUMN ADVICE.PHONE IS '手机号';
COMMENT ON COLUMN ADVICE.TITLE IS '标题';
COMMENT ON COLUMN ADVICE.DESCRIPTION IS '描述';
COMMENT ON COLUMN ADVICE.description_pictures IS '描述图片';
COMMENT ON COLUMN ADVICE.REMARK IS '备注';
COMMENT ON COLUMN ADVICE.STATUS IS '状态（WAIT待处理、CLOSED已关闭、ERROR未解决、SUCCESS已解决）';
COMMENT ON COLUMN ADVICE.INSERT_DATETIME IS '创建时间';
COMMENT ON COLUMN ADVICE.UPDATE_DATETIME IS '更新时间';



CREATE TABLE IF NOT EXISTS notice
(
    ID              VARCHAR(36) PRIMARY KEY DEFAULT GEN_RANDOM_UUID()::TEXT,
    MERCHANT_ID     VARCHAR(36)  NOT NULL,
    TITLE           VARCHAR(255),
    DESCRIPTION     TEXT,
    PICTURES        TEXT[],
    VIDEOS          TEXT[],
    NOTICE_DATETIME timestamp(0),
    INSERT_DATETIME TIMESTAMP(0) NOT NULL   DEFAULT NOW(),
    UPDATE_DATETIME TIMESTAMP(0) NOT NULL   DEFAULT NOW()
);
COMMENT ON TABLE notice IS '公告通知';
COMMENT ON COLUMN notice.MERCHANT_ID IS '主体ID';
COMMENT ON COLUMN notice.TITLE IS '标题';
COMMENT ON COLUMN notice.DESCRIPTION IS '描述';
COMMENT ON COLUMN notice.PICTURES IS '图集';
COMMENT ON COLUMN notice.VIDEOS IS '视频ID';
COMMENT ON COLUMN notice.NOTICE_DATETIME IS '通知时间';
COMMENT ON COLUMN notice.INSERT_DATETIME IS '创建时间';
COMMENT ON COLUMN notice.UPDATE_DATETIME IS '更新时间';

CREATE TABLE IF NOT EXISTS REPAIR
(
    ID                   VARCHAR(36) PRIMARY KEY DEFAULT GEN_RANDOM_UUID()::TEXT,
    CUSTOMER_ID          VARCHAR(36)  NOT NULL,
    NAME                 VARCHAR(50)  NOT NULL,
    PHONE                VARCHAR(11)  NOT NULL,
    ADDRESS              VARCHAR(255) NOT NULL,
    title                VARCHAR(255),
    description          VARCHAR(255),
    description_PICTURES TEXT[],
    REPLY                VARCHAR(255),
    REPLY_PICTURES       TEXT[],
    STATUS               VARCHAR(50)  NOT NULL,
    LABEL                VARCHAR(128),
    REPAIRER_CUSTOMER_ID VARCHAR(36),
    REPAIRER_NAME        VARCHAR(50),
    REPAIRER_PHONE       VARCHAR(11),
    MERCHANT_ID          VARCHAR(36)  NOT NULL,
    MERCHANT_SHORTNAME   VARCHAR(36)  NOT NULL,
    INSERT_DATETIME      TIMESTAMP(0) NOT NULL   DEFAULT NOW(),
    UPDATE_DATETIME      TIMESTAMP(0) NOT NULL   DEFAULT NOW()
);
COMMENT ON TABLE REPAIR IS '报事报修';
COMMENT ON COLUMN REPAIR.CUSTOMER_ID IS '用户ID';
COMMENT ON COLUMN REPAIR.NAME IS '姓名';
COMMENT ON COLUMN REPAIR.PHONE IS '手机号';
COMMENT ON COLUMN REPAIR.ADDRESS IS '详细地址';
COMMENT ON COLUMN REPAIR.title IS '申报标题';
COMMENT ON COLUMN REPAIR.description IS '申报描述';
COMMENT ON COLUMN REPAIR.description_PICTURES IS '申报图集';
COMMENT ON COLUMN REPAIR.REPLY IS '完成回复';
COMMENT ON COLUMN REPAIR.REPLY_PICTURES IS '完成图集';
COMMENT ON COLUMN REPAIR.STATUS IS '状态(待分配、待处理、待评价、已结束)';
COMMENT ON COLUMN REPAIR.LABEL IS '标签';
COMMENT ON COLUMN REPAIR.REPAIRER_CUSTOMER_ID IS '用户ID';
COMMENT ON COLUMN REPAIR.REPAIRER_NAME IS '姓名';
COMMENT ON COLUMN REPAIR.REPAIRER_PHONE IS '手机号';
COMMENT ON COLUMN REPAIR.MERCHANT_ID IS '主体ID';
COMMENT ON COLUMN REPAIR.MERCHANT_SHORTNAME IS '主体简称';
COMMENT ON COLUMN REPAIR.INSERT_DATETIME IS '创建时间';
COMMENT ON COLUMN REPAIR.UPDATE_DATETIME IS '更新时间';



CREATE TABLE IF NOT EXISTS PRODUCT
(
    ID              VARCHAR(36) PRIMARY KEY DEFAULT GEN_RANDOM_UUID()::TEXT,
    PRICE_UNIT      VARCHAR(15)                           NOT NULL,
    NAME            VARCHAR(90)                           NOT NULL,
    MERCHANT_ID     VARCHAR(36)                           NOT NULL
        CONSTRAINT PRODUCT_MERCHANT_ID_FK
            REFERENCES MERCHANT
            ON DELETE CASCADE,
    STATUS          BOOLEAN                 DEFAULT FALSE NOT NULL,
    PRICE           NUMERIC                 DEFAULT 0.00  NOT NULL,
    STOCK           INTEGER                 DEFAULT 0     NOT NULL
        CONSTRAINT PRODUCT_STOCK_CHECK
            CHECK (STOCK >= 0),
    MAX             INTEGER                 DEFAULT 1     NOT NULL
        CONSTRAINT PRODUCT_MAX_CHECK
            CHECK (MAX >= 1),
    MIN             INTEGER                 DEFAULT 1     NOT NULL
        CONSTRAINT PRODUCT_MIN_CHECK
            CHECK (MIN >= 1),
    VID             VARCHAR(50),
    DESCRIPTION     VARCHAR(255),
    LABEL           VARCHAR(50),
    ORDER_LABEL     VARCHAR(50),
    PICTURES        TEXT[],
    INSERT_DATETIME TIMESTAMP(0)            DEFAULT NOW(),
    UPDATE_DATETIME TIMESTAMP(0)            DEFAULT NOW()
);
COMMENT ON TABLE PRODUCT IS '商品';
COMMENT ON COLUMN PRODUCT.PRICE_UNIT IS '单价单位';
COMMENT ON COLUMN PRODUCT.NAME IS '商品名称';
COMMENT ON COLUMN PRODUCT.MERCHANT_ID IS '商户ID';
COMMENT ON COLUMN PRODUCT.STATUS IS '是否上架';
COMMENT ON COLUMN PRODUCT.PRICE IS '单价';
COMMENT ON COLUMN PRODUCT.STOCK IS '库存';
COMMENT ON COLUMN PRODUCT.MAX IS '最大购买量';
COMMENT ON COLUMN PRODUCT.MIN IS '最小购买量';
COMMENT ON COLUMN PRODUCT.VID IS '视频ID';
COMMENT ON COLUMN PRODUCT.DESCRIPTION IS '商品描述';
COMMENT ON COLUMN PRODUCT.LABEL IS '商品标签';
COMMENT ON COLUMN PRODUCT.ORDER_LABEL IS '标签排序';
COMMENT ON COLUMN PRODUCT.PICTURES IS '商品图集';
COMMENT ON COLUMN PRODUCT.INSERT_DATETIME IS '创建时间';
COMMENT ON COLUMN PRODUCT.UPDATE_DATETIME IS '更新时间';
CREATE INDEX IF NOT EXISTS I_PRODUCT_MERCHANT_ID ON PRODUCT (MERCHANT_ID);



CREATE TABLE IF NOT EXISTS ADDRESS
(
    ID          VARCHAR(36) PRIMARY KEY DEFAULT GEN_RANDOM_UUID()::TEXT,
    CUSTOMER_ID VARCHAR(36) NOT NULL
        CONSTRAINT ADDRESS_CUSTOMER_ID_FK
            REFERENCES CUSTOMER
            ON DELETE CASCADE,
    LOCATION    POINT       NOT NULL,
    TYPE        VARCHAR(50) NOT NULL,
    NAME        VARCHAR(50) NOT NULL,
    DETAIL      VARCHAR(50) NOT NULL,
    POSTCODE    CHAR(6)     NOT NULL,
    CODE        TEXT[]      NOT NULL,
    VALUE       TEXT[]      NOT NULL,
    INSERT_DATE DATE,
    UPDATE_DATE DATE
);
COMMENT ON TABLE ADDRESS IS '收货地址';
COMMENT ON COLUMN ADDRESS.CUSTOMER_ID IS '消费者ID';
COMMENT ON COLUMN ADDRESS.LOCATION IS '坐标';
COMMENT ON COLUMN ADDRESS.TYPE IS '类型';
COMMENT ON COLUMN ADDRESS.NAME IS '地址名称';
COMMENT ON COLUMN ADDRESS.DETAIL IS '地址详情';
COMMENT ON COLUMN ADDRESS.POSTCODE IS '邮编';
COMMENT ON COLUMN ADDRESS.CODE IS '省市区编号';
COMMENT ON COLUMN ADDRESS.VALUE IS '省市区';
COMMENT ON COLUMN ADDRESS.INSERT_DATE IS '创建时间';
COMMENT ON COLUMN ADDRESS.UPDATE_DATE IS '更新时间';

CREATE TABLE IF NOT EXISTS CART
(
    ID          VARCHAR(36) PRIMARY KEY DEFAULT GEN_RANDOM_UUID()::TEXT,
    CUSTOMER_ID VARCHAR(255)                          NOT NULL,
    PRODUCT_ID  VARCHAR(36)                           NOT NULL
        CONSTRAINT CART_PRODUCT_ID_FK
            REFERENCES PRODUCT
            ON DELETE CASCADE,
    MERCHANT_ID VARCHAR(36)                           NOT NULL
        CONSTRAINT CART_MERCHANT_ID_FK
            REFERENCES MERCHANT
            ON DELETE CASCADE,
    TOTAL       NUMERIC                               NOT NULL,
    CHECKED     BOOLEAN                 DEFAULT FALSE NOT NULL,
    NUM         INTEGER                 DEFAULT 1     NOT NULL
        CONSTRAINT CART_NUM_CHECK
            CHECK (NUM >= 1),
    INSERT_DATE DATE,
    UPDATE_DATE DATE
);
COMMENT ON TABLE CART IS '购物车';
COMMENT ON COLUMN CART.CUSTOMER_ID IS '消费者ID';
COMMENT ON COLUMN CART.PRODUCT_ID IS '商品ID';
COMMENT ON COLUMN CART.MERCHANT_ID IS '商户ID';
COMMENT ON COLUMN CART.TOTAL IS '小计';
COMMENT ON COLUMN CART.CHECKED IS '是否选中';
COMMENT ON COLUMN CART.NUM IS '购买数量';
COMMENT ON COLUMN CART.INSERT_DATE IS '创建时间';
COMMENT ON COLUMN CART.UPDATE_DATE IS '更新时间';
CREATE UNIQUE INDEX IF NOT EXISTS UI_CART_MERCHANT_ID_CUSTOMER_ID_PRODUCT_ID
    ON CART (MERCHANT_ID, CUSTOMER_ID, PRODUCT_ID);

CREATE TABLE IF NOT EXISTS SCORE
(
    ID                   VARCHAR(36) PRIMARY KEY DEFAULT GEN_RANDOM_UUID()::TEXT,
    ADDRESS_DETAIL       VARCHAR(255)                                                  NOT NULL,
    ADDRESS_NAME         VARCHAR(100)                                                  NOT NULL,
    CUSTOMER_ID          VARCHAR(36)                                                   NOT NULL,
    USERNAME             VARCHAR(20),
    ADDRESS_VALUE        TEXT[]                                                        NOT NULL,
    ADDRESS_CODE         TEXT[]                                                        NOT NULL,
    WAY                  VARCHAR(50)                                                   NOT NULL,
    PHONE                CHAR(11),
    LOCATION             POINT                                                         NOT NULL,
    POSTCODE             CHAR(6)                                                       NOT NULL,
    PREPAY_ID            VARCHAR(70)                                                   NOT NULL,
    DETAILS              JSONB                                                         NOT NULL,
    MERCHANT_ID          VARCHAR(36)                                                   NOT NULL,
    STORE_ADDRESS_DETAIL VARCHAR(50)                                                   NOT NULL,
    STORE_ADDRESS_NAME   VARCHAR(50)                                                   NOT NULL,
    STORE_ADDRESS_VALUE  TEXT[]                                                        NOT NULL,
    STORE_ADDRESS_CODE   TEXT[]                                                        NOT NULL,
    STORE_PHONE          CHAR(11)                                                      NOT NULL,
    STORE_LOCATION       POINT                                                         NOT NULL,
    STORE_POSTCODE       CHAR(6)                                                       NOT NULL,
    STORE_SHORTNAME      VARCHAR(20)                                                   NOT NULL,
    STORE_USERNAME       VARCHAR(10)                                                   NOT NULL,
    SP_APPID             VARCHAR(255)                                                  NOT NULL,
    SP_MCHID             VARCHAR(255)                                                  NOT NULL,
    SUB_APPID            VARCHAR(50)                                                   NOT NULL,
    SUB_MCHID            VARCHAR(50)                                                   NOT NULL,
    TOTAL                NUMERIC                                                       NOT NULL,
    STATUS               VARCHAR(50)             DEFAULT 'WAIT_PAY'::CHARACTER VARYING NOT NULL,
    OUT_TRADE_NO         VARCHAR(19)                                                   NOT NULL
        CONSTRAINT UK_SCORE_OUT_TRADE_NO
            UNIQUE,
    PAY_DATETIME         TIMESTAMP(0),
    TRANSACTION_ID       VARCHAR(50),
    INSERT_DATETIME      TIMESTAMP(0)
);
COMMENT ON TABLE SCORE IS '订单';
COMMENT ON COLUMN SCORE.ADDRESS_DETAIL IS '收货地址详情';
COMMENT ON COLUMN SCORE.ADDRESS_NAME IS '收货地址名称';
COMMENT ON COLUMN SCORE.CUSTOMER_ID IS '消费者ID';
COMMENT ON COLUMN SCORE.USERNAME IS '消费者联系人';
COMMENT ON COLUMN SCORE.ADDRESS_VALUE IS '省市区';
COMMENT ON COLUMN SCORE.ADDRESS_CODE IS '省市区编号';
COMMENT ON COLUMN SCORE.WAY IS '配送方式';
COMMENT ON COLUMN SCORE.PHONE IS '联系方式';
COMMENT ON COLUMN SCORE.LOCATION IS '坐标';
COMMENT ON COLUMN SCORE.POSTCODE IS '邮编';
COMMENT ON COLUMN SCORE.PREPAY_ID IS '微信订单ID';
COMMENT ON COLUMN SCORE.DETAILS IS '商品列表';
COMMENT ON COLUMN SCORE.MERCHANT_ID IS '商户ID';
COMMENT ON COLUMN SCORE.STORE_ADDRESS_DETAIL IS '商户地址详情';
COMMENT ON COLUMN SCORE.STORE_ADDRESS_NAME IS '商户地址名称';
COMMENT ON COLUMN SCORE.STORE_ADDRESS_VALUE IS '商户省市区';
COMMENT ON COLUMN SCORE.STORE_ADDRESS_CODE IS '商户省市区编号';
COMMENT ON COLUMN SCORE.STORE_PHONE IS '商户联系方式';
COMMENT ON COLUMN SCORE.STORE_LOCATION IS '商户坐标';
COMMENT ON COLUMN SCORE.STORE_POSTCODE IS '商户邮编';
COMMENT ON COLUMN SCORE.STORE_SHORTNAME IS '商户简称';
COMMENT ON COLUMN SCORE.STORE_USERNAME IS '商户联系人';
COMMENT ON COLUMN SCORE.SP_APPID IS '服务商应用ID';
COMMENT ON COLUMN SCORE.SP_MCHID IS '服务商户号';
COMMENT ON COLUMN SCORE.SUB_APPID IS '子商户应用ID';
COMMENT ON COLUMN SCORE.SUB_MCHID IS '子商户号';
COMMENT ON COLUMN SCORE.TOTAL IS '订单总金额';
COMMENT ON COLUMN SCORE.STATUS IS '订单状态';
COMMENT ON COLUMN SCORE.OUT_TRADE_NO IS '订单编号';
COMMENT ON COLUMN SCORE.INSERT_DATETIME IS '创建时间';
CREATE INDEX IF NOT EXISTS I_SCORE_INSERT_DATE_MERCHANT_ID
    ON SCORE (INSERT_DATETIME, MERCHANT_ID);
CREATE INDEX IF NOT EXISTS I_SCORE_INSERT_DATE_CUSTOMER_ID
    ON SCORE (INSERT_DATETIME, CUSTOMER_ID);