<?php

namespace Depicter\Database\Repository;

use Averta\Core\Utility\Arr;
use Averta\Core\Utility\Data;
use Depicter\Database\Entity\Lead;
use Depicter\Database\Entity\LeadField;
use Depicter\Utility\Sanitize;
use TypeRocket\Database\SqlRaw;
use \TypeRocket\Utility\Arr as TypeRocketArr;

class LeadRepository
{

	/**
	 * @var Lead Lead
	 */
	private Lead $lead;

	/**
	 * @var LeadField
	 */
	private LeadField $leadField;


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

	/**
	 * Access to an instance of Lead entity
	 *
	 * @return Lead
	 */
	public function lead(): Lead{
		try{
			if( empty( $this->lead ) ){
				$this->lead = Lead::new();
			}
		} catch(\Exception $e){}

		return $this->lead;
	}

	/**
	 * Access to an instance of LeadField
	 *
	 * @return LeadField
	 */
	public function leadField(): LeadField{
		try{
			if( empty( $this->leadField ) ){
				$this->leadField = LeadField::new();
			}
		} catch(\Exception $e){}

		return $this->leadField;
	}

	/**
	 * Removes a lead or leads by ID(s)
	 *
	 * @param mixed $id  Can be an ID or list of comma separated IDs
	 *
	 * @return bool
	 * @throws \Exception
	 */
	public function delete( $id )
	{
		$succeed = false;

		if( is_array( $id ) ){
			$ids = $id;
		} elseif( false !== strpos( $id, ',' ) ){
			$ids = explode(',', $id );
		} else {
			$ids = [$id];
		}

		foreach( $ids as $id ){
			$id = Sanitize::int( $id );
			if( $lead = $this->lead()->findById( $id ) ){
				$lead->delete();
				\Depicter::leadFieldRepository()->deleteByLeadId( $id );
				$succeed = true;
			}
		}

		return $succeed;
	}

	/**
	 * Create a lead record
	 *
	 * @param int $sourceId    Document ID
	 * @param int $contentId   The ID of form or survey
	 * @param int $contentName The Name of form or survey
	 *
	 * @return mixed
	 * @throws \Exception
	 */
	public function create( $sourceId, $contentId, $contentName = '' ) {
		return $this->lead()->create([
			'source_id'    => $sourceId,
			'content_id'   => $contentId,
			'content_name' => $contentName,
			'created_at'   => $this->lead()->currentDateTime()
        ]);
	}

	/**
	 * Update a meta by relation, relation ID and meta key
	 *
	 * @param       $id
	 * @param array $fields
	 *
	 * @return mixed
	 * @throws \Exception
	 */
	public function update( $id, array $fields = [] ) {
		if ( empty( $fields ) ) {
			return false;
		}

		$lead =  $this->lead()->findById( $id );

		if ( $lead && $lead->count() ){
			return $lead->first()->update($fields);
		}

		return false;
	}

	/**
	 * Get meta value by relation, relation ID and meta key
	 *
	 * @param $id
	 *
	 * @return array
	 * @throws \Exception
	 */
	public function get( $id ): array{
		$lead = Lead::new()->findById($id)->get();

		return $lead ? $lead->first()->toArray() : [];
	}

	/**
	 * @throws \Exception
	 */
	public function getResults( $args = [] ){

		// ensure args are valid
		$this->ensureValidArgs( $args );

		// if
		if( ! $args['includeFields'] ){
			return $this->getLeadsResults( $args );
		}

		return $this->getJointResults( $args );
	}

	/**
	 * @throws \Exception
	 */
	protected function getJointResults( $args ){
		// apply query args and find lead ids
		$leads = $this->getLeadsResults( $args );

		// skip if no lead found
		if( empty( $leads['leads'] ) ){
			return [];
		}
		$leads = $leads['leads'];
		// filter found lead ids
		$foundLeadsIds = TypeRocketArr::pluck( $leads, 'id' );
		$args['ids'] = $foundLeadsIds;

		// find all stored field names for above lead ids
		$leadFieldNames = \Depicter::leadFieldRepository()->getFieldNamesByLeadId( $foundLeadsIds );
		$args['fieldNames'] = $leadFieldNames;

		// join fields with found lead records
		return $this->getAppendedFieldsToLeadResults( $args );
	}

