<?php
use Wavelog\QSLManager\QSO;
use Wavelog\Dxcc\Dxcc;

class Logbookadvanced_model extends CI_Model {

	public function dupeSearchQuery($searchCriteria, $binding) {
		$order_by = ' ORDER BY col_call';

		// Build dynamic PARTITION BY
		$partition_by = "COL_CALL";
		$conditions = [];

		if (isset($searchCriteria['dupemode']) && $searchCriteria['dupemode'] === 'Y') {
			$partition_by .= ", COL_MODE, COALESCE(COL_SUBMODE, '')";
		}
		if (isset($searchCriteria['dupeband']) && $searchCriteria['dupeband'] === 'Y') {
			$partition_by .= ", COL_BAND";
		}
		if (isset($searchCriteria['dupesat']) && $searchCriteria['dupesat'] === 'Y') {
			$partition_by .= ", COL_SAT_NAME";
			$conditions[] = "COL_PROP_MODE = 'SAT' AND COL_SAT_NAME <> '' AND COL_SAT_NAME IS NOT NULL";
		}
		if (isset($searchCriteria['dupedate']) && $searchCriteria['dupedate'] === 'Y') {
			$order_by = ' ORDER BY col_call, col_time_on DESC';
		}

		if (isset($searchCriteria['de']) && $searchCriteria['de'] == '') {
			$stationids = 'null';
		} else {
			// Sanitize station IDs to prevent SQL injection
			$de_array = is_array($searchCriteria['de']) ? $searchCriteria['de'] : [$searchCriteria['de']];
			$sanitized_ids = array_map('intval', $de_array);
			$sanitized_ids = array_filter($sanitized_ids, function($id) {
				return $id > 0;
			});
			if (!empty($sanitized_ids)) {
				$stationids = implode(',', $sanitized_ids);
			} else {
				$stationids = 'null';
			}
		}

		$where_conditions = "";
		if (!empty($conditions)) {
			$where_conditions = "AND " . implode(" AND ", $conditions);
		}

		// Time window for the dupedate check (seconds), adjustable via the dupe search dialog
		$dupe_time = (int)($searchCriteria['dupedateval'] ?? 0);
		if ($dupe_time <= 0) {
			$dupe_time = 1800; // default 30 minutes
		}
		$dupe_time = min($dupe_time, 86400);

		// Build the dupedate HAVING condition
		$having_condition = isset($searchCriteria['dupedate']) && $searchCriteria['dupedate'] === 'Y'
			? "AND TIMESTAMPDIFF(SECOND, prev_time_on, col_time_on) <= $dupe_time"
			: "";

		$id_sql = "
			WITH ranked AS (
				SELECT
					q.col_primary_key,
					q.COL_CALL,
					q.col_time_on,
					q.station_id,
					LAG(q.col_time_on) OVER (
						PARTITION BY $partition_by
						ORDER BY q.col_time_on
					) as prev_time_on,
					LAG(q.col_primary_key) OVER (
						PARTITION BY $partition_by
						ORDER BY q.col_time_on
					) as prev_primary_key
				FROM " . $this->config->item('table_name') . " q
				JOIN station_profile sp ON q.station_id = sp.station_id
				WHERE sp.user_id = ?
					AND q.station_id IN ($stationids)
					$where_conditions
			)
			SELECT
				col_primary_key,
				prev_primary_key
			FROM ranked
			WHERE prev_time_on IS NOT NULL
			$having_condition
		";

		$id_query = $this->db->query($id_sql, array($this->session->userdata('user_id')));

		$ids2fetch = [];
		foreach ($id_query->result() as $id) {
			$ids2fetch[] = $id->col_primary_key;
			if ($id->prev_primary_key) {
				$ids2fetch[] = $id->prev_primary_key;
			}
		}
		$ids2fetch = array_unique($ids2fetch);

		$final_conditions[] = "qsos.station_id IN ($stationids)";

		if (!empty($ids2fetch)) {
			$final_conditions[] = "qsos.COL_PRIMARY_KEY IN (" . implode(',', $ids2fetch) . ")";
		} else {
			$final_conditions[] = "1 = 0";
		}

		if (($searchCriteria['ids'] ?? '') !== '') {
			// Sanitize IDs to prevent SQL injection
			if (is_array($searchCriteria['ids'])) {
				$sanitized_ids = array_map('intval', $searchCriteria['ids']);
				$sanitized_ids = array_filter($sanitized_ids, function($id) {
					return $id > 0;
				});
				if (!empty($sanitized_ids)) {
					$final_conditions[] = "qsos.COL_PRIMARY_KEY IN (" . implode(",", $sanitized_ids) . ")";
				}
			}
		}

		$where = trim(implode(" AND ", $final_conditions));
		if ($where != "") {
			$where = "AND $where";
		}

		$limit = '';

		if ($searchCriteria['qsoresults'] != 'All') {
			// Sanitize and enforce max limit to prevent DoS
			$max_results = 10000;
			$limit_value = max(1, min($max_results, intval($searchCriteria['qsoresults'])));
			$limit = ' limit ' . $limit_value;
		}

		$sql = "
			SELECT qsos.*, qsos.last_modified AS qso_last_modified, dxcc_entities.*, lotw_users.*, station_profile.*, satellite.*, dxcc_entities.name as dxccname, mydxcc.name AS station_country, exists(select 1 from qsl_images where qsoid = qsos.COL_PRIMARY_KEY) as qslcount, coalesce(contest.name, qsos.col_contest_id) as contestname
			FROM " . $this->config->item('table_name') . " qsos
			INNER JOIN station_profile ON qsos.station_id=station_profile.station_id
			LEFT OUTER JOIN satellite ON qsos.col_prop_mode='SAT' and qsos.COL_SAT_NAME = COALESCE(NULLIF(satellite.name, ''), NULLIF(satellite.displayname, ''))
			LEFT OUTER JOIN dxcc_entities ON qsos.col_dxcc = dxcc_entities.adif
			left outer join dxcc_entities mydxcc on qsos.col_my_dxcc = mydxcc.adif
			LEFT OUTER JOIN lotw_users ON qsos.col_call = lotw_users.callsign
			LEFT OUTER JOIN contest ON qsos.col_contest_id = contest.adifname
			WHERE station_profile.user_id =  ?
			$where
			$order_by
			$limit
		";
		return $this->db->query($sql, $binding);
	}

	private function wildcardPattern($value, $default) {
		if ($value === null) {
			return '';
		}
		if (strpos($value, '*') !== false || strpos($value, '?') !== false) {
			return str_replace(['*', '?'], ['%', '_'], $value);
		}
		if ($default === 'both') {
			return '%' . $value . '%';
		}
		if ($default === 'prefix') {
			return $value . '%';
		}
		return $value;
	}

