<?php

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

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- 24laser Support Tickets owns these custom tables; repository reads/writes intentionally bypass the posts API, and persistent object caching could expose stale ticket state.

/**
 * Ticket persistence used by the WordPress.org directory package.
 *
 * The repository keeps backward-compatible ticket fields where they already
 * exist, while this directory package only reads and writes the fields it owns.
 */
class DPSUPPORT_Ticket_Repository {

	private function table() {
		global $wpdb;
		return $wpdb->prefix . 'dpsupport_tickets';
	}

	public function create( array $data ) {
		global $wpdb;

		$now = current_time( 'mysql' );
		$row = array(
			'ticket_number'    => $this->generate_ticket_number(),
			'customer_id'      => ! empty( $data['customer_id'] ) ? absint( $data['customer_id'] ) : null,
			'customer_name'    => sanitize_text_field( $data['customer_name'] ?? '' ),
			'customer_email'   => sanitize_email( $data['customer_email'] ?? '' ),
			'subject'          => sanitize_text_field( $data['subject'] ?? '' ),
			'category'         => $this->sanitize_category( $data['category'] ?? get_option( 'dpsupport_default_category', 'allgemein' ) ),
			'status'           => $this->sanitize_status( $data['status'] ?? get_option( 'dpsupport_default_status', 'new' ) ),
			'priority'         => $this->sanitize_priority( $data['priority'] ?? get_option( 'dpsupport_default_priority', 'normal' ) ),
			'created_by'       => get_current_user_id() ?: null,
			'created_at'       => $now,
			'updated_at'       => $now,
			'closed_at'        => null,
			'archived_at'      => null,
		);

		$ok = $wpdb->insert(
			$this->table(),
			$row,
			array( '%s', '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s' )
		);

		if ( false === $ok ) {
			return new WP_Error( 'dpsupport_ticket_create_failed', __( 'Ticket konnte nicht gespeichert werden.', '24laser-support-tickets' ) );
		}

		return (int) $wpdb->insert_id;
	}

	public function get( $ticket_id ) {
		global $wpdb;

		return $wpdb->get_row(
			$wpdb->prepare(
				'SELECT * FROM %i WHERE id = %d LIMIT 1',
				$this->table(),
				absint( $ticket_id )
			)
		);
	}

	public function get_for_customer( $customer_id, $customer_email = '', $per_page = 25, $paged = 1 ) {
		global $wpdb;

		$customer_id    = absint( $customer_id );
		$customer_email = sanitize_email( $customer_email );
		$per_page       = max( 1, min( 100, absint( $per_page ) ) );
		$paged          = max( 1, absint( $paged ) );
		$offset         = ( $paged - 1 ) * $per_page;

		if ( ! $customer_id ) {
			return array();
		}

		if ( $customer_email ) {
			$results = $wpdb->get_results(
				$wpdb->prepare(
					'SELECT * FROM %i WHERE archived_at IS NULL AND (customer_id = %d OR ((customer_id IS NULL OR customer_id = 0) AND customer_email = %s)) ORDER BY updated_at DESC LIMIT %d OFFSET %d',
					$this->table(),
					$customer_id,
					$customer_email,
					$per_page,
					$offset
				)
			);
		} else {
			$results = $wpdb->get_results(
				$wpdb->prepare(
					'SELECT * FROM %i WHERE archived_at IS NULL AND customer_id = %d ORDER BY updated_at DESC LIMIT %d OFFSET %d',
					$this->table(),
					$customer_id,
					$per_page,
					$offset
				)
			);
		}

		return is_array( $results ) ? $results : array();
	}

	public function count_for_customer( $customer_id, $customer_email = '' ) {
		global $wpdb;

		$customer_id    = absint( $customer_id );
		$customer_email = sanitize_email( $customer_email );
		if ( ! $customer_id ) {
			return 0;
		}

		if ( $customer_email ) {
			return (int) $wpdb->get_var(
				$wpdb->prepare(
					'SELECT COUNT(*) FROM %i WHERE archived_at IS NULL AND (customer_id = %d OR ((customer_id IS NULL OR customer_id = 0) AND customer_email = %s))',
					$this->table(),
					$customer_id,
					$customer_email
				)
			);
		}

		return (int) $wpdb->get_var(
			$wpdb->prepare(
				'SELECT COUNT(*) FROM %i WHERE archived_at IS NULL AND customer_id = %d',
				$this->table(),
				$customer_id
			)
		);
	}

