<?php

class Sota extends CI_Model {

	function search_refs($term) {
		$json = [];
		$ref = strtoupper(trim((string) $term));
		if ($ref === '') {
			return $json;
		}

		$this->db->select('reference');
		$this->db->like('reference', $ref, 'after');
		$this->db->order_by('reference', 'asc');
		$this->db->limit(100);
		$q = $this->db->get('sota_directory');

		foreach ($q->result() as $row) {
			$json[] = ['name' => $row->reference];
		}

		return $json;
	}

	function get_all() {
		$this->load->model('logbooks_model');
		$logbooks_locations_array = $this->logbooks_model->list_logbook_relationships($this->session->userdata('active_station_logbook'));

		if (empty($logbooks_locations_array) || $logbooks_locations_array[0] === -1) {
			return [];
		}

		$location_list = "'" . implode("','", $logbooks_locations_array) . "'";

		// Default filter mirrors the SOTA page's checkbox defaults, so the
		// initial server-rendered table matches what the AJAX endpoints return
		// (awards/sota_table + awards/sota_map) before "Apply Filters" is used.
		$postdata = [
			'qsl'       => 1,
			'lotw'      => 1,
			'eqsl'      => null,
			'qrz'       => null,
			'clublog'   => null,
			'worked'    => 1,
			'confirmed' => 1,
			'band'      => 'All',
			'mode'      => 'All',
			'dateFrom'  => null,
			'dateTo'    => null,
		];

		return $this->get_qso_list($postdata, $location_list);
	}

	/*
	 * Cheap content signature for the plotted directory rows. Changes only
	 * when summits are added/removed/renamed/re-activated — i.e. only after
	 * the update_sota cron runs. Powers the weak ETag so 304s short-circuit
	 * before any row is touched.
	 */
	function directory_signature() {
		$sql = "SELECT COUNT(*) AS c,
				COALESCE(MAX(reference), '')     AS mr,
				COALESCE(MAX(valid_till), '')    AS vt,
				COALESCE(MAX(last_activated), '') AS la
			FROM sota_directory
			WHERE lat IS NOT NULL AND lon IS NOT NULL";
		$r = $this->db->query($sql)->row();
		return 'sota-dir-' . $r->c . '-' . substr(md5($r->mr . '|' . $r->vt . '|' . $r->la), 0, 16);
	}

	/*
	 * Stream the plotted directory as a JSON array straight to stdout. Rows
	 * are fetched one at a time via unbuffered_row() so peak memory stays
	 * flat regardless of directory size (~140k SOTA summits). Body shape is
	 * identical to the previous array-returning get_directory() — frontend
	 * parsing is unchanged.
	 */
	function stream_directory_json() {
		$sql = "SELECT reference, name, lat, lon, altitude, valid_from, valid_till
			FROM sota_directory
			WHERE lat IS NOT NULL AND lon IS NOT NULL
			ORDER BY reference";

		$query = $this->db->query($sql);

		echo '[';
		$first = true;
		while ($row = $query->unbuffered_row('object')) {
			echo ($first ? '' : ','), json_encode([
				'reference'  => $row->reference,
				'name'       => $row->name,
				'lat'        => (float) $row->lat,
				'lon'        => (float) $row->lon,
				'altitude'   => $row->altitude,
				'inactive'   => $this->_inactive($row->valid_from, $row->valid_till),
				'valid_from' => $row->valid_from,
				'valid_till' => $row->valid_till,
			]);
			$first = false;
		}
		echo ']';
	}

