<?php
/**
 * Gallery Model
 *
 * Handles database operations for image galleries.
 *
 * @package BoostBot
 * @since   1.0.0
 */

// Prevent direct access.
// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
if ( ! defined( 'ABSPATH' ) ) {
    exit;
}

/**
 * Class BoostBot_Gallery_Model
 *
 * Model for managing image galleries.
 */
class BoostBot_Gallery_Model extends BoostBot_Base_Model {

    /**
     * Table name for gallery images.
     *
     * @var string
     */
    private $images_table;

    /**
     * Constructor.
     */
    public function __construct() {
        parent::__construct();
        $this->table_name   = self::sanitize_identifier( $this->wpdb->prefix . 'boostbot_galleries' );
        $this->images_table = self::sanitize_identifier( $this->wpdb->prefix . 'boostbot_gallery_images' );
        $this->fillable     = array(
            'name',
            'description',
            'is_active',
        );
    }

    /**
     * Get gallery with images.
     *
     * @param int $gallery_id Gallery ID.
     * @return object|null Gallery with images or null.
     */
    public function get_with_images( int $gallery_id ): ?object {
        $gallery = $this->get( $gallery_id );

        if ( ! $gallery ) {
            return null;
        }

        $gallery->images = $this->get_images( $gallery_id );

        return $gallery;
    }

    /**
     * Get images for a gallery.
     *
     * @param int $gallery_id Gallery ID.
     * @return array Array of images.
     */
    public function get_images( int $gallery_id ): array {
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        return $this->wpdb->get_results(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $this->wpdb->prepare(
                "SELECT * FROM {$this->images_table} WHERE gallery_id = %d ORDER BY sort_order ASC", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $gallery_id
            )
        );
    }

    /**
     * Add image to gallery.
     *
     * @param int   $gallery_id Gallery ID.
     * @param array $image_data Image data.
     * @return int|false Image ID or false.
     */
    public function add_image( int $gallery_id, array $image_data ) {
        $data = array(
            'gallery_id'    => $gallery_id,
            'url'           => $image_data['url'],
            'attachment_id' => $image_data['attachment_id'] ?? null,
            'title'         => $image_data['title'] ?? null,
            'description'   => $image_data['description'] ?? null,
            'link_url'      => $image_data['link_url'] ?? null,
            'sort_order'    => $image_data['sort_order'] ?? 0,
        );

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $result = $this->wpdb->insert(
            $this->images_table,
            $data,
            array( '%d', '%s', '%d', '%s', '%s', '%s', '%d' )
        );

        if ( $result ) {
            return $this->wpdb->insert_id;
        }

        return false;
    }

