<?php
/**
 * Lead repository.
 *
 * @package SubmissionInbox
 */

namespace SUBIN\Leads;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Repository methods read/write Submission Inbox custom lead tables directly; WordPress has no native API for these records.
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table names are plugin-owned $wpdb->prefix values, and dynamic clauses are built from sanitized allowlists/placeholders in this repository.

/**
 * Reads and writes lead records.
 */
class LeadRepository {
	const STATUS_NEW      = 'new';
	const STATUS_SEEN     = 'seen';
	const STATUS_ARCHIVED = 'archived';

	const EMAIL_STATUS_SENT               = 'sent';
	const EMAIL_STATUS_FAILED             = 'failed';
	const EMAIL_STATUS_NO_EMAIL_GENERATED = 'no_email_generated';
	const EMAIL_STATUS_UNKNOWN            = 'unknown';

	const WORKFLOW_STATUS_OPEN      = 'open';
	const WORKFLOW_STATUS_CONTACTED = 'contacted';
	const WORKFLOW_STATUS_QUALIFIED = 'qualified';
	const WORKFLOW_STATUS_WON       = 'won';
	const WORKFLOW_STATUS_LOST      = 'lost';

	const PRIORITY_LOW    = 'low';
	const PRIORITY_NORMAL = 'normal';
	const PRIORITY_HIGH   = 'high';
	const PRIORITY_URGENT = 'urgent';

	/**
	 * Create a lead.
	 *
	 * @param array<string,mixed> $data Lead data.
	 * @return int
	 */
	public function create( array $data ) {
		global $wpdb;

		$table = $wpdb->prefix . 'subin_leads';
		$now   = current_time( 'mysql' );

		$inserted = $wpdb->insert(
			$table,
			array(
				'provider'        => sanitize_text_field( $data['provider'] ?? '' ),
				'form_id'         => sanitize_text_field( $data['form_id'] ?? '' ),
				'form_name'       => sanitize_text_field( $data['form_name'] ?? '' ),
				'source_url'      => esc_url_raw( $data['source_url'] ?? '' ),
				'referrer'        => esc_url_raw( $data['referrer'] ?? '' ),
				'customer_name'   => sanitize_text_field( $data['customer_name'] ?? '' ),
				'customer_email'  => sanitize_email( $data['customer_email'] ?? '' ),
				'customer_phone'  => sanitize_text_field( $data['customer_phone'] ?? '' ),
				'message_preview' => sanitize_textarea_field( $data['message_preview'] ?? '' ),
				'status'          => self::STATUS_NEW,
				'email_status'    => self::EMAIL_STATUS_UNKNOWN,
				'workflow_status' => self::WORKFLOW_STATUS_OPEN,
				'assigned_user_id' => ! empty( $data['assigned_user_id'] ) ? absint( $data['assigned_user_id'] ) : null,
				'follow_up_at'    => ! empty( $data['follow_up_at'] ) ? sanitize_text_field( $data['follow_up_at'] ) : null,
				'priority'        => self::PRIORITY_NORMAL,
				'last_activity_at' => $now,
				'submitted_at'    => sanitize_text_field( $data['submitted_at'] ?? $now ),
				'expires_at'      => ! empty( $data['expires_at'] ) ? sanitize_text_field( $data['expires_at'] ) : null,
				'created_at'      => $now,
				'updated_at'      => $now,
			),
			array( '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' )
		);

		return $inserted ? absint( $wpdb->insert_id ) : 0;
	}

	/**
	 * Update lead email status.
	 *
	 * @param int    $lead_id Lead id.
	 * @param string $status Email status.
	 * @return bool
	 */
	public function update_email_status( $lead_id, $status ) {
		if ( ! in_array( $status, $this->get_email_statuses(), true ) ) {
			return false;
		}

		return $this->update_field( $lead_id, 'email_status', $status );
	}

	/**
	 * Update lead status.
	 *
	 * @param int    $lead_id Lead id.
	 * @param string $status Lead status.
	 * @return bool
	 */
	public function update_status( $lead_id, $status ) {
		if ( ! in_array( $status, $this->get_statuses(), true ) ) {
			return false;
		}

		return $this->update_field( $lead_id, 'status', $status );
	}

	/**
	 * Update every lead with one status to another status.
	 *
	 * @param string $from_status Current status.
	 * @param string $to_status New status.
	 * @return int|false Number of affected rows or false on failure.
	 */
	public function update_status_bulk( $from_status, $to_status ) {
		global $wpdb;

		if ( ! in_array( $from_status, $this->get_statuses(), true ) || ! in_array( $to_status, $this->get_statuses(), true ) ) {
			return false;
		}

		$table = $wpdb->prefix . 'subin_leads';

		return $wpdb->update(
			$table,
			array(
				'status'     => $to_status,
				'updated_at' => current_time( 'mysql' ),
			),
			array( 'status' => $from_status ),
			array( '%s', '%s' ),
			array( '%s' )
		);
	}

