<?php
/**
 * Data access for documents.
 *
 * @package DropDMS
 */

defined( 'ABSPATH' ) || exit;

// Custom tables: direct queries are intended, values are always passed through $wpdb->prepare().
// phpcs:disable WordPress.DB.DirectDatabaseQuery

/**
 * Reads and writes rows of the documents table (without file contents).
 */
final class DropDMS_Documents {

	/** Allowed file types (checked on the server with finfo) and their extensions. */
	const ALLOWED = array(
		'application/pdf' => 'pdf',
		'image/jpeg'      => 'jpg',
		'image/png'       => 'png',
		'image/webp'      => 'webp',
		'image/gif'       => 'gif',
	);

	/** Columns returned by list queries (no OCR text, it can be large). */
	const LIST_COLUMNS = 'a.id, a.title, a.category_id, a.status, a.filename, a.mime, a.filesize, a.document_date, a.note, a.ocr_status, CHAR_LENGTH(a.ocr_text) AS ocr_chars, a.created, a.created_by, a.modified';

	/**
	 * One document with all columns.
	 *
	 * @param int $id Document ID.
	 * @return object|null
	 */
	public static function get( $id ) {
		global $wpdb;

		$row = $wpdb->get_row(
			$wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', DropDMS_Installer::documents_table(), $id )
		);

		return $row ? $row : null;
	}

	/**
	 * Finds a document by the SHA-256 hash of its content.
	 *
	 * @param string $hash Lower-case hex hash.
	 * @return object|null Object with id and title.
	 */
	public static function find_by_hash( $hash ) {
		global $wpdb;

		$row = $wpdb->get_row(
			$wpdb->prepare( 'SELECT id, title FROM %i WHERE sha256 = %s', DropDMS_Installer::documents_table(), $hash )
		);

		return $row ? $row : null;
	}