	public function searchDb($searchCriteria) {
		// Load all satellites once for PHP-side join (much faster than SQL COALESCE)
		$satellites = [];
		$sat_query = $this->db->query('SELECT name, displayname, orbit FROM satellite');
		foreach ($sat_query->result() as $sat) {
			$satellites[$sat->name] = [
				'displayname' => $sat->displayname,
				'orbit' => $sat->orbit
			];
		}

		$conditions = [];
		$binding = [$searchCriteria['user_id']];

		if (isset($searchCriteria['qsoids']) && ($searchCriteria['qsoids'] !== '')) {
			// Sanitize qsoids to prevent SQL injection
			$qsoids = $searchCriteria['qsoids'];
			if (is_array($qsoids)) {
				$sanitized_ids = array_map('intval', $qsoids);
			} else {
				// Handle comma-separated string
				$ids_array = explode(',', $qsoids);
				$sanitized_ids = array_map('intval', $ids_array);
			}
			$sanitized_ids = array_filter($sanitized_ids, function($id) {
				return $id > 0;
			});
			if (!empty($sanitized_ids)) {
				$ids2fetch = implode(',', $sanitized_ids);
				$conditions[] = "qsos.COL_PRIMARY_KEY in (".$ids2fetch.")";
			}
		}

		if ((isset($searchCriteria['dupes'])) && ($searchCriteria['dupes'] !== '')) {
			return $this->dupeSearchQuery($searchCriteria, $binding);
		}

		if ((isset($searchCriteria['invalid'])) && ($searchCriteria['invalid'] !== '')) {
			$id_sql="
				select GROUP_CONCAT(col_primary_key separator ',') as qsoids from (
					select col_primary_key from " . $this->config->item('table_name') . "
					join station_profile on " . $this->config->item('table_name') . ".station_id = station_profile.station_id
					where station_profile.user_id = ?
					and (coalesce(col_mode, '') = '' or col_mode = '0')

					union all

					select col_primary_key from " . $this->config->item('table_name') . "
					join station_profile on " . $this->config->item('table_name') . ".station_id = station_profile.station_id
					where station_profile.user_id = ?
					and coalesce(col_band, '') = ''

					union all

					select col_primary_key from " . $this->config->item('table_name') . "
					join station_profile on " . $this->config->item('table_name') . ".station_id = station_profile.station_id
					where station_profile.user_id = ?
					and coalesce(col_call, '') = ''

					union all

					select col_primary_key from " . $this->config->item('table_name') . "
					join station_profile on " . $this->config->item('table_name') . ".station_id = station_profile.station_id
					where station_profile.user_id = ?
					and (col_time_on is null or cast(col_time_on as date) = '1970-01-01')

					union all

					select col_primary_key from " . $this->config->item('table_name') . "
					join station_profile on " . $this->config->item('table_name') . ".station_id = station_profile.station_id
					where station_profile.user_id = ?
					and coalesce(col_cont, '') <> ''
					and col_cont NOT IN ('AF', 'AN', 'AS', 'EU', 'NA', 'OC', 'SA')
				) as x";

			$id_query = $this->db->query($id_sql, [$searchCriteria['user_id'], $searchCriteria['user_id'], $searchCriteria['user_id'], $searchCriteria['user_id'], $searchCriteria['user_id']]);

			$ids2fetch = '';

			foreach ($id_query->result() as $id) {
				$ids2fetch .= ','.$id->qsoids;
			}
			$ids2fetch = ltrim($ids2fetch, ',');
			if ($ids2fetch ?? '' !== '') {
				$conditions[] = "qsos.COL_PRIMARY_KEY in (".$ids2fetch.")";
			} else {
				$conditions[] = "1=0";
			}
		}

        if ($searchCriteria['dateFrom'] !== '') {
            $from = $searchCriteria['dateFrom'];
			$conditions[] = "COL_TIME_ON >= ?";
			$binding[] = $from . ' 00:00:00';
		}
        if ($searchCriteria['dateTo'] !== '') {
            $to = $searchCriteria['dateTo'];
			$conditions[] = "COL_TIME_ON <= ?";
			$binding[] = $to . ' 23:59:59';
		}
		if ($searchCriteria['de'] !== 'All' && $searchCriteria['qsoids'] === '') {
			if ($searchCriteria['de'] == '') {
				$stationids = 'null';
			} else {
				// Sanitize station IDs to prevent SQL injection
				$de_array = is_array($searchCriteria['de']) ? $searchCriteria['de'] : [$searchCriteria['de']];
				$sanitized_ids = array_map('intval', $de_array);
				$sanitized_ids = array_filter($sanitized_ids, function($id) {
					return $id > 0;
				});
				if (!empty($sanitized_ids)) {
					$stationids = implode(',', $sanitized_ids);
				} else {
					$stationids = 'null';
				}
			}
			$conditions[] = "qsos.station_id in (".$stationids.")";
		}
		if ($searchCriteria['dx'] !== '*' && $searchCriteria['dx'] !== '') {
			if (strtolower($searchCriteria['dx']) == '!empty') {
				$conditions[] = "COL_CALL <> ''";
			} else {
				$conditions[] = "COL_CALL like ?";
				$binding[] = $this->wildcardPattern(trim($searchCriteria['dx']), 'none');
			}
		}
		if ($searchCriteria['dx'] == '') {
			$conditions[] = "coalesce(COL_CALL, '') = ''";
		}

		if ($searchCriteria['mode'] !== '') {
			$conditions[] = "(COL_MODE = ? or COL_SUBMODE = ?)";
			$binding[] = $searchCriteria['mode'];
			$binding[] = $searchCriteria['mode'];
		}
		if ($searchCriteria['band'] !== '') {
			if($searchCriteria['band'] != "SAT") {
				$conditions[] = "COL_BAND = ? and (COL_PROP_MODE != 'SAT' OR COL_PROP_MODE IS NULL)";
				$binding[] = trim($searchCriteria['band']);
			} else {
				$conditions[] = "COL_PROP_MODE = 'SAT'";
				if ($searchCriteria['sats'] !== 'All') {
					$conditions[] = "COL_SAT_NAME = ?";
					$binding[] = trim($searchCriteria['sats']);
				}
			}
		}
		if ($searchCriteria['orbits'] !== 'All' && $searchCriteria['orbits'] !== '') {
			$conditions[] = "COL_SAT_NAME IN (SELECT name FROM satellite WHERE orbit = ?)";
			$binding[] = $searchCriteria['orbits'];
		}
		if ($searchCriteria['qslSent'] !== '') {
			$condition = "COL_QSL_SENT = ?";
			if ($searchCriteria['qslSent'] == 'N') {
				$condition = '('.$condition;
				$condition .= " OR COL_QSL_SENT IS NULL OR COL_QSL_SENT = '')";
			}
			$conditions[] = $condition;
			$binding[] = $searchCriteria['qslSent'];
		}
		if ($searchCriteria['qslReceived'] !== '') {
			$condition = "COL_QSL_RCVD = ?";
			if ($searchCriteria['qslReceived'] == 'N') {
				$condition = '('.$condition;
				$condition .= " OR COL_QSL_RCVD IS NULL OR COL_QSL_RCVD = '')";
			}
			$conditions[] = $condition;
			$binding[] = $searchCriteria['qslReceived'];
		}

		if ($searchCriteria['qslSentMethod'] !== '') {
			$condition = "COL_QSL_SENT_VIA = ?";
			$conditions[] = $condition;
			$binding[] = $searchCriteria['qslSentMethod'];
		}

		if ($searchCriteria['qslReceivedMethod'] !== '') {
			$condition = "COL_QSL_RCVD_VIA = ?";
			$conditions[] = $condition;
			$binding[] = $searchCriteria['qslReceivedMethod'];
		}

		if ($searchCriteria['lotwSent'] !== '') {
			$condition = "COL_LOTW_QSL_SENT = ?";
			if ($searchCriteria['lotwSent'] == 'N') {
				$condition = '('.$condition;
				$condition .= " OR COL_LOTW_QSL_SENT IS NULL OR COL_LOTW_QSL_SENT = '')";
			}
			$conditions[] = $condition;
			$binding[] = $searchCriteria['lotwSent'];
		}
		if ($searchCriteria['lotwReceived'] !== '') {
			$condition = "COL_LOTW_QSL_RCVD = ?";
			if ($searchCriteria['lotwReceived'] == 'N') {
				$condition = '('.$condition;
				$condition .= " OR COL_LOTW_QSL_RCVD IS NULL OR COL_LOTW_QSL_RCVD = '')";
			}
			$conditions[] = $condition;
			$binding[] = $searchCriteria['lotwReceived'];
		}

		if ($searchCriteria['clublogSent'] !== '') {
			$condition = "COL_CLUBLOG_QSO_UPLOAD_STATUS = ?";
			if ($searchCriteria['clublogSent'] == 'N') {
				$condition = '('.$condition;
				$condition .= " OR COL_CLUBLOG_QSO_UPLOAD_STATUS IS NULL OR COL_CLUBLOG_QSO_UPLOAD_STATUS = '')";
			}
			$conditions[] = $condition;
			$binding[] = $searchCriteria['clublogSent'];
		}
		if ($searchCriteria['clublogReceived'] !== '') {
			$condition = "COL_CLUBLOG_QSO_DOWNLOAD_STATUS = ?";
			if ($searchCriteria['clublogReceived'] == 'N') {
				$condition = '('.$condition;
				$condition .= " OR COL_CLUBLOG_QSO_DOWNLOAD_STATUS IS NULL OR COL_CLUBLOG_QSO_DOWNLOAD_STATUS = '')";
			}
			$conditions[] = $condition;
			$binding[] = $searchCriteria['clublogReceived'];
		}
		if ($searchCriteria['eqslSent'] !== '') {
			$condition = "COL_EQSL_QSL_SENT = ?";
			if ($searchCriteria['eqslSent'] == 'N') {
				$condition = '('.$condition;
				$condition .= " OR COL_EQSL_QSL_SENT IS NULL OR COL_EQSL_QSL_SENT = '')";
			}
			$conditions[] = $condition;
			$binding[] = $searchCriteria['eqslSent'];
		}
		if ($searchCriteria['eqslReceived'] !== '') {
			$condition = "COL_EQSL_QSL_RCVD = ?";
			if ($searchCriteria['eqslReceived'] == 'N') {
				$condition = '('.$condition;
				$condition .= " OR COL_EQSL_QSL_RCVD IS NULL OR COL_EQSL_QSL_RCVD = '')";
			}
			$conditions[] = $condition;
			$binding[] = $searchCriteria['eqslReceived'];
		}
		if ($searchCriteria['dclSent'] !== '') {
			$condition = "COL_DCL_QSL_SENT = ?";
			if ($searchCriteria['dclSent'] == 'N') {
				$condition = '('.$condition;
				$condition .= " OR COL_DCL_QSL_SENT IS NULL OR COL_DCL_QSL_SENT = '')";
			}
			$conditions[] = $condition;
			$binding[] = $searchCriteria['dclSent'];
		}
		if ($searchCriteria['dclReceived'] !== '') {
			$condition = "COL_DCL_QSL_RCVD = ?";
			if ($searchCriteria['dclReceived'] == 'N') {
				$condition = '('.$condition;
				$condition .= " OR COL_DCL_QSL_RCVD IS NULL OR COL_DCL_QSL_RCVD = '')";
			}
			$conditions[] = $condition;
			$binding[] = $searchCriteria['dclReceived'];
		}

		if ($searchCriteria['qrzSent'] !== '') {
			$condition = "COL_QRZCOM_QSO_UPLOAD_STATUS = ?";
			if ($searchCriteria['qrzSent'] == 'N') {
				$condition = '('.$condition;
				$condition .= " OR COL_QRZCOM_QSO_UPLOAD_STATUS IS NULL OR COL_QRZCOM_QSO_UPLOAD_STATUS = '')";
			}
			$conditions[] = $condition;
			$binding[] = $searchCriteria['qrzSent'];
		}
		if ($searchCriteria['qrzReceived'] !== '') {
			$condition = "COL_QRZCOM_QSO_DOWNLOAD_STATUS = ?";
			if ($searchCriteria['qrzReceived'] == 'N') {
				$condition = '('.$condition;
				$condition .= " OR COL_QRZCOM_QSO_DOWNLOAD_STATUS IS NULL OR COL_QRZCOM_QSO_DOWNLOAD_STATUS = '')";
			}
			$conditions[] = $condition;
			$binding[] = $searchCriteria['qrzReceived'];
		}

        if ($searchCriteria['iota'] !== '') {
			$conditions[] = "COL_IOTA = ?";
			$binding[] = $searchCriteria['iota'];
		}

        if (($searchCriteria['dxcc'] ?? '') !== '') {
			$conditions[] = "COL_DXCC = ?";
			$binding[] = $searchCriteria['dxcc'];
		}

        if ($searchCriteria['state'] !== '*' && $searchCriteria['state'] !== '') {
			if (strtolower($searchCriteria['state']) == '!empty') {
				$conditions[] = "COL_STATE <> ''";
			} else {
				$conditions[] = "COL_STATE like ?";
				$binding[] = $this->wildcardPattern($searchCriteria['state'], 'none');
			}
		}
		if ($searchCriteria['state'] == '') {
			$conditions[] = "coalesce(COL_STATE, '') = ''";
		}

		if ($searchCriteria['dok'] !== '*' && $searchCriteria['dok'] !== '') {
			if (strtolower($searchCriteria['dok']) == '!empty') {
				$conditions[] = "COL_DARC_DOK <> ''";
			} else {
				$conditions[] = "COL_DARC_DOK like ?";
				$binding[] = $this->wildcardPattern($searchCriteria['dok'], 'none');
			}
		}
		if ($searchCriteria['dok'] == '') {
			$conditions[] = "coalesce(COL_DARC_DOK, '') = ''";
		}

		if ($searchCriteria['county'] !== '*' && $searchCriteria['county'] !== '') {
			if (strtolower($searchCriteria['county']) == '!empty') {
				$conditions[] = "COL_CNTY <> ''";
			} else {
				$conditions[] = "COL_CNTY like ?";
				$binding[] = $this->wildcardPattern($searchCriteria['county'], 'both');
			}
		}
		if ($searchCriteria['county'] == '') {
			$conditions[] = "coalesce(COL_CNTY, '') = ''";
		}

		if ($searchCriteria['cqzone'] !== 'All') {
			if ($searchCriteria['cqzone'] == '') {
				$conditions[] = "(COL_CQZ = '' or COL_CQZ is null)";
			} else {
				$conditions[] = "COL_CQZ = ?";
				$binding[] = $searchCriteria['cqzone'];
			}
		}

		if ($searchCriteria['ituzone'] !== 'All') {
			if ($searchCriteria['ituzone'] == '') {
				$conditions[] = "(COL_ITUZ = '' or COL_ITUZ is null)";
			} else {
				$conditions[] = "COL_ITUZ = ?";
				$binding[] = $searchCriteria['ituzone'];
			}
		}

		if ($searchCriteria['qslvia'] !== '*' && $searchCriteria['qslvia'] !== '') {
			if (strtolower($searchCriteria['qslvia']) == '!empty') {
				$conditions[] = "COL_QSL_VIA <> ''";
			} else {
				$conditions[] = "COL_QSL_VIA like ?";
				$binding[] = $this->wildcardPattern($searchCriteria['qslvia'], 'prefix');
			}
		}

		if ($searchCriteria['qslvia'] == '') {
			$conditions[] = "coalesce(COL_QSL_VIA, '') = ''";
		}

		if ($searchCriteria['sota'] !== '*' && $searchCriteria['sota'] !== '') {
			if (strtolower($searchCriteria['sota']) == '!empty') {
				$conditions[] = "COL_SOTA_REF <> ''";
			} else {
				$conditions[] = "COL_SOTA_REF like ?";
				$binding[] = $this->wildcardPattern($searchCriteria['sota'], 'prefix');
			}
		}
		if ($searchCriteria['sota'] == '') {
			$conditions[] = "coalesce(COL_SOTA_REF, '') = ''";
		}


		if ($searchCriteria['comment'] !== '*' && $searchCriteria['comment'] !== '') {
			if (strtolower($searchCriteria['comment']) == '!empty') {
				$conditions[] = "COL_COMMENT <> ''";
			} else {
				$conditions[] = "COL_COMMENT like ?";
				$binding[] = $this->wildcardPattern($searchCriteria['comment'], 'both');
			}
		}
		if ($searchCriteria['comment'] == '') {
			$conditions[] = "coalesce(COL_COMMENT, '') = ''";
		}

		if ($searchCriteria['pota'] !== '*' && $searchCriteria['pota'] !== '') {
			if (strtolower($searchCriteria['pota']) == '!empty') {
				$conditions[] = "COL_POTA_REF <> ''";
			} else {
				$conditions[] = "COL_POTA_REF like ?";
				$binding[] = $this->wildcardPattern($searchCriteria['pota'], 'prefix');
			}
		}
		if ($searchCriteria['pota'] == '') {
			$conditions[] = "coalesce(COL_POTA_REF, '') = ''";
		}

		if ($searchCriteria['wwff'] !== '*' && $searchCriteria['wwff'] !== '') {
			if (strtolower($searchCriteria['wwff']) == '!empty') {
				$conditions[] = "COL_WWFF_REF <> ''";
			} else {
				$conditions[] = "COL_WWFF_REF like ?";
				$binding[] = $this->wildcardPattern($searchCriteria['wwff'], 'prefix');
			}
		}
		if ($searchCriteria['wwff'] == '') {
			$conditions[] = "coalesce(COL_WWFF_REF, '') = ''";
		}

		if ($searchCriteria['operator'] !== '*' && $searchCriteria['operator'] !== '') {
			if (strtolower($searchCriteria['operator']) == '!empty') {
				$conditions[] = "COL_OPERATOR <> ''";
			} else {
				$conditions[] = "COL_OPERATOR like ?";
				$binding[] = $this->wildcardPattern($searchCriteria['operator'], 'prefix');
			}
		}
		if ($searchCriteria['operator'] == '') {
			$conditions[] = "coalesce(COL_OPERATOR, '') = ''";
		}

        if ($searchCriteria['gridsquare'] !== '*' && $searchCriteria['gridsquare'] !== '') {
			if (strtolower($searchCriteria['gridsquare']) == '!empty') {
				$conditions[] = "(COL_GRIDSQUARE <> '' or COL_VUCC_GRIDS <> '')";
			} else {
				$gridPattern = $this->wildcardPattern($searchCriteria['gridsquare'], 'both');
				$conditions[] = "(COL_GRIDSQUARE like ? or COL_VUCC_GRIDS like ?)";
				$binding[] = $gridPattern;
				$binding[] = $gridPattern;
			}
        }

		if ($searchCriteria['gridsquare'] == '') {
			$conditions[] = "(coalesce(COL_GRIDSQUARE, '') = '' and coalesce(COL_VUCC_GRIDS, '') = '')";
		}


		if ($searchCriteria['distance'] !== '*' && $searchCriteria['distance'] !== '') {
			if (strtolower($searchCriteria['distance']) == '!empty') {
				$conditions[] = "COL_DISTANCE <> ''";
			} else {
				$conditions[] = "COL_DISTANCE >= ?";
				$binding[] = $searchCriteria['distance'];
			}
        }

		if ($searchCriteria['distance'] == '') {
			$conditions[] = "coalesce(COL_DISTANCE, '') = ''";
		}

		if ($searchCriteria['duration'] !== '*' && $searchCriteria['duration'] !== '') {
			$conditions[] = "TIMESTAMPDIFF(MINUTE, COL_TIME_ON, COL_TIME_OFF) >= ?";
			$binding[] = $searchCriteria['duration'];
        }

		if (($searchCriteria['propmode'] ?? '') == 'None') {
			$conditions[] = "(trim(COL_PROP_MODE) = '' OR COL_PROP_MODE is null)";
		} elseif ($searchCriteria['propmode'] !== '') {
			$conditions[] = "COL_PROP_MODE = ?";
			$binding[] = $searchCriteria['propmode'];
			if($searchCriteria['propmode'] == "SAT") {
				if ($searchCriteria['sats'] !== 'All') {
					$conditions[] = "COL_SAT_NAME = ?";
					$binding[] = trim($searchCriteria['sats']);
				}
			}
		}

		if ($searchCriteria['contest'] !== '*' && $searchCriteria['contest'] !== '') {
			if (strtolower($searchCriteria['contest']) == '!empty') {
				$conditions[] = "(COL_CONTEST_ID <> '' OR contest.name <> '')";
			} else {
				$contestPattern = $this->wildcardPattern($searchCriteria['contest'], 'both');
				$conditions[] = "(COL_CONTEST_ID <> '' OR contest.name <> '')";$conditions[] = "(COL_CONTEST_ID like ? OR contest.name like ?)";
				$binding[] = $contestPattern;
				$binding[] = $contestPattern;
			}
		}

		if ($searchCriteria['contest'] == '') {
			$conditions[] = "coalesce(COL_CONTEST_ID, '') = ''";
		}

		if ($searchCriteria['continent'] !== '') {
			if ($searchCriteria['continent'] == 'invalid') {
				$conditions[] = "COL_CONT NOT IN ('AF', 'AN', 'AS', 'EU', 'NA', 'OC', 'SA')";
				$conditions[] = "coalesce(COL_CONT, '') <> ''";
			} else if ($searchCriteria['continent'] == 'blank') {
				$conditions[] = "coalesce(COL_CONT, '') = ''";
			}
			else {
				$conditions[] = "COL_CONT = ?";
				$binding[] = $searchCriteria['continent'];
			}
		}

		if (($searchCriteria['ids'] ?? '') !== '') {
			// Sanitize IDs to prevent SQL injection
			if (is_array($searchCriteria['ids'])) {
				$sanitized_ids = array_map('intval', $searchCriteria['ids']);
				$sanitized_ids = array_filter($sanitized_ids, function($id) {
					return $id > 0;
				});
				if (!empty($sanitized_ids)) {
					$conditions[] = "qsos.COL_PRIMARY_KEY in (".implode(",",$sanitized_ids).")";
				}
			}
		}

		$where = trim(implode(" AND ", $conditions));
		if ($where != "") {
			$where = "AND $where";
		}

		$limit = '';

		if ($searchCriteria['qsoresults'] != 'All') {
			// Sanitize and enforce max limit to prevent DoS
			$max_results = 10000;
			$limit_value = max(1, min($max_results, intval($searchCriteria['qsoresults'])));
			$limit = ' limit ' . $limit_value;
		}

		$where2 = '';

		if ($searchCriteria['qslimages'] !== '') {
			if ($searchCriteria['qslimages'] == 'Y') {
				$where2 .= ' and exists(select 1 from qsl_images where qsoid = qsos.COL_PRIMARY_KEY)';
			}
			if ($searchCriteria['qslimages'] == 'N') {
				$where2 .= ' and not exists(select 1 from qsl_images where qsoid = qsos.COL_PRIMARY_KEY)';
			}
		}

		$sortorder = '';

		$sortColumn = '';
		$sortDirection = isset($searchCriteria['sortdirection']) && strtolower($searchCriteria['sortdirection']) === 'asc' ? 'asc' : 'desc';

		if ($searchCriteria['sortcolumn'] !== '') {
			switch($searchCriteria['sortcolumn']) {
				case 'qsotime':
					$sortColumn = 'qsos.COL_TIME_ON';
					break;
				case 'band':
					$sortColumn = 'qsos.COL_BAND';
					break;
				case 'mode':
					$sortColumn = 'qsos.COL_MODE';
					break;
				case 'qsomodified':
					$sortColumn = 'qsos.last_modified';
					break;
				default:
					$sortColumn = 'qsos.COL_TIME_ON';
			}

			$secondarySort = $sortDirection === 'asc' ? 'asc' : 'desc';
			$sortorder .= " ORDER BY $sortColumn $sortDirection";

			// Add secondary sorts for mode column
			if ($searchCriteria['sortdirection'] === 'mode') {
				$sortorder .= ", qsos.COL_SUBMODE $sortDirection";
			}

			$sortorder .= ", qsos.COL_PRIMARY_KEY $secondarySort";
		}

		$sql = "
			SELECT qsos.*, qsos.last_modified AS qso_last_modified, dxcc_entities.*, lotw_users.*, station_profile.*, dxcc_entities.name as dxccname, mydxcc.name AS station_country, exists(select 1 from qsl_images where qsoid = qsos.COL_PRIMARY_KEY) as qslcount, coalesce(contest.name, qsos.col_contest_id) as contestname
			FROM " . $this->config->item('table_name') . " qsos
			INNER JOIN station_profile ON qsos.station_id=station_profile.station_id
			LEFT OUTER JOIN dxcc_entities ON qsos.col_dxcc = dxcc_entities.adif
			left outer join dxcc_entities mydxcc on qsos.col_my_dxcc = mydxcc.adif
			LEFT OUTER JOIN lotw_users ON qsos.col_call = lotw_users.callsign
			LEFT OUTER JOIN contest ON qsos.col_contest_id = contest.adifname
			WHERE station_profile.user_id =  ?
			$where
			$where2
			$sortorder
			$limit
		";
		$query = $this->db->query($sql, $binding);

		// Add satellite data via PHP-side join (much faster than SQL COALESCE)
		$results = $query->result();
		foreach ($results as &$row) {
			$row->sat_name = $row->COL_SAT_NAME ?? null;
			$row->sat_displayname = null;
			$row->orbit = null;
			if (!empty($row->COL_SAT_NAME) && isset($satellites[$row->COL_SAT_NAME])) {
				$row->sat_displayname = $satellites[$row->COL_SAT_NAME]['displayname'];
				$row->orbit = $satellites[$row->COL_SAT_NAME]['orbit'];
			}
		}
		unset($row);

		// Return a query-like object with result() method for compatibility
		return new class($results) {
			private $data;

			public function __construct($data) {
				$this->data = $data;
			}

			public function result($type = 'object') {
				if ($type === 'array') {
					return json_decode(json_encode($this->data), true);
				}
				return $this->data;
			}

			public function num_rows() {
				return count($this->data);
			}

			public function row() {
				return !empty($this->data) ? $this->data[0] : null;
			}
		};

	}

