<?php
/**
 * SCHEMAFY Database Class
 * 
 * Handles all database operations for the Custom Schema by SCHEMAFY plugin
 * 
 * @package CustomSchemaSchemafy
 * @since 1.1.85
 * @author SCHEMAFY Team
 */

// Prevent direct access
if (!defined('ABSPATH')) {
    exit;
}

/**
 * Class SCHEMAFY_Database
 * 
 * Manages all database operations including table creation and CRUD operations
 */
class SCHEMAFY_Database {
    
    /**
     * Get table name with WordPress prefix
     *
     * @return string Full table name
     */
    public function get_table_name() {
        global $wpdb;
        return $wpdb->prefix . 'schemafy_custom_schemas';
    }

    /**
     * Create database table
     * Called on plugin activation
     * 
     * @return void
     */
    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,
            url VARCHAR(500) NOT NULL,
            schema_type VARCHAR(100) NOT NULL,
            schema_name VARCHAR(200) NOT NULL,
            schema_data LONGTEXT NOT NULL,
            meta_title TEXT,
            meta_description TEXT,
            og_title TEXT,
            og_description TEXT,
            og_image VARCHAR(500),
            og_type VARCHAR(50) DEFAULT 'website',
            twitter_card VARCHAR(50) DEFAULT 'summary_large_image',
            twitter_title TEXT,
            twitter_description TEXT,
            twitter_image VARCHAR(500),
            twitter_creator VARCHAR(100),
            is_active TINYINT(1) DEFAULT 1,
            created_at DATETIME,
            created_by BIGINT(20) UNSIGNED,
            updated_at DATETIME,
            PRIMARY KEY  (id),
            KEY url (url(191)),
            KEY schema_type (schema_type),
            KEY is_active (is_active),
            KEY url_active (url(191), is_active)
        ) $charset_collate;";
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);

        schemafy_log('[SCHEMAFY Schema] Database table created or updated: ' . $table_name);
    }
    
    /**
     * Get schema by ID
     * 
     * @param int $id Schema ID
     * @return object|null Schema object or null if not found
     */
    public function get_schema($id) {
        global $wpdb;

        $table_name = $this->get_table_name();

        $schema = $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM $table_name WHERE id = %d",
            $id
        ));

        // Convert is_active to integer for proper JavaScript boolean interpretation
        if ($schema) {
            $schema->is_active = intval($schema->is_active);
        }

        return $schema;
    }
    
    /**
     * Get schemas with pagination and filters
     * 
     * @param array $args Query arguments
     * @return array Array containing 'items', 'total', 'pages'
     */
    public function get_schemas($args = array()) {
        global $wpdb;
        
        $table_name = $this->get_table_name();
        
        // Default arguments
        $defaults = array(
            'page' => 1,
            'per_page' => 20,
            'url' => null,
            'schema_type' => null,
            'search' => null,
            'is_active' => null
        );
        
        $args = wp_parse_args($args, $defaults);
        
        // Build WHERE clause
        $where_clauses = array();
        $where_params = array();
        
        if (!empty($args['url'])) {
            $where_clauses[] = 'url = %s';
            $where_params[] = $args['url'];
        }
        
        if (!empty($args['schema_type'])) {
            $where_clauses[] = 'schema_type = %s';
            $where_params[] = $args['schema_type'];
        }
        
        if (!empty($args['search'])) {
            $where_clauses[] = '(url LIKE %s OR schema_name LIKE %s OR schema_data LIKE %s)';
            $search_param = '%' . $wpdb->esc_like($args['search']) . '%';
            $where_params[] = $search_param;
            $where_params[] = $search_param;
            $where_params[] = $search_param;
        }
        
        if ($args['is_active'] !== null) {
            $where_clauses[] = 'is_active = %d';
            $where_params[] = $args['is_active'];
        }
        
        $where_sql = '';
        if (!empty($where_clauses)) {
            $where_sql = 'WHERE ' . implode(' AND ', $where_clauses);
        }
        
        // Count total items
        // $table_name is from get_table_name() (trusted), dynamic conditions are prepared above.
        $count_query = "SELECT COUNT(*) FROM $table_name $where_sql";
        if (!empty($where_params)) {
            $count_query = $wpdb->prepare($count_query, $where_params); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- query string built from trusted table name + prepared placeholders
        }
        $total_items = intval($wpdb->get_var($count_query)); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $count_query already prepared above
        
        // Calculate pagination
        $total_pages = ceil($total_items / $args['per_page']);
        
        if ($total_pages < 1) {
            $args['page'] = 1;
            $offset = 0;
            $total_pages = 1;
        } elseif ($args['page'] > $total_pages) {
            $args['page'] = $total_pages;
            $offset = ($args['page'] - 1) * $args['per_page'];
        } else {
            $offset = ($args['page'] - 1) * $args['per_page'];
        }
        
        // Get items as arrays for consistency
        $items_query = "SELECT * FROM $table_name $where_sql ORDER BY updated_at DESC LIMIT %d OFFSET %d";
        $all_params = array_merge($where_params, array($args['per_page'], $offset));

        $items_query = $wpdb->prepare($items_query, $all_params); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- query string built from trusted table name + prepared placeholders
        $items = $wpdb->get_results($items_query, ARRAY_A); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $items_query already prepared above

        // Convert is_active to integer for proper JavaScript boolean interpretation
        if ($items) {
            foreach ($items as &$item) {
                $item['is_active'] = intval($item['is_active']);
            }
        }

        return array(
            'items' => $items ? $items : array(),
            'total' => $total_items,
            'pages' => $total_pages,
            'current_page' => $args['page'],
            'per_page' => $args['per_page']
        );
    }
    
    /**
     * Get schemas by URL
     * 
     * @param string $url URL to search
     * @param bool $active_only Whether to get only active schemas
     * @return array Array of schema objects
     */
    public function get_schemas_by_url($url, $active_only = false) {
        global $wpdb;

        $table_name = $this->get_table_name();

        $where_active = $active_only ? 'AND is_active = 1' : '';

        // Match both URL variants (with/without trailing slash)
        $normalized = untrailingslashit(strtok($url, '?'));
        $schemas = $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM $table_name
             WHERE (url = %s OR url = %s) $where_active
             ORDER BY created_at ASC",
            $normalized,
            trailingslashit($normalized)
        ), ARRAY_A);

        // Convert is_active to integer for proper JavaScript boolean interpretation
        if ($schemas) {
            foreach ($schemas as &$schema) {
                $schema['is_active'] = intval($schema['is_active']);
            }
        }

        return $schemas ? $schemas : array();
    }

    /**
     * Get schemas by URL path (flexible matching)
     *
     * Uses LIKE query to match URLs ignoring protocol and www prefix
     *
     * @param string $url URL to search
     * @param bool $active_only Whether to get only active schemas
     * @return array Array of schema objects
     */
    public function get_schemas_by_url_flexible($url, $active_only = false) {
        global $wpdb;

        $table_name = $this->get_table_name();
        $where_active = $active_only ? 'AND is_active = 1' : '';

        // Extract path from URL (without protocol and www)
        $parsed = wp_parse_url($url);
        if (!$parsed || !isset($parsed['host'])) {
            return array();
        }

        // Remove www. prefix if present
        $host = $parsed['host'];
        if (strpos($host, 'www.') === 0) {
            $host = substr($host, 4);
        }

        $path = isset($parsed['path']) ? $parsed['path'] : '/';

        // Build pattern: %://[www.]domain.com/path%
        $pattern = '%://%' . $wpdb->esc_like($host) . $wpdb->esc_like($path) . '%';

        $schemas = $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM $table_name
             WHERE url LIKE %s $where_active
             ORDER BY created_at ASC",
            $pattern
        ), ARRAY_A);

        // Convert is_active to integer for proper JavaScript boolean interpretation
        if ($schemas) {
            foreach ($schemas as &$schema) {
                $schema['is_active'] = intval($schema['is_active']);
            }
        }

        return $schemas ? $schemas : array();
    }
    
    /**
     * Create new schema
     * 
     * @param array $data Schema data
     * @return int|false Schema ID on success, false on failure
     */
    public function create_schema($data) {
        global $wpdb;

        $table_name = $this->get_table_name();

        $insert_data = array(
            'url' => $data['url'],
            'schema_type' => $data['schema_type'],
            'schema_name' => $data['schema_name'],
            'schema_data' => $data['schema_data'],
            'meta_title' => isset($data['meta_title']) ? $data['meta_title'] : null,
            'meta_description' => isset($data['meta_description']) ? $data['meta_description'] : null,
            'is_active' => isset($data['is_active']) ? intval($data['is_active']) : 1,
            'created_at' => isset($data['created_at']) ? $data['created_at'] : current_time('mysql'),
            'created_by' => isset($data['created_by']) ? intval($data['created_by']) : get_current_user_id(),
            'updated_at' => isset($data['updated_at']) ? $data['updated_at'] : current_time('mysql')
        );

        $format = array(
            '%s', // url
            '%s', // schema_type
            '%s', // schema_name
            '%s', // schema_data
            '%s', // meta_title
            '%s', // meta_description
            '%d', // is_active
            '%s', // created_at
            '%d', // created_by
            '%s'  // updated_at
        );

        $result = $wpdb->insert($table_name, $insert_data, $format);

        if ($result === false) {
            schemafy_log('[SCHEMAFY Schema] Failed to create schema: ' . $wpdb->last_error);
            return false;
        }

        return $wpdb->insert_id;
    }
    
    /**
     * Update existing schema
     * 
     * @param int $id Schema ID
     * @param array $data Schema data to update
     * @return bool True on success, false on failure
     */
    public function update_schema($id, $data) {
        global $wpdb;
        
        $table_name = $this->get_table_name();
        
        // Build update data dynamically
        $update_data = array();
        $format = array();
        
        if (isset($data['url'])) {
            $update_data['url'] = $data['url'];
            $format[] = '%s';
        }
        
        if (isset($data['schema_type'])) {
            $update_data['schema_type'] = $data['schema_type'];
            $format[] = '%s';
        }
        
        if (isset($data['schema_name'])) {
            $update_data['schema_name'] = $data['schema_name'];
            $format[] = '%s';
        }
        
        if (isset($data['schema_data'])) {
            $update_data['schema_data'] = $data['schema_data'];
            $format[] = '%s';
        }
        
        if (isset($data['meta_title'])) {
            $update_data['meta_title'] = $data['meta_title'];
            $format[] = '%s';
        }
        
        if (isset($data['meta_description'])) {
            $update_data['meta_description'] = $data['meta_description'];
            $format[] = '%s';
        }
        
        if (isset($data['is_active'])) {
            $update_data['is_active'] = intval($data['is_active']);
            $format[] = '%d';
        }
        
        if (empty($update_data)) {
            return false;
        }
        
        $result = $wpdb->update(
            $table_name,
            $update_data,
            array('id' => $id),
            $format,
            array('%d')
        );
        
        if ($result === false) {
            schemafy_log('[SCHEMAFY Schema] Failed to update schema ' . $id . ': ' . $wpdb->last_error);
            return false;
        }
        
        return true;
    }
    
    /**
     * Delete schema
     * 
     * @param int $id Schema ID
     * @return bool True on success, false on failure
     */
    public function delete_schema($id) {
        global $wpdb;
        
        $table_name = $this->get_table_name();
        
        $result = $wpdb->delete(
            $table_name,
            array('id' => $id),
            array('%d')
        );
        
        if ($result === false) {
            schemafy_log('[SCHEMAFY Schema] Failed to delete schema ' . $id . ': ' . $wpdb->last_error);
            return false;
        }
        
        return true;
    }

    /**
     * Duplicate schema
     * Creates a copy of an existing schema with a new name
     *
     * @param int $id Schema ID to duplicate
     * @return int|false New schema ID on success, false on failure
     */
    public function duplicate_schema($id) {
        global $wpdb;

        $table_name = $this->get_table_name();

        // Get original schema
        $original = $this->get_schema($id);
        if (!$original) {
            schemafy_log('[SCHEMAFY Schema] Cannot duplicate - schema ' . $id . ' not found');
            return false;
        }

        // Prepare duplicate data
        $duplicate_data = array(
            'url' => $original->url,
            'schema_type' => $original->schema_type,
            'schema_name' => $original->schema_name . ' (Copy)',
            'schema_data' => $original->schema_data,
            'meta_title' => $original->meta_title,
            'meta_description' => $original->meta_description,
            'og_title' => $original->og_title,
            'og_description' => $original->og_description,
            'og_image' => $original->og_image,
            'og_type' => $original->og_type,
            'twitter_card' => $original->twitter_card,
            'twitter_title' => $original->twitter_title,
            'twitter_description' => $original->twitter_description,
            'twitter_image' => $original->twitter_image,
            'twitter_creator' => $original->twitter_creator,
            'is_active' => 0, // Inactive by default to avoid conflicts
            'created_at' => current_time('mysql'),
            'created_by' => get_current_user_id(),
            'updated_at' => current_time('mysql')
        );

        $format = array(
            '%s', // url
            '%s', // schema_type
            '%s', // schema_name
            '%s', // schema_data
            '%s', // meta_title
            '%s', // meta_description
            '%s', // og_title
            '%s', // og_description
            '%s', // og_image
            '%s', // og_type
            '%s', // twitter_card
            '%s', // twitter_title
            '%s', // twitter_description
            '%s', // twitter_image
            '%s', // twitter_creator
            '%d', // is_active
            '%s', // created_at
            '%d', // created_by
            '%s'  // updated_at
        );

        $result = $wpdb->insert($table_name, $duplicate_data, $format);

        if ($result === false) {
            schemafy_log('[SCHEMAFY Schema] Failed to duplicate schema ' . $id . ': ' . $wpdb->last_error);
            return false;
        }

        return $wpdb->insert_id;
    }

    /**
     * Toggle schema active status
     *
     * @param int $id Schema ID
     * @param bool|null $new_status Optional. Specific status to set (true/false). If null, toggles current status.
     * @return bool|int New status (0 or 1) on success, false on failure
     */
    public function toggle_schema($id, $new_status = null) {
        global $wpdb;

        $table_name = $this->get_table_name();

        // If new_status is not provided, get current status and toggle it
        if ($new_status === null) {
            $current_status = $wpdb->get_var($wpdb->prepare(
                "SELECT is_active FROM $table_name WHERE id = %d",
                $id
            ));

            if ($current_status === null) {
                return false;
            }

            // Toggle status
            $new_status = $current_status == '1' ? 0 : 1;
        } else {
            // Convert boolean to integer
            $new_status = $new_status ? 1 : 0;
        }

        $result = $wpdb->update(
            $table_name,
            array('is_active' => $new_status),
            array('id' => $id),
            array('%d'),
            array('%d')
        );

        if ($result === false) {
            schemafy_log('[SCHEMAFY Schema] Failed to toggle schema ' . $id . ': ' . $wpdb->last_error);
            return false;
        }

        return $new_status;
    }
    
    /**
     * Bulk create schemas
     * 
     * @param array $schemas Array of schema data
     * @return array Results with success and error counts
     */
    public function bulk_create_schemas($schemas) {
        $results = array(
            'success' => 0,
            'failed' => 0,
            'ids' => array(),
            'errors' => array()
        );

        foreach ($schemas as $schema_data) {
            // Validate individual schema before attempting to create
            $validation_error = $this->validate_schema_data($schema_data);

            if ($validation_error !== null) {
                $results['failed']++;
                $results['errors'][] = sprintf(
                    // translators: %1$s, %2$s are values inserted at runtime.
                    __('Validation failed for URL %1$s: %2$s', 'schemafy'),
                    isset($schema_data['url']) ? $schema_data['url'] : 'unknown',
                    $validation_error
                );
                continue;
            }

            $id = $this->create_schema($schema_data);

            if ($id !== false) {
                $results['success']++;
                $results['ids'][] = $id;
            } else {
                $results['failed']++;
                $results['errors'][] = sprintf(
                    // translators: %s is a value inserted at runtime.
                    __('Failed to create schema for URL: %s', 'schemafy'),
                    $schema_data['url']
                );
            }
        }

        return $results;
    }

    /**
     * Validate schema data structure
     *
     * @param array $data Schema data to validate
     * @return string|null Error message or null if valid
     */
    private function validate_schema_data($data) {
        // Check required fields
        if (empty($data['url'])) {
            return __('URL is required', 'schemafy');
        }

        if (empty($data['schema_name'])) {
            return __('Schema name is required', 'schemafy');
        }

        if (empty($data['schema_data'])) {
            return __('Schema data is required', 'schemafy');
        }

        // Validate JSON format
        if (!empty($data['schema_data'])) {
            json_decode($data['schema_data']);
            if (json_last_error() !== JSON_ERROR_NONE) {
                return __('Invalid JSON format', 'schemafy');
            }
        }

        return null; // Valid
    }
    
    /**
     * Get meta data by URL from schemas table
     * 
     * @param string $url URL to search
     * @return object|null Meta data object or null
     */
    public function get_meta_by_url($url) {
        global $wpdb;
        
        $table_name = $this->get_table_name();
        
        $normalized = untrailingslashit(strtok($url, '?'));
        $meta = $wpdb->get_row($wpdb->prepare(
            "SELECT meta_title, meta_description FROM $table_name
             WHERE (url = %s OR url = %s) AND is_active = 1
             ORDER BY updated_at DESC LIMIT 1",
            $normalized,
            trailingslashit($normalized)
        ));
        
        return $meta;
    }
    
    /**
     * Update meta data for a specific URL
     * 
     * @param string $url URL
     * @param string $meta_title Meta title
     * @param string $meta_description Meta description
     * @return bool True on success, false on failure
     */
    public function update_meta_by_url($url, $meta_title, $meta_description) {
        global $wpdb;
        
        $table_name = $this->get_table_name();
        
        // Check if schema exists for this URL
        $normalized = untrailingslashit(strtok($url, '?'));
        $schema_id = $wpdb->get_var($wpdb->prepare(
            "SELECT id FROM $table_name WHERE (url = %s OR url = %s) ORDER BY updated_at DESC LIMIT 1",
            $normalized,
            trailingslashit($normalized)
        ));
        
        if ($schema_id) {
            // Update existing schema
            $result = $wpdb->update(
                $table_name,
                array(
                    'meta_title' => $meta_title,
                    'meta_description' => $meta_description
                ),
                array('id' => $schema_id),
                array('%s', '%s'),
                array('%d')
            );
            
            return $result !== false;
        }
        
        return false;
    }
    
    /**
     * Get count of schemas by status
     * 
     * @return array Array with 'active', 'inactive', and 'total' counts
     */
    public function get_schema_counts() {
        global $wpdb;
        
        $table_name = $this->get_table_name();
        
        $active = intval($wpdb->get_var("SELECT COUNT(*) FROM $table_name WHERE is_active = 1")); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- $table_name comes from $wpdb->prefix (trusted), identifiers cannot be placeholders, and this is a custom table with no object cache
        $inactive = intval($wpdb->get_var("SELECT COUNT(*) FROM $table_name WHERE is_active = 0")); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- $table_name comes from $wpdb->prefix (trusted), identifiers cannot be placeholders, and this is a custom table with no object cache
        $total = intval($wpdb->get_var("SELECT COUNT(*) FROM $table_name")); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- $table_name comes from $wpdb->prefix (trusted), identifiers cannot be placeholders, and this is a custom table with no object cache
        
        return array(
            'active' => $active,
            'inactive' => $inactive,
            'total' => $total
        );
    }
    
    /**
     * Check if schema exists
     * 
     * @param int $id Schema ID
     * @return bool True if exists, false otherwise
     */
    public function schema_exists($id) {
        global $wpdb;
        
        $table_name = $this->get_table_name();
        
        $exists = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM $table_name WHERE id = %d",
            $id
        ));
        
        return intval($exists) > 0;
    }
    
    /**
     * Get all unique URLs from schemas
     * 
     * @return array Array of URLs
     */
    public function get_all_urls() {
        global $wpdb;
        
        $table_name = $this->get_table_name();
        
        $urls = $wpdb->get_col("SELECT DISTINCT url FROM $table_name ORDER BY url ASC"); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- $table_name comes from $wpdb->prefix (trusted), identifiers cannot be placeholders, and this is a custom table with no object cache
        
        return $urls ? $urls : array();
    }
    
    /**
     * Get all unique schema types
     * 
     * @return array Array of schema types
     */
    public function get_all_schema_types() {
        global $wpdb;

        $table_name = $this->get_table_name();

        $types = $wpdb->get_col("SELECT DISTINCT schema_type FROM $table_name ORDER BY schema_type ASC"); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- $table_name comes from $wpdb->prefix (trusted), identifiers cannot be placeholders, and this is a custom table with no object cache

        return $types ? $types : array();
    }

    /**
     * Get all schemas (no pagination)
     *
     * @return array|false Array of schemas or false on error
     */
    public function get_all_schemas() {
        global $wpdb;

        $table_name = $this->get_table_name();

        $query = "SELECT * FROM $table_name ORDER BY updated_at DESC"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table_name from trusted get_table_name(), no user input
        $results = $wpdb->get_results($query, ARRAY_A); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- static query on trusted table name, no user input

        // Convert is_active to integer for proper JavaScript boolean interpretation
        if ($results) {
            foreach ($results as &$schema) {
                $schema['is_active'] = intval($schema['is_active']);
            }
        }

        return $results !== null ? $results : false;
    }

}