CREATE TABLE `ta_order`  (
  `order_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单id',
  `org_id` int(11) NULL DEFAULT NULL COMMENT '公司id',
  `create_date` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `pay_time` datetime(0) NULL DEFAULT NULL COMMENT '支付时间',
  `target_id` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'targetId',
  `target_type` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'targetType',
  `person_id` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '小程序用户id',
  `trade_no` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '微信订单号',
  `total_fee` int(11) NULL DEFAULT NULL COMMENT '实付金额 单位分',
  `pay_status` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '缴费状态 unpaid未缴费paid已缴费refunded已退费',
  `parent_order_id` int(11) NULL DEFAULT NULL COMMENT '如果是退费需要知道是那个订单退费',
  `transaction_id` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '微信订单号',
  `refund_no` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '退款单号',
  PRIMARY KEY (`order_id`) USING BTREE,
  INDEX `idx_target`(`target_id`, `target_type`) USING BTREE,
  INDEX `idx_order_org`(`org_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 209 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '订单表 ' ROW_FORMAT = Dynamic;

CREATE TABLE `ta_person_position`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `person_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `lon` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '经度',
  `lat` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '纬度',
  `citycode` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '城市编码',
  `country` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '国家',
  `province` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '省份',
  `city` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '城市',
  `district` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '区县',
  `address` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '详细地址',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

CREATE TABLE `ta_preselection`  (
  `preselection_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '预选id',
  `org_id` int(11) NULL DEFAULT NULL COMMENT '公司id',
  `building_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `sales_batch_id` int(11) NULL DEFAULT NULL COMMENT '销售批次id',
  `preselection_start_time` datetime(0) NULL DEFAULT NULL COMMENT '预选开始时间',
  `preselection_end_time` datetime(0) NULL DEFAULT NULL COMMENT '预选结束时间',
  `status` int(11) NULL DEFAULT NULL COMMENT '状态',
  `create_date` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`preselection_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 58 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '预选表 ' ROW_FORMAT = Dynamic;

CREATE TABLE `ta_raise`  (
  `raise_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '认筹id',
  `org_id` int(11) NULL DEFAULT NULL COMMENT '公司id',
  `building_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `sales_batch_id` int(11) NULL DEFAULT NULL COMMENT '销售批次id',
  `raise_start_time` datetime(0) NULL DEFAULT NULL COMMENT '认筹开始时间',
  `raise_end_time` datetime(0) NULL DEFAULT NULL COMMENT '认筹结束时间',
  `house_locking_type` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '锁房方式 auto自动锁房，manual手动锁房',
  `raise_price` int(11) NULL DEFAULT NULL COMMENT '认筹金额 单位是分！',
  `need_preselection` tinyint(1) NULL DEFAULT NULL COMMENT '认筹是否预选',
  `display_house_price` tinyint(1) NULL DEFAULT NULL COMMENT '是否展示房源价格',
  `pay_type` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '缴费方式 onLine线上offLine线下',
  `pay_description_offline` varchar(3072) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '缴费说明',
  `pay_description_online` varchar(3072) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `raise_precautions` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `pay_protocol` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '线上选房协议',
  `status` int(11) NULL DEFAULT NULL COMMENT '状态',
  `create_date` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`raise_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 68 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '认筹表 ' ROW_FORMAT = Dynamic;

CREATE TABLE `ta_raise_house`  (
  `raise_house_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '认筹单下的房源id',
  `org_id` int(11) NULL DEFAULT NULL COMMENT '公司id',
  `raise_record_id` int(11) NULL DEFAULT NULL COMMENT '认筹单id',
  `house_id` int(11) NULL DEFAULT NULL COMMENT '房源id',
  `create_date` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`raise_house_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 189 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '认筹单与房源关系表 ' ROW_FORMAT = Dynamic;

CREATE TABLE `ta_raise_record`  (
  `raise_record_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '认筹记录表',
  `org_id` int(11) NULL DEFAULT NULL COMMENT '公司id',
  `building_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '楼盘id',
  `raise_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '认筹id',
  `sales_batch_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '销售批次id',
  `person_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '认筹人',
  `person_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '认筹人姓名',
  `house_num` int(11) NULL DEFAULT NULL COMMENT '房源数',
  `house_locking_status` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '认筹的房源锁定状态 locked已锁定unlocked未锁定',
  `tel` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '账户手机号',
  `user_id` int(11) NULL DEFAULT NULL COMMENT '置业顾问id',
  `user_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '置业顾问姓名',
  `status` int(11) NULL DEFAULT NULL COMMENT '是否作废',
  `invalid_reason` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '作废原因',
  `pay_status` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '缴费状态 unpaid未缴费paid已缴费refunded已退费',
  `pay_type` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '缴费方式 onLine线上offLine线下',
  `refund_reason` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '退费原因',
  `create_date` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `update_date` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
  `update_user_name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新人',
  `idcard` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '身份证号',
  `name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `order_id` int(11) NULL DEFAULT NULL COMMENT '订单id',
  PRIMARY KEY (`raise_record_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 145 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '认筹单(认筹记录表) ' ROW_FORMAT = Dynamic;

CREATE TABLE `ta_wx_pay_config`  (
  `wx_pay_config_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '微信配置id',
  `org_id` int(11) NULL DEFAULT NULL COMMENT '公司id',
  `building_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '楼盘id',
  `mch_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '微信支付商户号',
  `appid` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'API密钥',
  `secret` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'Appsecret',
  `api_path` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'api证书路径',
  `mch_key` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '商户key',
  `create_date` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`wx_pay_config_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 23 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '微信支付设置 ' ROW_FORMAT = Dynamic;

CREATE TABLE `sys_advanced_config`  (
  `advanced_config_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '高级配置id',
  `advanced_config_name` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '高级配置名称',
  `create_date` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `status` int(11) NULL DEFAULT NULL COMMENT '状态',
  PRIMARY KEY (`advanced_config_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '高级配置基础表 ' ROW_FORMAT = Dynamic;

CREATE TABLE `sys_advanced_config_relation`  (
  `relation_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '对应关系id',
  `target_type` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '对应类型 menu菜单btn按钮',
  `target_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '对应类型的id menuId或btnId',
  `advanced_config_id` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`relation_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '高级配置对应关系表 ' ROW_FORMAT = Dynamic;

CREATE TABLE `ta_advanced_controller`  (
  `advanced_controller_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '高级配置控制id',
  `org_id` int(11) NULL DEFAULT NULL COMMENT '公司id',
  `advanced_config_id` int(11) NULL DEFAULT NULL COMMENT '高级配置id',
  `create_date` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`advanced_controller_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 79 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '高级配置控制表 ' ROW_FORMAT = Dynamic;





CREATE TABLE ta_miniapp_theme(
    `theme_id` INT NOT NULL AUTO_INCREMENT  COMMENT '主题ID' ,
    `theme_title` VARCHAR(512)    COMMENT '主题名称' ,
    `start_time` VARCHAR(32)    COMMENT '启用时间 格式MM-DDTHH24:MI' ,
    `end_time` VARCHAR(32)    COMMENT '停用时间 格式MM-DDTHH24:MI' ,
    `navi_front_color` VARCHAR(32)    COMMENT '导航栏前景色' ,
    `navi_back_color` VARCHAR(32)    COMMENT '导航栏背景色' ,
    `tabbar_font_color` VARCHAR(32)    COMMENT 'TabBar前景色' ,
    `tabbar_back_color` VARCHAR(32)    COMMENT 'TabBar背景色' ,
    `tabbar_select_color` VARCHAR(32)    COMMENT 'TabBar选择色' ,
    `status` TINYINT(4)    COMMENT '当前状态' ,
    `org_id` INT    COMMENT '公司id' ,
    `modify_user` INT    COMMENT '修改人' ,
    `modify_date` DATETIME    COMMENT '修改时间' ,
    PRIMARY KEY (theme_id)
) COMMENT = '小程序主题设置 ';

insert into ta_preselection (org_id, building_id, sales_batch_id, preselection_start_time, preselection_end_time, status, create_date)
(select org_id, building_id, sales_batch_id, preselection_start_time, preselection_end_time, status, create_date from ta_sales_batch);

ALTER TABLE ta_miniapp_theme ADD INDEX idx_apptheme_org(org_id);

ALTER TABLE `ta_miniapp_org_icon` ADD COLUMN `theme_id` INT NULL AFTER `org_id`;

alter table ta_housing_resources add house_locking_status varchar(32);
alter table ta_preselection_record add preselection_id varchar(32);
alter table ta_person add idcard varchar(32);
alter table ta_sales_batch add display_house_price TINYINT(1) COMMENT '是否展示房源价格';


INSERT INTO `sys_menu`(`menu_id`, `code`, `name`, `parent_code`, `icon`, `app_type`, `status`, `menu_root`, `sort_num`) VALUES (100, '/house/house/list', '房源管理', '96', NULL, 'estateagent-admin', 1, 96, 1);
INSERT INTO `sys_menu`(`menu_id`, `code`, `name`, `parent_code`, `icon`, `app_type`, `status`, `menu_root`, `sort_num`) VALUES (101, '/house/preSelect/list', '预选管理', '96', NULL, 'estateagent-admin', 1, 96, 2);
INSERT INTO `sys_menu`(`menu_id`, `code`, `name`, `parent_code`, `icon`, `app_type`, `status`, `menu_root`, `sort_num`) VALUES (102, '/house/raise/list', '认筹管理', '96', NULL, 'estateagent-admin', 1, 96, 3);

insert INTO ta_role_menu (role_id,menu_id) SELECT role_id,100 from ta_role WHERE is_admin = 1;
insert INTO ta_role_menu (role_id,menu_id) SELECT role_id,101 from ta_role WHERE is_admin = 1;
insert INTO ta_role_menu (role_id,menu_id) SELECT role_id,102 from ta_role WHERE is_admin = 1;


INSERT INTO `sys_button_in_menu`(`btn_id`, `menu_id`, `name`, `status`, `code`, `remark`) VALUES (190, 100, '新增', 1, 'admin.house.add.post', NULL);
INSERT INTO `sys_button_in_menu`(`btn_id`, `menu_id`, `name`, `status`, `code`, `remark`) VALUES (191, 100, '删除', 1, 'admin.house.del', NULL);
INSERT INTO `sys_button_in_menu`(`btn_id`, `menu_id`, `name`, `status`, `code`, `remark`) VALUES (192, 100, '查看详情', 1, 'admin.house.detail.get', NULL);
INSERT INTO `sys_button_in_menu`(`btn_id`, `menu_id`, `name`, `status`, `code`, `remark`) VALUES (193, 101, '新增', 1, 'admin.preselection.add.post', NULL);
INSERT INTO `sys_button_in_menu`(`btn_id`, `menu_id`, `name`, `status`, `code`, `remark`) VALUES (194, 101, '删除', 1, 'admin.preselection.del', NULL);
INSERT INTO `sys_button_in_menu`(`btn_id`, `menu_id`, `name`, `status`, `code`, `remark`) VALUES (195, 101, '查看详情', 1, 'admin.preselection.detail.get', NULL);
INSERT INTO `sys_button_in_menu`(`btn_id`, `menu_id`, `name`, `status`, `code`, `remark`) VALUES (196, 102, '新增', 1, 'admin.raise.add.post', NULL);
INSERT INTO `sys_button_in_menu`(`btn_id`, `menu_id`, `name`, `status`, `code`, `remark`) VALUES (197, 102, '删除', 1, 'admin.raise.del', NULL);
INSERT INTO `sys_button_in_menu`(`btn_id`, `menu_id`, `name`, `status`, `code`, `remark`) VALUES (198, 102, '查看详情', 1, 'admin.raise.detail.get', NULL);



insert INTO ta_role_button (role_id,btn_id) SELECT role_id,190 from ta_role WHERE is_admin = 1;
insert INTO ta_role_button (role_id,btn_id) SELECT role_id,191 from ta_role WHERE is_admin = 1;
insert INTO ta_role_button (role_id,btn_id) SELECT role_id,192 from ta_role WHERE is_admin = 1;
insert INTO ta_role_button (role_id,btn_id) SELECT role_id,193 from ta_role WHERE is_admin = 1;
insert INTO ta_role_button (role_id,btn_id) SELECT role_id,194 from ta_role WHERE is_admin = 1;
insert INTO ta_role_button (role_id,btn_id) SELECT role_id,195 from ta_role WHERE is_admin = 1;
insert INTO ta_role_button (role_id,btn_id) SELECT role_id,196 from ta_role WHERE is_admin = 1;
insert INTO ta_role_button (role_id,btn_id) SELECT role_id,197 from ta_role WHERE is_admin = 1;
insert INTO ta_role_button (role_id,btn_id) SELECT role_id,198 from ta_role WHERE is_admin = 1;



INSERT INTO `sys_advanced_config`(`advanced_config_id`, `advanced_config_name`, `create_date`, `status`) VALUES (1, '开通预选', '2020-02-29 14:40:41', 1);
INSERT INTO `sys_advanced_config`(`advanced_config_id`, `advanced_config_name`, `create_date`, `status`) VALUES (2, '开通认筹', '2020-02-29 14:41:01', 1);
INSERT INTO `sys_advanced_config`(`advanced_config_id`, `advanced_config_name`, `create_date`, `status`) VALUES (3, '开通直播活动', '2020-02-29 14:41:14', 1);

INSERT INTO `sys_advanced_config_relation`(`relation_id`, `target_type`, `target_id`, `advanced_config_id`) VALUES (1, 'menu', '98', 3);
INSERT INTO `sys_advanced_config_relation`(`relation_id`, `target_type`, `target_id`, `advanced_config_id`) VALUES (2, 'menu', '101', 1);
INSERT INTO `sys_advanced_config_relation`(`relation_id`, `target_type`, `target_id`, `advanced_config_id`) VALUES (3, 'menu', '102', 2);




alter table ta_housing_resources add house_locking_person varchar(64);
alter table ta_housing_resources add raise_heat int(11);
alter table ta_housing_resources add raise_real_heat int(11);

ALTER TABLE `ta_order` 
ADD INDEX `idx_order_target`(`target_id`, `target_type`);

ALTER TABLE `ta_order` 
ADD INDEX `idx_order_org`(`org_id`);

ALTER TABLE `ta_share_person_from` 
ADD INDEX `idx_share_person_org`(`org_id`),
ADD INDEX `idx_share_person_target`(`target_type`, `target_id`);


CREATE  PROCEDURE `change_house_order_status`( )
BEGIN
UPDATE ta_housing_resources r SET  r.house_locking_status = 'unlocked' WHERE
EXISTS (
SELECT
	raise_record_id 
FROM
	ta_raise_house t 
WHERE
	r.house_id = t.house_id and 
	EXISTS (
SELECT
	o.order_id 
FROM
	ta_order o 
WHERE
	o.target_id = t.raise_record_id 
	AND o.pay_status = "paying" 
	AND now( ) > SUBDATE( create_date, INTERVAL - 15 MINUTE ) 
	AND o.target_type = "house" 
	) 
	AND t.house_locking_status = "locked" 
	);
UPDATE ta_raise_record t 
SET t.house_locking_status = 'unlocked' 
WHERE
	EXISTS (
SELECT
	o.order_id 
FROM
	ta_order o 
WHERE
	o.target_id = t.raise_record_id 
	AND o.pay_status = "paying" 
	AND now( ) > SUBDATE( create_date, INTERVAL - 15 MINUTE ) 
	AND o.target_type = "house" 
	) 
	AND t.house_locking_status = "locked";
UPDATE ta_raise_house t 
SET t.house_locking_status = 'unlocked' 
WHERE
	EXISTS (
SELECT
	o.order_id 
FROM
	ta_order o 
WHERE
	o.target_id = t.raise_record_id 
	AND o.pay_status = "paying" 
	AND now( ) > SUBDATE( create_date, INTERVAL - 15 MINUTE ) 
	AND o.target_type = "house" 
	) 
	AND t.house_locking_status = "locked";
UPDATE ta_order 
SET pay_status = "timeout" 
WHERE
	pay_status = "paying" 
	AND now( ) > SUBDATE( create_date, INTERVAL - 15 MINUTE ) 
	AND target_type = "house";

END



DROP EVENT IF EXISTS change_house_order_status_event
CREATE EVENT change_house_order_status_event
ON SCHEDULE EVERY 15 minute  
ON COMPLETION PRESERVE
DO
BEGIN
CALL change_house_order_status();
END