package com.liquidnet.service.platform.service.impl.merchant;

import com.liquidnet.service.merchant.entity.MerchantFields;
import com.liquidnet.service.merchant.mapper.MerchantFieldsMapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.*;

@Service
@Slf4j
public class DataMigrationServiceImpl {

    @Autowired
    MerchantFieldsMapper fieldsMapper;

    public void dataFields() {

        try {

            // 1.添加场地
            Connection connection = DriverManager.getConnection("jdbc:mysql://39.107.71.112:3308/test_ln_scene", "testmall", "zhengzai!mYT");
            String sql = "SELECT mf.id AS 'field_id', 1 AS 'is_online', IF ( ISNULL( lsau.uid ), 0, lsau.uid ) AS 'claim_status', IF ( ISNULL( lsau.uid ), '', lsau.uid ) AS 'uid', 0 AS 'is_check', mf.`name` AS 'name', '' AS 'logo', IF ( LEFT ( mf.cover_img, 4 ) = 'http', mf.cover_img, '' ) AS 'background', IF ( ISNULL( mf.description ), '', mf.description ) AS 'description', NULL AS 'built_date', '' AS 'province_id', mf.province_name AS 'province_name', '' AS 'city_id', mf.city_name AS 'city_name', IF ( mf.ad_code > 7000, mf.ad_code, '' ) AS 'district_id', mf.district_name AS 'district_name', mf.address AS 'address', mf.longitude AS 'longitude', mf.latitude AS 'latitude', mm.`name` AS 'contact_name', mm.mobile AS 'contact_mobile', mm.email AS 'contact_email', '' AS 'company_id', IF ( ISNULL( mf.created_at ), mf.updated_at, mf.created_at ) AS 'created_at', mf.updated_at AS 'updated_at'  FROM testmall.`fields` AS mf LEFT JOIN testmall.merchants AS mm ON mm.id = mf.merchant_id AND mm.is_use = 'yes' LEFT JOIN test_ln_scene.adam_user AS lsau ON lsau.mobile = mm.mobile AND lsau.state = 1 WHERE mf.is_display = 1 AND mf.id IN ( 2, 8, 12, 16, 17, 24, 26, 28, 30, 31, 33, 34, 35, 37, 38, 45, 54, 58, 64, 72, 77, 93, 100, 110, 120, 133, 139, 206, 207, 235, 267, 278, 303, 332, 334, 341, 370, 374, 378, 379, 381, 384, 386, 394, 399, 431, 433, 435, 467, 482, 483, 518, 527, 529, 544, 554, 563, 570, 596, 614, 624, 627, 650, 663, 691, 693, 701, 713, 718, 724, 726, 729, 730, 762, 793, 795, 805, 809, 812, 818, 832, 835, 845, 847, 851, 853, 857, 858, 859, 861, 862, 863, 865, 869, 871, 872, 879, 881, 885, 886, 887, 888, 894, 896, 900, 903, 907, 912, 918, 920, 923, 924, 925, 931, 934, 940, 944, 946, 954, 960, 976, 977, 981, 982, 984, 989, 996, 997, 1004, 1038, 1042, 1064, 1070, 1101, 1102, 1155, 1180, 1202, 1358, 1863, 2265, 2814, 3077, 3093, 3190, 3338, 4059, 4155, 4222, 4571, 4573, 4574, 4585, 4586, 4588, 4596, 10001, 10003, 10004, 10005, 10006, 10007, 10008, 10014, 10016, 10017, 10018, 10019, 10020, 10021, 10022, 10023, 10024, 10026, 10027, 10028, 10029, 10030, 10031, 10032, 10033, 10034, 10035, 10036, 10039, 10040, 10042, 10043, 10044, 10045, 10046, 10047, 10048, 10050, 10051, 10052, 10053, 10054, 10055, 10056, 10057, 10058, 10059, 10061, 10062, 10063, 10064, 10065, 10069, 10074, 10076, 10077, 10078, 10079, 10080, 10081, 10082, 10084, 10085, 10086, 10088, 10090, 10094, 10095, 10097, 10098, 10100, 10101, 10102, 10103, 10105, 10106, 10107, 10108, 10109, 10113, 10115, 10117, 10119, 10121, 10123, 10125, 10131, 10132, 10134, 10135, 10136, 10139, 10140, 10141, 10142, 10144, 10145, 10146, 10147, 10148, 10149, 10151, 10153, 10155 )";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            ResultSet rs = preparedStatement.executeQuery();
            while (rs.next()) {
                MerchantFields fields = new MerchantFields();
                fields.setFieldId(rs.getString("field_id"));
                fields.setIsOnline(rs.getInt("is_online"));

                String contactMobile = rs.getString("contact_mobile");
                if (null != contactMobile && !contactMobile.isEmpty()) {
                    if (rs.getString("uid").isEmpty()) {
                        // register
                    } else {
                        fields.setClaimStatus(rs.getInt("claim_status"));
                        fields.setUid(rs.getString("uid"));
                    }
                } else {
                    fields.setClaimStatus(rs.getInt("claim_status"));
                    fields.setUid(rs.getString("uid"));
                }

                fields.setIsCheck(rs.getInt("is_check"));
                fields.setName(rs.getString("name"));
                fields.setLogo(rs.getString("logo"));
                fields.setBackground(rs.getString("background").contains(".") ? rs.getString("background") : "");
                fields.setDescription("空".equals(rs.getString("description")) ? "" : rs.getString("description"));
                fields.setBuiltDate(null != rs.getDate("built_date") ? rs.getDate("built_date").toLocalDate() : null );
                fields.setProvinceId(rs.getString("province_id"));
                fields.setProvinceName(rs.getString("province_name"));
                fields.setCityId(rs.getString("city_id"));
                fields.setCityName(rs.getString("city_name"));
                fields.setDistrictId(rs.getString("district_id"));
                fields.setDistrictName(rs.getString("district_name"));
                fields.setAddress(null != rs.getString("address") ? rs.getString("address") : "");
                fields.setLongitude(rs.getString("longitude").contains(".") ? rs.getString("longitude") : "");
                fields.setLatitude(rs.getString("latitude").contains(".") ? rs.getString("longitude") : "");
                fields.setContactName(null != rs.getString("contact_name") ? rs.getString("contact_name") : "");
                fields.setContactEmail(null != rs.getString("contact_email") ? rs.getString("contact_email") : "");
                fields.setCompanyId(rs.getString("company_id"));
                fields.setCreatedAt(rs.getTimestamp("created_at").toLocalDateTime());
                fields.setUpdatedAt(rs.getTimestamp("updated_at").toLocalDateTime());
                fieldsMapper.insert(fields);
            }

            

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
