<?php
/**
 * MTC - Camera Repository
 * DB layer for cameras (CRUD operations).
 *
 * Standalone functions — WordPress pattern.
 * Moved from admin/cameras-helpers.php.
 *
 * @package My Trail Camera
 * @since 8.2.0
 */

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

// ============================================================================
// CAMERAS DATABASE LAYER (v8.0)
// Replaces get_option('mtcam_cameras') / update_option pattern
// Atomic operations — no race conditions
// ============================================================================

/**
 * Get cameras table name
 */
function mtcam_cameras_table() {
    global $wpdb;
    return $wpdb->prefix . 'mtcam_cameras';
}

/**
 * Does cameras table exist? Checked via wp_option (without SHOW TABLES)
 */
function mtcam_cameras_table_exists() {
    global $mtcam_cameras_table_exists;

    if (isset($mtcam_cameras_table_exists)) return $mtcam_cameras_table_exists;

    $mtcam_cameras_table_exists = (bool) get_option('mtcam_cameras_table_created', false);
    return $mtcam_cameras_table_exists;
}

/**
 * Get all cameras as associative array [cam_id => array(...)]
 * Drop-in replacement for get_option('mtcam_cameras', array())
 */
function mtcam_get_cameras() {
    global $wpdb, $mtcam_cameras_cache;

    if (is_array($mtcam_cameras_cache)) return $mtcam_cameras_cache;

    $table = mtcam_cameras_table();

    // If table does not exist yet (before migration), fallback to wp_options
    if (!mtcam_cameras_table_exists()) {
        return get_option('mtcam_cameras', array());
    }

    $cameras = wp_cache_get( 'mtcam_all_cameras' );
    if ( false === $cameras ) {
        $rows = mtcam_db_get_results( 'SELECT * FROM `' . $wpdb->prefix . 'mtcam_cameras` ORDER BY created_date DESC', ARRAY_A );

        $cameras = array();
        if ($rows) {
            foreach ($rows as $row) {
                $cam_id = $row['cam_id'];
                $extra = (!empty($row['extra_data'])) ? json_decode($row['extra_data'], true) : array();
                if (!is_array($extra)) $extra = array();

                $cam = array(
                    'name'             => $row['name'] ?? '',
                    'location'         => $row['location'] ?? '',
                    'color'            => $row['color'] ?? MTCAM_DEFAULT_COLOR,
                    'gps_lat'          => $row['gps_lat'] ?? '',
                    'gps_lon'          => $row['gps_lon'] ?? '',
                    'active'           => $row['active'] ?? 'yes',
                    'tested'           => $row['tested'] ?? 'no',
                    'stolen_date'      => $row['stolen_date'] ?? '',
                    'police_reported'  => $row['police_reported'] ?? 'no',
                    'owner'            => $row['owner'] ?? 'me',
                    'owner_name'       => $row['owner_name'] ?? '',
                    'owner_email'      => $row['owner_email'] ?? '',
                    'price'            => floatval($row['price'] ?? 0),
                    'manufacturer'     => $row['manufacturer'] ?? '',
                    'imei'             => $row['imei'] ?? '',
                    'deploy_date'      => $row['deploy_date'] ?? '',
                    'purchase_date'    => $row['purchase_date'] ?? '',
                    'created_date'     => $row['created_date'] ?? '',
                    'power_type'       => $row['power_type'] ?? 'lithium',
                    'planned_days'     => intval($row['planned_days'] ?? 0),
                );

                $cameras[$cam_id] = array_merge($cam, $extra);
            }
        }

        wp_cache_set( 'mtcam_all_cameras', $cameras );
    }

    $mtcam_cameras_cache = $cameras;
    return $cameras;
}

/**
 * Save one camera (INSERT or UPDATE)
 * Atomic operation — no race conditions
 *
 * @param string $cam_id  Camera ID
 * @param array  $data    Camera data array
 * @return bool
 */
