USE hrms_payroll;

CREATE TABLE IF NOT EXISTS raw_attendance_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    employee_id BIGINT UNSIGNED NOT NULL,
    device_id BIGINT UNSIGNED NULL,
    punch_time DATETIME NOT NULL,
    punch_type ENUM('In', 'Out', 'Auto') DEFAULT 'Auto',
    source ENUM('Device', 'Web', 'Mobile', 'Manual') DEFAULT 'Device',
    latitude DECIMAL(10, 8) NULL,
    longitude DECIMAL(11, 8) NULL,
    ip_address VARCHAR(45) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE SET NULL,
    INDEX idx_employee_punch (employee_id, punch_time)
);

CREATE TABLE IF NOT EXISTS attendance_records (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    employee_id BIGINT UNSIGNED NOT NULL,
    date DATE NOT NULL,
    shift_id BIGINT UNSIGNED NULL,
    first_in DATETIME NULL,
    last_out DATETIME NULL,
    total_work_minutes INT UNSIGNED DEFAULT 0,
    total_break_minutes INT UNSIGNED DEFAULT 0,
    overtime_minutes INT UNSIGNED DEFAULT 0,
    status ENUM('Present', 'Absent', 'HalfDay', 'WeekOff', 'Holiday', 'OnLeave') DEFAULT 'Absent',
    late_minutes INT UNSIGNED DEFAULT 0,
    early_out_minutes INT UNSIGNED DEFAULT 0,
    is_regularized BOOLEAN DEFAULT FALSE,
    reconciliation_status ENUM('Reconciled', 'PendingException', 'Unpaired') DEFAULT 'Reconciled',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    FOREIGN KEY (shift_id) REFERENCES shifts(id) ON DELETE SET NULL,
    UNIQUE KEY uq_emp_date (employee_id, date)
);