<?php
/**
 * Answyr Desk — ticket store.
 *
 * Custom tables (not CPT) keep ticket queries fast and out of wp_posts.
 *   {prefix}answyr_desk_tickets   — one row per ticket
 *   {prefix}answyr_desk_messages  — thread (customer + agent + AI-draft) rows
 *
 * @package Answyr
 */

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

final class Answyr_Desk_Store {

	public static function tickets_table(): string {
		global $wpdb;
		return $wpdb->prefix . 'answyr_desk_tickets';
	}

	public static function messages_table(): string {
		global $wpdb;
		return $wpdb->prefix . 'answyr_desk_messages';
	}

	private const DB_VERSION = '3';

	/** Create/upgrade tables via dbDelta. Idempotent — also adds new columns. */
	public static function install(): void {
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		$charset  = $wpdb->get_charset_collate();
		$tickets  = self::tickets_table();
		$messages = self::messages_table();

		dbDelta(
			"CREATE TABLE {$tickets} (
				id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
				subject VARCHAR(255) NOT NULL DEFAULT '',
				customer_email VARCHAR(190) NOT NULL DEFAULT '',
				customer_name VARCHAR(190) NOT NULL DEFAULT '',
				status VARCHAR(20) NOT NULL DEFAULT 'open',
				priority VARCHAR(20) NOT NULL DEFAULT 'normal',
				tags VARCHAR(255) NOT NULL DEFAULT '',
				channel VARCHAR(20) NOT NULL DEFAULT 'form',
				rating TINYINT NULL,
				rating_comment VARCHAR(500) NULL,
				first_response_at DATETIME NULL,
				meta LONGTEXT NULL,
				created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
				updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
				PRIMARY KEY (id),
				KEY status (status),
				KEY priority (priority),
				KEY customer_email (customer_email)
			) {$charset};"
		);

