<?php
// includes/db_recipes.php
if (!defined('ABSPATH')) exit;

function allspice_db_cache_group(): string {
    return 'allspice_db';
}

function allspice_recipes_table_suffix(): string {
    return 'allspice_recipes';
}

function allspice_recipes_table_prefix(): string {
    global $wpdb;
    $prefix = (is_object($wpdb) && isset($wpdb->prefix)) ? (string) $wpdb->prefix : 'wp_';
    if (!preg_match('/^[A-Za-z0-9_]+$/', $prefix)) $prefix = 'wp_';
    return $prefix;
}

function allspice_recipes_table_name_raw(): string {
    return allspice_recipes_table_prefix() . allspice_recipes_table_suffix();
}

function allspice_recipes_table_ident(): string {
    $raw = allspice_recipes_table_name_raw();
    if (!preg_match('/^[A-Za-z0-9_]+$/', $raw)) $raw = 'wp_' . allspice_recipes_table_suffix();
    return '`' . $raw . '`';
}

function allspice_recipes_table_exists(): bool {
    static $exists = null;
    if ($exists !== null) return $exists;
    $cache_key = 'recipes_table_exists';
    $cached = wp_cache_get($cache_key, allspice_db_cache_group());
    if ($cached !== false) {
        $exists = (bool) $cached;
        return $exists;
    }
    global $wpdb;
    if (!is_object($wpdb)) {
        $exists = false;
        return $exists;
    }
    $raw = allspice_recipes_table_name_raw();
    if (!preg_match('/^[A-Za-z0-9_]+$/', $raw)) {
        $exists = false;
        wp_cache_set($cache_key, 0, allspice_db_cache_group(), 5 * MINUTE_IN_SECONDS);
        return $exists;
    }
    $pattern = $wpdb->esc_like($raw);
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Table existence check (cached via object cache).
    $found = $wpdb->get_var(
        $wpdb->prepare('SHOW TABLES LIKE %s', $pattern)
    );
    $exists = (strcasecmp((string)$found, (string)$raw) === 0);
    wp_cache_set($cache_key, $exists ? 1 : 0, allspice_db_cache_group(), HOUR_IN_SECONDS);
    return $exists;
}

function allspice_create_recipes_table(): void {
    global $wpdb;
    if (!is_object($wpdb)) return;
    $raw = allspice_recipes_table_name_raw();
    if (!preg_match('/^[A-Za-z0-9_]+allspice_recipes$/', $raw)) return;
    $table = allspice_recipes_table_ident(); // backticked
    $charset_collate = $wpdb->get_charset_collate();
    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    $sql = "CREATE TABLE {$table} (
        id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
        url_key VARCHAR(255) NOT NULL,
        url TEXT NULL,
        recipe_id VARCHAR(64) NULL,
        wp_post_id VARCHAR(64) NULL,
        status VARCHAR(32) NOT NULL DEFAULT 'recipe',
        import_status VARCHAR(32) NOT NULL DEFAULT 'imported',
        last_checked_gmt DATETIME NULL,
        synced_at_gmt DATETIME NULL,
    
        ingredients_json LONGTEXT NULL,
        time_text VARCHAR(64) NULL,
        recipe_title TEXT NULL,
        meta_description TEXT NULL,
        meta_description_valid TINYINT(1) NULL,
        takeaways_json LONGTEXT NULL,
        faqs_json LONGTEXT NULL,
        servings INT NULL,
        calories INT NULL,
    
        PRIMARY KEY (id),
        UNIQUE KEY url_key (url_key),
        KEY wp_post_id (wp_post_id),
        KEY last_checked_gmt (last_checked_gmt),
        KEY status_import (status, import_status),
        KEY recipe_id (recipe_id)
    ) {$charset_collate};";
    dbDelta($sql);
    wp_cache_delete('recipes_table_exists', allspice_db_cache_group());
}

function allspice_recipes_cache_key(string $prefix, string $url_key): string {
    return $prefix . ':' . md5($url_key);
}

