-- HRM employees previously had no link to any business unit at all, unlike every other
-- entity in this ERP. An employee can now belong to one or more businesses.
-- Idempotent (information_schema-guarded), additive only.
-- Deploy: mysql -u root -p db_abos_v0.1 < db/add_employee_business_access.sql

USE db_abos_v0.1;

CREATE TABLE IF NOT EXISTS employee_business_access (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    employee_id INT NOT NULL,
    business_id INT NOT NULL,
    is_deleted TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by INT NULL,
    INDEX idx_employee_business_access_employee (tenant_id, employee_id, is_deleted),
    INDEX idx_employee_business_access_business (tenant_id, business_id, is_deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;