<?php

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

class AIEO_DMM_Store_Builder {

    
    const BRANCH_SLUGS = array('piraeus', 'keratsini', 'korydallos', 'kallithea');

    private static function ensure_schema_loaded() {
        if (!function_exists('aieo_dmm_ensure_store_board') || !function_exists('aieo_dmm_table_exists')) {
            $f = AIEO_DIR . 'includes/dmm/sql/dmm-schema.php';
            if (is_readable($f)) { require_once $f; }
        }
    }

    
    public static function sync_supplier_terms() {
        global $wpdb;
        self::ensure_schema_loaded();
        $map = $wpdb->prefix . 'aieo_dmm_supplier_sku_map';
        if (!function_exists('aieo_dmm_table_exists') || !aieo_dmm_table_exists($map)) { return 0; }

        if (!taxonomy_exists('supplier')) {
            register_taxonomy('supplier', 'product', array('hierarchical' => false, 'public' => false, 'show_ui' => false));
        }

        
        $names = array();
        $sfe = $wpdb->prefix . 'pods_suppliers_from_erp';
        if (aieo_dmm_table_exists($sfe)) {
            foreach ((array) $wpdb->get_results("SELECT erp_id, name FROM {$sfe}", ARRAY_A) as $r) {
                $names[(string) $r['erp_id']] = (string) $r['name'];
            }
        }

        $erp_ids = $wpdb->get_col("SELECT DISTINCT supplier_erp_id FROM {$map} WHERE supplier_erp_id IS NOT NULL AND supplier_erp_id <> ''");
        $resolved = 0;
        foreach ((array) $erp_ids as $erp) {
            $erp = (string) $erp;
            $term_id = (int) $wpdb->get_var($wpdb->prepare(
                "SELECT term_id FROM {$wpdb->termmeta} WHERE meta_key = 'aieo_supplier_erp_id' AND meta_value = %s LIMIT 1",
                $erp
            ));
            if (!$term_id) {
                $name = (isset($names[$erp]) && $names[$erp] !== '') ? $names[$erp] : ('ERP Supplier ' . $erp);
                $res  = wp_insert_term($name, 'supplier');
                if (!is_wp_error($res)) {
                    $term_id = (int) $res['term_id'];
                } elseif (is_wp_error($res) && isset($res->error_data['term_exists'])) {
                    $term_id = (int) $res->error_data['term_exists'];
                }
                if ($term_id) { update_term_meta($term_id, 'aieo_supplier_erp_id', $erp); }
            }
            if ($term_id) {
                $t  = get_term($term_id);
                $nm = ($t && !is_wp_error($t)) ? $t->name : '';
                $wpdb->query($wpdb->prepare(
                    "UPDATE {$map} SET supplier_term_id = %d, supplier_name = %s WHERE supplier_erp_id = %s",
                    $term_id, $nm, $erp
                ));
                $resolved++;
            }
        }
        return $resolved;
    }

    
    public static function upsert_boards() {
        global $wpdb;
        self::ensure_schema_loaded();
        $reg   = $wpdb->prefix . 'aieo_dmm_branches';
        $stats = $wpdb->prefix . 'aieo_dmm_preorder_vitals_stats';
        if (!aieo_dmm_table_exists($reg) || !aieo_dmm_table_exists($stats)) { return array(); }

        
        
        
        $wpdb->query("SET @BULK_LOAD = 1");

        $slugs = (array) $wpdb->get_col("SELECT slug FROM {$reg} WHERE enabled = 1");
        $out   = array();
        foreach ($slugs as $raw) {
            $slug = preg_replace('/[^a-z0-9_]/', '', strtolower((string) $raw));
            if (!in_array($slug, self::BRANCH_SLUGS, true)) { continue; } 
            $board = aieo_dmm_ensure_store_board($slug);
            if (!$board) { continue; }

            $trend = "CASE WHEN s.{$slug}_30d = 0 AND s.{$slug}_60d = 0 THEN NULL ELSE s.trend_of_{$slug} END";

            $wpdb->query(
                "INSERT INTO `{$board}`
                   (product_id, parent_id, image_url, parent_image_url, brand, product_name, supplier, supplier_id,
                    menu_category, head_category, sub_category, sku, parent_sku, gtin, sku_norm,
                    sales_30d, sales_60d, sales_trend, snapshot_date, created, modified)
                 SELECT s.product_id, s.parent_id, s.image_url, s.parent_image_url, s.brand, s.name, s.supplier, s.supplier_id,
                        s.menu_category, s.head_category, s.sub_category, s.sku, s.parent_sku, s.gtin, s.sku_norm,
                        s.{$slug}_30d, s.{$slug}_60d, {$trend},
                        s.snapshot_date, NOW(), NOW()
                 FROM {$stats} s
                 WHERE s.snapshot_date = CURDATE() AND s.product_id > 0 AND s.supplier_id > 0 AND TRIM(COALESCE(s.sku, '')) <> ''
                   AND NOT EXISTS (SELECT 1 FROM `{$board}` b WHERE b.product_id = s.product_id)"
            );
            $inserted = (int) $wpdb->rows_affected;

            $wpdb->query(
                "UPDATE `{$board}` b
                   JOIN {$stats} s ON s.product_id = b.product_id
                    SET b.sales_30d = s.{$slug}_30d,
                        b.sales_60d = s.{$slug}_60d,
                        b.supplier_id = s.supplier_id,
                        b.supplier = s.supplier,
                        b.sales_trend = {$trend},
                        b.modified = NOW()
                  WHERE s.snapshot_date = CURDATE()"
            );
            $updated = (int) $wpdb->rows_affected;

            $out[$slug] = array('inserted' => $inserted, 'updated' => $updated);
        }
        $wpdb->query("SET @BULK_LOAD = 0");
        return $out;
    }

    
    public static function rebuild_all() {
        self::sync_supplier_terms();
        self::upsert_boards();
    }
}