function allspice_recipes_has_url_key(string $url_key): bool {
    if (!allspice_recipes_table_exists()) return false;
    $url_key = trim($url_key);
    if ($url_key === '') return false;
    $cache_key = allspice_recipes_cache_key('has', $url_key);
    $cached = wp_cache_get($cache_key, allspice_db_cache_group());
    if ($cached !== false) return (bool)$cached;
    global $wpdb;
    if (!is_object($wpdb)) return false;
    $table = allspice_recipes_table_ident();
    // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom table query (cached via object cache).
    // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table identifier cannot be parameterized; table name is fixed suffix + $wpdb->prefix.
    // phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query is prepared; table ident is validated/allowlisted.
    $exists = (bool) $wpdb->get_var(
        $wpdb->prepare(
            "SELECT 1 FROM {$table} WHERE url_key = %s AND status = %s AND import_status = %s LIMIT 1",
            $url_key,
            'recipe',
            'imported'
        )
    );
    // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery
    // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    // phpcs:enable PluginCheck.Security.DirectDB.UnescapedDBParameter
    wp_cache_set($cache_key, $exists ? 1 : 0, allspice_db_cache_group(), 10 * MINUTE_IN_SECONDS);
    return $exists;
}

function allspice_recipes_get_id_by_url_key(string $url_key): ?string {
    if (!allspice_recipes_table_exists()) return null;
    $url_key = trim($url_key);
    if ($url_key === '') return null;
    $cache_key = allspice_recipes_cache_key('rid', $url_key);
    $cached = wp_cache_get($cache_key, allspice_db_cache_group());
    if ($cached !== false) {
        $rid = is_string($cached) ? trim($cached) : '';
        return $rid === '' ? null : $rid;
    }
    global $wpdb;
    if (!is_object($wpdb)) return null;
    $table = allspice_recipes_table_ident();
    // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom table query (cached via object cache).
    // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table identifier cannot be parameterized; table name is fixed suffix + $wpdb->prefix.
    // phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query is prepared; table ident is validated/allowlisted.
    $rid = $wpdb->get_var(
        $wpdb->prepare(
            "SELECT recipe_id FROM {$table} WHERE url_key = %s AND status = %s AND import_status = %s LIMIT 1",
            $url_key,
            'recipe',
            'imported'
        )
    );
    // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery
    // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    // phpcs:enable PluginCheck.Security.DirectDB.UnescapedDBParameter
    $rid = is_string($rid) ? trim($rid) : '';
    wp_cache_set($cache_key, $rid, allspice_db_cache_group(), 10 * MINUTE_IN_SECONDS);
    return $rid === '' ? null : $rid;
}

function allspice_recipes_cache_flush(): void {
    wp_cache_delete('recipes_table_exists', allspice_db_cache_group());
}

function allspice_recipes_table_schema_cache_key(): string {
    $raw = allspice_recipes_table_name_raw();
    return 'recipes:schema_ok:' . md5($raw);
}

function allspice_recipes_table_ensure_columns(): void {
    if (!allspice_recipes_table_exists()) return;
    $group = allspice_db_cache_group();
    $cache_key = allspice_recipes_table_schema_cache_key();
    $cached = wp_cache_get($cache_key, $group);
    if ($cached === '1') return;
    global $wpdb;
    if (!is_object($wpdb)) return;
    $table = allspice_recipes_table_ident();
    // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    $cols = $wpdb->get_col("SHOW COLUMNS FROM {$table}", 0);
    // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    if (!is_array($cols)) $cols = [];
    $have = array_fill_keys($cols, true);
    $adds = [];
    if (empty($have['ingredients_json'])) $adds[] = "ADD COLUMN ingredients_json LONGTEXT NULL";
    if (empty($have['time_text'])) $adds[] = "ADD COLUMN time_text VARCHAR(64) NULL";
    if (empty($have['recipe_title'])) $adds[] = "ADD COLUMN recipe_title TEXT NULL";
    if (empty($have['meta_description'])) $adds[] = "ADD COLUMN meta_description TEXT NULL";
    if (empty($have['meta_description_valid'])) $adds[] = "ADD COLUMN meta_description_valid TINYINT(1) NULL";
    if (empty($have['takeaways_json'])) $adds[] = "ADD COLUMN takeaways_json LONGTEXT NULL";
    if (empty($have['faqs_json'])) $adds[] = "ADD COLUMN faqs_json LONGTEXT NULL";
    if (empty($have['servings'])) $adds[] = "ADD COLUMN servings INT NULL";
    if (empty($have['calories'])) $adds[] = "ADD COLUMN calories INT NULL";
//    if ($adds) {
//        $alter = "ALTER TABLE {$table} " . implode(', ', $adds);
//        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
//        $wpdb->query($alter);
//        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
//    }
//    wp_cache_set($cache_key, '1', $group, 6 * HOUR_IN_SECONDS);
    $ok = true;
    if ($adds) {
        $alter = "ALTER TABLE {$table} " . implode(', ', $adds);
        $res = $wpdb->query($alter);
        if ($res === false || !empty($wpdb->last_error)) {
            $ok = false;
            if (allspice_is_debug()) {
                allspice_console_log('[Allspice][DB] ensure_columns_failed', [
                    'alter' => $alter,
                    'last_error' => (string)$wpdb->last_error,
                ]);
            }
        }
    }
    if ($ok) {
        wp_cache_set($cache_key, '1', $group, 6 * HOUR_IN_SECONDS);
    } else {
        wp_cache_delete($cache_key, $group);
    }
}

