-- Enhanced Database Schema for GPS Tracking Business Requirements
-- This addresses the missing tables and fields for the complete business flow

USE sts_v2;

-- 1. Enhanced Users table with proper role management
ALTER TABLE users ADD COLUMN IF NOT EXISTS company_id INT DEFAULT NULL;
ALTER TABLE users ADD COLUMN IF NOT EXISTS permissions JSON DEFAULT NULL;
ALTER TABLE users ADD COLUMN IF NOT EXISTS subscription_status ENUM('active', 'expired', 'pending') DEFAULT 'pending';

-- 2. Companies table (for Super Admin management)
CREATE TABLE IF NOT EXISTS companies (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  contact_person VARCHAR(100),
  email VARCHAR(100),
  phone VARCHAR(15),
  address TEXT,
  subscription_plan ENUM('basic', 'premium', 'enterprise') DEFAULT 'basic',
  status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 3. Enhanced Schools table with subscription management
ALTER TABLE schools ADD COLUMN IF NOT EXISTS company_id INT DEFAULT 1;
ALTER TABLE schools ADD COLUMN IF NOT EXISTS subscription_plan_id INT DEFAULT NULL;
ALTER TABLE schools ADD COLUMN IF NOT EXISTS subscription_start_date DATE DEFAULT NULL;
ALTER TABLE schools ADD COLUMN IF NOT EXISTS subscription_end_date DATE DEFAULT NULL;
ALTER TABLE schools ADD COLUMN IF NOT EXISTS total_students INT DEFAULT 0;
ALTER TABLE schools ADD COLUMN IF NOT EXISTS active_devices INT DEFAULT 0;
ALTER TABLE schools ADD COLUMN IF NOT EXISTS contact_person VARCHAR(100);

-- 4. Subscription Plans table
CREATE TABLE IF NOT EXISTS subscription_plans (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  price_per_device DECIMAL(10,2) NOT NULL,
  billing_cycle ENUM('monthly', 'quarterly', 'annual') DEFAULT 'monthly',
  features JSON DEFAULT NULL,
  status ENUM('active', 'inactive') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 5. Enhanced Devices table with inventory management
ALTER TABLE devices ADD COLUMN IF NOT EXISTS school_id INT DEFAULT NULL;
ALTER TABLE devices ADD COLUMN IF NOT EXISTS imei_number VARCHAR(20) UNIQUE DEFAULT NULL;
ALTER TABLE devices ADD COLUMN IF NOT EXISTS sim_number VARCHAR(20) DEFAULT NULL;
ALTER TABLE devices ADD COLUMN IF NOT EXISTS model VARCHAR(50) DEFAULT NULL;
ALTER TABLE devices ADD COLUMN IF NOT EXISTS manufacturer VARCHAR(50) DEFAULT NULL;
ALTER TABLE devices ADD COLUMN IF NOT EXISTS purchase_date DATE DEFAULT NULL;
ALTER TABLE devices ADD COLUMN IF NOT EXISTS warranty_expiry DATE DEFAULT NULL;
ALTER TABLE devices ADD COLUMN IF NOT EXISTS activation_date TIMESTAMP DEFAULT NULL;
ALTER TABLE devices ADD COLUMN IF NOT EXISTS deactivation_date TIMESTAMP DEFAULT NULL;

-- 6. Device Assignment History
CREATE TABLE IF NOT EXISTS device_assignments (
  id INT PRIMARY KEY AUTO_INCREMENT,
  device_id VARCHAR(20) NOT NULL,
  student_id INT NOT NULL,
  school_id INT NOT NULL,
  assigned_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  unassigned_date TIMESTAMP DEFAULT NULL,
  status ENUM('active', 'inactive') DEFAULT 'active',
  assigned_by INT NOT NULL,
  FOREIGN KEY (device_id) REFERENCES devices(id),
  FOREIGN KEY (student_id) REFERENCES children(id),
  FOREIGN KEY (school_id) REFERENCES schools(id),
  FOREIGN KEY (assigned_by) REFERENCES users(id)
);

-- 7. Parent Management
CREATE TABLE IF NOT EXISTS parent_profiles (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  phone_number VARCHAR(15) UNIQUE NOT NULL,
  otp_code VARCHAR(6) DEFAULT NULL,
  otp_expiry TIMESTAMP DEFAULT NULL,
  is_phone_verified BOOLEAN DEFAULT FALSE,
  emergency_contact VARCHAR(15) DEFAULT NULL,
  relationship_to_child ENUM('father', 'mother', 'guardian') DEFAULT 'father',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 8. Geofencing
CREATE TABLE IF NOT EXISTS geofences (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  type ENUM('school', 'home', 'safe_zone', 'restricted') DEFAULT 'safe_zone',
  center_latitude DECIMAL(10, 8) NOT NULL,
  center_longitude DECIMAL(11, 8) NOT NULL,
  radius_meters INT NOT NULL,
  school_id INT DEFAULT NULL,
  student_id INT DEFAULT NULL,
  status ENUM('active', 'inactive') DEFAULT 'active',
  created_by INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (school_id) REFERENCES schools(id),
  FOREIGN KEY (student_id) REFERENCES children(id),
  FOREIGN KEY (created_by) REFERENCES users(id)
);

-- 9. Alerts and Notifications
CREATE TABLE IF NOT EXISTS alerts (
  id INT PRIMARY KEY AUTO_INCREMENT,
  type ENUM('geofence_entry', 'geofence_exit', 'sos', 'low_battery', 'device_offline', 'speed_limit') NOT NULL,
  student_id INT NOT NULL,
  device_id VARCHAR(20) NOT NULL,
  message TEXT NOT NULL,
  latitude DECIMAL(10, 8) DEFAULT NULL,
  longitude DECIMAL(11, 8) DEFAULT NULL,
  is_acknowledged BOOLEAN DEFAULT FALSE,
  acknowledged_by INT DEFAULT NULL,
  acknowledged_at TIMESTAMP DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (student_id) REFERENCES children(id),
  FOREIGN KEY (device_id) REFERENCES devices(id),
  FOREIGN KEY (acknowledged_by) REFERENCES users(id)
);

-- 10. Payment Management
CREATE TABLE IF NOT EXISTS payments (
  id INT PRIMARY KEY AUTO_INCREMENT,
  school_id INT NOT NULL,
  subscription_plan_id INT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  payment_method ENUM('cash', 'bank_transfer', 'online', 'cheque') DEFAULT 'cash',
  payment_status ENUM('pending', 'completed', 'failed', 'refunded') DEFAULT 'pending',
  payment_date DATE NOT NULL,
  billing_period_start DATE NOT NULL,
  billing_period_end DATE NOT NULL,
  collected_by INT NOT NULL,
  receipt_number VARCHAR(50) UNIQUE DEFAULT NULL,
  notes TEXT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (school_id) REFERENCES schools(id),
  FOREIGN KEY (subscription_plan_id) REFERENCES subscription_plans(id),
  FOREIGN KEY (collected_by) REFERENCES users(id)
);

-- 11. Support Tickets
CREATE TABLE IF NOT EXISTS support_tickets (
  id INT PRIMARY KEY AUTO_INCREMENT,
  ticket_number VARCHAR(20) UNIQUE NOT NULL,
  type ENUM('device_replacement', 'technical_issue', 'billing_query', 'feature_request', 'complaint') NOT NULL,
  priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
  status ENUM('open', 'in_progress', 'resolved', 'closed') DEFAULT 'open',
  school_id INT DEFAULT NULL,
  student_id INT DEFAULT NULL,
  device_id VARCHAR(20) DEFAULT NULL,
  subject VARCHAR(200) NOT NULL,
  description TEXT NOT NULL,
  created_by INT NOT NULL,
  assigned_to INT DEFAULT NULL,
  resolved_at TIMESTAMP DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (school_id) REFERENCES schools(id),
  FOREIGN KEY (student_id) REFERENCES children(id),
  FOREIGN KEY (device_id) REFERENCES devices(id),
  FOREIGN KEY (created_by) REFERENCES users(id),
  FOREIGN KEY (assigned_to) REFERENCES users(id)
);

-- Insert sample data for enhanced schema
INSERT IGNORE INTO companies (name, contact_person, email, phone) VALUES 
('GPS Tracking Solutions Ltd', 'John Admin', 'admin@gpstracking.com', '9999999999');

INSERT IGNORE INTO subscription_plans (name, price_per_device, billing_cycle, features) VALUES 
('Basic Plan', 500.00, 'monthly', '{"live_tracking": true, "geofencing": true, "alerts": true}'),
('Premium Plan', 800.00, 'monthly', '{"live_tracking": true, "geofencing": true, "alerts": true, "history": true, "reports": true}'),
('Enterprise Plan', 1200.00, 'monthly', '{"live_tracking": true, "geofencing": true, "alerts": true, "history": true, "reports": true, "api_access": true, "priority_support": true}');

-- Update existing schools with subscription info
UPDATE schools SET 
  company_id = 1,
  subscription_plan_id = 2,
  subscription_start_date = CURDATE(),
  subscription_end_date = DATE_ADD(CURDATE(), INTERVAL 1 YEAR),
  contact_person = 'School Admin'
WHERE id IN (1, 2);

-- Add foreign key constraints
ALTER TABLE schools ADD FOREIGN KEY (company_id) REFERENCES companies(id);
ALTER TABLE schools ADD FOREIGN KEY (subscription_plan_id) REFERENCES subscription_plans(id);
ALTER TABLE devices ADD FOREIGN KEY (school_id) REFERENCES schools(id);