<?php

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

class AIEO_Google_Reviews_Locations {

    public static function table() {
        global $wpdb;
        return $wpdb->prefix . 'aieo_google_locations';
    }

    
    public static function list_all($only_enabled = false) {
        global $wpdb;
        $table = self::table();
        $where = $only_enabled ? 'WHERE enabled = 1' : '';
        
        $rows = $wpdb->get_results("SELECT * FROM {$table} {$where} ORDER BY sort_order ASC, id ASC", ARRAY_A);
        return is_array($rows) ? $rows : array();
    }

    public static function get($id) {
        global $wpdb;
        $table = self::table();
        $row = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$table} WHERE id = %d", (int) $id), ARRAY_A);
        return is_array($row) ? $row : null;
    }

    public static function get_by_slug($slug) {
        global $wpdb;
        $table = self::table();
        $row = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$table} WHERE slug = %s", (string) $slug), ARRAY_A);
        return is_array($row) ? $row : null;
    }

    public static function get_by_place_id($place_id) {
        global $wpdb;
        $table = self::table();
        $row = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$table} WHERE place_id = %s LIMIT 1", (string) $place_id), ARRAY_A);
        return is_array($row) ? $row : null;
    }

    
    public static function save(array $data) {
        global $wpdb;
        $table = self::table();

        $id   = isset($data['id']) ? (int) $data['id'] : 0;
        $slug = isset($data['slug']) ? sanitize_key((string) $data['slug']) : '';
        $label = isset($data['label']) ? sanitize_text_field((string) $data['label']) : '';
        $place_id = isset($data['place_id']) ? sanitize_text_field((string) $data['place_id']) : '';
        $api_key = isset($data['api_key']) ? trim(sanitize_text_field((string) $data['api_key'])) : '';
        $language = isset($data['language']) ? substr(sanitize_text_field((string) $data['language']), 0, 10) : 'el';
        $enabled = !empty($data['enabled']) ? 1 : 0;

        if ($slug === '' || $label === '' || $place_id === '') {
            return new WP_Error('invalid_data', __('Slug, label and place_id are required.', 'ai-eshop-optimizer'));
        }

        
        $clash = $wpdb->get_var($wpdb->prepare(
            "SELECT id FROM {$table} WHERE slug = %s AND id <> %d LIMIT 1",
            $slug, $id
        ));
        if ($clash) {
            return new WP_Error('duplicate_slug', __('Another location already uses this slug.', 'ai-eshop-optimizer'));
        }

        $row = array(
            'slug'      => $slug,
            'label'     => $label,
            'place_id'  => $place_id,
            'api_key'   => $api_key !== '' ? $api_key : null,
            'language'  => $language,
            'enabled'   => $enabled,
        );
        if (array_key_exists('review_url', $data)) {
            $rurl = trim(sanitize_text_field((string) $data['review_url']));
            $row['review_url'] = $rurl !== '' ? $rurl : null;
        }

        
        
        
        $supp_text = array('phone' => 40, 'price_range' => 12, 'street' => 255, 'locality' => 120, 'region' => 120, 'postcode' => 20, 'country' => 2);
        foreach ($supp_text as $k => $max) {
            if (!array_key_exists($k, $data)) { continue; }
            $v = sanitize_text_field((string) $data[$k]);
            $v = function_exists('mb_substr') ? mb_substr($v, 0, $max, 'UTF-8') : substr($v, 0, $max);
            $row[$k] = '' !== $v ? $v : null;
        }
        foreach (array('gbp_url', 'image_url') as $k) {
            if (!array_key_exists($k, $data)) { continue; }
            $u = esc_url_raw(trim((string) $data[$k]));
            $row[$k] = '' !== $u ? substr($u, 0, 500) : null;
        }
        foreach (array('lat', 'lng') as $k) {
            if (!array_key_exists($k, $data)) { continue; }
            $row[$k] = ('' === $data[$k] || null === $data[$k]) ? null : (float) $data[$k];
        }
        
        
        foreach (array('listing_title' => 190, 'hero_heading' => 190) as $k => $max) {
            if (!array_key_exists($k, $data)) { continue; }
            $v = sanitize_text_field((string) $data[$k]);
            $v = function_exists('mb_substr') ? mb_substr($v, 0, $max, 'UTF-8') : substr($v, 0, $max);
            $row[$k] = '' !== $v ? $v : null;
        }
        if (array_key_exists('hero_description', $data)) {
            $v = trim(wp_kses_post((string) $data['hero_description']));
            $row['hero_description'] = '' !== $v ? $v : null;
        }

        $want_hq = !empty($data['is_hq']) ? 1 : 0;
        if (array_key_exists('is_hq', $data)) { $row['is_hq'] = $want_hq; }
        if (array_key_exists('sort_order', $data)) { $row['sort_order'] = max(0, (int) $data['sort_order']); }

        if ($id > 0) {
            $wpdb->update($table, $row, array('id' => $id));
            $saved_id = $id;
        } else {
            $wpdb->insert($table, $row);
            $saved_id = (int) $wpdb->insert_id;
        }
        
        if ($want_hq && $saved_id > 0) {
            
            $wpdb->query($wpdb->prepare("UPDATE {$table} SET is_hq = 0 WHERE id <> %d", $saved_id));
        }
        return $saved_id;
    }

    
    public static function get_hq() {
        global $wpdb;
        $table = self::table();
        
        $row = $wpdb->get_row("SELECT * FROM {$table} WHERE is_hq = 1 AND enabled = 1 ORDER BY id ASC LIMIT 1", ARRAY_A);
        if (!is_array($row)) {
            
            $row = $wpdb->get_row("SELECT * FROM {$table} WHERE enabled = 1 ORDER BY id ASC LIMIT 1", ARRAY_A);
        }
        return is_array($row) ? $row : null;
    }

    public static function delete($id) {
        global $wpdb;
        $table = self::table();
        $wpdb->delete($table, array('id' => (int) $id));
    }

    
    public static function record_fetch_result($id, $status, array $extra = array()) {
        global $wpdb;
        $table = self::table();
        $patch = array(
            'last_fetched_at'   => current_time('mysql', true),
            'last_fetch_status' => substr((string) $status, 0, 40),
        );
        if (array_key_exists('error_text', $extra)) {
            $patch['last_error_text'] = substr((string) $extra['error_text'], 0, 500);
        } else {
            $patch['last_error_text'] = null;
        }
        if (array_key_exists('rating', $extra)) {
            $patch['baseline_rating'] = is_null($extra['rating']) ? null : round((float) $extra['rating'], 1);
        }
        if (array_key_exists('review_count', $extra)) {
            $patch['baseline_review_count'] = is_null($extra['review_count']) ? null : (int) $extra['review_count'];
        }
        if (array_key_exists('google_name', $extra)) {
            $patch['google_name'] = substr((string) $extra['google_name'], 0, 190);
        }
        if (array_key_exists('address', $extra)) {
            $patch['address'] = substr((string) $extra['address'], 0, 500);
        }
        
        
        
        $supp_max = array('street' => 255, 'locality' => 120, 'region' => 120, 'postcode' => 20, 'country' => 2, 'phone' => 40);
        foreach (array('lat', 'lng', 'street', 'locality', 'region', 'postcode', 'country', 'hours_json', 'phone') as $k) {
            if (!array_key_exists($k, $extra)) { continue; }
            $v = $extra[$k];
            if (null === $v || '' === $v) { continue; }
            if ('lat' === $k || 'lng' === $k) {
                $patch[$k] = (float) $v;
            } elseif (isset($supp_max[$k])) {
                $patch[$k] = function_exists('mb_substr') ? mb_substr((string) $v, 0, $supp_max[$k], 'UTF-8') : substr((string) $v, 0, $supp_max[$k]);
            } else {
                $patch[$k] = (string) $v; 
            }
        }
        $wpdb->update($table, $patch, array('id' => (int) $id));
    }
}
