<?php
/**
 * MTC — Location Reviews
 *
 * DB table, CRUD operations, score calculation and map page.
 *
 * @package My Trail Camera
 * @since 8.2.0
 */

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

// ============================================================================
// DATABASE
// ============================================================================

/**
 * Create the location reviews table
 */
function mtcam_locations_install() {
    global $wpdb;
    $table = $wpdb->prefix . 'mtcam_location_reviews';
    $charset = $wpdb->get_charset_collate();
    
    $technical_col = '';

    $sql = "CREATE TABLE {$table} (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        cam_id VARCHAR(50) NOT NULL,
        location_name VARCHAR(255) NOT NULL,
        gps_lat VARCHAR(50) NOT NULL,
        gps_lon VARCHAR(50) NOT NULL,
        start_date DATE NOT NULL,
        end_date DATE NOT NULL,
        days_count INT UNSIGNED NOT NULL DEFAULT 0,
        total_videos INT UNSIGNED NOT NULL DEFAULT 0,
        interesting_videos INT UNSIGNED NOT NULL DEFAULT 0,
        people_count INT UNSIGNED NOT NULL DEFAULT 0,
        satisfaction TINYINT UNSIGNED NOT NULL DEFAULT 3,
        score DECIMAL(4,1) NOT NULL DEFAULT 0,
        rating VARCHAR(1) NOT NULL DEFAULT 'C',
        notes TEXT,
        weather_data LONGTEXT,
        profile VARCHAR(50) DEFAULT '',
        {$technical_col}
        created_at DATETIME NOT NULL,
        PRIMARY KEY (id),
        KEY cam_id (cam_id),
        KEY rating (rating),
        KEY gps (gps_lat, gps_lon)
    ) {$charset};";
    
    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    dbDelta($sql);

}

/**
 * Table name
 */
function mtcam_locations_table() {
    global $wpdb;
    return $wpdb->prefix . 'mtcam_location_reviews';
}

// ============================================================================
// SCORING — location rating calculation
// ============================================================================

/**
 * Calculate location score (0-100) and rating (A-D)
 *
 * Factors:
 *   - Activity per day (videos/day)           max 30 points
 *   - Interest ratio (interesting/total)      max 25 points
 *   - Low human disturbance                   max 20 points
 *   - User satisfaction (1-5)                 max 25 points
 */
function mtcam_calculate_location_score($data) {
    $days = max(1, intval($data['days_count']));
    $total = intval($data['total_videos']);
    $interesting = intval($data['interesting_videos']);
    $people = intval($data['people_count']);
    $satisfaction = intval($data['satisfaction']);
    
    // 1. Activity per day (0-30 points)
    // 3+ videos/day = max, 0 = min
    $activity_per_day = $total / $days;
    $activity_score = min(30, ($activity_per_day / 3) * 30);
    
    // 2. Footage interest ratio (0-25 points)
    // ratio of interesting to total
    $interest_ratio = ($total > 0) ? ($interesting / $total) : 0;
    $interest_score = $interest_ratio * 25;
    
    // 3. Low human disturbance (0-20 points)
    // 0 people/day = 20, 2+ people/day = 0
    $people_per_day = $people / $days;
    $people_score = max(0, 20 - ($people_per_day * 10));
    
    // 4. User satisfaction (0-25 points)
    // 1-5 stars -> 0-25 points
    $satisfaction_score = (($satisfaction - 1) / 4) * 25;
    
    $total_score = round($activity_score + $interest_score + $people_score + $satisfaction_score, 1);
    $total_score = min(100, max(0, $total_score));
    
    // Rating
    if ($total_score >= 75) {
        $rating = 'A';
    } elseif ($total_score >= 50) {
        $rating = 'B';
    } elseif ($total_score >= 25) {
        $rating = 'C';
    } else {
        $rating = 'D';
    }
    
    return array(
        'score' => $total_score,
        'rating' => $rating,
        'activity_per_day' => round($activity_per_day, 1),
        'interest_ratio' => round($interest_ratio * 100),
        'people_per_day' => round($people_per_day, 1),
    );
}

/**
 * Marker color by rating
 */
function mtcam_location_rating_color($rating) {
    $colors = array(
        'A' => '#4CAF50',
        'B' => '#8BC34A',
        'C' => '#FF9800',
        'D' => '#f44336',
    );
    return $colors[$rating] ?? '#999';
}

/**
 * Emoji by rating
 */
function mtcam_location_rating_emoji($rating) {
    $emojis = array(
        'A' => '🟢',
        'B' => '🟡',
        'C' => '🟠',
        'D' => '🔴',
    );
    return $emojis[$rating] ?? '⚪';
}

/**
 * Label by rating
 */
function mtcam_location_rating_label($rating) {
    $labels = array(
        'A' => __('Excellent location', 'my-trail-camera'),
        'B' => __('Good location', 'my-trail-camera'),
        'C' => __('Average location', 'my-trail-camera'),
        'D' => __('Unsatisfactory location', 'my-trail-camera'),
    );
    return $labels[$rating] ?? __('Unknown', 'my-trail-camera');
}

