<?php
/**
 * Weekly performance snapshot storage.
 *
 * @package ContentPerformanceAnalyzer
 */

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

class CPAP_Snapshots {

    /**
     * Record the latest per-page metrics once per UTC day.
     *
     * @param array $page_flags   Page flags keyed by URL.
     * @param array $ga_rows      Google Analytics rows keyed by URL.
     * @param array $gsc_rows     Google Search Console rows keyed by URL.
     * @param array $content_data Content rows keyed by URL.
     */
    public static function maybe_record($page_flags, $ga_rows, $gsc_rows, $content_data) {
        if ( get_option( 'cpanalyzer_snapshot_last_capture' ) === gmdate( 'Y-m-d' ) ) {
            return;
        }

        global $wpdb;

        $page_flags = (array) $page_flags;
        $ga_rows = (array) $ga_rows;
        $gsc_rows = (array) $gsc_rows;
        $content_data = (array) $content_data;
        $urls = array_unique(array_merge(
            array_keys($page_flags),
            array_keys($ga_rows),
            array_keys($gsc_rows),
            array_keys($content_data)
        ));
        $rows = array();
        $week_start = self::get_week_start();
        $captured_at = gmdate('Y-m-d H:i:s');

        foreach ($urls as $url) {
            $url = (string) $url;
            if ('' === $url) {
                continue;
            }

            $ga_row = isset($ga_rows[$url]) && is_array($ga_rows[$url]) ? $ga_rows[$url] : array();
            $gsc_row = isset($gsc_rows[$url]) && is_array($gsc_rows[$url]) ? $gsc_rows[$url] : array();
            $content = isset($content_data[$url]) && is_array($content_data[$url]) ? $content_data[$url] : array();
            $decay_diagnosis = isset($gsc_row['decay_diagnosis']) && is_array($gsc_row['decay_diagnosis'])
                ? $gsc_row['decay_diagnosis']
                : array();
            $aeo_score = null;

            if (function_exists('cpanalyzer_aeo_readiness') && !empty($content)) {
                $aeo_result = cpanalyzer_aeo_readiness($content);
                if (is_array($aeo_result) && array_key_exists('score', $aeo_result)) {
                    $aeo_score = (int) $aeo_result['score'];
                }
            }

            $rows[] = array(
                'url_hash'        => md5($url),
                'url'             => $url,
                'week_start'      => $week_start,
                'clicks'          => self::nullable_int($gsc_row, 'clicks'),
                'impressions'     => self::nullable_int($gsc_row, 'impressions'),
                'ctr'             => self::nullable_float($gsc_row, 'ctr'),
                'position'        => self::nullable_float($gsc_row, 'position'),
                'views'           => self::nullable_int($ga_row, 'views'),
                'engagement_rate' => self::nullable_float($ga_row, 'engagement'),
                'conversions'     => self::nullable_int($ga_row, 'form_fills'),
                'flag'            => self::nullable_text($page_flags, $url),
                'decay_type'      => self::nullable_text($decay_diagnosis, 'type'),
                'aeo_score'       => $aeo_score,
                'captured_at'     => $captured_at,
            );
        }

        foreach (array_chunk($rows, 100) as $chunk) {
            if (false === self::insert_chunk($chunk)) {
                return;
            }
        }

        update_option('cpanalyzer_snapshot_last_capture', gmdate('Y-m-d'), false);

        $table_name = esc_sql($wpdb->prefix . 'cpanalyzer_snapshots');
        $retention_date = gmdate('Y-m-d', strtotime('-78 weeks'));
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Local retention cleanup with a sanitized plugin table name.
        $wpdb->query($wpdb->prepare(
            "DELETE FROM {$table_name} WHERE week_start < %s",
            $retention_date
        ));
    }

    /**
     * Average post-fix metrics from the weekly history.
     *
     * First reader of the snapshots table: recovery tracking compares a
     * page's average clicks/views across the weeks AFTER it was marked
     * fixed against the baseline captured at mark time. Averaging whole
     * weeks beats a single live reading — one good day cannot fake a
     * recovery.
     *
     * @param string $url             URL key as used in the options arrays.
     * @param string $after_week_start Exclusive lower bound (Y-m-d, a Monday).
     * @return array{clicks: float|null, views: float|null, weeks: int}|null Null when no rows yet.
     */
    public static function get_average_since($url, $after_week_start) {
        global $wpdb;

        $table_name = esc_sql($wpdb->prefix . 'cpanalyzer_snapshots');
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Read of the plugin's own table with a sanitized name.
        $row = $wpdb->get_row($wpdb->prepare(
            "SELECT AVG(clicks) AS avg_clicks, AVG(views) AS avg_views, COUNT(*) AS weeks
                FROM {$table_name}
                WHERE url_hash = %s AND week_start > %s
                AND (clicks IS NOT NULL OR views IS NOT NULL)",
            md5((string) $url),
            $after_week_start
        ), ARRAY_A);

        if (empty($row) || (int) $row['weeks'] < 1) {
            return null;
        }

        return array(
            'clicks' => null === $row['avg_clicks'] ? null : (float) $row['avg_clicks'],
            'views'  => null === $row['avg_views'] ? null : (float) $row['avg_views'],
            'weeks'  => (int) $row['weeks'],
        );
    }