function mtcam_save_camera($cam_id, $data) {
    global $wpdb;
    $table = mtcam_cameras_table();

    // Fallback if table does not exist
    if (!mtcam_cameras_table_exists()) {
        $cameras = get_option('mtcam_cameras', array());
        $cameras[$cam_id] = $data;
        return update_option('mtcam_cameras', $cameras);
    }

    // Base columns in table — everything else goes into extra_data JSON
    $base_fields = array(
        'name', 'location', 'color', 'gps_lat', 'gps_lon',
        'active', 'tested', 'stolen_date', 'police_reported',
        'owner', 'owner_name', 'owner_email', 'price',
        'manufacturer', 'imei', 'deploy_date', 'purchase_date', 'created_date',
        'power_type', 'planned_days'
    );

    $base = array();
    $extra = array();

    foreach ($data as $key => $value) {
        if (in_array($key, $base_fields)) {
            $base[$key] = is_array($value) ? wp_json_encode($value) : (string) $value;
        } else {
            $extra[$key] = $value;
        }
    }

    // Defaults
    $defaults = array(
        'name' => '', 'location' => '', 'color' => MTCAM_DEFAULT_COLOR,
        'gps_lat' => '', 'gps_lon' => '', 'active' => 'yes', 'tested' => 'no',
        'stolen_date' => '', 'police_reported' => 'no', 'owner' => 'me',
        'owner_name' => '', 'owner_email' => '', 'price' => 0,
        'manufacturer' => '', 'imei' => '', 'deploy_date' => '',
        'purchase_date' => '', 'created_date' => current_time('mysql'),
        'power_type' => 'lithium', 'planned_days' => 0,
    );
    $base = array_merge($defaults, $base);

    // INSERT ... ON DUPLICATE KEY UPDATE — 1 query instead of SELECT + INSERT/UPDATE.
    // Values passed directly from $base in column order; `price` (float) and
    // `planned_days` (int) are coerced at the placeholder's type specifier level.
    $result = mtcam_db_query( $wpdb->prepare(
        'INSERT INTO `' . $wpdb->prefix . 'mtcam_cameras`
            (cam_id, name, location, color, gps_lat, gps_lon,
             active, tested, stolen_date, police_reported, owner,
             owner_name, owner_email, price, manufacturer, imei,
             deploy_date, purchase_date, created_date, power_type, planned_days, updated_at, extra_data)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %f, %s, %s, %s, %s, %s, %s, %d, %s, %s)
        ON DUPLICATE KEY UPDATE
            name = VALUES(name), location = VALUES(location), color = VALUES(color),
            gps_lat = VALUES(gps_lat), gps_lon = VALUES(gps_lon),
            active = VALUES(active), tested = VALUES(tested), stolen_date = VALUES(stolen_date),
            police_reported = VALUES(police_reported), owner = VALUES(owner),
            owner_name = VALUES(owner_name), owner_email = VALUES(owner_email),
            price = VALUES(price), manufacturer = VALUES(manufacturer), imei = VALUES(imei),
            deploy_date = VALUES(deploy_date), purchase_date = VALUES(purchase_date),
            power_type = VALUES(power_type), planned_days = VALUES(planned_days),
            updated_at = VALUES(updated_at), extra_data = VALUES(extra_data)',
        $cam_id,
        $base['name'], $base['location'], $base['color'], $base['gps_lat'], $base['gps_lon'],
        $base['active'], $base['tested'], $base['stolen_date'], $base['police_reported'], $base['owner'],
        $base['owner_name'], $base['owner_email'], floatval( $base['price'] ), $base['manufacturer'], $base['imei'],
        $base['deploy_date'], $base['purchase_date'], $base['created_date'], $base['power_type'], intval( $base['planned_days'] ),
        current_time('mysql'),
        wp_json_encode( $extra, JSON_UNESCAPED_UNICODE )
    ));
    mtcam_invalidate_cameras_cache( $cam_id );

    return $result !== false;
}

/**
 * Update specific camera fields without loading all cameras
 */
function mtcam_update_camera_fields($cam_id, $fields) {
    $cameras = mtcam_get_cameras();
    if (!isset($cameras[$cam_id])) return false;

    $cam = array_merge($cameras[$cam_id], $fields);
    return mtcam_save_camera($cam_id, $cam);
}

/**
 * Delete camera
 */
