-- Attendance Kiosk — database schema
-- Import this into your MySQL database via phpMyAdmin on cPanel.

CREATE TABLE IF NOT EXISTS staff (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    pin_hash VARCHAR(255) NOT NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS attendance_logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    staff_id INT UNSIGNED NOT NULL,
    punch_type ENUM('IN','OUT') NOT NULL,
    photo_path VARCHAR(255) NOT NULL,
    signature_path VARCHAR(255) NOT NULL,
    device_id VARCHAR(50) NOT NULL DEFAULT 'kiosk-01',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (staff_id) REFERENCES staff(id),
    INDEX idx_staff_date (staff_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS admin_users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Sample staff (PIN is "1234" for both — CHANGE after import)
-- Password hashes generated with PHP's password_hash(); replace before go-live.
INSERT INTO staff (name, pin_hash, active) VALUES
('Jane Doe', '$2y$10$examplehashexamplehashexamplehashexamplehash1', 1),
('John Mwangi', '$2y$10$examplehashexamplehashexamplehashexamplehash2', 1);

-- Sample admin login (username: admin / password: changeme)
-- Replace this hash immediately — see admin/create_admin.php helper below.
INSERT INTO admin_users (username, password_hash) VALUES
('admin', '$2y$10$examplehashexamplehashexamplehashexamplehash3');