package com.ease.gsms.server.repositories;

import com.ease.gsms.server.model.*;
import com.ease.gsms.server.model.util.IDEnum;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Collections;
import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;

@Repository
@Transactional
public class DeviceRepository {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Value("${sql.getIdentity}")
    private String callIdentitySQL;

    @Value("${sql.device.getStaleOnlineDevices}")
    private String getStaleOnlineDevicesSQL;

    @Value("${sql.device.getStaleOnlineSims}")
    private String getStaleOnlineSimsSQL;

    @Value("${sql.device.getDeviceByIdentifier}")
    private String getDeviceByIdentifierSQL;

    @Value("${sql.device.getDeviceBySimSerialNumber}")
    private String getDeviceBySimSerialNumberSQL;

    @Value("${sql.device.getSimsByDeviceIdentifier}")
    private String getSimsByDeviceIdentifierSQL;

    @Value("${sql.device.getSimByIdentifier}")
    private String getSimByIdentifierSQL;

    @Value("${sql.device.getUserDevices}")
    private String getUserDevicesSQL;

    @Value("${sql.device.getUserSims}")
    private String getUserSimsSQL;

    @Value("${sql.device.insertDevice}")
    private String insertDeviceSQL;

    @Value("${sql.device.insertSim}")
    private String insertSimSQL;

    @Value("${sql.device.updateDevice}")
    private String updateDeviceSQL;

    @Value("${sql.device.updateSim}")
    private String updateSimSQL;

    @Value("${sql.device.deleteUnreferencedSims}")
    private String deleteUnreferencedSimsSQL;

    public Device getDeviceByUuid(String deviceUuid) {

        try {

            return jdbcTemplate.queryForObject(getDeviceByIdentifierSQL, new Object[] { deviceUuid }, new DeviceRowMapper() );

        } catch (EmptyResultDataAccessException e) {

            return null;

        }

    }

    public Sim[] getSimsByDeviceUuid(String deviceUuid) {

        try {

            return jdbcTemplate.query(getSimsByDeviceIdentifierSQL, new Object[] { deviceUuid }, new SimRowMapper() ).stream().toArray(Sim[]::new);

        } catch (EmptyResultDataAccessException e) {

            return null;

        }

    }

    public Sim getSimBySerialNumber(String serialNumber) {

        try {

            return jdbcTemplate.queryForObject(getSimByIdentifierSQL, new Object[]{serialNumber}, new SimRowMapper());

        } catch (EmptyResultDataAccessException e) {

            return null;

        }

    }

    public DeviceInfo getDeviceInfoByUuid(String uuid) {

        Device device = getDeviceByUuid(uuid);

        Sim[] sims = getSimsByDeviceUuid(uuid);

        if (device != null) {

            return createDeviceInfo(Collections.singletonList(device), Arrays.asList(sims))[0];

        } else {

            return null;

        }

    }


    public Device getDeviceBySimSerialNumber(String simSerialNumber) {

        try {

            return jdbcTemplate.queryForObject(getDeviceBySimSerialNumberSQL, new Object[] { simSerialNumber }, new DeviceRowMapper() );

        } catch (EmptyResultDataAccessException e) {

            return null;

        }

    }

    public Device update(Device device) {

        String deviceUuid = device.getUuid();

        Device existing = deviceUuid == null ? null : getDeviceByUuid(deviceUuid);

        if (existing != null) { // update

            device.setId(existing.getId());

            jdbcTemplate.update(updateDeviceSQL, new DevicePreparedStatementSetter(device));

        } else { // insert

            jdbcTemplate.update(insertDeviceSQL, new DevicePreparedStatementSetter(device));

            device.setId(jdbcTemplate.queryForObject(callIdentitySQL, Long.class));

        }

        return device;

    }

    public void update(Sim sim) {

        String simSerialNumber = sim.getSimSerialNumber();

        Sim existing = simSerialNumber == null ? null : getSimBySerialNumber(simSerialNumber);

        if (existing != null) { // update

            sim.setId(existing.getId());

            jdbcTemplate.update(updateSimSQL, new SimPreparedStatementSetter(sim));

        } else { // insert

            jdbcTemplate.update(insertSimSQL, new SimPreparedStatementSetter(sim));

            sim.setId(jdbcTemplate.queryForObject(callIdentitySQL, Long.class));

        }

    }

