-- Complete Database Setup for Student Tracking System
-- Run this file manually in MySQL to create all required tables and test data

USE sts_v2;

-- Academic data tables for dropdown options

-- Standards table
CREATE TABLE IF NOT EXISTS standards (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL,
  display_order INT DEFAULT 0,
  status ENUM('active', 'inactive') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Divisions table
CREATE TABLE IF NOT EXISTS divisions (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(10) NOT NULL,
  display_order INT DEFAULT 0,
  status ENUM('active', 'inactive') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Roll numbers table
CREATE TABLE IF NOT EXISTS roll_numbers (
  id INT PRIMARY KEY AUTO_INCREMENT,
  number INT NOT NULL,
  display_order INT DEFAULT 0,
  status ENUM('active', 'inactive') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 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 with proper foreign key relationships
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) ON DELETE CASCADE,
  FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE SET NULL,
  FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE SET NULL
);

-- Insert sample standards
INSERT IGNORE INTO standards (name, display_order) VALUES 
('1st', 1), ('2nd', 2), ('3rd', 3), ('4th', 4), ('5th', 5), ('6th', 6),
('7th', 7), ('8th', 8), ('9th', 9), ('10th', 10), ('11th', 11), ('12th', 12);

-- Insert sample divisions
INSERT IGNORE INTO divisions (name, display_order) VALUES 
('A', 1), ('B', 2), ('C', 3), ('D', 4), ('E', 5);

-- Insert roll numbers (1-50)
INSERT IGNORE INTO roll_numbers (number, display_order) VALUES 
(1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6), (7, 7), (8, 8), (9, 9), (10, 10),
(11, 11), (12, 12), (13, 13), (14, 14), (15, 15), (16, 16), (17, 17), (18, 18), (19, 19), (20, 20),
(21, 21), (22, 22), (23, 23), (24, 24), (25, 25), (26, 26), (27, 27), (28, 28), (29, 29), (30, 30),
(31, 31), (32, 32), (33, 33), (34, 34), (35, 35), (36, 36), (37, 37), (38, 38), (39, 39), (40, 40),
(41, 41), (42, 42), (43, 43), (44, 44), (45, 45), (46, 46), (47, 47), (48, 48), (49, 49), (50, 50);

-- Insert sample schools
INSERT IGNORE 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 sample devices
INSERT IGNORE INTO devices (id, type, status, battery_level) VALUES 
('DEV001', 'GPS Tracker', 'active', 85),
('DEV002', 'GPS Tracker', 'active', 92),
('DEV003', 'GPS Tracker', 'active', 78),
('DEV004', 'GPS Tracker', 'active', 91);

-- Insert sample children for parent user (ID 4 - parent@email.com)
-- Note: Make sure the parent user exists before running this
INSERT IGNORE 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');

-- Verify the setup
SELECT 'Standards Count:' as Info, COUNT(*) as Count FROM standards;
SELECT 'Divisions Count:' as Info, COUNT(*) as Count FROM divisions;
SELECT 'Roll Numbers Count:' as Info, COUNT(*) as Count FROM roll_numbers;
SELECT 'Schools Count:' as Info, COUNT(*) as Count FROM schools;
SELECT 'Devices Count:' as Info, COUNT(*) as Count FROM devices;
SELECT 'Children Count:' as Info, COUNT(*) as Count FROM children;

-- Show sample data
SELECT 'Sample Children Data:' as Info;
SELECT c.id, c.first_name, c.last_name, c.standard, c.division, c.roll_number, 
       s.name as school_name, d.id as device_id
FROM children c
LEFT JOIN schools s ON c.school_id = s.id
LEFT JOIN devices d ON c.device_id = d.id
WHERE c.parent_id = 4;