<?php

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.SchemaChange -- This class intentionally manages plugin-owned custom tables and schema updates.

class Edocman_Lite_Database {

    /**
     * Run database upgrades for existing installations.
     *
     * Called on every plugin load (via plugins_loaded) so that sites upgraded
     * from an older release automatically receive new schema columns without
     * needing to deactivate/reactivate the plugin.
     */
    public function maybe_upgrade() {
        $installed_version = get_option( 'edocman_lite_db_version', '0' );

        if ( version_compare( $installed_version, EDOCMAN_LITE_DB_VERSION, '>=' ) ) {
            return;
        }

        global $wpdb;

        // 1.1 – add image_id column to categories and documents tables.
        if ( version_compare( $installed_version, '1.1', '<' ) ) {
            $table_categories = esc_sql( $wpdb->prefix . 'edocman_categories' );
            if ( $wpdb->get_var( "SHOW COLUMNS FROM `{$table_categories}` LIKE 'image_id'" ) === null ) {
                $result = $wpdb->query( "ALTER TABLE `{$table_categories}` ADD COLUMN `image_id` int(11) NOT NULL DEFAULT '0' AFTER `image`" );
                if ( $result === false ) {
                    $this->trigger_upgrade_warning( 'Edocman Lite: Failed to add image_id column to ' . $table_categories . ': ' . $wpdb->last_error );
                }
            }

            $table_documents = esc_sql( $wpdb->prefix . 'edocman_documents' );
            if ( $wpdb->get_var( "SHOW COLUMNS FROM `{$table_documents}` LIKE 'image_id'" ) === null ) {
                $result = $wpdb->query( "ALTER TABLE `{$table_documents}` ADD COLUMN `image_id` int(11) NOT NULL DEFAULT '0' AFTER `image`" );
                if ( $result === false ) {
                    $this->trigger_upgrade_warning( 'Edocman Lite: Failed to add image_id column to ' . $table_documents . ': ' . $wpdb->last_error );
                }
            }
        }

        // 1.2 – add is_main_category column to document_category table.
        if ( version_compare( $installed_version, '1.2', '<' ) ) {
            // Table names are identifiers, not values, so esc_sql() is the correct
            // sanitization here rather than $wpdb->prepare() (which handles values only).
            $table_doc_cat = esc_sql( $wpdb->prefix . 'edocman_document_category' );
            if ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$table_doc_cat}` LIKE %s", 'is_main_category' ) ) === null ) {
                $result = $wpdb->query( "ALTER TABLE `{$table_doc_cat}` ADD COLUMN `is_main_category` tinyint(1) NOT NULL DEFAULT '0'" );
                if ( $result === false ) {
                    $this->trigger_upgrade_warning( 'Edocman Lite: Failed to add is_main_category column to ' . $table_doc_cat . ': ' . $wpdb->last_error );
                }
            }
        }

        update_option( 'edocman_lite_db_version', EDOCMAN_LITE_DB_VERSION );
    }

    /**
     * Trigger a schema-upgrade warning without requiring newer WordPress versions.
     *
     * @param string $message Warning message.
     * @return void
     */
    private function trigger_upgrade_warning( $message ) {
        // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_trigger_error -- Schema upgrade failures should surface in older supported WordPress versions too.
        trigger_error( esc_html( $message ), E_USER_WARNING );
    }

    public function create_tables() {
        global $wpdb;
        
        $charset_collate = $wpdb->get_charset_collate();
        
        // Note: Edocman Lite now uses the main Edocman tables instead of separate lite tables
        // This ensures data compatibility and avoids migration issues
        
        // Categories table (using main edocman table)
        $table_categories = esc_sql( $wpdb->prefix . 'edocman_categories' );
        $sql_categories = "CREATE TABLE IF NOT EXISTS $table_categories (
            id int(11) NOT NULL AUTO_INCREMENT,
            parent_id int(10) unsigned DEFAULT '0',
            title varchar(255) DEFAULT NULL,
            alias varchar(255) DEFAULT NULL,
            image varchar(255) DEFAULT NULL,
            image_id int(11) NOT NULL DEFAULT '0',
            description text,
            access tinyint(3) unsigned DEFAULT '0',
            category_layout varchar(50) DEFAULT NULL,
            metakey varchar(255) DEFAULT NULL,
            metadesc tinytext,
            asset_id bigint(20) unsigned DEFAULT '0',
            created_user_id int(10) unsigned DEFAULT '0',
            created_time datetime DEFAULT NULL,
            modified_user_id int(10) unsigned DEFAULT '0',
            modified_time datetime DEFAULT NULL,
            path varchar(255) NOT NULL DEFAULT '',
            checked_out int(10) unsigned DEFAULT '0',
            checked_out_time datetime DEFAULT NULL,
            ordering int(10) unsigned DEFAULT '0',
            published tinyint(3) unsigned DEFAULT '0',
            user_ids varchar(255) DEFAULT '',
            language varchar(255) DEFAULT '*',
            PRIMARY KEY (id)
        ) $charset_collate;";

        // Documents table (using main edocman table)
        $table_documents = esc_sql( $wpdb->prefix . 'edocman_documents' );
        $sql_documents = "CREATE TABLE IF NOT EXISTS $table_documents (
            id int(11) NOT NULL AUTO_INCREMENT,
            title varchar(255) DEFAULT NULL,
            alias varchar(255) DEFAULT NULL,
            filename varchar(255) DEFAULT NULL,
            original_filename varchar(255) DEFAULT NULL,
            document_version varchar(10) NOT NULL DEFAULT '1.0',
            image varchar(255) DEFAULT NULL,
            image_id int(11) NOT NULL DEFAULT '0',
            document_url varchar(255) DEFAULT NULL,
            access int(11) DEFAULT '0',
            license_id int(10) UNSIGNED DEFAULT '0',
            short_description text,
            description text,
            metakey text,
            metadesc text,
            rating_count bigint(20) DEFAULT '0',
            rating_sum decimal(10,2) DEFAULT '0.00',
            hits int(10) UNSIGNED DEFAULT '0',
            downloads int(11) DEFAULT '0',
            asset_id int(10) UNSIGNED DEFAULT '0',
            created_user_id int(10) UNSIGNED DEFAULT '0',
            created_time datetime DEFAULT NULL,
            modified_user_id int(10) UNSIGNED DEFAULT '0',
            modified_time datetime DEFAULT NULL,
            checked_out int(10) UNSIGNED DEFAULT '0',
            checked_out_time datetime DEFAULT NULL,
            ordering int(10) UNSIGNED DEFAULT '0',
            published tinyint(3) UNSIGNED DEFAULT '0',
            user_ids varchar(255) DEFAULT '',
            language varchar(255) DEFAULT '*',
            indexed_content text,
            tags text,
            indicators varchar(50) DEFAULT NULL,
            publish_up datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
            publish_down datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
            params text,
            view_url varchar(255) DEFAULT '',
            file_size varchar(100) NOT NULL DEFAULT '',
            document_history text NOT NULL,
            is_locked tinyint(1) NOT NULL DEFAULT '0',
            locked_by int(11) NOT NULL DEFAULT '0',
            locked_time datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
            PRIMARY KEY (id)
        ) $charset_collate;";

        // Document-Category relationship table (using main edocman table)
        $table_doc_cat = esc_sql( $wpdb->prefix . 'edocman_document_category' );
        $sql_doc_cat = "CREATE TABLE IF NOT EXISTS $table_doc_cat (
            id int(11) NOT NULL AUTO_INCREMENT,
            document_id int(11) DEFAULT '0',
            category_id int(11) DEFAULT '0',
            is_main_category tinyint(1) NOT NULL DEFAULT '0',
            PRIMARY KEY (id)
        ) $charset_collate;";

        // Config table
        $table_configs = esc_sql( $wpdb->prefix . 'edocman_configs' );
        $sql_configs = "CREATE TABLE IF NOT EXISTS $table_configs (
            id int(11) NOT NULL AUTO_INCREMENT,
            config_key varchar(255) DEFAULT NULL,
            config_value text,
            PRIMARY KEY (id)
        ) $charset_collate;";

        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql_categories);
        dbDelta($sql_documents);
        dbDelta($sql_doc_cat);
        dbDelta($sql_configs);
        
        // Insert default configurations
        $this->insert_default_configs();

        // Record the current DB version so maybe_upgrade() can track future upgrades.
        update_option( 'edocman_lite_db_version', EDOCMAN_LITE_DB_VERSION );
    }

    private function insert_default_configs() {
        global $wpdb;
        $table_configs = esc_sql( $wpdb->prefix . 'edocman_configs' );
        
        // Check if configs already exist
        $count = $wpdb->get_var( $wpdb->prepare(
            "SELECT COUNT(*) FROM {$table_configs} WHERE config_key LIKE %s",
            'edocman_lite_%'
        ) );
        
        if ( $count == 0 ) {
            $upload_dir      = wp_upload_dir();
            $default_configs = array(
                array( 'config_key' => 'edocman_lite_documents_path',     'config_value' => $upload_dir['basedir'] . '/edocman-lite' ),
                array( 'config_key' => 'edocman_lite_allow_download',     'config_value' => '1' ),
                array( 'config_key' => 'edocman_lite_allow_view',         'config_value' => '1' ),
                array( 'config_key' => 'edocman_lite_documents_per_page', 'config_value' => '10' ),
                array( 'config_key' => 'edocman_lite_allowed_file_types', 'config_value' => 'pdf,doc,docx,xls,xlsx,ppt,pptx,txt,zip,rar' ),
                array( 'config_key' => 'edocman_lite_max_file_size',      'config_value' => '10' ),
                array( 'config_key' => 'edocman_lite_hierarchical_folders','config_value' => '0' ),
            );

            foreach ( $default_configs as $config ) {
                $wpdb->insert( $table_configs, $config );
            }
        }

    }

    public function get_categories($parent_id = 0, $published_only = false) {
        global $wpdb;
        $table = esc_sql( $wpdb->prefix . 'edocman_categories' );
        
        if ( $published_only ) {
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            return $wpdb->get_results( $wpdb->prepare(
                "SELECT * FROM {$table} WHERE parent_id = %d AND published = 1 ORDER BY ordering ASC, title ASC",
                $parent_id
            ) );
        }

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        return $wpdb->get_results( $wpdb->prepare(
            "SELECT * FROM {$table} WHERE parent_id = %d ORDER BY ordering ASC, title ASC",
            $parent_id
        ) );
    }

    public function get_all_categories($published_only = false) {
        global $wpdb;
        $table = esc_sql( $wpdb->prefix . 'edocman_categories' );

        if ($published_only) {
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            return $wpdb->get_results( $wpdb->prepare(
                "SELECT * FROM {$table} WHERE published = %d ORDER BY ordering ASC, title ASC",
                1
            ) );
        }

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.NoCaching
        return $wpdb->get_results( "SELECT * FROM {$table} ORDER BY ordering ASC, title ASC" );
    }

    public function get_category($id) {
        global $wpdb;
        $table = esc_sql( $wpdb->prefix . 'edocman_categories' );

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        return $wpdb->get_row($wpdb->prepare("SELECT * FROM {$table} WHERE id = %d", $id));
    }

    public function save_category($data) {
        global $wpdb;
        $table = esc_sql( $wpdb->prefix . 'edocman_categories' );
        
        // Set default values for full edocman schema fields if not present
        $defaults = array(
            'image_id' => 0,
            'access' => 0,
            'category_layout' => null,
            'metakey' => '',
            'metadesc' => '',
            'asset_id' => 0,
            'path' => '',
            'checked_out' => 0,
            'checked_out_time' => null,
            'user_ids' => '',
            'language' => '*',
            'ordering' => 0,
            'published' => 0
        );
        
        // Merge defaults with provided data
        $data = array_merge($defaults, $data);
        
        if (isset($data['id']) && $data['id'] > 0) {
            $data['modified_time'] = current_time('mysql');
            $data['modified_user_id'] = get_current_user_id();
            $wpdb->update($table, $data, array('id' => $data['id']));
            return $data['id'];
        } else {
            $data['created_time'] = current_time('mysql');
            $data['created_user_id'] = get_current_user_id();
            $wpdb->insert($table, $data);
            return $wpdb->insert_id;
        }
    }

    public function delete_category($id) {
        global $wpdb;
        $table = esc_sql( $wpdb->prefix . 'edocman_categories' );
        
        return $wpdb->delete($table, array('id' => $id));
    }

    public function get_documents($category_id = 0, $published_only = false, $limit = 0, $offset = 0) {
        global $wpdb;
        $table_docs    = esc_sql( $wpdb->prefix . 'edocman_documents' );
        $table_doc_cat = esc_sql( $wpdb->prefix . 'edocman_document_category' );
        
        if ($category_id > 0) {
            if ( $limit > 0 ) {
                if ( $published_only ) {
                    return $wpdb->get_results( $wpdb->prepare(
                        "SELECT d.* FROM {$table_docs} d INNER JOIN {$table_doc_cat} dc ON d.id = dc.document_id WHERE dc.category_id = %d AND d.published = 1 ORDER BY d.ordering ASC, d.title ASC LIMIT %d OFFSET %d",
                        $category_id,
                        $limit,
                        $offset
                    ) );
                }

                return $wpdb->get_results( $wpdb->prepare(
                    "SELECT d.* FROM {$table_docs} d INNER JOIN {$table_doc_cat} dc ON d.id = dc.document_id WHERE dc.category_id = %d ORDER BY d.ordering ASC, d.title ASC LIMIT %d OFFSET %d",
                    $category_id,
                    $limit,
                    $offset
                ) );
            }

            if ( $published_only ) {
                return $wpdb->get_results( $wpdb->prepare(
                    "SELECT d.* FROM {$table_docs} d INNER JOIN {$table_doc_cat} dc ON d.id = dc.document_id WHERE dc.category_id = %d AND d.published = 1 ORDER BY d.ordering ASC, d.title ASC",
                    $category_id
                ) );
            }

            return $wpdb->get_results( $wpdb->prepare(
                "SELECT d.* FROM {$table_docs} d INNER JOIN {$table_doc_cat} dc ON d.id = dc.document_id WHERE dc.category_id = %d ORDER BY d.ordering ASC, d.title ASC",
                $category_id
            ) );
        } else {
            if ( $limit > 0 ) {
                if ( $published_only ) {
                    return $wpdb->get_results( $wpdb->prepare(
                        "SELECT * FROM {$table_docs} WHERE published = 1 ORDER BY ordering ASC, title ASC LIMIT %d OFFSET %d",
                        $limit,
                        $offset
                    ) );
                }

                return $wpdb->get_results( $wpdb->prepare(
                    "SELECT * FROM {$table_docs} ORDER BY ordering ASC, title ASC LIMIT %d OFFSET %d",
                    $limit,
                    $offset
                ) );
            }

            if ( $published_only ) {
                return $wpdb->get_results( "SELECT * FROM {$table_docs} WHERE published = 1 ORDER BY ordering ASC, title ASC" );
            }

            return $wpdb->get_results( "SELECT * FROM {$table_docs} ORDER BY ordering ASC, title ASC" );
        }
    }

    public function get_document($id) {
        global $wpdb;
        $table = esc_sql( $wpdb->prefix . 'edocman_documents' );
        
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        return $wpdb->get_row($wpdb->prepare("SELECT * FROM {$table} WHERE id = %d", $id));
    }

    public function save_document($data) {
        global $wpdb;
        $table = esc_sql( $wpdb->prefix . 'edocman_documents' );
        
        // Set default values for full edocman schema fields if not present
        $defaults = array(
            'document_version' => '1.0',
            'image_id' => 0,
            'access' => 0,
            'license_id' => 0,
            'rating_count' => 0,
            'rating_sum' => 0.00,
            'hits' => 0,
            'downloads' => 0,
            'asset_id' => 0,
            'user_ids' => '',
            'language' => '*',
            'indexed_content' => '',
            'tags' => '',
            'indicators' => null,
            'publish_up' => '1000-01-01 00:00:00',
            'publish_down' => '1000-01-01 00:00:00',
            'params' => '',
            'view_url' => '',
            'document_history' => '',
            'is_locked' => 0,
            'locked_by' => 0,
            'locked_time' => '1000-01-01 00:00:00',
            'checked_out' => 0,
            'checked_out_time' => null,
            'metakey' => '',
            'metadesc' => ''
        );
        
        // Merge defaults with provided data
        $data = array_merge($defaults, $data);
        
        if (isset($data['id']) && $data['id'] > 0) {
            $data['modified_time'] = current_time('mysql');
            $data['modified_user_id'] = get_current_user_id();
            $wpdb->update($table, $data, array('id' => $data['id']));
            return $data['id'];
        } else {
            $data['created_time'] = current_time('mysql');
            $data['created_user_id'] = get_current_user_id();
            $wpdb->insert($table, $data);
            return $wpdb->insert_id;
        }
    }

    public function delete_document($id) {
        global $wpdb;
        $table = esc_sql( $wpdb->prefix . 'edocman_documents' );
        $table_doc_cat = esc_sql( $wpdb->prefix . 'edocman_document_category' );
        
        // Delete relationships first
        $wpdb->delete($table_doc_cat, array('document_id' => $id));
        
        // Delete document
        return $wpdb->delete($table, array('id' => $id));
    }

    public function link_document_to_category($document_id, $category_id, $is_main = false) {
        global $wpdb;
        $table = esc_sql( $wpdb->prefix . 'edocman_document_category' );
        
        $wpdb->insert($table, array(
            'document_id' => $document_id,
            'category_id' => $category_id,
            'is_main_category' => $is_main ? 1 : 0
        ));
    }

    public function get_document_categories($document_id) {
        global $wpdb;
        $table_doc_cat = esc_sql( $wpdb->prefix . 'edocman_document_category' );

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        return $wpdb->get_results($wpdb->prepare(
            "SELECT category_id, is_main_category FROM {$table_doc_cat} WHERE document_id = %d ORDER BY is_main_category DESC",
            $document_id
        ));
    }

    /**
     * Get the main category of a document.
     *
     * @param int $document_id
     * @return int Category ID or 0 if none.
     */
    public function get_main_category_id($document_id) {
        global $wpdb;
        $table_doc_cat = esc_sql( $wpdb->prefix . 'edocman_document_category' );

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $result = $wpdb->get_var( $wpdb->prepare(
            "SELECT category_id FROM {$table_doc_cat} WHERE document_id = %d AND is_main_category = 1 LIMIT 1",
            $document_id
        ) );

        return $result ? intval($result) : 0;
    }

    /**
     * Save document-category relationships.
     *
     * @param int   $document_id
     * @param int   $main_category_id       The main category (is_main_category = 1).
     * @param array $additional_category_ids Additional categories (is_main_category = 0).
     */
    public function update_document_categories($document_id, $main_category_id, $additional_category_ids = array()) {
        global $wpdb;
        $table = esc_sql( $wpdb->prefix . 'edocman_document_category' );

        // Delete existing relationships
        $wpdb->delete($table, array('document_id' => $document_id));

        $main_category_id = intval($main_category_id);

        // Insert main category first
        if ($main_category_id > 0) {
            $this->link_document_to_category($document_id, $main_category_id, true);
        }

        // Insert additional categories (skip if already the main category)
        foreach ($additional_category_ids as $category_id) {
            $category_id = intval($category_id);
            if ($category_id > 0 && $category_id !== $main_category_id) {
                $this->link_document_to_category($document_id, $category_id, false);
            }
        }
    }

    public function increment_hits($document_id) {
        global $wpdb;
        $table = esc_sql( $wpdb->prefix . 'edocman_documents' );

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $wpdb->query($wpdb->prepare(
            "UPDATE {$table} SET hits = hits + 1 WHERE id = %d",
            $document_id
        ));
    }

    public function increment_downloads($document_id) {
        global $wpdb;
        $table = esc_sql( $wpdb->prefix . 'edocman_documents' );

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $wpdb->query($wpdb->prepare(
            "UPDATE {$table} SET downloads = downloads + 1 WHERE id = %d",
            $document_id
        ));
    }

    public function get_documents_by_user( $user_id, $published_only = false ) {
        global $wpdb;
        $table = esc_sql( $wpdb->prefix . 'edocman_documents' );

        if ( $published_only ) {
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            return $wpdb->get_results( $wpdb->prepare(
                "SELECT * FROM {$table} WHERE created_user_id = %d AND published = 1 ORDER BY created_time DESC",
                intval( $user_id )
            ) );
        }

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        return $wpdb->get_results( $wpdb->prepare(
            "SELECT * FROM {$table} WHERE created_user_id = %d ORDER BY created_time DESC",
            intval( $user_id )
        ) );
    }

    public function get_config($key) {
        global $wpdb;
        $table = esc_sql( $wpdb->prefix . 'edocman_configs' );

        // Prefix lite config keys for compatibility
        $prefixed_key = 'edocman_lite_' . $key;

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $result = $wpdb->get_var( $wpdb->prepare(
            "SELECT config_value FROM {$table} WHERE config_key = %s",
            $prefixed_key
        ) );

        return $result;
    }

    public function set_config($key, $value) {
        global $wpdb;
        $table = esc_sql( $wpdb->prefix . 'edocman_configs' );
        
        // Prefix lite config keys for compatibility
        $prefixed_key = 'edocman_lite_' . $key;
        
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $existing = $wpdb->get_var($wpdb->prepare(
            "SELECT id FROM {$table} WHERE config_key = %s",
            $prefixed_key
        ));
        
        if ($existing) {
            $wpdb->update($table, 
                array('config_value' => $value),
                array('config_key' => $prefixed_key)
            );
        } else {
            $wpdb->insert($table, array(
                'config_key' => $prefixed_key,
                'config_value' => $value
            ));
        }
    }
}

// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.SchemaChange
