<?php
/**
 * Class to interact with the database for the accessible_docs table.
 *
 * @link       https://group107.com
 * @since      1.0.0
 *
 * @package    accessible-docs
 * @subpackage accessible-docs/includes
 * @author     Group107 <contact@group107.com>
 */

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

class Group107_AccessibleDocs_Db_Handler {

    /**
     * Version of the table schema. Bump it when the CREATE TABLE statement changes.
     */
    const DB_VERSION = '1';

    /**
     * Option (per site) that stores the installed schema version.
     */
    const DB_VERSION_OPTION = 'accessible_docs_db_version';

    /**
     * Create or upgrade the table of the current site.
     *
     * @return void
     */
    public static function create_table() {
        global $wpdb;
        $table_name = self::get_table_name();
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE $table_name (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            email varchar(255) NOT NULL,
            date datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
            file_id bigint(20) UNSIGNED NOT NULL,
            accessible_docs_file_id varchar(255) NULL,
            accessible_docs_status varchar(255) NOT NULL,
            PRIMARY KEY  (id)
        ) $charset_collate;";

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        dbDelta($sql);
        update_option(self::DB_VERSION_OPTION, self::DB_VERSION);
    }

    /**
     * Create the table if it is missing or outdated on the current site.
     *
     * Covers sites where the activation hook did not run for them: network activation,
     * sites added to a network later and plugin updates.
     *
     * @return void
     */
    public static function maybe_create_table() {
        if (get_option(self::DB_VERSION_OPTION) !== self::DB_VERSION) {
            self::create_table();
        }
    }

    /**
     * Requests that are still being processed, oldest status check first.
     *
     * @param int $limit    Maximum number of rows.
     * @param int $max_days Ignore requests older than this many days.
     * @return array Rows with id, accessible_docs_file_id.
     */
    public static function get_in_process_records($limit = 25, $max_days = 30) {
        global $wpdb;
        $table_name = esc_sql(self::get_table_name());
        $query = $wpdb->prepare(
            "SELECT id, accessible_docs_file_id FROM `{$table_name}` WHERE accessible_docs_status = 'in-process' AND accessible_docs_file_id <> '' AND date >= %s ORDER BY date ASC LIMIT %d",
            gmdate('Y-m-d H:i:s', time() - $max_days * DAY_IN_SECONDS + (int) (get_option('gmt_offset') * HOUR_IN_SECONDS)),
            $limit
        );
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        return $wpdb->get_results($query, ARRAY_A);
    }

    /**
     * Mark every request for this AccessibleDocs file as completed.
     *
     * @param string $accessible_docs_file_id File id in the AccessibleDocs system.
     * @return int|false Number of updated rows.
     */
    public static function mark_completed_by_accessible_docs_id($accessible_docs_file_id) {
        global $wpdb;
        return $wpdb->update(
            self::get_table_name(),
            array('accessible_docs_status' => 'completed'),
            array('accessible_docs_file_id' => $accessible_docs_file_id),
            array('%s'),
            array('%s')
        );
    }

    /**
     * Get the database table name.
     *
     * @return string
     */
    public static function get_table_name() {
        global $wpdb;
        return $wpdb->prefix . 'accessible_docs';
    }

    /**
     * Save email, file_id, accessible_docs_file_id, and accessible_docs_status to the database.
     *
     * @param string $email The email address.
     * @param int    $file_id The file ID from the media library.
     * @param string $accessible_docs_file_id File id in AccessibleDocs system.
     * @return bool True if insertion was successful, false otherwise.
     */
    public static function save_accessible_docs_file($email, $file_id, $accessible_docs_file_id) {
        global $wpdb;
        // Get table name and insert it with backticks.
        $table_name = self::get_table_name();
        // Prepare data to insert.
        $data = array(
            'email'                   => sanitize_email($email),
            'date'                    => current_time('mysql'),
            'file_id'                 => intval($file_id),
            'accessible_docs_file_id' => $accessible_docs_file_id,
            'accessible_docs_status'  => 'in-process',
        );
        // Insert the data into the database.
        $inserted = $wpdb->insert($table_name, $data);
        // Return whether the insertion was successful.
        return (bool) $inserted;
    }

    /**
     * Check if a record exists with the provided email and file_id.
     * If it exists, returns accessible_docs_file_id and accessible_docs_status, otherwise returns false.
     *
     * @param string $email The email address to check.
     * @param int    $file_id The file ID to check.
     * @return mixed The record if it exists, false otherwise.
     */
    public static function check_existing_accessible_docs_file($email, $file_id) {
        global $wpdb;
        $table_name = esc_sql(self::get_table_name());
        $query = $wpdb->prepare(
            "SELECT accessible_docs_file_id, accessible_docs_status FROM `{$table_name}` WHERE email = %s AND file_id = %d",
            $email,
            $file_id
        );
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $result = $wpdb->get_row($query, ARRAY_A);
        return $result ?: false;
    }


    /**
     * Update the accessible_docs_status to 'completed' for a given file_id and email.
     *
     * @param int    $file_id The file ID.
     * @param string $email   The email address.
     */
    public static function accessible_docs_update_file_status($file_id, $email) {
        global $wpdb;
        $table_name = self::get_table_name();
        $wpdb->update(
            $table_name,
            array('accessible_docs_status' => 'completed'),
            array('file_id' => $file_id, 'email' => $email),
            array('%s'),
            array('%d', '%s')
        );
    }

