<?php

namespace GriffinForms\Admin\Sql;

class Submission extends \GriffinForms\Admin\Sql\Format
{
    /**
     * Fetch file row from the files table by token.
     * Returns array: ['path' => ?, 'url' => ?, 'mime' => ?, 'size' => ?]
     */
    public function fetchFileRowByToken($token)
    {
        if (!is_string($token) || $token === '') { return []; }
        global $wpdb;
        $table = $this->config->getTable('files');
        $row = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT path, url, mime, size_bytes, image_w, image_h FROM %i WHERE token = %s LIMIT 1",
                $table,
                $token
            ),
            ARRAY_A
        );
        return is_array($row) ? $row : [];
    }

    /**
     * Get the total size in bytes of all files in the 'files' table with status 'attached'.
     *
     * @return int Total size in bytes of all 'attached' files. Returns 0 if none found.
     */
    public function getTotalAttachedBytes()
    {
        global $wpdb;
        $table = $this->config->getTable('files');
        $sql = $wpdb->prepare(
            "SELECT SUM(size_bytes) FROM %i WHERE status = %s",
            $table, 'attached'
        );
        $total = $wpdb->get_var($sql);
        return (int) ($total ?? 0);
    }

    /**
     * Get the total size in bytes of all files in the 'files' table with status 'temp'.
     *
     * @return int Total size in bytes of all 'temp' files. Returns 0 if none found.
     */
    public function getTotalTempBytes()
    {
        global $wpdb;
        $table = $this->config->getTable('files');
        $sql = $wpdb->prepare(
            "SELECT SUM(size_bytes) FROM %i WHERE status = %s",
            $table, 'temp'
        );
        $total = $wpdb->get_var($sql);
        return (int) ($total ?? 0);
    }

    /**
     * Get total storage used by files.
     *
     * By default, this returns the total bytes of files with status 'attached'.
     * If $include_temp is true, it will include both 'attached' and 'temp' files.
     *
     * @param bool $include_temp Whether to include 'temp' files in the total. Default false.
     * @return int Total size in bytes. Returns 0 if none found.
     */
    public function getTotalStorageBytes($include_temp = false)
    {
        global $wpdb;
        $table = $this->config->getTable('files');

        if ($include_temp) {
            // Sum attached + temp in one query
            $sql = $wpdb->prepare(
                "SELECT COALESCE(SUM(size_bytes), 0) FROM %i WHERE status IN (%s, %s)",
                $table,
                'attached',
                'temp'
            );
        } else {
            // Sum only attached
            $sql = $wpdb->prepare(
                "SELECT COALESCE(SUM(size_bytes), 0) FROM %i WHERE status = %s",
                $table,
                'attached'
            );
        }

        $total = $wpdb->get_var($sql);
        return (int) ($total ?? 0);
    }
}