	public function getSearchResult($searchCriteria) {
		return $this->searchDb($searchCriteria);
	}

	public function getSearchResultArray($searchCriteria) {
		$result = $this->searchDb($searchCriteria);
		return $result->result('array');
	}

  /*
   * @param array $searchCriteria
   * @return array
   */
  public function searchQsos($searchCriteria) : array {
		$results = $this->getSearchResultArray($searchCriteria);

        $qsos = [];
        foreach ($results as $data) {
            $qsos[] = new QSO($data);
        }

		return $qsos;
	}

    public function getQsosForAdif($ids, $user_id, $sortColumnVar = 'qsotime', $sortDirection = 'desc') : object {
		$binding = [$user_id];
        $conditions[] = "COL_PRIMARY_KEY in ?";
        $binding[] = json_decode($ids, true);

		$where = trim(implode(" AND ", $conditions));
		if ($where != "") {
			$where = "AND $where";
		}

		$sortorder = '';

		$sortColumnVar = '';
		$sortDirection = $sortDirection === 'asc' ? 'asc' : 'desc';

		if ($sortColumnVar !== '') {
			switch($sortColumnVar) {
				case 'qsotime':
					$sortColumn = 'qsos.COL_TIME_ON';
					break;
				case 'band':
					$sortColumn = 'qsos.COL_BAND';
					break;
				case 'mode':
					$sortColumn = 'qsos.COL_MODE';
					break;
				case 'qsomodified':
					$sortColumn = 'qsos.last_modified';
					break;
				default:
					$sortColumn = 'qsos.COL_TIME_ON';
			}

			$secondarySort = $sortDirection === 'asc' ? 'asc' : 'desc';
			$sortorder .= " ORDER BY $sortColumn $sortDirection";

			// Add secondary sorts for mode column
			if ($sortDirection === 'mode') {
				$sortorder .= ", qsos.COL_SUBMODE $sortDirection";
			}

			$sortorder .= ", qsos.COL_PRIMARY_KEY $secondarySort";
		}

		$sql = "
			SELECT qsos.*, qsos.last_modified AS qso_last_modified, lotw_users.*, station_profile.*, dxcc_entities.name AS station_country, d2.adif as adif, d2.name as dxccname, exists(select 1 from qsl_images where qsoid = qsos.COL_PRIMARY_KEY) as qslcount, coalesce(contest.name, qsos.col_contest_id) as contestname
			FROM " . $this->config->item('table_name') . " qsos
			INNER JOIN station_profile ON qsos.station_id = station_profile.station_id
			LEFT OUTER JOIN dxcc_entities ON qsos.COL_MY_DXCC = dxcc_entities.adif
			LEFT OUTER JOIN dxcc_entities d2 ON qsos.COL_DXCC = d2.adif
			LEFT OUTER JOIN lotw_users ON qsos.col_call=lotw_users.callsign
			LEFT OUTER JOIN contest ON qsos.col_contest_id = contest.adifname
			WHERE station_profile.user_id =  ?
			$where
			$sortorder
		";

		return $this->db->query($sql, $binding);
    }

