<?php

declare(strict_types=1);

namespace FormConnector\Features\Attribution;

/**
 * Attribution Tracker
 *
 * Records and manages marketing attribution data for form submissions.
 * Works alongside the existing submission logs and UTM tracking to provide
 * campaign-level analytics with optional lead status tracking.
 */
class AttributionTracker {

	/**
	 * Singleton instance
	 */
	private static ?AttributionTracker $instance = null;

	/**
	 * Table name (without prefix)
	 */
	private const TABLE_NAME = 'fc_attribution';

	/**
	 * DB version for migrations
	 */
	public const DB_VERSION = '1.0.0';

	/**
	 * DB version option key
	 */
	private const DB_VERSION_OPTION = 'fc_attribution_db_version';

	/**
	 * Valid lead statuses
	 */
	public const LEAD_STATUSES = ['new', 'qualified', 'won', 'lost', 'unknown'];

	/**
	 * Get singleton instance
	 */
	public static function get_instance(): self {
		if (self::$instance === null) {
			self::$instance = new self();
		}
		return self::$instance;
	}

	/**
	 * Get the full table name (with WP prefix).
	 */
	public function get_table_name(): string {
		global $wpdb;
		return $wpdb->prefix . self::TABLE_NAME;
	}

	/**
	 * Create the attribution table.
	 */
	public function create_table(): void {
		global $wpdb;

		$table_name      = $this->get_table_name();
		$charset_collate = $wpdb->get_charset_collate();

		$sql = "CREATE TABLE {$table_name} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			submission_id bigint(20) unsigned DEFAULT NULL,
			form_plugin varchar(50) NOT NULL DEFAULT '',
			form_id varchar(50) NOT NULL DEFAULT '',
			form_name varchar(255) NOT NULL DEFAULT '',
			submitted_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			utm_source varchar(255) NOT NULL DEFAULT '',
			utm_medium varchar(255) NOT NULL DEFAULT '',
			utm_campaign varchar(255) NOT NULL DEFAULT '',
			utm_term varchar(255) NOT NULL DEFAULT '',
			utm_content varchar(255) NOT NULL DEFAULT '',
			referrer varchar(500) NOT NULL DEFAULT '',
			landing_page varchar(500) NOT NULL DEFAULT '',
			gclid varchar(255) NOT NULL DEFAULT '',
			fbclid varchar(255) NOT NULL DEFAULT '',
			msclkid varchar(255) NOT NULL DEFAULT '',
			location_id varchar(100) NOT NULL DEFAULT '',
			location_name varchar(255) NOT NULL DEFAULT '',
			carehq_contact_id varchar(100) NOT NULL DEFAULT '',
			lead_status varchar(20) NOT NULL DEFAULT 'new',
			lead_value decimal(10,2) DEFAULT NULL,
			status_updated_at datetime DEFAULT NULL,
			PRIMARY KEY (id),
			KEY submission_id (submission_id),
			KEY submitted_at (submitted_at),
			KEY utm_source (utm_source(100)),
			KEY utm_medium (utm_medium(100)),
			KEY utm_campaign (utm_campaign(100)),
			KEY lead_status (lead_status),
			KEY location_id (location_id),
			KEY form_plugin_form_id (form_plugin, form_id)
		) {$charset_collate};";

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta($sql);

		update_option(self::DB_VERSION_OPTION, self::DB_VERSION);
	}

	/**
	 * Check if migration is needed and run it.
	 */
	public static function maybe_migrate(): void {
		$stored = get_option(self::DB_VERSION_OPTION, '0.0.0');
		if (version_compare($stored, self::DB_VERSION, '<')) {
			self::get_instance()->create_table();
		}
	}

	/**
	 * Record an attribution entry from a form submission.
	 *
	 * @param array $data Attribution data
	 * @return int|false Insert ID or false on failure
	 */
	public function record(array $data): int|false {
		global $wpdb;

		$table = $this->get_table_name();

		$insert = [
			'submission_id'     => isset($data['submission_id']) ? (int) $data['submission_id'] : null,
			'form_plugin'       => sanitize_text_field($data['form_plugin'] ?? ''),
			'form_id'           => sanitize_text_field($data['form_id'] ?? ''),
			'form_name'         => sanitize_text_field($data['form_name'] ?? ''),
			'submitted_at'      => $data['submitted_at'] ?? current_time('mysql', true),
			'utm_source'        => sanitize_text_field($data['utm_source'] ?? ''),
			'utm_medium'        => sanitize_text_field($data['utm_medium'] ?? ''),
			'utm_campaign'      => sanitize_text_field($data['utm_campaign'] ?? ''),
			'utm_term'          => sanitize_text_field($data['utm_term'] ?? ''),
			'utm_content'       => sanitize_text_field($data['utm_content'] ?? ''),
			'referrer'          => sanitize_text_field($data['referrer'] ?? ''),
			'landing_page'      => sanitize_url($data['landing_page'] ?? ''),
			'gclid'             => sanitize_text_field($data['gclid'] ?? ''),
			'fbclid'            => sanitize_text_field($data['fbclid'] ?? ''),
			'msclkid'           => sanitize_text_field($data['msclkid'] ?? ''),
			'location_id'       => sanitize_text_field($data['location_id'] ?? ''),
			'location_name'     => sanitize_text_field($data['location_name'] ?? ''),
			'carehq_contact_id' => sanitize_text_field($data['carehq_contact_id'] ?? ''),
			'lead_status'       => in_array($data['lead_status'] ?? 'new', self::LEAD_STATUSES, true)
				? $data['lead_status']
				: 'new',
		];

		$result = $wpdb->insert($table, $insert);
		return $result !== false ? (int) $wpdb->insert_id : false;
	}

	/**
	 * Update lead status by CareHQ contact ID.
	 *
	 * @param string      $contact_id CareHQ contact ID
	 * @param string      $status     New lead status
	 * @param float|null  $value      Optional lead value
	 * @return int Number of rows updated
	 */
	public function update_lead_status(string $contact_id, string $status, ?float $value = null): int {
		global $wpdb;

		if (empty($contact_id) || !in_array($status, self::LEAD_STATUSES, true)) {
			return 0;
		}

		$table = $this->get_table_name();
		$update = [
			'lead_status'      => $status,
			'status_updated_at' => current_time('mysql', true),
		];

		if ($value !== null) {
			$update['lead_value'] = $value;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
		$rows = $wpdb->update(
			$table,
			$update,
			['carehq_contact_id' => $contact_id]
		);

		return (int) $rows;
	}

	/**
	 * Delete attribution rows for a specific email (GDPR erasure).
	 * Since we don't store email directly, we match by submission_id
	 * using the logs table for cross-reference.
	 *
	 * @param array $submission_ids Array of submission IDs to delete
	 * @return int Number of rows deleted
	 */
	public function delete_by_submission_ids(array $submission_ids): int {
		global $wpdb;

		if (empty($submission_ids)) {
			return 0;
		}

		$table = $this->get_table_name();
		$ids   = array_map('intval', $submission_ids);
		$placeholders = implode(',', array_fill(0, count($ids), '%d'));

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$deleted = $wpdb->query($wpdb->prepare(
			"DELETE FROM {$table} WHERE submission_id IN ({$placeholders})",
			$ids
		));

		return (int) $deleted;
	}

	/**
	 * Drop the attribution table (for uninstall).
	 */
	public function drop_table(): void {
		global $wpdb;
		$table = $this->get_table_name();
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$wpdb->query("DROP TABLE IF EXISTS {$table}");
		delete_option(self::DB_VERSION_OPTION);
	}
}