	public function customer_owns_ticket( $ticket, $customer_id, $customer_email = '' ) {
		if ( ! $ticket || ! $customer_id ) {
			return false;
		}

		if ( (int) $ticket->customer_id === (int) $customer_id ) {
			return true;
		}

		return empty( $ticket->customer_id )
			&& $customer_email
			&& strtolower( (string) $ticket->customer_email ) === strtolower( sanitize_email( $customer_email ) );
	}

	public function update( $ticket_id, array $data ) {
		global $wpdb;

		$allowed = array();
		$formats = array();

		if ( isset( $data['status'] ) ) {
			$allowed['status']    = $this->sanitize_status( $data['status'] );
			$allowed['closed_at'] = 'closed' === $allowed['status'] ? current_time( 'mysql' ) : null;
			$formats[]            = '%s';
			$formats[]            = '%s';
		}

		if ( isset( $data['priority'] ) ) {
			$allowed['priority'] = $this->sanitize_priority( $data['priority'] );
			$formats[] = '%s';
		}

		if ( isset( $data['category'] ) ) {
			$allowed['category'] = $this->sanitize_category( $data['category'] );
			$formats[] = '%s';
		}

		if ( array_key_exists( 'customer_id', $data ) ) {
			$allowed['customer_id'] = ! empty( $data['customer_id'] ) ? absint( $data['customer_id'] ) : null;
			$formats[] = '%d';
		}

		if ( isset( $data['customer_name'] ) ) {
			$allowed['customer_name'] = sanitize_text_field( $data['customer_name'] );
			$formats[] = '%s';
		}

		if ( isset( $data['customer_email'] ) ) {
			$allowed['customer_email'] = sanitize_email( $data['customer_email'] );
			$formats[] = '%s';
		}

		if ( isset( $data['subject'] ) ) {
			$allowed['subject'] = sanitize_text_field( $data['subject'] );
			$formats[] = '%s';
		}

		if ( ! $allowed ) {
			return true;
		}

		$allowed['updated_at'] = current_time( 'mysql' );
		$formats[] = '%s';

		$result = $wpdb->update(
			$this->table(),
			$allowed,
			array( 'id' => absint( $ticket_id ) ),
			$formats,
			array( '%d' )
		);

		return false !== $result;
	}

