<?php

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

/**
 * Data Layer
 *
 * Centralised data storage and retrieval for all
 * platform features. Provides a clean API for storing
 * keyword research, rank tracking, content briefs,
 * and any other platform data.
 */
class ShadowSync_SEO_Data_Layer {

    private static $table_suffix = 'shadowsync_seo_platform';

    /**
     * Create platform database table on plugin activation
     */
    public static function install() {

        global $wpdb;

        $table    = $wpdb->prefix . self::$table_suffix;
        $charset  = $wpdb->get_charset_collate();

        // dbDelta parses the statement itself and never runs it through
        // prepare(), so the table name is interpolated here rather than bound
        // with %i. It is $wpdb->prefix plus a hardcoded suffix.
        $sql = "CREATE TABLE IF NOT EXISTS {$table} (
            id          BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            data_type   VARCHAR(50)  NOT NULL,
            data_key    VARCHAR(200) NOT NULL,
            data_value  LONGTEXT,
            post_id     BIGINT(20) UNSIGNED DEFAULT NULL,
            created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            INDEX idx_type_key (data_type, data_key),
            INDEX idx_post_id  (post_id)
        ) {$charset};";

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

        update_option('shadowsync_seo_db_version', '1.0');
    }

    /**
     * Store a value
     *
     * @param string $type  e.g. 'keyword_research', 'rank_tracking', 'content_brief'
     * @param string $key   unique identifier
     * @param mixed  $value data to store (will be JSON encoded)
     * @param int    $post_id optional post association
     */
    public static function set($type, $key, $value, $post_id = null) {

        global $wpdb;
        $table = $wpdb->prefix . self::$table_suffix;

        $existing = self::get_id($type, $key, $post_id);

        $data = [
            'data_type'  => sanitize_key($type),
            'data_key'   => sanitize_text_field($key),
            'data_value' => is_array($value) || is_object($value) ? wp_json_encode($value) : $value,
            'post_id'    => $post_id ? intval($post_id) : null,
            'updated_at' => current_time('mysql'),
        ];

        if ($existing) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->update($table, $data, ['id' => $existing]);
        } else {
            $data['created_at'] = current_time('mysql');
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->insert($table, $data);
        }

        return $wpdb->insert_id ?: $existing;
    }

    /**
     * Retrieve a value
     */
    public static function get($type, $key, $post_id = null) {

        global $wpdb;
        $table = $wpdb->prefix . self::$table_suffix;

        // Table name is bound with %i, the identifier placeholder added in
        // WordPress 6.2, so it is escaped by prepare() like any other value.
        if ($post_id) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $result = $wpdb->get_var($wpdb->prepare(
                "SELECT data_value FROM %i WHERE data_type = %s AND data_key = %s AND post_id = %d",
                $table, $type, $key, $post_id
            ));
        } else {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $result = $wpdb->get_var($wpdb->prepare(
                "SELECT data_value FROM %i WHERE data_type = %s AND data_key = %s",
                $table, $type, $key
            ));
        }

        if ($result === null) return null;

        $decoded = json_decode($result, true);
        return json_last_error() === JSON_ERROR_NONE ? $decoded : $result;
    }

    /**
     * Get all entries of a type
     */
    public static function get_all($type, $post_id = null, $limit = 100) {

        global $wpdb;
        $table = $wpdb->prefix . self::$table_suffix;

        if ($post_id) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $rows = $wpdb->get_results($wpdb->prepare(
                "SELECT * FROM %i WHERE data_type = %s AND post_id = %d ORDER BY updated_at DESC LIMIT %d",
                $table, $type, $post_id, $limit
            ), ARRAY_A);
        } else {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $rows = $wpdb->get_results($wpdb->prepare(
                "SELECT * FROM %i WHERE data_type = %s ORDER BY updated_at DESC LIMIT %d",
                $table, $type, $limit
            ), ARRAY_A);
        }

        return array_map(function($row) {
            $decoded = json_decode($row['data_value'], true);
            $row['data_value'] = json_last_error() === JSON_ERROR_NONE ? $decoded : $row['data_value'];
            return $row;
        }, $rows ?: []);
    }

    /**
     * Delete a value
     */
    public static function delete($type, $key, $post_id = null) {

        global $wpdb;
        $table = $wpdb->prefix . self::$table_suffix;

        $where = ['data_type' => $type, 'data_key' => $key];
        if ($post_id) $where['post_id'] = $post_id;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return $wpdb->delete($table, $where);
    }

    /**
     * Get the ID of an existing record
     */
    private static function get_id($type, $key, $post_id = null) {

        global $wpdb;
        $table = $wpdb->prefix . self::$table_suffix;

        if ($post_id) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            return $wpdb->get_var($wpdb->prepare(
                "SELECT id FROM %i WHERE data_type = %s AND data_key = %s AND post_id = %d",
                $table, $type, $key, $post_id
            ));
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return $wpdb->get_var($wpdb->prepare(
            "SELECT id FROM %i WHERE data_type = %s AND data_key = %s",
            $table, $type, $key
        ));
    }

    /**
     * Clean up old data (called by autopilot)
     */
    public static function cleanup($days = 90) {

        global $wpdb;
        $table = $wpdb->prefix . self::$table_suffix;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $wpdb->query($wpdb->prepare(
            "DELETE FROM %i WHERE created_at < DATE_SUB(NOW(), INTERVAL %d DAY)
             AND data_type IN ('rank_tracking_history', 'debug_log')",
            $table,
            $days
        ));
    }
}