-- Fix database structure
USE sts_v2;

-- Drop existing tables if they exist

-- Create unified users table
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  user_type ENUM('admin', 'school', 'parent') NOT NULL,
  phone VARCHAR(20),
  address TEXT,
  last_login TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insert test users
INSERT INTO users (first_name, last_name, email, password_hash, user_type, phone, address) VALUES 
('Admin', 'User', 'admin@gps.com', '$2a$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin', '555-0100', 'Admin Office'),
('Green Valley', 'School', 'school@greenvalley.com', '$2a$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'school', '555-0101', '123 School St'),
('John', 'Parent', 'parent@example.com', '$2a$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'parent', '555-0102', '456 Parent Ave');

-- Verify data
SELECT id, first_name, last_name, email, user_type FROM users;