<?php
if (!defined('ABSPATH')) exit;

class AIWEST_Goals {
    private static function tables() {
        if (class_exists('AIWEST_Database') && method_exists('AIWEST_Database', 'tables')) {
            return AIWEST_Database::tables();
        }
        global $wpdb;
        return ['goals' => $wpdb->prefix . 'aiwest_goals'];
    }

    public static function table_exists($table_name) {
        global $wpdb;
        if (class_exists('AIWEST_Database') && method_exists('AIWEST_Database', 'table_exists')) {
            return AIWEST_Database::table_exists($table_name);
        }
        // Table discovery is used only for the plugin's private schema and must reflect upgrades immediately.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table_name)) === $table_name;
    }

    public static function ensure_tables() {
        if (class_exists('AIWEST_Database') && method_exists('AIWEST_Database', 'create_tables')) {
            AIWEST_Database::create_tables();
            return true;
        }
        return false;
    }

    public static function add($title, $description = '', $priority = 'normal') {
        global $wpdb;
        $t = self::tables();
        self::ensure_tables();

        $title = sanitize_text_field($title);
        $description = wp_kses_post($description);
        $priority = sanitize_text_field($priority);

        if ($title === '') {
            return new WP_Error('aiwest_empty_goal_title', 'A goal cannot be saved without a title.');
        }

        if (empty($t['goals']) || !self::table_exists($t['goals'])) {
            return new WP_Error('aiwest_goals_table_missing', 'The AI Webmaster Studio goals table does not exist or could not be created: ' . ($t['goals'] ?? 'unknown'));
        }

        // Goal writes intentionally use the plugin's private table; WordPress has no equivalent content API.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $ok = $wpdb->insert($t['goals'], [
            'goal_title' => $title,
            'goal_description' => $description,
            'priority' => $priority ?: 'normal',
            'status' => 'active',
            'created_at' => current_time('mysql'),
            'updated_at' => current_time('mysql')
        ], ['%s','%s','%s','%s','%s','%s']);

        if ($ok === false) {
            if (class_exists('AIWEST_Logger')) {
                AIWEST_Logger::error('Goal insert failed.', [
                    'db_error' => $wpdb->last_error,
                    'query' => $wpdb->last_query,
                    'table' => $t['goals']
                ]);
            }
            return new WP_Error('aiwest_goal_insert_failed', 'The goal could not be saved. Database error: ' . ($wpdb->last_error ?: 'no details'));
        }

        if (class_exists('AIWEST_Logger')) {
            AIWEST_Logger::info('Goal added.', ['goal_id' => $wpdb->insert_id, 'title' => $title]);
        }

        return intval($wpdb->insert_id);
    }

    public static function update($id, $title, $description = '', $priority = 'normal', $status = 'active') {
        global $wpdb;
        $t = self::tables();

        $id = intval($id);
        if ($id <= 0) return new WP_Error('aiwest_invalid_goal_id', 'Invalid goal ID.');

        if (empty($t['goals']) || !self::table_exists($t['goals'])) {
            return new WP_Error('aiwest_goals_table_missing', 'The AI Webmaster Studio goals table does not exist.');
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- This is a write to the plugin's private goals table; no cache read is involved.
        $ok = $wpdb->update($t['goals'], [
            'goal_title' => sanitize_text_field($title),
            'goal_description' => wp_kses_post($description),
            'priority' => sanitize_text_field($priority),
            'status' => sanitize_text_field($status),
            'updated_at' => current_time('mysql')
        ], ['id' => $id], ['%s','%s','%s','%s','%s'], ['%d']);

        if ($ok === false) {
            if (class_exists('AIWEST_Logger')) {
                AIWEST_Logger::error('Goal update failed.', ['db_error' => $wpdb->last_error, 'goal_id' => $id]);
            }
            return new WP_Error('aiwest_goal_update_failed', 'The goal could not be updated: ' . ($wpdb->last_error ?: 'no details'));
        }

        return true;
    }

    public static function delete($id) {
        global $wpdb;
        $t = self::tables();
        if (empty($t['goals']) || !self::table_exists($t['goals'])) return false;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- This is a delete from the plugin's private goals table.
        return $wpdb->delete($t['goals'], ['id' => intval($id)], ['%d']);
    }

    public static function active() {
        global $wpdb;
        $t = self::tables();
        if (empty($t['goals']) || !self::table_exists($t['goals'])) return [];
        // Goal state is mutable and stored in a private plugin table.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return $wpdb->get_results($wpdb->prepare("SELECT id, goal_title, goal_description, priority, status FROM %i WHERE status = 'active' ORDER BY id DESC", $t['goals']), ARRAY_A);
    }

    public static function all() {
        global $wpdb;
        $t = self::tables();
        if (empty($t['goals']) || !self::table_exists($t['goals'])) return [];
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return $wpdb->get_results($wpdb->prepare('SELECT * FROM %i ORDER BY status ASC, id DESC', $t['goals']));
    }

    public static function get($id) {
        global $wpdb;
        $t = self::tables();
        if (empty($t['goals']) || !self::table_exists($t['goals'])) return null;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return $wpdb->get_row($wpdb->prepare('SELECT * FROM %i WHERE id = %d', $t['goals'], intval($id)));
    }
}