-- First, rename the existing table to backup
RENAME TABLE active_miners TO active_miners_backup;

-- Create the new table with all required columns
CREATE TABLE active_miners (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    hwid VARCHAR(255) NOT NULL,
    machine_id VARCHAR(255) NOT NULL,
    processor_id VARCHAR(255) NOT NULL,
    bios_id VARCHAR(255) NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    algorithm VARCHAR(50) NOT NULL,
    worker_name VARCHAR(255) NOT NULL,
    hashrate FLOAT DEFAULT 0,
    shares INT DEFAULT 0,
    valid_shares INT DEFAULT 0,
    invalid_shares INT DEFAULT 0,
    uptime FLOAT DEFAULT 0,
    last_seen DATETIME NOT NULL,
    status VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_username (username),
    INDEX idx_last_seen (last_seen)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Migrate data from backup table to new table
INSERT INTO active_miners (
    id, username, hwid, machine_id, processor_id, bios_id, 
    ip_address, algorithm, worker_name, hashrate, shares,
    valid_shares, invalid_shares, uptime, last_seen, status, created_at
)
SELECT 
    id, username, machine_id as hwid, machine_id, 
    SUBSTRING_INDEX(machine_id, '-', 1) as processor_id,
    SUBSTRING_INDEX(machine_id, '-', -1) as bios_id,
    ip_address, algorithm, worker_name, hashrate, 
    valid_shares as shares, valid_shares, invalid_shares,
    uptime, last_seen, status, created_at
FROM active_miners_backup;

-- Drop the backup table after successful migration
DROP TABLE IF EXISTS active_miners_backup; 