<?php

declare(strict_types=1);


/**
 * Sustainability History Provider
 *
 * Tracks sustainability metrics over time for trending and improvement tracking.
 *
 * @package ProRank\SEO\Core\DataProviders
 */

namespace ProRank\SEO\Core\DataProviders;

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


/**
 * Provides historical sustainability tracking and trending
 */


class SustainabilityHistory {
    /**
     * Database table name (without prefix)
     */
    const TABLE_NAME = 'prorank_sustainability_history';

    /**
     * Database version for migrations
     */
    const DB_VERSION = '1.0';

    /**
     * Option key for DB version
     */
    const DB_VERSION_OPTION = 'prorank_sustainability_history_db_version';

    /**
     * Create the history table if it doesn't exist
     *
     * @return bool
     */
    public static function create_table() {
        global $wpdb;

        $table_name = $wpdb->prefix . self::TABLE_NAME;
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE IF NOT EXISTS {$table_name} (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            url VARCHAR(500) NOT NULL,
            post_id BIGINT UNSIGNED NULL,
            carbon_per_view DECIMAL(10,4) DEFAULT 0,
            page_size_bytes BIGINT UNSIGNED DEFAULT 0,
            rating VARCHAR(2) DEFAULT 'F',
            green_hosting TINYINT(1) DEFAULT 0,
            energy_per_view DECIMAL(10,6) DEFAULT 0,
            cleaner_than_percent DECIMAL(5,2) DEFAULT 0,
            checked_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_url (url(191)),
            INDEX idx_post_id (post_id),
            INDEX idx_checked_at (checked_at),
            INDEX idx_rating (rating)
        ) {$charset_collate};";

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

        update_option( self::DB_VERSION_OPTION, self::DB_VERSION );

        return true;
    }

