-- backend/database.sql
-- Use this file to import the database schema into MySQL / MariaDB

CREATE DATABASE IF NOT EXISTS attendance_db;
USE attendance_db;

CREATE TABLE IF NOT EXISTS `batches` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `batch_name` VARCHAR(100) NOT NULL,
  `hospital_lat` DOUBLE NOT NULL,
  `hospital_lng` DOUBLE NOT NULL,
  `radius_meters` INT(11) DEFAULT 500,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `users` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(50) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `role` VARCHAR(50) DEFAULT 'Student',
  `batch_id` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`batch_id`) REFERENCES `batches`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `location_logs` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `latitude` DOUBLE DEFAULT NULL,
  `longitude` DOUBLE DEFAULT NULL,
  `altitude` DOUBLE DEFAULT NULL,
  `accuracy` DOUBLE DEFAULT NULL,
  `timestamp_recorded` BIGINT(20) DEFAULT NULL,
  `timestamp_synced` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert a demo account with password 'demo'
-- Note: Re-hash this password when managing your own system or let the PHP script auto-generate it.
INSERT IGNORE INTO `users` (`username`, `password_hash`, `name`, `role`) VALUES
('demo', '$2y$10$pLw8xUq4xUq4xUq4xUq4x.xUq4xUq4xUq4xUq4xUq4xUq4xUq4xUq', 'Demo Student', 'Student');