    public void update(Device device, Sim[] sims) {

        update(device);

        for (Sim sim : sims) {

            sim.setPhysicalDeviceId(device.getId());

            update(sim);

        }

        List<String> validSimSerialNumbers = Arrays.stream(sims).map(Sim::getSimSerialNumber).collect(Collectors.toList());

        StringBuilder sb = new StringBuilder();

        sb.append(deleteUnreferencedSimsSQL);

        int validSimCount = validSimSerialNumbers.size();

        if (validSimCount > 0) {

            sb
                .append(" and sim_serial_number not in (")
                .append(
                        String.join(",", Collections.nCopies(validSimCount, "?"))
                )
                .append(")");

        }

        Object[] args = new Object[validSimCount + 1];

        args[0] = device.getId();

        System.arraycopy(validSimSerialNumbers.toArray(), 0, args, 1, validSimCount);

        jdbcTemplate.update(sb.toString(), args);



    }

    public DeviceInfo[] getStaleOnlineDevice(long timestamp) {

        return createDeviceInfo(
                jdbcTemplate.query(getStaleOnlineDevicesSQL, new Object[]{ timestamp }, new DeviceRowMapper()),
                jdbcTemplate.query(getStaleOnlineSimsSQL, new Object[]{ timestamp }, new SimRowMapper())
        );

    }

    public DeviceInfo[] getUserDevices(String userName) {

        return createDeviceInfo(
                jdbcTemplate.query(getUserDevicesSQL, new Object[]{ userName }, new DeviceRowMapper()),
                jdbcTemplate.query(getUserSimsSQL, new Object[]{ userName }, new SimRowMapper())
        );

    }

    private DeviceInfo[] createDeviceInfo(List<Device> deviceList, List<Sim> simList) {

        DeviceInfo[] deviceInfos = new DeviceInfo[deviceList.size()];

        int i = 0;

        for (Device device:  deviceList) {

            deviceInfos[i++] = new DeviceInfo(
                    device,
                    simList.stream().filter(sim -> sim.getPhysicalDeviceId().equals(device.getId())).toArray(Sim[]::new)
            );

        }

        return deviceInfos;

    }

    class DevicePreparedStatementSetter implements PreparedStatementSetter  {

        private Device device;

        public DevicePreparedStatementSetter(Device device) {

            this.device = device;

        }

        @Override
        public void setValues(PreparedStatement ps) throws SQLException {

            int parameterIndex = 1;

            ps.setString(parameterIndex++,  device.getUuid());
            ps.setString(parameterIndex++,  device.getOwner());
            ps.setString(parameterIndex++,  device.getCordova());
            ps.setString(parameterIndex++,  device.getModel());
            ps.setString(parameterIndex++,  device.getPlatform());
            ps.setString(parameterIndex++,  device.getVersion());
            ps.setString(parameterIndex++,  device.getManufacturer());
            ps.setBoolean(parameterIndex++, device.getIsVirtual());
            ps.setString(parameterIndex++,  device.getSerial());
            ps.setString(parameterIndex++,  device.getCarrierName());
            ps.setString(parameterIndex++,  device.getCountryCode());
            ps.setString(parameterIndex++,  device.getMcc());
            ps.setString(parameterIndex++,  device.getMnc());
            ps.setInt(parameterIndex++,     device.getCallState().getId());
            ps.setInt(parameterIndex++,     device.getDataActivity().getId());
            ps.setInt(parameterIndex++,     device.getNetworkType().getId());
            ps.setInt(parameterIndex++,     device.getPhoneType().getId());
            ps.setInt(parameterIndex++,     device.getSimState().getId());
            ps.setBoolean(parameterIndex++, device.getIsNetworkRoaming());
            ps.setInt(parameterIndex++,     device.getPhoneCount() != null ? device.getPhoneCount() : -1);
            ps.setInt(parameterIndex++,     device.getActiveSubscriptionInfoCount());
            ps.setInt(parameterIndex++,     device.getActiveSubscriptionInfoCountMax());
            ps.setString(parameterIndex++,  device.getPhoneNumber());
            ps.setString(parameterIndex++,  device.getDeviceId());
            ps.setString(parameterIndex++,  device.getDeviceSoftwareVersion());
            ps.setString(parameterIndex++,  device.getSimSerialNumber());
            ps.setString(parameterIndex++,  device.getSubscriberId());
            ps.setLong(parameterIndex++,    device.getLastSeen().getTime());
            ps.setBoolean(parameterIndex++, device.getOnline());

            if (device.getId() != null && device.getId() >= 0) {

                ps.setLong(parameterIndex, device.getId());

            }

        }

    }

