<?php
/**
 * Database storage handler for AltTextLab plugin assets.
 *
 */

class AltTextLab_Storage {

    /**
     * Deletes AltTextLab associated data for a specific WordPress post
     *
     */
    public static function remove_alttextlab_asset($post_id)
    {
        global $wpdb;
        $table_name = $wpdb->prefix . ALT_TEXT_LAB_DB_ASSET_TABLE;

        $wpdb->query($wpdb->prepare("DELETE FROM {$table_name} WHERE wp_post_id = %d", $post_id));
    }

    /**
     * Stores the AI-generated alternative text for an image attachment.
     *
     */
    public static function record_alttextlab_asset($attachment_id, $response_api_id, $response_api_text) {
        global $wpdb;
        $table_name = $wpdb->prefix . ALT_TEXT_LAB_DB_ASSET_TABLE;

        $wpdb->query(
            $wpdb->prepare(
                "INSERT INTO {$table_name}(alt_text_lab_id, alt_text, wp_post_id) 
             VALUES (%s, %s, %d);",
                $response_api_id,
                $response_api_text,
                $attachment_id
            )
        );

    }

    /**
     * Returns the total number of records in the assets table
     *
     */
    public function get_count_total_assets()
    {
        global $wpdb;
        $alttextlab_asset_table = $wpdb->prefix . ALT_TEXT_LAB_DB_ASSET_TABLE;

        // Get the total number of assets
        $sql_total_assets = "SELECT COUNT(*) FROM {$alttextlab_asset_table}";

        $total_assets = $wpdb->get_var($wpdb->prepare($sql_total_assets));

        return $total_assets;
    }

    /**
     * Retrieves paginated assets from the database with specified limits
     *
     */
    public function get_assets_paginated($limit, $offset)
    {
        global $wpdb;
        $alttextlab_asset_table = $wpdb->prefix . ALT_TEXT_LAB_DB_ASSET_TABLE;

        // Get the assets for the current page
        $alttextlab_assets = $wpdb->get_results( $wpdb->prepare("
            SELECT asset_table.id, asset_table.wp_post_id, asset_table.created_at, asset_table.alt_text
            FROM {$alttextlab_asset_table} asset_table
            ORDER BY asset_table.created_at DESC
            LIMIT %d OFFSET %d
        ", $limit, $offset)
        );

        return $alttextlab_assets;
    }

}