-- Children and related tables for profile management

-- Schools table
CREATE TABLE IF NOT EXISTS schools (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  address TEXT,
  phone VARCHAR(15),
  email VARCHAR(100),
  status ENUM('active', 'inactive') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Devices table
CREATE TABLE IF NOT EXISTS devices (
  id VARCHAR(20) PRIMARY KEY,
  type VARCHAR(50) DEFAULT 'GPS Tracker',
  status ENUM('active', 'inactive', 'maintenance') DEFAULT 'active',
  battery_level INT DEFAULT 85,
  last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Children table
CREATE TABLE IF NOT EXISTS children (
  id INT PRIMARY KEY AUTO_INCREMENT,
  parent_id INT NOT NULL,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  standard VARCHAR(10),
  division VARCHAR(5),
  roll_number INT,
  school_id INT,
  device_id VARCHAR(20),
  status ENUM('active', 'inactive') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (parent_id) REFERENCES users(id),
  FOREIGN KEY (school_id) REFERENCES schools(id),
  FOREIGN KEY (device_id) REFERENCES devices(id)
);

-- Insert sample data
INSERT INTO schools (name, address, phone, email) VALUES 
('Green Valley Elementary', '123 School St, City', '555-0123', 'info@greenvalley.edu'),
('Sunrise High School', '456 Education Ave, City', '555-0124', 'contact@sunrise.edu');

INSERT INTO devices (id, type, status, battery_level) VALUES 
('DEV001', 'GPS Tracker', 'active', 85),
('DEV002', 'GPS Tracker', 'active', 92);

-- Insert sample children (assuming parent user ID 4 exists)
INSERT INTO children (parent_id, first_name, last_name, standard, division, roll_number, school_id, device_id) VALUES 
(4, 'Emma', 'Johnson', '5th', 'A', 15, 1, 'DEV001'),
(4, 'Alex', 'Johnson', '3rd', 'B', 22, 1, 'DEV002');