    /**
     * Update accessible_docs_file_id and accessible_docs_status to 'in-process' for a given file_id and email.
     *
     * @param int    $file_id The file ID.
     * @param string $email   The email address.
     * @param string $accessible_docs_file_id AccessibleDocs file id.
     * @return int|false The number of rows updated, or false on error.
     */
    public static function accessible_docs_update_file_id($file_id, $email, $accessible_docs_file_id) {
        global $wpdb;
        $table_name = self::get_table_name();
        return $wpdb->update(
            $table_name,
            array('accessible_docs_status' => 'in-process', 'accessible_docs_file_id' => $accessible_docs_file_id),
            array('file_id' => $file_id, 'email' => $email),
            array('%s', '%s'),
            array('%d', '%s')
        );
    }

    /**
     * Get all documents from the accessible_docs table with optional search, date range, status filter, and pagination.
     *
     * @since 1.0.0
     *
     * @param string $search_query  (Optional) Search query to filter by email or document title.
     * @param string $start_date    (Optional) Start date for filtering (format: Y-m-d).
     * @param string $end_date      (Optional) End date for filtering (format: Y-m-d).
     * @param string $status_filter (Optional) Status to filter documents by.
     * @param int    $paged         (Optional) Current page number (default is 1).
     * @param int    $items_per_page (Optional) Number of items to display per page (default is 20).
     *
     * @return array List of documents matching the filters and pagination.
     */
    public static function accessible_docs_get_documents($search_query = '', $start_date = '', $end_date = '', $status_filter = '', $paged = 1, $items_per_page = 20) {
        global $wpdb;
        $table_name = esc_sql(self::get_table_name());
        $posts_table = esc_sql($wpdb->posts);
        $offset = ($paged - 1) * $items_per_page;
        $search_query = trim($search_query);
        $search_query = preg_replace('/\.[a-zA-Z0-9]+$/', '', $search_query);
        $search_query = sanitize_text_field($search_query);

        $query = "SELECT * FROM `{$table_name}` WHERE 1=1";
        $params = array();

        if (!empty($search_query)) {
            $query .= " AND (email LIKE %s OR file_id IN (SELECT ID FROM `{$posts_table}` WHERE post_name LIKE %s))";
            $params[] = '%' . $wpdb->esc_like($search_query) . '%';
            $params[] = '%' . sanitize_title($search_query) . '%';
        }
        if (!empty($start_date)) {
            $query .= " AND date >= %s";
            $params[] = $start_date . ' 00:00:00';
        }
        if (!empty($end_date)) {
            $query .= " AND date <= %s";
            $params[] = $end_date . ' 23:59:59';
        }
        if (!empty($status_filter)) {
            $query .= " AND accessible_docs_status = %s";
            $params[] = $status_filter;
        }
        $query .= " ORDER BY date DESC LIMIT %d OFFSET %d";
        $params[] = $items_per_page;
        $params[] = $offset;
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        return $wpdb->get_results($wpdb->prepare($query, ...$params));
    }

    /**
     * Get the total count of documents in the accessible_docs table with optional filters.
     *
     * @since 1.0.0
     *
     * @param string $search_query  (Optional) Search query to filter by email or document title.
     * @param string $start_date    (Optional) Start date for filtering (format: Y-m-d).
     * @param string $end_date      (Optional) End date for filtering (format: Y-m-d).
     * @param string $status_filter (Optional) Status to filter documents by.
     *
     * @return int Total count of documents matching the filters.
     */
    public static function accessible_docs_get_documents_count($search_query = '', $start_date = '', $end_date = '', $status_filter = '') {
        global $wpdb;
        $table_name = esc_sql(self::get_table_name());
        $posts_table = esc_sql($wpdb->posts);
        $search_query = trim($search_query);
        $search_query = preg_replace('/\.[a-zA-Z0-9]+$/', '', $search_query);
        $search_query = sanitize_text_field($search_query);

        $query = "SELECT COUNT(*) FROM `{$table_name}` WHERE 1=1";
        $params = array();

        if (!empty($search_query)) {
            $query .= " AND (email LIKE %s OR file_id IN (SELECT ID FROM `{$posts_table}` WHERE post_name LIKE %s))";
            $params[] = '%' . $wpdb->esc_like($search_query) . '%';
            $params[] = '%' . sanitize_title($search_query) . '%';
        }
        if (!empty($start_date)) {
            $query .= " AND date >= %s";
            $params[] = $start_date . ' 00:00:00';
        }
        if (!empty($end_date)) {
            $query .= " AND date <= %s";
            $params[] = $end_date . ' 23:59:59';
        }
        if (!empty($status_filter)) {
            $query .= " AND accessible_docs_status = %s";
            $params[] = $status_filter;
        }
        if (!empty($params)) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            return (int) $wpdb->get_var($wpdb->prepare($query, ...$params));
        } else {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            return (int) $wpdb->get_var($query);
        }
    }


    /**
     * Clear all records from the accessible_docs table.
     *
     * @since 1.0.0
     */
    public static function accessible_docs_clear_table() {
        global $wpdb;
        $table_name = esc_sql(self::get_table_name());
        $query = "TRUNCATE TABLE `{$table_name}`";
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        return $wpdb->query($query);
    }


    /**
     * Deletes selected records from the accessible documents table.
     *
     * @param array $ids Array of record IDs to delete.
     * @return bool True on success, false on failure.
     */
    public static function accessible_docs_delete_selected_records($ids) {
        global $wpdb;
        if (empty($ids) || !is_array($ids)) {
            return false;
        }
        $table_name = esc_sql(self::get_table_name());
        $placeholders = implode(',', array_fill(0, count($ids), '%d'));
        $query = $wpdb->prepare(
            "DELETE FROM `{$table_name}` WHERE id IN ($placeholders)",
            ...$ids
        );
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        return (bool) $wpdb->query($query);
    }

}