<?php

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

function aieo_google_reviews_create_locations_table() {
    global $wpdb;

    $table = $wpdb->prefix . 'aieo_google_locations';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `id`                    bigint unsigned NOT NULL AUTO_INCREMENT,
        `slug`                  varchar(64)     NOT NULL,
        `label`                 varchar(190)    NOT NULL,
        `place_id`              varchar(190)    NOT NULL,
        `api_key`               varchar(255)    DEFAULT NULL,
        `language`              varchar(10)     NOT NULL DEFAULT 'el',
        `address`               varchar(500)    DEFAULT NULL,
        `google_name`           varchar(190)    DEFAULT NULL,
        `baseline_rating`       decimal(2,1)    DEFAULT NULL,
        `baseline_review_count` int unsigned    DEFAULT NULL,
        `last_fetched_at`       datetime        DEFAULT NULL,
        `last_fetch_status`     varchar(40)     DEFAULT NULL,
        `last_error_text`       varchar(500)    DEFAULT NULL,
        `enabled`               tinyint(1)      NOT NULL DEFAULT 1,
        `created_at`            datetime        NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_at`            datetime        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `slug` (`slug`)
    ) {$charset_collate};";

    $wpdb->query($sql);

    aieo_create_index_if_not_exists('aieo_grv_idx_loc_place', $table, '(place_id)');
    aieo_create_index_if_not_exists('aieo_grv_idx_loc_enabled', $table, '(enabled)');

    
    
    
    aieo_dmm_ensure_column($table, 'review_url', 'varchar(500) DEFAULT NULL');

    
    
    
    
    
    aieo_dmm_ensure_column($table, 'is_hq',       'tinyint(1) NOT NULL DEFAULT 0');
    aieo_dmm_ensure_column($table, 'lat',         'decimal(10,7) DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'lng',         'decimal(10,7) DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'street',      'varchar(255) DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'locality',    'varchar(120) DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'region',      'varchar(120) DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'postcode',    'varchar(20)  DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'country',     'varchar(2)   DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'hours_json',  'text         DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'phone',       'varchar(40)  DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'price_range', 'varchar(12)  DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'gbp_url',     'varchar(500) DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'image_url',   'varchar(500) DEFAULT NULL');

    
    
    
    aieo_dmm_ensure_column($table, 'sort_order',  'int unsigned NOT NULL DEFAULT 0');

    
    
    
    
    aieo_dmm_ensure_column($table, 'listing_title',    'varchar(190) DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'hero_heading',     'varchar(190) DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'hero_description', 'text         DEFAULT NULL');

    
    
    
    
    aieo_dmm_ensure_column($table, 'branch_id', 'bigint unsigned DEFAULT NULL');

    
    
    
    require_once dirname(__DIR__) . '/google-reviews/class-aieo-google-reviews-locations.php';
    foreach (AIEO_Google_Reviews_Locations::CONTACT_COLUMNS as $column => $definition) {
        aieo_dmm_ensure_column($table, $column, $definition);
    }
}

function aieo_google_reviews_create_reviews_table() {
    global $wpdb;

    $table = $wpdb->prefix . 'aieo_google_reviews';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `id`                   bigint unsigned NOT NULL AUTO_INCREMENT,
        `location_id`          bigint unsigned NOT NULL,
        `review_hash`          char(64)        NOT NULL,
        `author_name`          varchar(190)    NOT NULL,
        `author_url`           varchar(500)    DEFAULT NULL,
        `profile_photo_url`    varchar(500)    DEFAULT NULL,
        `rating`               tinyint unsigned NOT NULL,
        `review_text`          text            DEFAULT NULL,
        `review_text_original` text            DEFAULT NULL,
        `language`             varchar(10)     DEFAULT NULL,
        `original_language`    varchar(10)     DEFAULT NULL,
        `relative_time`        varchar(60)     DEFAULT NULL,
        `review_time`          int unsigned    NOT NULL,
        `matched_user_id`      bigint unsigned DEFAULT NULL,
        `match_method`         varchar(20)     DEFAULT NULL,
        `match_score`          decimal(3,2)    DEFAULT NULL,
        `raw_json`             longtext        DEFAULT NULL,
        `seen_at`              datetime        NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `is_visible`           tinyint(1)      NOT NULL DEFAULT 1,
        PRIMARY KEY (`id`),
        UNIQUE KEY `location_review` (`location_id`, `review_hash`)
    ) {$charset_collate};";

    $wpdb->query($sql);

    aieo_create_index_if_not_exists('aieo_grv_idx_rev_loc',     $table, '(location_id)');
    aieo_create_index_if_not_exists('aieo_grv_idx_rev_rating',  $table, '(rating)');
    aieo_create_index_if_not_exists('aieo_grv_idx_rev_time',    $table, '(review_time)');
    aieo_create_index_if_not_exists('aieo_grv_idx_rev_match',   $table, '(matched_user_id)');
    aieo_create_index_if_not_exists('aieo_grv_idx_rev_visible', $table, '(is_visible)');
    
    
    aieo_create_index_if_not_exists('aieo_grv_idx_rev_block',   $table, '(location_id, is_visible, rating, review_time)');
}

function aieo_google_reviews_install_schema() {
    aieo_google_reviews_create_locations_table();
    aieo_google_reviews_create_reviews_table();
}