<?php
/**
 * Lead notes and activity 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 workflow tables directly.
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table names are plugin-owned $wpdb->prefix values.

/**
 * Stores lead notes and workflow activity.
 */
class LeadActivityRepository {
	/**
	 * Add a note and activity entry.
	 *
	 * @param int    $lead_id Lead id.
	 * @param string $note Note text.
	 * @param int    $user_id User id.
	 * @return int
	 */
	public function add_note( $lead_id, $note, $user_id = 0 ) {
		global $wpdb;

		$lead_id = absint( $lead_id );
		$note    = sanitize_textarea_field( (string) $note );
		$user_id = absint( $user_id );
		if ( $lead_id <= 0 || '' === trim( $note ) ) {
			return 0;
		}

		$now      = current_time( 'mysql' );
		$inserted = $wpdb->insert(
			$wpdb->prefix . 'subin_lead_notes',
			array(
				'lead_id'    => $lead_id,
				'user_id'    => $user_id,
				'note'       => $note,
				'created_at' => $now,
				'updated_at' => $now,
			),
			array( '%d', '%d', '%s', '%s', '%s' )
		);

		if ( ! $inserted ) {
			return 0;
		}

		$note_id = absint( $wpdb->insert_id );
		$this->add_activity(
			$lead_id,
			'note_added',
			__( 'Note added.', 'submission-inbox' ),
			array( 'note_id' => $note_id ),
			$user_id
		);

		do_action( 'subin_lead_note_added', $lead_id, $note_id );

		return $note_id;
	}