    /**
     * Monday of the current ISO week (UTC) — public so mark-as-fixed can
     * record which week a fix landed in.
     *
     * @return string
     */
    public static function current_week_start() {
        return self::get_week_start();
    }

    /**
     * Return the Monday for an ISO week.
     *
     * @param int|null $timestamp Optional Unix timestamp for tests.
     * @return string
     */
    private static function get_week_start($timestamp = null) {
        if (null === $timestamp) {
            return gmdate('Y-m-d', strtotime(gmdate('o') . '-W' . gmdate('W') . '-1'));
        }

        return gmdate('Y-m-d', strtotime(gmdate('o', $timestamp) . '-W' . gmdate('W', $timestamp) . '-1'));
    }

    /**
     * Insert or update up to 100 snapshot rows.
     *
     * @param array $rows Snapshot rows.
     * @return int|false
     */
    private static function insert_chunk($rows) {
        global $wpdb;

        $table_name = esc_sql($wpdb->prefix . 'cpanalyzer_snapshots');
        $row_sql = array();
        $values = array();

        foreach ($rows as $row) {
            $placeholders = array();
            self::add_value($placeholders, $values, $row['url_hash'], '%s');
            self::add_value($placeholders, $values, $row['url'], '%s');
            self::add_value($placeholders, $values, $row['week_start'], '%s');
            self::add_value($placeholders, $values, $row['clicks'], '%d');
            self::add_value($placeholders, $values, $row['impressions'], '%d');
            self::add_value($placeholders, $values, $row['ctr'], '%f');
            self::add_value($placeholders, $values, $row['position'], '%f');
            self::add_value($placeholders, $values, $row['views'], '%d');
            self::add_value($placeholders, $values, $row['engagement_rate'], '%f');
            self::add_value($placeholders, $values, $row['conversions'], '%d');
            self::add_value($placeholders, $values, $row['flag'], '%s');
            self::add_value($placeholders, $values, $row['decay_type'], '%s');
            self::add_value($placeholders, $values, $row['aeo_score'], '%d');
            self::add_value($placeholders, $values, $row['captured_at'], '%s');
            $row_sql[] = '(' . implode(', ', $placeholders) . ')';
        }

        $sql = "INSERT INTO {$table_name}
            (url_hash, url, week_start, clicks, impressions, ctr, position, views, engagement_rate, conversions, flag, decay_type, aeo_score, captured_at)
            VALUES " . implode(', ', $row_sql) . "
            ON DUPLICATE KEY UPDATE
                url = VALUES(url),
                clicks = IFNULL(VALUES(clicks), clicks),
                impressions = IFNULL(VALUES(impressions), impressions),
                ctr = IFNULL(VALUES(ctr), ctr),
                position = IFNULL(VALUES(position), position),
                views = IFNULL(VALUES(views), views),
                engagement_rate = IFNULL(VALUES(engagement_rate), engagement_rate),
                conversions = IFNULL(VALUES(conversions), conversions),
                flag = IFNULL(VALUES(flag), flag),
                decay_type = IFNULL(VALUES(decay_type), decay_type),
                aeo_score = IFNULL(VALUES(aeo_score), aeo_score),
                captured_at = VALUES(captured_at)";

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Dynamic placeholders are assembled above and every non-null value is passed to prepare().
        return $wpdb->query($wpdb->prepare($sql, $values));
    }

    /**
     * Add a prepared value or a literal SQL NULL.
     *
     * @param array  $placeholders Row placeholders.
     * @param array  $values       Prepared values.
     * @param mixed  $value        Value to add.
     * @param string $placeholder  WordPress placeholder.
     */
    private static function add_value(&$placeholders, &$values, $value, $placeholder) {
        if (null === $value) {
            $placeholders[] = 'NULL';
            return;
        }

        $placeholders[] = $placeholder;
        $values[] = $value;
    }

    /**
     * Return an integer value or null when absent.
     *
     * @param array  $row Data row.
     * @param string $key Data key.
     * @return int|null
     */
    private static function nullable_int($row, $key) {
        if (!array_key_exists($key, $row) || null === $row[$key] || '' === $row[$key]) {
            return null;
        }

        return (int) $row[$key];
    }

    /**
     * Return a float value or null when absent.
     *
     * @param array  $row Data row.
     * @param string $key Data key.
     * @return float|null
     */
    private static function nullable_float($row, $key) {
        if (!array_key_exists($key, $row) || null === $row[$key] || '' === $row[$key]) {
            return null;
        }

        return (float) $row[$key];
    }

    /**
     * Return sanitized text or null when absent.
     *
     * @param array  $row Data row.
     * @param string $key Data key.
     * @return string|null
     */
    private static function nullable_text($row, $key) {
        if (!array_key_exists($key, $row) || null === $row[$key] || '' === $row[$key]) {
            return null;
        }

        return sanitize_text_field((string) $row[$key]);
    }
}