    /**
     * Check if table exists
     *
     * @return bool
     */
    public static function table_exists() {
        global $wpdb;
        $table_name = $wpdb->prefix . self::TABLE_NAME;
        return $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name ) ) === $table_name;
    }

    /**
     * Record a sustainability snapshot
     *
     * @param array $data Sustainability data
     * @return int|false Insert ID or false on failure
     */
    public function record_snapshot( array $data ) {
        global $wpdb;

        if ( ! self::table_exists() ) {
            self::create_table();
        }

        $table_name = $wpdb->prefix . self::TABLE_NAME;

        $insert_data = [
            'url'                  => $data['url'] ?? '',
            'post_id'              => $data['post_id'] ?? null,
            'carbon_per_view'      => $data['carbon_per_view'] ?? 0,
            'page_size_bytes'      => $data['page_size_bytes'] ?? 0,
            'rating'               => $data['rating'] ?? 'F',
            'green_hosting'        => $data['green_hosting'] ? 1 : 0,
            'energy_per_view'      => $data['energy_per_view'] ?? 0,
            'cleaner_than_percent' => $data['cleaner_than_percent'] ?? 0,
            'checked_at'           => current_time( 'mysql' ),
        ];

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

        return $result ? $wpdb->insert_id : false;
    }

    /**
     * Record snapshot from sustainability estimate data
     *
     * @param array    $estimate Sustainability estimate from SustainabilityProvider
     * @param int|null $post_id  Optional post ID
     * @return int|false
     */
    public function record_from_estimate( array $estimate, ?int $post_id = null ) {
        return $this->record_snapshot( [
            'url'                  => $estimate['url'] ?? '',
            'post_id'              => $post_id,
            'carbon_per_view'      => $estimate['metrics']['carbon_per_view'] ?? 0,
            'page_size_bytes'      => $estimate['metrics']['bytes_transferred'] ?? 0,
            'rating'               => $estimate['comparison']['rating'] ?? 'F',
            'green_hosting'        => $estimate['metrics']['green_hosting'] ?? false,
            'energy_per_view'      => $estimate['metrics']['energy_per_view'] ?? 0,
            'cleaner_than_percent' => $estimate['comparison']['cleaner_than_percent'] ?? 0,
        ] );
    }

    /**
     * Get history for a URL
     *
     * @param string $url   URL to get history for
     * @param int    $limit Number of records to return
     * @return array
     */
    public function get_url_history( string $url, int $limit = 30 ) {
        global $wpdb;

        if ( ! self::table_exists() ) {
            return [];
        }

        $table_name = $wpdb->prefix . self::TABLE_NAME;

        return $wpdb->get_results(
            $wpdb->prepare(
                "SELECT * FROM {$table_name} WHERE url = %s ORDER BY checked_at DESC LIMIT %d",
                $url,
                $limit
            ),
            ARRAY_A
        );
    }

    /**
     * Get history for a post
     *
     * @param int $post_id Post ID
     * @param int $limit   Number of records to return
     * @return array
     */
    public function get_post_history( int $post_id, int $limit = 30 ) {
        global $wpdb;

        if ( ! self::table_exists() ) {
            return [];
        }

        $table_name = $wpdb->prefix . self::TABLE_NAME;

        return $wpdb->get_results(
            $wpdb->prepare(
                "SELECT * FROM {$table_name} WHERE post_id = %d ORDER BY checked_at DESC LIMIT %d",
                $post_id,
                $limit
            ),
            ARRAY_A
        );
    }

    /**
     * Get site-wide history (averages over time)
     *
     * @param int $days Number of days to look back
     * @return array
     */
    public function get_site_history( int $days = 30 ) {
        global $wpdb;

        if ( ! self::table_exists() ) {
            return [];
        }

        $table_name = $wpdb->prefix . self::TABLE_NAME;

        return $wpdb->get_results(
            $wpdb->prepare(
                "SELECT
                    DATE(checked_at) as date,
                    AVG(carbon_per_view) as avg_carbon,
                    AVG(page_size_bytes) as avg_size,
                    AVG(cleaner_than_percent) as avg_cleaner_than,
                    COUNT(*) as page_count
                FROM {$table_name}
                WHERE checked_at >= DATE_SUB(NOW(), INTERVAL %d DAY)
                GROUP BY DATE(checked_at)
                ORDER BY date ASC",
                $days
            ),
            ARRAY_A
        );
    }

    /**
     * Get trending data
     *
     * @param string $period Period: '7d', '30d', '90d'
     * @return array
     */
    public function get_trending( string $period = '30d' ) {
        $days = match ( $period ) {
            '7d'  => 7,
            '90d' => 90,
            default => 30,
        };

        $history = $this->get_site_history( $days );

        if ( count( $history ) < 2 ) {
            return [
                'period'           => $period,
                'data_points'      => count( $history ),
                'has_enough_data'  => false,
                'carbon_trend'     => 0,
                'size_trend'       => 0,
                'rating_trend'     => 0,
                'history'          => $history,
            ];
        }

        // Calculate trends (first vs last data point)
        $first = $history[0];
        $last = $history[ count( $history ) - 1 ];

        $carbon_change = $first['avg_carbon'] > 0
            ? ( ( $last['avg_carbon'] - $first['avg_carbon'] ) / $first['avg_carbon'] ) * 100
            : 0;

        $size_change = $first['avg_size'] > 0
            ? ( ( $last['avg_size'] - $first['avg_size'] ) / $first['avg_size'] ) * 100
            : 0;

        $rating_change = $last['avg_cleaner_than'] - $first['avg_cleaner_than'];

        return [
            'period'           => $period,
            'data_points'      => count( $history ),
            'has_enough_data'  => true,
            'carbon_trend'     => round( $carbon_change, 2 ),
            'size_trend'       => round( $size_change, 2 ),
            'rating_trend'     => round( $rating_change, 2 ),
            'first_reading'    => $first,
            'last_reading'     => $last,
            'history'          => $history,
        ];
    }

    /**
     * Get improvement summary
     *
     * @return array
     */
    public function get_improvement_summary() {
        global $wpdb;

        if ( ! self::table_exists() ) {
            return [
                'total_snapshots'    => 0,
                'unique_urls'        => 0,
                'avg_carbon'         => 0,
                'avg_size'           => 0,
                'green_hosting_pct'  => 0,
                'best_rating'        => 'N/A',
                'worst_rating'       => 'N/A',
            ];
        }

        $table_name = $wpdb->prefix . self::TABLE_NAME;

        $stats = $wpdb->get_row(
            "SELECT
                COUNT(*) as total_snapshots,
                COUNT(DISTINCT url) as unique_urls,
                AVG(carbon_per_view) as avg_carbon,
                AVG(page_size_bytes) as avg_size,
                AVG(green_hosting) * 100 as green_hosting_pct
            FROM {$table_name}",
            ARRAY_A
        );

        // Get best/worst ratings from recent data
        $ratings = $wpdb->get_col(
            "SELECT DISTINCT rating FROM {$table_name}
            WHERE checked_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
            ORDER BY FIELD(rating, 'A+', 'A', 'B', 'C', 'D', 'F')"
        );

        return [
            'total_snapshots'   => (int) ( $stats['total_snapshots'] ?? 0 ),
            'unique_urls'       => (int) ( $stats['unique_urls'] ?? 0 ),
            'avg_carbon'        => round( (float) ( $stats['avg_carbon'] ?? 0 ), 4 ),
            'avg_size'          => (int) ( $stats['avg_size'] ?? 0 ),
            'green_hosting_pct' => round( (float) ( $stats['green_hosting_pct'] ?? 0 ), 1 ),
            'best_rating'       => $ratings[0] ?? 'N/A',
            'worst_rating'      => end( $ratings ) ?: 'N/A',
        ];
    }

    /**
     * Export history data
     *
     * @param string $format Format: 'json' or 'csv'
     * @param int    $days   Number of days to export
     * @return string
     */
    public function export( string $format = 'json', int $days = 90 ) {
        global $wpdb;

        if ( ! self::table_exists() ) {
            return $format === 'json' ? '[]' : '';
        }

        $table_name = $wpdb->prefix . self::TABLE_NAME;

        $data = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT * FROM {$table_name}
                WHERE checked_at >= DATE_SUB(NOW(), INTERVAL %d DAY)
                ORDER BY checked_at DESC",
                $days
            ),
            ARRAY_A
        );

        if ( $format === 'csv' ) {
            if ( empty( $data ) ) {
                return '';
            }

            $output = implode( ',', array_keys( $data[0] ) ) . "\n";
            foreach ( $data as $row ) {
                $output .= implode( ',', array_map( function ( $val ) {
                    return '"' . str_replace( '"', '""', (string) $val ) . '"';
                }, $row ) ) . "\n";
            }
            return $output;
        }

        return wp_json_encode( $data, JSON_PRETTY_PRINT );
    }

    /**
     * Clean up old history records
     *
     * @param int $days_to_keep Number of days to keep
     * @return int Number of deleted rows
     */
    public function cleanup( int $days_to_keep = 365 ) {
        global $wpdb;

        if ( ! self::table_exists() ) {
            return 0;
        }

        $table_name = $wpdb->prefix . self::TABLE_NAME;

        return $wpdb->query(
            $wpdb->prepare(
                "DELETE FROM {$table_name} WHERE checked_at < DATE_SUB(NOW(), INTERVAL %d DAY)",
                $days_to_keep
            )
        );
    }

    /**
     * Drop the table (for uninstall)
     *
     * @return bool
     */
    public static function drop_table() {
        global $wpdb;
        $table_name = $wpdb->prefix . self::TABLE_NAME;
        return $wpdb->query( "DROP TABLE IF EXISTS {$table_name}" ) !== false;
    }
}