	/**
	 * Get a lead by id.
	 *
	 * @param int $lead_id Lead id.
	 * @return object|null
	 */
	public function get( $lead_id ) {
		global $wpdb;

		$table = $wpdb->prefix . 'subin_leads';

		return $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", absint( $lead_id ) ) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		);
	}

	/**
	 * Find a lead by provider and an external entry id stored in lead fields.
	 *
	 * @param string $provider Provider key.
	 * @param string $external_entry_id External entry/submission id.
	 * @param string $field_key Metadata field key.
	 * @return object|null
	 */
	public function find_by_provider_external_entry_id( $provider, $external_entry_id, $field_key ) {
		global $wpdb;

		$provider          = sanitize_key( $provider );
		$external_entry_id = sanitize_text_field( $external_entry_id );
		$field_key         = sanitize_key( $field_key );

		if ( '' === $provider || '' === $external_entry_id || '' === $field_key ) {
			return null;
		}

		$leads_table  = $wpdb->prefix . 'subin_leads';
		$fields_table = $wpdb->prefix . 'subin_lead_fields';

		return $wpdb->get_row(
			$wpdb->prepare(
				"SELECT leads.*
				FROM {$leads_table} leads
				INNER JOIN {$fields_table} fields ON fields.lead_id = leads.id
				WHERE leads.provider = %s
					AND fields.field_key = %s
					AND fields.field_value = %s
				ORDER BY leads.id DESC
				LIMIT 1",
				$provider,
				$field_key,
				$external_entry_id
			) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		);
	}

	/**
	 * Get paginated leads.
	 *
	 * @param array<string,mixed> $args Query args.
	 * @return array<int,object>
	 */
	public function get_paginated( array $args ) {
		return $this->get_paginated_leads( $args );
	}

