-- Database: majoogaa

-- Roles table (optional but good for extensibility)
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    full_name VARCHAR(100),
    role ENUM('Admin', 'Manager') DEFAULT 'Manager',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Shifts table
CREATE TABLE IF NOT EXISTS shifts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    shift_name VARCHAR(50) NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    early_checkin_allowed INT DEFAULT 0, -- in minutes
    early_checkout_allowed INT DEFAULT 0, -- in minutes
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Employees table
CREATE TABLE IF NOT EXISTS employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id VARCHAR(20) UNIQUE NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    email VARCHAR(100),
    position VARCHAR(100),
    department VARCHAR(100),
    salary DECIMAL(10, 2),
    hire_date DATE,
    shift_id INT,
    status ENUM('Active', 'Inactive') DEFAULT 'Active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (shift_id) REFERENCES shifts(id) ON DELETE SET NULL
);

-- Face Data table
CREATE TABLE IF NOT EXISTS face_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    descriptor JSON NOT NULL, -- Storing the face-api.js descriptor array
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
);

-- 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,
    check_out_time TIME,
    status_in ENUM('On Time', 'Early', 'Late') DEFAULT 'On Time',
    status_out ENUM('On Time', 'Early', 'Late') DEFAULT 'On Time',
    early_in_minutes INT DEFAULT 0,
    late_in_minutes INT DEFAULT 0,
    early_out_minutes INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    UNIQUE KEY `one_per_day` (employee_id, date)
);

-- Insert a default Admin user (password: admin123 - will hash in backend later, for now just placeholder)
INSERT INTO users (username, password, full_name, role) 
VALUES ('admin', '$2a$10$7zYV.XmB6S/1Tz8iPzQ/7O4X5z8z8z8z8z8z8z8z8z8z8z8z8z8z', 'System Administrator', 'Admin')
ON DUPLICATE KEY UPDATE username=username;

-- Insert default shifts based on screenshots
INSERT INTO shifts (shift_name, start_time, end_time, early_checkin_allowed, early_checkout_allowed)
VALUES 
('Morning Shift', '08:00:00', '16:00:00', 60, 30),
('Evening Shift', '14:00:00', '22:00:00', 60, 30),
('Night Shift', '22:00:00', '06:00:00', 60, 30)
ON DUPLICATE KEY UPDATE shift_name=shift_name;
