CREATE TABLE IF NOT EXISTS stores_quota (
    `District_TC` VARCHAR(2) CHARACTER SET utf8,
    `District_SC` VARCHAR(2) CHARACTER SET utf8,
    `Store_name_in_SMS_TC` VARCHAR(13) CHARACTER SET utf8,
    `Store_name_in_SMS_SC` VARCHAR(13) CHARACTER SET utf8,
    `LEN` INT,
    `Quota_for_7_1_20_1` INT,
    `Quota_for_21_1_27_1` INT ,
    `Claimed_count` INT ,
    `store_phone_num` VARCHAR(17) CHARACTER SET utf8,
    `op_time` VARCHAR(13) CHARACTER SET utf8,
    `location` VARCHAR(40) CHARACTER SET utf8,
    CHECK (Quota_for_7_1_20_1+Quota_for_21_1_27_1 <= Claimed_count)
);
INSERT INTO stores_quota VALUES
    ('九龍','九龙','海運大廈連卡佛專櫃','海运大厦连卡佛专柜',9,2500,1100,0,'+852 12345678','00:00-23:59','simple location'),
    ('香港','香港','時代廣場連卡佛專櫃','时代广场连卡佛专柜',9,900,410,0,'+852 12345678','00:00-23:59','simple location'),
    ('香港','香港','銅鑼灣SOGO專櫃','铜锣湾崇光百货专柜',9,1600,650,0,'+852 12345678','00:00-23:59','simple location'),
    ('九龍','九龙','尖沙咀SOGO專櫃','尖沙咀崇光百货专柜',9,900,450,0,'+852 12345678','00:00-23:59','simple location'),
    ('澳門','澳门','澳門新八佰伴專櫃','澳门新八佰伴专柜',8,1600,750,0,'+852 12345678','00:00-23:59','simple location'),
    ('九龍','九龙','朗豪坊L1專櫃','朗豪坊L1专柜',7,1800,600,0,'+852 12345678','00:00-23:59','simple location'),
    ('新界','新界','荃灣廣場專櫃','荃湾广场专柜',6,250,100,0,'+852 12345678','00:00-23:59','simple location'),
    ('香港','香港','置地廣場專櫃 ','置地广场专柜',7,640,300,0,'+852 12345678','00:00-23:59','simple location'),
    ('香港','香港','太古廣場專櫃 ','太古广场专柜',7,350,130,0,'+852 12345678','00:00-23:59','simple location'),
    ('九龍','九龙','The One專櫃 ','The One专柜',10,500,210,0,'+852 12345678','00:00-23:59','simple location'),
    ('香港','香港','上環永安專櫃','上环永安专柜',6,500,150,0,'+852 12345678','00:00-23:59','simple location'),
    ('九龍','九龙','彌敦道永安專櫃','弥敦道永安专柜',7,450,150,0,'+852 12345678','00:00-23:59','simple location'),
    ('九龍','九龙','又一城專門店','又一城专门店',6,1200,550,0,'+852 12345678','00:00-23:59','simple location'),
    ('新界','新界','新城巿廣場專門店','新城巿广场专门店',8,2300,1000,0,'+852 12345678','00:00-23:59','simple location'),
    ('香港','香港','IFC專門店','国际金融中心专门店',6,1000,450,0,'+852 12345678','00:00-23:59','simple location'),
    ('九龍','九龙','圓方專門店','圓方专门店',5,660,280,0,'+852 12345678','00:00-23:59','simple location'),
    ('新界','新界','將軍澳PopCorn專門店','将军澳PopCorn专门店',13,450,170,0,'+852 12345678','00:00-23:59','simple location'),
    ('新界','新界','屯門市廣場專門店','屯门广场专门店',8,1250,550,0,'+852 12345678','00:00-23:59','simple location'),
    ('新界','新界','上水廣場專門店','上水广场专门店',7,1800,800,0,'+852 12345678','00:00-23:59','simple location'),
    ('香港','香港','太古城專門店','太古城专门店',6,450,180,0,'+852 12345678','00:00-23:59','simple location'),
    ('九龍','九龙','德褔廣場專門店','德福广场专门店',7,800,350,0,'+852 12345678','00:00-23:59','simple location'),
    ('九龍','九龙','新世紀廣場專門店','新世纪广场专门店',8,650,250,0,'+852 12345678','00:00-23:59','simple location'),
    ('新界','新界','V City專門店','V City专门店',9,750,350,0,'+852 12345678','00:00-23:59','simple location'),
    ('新界','新界','Yoho1期專門店','形点1期专门店',9,750,320,0,'+852 12345678','00:00-23:59','simple location'),
    ('新界','新界','葵芳新都會專門店','葵芳新都会专门店',8,350,150,0,'+852 12345678','00:00-23:59','simple location'),
    ('香港','香港','北角海匯酒店專門店','北角海汇酒店专门店',9,100,100,0,'+852 12345678','00:00-23:59','simple location');

CREATE TABLE IF NOT EXISTS gift (
    `name` VARCHAR(30) CHARACTER SET utf8 NOT NULL,
    `phone_num_pre` CHAR(3) CHARACTER SET utf8 NOT NULL,
    `phone_num` VARCHAR(13) CHARACTER SET utf8 NOT NULL,
    `email` VARCHAR(30) CHARACTER SET utf8,
    `area` CHAR(2) CHARACTER SET utf8 NOT NULL,
    `branch` VARCHAR(13) CHARACTER SET utf8  NOT NULL,
    `cofirm_recive_ad` CHAR(1) NOT NULL,
    `redeemed` CHAR(1) DEFAULT 'F',
    PRIMARY KEY (phone_num)
);