	/**
	 * Add an activity entry.
	 *
	 * @param int                 $lead_id Lead id.
	 * @param string              $type Activity type.
	 * @param string              $message Activity message.
	 * @param array<string,mixed> $metadata Metadata.
	 * @param int                 $actor_user_id Actor user id.
	 * @return int
	 */
	public function add_activity( $lead_id, $type, $message, array $metadata = array(), $actor_user_id = 0 ) {
		global $wpdb;

		$lead_id = absint( $lead_id );
		if ( $lead_id <= 0 ) {
			return 0;
		}

		$metadata_json = wp_json_encode( $metadata );
		$inserted      = $wpdb->insert(
			$wpdb->prefix . 'subin_lead_activity',
			array(
				'lead_id'          => $lead_id,
				'actor_user_id'    => absint( $actor_user_id ),
				'activity_type'    => sanitize_key( (string) $type ),
				'activity_message' => sanitize_text_field( (string) $message ),
				'metadata_json'    => is_string( $metadata_json ) ? $metadata_json : '',
				'created_at'       => current_time( 'mysql' ),
			),
			array( '%d', '%d', '%s', '%s', '%s', '%s' )
		);

		if ( $inserted ) {
			$wpdb->update(
				$wpdb->prefix . 'subin_leads',
				array( 'last_activity_at' => current_time( 'mysql' ) ),
				array( 'id' => $lead_id ),
				array( '%s' ),
				array( '%d' )
			);
		}

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

	/**
	 * Get notes for a lead.
	 *
	 * @param int $lead_id Lead id.
	 * @return array<int,object>
	 */
	public function get_notes( $lead_id ) {
		global $wpdb;

		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT notes.*, users.display_name
				FROM {$wpdb->prefix}subin_lead_notes notes
				LEFT JOIN {$wpdb->users} users ON users.ID = notes.user_id
				WHERE notes.lead_id = %d
				ORDER BY notes.created_at DESC, notes.id DESC",
				absint( $lead_id )
			)
		);
	}

	/**
	 * Update a single lead note.
	 *
	 * @param int    $note_id Note id.
	 * @param int    $lead_id Lead id.
	 * @param string $note Note text.
	 * @param int    $user_id User id.
	 * @return bool
	 */
	public function update_note( $note_id, $lead_id, $note, $user_id = 0 ) {
		global $wpdb;

		$note_id = absint( $note_id );
		$lead_id = absint( $lead_id );
		$note    = sanitize_textarea_field( (string) $note );
		$user_id = absint( $user_id );
		if ( $note_id <= 0 || $lead_id <= 0 || '' === trim( $note ) ) {
			return false;
		}

		$existing = $this->get_note( $note_id, $lead_id );
		if ( ! $existing ) {
			return false;
		}

		$previous_note = sanitize_textarea_field( (string) ( $existing->note ?? '' ) );
		if ( $previous_note === $note ) {
			return true;
		}

		$updated = $wpdb->update(
			$wpdb->prefix . 'subin_lead_notes',
			array(
				'note'       => $note,
				'updated_at' => current_time( 'mysql' ),
			),
			array(
				'id'      => $note_id,
				'lead_id' => $lead_id,
			),
			array( '%s', '%s' ),
			array( '%d', '%d' )
		);

		if ( false === $updated ) {
			return false;
		}

		$this->add_activity(
			$lead_id,
			'note_updated',
			__( 'Note updated.', 'submission-inbox' ),
			array(
				'note_id'          => $note_id,
				'previous_excerpt' => $this->note_excerpt( $previous_note ),
				'updated_excerpt'  => $this->note_excerpt( $note ),
			),
			$user_id
		);

		do_action( 'subin_lead_note_updated', $lead_id, $note_id );

		return true;
	}

	/**
	 * Delete a single lead note.
	 *
	 * @param int $note_id Note id.
	 * @param int $lead_id Lead id.
	 * @param int $user_id User id.
	 * @return bool
	 */
	public function delete_note( $note_id, $lead_id, $user_id = 0 ) {
		global $wpdb;

		$note_id = absint( $note_id );
		$lead_id = absint( $lead_id );
		$user_id = absint( $user_id );
		if ( $note_id <= 0 || $lead_id <= 0 ) {
			return false;
		}

		$existing = $this->get_note( $note_id, $lead_id );
		if ( ! $existing ) {
			return false;
		}

		$deleted = $wpdb->delete(
			$wpdb->prefix . 'subin_lead_notes',
			array(
				'id'      => $note_id,
				'lead_id' => $lead_id,
			),
			array( '%d', '%d' )
		);

		if ( ! $deleted ) {
			return false;
		}

		$this->add_activity(
			$lead_id,
			'note_deleted',
			__( 'Note deleted.', 'submission-inbox' ),
			array(
				'note_id'         => $note_id,
				'deleted_excerpt' => $this->note_excerpt( (string) ( $existing->note ?? '' ) ),
			),
			$user_id
		);

		do_action( 'subin_lead_note_deleted', $lead_id, $note_id );

		return true;
	}

	/**
	 * Get one note belonging to a lead.
	 *
	 * @param int $note_id Note id.
	 * @param int $lead_id Lead id.
	 * @return object|null
	 */
	private function get_note( $note_id, $lead_id ) {
		global $wpdb;

		$note_id = absint( $note_id );
		$lead_id = absint( $lead_id );
		if ( $note_id <= 0 || $lead_id <= 0 ) {
			return null;
		}

		return $wpdb->get_row(
			$wpdb->prepare(
				"SELECT *
				FROM {$wpdb->prefix}subin_lead_notes
				WHERE id = %d AND lead_id = %d
				LIMIT 1",
				$note_id,
				$lead_id
			)
		);
	}

	/**
	 * Build a short note excerpt for activity metadata.
	 *
	 * @param string $note Note text.
	 * @return string
	 */
	private function note_excerpt( $note ) {
		return wp_trim_words( sanitize_textarea_field( (string) $note ), 20, '...' );
	}

	/**
	 * Get activity timeline for a lead.
	 *
	 * @param int $lead_id Lead id.
	 * @return array<int,object>
	 */
	public function get_timeline( $lead_id ) {
		global $wpdb;

		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT activity.*, users.display_name
				FROM {$wpdb->prefix}subin_lead_activity activity
				LEFT JOIN {$wpdb->users} users ON users.ID = activity.actor_user_id
				WHERE activity.lead_id = %d
				ORDER BY activity.created_at DESC, activity.id DESC",
				absint( $lead_id )
			)
		);
	}

	/**
	 * Delete workflow rows for a lead.
	 *
	 * @param int $lead_id Lead id.
	 * @return void
	 */
	public function delete_by_lead_id( $lead_id ) {
		global $wpdb;

		$lead_id = absint( $lead_id );
		if ( $lead_id <= 0 ) {
			return;
		}

		$wpdb->delete( $wpdb->prefix . 'subin_lead_notes', array( 'lead_id' => $lead_id ), array( '%d' ) );
		$wpdb->delete( $wpdb->prefix . 'subin_lead_activity', array( 'lead_id' => $lead_id ), array( '%d' ) );
	}
}
