<?php
/**
 * Database Installation and Migration
 * * Table creation, migration from wp_options, default settings.
 * * @package My Trail Camera
 * @since 8.1.0
 */

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

/**
 * Migrate old table names and options from pre-8.3 prefixes (thor_, sd_tracker_) to mtcam_.
 * Runs once on upgrade — safe to call multiple times.
 */
function mtcam_migrate_prefixes() {
    if (get_option('mtcam_prefixes_migrated')) return;
    global $wpdb;

    // Rename tables: old name => new name (without prefix)
    $table_renames = array(
        'sd_tracker'            => 'mtcam_detections',
        'thor_cameras'          => 'mtcam_cameras',
        'thor_timeline'         => 'mtcam_timeline',
        'thor_location_reviews' => 'mtcam_location_reviews',
    );
    foreach ($table_renames as $old_suffix => $new_suffix) {
        $old_table = $wpdb->prefix . $old_suffix;
        $new_table = $wpdb->prefix . $new_suffix;
        // Only rename if old exists and new does not
        $old_exists = mtcam_db_get_var($wpdb->prepare('SHOW TABLES LIKE %s', $old_table));
        $new_exists = mtcam_db_get_var($wpdb->prepare('SHOW TABLES LIKE %s', $new_table));
        if ($old_exists && !$new_exists) {
            mtcam_db_query('RENAME TABLE `' . esc_sql($old_table) . '` TO `' . esc_sql($new_table) . '`');
        }
    }

    // Rename options: old prefix => new prefix
    $option_prefixes = array('sd_tracker_' => 'mtcam_', 'thor_' => 'mtcam_');
    foreach ($option_prefixes as $old_prefix => $new_prefix) {
        $old_options = mtcam_db_get_results(
            $wpdb->prepare(
                "SELECT option_name, option_value FROM {$wpdb->options} WHERE option_name LIKE %s",
                $wpdb->esc_like($old_prefix) . '%'
            )
        );
        foreach ($old_options as $opt) {
            $new_name = $new_prefix . substr($opt->option_name, strlen($old_prefix));
            if (get_option($new_name) === false) {
                update_option($new_name, maybe_unserialize($opt->option_value));
            }
            delete_option($opt->option_name);
        }
    }

    // Rename transients with old prefixes
    $transient_prefixes = array('thor_', 'mtc_');
    foreach ($transient_prefixes as $old_tp) {
        $old_transients = mtcam_db_get_col(
            $wpdb->prepare(
                "SELECT option_name FROM {$wpdb->options} WHERE option_name LIKE %s",
                '_transient_' . $wpdb->esc_like($old_tp) . '%'
            )
        );
        foreach ($old_transients as $t) {
            delete_transient(str_replace('_transient_', '', $t));
        }
    }

    // Rename upload directories
    $upload_dir = wp_upload_dir();
    $dir_renames = array('thor_cameras' => 'mtcam_cameras', 'thor_temp' => 'mtcam_temp', 'thor-tmp' => 'mtcam-tmp');
    foreach ($dir_renames as $old_dir => $new_dir) {
        $old_path = $upload_dir['basedir'] . '/' . $old_dir;
        $new_path = $upload_dir['basedir'] . '/' . $new_dir;
        if (is_dir($old_path) && !is_dir($new_path)) {
            rename($old_path, $new_path);
        }
    }

    update_option('mtcam_prefixes_migrated', true);
}

/**
 * Create/update all database tables and set defaults
 * 
 * @param string $table_name   Main logs table (wp_mtcam_detections)
 * @param string $extra_table  Extra table name (optional).
 * @param string $version      Current plugin version
 */