	/**
	 * Stores a new document including the file, inside one transaction.
	 *
	 * @param array       $doc   Columns: title, category_id, filename, mime, filesize, sha256, ocr_text.
	 * @param string      $path  Path of the uploaded file.
	 * @param string|null $thumb JPEG thumbnail or null.
	 * @return int New document ID.
	 * @throws RuntimeException When saving fails (the transaction is rolled back).
	 */
	public static function create( array $doc, $path, $thumb ) {
		global $wpdb;

		$wpdb->query( 'START TRANSACTION' );

		try {
			$ok = $wpdb->insert(
				DropDMS_Installer::documents_table(),
				array(
					'title'       => $doc['title'],
					'category_id' => (int) $doc['category_id'],
					'status'      => 1,
					'filename'    => $doc['filename'],
					'mime'        => $doc['mime'],
					'filesize'    => (int) $doc['filesize'],
					'sha256'      => $doc['sha256'],
					'ocr_text'    => $doc['ocr_text'],
					'ocr_status'  => '' !== $doc['ocr_text'] ? 'ok' : 'none',
					'created'     => current_time( 'mysql', true ),
					'created_by'  => get_current_user_id(),
				),
				array( '%s', '%d', '%d', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%d' )
			);

			if ( ! $ok ) {
				throw new RuntimeException( esc_html( $wpdb->last_error ) );
			}

			$id = (int) $wpdb->insert_id;

			$ok = $wpdb->insert(
				DropDMS_Installer::data_table(),
				array(
					'document_id' => $id,
					'data'        => '',
				),
				array( '%d', '%s' )
			);

			if ( ! $ok ) {
				throw new RuntimeException( esc_html( $wpdb->last_error ) );
			}

			// In one piece when max_allowed_packet and PHP memory allow it, otherwise in large blocks.
			DropDMS_Storage::write_file( $id, $path, (int) $doc['filesize'] );

			if ( null !== $thumb ) {
				DropDMS_Storage::store_thumb( $id, $thumb );
			}

			// Check: stored length == original size.
			$stored = (int) $wpdb->get_var(
				$wpdb->prepare( 'SELECT LENGTH(data) FROM %i WHERE document_id = %d', DropDMS_Installer::data_table(), $id )
			);

			if ( $stored !== (int) $doc['filesize'] ) {
				throw new RuntimeException( esc_html__( 'The stored file size does not match the upload.', 'dropdms' ) );
			}

			$wpdb->query( 'COMMIT' );
		} catch ( Throwable $e ) {
			$wpdb->query( 'ROLLBACK' );
			throw new RuntimeException( esc_html( $e->getMessage() ), 0, $e ); // phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- escaped.
		}

		DropDMS_Categories::update_counts();

		/**
		 * Fires after a document was added to the archive.
		 *
		 * @param int $id Document ID.
		 */
		do_action( 'dropdms_document_created', $id );

		return $id;
	}

	/**
	 * Updates editable columns.
	 *
	 * @param int   $id   Document ID.
	 * @param array $data Columns (title, category_id, status, document_date, note, ocr_text).
	 */
	public static function update( $id, array $data ) {
		global $wpdb;

		$formats = array(
			'title'         => '%s',
			'category_id'   => '%d',
			'status'        => '%d',
			'document_date' => '%s',
			'note'          => '%s',
			'ocr_text'      => '%s',
			'ocr_status'    => '%s',
			'modified'      => '%s',
		);

		if ( array_key_exists( 'ocr_text', $data ) ) {
			$data['ocr_status'] = '' !== (string) $data['ocr_text'] ? 'ok' : 'none';
		}

		$data['modified'] = current_time( 'mysql', true );
		$data             = array_intersect_key( $data, $formats );
		$format           = array();

		foreach ( array_keys( $data ) as $key ) {
			$format[] = $formats[ $key ]; // Same order as $data; wpdb writes null values as NULL.
		}

		$wpdb->update( DropDMS_Installer::documents_table(), $data, array( 'id' => (int) $id ), $format, array( '%d' ) );

		if ( array_key_exists( 'category_id', $data ) || array_key_exists( 'status', $data ) ) {
			DropDMS_Categories::update_counts();
		}

		/**
		 * Fires after a document was updated.
		 *
		 * @param int   $id   Document ID.
		 * @param array $data Updated columns.
		 */
		do_action( 'dropdms_document_updated', (int) $id, $data );
	}

	/**
	 * Deletes documents including their files.
	 *
	 * @param int[] $ids Document IDs.
	 * @return int Number of deleted documents.
	 */
	public static function delete( array $ids ) {
		global $wpdb;

		$ids     = array_filter( array_map( 'absint', $ids ) );
		$deleted = 0;

		foreach ( $ids as $id ) {
			$rows = (int) $wpdb->delete( DropDMS_Installer::documents_table(), array( 'id' => $id ), array( '%d' ) );
			$wpdb->delete( DropDMS_Installer::data_table(), array( 'document_id' => $id ), array( '%d' ) );

			if ( $rows > 0 ) {
				++$deleted;

				/**
				 * Fires after a document was deleted.
				 *
				 * @param int $id Document ID.
				 */
				do_action( 'dropdms_document_deleted', $id );
			}
		}

		DropDMS_Categories::update_counts();

		return $deleted;
	}

	/**
	 * Lists documents.
	 *
	 * @param array $args {
	 *     @type string $search      Search terms.
	 *     @type int[]  $categories  Restrict to these categories (0 = without category).
	 *     @type int    $status      1 = published only, 0 = unpublished only, null = all.
	 *     @type string $date_from   Y-m-d, compares the document date.
	 *     @type string $date_to     Y-m-d.
	 *     @type string $orderby     Column.
	 *     @type string $order       ASC or DESC.
	 *     @type int    $per_page    Rows per page.
	 *     @type int    $page        1-based page.
	 * }
	 * @return array{items: object[], total: int}
	 */
	public static function query( array $args ) {
		global $wpdb;

		$args = array_merge(
			array(
				'search'     => '',
				'categories' => null,
				'status'     => null,
				'date_from'  => '',
				'date_to'    => '',
				'orderby'    => 'created',
				'order'      => 'DESC',
				'per_page'   => 20,
				'page'       => 1,
			),
			$args
		);

		$where  = array( '1=1' );
		$params = array( DropDMS_Installer::documents_table() );

		if ( is_array( $args['categories'] ) ) {
			$cats = array_values( array_unique( array_map( 'absint', $args['categories'] ) ) );

			if ( ! $cats ) {
				return array(
					'items' => array(),
					'total' => 0,
				);
			}

			$where[] = 'a.category_id IN (' . implode( ',', array_fill( 0, count( $cats ), '%d' ) ) . ')';
			$params  = array_merge( $params, $cats );
		}

		if ( null !== $args['status'] ) {
			$where[]  = 'a.status = %d';
			$params[] = (int) $args['status'];
		}

		foreach ( array( 'date_from' => '>=', 'date_to' => '<=' ) as $key => $op ) {
			if ( preg_match( '/^\d{4}-\d{2}-\d{2}$/', (string) $args[ $key ] ) ) {
				$where[]  = 'a.document_date ' . $op . ' %s';
				$params[] = $args[ $key ];
			}
		}

		$search = DropDMS_Search::where( (string) $args['search'] );

		if ( $search['sql'] ) {
			$where[] = $search['sql'];
			$params  = array_merge( $params, $search['args'] );
		}

		$columns = array( 'id', 'title', 'category_id', 'filesize', 'document_date', 'created', 'ocr_chars' );
		$orderby = in_array( $args['orderby'], $columns, true ) ? $args['orderby'] : 'created';
		$order   = 'ASC' === strtoupper( (string) $args['order'] ) ? 'ASC' : 'DESC';
		$sort    = 'ocr_chars' === $orderby ? 'ocr_chars' : 'a.' . $orderby;

		$per_page = max( 1, min( 500, (int) $args['per_page'] ) );
		$offset   = ( max( 1, (int) $args['page'] ) - 1 ) * $per_page;

		$where_sql = implode( ' AND ', $where );

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders, PluginCheck.Security.DirectDB.UnescapedDBParameter -- WHERE built from fixed fragments with placeholders above, values passed to prepare().
		$total = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i AS a WHERE {$where_sql}", $params ) );
		$items = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT ' . self::LIST_COLUMNS . " FROM %i AS a WHERE {$where_sql} ORDER BY {$sort} {$order}, a.id {$order} LIMIT %d OFFSET %d",
				array_merge( $params, array( $per_page, $offset ) )
			)
		);
		// phpcs:enable

