<?php
/**
 * CustomTables Joomla! 3.x/4.x/5.x/6.x Component and WordPress 6.x Plugin
 * @package Custom Tables
 * @author Ivan Komlev <support@joomlaboat.com>
 * @link https://joomlaboat.com
 * @copyright (C) 2018-2026. Ivan Komlev
 * @license GNU/GPL Version 2 or later - https://www.gnu.org/licenses/gpl-2.0.html
 **/

namespace CustomTables;

// No direct access to this file
if (!defined('ABSPATH')) exit;

use Exception;

class ImportCSV
{
	/**
	 * @throws Exception
	 * @since 3.2.2
	 */
	public static function importCSVFile($filename, $ct_tableid, bool $sample = false): array
	{
		self::deleteOldTempCsvFiles(CUSTOMTABLES_TEMP_PATH);

		if (file_exists($filename)) {
			try {

				$separator = common::inputGetCmd('separator', null);

				if ($separator == 'comma')
					$separator = ",";
				elseif ($separator == 'semicolon')
					$separator = ";";
				elseif ($separator == 'tab')
					$separator = "\t";
				elseif ($separator == 'space')
					$separator = " ";

				$enclosure = common::inputGetCmd('enclosure', '"');
				if ($enclosure == 'apostrophe')
					$enclosure = '\'';
				else
					$enclosure = '"';

				$start_from = common::inputGetInt('start_from', 1);
				$update_insert = common::inputGetCmd('update_insert', 'insert_match_id');

				if ($sample)
					return self::importCSVdataSample($filename, $ct_tableid, $separator, $enclosure, $start_from, $update_insert);
				else
					return self::importCSVdata($filename, $ct_tableid, $separator ?? ',', $enclosure, $start_from, $update_insert);
			} catch (Exception $e) {
				throw new Exception($e->getMessage());
			}
		} else
			throw new Exception(esc_html__("File not found", "customtables"));
	}

	static protected function deleteOldTempCsvFiles(string $dir): void
	{
		$files = glob($dir . '/ct_*_*.csv');
		$now = time();

		foreach ($files as $file) {
			if (is_file($file) && ($now - filemtime($file)) > 3600) {
				echo 'to delete: ' . $file;

				unlink($file);
				die;
			}
		}
	}

	/**
	 * @throws Exception
	 * @since 3.2.2
	 */
	private static function importCSVDataSample(string $filename, int $ct_tableid, ?string $separator, string $enclosure, int $start_from, string $update_insert): array
	{
		if ($separator === null) {
			$separator = self::detectDelimiter($filename);
			if ($separator === null)
				$separator = ',';
		}

		$arrayOfLines = self::getLines($filename, $separator, $enclosure);

		if ($arrayOfLines === null)
			throw new Exception(esc_html__("CSV file is empty.", "customtables"));

		$ct = new CT([], true);
		$ct->getTable($ct_tableid);
		$line = $arrayOfLines[0];
		$prepareFieldList = self::prepareFieldList($line, $ct->Table->fields);
		$fieldList = $prepareFieldList['fieldList'];
		//$fields = self::processFieldParams($fieldList, $ct->Table->fields);//return associative array

		$fieldNames = [];
		foreach ($fieldList as $field) {
			if ($field == -2)
				$fieldNames[] = null;
			elseif ($field == -1)
				$fieldNames[] = '_id';
			elseif ($field > -1)
				$fieldNames[] = $ct->Table->fields[$field]['fieldname'];
		}

		$recordSample = [];
		$offset = 0;

		$count = 0;
		for ($i = $offset; $i < count($arrayOfLines); $i++) {
			if (count($arrayOfLines[$i]) > 0) {
				//$result = self::prepareSQLQuery($ct, $fieldList, $fields, $arrayOfLines[$i]);
				$recordSample[] = $arrayOfLines[$i];
				$count += 1;
				if ($count >= 20)
					break;
			}
		}

		if ($separator == ';')
			$separator_id = "semicolon";
		elseif ($separator == "\t")
			$separator_id = 'tab';
		elseif ($separator == ' ')
			$separator_id = "space";
		else
			$separator_id = "comma";

		return ['fields' => $fieldNames, 'records' => $recordSample, 'start_from' => ($prepareFieldList['header'] ? 2 : 0), 'separator' => $separator_id];
	}