	/**
	 * Retrieves leads results based on passed filters.
	 * Finds field values for a search term and returns corresponding lead results
	 *
	 * @param array $args     Filter options
	 *
	 * @return array
	 * @throws \Exception
	 */
	protected function getLeadsResults( $args ){
		// Purpose of joining tables is being able to search in leadField values as well
		$leadTable = $this->lead()->getTable();
		$leads = Lead::new()->select(
			"{$leadTable}.id",
			"{$leadTable}.source_id",
			"{$leadTable}.content_id",
			"{$leadTable}.content_name",
			"{$leadTable}.created_at",
			"lf.name as fieldName",
			"lf.value as fieldValue"
		)->join( "{$this->leadField()->getTable()} AS lf", "{$leadTable}.id", "=", "lf.lead_id" );

		if( ! empty( $args['dateStart'] ) ){
			$args['dateStart'] = $this->normalizeDateTime( $args['dateStart'], 'start' );
			$leads->where( "{$leadTable}.created_at", '>=', $args['dateStart'] );
		}

		if( ! empty( $args['dateEnd'] ) ){
			$args['dateEnd'] = $this->normalizeDateTime( $args['dateEnd'], 'end' );
			$leads->where( "{$leadTable}.created_at", '<=', $args['dateEnd'] );
		}

		if( ! empty( $args['sources'] ) ){
			$leads->where( "{$leadTable}.source_id", 'in', $args['sources'] );
		}

		if( ! empty( $args['s'] ) ){
			$search = "'%". $args['s'] ."%'";
			$leads->appendRawWhere('AND', "( lf.value like {$search} OR {$leadTable}.content_name like {$search} )");
		}

		if( ! empty( $args['orderBy'] ) && ! empty( $args['order'] ) ){
			// Resolve ordering against known columns only, never raw request input.
			list( $orderByColumn, $orderDirection ) = $this->resolveOrder( $args, $leadTable, [ 'fieldName', 'fieldValue' ] );
			$leads->orderBy( $orderByColumn, $orderDirection );
		}

		$leads = $leads->groupBy("{$leadTable}.id");
		$results = $this->paginate( $leads, $args );

		return $results['leads'] ? [
			'numberOfLeads' => $results['numberOfLeads'],
			'numberOfPages' => $results['numberOfPages'],
			'page' => $args['page'],
			'leads' => $results['leads']->toArray()
		] : [];
	}