function allspice_recipes_get_overview_by_url_key(string $url_key): ?array {
    if (!allspice_recipes_table_exists()) return null;
    $url_key = trim($url_key);
    if ($url_key === '') return null;
    if (function_exists('allspice_recipes_table_ensure_columns')) {
        allspice_recipes_table_ensure_columns();
    }
    $cache_key = allspice_recipes_cache_key('ov', $url_key);
    $cached = wp_cache_get($cache_key, allspice_db_cache_group());
    if ($cached !== false && is_array($cached)) return $cached;
    global $wpdb;
    if (!is_object($wpdb)) return null;
    $table = allspice_recipes_table_ident();
    // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery
    // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    // phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter
    $row = $wpdb->get_row(
        $wpdb->prepare(
            "SELECT recipe_id, url, wp_post_id, ingredients_json, time_text, recipe_title, meta_description, meta_description_valid, takeaways_json, faqs_json, servings, calories
            FROM {$table}
            WHERE url_key = %s AND status = %s AND import_status = %s
            LIMIT 1",
            $url_key,
            'recipe',
            'imported'
        ),
        ARRAY_A
    );
    // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery
    // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    // phpcs:enable PluginCheck.Security.DirectDB.UnescapedDBParameter
    if (!$row || !is_array($row)) return null;
    $out = [
        'recipe_id' => null,
        'url' => null,
        'wp_post_id' => null,
        'ingredients' => [],
        'time' => null,
        'recipe_title' => null,
        'meta_description' => null,
        'meta_description_valid' => null,
        'takeaways' => [],
        'faqs' => [],
        'servings' => null,
        'calories' => null,
    ];
    $rid = isset($row['recipe_id']) ? trim((string)$row['recipe_id']) : '';
    $out['recipe_id'] = ($rid !== '') ? $rid : null;
    $u = isset($row['url']) ? trim((string)$row['url']) : '';
    $out['url'] = ($u !== '') ? $u : null;
    $wpid = isset($row['wp_post_id']) ? trim((string)$row['wp_post_id']) : '';
    $out['wp_post_id'] = ($wpid !== '') ? $wpid : null;
    if (!empty($row['ingredients_json'])) {
        $decoded = json_decode((string)$row['ingredients_json'], true);
        if (is_array($decoded)) $out['ingredients'] = $decoded;
    }
    $time = isset($row['time_text']) ? trim((string)$row['time_text']) : '';
    $out['time'] = ($time !== '') ? $time : null;
    $ti = isset($row['recipe_title']) ? trim((string)$row['recipe_title']) : '';
    $out['recipe_title'] = ($ti !== '') ? $ti : null;
    $md = isset($row['meta_description']) ? trim((string)$row['meta_description']) : '';
    $out['meta_description'] = ($md !== '') ? $md : null;
    $out['meta_description_valid'] = (isset($row['meta_description_valid']) && $row['meta_description_valid'] !== null) ? (bool)intval($row['meta_description_valid']) : null;
    if (!empty($row['takeaways_json'])) {
        $decoded = json_decode((string)$row['takeaways_json'], true);
        if (is_array($decoded)) $out['takeaways'] = $decoded;
    }
    if (!empty($row['faqs_json'])) {
        $decoded = json_decode((string)$row['faqs_json'], true);
        if (is_array($decoded)) $out['faqs'] = $decoded;
    }
    $out['servings'] = (isset($row['servings']) && $row['servings'] !== null) ? (int)$row['servings'] : null;
    $out['calories'] = (isset($row['calories']) && $row['calories'] !== null) ? (int)$row['calories'] : null;
    wp_cache_set($cache_key, $out, allspice_db_cache_group(), 10 * MINUTE_IN_SECONDS);
    return $out;
}