	//https://stackoverflow.com/questions/26717462/php-best-approach-to-detect-csv-delimiter/59581170

	private static function detectDelimiter($csvFile, string $enclosure = '"'): ?string
	{
		//first line is a list of field name, so this approach is ok here
		$separators = [";" => 0, "," => 0, "\t" => 0, "|" => 0];
		$escape = '\\';

		$handle = fopen($csvFile, "r");
		$firstLine = fgets($handle);
		fclose($handle);
		foreach ($separators as $separator => &$count) {
			$count = count(str_getcsv($firstLine, $separator, $enclosure, $escape));
		}

		if ($count == 0)
			return null;

		return array_search(max($separators), $separators);
	}

	private static function getLines($filename, ?string $separator, string $enclosure = '"', string $escape = '\\'): ?array
	{
		if (($handle = fopen($filename, "r")) !== FALSE) {
			$lines = [];

			while (($data = fgetcsv($handle, 0, $separator, $enclosure, $escape)) !== FALSE)
				$lines[] = $data;

			fclose($handle);
			return $lines;
		}
		return null;
	}

	private static function prepareFieldList(array $fieldNames, array $fields): array
	{
		$fieldList = array();
		$fieldsFoundCount = 0;

		foreach ($fieldNames as $fieldName_raw) {

			$fieldName_ = self::removeBomUtf8($fieldName_raw);

			if ($fieldName_ == '#') {
				$fieldList[] = -1;//id field
				$fieldsFoundCount += 1;
				$found = true;
			} else {
				$index = 0;
				$fieldName = strtolower(preg_replace("/[^a-zA-Z1-9]/", "", $fieldName_));
				$found = false;

				foreach ($fields as $field) {
					if ((int)$field['published'] === 1) {
						$clean_field_name = strtolower(preg_replace("/[^a-zA-Z1-9]/", "", $field['fieldtitle']));

						if ($clean_field_name == $fieldName or (string)$field['fieldname'] == $fieldName or (string)$field['fieldtitle'] == $fieldName) {
							$fieldList[] = $index;
							$fieldsFoundCount += 1;
							$found = true;
							break;
						}
					}
					$index++;
				}
			}

			if (!$found)
				$fieldList[] = -2;
		}
		return ['fieldList' => $fieldList, 'header' => $fieldsFoundCount > 0];
	}

	private static function removeBomUtf8($s): string
	{
		if (substr($s, 0, 3) == chr(hexdec('EF')) . chr(hexdec('BB')) . chr(hexdec('BF'))) {
			return substr($s, 3);
		} else {
			if (substr($s, 0, 2) == chr(hexdec('FF')) . chr(hexdec('FE')))
				return substr($s, 2);
			return $s;
		}
	}

