<?php
if (!defined('WPINC')) {
    die;
}

class Studiou_WC_FPP_DB {

    const TABLE_NAME = 'studiou_fpp_files';

    public function __construct() {
        $this->register_media_taxonomy();
    }

    private function register_media_taxonomy() {
        add_action('init', array($this, 'register_media_category_taxonomy'));
    }

    public function register_media_category_taxonomy() {
        $labels = array(
            'name'              => __('Media Categories', 'studiou-wc-free-photo-product'),
            'singular_name'     => __('Media Category', 'studiou-wc-free-photo-product'),
            'search_items'      => __('Search Media Categories', 'studiou-wc-free-photo-product'),
            'all_items'         => __('All Media Categories', 'studiou-wc-free-photo-product'),
            'parent_item'       => __('Parent Media Category', 'studiou-wc-free-photo-product'),
            'parent_item_colon' => __('Parent Media Category:', 'studiou-wc-free-photo-product'),
            'edit_item'         => __('Edit Media Category', 'studiou-wc-free-photo-product'),
            'update_item'       => __('Update Media Category', 'studiou-wc-free-photo-product'),
            'add_new_item'      => __('Add New Media Category', 'studiou-wc-free-photo-product'),
            'new_item_name'     => __('New Media Category Name', 'studiou-wc-free-photo-product'),
            'menu_name'         => __('Media Categories', 'studiou-wc-free-photo-product'),
        );

        register_taxonomy('studiou_media_category', 'attachment', array(
            'hierarchical'      => true,
            'labels'            => $labels,
            'show_ui'           => true,
            'show_admin_column' => true,
            'query_var'         => true,
            'rewrite'           => array('slug' => 'media-category'),
            'show_in_rest'      => true,
        ));
    }

    public function get_table_name() {
        global $wpdb;
        return $wpdb->prefix . self::TABLE_NAME;
    }

    public function create_tables() {
        global $wpdb;

        $table_name = $this->get_table_name();
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE {$table_name} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            product_id bigint(20) unsigned NOT NULL,
            variation_id bigint(20) unsigned DEFAULT 0,
            order_id bigint(20) unsigned DEFAULT 0,
            order_item_id bigint(20) unsigned DEFAULT 0,
            attachment_id bigint(20) unsigned NOT NULL,
            customer_id bigint(20) unsigned DEFAULT 0,
            session_key varchar(64) DEFAULT '',
            batch_token varchar(32) NOT NULL DEFAULT '',
            file_name varchar(255) NOT NULL,
            status varchar(20) NOT NULL DEFAULT 'ready',
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY product_id (product_id),
            KEY order_id (order_id),
            KEY attachment_id (attachment_id),
            KEY status (status),
            KEY customer_id (customer_id),
            KEY batch_token (batch_token)
        ) {$charset_collate};";

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