function mtcam_install($table_name, $extra_table, $version) {
    global $wpdb;
    
    // Main logs table
    $sql = "CREATE TABLE {$table_name} (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        time datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
        ip varchar(100) DEFAULT '' NOT NULL,
        lat varchar(50) DEFAULT '?' NOT NULL,
        lon varchar(50) DEFAULT '?' NOT NULL,
        acc varchar(50) DEFAULT '?' NOT NULL,
        device_id varchar(50) DEFAULT '?' NOT NULL,
        file_name varchar(255) DEFAULT '?' NOT NULL,
        file_type varchar(10) DEFAULT '?' NOT NULL,
        sys_user varchar(100) DEFAULT '?' NOT NULL,
        os_info varchar(255) DEFAULT '?' NOT NULL,
        ram_info varchar(50) DEFAULT '?' NOT NULL,
        screen_res varchar(50) DEFAULT '?' NOT NULL,
        timezone varchar(50) DEFAULT '?' NOT NULL,
        cpu_cores varchar(20) DEFAULT '?' NOT NULL,
        isp_info varchar(255) DEFAULT '?' NOT NULL,
        ua text NOT NULL,
        extra_data text NOT NULL,
        PRIMARY KEY (id),
        KEY time (time),
        KEY device_id (device_id),
        KEY ip (ip)
    ) {$wpdb->get_charset_collate()};";
    

    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    dbDelta($sql);
    
    // Cameras table
    $cameras_table = mtcam_cameras_table();
    $sql_cameras = "CREATE TABLE {$cameras_table} (
        cam_id varchar(50) NOT NULL,
        name varchar(255) DEFAULT '' NOT NULL,
        location varchar(255) DEFAULT '' NOT NULL,
        color varchar(20) DEFAULT '#9C27B0' NOT NULL,
        gps_lat varchar(50) DEFAULT '' NOT NULL,
        gps_lon varchar(50) DEFAULT '' NOT NULL,
        active varchar(5) DEFAULT 'yes' NOT NULL,
        tested varchar(5) DEFAULT 'no' NOT NULL,
        stolen_date varchar(20) DEFAULT '' NOT NULL,
        police_reported varchar(5) DEFAULT 'no' NOT NULL,
        owner varchar(20) DEFAULT 'me' NOT NULL,
        owner_name varchar(255) DEFAULT '' NOT NULL,
        owner_email varchar(255) DEFAULT '' NOT NULL,
        price decimal(10,2) DEFAULT 0 NOT NULL,
        manufacturer varchar(255) DEFAULT '' NOT NULL,
        imei varchar(100) DEFAULT '' NOT NULL,
        serial_number varchar(100) DEFAULT '' NOT NULL,
        deploy_date varchar(20) DEFAULT '' NOT NULL,
        purchase_date varchar(20) DEFAULT '' NOT NULL,
        created_date datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
        updated_at datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
        power_type varchar(20) DEFAULT 'lithium' NOT NULL,
        planned_days int(11) DEFAULT 0 NOT NULL,
        extra_data longtext,
        PRIMARY KEY (cam_id),
        KEY active (active),
        KEY owner_email (owner_email),
        KEY stolen_date (stolen_date)
    ) {$wpdb->get_charset_collate()};";
    dbDelta($sql_cameras);

    // Mark table as created (used by mtcam_cameras_table_exists)
    update_option('mtcam_cameras_table_created', true);
    global $mtcam_cameras_table_exists;
    $mtcam_cameras_table_exists = true;
    
    // Migrate from wp_options if needed
    mtcam_migrate_cameras_from_options();
    
    
    // File tokens table (trap file verification)
    $tokens_table = $wpdb->prefix . 'mtcam_file_tokens';
    $sql_tokens = "CREATE TABLE {$tokens_table} (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        cam_id varchar(50) NOT NULL,
        file_token varchar(64) NOT NULL,
        file_name varchar(255) NOT NULL,
        created_at datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
        PRIMARY KEY (id),
        UNIQUE KEY file_token (file_token),
        KEY cam_id (cam_id)
    ) {$wpdb->get_charset_collate()};";
    dbDelta($sql_tokens);

    // Location reviews table
    if (function_exists('mtcam_locations_install')) {
        mtcam_locations_install();
    }
    
    // Defaults
    $defaults = array(
        'mtcam_notifications'     => 'yes',
        'mtcam_notify_email'      => get_option('admin_email'),
        'mtcam_email_custom_text' => 'MTC security system detected unauthorized access!',
        'mtcam_show_ip'           => 'yes',
        'mtcam_show_photo'        => 'yes',
        'mtcam_auto_report'       => 'no',
        'mtcam_data_retention'    => 90,
        'mtcam_gps_required'      => 'no',
        'mtcam_theft_alert_radius'      => '1',
        'mtcam_auto_mark_stolen'  => 'yes',
        'mtcam_show_battery_stolen' => 'yes',
    );
    
    foreach ($defaults as $key => $value) {
        if (get_option($key) === false) {
            update_option($key, $value);
        }
    }
    
    update_option('mtcam_version', $version);

    // Run the accessory-dedup migration after all other setup.
    mtcam_migrate_accessory_dedup();

    // Alkaline batteries removed — migrate existing cameras to lithium.
    mtcam_migrate_alkaline_to_lithium();

    // Battery presets introduced — backfill existing cameras with a sensible default.
    mtcam_migrate_battery_presets();
}

/**
 * One-time migration: alkaline batteries are no longer supported —
 * any camera with power_type = 'alkaline' is switched to 'lithium'.
 *
 * Idempotent — guarded by the `mtcam_alkaline_migrated_v1` option.
 */
function mtcam_migrate_alkaline_to_lithium() {
    if ( get_option( 'mtcam_alkaline_migrated_v1', false ) ) {
        return;
    }

    global $wpdb;
    $cameras_table = mtcam_cameras_table();
    mtcam_db_query( $wpdb->prepare(
        "UPDATE {$cameras_table} SET power_type = %s WHERE power_type = %s",
        'lithium',
        'alkaline'
    ) );

    update_option( 'mtcam_alkaline_migrated_v1', true );
}

/**
 * One-time migration: populate battery_preset + cell_count for existing cameras.
 *
 * Heuristic (user confirmed cell_count is always manual — this is only a best-guess
 * default derived from the profile's battery_type string, e.g. "8× AA" → 8):
 *  - Parse leading integer from profile['battery_type'] as cell count.
 *  - AA/AAA battery_type → generic_primary_lithium_aa / _aaa preset.
 *  - Built-in packs → internal_lipo_1s preset with cell_count = 1.
 *  - Unknown → leave preset empty and cell_count = 0 (code falls back to profile capacity).
 *
 * Idempotent — guarded by the `mtcam_battery_presets_migrated_v1` option.
 */