	private static function importCSVData(string $filename, int $ct_tableid, string $separator, string $enclosure, int $start_from, string $update_insert): array
	{
		if ($separator === null) {
			$separator = self::detectDelimiter($filename);
			if ($separator === null)
				$separator = '"';
		}

		$arrayOfLines = self::getLines($filename, $separator, $enclosure);

		if ($arrayOfLines === null)
			throw new Exception(esc_html__("CSV file is empty.", "customtables"));

		$ct = new CT([], true);
		$ct->getTable($ct_tableid);
		$line = $arrayOfLines[0];
		$prepareFieldList = self::prepareFieldList($line, $ct->Table->fields);
		$fieldList = $prepareFieldList['fieldList'];
		$fields = self::processFieldParams($fieldList, $ct->Table->fields);//return associative array

		$new_fieldList = [];

		$index = 0;
		foreach ($fieldList as $field) {
			$control_name = 'field_map_' . $index;
			$f = common::inputGetCmd($control_name, '');

			if (empty($f)) {
				$new_fieldList[] = $field;
			} else {
				if ($f == '_id')
					$new_fieldList[] = -1;
				elseif ($f == '_ignore')
					$new_fieldList[] = -2;
				else {
					$p = 0;
					foreach ($ct->Table->fields as $fieldRow) {
						if ($fieldRow['fieldname'] == $f)
							$new_fieldList[] = $p;

						$p += 1;
					}
				}
			}

			$index += 1;
		}

		$fieldList = $new_fieldList;

		if ($prepareFieldList['header'])
			$offset = 1;
		else
			$offset = 0;

		for ($i = $offset; $i < count($arrayOfLines); $i++) {
			if (count($arrayOfLines[$i]) > 0) {
				$result = self::prepareSQLQuery($ct, $fieldList, $fields, $arrayOfLines[$i], $update_insert);

				if ($update_insert == 'insert_ignore_id') {
					$result->data[$ct->Table->realidfieldname] = null;
					$listing_id = null;
				} else {
					if ($update_insert == 'insert_match_id' and empty($result->data[$ct->Table->realidfieldname])) {
						$listing_id = null;
					} else {
						$listing_id = self::findRecord($ct->Table->realtablename, $ct->Table->realidfieldname, $ct->Table->published_field_found, $result->where);
					}
				}

				if (count($result->data) > 0) {
					try {
						if (is_null($listing_id))
							database::insert($ct->Table->realtablename, $result->data);
						else
							database::update($ct->Table->realtablename, $result->data, $result->where);

					} catch (Exception $e) {
						throw new Exception($e->getMessage());
					}
				}
			}
		}
		return [];
	}

	/**
	 * @throws Exception
	 * @since 3.2.2
	 */
	private static function processFieldParams(array $fieldList, array $fields): array
	{
		foreach ($fieldList as $f_index) {
			if ($f_index >= 0) {
				$fieldType = $fields[$f_index]['type'];
				if ($fieldType == 'sqljoin' or $fieldType == 'records') {

					$type_params = CTMiscHelper::csv_explode(',', $fields[$f_index]['typeparams']);

					$tableName = $type_params[0];
					$fieldName = $type_params[1];

					$ct = new CT([], true);
					$ct->getTable($tableName);

					if (!$ct->Table) {
						echo common::ctJsonEncode(['error' => 'sqljoin field(' . $fields[$f_index]['fieldtitle'] . ') table not found']);
						die;//Import CSV field error
					}

					$SQJJoinField = $ct->Table->getFieldByName($fieldName);

					$fields[$f_index]['sqljoin'] = (object)[
						'table' => $ct->Table->realtablename,
						'field' => $SQJJoinField['realfieldname'],
						'realidfieldname' => $ct->Table->realidfieldname,
						'published_field_found' => $ct->Table->published_field_found];
				}
			}
		}
		return $fields;
	}