function mtcam_delete_camera($cam_id) {
    global $wpdb;
    $table = mtcam_cameras_table();
    $safe_cam_id = sanitize_text_field($cam_id);

    // ================================================================
    // 1. DETECTIONS — mtcam_detections
    // ================================================================

    // Find all log IDs for this camera (device_id = cam_id)
    $log_ids = mtcam_db_get_col( $wpdb->prepare(
        'SELECT id FROM `' . $wpdb->prefix . 'mtcam_detections` WHERE device_id = %s',
        $safe_cam_id
    ));

    if (!empty($log_ids)) {

        // Delete the detections themselves
        mtcam_db_delete( $wpdb->prefix . 'mtcam_detections', array( 'device_id' => $safe_cam_id ) );
        wp_cache_delete( 'mtcam_detection_counts' );
        wp_cache_delete( 'mtcam_admin_logs' );
    }

    // ================================================================
    // 3. LOCATION REVIEWS — camera location reviews
    // ================================================================
    mtcam_db_delete( $wpdb->prefix . 'mtcam_location_reviews', array( 'cam_id' => $safe_cam_id ) );
    wp_cache_delete( 'mtcam_location_reviews_all' );
    wp_cache_delete( 'mtcam_location_reviews_stats' );

    // ================================================================
    // 6. FILE TOKENS — trap file verification tokens
    // ================================================================
    mtcam_delete_file_tokens( $safe_cam_id );

    // ================================================================
    // 7. CAMERA — main record
    // ================================================================
    if (!mtcam_cameras_table_exists()) {
        // Fallback for old installations without custom table
        $cameras = get_option('mtcam_cameras', array());
        unset($cameras[$cam_id]);
        return update_option('mtcam_cameras', $cameras);
    }

    mtcam_db_delete( $table, array( 'cam_id' => $safe_cam_id ) );
    wp_cache_delete( 'mtcam_all_cameras' );

    // Also delete from old wp_options (to avoid ghosts after migration)
    $old_cameras = get_option('mtcam_cameras', array());
    if (isset($old_cameras[$cam_id])) {
        unset($old_cameras[$cam_id]);
        update_option('mtcam_cameras', $old_cameras);
    }

    mtcam_invalidate_cameras_cache( $cam_id );
    return true;
}

/**
 * Does camera exist?
 */
function mtcam_camera_exists($cam_id) {
    global $wpdb;
    $table = mtcam_cameras_table();

    if (!mtcam_cameras_table_exists()) {
        $cameras = get_option('mtcam_cameras', array());
        return isset($cameras[$cam_id]);
    }

    $exists_cache_key = 'mtcam_cam_exists_' . $cam_id;
    $exists = wp_cache_get( $exists_cache_key );
    if ( false === $exists ) {
        $exists = mtcam_db_get_var( $wpdb->prepare(
            'SELECT 1 FROM `' . $wpdb->prefix . 'mtcam_cameras` WHERE cam_id = %s LIMIT 1', $cam_id
        ));
        wp_cache_set( $exists_cache_key, $exists );
    }
    return (bool) $exists;
}

/**
 * Invalidate cache
 */
function mtcam_invalidate_cameras_cache( $cam_id = '' ) {
    global $mtcam_cameras_cache;
    $mtcam_cameras_cache = null;
    wp_cache_delete( 'mtcam_all_cameras' );
    if ( '' !== $cam_id ) {
        wp_cache_delete( 'mtcam_cam_exists_' . $cam_id );
    }
}

// ═══════════════════════════════════════════════════════════════════════
// FILE TOKENS — trap file verification
// ═══════════════════════════════════════════════════════════════════════

/**
 * Save file tokens for a trail camera. Deletes old tokens first.
 *
 * @param string $cam_id Camera ID.
 * @param array  $tokens Array of [ 'file_name' => 'token_hex', ... ].
 */
function mtcam_save_file_tokens( $cam_id, $tokens ) {
    global $wpdb;
    $table = $wpdb->prefix . 'mtcam_file_tokens';

    mtcam_delete_file_tokens( $cam_id );

    foreach ( $tokens as $file_name => $token ) {
        mtcam_db_insert( $table, array(
            'cam_id'     => sanitize_text_field( $cam_id ),
            'file_token' => sanitize_text_field( $token ),
            'file_name'  => sanitize_text_field( $file_name ),
        ) );
    }
}

/**
 * Verify a file token exists for a given trail camera.
 *
 * @param string $cam_id Camera ID.
 * @param string $token  File token from request.
 * @return bool True if valid.
 */
function mtcam_verify_file_token( $cam_id, $token ) {
    global $wpdb;
    $table = $wpdb->prefix . 'mtcam_file_tokens';

    $count = mtcam_db_get_var( $wpdb->prepare(
        "SELECT COUNT(*) FROM `{$table}` WHERE cam_id = %s AND file_token = %s",
        $cam_id,
        $token
    ) );

    return intval( $count ) > 0;
}

/**
 * Delete all file tokens for a trail camera.
 *
 * @param string $cam_id Camera ID.
 */
function mtcam_delete_file_tokens( $cam_id ) {
    global $wpdb;
    $table = $wpdb->prefix . 'mtcam_file_tokens';

    mtcam_db_query( $wpdb->prepare(
        "DELETE FROM `{$table}` WHERE cam_id = %s",
        sanitize_text_field( $cam_id )
    ) );
}
