CREATE DATABASE IF NOT EXISTS travel_performa;
USE travel_performa;

CREATE TABLE `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `employee_id` VARCHAR(50) UNIQUE NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(150) UNIQUE NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `designation` VARCHAR(100),
  `mobile` VARCHAR(20),
  `role` ENUM('admin','user') DEFAULT 'user',
  `status` ENUM('active','inactive') DEFAULT 'active',
  `dob` DATE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `api_tokens` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `token_hash` VARCHAR(255) UNIQUE NOT NULL,
  `device_info` VARCHAR(255),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `expires_at` TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `districts` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `status` ENUM('active','inactive') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_districts` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `district_id` INT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `unique_user_district` (`user_id`, `district_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`district_id`) REFERENCES `districts`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `blocks` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `district_id` INT NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `status` ENUM('active','inactive') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`district_id`) REFERENCES `districts`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `schools` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `district_id` INT NOT NULL,
  `block_id` INT NOT NULL,
  `name` VARCHAR(150) NOT NULL,
  `principal_name` VARCHAR(100),
  `contact_number` VARCHAR(20),
  `address` TEXT,
  `status` ENUM('active','inactive') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`district_id`) REFERENCES `districts`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`block_id`) REFERENCES `blocks`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `assigned_students` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `father_name` VARCHAR(100),
  `father_occupation` VARCHAR(100),
  `school_name` VARCHAR(150),
  `class` VARCHAR(50),
  `district_id` INT,
  `block_id` INT,
  `address` TEXT,
  `phone` VARCHAR(20),
  `status` ENUM('assigned','completed') DEFAULT 'assigned',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by_user_at` TIMESTAMP NULL,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`district_id`) REFERENCES `districts`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`block_id`) REFERENCES `blocks`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `attendance` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `attendance_date` DATE NOT NULL,
  `check_in` DATETIME,
  `check_in_image` VARCHAR(255),
  `check_in_lat` DECIMAL(10,8),
  `check_in_lng` DECIMAL(11,8),
  `check_in_accuracy` FLOAT,
  `check_in_address` TEXT,
  `check_out` DATETIME,
  `check_out_image` VARCHAR(255),
  `check_out_lat` DECIMAL(10,8),
  `check_out_lng` DECIMAL(11,8),
  `check_out_accuracy` FLOAT,
  `check_out_address` TEXT,
  `status` ENUM('Checked In','Checked Out') NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `unique_user_date` (`user_id`, `attendance_date`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `tracking_sessions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `attendance_id` INT NOT NULL,
  `tracking_date` DATE NOT NULL,
  `started_at` DATETIME NOT NULL,
  `ended_at` DATETIME,
  `start_lat` DECIMAL(10,8),
  `start_lng` DECIMAL(11,8),
  `start_accuracy` FLOAT,
  `end_lat` DECIMAL(10,8),
  `end_lng` DECIMAL(11,8),
  `end_accuracy` FLOAT,
  `raw_distance_km` FLOAT DEFAULT 0,
  `filtered_distance_km` FLOAT DEFAULT 0,
  `total_points` INT DEFAULT 0,
  `valid_points` INT DEFAULT 0,
  `rejected_points` INT DEFAULT 0,
  `tracking_quality_percentage` FLOAT,
  `status` ENUM('active','completed','interrupted','force_stopped') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`attendance_id`) REFERENCES `attendance`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `location_logs` (
  `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
  `client_point_id` VARCHAR(100) UNIQUE NOT NULL,
  `user_id` INT NOT NULL,
  `tracking_session_id` INT NOT NULL,
  `latitude` DECIMAL(10,8) NOT NULL,
  `longitude` DECIMAL(11,8) NOT NULL,
  `accuracy` FLOAT,
  `altitude` FLOAT,
  `speed` FLOAT,
  `bearing` FLOAT,
  `location_type` ENUM('check_in','tracking','performa_submission','check_out') DEFAULT 'tracking',
  `recorded_at` DATETIME NOT NULL,
  `received_at` DATETIME NOT NULL,
  `is_valid` TINYINT(1) DEFAULT 1,
  `rejection_reason` VARCHAR(255),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`tracking_session_id`) REFERENCES `tracking_sessions`(`id`) ON DELETE CASCADE,
  INDEX `idx_user_session_time` (`user_id`, `tracking_session_id`, `recorded_at`),
  INDEX `idx_validity` (`is_valid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `detected_stops` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `tracking_session_id` INT NOT NULL,
  `start_time` DATETIME NOT NULL,
  `end_time` DATETIME,
  `duration_minutes` INT,
  `center_lat` DECIMAL(10,8),
  `center_lng` DECIMAL(11,8),
  `radius_meters` FLOAT,
  `nearest_performa_id` INT,
  `address` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`tracking_session_id`) REFERENCES `tracking_sessions`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `tracking_events` (
  `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `tracking_session_id` INT NOT NULL,
  `event_type` VARCHAR(50) NOT NULL,
  `event_time` DATETIME NOT NULL,
  `details` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`tracking_session_id`) REFERENCES `tracking_sessions`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `performas` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `tracking_session_id` INT,
  `visit_date` DATE NOT NULL,
  `district_id` INT,
  `block_id` INT,
  `destination_type` ENUM('school','institution','home_visit') NOT NULL,
  `school_name` VARCHAR(150),
  `poc` VARCHAR(100),
  `designation` VARCHAR(100),
  `mobile` VARCHAR(20),
  `student_name` VARCHAR(100),
  `parent_name` VARCHAR(100),
  `relation` VARCHAR(50),
  `student_class` VARCHAR(50),
  `father_occupation` VARCHAR(100),
  `output` ENUM('accepted','not_accepted','revisit') NOT NULL,
  `not_accepted_reason` TEXT,
  `revisit_date` DATE,
  `test_date` DATE,
  `latitude` DECIMAL(10,8),
  `longitude` DECIMAL(11,8),
  `location_accuracy` FLOAT,
  `location_address` TEXT,
  `assigned_student_id` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`tracking_session_id`) REFERENCES `tracking_sessions`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`district_id`) REFERENCES `districts`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`block_id`) REFERENCES `blocks`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`assigned_student_id`) REFERENCES `assigned_students`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert a default admin user (password is 'password123' using BCRYPT)
INSERT INTO `users` (`employee_id`, `name`, `email`, `password`, `role`) VALUES 
('ADMIN-001', 'System Admin', 'admin@travelperforma.local', '$2y$10$D6jTQUGnvv7GsJaJAQGRje3hBHV9gXn8SBEIgoErMt8DNcf7vQ2.u', 'admin');