	public function updateQsl($ids, $user_id, $method, $sent) {

		if(!$this->user_model->authorize(2)) {
			return array('message' => 'Error');
		} else {
			// Sanitize IDs to prevent SQL injection
			$ids_array = json_decode($ids, true);
			if (is_array($ids_array)) {
				$sanitized_ids = array_map('intval', $ids_array);
				$sanitized_ids = array_filter($sanitized_ids, function($id) {
					return $id > 0;
				});
			} else {
				$sanitized_ids = [];
			}

			if (empty($sanitized_ids)) {
				return array('message' => 'Error');
			}

			$sql = "UPDATE " . $this->config->item('table_name') ."
				SET
				COL_QSLSDATE = CURRENT_TIMESTAMP,
				COL_QSL_SENT = ?,
				COL_QSL_SENT_VIA = COALESCE(
					NULLIF(?, ''),
					NULLIF(COL_QSL_SENT_VIA, ''),
					'B'
				),
				COL_CLUBLOG_QSO_UPLOAD_STATUS = CASE
					WHEN COL_CLUBLOG_QSO_UPLOAD_STATUS IN ('Y', 'I') THEN 'M'
					ELSE COL_CLUBLOG_QSO_UPLOAD_STATUS
				END,
				COL_QRZCOM_QSO_UPLOAD_STATUS = CASE
					WHEN COL_QRZCOM_QSO_UPLOAD_STATUS IN ('Y', 'I') THEN 'M'
					ELSE COL_QRZCOM_QSO_UPLOAD_STATUS
				END
				WHERE COL_PRIMARY_KEY IN (".implode(',', $sanitized_ids).")";
			$binding[] = $sent;
			$binding[] = $method ?? '';
			$this->db->query($sql, $binding);

			return array('message' => 'OK');
		}
	}

	public function updateQslReceived($ids, $user_id, $method, $sent) {

		if(!$this->user_model->authorize(2)) {
			return array('message' => 'Error');
		} else {
			// Sanitize IDs to prevent SQL injection
			$ids_array = json_decode($ids, true);
			if (is_array($ids_array)) {
				$sanitized_ids = array_map('intval', $ids_array);
				$sanitized_ids = array_filter($sanitized_ids, function($id) {
					return $id > 0;
				});
			} else {
				$sanitized_ids = [];
			}

			if (empty($sanitized_ids)) {
				return array('message' => 'Error');
			}

			$sql = "UPDATE " . $this->config->item('table_name') ."
				SET
				COL_QSLRDATE = CURRENT_TIMESTAMP,
				COL_QSL_RCVD = ?,
				COL_QSL_RCVD_VIA = ?,
				COL_CLUBLOG_QSO_UPLOAD_STATUS = CASE
				WHEN COL_CLUBLOG_QSO_UPLOAD_STATUS IN ('Y', 'I') THEN 'M'
				ELSE COL_CLUBLOG_QSO_UPLOAD_STATUS
				END,
				COL_QRZCOM_QSO_UPLOAD_STATUS = CASE
				WHEN COL_QRZCOM_QSO_UPLOAD_STATUS IN ('Y', 'I') THEN 'M'
				ELSE COL_QRZCOM_QSO_UPLOAD_STATUS
				END
				WHERE COL_PRIMARY_KEY IN (".implode(',', $sanitized_ids).")";
			$binding[] = $sent;
			$binding[] = $method;
			$this->db->query($sql, $binding);
			return array('message' => 'OK');
		}
	}

	public function updateQsoWithCallbookInfo($qsoID, $qso, $callbook, $gridsquareAccuracyCheck, $station_gridsquare = null) {
		$updatedData = array();
		$updated = false;
		if (!empty($callbook['name']) && empty($qso['COL_NAME'])) {
			$updatedData['COL_NAME'] = $callbook['name'];
			$updated = true;
		}
		if (!empty($callbook['gridsquare']) && $callbook['geoloc'] != 'grid') {
			if (empty($qso['COL_GRIDSQUARE']) && empty($qso['COL_VUCC_GRIDS'] )) {
				if (strpos(trim($callbook['gridsquare']), ',') === false) {
					$updatedData['COL_GRIDSQUARE'] = strtoupper(trim($callbook['gridsquare']));
					if ($station_gridsquare != null && $station_gridsquare != '') {
						if (!$this->load->is_loaded('Qra')) {
							$this->load->library('Qra');
						}
						$updatedData['COL_DISTANCE'] = $this->qra->distance($station_gridsquare, strtoupper(trim($callbook['gridsquare'])), 'K');
					}
				} else {
					$updatedData['COL_VUCC_GRIDS'] = strtoupper(trim($callbook['gridsquare']));
					if ($station_gridsquare != null && $station_gridsquare != '') {
						if (!$this->load->is_loaded('Qra')) {
							$this->load->library('Qra');
						}
						$updatedData['COL_DISTANCE'] = $this->qra->distance($station_gridsquare, strtoupper(trim($callbook['gridsquare'])), 'K');
					}
				}
				$updated = true;
			} else if (!empty($qso['COL_GRIDSQUARE']) && $gridsquareAccuracyCheck == true) {
				$existingGridsquare = $qso['COL_GRIDSQUARE'];
				//Check if existing gridsquare is less accurate than callbook gridsquare
				if (strlen(trim($callbook['gridsquare'])) > strlen(trim($existingGridsquare))) {
					if ($existingGridsquare == substr($callbook['gridsquare'], 0, strlen($existingGridsquare))) {
						//Callbook gridsquare is more accurate, update it
						$updatedData['COL_GRIDSQUARE'] = strtoupper(trim($callbook['gridsquare']));
						if ($station_gridsquare != null && $station_gridsquare != '') {
							if (!$this->load->is_loaded('Qra')) {
								$this->load->library('Qra');
							}
							$updatedData['COL_DISTANCE'] = $this->qra->distance($station_gridsquare, strtoupper(trim($callbook['gridsquare'])), 'K');
						}
						$updated = true;
					}
				}
			}
		}
		if (!empty($callbook['city']) && empty($qso['COL_QTH'])) {
			$updatedData['COL_QTH'] = $callbook['city'];
			$updated = true;
		}
		if (!empty($callbook['lat']) && empty($qso['COL_LAT'])) {
			$updatedData['COL_LAT'] = substr(($callbook['lat'] ?? ''),0,11);
			$updated = true;
		}
		if (!empty($callbook['long']) && empty($qso['COL_LON'])) {
			$updatedData['COL_LON'] = substr(($callbook['long'] ?? ''),0,11);
			$updated = true;
		}
		if (!empty($callbook['iota']) && empty($qso['COL_IOTA'])) {
			$updatedData['COL_IOTA'] = $callbook['iota'];
			$updated = true;
		}
		if (!empty($callbook['state']) && empty($qso['COL_STATE'])) {
			$updatedData['COL_STATE'] = $callbook['state'];
			$updated = true;
		}
		if (!empty($callbook['us_county']) && empty($qso['COL_CNTY'])) {
			$updatedData['COL_CNTY'] = $callbook['state'].','.$callbook['us_county'];
			$updated = true;
		}
		if (!empty($callbook['county']) && empty($qso['COL_CNTY'])) {
			$updatedData['COL_CNTY'] = $callbook['county'];
			$updated = true;
		}
		if (!empty($callbook['qslmgr']) && empty($qso['COL_QSL_VIA'])) {
			$updatedData['COL_QSL_VIA'] = $callbook['qslmgr'];
			$updated = true;
		}
		if (!empty($callbook['ituz']) && empty($qso['COL_ITUZ'])) {
			$updatedData['COL_ITUZ'] = $callbook['ituz'];
			$updated = true;
		}
		if (!empty($callbook['cqz']) && empty($qso['COL_CQZ'])) {
			$updatedData['COL_CQZ'] = $callbook['cqz'];
			$updated = true;
		}
		if (!empty($callbook['darc_dok']) && empty($qso['COL_DARC_DOK'])) {
			$updatedData['COL_DARC_DOK'] = strtoupper($callbook['darc_dok']);
			$updated = true;
		}
		if (empty($qso['COL_CONT'])) {
			$updatedData['COL_CONT'] = $this->logbook_model->getContinent($callbook['dxcc']);
			$updated = true;
		}
		if (!empty($callbook['email']) && empty($qso['COL_EMAIL'])) {
			$updatedData['COL_EMAIL'] = $callbook['email'];
			$updated = true;
		}

		//Also set QRZ.com status to modified
		if($updated == true && $qso['COL_QRZCOM_QSO_UPLOAD_STATUS'] == 'Y') {
			$updatedData['COL_QRZCOM_QSO_UPLOAD_STATUS'] = 'M';
		}

		if (count($updatedData) > 0) {
			$this->db->where('COL_PRIMARY_KEY', $qsoID);
			$this->db->update($this->config->item('table_name'), $updatedData);
			return true;
		}

		return false;
    }

