-- Update devices table to include school_id and additional fields
USE sts_v2;

-- Add school_id and other missing columns to devices table
ALTER TABLE devices 
ADD COLUMN IF NOT EXISTS school_id INT,
ADD COLUMN IF NOT EXISTS device_id VARCHAR(50),
ADD COLUMN IF NOT EXISTS imei_number VARCHAR(20),
ADD COLUMN IF NOT EXISTS sim_number VARCHAR(15),
ADD COLUMN IF NOT EXISTS model VARCHAR(50),
ADD COLUMN IF NOT EXISTS manufacturer VARCHAR(50);

-- Add foreign key constraint for school_id
ALTER TABLE devices 
ADD CONSTRAINT fk_devices_school 
FOREIGN KEY (school_id) REFERENCES users(id) ON DELETE CASCADE;

-- Update existing devices with school_id and device_id
UPDATE devices SET 
  school_id = 1,  -- Assuming school user ID is 1
  device_id = id,
  imei_number = CONCAT('IMEI', LPAD(SUBSTRING(id, 4), 12, '0')),
  sim_number = CONCAT('98765432', LPAD(SUBSTRING(id, 4), 2, '0')),
  model = 'GT06N',
  manufacturer = 'Concox'
WHERE school_id IS NULL;

-- Insert additional sample devices for school
INSERT IGNORE INTO devices (id, device_id, school_id, type, status, battery_level, imei_number, sim_number, model, manufacturer) VALUES 
('DEV005', 'DEV005', 1, 'GPS Tracker', 'active', 88, 'IMEI123456789005', '9876543205', 'GT06N', 'Concox'),
('DEV006', 'GPS Tracker', 1, 'GPS Tracker', 'active', 95, 'IMEI123456789006', '9876543206', 'GT06N', 'Concox');

-- Verify the updates
SELECT 'Updated Devices:' as Info;
SELECT * FROM devices WHERE school_id = 1;