--
-- Host: localhost    Database: mmwave_v3
-- ------------------------------------------------------


--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `role` enum('admin','family','caregiver') NOT NULL,
  `line_user_id` varchar(100) DEFAULT NULL,
  `bind_code` varchar(10) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `last_login` datetime DEFAULT NULL,
  `notify_enabled` tinyint(1) DEFAULT 1,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` VALUES
(1,'U1224006','$2y$10$nBH0TYOW1xHGq3rRf0J6sONB1ni0c7.XdKEH/o8ku4wbMtopk0Rfu','王小明','admin','U44fadc9fa33b60a400fdd833fdeb9102','EEC338',1,'2026-01-13 16:20:52','2026-05-26 02:18:34',1),
(2,'U1224008','$2y$10$Q2c5IHNOLD5rF3nlfq4L7.1IgkzyzgRheBoD7IK53RAT3A9FWVHCm','哥哥','family',NULL,'6C5142',1,'2026-01-14 06:42:04','2026-05-25 21:24:14',1),
(3,'U1224009','$2y$10$3CqRj6vMLarDXdBYzamnduLA0jG1S50F8k61/eu0BHLWVc403BvEa','照護人員1','caregiver',NULL,'F764A1',1,'2026-01-14 06:48:31',NULL,1),
(4,'U1224018','scrypt:32768:8:1$7fLHOqY3VWWeqMw3$43624c2a70db1b190b22f32def344fe6c1ce77d49c11c460ae6aea8e7002806ad62d14adab681ef2e0e5379df8fe530686416f4866d9ca82576874741a78bb40','爸爸','family','Ue2e020c1e83d2fbfda4e7ed8f1aba741','A35FA9',1,'2026-03-18 06:14:01','2026-05-25 18:54:41',1),
(5,'U1224019','scrypt:32768:8:1$tVxKFfEvpDHYjRMy$3ae8723badb77bd9b5e9efe2c5bad8de6af06d6e634ea65c5bc201c03e5b872a89e8f44472624aa5ed5176c4bb31f4c91b14b18b6acfff0ca77da399353aa64c','媽媽','family','U44fadc9fa33b60a400fdd833fdeb9102','8ED6D3',1,'2026-03-18 06:16:02','2026-05-25 15:01:07',0);

--
-- Table structure for table `rooms`
--

CREATE TABLE `rooms` (
  `room_id` int(11) NOT NULL AUTO_INCREMENT,
  `room_name` varchar(100) NOT NULL,
  `last_activity` enum('walk','stand','sit','fall','lay','transition','null') DEFAULT 'null',
  `last_x` float DEFAULT 0,
  `last_y` float DEFAULT 0,
  `last_confidence` float DEFAULT 0,
  `current_act_start` datetime DEFAULT current_timestamp(),
  `last_seen_activity` datetime DEFAULT NULL,
  `last_seen_position` datetime DEFAULT NULL,
  PRIMARY KEY (`room_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `rooms`
--

INSERT INTO `rooms` VALUES
(1,'home','sit',1.2,2,0.59,'2026-05-26 01:42:13','2026-05-26 01:42:13','2026-05-26 01:35:21');

--
-- Table structure for table `radar_devices`
--

CREATE TABLE `radar_devices` (
  `device_id` int(11) NOT NULL AUTO_INCREMENT,
  `room_id` int(11) NOT NULL,
  `device_name` varchar(100) NOT NULL,
  `device_role` enum('position','activity','all') NOT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  PRIMARY KEY (`device_id`),
  KEY `room_id` (`room_id`),
  CONSTRAINT `radar_devices_ibfk_1` FOREIGN KEY (`room_id`) REFERENCES `rooms` (`room_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `radar_devices`
--

INSERT INTO `radar_devices` VALUES
(1,1,'rader1','activity','active'),
(2,1,'rader2','position','active');

--
-- Table structure for table `system_settings`
--

CREATE TABLE `system_settings` (
  `setting_key` varchar(50) NOT NULL,
  `setting_value` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `system_settings`
--

INSERT INTO `system_settings` VALUES
('test_connection','success_from_external_ip'),
('threshold_no_home_min','10'),
('threshold_no_move_min','3');