	/**
	 * @throws Exception
	 * @since 3.2.2
	 */
	private static function prepareSQLQuery(CT $ct, array $fieldList, array $fields, array $line, string $update_insert): object
	{
		$data = [];
		$whereClause = new MySQLWhereClause();

		$i = 0;

		$idFieldIncluded = false;
		if (in_array(-1, $fieldList))
			$idFieldIncluded = true;

		foreach ($fieldList as $f_index) {
			if ($f_index >= 0) {
				$fieldType = $fields[$f_index]['type'];
				$fieldParamsString = $fields[$f_index]['typeparams'];
				$fieldParams = CTMiscHelper::csv_explode(',', $fieldParamsString);

				if ($fieldType == 'sqljoin') {

					if (isset($fields[$f_index]['sqljoin'])) {
						$realtablename = $fields[$f_index]['sqljoin']->table;

						$vlu = self::findSQLJoin(
							$realtablename,
							$fields[$f_index]['sqljoin']->field,
							$fields[$f_index]['sqljoin']->realidfieldname,
							(bool)$fields[$f_index]['sqljoin']->published_field_found,
							$line[$i]);

						if (is_null($vlu))//Join table record doesn't exist
						{
							database::insert($realtablename, [$fields[$f_index]['sqljoin']->field => $line[$i]]);

							$vlu = self::findSQLJoin(
								$realtablename,
								$fields[$f_index]['sqljoin']->field,
								$fields[$f_index]['sqljoin']->realidfieldname,
								(bool)$fields[$f_index]['sqljoin']->published_field_found,
								$line[$i]);
						}

						if ((int)$vlu > 0) {
							if (!$idFieldIncluded and $i == 0 and $update_insert == 'insert_match_column')
								$whereClause->addCondition($fields[$f_index]['realfieldname'], (int)$vlu);

							$data[$fields[$f_index]['realfieldname']] = (int)$vlu;
						} else {
							if (!$idFieldIncluded and $i == 0 and $update_insert == 'insert_match_column')
								$whereClause->addCondition($fields[$f_index]['realfieldname'], null);

							$data[$fields[$f_index]['realfieldname']] = null;
						}
					}
				} elseif ($fieldType == 'records') {

					if (isset($fields[$f_index]['sqljoin'])) {
						$realtablename = $fields[$f_index]['sqljoin']->table;

						$vlu = self::findSQLRecordJoin(
							$realtablename,
							$fields[$f_index]['sqljoin']->field,
							$fields[$f_index]['sqljoin']->realidfieldname,
							(bool)$fields[$f_index]['sqljoin']->published_field_found,
							$line[$i]);

						if (is_null($vlu)) {

							database::insert($realtablename, [$fields[$f_index]['sqljoin']->field => $line[$i]]);

							$vlu = self::findSQLRecordJoin(
								$realtablename,
								$fields[$f_index]['sqljoin']->field,
								$fields[$f_index]['sqljoin']->realidfieldname,
								(bool)$fields[$f_index]['sqljoin']->published_field_found,
								$line[$i]);
						}

						if (!is_null($vlu) and $vlu != '') {

							if (!$idFieldIncluded and $i == 0 and $update_insert == 'insert_match_column')
								$whereClause->addCondition($fields[$f_index]['realfieldname'], '%,' . implode(',', $vlu) . ',%', 'LIKE');

							$data[$fields[$f_index]['realfieldname']] = ',' . implode(',', $vlu) . ',';
						} else {

							if (!$idFieldIncluded and $i == 0 and $update_insert == 'insert_match_column')
								$whereClause->addCondition($fields[$f_index]['realfieldname'], null);

							$data[$fields[$f_index]['realfieldname']] = null;
						}
					}
				} elseif ($fieldType == 'date' or $fieldType == 'creationtime' or $fieldType == 'changetime') {
					if (isset($line[$i]) and $line[$i] != '') {

						$dateString = CTMiscHelper::standardizeDate($line[$i]);

						if (!$idFieldIncluded and $i == 0 and $update_insert == 'insert_match_column')
							$whereClause->addCondition($fields[$f_index]['realfieldname'], $dateString);

						$data[$fields[$f_index]['realfieldname']] = $dateString;
					} else {

						if (!$idFieldIncluded and $i == 0 and $update_insert == 'insert_match_column')
							$whereClause->addCondition($fields[$f_index]['realfieldname'], null);

						$data[$fields[$f_index]['realfieldname']] = null;
					}
				} elseif ($fieldType == 'int' or $fieldType == 'user' or $fieldType == 'userid') {
					if (isset($line[$i]) and $line[$i] != '') {

						if (!$idFieldIncluded)
							$whereClause->addCondition($fields[$f_index]['realfieldname'], (int)$line[$i]);

						$data[$fields[$f_index]['realfieldname']] = (int)$line[$i];
					} else {

						if (!$idFieldIncluded)
							$whereClause->addCondition($fields[$f_index]['realfieldname'], null);

						$data[$fields[$f_index]['realfieldname']] = null;
					}
				} elseif ($fieldType == 'float') {
					if (isset($line[$i]) and $line[$i] != '') {

						if (!$idFieldIncluded and $i == 0 and $update_insert == 'insert_match_column')
							$whereClause->addCondition($fields[$f_index]['realfieldname'], (float)$line[$i]);

						$data[$fields[$f_index]['realfieldname']] = (float)$line[$i];
					} else {

						if (!$idFieldIncluded and $i == 0 and $update_insert == 'insert_match_column')
							$whereClause->addCondition($fields[$f_index]['realfieldname'], null);

						$data[$fields[$f_index]['realfieldname']] = null;
					}
				} elseif ($fieldType == 'checkbox') {
					if (isset($line[$i]) and $line[$i] != '') {
						if ($line[$i] == 'Yes' or $line[$i] == '1')
							$vlu = 1;
						else
							$vlu = 0;

						if (!$idFieldIncluded and $i == 0 and $update_insert == 'insert_match_column')
							$whereClause->addCondition($fields[$f_index]['realfieldname'], $vlu);

						$data[$fields[$f_index]['realfieldname']] = $vlu;
					}
				} elseif ($fieldType == 'time') {
					$path = CUSTOMTABLES_LIBRARIES_PATH . DIRECTORY_SEPARATOR . 'customtables' . DIRECTORY_SEPARATOR . 'html' . DIRECTORY_SEPARATOR . 'inputbox' . DIRECTORY_SEPARATOR;
					require_once($path . 'time.php');

					$seconds = InputBox_Time::formattedTime2Seconds($line[$i]);
					$ticks = InputBox_Time::seconds2Ticks($seconds, $fieldParams);

					if (!$idFieldIncluded and $i == 0 and $update_insert == 'insert_match_column')
						$whereClause->addCondition($fields[$f_index]['realfieldname'], $ticks);

					$data[$fields[$f_index]['realfieldname']] = $ticks;

				} else {

					if (isset($line[$i])) {
						$vlu = $line[$i];

						if (!$idFieldIncluded and $i == 0 and $update_insert == 'insert_match_column')
							$whereClause->addCondition($fields[$f_index]['realfieldname'], $vlu);

						$data[$fields[$f_index]['realfieldname']] = $vlu;
					}
				}
			} elseif ($f_index == -1) {
				if (isset($line[$i])) {
					$vlu = $line[$i];

					if ($update_insert == 'insert_match_id')
						$whereClause->addCondition($ct->Table->realidfieldname, $vlu);

					if ($update_insert !== 'insert_ignore_id')
						$data[$ct->Table->realidfieldname] = $vlu;
				}
			}
			$i++;
		}

		return (object)['data' => $data, 'where' => $whereClause];
	}