// ============================================================================
// CRUD
// ============================================================================

/**
 * Save a location review
 */
function mtcam_location_review_save($data) {
    global $wpdb;
    $table = mtcam_locations_table();

    $score_data = mtcam_calculate_location_score($data);
    $score_val  = $score_data['score'];
    $rating_val = $score_data['rating'];

    $row = array(
        'cam_id'             => sanitize_text_field($data['cam_id']),
        'location_name'      => sanitize_text_field($data['location_name']),
        'gps_lat'            => sanitize_text_field($data['gps_lat']),
        'gps_lon'            => sanitize_text_field($data['gps_lon']),
        'start_date'         => sanitize_text_field($data['start_date']),
        'end_date'           => sanitize_text_field($data['end_date']),
        'days_count'         => intval($data['days_count']),
        'total_videos'       => intval($data['total_videos']),
        'interesting_videos' => intval($data['interesting_videos']),
        'people_count'       => intval($data['people_count']),
        'satisfaction'       => intval($data['satisfaction']),
        'score'              => $score_val,
        'rating'             => $rating_val,
        'notes'              => sanitize_textarea_field($data['notes'] ?? ''),
        'profile'            => sanitize_text_field($data['profile'] ?? ''),
        'created_at'         => !empty($data['created_at']) ? sanitize_text_field($data['created_at']) : current_time('mysql'),
    );
    $formats = array( '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%d', '%d', '%d', '%f', '%s', '%s', '%s', '%s' );

    mtcam_db_insert( $table, $row, $formats );

    wp_cache_delete( 'mtcam_location_reviews_all' );
    wp_cache_delete( 'mtcam_location_reviews_stats' );

    return $wpdb->insert_id;
}

/**
 * Get all reviews (for map)
 */
function mtcam_location_reviews_get_all() {
    global $wpdb;
    $table = mtcam_locations_table();
    
    $cached = wp_cache_get( 'mtcam_location_reviews_all' );
    if ( false !== $cached ) {
        return $cached;
    }
    $result = mtcam_db_get_results(
        'SELECT * FROM `' . $wpdb->prefix . 'mtcam_location_reviews` ORDER BY created_at DESC',
        ARRAY_A
    );
    wp_cache_set( 'mtcam_location_reviews_all', $result );
    return $result;
}

/**
 * Get reviews for a specific camera
 */
function mtcam_location_reviews_by_camera($cam_id) {
    global $wpdb;

    $cache_key = 'mtcam_loc_reviews_' . $cam_id;
    $cached = wp_cache_get( $cache_key );
    if ( false !== $cached ) {
        return $cached;
    }
    $result = mtcam_db_get_results( $wpdb->prepare(
        'SELECT * FROM `' . $wpdb->prefix . 'mtcam_location_reviews` WHERE cam_id = %s ORDER BY created_at DESC',
        $cam_id
    ), ARRAY_A);
    wp_cache_set( $cache_key, $result );
    return $result;
}

/**
 * Statistics for map
 */
function mtcam_location_reviews_stats() {
    global $wpdb;
    $table = mtcam_locations_table();

    $cached = wp_cache_get( 'mtcam_location_reviews_stats' );
    if ( false !== $cached ) {
        return $cached;
    }

    $valid_where = '';
    $select_technical = '0 as technical';

    // One query returns everything: totals include flagged; avg_score only from valid.
    $row = mtcam_db_get_row(
        'SELECT COUNT(*) as total,
                ' . $select_technical . ',
                SUM(days_count) as total_days,
                SUM(total_videos) as total_videos
         FROM `' . $wpdb->prefix . 'mtcam_location_reviews`',
        ARRAY_A
    );

    // Avg score only from non-flagged reviews (flagged reviews have score=0).
    $avg_row = mtcam_db_get_row(
        'SELECT AVG(score) as avg_score FROM `' . $wpdb->prefix . 'mtcam_location_reviews`' . $valid_where,
        ARRAY_A
    );

    $result = array(
        'total'        => intval( $row['total'] ?? 0 ),
        'technical'    => intval( $row['technical'] ?? 0 ),
        'total_days'   => intval( $row['total_days'] ?? 0 ),
        'total_videos' => intval( $row['total_videos'] ?? 0 ),
        'avg_score'    => floatval( $avg_row['avg_score'] ?? 0 ),
    );
    $result['valid'] = max( 0, $result['total'] - $result['technical'] );

    wp_cache_set( 'mtcam_location_reviews_stats', $result );
    return $result;
}

/**
 * Delete a review
 */
function mtcam_location_review_delete($id) {
    global $wpdb;
    $deleted = mtcam_db_delete( mtcam_locations_table(), array( 'id' => intval( $id ) ), array( '%d' ) );
    wp_cache_delete( 'mtcam_location_reviews_all' );
    wp_cache_delete( 'mtcam_location_reviews_stats' );
    return $deleted;
}

