database.sql

CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT PRIMARY KEY, fullname VARCHAR(255) NOT NULL, username VARCHAR(50) NOT NULL UNIQUE, password VARCHAR(255) NOT NULL, role ENUM('admin', 'guard', 'general') DEFAULT 'general', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS visitor_logs (id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NULL, id_card VARCHAR(13) NOT NULL, fullname VARCHAR(255) NOT NULL, phone VARCHAR(15), department VARCHAR(100), objective TEXT, image_path VARCHAR(255), entry_time DATETIME DEFAULT CURRENT_TIMESTAMP, exit_time DATETIME NULL, recorded_by INT, status ENUM('in', 'out') DEFAULT 'in', FOREIGN KEY (recorded_by) REFERENCES users(id), FOREIGN KEY (user_id) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS company_settings (id INT PRIMARY KEY, company_name VARCHAR(255), address TEXT, phone VARCHAR(20), logo VARCHAR(255)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO users (fullname, username, password, role) VALUES ('ระบบผู้ดูแล (Default)', 'admin', '$2y$10$mC7p7V.v/5vW5kFpY9XN7eLz5G5qX5G5qX5G5qX5G5qX5G5qX5G5q', 'admin'); INSERT INTO company_settings (id, company_name, address, phone) VALUES (1, 'บริษัท ตัวอย่าง จำกัด', '123 ถนนสุขุมวิท กรุงเทพฯ', '02-123-4567');