	function get_map_data($postdata, $location_list) {
		$bindings = [];

		$sql = "SELECT thcv.COL_SOTA_REF AS reference,
			MAX(sd.lat) AS lat, MAX(sd.lon) AS lon, MAX(sd.name) AS name, MAX(sd.altitude) AS altitude,
			MAX(sd.valid_from) AS valid_from, MAX(sd.valid_till) AS valid_till,
				MAX(CASE WHEN thcv.COL_QSL_RCVD = 'Y' THEN 1 ELSE 0 END) AS qsl,
				MAX(CASE WHEN thcv.COL_LOTW_QSL_RCVD = 'Y' THEN 1 ELSE 0 END) AS lotw,
				MAX(CASE WHEN thcv.COL_EQSL_QSL_RCVD = 'Y' THEN 1 ELSE 0 END) AS eqsl,
				MAX(CASE WHEN thcv.COL_QRZCOM_QSO_DOWNLOAD_STATUS = 'Y' THEN 1 ELSE 0 END) AS qrz,
				MAX(CASE WHEN thcv.COL_CLUBLOG_QSO_DOWNLOAD_STATUS = 'Y' THEN 1 ELSE 0 END) AS clublog
			FROM " . $this->config->item('table_name') . " thcv
			LEFT JOIN sota_directory sd ON sd.reference = thcv.COL_SOTA_REF
			WHERE thcv.station_id IN (" . $location_list . ")
				AND thcv.COL_SOTA_REF IS NOT NULL
				AND thcv.COL_SOTA_REF <> ''";

		$band = $postdata['band'] ?? 'All';
		if ($band == 'All') {
			$sql .= " and (thcv.col_prop_mode !='SAT' or thcv.col_prop_mode is NULL)";
		}
		else if ($band == 'SAT') {
			$sql .= " and thcv.col_prop_mode = ?";
			$bindings[] = $band;
		} else {
			$sql .= " AND thcv.COL_BAND = ?";
			$sql .= " and (thcv.col_prop_mode !='SAT' or thcv.col_prop_mode is NULL)";
			$bindings[] = $band;
		}

		$mode = $postdata['mode'] ?? 'All';
		if ($mode !== 'All' && $mode !== '') {
			$sql .= " AND (thcv.COL_MODE = ? OR thcv.COL_SUBMODE = ?)";
			$bindings[] = $mode;
			$bindings[] = $mode;
		}

		if (!empty($postdata['dateFrom'])) {
			$sql .= " AND thcv.COL_TIME_ON >= ?";
			$bindings[] = $postdata['dateFrom'] . ' 00:00:00';
		}
		if (!empty($postdata['dateTo'])) {
			$sql .= " AND thcv.COL_TIME_ON <= ?";
			$bindings[] = $postdata['dateTo'] . ' 23:59:59';
		}

		$sql .= " GROUP BY thcv.COL_SOTA_REF";
		$sql .= " ORDER BY thcv.COL_SOTA_REF ASC";

		$query = $this->db->query($sql, $bindings);

		$result = [];
		foreach ($query->result() as $row) {
			$isConfirmed = false;
			if (($postdata['qsl'] ?? 0) == 1 && $row->qsl) $isConfirmed = true;
			elseif (($postdata['lotw'] ?? 0) == 1 && $row->lotw) $isConfirmed = true;
			elseif (($postdata['eqsl'] ?? 0) == 1 && $row->eqsl) $isConfirmed = true;
			elseif (($postdata['qrz'] ?? 0) == 1 && $row->qrz) $isConfirmed = true;
			elseif (($postdata['clublog'] ?? 0) == 1 && $row->clublog) $isConfirmed = true;

			$status = $isConfirmed ? 'C' : 'W';

			if ($status == 'C' && ($postdata['confirmed'] ?? null) != 1) continue;
			if ($status == 'W' && ($postdata['worked'] ?? null) != 1) continue;

			$result[] = [
				'reference' => $row->reference,
				'name'      => $row->name,
				'lat'       => $row->lat !== null ? (float) $row->lat : null,
				'lon'       => $row->lon !== null ? (float) $row->lon : null,
				'altitude'  => $row->altitude !== null ? (int) $row->altitude : null,
				'inactive'  => $this->_inactive($row->valid_from, $row->valid_till),
				'status'    => $status,
			];
		}

		return $result;
	}

