<?php

namespace GriffinForms\Admin\Sql;

class Submissions extends \GriffinForms\Admin\Sql\Lists
{     
    protected function setSearchColumns()
    {
        $this->search_columns = array(
            'submission'
        );
    }
    
    protected function formIdQuery($form_id)
    {
        if ($form_id === "-1") {
            return true;
        }
        
        return 'form_id = ' . $form_id;
    }
    
    protected function editedQuery($value)
    {
        return 'submission_edit_count > 0';
    }

    protected function isreadQuery($value, &$params = [])
    {
        $params[] = (int) (bool) $value;
        return 'is_read = %d';
    }

    protected function statusQuery($status)
    {
        if ($status === "complete") {
            return 'status=1';
        } else {
            return 'status=0';
        }
        
    }
    /**
     * Get number of attached files for a submission.
     * Counts only rows promoted to attached status.
     *
     * @param int $submission_id
     * @return int Attachment count (0 if none)
     */
    public function getAttachmentCountBySubmissionId($submission_id)
    {
        $submission_id = (int) $submission_id;
        if ($submission_id <= 0) {
            return 0;
        }
        global $wpdb;
        $table = $this->config->getTable('files');
        $sql = $wpdb->prepare(
            "SELECT COUNT(*) FROM %i WHERE submission_id = %d AND status = 'attached'",
            $table, $submission_id
        );
        $count = (int) $wpdb->get_var($sql);
        return $count > 0 ? $count : 0;
    }

    /**
     * Get total size (in bytes) of attached files for a submission.
     * Sums the `size` column for rows in attached status.
     *
     * @param int $submission_id
     * @return int Total bytes (0 if none)
     */
    public function getTotalAttachmentSizeBySubmissionId($submission_id)
    {
        $submission_id = (int) $submission_id;
        if ($submission_id <= 0) {
            return 0;
        }
        global $wpdb;
        $table = $this->config->getTable('files');
        $sql = $wpdb->prepare(
            "SELECT SUM(size_bytes) FROM %i WHERE submission_id = %d AND status = 'attached'",
            $table, $submission_id
        );
        $sum = $wpdb->get_var($sql);
        return $sum !== null ? (int) $sum : 0;
    }

    /**
     * Delete all attached files for a submission from filesystem and database.
     *
     * For the given submission ID, attempts to delete each attached file from disk,
     * then removes all corresponding 'attached' file rows from the database.
     *
     * @param int $submission_id Submission ID whose attachments to delete.
     * @return int Number of deleted rows (files removed from DB), or false on invalid input.
     */
    public function deleteAttachmentsBySubmissionId($submission_id)
    {
        $submission_id = (int) $submission_id;
        if ($submission_id <= 0) {
            return false;
        }
        global $wpdb;
        $table = $this->config->getTable('files');
        // Fetch all attached files for this submission
        $files = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT id, path FROM %i WHERE submission_id = %d AND status = 'attached'",
                $table, $submission_id
            ),
            ARRAY_A
        );
        if ($files && is_array($files)) {
            // Initialize WP_Filesystem
            if (!function_exists('WP_Filesystem')) {
                require_once ABSPATH . 'wp-admin/includes/file.php';
            }
            WP_Filesystem();
            global $wp_filesystem;

            foreach ($files as $file) {
                $path = isset($file['path']) ? (string) $file['path'] : '';
                if ($path === '') {
                    continue;
                }

                // Prefer WordPress filesystem API when available
                if (is_object($wp_filesystem) && method_exists($wp_filesystem, 'exists') && method_exists($wp_filesystem, 'delete')) {
                    if ($wp_filesystem->exists($path)) {
                        $wp_filesystem->delete($path, false, 'f'); // delete file (not recursive)
                    }
                } else {
                    // Fallback to PHP unlink if FS API unavailable
                    if (file_exists($path)) {
                        @unlink($path);
                    }
                }
            }
        }
        // Delete attached file rows from the database
        $deleted = $wpdb->delete(
            $table,
            array(
                'submission_id' => $submission_id,
                'status' => 'attached'
            )
        );
        return is_numeric($deleted) ? (int) $deleted : 0;
    }
}