	function get_modes() {

		$modes = array();

		$sql = "SELECT distinct col_mode, col_submode FROM " . $this->config->item('table_name') . " thcv
		JOIN station_profile on thcv.station_id = station_profile.station_id WHERE station_profile.user_id = ? ORDER BY col_mode, col_submode";

		$query = $this->db->query($sql, array($this->session->userdata('user_id')));

		foreach($query->result() as $mode){
			if ($mode->col_submode == null || $mode->col_submode == "") {
				$newMode = $mode->col_mode;
			} else {
				$newMode = $mode->col_submode;
			}

			// Skip rows where both mode and submode are empty/null
			if ($newMode == null || $newMode == "") {
				continue;
			}

			// Case-insensitive check if mode already exists
			if (!in_array(strtolower($newMode), array_map('strtolower', $modes))) {
				array_push($modes, $newMode);
			}
		}

		return $modes;
	}

	function get_worked_bands() {
		// get all worked slots from database
		$sql = "SELECT distinct `COL_BAND` as `COL_BAND` FROM `".$this->config->item('table_name')."` thcv
			JOIN station_profile on thcv.station_id = station_profile.station_id WHERE station_profile.user_id = ? AND (COL_PROP_MODE != \"SAT\" OR COL_PROP_MODE IS NULL) ORDER BY col_band";

		$data = $this->db->query($sql, array($this->session->userdata('user_id')));

		$worked_slots = array();
		foreach($data->result() as $row){
			array_push($worked_slots, strtolower($row->COL_BAND));
		}

		$sql = "SELECT count(*) as count FROM `".$this->config->item('table_name')."` thcv
			JOIN station_profile on thcv.station_id = station_profile.station_id WHERE station_profile.user_id = ? AND COL_PROP_MODE = \"SAT\"";

		$SAT_data = $this->db->query($sql, array($this->session->userdata('user_id')));

		foreach($SAT_data->result() as $row){
			array_push($worked_slots, 'SAT');
		}

		usort(
			$worked_slots,
			function($b, $a) {
				sscanf($a, '%f%s', $ac, $ar);
				sscanf($b, '%f%s', $bc, $br);
				if ($ar == $br) {
					return ($ac < $bc) ? -1 : 1;
				}
				return ($ar < $br) ? -1 : 1;
			}
		);

		return $worked_slots;
	}

	function get_worked_sats() {
		// get all worked sats from database
		$sql = "SELECT distinct col_sat_name FROM ".$this->config->item('table_name')." thcv
		JOIN station_profile on thcv.station_id = station_profile.station_id WHERE station_profile.user_id = ? AND col_sat_name IS NOT NULL AND col_sat_name != '' ORDER BY col_sat_name";

		$data = $this->db->query($sql, array($this->session->userdata('user_id')));

		$worked_sats = array();
		foreach($data->result() as $row){
			array_push($worked_sats, $row->col_sat_name);
		}

		return $worked_sats;
	}

	function getQslsForQsoIds($ids) {
        $this->db->select('*');
		$this->db->from($this->config->item('table_name'));
        $this->db->join('qsl_images', 'qsl_images.qsoid = ' . $this->config->item('table_name') . '.col_primary_key');
		$this->db->join('station_profile', 'station_profile.station_id = '.$this->config->item('table_name').'.station_id');
		$this->db->where('station_profile.user_id', $this->session->userdata('user_id'));
        $this->db->where_in('qsoid', $ids);
        $this->db->order_by("id", "desc");

        return $this->db->get()->result();
    }

	function saveEditedQsos($ids, $column, $value, $value2, $value3, $value4) {
		$skipqrzupdate = false;
		switch($column) {
			case "cqz": $column = 'COL_CQZ'; break;
			case "ituz": $column = 'COL_ITUZ'; break;
			case "dxcc": $column = 'COL_DXCC'; break;
			case "iota": $column = 'COL_IOTA'; break;
			case "state": $column = 'COL_STATE'; break;
			case "propagation": $column = 'COL_PROP_MODE'; break;
			case "station": $column = 'station_id'; break;
			case "operator": $column = 'COL_OPERATOR'; break;
			case "comment": $column = 'COL_COMMENT'; break;
			case "band": $column = 'COL_BAND'; break;
			case "mode": $column = 'COL_MODE'; break;
			case "date": $column = 'COL_TIME_ON'; break;
			case "pota": $column = 'COL_POTA_REF'; break;
			case "sota": $column = 'COL_SOTA_REF'; break;
			case "wwff": $column = 'COL_WWFF_REF'; break;
			case "sig": $column = 'COL_SIG'; break;
			case "gridsquare": $column = 'COL_GRIDSQUARE'; break;
			case "qslvia": $column = 'COL_QSL_VIA'; break;
			case "satellite": $column = 'COL_SAT_NAME'; break;
			case "contest": $column = 'COL_CONTEST_ID'; break;
			case "lotwsent": $column = 'COL_LOTW_QSL_SENT'; break;
			case "lotwreceived": $column = 'COL_LOTW_QSL_RCVD'; break;
			case "qslmsg": $column = 'COL_QSLMSG'; break;
			case "continent": $column = 'COL_CONT'; break;
			case "qslsent": $column = 'COL_QSL_SENT'; break;
			case "qslreceived": $column = 'COL_QSL_RCVD'; break;
			case "qrzsent": $column = 'COL_QRZCOM_QSO_UPLOAD_STATUS'; break;
			case "qrzreceived": $column = 'COL_QRZCOM_QSO_DOWNLOAD_STATUS'; break;
			case "eqslsent": $column = 'COL_EQSL_QSL_SENT'; break;
			case "eqslreceived": $column = 'COL_EQSL_QSL_RCVD'; break;
			case "dclsent": $column = 'COL_DCL_QSL_SENT'; break;
			case "dclreceived": $column = 'COL_DCL_QSL_RCVD'; break;
			case "stationpower": $column = 'COL_TX_PWR'; break;
			case "clublogsent": $column = 'COL_CLUBLOG_QSO_UPLOAD_STATUS'; break;
			case "clublogreceived": $column = 'COL_CLUBLOG_QSO_DOWNLOAD_STATUS'; break;
			case "region": $column = 'COL_REGION'; break;
			case "distance": $column = 'COL_DISTANCE'; break;
			case "dok": $column = 'COL_DARC_DOK'; break;
			case "stxstring": $column = 'COL_STX_STRING'; break;
			case "rstr": $column = 'COL_RST_RCVD'; break;
			case "rsts": $column = 'COL_RST_SENT'; break;
			case "qslsentmethod": $column = 'COL_QSL_SENT_VIA'; break;
			case "qslreceivedmethod": $column = 'COL_QSL_RCVD_VIA'; break;
			case "frequency": $column = 'COL_FREQUENCY'; break;
			default: return;
		}

		$this->db->trans_start();

		if ($column == 'COL_DARC_DOK') {
			$value=strtoupper($value);
		}
		if ($column == 'COL_SIG') {
			$value=strtoupper($value);
			$value3=strtoupper($value3);
		}
		if ($column == 'station_id') {

			$this->load->model('stations');
			// Need to copy over from station profile to my_columns
			$station_profile = $this->stations->profile_clean($value);
			$stationid = $value;
			$stationCallsign = $station_profile->station_callsign;
			$iotaRef = $station_profile->station_iota ?? '';
			$sotaRef = $station_profile->station_sota ?? '';
			$wwffRef = $station_profile->station_wwff ?? '';
			$potaRef = $station_profile->station_pota ?? '';
			$sig     = $station_profile->station_sig ?? '';
			$sigInfo = $station_profile->station_sig_info ?? '';
			$gridsquare = '';
			$vuccGrids = '';

			if (strpos($station_profile->station_gridsquare, ',') !== false) {
				$vuccGrids = $station_profile->station_gridsquare ?? '';
			} else {
				$gridsquare = $station_profile->station_gridsquare ?? '';
			}

			$sql = "UPDATE ".$this->config->item('table_name')." JOIN station_profile ON ". $this->config->item('table_name').".station_id = station_profile.station_id" .
			" SET " . $this->config->item('table_name').".STATION_ID = ?" .
			", " . $this->config->item('table_name') . ".COL_MY_IOTA = ?" .
			", " . $this->config->item('table_name') . ".COL_MY_SOTA_REF = ?" .
			", " . $this->config->item('table_name') . ".COL_MY_WWFF_REF = ?" .
			", " . $this->config->item('table_name') . ".COL_MY_POTA_REF = ?" .
			", " . $this->config->item('table_name') . ".COL_MY_SIG = ?" .
			", " . $this->config->item('table_name') . ".COL_MY_SIG_INFO = ?" .
			", " . $this->config->item('table_name') . ".COL_STATION_CALLSIGN = ?" .
			", " . $this->config->item('table_name') . ".COL_MY_GRIDSQUARE = ?" .
			", " . $this->config->item('table_name') . ".COL_MY_VUCC_GRIDS = ?" .
			" WHERE " . $this->config->item('table_name').".col_primary_key in ? and station_profile.user_id = ?";

			$query = $this->db->query($sql, array($stationid, $iotaRef, $sotaRef, $wwffRef, $potaRef, $sig, $sigInfo, $stationCallsign, $gridsquare, $vuccGrids, json_decode($ids, true), $this->session->userdata('user_id')));
		} else if ($column == 'COL_BAND') {

			if ($value == '') return;

			$bandrx = $value2 == '' ? '' : $value2;
			$sql = "UPDATE ".$this->config->item('table_name')." JOIN station_profile ON ". $this->config->item('table_name').".station_id = station_profile.station_id" .
			" SET " . $this->config->item('table_name').".COL_BAND = ?" .
			", " . $this->config->item('table_name').".COL_BAND_RX = ?" .
			", " . $this->config->item('table_name').".COL_FREQ = ?" .
			", " . $this->config->item('table_name').".COL_FREQ_RX = ?" .
			" WHERE " . $this->config->item('table_name').".col_primary_key in ? and station_profile.user_id = ?";

			$frequencyBand = $this->frequency->defaultFrequencies[$value]['CW'];
			$frequencyBandRx = $bandrx == '' ? null : $this->frequency->defaultFrequencies[$bandrx]['CW'];

			$query = $this->db->query($sql, array($value, $value2, $frequencyBand, $frequencyBandRx, json_decode($ids, true), $this->session->userdata('user_id')));
		}  else if ($column == 'COL_FREQUENCY') {

			if ($value == '') return;

			if (trim($value2 ?? '') == '') { $value2=null; }

			$sql = "UPDATE ".$this->config->item('table_name')." JOIN station_profile ON ". $this->config->item('table_name').".station_id = station_profile.station_id" .
			" SET " . $this->config->item('table_name').".COL_FREQ = ?" .
			", " . $this->config->item('table_name').".COL_FREQ_RX = ?" .
			", " . $this->config->item('table_name').".COL_BAND = ?" .
			", " . $this->config->item('table_name').".COL_BAND_RX = ?" .
			" WHERE " . $this->config->item('table_name').".col_primary_key in ? and station_profile.user_id = ?";

			$band = $this->frequency->GetBand($value);
			$bandRx = $value2 == null ? null : $this->frequency->GetBand($value2);

			$query = $this->db->query($sql, array($value, $value2, $band, $bandRx, json_decode($ids, true), $this->session->userdata('user_id')));
		} else if ($column == 'COL_GRIDSQUARE') {
			$grid_value = null;
			$vucc_value = null;
			if ($value != '') {
				if(!$this->load->is_loaded('Qra')) {
					$this->load->library('Qra');
				}
				$latlng=$this->qra->qra2latlong(trim(xss_clean($value) ?? ''));
				if ($latlng[1] ?? '--' != '--') {
					if (strpos(trim(xss_clean($value) ?? ''), ',') !== false) {
						$grid_value = null;
						$vucc_value = strtoupper(preg_replace('/\s+/', '', xss_clean($value) ?? ''));
					} else {
						$vucc_value = null;
						$grid_value = strtoupper(trim(xss_clean($value) ?? ''));
					}

				}
			}
			$sql = "UPDATE ".$this->config->item('table_name')." JOIN station_profile ON ". $this->config->item('table_name').".station_id = station_profile.station_id" .
				" SET " . $this->config->item('table_name').".COL_GRIDSQUARE = ?" .
				", " . $this->config->item('table_name').".COL_VUCC_GRIDS = ?" .
				" WHERE " . $this->config->item('table_name').".col_primary_key in ? and station_profile.user_id = ?";
			$query = $this->db->query($sql, array($grid_value, $vucc_value, json_decode($ids, true), $this->session->userdata('user_id')));
		} else if ($column == 'COL_MODE') {

			$this->load->model('logbook_model');
			$mode = $this->logbook_model->get_main_mode_if_submode($value);
			if ($mode == null) {
				$col_mode = $value;
				$col_submode = null;
			} else {
				$col_mode = $mode;
				$col_submode = $value;
			}

			$sql = "UPDATE ".$this->config->item('table_name')." JOIN station_profile ON ". $this->config->item('table_name').".station_id = station_profile.station_id" .
			" SET " . $this->config->item('table_name').".COL_MODE = ?" .
			", " . $this->config->item('table_name').".COL_SUBMODE = ?" .
			" WHERE " . $this->config->item('table_name').".col_primary_key in ? and station_profile.user_id = ?";

			$query = $this->db->query($sql, array($col_mode, $col_submode, json_decode($ids, true), $this->session->userdata('user_id')));
		} else if ($column == 'COL_QSL_VIA') {

			$sql = "UPDATE ".$this->config->item('table_name')." JOIN station_profile ON ". $this->config->item('table_name').".station_id = station_profile.station_id" .
			" SET " . $this->config->item('table_name').".COL_QSL_VIA = ?" .
			" WHERE " . $this->config->item('table_name').".col_primary_key in ? and station_profile.user_id = ?";

			$query = $this->db->query($sql, array(mb_convert_encoding($value ?? '', 'UTF-8', 'UTF-8') ?: NULL, json_decode($ids, true), $this->session->userdata('user_id')));
		} else if ($column == 'COL_TIME_ON') {

			$sql = "UPDATE ".$this->config->item('table_name')." JOIN station_profile ON ". $this->config->item('table_name').".station_id = station_profile.station_id" .
			" SET " . $this->config->item('table_name').".col_time_on = TIMESTAMP(CONCAT(DATE_FORMAT(?, '%Y-%m-%d'), ' ', cast(col_time_on as time)))" .
			" WHERE " . $this->config->item('table_name').".col_primary_key in ? and station_profile.user_id = ?";

			$query = $this->db->query($sql, array($value, json_decode($ids, true), $this->session->userdata('user_id')));

		} else if ($column == 'COL_SAT_NAME') {
			$bindings=[];

			$propmode = $value == '' ? '' : 'SAT';
			$satmode = $value2 ?? '';
			$bandtx = $value3 == '' ? '' : $value3;
			$bandrx = $value4 == '' ? '' : $value4;

			$bindings[] = $value;
			$bindings[] = $propmode;

			$sql = "UPDATE ".$this->config->item('table_name')." JOIN station_profile ON ". $this->config->item('table_name').".station_id = station_profile.station_id" .
			" SET " . $this->config->item('table_name').".COL_SAT_NAME = ?" .
			", " . $this->config->item('table_name').".COL_PROP_MODE = ?";

			if ($satmode != '') {
				$sql .= ", " . $this->config->item('table_name').".COL_SAT_MODE = ?";
				$bindings[] = $satmode;
			}

			if ($bandtx != '') {
				$sql .= ", " . $this->config->item('table_name').".COL_BAND = ?";
				$bindings[] = $bandtx;
				$frequencyBand = $this->frequency->defaultFrequencies[$bandtx]['CW'];
				$sql .= ", " . $this->config->item('table_name').".COL_FREQ = ?";
				$bindings[] = $frequencyBand;
			}

			if ($bandrx != '') {
				$sql .= ", " . $this->config->item('table_name').".COL_BAND_RX = ?";
				$bindings[] = $bandrx;
				$frequencyBandRx = $bandrx == '' ? null : $this->frequency->defaultFrequencies[$bandrx]['CW'];
				$sql .= ", " . $this->config->item('table_name').".COL_FREQ_RX = ?";
				$bindings[] = $frequencyBandRx;
			}

			$sql .= " WHERE " . $this->config->item('table_name').".col_primary_key in ? and station_profile.user_id = ?";

			$bindings[] = json_decode($ids, true);
			$bindings[] = $this->session->userdata('user_id');

			$query = $this->db->query($sql, $bindings);
		} else if ($column == 'COL_LOTW_QSL_SENT') {

			$sql = "UPDATE ".$this->config->item('table_name')." JOIN station_profile ON ". $this->config->item('table_name').".station_id = station_profile.station_id" .
			" SET " . $this->config->item('table_name').".COL_LOTW_QSL_SENT = ?, " . $this->config->item('table_name').".COL_LOTW_QSLSDATE = now()" .
			" WHERE " . $this->config->item('table_name').".col_primary_key in ? and station_profile.user_id = ?";

			$query = $this->db->query($sql, array($value, json_decode($ids, true), $this->session->userdata('user_id')));
		} else if ($column == 'COL_LOTW_QSL_RCVD') {

			$sql = "UPDATE ".$this->config->item('table_name')." JOIN station_profile ON ". $this->config->item('table_name').".station_id = station_profile.station_id" .
			" SET " . $this->config->item('table_name').".COL_LOTW_QSL_RCVD = ?, " . $this->config->item('table_name').".COL_LOTW_QSLRDATE = now()" .
			" WHERE " . $this->config->item('table_name').".col_primary_key in ? and station_profile.user_id = ?";

			$query = $this->db->query($sql, array($value, json_decode($ids, true), $this->session->userdata('user_id')));
		} else if ($column == 'COL_DCL_QSL_SENT') {
			$skipqrzupdate = true;

			$sql = "UPDATE ".$this->config->item('table_name')." JOIN station_profile ON ". $this->config->item('table_name').".station_id = station_profile.station_id" .
			" SET " . $this->config->item('table_name').".COL_DCL_QSL_SENT = ?, " . $this->config->item('table_name').".COL_DCL_QSLSDATE = now()" .
			" WHERE " . $this->config->item('table_name').".col_primary_key in ? and station_profile.user_id = ?";

			$query = $this->db->query($sql, array($value, json_decode($ids, true), $this->session->userdata('user_id')));
		} else if ($column == 'COL_DCL_QSL_RCVD') {
			$skipqrzupdate = true;

			$sql = "UPDATE ".$this->config->item('table_name')." JOIN station_profile ON ". $this->config->item('table_name').".station_id = station_profile.station_id" .
			" SET " . $this->config->item('table_name').".COL_DCL_QSL_RCVD = ?, " . $this->config->item('table_name').".COL_DCL_QSLRDATE = now()" .
			" WHERE " . $this->config->item('table_name').".col_primary_key in ? and station_profile.user_id = ?";

			$query = $this->db->query($sql, array($value, json_decode($ids, true), $this->session->userdata('user_id')));

		} else if ($column == 'COL_QRZCOM_QSO_UPLOAD_STATUS') {
			$skipqrzupdate = true;

			$sql = "UPDATE ".$this->config->item('table_name')." JOIN station_profile ON ". $this->config->item('table_name').".station_id = station_profile.station_id" .
			" SET " . $this->config->item('table_name').".COL_QRZCOM_QSO_UPLOAD_STATUS = ?, " . $this->config->item('table_name').".COL_QRZCOM_QSO_UPLOAD_DATE = now()" .
			" WHERE " . $this->config->item('table_name').".col_primary_key in ? and station_profile.user_id = ?";

			$query = $this->db->query($sql, array($value, json_decode($ids, true), $this->session->userdata('user_id')));
		} else if ($column == 'COL_QRZCOM_QSO_DOWNLOAD_STATUS') {
			$skipqrzupdate = true;

			$sql = "UPDATE ".$this->config->item('table_name')." JOIN station_profile ON ". $this->config->item('table_name').".station_id = station_profile.station_id" .
			" SET " . $this->config->item('table_name').".COL_QRZCOM_QSO_DOWNLOAD_STATUS = ?, " . $this->config->item('table_name').".COL_QRZCOM_QSO_DOWNLOAD_DATE = now()" .
			" WHERE " . $this->config->item('table_name').".col_primary_key in ? and station_profile.user_id = ?";

			$query = $this->db->query($sql, array($value, json_decode($ids, true), $this->session->userdata('user_id')));

		} else if ($column == 'COL_EQSL_QSL_SENT') {
			$skipqrzupdate = true;

			$sql = "UPDATE ".$this->config->item('table_name')." JOIN station_profile ON ". $this->config->item('table_name').".station_id = station_profile.station_id" .
			" SET " . $this->config->item('table_name').".COL_EQSL_QSL_SENT = ?, " . $this->config->item('table_name').".COL_EQSL_QSLSDATE = now()" .
			" WHERE " . $this->config->item('table_name').".col_primary_key in ? and station_profile.user_id = ?";

			$query = $this->db->query($sql, array($value, json_decode($ids, true), $this->session->userdata('user_id')));
		} else if ($column == 'COL_EQSL_QSL_RCVD') {
			$skipqrzupdate = true;

			$sql = "UPDATE ".$this->config->item('table_name')." JOIN station_profile ON ". $this->config->item('table_name').".station_id = station_profile.station_id" .
			" SET " . $this->config->item('table_name').".COL_EQSL_QSL_RCVD = ?, " . $this->config->item('table_name').".COL_EQSL_QSLRDATE = now()" .
			" WHERE " . $this->config->item('table_name').".col_primary_key in ? and station_profile.user_id = ?";

			$query = $this->db->query($sql, array($value, json_decode($ids, true), $this->session->userdata('user_id')));

		} else if ($column == 'COL_CLUBLOG_QSO_DOWNLOAD_STATUS') {
			$skipqrzupdate = true;

			$sql = "UPDATE ".$this->config->item('table_name')." JOIN station_profile ON ". $this->config->item('table_name').".station_id = station_profile.station_id" .
			" SET " . $this->config->item('table_name').".COL_CLUBLOG_QSO_DOWNLOAD_STATUS = ?, " . $this->config->item('table_name').".COL_CLUBLOG_QSO_DOWNLOAD_DATE = now()" .
			" WHERE " . $this->config->item('table_name').".col_primary_key in ? and station_profile.user_id = ?";

			$query = $this->db->query($sql, array($value, json_decode($ids, true), $this->session->userdata('user_id')));

		} else if ($column == 'COL_CLUBLOG_QSO_UPLOAD_STATUS') {
			$skipqrzupdate = true;

			$sql = "UPDATE ".$this->config->item('table_name')." JOIN station_profile ON ". $this->config->item('table_name').".station_id = station_profile.station_id" .
			" SET " . $this->config->item('table_name').".COL_CLUBLOG_QSO_UPLOAD_STATUS = ?, " . $this->config->item('table_name').".COL_CLUBLOG_QSO_UPLOAD_DATE = now()" .
			" WHERE " . $this->config->item('table_name').".col_primary_key in ? and station_profile.user_id = ?";

			$query = $this->db->query($sql, array($value, json_decode($ids, true), $this->session->userdata('user_id')));

		} else if ($column == 'COL_QSLMSG') {

			$sql = "UPDATE ".$this->config->item('table_name')." JOIN station_profile ON ". $this->config->item('table_name').".station_id = station_profile.station_id" .
			" SET " . $this->config->item('table_name').".COL_QSLMSG = ? " .
			" WHERE " . $this->config->item('table_name').".col_primary_key in ? and station_profile.user_id = ?";

			$query = $this->db->query($sql, array($value, json_decode($ids, true), $this->session->userdata('user_id')));
		} else if ($column == 'COL_CONT') {

			$sql = "UPDATE ".$this->config->item('table_name')." JOIN station_profile ON ". $this->config->item('table_name').".station_id = station_profile.station_id" .
			" SET " . $this->config->item('table_name').".COL_CONT = ? " .
			" WHERE " . $this->config->item('table_name').".col_primary_key in ? and station_profile.user_id = ?";

			$query = $this->db->query($sql, array($value, json_decode($ids, true), $this->session->userdata('user_id')));
		} else if ($column == 'COL_TX_PWR') {

			$sql = "UPDATE ".$this->config->item('table_name')." JOIN station_profile ON ". $this->config->item('table_name').".station_id = station_profile.station_id" .
			" SET " . $this->config->item('table_name').".COL_TX_PWR = ? " .
			" WHERE " . $this->config->item('table_name').".col_primary_key in ? and station_profile.user_id = ?";

			$query = $this->db->query($sql, array($value, json_decode($ids, true), $this->session->userdata('user_id')));
		} else if ($column == 'COL_REGION') {

			$sql = "UPDATE ".$this->config->item('table_name')." JOIN station_profile ON ". $this->config->item('table_name').".station_id = station_profile.station_id" .
			" SET " . $this->config->item('table_name').".COL_REGION = ? " .
			" WHERE " . $this->config->item('table_name').".col_primary_key in ? and station_profile.user_id = ?";

			$query = $this->db->query($sql, array($value, json_decode($ids, true), $this->session->userdata('user_id')));
		} else if ($column == 'COL_QSL_SENT_VIA') {

			$sql = "UPDATE ".$this->config->item('table_name')." JOIN station_profile ON ". $this->config->item('table_name').".station_id = station_profile.station_id" .
			" SET " . $this->config->item('table_name').".COL_QSL_SENT_VIA = ? " .
			" WHERE " . $this->config->item('table_name').".col_primary_key in ? and station_profile.user_id = ?";

			$query = $this->db->query($sql, array($value, json_decode($ids, true), $this->session->userdata('user_id')));
		} else if ($column == 'COL_QSL_RCVD_VIA') {

			$sql = "UPDATE ".$this->config->item('table_name')." JOIN station_profile ON ". $this->config->item('table_name').".station_id = station_profile.station_id" .
			" SET " . $this->config->item('table_name').".COL_QSL_RCVD_VIA = ? " .
			" WHERE " . $this->config->item('table_name').".col_primary_key in ? and station_profile.user_id = ?";

			$query = $this->db->query($sql, array($value, json_decode($ids, true), $this->session->userdata('user_id')));
		} else if ($column == 'COL_DISTANCE' && $value == '') {
			$this->update_distances($ids);
			$skipqrzupdate = true;
		} else if ($column == 'COL_SIG') {
			$args = array();
			if ($value != '' || $value2 == "true" || $value3 != '' || $value4 == "true") {
				$sql = "UPDATE ".$this->config->item('table_name')." JOIN station_profile ON ".$this->config->item('table_name').".station_id = station_profile.station_id SET ";
				if ($value != '' || $value2 == "true") {
					$sql .= $this->config->item('table_name').".COL_SIG = ?";
					$args[] = ($value2 == "true" ? '' : $value);
				}
				if ($value3 != '' || $value4 == "true") {
					if ($value != '' || $value2 == "true") {
						$sql .= ", ";
					}
					$sql .= $this->config->item('table_name').".COL_SIG_INFO = ?";
					$args[] = ($value4 == "true" ? '' : $value3);
				}
				$sql .= " WHERE " . $this->config->item('table_name').".col_primary_key in ? and station_profile.user_id = ?";
				$args[] = json_decode($ids, true);
				$args[] = $this->session->userdata('user_id');
				$query = $this->db->query($sql, $args);
			}
		} else {

			if ($value == "null") {
				$value = null;
			}

			$sql = "UPDATE ".$this->config->item('table_name')." JOIN station_profile ON ".$this->config->item('table_name').".station_id = station_profile.station_id SET " . $this->config->item('table_name').".".$column . " = ? WHERE " . $this->config->item('table_name').".col_primary_key in ? and station_profile.user_id = ?";

			$query = $this->db->query($sql, array($value, json_decode($ids, true), $this->session->userdata('user_id')));
		}

		if (!$skipqrzupdate) {
			$sql = "UPDATE ".$this->config->item('table_name')." JOIN station_profile ON ".$this->config->item('table_name').".station_id = station_profile.station_id SET " . $this->config->item('table_name').".COL_QRZCOM_QSO_UPLOAD_STATUS = 'M' WHERE " . $this->config->item('table_name').".col_primary_key in ? and station_profile.user_id = ? and col_qrzcom_qso_upload_status = 'Y'";
			$query = $this->db->query($sql, array(json_decode($ids, true), $this->session->userdata('user_id')));
		}

		$this->db->trans_complete();

		return array('message' => 'OK');
    }

	public function update_distances($ids) {
		$idarray = (json_decode($ids, true));
		ini_set('memory_limit', '-1');
		$this->db->trans_start();
		$this->db->select("COL_PRIMARY_KEY, COL_GRIDSQUARE, COL_ANT_PATH, station_gridsquare");
		$this->db->join('station_profile', 'station_profile.station_id = ' . $this->config->item('table_name') . '.station_id');

		$this->db->where("COL_GRIDSQUARE is NOT NULL");
		$this->db->where("COL_GRIDSQUARE != ''");
		$this->db->where_in("COL_PRIMARY_KEY", $idarray);
		$query = $this->db->get($this->config->item('table_name'));

		if ($query->num_rows() > 0) {
			if (!$this->load->is_loaded('Qra')) {
				$this->load->library('Qra');
			}
			foreach ($query->result() as $row) {
				$ant_path = $row->COL_ANT_PATH ?? null;
				$distance = $this->qra->distance($row->station_gridsquare, $row->COL_GRIDSQUARE, 'K', $ant_path);
				$data = array(
					'COL_DISTANCE' => $distance,
				);

				$this->db->where(array('COL_PRIMARY_KEY' => $row->COL_PRIMARY_KEY));
				$this->db->update($this->config->item('table_name'), $data);
			}
		}
		$this->db->trans_complete();
	}


	function deleteQsos($ids) {
		$this->db->trans_start();

		$sql = "delete from " . $this->config->item('table_name') . " WHERE col_primary_key in ? and station_id in (select station_id from station_profile where user_id = ?)";

		$query = $this->db->query($sql, array(json_decode($ids, true), $this->session->userdata('user_id')));
		$this->db->trans_complete();
    }

	function getPrimarySubdivisonsDxccs() {
		$sql = "select distinct primary_subdivisions.adif, dxcc_entities.name, dxcc_entities.prefix
		from primary_subdivisions
		join dxcc_entities on primary_subdivisions.adif = dxcc_entities.adif
		order by prefix";

		$query = $this->db->query($sql);
		return $query->result();
    }

	function getSubdivisons($dxccid) {
		$sql = "select * from primary_subdivisions where adif = ? order by subdivision";

		$query = $this->db->query($sql, array($dxccid));
		return $query->result();
    }

	function fixCqZones($ids = null) {
		if ($ids == null) {
			$sql = "UPDATE ".$this->config->item('table_name')." JOIN dxcc_entities ON ". $this->config->item('table_name').".col_dxcc = dxcc_entities.adif JOIN station_profile ON ". $this->config->item('table_name').".station_id = station_profile.station_id" .
					" SET " . $this->config->item('table_name').".COL_CQZ = dxcc_entities.cqz" .
					" WHERE station_profile.user_id = ? and " . $this->config->item('table_name') . ".COL_CQZ IS NULL";

			$query = $this->db->query($sql, array($this->session->userdata('user_id')));
			return $this->db->affected_rows();
		}
		$sql = "UPDATE ".$this->config->item('table_name')." JOIN dxcc_entities ON ". $this->config->item('table_name').".col_dxcc = dxcc_entities.adif JOIN station_profile ON ". $this->config->item('table_name').".station_id = station_profile.station_id" .
			" SET " . $this->config->item('table_name').".COL_CQZ = dxcc_entities.cqz" .
			" WHERE " . $this->config->item('table_name').".col_primary_key in ? and station_profile.user_id = ?";

		$query = $this->db->query($sql, array(json_decode($ids, true), $this->session->userdata('user_id')));
		return $this->db->affected_rows();
	}


	function fixItuZones($ids = null) {
		if ($ids == null) {
			$sql = "UPDATE ".$this->config->item('table_name')." JOIN dxcc_entities ON ". $this->config->item('table_name').".col_dxcc = dxcc_entities.adif JOIN station_profile ON ". $this->config->item('table_name').".station_id = station_profile.station_id" .
					" SET " . $this->config->item('table_name').".COL_ITUZ = dxcc_entities.ituz" .
					" WHERE station_profile.user_id = ? and " . $this->config->item('table_name') . ".COL_ITUZ IS NULL";
			$query = $this->db->query($sql, array($this->session->userdata('user_id')));
			return $this->db->affected_rows();
		}
		$sql = "UPDATE ".$this->config->item('table_name')." JOIN dxcc_entities ON ". $this->config->item('table_name').".col_dxcc = dxcc_entities.adif JOIN station_profile ON ". $this->config->item('table_name').".station_id = station_profile.station_id" .
			" SET " . $this->config->item('table_name').".COL_ITUZ = dxcc_entities.ituz" .
			" WHERE " . $this->config->item('table_name').".col_primary_key in ? and station_profile.user_id = ?";

		$query = $this->db->query($sql, array(json_decode($ids, true), $this->session->userdata('user_id')));
		return $this->db->affected_rows();
    }

	/**
	 * Fix state for a single QSO using GeoJSON lookup
	 *
	 * @param int $qso_id QSO primary key
	 * @return array Result array with success, dxcc_name, dxcc_number, state_code, skipped
	 */
	function fixStateSingle($qso_id) {
		$this->load->library('Geojson');

		// Get QSO data
		$sql = "SELECT COL_PRIMARY_KEY, COL_CALL, COL_GRIDSQUARE, COL_DXCC, COL_STATE, d.name as dxcc_name
				FROM " . $this->config->item('table_name') . " qsos
				JOIN station_profile ON qsos.station_id = station_profile.station_id
				LEFT JOIN dxcc_entities d ON qsos.COL_DXCC = d.adif
				WHERE qsos.COL_PRIMARY_KEY = ? AND station_profile.user_id = ?";

		$query = $this->db->query($sql, [$qso_id, $this->session->userdata('user_id')]);

		if ($query->num_rows() === 0) {
			return [
				'success' => false,
				'skipped' => true,
				'reason' => 'QSO not found'
			];
		}

		$qso = $query->row();
		$callsign = $qso->COL_CALL ?? 'Unknown';
		$dxcc = (int)$qso->COL_DXCC;
		$gridsquare = $qso->COL_GRIDSQUARE;
		$state = $qso->COL_STATE ?? '';
		$dxcc_name = $qso->dxcc_name ?? 'Unknown';

		// Skip if state is already populated
		if (!empty($state)) {
			return [
				'success' => false,
				'skipped' => true,
				'callsign' => $callsign,
				'dxcc_number' => $dxcc,
				'dxcc_name' => $dxcc_name,
				'reason' => 'State already set'
			];
		}

		// Check if gridsquare exists
		if (empty($gridsquare)) {
			return [
				'success' => false,
				'skipped' => true,
				'callsign' => $callsign,
				'dxcc_number' => $dxcc,
				'dxcc_name' => $dxcc_name,
				'reason' => 'No gridsquare'
			];
		}

		// Check if gridsquare is precise enough (at least 6 characters)
		if (strlen($gridsquare) < 6) {
			return [
				'success' => false,
				'skipped' => true,
				'callsign' => $callsign,
				'dxcc_number' => $dxcc,
				'dxcc_name' => $dxcc_name,
				'reason' => 'Gridsquare not precise enough'
			];
		}

		// Check if state is supported for this DXCC
		if (!$this->geojson->isStateSupported($dxcc)) {
			return [
				'success' => false,
				'skipped' => true,
				'callsign' => $callsign,
				'dxcc_number' => $dxcc,
				'dxcc_name' => $dxcc_name,
				'reason' => 'DXCC not supported'
			];
		}

		// Find state from gridsquare
		$state = $this->geojson->findStateFromGridsquare($gridsquare, $dxcc);

		if ($state === null || !isset($state['code'])) {
			// Get coordinates for debugging
			$coords = $this->geojson->gridsquareToLatLng($gridsquare);
			return [
				'success' => false,
				'skipped' => false,
				'callsign' => $callsign,
				'dxcc_number' => $dxcc,
				'dxcc_name' => $dxcc_name,
				'gridsquare' => $gridsquare,
				'lat' => $coords['lat'] ?? null,
				'lng' => $coords['lng'] ?? null,
				'reason' => 'State not found in GeoJSON'
			];
		}

		// Update the state
		$update_sql = "UPDATE " . $this->config->item('table_name') . "
					   SET COL_STATE = ?
					   WHERE COL_PRIMARY_KEY = ?";

		$this->db->query($update_sql, [$state['code'], $qso_id]);

		return [
			'success' => true,
			'skipped' => false,
			'callsign' => $callsign,
			'dxcc_number' => $dxcc,
			'dxcc_name' => $dxcc_name,
			'state_code' => $state['code'],
			'state_name' => $state['name'] ?? null
		];
	}

	function getQsoForMerge($qsoId) {
		$qsoId = intval($qsoId);
		$sql = "SELECT thcv.* FROM " . $this->config->item('table_name') . " thcv
		join station_profile ON thcv.station_id = station_profile.station_id
		WHERE thcv.COL_PRIMARY_KEY = ?
		and station_profile.user_id = ?";

		$query = $this->db->query($sql, array($qsoId, $this->session->userdata('user_id')));
		return $query->row();
	}

	function mergeQsos($qsoId1, $qsoId2, $mergeData) {
		$qsoId1 = intval($qsoId1);
		$qsoId2 = intval($qsoId2);
		$primaryId = intval($mergeData['primaryQso']);

		if (!in_array($primaryId, [$qsoId1, $qsoId2])) {
			return ['success' => false, 'message' => __("Invalid primary QSO ID, or you do not have permission to access one of the QSOs")];
		}

		$secondaryId = ($primaryId == $qsoId1) ? $qsoId2 : $qsoId1;

		// Get both QSOs
		$primaryQso = $this->getQsoForMerge($primaryId);
		$secondaryQso = $this->getQsoForMerge($secondaryId);

		if (!$primaryQso || !$secondaryQso) {
			return ['success' => false, 'message' => __("QSO not found")];
		}

		// Merge the data
		$updateData = [];
		$mergeableFields = [
			'COL_RST_SENT', 'COL_RST_RCVD',
			'COL_NAME', 'COL_QTH', 'COL_GRID', 'COL_STATE',
			'COL_CNTY', 'COL_COUNTRY', 'COL_DXCC',
			'COL_CQZ', 'COL_ITUZ',
			'COL_ADDRESS', 'COL_AGE',
			'COL_ARRL_SECT', 'COL_BIOGRAPHY', 'COL_EMAIL',
			'COL_IOTA', 'COL_POTA', 'COL_SOTA', 'COL_WWFF',
			'COL_DARC_DOK', 'COL_SIG', 'COL_SIG_INFO',
			'COL_FISTS', 'COL_FISTS_CC', 'COL_SKCC', 'COL_TEN_TEN', 'COL_UKSMG',
			'COL_VUCC_GRIDS', 'COL_USACA_COUNTIES',
			'COL_COMMENT', 'COL_QSL_VIA',
			'COL_QSLMSG', 'COL_QSLMSG_RCVD', 'COL_QSLSDATE', 'COL_QSLRDATE',
			'COL_QSL_SENT', 'COL_QSL_RCVD',
			'COL_QSL_SENT_VIA', 'COL_QSL_RCVD_VIA',
			'COL_LOTW_QSL_SENT', 'COL_LOTW_QSL_RCVD',
			'COL_LOTW_QSLSDATE', 'COL_LOTW_QSLRDATE', 'COL_LOTW_STATUS',
			'COL_CLUBLOG_QSO_UPLOAD_DATE', 'COL_CLUBLOG_QSO_UPLOAD_STATUS',
			'COL_CLUBLOG_QSO_DOWNLOAD_DATE', 'COL_CLUBLOG_QSO_DOWNLOAD_STATUS',
			'COL_EQSL_QSL_SENT', 'COL_EQSL_QSL_RCVD',
			'COL_EQSL_QSLSDATE', 'COL_EQSL_QSLRDATE',
			'COL_EQSL_AG', 'COL_EQSL_STATUS',
			'COL_QRZCOM_QSO_UPLOAD_STATUS', 'COL_QRZCOM_QSO_UPLOAD_DATE',
			'COL_QRZCOM_QSO_DOWNLOAD_DATE', 'COL_QRZCOM_QSO_DOWNLOAD_STATUS',
			'COL_HRDLOG_QSO_UPLOAD_STATUS', 'COL_HRDLOG_QSO_UPLOAD_DATE',
			'COL_DCL_QSL_SENT', 'COL_DCL_QSL_RCVD',
			'COL_DCL_QSLSDATE', 'COL_DCL_QSLRDATE',
			'COL_OPERATOR', 'COL_OWNER_CALLSIGN', 'COL_STATION_CALLSIGN',
			'COL_MY_DXCC', 'COL_MY_COUNTRY', 'COL_MY_STATE',
			'COL_MY_CNTY', 'COL_MY_CNTY_ALT', 'COL_MY_CQ_ZONE', 'COL_MY_ITU_ZONE',
			'COL_MY_GRIDSQUARE', 'COL_MY_IOTA', 'COL_MY_IOTA_ISLAND_ID',
			'COL_MY_SOTA_REF', 'COL_MY_POTA_REF', 'COL_MY_WWFF_REF',
			'COL_MY_VUCC_GRIDS', 'COL_MY_DARC_DOK', 'COL_MY_FISTS',
			'COL_MY_NAME', 'COL_MY_CITY', 'COL_MY_POSTAL_CODE', 'COL_MY_STREET',
			'COL_MY_ANTENNA', 'COL_MY_RIG', 'COL_MY_SIG', 'COL_MY_SIG_INFO',
			'COL_SAT_MODE', 'COL_SAT_NAME',
			'COL_PROP_MODE', 'COL_BAND', 'COL_MODE', 'COL_SUBMODE',
			'COL_FREQ', 'COL_FREQ_RX', 'COL_BAND_RX',
			'COL_ANT_AZ', 'COL_ANT_EL', 'COL_ANT_PATH',
			'COL_A_INDEX', 'COL_K_INDEX',
			'COL_SFI', 'COL_TX_PWR', 'COL_RX_PWR',
			'COL_STX', 'COL_STX_STRING',
			'COL_SRX', 'COL_SRX_STRING', 'COL_CONTEST_ID',
			'COL_PRECEDENCE', 'COL_MORSE_KEY_TYPE', 'COL_MORSE_KEY_INFO',
			'COL_SILENT_KEY', 'COL_SWL', 'COL_WEB',
			'COL_QSL_RCVD_VIA', 'COL_DISTANCE',
			'COL_REGION', 'COL_RIG', 'COL_NOTES', 'COL_QSO_COMPLETE',
			'COL_USER_DEFINED_0', 'COL_USER_DEFINED_1', 'COL_USER_DEFINED_2',
			'COL_USER_DEFINED_3', 'COL_USER_DEFINED_4', 'COL_USER_DEFINED_5',
			'COL_USER_DEFINED_6', 'COL_USER_DEFINED_7', 'COL_USER_DEFINED_8',
			'COL_USER_DEFINED_9'
		];

		foreach ($mergeableFields as $field) {
			$fieldName = substr($field, 4); // Remove 'COL_' prefix
			if (isset($mergeData[$fieldName]) && $mergeData[$fieldName] != 'primary') {
				$sourceQso = ($mergeData[$fieldName] == 'qso1') ?
					($primaryId == $qsoId1 ? $primaryQso : $secondaryQso) :
					($primaryId == $qsoId2 ? $primaryQso : $secondaryQso);

				// Determine the source value
				$sourceValue = isset($sourceQso->$field) ? $sourceQso->$field : null;
				$primaryValue = isset($primaryQso->$field) ? $primaryQso->$field : null;

				// Only update if:
				// 1. Source has a value (not null/empty) - use that value
				// 2. Source is empty BUT primary has a value - clear the field
				// 3. Both are empty - skip (no change needed)
				if ($sourceValue !== null && $sourceValue !== '') {
					// Source has a value, use it
					$updateData[$field] = $sourceValue;
				} elseif (($sourceValue === null || $sourceValue === '') &&
				          ($primaryValue !== null && $primaryValue !== '')) {
					// Source is empty but primary has a value - clear the field
					$updateData[$field] = '';
				}
				// If both are empty, skip this field
			}
		}

		if (empty($updateData)) {
			return ['success' => false, 'message' => __("No fields selected for merge or no differences between QSOs")];
		}

		// Update the primary QSO
		$this->db->where('COL_PRIMARY_KEY', $primaryId);
		$this->db->update($this->config->item('table_name'), $updateData);

		// Delete the secondary QSO
		$this->db->where('COL_PRIMARY_KEY', $secondaryId);
		$this->db->delete($this->config->item('table_name'));

		return [
			'success' => true,
			'message' => __("QSOs merged successfully"),
			'primaryId' => $primaryId,
			'deletedId' => $secondaryId
		];
	}
}
