CREATE DATABASE IF NOT EXISTS access_control CHARACTER SET utf8mb4;
USE access_control;

CREATE TABLE visitors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    national_id VARCHAR(10) UNIQUE NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    phone VARCHAR(11),
    identity_verified TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE visit_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    visitor_id INT NOT NULL,
    guard_id INT NOT NULL,
    entry_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    exit_time TIMESTAMP NULL,
    equipment TEXT,
    status ENUM('inside','exited') DEFAULT 'inside',
    FOREIGN KEY (visitor_id) REFERENCES visitors(id),
    FOREIGN KEY (guard_id) REFERENCES users(id)
);