    /**
     * Update image.
     *
     * @param int   $image_id   Image ID.
     * @param array $image_data Image data.
     * @return bool True on success.
     */
    public function update_image( int $image_id, array $image_data ): bool {
        $data = array();

        if ( isset( $image_data['url'] ) ) {
            $data['url'] = $image_data['url'];
        }
        if ( isset( $image_data['title'] ) ) {
            $data['title'] = $image_data['title'];
        }
        if ( isset( $image_data['description'] ) ) {
            $data['description'] = $image_data['description'];
        }
        if ( isset( $image_data['link_url'] ) ) {
            $data['link_url'] = $image_data['link_url'];
        }
        if ( isset( $image_data['sort_order'] ) ) {
            $data['sort_order'] = $image_data['sort_order'];
        }

        if ( empty( $data ) ) {
            return false;
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = $this->wpdb->update(
            $this->images_table,
            $data,
            array( 'id' => $image_id ),
            null,
            array( '%d' )
        );

        return $result !== false;
    }

    /**
     * Delete image.
     *
     * @param int $image_id Image ID.
     * @return bool True on success.
     */
    public function delete_image( int $image_id ): bool {
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = $this->wpdb->delete(
            $this->images_table,
            array( 'id' => $image_id ),
            array( '%d' )
        );

        return $result !== false;
    }

    /**
     * Reorder images.
     *
     * @param int   $gallery_id Gallery ID.
     * @param array $image_ids  Ordered array of image IDs.
     * @return bool True on success.
     */
    public function reorder_images( int $gallery_id, array $image_ids ): bool {
        foreach ( $image_ids as $order => $image_id ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $this->wpdb->update(
                $this->images_table,
                array( 'sort_order' => $order ),
                array(
                    'id'         => intval( $image_id ),
                    'gallery_id' => $gallery_id,
                ),
                array( '%d' ),
                array( '%d', '%d' )
            );
        }

        return true;
    }

    /**
     * Get all active galleries.
     *
     * @return array Array of galleries.
     */
    public function get_active(): array {
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $galleries = $this->wpdb->get_results(
            "SELECT * FROM {$this->table_name} WHERE is_active = 1 ORDER BY name ASC" // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        );

        foreach ( $galleries as $gallery ) {
            $gallery->images = $this->get_images( $gallery->id );
            $gallery->image_count = count( $gallery->images );
        }

        return $galleries;
    }

    /**
     * Get all galleries with image counts.
     *
     * @param array $args Query arguments.
     * @return array Array of galleries.
     */
    public function get_all_with_counts( array $args = array() ): array {
        $defaults = array(
            'limit'   => 50,
            'offset'  => 0,
            'orderby' => 'created_at',
            'order'   => 'DESC',
        );

        $args = wp_parse_args( $args, $defaults );

        // Validate orderby/order against allow-lists before interpolation to
        // prevent SQL injection via caller-supplied values.
        $allowed_orderby = array( 'id', 'name', 'description', 'is_active', 'created_at', 'updated_at', 'image_count' );
        $orderby         = in_array( $args['orderby'], $allowed_orderby, true ) ? $args['orderby'] : 'created_at';
        $order           = in_array( strtoupper( (string) $args['order'] ), array( 'ASC', 'DESC' ), true ) ? strtoupper( $args['order'] ) : 'DESC';

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $galleries = $this->wpdb->get_results(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $this->wpdb->prepare(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                "SELECT g.*,
                        (SELECT COUNT(*) FROM {$this->images_table} gi WHERE gi.gallery_id = g.id) as image_count
                 FROM {$this->table_name} g
                 ORDER BY {$orderby} {$order}
                 LIMIT %d OFFSET %d",
                $args['limit'],
                $args['offset']
            )
        );

        return $galleries;
    }

    /**
     * Delete gallery and all its images.
     *
     * @param int $gallery_id Gallery ID.
     * @return bool True on success.
     */
    public function delete_with_images( int $gallery_id ): bool {
        // Delete images first.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $this->wpdb->delete(
            $this->images_table,
            array( 'gallery_id' => $gallery_id ),
            array( '%d' )
        );

        // Delete gallery.
        return $this->delete( $gallery_id );
    }

    /**
     * Create gallery with images.
     *
     * @param array $data   Gallery data.
     * @param array $images Array of image data.
     * @return int|false Gallery ID or false.
     */
    public function create_with_images( array $data, array $images = array() ) {
        $gallery_id = $this->create( $data );

        if ( ! $gallery_id ) {
            return false;
        }

        foreach ( $images as $index => $image ) {
            $image['sort_order'] = $index;
            $this->add_image( $gallery_id, $image );
        }

        return $gallery_id;
    }

    /**
     * Get single image by ID.
     *
     * @param int $image_id Image ID.
     * @return object|null Image or null.
     */
    public function get_image( int $image_id ): ?object {
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        return $this->wpdb->get_row(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $this->wpdb->prepare(
                "SELECT * FROM {$this->images_table} WHERE id = %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $image_id
            )
        );
    }

    /**
     * Restrict a SQL identifier to [A-Za-z0-9_] characters.
     *
     * DDL cannot use $wpdb->prepare() for identifiers, so any identifier
     * that ends up interpolated into a query first passes through this
     * allow-list.
     *
     * @param string $identifier Raw identifier.
     * @return string Identifier with disallowed characters removed.
     */
    protected static function sanitize_identifier( $identifier ) {
        return preg_replace( '/[^A-Za-z0-9_]/', '', (string) $identifier );
    }
}







