-- Restructured Database - Combine devices with children details
-- Run this to restructure the existing database

USE sts_v2;

-- Drop existing foreign key constraints
ALTER TABLE children DROP FOREIGN KEY children_ibfk_3;
ALTER TABLE device_subscriptions DROP FOREIGN KEY device_subscriptions_ibfk_1;

-- Drop separate devices table
DROP TABLE IF EXISTS devices;

-- Update children table to include device details
ALTER TABLE children 
ADD COLUMN device_type VARCHAR(50) DEFAULT 'GPS Tracker',
ADD COLUMN device_status ENUM('active', 'inactive', 'maintenance') DEFAULT 'active',
ADD COLUMN battery_level INT DEFAULT 85,
ADD COLUMN last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ADD COLUMN device_created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

-- Update device_id to be auto-generated based on child ID
UPDATE children SET device_id = CONCAT('DEV', LPAD(id, 3, '0')) WHERE device_id IS NULL OR device_id = '';

-- Make device_id unique and not null
ALTER TABLE children 
MODIFY COLUMN device_id VARCHAR(20) NOT NULL UNIQUE;

-- Update device_subscriptions to reference children.device_id
ALTER TABLE device_subscriptions 
ADD CONSTRAINT fk_device_subscriptions_device 
FOREIGN KEY (device_id) REFERENCES children(device_id) ON DELETE CASCADE;

-- Update sample children data with device details
UPDATE children SET 
  device_type = 'GPS Tracker',
  device_status = 'active',
  battery_level = CASE 
    WHEN id = 1 THEN 85
    WHEN id = 2 THEN 92
    ELSE 80
  END,
  last_update = NOW(),
  device_created_at = NOW()
WHERE parent_id = 4;

-- Verify the restructured data
SELECT 'Updated Children with Device Details:' as Info;
SELECT c.id, c.first_name, c.last_name, c.device_id, c.device_type, 
       c.device_status, c.battery_level, c.standard, c.division, c.roll_number,
       s.name as school_name
FROM children c
LEFT JOIN schools s ON c.school_id = s.id
WHERE c.parent_id = 4;