<?php
/**
 * Lead storage.
 *
 * @package Dealroot
 */

namespace Dealroot;

defined( 'ABSPATH' ) || exit;

/**
 * Data access for the leads table.
 */
class Leads {

	const STATUSES = array( 'new', 'contacted', 'qualified', 'won', 'lost', 'spam' );

	/**
	 * Counts per status already asked for in this request, forgotten when a lead changes.
	 *
	 * @var array<string, array<string, int>>
	 */
	private static $counted = array();

	/**
	 * Translated status labels.
	 *
	 * @return array
	 */
	public static function status_labels() {
		return array(
			'new'       => __( 'New', 'dealroot' ),
			'contacted' => __( 'Contacted', 'dealroot' ),
			'qualified' => __( 'Qualified', 'dealroot' ),
			'won'       => __( 'Won', 'dealroot' ),
			'lost'      => __( 'Lost', 'dealroot' ),
			'spam'      => __( 'Spam', 'dealroot' ),
		);
	}

	/**
	 * Inserts a lead.
	 *
	 * @param array $data Column values.
	 * @return int Lead ID, 0 on failure.
	 */
	public static function insert( array $data ) {
		global $wpdb;

		self::$counted = array();

		$now      = current_time( 'mysql', true );
		$defaults = array(
			'created_at'    => $now,
			'updated_at'    => $now,
			'form_plugin'   => '',
			'form_id'       => '',
			'form_name'     => '',
			'name'          => '',
			'email'         => '',
			'phone'         => '',
			'fields'        => '[]',
			'status'        => 'new',
			'value'         => null,
			'currency'      => '',
			'won_at'        => null,
			'note'          => '',
			'channel'       => '',
			'source'        => '',
			'medium'        => '',
			'campaign'      => '',
			'last_channel'  => '',
			'last_source'   => '',
			'last_medium'   => '',
			'last_campaign' => '',
			'gclid'         => '',
			'gbraid'        => '',
			'wbraid'        => '',
			'fbclid'        => '',
			'msclkid'       => '',
			'landing_page'  => '',
			'attribution'   => '',
			'clarity'       => '',
			'device'        => '',
			'browser'       => '',
			'country'       => '',
			'city'          => '',
			'lat'           => 0,
			'lon'           => 0,
			'geo'           => '',
		);

		$row = array_intersect_key( wp_parse_args( $data, $defaults ), $defaults );

		$inserted = $wpdb->insert( Installer::table(), $row ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$id       = $inserted ? (int) $wpdb->insert_id : 0;

		// A lead saved as won or qualified, e.g. a paid order, reached that step on being saved:
		// conversions and notices follow it as after a change of status, once it is announced.
		if ( $id && in_array( $row['status'], array( 'contacted', 'qualified', 'won', 'lost' ), true ) ) {
			self::$reached[ $id ] = $row['status'];
			add_action( 'dealroot_lead_created', array( __CLASS__, 'reached' ), 100 );
		}

		return $id;
	}

	/**
	 * Statuses of leads saved in this request with a status beyond new.
	 *
	 * @var array<int, string>
	 */
	private static $reached = array();

	/**
	 * Tells the status of a lead saved as won, qualified or the like, after it was announced.
	 *
	 * @param int $lead_id Lead ID.
	 */
	public static function reached( $lead_id ) {
		$lead_id = (int) $lead_id;

		if ( isset( self::$reached[ $lead_id ] ) ) {
			$status = self::$reached[ $lead_id ];
			unset( self::$reached[ $lead_id ] );

			/** This action is documented in includes/class-leads.php */
			do_action( 'dealroot_lead_status_changed', $lead_id, $status, 'new' );
		}
	}

	/**
	 * Fetches one lead.
	 *
	 * @param int $id Lead ID.
	 * @return object|null
	 */
	public static function get( $id ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', Installer::table(), $id ) );
	}

	/**
	 * Updates status, value and note.
	 *
	 * @param int   $id      Lead ID.
	 * @param array $changes Allowed keys: status, value, currency, note.
	 * @return bool
	 */
	public static function update( $id, array $changes ) {
		global $wpdb;

		self::$counted = array();

		$lead = self::get( $id );
		if ( ! $lead ) {
			return false;
		}

		$row = array( 'updated_at' => current_time( 'mysql', true ) );

		if ( isset( $changes['status'] ) && in_array( $changes['status'], self::STATUSES, true ) ) {
			$row['status'] = $changes['status'];

			// First reaction to a new enquiry, used for the response time.
			if ( 'new' === $lead->status && ! in_array( $changes['status'], array( 'new', 'spam' ), true ) && empty( $lead->contacted_at ) ) {
				$row['contacted_at'] = $row['updated_at'];
			}

			if ( 'won' === $changes['status'] && 'won' !== $lead->status ) {
				$row['won_at'] = $row['updated_at'];
			} elseif ( 'won' !== $changes['status'] ) {
				$row['won_at'] = null;
			}
		}

		if ( isset( $changes['lost_reason'] ) && ( '' === $changes['lost_reason'] || in_array( $changes['lost_reason'], Lost_Reasons::REASONS, true ) ) ) {
			$row['lost_reason'] = $changes['lost_reason'];
		}

		// A deal that is no longer lost has no reason for it.
		if ( isset( $row['status'] ) && 'lost' !== $row['status'] ) {
			$row['lost_reason'] = '';
		}

		if ( array_key_exists( 'value', $changes ) ) {
			$row['value'] = ( null === $changes['value'] || '' === $changes['value'] ) ? null : round( (float) $changes['value'], 2 );
		}

		if ( isset( $changes['currency'] ) ) {
			$row['currency'] = Settings::sanitize_currency( $changes['currency'] );
		}

		if ( isset( $changes['note'] ) ) {
			$row['note'] = sanitize_textarea_field( $changes['note'] );
		}

		foreach ( array( 'name', 'phone', 'source', 'medium', 'campaign' ) as $key ) {
			if ( isset( $changes[ $key ] ) ) {
				$row[ $key ] = mb_substr( sanitize_text_field( $changes[ $key ] ), 0, 'phone' === $key ? 64 : 191 );
			}
		}

		if ( isset( $changes['email'] ) ) {
			$row['email'] = mb_substr( sanitize_email( $changes['email'] ), 0, 191 );
		}

		if ( isset( $changes['channel'] ) && array_key_exists( $changes['channel'], Attribution::channel_labels() ) ) {
			$row['channel'] = $changes['channel'];
		}

		$updated = false !== $wpdb->update( Installer::table(), $row, array( 'id' => (int) $id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

		if ( $updated && isset( $row['status'] ) && $row['status'] !== $lead->status ) {
			/**
			 * Fires after a lead changes status.
			 *
			 * @param int    $id         Lead ID.
			 * @param string $new_status New status.
			 * @param string $old_status Previous status.
			 */
			do_action( 'dealroot_lead_status_changed', (int) $id, $row['status'], $lead->status );
		}

		return $updated;
	}

	/**
	 * Stores the share of the deal value that is profit, e.g. 0.3 for 30 %. Leaves the update
	 * time alone, as nothing about the lead itself changed.
	 *
	 * @param int        $id     Lead ID.
	 * @param float|null $margin Margin, null when unknown.
	 * @return bool
	 */
	public static function set_margin( $id, $margin ) {
		global $wpdb;

		$margin = null === $margin ? null : round( max( -1, min( 1, (float) $margin ) ), 4 );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return false !== $wpdb->update( Installer::table(), array( 'margin' => $margin ), array( 'id' => (int) $id ), array( '%f' ), array( '%d' ) );
	}

	/**
	 * Deletes leads.
	 *
	 * @param int[] $ids Lead IDs.
	 * @return int Rows deleted.
	 */
	public static function delete( array $ids ) {
		global $wpdb;

		self::$counted = array();

		$ids = array_filter( array_map( 'absint', $ids ) );
		if ( ! $ids ) {
			return 0;
		}

		Messages::delete_for( $ids );

		/**
		 * Fires before leads are deleted, so add-ons delete what belongs to them, e.g. recordings.
		 *
		 * @param int[] $ids Lead IDs.
		 */
		do_action( 'dealroot_leads_deleted', $ids );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (int) $wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE id IN (' . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ')', array_merge( array( Installer::table() ), $ids ) ) );
	}

	/**
	 * Paginated, filtered list.
	 *
	 * @param array $args status, channel, search, ids, orderby, order, per_page, page.
	 * @return array{items: array, total: int}
	 */
	public static function query( array $args ) {
		global $wpdb;

		$args = wp_parse_args(
			$args,
			array(
				'status'   => '',
				'channel'  => null,
				'search'   => '',
				'from'     => '',
				'to'       => '',
				'column'   => '',
				'value'    => null,
				'no_value' => false,
				'orderby'  => 'created_at',
				'order'    => 'DESC',
				'per_page' => 20,
				'page'     => 1,
			)
		);

		list( $where, $params ) = self::where( $args );

		$orderby = in_array( $args['orderby'], array( 'created_at', 'name', 'status', 'value', 'channel' ), true ) ? $args['orderby'] : 'created_at';
		$order   = 'ASC' === strtoupper( $args['order'] ) ? 'ASC' : 'DESC';
		$limit   = max( 1, (int) $args['per_page'] );
		$offset  = ( max( 1, (int) $args['page'] ) - 1 ) * $limit;
		$table   = Installer::table();

		// Newest first: who wrote again comes first, like a new enquiry.
		$first = 'created_at' === $orderby && 'DESC' === $order ? 'unread DESC, ' : '';

		// The conditions from where() contain placeholders only, the values are in $params.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$total = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE {$where}", array_merge( array( $table ), $params ) ) );

		$items = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM %i WHERE {$where} ORDER BY {$first}%i {$order}, id {$order} LIMIT %d OFFSET %d",
				array_merge( array( $table ), $params, array( $orderby, $limit, $offset ) )
			)
		);
		// phpcs:enable

		return array(
			'items' => $items ? $items : array(),
			'total' => $total,
		);
	}

	/**
	 * Counts per status for the list view tabs.
	 *
	 * @param array $args Same filters as query(), status is ignored.
	 * @return array
	 */
	public static function status_counts( array $args = array() ) {
		global $wpdb;

		$known                  = &self::$counted;
		$args['status']         = 'any';
		list( $where, $params ) = self::where( $args );
		$key                    = md5( $where . '|' . implode( '|', $params ) );

		// The menu and the navigation both ask on each screen.
		if ( isset( $known[ $key ] ) ) {
			return $known[ $key ];
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Placeholders only, see where().
		$rows = $wpdb->get_results( $wpdb->prepare( "SELECT status, COUNT(*) AS total FROM %i WHERE {$where} GROUP BY status", array_merge( array( Installer::table() ), $params ) ) );

		$counts = array_fill_keys( self::STATUSES, 0 );
		foreach ( (array) $rows as $row ) {
			$counts[ $row->status ] = (int) $row->total;
		}

		$known[ $key ] = $counts;

		return $counts;
	}

	/**
	 * Other leads from the same person, matched by email or phone.
	 *
	 * @param object $lead Lead.
	 * @return array
	 */
	public static function related( $lead ) {
		global $wpdb;

		if ( '' === $lead->email && '' === $lead->phone ) {
			return array();
		}

		// NULLIF() keeps an empty email or phone from matching every lead without one.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (array) $wpdb->get_results(
			$wpdb->prepare(
				"SELECT id, created_at, form_name, status, channel, value, currency FROM %i
				WHERE (email = NULLIF(%s, '') OR phone = NULLIF(%s, '')) AND id <> %d
				ORDER BY created_at DESC LIMIT 20",
				Installer::table(),
				$lead->email,
				$lead->phone,
				(int) $lead->id
			)
		);
	}

	/**
	 * How many leads each email address has, for the given emails.
	 *
	 * @param string[] $emails Email addresses.
	 * @return array Email => count.
	 */
	public static function counts_by_email( array $emails ) {
		global $wpdb;

		$emails = array_values( array_unique( array_filter( $emails ) ) );
		if ( ! $emails ) {
			return array();
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT email, COUNT(*) AS total FROM %i WHERE email IN (' . implode( ',', array_fill( 0, count( $emails ), '%s' ) ) . ") AND status <> 'spam' GROUP BY email", array_merge( array( Installer::table() ), $emails ) ) );

		return wp_list_pluck( (array) $rows, 'total', 'email' );
	}

	/**
	 * Leads per form, without spam.
	 *
	 * @return array<string, int> "plugin:form ID" => count.
	 */
	public static function counts_by_form() {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$rows   = $wpdb->get_results( $wpdb->prepare( "SELECT form_plugin, form_id, COUNT(*) AS total FROM %i WHERE status <> 'spam' GROUP BY form_plugin, form_id", Installer::table() ) );
		$counts = array();

		foreach ( (array) $rows as $row ) {
			$counts[ $row->form_plugin . ':' . $row->form_id ] = (int) $row->total;
		}

		return $counts;
	}

	/**
	 * Leads for one email address, used by the privacy tools.
	 *
	 * @param string $email Email address.
	 * @return array
	 */
	public static function by_email( $email ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (array) $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE email = %s', Installer::table(), $email ) );
	}

	/**
	 * Removes leads older than the retention window.
	 *
	 * @param int $days Age in days.
	 * @return int Rows deleted.
	 */
	public static function purge_older_than( $days ) {
		global $wpdb;

		if ( $days < 1 ) {
			return 0;
		}

		$cutoff = gmdate( 'Y-m-d H:i:s', time() - (int) $days * DAY_IN_SECONDS );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (int) $wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE created_at < %s', Installer::table(), $cutoff ) );
	}

	/**
	 * Builds the WHERE clause for query().
	 *
	 * @param array $args Query args.
	 * @return array{0: string, 1: array}
	 */
	private static function where( array $args ) {
		global $wpdb;

		$args = wp_parse_args(
			$args,
			array(
				'status'   => '',
				'channel'  => null,
				'search'   => '',
				'from'     => '',
				'to'       => '',
				'column'   => '',
				'value'    => null,
				'no_value' => false,
				'closed'   => '',
				'ids'      => null,
			)
		);

		$where  = array( '1=1' );
		$params = array();

		// Won or lost since a time, e.g. the closed deals of the last weeks on the board.
		if ( '' !== $args['closed'] ) {
			$where[]  = 'COALESCE(won_at, updated_at) >= %s';
			$params[] = $args['closed'];
		}

		// Only these leads, e.g. those of one person in the team. An empty list matches none.
		if ( is_array( $args['ids'] ) ) {
			$ids     = array_values( array_filter( array_map( 'absint', $args['ids'] ) ) );
			$where[] = $ids ? 'id IN (' . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ')' : '1=0';
			$params  = array_merge( $params, $ids );
		}

		if ( in_array( $args['status'], self::STATUSES, true ) ) {
			$where[]  = 'status = %s';
			$params[] = $args['status'];
		} elseif ( 'any' !== $args['status'] ) {
			$where[] = "status <> 'spam'";
		}

		if ( null !== $args['channel'] && array_key_exists( $args['channel'], Attribution::channel_labels() ) ) {
			$where[]  = 'channel = %s';
			$params[] = $args['channel'];
		}

		if ( '' !== $args['from'] ) {
			$where[]  = 'created_at >= %s';
			$params[] = $args['from'];
		}

		if ( '' !== $args['to'] ) {
			$where[]  = 'created_at < %s';
			$params[] = $args['to'];
		}

		if ( null !== $args['value'] && in_array( $args['column'], Reports::GROUP_COLUMNS, true ) ) {
			if ( 'landing_page' === $args['column'] && '' !== $args['value'] ) {
				$where[]  = '(landing_page = %s OR landing_page LIKE %s)';
				$params[] = $args['value'];
				$params[] = $wpdb->esc_like( $args['value'] ) . '?%';
			} elseif ( in_array( $args['column'], array( 'campaign', 'last_campaign' ), true ) && 0 === strpos( $args['value'], '~' ) ) {
				// No campaign, from one channel, e.g. "~organic_search".
				$where[] = "%i = '' AND %i = %s";
				$params  = array_merge( $params, array( $args['column'], 'last_campaign' === $args['column'] ? 'last_channel' : 'channel', substr( $args['value'], 1 ) ) );
			} elseif ( in_array( $args['column'], array( 'source', 'last_source' ), true ) && '(direct)' === $args['value'] ) {
				$where[] = "(%i = %s OR %i = '')";
				$params  = array_merge( $params, array( $args['column'], $args['value'], $args['column'] ) );
			} elseif ( in_array( $args['column'], array( 'source', 'last_source' ), true ) && '' !== $args['value'] ) {
				// The report groups the spellings of a source, e.g. ig and instagram, and shows
				// its bought clicks apart, e.g. Google Ads next to Google.
				$key      = Sources::key( $args['value'] );
				$paid     = Sources::split( $key );
				$channel  = 'last_source' === $args['column'] ? 'last_channel' : 'channel';
				$values   = Sources::spellings( $args['column'], $key );
				$where[]  = '%i IN (' . implode( ', ', array_fill( 0, count( $values ), '%s' ) ) . ')';
				$params[] = $args['column'];
				$params   = array_merge( $params, $values );

				if ( $paid ) {
					$where[] = '%i = %s';
					$params  = array_merge( $params, array( $channel, $paid[1] ) );
				} else {
					$where[] = '%i NOT IN (' . implode( ', ', array_fill( 0, count( Sources::PAID_CHANNELS ), '%s' ) ) . ')';
					$params  = array_merge( $params, array( $channel ), Sources::PAID_CHANNELS );
				}
			} else {
				$where[] = '%i = %s';
				$params  = array_merge( $params, array( $args['column'], $args['value'] ) );
			}
		}

		if ( $args['no_value'] ) {
			$where[] = 'value IS NULL';
		}

		if ( '' !== $args['search'] ) {
			$like    = '%' . $wpdb->esc_like( $args['search'] ) . '%';
			$where[] = '(name LIKE %s OR email LIKE %s OR phone LIKE %s OR campaign LIKE %s OR form_name LIKE %s OR fields LIKE %s)';
			$params  = array_merge( $params, array( $like, $like, $like, $like, $like, $like ) );
		}

		return array( implode( ' AND ', $where ), $params );
	}
}