	/**
	 * Get paginated leads.
	 *
	 * @param array<string,mixed> $args Query args.
	 * @return array<int,object>
	 */
	public function get_paginated_leads( array $args ) {
		global $wpdb;

		$table   = $wpdb->prefix . 'subin_leads';
		$where   = $this->build_where( $args );
		$limit   = max( 1, absint( $args['per_page'] ?? 20 ) );
		$offset  = max( 0, absint( $args['offset'] ?? 0 ) );
		$sql     = "SELECT * FROM {$table} {$where['sql']} ORDER BY submitted_at DESC, id DESC LIMIT %d OFFSET %d";
		$params  = array_merge( $where['params'], array( $limit, $offset ) );

		return $wpdb->get_results( $wpdb->prepare( $sql, $params ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
	}

	/**
	 * Count leads.
	 *
	 * @param array<string,mixed> $args Query args.
	 * @return int
	 */
	public function count( array $args = array() ) {
		return $this->count_leads( $args );
	}

	/**
	 * Count leads.
	 *
	 * @param array<string,mixed> $args Query args.
	 * @return int
	 */
	public function count_leads( array $args = array() ) {
		global $wpdb;

		$table   = $wpdb->prefix . 'subin_leads';
		$where   = $this->build_where( $args );
		$sql     = "SELECT COUNT(*) FROM {$table} {$where['sql']}";

		if ( ! empty( $where['params'] ) ) {
			return absint( $wpdb->get_var( $wpdb->prepare( $sql, $where['params'] ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		}

		return absint( $wpdb->get_var( $sql ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
	}

	/**
	 * Get distinct form names.
	 *
	 * @return array<int,string>
	 */
	public function get_distinct_form_names() {
		global $wpdb;

		$table = $wpdb->prefix . 'subin_leads';

		return array_map(
			'sanitize_text_field',
			$wpdb->get_col( "SELECT DISTINCT form_name FROM {$table} WHERE form_name IS NOT NULL AND form_name != '' ORDER BY form_name ASC" ) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		);
	}

	/**
	 * Get form choices for inbox filters.
	 *
	 * @return array<int,array{provider:string,form_id:string,form_name:string}>
	 */
	public function get_form_filter_options() {
		global $wpdb;

		$forms_table = $wpdb->prefix . 'subin_forms';
		$leads_table = $wpdb->prefix . 'subin_leads';
		$options     = array();
		$seen        = array();

		$built_in_forms = $wpdb->get_results( "SELECT id, title FROM {$forms_table} ORDER BY title ASC" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		foreach ( $built_in_forms as $form ) {
			$form_id = (string) absint( $form->id );
			$key     = 'submission_inbox_form|' . $form_id;

			$seen[ $key ] = true;
			$options[]    = array(
				'provider'  => 'submission_inbox_form',
				'form_id'   => $form_id,
				'form_name' => sanitize_text_field( $form->title ),
			);
		}

		$lead_forms = $wpdb->get_results(
			"SELECT provider, form_id, MAX(form_name) AS form_name
			FROM {$leads_table}
			WHERE form_id IS NOT NULL AND form_id != ''
			GROUP BY provider, form_id
			ORDER BY form_name ASC"
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		foreach ( $lead_forms as $form ) {
			$provider = sanitize_key( $form->provider );
			$form_id  = sanitize_text_field( $form->form_id );
			$key      = $provider . '|' . $form_id;

			if ( isset( $seen[ $key ] ) ) {
				continue;
			}

			$seen[ $key ] = true;
			$options[]    = array(
				'provider'  => $provider,
				'form_id'   => $form_id,
				'form_name' => sanitize_text_field( $form->form_name ),
			);
		}

		return $options;
	}

	/**
	 * Get leads for CSV export.
	 *
	 * @param array<string,mixed> $filters Filters.
	 * @param int                 $limit Export limit.
	 * @return array<int,object>
	 */
	public function get_leads_for_export( array $filters, $limit = 5000 ) {
		global $wpdb;

		$table   = $wpdb->prefix . 'subin_leads';
		$where   = $this->build_where( $filters );
		$limit   = max( 1, absint( $limit ) );
		$sql     = "SELECT * FROM {$table} {$where['sql']} ORDER BY submitted_at DESC, id DESC LIMIT %d";
		$params  = array_merge( $where['params'], array( $limit ) );

		return $wpdb->get_results( $wpdb->prepare( $sql, $params ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
	}

	/**
	 * Get summary counts.
	 *
	 * @return array<string,int>
	 */
	public function get_summary_counts() {
		return array(
			'total'              => $this->count(),
			'new'                => $this->count( array( 'status' => self::STATUS_NEW ) ),
			'failed'             => $this->count( array( 'email_status' => self::EMAIL_STATUS_FAILED ) ),
			'no_email_generated' => $this->count( array( 'email_status' => self::EMAIL_STATUS_NO_EMAIL_GENERATED ) ),
			'unknown'            => $this->count( array( 'email_status' => self::EMAIL_STATUS_UNKNOWN ) ),
		);
	}

	/**
	 * Delete a lead.
	 *
	 * @param int $lead_id Lead id.
	 * @return bool
	 */
	public function delete( $lead_id ) {
		global $wpdb;

		$table = $wpdb->prefix . 'subin_leads';

		return false !== $wpdb->delete( $table, array( 'id' => absint( $lead_id ) ), array( '%d' ) );
	}

	/**
	 * Get expired lead ids.
	 *
	 * @param int $limit Limit.
	 * @return array<int,int>
	 */
	public function get_expired_lead_ids( $limit = 500 ) {
		global $wpdb;

		$table = $wpdb->prefix . 'subin_leads';
		$now   = current_time( 'mysql' );

		return array_map(
			'absint',
			$wpdb->get_col(
				$wpdb->prepare(
					"SELECT id FROM {$table} WHERE expires_at IS NOT NULL AND expires_at != '' AND expires_at < %s ORDER BY expires_at ASC LIMIT %d",
					$now,
					max( 1, absint( $limit ) )
				) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			)
		);
	}

	/**
	 * Build WHERE clause.
	 *
	 * @param array<string,mixed> $args Query args.
	 * @return array{sql:string,params:array<int,mixed>}
	 */
	private function build_where( array $args ) {
		global $wpdb;

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

		if ( ! empty( $args['status'] ) && in_array( $args['status'], $this->get_statuses(), true ) ) {
			$where[]  = 'status = %s';
			$params[] = $args['status'];
		}

		if ( ! empty( $args['email_status'] ) && in_array( $args['email_status'], $this->get_email_statuses(), true ) ) {
			$where[]  = 'email_status = %s';
			$params[] = $args['email_status'];
		}

		if ( ! empty( $args['needs_attention'] ) ) {
			$where[]  = "email_status IN ('failed', 'no_email_generated', 'unknown')";
		}

		if ( ! empty( $args['provider'] ) && in_array( $args['provider'], $this->get_providers(), true ) ) {
			$where[]  = 'provider = %s';
			$params[] = $args['provider'];
		}

		if ( ! empty( $args['form_provider'] ) && in_array( $args['form_provider'], $this->get_providers(), true ) ) {
			$where[]  = 'provider = %s';
			$params[] = $args['form_provider'];
		}

		if ( ! empty( $args['form_id'] ) ) {
			$where[]  = 'form_id = %s';
			$params[] = sanitize_text_field( $args['form_id'] );
		}

		if ( ! empty( $args['form'] ) ) {
			$where[]  = 'form_name = %s';
			$params[] = sanitize_text_field( $args['form'] );
		}

		if ( ! empty( $args['submitted_after'] ) ) {
			$where[]  = 'submitted_at >= %s';
			$params[] = sanitize_text_field( $args['submitted_after'] );
		}

		if ( ! empty( $args['workflow_status'] ) && in_array( $args['workflow_status'], $this->get_workflow_statuses(), true ) ) {
			$where[]  = 'workflow_status = %s';
			$params[] = $args['workflow_status'];
		}

		if ( ! empty( $args['priority'] ) && in_array( $args['priority'], $this->get_priorities(), true ) ) {
			$where[]  = 'priority = %s';
			$params[] = $args['priority'];
		}

		if ( isset( $args['assigned_user_id'] ) && '' !== (string) $args['assigned_user_id'] ) {
			$assigned_user_id = absint( $args['assigned_user_id'] );
			if ( $assigned_user_id > 0 ) {
				$where[]  = 'assigned_user_id = %d';
				$params[] = $assigned_user_id;
			} elseif ( '0' === (string) $args['assigned_user_id'] ) {
				$where[] = '(assigned_user_id IS NULL OR assigned_user_id = 0)';
			}
		}

		if ( ! empty( $args['overdue_follow_up'] ) ) {
			$where[]  = 'follow_up_at IS NOT NULL AND follow_up_at != "" AND follow_up_at < %s';
			$params[] = current_time( 'mysql' );
		}

		if ( ! empty( $args['date_from'] ) ) {
			$where[]  = 'submitted_at >= %s';
			$params[] = sanitize_text_field( $args['date_from'] ) . ' 00:00:00';
		}

		if ( ! empty( $args['date_to'] ) ) {
			$where[]  = 'submitted_at <= %s';
			$params[] = sanitize_text_field( $args['date_to'] ) . ' 23:59:59';
		}

		if ( ! empty( $args['search'] ) ) {
			$like     = '%' . $wpdb->esc_like( sanitize_text_field( $args['search'] ) ) . '%';
			$where[]  = '(customer_email LIKE %s OR customer_name LIKE %s OR customer_phone LIKE %s OR form_name LIKE %s OR message_preview LIKE %s)';
			$params[] = $like;
			$params[] = $like;
			$params[] = $like;
			$params[] = $like;
			$params[] = $like;
		}

		return array(
			'sql'    => ! empty( $where ) ? 'WHERE ' . implode( ' AND ', $where ) : '',
			'params' => $params,
		);
	}

	/**
	 * Update a lead field.
	 *
	 * @param int    $lead_id Lead id.
	 * @param string $field Field name.
	 * @param string $value Field value.
	 * @return bool
	 */
	private function update_field( $lead_id, $field, $value ) {
		global $wpdb;

		$table = $wpdb->prefix . 'subin_leads';

		return false !== $wpdb->update(
			$table,
			array(
				$field       => $value,
				'updated_at' => current_time( 'mysql' ),
			),
			array( 'id' => absint( $lead_id ) ),
			array( '%s', '%s' ),
			array( '%d' )
		);
	}

	/**
	 * Get allowed lead statuses.
	 *
	 * @return array<int,string>
	 */
	private function get_statuses() {
		return array( self::STATUS_NEW, self::STATUS_SEEN, self::STATUS_ARCHIVED );
	}

	/**
	 * Get allowed email statuses.
	 *
	 * @return array<int,string>
	 */
	private function get_email_statuses() {
		return array(
			self::EMAIL_STATUS_SENT,
			self::EMAIL_STATUS_FAILED,
			self::EMAIL_STATUS_NO_EMAIL_GENERATED,
			self::EMAIL_STATUS_UNKNOWN,
		);
	}

	/**
	 * Get allowed providers.
	 *
	 * @return array<int,string>
	 */
	private function get_providers() {
		return ( new \SUBIN\Integrations\ProviderRegistry() )->active_keys();
	}

	/**
	 * Get allowed workflow statuses.
	 *
	 * @return array<int,string>
	 */
	private function get_workflow_statuses() {
		return array(
			self::WORKFLOW_STATUS_OPEN,
			self::WORKFLOW_STATUS_CONTACTED,
			self::WORKFLOW_STATUS_QUALIFIED,
			self::WORKFLOW_STATUS_WON,
			self::WORKFLOW_STATUS_LOST,
		);
	}

	/**
	 * Get allowed priorities.
	 *
	 * @return array<int,string>
	 */
	private function get_priorities() {
		return array(
			self::PRIORITY_LOW,
			self::PRIORITY_NORMAL,
			self::PRIORITY_HIGH,
			self::PRIORITY_URGENT,
		);
	}
}
