<?php
/**
 * Database operations for the plugin.
 *
 * This class handles all database operations, including creating tables,
 * performing queries, and managing data.
 *
 * @package    QDR_Temporary_Shared_Storage
 * @since      1.0.0
 */
class QDR_TSS_DB_Manager {

    /**
     * The single instance of the class.
     *
     * @var QDR_TSS_DB_Manager
     * @since 1.0.0
     */
    protected static $_instance = null;

    /**
     * The database table name.
     *
     * @var string
     * @since 1.0.0
     */
    protected $table_name;

    /**
     * Main QDR_TSS_DB_Manager Instance.
     *
     * Ensures only one instance of QDR_TSS_DB_Manager is loaded or can be loaded.
     *
     * @since 1.0.0
     * @static
     * @return QDR_TSS_DB_Manager - Main instance.
     */
    public static function instance() {
        if (is_null(self::$_instance)) {
            self::$_instance = new self();
        }
        return self::$_instance;
    }

    /**
     * QDR_TSS_DB_Manager Constructor.
     */
    public function __construct() {
        global $wpdb;
        $this->table_name = $wpdb->prefix . QDR_TSS_PLUGIN_TABLE;
    }

    /**
     * Create the database table.
     *
     * @since 1.0.0
     * @return bool True on success, false on failure.
     */
    public function create_table() {
        global $wpdb;
        
        $charset_collate = $wpdb->get_charset_collate();
        
        $sql = "CREATE TABLE $this->table_name (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            fileid varchar(50) NOT NULL,
            original_file_name varchar(255) NOT NULL,
            description text NULL,
            message text NULL,
            active_from datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
            active_to datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
            password varchar(255) NOT NULL,
            reference varchar(255) NULL,
            upload_date datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
            download_count int DEFAULT 0 NOT NULL,
            last_download datetime DEFAULT '0000-00-00 00:00:00' NULL,
            file_path varchar(255) NOT NULL,
            file_size bigint(20) NOT NULL,
            PRIMARY KEY  (id),
            KEY fileid (fileid),
            KEY reference (reference),
            KEY active_to (active_to)
        ) $charset_collate;";
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        return dbDelta($sql);
    }

    /**
     * Get an item by ID.
     *
     * @since 1.0.0
     * @param int $id The item ID.
     * @return object|null Database row as object or null if not found.
     */
    public function get_item($id) {
        global $wpdb;
        
        return $wpdb->get_row($wpdb->prepare("SELECT * FROM $this->table_name WHERE id = %d", $id));
    }

    /**
     * Get an item by fileid.
     *
     * @since 1.0.0
     * @param string $fileid The file ID.
     * @return object|null Database row as object or null if not found.
     */
    public function get_item_by_fileid($fileid) {
        global $wpdb;
        
        return $wpdb->get_row($wpdb->prepare("SELECT * FROM $this->table_name WHERE fileid = %s", $fileid));
    }

    /**
     * Get items with pagination and filtering.
     *
     * @since 1.0.0
     * @param array $args {
     *     Optional. Arguments to retrieve items.
     *
     *     @type int    $per_page        Number of items per page. Default 10.
     *     @type int    $page            Page number. Default 1.
     *     @type string $orderby         Column to order by. Default 'upload_date'.
     *     @type string $order           Order direction. Default 'DESC'.
     *     @type string $original_file_name Filter by file name.
     *     @type string $reference       Filter by reference.
     * }
     * @return array {
     *     @type array  $items       The items.
     *     @type int    $total       Total number of items.
     *     @type int    $total_pages Total number of pages.
     * }
     */
    public function get_items($args = array()) {
        global $wpdb;
        
        // Default arguments
        $defaults = array(
            'per_page' => 10,
            'page' => 1,
            'orderby' => 'upload_date',
            'order' => 'DESC',
            'original_file_name' => '',
            'reference' => ''
        );
        
        $args = wp_parse_args($args, $defaults);
        
        // Ensure valid values
        $args['per_page'] = absint($args['per_page']);
        $args['page'] = absint($args['page']);
        $args['order'] = strtoupper($args['order']) === 'ASC' ? 'ASC' : 'DESC';
        
        // Allowed columns for orderby
        $allowed_orderby_columns = array(
            'fileid', 'original_file_name', 'description', 'active_from', 'active_to',
            'reference', 'upload_date', 'download_count', 'last_download'
        );
        
        if (!in_array($args['orderby'], $allowed_orderby_columns)) {
            $args['orderby'] = 'upload_date';
        }
        
        // Build query
        $query = "SELECT * FROM $this->table_name";
        $count_query = "SELECT COUNT(*) FROM $this->table_name";
        
        $where_conditions = array();
        $query_values = array();
        
        // Filter by original_file_name
        if (!empty($args['original_file_name'])) {
            $where_conditions[] = "original_file_name LIKE %s";
            $query_values[] = '%' . $wpdb->esc_like($args['original_file_name']) . '%';
        }
        
        // Filter by reference
        if (!empty($args['reference'])) {
            $where_conditions[] = "reference LIKE %s";
            $query_values[] = '%' . $wpdb->esc_like($args['reference']) . '%';
        }
        
        // Add WHERE conditions if any
        if (!empty($where_conditions)) {
            $where_clause = " WHERE " . implode(" AND ", $where_conditions);
            $query .= $where_clause;
            $count_query .= $where_clause;
        }
        
        // Add ordering
        $query .= " ORDER BY {$args['orderby']} {$args['order']}";
        
        // Add pagination
        $offset = ($args['page'] - 1) * $args['per_page'];
        $query .= " LIMIT %d OFFSET %d";
        $query_values[] = $args['per_page'];
        $query_values[] = $offset;
        
        // Prepare and execute queries
        $items = $wpdb->get_results($wpdb->prepare($query, $query_values));
        
        $total_query_values = array_slice($query_values, 0, -2);
        $total = $wpdb->get_var($wpdb->prepare($count_query, $total_query_values));
        
        return array(
            'items' => $items,
            'total' => (int) $total,
            'total_pages' => ceil($total / $args['per_page'])
        );
    }

    /**
     * Insert a new item.
     *
     * @since 1.0.0
     * @param array $data The item data.
     * @return int|false The item ID on success, false on failure.
     */
    public function insert_item($data) {
        global $wpdb;
        
        $result = $wpdb->insert(
            $this->table_name,
            $data,
            $this->get_column_formats($data)
        );
        
        return $result ? $wpdb->insert_id : false;
    }

    /**
     * Update an existing item.
     *
     * @since 1.0.0
     * @param int   $id   The item ID.
     * @param array $data The item data.
     * @return bool True on success, false on failure.
     */
    public function update_item($id, $data) {
        global $wpdb;
        
        return $wpdb->update(
            $this->table_name,
            $data,
            array('id' => $id),
            $this->get_column_formats($data),
            array('%d')
        );
    }

    /**
     * Update an item by fileid.
     *
     * @since 1.0.0
     * @param string $fileid The file ID.
     * @param array  $data   The item data.
     * @return bool True on success, false on failure.
     */
    public function update_item_by_fileid($fileid, $data) {
        global $wpdb;
        
        return $wpdb->update(
            $this->table_name,
            $data,
            array('fileid' => $fileid),
            $this->get_column_formats($data),
            array('%s')
        );
    }

    /**
     * Delete an item.
     *
     * @since 1.0.0
     * @param int $id The item ID.
     * @return bool True on success, false on failure.
     */
    public function delete_item($id) {
        global $wpdb;
        
        return $wpdb->delete(
            $this->table_name,
            array('id' => $id),
            array('%d')
        );
    }

    /**
     * Delete an item by fileid.
     *
     * @since 1.0.0
     * @param string $fileid The file ID.
     * @return bool True on success, false on failure.
     */
    public function delete_item_by_fileid($fileid) {
        global $wpdb;
        
        return $wpdb->delete(
            $this->table_name,
            array('fileid' => $fileid),
            array('%s')
        );
    }

    /**
     * Get expired items.
     *
     * @since 1.0.0
     * @return array Array of expired items.
     */
    public function get_expired_items() {
        global $wpdb;
        
        $current_time = current_time('mysql');
        
        return $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM $this->table_name WHERE active_to < %s",
            $current_time
        ));
    }

    /**
     * Delete expired items.
     *
     * @since 1.0.0
     * @return int Number of deleted items.
     */
    public function delete_expired_items() {
        global $wpdb;
        
        $current_time = current_time('mysql');
        
        return $wpdb->query($wpdb->prepare(
            "DELETE FROM $this->table_name WHERE active_to < %s",
            $current_time
        ));
    }

    /**
     * Increment download count and update last download time.
     *
     * @since 1.0.0
     * @param int $id The item ID.
     * @return bool True on success, false on failure.
     */
    public function increment_download_count($id) {
        global $wpdb;
        
        return $wpdb->query($wpdb->prepare(
            "UPDATE $this->table_name SET download_count = download_count + 1, last_download = %s WHERE id = %d",
            current_time('mysql'),
            $id
        ));
    }

    /**
     * Calculate total storage usage.
     *
     * @since 1.0.0
     * @return int Total storage usage in bytes.
     */
    public function get_total_storage() {
        global $wpdb;
        
        $total = $wpdb->get_var("SELECT SUM(file_size) FROM $this->table_name");
        
        return $total ? (int) $total : 0;
    }

    /**
     * Check if adding a new file would exceed storage limit.
     *
     * @since 1.0.0
     * @param int $file_size The file size in bytes.
     * @param int $max_size  The maximum allowed storage size in bytes.
     * @return bool True if adding the file would exceed limit, false otherwise.
     */
    public function would_exceed_storage_limit($file_size, $max_size) {
        $current_usage = $this->get_total_storage();
        
        return ($current_usage + $file_size) > $max_size;
    }

    /**
     * Get column formats for data.
     *
     * @since 1.0.0
     * @access private
     * @param array $data The data to get formats for.
     * @return array The column formats.
     */
    private function get_column_formats($data) {
        $formats = array();
        
        $columns = array(
            'id' => '%d',
            'fileid' => '%s',
            'original_file_name' => '%s',
            'description' => '%s',
            'message' => '%s',
            'active_from' => '%s',
            'active_to' => '%s',
            'password' => '%s',
            'reference' => '%s',
            'upload_date' => '%s',
            'download_count' => '%d',
            'last_download' => '%s',
            'file_path' => '%s',
            'file_size' => '%d'
        );
        
        foreach ($data as $column => $value) {
            if (isset($columns[$column])) {
                $formats[] = $columns[$column];
            } else {
                $formats[] = '%s';
            }
        }
        
        return $formats;
    }
}