<?php
/**
 * This file manages database operations for the plugin.
 *
 * A class for database management.
 *
 * Responsible for executing operations on the plugin's database structure.
 *
 */
class AltTextLab_Database
{
    /**
     * Current version number of the database schema.
     *
     */
    private $db_version = '1.0.0';


    /**
     * Creates or modifies database tables according to the current schema.
     *
     */
    public function create_or_update_table()
    {
        global $wpdb;
        $table_name = $wpdb->prefix . ALT_TEXT_LAB_DB_ASSET_TABLE;
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE $table_name (
                  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                  wp_post_id bigint(20) unsigned NOT NULL,
                  alt_text_lab_id varchar(128) NOT NULL,
                  alt_text text NOT NULL,
                  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
                  PRIMARY KEY (id),
                  KEY idx_alttextlab_wp_post (wp_post_id)
                ) $charset_collate;";

        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
    }

    /**
     * Verifies if the database structure requires updating.
     *
     */
    public function check_database_schema()
    {
        $installed_db_version = get_option('alttextlab_db_version', '');

        if ($installed_db_version != $this->db_version) {
            $this->create_or_update_table();
            update_option('alttextlab_db_version', $this->db_version);
        }
    }

    /**
     * Generates SQL query to count all image attachments in WordPress media library
     *
     */
    public function query_all_images($wpdb)
    {
        $all_images_query = "
            SELECT COUNT(*) as total_images
            FROM {$wpdb->posts} post_table
            WHERE (post_table.post_mime_type LIKE 'image/%')
              AND post_table.post_status = 'inherit'
              AND post_table.post_type = 'attachment'
        ";

        return $all_images_query;
    }

    /**
     * Generates SQL query to count image attachments missing alternative text
     *
     */
    public function query_images_without_alt_text($wpdb)
    {
        $images_without_alt_text_sql = "
            SELECT COUNT(DISTINCT post_table.ID) as total_images
            FROM {$wpdb->posts} post_table
              LEFT JOIN {$wpdb->postmeta} postmeta_talbe
                ON (post_table.ID = postmeta_talbe.post_id AND postmeta_talbe.meta_key = '_wp_attachment_image_alt')
              LEFT JOIN {$wpdb->postmeta} AS empty_postmeta_table ON (post_table.ID = empty_postmeta_table.post_id)
            WHERE (post_table.post_mime_type LIKE 'image/%')
              AND (postmeta_talbe.post_id IS NULL OR (empty_postmeta_table.meta_key = '_wp_attachment_image_alt' AND empty_postmeta_table.meta_value = ''))
              AND post_table.post_status = 'inherit'
              AND post_table.post_type = 'attachment'
        ";

        return $images_without_alt_text_sql;
    }
}