		return array(
			'items' => $items ? $items : array(),
			'total' => $total,
		);
	}

	/**
	 * Removes invalid characters from recognized text and limits its length.
	 *
	 * @param string $text Raw text.
	 */
	public static function clean_text( $text ) {
		$text = mb_convert_encoding( (string) $text, 'UTF-8', 'UTF-8' );
		$text = (string) preg_replace( '/[^\P{C}\n\r\t]+/u', '', $text );
		$text = (string) preg_replace( "/\r\n?/", "\n", $text );

		return mb_substr( trim( $text ), 0, 2000000 );
	}

	/**
	 * Cleans the original file name and adjusts the extension to the detected type.
	 *
	 * @param string $name Original name.
	 * @param string $ext  Extension of the detected type.
	 */
	public static function clean_name( $name, $ext ) {
		$name = wp_basename( str_replace( '\\', '/', (string) $name ) );
		$name = (string) preg_replace( '/[[:cntrl:]]+/', '', $name );
		$name = mb_substr( trim( $name ), 0, 200 );

		if ( '' === $name ) {
			return 'document.' . $ext;
		}

		// E.g. a PNG named .pdf.
		$current = strtolower( pathinfo( $name, PATHINFO_EXTENSION ) );

		if ( $current !== $ext && ! ( 'jpg' === $ext && 'jpeg' === $current ) ) {
			if ( in_array( $current, array( 'pdf', 'jpg', 'jpeg', 'png', 'webp', 'gif' ), true ) ) {
				$name = pathinfo( $name, PATHINFO_FILENAME );
			}

			$name .= '.' . $ext;
		}

		return $name;
	}
}