function mtcam_migrate_battery_presets() {
    if ( get_option( 'mtcam_battery_presets_migrated_v1', false ) ) {
        return;
    }

    if ( ! function_exists( 'mtcam_get_cameras' ) || ! function_exists( 'mtcam_save_camera' ) ) {
        return;
    }

    $cameras = mtcam_get_cameras();
    foreach ( $cameras as $cam_id => $cam ) {
        if ( ! empty( $cam['battery_preset'] ) ) {
            continue;
        }

        $profile = function_exists( 'mtcam_get_camera_profile' ) ? mtcam_get_camera_profile( $cam['profile'] ?? 'custom' ) : array();
        $btype   = strtolower( $profile['battery_type'] ?? '' );

        $preset_id  = '';
        $cell_count = 0;

        if ( preg_match( '/^\s*(\d+)/', $btype, $m ) ) {
            $cell_count = intval( $m[1] );
        }

        if ( strpos( $btype, 'aaa' ) !== false ) {
            $preset_id = 'generic_primary_lithium_aaa';
        } elseif ( strpos( $btype, 'aa' ) !== false ) {
            $preset_id = 'generic_primary_lithium_aa';
        } elseif ( strpos( $btype, 'built' ) !== false || strpos( $btype, 'lipo' ) !== false || strpos( $btype, 'mah' ) !== false ) {
            $preset_id = 'internal_lipo_1s';
            if ( $cell_count === 0 ) {
                $cell_count = 1;
            }
        }

        if ( $preset_id === '' && $cell_count === 0 ) {
            continue;
        }

        $cam['battery_preset'] = $preset_id;
        $cam['cell_count']     = $cell_count;
        mtcam_save_camera( $cam_id, $cam );
    }

    update_option( 'mtcam_battery_presets_migrated_v1', true );
}

/**
 * One-time migration: the old "Price & Accessories" column carried acc_lock /
 * acc_metalbox / acc_gpstrack that duplicated security + GPS-tracker entries
 * once the lists were merged. Copy any ticked acc_* entries (and their price)
 * into the canonical sec_* / gps_other fields, then clear the legacy keys.
 *
 * Idempotent — guarded by the `mtcam_acc_dedup_migrated_v1` option.
 */
function mtcam_migrate_accessory_dedup() {
    if ( get_option( 'mtcam_acc_dedup_migrated_v1', false ) ) {
        return;
    }

    $map = array(
        // legacy key      => array( target_checkbox, target_price_key )
        'acc_lock'     => array( 'sec_padlock', 'sec_padlock_price' ),
        'acc_metalbox' => array( 'sec_box',     'sec_box_price' ),
        'acc_gpstrack' => array( 'gps_other',   'gps_other_price' ),
    );

    $cameras = mtcam_get_cameras();
    foreach ( $cameras as $cam_id => $cam ) {
        $changed = false;

        foreach ( $map as $legacy => $target ) {
            list( $target_flag, $target_price ) = $target;

            if ( ( $cam[ $legacy ] ?? 'no' ) === 'yes' ) {
                // Tick the equivalent security/GPS flag.
                $cam[ $target_flag ] = 'yes';

                // Keep the higher of the two prices (never silently lower an existing value).
                $legacy_price = floatval( $cam[ $legacy . '_price' ] ?? 0 );
                $existing     = floatval( $cam[ $target_price ] ?? 0 );
                $cam[ $target_price ] = max( $existing, $legacy_price );

                $changed = true;
            }

            // Always drop the legacy keys — the UI no longer shows them.
            if ( isset( $cam[ $legacy ] ) )             { unset( $cam[ $legacy ] ); $changed = true; }
            if ( isset( $cam[ $legacy . '_price' ] ) )  { unset( $cam[ $legacy . '_price' ] ); $changed = true; }
        }

        if ( $changed ) {
            mtcam_save_camera( $cam_id, $cam );
        }
    }

    update_option( 'mtcam_acc_dedup_migrated_v1', true );
}

/**
 * One-time migration from wp_options to mtcam_cameras table
 */
function mtcam_migrate_cameras_from_options() {
    if (get_option('mtcam_cameras_remigrated_v2', false)) return;
    
    global $wpdb;
    $table = mtcam_cameras_table();
    
    $old_cameras = get_option('mtcam_cameras', array());
    if (empty($old_cameras)) {
        update_option('mtcam_cameras_migrated', true);
        update_option('mtcam_cameras_remigrated_v2', true);
        return;
    }
    
    mtcam_db_query( 'TRUNCATE TABLE `' . $wpdb->prefix . 'mtcam_cameras`' );
    wp_cache_delete( 'mtcam_all_cameras' );
    
    $migrated = 0;
    foreach ($old_cameras as $cam_id => $cam_data) {
        if (mtcam_save_camera($cam_id, $cam_data)) {
            $migrated++;
        }
    }
    
    update_option('mtcam_cameras_migrated', true);
    update_option('mtcam_cameras_remigrated_v2', true);
}