    class SimPreparedStatementSetter implements PreparedStatementSetter  {

        private Sim sim;

        public SimPreparedStatementSetter(Sim sim) {

            this.sim = sim;

        }

        @Override
        public void setValues(PreparedStatement ps) throws SQLException {

            int parameterIndex = 1;

            ps.setString(parameterIndex++,  sim.getCarrierName());
            ps.setString(parameterIndex++,  sim.getDisplayName());
            ps.setString(parameterIndex++,  sim.getCountryCode());
            ps.setString(parameterIndex++,  sim.getMcc());
            ps.setString(parameterIndex++,  sim.getMnc());
            ps.setBoolean(parameterIndex++, sim.getIsNetworkRoaming());
            ps.setBoolean(parameterIndex++, sim.getIsDataRoaming());
            ps.setInt(parameterIndex++,     sim.getSimSlotIndex());
            ps.setString(parameterIndex++,  sim.getPhoneNumber());
            ps.setString(parameterIndex++,  sim.getDeviceId());
            ps.setString(parameterIndex++,  sim.getSimSerialNumber());
            ps.setString(parameterIndex++,  sim.getSubscriptionId());
            ps.setLong(parameterIndex++,    sim.getPhysicalDeviceId());
            ps.setLong(parameterIndex++,    sim.getLastSeen().getTime());
            ps.setBoolean(parameterIndex++, sim.getUsable());

            if (sim.getId() != null && sim.getId() >= 0) {

                ps.setLong(parameterIndex, sim.getId());

            }

        }

    }

    class DeviceRowMapper implements RowMapper<Device> {

        @Override
        public Device mapRow(ResultSet resultSet, int i) throws SQLException, DataAccessException {
            return new Device(
                    resultSet.getLong("id"),
                    resultSet.getString("uuid"),
                    resultSet.getString("owner"),
                    resultSet.getString("cordova"),
                    resultSet.getString("model"),
                    resultSet.getString("platform"),
                    resultSet.getString("version"),
                    resultSet.getString("manufacturer"),
                    resultSet.getBoolean("is_virtual"),
                    resultSet.getString("serial"),
                    resultSet.getString("carrier_name"),
                    resultSet.getString("country_code"),
                    resultSet.getString("mcc"),
                    resultSet.getString("mnc"),
                    IDEnum.byId(CallState.class, resultSet.getInt("call_state")),
                    IDEnum.byId(DataActivity.class, resultSet.getInt("data_activity")),
                    IDEnum.byId(NetworkType.class, resultSet.getInt("network_type")),
                    IDEnum.byId(PhoneType.class, resultSet.getInt("phone_type")),
                    IDEnum.byId(SimState.class, resultSet.getInt("sim_state")),
                    resultSet.getBoolean("is_network_roaming"),
                    resultSet.getInt("phone_count"),
                    resultSet.getInt("active_subscription_info_count"),
                    resultSet.getInt("active_subscription_info_count_max"),
                    resultSet.getString("phone_number"),
                    resultSet.getString("device_id"),
                    resultSet.getString("device_software_version"),
                    resultSet.getString("sim_serial_number"),
                    resultSet.getString("subscriber_id"),
                    new Date(resultSet.getLong("last_seen")),
                    resultSet.getBoolean("online")
            );
        }

    }

    class SimRowMapper implements RowMapper<Sim> {

        @Override
        public Sim mapRow(ResultSet resultSet, int i) throws SQLException, DataAccessException {
            return new Sim(
                    resultSet.getLong("id"),
                    resultSet.getLong("physical_device_id"),
                    resultSet.getString("carrier_name"),
                    resultSet.getString("display_name"),
                    resultSet.getString("country_code"),
                    resultSet.getString("mcc"),
                    resultSet.getString("mnc"),
                    resultSet.getBoolean("is_network_roaming"),
                    resultSet.getBoolean("is_data_roaming"),
                    resultSet.getInt("sim_slot_index"),
                    resultSet.getString("phone_number"),
                    resultSet.getString("device_id"),
                    resultSet.getString("sim_serial_number"),
                    resultSet.getString("subscription_id"),
                    new Date(resultSet.getLong("last_seen")),
                    resultSet.getBoolean("usable")
            );
        }

    }
}
