-- Employee QR Code Attendance System Database
-- Run this SQL file in your MySQL database (via phpMyAdmin or command line)

CREATE DATABASE IF NOT EXISTS employee_attendance CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE employee_attendance;

-- Admins table
CREATE TABLE IF NOT EXISTS admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Employees table
CREATE TABLE IF NOT EXISTS employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    department VARCHAR(255) NOT NULL,
    qr_token VARCHAR(64) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Attendance table
CREATE TABLE IF NOT EXISTS attendance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    date DATE NOT NULL,
    check_in_time TIME NULL,
    check_out_time TIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    UNIQUE KEY unique_attendance (employee_id, date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create index for faster lookups
CREATE INDEX idx_employee_date ON attendance(employee_id, date);
CREATE INDEX idx_qr_token ON employees(qr_token);

-- Insert default admin
-- IMPORTANT: After importing this SQL, run create_admin.php to set up your admin account
-- OR manually insert with a proper password hash:
-- 
-- To generate password hash, run in PHP:
-- <?php echo password_hash('your_password', PASSWORD_DEFAULT); ?>
--
-- Then insert:
-- INSERT INTO admins (email, password_hash) VALUES 
-- ('admin@example.com', 'GENERATED_HASH_HERE')
-- ON DUPLICATE KEY UPDATE email=email;

-- Temporary admin (password: admin123) - Use create_admin.php for proper setup
-- This hash is for 'admin123': $2y$10$N9qo8uLOickgx2ZMRZoMyeIjZAgcfl7p92ldGxad68LJZdL17lhWy
INSERT INTO admins (email, password_hash) VALUES 
('admin@example.com', '$2y$10$N9qo8uLOickgx2ZMRZoMyeIjZAgcfl7p92ldGxad68LJZdL17lhWy')
ON DUPLICATE KEY UPDATE email=email;
