CREATE DATABASE IF NOT EXISTS nicu_scan CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE nicu_scan;

CREATE TABLE IF NOT EXISTS babies (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tag_uid VARCHAR(32) NOT NULL UNIQUE,
  baby_name VARCHAR(120) NOT NULL,
  birth_date DATE NOT NULL,
  registration_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  signed_out_date DATETIME NULL,
  status ENUM('admitted','signed_out') NOT NULL DEFAULT 'admitted',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_status (status),
  INDEX idx_birth_date (birth_date)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS scan_history (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tag_uid VARCHAR(32) NOT NULL,
  action_type ENUM('scan','register','sign_out','update') NOT NULL,
  note VARCHAR(255) NULL,
  scanned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  device_name VARCHAR(120) NULL,
  ip_address VARCHAR(45) NULL,
  INDEX idx_uid_time (tag_uid, scanned_at),
  INDEX idx_action_time (action_type, scanned_at)
) ENGINE=InnoDB;