	public function touch( $ticket_id ) {
		global $wpdb;

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

	public function archive( $ticket_id ) {
		global $wpdb;

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

	public function restore( $ticket_id ) {
		global $wpdb;

		return false !== $wpdb->query(
			$wpdb->prepare(
				'UPDATE %i SET archived_at = NULL, updated_at = %s WHERE id = %d',
				$this->table(),
				current_time( 'mysql' ),
				absint( $ticket_id )
			)
		);
	}

	public function delete_permanently( $ticket_id ) {
		global $wpdb;

		$ticket_id = absint( $ticket_id );
		if ( ! $ticket_id ) {
			return false;
		}

		$wpdb->delete( $wpdb->prefix . 'dpsupport_attachments', array( 'ticket_id' => $ticket_id ), array( '%d' ) );
		$wpdb->delete( $wpdb->prefix . 'dpsupport_activity', array( 'ticket_id' => $ticket_id ), array( '%d' ) );
		$wpdb->delete( $wpdb->prefix . 'dpsupport_messages', array( 'ticket_id' => $ticket_id ), array( '%d' ) );

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

	public function query( array $args = array() ) {
		global $wpdb;

		$defaults = array(
			'status'   => '',
			'priority' => '',
			'category' => '',
			'search'   => '',
			'paged'    => 1,
			'per_page' => 20,
			'archive'  => 'active',
		);

		$args   = wp_parse_args( $args, $defaults );
		$where  = array( '1=1' );
		$params = array();
		$this->apply_filters_to_where( $args, $where, $params );

		$per_page = max( 1, min( 100, absint( $args['per_page'] ) ) );
		$paged    = max( 1, absint( $args['paged'] ) );
		$offset   = ( $paged - 1 ) * $per_page;
		$sql          = 'SELECT * FROM %i WHERE ' . implode( ' AND ', $where ) . ' ORDER BY updated_at DESC LIMIT %d OFFSET %d';
		$prepare_args = array_merge( array( $this->table() ), $params, array( $per_page, $offset ) );

		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- SQL fragments come only from fixed plugin-owned clauses; identifiers and all external values are prepared below.
		return $wpdb->get_results(
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- WHERE fragments are selected only from fixed plugin-owned clauses; all values and the table identifier use placeholders.
			$wpdb->prepare( $sql, $prepare_args )
		);
	}

	public function count( array $args = array() ) {
		global $wpdb;

		$defaults = array(
			'status'   => '',
			'priority' => '',
			'category' => '',
			'search'   => '',
			'archive'  => 'active',
		);

		$args   = wp_parse_args( $args, $defaults );
		$where  = array( '1=1' );
		$params = array();
		$this->apply_filters_to_where( $args, $where, $params );

		$sql          = 'SELECT COUNT(*) FROM %i WHERE ' . implode( ' AND ', $where );
		$prepare_args = array_merge( array( $this->table() ), $params );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Read from the plugin's own custom table; all identifiers/values are prepared below.
		return (int) $wpdb->get_var(
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- WHERE fragments are selected only from fixed plugin-owned clauses; all values and the table identifier use placeholders.
			$wpdb->prepare( $sql, $prepare_args )
		);
	}

	private function apply_filters_to_where( array $args, array &$where, array &$params ) {
		global $wpdb;

		if ( 'archived' === $args['archive'] ) {
			$where[] = 'archived_at IS NOT NULL';
		} elseif ( 'all' !== $args['archive'] ) {
			$where[] = 'archived_at IS NULL';
		}

		if ( $args['status'] && array_key_exists( $args['status'], $this->statuses() ) ) {
			$where[]  = 'status = %s';
			$params[] = $args['status'];
		}

		if ( $args['priority'] && array_key_exists( $args['priority'], $this->priorities() ) ) {
			$where[]  = 'priority = %s';
			$params[] = $args['priority'];
		}

		if ( $args['category'] && array_key_exists( $args['category'], $this->categories() ) ) {
			$where[]  = 'category = %s';
			$params[] = $args['category'];
		}

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

	public function statuses() {
		return array(
			'new'              => __( 'Neu', '24laser-support-tickets' ),
			'open'             => __( 'Offen', '24laser-support-tickets' ),
			'waiting_customer' => __( 'Warten auf Kunde', '24laser-support-tickets' ),
			'waiting_internal' => __( 'Warten intern', '24laser-support-tickets' ),
			'resolved'         => __( 'Gelöst', '24laser-support-tickets' ),
			'closed'           => __( 'Geschlossen', '24laser-support-tickets' ),
		);
	}

	public function priorities() {
		return array(
			'low'    => __( 'Niedrig', '24laser-support-tickets' ),
			'normal' => __( 'Normal', '24laser-support-tickets' ),
			'high'   => __( 'Hoch', '24laser-support-tickets' ),
			'urgent' => __( 'Dringend', '24laser-support-tickets' ),
		);
	}

	public function categories() {
		$categories = get_option( 'dpsupport_categories', array() );

		if ( ! is_array( $categories ) || ! $categories ) {
			$categories = array(
				'allgemein' => __( 'Allgemein', '24laser-support-tickets' ),
				'sonstiges' => __( 'Sonstiges', '24laser-support-tickets' ),
			);
		}

		return $categories;
	}

	public function sanitize_status( $status ) {
		$status = sanitize_key( $status );
		return array_key_exists( $status, $this->statuses() ) ? $status : 'new';
	}

	public function sanitize_priority( $priority ) {
		$priority = sanitize_key( $priority );
		return array_key_exists( $priority, $this->priorities() ) ? $priority : 'normal';
	}

	public function sanitize_category( $category ) {
		$category   = sanitize_key( $category );
		$categories = $this->categories();

		if ( array_key_exists( $category, $categories ) ) {
			return $category;
		}

		$default = sanitize_key( get_option( 'dpsupport_default_category', 'allgemein' ) );
		return array_key_exists( $default, $categories ) ? $default : array_key_first( $categories );
	}

	private function generate_ticket_number() {
		global $wpdb;

		do {
			$number = 'DP-' . wp_date( 'ymd' ) . '-' . strtoupper( wp_generate_password( 5, false, false ) );
			$exists = (int) $wpdb->get_var(
				$wpdb->prepare(
					'SELECT COUNT(*) FROM %i WHERE ticket_number = %s',
					$this->table(),
					$number
				)
			);
		} while ( $exists > 0 );

		return $number;
	}
}

// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching