<?php
namespace services;

class SupplementalFeed
{
    static private function get_client_bestseller_settings( $client_id )
    {
        global $mdb;

        $row = $mdb -> query(
            'SELECT
               bestseller_roas_entry,
               bestseller_roas_exit,
               min_conversions,
               cooldown_period
             FROM clients
             WHERE id = :client_id
             LIMIT 1',
            [ ':client_id' => (int) $client_id ]
        ) -> fetch( \PDO::FETCH_ASSOC );

        if ( !is_array( $row ) )
        {
            return [
                'bestseller_roas_entry' => null,
                'bestseller_roas_exit' => null,
                'min_conversions' => 10,
                'cooldown_period' => 14
            ];
        }

        $entry_raw = trim( (string) ( $row['bestseller_roas_entry'] ?? '' ) );
        $exit_raw = trim( (string) ( $row['bestseller_roas_exit'] ?? '' ) );
        $min_conversions = (int) ( $row['min_conversions'] ?? 10 );
        $cooldown_period = (int) ( $row['cooldown_period'] ?? 14 );

        return [
            'bestseller_roas_entry' => $entry_raw !== '' ? (float) $entry_raw : null,
            'bestseller_roas_exit' => $exit_raw !== '' ? (float) $exit_raw : null,
            'min_conversions' => $min_conversions > 0 ? $min_conversions : 10,
            'cooldown_period' => $cooldown_period > 0 ? $cooldown_period : 14
        ];
    }

    static private function is_below_exit_for_cooldown( $product_id, $roas_exit, $cooldown_period )
    {
        global $mdb;

        $cooldown_period = max( 1, (int) $cooldown_period );
        $rows = $mdb -> query(
            'SELECT
               h30.date_add,
               CASE
                 WHEN SUM( h30.cost ) > 0 THEN ( SUM( h30.conversions_value ) / SUM( h30.cost ) ) * 100
                 ELSE 0
               END AS roas
             FROM products_history_30 h30
             WHERE h30.product_id = :product_id
             GROUP BY h30.date_add
             ORDER BY h30.date_add DESC
             LIMIT ' . $cooldown_period,
            [ ':product_id' => (int) $product_id ]
        ) -> fetchAll( \PDO::FETCH_ASSOC );

        if ( count( (array) $rows ) < $cooldown_period )
        {
            return false;
        }

        foreach ( $rows as $row )
        {
            $roas = (float) ( $row['roas'] ?? 0 );
            if ( $roas >= (float) $roas_exit )
            {
                return false;
            }
        }

        return true;
    }

    static private function refresh_bestseller_labels_for_client( $client_id )
    {
        global $mdb;

        $settings = self::get_client_bestseller_settings( $client_id );
        $entry = $settings['bestseller_roas_entry'];
        $exit = $settings['bestseller_roas_exit'];
        $min_conversions = (float) $settings['min_conversions'];
        $cooldown_period = (int) $settings['cooldown_period'];

        if ( $entry === null || $exit === null )
        {
            return 0;
        }

        $products = $mdb -> query(
            'SELECT
               p.id,
               p.custom_label_4,
               COALESCE( SUM( pa.cost_30 ), 0 ) AS cost_30,
               COALESCE( SUM( pa.conversion_value_30 ), 0 ) AS conversion_value_30,
               COALESCE( SUM( pa.conversions_30 ), 0 ) AS conversions_30
             FROM products p
             LEFT JOIN products_aggregate pa ON pa.product_id = p.id
             WHERE p.client_id = :client_id
             GROUP BY p.id, p.custom_label_4',
            [ ':client_id' => (int) $client_id ]
        ) -> fetchAll( \PDO::FETCH_ASSOC );

        $updated_count = 0;
        foreach ( (array) $products as $row )
        {
            $product_id = (int) ( $row['id'] ?? 0 );
            if ( $product_id <= 0 )
            {
                continue;
            }

            $cost_30 = (float) ( $row['cost_30'] ?? 0 );
            $conversion_value_30 = (float) ( $row['conversion_value_30'] ?? 0 );
            $conversions_30 = (float) ( $row['conversions_30'] ?? 0 );
            $roas_30 = $cost_30 > 0 ? ( $conversion_value_30 / $cost_30 ) * 100 : 0;

            $current_label = trim( (string) ( $row['custom_label_4'] ?? '' ) );
            if ( $current_label !== '' && $current_label !== 'bestseller' )
            {
                continue;
            }

            $entry_met = $roas_30 >= (float) $entry && $conversions_30 >= $min_conversions;
            $target_label = '';

            if ( $entry_met )
            {
                $target_label = 'bestseller';
            }
            else if ( $current_label === 'bestseller' )
            {
                $can_exit = self::is_below_exit_for_cooldown( $product_id, (float) $exit, $cooldown_period );
                $target_label = $can_exit ? '' : 'bestseller';
            }

            if ( $target_label !== $current_label )
            {
                $mdb -> update( 'products', [ 'custom_label_4' => $target_label ], [ 'id' => $product_id ] );
                $updated_count++;
            }
        }

        return $updated_count;
    }

    /**
     * Generuje supplemental feed TSV dla klienta.
     * Zwraca tablice ze statystykami: products_total, products_written, file.
     */
    static public function generate_for_client( $client_id )
    {
        global $mdb;

        $client_id = (int) $client_id;
        $labels_updated = self::refresh_bestseller_labels_for_client( $client_id );

        $products = $mdb -> query(
            "SELECT p.offer_id, p.title, p.description, p.google_product_category, p.custom_label_3, p.custom_label_4
             FROM products p
             WHERE p.client_id = :client_id
               AND p.offer_id IS NOT NULL
               AND p.offer_id <> ''
               AND ( p.title IS NOT NULL OR p.description IS NOT NULL OR p.google_product_category IS NOT NULL OR p.custom_label_3 IS NOT NULL OR p.custom_label_4 IS NOT NULL )",
            [ ':client_id' => $client_id ]
        ) -> fetchAll( \PDO::FETCH_ASSOC );

        $feeds_dir = __DIR__ . '/../../feeds';
        if ( !is_dir( $feeds_dir ) )
        {
            mkdir( $feeds_dir, 0755, true );
        }

        $filename = 'supplemental_' . $client_id . '.tsv';
        $file_path = $feeds_dir . '/' . $filename;
        $fp = fopen( $file_path, 'w' );

        if ( $fp === false )
        {
            throw new \RuntimeException( 'Nie mozna otworzyc pliku: ' . $file_path );
        }

        fwrite( $fp, "id\ttitle\tdescription\tgoogle_product_category\tcustom_label_3\tcustom_label_4\n" );

        $written = 0;
        foreach ( $products as $row )
        {
            $offer_id    = self::normalize_feed_offer_id( $row['offer_id'] ?? '' );
            $title       = self::sanitize_for_tsv( $row['title'] ?? '' );
            $description = self::sanitize_for_tsv( $row['description'] ?? '' );
            $category    = trim( (string) ( $row['google_product_category'] ?? '' ) );
            $custom_label_3 = trim( (string) ( $row['custom_label_3'] ?? '' ) );
            $custom_label_4 = trim( (string) ( $row['custom_label_4'] ?? '' ) );

            if ( $offer_id === '' || ( $title === '' && $description === '' && $category === '' && $custom_label_3 === '' && $custom_label_4 === '' ) )
            {
                continue;
            }

            fwrite( $fp, implode( "\t", [
                $offer_id,
                $title,
                $description,
                $category,
                $custom_label_3,
                $custom_label_4
            ] ) . "\n" );

            $written++;
        }

        fclose( $fp );

        return [
            'products_total'   => count( $products ),
            'products_written' => $written,
            'file'             => $filename,
            'labels_updated'   => $labels_updated
        ];
    }

    /**
     * Czyści HTML z tekstu na potrzeby TSV.
     * Zachowuje strukturę (akapity, listy) jako escaped \n.
     * GMC interpretuje literalny \n jako nowa linia w opisie.
     */
    static private function sanitize_for_tsv( $value )
    {
        $value = (string) $value;
        if ( $value === '' ) return '';

        // <li> → newline + punktor
        $value = preg_replace( '#<li[^>]*>#i', "\n- ", $value );

        // Blokowe tagi zamykajace → newline (akapity, divy, listy, naglowki)
        $value = preg_replace( '#</(?:p|div|h[1-6]|tr|ul|ol)>#i', "\n", $value );
        $value = preg_replace( '#<br\s*/?>#i', "\n", $value );

        // Usun pozostale tagi
        $value = strip_tags( $value );

        // Dekoduj encje HTML
        $value = html_entity_decode( $value, ENT_QUOTES | ENT_HTML5, 'UTF-8' );

        // Taby → spacja (tab lamie TSV)
        $value = str_replace( "\t", ' ', $value );

        // Normalizuj newline
        $value = str_replace( "\r\n", "\n", $value );
        $value = str_replace( "\r", "\n", $value );

        // Wielokrotne puste linie → max 1
        $value = preg_replace( "/\n{3,}/", "\n\n", $value );

        // Spacje wielokrotne w ramach linii → jedna
        $value = preg_replace( '/ {2,}/', ' ', $value );

        // Trim kazdej linii
        $value = implode( "\n", array_map( 'trim', explode( "\n", $value ) ) );
        $value = trim( $value );

        // Escape newline jako literalny \n dla TSV
        $value = str_replace( "\n", '\\n', $value );

        return $value;
    }

    /**
     * Normalizuje offer_id do formatu oczekiwanego przez feed supplemental.
     * - usuwa ewentualny prefix channel:lang:country:
     * - dla Shopify wymusza wielkie litery kodu kraju: shopify_PL_...
     */
    static private function normalize_feed_offer_id( $offer_id )
    {
        $offer_id = trim( (string) $offer_id );
        if ( $offer_id === '' ) return '';

        $offer_id = trim( $offer_id, " \t\n\r\0\x0B'\"" );

        if ( preg_match( '/^[a-z_]+:[a-z]{2,8}:[a-z]{2,8}:(.+)$/i', $offer_id, $matches ) )
        {
            $offer_id = trim( (string) ( $matches[1] ?? '' ) );
        }

        if ( preg_match( '/^shopify_([a-z]{2})_(.+)$/i', $offer_id, $matches ) )
        {
            $offer_id = 'shopify_' . strtoupper( (string) $matches[1] ) . '_' . (string) $matches[2];
        }

        return $offer_id;
    }
}