	/**
	 * @throws Exception
	 * @since 3.2.2
	 */
	private static function findSQLJoin($realtablename, $join_realfieldname, $realidfieldname, bool $published_field_found, $vlu)
	{
		//TODO method not found
		$whereClause = new MySQLWhereClause();
		$whereClause->addCondition($join_realfieldname, $vlu);
		return self::findRecord($realtablename, $realidfieldname, $published_field_found, $whereClause);
	}

	/**
	 * @throws Exception
	 * @since 3.2.2
	 */
	private static function findRecord($realtablename, $realidfieldname, bool $published_field_found, MySQLWhereClause $whereClause)
	{
		if ($published_field_found)
			$whereClause->addCondition('published', 1);

		$rows = database::loadAssocList($realtablename, [$realidfieldname], $whereClause, null, null, 1);

		if (count($rows) == 0)
			return null;

		return $rows[0][$realidfieldname];
	}

	/**
	 * @throws Exception
	 * @since 3.2.2
	 */
	private static function findSQLRecordJoin($realtablename, $join_realfieldname, $realidfieldname, bool $published_field_found, $values_str): ?array
	{
		$whereClause = new MySQLWhereClause();
		$values = explode(',', $values_str);

		foreach ($values as $vlu)
			$whereClause->addOrCondition($join_realfieldname, $vlu);


		if ($published_field_found)
			$whereClause->addCondition('published', 1);

		$rows = database::loadAssocList($realtablename, [$realidfieldname], $whereClause);

		if (count($rows) == 0)
			return null;

		$listing_ids = array();
		foreach ($rows as $row)
			$listing_ids[] = $row[$realidfieldname];

		return $listing_ids;
	}
}