        update_option('studiou_wcfpp_db_version', STUDIOU_WCFPP_VERSION);
    }

    public function insert_file_record($data) {
        global $wpdb;

        $defaults = array(
            'product_id'    => 0,
            'variation_id'  => 0,
            'order_id'      => 0,
            'order_item_id' => 0,
            'attachment_id' => 0,
            'customer_id'   => get_current_user_id(),
            'session_key'   => '',
            'batch_token'   => '',
            'file_name'     => '',
            'status'        => 'ready',
            'created_at'    => current_time('mysql'),
            'updated_at'    => current_time('mysql'),
        );

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

        $result = $wpdb->insert(
            $this->get_table_name(),
            $data,
            array('%d', '%d', '%d', '%d', '%d', '%d', '%s', '%s', '%s', '%s', '%s', '%s')
        );

        if ($result === false) {
            return false;
        }

        return $wpdb->insert_id;
    }

    public function get_file_record($id) {
        global $wpdb;
        $table = $this->get_table_name();
        return $wpdb->get_row($wpdb->prepare("SELECT * FROM {$table} WHERE id = %d", $id));
    }

    public function get_file_records($args = array()) {
        global $wpdb;
        $table = $this->get_table_name();

        $defaults = array(
            'product_id' => 0,
            'order_id'   => 0,
            'status'     => '',
            'orderby'    => 'created_at',
            'order'      => 'DESC',
            'limit'      => 50,
            'offset'     => 0,
            'search'     => '',
        );
        $args = wp_parse_args($args, $defaults);

        $where = array('1=1');
        $values = array();

        if (!empty($args['product_id'])) {
            $where[] = 'product_id = %d';
            $values[] = $args['product_id'];
        }
        if (!empty($args['order_id'])) {
            $where[] = 'order_id = %d';
            $values[] = $args['order_id'];
        }
        if (!empty($args['status'])) {
            $where[] = 'status = %s';
            $values[] = $args['status'];
        }
        if (!empty($args['search'])) {
            $where[] = 'file_name LIKE %s';
            $values[] = '%' . $wpdb->esc_like($args['search']) . '%';
        }

        $where_clause = implode(' AND ', $where);

        $allowed_orderby = array('id', 'product_id', 'order_id', 'file_name', 'status', 'created_at', 'updated_at');
        $orderby = in_array($args['orderby'], $allowed_orderby) ? $args['orderby'] : 'created_at';
        $order = strtoupper($args['order']) === 'ASC' ? 'ASC' : 'DESC';

        $sql = "SELECT * FROM {$table} WHERE {$where_clause} ORDER BY {$orderby} {$order} LIMIT %d OFFSET %d";
        $values[] = (int) $args['limit'];
        $values[] = (int) $args['offset'];

        if (!empty($values)) {
            $sql = $wpdb->prepare($sql, $values);
        }

        return $wpdb->get_results($sql);
    }

    public function count_file_records($args = array()) {
        global $wpdb;
        $table = $this->get_table_name();

        $where = array('1=1');
        $values = array();

        if (!empty($args['product_id'])) {
            $where[] = 'product_id = %d';
            $values[] = $args['product_id'];
        }
        if (!empty($args['order_id'])) {
            $where[] = 'order_id = %d';
            $values[] = $args['order_id'];
        }
        if (!empty($args['status'])) {
            $where[] = 'status = %s';
            $values[] = $args['status'];
        }
        if (!empty($args['search'])) {
            $where[] = 'file_name LIKE %s';
            $values[] = '%' . $wpdb->esc_like($args['search']) . '%';
        }

        $where_clause = implode(' AND ', $where);
        $sql = "SELECT COUNT(*) FROM {$table} WHERE {$where_clause}";

        if (!empty($values)) {
            $sql = $wpdb->prepare($sql, $values);
        }

        return (int) $wpdb->get_var($sql);
    }

    public function update_file_record($id, $data) {
        global $wpdb;

        $data['updated_at'] = current_time('mysql');

        return $wpdb->update(
            $this->get_table_name(),
            $data,
            array('id' => $id),
            null,
            array('%d')
        );
    }

    public function update_file_status($id, $status) {
        $allowed = array('ready', 'downloaded', 'solved');
        if (!in_array($status, $allowed)) {
            return false;
        }
        return $this->update_file_record($id, array('status' => $status));
    }

    public function delete_file_record($id) {
        global $wpdb;

        $record = $this->get_file_record($id);
        if (!$record) {
            return false;
        }

        // Delete the attachment from media library
        if ($record->attachment_id) {
            wp_delete_attachment($record->attachment_id, true);
        }

        return $wpdb->delete(
            $this->get_table_name(),
            array('id' => $id),
            array('%d')
        );
    }

    public function get_file_records_by_ids($ids) {
        global $wpdb;
        $table = $this->get_table_name();

        if (empty($ids)) {
            return array();
        }

        $ids = array_map('intval', $ids);
        $placeholders = implode(',', array_fill(0, count($ids), '%d'));
        $sql = $wpdb->prepare("SELECT * FROM {$table} WHERE id IN ({$placeholders})", $ids);

        return $wpdb->get_results($sql);
    }

    /**
     * Return all file records belonging to a batch token that are not yet bound to an order.
     *
     * @param string $batch_token
     * @param int    $product_id  Optional — restrict to a single product_id.
     * @return array of row objects, oldest first (ascending id).
     */
    public function get_batch_files($batch_token, $product_id = 0) {
        global $wpdb;
        if (empty($batch_token)) {
            return array();
        }
        $table = $this->get_table_name();

        if ((int) $product_id > 0) {
            $sql = $wpdb->prepare(
                "SELECT * FROM {$table}
                 WHERE batch_token = %s
                   AND order_id = 0
                   AND product_id = %d
                 ORDER BY id ASC",
                $batch_token,
                (int) $product_id
            );
        } else {
            $sql = $wpdb->prepare(
                "SELECT * FROM {$table}
                 WHERE batch_token = %s
                   AND order_id = 0
                 ORDER BY id ASC",
                $batch_token
            );
        }
        return $wpdb->get_results($sql);
    }

    /**
     * Count batch files for a product (enforces per-product max-uploads cap).
     */
    public function count_batch_files($batch_token, $product_id) {
        global $wpdb;
        if (empty($batch_token) || (int) $product_id <= 0) {
            return 0;
        }
        $table = $this->get_table_name();
        return (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$table}
             WHERE batch_token = %s
               AND order_id = 0
               AND product_id = %d",
            $batch_token,
            (int) $product_id
        ));
    }

    public function link_file_to_order($file_record_id, $order_id, $order_item_id) {
        return $this->update_file_record($file_record_id, array(
            'order_id'      => $order_id,
            'order_item_id' => $order_item_id,
        ));
    }

    public function get_media_categories() {
        $terms = get_terms(array(
            'taxonomy'   => 'studiou_media_category',
            'hide_empty' => false,
        ));

        if (is_wp_error($terms)) {
            return array();
        }

        return $terms;
    }
}