<?php
/**
 * Case data repository.
 *
 * @package AarambhaCaseManagement
 */

namespace AarambhaCaseManagement\Database;

defined( 'ABSPATH' ) || exit;

/**
 * Handles all database access for cases.
 */
class CaseRepository {

	/**
	 * Cases table.
	 *
	 * @var string
	 */
	private string $table;

	/**
	 * Columns allowed for ORDER BY, to prevent SQL injection via identifiers.
	 *
	 * @var array
	 */
	private const ALLOWED_ORDERBY = [
		'case_number',
		'customer_name',
		'customer_email',
		'status',
		'created_at',
		'updated_at',
	];

	/**
	 * Constructor.
	 */
	public function __construct() {

		$this->table = Database::get_table( Database::TABLE_CASES );
	}

	/**
	 * Format a case number.
	 *
	 * @param int $id Case ID.
	 * @return string
	 */
	private function format_case_number( int $id ): string {

		return sprintf( 'CM-%06d', $id );
	}

	/**
	 * Create a new case.
	 *
	 * @param array $data Case data.
	 * @return array
	 */
	public function create( array $data ): array {

		global $wpdb;
		$now = current_time( 'mysql' );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom plugin table, no core API available.
		$inserted = $wpdb->insert(
			$this->table,
			[
				'case_number'    => '',
				'customer_name'  => $data['customer_name'],
				'customer_email' => $data['customer_email'],
				'phone'          => $data['phone'],
				'subject'        => $data['subject'],
				'description'    => $data['description'],
				'status'         => 'pending',
				'created_at'     => $now,
				'updated_at'     => $now,
			],
			[
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
			]
		);

		if ( false === $inserted ) {
			return [];
		}

		$case_id     = (int) $wpdb->insert_id;
		$case_number = $this->format_case_number( $case_id );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table, no core API available; single-row update by primary key immediately after insert, not a good caching candidate.
		$wpdb->update(
			$this->table,
			[
				'case_number' => $case_number,
			],
			[
				'id' => $case_id,
			],
			[
				'%s',
			],
			[
				'%d',
			]
		);
		// phpcs:enable

		return [
			'id'          => $case_id,
			'case_number' => $case_number,
		];
	}

	/**
	 * Find a case by ID.
	 *
	 * @param int $id Case ID.
	 * @return array|null
	 */
	public function find( int $id ): ?array {

		global $wpdb;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		// Custom plugin table, no core API available. Single-row lookup by primary key;
		// caching adds complexity with limited benefit for this admin-only, low-traffic table.
		// {$this->table} is a table name built internally from Database::get_table(), never
		// user input; the actual query value is passed through prepare()'s %d placeholder.
		$case = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$this->table} WHERE id = %d",
				$id
			),
			ARRAY_A
		);
		// phpcs:enable

		return $case ?: null;
	}

	/**
	 * Get all cases.
	 *
	 * @param string $orderby  Column to sort by. Must be one of ALLOWED_ORDERBY.
	 * @param string $order    Sort direction, ASC or DESC.
	 * @param int    $per_page Number of results per page.
	 * @param int    $page     Current page number.
	 * @return array
	 */
	public function find_all(
		string $orderby = 'created_at',
		string $order = 'DESC',
		int $per_page = 10,
		int $page = 1
	): array {

		global $wpdb;

		// Whitelist orderby/order before use, since these can't be passed through $wpdb->prepare() as identifiers.
		if ( ! in_array( $orderby, self::ALLOWED_ORDERBY, true ) ) {
			$orderby = 'created_at';
		}

		$order = strtoupper( $order );
		if ( ! in_array( $order, [ 'ASC', 'DESC' ], true ) ) {
			$order = 'DESC';
		}

		$offset = ( $page - 1 ) * $per_page;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		// Custom plugin table, no core API available. Paginated/sorted listing; not a good
		// caching candidate since inputs vary per request. {$this->table} and {$wpdb->prefix}
		// are internal, never user input. {$orderby}/{$order} are validated against a fixed
		// whitelist above, not raw user input.
		return $wpdb->get_results(
			$wpdb->prepare(
				"
                SELECT
                    c.*,
                    COUNT(cf.attachment_id) AS attachment_count,
                    MIN(cf.attachment_id) AS preview_attachment
                FROM {$this->table} c
                LEFT JOIN {$wpdb->prefix}aarambha_case_management_case_files cf
                    ON c.id = cf.case_id
                GROUP BY c.id
                ORDER BY {$orderby} {$order}
                LIMIT %d OFFSET %d
                ",
				$per_page,
				$offset
			),
			ARRAY_A
		);
		// phpcs:enable
	}

	/**
	 * Delete a case.
	 *
	 * @param int $id Case ID.
	 * @return bool
	 */
	public function delete( int $id ): bool {

		global $wpdb;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table, no core API available; single-row delete by primary key, not a good caching candidate.
		return false !== $wpdb->delete(
			$this->table,
			[
				'id' => $id,
			],
			[
				'%d',
			]
		);
		// phpcs:enable
	}

	/**
	 * Update a case.
	 *
	 * @param int   $id   Case ID.
	 * @param array $data Updated data.
	 * @return bool
	 */
	public function update( int $id, array $data ): bool {

		global $wpdb;

		$data['updated_at'] = current_time( 'mysql' );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table, no core API available; single-row update by primary key, not a good caching candidate.
		$updated = $wpdb->update(
			$this->table,
			$data,
			[
				'id' => $id,
			]
		);
		// phpcs:enable

		return false !== $updated;
	}

	/**
	 * Count all cases.
	 *
	 * @return int
	 */
	public function count(): int {

		global $wpdb;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		// Custom plugin table, no core API available. Simple count used for admin pagination;
		// low query cost, not worth cache invalidation complexity. {$this->table} is a table
		// name built internally from Database::get_table(), never user input; no query values
		// to prepare.
		return (int) $wpdb->get_var(
			"SELECT COUNT(*) FROM {$this->table}"
		);
		// phpcs:enable
	}

	/**
	 * Update a case status.
	 *
	 * @param int    $case_id Case ID.
	 * @param string $status  New status.
	 *
	 * @return bool
	 */
	public function update_status( int $case_id, string $status ): bool {

		global $wpdb;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table, no core API available; single-row update by primary key, not a good caching candidate.
		$updated = $wpdb->update(
			$this->table,
			[
				'status'     => $status,
				'updated_at' => current_time( 'mysql' ),
			],
			[
				'id' => $case_id,
			],
			[
				'%s',
				'%s',
			],
			[
				'%d',
			]
		);
		// phpcs:enable

		return false !== $updated;
	}
}