ALTER TABLE DEPOSIT DROP CONSTRAINT FK_DST_TO_USERS;
ALTER TABLE USERS DROP CONSTRAINT FK_USER_TO_MMS;
ALTER TABLE MONEY DROP CONSTRAINT FK_MNY_TO_USER;
ALTER TABLE BILLINFO DROP CONSTRAINT FK_BIF_TO_USER_DELETE;
ALTER TABLE GOODS DROP CONSTRAINT FK_GOODS_TO_CATEGORY;
ALTER TABLE GOODS DROP CONSTRAINT FK_GOODS_TO_BRAND;
ALTER TABLE GOODS DROP CONSTRAINT FK_GOODS_TO_GDGROUP;
ALTER TABLE GOODS DROP CONSTRAINT FK_GOODS_TO_COMPANY;
ALTER TABLE GOODSPRICE DROP CONSTRAINT FK_GOODSPRICE_TO_GOODS;
ALTER TABLE GDSAPENDINFO DROP CONSTRAINT FK_APNDINFO_TO_GOODS;
ALTER TABLE SELLGOODS DROP CONSTRAINT FK_SELLGOODS_TO_GOODS;
ALTER TABLE TTLNUMSOLD DROP CONSTRAINT FK_TNS_TO_GOODS;
ALTER TABLE USERS DROP CONSTRAINT FK_USER_LEVEL_REF_NO;
ALTER TABLE MONEY DROP CONSTRAINT FK_MNY_TO_USER_DELETE;
ALTER TABLE GDGROUP DROP CONSTRAINT FK_GRP_TO_CMP;
ALTER TABLE CATEGORY DROP CONSTRAINT FK_CAT_TO_GRP;
ALTER TABLE BRAND DROP CONSTRAINT FK_BRD_TO_GRP;
ALTER TABLE PENDGOODS DROP CONSTRAINT FK_PENDGOODS_TO_GOODS;
ALTER TABLE USERPREFERENCE DROP CONSTRAINT FK_UPR_TO_USER_DELETE;
ALTER TABLE GDSCOMMENT DROP CONSTRAINT FK_GC_TO_GOODS_DELETE;
ALTER TABLE GDSBID DROP CONSTRAINT FK_GB_TO_GOODS_DELETE;

drop table GDSCOMMENT;
drop table GDSBID;
drop table USERPREFERENCE;
drop table MEMBERSHIP;
drop table USERS;
drop table MONEY;
drop table DEPOSIT;
drop table PROFIT;
drop table BILLINFO;
drop table TTLNUMSOLD;
drop table GOODSPRICE;
drop table PROHIBITPHONE;
drop table PENDGOODS;
drop table SELLGOODS;
drop table GOODS;
drop table CATEGORY;
drop table BRAND;
drop table GDGROUP;
drop table COMPANY;
drop table SHOPCART;
drop table GDSAPENDINFO;

create table GDSCOMMENT(
    GDS_REF_NO INTEGER NOT NULL,
    USER_REF_NO INTEGER NOT NULL,
    USER_NAME VARCHAR(32),
    GC_LEVEL INTEGER,
    GC_COMMENT LONGTEXT,
    GC_DATE VARCHAR(32),
    CONSTRAINT FK_GC_TO_GOODS_DELETE FOREIGN KEY (GDS_REF_NO) REFERENCES GOODS(GDS_REF_NO) ON DELETE CASCADE    
);

create table GDSBID(
    GDS_REF_NO INTEGER NOT NULL,
    GDS_NAME VARCHAR(128),
    USER_REF_NO INTEGER NOT NULL,
    USER_EMAIL VARCHAR(128),
    USER_NAME VARCHAR(32),
    GB_PRICE FLOAT,
    GB_COMMENT LONGTEXT,
    GB_DATE VARCHAR(32),
    GB_STATUS_FLAG INTEGER,
    CONSTRAINT FK_GB_TO_GOODS_DELETE FOREIGN KEY (GDS_REF_NO) REFERENCES GOODS(GDS_REF_NO) ON DELETE CASCADE    
);

create table USERPREFERENCE(
    USER_REF_NO INTEGER NOT NULL PRIMARY KEY,
    UPR_LANGUAGE VARCHAR(32),
    CONSTRAINT FK_UPR_TO_USER_DELETE FOREIGN KEY (USER_REF_NO) REFERENCES USERS(USER_REF_NO) ON DELETE CASCADE
);

create table MEMBERSHIP(
    MMS_REF_NO INTEGER NOT NULL PRIMARY KEY,
    MMS_NAME_EN VARCHAR(32),	
    MMS_NAME_CN VARCHAR(32),	
    MMS_NAME_GM VARCHAR(32),	
    MMS_DISCOUNT FLOAT,
    MMS_UPLINK_DISCOUNT FLOAT	
);

create table USERS(
    USER_REF_NO INTEGER NOT NULL PRIMARY KEY,
    USER_ACCT VARCHAR(32),
    USER_PWD VARCHAR(32),
    USER_NAME VARCHAR(32),
    USER_ROLE VARCHAR(32),
    USER_REMARK VARCHAR(128),
    USER_SEX VARCHAR(16),
    USER_CREATE_DATE VARCHAR(32),
    USER_MODIFY_DATE VARCHAR(32),
    USER_LAST_ACCESS_IP VARCHAR(32),
    USER_PHONE VARCHAR(32),
    USER_FAX VARCHAR(32),
    USER_ADDRESS LONGTEXT,
    USER_ADDRESS_2 VARCHAR(255),
    USER_POST_CODE VARCHAR(32),
    USER_UPLINK_REF_NO INTEGER,	 
    USER_LEVEL_REF_NO INTEGER,	
    USER_BIRTHDAY VARCHAR(16),
    USER_EMAIL VARCHAR(128),
    USER_CMP_REF_NO INTEGER,
    USER_IDENTITYNUM VARCHAR(32),	
    USER_RELATIVE_NAME VARCHAR(32),	
    USER_ADD_GOODS_ALLOWED INTEGER,
    USER_ADD_GOODS_USED INTEGER,
    USER_GRP_REF_NO INTEGER, 
    USER_BRD_REF_NO INTEGER,
    USER_FROM_COUNTRY VARCHAR(32),
    CONSTRAINT FK_USER_TO_MMS FOREIGN KEY (USER_LEVEL_REF_NO) REFERENCES MEMBERSHIP(MMS_REF_NO)
);

create table MONEY(
    MNY_REF_NO INTEGER NOT NULL PRIMARY KEY,
    USER_REF_NO INTEGER,
    MNY_REMAINING FLOAT,
    MNY_CURRENCY VARCHAR(32),
    MNY_SPENT FLOAT,
    MNY_EARNED FLOAT,	
    MNY_DEPOSIT FLOAT,
    CONSTRAINT FK_MNY_TO_USER_DELETE FOREIGN KEY (USER_REF_NO) REFERENCES USERS(USER_REF_NO) ON DELETE CASCADE
);

create table DEPOSIT(
    DST_REF_NO INTEGER NOT NULL PRIMARY KEY,
    USER_REF_NO INTEGER,
    DST_DEPOSITED FLOAT,
    DST_CREATE_DATE VARCHAR(32),
    CONSTRAINT FK_DST_TO_USERS FOREIGN KEY (USER_REF_NO) REFERENCES USERS(USER_REF_NO)
);

create table BILLINFO
(
    BIF_REF_NO INTEGER NOT NULL PRIMARY KEY,
    BIF_PHONE VARCHAR(32),
    BIF_FAX VARCHAR(32),
    BIF_ADDRESS VARCHAR(255),
    BIF_ADDRESS_2 VARCHAR(255),
    BIF_POST_CODE VARCHAR(32),
    CONSTRAINT FK_BIF_TO_USER_DELETE FOREIGN KEY (USER_REF_NO) REFERENCES USERS(USER_REF_NO) ON DELETE CASCADE
);

create table COMPANY(
    CMP_REF_NO INTEGER NOT NULL PRIMARY KEY,
    CMP_NAME_CN VARCHAR(64),
    CMP_NAME_EN VARCHAR(64),
    CMP_NAME_GM VARCHAR(64),
    CMP_DESCRPTION_EN          VARCHAR(128),   
    CMP_DESCRPTION_CN          VARCHAR(128),   
    CMP_DESCRPTION_GM          VARCHAR(128),   
    CMP_CREATION_DATE       VARCHAR(32),   
    CMP_URL                 VARCHAR(128), 
    CMP_PHONE               VARCHAR(32),  
    CMP_FAX                 VARCHAR(32),   
    CMP_EMAIL               VARCHAR(32),   
    CMP_ADDR1               VARCHAR(128),   
    CMP_ADDR2               VARCHAR(128),   
    CMP_ADDR3               VARCHAR(128),   
    CMP_POST_CODE           VARCHAR(16),    
    CMP_CITY                VARCHAR(16),    
    CMP_PROVINCE            VARCHAR(32),
    CMP_COUNTRY             VARCHAR(32),
    CMP_STATUS              VARCHAR(16)
);
create table GDGROUP(
    GRP_REF_NO INTEGER NOT NULL PRIMARY KEY,
    GRP_NAME_CN VARCHAR(64),
    GRP_NAME_EN VARCHAR(64),
    GRP_NAME_GM VARCHAR(64),
    CMP_REF_NO INTEGER,
    GRP_STATUS VARCHAR(16),
    GRP_COUNTRY VARCHAR(32),    
    GRP_LANGUAGE VARCHAR(32),     
    CONSTRAINT FK_GRP_TO_CMP FOREIGN KEY (CMP_REF_NO) REFERENCES COMPANY(CMP_REF_NO)
);

create table CATEGORY(
    CAT_REF_NO INTEGER NOT NULL PRIMARY KEY,
    CAT_NAME_CN VARCHAR(64),
    CAT_NAME_EN VARCHAR(64),
    CAT_NAME_GM VARCHAR(64),
    GRP_REF_NO INTEGER,
    CAT_STATUS VARCHAR(16),
    CAT_COUNTRY VARCHAR(32),   
    CAT_LANGUAGE VARCHAR(32),  
    CONSTRAINT FK_CAT_TO_GRP FOREIGN KEY (GRP_REF_NO) REFERENCES GDGROUP(GRP_REF_NO)
);

create table BRAND(
    BRD_REF_NO INTEGER NOT NULL PRIMARY KEY,
    BRD_NAME_CN VARCHAR(64),
    BRD_NAME_EN VARCHAR(64),
    BRD_NAME_GM VARCHAR(64),
    GRP_REF_NO INTEGER,
    BRD_STATUS VARCHAR(16),
    BRD_POST_PIC VARCHAR(128),
    BRD_COUNTRY VARCHAR(32),    
    BRD_LANGUAGE VARCHAR(32), 
    CONSTRAINT FK_BRD_TO_GRP FOREIGN KEY (GRP_REF_NO) REFERENCES GDGROUP(GRP_REF_NO)
);

CREATE TABLE GOODS(
    GDS_REF_NO INTEGER NOT NULL PRIMARY KEY,
    GDS_NAME_CN VARCHAR(128),
    GDS_NAME_EN VARCHAR(128),
    GDS_NAME_GM VARCHAR(128),
    GDS_PICT_S  VARCHAR(128),
    GDS_PICT_B  VARCHAR(128),
    GDS_NUM_IN_STOCK INTEGER,
    GDS_SERIAL_NO VARCHAR(64),
    GDS_PRICE FLOAT,
    GDS_DESCRIPTION_EN LONGTEXT,
    GDS_DESCRIPTION_CN LONGTEXT,
    GDS_DESCRIPTION_GM LONGTEXT,
    GDS_PROMOTION_FLAG VARCHAR(4),  
    GDS_DISCOUNT FLOAT,
    GDS_CATEGORY_REF_NO INTEGER,
    GDS_BRAND_REF_NO INTEGER,
    GDS_GROUP_REF_NO INTEGER,
    GDS_COMP_REF_NO INTEGER,
    GDS_STOP_SELL_FLAG INTEGER,
    GDS_CREATE_DATE VARCHAR(32),
    GDS_EXPIRE_DATE VARCHAR(32),
    GDS_NEW_GOOD_FLAG INTEGER,
    GDS_SPECIAL_GOOD_FLAG INTEGER,
    GDS_SPECIAL_GOOD_MIN_DISCOUNT FLOAT,    
    USER_REF_NO INTEGER,    	 
    GDS_COUNTRY VARCHAR(32),    
    GDS_LANGUAGE VARCHAR(32),    
    GDS_CUR_EX_RATE_FLAG INTEGER,    	     
    CONSTRAINT FK_GOODS_TO_CATEGORY FOREIGN KEY (GDS_CATEGORY_REF_NO) REFERENCES CATEGORY(CAT_REF_NO),
    CONSTRAINT FK_GOODS_TO_BRAND FOREIGN KEY (GDS_BRAND_REF_NO) REFERENCES BRAND(BRD_REF_NO),
    CONSTRAINT FK_GOODS_TO_GDGROUP FOREIGN KEY (GDS_GROUP_REF_NO) REFERENCES GDGROUP(GRP_REF_NO),
    CONSTRAINT FK_GOODS_TO_COMPANY FOREIGN KEY (GDS_COMP_REF_NO) REFERENCES COMPANY(CMP_REF_NO)
);

create table GDSAPENDINFO
(
    GAI_REF_NO INTEGER NOT NULL PRIMARY KEY,
    GAI_PHONE_NUM VARCHAR(128),
    GAI_PIN_NUM VARCHAR(32),  
    GAI_CARD_NUM VARCHAR(32),
    GAI_CARD_SERIALNO VARCHAR(32),  
    GAI_RESERVED_ONE VARCHAR(32),  
    GAI_RESERVED_TWO VARCHAR(32),  
    GDS_REF_NO INTEGER,	
    GAI_SOLD_FLAG INTEGER,	
    GAI_EXPIRE_DATE VARCHAR(32),  
    GAI_HOTLINE VARCHAR(32),  
    CONSTRAINT FK_APNDINFO_TO_GOODS FOREIGN KEY (GDS_REF_NO) REFERENCES GOODS(GDS_REF_NO)    	
);

create table TTLNUMSOLD(
    TNS_REF_NO INTEGER NOT NULL PRIMARY KEY,
    TNS_NUM_OUTLET_AVG FLOAT,
    GDS_REF_NO INTEGER,
    GDSPR_CREATE_DATE VARCHAR(32),
    CONSTRAINT FK_TNS_TO_GOODS FOREIGN KEY (GDS_REF_NO) REFERENCES GOODS(GDS_REF_NO)
);


create table GOODSPRICE(
    GDSPR_REF_NO INTEGER NOT NULL PRIMARY KEY,
    GDSPR_INLET FLOAT,
    GDS_REF_NO INTEGER,
    GDSPR_NUM_INLET INTEGER,
    GDSPR_CREATE_DATE VARCHAR(32),
    CONSTRAINT FK_GOODSPRICE_TO_GOODS FOREIGN KEY (GDS_REF_NO) REFERENCES GOODS(GDS_REF_NO) ON DELETE CASCADE
);



create table SELLGOODS(
    SLL_GDS_REF_NO INTEGER NOT NULL PRIMARY KEY,
    GDS_REF_NO INTEGER,
    GDS_NAME VARCHAR(128),
    SLL_GDS_PRICE FLOAT,
    USER_REF_NO INTEGER,
    SLL_GDS_NUM_OUTLET INTEGER,   
    SLL_GDS_CREATE_DATE VARCHAR(32),
    CONSTRAINT FK_SELLGOODS_TO_GOODS FOREIGN KEY (GDS_REF_NO) REFERENCES GOODS(GDS_REF_NO)
);

create table PENDGOODS(
    PND_GDS_REF_NO INTEGER NOT NULL PRIMARY KEY,
    GDS_REF_NO INTEGER,
    GDS_NAME VARCHAR(128),
    PND_GDS_PRICE FLOAT,
    USER_REF_NO INTEGER,
    PND_GDS_NUM_OUTLET INTEGER,   
    PND_GDS_CREATE_DATE VARCHAR(32),
    CONSTRAINT FK_PENDGOODS_TO_GOODS FOREIGN KEY (GDS_REF_NO) REFERENCES GOODS(GDS_REF_NO)
);

create table PROHIBITPHONE
(
    PHP_REF_NO INTEGER NOT NULL PRIMARY KEY,
    PHP_NUMBER VARCHAR(32)    
);

create table PROFIT
(
    PFT_REF_NO INTEGER NOT NULL PRIMARY KEY,
    PFT_INLET_AMOUNT FLOAT,
    PFT_OUTLET_AMOUNT FLOAT,
    PFT_STOCK_AMOUNT FLOAT,
    PFT_PROFIT FLOAT,
    PFT_YEAR_MONTH VARCHAR(16)
);

create table SHOPCART
(
    SC_REF_NO INTEGER NOT NULL PRIMARY KEY,
    SC_GDS_NAME VARCHAR(128),
    SC_GDS_PATH_S VARCHAR(128),
    SC_GDS_PATH_B VARCHAR(128),
    SC_GDS_SERIAL_NO VARCHAR(64),
    SC_GDS_PRICE FLOAT,
    SC_GDS_QUANTITY INTEGER,
    SC_GDS_DISCOUNT FLOAT,
    SC_GDS_REF_NO INTEGER,
    SC_GDS_INLET_PRICE FLOAT,
    SC_GDS_SES_ID VARCHAR(128),
    SC_GDS_CREATE_TIME VARCHAR(32)
);


ALTER TABLE USERS ADD CONSTRAINT FK_USER_LEVEL_REF_NO FOREIGN KEY (USER_LEVEL_REF_NO) REFERENCES MEMBERSHIP(MMS_REF_NO) ON DELETE SET NULL;

INSERT INTO USERS(USER_REF_NO, USER_ACCT, USER_PWD, USER_NAME, USER_ROLE, USER_REMARK, USER_SEX, USER_CREATE_DATE, USER_PHONE,USER_FAX, USER_ADDRESS, USER_ADDRESS_2, USER_POST_CODE, USER_LEVEL_REF_NO, USER_BIRTHDAY, USER_EMAIL) VALUES(1, 'admin', 'admin', 'Zou Xiong', 'admin', '', 'M', '2003:08:12', '97997709', '', 'BLK 287C Jurong Ease ST 21 #08-326', '', '603287', 1, '1976:11:04', 'sandodo@hotmail.com');

INSERT INTO MONEY(MNY_REF_NO, USER_REF_NO, MNY_REMAINING, MNY_CURRENCY, MNY_SPENT, MNY_EARNED) VALUES(1, 1, 0, 'S$', 0, 0);

INSERT INTO COMPANY(CMP_REF_NO, CMP_NAME_EN) VALUES(1, 'Loewen Enterprise');

commit;