<?php
/**
 * Database operations for BW Gallery
 */

if (!defined('ABSPATH')) {
    exit;
}

class BW_Gallery_DB {
    
    private $galleries_table;
    private $gallery_images_table;
    
    public function __construct() {
        global $wpdb;
        $this->galleries_table = $wpdb->prefix . 'bwg_galleries';
        $this->gallery_images_table = $wpdb->prefix . 'bwg_gallery_images';
    }
    
    public function create_tables() {
        global $wpdb;
        
        $charset_collate = $wpdb->get_charset_collate();
        
        // Galleries table
        $sql_galleries = "CREATE TABLE IF NOT EXISTS {$this->galleries_table} (
            id int(11) NOT NULL AUTO_INCREMENT,
            name varchar(255) NOT NULL,
            description text,
            settings longtext,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            PRIMARY KEY (id)
        ) $charset_collate;";
        
        // Gallery images table with tab support
        $sql_images = "CREATE TABLE IF NOT EXISTS {$this->gallery_images_table} (
            id int(11) NOT NULL AUTO_INCREMENT,
            gallery_id int(11) NOT NULL,
            image_id int(11) NOT NULL,
            tab_name varchar(255) DEFAULT '',
            sort_order int(11) DEFAULT 0,
            caption text,
            PRIMARY KEY (id),
            KEY gallery_id (gallery_id),
            KEY image_id (image_id)
        ) $charset_collate;";
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql_galleries);
        dbDelta($sql_images);
    }
    
    public function insert_gallery($data) {
        global $wpdb;
        
        $current_time = current_time('mysql');
        
        $wpdb->insert(
            $this->galleries_table,
            array(
                'name' => sanitize_text_field($data['name']),
                'description' => sanitize_textarea_field($data['description']),
                'settings' => json_encode($data['settings']),
                'created_at' => $current_time,
                'updated_at' => $current_time
            ),
            array('%s', '%s', '%s', '%s', '%s')
        );
        
        return $wpdb->insert_id;
    }
    
    public function update_gallery($id, $data) {
        global $wpdb;
        
        return $wpdb->update(
            $this->galleries_table,
            array(
                'name' => sanitize_text_field($data['name']),
                'description' => sanitize_textarea_field($data['description']),
                'settings' => json_encode($data['settings']),
                'updated_at' => current_time('mysql')
            ),
            array('id' => $id),
            array('%s', '%s', '%s', '%s'),
            array('%d')
        );
    }
    
    public function get_gallery($id) {
        global $wpdb;
        
        $gallery = $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$this->galleries_table} WHERE id = %d",
            $id
        ), ARRAY_A);
        
        if ($gallery) {
            $gallery['settings'] = json_decode($gallery['settings'], true);
        }
        
        return $gallery;
    }
    
    public function get_galleries($args = array()) {
        global $wpdb;
        
        $defaults = array(
            'orderby' => 'created_at',
            'order' => 'DESC',
            'limit' => -1,
            'offset' => 0
        );
        
        $args = wp_parse_args($args, $defaults);
        
        $sql = "SELECT * FROM {$this->galleries_table}";
        $sql .= " ORDER BY {$args['orderby']} {$args['order']}";
        
        if ($args['limit'] > 0) {
            $sql .= $wpdb->prepare(" LIMIT %d OFFSET %d", $args['limit'], $args['offset']);
        }
        
        $galleries = $wpdb->get_results($sql, ARRAY_A);
        
        foreach ($galleries as &$gallery) {
            $gallery['settings'] = json_decode($gallery['settings'], true);
        }
        
        return $galleries;
    }
    
    public function delete_gallery($id) {
        global $wpdb;
        
        // Delete gallery images first
        $this->delete_gallery_images($id);
        
        // Delete gallery
        return $wpdb->delete(
            $this->galleries_table,
            array('id' => $id),
            array('%d')
        );
    }
    
    public function add_gallery_image($gallery_id, $image_id, $tab_name = '', $caption = '', $sort_order = 0) {
        global $wpdb;
        
        return $wpdb->insert(
            $this->gallery_images_table,
            array(
                'gallery_id' => $gallery_id,
                'image_id' => $image_id,
                'tab_name' => sanitize_text_field($tab_name),
                'caption' => sanitize_text_field($caption),
                'sort_order' => $sort_order
            ),
            array('%d', '%d', '%s', '%s', '%d')
        );
    }
    
    public function update_gallery_images($gallery_id, $images) {
        global $wpdb;
        
        // Delete existing images
        $this->delete_gallery_images($gallery_id);
        
        // Insert new images
        foreach ($images as $index => $image) {
            $this->add_gallery_image(
                $gallery_id,
                $image['id'],
                $image['tab'] ?? '',
                $image['caption'] ?? '',
                $index
            );
        }
    }
    
    public function get_gallery_images($gallery_id) {
        global $wpdb;
        
        return $wpdb->get_results($wpdb->prepare(
            "SELECT gi.*, p.post_title, p.post_excerpt, p.post_content
             FROM {$this->gallery_images_table} gi
             LEFT JOIN {$wpdb->posts} p ON gi.image_id = p.ID
             WHERE gi.gallery_id = %d
             ORDER BY gi.sort_order ASC",
            $gallery_id
        ), ARRAY_A);
    }
    
    public function delete_gallery_images($gallery_id) {
        global $wpdb;
        
        return $wpdb->delete(
            $this->gallery_images_table,
            array('gallery_id' => $gallery_id),
            array('%d')
        );
    }
}