	function get_qso_list($postdata, $location_list) {
		$bindings = [];

		$sql = "SELECT thcv.COL_SOTA_REF, thcv.COL_TIME_ON, thcv.COL_CALL, thcv.COL_BAND,
				thcv.COL_SAT_NAME, thcv.COL_RST_SENT, thcv.COL_RST_RCVD, thcv.COL_PRIMARY_KEY,
				CASE WHEN thcv.COL_QSL_RCVD = 'Y' THEN 1 ELSE 0 END AS qsl,
				CASE WHEN thcv.COL_LOTW_QSL_RCVD = 'Y' THEN 1 ELSE 0 END AS lotw,
				CASE WHEN thcv.COL_EQSL_QSL_RCVD = 'Y' THEN 1 ELSE 0 END AS eqsl,
				CASE WHEN thcv.COL_QRZCOM_QSO_DOWNLOAD_STATUS = 'Y' THEN 1 ELSE 0 END AS qrz,
				CASE WHEN thcv.COL_CLUBLOG_QSO_DOWNLOAD_STATUS = 'Y' THEN 1 ELSE 0 END AS clublog
			FROM " . $this->config->item('table_name') . " thcv
			WHERE thcv.station_id IN (" . $location_list . ")
				AND thcv.COL_SOTA_REF IS NOT NULL
				AND thcv.COL_SOTA_REF <> ''";

		$band = $postdata['band'] ?? 'All';
		if ($band == 'All') {
			$sql .= " and (thcv.col_prop_mode !='SAT' or thcv.col_prop_mode is NULL)";
		}
		else if ($band == 'SAT') {
			$sql .= " and thcv.col_prop_mode = ?";
			$bindings[] = $band;
		} else {
			$sql .= " AND thcv.COL_BAND = ?";
			$sql .= " and (thcv.col_prop_mode !='SAT' or thcv.col_prop_mode is NULL)";
			$bindings[] = $band;
		}

		$mode = $postdata['mode'] ?? 'All';
		if ($mode !== 'All' && $mode !== '') {
			$sql .= " AND (thcv.COL_MODE = ? OR thcv.COL_SUBMODE = ?)";
			$bindings[] = $mode;
			$bindings[] = $mode;
		}

		if (!empty($postdata['dateFrom'])) {
			$sql .= " AND thcv.COL_TIME_ON >= ?";
			$bindings[] = $postdata['dateFrom'] . ' 00:00:00';
		}
		if (!empty($postdata['dateTo'])) {
			$sql .= " AND thcv.COL_TIME_ON <= ?";
			$bindings[] = $postdata['dateTo'] . ' 23:59:59';
		}

		$sql .= " ORDER BY thcv.COL_SOTA_REF ASC, thcv.COL_TIME_ON ASC";

		$query = $this->db->query($sql, $bindings);

		$result = [];
		foreach ($query->result() as $row) {
			$isConfirmed = false;
			if (($postdata['qsl'] ?? 0) == 1 && $row->qsl) $isConfirmed = true;
			elseif (($postdata['lotw'] ?? 0) == 1 && $row->lotw) $isConfirmed = true;
			elseif (($postdata['eqsl'] ?? 0) == 1 && $row->eqsl) $isConfirmed = true;
			elseif (($postdata['qrz'] ?? 0) == 1 && $row->qrz) $isConfirmed = true;
			elseif (($postdata['clublog'] ?? 0) == 1 && $row->clublog) $isConfirmed = true;

			if ($isConfirmed && ($postdata['confirmed'] ?? null) != 1) continue;
			if (!$isConfirmed && ($postdata['worked'] ?? null) != 1) continue;

			$result[] = $row;
		}

		return $result;
	}

	// A summit is inactive when today falls outside [valid_from, valid_till].
	// A NULL bound means "no constraint" on that side, and the closing day
	// (today == valid_till) still counts as active.
	private function _inactive($valid_from, $valid_till) {
		$today = date('Y-m-d');
		if ($valid_from !== null && $today < $valid_from) {
			return true;
		}
		if ($valid_till !== null && $today > $valid_till) {
			return true;
		}
		return false;
	}
}

?>