		dbDelta(
			"CREATE TABLE {$messages} (
				id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
				ticket_id BIGINT UNSIGNED NOT NULL,
				author_type VARCHAR(20) NOT NULL DEFAULT 'customer',
				author_id BIGINT UNSIGNED NULL,
				body LONGTEXT NOT NULL,
				is_draft TINYINT(1) NOT NULL DEFAULT 0,
				meta LONGTEXT NULL,
				created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
				PRIMARY KEY (id),
				KEY ticket_id (ticket_id)
			) {$charset};"
		);

		update_option( 'answyr_desk_db_version', self::DB_VERSION );
	}

	/** Run dbDelta when the stored schema version is behind (no reactivation needed). */
	public static function maybe_upgrade(): void {
		if ( (string) get_option( 'answyr_desk_db_version', '0' ) !== self::DB_VERSION ) {
			self::install();
		}
	}

	/** @param array<string,mixed> $data */
	public static function create_ticket( array $data ): int {
		global $wpdb;
		$row = array(
			'subject'        => sanitize_text_field( $data['subject'] ?? '' ),
			'customer_email' => sanitize_email( $data['customer_email'] ?? '' ),
			'customer_name'  => sanitize_text_field( $data['customer_name'] ?? '' ),
			'status'         => 'open',
			'channel'        => sanitize_key( $data['channel'] ?? 'form' ),
		);
		// Structured customer-context snapshot (from the intake fields) lives in meta.
		if ( ! empty( $data['context'] ) && is_array( $data['context'] ) ) {
			$row['meta'] = wp_json_encode( array( 'context' => array_values( $data['context'] ) ) );
		}
		$wpdb->insert( self::tickets_table(), $row );
		return (int) $wpdb->insert_id;
	}

	/**
	 * The customer-context snapshot captured at intake, as an ordered list of
	 * { label, value } pairs. Empty when the ticket has none.
	 *
	 * @return array<int,array{label:string,value:string}>
	 */
	public static function get_context( int $ticket_id ): array {
		$ticket = self::get_ticket( $ticket_id );
		if ( ! $ticket || empty( $ticket->meta ) ) {
			return array();
		}
		$meta = json_decode( (string) $ticket->meta, true );
		if ( ! is_array( $meta ) || empty( $meta['context'] ) || ! is_array( $meta['context'] ) ) {
			return array();
		}
		$out = array();
		foreach ( $meta['context'] as $pair ) {
			if ( is_array( $pair ) && isset( $pair['label'], $pair['value'] ) ) {
				$out[] = array(
					'label' => (string) $pair['label'],
					'value' => (string) $pair['value'],
				);
			}
		}
		return $out;
	}

	/**
	 * @param string              $author_type customer|agent|ai
	 * @param array<string,mixed> $meta
	 */
	public static function add_message( int $ticket_id, string $author_type, string $body, bool $is_draft = false, array $meta = array() ): int {
		global $wpdb;
		$wpdb->insert(
			self::messages_table(),
			array(
				'ticket_id'   => $ticket_id,
				'author_type' => sanitize_key( $author_type ),
				'body'        => wp_kses_post( $body ),
				'is_draft'    => $is_draft ? 1 : 0,
				'meta'        => $meta ? wp_json_encode( $meta ) : null,
			)
		);
		$wpdb->update( self::tickets_table(), array( 'updated_at' => current_time( 'mysql' ) ), array( 'id' => $ticket_id ) );
		return (int) $wpdb->insert_id;
	}

	/** @return array<int,object> */
	public static function thread( int $ticket_id ): array {
		global $wpdb;
		return $wpdb->get_results(
			$wpdb->prepare(
				'SELECT * FROM ' . self::messages_table() . ' WHERE ticket_id = %d ORDER BY created_at ASC, id ASC',
				$ticket_id
			)
		);
	}

	/**
	 * Who the ticket is waiting on, derived from the last visible reply. Returns
	 * 'agent' when the customer spoke last (we owe a response), 'customer' when the
	 * agent spoke last (their move), or '' when there is no customer/agent reply yet.
	 * Internal notes and unsent AI drafts do not count.
	 *
	 * @param array<int,int> $ticket_ids
	 * @return array<int,string> ticket_id => 'agent'|'customer'
	 */
	public static function waiting_on_map( array $ticket_ids ): array {
		global $wpdb;
		$ids = array_values( array_filter( array_map( 'intval', $ticket_ids ) ) );
		if ( ! $ids ) {
			return array();
		}
		$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
		$messages     = self::messages_table();
		// Latest visible customer/agent message per ticket (id is monotonic).
		$sql = "SELECT m.ticket_id, m.author_type FROM {$messages} m
			JOIN (
				SELECT ticket_id, MAX(id) AS max_id FROM {$messages}
				WHERE ticket_id IN ({$placeholders})
					AND author_type IN ('customer','agent') AND is_draft = 0
				GROUP BY ticket_id
			) last ON last.max_id = m.id";
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- placeholders bound via $ids below.
		$rows = $wpdb->get_results( $wpdb->prepare( $sql, $ids ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared
		$out  = array();
		foreach ( $rows as $row ) {
			$out[ (int) $row->ticket_id ] = 'customer' === $row->author_type ? 'agent' : 'customer';
		}
		return $out;
	}

	/** Single-ticket convenience wrapper around waiting_on_map(). */
	public static function waiting_on( int $ticket_id ): string {
		$map = self::waiting_on_map( array( $ticket_id ) );
		return $map[ $ticket_id ] ?? '';
	}

	public static function get_ticket( int $ticket_id ): ?object {
		global $wpdb;
		$row = $wpdb->get_row(
			$wpdb->prepare( 'SELECT * FROM ' . self::tickets_table() . ' WHERE id = %d', $ticket_id )
		);
		return $row ?: null;
	}

	/** @return array<int,object> */
	public static function list_tickets( string $status = 'open', int $limit = 50 ): array {
		global $wpdb;
		return $wpdb->get_results(
			$wpdb->prepare(
				'SELECT * FROM ' . self::tickets_table() . ' WHERE status = %s ORDER BY updated_at DESC LIMIT %d',
				$status,
				$limit
			)
		);
	}

	/**
	 * All tickets for a customer email (any status) — for the customer portal.
	 * Matched case-insensitively against the account email.
	 *
	 * @return array<int,object>
	 */
	public static function list_by_email( string $email, int $limit = 50 ): array {
		global $wpdb;
		return $wpdb->get_results(
			$wpdb->prepare(
				'SELECT * FROM ' . self::tickets_table() . ' WHERE LOWER(customer_email) = %s ORDER BY updated_at DESC LIMIT %d',
				strtolower( $email ),
				$limit
			)
		);
	}

	/** Canonical ticket statuses (workflow order). */
	public static function statuses(): array {
		return array(
			'open'     => __( 'Open', 'answyr' ),
			'pending'  => __( 'Pending', 'answyr' ),
			'resolved' => __( 'Resolved', 'answyr' ),
			'closed'   => __( 'Closed', 'answyr' ),
		);
	}

	public static function is_status( string $status ): bool {
		return array_key_exists( $status, self::statuses() );
	}

	/**
	 * Flexible inbox query: status filter, keyword search, pagination.
	 * A status of 'all' (or empty) returns every status.
	 *
	 * @param array{status?:string,search?:string,limit?:int,offset?:int} $args
	 * @return array<int,object>
	 */
	public static function query_tickets( array $args = array() ): array {
		global $wpdb;
		list( $where, $params ) = self::build_filter( $args );
		$limit                  = max( 1, (int) ( $args['limit'] ?? 25 ) );
		$offset                 = max( 0, (int) ( $args['offset'] ?? 0 ) );
		$params[]               = $limit;
		$params[]               = $offset;
		// Triage order: urgent/high first, then most-recently updated.
		$order = "ORDER BY FIELD( priority, 'urgent', 'high', 'normal', 'low' ), updated_at DESC";
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $where uses %s placeholders bound via $params; $order is a literal.
		$sql = 'SELECT * FROM ' . self::tickets_table() . " {$where} {$order} LIMIT %d OFFSET %d";
		return $wpdb->get_results( $wpdb->prepare( $sql, $params ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
	}

	/**
	 * Row count for the same filter — drives status-tab counts + pagination.
	 *
	 * @param array{status?:string,search?:string} $args
	 */
	public static function count_tickets( array $args = array() ): int {
		global $wpdb;
		list( $where, $params ) = self::build_filter( $args );
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $where uses %s placeholders bound via $params.
		$sql = 'SELECT COUNT(*) FROM ' . self::tickets_table() . " {$where}";
		if ( ! $params ) {
			return (int) $wpdb->get_var( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		}
		return (int) $wpdb->get_var( $wpdb->prepare( $sql, $params ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
	}

	/**
	 * Shared WHERE builder for query_tickets/count_tickets.
	 *
	 * @param array{status?:string,search?:string} $args
	 * @return array{0:string,1:array<int,mixed>}
	 */
	private static function build_filter( array $args ): array {
		global $wpdb;
		$clauses = array();
		$params  = array();

		$status = isset( $args['status'] ) ? sanitize_key( (string) $args['status'] ) : 'open';
		if ( '' !== $status && 'all' !== $status ) {
			$clauses[] = 'status = %s';
			$params[]  = $status;
		}

		$search = isset( $args['search'] ) ? trim( (string) $args['search'] ) : '';
		if ( '' !== $search ) {
			$like      = '%' . $wpdb->esc_like( $search ) . '%';
			$clauses[] = '(subject LIKE %s OR customer_email LIKE %s OR customer_name LIKE %s)';
			$params[]  = $like;
			$params[]  = $like;
			$params[]  = $like;
		}

		$priority = isset( $args['priority'] ) ? sanitize_key( (string) $args['priority'] ) : '';
		if ( '' !== $priority && 'all' !== $priority ) {
			$clauses[] = 'priority = %s';
			$params[]  = $priority;
		}

		$tag = isset( $args['tag'] ) ? self::tag_slug( (string) $args['tag'] ) : '';
		if ( '' !== $tag ) {
			// tags is a comma-joined slug list (",a,b,c,"); match the delimited slug.
			$clauses[] = 'CONCAT(\',\', tags, \',\') LIKE %s';
			$params[]  = '%,' . $wpdb->esc_like( $tag ) . ',%';
		}

		$where = $clauses ? 'WHERE ' . implode( ' AND ', $clauses ) : '';
		return array( $where, $params );
	}

	public static function set_status( int $ticket_id, string $status ): void {
		global $wpdb;
		$wpdb->update(
			self::tickets_table(),
			array(
				'status'     => sanitize_key( $status ),
				'updated_at' => current_time( 'mysql' ),
			),
			array( 'id' => $ticket_id )
		);
	}

	/** Priority levels, low→urgent. */
	public static function priorities(): array {
		return array(
			'low'    => __( 'Low', 'answyr' ),
			'normal' => __( 'Normal', 'answyr' ),
			'high'   => __( 'High', 'answyr' ),
			'urgent' => __( 'Urgent', 'answyr' ),
		);
	}

	public static function is_priority( string $priority ): bool {
		return array_key_exists( $priority, self::priorities() );
	}

	public static function set_priority( int $ticket_id, string $priority ): void {
		global $wpdb;
		$wpdb->update( self::tickets_table(), array( 'priority' => sanitize_key( $priority ) ), array( 'id' => $ticket_id ) );
	}

	/** Normalize a single tag to a slug. */
	public static function tag_slug( string $tag ): string {
		return sanitize_title( $tag );
	}

	/**
	 * Parse a free-form tag string ("Billing, Bug Report") into clean slugs.
	 *
	 * @return array<int,string>
	 */
	public static function parse_tags( string $raw ): array {
		$out = array();
		foreach ( explode( ',', $raw ) as $piece ) {
			$slug = self::tag_slug( $piece );
			if ( '' !== $slug && ! in_array( $slug, $out, true ) ) {
				$out[] = $slug;
			}
		}
		return $out;
	}

	/** @param array<int,string> $tags */
	public static function set_tags( int $ticket_id, array $tags ): void {
		global $wpdb;
		$clean = array();
		foreach ( $tags as $t ) {
			$slug = self::tag_slug( (string) $t );
			if ( '' !== $slug && ! in_array( $slug, $clean, true ) ) {
				$clean[] = $slug;
			}
		}
		$wpdb->update( self::tickets_table(), array( 'tags' => implode( ',', $clean ) ), array( 'id' => $ticket_id ) );
	}

	/** @return array<int,string> */
	public static function tags_of( ?object $ticket ): array {
		if ( ! $ticket || '' === (string) ( $ticket->tags ?? '' ) ) {
			return array();
		}
		return array_filter( explode( ',', (string) $ticket->tags ) );
	}

	/** Distinct tag slugs in use, for filter/suggestion UIs. @return array<int,string> */
	public static function all_tags(): array {
		global $wpdb;
		$rows = $wpdb->get_col( 'SELECT DISTINCT tags FROM ' . self::tickets_table() . " WHERE tags <> ''" ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$out  = array();
		foreach ( $rows as $row ) {
			foreach ( explode( ',', (string) $row ) as $slug ) {
				$slug = trim( $slug );
				if ( '' !== $slug && ! in_array( $slug, $out, true ) ) {
					$out[] = $slug;
				}
			}
		}
		sort( $out );
		return $out;
	}

	/** Stamp the first agent response time once, for response-time metrics. */
	public static function mark_first_response( int $ticket_id ): void {
		global $wpdb;
		$ticket = self::get_ticket( $ticket_id );
		if ( $ticket && null === $ticket->first_response_at ) {
			$wpdb->update( self::tickets_table(), array( 'first_response_at' => current_time( 'mysql' ) ), array( 'id' => $ticket_id ) );
		}
	}

	public static function set_rating( int $ticket_id, int $rating, string $comment = '' ): void {
		global $wpdb;
		$wpdb->update(
			self::tickets_table(),
			array(
				'rating'         => max( 1, min( 5, $rating ) ),
				'rating_comment' => sanitize_text_field( $comment ),
			),
			array( 'id' => $ticket_id )
		);
	}

	// ── Insights / metrics ─────────────────────────────────────────────────────

	/** @return array<string,int> status => count (every canonical status present). */
	public static function count_by_status(): array {
		global $wpdb;
		$rows = $wpdb->get_results( 'SELECT status, COUNT(*) AS n FROM ' . self::tickets_table() . ' GROUP BY status' ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$out  = array_fill_keys( array_keys( self::statuses() ), 0 );
		foreach ( $rows as $row ) {
			$out[ $row->status ] = (int) $row->n;
		}
		return $out;
	}

	/** Tickets created within the last $days, optionally scoped to a channel. */
	public static function created_since( int $days, ?string $channel = null ): int {
		global $wpdb;
		if ( null === $channel ) {
			return (int) $wpdb->get_var(
				$wpdb->prepare(
					'SELECT COUNT(*) FROM ' . self::tickets_table() . ' WHERE created_at >= ( NOW() - INTERVAL %d DAY )',
					$days
				)
			);
		}
		return (int) $wpdb->get_var(
			$wpdb->prepare(
				'SELECT COUNT(*) FROM ' . self::tickets_table() . ' WHERE channel = %s AND created_at >= ( NOW() - INTERVAL %d DAY )',
				sanitize_key( $channel ),
				$days
			)
		);
	}

	/** Total tickets that arrived via a given channel (e.g. 'chat' escalations). */
	public static function count_by_channel( string $channel ): int {
		global $wpdb;
		return (int) $wpdb->get_var(
			$wpdb->prepare(
				'SELECT COUNT(*) FROM ' . self::tickets_table() . ' WHERE channel = %s',
				sanitize_key( $channel )
			)
		);
	}

	/** Average first-response time in minutes, over tickets that have a response. */
	public static function avg_first_response_minutes(): ?float {
		global $wpdb;
		$avg = $wpdb->get_var( 'SELECT AVG( TIMESTAMPDIFF( MINUTE, created_at, first_response_at ) ) FROM ' . self::tickets_table() . ' WHERE first_response_at IS NOT NULL' ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		return null === $avg ? null : (float) $avg;
	}

	/** @return array{count:int,positive:int,avg:?float} CSAT roll-up (positive = rating >= 4). */
	public static function rating_summary(): array {
		global $wpdb;
		$row = $wpdb->get_row( 'SELECT COUNT(*) AS c, SUM( rating >= 4 ) AS p, AVG( rating ) AS a FROM ' . self::tickets_table() . ' WHERE rating IS NOT NULL' ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		return array(
			'count'    => (int) ( $row->c ?? 0 ),
			'positive' => (int) ( $row->p ?? 0 ),
			'avg'      => null === ( $row->a ?? null ) ? null : (float) $row->a,
		);
	}
}