	/**
	 * Normalize date to include time
	 *
	 * @param string $date
	 * @param string $type
	 *
	 * @return string
	 */
	public function normalizeDateTime( string $date, $type = 'start' ){
		if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $date)){
			return $date . ' ' . ($type === 'start' ? '00:00:00' : '23:59:59');
		}
		return $date;
	}


	/**
	 * Apply pagination if possible
	 *
	 * @param Lead $leads
	 * @param $args
	 *
	 * @return array|mixed
	 */
	protected function paginate( $leads, $args = [] ){
		$numberOfLeads = $leads ? $leads->count() : 0;
		if ( !empty( $args['perPage'] ) ) {
			$args['page'] = $args['page'] ?? 1;

			if ( $pager = $leads->paginate( $args['perPage'], $args['page'] ) ) {
				$leads = $pager->getResults();
			} else {
				$leads = [];
			}
		} else {
			$leads = $leads ? $leads->findAll()->get() : [];
		}

		return [
			'leads' => $leads,
			'numberOfPages' => !empty( $args['perPage'] ) ? ceil( $numberOfLeads / $args['perPage'] ) : 1,
			'numberOfLeads' => $numberOfLeads,
		];
	}

	/**
	 * Queries records of leads with specified fields
	 *
	 * @param $columns
	 *
	 * @return Lead
	 * @throws \Exception
	 */
	protected function select( $columns = [] ) {
		$entity  = Lead::new();
		$columns = !empty( $columns ) ? $columns : $entity->getTableColumns();
		return $entity->select( $columns );
	}

	/**
	 * Ensure input arguments are valid
	 *
	 * @param $args
	 */
	protected function ensureValidArgs( &$args ){
		// ensure $args['sources'] exists and is an array
		Arr::ensureItemIsArray( $args, 'sources', [] );

		// ensure $args['columns'] exists and is an array
		Arr::ensureItemIsArray( $args, 'columns', [] );

		// ensure $args['ids'] exists and is an array
		Arr::ensureItemIsArray( $args, 'ids', [] );

		// set defaults
		$args['order']   = $args['order']   ?? 'DESC';
		$args['orderBy'] = $args['orderBy'] ?? 'id';

		// whether to do a joint query for retrieving lead fields or not
		$args['includeFields'] = Data::isTrue( $args['includeFields'] ?? false );
		// whether to skip custom fields for just use know form fields
		$args['skipCustomFields'] = Data::isTrue( $args['skipCustomFields'] ?? false );

		// ensure valid column names are set
		if( empty( $args['columns'] ) ) {
			$args['columns'] = $this->lead()->getTableColumns();
		} else {
			// ensure valid column names
			$args['columns'] = array_intersect( $args['columns'], $this->lead()->getTableColumns() ) ;
		}
	}

	/**
	 * Builds and runs a joint query
	 *
	 * @param array $args  Filter arguments
	 *
	 * @return array
	 * @throws \Exception
	 */
	private function getAppendedFieldsToLeadResults( $args ){

		$LeadTableColumns = $args['columns'];
		$leadFieldTableColumns = $args['fieldNames'] ?? [];
		
		// drop custom lead fields from list of query columns
		if( $args['skipCustomFields'] ){
			$leadFieldTableColumns = array_intersect( $leadFieldTableColumns, LeadFieldRepository::KNOWN_FORM_FIELD_NAMES );
		}
		$leadFieldTableColumns = array_unique( $leadFieldTableColumns );

		// prefix lead columns for joint query
		$columns = array_map( function( $column ){
			return "l.{$column}";
		}, $LeadTableColumns );

		// prefix and add leadField columns for joint query.
		// Field names come from public form submissions, so they are never interpolated
		// into SQL: the compared value is bound and the alias is rebuilt from a safe
		// character set.
		$fieldAliases = [];
		foreach( $leadFieldTableColumns as $leadFieldTableColumn ){
			$alias = $this->fieldAlias( $leadFieldTableColumn );

			// drop unusable or colliding aliases instead of emitting broken SQL
			if( '' === $alias || isset( $fieldAliases[ $alias ] ) ){
				continue;
			}
			$fieldAliases[ $alias ] = true;

			// using sqlRaw to bypass 'tickSqlName' filter
			$columns[] = new SqlRaw( sprintf(
				"MAX(IF(lf.name = %s, lf.value, NULL)) AS `%s`",
				$this->quote( $leadFieldTableColumn ),
				$alias
			) );
		}

		$leads = Lead::new()->select( $columns )
			->as( 'l' )
		    ->join( "{$this->leadField()->getTable()} AS lf", "l.id", "=", "lf.lead_id" )
		    ->where( "l.id", 'IN', $args['ids'] );

		if( ! empty( $args['orderBy'] ) && ! empty( $args['order'] ) ){
			// Resolve ordering against lead columns and the aliases actually emitted above.
			list( $orderByColumn, $orderDirection ) = $this->resolveOrder( $args, 'l', array_keys( $fieldAliases ) );
			$leads->orderBy( $orderByColumn, $orderDirection );
		}

		$leads = $leads->groupBy("l.id")->get();

		return $leads ? $leads->toArray() : [];
	}

	/**
	 * Quotes a value for safe inclusion in a raw SQL fragment.
	 *
	 * Mirrors TypeRocket\Database\Query::prepareValue(). The placeholder escape is
	 * removed because the returned fragment is spliced into SQL that the query builder
	 * runs itself, rather than being passed back through $wpdb::prepare().
	 *
	 * @param mixed $value
	 *
	 * @return string
	 */
	private function quote( $value ): string {
		global $wpdb;

		return $wpdb->remove_placeholder_escape( $wpdb->prepare( '%s', (string) $value ) );
	}

	/**
	 * Builds a safe column alias out of an untrusted lead field name.
	 *
	 * Lead field names are supplied by visitors submitting a form, so only a known
	 * safe character set is allowed through. An empty result means the name cannot be
	 * represented as a column and should be skipped.
	 *
	 * @param mixed $name
	 *
	 * @return string
	 */
	private function fieldAlias( $name ): string {
		$alias = preg_replace( '/[^\p{L}\p{N}_\- ]/u', '', (string) $name );

		// preg_replace() returns null on malformed UTF-8, fall back to an ASCII only pass
		if( null === $alias ){
			$alias = preg_replace( '/[^A-Za-z0-9_\- ]/', '', (string) $name );
		}

		return trim( (string) $alias );
	}

	/**
	 * Resolves order by / order arguments against a list of permitted columns.
	 *
	 * Anything unrecognised falls back to the lead ID, so request input never reaches
	 * the ORDER BY clause verbatim.
	 *
	 * @param array  $args          Filter arguments
	 * @param string $tableAlias    Table name or alias holding the lead columns
	 * @param array  $extraAllowed  Additional select aliases that may be ordered by
	 *
	 * @return array  [ column, direction ]
	 */
	private function resolveOrder( array $args, string $tableAlias, array $extraAllowed = [] ): array {
		$direction = 'ASC' === strtoupper( (string) ( $args['order'] ?? '' ) ) ? 'ASC' : 'DESC';
		$orderBy   = (string) ( $args['orderBy'] ?? '' );

		if( in_array( $orderBy, $this->lead()->getTableColumns(), true ) ){
			return [ "{$tableAlias}.{$orderBy}", $direction ];
		}

		// aliases already use a safe character set, but may contain characters that
		// tickSqlName() would strip, so quote them explicitly
		if( in_array( $orderBy, $extraAllowed, true ) ){
			return [ new SqlRaw( '`' . $orderBy . '`' ), $direction ];
		}

		return [ "{$tableAlias}.id", $direction ];
	}

}